WebSVN – eFlore/Projets.eflore-projets – //branches/v5.1-acheb/docs/projets/cel/bdd/cel_v1_00.sql

-- 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;
Subversion Repositories eFlore/Projets.eflore-projets

(root)//branches/v5.1-acheb/docs/projets/cel/bdd/cel_v1_00.sql @ 1242 – Rev 909
