From 9f7b3c1a46383496e6a30e0aad8d4252e1a94fda Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Wed, 30 Mar 2005 13:49:52 +0000 Subject: [PATCH] Add a script (and output SQL) for getting the country list from Wikipedia. --- parse/parse-wiki-countrylist.pl | 34 ++++++ sql/basic-data.sql | 2 +- sql/countrylist.sql | 204 ++++++++++++++++++++++++++++++++ 3 files changed, 239 insertions(+), 1 deletion(-) create mode 100644 parse/parse-wiki-countrylist.pl create mode 100644 sql/countrylist.sql diff --git a/parse/parse-wiki-countrylist.pl b/parse/parse-wiki-countrylist.pl new file mode 100644 index 0000000..fc0437b --- /dev/null +++ b/parse/parse-wiki-countrylist.pl @@ -0,0 +1,34 @@ +#! /usr/bin/perl +use strict; +use warnings; + +# Parses country list from +# http://en.wikipedia.org/w/index.php?title=List_of_IOC_country_codes&action=edit + +print "begin;\n"; + +while (<>) { + m/ + ^ \* \s* + ( [A-Z]{3} ) # country code + \s* - \s* + \[\[ + ( ?: .*? \| ) ? # optional article lookup + ( .*? ) # country name + \]\] + /x or next; + + my ($countrycode, $countryname) = ($1, $2); + + # fix some wikisyntax ickyness :-) + $countryname =~ s/\]\]//g; + $countryname =~ s/\[\[//g; + + # minimal SQL escaping + $countryname =~ s/'/\\'/g; + + printf "INSERT INTO countries (countryname,countrycode) VALUES ('%s','%s');\n", + $countryname, $countrycode; +} + +printf "commit;\n"; diff --git a/sql/basic-data.sql b/sql/basic-data.sql index c66825c..532d2eb 100644 --- a/sql/basic-data.sql +++ b/sql/basic-data.sql @@ -2,7 +2,7 @@ INSERT INTO machines (machinename) VALUES ('Dancing Stage EuroMix 2'); INSERT INTO machines (machinename) VALUES ('DDR Extreme'); INSERT INTO scoringsystems (scoringsystemname) VALUES ('10K Machine score'); -INSERT INTO countries (countryname) VALUES ('Europe', 'EUR'); -- special exception +INSERT INTO countries (countryname, countrycode) VALUES ('Europe', 'EUR'); -- special exception INSERT INTO seasons (seasonname) VALUES ('European Cup'); INSERT INTO seasons (seasonname) VALUES ('Challenge Cup 2003'); INSERT INTO seasons (seasonname) VALUES ('Challenge Cup 2004'); diff --git a/sql/countrylist.sql b/sql/countrylist.sql new file mode 100644 index 0000000..81c6a43 --- /dev/null +++ b/sql/countrylist.sql @@ -0,0 +1,204 @@ +begin; +INSERT INTO countries (countryname,countrycode) VALUES ('Afghanistan','AFG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Netherlands Antilles','AHO'); +INSERT INTO countries (countryname,countrycode) VALUES ('Albania','ALB'); +INSERT INTO countries (countryname,countrycode) VALUES ('Algeria','ALG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Andorra','AND'); +INSERT INTO countries (countryname,countrycode) VALUES ('Angola','ANG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Antigua and Barbuda','ANT'); +INSERT INTO countries (countryname,countrycode) VALUES ('American Samoa','ASA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Argentina','ARG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Armenia','ARM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Aruba','ARU'); +INSERT INTO countries (countryname,countrycode) VALUES ('Australia','AUS'); +INSERT INTO countries (countryname,countrycode) VALUES ('Austria','AUT'); +INSERT INTO countries (countryname,countrycode) VALUES ('Azerbaijan','AZE'); +INSERT INTO countries (countryname,countrycode) VALUES ('The Bahamas','BAH'); +INSERT INTO countries (countryname,countrycode) VALUES ('Bangladesh','BAN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Barbados','BAR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Burundi','BDI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Belgium','BEL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Benin','BEN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Bermuda','BER'); +INSERT INTO countries (countryname,countrycode) VALUES ('Bhutan','BHU'); +INSERT INTO countries (countryname,countrycode) VALUES ('Bosnia Herzegovina','BIH'); +INSERT INTO countries (countryname,countrycode) VALUES ('Belize','BIZ'); +INSERT INTO countries (countryname,countrycode) VALUES ('Belarus','BLR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Bolivia','BOL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Botswana','BOT'); +INSERT INTO countries (countryname,countrycode) VALUES ('Brazil','BRA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Bahrain','BRN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Brunei Darussalam','BRU'); +INSERT INTO countries (countryname,countrycode) VALUES ('Bulgaria','BUL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Burkina Faso','BUR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Central African Republic','CAF'); +INSERT INTO countries (countryname,countrycode) VALUES ('Cambodia','CAM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Canada','CAN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Cayman Islands','CAY'); +INSERT INTO countries (countryname,countrycode) VALUES ('Republic of the Congo','CGO'); +INSERT INTO countries (countryname,countrycode) VALUES ('Chad','CHA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Chile','CHI'); +INSERT INTO countries (countryname,countrycode) VALUES ('People\'s Republic of China','CHN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Côte d\'Ivoire','CIV'); +INSERT INTO countries (countryname,countrycode) VALUES ('Cameroon','CMR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Democratic Republic of the Congo','COD'); +INSERT INTO countries (countryname,countrycode) VALUES ('Cook Islands','COK'); +INSERT INTO countries (countryname,countrycode) VALUES ('Colombia','COL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Comoros','COM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Cape Verde','CPV'); +INSERT INTO countries (countryname,countrycode) VALUES ('Costa Rica','CRC'); +INSERT INTO countries (countryname,countrycode) VALUES ('Croatia','CRO'); +INSERT INTO countries (countryname,countrycode) VALUES ('Cuba','CUB'); +INSERT INTO countries (countryname,countrycode) VALUES ('Cyprus','CYP'); +INSERT INTO countries (countryname,countrycode) VALUES ('Czech Republic','CZE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Denmark','DEN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Djibouti','DJI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Dominica','DMA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Dominican Republic','DOM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Ecuador','ECU'); +INSERT INTO countries (countryname,countrycode) VALUES ('Egypt','EGY'); +INSERT INTO countries (countryname,countrycode) VALUES ('Eritrea','ERI'); +INSERT INTO countries (countryname,countrycode) VALUES ('El Salvador','ESA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Spain','ESP'); +INSERT INTO countries (countryname,countrycode) VALUES ('Estonia','EST'); +INSERT INTO countries (countryname,countrycode) VALUES ('Ethiopia','ETH'); +INSERT INTO countries (countryname,countrycode) VALUES ('Fiji','FIJ'); +INSERT INTO countries (countryname,countrycode) VALUES ('Finland','FIN'); +INSERT INTO countries (countryname,countrycode) VALUES ('France','FRA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Federated States of Micronesia','FSM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Gabon','GAB'); +INSERT INTO countries (countryname,countrycode) VALUES ('The Gambia','GAM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Great Britain','GBR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Guinea-Bissau','GBS'); +INSERT INTO countries (countryname,countrycode) VALUES ('Georgia','GEO'); +INSERT INTO countries (countryname,countrycode) VALUES ('Equatorial Guinea','GEQ'); +INSERT INTO countries (countryname,countrycode) VALUES ('Germany','GER'); +INSERT INTO countries (countryname,countrycode) VALUES ('Ghana','GHA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Greece','GRE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Grenada','GRN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Guatemala','GUA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Guinea','GUI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Guam','GUM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Guyana','GUY'); +INSERT INTO countries (countryname,countrycode) VALUES ('Haiti','HAI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Hong Kong, China','HKG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Honduras','HON'); +INSERT INTO countries (countryname,countrycode) VALUES ('Hungary','HUN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Indonesia','INA'); +INSERT INTO countries (countryname,countrycode) VALUES ('India','IND'); +INSERT INTO countries (countryname,countrycode) VALUES ('Islamic Republic of Iran','IRI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Ireland','IRL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Iraq','IRQ'); +INSERT INTO countries (countryname,countrycode) VALUES ('Iceland','ISL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Israel','ISR'); +INSERT INTO countries (countryname,countrycode) VALUES ('U.S. Virgin Islands','ISV'); +INSERT INTO countries (countryname,countrycode) VALUES ('Italy','ITA'); +INSERT INTO countries (countryname,countrycode) VALUES ('British Virgin Islands','IVB'); +INSERT INTO countries (countryname,countrycode) VALUES ('Jamaica','JAM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Jordan','JOR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Japan','JPN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Kazakhstan','KAZ'); +INSERT INTO countries (countryname,countrycode) VALUES ('Kenya','KEN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Kiribati','KIR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Kyrgyzstan','KGZ'); +INSERT INTO countries (countryname,countrycode) VALUES ('South Korea','KOR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Saudi Arabia','KSA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Kuwait','KUW'); +INSERT INTO countries (countryname,countrycode) VALUES ('Laos','LAO'); +INSERT INTO countries (countryname,countrycode) VALUES ('Latvia','LAT'); +INSERT INTO countries (countryname,countrycode) VALUES ('Libya','LBA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Liberia','LBR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Saint Lucia','LCA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Lesotho','LES'); +INSERT INTO countries (countryname,countrycode) VALUES ('Lebanon','LIB'); +INSERT INTO countries (countryname,countrycode) VALUES ('Liechtenstein','LIE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Lithuania','LTU'); +INSERT INTO countries (countryname,countrycode) VALUES ('Luxembourg','LUX'); +INSERT INTO countries (countryname,countrycode) VALUES ('Madagascar','MAD'); +INSERT INTO countries (countryname,countrycode) VALUES ('Morocco','MAR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Malaysia','MAS'); +INSERT INTO countries (countryname,countrycode) VALUES ('Malawi','MAW'); +INSERT INTO countries (countryname,countrycode) VALUES ('Moldova','MDA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Maldives','MDV'); +INSERT INTO countries (countryname,countrycode) VALUES ('Mexico','MEX'); +INSERT INTO countries (countryname,countrycode) VALUES ('Mongolia','MGL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Former Yugoslav Republic of Macedonia','MKD'); +INSERT INTO countries (countryname,countrycode) VALUES ('Mali','MLI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Malta','MLT'); +INSERT INTO countries (countryname,countrycode) VALUES ('Monaco','MON'); +INSERT INTO countries (countryname,countrycode) VALUES ('Mozambique','MOZ'); +INSERT INTO countries (countryname,countrycode) VALUES ('Mauritius','MRI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Mauritania','MTN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Myanmar','MYA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Namibia','NAM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Nicaragua','NCA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Netherlands','NED'); +INSERT INTO countries (countryname,countrycode) VALUES ('Nepal','NEP'); +INSERT INTO countries (countryname,countrycode) VALUES ('Nigeria','NGR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Niger','NIG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Norway','NOR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Nauru','NRU'); +INSERT INTO countries (countryname,countrycode) VALUES ('New Zealand','NZL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Oman','OMA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Pakistan','PAK'); +INSERT INTO countries (countryname,countrycode) VALUES ('Panama','PAN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Paraguay','PAR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Peru','PER'); +INSERT INTO countries (countryname,countrycode) VALUES ('Philippines','PHI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Palestine','PLE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Palau','PLW'); +INSERT INTO countries (countryname,countrycode) VALUES ('Papua New Guinea','PNG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Poland','POL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Portugal','POR'); +INSERT INTO countries (countryname,countrycode) VALUES ('North Korea','PRK'); +INSERT INTO countries (countryname,countrycode) VALUES ('Puerto Rico','PUR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Qatar','QAT'); +INSERT INTO countries (countryname,countrycode) VALUES ('Romania','ROM'); +INSERT INTO countries (countryname,countrycode) VALUES ('South Africa','RSA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Russia','RUS'); +INSERT INTO countries (countryname,countrycode) VALUES ('Rwanda','RWA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Samoa','SAM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Serbia and Montenegro','SCG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Senegal','SEN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Seychelles','SEY'); +INSERT INTO countries (countryname,countrycode) VALUES ('Singapore','SIN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Saint Kitts and Nevis','SKN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Sierra Leone','SLE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Slovenia','SLO'); +INSERT INTO countries (countryname,countrycode) VALUES ('San Marino','SMR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Solomon Islands','SOL'); +INSERT INTO countries (countryname,countrycode) VALUES ('Somalia','SOM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Sri Lanka','SRI'); +INSERT INTO countries (countryname,countrycode) VALUES ('São Tomé and Príncipe','STP'); +INSERT INTO countries (countryname,countrycode) VALUES ('Sudan','SUD'); +INSERT INTO countries (countryname,countrycode) VALUES ('Switzerland','SUI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Suriname','SUR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Slovakia','SVK'); +INSERT INTO countries (countryname,countrycode) VALUES ('Sweden','SWE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Swaziland','SWZ'); +INSERT INTO countries (countryname,countrycode) VALUES ('Syria','SYR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Tanzania','TAN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Tonga','TGA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Thailand','THA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Tajikistan','TJK'); +INSERT INTO countries (countryname,countrycode) VALUES ('Turkmenistan','TKM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Timor-Leste','TLS'); +INSERT INTO countries (countryname,countrycode) VALUES ('Togo','TOG'); +INSERT INTO countries (countryname,countrycode) VALUES ('Chinese Taipei','TPE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Trinidad and Tobago','TRI'); +INSERT INTO countries (countryname,countrycode) VALUES ('Tunisia','TUN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Turkey','TUR'); +INSERT INTO countries (countryname,countrycode) VALUES ('United Arab Emirates','UAE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Uganda','UGA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Ukraine','UKR'); +INSERT INTO countries (countryname,countrycode) VALUES ('Uruguay','URU'); +INSERT INTO countries (countryname,countrycode) VALUES ('United States','USA'); +INSERT INTO countries (countryname,countrycode) VALUES ('Uzbekistan','UZB'); +INSERT INTO countries (countryname,countrycode) VALUES ('Vanuatu','VAN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Venezuela','VEN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Vietnam','VIE'); +INSERT INTO countries (countryname,countrycode) VALUES ('Saint Vincent and the Grenadines','VIN'); +INSERT INTO countries (countryname,countrycode) VALUES ('Yemen','YEM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Zambia','ZAM'); +INSERT INTO countries (countryname,countrycode) VALUES ('Zimbabwe','ZIM'); +commit; -- 2.39.2