276bea96d0bc0eed155e85aaa178252c48353fcc
1 DROP TABLE IF EXISTS profile_addresses
;
3 CREATE TABLE IF NOT EXISTS 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
)
37 INSERT INTO profile_addresses (pid
, id, postalCode
, updateTime
, pub
, comment, latitude
, longitude
, countryId
,
39 SELECT uid
, adrid
, postcode
, datemaj
, pub
, comment, glat
, glng
, 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 CREATE TABLE IF NOT EXISTS geoloc_countries (
48 iso_3166_1_a2
CHAR(2) NOT NULL,
49 iso_3166_1_a3
CHAR(3) NOT NULL,
50 iso_3166_1_num
SMALLINT(3) UNSIGNED
NOT NULL,
51 worldRegion
CHAR(2) DEFAULT NULL,
52 countryFR
VARCHAR(255) NOT NULL,
53 country
VARCHAR(255) NOT NULL,
54 capital
VARCHAR(255) NOT NULL,
55 nationalityFR
VARCHAR(255) DEFAULT NULL,
56 nationality
VARCHAR(255) DEFAULT NULL,
57 phonePrefix
SMALLINT(5) UNSIGNED
DEFAULT NULL,
58 phoneFormat
VARCHAR(255) NOT NULL,
59 licensePlate
CHAR(4) DEFAULT NULL,
60 belongsTo
CHAR(2) DEFAULT NULL,
61 PRIMARY KEY(iso_3166_1_a2
),
62 UNIQUE KEY(iso_3166_1_a3
),
63 UNIQUE KEY(iso_3166_1_num
),
76 INSERT INTO geoloc_countries (iso_3166_1_a2
, iso_3166_1_a3
, iso_3166_1_num
, worldRegion
,
77 countryFR
, country
, capital
, nationalityFR
,
78 phonePrefix
, phoneFormat
, licensePlate
)
79 SELECT a2
, a3
, n3
, worldrgn
, pays
, country
, capital
, nat
, phoneprf
, phoneformat
, license_plate
81 DROP TABLE geoloc_pays
;
83 CREATE TABLE IF NOT EXISTS geoloc_administrativeareas (
84 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
,
85 name VARCHAR(255) NOT NULL,
86 country
CHAR(2) NOT NULL,
88 UNIQUE KEY(id, name, country
),
94 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
95 id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
,
96 name VARCHAR(255) NOT NULL,
97 country
CHAR(2) NOT NULL,
99 UNIQUE KEY(id, name, country
),
105 CREATE TABLE IF NOT EXISTS geoloc_localities (
106 id BIGINT(20) UNSIGNED
NOT NULL AUTO_INCREMENT
,
107 name VARCHAR(255) NOT NULL,
108 country
CHAR(2) NOT NULL,
110 UNIQUE KEY(id, name, country
),
116 -- vim:set syntax=mysql: