Subversion Repositories eFlore/Applications.del

Compare Revisions

Ignore whitespace Rev 731 → Rev 714

/trunk/doc/bdd/sql/tela/tb_del.sql
New file
0,0 → 1,116
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
 
 
-- -----------------------------------------------------
-- Table `del_image_protocole`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `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 NULL ,
PRIMARY KEY (`id_protocole`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
-- -----------------------------------------------------
-- Table `del_image_vote`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `del_image_vote` ;
 
CREATE TABLE IF NOT EXISTS `del_image_vote` (
`id_vote` BIGINT NOT NULL AUTO_INCREMENT ,
`ce_image` BIGINT NOT NULL ,
`ce_protocole` INT(11) NOT NULL ,
`ce_utilisateur` INT NULL ,
`valeur` TINYINT(1) NOT NULL ,
`date` DATETIME NOT NULL ,
PRIMARY KEY (`id_vote`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
-- -----------------------------------------------------
-- Table `del_image_tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `del_image_tag` ;
 
CREATE TABLE IF NOT EXISTS `del_image_tag` (
`id_tag` BIGINT NOT NULL AUTO_INCREMENT ,
`ce_image` BIGINT NOT NULL ,
`ce_utilisateur` INT NOT NULL ,
`tag` VARCHAR(45) NOT NULL ,
`md5` VARCHAR(32) NOT NULL ,
PRIMARY KEY (`id_tag`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
-- -----------------------------------------------------
-- Table `del_commentaire`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `del_commentaire` ;
 
CREATE TABLE IF NOT EXISTS `del_commentaire` (
`id_commentaire` BIGINT NOT NULL AUTO_INCREMENT ,
`ce_observation` BIGINT NOT NULL ,
`ce_commentaire_parent` BIGINT NULL ,
`ce_proposition` BIGINT NULL ,
`texte` TEXT NOT NULL ,
`ce_utilisateur` INT NULL ,
`utilisateur_prenom` VARCHAR(255) NOT NULL ,
`utilisateur_nom` VARCHAR(255) NOT NULL ,
`utilisateur_courriel` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`id_commentaire`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
-- -----------------------------------------------------
-- Table `del_proposition`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `del_proposition` ;
 
CREATE TABLE IF NOT EXISTS `del_proposition` (
`id_proposition` BIGINT NOT NULL ,
`nom_sel` VARCHAR(255) NOT NULL COMMENT 'Nom scientifique sélectionné\n' ,
`nom_sel_nn` INT NULL COMMENT 'Numéro nomenclatural du nom scientifique selectionné\n' ,
`nom_ret` VARCHAR(255) NULL COMMENT 'Nom scientifique retenu\n' ,
`nom_ret_nn` INT NULL COMMENT 'Numéro nomenclatural du nom scientifique retenu\n' ,
`nt` INT NULL COMMENT 'Numéro taxonomique\n' ,
`famille` VARCHAR(255) NULL ,
`nom_referentiel` VARCHAR(45) NULL COMMENT 'Nom du référentiel utilisé (ex bdtfx_v1)\n' ,
PRIMARY KEY (`id_proposition`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
-- -----------------------------------------------------
-- Table `del_proposition_vote`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `del_proposition_vote` ;
 
CREATE TABLE IF NOT EXISTS `del_proposition_vote` (
`id_vote` BIGINT NOT NULL AUTO_INCREMENT ,
`ce_proposition` BIGINT NOT NULL ,
`ce_utilisateur` INT NULL ,
`valeur` TINYINT(1) NOT NULL ,
`date` DATETIME NOT NULL ,
PRIMARY KEY (`id_vote`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
/trunk/doc/bdd/sql/tela/tb_del_cel_vues.sql
1,13 → 1,9
-- TODO : Voir comment remplacer le test REGEXP par quelque chose de plus simple.
 
DROP VIEW IF EXISTS del_utilisateur ;
DROP TABLE IF EXISTS del_utilisateur;
CREATE VIEW del_utilisateur AS
SELECT id_utilisateur, prenom, nom, courriel, mot_de_passe, licence_acceptee
FROM tb_cel.cel_utilisateurs;
 
DROP VIEW IF EXISTS del_observation ;
DROP TABLE IF EXISTS del_observation;
CREATE VIEW del_observation AS
SELECT id_observation, ce_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille,
ce_zone_geo, zone_geo, lieudit, station, milieu,
17,8 → 13,6
WHERE transmission = '1'
AND ce_utilisateur REGEXP '^[[:digit:]]+$';
 
DROP VIEW IF EXISTS del_obs_images ;
DROP TABLE IF EXISTS del_obs_images;
CREATE VIEW del_obs_images AS
SELECT oi.id_image, oi.id_utilisateur, oi.id_observation, oi.date_liaison
FROM tb_cel.cel_obs_images AS oi
26,8 → 20,6
WHERE o.transmission = '1'
AND oi.id_utilisateur REGEXP '^[[:digit:]]+$';
 
DROP VIEW IF EXISTS del_image ;
DROP TABLE IF EXISTS del_image;
CREATE VIEW del_image AS
SELECT i.id_image, i.ce_utilisateur, i.hauteur, i.largeur, i.date_prise_de_vue, i.mots_cles_texte,
i.commentaire, i.nom_original, i.date_modification, i.date_creation, i.publiable_eflore