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;