X-Git-Url: http://dxcluster.net/gitweb/gitweb.cgi?a=blobdiff_plain;f=perl%2Fspot2sql.pl;h=2f1e76b8aef6ec3bf98470e40e14f2c69eb99694;hb=2d1175b409d790f991091e8f2884d26591021b9a;hp=7ffb758bb8769a2c36540e0f2737e21b3916039c;hpb=be05d6ea96d1b9bd023900eab7ec5d119d99853a;p=spider.git

diff --git a/perl/spot2sql.pl b/perl/spot2sql.pl
index 7ffb758b..2f1e76b8 100755
--- a/perl/spot2sql.pl
+++ b/perl/spot2sql.pl
@@ -1,3 +1,4 @@
+#!/usr/bin/perl
 #
 # $Id$
 #
@@ -11,28 +12,67 @@ BEGIN {
 	sub mkver {};
 	
 	# root of directory tree for this system
-	$root = "/spider";
-	$root = $ENV{'DXSPIDER_ROOT'} if $ENV{'DXSPIDER_ROOT'};
+	$main::root = "/spider";
+	$main::root = $ENV{'DXSPIDER_ROOT'} if $ENV{'DXSPIDER_ROOT'};
 	
 	unshift @INC, "$root/perl";     # this IS the right way round!
 	unshift @INC, "$root/local";
 }
 
+use strict;
+
 use DXUtil;
 use Spot;
 use DBI;
-use DBD::SQLite;
+
+our $root;
 
 Spot::init();
 
-my $dbh = DBI->connect("dbi:SQLite:dbname=$root/data/spider.sdb","","")
-	or die "cannot open $root/data/spider.sdb";
+my $dbh;
+my $sort = lc shift || 'sqlite';
+
+if ($sort eq 'sqlite') {
+	unlink "$root/data/spider.db";
+	$dbh = DBI->connect("dbi:SQLite:dbname=$root/data/spider.db","","")
+		or die "cannot open $root/data/spider.db";
+	$dbh->do("PRAGMA default_synchronous = OFF");
+} elsif ($sort eq 'mysql') {
+	$dbh = DBI->connect("dbi:mysql:dbname=spider","spider","spider")
+		or die $DBI::errstr;
+} elsif ($sort eq 'pg') {
+	$dbh = DBI->connect("dbi:Pg:dbname=spider","postgres","")
+		or die $DBI::errstr;
+} else {
+	die "invalid database type: $sort";
+}
+
+$dbh->{PrintError} = 0;
+$dbh->{PrintWarn} = 0;
 
 opendir DIR, "$root/data/spots" or die "No spot directory $!\n";
 my @years = grep {/^\d/} readdir DIR;
 closedir DIR;
 
-$dbh->do("delete from spots");
+my $start = time;
+
+eval { $dbh->do("drop table spots");};
+
+$dbh->do("CREATE TABLE spots (freq real,
+spotted varchar(255),
+t int,
+comment varchar(255),
+spotter varchar(255),
+spotted_dxcc int,
+spotter_dxcc int,
+origin varchar(255),
+spotted_itu int,
+spotted_cq int,
+spotter_itu int,
+spotter_cq int,
+spotted_state varchar(2),
+spotter_state varchar(2)
+)");
 
 my $sth = $dbh->prepare("insert into spots values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)") or die "prepare\n";
 
@@ -51,9 +91,28 @@ foreach my $year (@years) {
 		if ($fh) {
 			$dbh->begin_work;
 			while (<$fh>) {
+				if ($sort eq 'pg' && $count && $count % 100 == 0) {
+					$dbh->commit;
+					$dbh->begin_work;
+				}
 				my @s = split /\^/;
-				push @s, undef while @s < 14;
-				$sth->execute(@s);
+				if ($sort eq 'pg') {
+					push @s, '' while @s < 14;
+					$s[5]+=0;
+					$s[6]+=0;
+					$s[8]+=0;
+					$s[9]+=0;
+					$s[10]+=0;
+					$s[11]+=0;
+				} else {
+					push @s, undef while @s < 14;
+				}
+				eval { $sth->execute(@s) };
+				if ($@) {
+					print DBI::neat_list(@s);
+					$dbh->rollback;
+					$dbh->begin_work;
+				}
 				$count++;
 			}
 			$dbh->commit;
@@ -63,6 +122,24 @@ foreach my $year (@years) {
 }
 print "\n";
 $sth->finish;
+
+my $secs = time - $start;
+print "Load took $secs\n";
+$secs = time;
+
+$dbh->do("CREATE INDEX spotted_idx on spots(spotted)");
+
+my $secs = time - $start;
+print "Spotted index took $secs\n";
+$secs = time;
+
+$dbh->do("CREATE INDEX t_idx on spots(t)");
+
+my $secs = time - $start;
+print "T index took $secs\n";
+$secs = time;
+
 $dbh->disconnect;
 
 
+