use strict;
use warnings;
use DBI;

# Konfiguration lesen
open(CFG,'<', 'connect-db') or die "Oops: $!\n";
chomp(my $username = <CFG>);
chomp(my $password = <CFG>);
chomp(my $database = <CFG>);
close(CFG);

# Zusammenbauen der Datasource: DBI/Modul/Datenbank/Host
my $dsn = 'DBI:mysql:'.$database.':localhost';

# Nun der Connect
my $dbh = DBI->connect($dsn, $username, $password,
		                   { RaiseError => 1,
		                     PrintError => 1,
		                     AutoCommit => 1}
		                  ) or die "Oops: $DBI::errstr\n";

# Alte Tabelle loeschen, sofern vorhanden
$dbh->do("DROP TABLE IF EXISTS IPtoC;");

# Tabelle neu anlegen
$dbh->do('CREATE TABLE IPtoC (
      ID INTEGER AUTO_INCREMENT,
      IPfrom INTEGER UNSIGNED,
      IPto   INTEGER UNSIGNED,
      CC2    CHAR(2),
      CC3    CHAR(3),
      Name   VARCHAR(60),
      PRIMARY KEY (ID));'
      ) or die $dbh->errstr();

# Daten einlesen
my ($IPfrom, $IPto, $CC2, $CC3, $Name);
my @propeller = ('|','/','-','\\');    # fuer Fortschrittsanzeige
my $pp = 0;                            # (drehender Propeller)
$| = 1;                                # dafuer auch ungepufferte Ausgabe

my $sth = $dbh->prepare("INSERT INTO IPtoC (IPfrom,IPto,CC2,CC3,Name)
                        VALUES (?,?,?,?,?);") or die $dbh->errstr();
open (DAT,'<','ip-to-country.csv');
while (<DAT>)
  {
  # Format der Eingabe z.B.:
  # "201859072","201859087","VI","VIR","VIRGIN ISLANDS, U.S."
  # Achtung: split nur mit Komma ginge schief
  ($IPfrom, $IPto, $CC2, $CC3, $Name) = split('","',$_);
  $IPfrom =~ s/^"//;        # vorderes Gaensefuesschen weg
  chomp($Name);             # Newline weg
  $Name =~ s/"$//;          # hinteres Gaensefuesschen weg
  $sth->execute($IPfrom, $IPto, $CC2, $CC3, $Name)
        or die $dbh->errstr();
  print $propeller[$pp++%4],"\b";   # Simpel-Fortschrittsanzeige
  }

close(DAT);


$dbh->disconnect();