From b8ed94e96c3e69447bdec8d80dff82b0e8bc9ad0 Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Tue, 16 Sep 2008 15:37:44 +0200 Subject: [PATCH] Adds scripts to update geoloc_pays and to import AX nationalities. --- upgrade/merge-0.0.1/04_nationalities.sql | 28 +++ upgrade/merge-0.0.1/05_check_nationalities.sql | 10 ++ upgrade/merge-0.0.1/06_merge_nationalities.sql | 23 +++ upgrade/newdirectory-0.0.1/08_nationalities.sql | 219 ++++++++++++++++++++++++ 4 files changed, 280 insertions(+) create mode 100644 upgrade/merge-0.0.1/04_nationalities.sql create mode 100644 upgrade/merge-0.0.1/05_check_nationalities.sql create mode 100644 upgrade/merge-0.0.1/06_merge_nationalities.sql create mode 100644 upgrade/newdirectory-0.0.1/08_nationalities.sql diff --git a/upgrade/merge-0.0.1/04_nationalities.sql b/upgrade/merge-0.0.1/04_nationalities.sql new file mode 100644 index 0000000..abacfb6 --- /dev/null +++ b/upgrade/merge-0.0.1/04_nationalities.sql @@ -0,0 +1,28 @@ +CREATE TEMPORARY TABLE IF NOT EXISTS tmp_update_fusionax_anciens ( + good CHAR(4) DEFAULT NULL, + bad CHAR(4) DEFAULT NULL, + PRIMARY KEY(bad), + UNIQUE KEY(good) +) CHARSET=utf8; + +INSERT IGNORE INTO tmp_update_fusionax_anciens (bad, good) + VALUES ('TC', 'CAM'), + ('SH', 'CN'), + ('R', 'RO'), + ('TW', 'RC'), + ('TG', 'RT'), + ('U', 'ROU'), + ('KP', 'ROK'), + ('CRO', 'HR'), + ('UKR', 'UA'), + ('AM', 'ARM'), + ('CS', 'CZ'), + ('SU', 'RUS'), + ('LET', 'LV'), + ('MDA', 'MD'); + + UPDATE fusionax_anciens AS f +INNER JOIN tmp_update_fusionax_anciens AS t ON (f.Code_nationalite = t.bad) + SET f.Code_nationalite = t.good; + +-- vim:set syntax=mysql: diff --git a/upgrade/merge-0.0.1/05_check_nationalities.sql b/upgrade/merge-0.0.1/05_check_nationalities.sql new file mode 100644 index 0000000..f575425 --- /dev/null +++ b/upgrade/merge-0.0.1/05_check_nationalities.sql @@ -0,0 +1,10 @@ +-- Query to check if all the nationalities are now rocognized +-- If the result of this query is not empty, 04_nationalities.sql nedds to be updated +SELECT DISTINCT Code_nationalite + FROM fusionax_anciens AS f + WHERE NOT EXISTS (SELECT * + FROM geoloc_pays AS g + WHERE g.license_plate = f.Code_nationalite); + + +-- vim:set syntax=mysql: diff --git a/upgrade/merge-0.0.1/06_merge_nationalities.sql b/upgrade/merge-0.0.1/06_merge_nationalities.sql new file mode 100644 index 0000000..52f7383 --- /dev/null +++ b/upgrade/merge-0.0.1/06_merge_nationalities.sql @@ -0,0 +1,23 @@ +ALTER TABLE geoloc_pays ADD INDEX (license_plate); + + UPDATE auth_user_md5 AS u +LEFT JOIN fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien) +LEFT JOIN geoloc_pays AS g ON (g.license_plate = f.Code_nationalite) + SET u.nationalite = g.a2 + WHERE u.nationalite IS NULL; + + UPDATE auth_user_md5 AS u +LEFT JOIN fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien) +LEFT JOIN geoloc_pays AS g ON (g.license_plate = f.Code_nationalite) + SET u.nationalite2 = g.a2 + WHERE u.nationalite != g.a2 AND u.nationalite2 IS NULL; + + UPDATE auth_user_md5 AS u +LEFT JOIN fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien) +LEFT JOIN geoloc_pays AS g ON (g.license_plate = f.Code_nationalite) + SET u.nationalite3 = g.a2 + WHERE u.nationalite != g.a2 AND u.nationalite2 != g.a2 AND u.nationalite3 IS NULL; + +ALTER TABLE geoloc_pays DROP INDEX (license_plate); + +-- vim:set syntax=mysql: diff --git a/upgrade/newdirectory-0.0.1/08_nationalities.sql b/upgrade/newdirectory-0.0.1/08_nationalities.sql new file mode 100644 index 0000000..247c102 --- /dev/null +++ b/upgrade/newdirectory-0.0.1/08_nationalities.sql @@ -0,0 +1,219 @@ +ALTER TABLE geoloc_pays ADD COLUMN license_plate CHAR(4) DEFAULT NULL; + +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) +) 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: -- 2.1.4