#!/usr/bin/perl -w # # Screenscrapes the ISO 3166 country code list off the web and makes a MySQL SQL # insert file. Will create insert file only if content on web differs from SQL # file, and so could be run periodically from cron if required. # # Copyright (C) 2003 Wm. Rhodes # # This program is free software; you can redistribute it and/or modify it under # the terms of the GNU General Public License as published by the Free Software # Foundation at: . # use strict; use File::Basename; use Cwd; use LWP::Simple; use Geography::Countries; # The ISO country list from the web my $isourl = "http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1-semic.txt"; # The location and name of current local ISO SQL file my $isolist = cwd . "/iso_country_list.sql"; if ( !-e $isolist || (stat($isolist))[9] < (head($isourl))[1] ) { print "File $isolist requires updating.\n"; my $content = get $isourl || die "Couldn't get $isourl: $!\n"; open(ISOLIST, ">$isolist") || die "Couldn't open $isolist: $!\n"; print ISOLIST "# ". basename($isolist) ."\n". "#\n". "# This will create and then populate a MySQL table with a list of the names and\n". "# ISO 3166 codes for countries in existence as of the date below.\n". "#\n". "# Usage:\n". "# mysql -u username -ppassword database_name < ./" . basename($isolist) ."\n". "#\n". "# For updates to this file, see http://27.org/isocountrylist/\n". "# For more about ISO 3166, see http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html\n". "#\n". "# Created by ". basename($0) ." on ". scalar(localtime) .".\n". "# Wm. Rhodes \n". "#\n\n". "CREATE TABLE IF NOT EXISTS country (\n". " iso CHAR(2) NOT NULL PRIMARY KEY,\n". " name VARCHAR(80) NOT NULL,\n". " printable_name VARCHAR(80) NOT NULL,\n". " iso3 CHAR(3),\n". " numcode SMALLINT,\n". ");\n\n"; foreach (split(/\n/, $content)) { if (/^[A-Z]{2,}/) { my ($name, $code2) = split(/;/, $_); my $printable_name = $name; $printable_name =~ s/(\w+)/\u\L$1/g; $printable_name =~ s/\b(And|Of|The|S)\b/\l$1/g; $printable_name =~ s/'/\\'/; chop($code2); $name =~ s/'/\\'/g; my ($code3, $numcode) = (country($code2))[1,2]; # ('PM', 'SPM', 666, 'Saint Pierre and Miquelon', 1) $code3 = defined($code3) ? "'$code3'" : "NULL"; $numcode = defined($numcode) ? "'$numcode'" : "NULL"; print ISOLIST "INSERT INTO country VALUES ('$code2','$name','$printable_name',$code3,$numcode);\n"; } } close(ISOLIST); my $size = (stat($isolist))[7]; print "Wrote $size bytes to $isolist at ". scalar(localtime) ."\n"; } else { print "The current ISO SQL file at $isolist is up to date.\n"; }