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 080cf666ce0cbc3dc99032bbc4a1d9452af24bd1..1c581b5fdc12d48a1430d608fda77529077aa8e6 100644 (file)
--- 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 7ffb758bb8769a2c36540e0f2737e21b3916039c..2f1e76b8aef6ec3bf98470e40e14f2c69eb99694 100755 (executable)
--- 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;
 
 
+