Subversion Repositories eFlore/Projets.eflore-projets

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
120 jpm 1 
-- Structure de la BDD du CEL au 10-11-2011 (version 1.00)
2 
--
3 
-- Structure de la table 'cel_images'
4 
--
5 
CREATE TABLE IF NOT EXISTS cel_images (
6 
  ci_id_image bigint(20) NOT NULL AUTO_INCREMENT,
7 
  ci_ordre bigint(20) NOT NULL,
8 
  ci_ce_utilisateur varchar(60) NOT NULL,
9 
  ci_publiable_eflore tinyint(1) NOT NULL,
10 
  ci_note_image tinyint(5) NOT NULL DEFAULT '-1',
11 
  ci_meta_height int(11) NOT NULL,
12 
  ci_meta_width int(11) NOT NULL,
13 
  ci_meta_make varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
14 
  ci_meta_model varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
15 
  ci_meta_x_resolution varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
16 
  ci_meta_y_resolution varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
17 
  ci_meta_date_time varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
18 
  ci_meta_date date DEFAULT NULL,
19 
  ci_meta_date_ajout datetime NOT NULL,
20 
  ci_meta_gps varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
21 
  ci_meta_user_comment longtext CHARACTER SET ascii,
22 
  ci_meta_comment longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
23 
  ci_meta_mots_cles varchar(800) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
24 
  ci_meta_exif_exposure_time varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
25 
  ci_meta_exif_f_number varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
26 
  ci_meta_exif_exif_version int(11) DEFAULT NULL,
27 
  ci_meta_exif_compressed_bits_per_pixel varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
28 
  ci_meta_exif_shutter_speed_value varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
29 
  ci_meta_exif_aperture_value varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
30 
  ci_meta_exif_exposure_bias_value varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
31 
  ci_meta_exif_max_aperture_value varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
32 
  ci_meta_exif_metering_mode int(5) DEFAULT NULL,
33 
  ci_meta_exif_light_source varchar(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
34 
  ci_meta_exif_flash int(5) DEFAULT NULL,
35 
  ci_meta_exif_focal_length varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
36 
  ci_meta_exif_flash_pix_version int(10) DEFAULT NULL,
37 
  ci_meta_exif_color_space int(5) DEFAULT NULL,
38 
  ci_meta_exif_interoperability_offset int(10) DEFAULT NULL,
39 
  ci_meta_exif_focal_plane_x_resolution varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
40 
  ci_meta_exif_focal_plane_y_resolution varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
41 
  ci_meta_exif_focal_plane_resolution_unit int(5) DEFAULT NULL,
42 
  ci_meta_exif_sensing_method int(5) DEFAULT NULL,
43 
  ci_meta_exif_file_source text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
44 
  ci_meta_exif_custom_rendered int(5) DEFAULT NULL,
45 
  ci_meta_exif_exposure_mode int(5) DEFAULT NULL,
46 
  ci_meta_exif_white_balance int(5) DEFAULT NULL,
47 
  ci_meta_exif_digital_zoom_ratio varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
48 
  ci_meta_exif_scene_capture_type int(5) DEFAULT NULL,
49 
  ci_meta_exif_gain_control int(5) DEFAULT NULL,
50 
  ci_meta_exif_contrast int(5) DEFAULT NULL,
51 
  ci_meta_exif_saturation int(5) DEFAULT NULL,
52 
  ci_meta_exif_sharpness int(5) DEFAULT NULL,
53 
  ci_meta_exif_subject_distance_range int(5) DEFAULT NULL,
54 
  ci_meta_exif_autres longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
55 
  ci_meta_iptc_category varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
56 
  ci_meta_iptc_mots_cles longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
57 
  ci_meta_iptc_by_line varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
58 
  ci_meta_iptc_by_line_title varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
59 
  ci_meta_iptc_city varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
60 
  ci_meta_iptc_sub_location varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
61 
  ci_meta_iptc_province_state varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
62 
  ci_meta_iptc_country_primary_location_code varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
63 
  ci_meta_iptc_country_name varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
64 
  ci_meta_iptc_headline varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
65 
  ci_meta_iptc_credit varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
66 
  ci_meta_iptc_copyright_notice varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
67 
  ci_meta_iptc_contact varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
68 
  ci_meta_iptc_autres longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
69 
  ci_nom_original varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
70 
  ci_md5 varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
71 
  PRIMARY KEY (ci_id_image),
72 
  KEY ci_id_image (ci_id_image,ci_ordre,ci_ce_utilisateur),
73 
  KEY ci_ce_utilisateur (ci_ce_utilisateur)
74 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=51035 ;
75 
 
76 
--
77 
-- Structure de la table 'cel_inventory'
78 
--
79 
CREATE TABLE IF NOT EXISTS cel_inventory (
80 
  id bigint(20) NOT NULL AUTO_INCREMENT,
81 
  identifiant varchar(128) NOT NULL,
82 
  prenom_utilisateur varchar(255) DEFAULT NULL COMMENT 'Prénom de l''utilisateur ayant saisi l''observation.',
83 
  nom_utilisateur varchar(255) DEFAULT NULL COMMENT 'Nom de l''utilisateur ayant saisi l''observation.',
84 
  ordre bigint(20) NOT NULL,
85 
  nom_sel varchar(255) NOT NULL,
86 
  num_nom_sel int(11) NOT NULL,
87 
  nom_ret varchar(255) NOT NULL,
88 
  num_nom_ret int(11) NOT NULL,
89 
  num_taxon int(11) NOT NULL,
90 
  famille varchar(255) NOT NULL,
91 
  location varchar(50) NOT NULL,
92 
  id_location varchar(10) NOT NULL,
93 
  date_observation datetime NOT NULL,
94 
  lieudit varchar(255) NOT NULL,
95 
  station varchar(255) NOT NULL,
96 
  milieu varchar(255) NOT NULL,
97 
  commentaire varchar(1024) NOT NULL,
98 
  transmission tinyint(4) NOT NULL,
99 
  date_creation datetime NOT NULL,
100 
  date_modification datetime NOT NULL,
101 
  date_transmission datetime NOT NULL,
102 
  mots_cles longtext NOT NULL,
103 
  mots_cles_texte text,
104 
  coord_x varchar(50) NOT NULL,
105 
  coord_y varchar(50) NOT NULL,
106 
  ref_geo varchar(25) NOT NULL,
107 
  PRIMARY KEY (id),
108 
  UNIQUE KEY identifiant_ordre (identifiant,ordre),
109 
  KEY identifiant_date_creation (identifiant,date_creation),
110 
  KEY id_location (id_location),
111 
  KEY location (location),
112 
  KEY date_observation (date_observation),
113 
  KEY nom_ret (nom_ret),
114 
  KEY identifiant (identifiant),
115 
  KEY coordonnees (coord_x,coord_y)
116 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=426372 ;
117 
 
118 
--
119 
-- Structure de la table 'cel_mots_cles_images'
120 
--
121 
CREATE TABLE IF NOT EXISTS cel_mots_cles_images (
122 
  cmc_mot_cle varchar(50) COLLATE utf8_unicode_ci NOT NULL,
123 
  cmc_bg int(10) NOT NULL,
124 
  cmc_bd int(10) NOT NULL,
125 
  cmc_id_mot_cle_general varchar(50) COLLATE utf8_unicode_ci NOT NULL,
126 
  cmc_id_mot_cle_utilisateur varchar(50) COLLATE utf8_unicode_ci NOT NULL,
127 
  cmc_id_proprietaire varchar(50) COLLATE utf8_unicode_ci NOT NULL,
128 
  cmc_id_parent varchar(50) COLLATE utf8_unicode_ci NOT NULL,
129 
  cmc_niveau int(10) NOT NULL,
130 
  PRIMARY KEY (cmc_id_mot_cle_utilisateur,cmc_id_proprietaire)
131 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
132 
 
133 
--
134 
-- Structure de la table 'cel_mots_cles_obs'
135 
--
136 
CREATE TABLE IF NOT EXISTS cel_mots_cles_obs (
137 
  cmc_mot_cle varchar(50) COLLATE utf8_unicode_ci NOT NULL,
138 
  cmc_bg int(20) NOT NULL,
139 
  cmc_bd int(20) NOT NULL,
140 
  cmc_id_mot_cle_general varchar(50) COLLATE utf8_unicode_ci NOT NULL,
141 
  cmc_id_mot_cle_utilisateur varchar(128) COLLATE utf8_unicode_ci NOT NULL,
142 
  cmc_id_proprietaire varchar(50) COLLATE utf8_unicode_ci NOT NULL,
143 
  cmc_id_parent varchar(50) COLLATE utf8_unicode_ci NOT NULL,
144 
  cmc_niveau int(11) NOT NULL,
145 
  PRIMARY KEY (cmc_id_mot_cle_utilisateur,cmc_id_proprietaire)
146 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT COMMENT='Table des mots clés, à utiliser avec des transactions !';
147 
 
148 
--
149 
-- Structure de la table 'cel_obs_images'
150 
--
151 
CREATE TABLE IF NOT EXISTS cel_obs_images (
152 
  coi_ce_image int(11) NOT NULL,
153 
  coi_ce_utilisateur varchar(100) CHARACTER SET utf8 NOT NULL,
154 
  coi_ce_observation int(11) NOT NULL,
155 
  coi_date_liaison datetime NOT NULL,
156 
  PRIMARY KEY (coi_ce_image,coi_ce_observation),
157 
  KEY coi_ce_utilisateur (coi_ce_utilisateur),
158 
  KEY coi_ce_observation (coi_ce_observation),
159 
  KEY coi_ce_image (coi_ce_image)
160 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
161 
 
162 
--
163 
-- Structure de la table 'locations'
164 
--
165 
CREATE TABLE IF NOT EXISTS locations (
166 
  `name` varchar(50) NOT NULL,
167 
  maj_name varchar(50) NOT NULL,
168 
  `code` varchar(3) NOT NULL DEFAULT '0',
169 
  insee_code varchar(5) NOT NULL DEFAULT '0',
170 
  sector varchar(3) NOT NULL,
171 
  x_utm varchar(10) NOT NULL DEFAULT '0',
172 
  y_utm varchar(10) NOT NULL DEFAULT '0',
173 
  wgs84_latitude double NOT NULL,
174 
  wgs84_longitude double NOT NULL,
175 
  update_date datetime NOT NULL,
176 
  PRIMARY KEY (`name`,`code`),
177 
  KEY MAJ (maj_name,`code`),
178 
  KEY sector (sector,x_utm,y_utm)
179 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;