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 INSERT INTO profile_addresses (pid
, jobid
, id, postalCode
, pub
, comment,
48 latitude
, longitude
, countryId
, type)
49 SELECT uid
, entrid
, 0, postcode
, adr_pub
, NULL, glat
, glng
,
50 IF(country
= '' OR country
= '00', NULL, country
), 'job'
51 FROM #x4dat#.entreprises
;
53 DROP TABLE IF EXISTS geoloc_countries
;
54 CREATE TABLE geoloc_countries (
55 iso_3166_1_a2
CHAR(2) NOT NULL,
56 iso_3166_1_a3
CHAR(3) NOT NULL,
57 iso_3166_1_num
SMALLINT(3) UNSIGNED
NOT NULL,
58 worldRegion
CHAR(2) DEFAULT NULL,
59 countryFR
VARCHAR(255) NOT NULL,
60 country
VARCHAR(255) NOT NULL,
61 capital
VARCHAR(255) NOT NULL,
62 nationalityFR
VARCHAR(255) DEFAULT NULL,
63 nationality
VARCHAR(255) DEFAULT NULL,
64 phonePrefix
SMALLINT(5) UNSIGNED
DEFAULT NULL,
65 phoneFormat
VARCHAR(255) NOT NULL,
66 licensePlate
CHAR(4) DEFAULT NULL,
67 belongsTo
CHAR(2) DEFAULT NULL,
68 PRIMARY KEY(iso_3166_1_a2
),
69 UNIQUE KEY(iso_3166_1_a3
),
70 UNIQUE KEY(iso_3166_1_num
),
73 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
75 CREATE TEMPORARY TABLE tmp_update_geoloc_pays (
76 a2
CHAR(2) DEFAULT NULL,
77 code
CHAR(4) DEFAULT NULL,
80 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
82 INSERT IGNORE INTO tmp_update_geoloc_pays (a2
, code
)
287 INSERT INTO geoloc_countries (iso_3166_1_a2
, iso_3166_1_a3
, iso_3166_1_num
, worldRegion
,
288 countryFR
, country
, capital
, nationalityFR
,
289 phonePrefix
, phoneFormat
, licensePlate
)
291 CASE g.a2
WHEN "MG" THEN 450
295 worldrgn
, pays
, country
, capital
, nat
,
297 FROM #x4dat#.geoloc_pays
AS g
298 INNER JOIN tmp_update_geoloc_pays
AS t
ON (t.a2
= g.a2
);
300 DROP TABLE IF EXISTS geoloc_administrativeareas
;
301 CREATE TABLE geoloc_administrativeareas (
302 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
,
303 name VARCHAR(255) NOT NULL,
304 country
CHAR(2) NOT NULL,
306 UNIQUE KEY(id, name, country
),
310 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
312 DROP TABLE IF EXISTS geoloc_subadministrativeareas
;
313 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
314 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
,
315 name VARCHAR(255) NOT NULL,
316 country
CHAR(2) NOT NULL,
318 UNIQUE KEY(id, name, country
),
322 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
324 DROP TABLE IF EXISTS geoloc_localities
;
325 CREATE TABLE IF NOT EXISTS geoloc_localities (
326 id BIGINT(20) UNSIGNED
NOT NULL AUTO_INCREMENT
,
327 name VARCHAR(255) NOT NULL,
328 country
CHAR(2) NOT NULL,
330 UNIQUE KEY(id, name, country
),
334 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
336 -- vim:set syntax=mysql: