Line 21... |
Line 21... |
21 |
-- Table `del_utilisateur`
|
21 |
-- Table `del_utilisateur`
|
22 |
-- -----------------------------------------------------
|
22 |
-- -----------------------------------------------------
|
23 |
DROP TABLE IF EXISTS `del_utilisateur` ;
|
23 |
DROP TABLE IF EXISTS `del_utilisateur` ;
|
Line 24... |
Line 24... |
24 |
|
24 |
|
25 |
CREATE TABLE IF NOT EXISTS `del_utilisateur` (
|
25 |
CREATE TABLE IF NOT EXISTS `del_utilisateur` (
|
26 |
`id_utilisateur` VARCHAR(128) NOT NULL COMMENT 'Type varchar pour éviter problème de jointure.' ,
|
26 |
`id_utilisateur` INT NOT NULL AUTO_INCREMENT COMMENT 'Type varchar pour éviter problème de jointure.' ,
|
27 |
`prenom` VARCHAR(255) NOT NULL ,
|
27 |
`prenom` VARCHAR(255) NOT NULL ,
|
28 |
`nom` VARCHAR(255) NOT NULL ,
|
28 |
`nom` VARCHAR(255) NOT NULL ,
|
29 |
`courriel` VARCHAR(255) NOT NULL ,
|
29 |
`courriel` VARCHAR(255) NOT NULL ,
|
30 |
`mot_de_passe` VARCHAR(45) NULL DEFAULT NULL ,
|
30 |
`mot_de_passe` VARCHAR(45) NULL DEFAULT NULL ,
|
Line 216... |
Line 216... |
216 |
DROP VIEW IF EXISTS `del_utilisateur` ;
|
216 |
DROP VIEW IF EXISTS `del_utilisateur` ;
|
217 |
DROP TABLE IF EXISTS `del_utilisateur`;
|
217 |
DROP TABLE IF EXISTS `del_utilisateur`;
|
218 |
DELIMITER $$
|
218 |
DELIMITER $$
|
219 |
CREATE OR REPLACE VIEW del_utilisateur AS
|
219 |
CREATE OR REPLACE VIEW del_utilisateur AS
|
220 |
SELECT
|
220 |
SELECT
|
221 |
CAST(U_ID AS CHAR(128)) AS id_utilisateur,
|
221 |
U_ID AS id_utilisateur,
|
222 |
CONVERT(U_SURNAME USING UTF8) AS prenom,
|
222 |
CONVERT(U_SURNAME USING UTF8) AS prenom,
|
223 |
CONVERT(U_NAME USING UTF8) AS nom,
|
223 |
CONVERT(U_NAME USING UTF8) AS nom,
|
224 |
CONVERT(U_MAIL USING UTF8) AS courriel,
|
224 |
CONVERT(U_MAIL USING UTF8) AS courriel,
|
225 |
CONVERT(U_PASSWD USING UTF8) AS mot_de_passe
|
225 |
CONVERT(U_PASSWD USING UTF8) AS mot_de_passe
|
226 |
FROM tela_prod_v4.annuaire_tela;
|
226 |
FROM tela_prod_v4.annuaire_tela;
|
Line 234... |
Line 234... |
234 |
-- -----------------------------------------------------
|
234 |
-- -----------------------------------------------------
|
235 |
DROP VIEW IF EXISTS `del_observation` ;
|
235 |
DROP VIEW IF EXISTS `del_observation` ;
|
236 |
DROP TABLE IF EXISTS `del_observation`;
|
236 |
DROP TABLE IF EXISTS `del_observation`;
|
237 |
DELIMITER $$
|
237 |
DELIMITER $$
|
238 |
CREATE OR REPLACE VIEW del_observation AS
|
238 |
CREATE OR REPLACE VIEW del_observation AS
|
239 |
SELECT id_observation, ce_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
|
239 |
SELECT id_observation, CAST(ce_utilisateur AS UNSIGNED) AS ce_utilisateur, prenom_utilisateur, nom_utilisateur, courriel_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
|
240 |
ce_zone_geo, zone_geo, lieudit, station, milieu,
|
240 |
ce_zone_geo, zone_geo, lieudit, station, milieu,
|
241 |
date_observation, mots_cles_texte, commentaire,
|
241 |
date_observation, mots_cles_texte, commentaire,
|
242 |
date_creation, date_modification, date_transmission
|
242 |
date_creation, date_modification, date_transmission
|
243 |
FROM tb_cel.cel_obs AS o
|
243 |
FROM tb_cel.cel_obs AS o
|
244 |
WHERE transmission = '1'
|
244 |
WHERE transmission = '1'
|
Line 252... |
Line 252... |
252 |
-- -----------------------------------------------------
|
252 |
-- -----------------------------------------------------
|
253 |
DROP VIEW IF EXISTS `del_image` ;
|
253 |
DROP VIEW IF EXISTS `del_image` ;
|
254 |
DROP TABLE IF EXISTS `del_image`;
|
254 |
DROP TABLE IF EXISTS `del_image`;
|
255 |
DELIMITER $$
|
255 |
DELIMITER $$
|
256 |
CREATE OR REPLACE VIEW del_image AS
|
256 |
CREATE OR REPLACE VIEW del_image AS
|
257 |
SELECT i.id_image, i.ce_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
|
257 |
SELECT i.id_image, CAST(i.ce_utilisateur AS UNSIGNED) AS ce_utilisateur, i.prenom_utilisateur, i.nom_utilisateur, i.courriel_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
|
258 |
i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore
|
258 |
i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore
|
259 |
FROM tb_cel.cel_images AS i
|
259 |
FROM tb_cel.cel_images AS i
|
260 |
INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
|
260 |
INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
|
261 |
INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
|
261 |
INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
|
262 |
WHERE o.transmission = '1'
|
262 |
WHERE o.transmission = '1'
|
Line 264... |
Line 264... |
264 |
DELIMITER ;
|
264 |
DELIMITER ;
|
Line 265... |
Line 265... |
265 |
|
265 |
|
Line 266... |
Line 266... |
266 |
;
|
266 |
;
|
267 |
|
267 |
|
268 |
-- -----------------------------------------------------
|
268 |
-- -----------------------------------------------------
|
269 |
-- View `del_obs_images`
|
269 |
-- View `del_obs_image`
|
270 |
-- -----------------------------------------------------
|
270 |
-- -----------------------------------------------------
|
271 |
DROP VIEW IF EXISTS `del_obs_image` ;
|
271 |
DROP VIEW IF EXISTS `del_obs_image` ;
|
272 |
DROP TABLE IF EXISTS `del_obs_image`;
|
272 |
DROP TABLE IF EXISTS `del_obs_image`;
|