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 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);
54 DROP TABLE IF EXISTS geoloc_countries
;
55 CREATE TABLE geoloc_countries (
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,
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,
69 PRIMARY KEY(iso_3166_1_a2
),
70 UNIQUE KEY(iso_3166_1_a3
),
71 UNIQUE KEY(iso_3166_1_num
),
74 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
76 CREATE TEMPORARY TABLE tmp_update_geoloc_pays (
77 a2
CHAR(2) DEFAULT NULL,
78 code
CHAR(4) DEFAULT NULL,
81 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
83 INSERT IGNORE INTO tmp_update_geoloc_pays (a2
, code
)
288 INSERT INTO geoloc_countries (iso_3166_1_a2
, iso_3166_1_a3
, iso_3166_1_num
, worldRegion
,
289 countryFR
, country
, capital
, nationalityFR
,
290 phonePrefix
, phoneFormat
, licensePlate
)
292 CASE g.a2
WHEN "MG" THEN 450
296 worldrgn
, pays
, country
, capital
, nat
,
298 FROM #x4dat#.geoloc_pays
AS g
299 INNER JOIN tmp_update_geoloc_pays
AS t
ON (t.a2
= g.a2
);
301 DROP TABLE IF EXISTS geoloc_administrativeareas
;
302 CREATE TABLE geoloc_administrativeareas (
303 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
,
304 name VARCHAR(255) NOT NULL,
305 country
CHAR(2) NOT NULL,
307 UNIQUE KEY(id, name, country
),
311 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
313 DROP TABLE IF EXISTS geoloc_subadministrativeareas
;
314 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
315 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
,
316 name VARCHAR(255) NOT NULL,
317 country
CHAR(2) NOT NULL,
319 UNIQUE KEY(id, name, country
),
323 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
325 DROP TABLE IF EXISTS geoloc_localities
;
326 CREATE TABLE IF NOT EXISTS geoloc_localities (
327 id BIGINT(20) UNSIGNED
NOT NULL AUTO_INCREMENT
,
328 name VARCHAR(255) NOT NULL,
329 country
CHAR(2) NOT NULL,
331 UNIQUE KEY(id, name, country
),
335 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
337 -- vim:set syntax=mysql: