Rev 172 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
-- 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;