Subversion Repositories eFlore/Applications.del

Rev

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

Rev 1268 Rev 1270
Line 1... Line 1...
1 
-- -----------------------------------------------------
 1 
-- -----------------------------------------------------
2 
-- Placeholder table for view `del_utilisateurs`
 - 
 
3 
-- -----------------------------------------------------
 - 
 
4 
CREATE TABLE IF NOT EXISTS `del_utilisateur` (`id_utilisateur` INT, `prenom` INT, `nom` INT, `courriel` INT, `mot_de_passe` INT);
 - 
 
5 
 
 - 
 
6 
-- -----------------------------------------------------
 - 
 
7 
-- Placeholder table for view `del_observations`
 - 
 
8 
-- -----------------------------------------------------
 - 
 
9 
CREATE TABLE IF NOT EXISTS `del_observation` (`id_observation` INT, `ce_utilisateur` INT, `nom_sel` INT, `nom_sel_nn` INT, `nom_ret` INT, `nom_ret_nn` INT, `nt` INT, `famille` INT, `ce_zone_geo` INT, `zone_geo` INT, `lieudit` INT, `station` INT, `milieu` INT, `date_observation` INT, `mots_cles_texte` INT, `commentaire` INT, `date_creation` INT, `date_modification` INT, `date_transmission` INT);
 - 
 
10 
 
 - 
 
11 
-- -----------------------------------------------------
 - 
 
12 
-- Placeholder table for view `del_image`
 - 
 
13 
-- -----------------------------------------------------
 - 
 
14 
CREATE TABLE IF NOT EXISTS `del_image` (`id_image` INT, `ce_utilisateur` INT, `hauteur` INT, `largeur` INT, `date_prise_de_vue` INT, `mots_cles_texte` INT, `commentaire` INT, `nom_original` INT, `date_modification` INT, `date_creation` INT, `publiable_eflore` INT);
 - 
 
15 
 
 - 
 
16 
-- -----------------------------------------------------
 - 
 
17 
-- Placeholder table for view `del_obs_images`
 - 
 
18 
-- -----------------------------------------------------
 - 
 
19 
CREATE TABLE IF NOT EXISTS `del_obs_image` (`id_image` INT, `id_observation` INT, `date_liaison` INT);
 - 
 
20 
 
 - 
 
21 
-- -----------------------------------------------------
 - 
 
22 
-- View `del_utilisateurs`
 2 
-- View `del_utilisateur`
23 
-- -----------------------------------------------------
 3 
-- -----------------------------------------------------
24 
DROP VIEW IF EXISTS `del_utilisateur` ;
 4 
DROP VIEW IF EXISTS `del_utilisateur` ;
25 
DROP TABLE IF EXISTS `del_utilisateur`;
 5 
DROP TABLE IF EXISTS `del_utilisateur`;
26 
DELIMITER $$
 6 
DELIMITER $$
27 
CREATE  OR REPLACE VIEW del_utilisateur AS
7 
CREATE  OR REPLACE VIEW del_utilisateur AS
- 
 
 8 
	SELECT
28 
	SELECT CAST(U_ID AS CHAR) AS id_utilisateur, U_SURNAME AS prenom, U_NAME AS nom, U_MAIL AS courriel, U_PASSWD AS mot_de_passe
9 
    CAST(U_ID AS CHAR(128)) AS id_utilisateur,
- 
 
 10 
    CONVERT(U_SURNAME USING UTF8) AS prenom,
- 
 
 11 
    CONVERT(U_NAME USING UTF8) AS nom,
- 
 
 12 
    CONVERT(U_MAIL USING UTF8) AS courriel,
- 
 
 13 
    CONVERT(U_PASSWD USING UTF8) AS mot_de_passe
29 
	FROM tela_prod_v4.annuaire_tela;
 14 
	FROM tela_prod_v4.annuaire_tela;
30 
$$
 15 
$$
31 
DELIMITER ;
 16 
DELIMITER ;
Line 32... Line 17...
32 
 
 17 
 
Line 33... Line 18...
33 
;
 18 
;
34 
 
 19 
 
35 
-- -----------------------------------------------------
 20 
-- -----------------------------------------------------
36 
-- View `del_observations`
 21 
-- View `del_observation`
37 
-- -----------------------------------------------------
 22 
-- -----------------------------------------------------
38 
DROP VIEW IF EXISTS `del_observation` ;
 23 
DROP VIEW IF EXISTS `del_observation` ;
39 
DROP TABLE IF EXISTS `del_observation`;
 24 
DROP TABLE IF EXISTS `del_observation`;
40 
DELIMITER $$
 25 
DELIMITER $$
41 
CREATE  OR REPLACE VIEW del_observation AS
26 
CREATE  OR REPLACE VIEW del_observation AS
42 
    SELECT id_observation, ce_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
27 
    SELECT id_observation, ce_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
43 
        ce_zone_geo, zone_geo, lieudit, station, milieu,
28 
        ce_zone_geo, zone_geo, lieudit, station, milieu,
44 
        date_observation, mots_cles_texte, commentaire,
29 
        date_observation, mots_cles_texte, commentaire,
45 
        date_creation, date_modification, date_transmission
- 
 
46 
    FROM tb_cel.cel_obs AS o
 30 
        date_creation, date_modification, date_transmission
47 
        INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
 31 
    FROM tb_cel.cel_obs AS o
48 
    WHERE transmission = '1'
32 
    WHERE transmission = '1'
Line 49... Line 33...
49 
$$
 33 
$$
Line 50... Line 34...
50 
DELIMITER ;
 34 
DELIMITER ;
51 
 
 35 
 
52 
;
 36 
;
53 
 
 37 
 
54 
-- -----------------------------------------------------
 38 
-- -----------------------------------------------------
55 
-- View `del_images`
 39 
-- View `del_image`
56 
-- -----------------------------------------------------
 40 
-- -----------------------------------------------------
57 
DROP VIEW IF EXISTS `del_image` ;
 41 
DROP VIEW IF EXISTS `del_image` ;
58 
DROP TABLE IF EXISTS `del_image`;
 42 
DROP TABLE IF EXISTS `del_image`;
59 
DELIMITER $$
 43 
DELIMITER $$
60 
CREATE  OR REPLACE VIEW del_image AS
 44 
CREATE  OR REPLACE VIEW del_image AS
61 
    SELECT i.id_image, i.ce_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
45 
    SELECT i.id_image, i.ce_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
62 
        i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore
- 
 
63 
    FROM tb_cel.cel_images AS i
46 
        i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore
64 
        INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
47 
    FROM tb_cel.cel_images AS i
65 
        INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
 48 
        INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
Line 66... Line 49...
66 
        INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (i.ce_utilisateur = u.id_utilisateur)
 49 
        INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
Line 67... Line 50...
67 
    WHERE o.transmission = '1'
50 
    WHERE o.transmission = '1'
68 
$$
 51 
$$
69 
DELIMITER ;
 52 
DELIMITER ;
70 
 
 53 
 
71 
;
 54 
;
72 
 
 55 
 
73 
-- -----------------------------------------------------
 56 
-- -----------------------------------------------------
74 
-- View `del_obs_image`
 57 
-- View `del_obs_images`
75 
-- -----------------------------------------------------
 58 
-- -----------------------------------------------------
76 
DROP VIEW IF EXISTS `del_obs_image` ;
 59 
DROP VIEW IF EXISTS `del_obs_image` ;
77 
DROP TABLE IF EXISTS `del_obs_image`;
 - 
 
78 
DELIMITER $$
 60 
DROP TABLE IF EXISTS `del_obs_image`;
79 
CREATE  OR REPLACE VIEW del_obs_image AS
61 
DELIMITER $$
80 
    SELECT oi.id_image, oi.id_observation, oi.date_liaison
 62 
CREATE  OR REPLACE VIEW del_obs_image AS
Line 81... Line 63...
81 
    FROM tb_cel.cel_obs_images AS oi
63 
    SELECT oi.id_image, oi.id_observation, oi.date_liaison
82 
        INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
 64 
    FROM tb_cel.cel_obs_images AS oi