1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
|
# La table contenant les points
CREATE TABLE `points` (
`record_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`obj_id` int(11) unsigned NOT NULL,
`version` smallint(5) unsigned NOT NULL DEFAULT '1',
`model_id` varchar(250) DEFAULT NULL COMMENT,
`point` POINT
PRIMARY KEY (`record_id`),
KEY `obj_index` (`obj_id`),
KEY `model` (`model_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
# La table représentant un enregistrement de chemin (rien de géométrique là-dedans).
CREATE TABLE `chemins` (
`record_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`obj_id` int(11) unsigned NOT NULL,
`version` smallint(5) unsigned NOT NULL DEFAULT '1',
`model_id` varchar(250) DEFAULT NULL COMMENT,
PRIMARY KEY (`record_id`),
KEY `obj_index` (`obj_id`),
KEY `model` (`model_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
# La table qui constitue un noeud membre d'un chemin.
CREATE TABLE `chemins_contenu` (
`content_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`classe` text NOT NULL,
`obj_id` int(10) unsigned NOT NULL,
`position` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`content_id`),
UNIQUE KEY `unicity` (`obj_id`,`classe`(100),`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
# Enfin la table qui fait le lien entre les noeuds membres et les chemins
CREATE TABLE `chemins_contenuCollection` (
`record_id` int(11) unsigned NOT NULL,
`content_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`record_id`,`content_id`),
KEY `content_id` (`content_id`),
CONSTRAINT `ibfk_1` FOREIGN KEY (`record_id`) REFERENCES `chemins` (`record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `ibfk_2` FOREIGN KEY (`content_id`) REFERENCES `chemins_contenu` (`content_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Partager