From 2d1175b409d790f991091e8f2884d26591021b9a Mon Sep 17 00:00:00 2001 From: minima Date: Mon, 9 May 2005 16:37:29 +0000 Subject: [PATCH 1/1] enhance spot copier to use mysql and pg as well as sqlite fix pagelth issue --- perl/DXCommandmode.pm | 9 ++++- perl/spot2sql.pl | 93 +++++++++++++++++++++++++++++++++++++++---- 2 files changed, 92 insertions(+), 10 deletions(-) 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; + -- 2.43.0