X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2F15_addresses.sql;h=8ce64b6025d6dd9228925f38d569af8188bdf6cb;hb=843a2191e4c76f0beb00c754f06974de76791238;hp=2311f472d9b2aa890a400762e62e2da0b146c7b0;hpb=b032b01b8919b36ec66f8a0395132953d8c01cf2;p=platal.git diff --git a/upgrade/newdirectory-0.0.1/15_addresses.sql b/upgrade/newdirectory-0.0.1/15_addresses.sql index 2311f47..8ce64b6 100644 --- a/upgrade/newdirectory-0.0.1/15_addresses.sql +++ b/upgrade/newdirectory-0.0.1/15_addresses.sql @@ -1,6 +1,6 @@ DROP TABLE IF EXISTS profile_addresses; -CREATE TABLE IF NOT EXISTS profile_addresses ( +CREATE TABLE profile_addresses ( pid INT(11) DEFAULT NULL, jobid INT(6) UNSIGNED DEFAULT NULL, type ENUM('home','job','hq') NOT NULL DEFAULT 'home', @@ -32,19 +32,27 @@ CREATE TABLE IF NOT EXISTS profile_addresses ( INDEX administrativeAreaId (administrativeAreaId), INDEX subAdministrativeAreaId (subAdministrativeAreaId), INDEX countryId (countryId) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; -INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, countryId, - type, flags) - SELECT uid, adrid, postcode, datemaj, pub, comment, glat, glng, country, +INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, + countryId, type, flags) + SELECT uid, adrid, postcode, datemaj, pub, NULL, glat, glng, IF(country = '' OR country = '00', NULL, country), IF(FIND_IN_SET('pro', 'statut'), 'job', 'home'), - CONCAT(IF(FIND_IN_SET('res-secondaire', 'statut'), 'secondary,', ''), - IF(FIND_IN_SET('courrier', 'statut'), 'mail,', ''), - IF(FIND_IN_SET('active', 'statut'), 'current,', ''), - IF(FIND_IN_SET('temporaire', 'statut'), 'temporary', '')) - FROM adresses; + CONCAT(IF(FIND_IN_SET('res-secondaire', statut), 'secondary,', ''), + IF(FIND_IN_SET('courrier', statut), 'mail,', ''), + IF(FIND_IN_SET('active', statut), 'current,', ''), + IF(FIND_IN_SET('temporaire', statut), 'temporary', '')) + FROM #x4dat#.adresses; -CREATE TABLE IF NOT EXISTS geoloc_countries ( +INSERT INTO profile_addresses (pid, jobid, id, postalCode, pub, comment, + latitude, longitude, countryId, type) + SELECT e.uid, j.id, e.entrid, e.postcode, e.adr_pub, NULL, e.glat, e.glng, + IF(e.country = '' OR e.country = '00', NULL, e.country), 'job' + FROM #x4dat#.entreprises AS e + INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name); + +DROP TABLE IF EXISTS geoloc_countries; +CREATE TABLE geoloc_countries ( iso_3166_1_a2 CHAR(2) NOT NULL, iso_3166_1_a3 CHAR(3) NOT NULL, iso_3166_1_num SMALLINT(3) UNSIGNED NOT NULL, @@ -63,23 +71,235 @@ CREATE TABLE IF NOT EXISTS geoloc_countries ( UNIQUE KEY(iso_3166_1_num), INDEX(iso_3166_1_a2), INDEX(phonePrefix) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; -UPDATE geoloc_pays - SET n3 = 450 - WHERE a2 = "MG"; +CREATE TEMPORARY TABLE tmp_update_geoloc_pays ( + a2 CHAR(2) DEFAULT NULL, + code CHAR(4) DEFAULT NULL, + PRIMARY KEY(a2), + UNIQUE KEY(code) +) ENGINE=InnoDB, CHARSET=utf8; -UPDATE geoloc_pays - SET n3 = 807 - WHERE a2 = "MK"; +INSERT IGNORE INTO tmp_update_geoloc_pays (a2, code) + VALUES ('AF', 'AFG'), + ('ZA', 'ZA'), + ('AL', 'AL'), + ('DZ', 'DZ'), + ('DE', 'D'), + ('AD', 'AND'), + ('AO', 'ANG'), + ('AG', 'AG'), + ('AN', 'NA'), + ('SA', 'KSA'), + ('AR', 'RA'), + ('AM', 'ARM'), + ('AU', 'AUS'), + ('AT', 'A'), + ('AZ', 'AZ'), + ('BS', 'BS'), + ('BH', 'BRN'), + ('BD', 'BD'), + ('BB', 'BDS'), + ('BY', 'BY'), + ('BE', 'B'), + ('BZ', 'BZ'), + ('BJ', 'DY'), + ('BM', ''), + ('BT', 'BHT'), + ('BO', 'BOL'), + ('BA', 'BIH'), + ('BW', 'RB'), + ('BR', 'BR'), + ('BN', 'BRU'), + ('BG', 'BG'), + ('BF', 'BF'), + ('BI', 'BU'), + ('KH', 'K'), + ('CM', 'CAM'), + ('CA', 'CDN'), + ('CV', 'CV'), + ('CF', 'RCA'), + ('CG', 'CGO'), + ('CL', 'RCH'), + ('CN', 'CN'), + ('CY', 'CY'), + ('CO', 'CO'), + ('KM', 'COM'), + ('CG', 'RCB'), + ('CD', 'CD'), + ('KR', 'ROK'), + ('KP', 'DVRK'), + ('CR', 'CR'), + ('CI', 'CI'), + ('HR', 'HR'), + ('CU', 'C'), + ('DK', 'DK'), + ('DJ', 'DJI'), + ('DO', 'DOM'), + ('DM', 'WD'), + ('EG', 'ET'), + ('SV', 'ES'), + ('AE', 'UAE'), + ('EC', 'EC'), + ('ER', 'ER'), + ('ES', 'E'), + ('EE', 'EST'), + ('US', 'USA'), + ('ET', 'ETH'), + ('FO', 'FR'), + ('FJ', 'FJI'), + ('FI', 'FIN'), + ('FR', 'F'), + ('GA', 'G'), + ('GM', 'WAG'), + ('GE', 'GE'), + ('GH', 'GH'), + ('GI', 'GBZ'), + ('GR', 'GR'), + ('GD', 'WG'), + ('GL', 'KN'), + ('GT', 'GCA'), + ('GN', 'RG'), + ('GQ', 'GQ'), + ('GY', 'GUY'), + ('HT', 'RH'), + ('HN', 'HN'), + ('HK', 'HK'), + ('HU', 'H'), + ('VG', 'BVI'), + ('IN', 'IND'), + ('ID', 'RI'), + ('IR', 'IR'), + ('IQ', 'IRQ'), + ('IE', 'IRL'), + ('IS', 'IS'), + ('IL', 'IL'), + ('IT', 'I'), + ('JM', 'JA'), + ('JP', 'J'), + ('JO', 'JOR'), + ('KZ', 'KZ'), + ('KE', 'EAK'), + ('KG', 'KS'), + ('KI', 'KIR'), + ('KW', 'KWT'), + ('LA', 'LAO'), + ('LS', 'LS'), + ('LV', 'LV'), + ('LB', 'RL'), + ('LR', 'LB'), + ('LY', 'LAR'), + ('LI', 'FL'), + ('LT', 'LT'), + ('LU', 'L'), + ('MK', 'MK'), + ('MG', 'RM'), + ('MY', 'MAL'), + ('MW', 'MW'), + ('MV', 'MV'), + ('ML', 'RMM'), + ('MT', 'M'), + ('MA', 'MA'), + ('MH', 'MH'), + ('MU', 'MS'), + ('MR', 'RIM'), + ('MX', 'MEX'), + ('FM', 'FSM'), + ('MD', 'MD'), + ('MC', 'MC'), + ('MN', 'MGL'), + ('MZ', 'MOC'), + ('MM', 'MYA'), + ('NA', 'NAM'), + ('NR', 'NAU'), + ('NP', 'NEP'), + ('NI', 'NIC'), + ('NE', 'RN'), + ('NG', 'WAN'), + ('NO', 'N'), + ('NZ', 'NZ'), + ('OM', 'OM'), + ('UG', 'EAU'), + ('UZ', 'UZ'), + ('PK', 'PK'), + ('PW', 'PAL'), + ('PS', 'PS'), + ('PA', 'PA'), + ('PG', 'PNG'), + ('PY', 'PY'), + ('NL', 'NL'), + ('PE', 'PE'), + ('PH', 'RP'), + ('PL', 'PL'), + ('PT', 'P'), + ('QA', 'Q'), + ('RE', 'RE'), + ('RO', 'RO'), + ('GB', 'GB'), + ('RU', 'RUS'), + ('RW', 'RWA'), + ('LC', 'WL'), + ('KN', 'SCN'), + ('SM', 'RSM'), + ('VA', 'V'), + ('VC', 'WV'), + ('SB', 'SOL'), + ('WS', 'WS'), + ('ST', 'STP'), + ('SN', 'SN'), + ('CS', 'SCG'), + ('SC', 'SY'), + ('SL', 'WAL'), + ('SG', 'SGP'), + ('SK', 'SK'), + ('SI', 'SLO'), + ('SO', 'SP'), + ('SD', 'SUD'), + ('LK', 'CL'), + ('SE', 'S'), + ('CH', 'CH'), + ('SR', 'SME'), + ('SZ', 'SD'), + ('SY', 'SYR'), + ('TJ', 'TJ'), + ('TW', 'RC'), + ('TZ', 'EAT'), + ('TD', 'TCH'), + ('CZ', 'CZ'), + ('TH', 'THA'), + ('TL', 'TL'), + ('TG', 'RT'), + ('TO', 'TO'), + ('TT', 'TT'), + ('TN', 'TN'), + ('TM', 'TM'), + ('TR', 'TR'), + ('TV', 'TUV'), + ('UA', 'UA'), + ('UY', 'ROU'), + ('VU', 'VU'), + ('VE', 'YV'), + ('VN', 'VN'), + ('YE', 'YAR'), + ('YU', 'YU'), + ('ZM', 'Z'), + ('ZW', 'ZW'); INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion, countryFR, country, capital, nationalityFR, phonePrefix, phoneFormat, licensePlate) - SELECT a2, a3, n3, worldrgn, pays, country, capital, nat, phoneprf, phoneformat, license_plate - FROM geoloc_pays; + SELECT g.a2, g.a3, + CASE g.a2 WHEN "MG" THEN 450 + WHEN "MK" THEN 807 + WHEN "ER" THEN 232 + ELSE n3 END, + worldrgn, pays, country, capital, nat, + phoneprf, '', t.code + FROM #x4dat#.geoloc_pays AS g + INNER JOIN tmp_update_geoloc_pays AS t ON (t.a2 = g.a2); -CREATE TABLE IF NOT EXISTS geoloc_administrativeareas ( +DROP TABLE IF EXISTS geoloc_administrativeareas; +CREATE TABLE geoloc_administrativeareas ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, country CHAR(2) NOT NULL, @@ -88,8 +308,9 @@ CREATE TABLE IF NOT EXISTS geoloc_administrativeareas ( INDEX(id), INDEX(name), INDEX(country) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; +DROP TABLE IF EXISTS geoloc_subadministrativeareas; CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, @@ -99,8 +320,9 @@ CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas ( INDEX(id), INDEX(name), INDEX(country) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; +DROP TABLE IF EXISTS geoloc_localities; CREATE TABLE IF NOT EXISTS geoloc_localities ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, @@ -110,6 +332,6 @@ CREATE TABLE IF NOT EXISTS geoloc_localities ( INDEX(id), INDEX(name), INDEX(country) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; -- vim:set syntax=mysql: