Subversion Repositories eFlore/Applications.del

Compare Revisions

No changes between revisions

Ignore whitespace Rev 1269 → Rev 1270

/trunk/doc/bdd/sql/del.sql
23,7 → 23,7
DROP TABLE IF EXISTS `del_utilisateur` ;
 
CREATE TABLE IF NOT EXISTS `del_utilisateur` (
`id_utilisateur` VARCHAR(20) NOT NULL COMMENT 'Type varchar pour éviter problème de jointure.' ,
`id_utilisateur` VARCHAR(128) NOT NULL COMMENT 'Type varchar pour éviter problème de jointure.' ,
`prenom` VARCHAR(255) NOT NULL ,
`nom` VARCHAR(255) NOT NULL ,
`courriel` VARCHAR(255) NOT NULL ,
211,33 → 211,18
 
 
-- -----------------------------------------------------
-- Placeholder table for view `del_utilisateurs`
-- View `del_utilisateur`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `del_utilisateur` (`id_utilisateur` INT, `prenom` INT, `nom` INT, `courriel` INT, `mot_de_passe` INT);
 
-- -----------------------------------------------------
-- Placeholder table for view `del_observations`
-- -----------------------------------------------------
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);
 
-- -----------------------------------------------------
-- Placeholder table for view `del_image`
-- -----------------------------------------------------
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);
 
-- -----------------------------------------------------
-- Placeholder table for view `del_obs_images`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `del_obs_image` (`id_image` INT, `id_observation` INT, `date_liaison` INT);
 
-- -----------------------------------------------------
-- View `del_utilisateurs`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_utilisateur` ;
DROP TABLE IF EXISTS `del_utilisateur`;
DELIMITER $$
CREATE OR REPLACE VIEW del_utilisateur AS
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
SELECT
CAST(U_ID AS CHAR(128)) AS id_utilisateur,
CONVERT(U_SURNAME USING UTF8) AS prenom,
CONVERT(U_NAME USING UTF8) AS nom,
CONVERT(U_MAIL USING UTF8) AS courriel,
CONVERT(U_PASSWD USING UTF8) AS mot_de_passe
FROM tela_prod_v4.annuaire_tela;
$$
DELIMITER ;
245,7 → 230,7
;
 
-- -----------------------------------------------------
-- View `del_observations`
-- View `del_observation`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_observation` ;
DROP TABLE IF EXISTS `del_observation`;
256,7 → 241,6
date_observation, mots_cles_texte, commentaire,
date_creation, date_modification, date_transmission
FROM tb_cel.cel_obs AS o
INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
WHERE transmission = '1'
$$
DELIMITER ;
264,7 → 248,7
;
 
-- -----------------------------------------------------
-- View `del_images`
-- View `del_image`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_image` ;
DROP TABLE IF EXISTS `del_image`;
275,7 → 259,6
FROM tb_cel.cel_images AS i
INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (i.ce_utilisateur = u.id_utilisateur)
WHERE o.transmission = '1'
$$
DELIMITER ;
283,7 → 266,7
;
 
-- -----------------------------------------------------
-- View `del_obs_image`
-- View `del_obs_images`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_obs_image` ;
DROP TABLE IF EXISTS `del_obs_image`;
292,7 → 275,6
SELECT oi.id_image, oi.id_observation, oi.date_liaison
FROM tb_cel.cel_obs_images AS oi
INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
WHERE o.transmission = '1'
$$
DELIMITER ;
302,4 → 284,4
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
/trunk/doc/bdd/sql/tela/tb_del_cel_vues.sql
1,31 → 1,16
-- -----------------------------------------------------
-- Placeholder table for view `del_utilisateurs`
-- View `del_utilisateur`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `del_utilisateur` (`id_utilisateur` INT, `prenom` INT, `nom` INT, `courriel` INT, `mot_de_passe` INT);
 
-- -----------------------------------------------------
-- Placeholder table for view `del_observations`
-- -----------------------------------------------------
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);
 
-- -----------------------------------------------------
-- Placeholder table for view `del_image`
-- -----------------------------------------------------
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);
 
-- -----------------------------------------------------
-- Placeholder table for view `del_obs_images`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `del_obs_image` (`id_image` INT, `id_observation` INT, `date_liaison` INT);
 
-- -----------------------------------------------------
-- View `del_utilisateurs`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_utilisateur` ;
DROP TABLE IF EXISTS `del_utilisateur`;
DELIMITER $$
CREATE OR REPLACE VIEW del_utilisateur AS
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
SELECT
CAST(U_ID AS CHAR(128)) AS id_utilisateur,
CONVERT(U_SURNAME USING UTF8) AS prenom,
CONVERT(U_NAME USING UTF8) AS nom,
CONVERT(U_MAIL USING UTF8) AS courriel,
CONVERT(U_PASSWD USING UTF8) AS mot_de_passe
FROM tela_prod_v4.annuaire_tela;
$$
DELIMITER ;
33,7 → 18,7
;
 
-- -----------------------------------------------------
-- View `del_observations`
-- View `del_observation`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_observation` ;
DROP TABLE IF EXISTS `del_observation`;
44,7 → 29,6
date_observation, mots_cles_texte, commentaire,
date_creation, date_modification, date_transmission
FROM tb_cel.cel_obs AS o
INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
WHERE transmission = '1'
$$
DELIMITER ;
52,7 → 36,7
;
 
-- -----------------------------------------------------
-- View `del_images`
-- View `del_image`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_image` ;
DROP TABLE IF EXISTS `del_image`;
63,7 → 47,6
FROM tb_cel.cel_images AS i
INNER JOIN tb_cel.cel_obs_images AS oi ON (i.id_image = oi.id_image)
INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (i.ce_utilisateur = u.id_utilisateur)
WHERE o.transmission = '1'
$$
DELIMITER ;
71,7 → 54,7
;
 
-- -----------------------------------------------------
-- View `del_obs_image`
-- View `del_obs_images`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `del_obs_image` ;
DROP TABLE IF EXISTS `del_obs_image`;
80,7 → 63,6
SELECT oi.id_image, oi.id_observation, oi.date_liaison
FROM tb_cel.cel_obs_images AS oi
INNER JOIN tb_cel.cel_obs AS o ON (oi.id_observation = o.id_observation)
INNER JOIN tb_cel.cel_utilisateurs_infos AS u ON (o.ce_utilisateur = u.id_utilisateur)
WHERE o.transmission = '1'
$$
DELIMITER ;
/trunk/doc/bdd/sql/protocoles.sql
1,19 → 1,9
--
-- Structure de la table `del_image_protocole`
--
 
CREATE TABLE IF NOT EXISTS `del_image_protocole` (
`id_protocole` int(11) NOT NULL AUTO_INCREMENT,
`intitule` varchar(255) NOT NULL,
`descriptif` text,
PRIMARY KEY (`id_protocole`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
--
-- Contenu de la table `del_image_protocole`
--
 
INSERT INTO `del_image_protocole` (`id_protocole`, `intitule`, `descriptif`) VALUES
(1, 'esthetique', 'qualité de l''image'),
(2, 'capitalisation de scans de feuilles', 'scanner ou photographier sur fond blanc des feuilles d''arbres et les transmettre via le carnet en ligne'),
(3, 'Capitalisation multicritères d''images d''arbres', 'photographier en extérieur les organes (feuille, fruit, tronc, etc.) de plantes et transmettre les photos via le Carnet en ligne.');
INSERT INTO `del_image_protocole` (`id_protocole`, `intitule`, `descriptif`, `tag`) VALUES
(1, 'Caractères d''identification', 'Caractères d''identification', 'caractere'),
(3, 'Capitalisation d''images', 'photographier en extérieur les organes (feuille, fruit, tronc, etc.) de plantes et transmettre les photos via le Carnet en ligne.', 'Plantnet'),
(4, 'Défi images', 'Défi images ', 'DefiPhoto'),
(5, 'Enquête Gentiane-Azuré', 'Participez à la localisation des Gentianes Croisette, témoins et actrices du cycle de vie du papillon Azuré de la Croisette', 'GentianeAzure');
/trunk/doc/bdd/schema_bdd_del.mwb
Cannot display: file marked as a binary type.
svn:mime-type = application/octet-stream
/trunk/doc/bdd/schema_bdd_del.pdf
Cannot display: file marked as a binary type.
svn:mime-type = application/octet-stream