From: minima <minima>
Date: Mon, 9 May 2005 16:37:29 +0000 (+0000)
Subject: enhance spot copier to use mysql and pg as well as sqlite
X-Git-Tag: R_1_52~72
X-Git-Url: http://www.dxcluster.org/gitweb/gitweb.cgi?p=spider.git;a=commitdiff_plain;h=2d1175b409d790f991091e8f2884d26591021b9a

enhance spot copier to use mysql and pg as well as sqlite
fix pagelth issue
---

diff --git a/perl/DXCommandmode.pm b/perl/DXCommandmode.pm
index 080cf666..1c581b5f 100644
--- a/perl/DXCommandmode.pm
+++ b/perl/DXCommandmode.pm
@@ -41,7 +41,8 @@ use Thingy::Hello;
 use Thingy::Bye;
 
 use strict;
-use vars qw(%Cache %cmd_cache $errstr %aliases $scriptbase $maxerrors %nothereslug $maxbadcount $msgpolltime);
+use vars qw(%Cache %cmd_cache $errstr %aliases $scriptbase $maxerrors %nothereslug
+			$maxbadcount $msgpolltime $default_pagelth);
 
 %Cache = ();					# cache of dynamically loaded routine's mod times
 %cmd_cache = ();				# cache of short names
@@ -51,6 +52,8 @@ $scriptbase = "$main::root/scripts"; # the place where all users start scripts g
 $maxerrors = 20;				# the maximum number of concurrent errors allowed before disconnection
 $maxbadcount = 3;				# no of bad words allowed before disconnection
 $msgpolltime = 3600;			# the time between polls for new messages 
+$default_pagelth = 20;			# the default page length 0 = unlimited
+
 
 
 use vars qw($VERSION $BRANCH);
@@ -96,7 +99,9 @@ sub start
 	$self->state('prompt');		# a bit of room for further expansion, passwords etc
 	$self->{priv} = $user->priv || 0;
 	$self->{lang} = $user->lang || $main::lang || 'en';
-	$self->{pagelth} = $user->pagelth || 20;
+	my $pagelth = $user->pagelth;
+	$pagelth = $default_pagelth unless defined $pagelth;
+	$self->{pagelth} = $pagelth;
 	($self->{width}) = $line =~ /width=(\d+)/; $line =~ s/\s*width=\d+\s*//;
 	$self->{width} = 80 unless $self->{width} && $self->{width} > 80;
 	$self->{consort} = $line;	# save the connection type
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;
 
 
+