| 1 | DROP TABLE IF EXISTS profile_addresses; |
| 2 | |
| 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, |
| 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 | ) 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, 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 | |
| 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), |
| 64 | INDEX(iso_3166_1_a2), |
| 65 | INDEX(phonePrefix) |
| 66 | ) CHARSET=utf8; |
| 67 | |
| 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, |
| 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 | |
| 82 | CREATE TABLE IF NOT EXISTS geoloc_administrativeareas ( |
| 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 | |
| 93 | CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas ( |
| 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 | |
| 104 | CREATE TABLE IF NOT EXISTS geoloc_localities ( |
| 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: |