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