Subversion Repositories eFlore/Applications.del

Rev

Rev 1031 | Rev 1246 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 1031 Rev 1032
Line 22... Line 22...
22 
-- View `del_utilisateurs`
 22 
-- View `del_utilisateurs`
23 
-- -----------------------------------------------------
 23 
-- -----------------------------------------------------
24 
DROP VIEW IF EXISTS `del_utilisateur` ;
 24 
DROP VIEW IF EXISTS `del_utilisateur` ;
25 
DROP TABLE IF EXISTS `del_utilisateur`;
 25 
DROP TABLE IF EXISTS `del_utilisateur`;
26 
DELIMITER $$
 26 
DELIMITER $$
27 
CREATE  OR REPLACE VIEW del_utilisateur AS
27 
CREATE  OR REPLACE ALGORITHM=MERGE VIEW del_utilisateur AS
28 
	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
28 
	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
29 
	FROM tb_cel.cel_utilisateurs_infos AS ui
29 
	FROM tb_cel.cel_utilisateurs_infos AS ui
30 
		LEFT JOIN tela_prod_v4.annuaire_tela AS at ON (ui.id_utilisateur = at.U_ID);
 30 
		LEFT JOIN tela_prod_v4.annuaire_tela AS at ON (ui.id_utilisateur = at.U_ID);
31 
$$
 31 
$$
32 
DELIMITER ;
 32 
DELIMITER ;
Line 38... Line 38...
38 
-- -----------------------------------------------------
 38 
-- -----------------------------------------------------
39 
DROP VIEW IF EXISTS `del_observation` ;
 39 
DROP VIEW IF EXISTS `del_observation` ;
40 
DROP TABLE IF EXISTS `del_observation`
 40 
DROP TABLE IF EXISTS `del_observation`
Line 41... Line 41...
41 
 
 41 
 
42 
DELIMITER $$
 42 
DELIMITER $$
43 
CREATE  OR REPLACE VIEW del_observation AS
43 
CREATE  OR REPLACE ALGORITHM=MERGE VIEW  del_observation AS
44 
    SELECT o.id_observation, ce_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
44 
    SELECT o.id_observation, ce_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
45 
        ce_zone_geo, zone_geo, lieudit, station, milieu,
45 
        ce_zone_geo, zone_geo, lieudit, station, milieu,
46 
        date_observation, mots_cles_texte, commentaire,
46 
        date_observation, mots_cles_texte, commentaire,
47 
        date_creation, date_modification, date_transmission
47 
        date_creation, date_modification, date_transmission
48 
    FROM tb_cel.cel_obs AS o
 - 
 
49 
        INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
 - 
 
50 
	INNER JOIN tb_cel.cel_obs_images AS coi ON o.id_observation = coi.id_observation
 48 
    FROM tb_cel.cel_obs AS o
51 
    WHERE transmission = '1'
 49 
    WHERE transmission = '1'
Line 52... Line 50...
52 
$$
 50 
$$
Line 59... Line 57...
59 
-- View `del_images`
 57 
-- View `del_images`
60 
-- -----------------------------------------------------
 58 
-- -----------------------------------------------------
61 
DROP VIEW IF EXISTS `del_image` ;
 59 
DROP VIEW IF EXISTS `del_image` ;
62 
DROP TABLE IF EXISTS `del_image`;
 60 
DROP TABLE IF EXISTS `del_image`;
63 
DELIMITER $$
 61 
DELIMITER $$
64 
CREATE  OR REPLACE VIEW del_image AS
 62 
CREATE  OR REPLACE ALGORITHM=MERGE VIEW del_image AS
65 
    SELECT i.id_image, i.ce_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
63 
    SELECT i.id_image, i.ce_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
66 
        i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore
64 
        i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore
67 
    FROM tb_cel.cel_images AS i
65 
    FROM tb_cel.cel_images AS i
68 
        INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
- 
 
69 
        INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
 - 
 
70 
        INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (i.ce_utilisateur = u.id_utilisateur)
 - 
 
71 
    WHERE o.transmission = '1'
- 
 
72 
$$
 66 
$$
73 
DELIMITER ;
 67 
DELIMITER ;
Line 74... Line 68...
74 
 
 68 
 
Line 78... Line 72...
78 
-- View `del_obs_images`
 72 
-- View `del_obs_images`
79 
-- -----------------------------------------------------
 73 
-- -----------------------------------------------------
80 
DROP VIEW IF EXISTS `del_obs_image` ;
 74 
DROP VIEW IF EXISTS `del_obs_image` ;
81 
DROP TABLE IF EXISTS `del_obs_image`;
 75 
DROP TABLE IF EXISTS `del_obs_image`;
82 
DELIMITER $$
 76 
DELIMITER $$
83 
CREATE  OR REPLACE VIEW del_obs_image AS
77 
CREATE  OR REPLACE ALGORITHM=MERGE VIEW  del_obs_image AS
84 
    SELECT oi.id_image, oi.id_observation, oi.date_liaison
 78 
    SELECT oi.id_image, oi.id_observation, oi.date_liaison
85 
    FROM tb_cel.cel_obs_images AS oi
79 
    FROM tb_cel.cel_obs_images AS oi
86 
        INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
 80 
        INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
87 
        INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
 - 
 
88 
    WHERE o.transmission = '1'
81 
    WHERE o.transmission = '1'
89 
$$
 82 
$$
90 
DELIMITER ;
 83 
DELIMITER ;
Line 91... Line 84...
91 
 
 84 
 
92 
;
 85 
;