From fcff94a52854dd37bba32fd111f1849e64d83c28 Mon Sep 17 00:00:00 2001 From: Florent Bruneau Date: Sun, 21 Feb 2010 21:12:03 +0100 Subject: [PATCH] Avoid copy of geoloc_pays during database migration. Signed-off-by: Florent Bruneau --- upgrade/account/copy_tables.php | 7 - upgrade/newdirectory-0.0.1/04_telephone.sql | 6 - upgrade/newdirectory-0.0.1/08_nationalities.sql | 220 ---------------------- upgrade/newdirectory-0.0.1/15_addresses.sql | 232 ++++++++++++++++++++++-- 4 files changed, 219 insertions(+), 246 deletions(-) delete mode 100644 upgrade/newdirectory-0.0.1/08_nationalities.sql diff --git a/upgrade/account/copy_tables.php b/upgrade/account/copy_tables.php index aa4b443..6bf56cc 100755 --- a/upgrade/account/copy_tables.php +++ b/upgrade/account/copy_tables.php @@ -22,10 +22,6 @@ require('./connect.db.inc.php'); -$globals->dbuser = 'admin'; -$globals->dbpwd = 'lknjiuhb'; -$globals->debug = 1; - function copyTable($source, $target, $convertToInnoDB = true) { XDB::execute('CREATE TABLE ' . $target . ' @@ -76,7 +72,6 @@ copyTable('#x4dat#.newsletter_art', 'newsletter_art'); copyTable('#x4dat#.newsletter_cat', 'newsletter_cat'); copyTable('#x4dat#.newsletter_ins', 'newsletter_ins'); - copyTable('#x4dat#.evenements', 'announces'); copyTable('#x4dat#.evenements_photo', 'announce_photos'); copyTable('#x4dat#.evenements_vus', 'announce_read'); @@ -134,8 +129,6 @@ copyTable('#x4dat#.survey_votes', 'survey_votes'); copyTable('#x4dat#.watch_profile', 'watch_profile'); copyTable('#x4dat#.perte_pass', 'account_lost_passwords'); -copyTable('#x4dat#.geoloc_pays', 'geoloc_pays'); - copyTable('#x4dat#.emails', 'emails'); copyTable('#x4dat#.aliases', 'aliases'); copyTable('#x4dat#.virtual', 'virtual'); diff --git a/upgrade/newdirectory-0.0.1/04_telephone.sql b/upgrade/newdirectory-0.0.1/04_telephone.sql index 00609e5..93846f0 100644 --- a/upgrade/newdirectory-0.0.1/04_telephone.sql +++ b/upgrade/newdirectory-0.0.1/04_telephone.sql @@ -16,10 +16,4 @@ CREATE TABLE `profile_phones` ( ) ENGINE=InnoDB, CHARSET=utf8; --- Adds a temporary column to convert phone prefixes from varchar to int -ALTER TABLE `geoloc_pays` ADD COLUMN `tmp_phoneprf` smallint unsigned NULL; - --- Adds phone format column -ALTER TABLE `geoloc_pays` ADD COLUMN `phoneformat` varchar(25) NOT NULL AFTER `nat`; - # vim:set syntax=mysql: diff --git a/upgrade/newdirectory-0.0.1/08_nationalities.sql b/upgrade/newdirectory-0.0.1/08_nationalities.sql deleted file mode 100644 index 439ba60..0000000 --- a/upgrade/newdirectory-0.0.1/08_nationalities.sql +++ /dev/null @@ -1,220 +0,0 @@ -ALTER TABLE geoloc_pays ADD COLUMN license_plate CHAR(4) DEFAULT NULL; -DROP TABLE IF EXISTS tmp_update_geoloc_pays; - -CREATE TEMPORARY TABLE IF NOT EXISTS tmp_update_geoloc_pays ( - a2 CHAR(2) DEFAULT NULL, - code CHAR(4) DEFAULT NULL, - PRIMARY KEY(a2), - UNIQUE KEY(code) -) ENGINE=InnoDB, CHARSET=utf8; - -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'); - - UPDATE geoloc_pays AS g -INNER JOIN tmp_update_geoloc_pays AS t ON (t.a2 = g.a2) - SET g.license_plate = t.code; - --- vim:set syntax=mysql: diff --git a/upgrade/newdirectory-0.0.1/15_addresses.sql b/upgrade/newdirectory-0.0.1/15_addresses.sql index a529d59..0294ea4 100644 --- a/upgrade/newdirectory-0.0.1/15_addresses.sql +++ b/upgrade/newdirectory-0.0.1/15_addresses.sql @@ -66,23 +66,230 @@ CREATE TABLE geoloc_countries ( INDEX(phonePrefix) ) ENGINE=InnoDB, CHARSET=utf8; -UPDATE geoloc_pays - SET n3 = 450 - WHERE a2 = "MG"; - -UPDATE geoloc_pays - SET n3 = 807 - WHERE a2 = "MK"; +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 = 232 - WHERE a2 = "ER"; +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, phoneformat, t.code + FROM #x4dat#.geoloc_pays AS g + INNER JOIN tmp_update_geoloc_pays AS t ON (t.a2 = g.a2); DROP TABLE IF EXISTS geoloc_administrativeareas; CREATE TABLE geoloc_administrativeareas ( @@ -120,5 +327,4 @@ CREATE TABLE IF NOT EXISTS geoloc_localities ( INDEX(country) ) ENGINE=InnoDB, CHARSET=utf8; -DROP TABLE geoloc_pays; -- vim:set syntax=mysql: -- 2.1.4