Line 21... |
Line 21... |
21 |
PRIMARY KEY (`id_utilisateur`) )
|
21 |
PRIMARY KEY (`id_utilisateur`) )
|
22 |
ENGINE = MyISAM
|
22 |
ENGINE = MyISAM
|
23 |
DEFAULT CHARACTER SET = utf8
|
23 |
DEFAULT CHARACTER SET = utf8
|
24 |
COLLATE = utf8_general_ci;
|
24 |
COLLATE = utf8_general_ci;
|
Line 25... |
Line -... |
25 |
|
- |
|
26 |
-- -----------------------------------------------------
|
- |
|
27 |
-- Table `cel_utilisateurs_infos`
|
- |
|
28 |
-- -----------------------------------------------------
|
- |
|
29 |
DROP TABLE IF EXISTS `cel_utilisateurs_infos` ;
|
- |
|
30 |
|
- |
|
31 |
CREATE TABLE IF NOT EXISTS `cel_utilisateurs_infos` (
|
- |
|
32 |
`id_utilisateur` INT NOT NULL ,
|
- |
|
33 |
`admin` TINYINT(1) NOT NULL DEFAULT 0 ,
|
- |
|
34 |
`licence_acceptee` TINYINT(1) NOT NULL DEFAULT 0 ,
|
- |
|
35 |
`preferences` LONGTEXT NULL DEFAULT NULL ,
|
- |
|
36 |
`date_premiere_utilisation` DATETIME NOT NULL ,
|
- |
|
37 |
PRIMARY KEY (`id_utilisateur`) )
|
- |
|
38 |
ENGINE = MyISAM
|
- |
|
39 |
DEFAULT CHARACTER SET = utf8
|
- |
|
40 |
COLLATE = utf8_general_ci
|
- |
|
41 |
COMMENT = 'Contient les infos utilisateurs spécifiques au CEL.\nA utilis' /* comment truncated */;
|
- |
|
42 |
|
- |
|
43 |
|
- |
|
44 |
-- -----------------------------------------------------
|
- |
|
45 |
-- View `cel_utilisateurs`
|
- |
|
46 |
-- -----------------------------------------------------
|
- |
|
47 |
DROP VIEW IF EXISTS `cel_utilisateurs` ;
|
- |
|
48 |
DROP TABLE IF EXISTS `cel_utilisateurs`;
|
- |
|
49 |
CREATE OR REPLACE VIEW `cel_utilisateurs` AS
|
- |
|
50 |
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,
|
- |
|
51 |
ui.licence_acceptee, ui.admin, ui.preferences
|
- |
|
52 |
FROM cel_utilisateurs_infos AS ui
|
- |
|
53 |
LEFT JOIN tela_prod_v4.annuaire_tela AS at ON (ui.id_utilisateur = at.U_ID);
|
- |
|
Line 54... |
Line 25... |
54 |
|
25 |
|
55 |
|
26 |
|
56 |
-- -----------------------------------------------------
|
27 |
-- -----------------------------------------------------
|
57 |
-- Table `cel_obs_images`
|
28 |
-- Table `cel_obs_images`
|
Line 123... |
Line 94... |
123 |
`hauteur` INT NOT NULL ,
|
94 |
`hauteur` INT NOT NULL ,
|
124 |
`largeur` INT NOT NULL ,
|
95 |
`largeur` INT NOT NULL ,
|
125 |
`appareil_fabriquant` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
|
96 |
`appareil_fabriquant` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
|
126 |
`appareil_modele` 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 ,
|
127 |
`date_prise_de_vue` DATETIME NULL DEFAULT NULL ,
|
98 |
`date_prise_de_vue` DATETIME NULL DEFAULT NULL ,
|
128 |
`note_qualite` TINYINT(5) NULL DEFAULT NULL ,
|
99 |
`note_qualite` DECIMAL(1,0) NULL DEFAULT NULL ,
|
129 |
`mots_cles_texte` LONGTEXT NULL DEFAULT NULL COMMENT 'Champ calculé contenant la liste des mots clés utilisateurs séparé par des virgules.\n' ,
|
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' ,
|
130 |
`commentaire` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
|
101 |
`commentaire` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
|
131 |
`nom_original` VARCHAR(255) 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 ,
|
132 |
`md5` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
|
103 |
`md5` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
|
133 |
`meta_exif` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
|
104 |
`meta_exif` LONGTEXT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
|
Line 251... |
Line 222... |
251 |
ENGINE = MyISAM
|
222 |
ENGINE = MyISAM
|
252 |
DEFAULT CHARACTER SET = utf8
|
223 |
DEFAULT CHARACTER SET = utf8
|
253 |
COLLATE = utf8_general_ci;
|
224 |
COLLATE = utf8_general_ci;
|
Line -... |
Line 225... |
- |
|
225 |
|
- |
|
226 |
|
- |
|
227 |
-- -----------------------------------------------------
|
- |
|
228 |
-- Table `cel_utilisateurs_infos`
|
- |
|
229 |
-- -----------------------------------------------------
|
- |
|
230 |
DROP TABLE IF EXISTS `cel_utilisateurs_infos` ;
|
- |
|
231 |
|
- |
|
232 |
CREATE TABLE IF NOT EXISTS `cel_utilisateurs_infos` (
|
- |
|
233 |
`id_utilisateur` INT NOT NULL ,
|
- |
|
234 |
`admin` TINYINT(1) NOT NULL DEFAULT 0 ,
|
- |
|
235 |
`licence_acceptee` TINYINT(1) NOT NULL DEFAULT 0 ,
|
- |
|
236 |
`preferences` LONGTEXT NULL DEFAULT NULL ,
|
- |
|
237 |
`date_premiere_utilisation` DATETIME NOT NULL ,
|
- |
|
238 |
PRIMARY KEY (`id_utilisateur`) )
|
- |
|
239 |
ENGINE = MyISAM
|
- |
|
240 |
DEFAULT CHARACTER SET = utf8
|
- |
|
241 |
COLLATE = utf8_general_ci
|
- |
|
242 |
COMMENT = 'Contient les infos utilisateurs spécifiques au CEL.\nA utilis' /* comment truncated */;
|
- |
|
243 |
|
- |
|
244 |
|
- |
|
245 |
-- -----------------------------------------------------
|
- |
|
246 |
-- Placeholder table for view `cel_tapir`
|
- |
|
247 |
-- -----------------------------------------------------
|
- |
|
248 |
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);
|
- |
|
249 |
|
- |
|
250 |
-- -----------------------------------------------------
|
- |
|
251 |
-- View `cel_utilisateurs`
|
- |
|
252 |
-- -----------------------------------------------------
|
- |
|
253 |
DROP VIEW IF EXISTS `cel_utilisateurs` ;
|
- |
|
254 |
DROP TABLE IF EXISTS `cel_utilisateurs`;
|
- |
|
255 |
CREATE OR REPLACE VIEW `cel_utilisateurs` AS
|
- |
|
256 |
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,
|
- |
|
257 |
ui.licence_acceptee, ui.admin, ui.preferences, ui.date_premiere_utilisation
|
- |
|
258 |
FROM cel_utilisateurs_infos AS ui
|
- |
|
259 |
LEFT JOIN tela_prod_v4.annuaire_tela AS at ON (ui.id_utilisateur = at.U_ID);
|
- |
|
260 |
|
- |
|
261 |
-- -----------------------------------------------------
|
- |
|
262 |
-- View `cel_tapir`
|
- |
|
263 |
-- -----------------------------------------------------
|
- |
|
264 |
DROP VIEW IF EXISTS `cel_tapir` ;
|
- |
|
265 |
DROP TABLE IF EXISTS `cel_tapir`;
|
- |
|
266 |
DELIMITER $$
|
- |
|
267 |
CREATE OR REPLACE VIEW `cel_tapir` AS
|
- |
|
268 |
SELECT concat(_utf8'urn:lsid:tela-botanica.org:cel:', o.id_observation) AS guid,
|
- |
|
269 |
o.id_observation AS observation_id,
|
- |
|
270 |
date_format(o.date_observation,'%Y-%m-%d') AS observation_date,
|
- |
|
271 |
o.nom_sel AS nom_scientifique_complet,
|
- |
|
272 |
o.nom_sel_nn AS nom_num_nomenclatural,
|
- |
|
273 |
o.nt AS nom_num_taxonomique,
|
- |
|
274 |
o.famille AS nom_famille,
|
- |
|
275 |
concat(_utf8'',zg.nom,' [INSEE:',zg.code,']') AS lieu_commune_nom_complet,
|
- |
|
276 |
zg.nom AS lieu_commune_nom,
|
- |
|
277 |
zg.code AS lieu_commune_code_insee,
|
- |
|
278 |
concat(zg.utm_secteur,' ',zg.utm_x,'m E ',zg.utm_y,'m N') AS lieu_commune_utm_coordonnees_bruts,
|
- |
|
279 |
zg.utm_secteur AS lieu_commune_utm_zone,
|
- |
|
280 |
zg.utm_x AS lieu_commune_utm_x,
|
- |
|
281 |
zg.utm_y AS lieu_commune_utm_y,
|
- |
|
282 |
o.latitude AS lieu_latitude,
|
- |
|
283 |
o.longitude AS lieu_longitude,
|
- |
|
284 |
o.geodatum AS lieu_geodatum,
|
- |
|
285 |
o.lieudit AS lieu_localite,
|
- |
|
286 |
o.prenom_utilisateur AS observateur_prenom,
|
- |
|
287 |
o.nom_utilisateur AS observateur_nom,
|
- |
|
288 |
o.courriel_utilisateur AS observateur_courriel,
|
- |
|
289 |
concat(o.prenom_utilisateur,_utf8' ',o.nom_utilisateur) AS observateur_intitule,
|
- |
|
290 |
o.commentaire AS observation_commentaire,
|
- |
|
291 |
concat(_utf8'<?xml version="1.0" encoding="UTF-8"?>',
|
- |
|
292 |
'<info>',
|
- |
|
293 |
'<nom_num_nomenclatural>',o.nom_sel_nn,'</nom_num_nomenclatural>',
|
- |
|
294 |
'<nom_num_taxonomique>',o.nt,'</nom_num_taxonomique>',
|
- |
|
295 |
'<nom_referentiel>',o.nom_referentiel,'</nom_referentiel>',
|
- |
|
296 |
'<saisie_date_transmission>',o.date_transmission,'</saisie_date_transmission>',
|
- |
|
297 |
'<saisie_date_creation>',o.date_creation,'</saisie_date_creation>',
|
- |
|
298 |
'<lieu_commune_utm_secteur>',o.utm_secteur,'</lieu_commune_utm_secteur>',
|
- |
|
299 |
'<ordre>',o.ordre,'</ordre>',
|
- |
|
300 |
'<nom_ret>',o.nom_ret,'</nom_ret>',
|
- |
|
301 |
'<num_nom_ret>',o.nom_ret_nn,'</num_nom_ret>',
|
- |
|
302 |
'<station>',o.station,'</station>',
|
- |
|
303 |
'<milieu>',o.milieu,'</milieu>',
|
- |
|
304 |
'<mots_cles>',o.mots_cles_texte,'</mots_cles>',
|
- |
|
305 |
'<zg_date_modification>',zg.date_modification,'</zg_date_modification>',
|
- |
|
306 |
'</info>') AS observation_information_complement,
|
- |
|
307 |
o.date_modification AS saisie_date_modification,
|
- |
|
308 |
o.date_creation AS saisie_date_creation
|
- |
|
309 |
FROM cel_obs AS o
|
- |
|
310 |
LEFT JOIN cel_zones_geo AS zg ON (o.ce_zone_geo = zg.id_zone_geo)
|
- |
|
311 |
WHERE o.transmission = 1;
|
- |
|
312 |
|
- |
|
313 |
$$
|
- |
|
314 |
DELIMITER ;
|
- |
|
315 |
|
- |
|
316 |
;
|
254 |
|
317 |
|
255 |
|
318 |
|
256 |
SET SQL_MODE=@OLD_SQL_MODE;
|
319 |
SET SQL_MODE=@OLD_SQL_MODE;
|