#!./perl
#
# iso_import -- Import ISO standard information - countries
#
#	Written by Clint Goss <clint@goss.com>, November 1998
#

BEGIN {
	# Access Perl modules

	#push (@INC, ...your lib directory here...);

	# Point to our preferred installation of Oracle

	#$ENV{'ORACLE_HOME'} = ...your Oracle Home here...);
}

# Perl Modules

use OraConnection;	# Connection to Oracle, incl DBI and DBD::Oracle
use FileHandle;
use strict;		# Restrict unsafe variables, refs, barewords

# Set up the connection to Oracle

my ($database, $username, $password) = qw (ora9 demo demo);
my ($dbc) = new OraConnection;
$dbc->connect ($database, $username, $password);

# Statistics

my ($countCntIns) = 0;

&importRef ("iso_3166.txt");

print "Inserted $countCntIns into Countries table.\n";

exit;

# Import one ISO Country file containing Reference table information

sub importRef {
	my ($fileName) = @_;

	open (ISO_LOG, "<" . $fileName) || die "Can't open " . $fileName;

	# Flag if we have found the "---------------" line which marks the
	# start of the country data.

	my ($foundHeader);

	my ($line);
	while ($line = <ISO_LOG>) {

		# "Just tidy us up a bit"

		chomp $line;
		$line =~ s/^\s*//;
		$line =~ s/\s*$//;

		# Check if this is the header line

		if (! $foundHeader) {
			if ($line =~ /^----------/) { $foundHeader = 1; }
			next;
		}

		# Here if we've already found header
		# Check if we're at the end of the data

		if ($line =~ /^----------/) { last; }
		if ($line eq "") { next; }

		# Here if we got a real line of country data.

# The format is (for example):
#          1         2         3         4         5         6         7
# 1234567890123456789012345678901234567890123456789012345678901234567890
# BRITISH INDIAN OCEAN TERRITORY                  IO      IOT     086

		my ($uName) = substr ($line, 0, 48);
		$uName =~ s/^\s*//;
		$uName =~ s/\s*$//;

		my ($remainder) = substr ($line, 48);

		$remainder =~ s/^\s*//;
		$remainder =~ s/\s*$//;

		my ($a2, $a3, $number) = split (/\s+/, $remainder, 3);

		# Convert the country name to upper lower case

		my ($ulName) = ucfirst lc $uName;

		# Convert the first character of the second name
		# of two-part and hyphenated names to upper case.

		$ulName =~ s/([ -\/])([a-z])/$1.uc($2)/ge;

		# Restore English-language articles back to lower case

		$ulName =~ s/ Of / of /g;
		$ulName =~ s/ Of\)/ of)/g;
		$ulName =~ s/ Of$/ of/;

		$ulName =~ s/ And / and /g;
		$ulName =~ s/ And$/ and/;

		$ulName =~ s/ The / the /g;
		$ulName =~ s/ The$/ the/;

		# Restore Word'S back to Word's

		$ulName =~ s/'S /'s /g;

		# Debugging:
		# print "Country $number $a2 $a3 $ulName\n";

		# Insert into COUNTRIES table

		my ($stmt) = "INSERT into COUNTRIES (" .
			"cnt_code_a2, " .
			"cnt_code_a3, " .
			"cnt_code_number, " .
			"cnt_name_upper_case, " .
			"cnt_name_mixed_case) " .
		"VALUES (" .
			$dbc->quote ($a2)	. ", " .
			$dbc->quote ($a3)	. ", " .
			$dbc->quote ($number)	. ", " .
			$dbc->quote ($uName)	. ", " .
			$dbc->quote ($ulName)	. ")";

		$dbc->exec ($stmt);
		$countCntIns++;
	}
}