Commit | Line | Data |
---|---|---|
8f2f5083 SJ |
1 | DROP TABLE IF EXISTS profile_addresses; |
2 | ||
041a5cec SJ |
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, | |
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) | |
35 | ) CHARSET=utf8; | |
36 | ||
8f2f5083 SJ |
37 | INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, countryId, |
38 | type, flags) | |
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 adresses; | |
46 | ||
041a5cec | 47 | CREATE TABLE IF NOT EXISTS geoloc_countries ( |
644f8ee5 SJ |
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, | |
041a5cec SJ |
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, | |
644f8ee5 SJ |
61 | PRIMARY KEY(iso_3166_1_a2), |
62 | UNIQUE KEY(iso_3166_1_a3), | |
63 | UNIQUE KEY(iso_3166_1_num), | |
64 | INDEX(iso_3166_1_a2), | |
041a5cec SJ |
65 | INDEX(phonePrefix) |
66 | ) CHARSET=utf8; | |
67 | ||
644f8ee5 SJ |
68 | UPDATE geoloc_pays |
69 | SET n3 = 450 | |
70 | WHERE a2 = "MG"; | |
71 | ||
72 | UPDATE geoloc_pays | |
73 | SET n3 = 807 | |
74 | WHERE a2 = "MK"; | |
75 | ||
76 | INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion, | |
041a5cec SJ |
77 | countryFR, country, capital, nationalityFR, |
78 | phonePrefix, phoneFormat, licensePlate) | |
79 | SELECT a2, a3, n3, worldrgn, pays, country, capital, nat, phoneprf, phoneformat, license_plate | |
80 | FROM geoloc_pays; | |
81 | ||
4c906759 | 82 | CREATE TABLE IF NOT EXISTS geoloc_administrativeareas ( |
041a5cec SJ |
83 | id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, |
84 | name VARCHAR(255) NOT NULL, | |
85 | country CHAR(2) NOT NULL, | |
86 | PRIMARY KEY(id), | |
87 | UNIQUE KEY(id, name, country), | |
88 | INDEX(id), | |
89 | INDEX(name), | |
90 | INDEX(country) | |
91 | ) CHARSET=utf8; | |
92 | ||
4c906759 | 93 | CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas ( |
041a5cec SJ |
94 | id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, |
95 | name VARCHAR(255) NOT NULL, | |
96 | country CHAR(2) NOT NULL, | |
97 | PRIMARY KEY(id), | |
98 | UNIQUE KEY(id, name, country), | |
99 | INDEX(id), | |
100 | INDEX(name), | |
101 | INDEX(country) | |
102 | ) CHARSET=utf8; | |
103 | ||
4c906759 | 104 | CREATE TABLE IF NOT EXISTS geoloc_localities ( |
041a5cec SJ |
105 | id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
106 | name VARCHAR(255) NOT NULL, | |
107 | country CHAR(2) NOT NULL, | |
108 | PRIMARY KEY(id), | |
109 | UNIQUE KEY(id, name, country), | |
110 | INDEX(id), | |
111 | INDEX(name), | |
112 | INDEX(country) | |
113 | ) CHARSET=utf8; | |
114 | ||
115 | -- vim:set syntax=mysql: |