Subversion Repositories eFlore/Applications.del

Rev

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

Rev 735 Rev 784
Line -... Line 1...
- 
 
 1 
-- -----------------------------------------------------
- 
 
 2 
-- View `del_utilisateurs`
1 
-- TODO : Voir comment remplacer le test REGEXP par quelque chose de plus simple.
 3 
-- -----------------------------------------------------
2 
 
 - 
 
3 
DROP VIEW IF EXISTS del_utilisateur ;
 4 
DROP VIEW IF EXISTS `del_utilisateur` ;
4 
DROP TABLE IF EXISTS del_utilisateur;
 5 
DROP TABLE IF EXISTS `del_utilisateur`;
- 
 
 6 
DELIMITER $$
5 
CREATE  OR REPLACE VIEW del_utilisateur AS
7 
CREATE  OR REPLACE VIEW del_utilisateur AS
6 
	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,
8 
	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,
7 
		ui.licence_acceptee, ui.admin, ui.preferences, ui.date_premiere_utilisation
9 
		ui.licence_acceptee, ui.admin, ui.preferences, ui.date_premiere_utilisation
8 
	FROM tb_cel.cel_utilisateurs_infos AS ui
10 
	FROM tb_cel.cel_utilisateurs_infos AS ui
9 
		LEFT JOIN tela_prod_v4.annuaire_tela AS at ON (ui.id_utilisateur = at.U_ID);
 11 
		LEFT JOIN tela_prod_v4.annuaire_tela AS at ON (ui.id_utilisateur = at.U_ID);
- 
 
 12 
$$
- 
 
 13 
DELIMITER ;
- 
 
 14 
 
- 
 
 15 
;
- 
 
 16 
 
- 
 
 17 
-- -----------------------------------------------------
- 
 
 18 
-- View `del_observations`
- 
 
 19 
-- -----------------------------------------------------
- 
 
 20 
DROP VIEW IF EXISTS `del_observation` ;
- 
 
 21 
DROP TABLE IF EXISTS `del_observation`;
- 
 
 22 
DELIMITER $$
- 
 
 23 
CREATE  OR REPLACE VIEW del_observation AS
- 
 
 24 
    SELECT id_observation, ce_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
- 
 
 25 
        ce_zone_geo, zone_geo, lieudit, station, milieu,
- 
 
 26 
        date_observation, mots_cles_texte, commentaire,
- 
 
 27 
        date_creation, date_modification, date_transmission
- 
 
 28 
    FROM tb_cel.cel_obs AS o
- 
 
 29 
        INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
- 
 
 30 
    WHERE transmission = '1'
- 
 
 31 
$$
- 
 
 32 
DELIMITER ;
- 
 
 33 
 
- 
 
 34 
;
- 
 
 35 
 
- 
 
 36 
-- -----------------------------------------------------
- 
 
 37 
-- View `del_images`
- 
 
 38 
-- -----------------------------------------------------
- 
 
 39 
DROP VIEW IF EXISTS `del_image` ;
- 
 
 40 
DROP TABLE IF EXISTS `del_image`;
- 
 
 41 
DELIMITER $$
- 
 
 42 
CREATE  OR REPLACE VIEW del_image AS
- 
 
 43 
    SELECT i.id_image, i.ce_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
- 
 
 44 
        i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore
- 
 
 45 
    FROM tb_cel.cel_images AS i
- 
 
 46 
        INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
- 
 
 47 
        INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
- 
 
 48 
        INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (i.ce_utilisateur = u.id_utilisateur)
- 
 
 49 
    WHERE o.transmission = '1'
- 
 
 50 
$$
- 
 
 51 
DELIMITER ;
- 
 
 52 
 
- 
 
 53 
;
- 
 
 54 
 
- 
 
 55 
-- -----------------------------------------------------
- 
 
 56 
-- View `del_obs_images`
- 
 
 57 
-- -----------------------------------------------------
- 
 
 58 
DROP VIEW IF EXISTS `del_obs_image` ;
- 
 
 59 
DROP TABLE IF EXISTS `del_obs_image`;
- 
 
 60 
DELIMITER $$
- 
 
 61 
CREATE  OR REPLACE VIEW del_obs_image AS
- 
 
 62 
    SELECT oi.id_image, oi.id_observation, oi.date_liaison
- 
 
 63 
    FROM tb_cel.cel_obs_images AS oi
- 
 
 64 
        INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
- 
 
 65 
        INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
- 
 
 66 
    WHERE o.transmission = '1'
- 
 
 67 
$$
- 
 
 68 
DELIMITER ;
Line 10... Line -...
10 
 
 - 
 
11 
DROP VIEW IF EXISTS del_observation ;
 - 
 
12 
DROP TABLE IF EXISTS del_observation;
 - 
 
13 
CREATE VIEW del_observation AS
- 
 
14 
	SELECT id_observation, ce_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
- 
 
15 
		ce_zone_geo, zone_geo, lieudit, station, milieu,
- 
 
16 
		date_observation, mots_cles_texte, commentaire,
- 
 
17 
		date_creation, date_modification, date_transmission
- 
 
18 
	FROM tb_cel.cel_obs
 - 
 
19 
	WHERE transmission = '1'
- 
 
20 
		AND ce_utilisateur REGEXP '^[[:digit:]]+$';
 - 
 
21 
 
 - 
 
22 
DROP VIEW IF EXISTS del_obs_image ;
 - 
 
23 
DROP TABLE IF EXISTS del_obs_image;
 - 
 
24 
CREATE VIEW del_obs_image AS
- 
 
25 
	SELECT oi.id_image, oi.id_utilisateur, oi.id_observation, oi.date_liaison
- 
 
26 
	FROM tb_cel.cel_obs_images AS oi
- 
 
27 
		INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
 - 
 
28 
	WHERE o.transmission = '1'
- 
 
29 
		AND oi.id_utilisateur REGEXP '^[[:digit:]]+$';
 - 
 
30 
 
 - 
 
31 
DROP VIEW IF EXISTS del_image ;
 - 
 
32 
DROP TABLE IF EXISTS del_image;
 - 
 
33 
CREATE VIEW del_image AS
 - 
 
34 
	SELECT i.id_image, i.ce_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
- 
 
35 
		i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore
- 
 
36 
	FROM tb_cel.cel_images AS i
- 
 
37 
		INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
- 
 
38 
		INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
 - 
 
39 
	WHERE o.transmission = '1'
- 
 
40 
		AND i.ce_utilisateur REGEXP '^[[:digit:]]+$';
 69 
 
- 
 
 70 
;
41 

            71