Add a script (and output SQL) for getting the country list from Wikipedia.

diff --git a/parse/parse-wiki-countrylist.pl b/parse/parse-wiki-countrylist.pl
new file mode 100644 (file)
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 c66825c72f52f474a163b870e248953775a38c69..532d2ebb4ea6091c3bf56d66846ed5372bdaaf42 100644 (file)
--- 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 (file)
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;