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; |