| 1 | DROP TABLE IF EXISTS profile_addresses; |
| 2 | |
| 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, |
| 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, |
| 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), |
| 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 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 36 | |
| 37 | INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, countryId, |
| 38 | type, flags) |
| 39 | SELECT uid, adrid, postcode, datemaj, pub, NULL, 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; |
| 46 | |
| 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), |
| 65 | INDEX(iso_3166_1_a2), |
| 66 | INDEX(phonePrefix) |
| 67 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 68 | |
| 69 | CREATE TEMPORARY TABLE tmp_update_geoloc_pays ( |
| 70 | a2 CHAR(2) DEFAULT NULL, |
| 71 | code CHAR(4) DEFAULT NULL, |
| 72 | PRIMARY KEY(a2), |
| 73 | UNIQUE KEY(code) |
| 74 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 75 | |
| 76 | INSERT IGNORE INTO tmp_update_geoloc_pays (a2, code) |
| 77 | VALUES ('AF', 'AFG'), |
| 78 | ('ZA', 'ZA'), |
| 79 | ('AL', 'AL'), |
| 80 | ('DZ', 'DZ'), |
| 81 | ('DE', 'D'), |
| 82 | ('AD', 'AND'), |
| 83 | ('AO', 'ANG'), |
| 84 | ('AG', 'AG'), |
| 85 | ('AN', 'NA'), |
| 86 | ('SA', 'KSA'), |
| 87 | ('AR', 'RA'), |
| 88 | ('AM', 'ARM'), |
| 89 | ('AU', 'AUS'), |
| 90 | ('AT', 'A'), |
| 91 | ('AZ', 'AZ'), |
| 92 | ('BS', 'BS'), |
| 93 | ('BH', 'BRN'), |
| 94 | ('BD', 'BD'), |
| 95 | ('BB', 'BDS'), |
| 96 | ('BY', 'BY'), |
| 97 | ('BE', 'B'), |
| 98 | ('BZ', 'BZ'), |
| 99 | ('BJ', 'DY'), |
| 100 | ('BM', ''), |
| 101 | ('BT', 'BHT'), |
| 102 | ('BO', 'BOL'), |
| 103 | ('BA', 'BIH'), |
| 104 | ('BW', 'RB'), |
| 105 | ('BR', 'BR'), |
| 106 | ('BN', 'BRU'), |
| 107 | ('BG', 'BG'), |
| 108 | ('BF', 'BF'), |
| 109 | ('BI', 'BU'), |
| 110 | ('KH', 'K'), |
| 111 | ('CM', 'CAM'), |
| 112 | ('CA', 'CDN'), |
| 113 | ('CV', 'CV'), |
| 114 | ('CF', 'RCA'), |
| 115 | ('CG', 'CGO'), |
| 116 | ('CL', 'RCH'), |
| 117 | ('CN', 'CN'), |
| 118 | ('CY', 'CY'), |
| 119 | ('CO', 'CO'), |
| 120 | ('KM', 'COM'), |
| 121 | ('CG', 'RCB'), |
| 122 | ('CD', 'CD'), |
| 123 | ('KR', 'ROK'), |
| 124 | ('KP', 'DVRK'), |
| 125 | ('CR', 'CR'), |
| 126 | ('CI', 'CI'), |
| 127 | ('HR', 'HR'), |
| 128 | ('CU', 'C'), |
| 129 | ('DK', 'DK'), |
| 130 | ('DJ', 'DJI'), |
| 131 | ('DO', 'DOM'), |
| 132 | ('DM', 'WD'), |
| 133 | ('EG', 'ET'), |
| 134 | ('SV', 'ES'), |
| 135 | ('AE', 'UAE'), |
| 136 | ('EC', 'EC'), |
| 137 | ('ER', 'ER'), |
| 138 | ('ES', 'E'), |
| 139 | ('EE', 'EST'), |
| 140 | ('US', 'USA'), |
| 141 | ('ET', 'ETH'), |
| 142 | ('FO', 'FR'), |
| 143 | ('FJ', 'FJI'), |
| 144 | ('FI', 'FIN'), |
| 145 | ('FR', 'F'), |
| 146 | ('GA', 'G'), |
| 147 | ('GM', 'WAG'), |
| 148 | ('GE', 'GE'), |
| 149 | ('GH', 'GH'), |
| 150 | ('GI', 'GBZ'), |
| 151 | ('GR', 'GR'), |
| 152 | ('GD', 'WG'), |
| 153 | ('GL', 'KN'), |
| 154 | ('GT', 'GCA'), |
| 155 | ('GN', 'RG'), |
| 156 | ('GQ', 'GQ'), |
| 157 | ('GY', 'GUY'), |
| 158 | ('HT', 'RH'), |
| 159 | ('HN', 'HN'), |
| 160 | ('HK', 'HK'), |
| 161 | ('HU', 'H'), |
| 162 | ('VG', 'BVI'), |
| 163 | ('IN', 'IND'), |
| 164 | ('ID', 'RI'), |
| 165 | ('IR', 'IR'), |
| 166 | ('IQ', 'IRQ'), |
| 167 | ('IE', 'IRL'), |
| 168 | ('IS', 'IS'), |
| 169 | ('IL', 'IL'), |
| 170 | ('IT', 'I'), |
| 171 | ('JM', 'JA'), |
| 172 | ('JP', 'J'), |
| 173 | ('JO', 'JOR'), |
| 174 | ('KZ', 'KZ'), |
| 175 | ('KE', 'EAK'), |
| 176 | ('KG', 'KS'), |
| 177 | ('KI', 'KIR'), |
| 178 | ('KW', 'KWT'), |
| 179 | ('LA', 'LAO'), |
| 180 | ('LS', 'LS'), |
| 181 | ('LV', 'LV'), |
| 182 | ('LB', 'RL'), |
| 183 | ('LR', 'LB'), |
| 184 | ('LY', 'LAR'), |
| 185 | ('LI', 'FL'), |
| 186 | ('LT', 'LT'), |
| 187 | ('LU', 'L'), |
| 188 | ('MK', 'MK'), |
| 189 | ('MG', 'RM'), |
| 190 | ('MY', 'MAL'), |
| 191 | ('MW', 'MW'), |
| 192 | ('MV', 'MV'), |
| 193 | ('ML', 'RMM'), |
| 194 | ('MT', 'M'), |
| 195 | ('MA', 'MA'), |
| 196 | ('MH', 'MH'), |
| 197 | ('MU', 'MS'), |
| 198 | ('MR', 'RIM'), |
| 199 | ('MX', 'MEX'), |
| 200 | ('FM', 'FSM'), |
| 201 | ('MD', 'MD'), |
| 202 | ('MC', 'MC'), |
| 203 | ('MN', 'MGL'), |
| 204 | ('MZ', 'MOC'), |
| 205 | ('MM', 'MYA'), |
| 206 | ('NA', 'NAM'), |
| 207 | ('NR', 'NAU'), |
| 208 | ('NP', 'NEP'), |
| 209 | ('NI', 'NIC'), |
| 210 | ('NE', 'RN'), |
| 211 | ('NG', 'WAN'), |
| 212 | ('NO', 'N'), |
| 213 | ('NZ', 'NZ'), |
| 214 | ('OM', 'OM'), |
| 215 | ('UG', 'EAU'), |
| 216 | ('UZ', 'UZ'), |
| 217 | ('PK', 'PK'), |
| 218 | ('PW', 'PAL'), |
| 219 | ('PS', 'PS'), |
| 220 | ('PA', 'PA'), |
| 221 | ('PG', 'PNG'), |
| 222 | ('PY', 'PY'), |
| 223 | ('NL', 'NL'), |
| 224 | ('PE', 'PE'), |
| 225 | ('PH', 'RP'), |
| 226 | ('PL', 'PL'), |
| 227 | ('PT', 'P'), |
| 228 | ('QA', 'Q'), |
| 229 | ('RE', 'RE'), |
| 230 | ('RO', 'RO'), |
| 231 | ('GB', 'GB'), |
| 232 | ('RU', 'RUS'), |
| 233 | ('RW', 'RWA'), |
| 234 | ('LC', 'WL'), |
| 235 | ('KN', 'SCN'), |
| 236 | ('SM', 'RSM'), |
| 237 | ('VA', 'V'), |
| 238 | ('VC', 'WV'), |
| 239 | ('SB', 'SOL'), |
| 240 | ('WS', 'WS'), |
| 241 | ('ST', 'STP'), |
| 242 | ('SN', 'SN'), |
| 243 | ('CS', 'SCG'), |
| 244 | ('SC', 'SY'), |
| 245 | ('SL', 'WAL'), |
| 246 | ('SG', 'SGP'), |
| 247 | ('SK', 'SK'), |
| 248 | ('SI', 'SLO'), |
| 249 | ('SO', 'SP'), |
| 250 | ('SD', 'SUD'), |
| 251 | ('LK', 'CL'), |
| 252 | ('SE', 'S'), |
| 253 | ('CH', 'CH'), |
| 254 | ('SR', 'SME'), |
| 255 | ('SZ', 'SD'), |
| 256 | ('SY', 'SYR'), |
| 257 | ('TJ', 'TJ'), |
| 258 | ('TW', 'RC'), |
| 259 | ('TZ', 'EAT'), |
| 260 | ('TD', 'TCH'), |
| 261 | ('CZ', 'CZ'), |
| 262 | ('TH', 'THA'), |
| 263 | ('TL', 'TL'), |
| 264 | ('TG', 'RT'), |
| 265 | ('TO', 'TO'), |
| 266 | ('TT', 'TT'), |
| 267 | ('TN', 'TN'), |
| 268 | ('TM', 'TM'), |
| 269 | ('TR', 'TR'), |
| 270 | ('TV', 'TUV'), |
| 271 | ('UA', 'UA'), |
| 272 | ('UY', 'ROU'), |
| 273 | ('VU', 'VU'), |
| 274 | ('VE', 'YV'), |
| 275 | ('VN', 'VN'), |
| 276 | ('YE', 'YAR'), |
| 277 | ('YU', 'YU'), |
| 278 | ('ZM', 'Z'), |
| 279 | ('ZW', 'ZW'); |
| 280 | |
| 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) |
| 284 | SELECT g.a2, g.a3, |
| 285 | CASE g.a2 WHEN "MG" THEN 450 |
| 286 | WHEN "MK" THEN 807 |
| 287 | WHEN "ER" THEN 232 |
| 288 | ELSE n3 END, |
| 289 | worldrgn, pays, country, capital, nat, |
| 290 | phoneprf, phoneformat, t.code |
| 291 | FROM #x4dat#.geoloc_pays AS g |
| 292 | INNER JOIN tmp_update_geoloc_pays AS t ON (t.a2 = g.a2); |
| 293 | |
| 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, |
| 299 | PRIMARY KEY(id), |
| 300 | UNIQUE KEY(id, name, country), |
| 301 | INDEX(id), |
| 302 | INDEX(name), |
| 303 | INDEX(country) |
| 304 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 305 | |
| 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, |
| 311 | PRIMARY KEY(id), |
| 312 | UNIQUE KEY(id, name, country), |
| 313 | INDEX(id), |
| 314 | INDEX(name), |
| 315 | INDEX(country) |
| 316 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 317 | |
| 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, |
| 323 | PRIMARY KEY(id), |
| 324 | UNIQUE KEY(id, name, country), |
| 325 | INDEX(id), |
| 326 | INDEX(name), |
| 327 | INDEX(country) |
| 328 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 329 | |
| 330 | -- vim:set syntax=mysql: |