Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

Ignore whitespace Rev 119 → Rev 120

/trunk/docs/cel/bdd/cel_v1_00.sql
New file
0,0 → 1,179
-- Structure de la BDD du CEL au 10-11-2011 (version 1.00)
--
-- Structure de la table 'cel_images'
--
CREATE TABLE IF NOT EXISTS cel_images (
ci_id_image bigint(20) NOT NULL AUTO_INCREMENT,
ci_ordre bigint(20) NOT NULL,
ci_ce_utilisateur varchar(60) NOT NULL,
ci_publiable_eflore tinyint(1) NOT NULL,
ci_note_image tinyint(5) NOT NULL DEFAULT '-1',
ci_meta_height int(11) NOT NULL,
ci_meta_width int(11) NOT NULL,
ci_meta_make varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_model varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_x_resolution varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_y_resolution varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_date_time varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_date date DEFAULT NULL,
ci_meta_date_ajout datetime NOT NULL,
ci_meta_gps varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_user_comment longtext CHARACTER SET ascii,
ci_meta_comment longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
ci_meta_mots_cles varchar(800) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_exposure_time varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_f_number varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_exif_version int(11) DEFAULT NULL,
ci_meta_exif_compressed_bits_per_pixel varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_shutter_speed_value varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_aperture_value varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_exposure_bias_value varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_max_aperture_value varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_metering_mode int(5) DEFAULT NULL,
ci_meta_exif_light_source varchar(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_flash int(5) DEFAULT NULL,
ci_meta_exif_focal_length varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_flash_pix_version int(10) DEFAULT NULL,
ci_meta_exif_color_space int(5) DEFAULT NULL,
ci_meta_exif_interoperability_offset int(10) DEFAULT NULL,
ci_meta_exif_focal_plane_x_resolution varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_focal_plane_y_resolution varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_focal_plane_resolution_unit int(5) DEFAULT NULL,
ci_meta_exif_sensing_method int(5) DEFAULT NULL,
ci_meta_exif_file_source text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
ci_meta_exif_custom_rendered int(5) DEFAULT NULL,
ci_meta_exif_exposure_mode int(5) DEFAULT NULL,
ci_meta_exif_white_balance int(5) DEFAULT NULL,
ci_meta_exif_digital_zoom_ratio varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_exif_scene_capture_type int(5) DEFAULT NULL,
ci_meta_exif_gain_control int(5) DEFAULT NULL,
ci_meta_exif_contrast int(5) DEFAULT NULL,
ci_meta_exif_saturation int(5) DEFAULT NULL,
ci_meta_exif_sharpness int(5) DEFAULT NULL,
ci_meta_exif_subject_distance_range int(5) DEFAULT NULL,
ci_meta_exif_autres longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
ci_meta_iptc_category varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_mots_cles longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
ci_meta_iptc_by_line varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_by_line_title varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_city varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_sub_location varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_province_state varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_country_primary_location_code varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_country_name varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_headline varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_credit varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_copyright_notice varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_contact varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_meta_iptc_autres longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
ci_nom_original varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
ci_md5 varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (ci_id_image),
KEY ci_id_image (ci_id_image,ci_ordre,ci_ce_utilisateur),
KEY ci_ce_utilisateur (ci_ce_utilisateur)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=51035 ;
 
--
-- Structure de la table 'cel_inventory'
--
CREATE TABLE IF NOT EXISTS cel_inventory (
id bigint(20) NOT NULL AUTO_INCREMENT,
identifiant varchar(128) NOT NULL,
prenom_utilisateur varchar(255) DEFAULT NULL COMMENT 'Prénom de l''utilisateur ayant saisi l''observation.',
nom_utilisateur varchar(255) DEFAULT NULL COMMENT 'Nom de l''utilisateur ayant saisi l''observation.',
ordre bigint(20) NOT NULL,
nom_sel varchar(255) NOT NULL,
num_nom_sel int(11) NOT NULL,
nom_ret varchar(255) NOT NULL,
num_nom_ret int(11) NOT NULL,
num_taxon int(11) NOT NULL,
famille varchar(255) NOT NULL,
location varchar(50) NOT NULL,
id_location varchar(10) NOT NULL,
date_observation datetime NOT NULL,
lieudit varchar(255) NOT NULL,
station varchar(255) NOT NULL,
milieu varchar(255) NOT NULL,
commentaire varchar(1024) NOT NULL,
transmission tinyint(4) NOT NULL,
date_creation datetime NOT NULL,
date_modification datetime NOT NULL,
date_transmission datetime NOT NULL,
mots_cles longtext NOT NULL,
mots_cles_texte text,
coord_x varchar(50) NOT NULL,
coord_y varchar(50) NOT NULL,
ref_geo varchar(25) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY identifiant_ordre (identifiant,ordre),
KEY identifiant_date_creation (identifiant,date_creation),
KEY id_location (id_location),
KEY location (location),
KEY date_observation (date_observation),
KEY nom_ret (nom_ret),
KEY identifiant (identifiant),
KEY coordonnees (coord_x,coord_y)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=426372 ;
 
--
-- Structure de la table 'cel_mots_cles_images'
--
CREATE TABLE IF NOT EXISTS cel_mots_cles_images (
cmc_mot_cle varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_bg int(10) NOT NULL,
cmc_bd int(10) NOT NULL,
cmc_id_mot_cle_general varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_id_mot_cle_utilisateur varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_id_proprietaire varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_id_parent varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_niveau int(10) NOT NULL,
PRIMARY KEY (cmc_id_mot_cle_utilisateur,cmc_id_proprietaire)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Structure de la table 'cel_mots_cles_obs'
--
CREATE TABLE IF NOT EXISTS cel_mots_cles_obs (
cmc_mot_cle varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_bg int(20) NOT NULL,
cmc_bd int(20) NOT NULL,
cmc_id_mot_cle_general varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_id_mot_cle_utilisateur varchar(128) COLLATE utf8_unicode_ci NOT NULL,
cmc_id_proprietaire varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_id_parent varchar(50) COLLATE utf8_unicode_ci NOT NULL,
cmc_niveau int(11) NOT NULL,
PRIMARY KEY (cmc_id_mot_cle_utilisateur,cmc_id_proprietaire)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT COMMENT='Table des mots clés, à utiliser avec des transactions !';
 
--
-- Structure de la table 'cel_obs_images'
--
CREATE TABLE IF NOT EXISTS cel_obs_images (
coi_ce_image int(11) NOT NULL,
coi_ce_utilisateur varchar(100) CHARACTER SET utf8 NOT NULL,
coi_ce_observation int(11) NOT NULL,
coi_date_liaison datetime NOT NULL,
PRIMARY KEY (coi_ce_image,coi_ce_observation),
KEY coi_ce_utilisateur (coi_ce_utilisateur),
KEY coi_ce_observation (coi_ce_observation),
KEY coi_ce_image (coi_ce_image)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Structure de la table 'locations'
--
CREATE TABLE IF NOT EXISTS locations (
`name` varchar(50) NOT NULL,
maj_name varchar(50) NOT NULL,
`code` varchar(3) NOT NULL DEFAULT '0',
insee_code varchar(5) NOT NULL DEFAULT '0',
sector varchar(3) NOT NULL,
x_utm varchar(10) NOT NULL DEFAULT '0',
y_utm varchar(10) NOT NULL DEFAULT '0',
wgs84_latitude double NOT NULL,
wgs84_longitude double NOT NULL,
update_date datetime NOT NULL,
PRIMARY KEY (`name`,`code`),
KEY MAJ (maj_name,`code`),
KEY sector (sector,x_utm,y_utm)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;