Commit | Line | Data |
---|---|---|
8f2f5083 SJ |
1 | DROP TABLE IF EXISTS profile_addresses; |
2 | ||
d0293d9b | 3 | CREATE TABLE profile_addresses ( |
041a5cec SJ |
4 | pid INT(11) DEFAULT NULL, |
5 | jobid INT(6) UNSIGNED DEFAULT NULL, | |
6 | type ENUM('home','job','hq') NOT NULL DEFAULT 'home', | |
7 | id TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, | |
8 | flags SET('current','temporary','secondary','mail','cedex') DEFAULT NULL, | |
9 | accuracy TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, | |
10 | text TEXT NOT NULL, | |
11 | postalText TEXT NOT NULL, | |
12 | postalCode VARCHAR(255) DEFAULT NULL, | |
13 | localityId INT(11) DEFAULT NULL, | |
14 | subAdministrativeAreaId INT(11) DEFAULT NULL, | |
15 | administrativeAreaId INT(11) DEFAULT NULL, | |
16 | countryId CHAR(2) DEFAULT NULL, | |
17 | latitude FLOAT(10,7) DEFAULT NULL, | |
18 | longitude FLOAT(10,7) DEFAULT NULL, | |
19 | north FLOAT(10,7) DEFAULT NULL, | |
20 | south FLOAT(10,7) DEFAULT NULL, | |
21 | east FLOAT(10,7) DEFAULT NULL, | |
22 | west FLOAT(10,7) DEFAULT NULL, | |
eecbf7f5 | 23 | updateTime DATETIME NOT NULL DEFAULT 0, |
041a5cec SJ |
24 | pub ENUM('public','ax','private') NOT NULL DEFAULT 'private', |
25 | comment VARCHAR(255) DEFAULT NULL, | |
26 | PRIMARY KEY(pid, jobid, type, id), | |
27 | INDEX pid (pid), | |
28 | INDEX jobid (jobid), | |
29 | INDEX type (type), | |
30 | INDEX adrid (id), | |
31 | INDEX localityId (localityId), | |
32 | INDEX administrativeAreaId (administrativeAreaId), | |
33 | INDEX subAdministrativeAreaId (subAdministrativeAreaId), | |
34 | INDEX countryId (countryId) | |
950bf4f6 | 35 | ) ENGINE=InnoDB, CHARSET=utf8; |
041a5cec | 36 | |
30c41429 | 37 | INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, |
ff900fde SJ |
38 | countryId, type, flags) |
39 | SELECT uid, adrid, postcode, datemaj, pub, NULL, glat, glng, IF(country = '' OR country = '00', NULL, country), | |
8f2f5083 | 40 | IF(FIND_IN_SET('pro', 'statut'), 'job', 'home'), |
68f434ea SJ |
41 | CONCAT(IF(FIND_IN_SET('res-secondaire', statut), 'secondary,', ''), |
42 | IF(FIND_IN_SET('courrier', statut), 'mail,', ''), | |
43 | IF(FIND_IN_SET('active', statut), 'current,', ''), | |
44 | IF(FIND_IN_SET('temporaire', statut), 'temporary', '')) | |
100e66fc | 45 | FROM #x4dat#.adresses; |
8f2f5083 | 46 | |
d989355f SJ |
47 | INSERT INTO profile_addresses (pid, jobid, id, postalCode, pub, comment, |
48 | latitude, longitude, countryId, type) | |
1daa72c2 SJ |
49 | SELECT e.uid, j.id, e.entrid, e.postcode, e.adr_pub, NULL, e.glat, e.glng, |
50 | IF(e.country = '' OR e.country = '00', NULL, e.country), 'job' | |
51 | FROM #x4dat#.entreprises AS e | |
52 | INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name); | |
d989355f | 53 | |
d0293d9b FB |
54 | DROP TABLE IF EXISTS geoloc_countries; |
55 | CREATE TABLE geoloc_countries ( | |
644f8ee5 SJ |
56 | iso_3166_1_a2 CHAR(2) NOT NULL, |
57 | iso_3166_1_a3 CHAR(3) NOT NULL, | |
58 | iso_3166_1_num SMALLINT(3) UNSIGNED NOT NULL, | |
041a5cec SJ |
59 | worldRegion CHAR(2) DEFAULT NULL, |
60 | countryFR VARCHAR(255) NOT NULL, | |
61 | country VARCHAR(255) NOT NULL, | |
62 | capital VARCHAR(255) NOT NULL, | |
63 | nationalityFR VARCHAR(255) DEFAULT NULL, | |
64 | nationality VARCHAR(255) DEFAULT NULL, | |
65 | phonePrefix SMALLINT(5) UNSIGNED DEFAULT NULL, | |
66 | phoneFormat VARCHAR(255) NOT NULL, | |
67 | licensePlate CHAR(4) DEFAULT NULL, | |
68 | belongsTo CHAR(2) DEFAULT NULL, | |
644f8ee5 SJ |
69 | PRIMARY KEY(iso_3166_1_a2), |
70 | UNIQUE KEY(iso_3166_1_a3), | |
71 | UNIQUE KEY(iso_3166_1_num), | |
72 | INDEX(iso_3166_1_a2), | |
041a5cec | 73 | INDEX(phonePrefix) |
950bf4f6 | 74 | ) ENGINE=InnoDB, CHARSET=utf8; |
041a5cec | 75 | |
fcff94a5 FB |
76 | CREATE TEMPORARY TABLE tmp_update_geoloc_pays ( |
77 | a2 CHAR(2) DEFAULT NULL, | |
78 | code CHAR(4) DEFAULT NULL, | |
79 | PRIMARY KEY(a2), | |
80 | UNIQUE KEY(code) | |
81 | ) ENGINE=InnoDB, CHARSET=utf8; | |
644f8ee5 | 82 | |
fcff94a5 FB |
83 | INSERT IGNORE INTO tmp_update_geoloc_pays (a2, code) |
84 | VALUES ('AF', 'AFG'), | |
85 | ('ZA', 'ZA'), | |
86 | ('AL', 'AL'), | |
87 | ('DZ', 'DZ'), | |
88 | ('DE', 'D'), | |
89 | ('AD', 'AND'), | |
90 | ('AO', 'ANG'), | |
91 | ('AG', 'AG'), | |
92 | ('AN', 'NA'), | |
93 | ('SA', 'KSA'), | |
94 | ('AR', 'RA'), | |
95 | ('AM', 'ARM'), | |
96 | ('AU', 'AUS'), | |
97 | ('AT', 'A'), | |
98 | ('AZ', 'AZ'), | |
99 | ('BS', 'BS'), | |
100 | ('BH', 'BRN'), | |
101 | ('BD', 'BD'), | |
102 | ('BB', 'BDS'), | |
103 | ('BY', 'BY'), | |
104 | ('BE', 'B'), | |
105 | ('BZ', 'BZ'), | |
106 | ('BJ', 'DY'), | |
107 | ('BM', ''), | |
108 | ('BT', 'BHT'), | |
109 | ('BO', 'BOL'), | |
110 | ('BA', 'BIH'), | |
111 | ('BW', 'RB'), | |
112 | ('BR', 'BR'), | |
113 | ('BN', 'BRU'), | |
114 | ('BG', 'BG'), | |
115 | ('BF', 'BF'), | |
116 | ('BI', 'BU'), | |
117 | ('KH', 'K'), | |
118 | ('CM', 'CAM'), | |
119 | ('CA', 'CDN'), | |
120 | ('CV', 'CV'), | |
121 | ('CF', 'RCA'), | |
122 | ('CG', 'CGO'), | |
123 | ('CL', 'RCH'), | |
124 | ('CN', 'CN'), | |
125 | ('CY', 'CY'), | |
126 | ('CO', 'CO'), | |
127 | ('KM', 'COM'), | |
128 | ('CG', 'RCB'), | |
129 | ('CD', 'CD'), | |
130 | ('KR', 'ROK'), | |
131 | ('KP', 'DVRK'), | |
132 | ('CR', 'CR'), | |
133 | ('CI', 'CI'), | |
134 | ('HR', 'HR'), | |
135 | ('CU', 'C'), | |
136 | ('DK', 'DK'), | |
137 | ('DJ', 'DJI'), | |
138 | ('DO', 'DOM'), | |
139 | ('DM', 'WD'), | |
140 | ('EG', 'ET'), | |
141 | ('SV', 'ES'), | |
142 | ('AE', 'UAE'), | |
143 | ('EC', 'EC'), | |
144 | ('ER', 'ER'), | |
145 | ('ES', 'E'), | |
146 | ('EE', 'EST'), | |
147 | ('US', 'USA'), | |
148 | ('ET', 'ETH'), | |
149 | ('FO', 'FR'), | |
150 | ('FJ', 'FJI'), | |
151 | ('FI', 'FIN'), | |
152 | ('FR', 'F'), | |
153 | ('GA', 'G'), | |
154 | ('GM', 'WAG'), | |
155 | ('GE', 'GE'), | |
156 | ('GH', 'GH'), | |
157 | ('GI', 'GBZ'), | |
158 | ('GR', 'GR'), | |
159 | ('GD', 'WG'), | |
160 | ('GL', 'KN'), | |
161 | ('GT', 'GCA'), | |
162 | ('GN', 'RG'), | |
163 | ('GQ', 'GQ'), | |
164 | ('GY', 'GUY'), | |
165 | ('HT', 'RH'), | |
166 | ('HN', 'HN'), | |
167 | ('HK', 'HK'), | |
168 | ('HU', 'H'), | |
169 | ('VG', 'BVI'), | |
170 | ('IN', 'IND'), | |
171 | ('ID', 'RI'), | |
172 | ('IR', 'IR'), | |
173 | ('IQ', 'IRQ'), | |
174 | ('IE', 'IRL'), | |
175 | ('IS', 'IS'), | |
176 | ('IL', 'IL'), | |
177 | ('IT', 'I'), | |
178 | ('JM', 'JA'), | |
179 | ('JP', 'J'), | |
180 | ('JO', 'JOR'), | |
181 | ('KZ', 'KZ'), | |
182 | ('KE', 'EAK'), | |
183 | ('KG', 'KS'), | |
184 | ('KI', 'KIR'), | |
185 | ('KW', 'KWT'), | |
186 | ('LA', 'LAO'), | |
187 | ('LS', 'LS'), | |
188 | ('LV', 'LV'), | |
189 | ('LB', 'RL'), | |
190 | ('LR', 'LB'), | |
191 | ('LY', 'LAR'), | |
192 | ('LI', 'FL'), | |
193 | ('LT', 'LT'), | |
194 | ('LU', 'L'), | |
195 | ('MK', 'MK'), | |
196 | ('MG', 'RM'), | |
197 | ('MY', 'MAL'), | |
198 | ('MW', 'MW'), | |
199 | ('MV', 'MV'), | |
200 | ('ML', 'RMM'), | |
201 | ('MT', 'M'), | |
202 | ('MA', 'MA'), | |
203 | ('MH', 'MH'), | |
204 | ('MU', 'MS'), | |
205 | ('MR', 'RIM'), | |
206 | ('MX', 'MEX'), | |
207 | ('FM', 'FSM'), | |
208 | ('MD', 'MD'), | |
209 | ('MC', 'MC'), | |
210 | ('MN', 'MGL'), | |
211 | ('MZ', 'MOC'), | |
212 | ('MM', 'MYA'), | |
213 | ('NA', 'NAM'), | |
214 | ('NR', 'NAU'), | |
215 | ('NP', 'NEP'), | |
216 | ('NI', 'NIC'), | |
217 | ('NE', 'RN'), | |
218 | ('NG', 'WAN'), | |
219 | ('NO', 'N'), | |
220 | ('NZ', 'NZ'), | |
221 | ('OM', 'OM'), | |
222 | ('UG', 'EAU'), | |
223 | ('UZ', 'UZ'), | |
224 | ('PK', 'PK'), | |
225 | ('PW', 'PAL'), | |
226 | ('PS', 'PS'), | |
227 | ('PA', 'PA'), | |
228 | ('PG', 'PNG'), | |
229 | ('PY', 'PY'), | |
230 | ('NL', 'NL'), | |
231 | ('PE', 'PE'), | |
232 | ('PH', 'RP'), | |
233 | ('PL', 'PL'), | |
234 | ('PT', 'P'), | |
235 | ('QA', 'Q'), | |
236 | ('RE', 'RE'), | |
237 | ('RO', 'RO'), | |
238 | ('GB', 'GB'), | |
239 | ('RU', 'RUS'), | |
240 | ('RW', 'RWA'), | |
241 | ('LC', 'WL'), | |
242 | ('KN', 'SCN'), | |
243 | ('SM', 'RSM'), | |
244 | ('VA', 'V'), | |
245 | ('VC', 'WV'), | |
246 | ('SB', 'SOL'), | |
247 | ('WS', 'WS'), | |
248 | ('ST', 'STP'), | |
249 | ('SN', 'SN'), | |
250 | ('CS', 'SCG'), | |
251 | ('SC', 'SY'), | |
252 | ('SL', 'WAL'), | |
253 | ('SG', 'SGP'), | |
254 | ('SK', 'SK'), | |
255 | ('SI', 'SLO'), | |
256 | ('SO', 'SP'), | |
257 | ('SD', 'SUD'), | |
258 | ('LK', 'CL'), | |
259 | ('SE', 'S'), | |
260 | ('CH', 'CH'), | |
261 | ('SR', 'SME'), | |
262 | ('SZ', 'SD'), | |
263 | ('SY', 'SYR'), | |
264 | ('TJ', 'TJ'), | |
265 | ('TW', 'RC'), | |
266 | ('TZ', 'EAT'), | |
267 | ('TD', 'TCH'), | |
268 | ('CZ', 'CZ'), | |
269 | ('TH', 'THA'), | |
270 | ('TL', 'TL'), | |
271 | ('TG', 'RT'), | |
272 | ('TO', 'TO'), | |
273 | ('TT', 'TT'), | |
274 | ('TN', 'TN'), | |
275 | ('TM', 'TM'), | |
276 | ('TR', 'TR'), | |
277 | ('TV', 'TUV'), | |
278 | ('UA', 'UA'), | |
279 | ('UY', 'ROU'), | |
280 | ('VU', 'VU'), | |
281 | ('VE', 'YV'), | |
282 | ('VN', 'VN'), | |
283 | ('YE', 'YAR'), | |
284 | ('YU', 'YU'), | |
285 | ('ZM', 'Z'), | |
286 | ('ZW', 'ZW'); | |
d0293d9b | 287 | |
644f8ee5 | 288 | INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion, |
041a5cec SJ |
289 | countryFR, country, capital, nationalityFR, |
290 | phonePrefix, phoneFormat, licensePlate) | |
fcff94a5 FB |
291 | SELECT g.a2, g.a3, |
292 | CASE g.a2 WHEN "MG" THEN 450 | |
293 | WHEN "MK" THEN 807 | |
294 | WHEN "ER" THEN 232 | |
295 | ELSE n3 END, | |
296 | worldrgn, pays, country, capital, nat, | |
ff900fde | 297 | phoneprf, '', t.code |
fcff94a5 FB |
298 | FROM #x4dat#.geoloc_pays AS g |
299 | INNER JOIN tmp_update_geoloc_pays AS t ON (t.a2 = g.a2); | |
041a5cec | 300 | |
d0293d9b FB |
301 | DROP TABLE IF EXISTS geoloc_administrativeareas; |
302 | CREATE TABLE geoloc_administrativeareas ( | |
041a5cec SJ |
303 | id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, |
304 | name VARCHAR(255) NOT NULL, | |
305 | country CHAR(2) NOT NULL, | |
306 | PRIMARY KEY(id), | |
307 | UNIQUE KEY(id, name, country), | |
308 | INDEX(id), | |
309 | INDEX(name), | |
310 | INDEX(country) | |
950bf4f6 | 311 | ) ENGINE=InnoDB, CHARSET=utf8; |
041a5cec | 312 | |
d0293d9b | 313 | DROP TABLE IF EXISTS geoloc_subadministrativeareas; |
4c906759 | 314 | CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas ( |
041a5cec SJ |
315 | id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, |
316 | name VARCHAR(255) NOT NULL, | |
317 | country CHAR(2) NOT NULL, | |
318 | PRIMARY KEY(id), | |
319 | UNIQUE KEY(id, name, country), | |
320 | INDEX(id), | |
321 | INDEX(name), | |
322 | INDEX(country) | |
950bf4f6 | 323 | ) ENGINE=InnoDB, CHARSET=utf8; |
041a5cec | 324 | |
d0293d9b | 325 | DROP TABLE IF EXISTS geoloc_localities; |
4c906759 | 326 | CREATE TABLE IF NOT EXISTS geoloc_localities ( |
041a5cec SJ |
327 | id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
328 | name VARCHAR(255) NOT NULL, | |
329 | country CHAR(2) NOT NULL, | |
330 | PRIMARY KEY(id), | |
331 | UNIQUE KEY(id, name, country), | |
332 | INDEX(id), | |
333 | INDEX(name), | |
334 | INDEX(country) | |
950bf4f6 | 335 | ) ENGINE=InnoDB, CHARSET=utf8; |
041a5cec SJ |
336 | |
337 | -- vim:set syntax=mysql: |