1 DROP TABLE IF EXISTS profile_addresses
;
3 CREATE TABLE profile_addresses (
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,
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,
23 updateTime DATETIME
NOT NULL DEFAULT 0,
24 pub
ENUM('public','ax','private') NOT NULL DEFAULT 'private',
25 comment VARCHAR(255) DEFAULT NULL,
26 PRIMARY KEY(pid
, jobid
, type, id),
31 INDEX localityId (localityId
),
32 INDEX administrativeAreaId (administrativeAreaId
),
33 INDEX subAdministrativeAreaId (subAdministrativeAreaId
),
34 INDEX countryId (countryId
)
35 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
37 INSERT INTO profile_addresses (pid
, id, postalCode
, updateTime
, pub
, comment, latitude
, longitude
,
38 countryId
, type, flags
)
39 SELECT uid
, adrid
, postcode
, datemaj
, pub
, NULL, glat
, glng
, IF(country
= '' OR country
= '00', NULL, country
),
40 IF(FIND_IN_SET('pro', 'statut'), 'job', 'home'),
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', ''))
45 FROM #x4dat#.adresses
;
47 DROP TABLE IF EXISTS geoloc_countries
;
48 CREATE TABLE geoloc_countries (
49 iso_3166_1_a2
CHAR(2) NOT NULL,
50 iso_3166_1_a3
CHAR(3) NOT NULL,
51 iso_3166_1_num
SMALLINT(3) UNSIGNED
NOT NULL,
52 worldRegion
CHAR(2) DEFAULT NULL,
53 countryFR
VARCHAR(255) NOT NULL,
54 country
VARCHAR(255) NOT NULL,
55 capital
VARCHAR(255) NOT NULL,
56 nationalityFR
VARCHAR(255) DEFAULT NULL,
57 nationality
VARCHAR(255) DEFAULT NULL,
58 phonePrefix
SMALLINT(5) UNSIGNED
DEFAULT NULL,
59 phoneFormat
VARCHAR(255) NOT NULL,
60 licensePlate
CHAR(4) DEFAULT NULL,
61 belongsTo
CHAR(2) DEFAULT NULL,
62 PRIMARY KEY(iso_3166_1_a2
),
63 UNIQUE KEY(iso_3166_1_a3
),
64 UNIQUE KEY(iso_3166_1_num
),
67 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
69 CREATE TEMPORARY TABLE tmp_update_geoloc_pays (
70 a2
CHAR(2) DEFAULT NULL,
71 code
CHAR(4) DEFAULT NULL,
74 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
76 INSERT IGNORE INTO tmp_update_geoloc_pays (a2
, code
)
281 INSERT INTO geoloc_countries (iso_3166_1_a2
, iso_3166_1_a3
, iso_3166_1_num
, worldRegion
,
282 countryFR
, country
, capital
, nationalityFR
,
283 phonePrefix
, phoneFormat
, licensePlate
)
285 CASE g.a2
WHEN "MG" THEN 450
289 worldrgn
, pays
, country
, capital
, nat
,
291 FROM #x4dat#.geoloc_pays
AS g
292 INNER JOIN tmp_update_geoloc_pays
AS t
ON (t.a2
= g.a2
);
294 DROP TABLE IF EXISTS geoloc_administrativeareas
;
295 CREATE TABLE geoloc_administrativeareas (
296 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
,
297 name VARCHAR(255) NOT NULL,
298 country
CHAR(2) NOT NULL,
300 UNIQUE KEY(id, name, country
),
304 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
306 DROP TABLE IF EXISTS geoloc_subadministrativeareas
;
307 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
308 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
,
309 name VARCHAR(255) NOT NULL,
310 country
CHAR(2) NOT NULL,
312 UNIQUE KEY(id, name, country
),
316 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
318 DROP TABLE IF EXISTS geoloc_localities
;
319 CREATE TABLE IF NOT EXISTS geoloc_localities (
320 id BIGINT(20) UNSIGNED
NOT NULL AUTO_INCREMENT
,
321 name VARCHAR(255) NOT NULL,
322 country
CHAR(2) NOT NULL,
324 UNIQUE KEY(id, name, country
),
328 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
330 -- vim:set syntax=mysql: