Subversion Repositories eFlore/Projets.eflore-projets

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
224 jpm 1
-- -----------------------------------------------------
2
-- Table `ontologie_type`
3
-- -----------------------------------------------------
4
CREATE  TABLE IF NOT EXISTS `ontologie_type` (
5
  `id_type` INT NOT NULL ,
6
  `id_type_parent` INT NOT NULL DEFAULT 0 ,
7
  `type` VARCHAR(45) NULL ,
8
  `description` TEXT NULL ,
9
  PRIMARY KEY (`id_type`) )
10
ENGINE = MyISAM
11
DEFAULT CHARACTER SET = utf8
12
COLLATE = utf8_general_ci;
13
 
14
 
15
-- -----------------------------------------------------
16
-- Table `ontologie_auteur`
17
-- -----------------------------------------------------
18
CREATE  TABLE IF NOT EXISTS `ontologie_auteur` (
19
  `id_auteur` INT NOT NULL ,
20
  `prenom` VARCHAR(45) NULL ,
21
  `nom` VARCHAR(45) NULL ,
22
  `pseudo` VARCHAR(90) NULL ,
23
  `naissance_date` DATE NULL ,
24
  `deces_date` DATE NULL ,
25
  PRIMARY KEY (`id_auteur`) )
26
ENGINE = MyISAM
27
DEFAULT CHARACTER SET = utf8
28
COLLATE = utf8_general_ci;
29
 
30
 
31
-- -----------------------------------------------------
32
-- Table `ontologie_publication`
33
-- -----------------------------------------------------
34
CREATE  TABLE IF NOT EXISTS `ontologie_publication` (
35
  `id_publication` INT NOT NULL ,
36
  `ce_auteur_principal` INT NOT NULL ,
37
  `titre` VARCHAR(255) NULL ,
38
  `date` DATE NULL ,
39
  `uri` VARCHAR(255) NULL ,
40
  PRIMARY KEY (`id_publication`) ,
41
  INDEX `fk_ontologie_publication_ontologie_auteur1` (`ce_auteur_principal` ASC) ,
42
  CONSTRAINT `fk_ontologie_publication_ontologie_auteur1`
43
    FOREIGN KEY (`ce_auteur_principal` )
44
    REFERENCES `ontologie_auteur` (`id_auteur` )
45
    ON DELETE NO ACTION
46
    ON UPDATE NO ACTION)
47
ENGINE = MyISAM
48
DEFAULT CHARACTER SET = utf8
49
COLLATE = utf8_general_ci;
50
 
51
 
52
-- -----------------------------------------------------
53
-- Table `ontologie_image`
54
-- -----------------------------------------------------
55
CREATE  TABLE IF NOT EXISTS `ontologie_image` (
56
  `id_image` INT NOT NULL ,
57
  `uri` VARCHAR(255) NULL ,
58
  `ce_publication` INT NOT NULL ,
59
  PRIMARY KEY (`id_image`) ,
60
  INDEX `fk_ontologie_image_ontologie_publication1` (`ce_publication` ASC) ,
61
  CONSTRAINT `fk_ontologie_image_ontologie_publication1`
62
    FOREIGN KEY (`ce_publication` )
63
    REFERENCES `ontologie_publication` (`id_publication` )
64
    ON DELETE NO ACTION
65
    ON UPDATE NO ACTION)
66
ENGINE = MyISAM
67
DEFAULT CHARACTER SET = utf8
68
COLLATE = utf8_general_ci;
69
 
70
 
71
-- -----------------------------------------------------
72
-- Table `ontologie_terme`
73
-- -----------------------------------------------------
74
CREATE  TABLE IF NOT EXISTS `ontologie_terme` (
75
  `id_terme` INT NOT NULL COMMENT 'Identifiant global du terme (GLOBALID)' ,
76
  `ce_type` INT NOT NULL ,
77
  `terme` VARCHAR(255) NOT NULL ,
78
  `definition` TEXT NULL ,
79
  `terme_fr` VARCHAR(255) NULL ,
80
  `definition_fr` TEXT NULL ,
81
  `notes_fr` TEXT NULL ,
82
  `preference` TINYINT(1)  NULL COMMENT '1 = PREFERED TERM\n0 = DISALLOWED TERM\n' ,
83
  `ce_auteur` INT NOT NULL ,
84
  `ce_publication` INT NOT NULL ,
85
  `ce_image` INT NOT NULL ,
86
  PRIMARY KEY (`id_terme`) ,
87
  INDEX `fk_ontologie_terme_ontologie_terme_type` (`ce_type` ASC) ,
88
  INDEX `fk_ontologie_terme_ontologie_auteur1` (`ce_auteur` ASC) ,
89
  INDEX `fk_ontologie_terme_ontologie_publication1` (`ce_publication` ASC) ,
90
  INDEX `fk_ontologie_terme_ontologie_image1` (`ce_image` ASC) ,
91
  CONSTRAINT `fk_ontologie_terme_ontologie_terme_type`
92
    FOREIGN KEY (`ce_type` )
93
    REFERENCES `ontologie_type` (`id_type` )
94
    ON DELETE NO ACTION
95
    ON UPDATE NO ACTION,
96
  CONSTRAINT `fk_ontologie_terme_ontologie_auteur1`
97
    FOREIGN KEY (`ce_auteur` )
98
    REFERENCES `ontologie_auteur` (`id_auteur` )
99
    ON DELETE NO ACTION
100
    ON UPDATE NO ACTION,
101
  CONSTRAINT `fk_ontologie_terme_ontologie_publication1`
102
    FOREIGN KEY (`ce_publication` )
103
    REFERENCES `ontologie_publication` (`id_publication` )
104
    ON DELETE NO ACTION
105
    ON UPDATE NO ACTION,
106
  CONSTRAINT `fk_ontologie_terme_ontologie_image1`
107
    FOREIGN KEY (`ce_image` )
108
    REFERENCES `ontologie_image` (`id_image` )
109
    ON DELETE NO ACTION
110
    ON UPDATE NO ACTION)
111
ENGINE = MyISAM
112
DEFAULT CHARACTER SET = utf8
113
COLLATE = utf8_general_ci;
114
 
115
 
116
-- -----------------------------------------------------
117
-- Table `ontologie_hierarchie`
118
-- -----------------------------------------------------
119
CREATE  TABLE IF NOT EXISTS `ontologie_hierarchie` (
120
  `id_noeud` INT NOT NULL ,
121
  `id_noeud_parent` INT NOT NULL ,
122
  `chemin_noms` VARCHAR(500) NOT NULL ,
123
  `chemin_ids` VARCHAR(100) NOT NULL ,
124
  `ce_terme` INT NOT NULL ,
125
  PRIMARY KEY (`id_noeud`) ,
126
  INDEX `fk_ontologie_hierarchie_ontologie_terme1` (`ce_terme` ASC) ,
127
  CONSTRAINT `fk_ontologie_hierarchie_ontologie_terme1`
128
    FOREIGN KEY (`ce_terme` )
129
    REFERENCES `ontologie_terme` (`id_terme` )
130
    ON DELETE NO ACTION
131
    ON UPDATE NO ACTION)
132
ENGINE = MyISAM
133
DEFAULT CHARACTER SET = utf8
134
COLLATE = utf8_general_ci;
135
 
136
 
137
-- -----------------------------------------------------
138
-- Table `ontologie_relation`
139
-- -----------------------------------------------------
140
CREATE  TABLE IF NOT EXISTS `ontologie_relation` (
141
  `id_terme_01` INT NOT NULL ,
142
  `id_terme_02` INT NOT NULL ,
143
  `relation` VARCHAR(45) NULL COMMENT 'Relation entre terme_01 et terme_02.' ,
144
  PRIMARY KEY (`id_terme_01`, `id_terme_02`) ,
145
  INDEX `fk_ontologie_relations_ontologie_terme2` (`id_terme_02` ASC) ,
146
  CONSTRAINT `fk_ontologie_relations_ontologie_terme1`
147
    FOREIGN KEY (`id_terme_01` )
148
    REFERENCES `ontologie_terme` (`id_terme` )
149
    ON DELETE NO ACTION
150
    ON UPDATE NO ACTION,
151
  CONSTRAINT `fk_ontologie_relations_ontologie_terme2`
152
    FOREIGN KEY (`id_terme_02` )
153
    REFERENCES `ontologie_terme` (`id_terme` )
154
    ON DELETE NO ACTION
155
    ON UPDATE NO ACTION)
156
ENGINE = MyISAM
157
DEFAULT CHARACTER SET = utf8
158
COLLATE = utf8_general_ci;
159
 
160
-- -----------------------------------------------------
161
-- Data for table `ontologie_type`
162
-- -----------------------------------------------------
163
 
164
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (1, 0, 'Global', NULL);
165
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (4, 1, 'Modificateur relatif', NULL);
166
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (5, 1, 'Valeur relative', NULL);
167
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (6, 1, 'Modificateur spatial', NULL);
168
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (7, 1, 'Localisateur de region', NULL);
169
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (8, 1, 'Modificateur temporel', NULL);
170
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (9, 1, 'Unité', NULL);
171
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (10, 1, 'Propriété quantitative', NULL);
172
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (11, 1, 'Propriété quantitative redéfinie', NULL);
173
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (12, 0, 'Botanique', NULL);
174
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (13, 12, 'État qualitatif', NULL);
175
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (14, 12, 'Type de structure', NULL);
176
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (15, 12, 'Structure', NULL);
177
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (16, 12, 'Région', NULL);
178
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (17, 12, 'Structure générique', NULL);
179
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (18, 12, 'Groupe d\'états', NULL);
180
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (19, 18, 'Sous-groupe d\'états', NULL);
181
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (2, 1, 'Modificateur de fréquence', NULL);
182
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (3, 1, 'Qualificateur', NULL);