Subversion Repositories eFlore/Applications.cel

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
1174 jpm 1
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
 
5
 
6
-- -----------------------------------------------------
1202 jpm 7
-- Table `cel_obs_images`
8
-- -----------------------------------------------------
9
DROP TABLE IF EXISTS `cel_obs_images` ;
10
 
11
CREATE  TABLE IF NOT EXISTS `cel_obs_images` (
12
  `id_image` BIGINT NOT NULL ,
13
  `id_observation` BIGINT NOT NULL ,
14
  `date_liaison` DATETIME NOT NULL ,
15
  PRIMARY KEY (`id_image`, `id_observation`) ,
16
  INDEX `observation` (`id_observation` ASC) ,
17
  INDEX `image` (`id_image` ASC) )
18
ENGINE = MyISAM
19
DEFAULT CHARACTER SET = utf8
20
COLLATE = utf8_unicode_ci;
21
 
22
 
23
-- -----------------------------------------------------
1174 jpm 24
-- Table `cel_utilisateurs`
25
-- -----------------------------------------------------
26
DROP TABLE IF EXISTS `cel_utilisateurs` ;
27
 
28
CREATE  TABLE IF NOT EXISTS `cel_utilisateurs` (
29
  `id_utilisateur` INT NOT NULL ,
1196 jpm 30
  `prenom` VARCHAR(255) NULL DEFAULT NULL ,
31
  `nom` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
1174 jpm 32
  `courriel` VARCHAR(255) NOT NULL ,
1196 jpm 33
  `mot_de_passe` VARCHAR(45) NOT NULL ,
34
  `admin` TINYINT(1) NULL DEFAULT '0' ,
35
  `licence_acceptee` TINYINT(1) NULL DEFAULT '0' COMMENT 'Acceptation de la licence utilisateur pour le cel\n' ,
36
  `preferences` LONGTEXT NULL DEFAULT NULL COMMENT 'Préférences utilisateur sérialisées sous une forme à définir\n' ,
37
  `date__premiere_utilisation` DATETIME NOT NULL ,
1174 jpm 38
  PRIMARY KEY (`id_utilisateur`) )
39
ENGINE = MyISAM
40
DEFAULT CHARACTER SET = utf8
41
COLLATE = utf8_general_ci;
42
 
43
 
44
-- -----------------------------------------------------
45
-- Table `cel_mots_cles_images`
46
-- -----------------------------------------------------
47
DROP TABLE IF EXISTS `cel_mots_cles_images` ;
48
 
49
CREATE  TABLE IF NOT EXISTS `cel_mots_cles_images` (
50
  `id_mot_cle_image` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL COMMENT 'Identifiant du mot-clé pour une position donnée dans l\'arbre d\'utilisateur.\nLe même mot-clé peut être placé à plusieurs endroits de l\'arbre et aura donc deux id distincts.' ,
1202 jpm 51
  `id_utilisateur` VARCHAR(32) NOT NULL ,
1174 jpm 52
  `mot_cle` VARCHAR(50) NOT NULL COMMENT 'Mot clé de l\'utilisateur pour une position dans l\'arbre.' ,
53
  `md5` VARCHAR(32) NOT NULL COMMENT 'MD5 du mot clé en minuscule.' ,
54
  `bg` BIGINT NOT NULL COMMENT 'Bordure gauche.' ,
55
  `bd` BIGINT NOT NULL COMMENT 'bordure droite.' ,
56
  `niveau` INT NOT NULL COMMENT 'Niveau du mot clé dans l\'arbre.' ,
57
  `ce_mot_cle_image_parent` VARCHAR(128) NOT NULL ,
1202 jpm 58
  PRIMARY KEY (`id_mot_cle_image`, `id_utilisateur`) )
1174 jpm 59
ENGINE = InnoDB
60
DEFAULT CHARACTER SET = utf8
61
COLLATE = utf8_general_ci
1185 jpm 62
COMMENT = 'Table des mots clés, à utiliser avec des transactions !\n'
1174 jpm 63
ROW_FORMAT = COMPACT;
64
 
65
 
66
-- -----------------------------------------------------
67
-- Table `cel_images_mots_cles`
68
-- -----------------------------------------------------
69
DROP TABLE IF EXISTS `cel_images_mots_cles` ;
70
 
71
CREATE  TABLE IF NOT EXISTS `cel_images_mots_cles` (
72
  `id_image` BIGINT NOT NULL ,
73
  `id_mot_cle_image` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
1202 jpm 74
  `id_utilisateur` VARCHAR(32) NOT NULL ,
75
  PRIMARY KEY (`id_image`, `id_mot_cle_image`, `id_utilisateur`) ,
1174 jpm 76
  INDEX `image` (`id_image` ASC) )
77
ENGINE = MyISAM
78
DEFAULT CHARACTER SET = utf8
79
COLLATE = utf8_general_ci;
80
 
81
 
82
-- -----------------------------------------------------
83
-- Table `cel_images`
84
-- -----------------------------------------------------
85
DROP TABLE IF EXISTS `cel_images` ;
86
 
87
CREATE  TABLE IF NOT EXISTS `cel_images` (
88
  `id_image` BIGINT NOT NULL AUTO_INCREMENT ,
89
  `ordre` BIGINT NOT NULL ,
90
  `ce_utilisateur` VARCHAR(32) NOT NULL COMMENT 'L\'id utilisateur est un int mais on utilise un varchar pour stocker des observations avec des identifiants temporaires\n' ,
1195 jpm 91
  `prenom_utilisateur` VARCHAR(255) NULL DEFAULT NULL ,
92
  `nom_utilisateur` VARCHAR(255) NULL DEFAULT NULL ,
93
  `courriel_utilisateur` VARCHAR(255) NULL DEFAULT NULL ,
1174 jpm 94
  `hauteur` INT NOT NULL ,
95
  `largeur` INT NOT NULL ,
96
  `appareil_fabriquant` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
97
  `appareil_modele` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
98
  `date_prise_de_vue` DATETIME NULL DEFAULT NULL ,
1197 jpm 99
  `note_qualite` DECIMAL(1,0) NULL DEFAULT NULL ,
1195 jpm 100
  `mots_cles_texte` LONGTEXT NULL DEFAULT NULL COMMENT 'Champ calculé contenant la liste des mots clés utilisateurs séparé par des virgules.\n' ,
1174 jpm 101
  `commentaire` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
102
  `nom_original` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
103
  `md5` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
104
  `meta_exif` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
105
  `meta_iptc` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
1195 jpm 106
  `meta_xmp` LONGTEXT NULL DEFAULT NULL ,
107
  `meta_makernote` LONGTEXT NULL DEFAULT NULL ,
1202 jpm 108
  `meta_autres` LONGTEXT NULL DEFAULT NULL ,
1196 jpm 109
  `date_modification` DATETIME NOT NULL ,
110
  `date_creation` DATETIME NOT NULL COMMENT 'Date d\'ajout de l\'image au CEL.' ,
1195 jpm 111
  `publiable_eflore` TINYINT(1) NOT NULL DEFAULT 1 ,
1174 jpm 112
  PRIMARY KEY (`id_image`) ,
113
  INDEX `id_image` (`id_image` ASC, `ordre` ASC, `ce_utilisateur` ASC) )
114
ENGINE = MyISAM
115
AUTO_INCREMENT = 265
116
DEFAULT CHARACTER SET = utf8
117
COLLATE = utf8_general_ci;
118
 
119
 
120
-- -----------------------------------------------------
121
-- Table `cel_mots_cles_obs`
122
-- -----------------------------------------------------
123
DROP TABLE IF EXISTS `cel_mots_cles_obs` ;
124
 
125
CREATE  TABLE IF NOT EXISTS `cel_mots_cles_obs` (
126
  `id_mot_cle_obs` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
1202 jpm 127
  `id_utilisateur` VARCHAR(32) NOT NULL ,
1174 jpm 128
  `mot_cle` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
129
  `md5` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
130
  `bg` BIGINT NOT NULL ,
131
  `bd` BIGINT NOT NULL ,
132
  `niveau` INT NOT NULL ,
133
  `ce_mot_cle_obs_parent` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
1202 jpm 134
  PRIMARY KEY (`id_mot_cle_obs`, `id_utilisateur`) )
1174 jpm 135
ENGINE = InnoDB
136
DEFAULT CHARACTER SET = utf8
1185 jpm 137
COLLATE = utf8_general_ci
138
COMMENT = 'Table des mots clés, à utiliser avec des transactions !'
1174 jpm 139
ROW_FORMAT = COMPACT;
140
 
141
 
142
-- -----------------------------------------------------
143
-- Table `cel_obs_mots_cles`
144
-- -----------------------------------------------------
145
DROP TABLE IF EXISTS `cel_obs_mots_cles` ;
146
 
147
CREATE  TABLE IF NOT EXISTS `cel_obs_mots_cles` (
148
  `id_observation` BIGINT NOT NULL ,
149
  `id_mot_cle_obs` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
1202 jpm 150
  `id_utilisateur` VARCHAR(32) NOT NULL ,
151
  PRIMARY KEY (`id_observation`, `id_mot_cle_obs`, `id_utilisateur`) ,
1174 jpm 152
  INDEX `observation` (`id_observation` ASC) )
153
ENGINE = MyISAM
154
DEFAULT CHARACTER SET = utf8
155
COLLATE = utf8_general_ci;
156
 
157
 
158
-- -----------------------------------------------------
159
-- Table `cel_zones_geo`
160
-- -----------------------------------------------------
161
DROP TABLE IF EXISTS `cel_zones_geo` ;
162
 
163
CREATE  TABLE IF NOT EXISTS `cel_zones_geo` (
164
  `id_zone_geo` VARCHAR(50) NOT NULL ,
165
  `code` VARCHAR(10) NOT NULL ,
166
  `nom` VARCHAR(255) NOT NULL ,
167
  `utm_secteur` CHAR(3) NOT NULL ,
168
  `utm_x` INT NOT NULL DEFAULT '0' ,
169
  `utm_y` INT NOT NULL DEFAULT '0' ,
170
  `wgs84_latitude` FLOAT NOT NULL ,
171
  `wgs84_longitude` FLOAT NOT NULL ,
172
  `date_modification` DATETIME NOT NULL ,
1195 jpm 173
  `ce_zone_geo_parente` VARCHAR(50) NULL DEFAULT NULL ,
1174 jpm 174
  `bg` BIGINT NULL ,
175
  `bd` BIGINT NULL ,
176
  `niveau` INT NULL ,
177
  PRIMARY KEY (`id_zone_geo`) ,
178
  INDEX `nom` (`nom` ASC) )
179
ENGINE = MyISAM
180
DEFAULT CHARACTER SET = utf8
181
COLLATE = utf8_general_ci;
182
 
183
 
184
-- -----------------------------------------------------
185
-- Table `cel_obs`
186
-- -----------------------------------------------------
187
DROP TABLE IF EXISTS `cel_obs` ;
188
 
189
CREATE  TABLE IF NOT EXISTS `cel_obs` (
190
  `id_observation` BIGINT NOT NULL AUTO_INCREMENT ,
191
  `ordre` BIGINT NOT NULL ,
192
  `ce_utilisateur` VARCHAR(32) NOT NULL ,
1195 jpm 193
  `prenom_utilisateur` VARCHAR(255) NULL DEFAULT NULL ,
194
  `nom_utilisateur` VARCHAR(255) NULL DEFAULT NULL ,
195
  `courriel_utilisateur` VARCHAR(255) NULL DEFAULT NULL ,
196
  `nom_sel` VARCHAR(255) NULL DEFAULT NULL ,
197
  `nom_sel_nn` DECIMAL(9,0) NULL DEFAULT NULL COMMENT 'Numéro du nom sélectionné.' ,
198
  `nom_ret` VARCHAR(255) NULL DEFAULT NULL ,
199
  `nom_ret_nn` DECIMAL(9,0) NULL DEFAULT NULL COMMENT 'Numéro du nom retenu.' ,
200
  `nt` DECIMAL(9,0) NULL DEFAULT NULL COMMENT 'Numéro taxonomique.' ,
201
  `famille` VARCHAR(255) NULL DEFAULT NULL ,
202
  `nom_referentiel` VARCHAR(255) NULL DEFAULT NULL ,
203
  `ce_zone_geo` VARCHAR(50) NULL DEFAULT NULL ,
204
  `zone_geo` VARCHAR(255) NULL DEFAULT NULL ,
205
  `lieudit` VARCHAR(255) NULL DEFAULT NULL ,
206
  `station` VARCHAR(255) NULL DEFAULT NULL ,
207
  `milieu` VARCHAR(255) NULL DEFAULT NULL ,
208
  `latitude` DECIMAL(8,5) NULL DEFAULT NULL ,
209
  `longitude` DECIMAL(8,5) NULL DEFAULT NULL ,
210
  `geodatum` VARCHAR(25) NULL DEFAULT NULL COMMENT 'Référentiel géographique utilisé.\nPar exmple : WGS84' ,
1196 jpm 211
  `date_observation` DATETIME NULL DEFAULT NULL ,
1195 jpm 212
  `mots_cles_texte` LONGTEXT NULL DEFAULT NULL COMMENT 'Champ calculé contenant la liste des mots clés utilisateurs séparé par des virgules.' ,
213
  `commentaire` TEXT NULL DEFAULT NULL ,
214
  `transmission` TINYINT(1) NULL DEFAULT NULL ,
1196 jpm 215
  `date_creation` DATETIME NULL DEFAULT NULL ,
216
  `date_modification` DATETIME NULL DEFAULT NULL ,
217
  `date_transmission` DATETIME NULL DEFAULT NULL ,
1174 jpm 218
  PRIMARY KEY (`id_observation`) ,
219
  INDEX `id_obs` (`ordre` ASC, `ce_utilisateur` ASC) ,
220
  INDEX `date_creation` (`ce_utilisateur` ASC, `date_creation` ASC) ,
221
  INDEX `coordonnees` (`latitude` ASC, `longitude` ASC) ,
222
  INDEX `nom_retenu` (`nom_ret` ASC) ,
223
  INDEX `date_observation` (`date_observation` ASC) )
224
ENGINE = MyISAM
225
DEFAULT CHARACTER SET = utf8
226
COLLATE = utf8_general_ci;
227
 
1196 jpm 228
 
1197 jpm 229
-- -----------------------------------------------------
230
-- Table `cel_utilisateurs_infos`
231
-- -----------------------------------------------------
232
DROP TABLE IF EXISTS `cel_utilisateurs_infos` ;
233
 
234
CREATE  TABLE IF NOT EXISTS `cel_utilisateurs_infos` (
235
  `id_utilisateur` INT NOT NULL ,
236
  `admin` TINYINT(1) NOT NULL DEFAULT 0 ,
237
  `licence_acceptee` TINYINT(1) NOT NULL DEFAULT 0 ,
238
  `preferences` LONGTEXT NULL DEFAULT NULL ,
239
  `date_premiere_utilisation` DATETIME NOT NULL ,
240
  PRIMARY KEY (`id_utilisateur`) )
241
ENGINE = MyISAM
242
DEFAULT CHARACTER SET = utf8
243
COLLATE = utf8_general_ci
244
COMMENT = 'Contient les infos utilisateurs spécifiques au CEL.\nA utilis' /* comment truncated */;
245
 
246
 
247
-- -----------------------------------------------------
1202 jpm 248
-- Placeholder table for view `cel_utilisateurs`
249
-- -----------------------------------------------------
250
CREATE TABLE IF NOT EXISTS `cel_utilisateurs` (`id_utilisateur` INT, `prenom` INT, `nom` INT, `courriel` INT, `mot_de_passe` INT, `licence_acceptee` INT, `admin` INT, `preferences` INT, `date_premiere_utilisation` INT);
251
 
252
-- -----------------------------------------------------
1197 jpm 253
-- Placeholder table for view `cel_tapir`
254
-- -----------------------------------------------------
255
CREATE TABLE IF NOT EXISTS `cel_tapir` (`guid` INT, `observation_id` INT, `observation_date` INT, `nom_scientifique_complet` INT, `nom_num_nomenclatural` INT, `nom_num_taxonomique` INT, `nom_famille` INT, `lieu_commune_nom_complet` INT, `lieu_commune_nom` INT, `lieu_commune_code_insee` INT, `lieu_commune_utm_coordonnees_bruts` INT, `lieu_commune_utm_zone` INT, `lieu_commune_utm_x` INT, `lieu_commune_utm_y` INT, `lieu_latitude` INT, `lieu_longitude` INT, `lieu_geodatum` INT, `lieu_localite` INT, `observateur_prenom` INT, `observateur_nom` INT, `observateur_courriel` INT, `observateur_intitule` INT, `observation_commentaire` INT, `observation_information_complement` INT, `saisie_date_modification` INT, `saisie_date_creation` INT);
256
 
257
-- -----------------------------------------------------
258
-- View `cel_utilisateurs`
259
-- -----------------------------------------------------
260
DROP VIEW IF EXISTS `cel_utilisateurs` ;
261
DROP TABLE IF EXISTS `cel_utilisateurs`;
262
CREATE  OR REPLACE VIEW `cel_utilisateurs` AS
263
	SELECT at.U_ID AS id_utilisateur, at.U_SURNAME AS prenom, at.U_NAME AS nom, at.U_MAIL AS courriel, at.U_PASSWD AS mot_de_passe,
264
		ui.licence_acceptee, ui.admin, ui.preferences, ui.date_premiere_utilisation
265
	FROM cel_utilisateurs_infos AS ui
266
		LEFT JOIN tela_prod_v4.annuaire_tela AS at ON (ui.id_utilisateur = at.U_ID);
267
 
268
-- -----------------------------------------------------
269
-- View `cel_tapir`
270
-- -----------------------------------------------------
271
DROP VIEW IF EXISTS `cel_tapir` ;
272
DROP TABLE IF EXISTS `cel_tapir`;
273
DELIMITER $$
274
CREATE  OR REPLACE VIEW `cel_tapir` AS
275
    SELECT concat(_utf8'urn:lsid:tela-botanica.org:cel:', o.id_observation) AS guid,
276
        o.id_observation AS observation_id,
277
        date_format(o.date_observation,'%Y-%m-%d') AS observation_date,
278
        o.nom_sel AS nom_scientifique_complet,
279
        o.nom_sel_nn AS nom_num_nomenclatural,
280
        o.nt AS nom_num_taxonomique,
281
        o.famille AS nom_famille,
282
        concat(_utf8'',zg.nom,' [INSEE:',zg.code,']') AS lieu_commune_nom_complet,
283
        zg.nom AS lieu_commune_nom,
284
        zg.code AS lieu_commune_code_insee,
285
        concat(zg.utm_secteur,' ',zg.utm_x,'m E ',zg.utm_y,'m N') AS lieu_commune_utm_coordonnees_bruts,
286
        zg.utm_secteur AS lieu_commune_utm_zone,
287
        zg.utm_x AS lieu_commune_utm_x,
288
        zg.utm_y AS lieu_commune_utm_y,
289
        o.latitude AS lieu_latitude,
290
        o.longitude AS lieu_longitude,
291
        o.geodatum AS lieu_geodatum,
292
        o.lieudit AS lieu_localite,
293
        o.prenom_utilisateur AS observateur_prenom,
294
        o.nom_utilisateur AS observateur_nom,
295
        o.courriel_utilisateur AS observateur_courriel,
296
        concat(o.prenom_utilisateur,_utf8' ',o.nom_utilisateur) AS observateur_intitule,
297
        o.commentaire AS observation_commentaire,
298
        concat(_utf8'<?xml version="1.0" encoding="UTF-8"?>',
299
            '<info>',
300
            '<nom_num_nomenclatural>',o.nom_sel_nn,'</nom_num_nomenclatural>',
1202 jpm 301
            '<nom_ret>',o.nom_ret,'</nom_ret>',
302
            '<nom_num_ret>',o.nom_ret_nn,'</nom_num_ret>',
1197 jpm 303
            '<nom_num_taxonomique>',o.nt,'</nom_num_taxonomique>',
304
            '<nom_referentiel>',o.nom_referentiel,'</nom_referentiel>',
305
            '<saisie_date_transmission>',o.date_transmission,'</saisie_date_transmission>',
306
            '<saisie_date_creation>',o.date_creation,'</saisie_date_creation>',
307
            '<ordre>',o.ordre,'</ordre>',
308
            '<station>',o.station,'</station>',
309
            '<milieu>',o.milieu,'</milieu>',
310
            '<mots_cles>',o.mots_cles_texte,'</mots_cles>',
1202 jpm 311
            '<zg_utm_secteur>',zg.utm_secteur,'</zg_utm_secteur>',
1197 jpm 312
            '<zg_date_modification>',zg.date_modification,'</zg_date_modification>',
313
            '</info>') AS observation_information_complement,
314
        o.date_modification AS saisie_date_modification,
315
        o.date_creation AS saisie_date_creation
316
    FROM cel_obs AS o
317
        LEFT JOIN cel_zones_geo AS zg ON (o.ce_zone_geo = zg.id_zone_geo)
318
    WHERE o.transmission = 1;
319
$$
320
DELIMITER ;
321
 
322
;
323
 
324
 
1174 jpm 325
SET SQL_MODE=@OLD_SQL_MODE;
326
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
1196 jpm 327
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;