#!/usr/bin/perl
#/***************************************************************************
# *   Copyright (C) 2004 by Michael Moritz                                  *
# *   mimo@restoel.net                                                      *
# *                                                                         *
# *   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; either version 2 of the License, or     *
# *   (at your option) any later version.                                   *
# *                                                                         *
# *   This program is distributed in the hope that it will be useful,       *
# *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
# *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
# *   GNU General Public License for more details.                          *
# *                                                                         *
# *   You should have received a copy of the GNU General Public License     *
# *   along with this program; if not, write to the                         *
# *   Free Software Foundation, Inc.,                                       *
# *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
# ***************************************************************************/
#
# gps-db-update.pl Database update script for gps
#
# update db for format used in gps version 0.92+
# and not OLDWEAK mode (\see db.cpp \see defs.h)
#
use Fcntl;
use DBI;
use Sys::Syslog qw(:DEFAULT setlogsock);
#
# Usage: gps-db-update.pl <config file>
#
sub usage {
	print "Usage: ".$0." configfile\n";
}

sub read_config {
	my $fn = shift;
	my $User_Preferences;
	open(CONFIG, $fn) or die "can't open $fn: $!";	
	while (<CONFIG>) {
	    chomp;                  # no newline
	    s/#.*//;                # no comments
	    s/^\s+//;               # no leading white
	    s/\s+$//;               # no trailing white
	    next unless length;     # anything left?
	    my ($var, $value) = split(/\s*=\s*/, $_, 2);
	    $User_Preferences->{$var} = $value;
	}
	close(CONFIG);
	return $User_Preferences;
} 

while ($option = shift(@ARGV)) {
	$configfile = $option;
}
if($configfile eq "") {
	&usage();
	exit 1;
}
print STDOUT "config file=".$configfile."\n";
my $hash_ref = &read_config($configfile);
#print "the keys... ", sort keys %$hash_ref, "...\n";

if($verbose) {
    while ( my ($key, $value) = each(%$hash_ref) ) {
        print "$key => $value\n";
    }
}
my $dbh = DBI->connect('DBI:'.$hash_ref->{dbtype}.':'.$hash_ref->{db_dbname},
			$hash_ref->{db_username},
			$hash_ref->{db_password} )
                or die "Couldn't connect to database: " . DBI->errstr;

print STDOUT "Connected to DB\n";
# begin
$sql = "CREATE TABLE NewTrip (
	sender VARCHAR(200) NOT NULL,
	recipient VARCHAR(200) NOT NULL,
	ip64 NUMERIC(4,0) DEFAULT 0 NOT NULL,
	ip32 NUMERIC(4,0) DEFAULT 0 NOT NULL,
	ip16 NUMERIC(4,0) DEFAULT 0 NOT NULL,
	ip8 NUMERIC(4,0) DEFAULT 0 NOT NULL,
	count INTEGER NOT NULL DEFAULT '0',
	uts INTEGER NOT NULL,
	PRIMARY KEY (sender,recipient,ip64,ip32,ip16,ip8)
	)";
print STDOUT $sql."\n";
$dbh->do($sql)	or die "SQL: " . $dbh->errstr ."\n";

$sql = "CREATE INDEX sender_recipient_index ON NewTrip(sender(15),recipient(15))";	
print STDOUT $sql."\n";
$dbh->do($sql)	or die "SQL: " . $dbh->errstr ."\n";

$sql = "CREATE INDEX client_ip_index on NewTrip ( ip64,ip32,ip16,ip8 )";
print STDOUT $sql."\n";
$dbh->do($sql)	or die "SQL: " . $dbh->errstr ."\n";

#main
my $sth = $dbh->prepare('SELECT * FROM Triplets ')	or die "SQL: " . $dbh->errstr ."\n";
$sth->execute() or die "SQL: " . $dbh->errstr ."\n";

my $count = 0;
while( my $hash_ref = $sth->fetchrow_hashref() ) {
	my ($ip64,$ip32,$ip16,$ip8) = split(/\./,$hash_ref->{client_address});
	$sql = "INSERT INTO NewTrip VALUES ( "
		.$dbh->quote($hash_ref->{sender}).", "
		.$dbh->quote($hash_ref->{recipient}).", "
		."$ip64, $ip32, $ip16, $ip8, "
		.$hash_ref->{count}.", "
		.$hash_ref->{uts}." )"
		;
	print STDOUT $sql."\n";
	$dbh->do($sql)	or die "SQL: " . $dbh->errstr ."\n";
	$count++;
}	
$sth->finish;

$sql = "RENAME TABLE Triplets TO Triplets.bck";
print STDOUT $sql."\n";
$dbh->do($sql)	or die "SQL: " . $dbh->errstr ."\n";

$sql = "RENAME TABLE NewTrip TO Triplets";
print STDOUT $sql."\n";
$dbh->do($sql)	or die "SQL: " . $dbh->errstr ."\n";

print STDOUT "Updated $count records\n";
#$sql = "DROP TABLE NewTrip";
#print STDOUT $sql."\n";
#$dbh->do($sql)
#	or die "SQL: " . $dbh->errstr ."\n";
#$dbh->disconnect;
print STDOUT "Disconnected from DB\n";
exit 0;