From 917f7586cf60e96e07233c72b9854d754638f253 Mon Sep 17 00:00:00 2001 From: minima Date: Tue, 27 Dec 2005 21:14:31 +0000 Subject: [PATCH] more QSL changes --- Changes | 4 ++ cmd/show/dx.pl | 14 ++++-- perl/DXSql.pm | 113 ++++++++++++++++++++++++++++++++++++++++++- perl/DXSql/SQLite.pm | 47 ++++++++++++++++++ perl/DXSql/mysql.pm | 68 ++++++++++++++++++++++++++ perl/Spot.pm | 62 +++++++++++++++++++++++- perl/cluster.pl | 4 ++ 7 files changed, 307 insertions(+), 5 deletions(-) create mode 100644 perl/DXSql/mysql.pm diff --git a/Changes b/Changes index 4e14f087..158ab7ab 100644 --- a/Changes +++ b/Changes @@ -1,3 +1,7 @@ +27Dec05======================================================================= +1. put some more flesh on the SQL stuff (which may turn out to be a bit of a +red herring as it doesn't appear to be significantly faster (for spots) than +the existing code). 21Dec05======================================================================= 1. Add start of optional SQL working. 2. Fix BadWords so that the simple word check splits words on word boundaries. diff --git a/cmd/show/dx.pl b/cmd/show/dx.pl index 1203cb3b..fd3adeb0 100644 --- a/cmd/show/dx.pl +++ b/cmd/show/dx.pl @@ -314,7 +314,11 @@ if ($state) { push @expr, "\$f12 eq '$_'"; push @hint, "m{$_}"; } - $expr .= @expr > 1 ? '($f12 && (' . join(' || ', @expr) . '))' : "(\$f12 && $expr[0])"; + if ($main::dbh) { + $expr .= @expr > 1 ? '(' . join(' || ', @expr) . ')' : "$expr[0]"; + } else { + $expr .= @expr > 1 ? '(\$f12 && (' . join(' || ', @expr) . '))' : "(\$f12 && $expr[0])"; + } $hint .= @hint > 1 ? '(' . join(' || ', @hint) . ')' : $hint[0]; } if ($bystate) { @@ -326,7 +330,11 @@ if ($bystate) { push @expr, "\$f13 eq '$_'"; push @hint, "m{$_}"; } - $expr .= @expr > 1 ? '($f13 && (' . join(' || ', @expr) . '))' : "(\$f13 && $expr[0])"; + if ($main::dbh) { + $expr .= @expr > 1 ? '(' . join(' || ', @expr) . ')' : "$expr[0]"; + } else { + $expr .= @expr > 1 ? '(\$f13 && (' . join(' || ', @expr) . '))' : "(\$f13 && $expr[0])"; + } $hint .= @hint > 1 ? '(' . join(' || ', @hint) . ')' : $hint[0]; } @@ -365,7 +373,7 @@ foreach $ref (@res) { push @out, VE7CC::dx_spot($self, @$ref); } else { if ($real) { - push @out, $self->format_dx_spot($ref); + push @out, $self->format_dx_spot(@$ref); } else { push @out, Spot::formatl(@$ref); } diff --git a/perl/DXSql.pm b/perl/DXSql.pm index a327e07e..fcf4e4ce 100644 --- a/perl/DXSql.pm +++ b/perl/DXSql.pm @@ -10,6 +10,8 @@ package DXSql; use strict; +use DXDebug; + use vars qw($VERSION $BRANCH); $VERSION = sprintf( "%d.%03d", q$Revision$ =~ /(\d+)\.(\d+)/ ); $BRANCH = sprintf( "%d.%03d", q$Revision$ =~ /\d+\.\d+\.(\d+)\.(\d+)/ || (0,0)); @@ -60,7 +62,7 @@ sub connect my $dbh; eval { no strict 'refs'; - $dbh = DBI->connect($dsn, $user, $passwd); + $dbh = DBI->connect($dsn, $user, $passwd, {AutoCommit => 0}); }; unless ($dbh) { $active = 0; @@ -75,5 +77,114 @@ sub finish my $self = shift; $self->{dbh}->disconnect; } + +sub do +{ + my $self = shift; + my $s = shift; + + eval { $self->{dbh}->do($s); }; +} + +sub commit +{ + $_[0]->{dbh}->commit; + $_[0]->{dbh}->{AutoCommit} = 0; +} + +sub rollback +{ + $_[0]->{dbh}->rollback; + $_[0]->{dbh}->{AutoCommit} = 0; +} + +sub quote +{ + return $_[0]->{dbh}->quote($_[1]); +} + +sub prepare +{ + return $_[0]->{dbh}->prepare($_[1]); +} + +sub spot_insert_prepare +{ + my $self = shift; + return $self->prepare('insert into spot values(?' . ',?' x 14 . ')'); +} + +sub spot_insert +{ + my $self = shift; + my $spot = shift; + my $sth = shift; + + if ($sth) { + eval {$sth->execute(undef, @$spot)}; + } else { + my $s = "insert into spot values(NULL,"; + $s .= sprintf("%.1f,", $spot->[0]); + $s .= $self->quote($spot->[1]) . "," ; + $s .= $spot->[2] . ','; + $s .= (length $spot->[3] ? $self->quote($spot->[3]) : 'NULL') . ','; + $s .= $self->quote($spot->[4]) . ','; + $s .= $spot->[5] . ','; + $s .= $spot->[6] . ','; + $s .= (length $spot->[7] ? $self->quote($spot->[7]) : 'NULL') . ','; + $s .= $spot->[8] . ','; + $s .= $spot->[9] . ','; + $s .= $spot->[10] . ','; + $s .= $spot->[11] . ','; + $s .= (length $spot->[12] ? $self->quote($spot->[12]) : 'NULL') . ','; + $s .= (length $spot->[13] ? $self->quote($spot->[13]) : 'NULL') . ')'; + eval {$self->do($s)}; + } +} + +sub spot_search +{ + my $self = shift; + my $expr = shift; + my $dayfrom = shift; + my $dayto = shift; + my $n = shift; + my $dxchan = shift; + + dbg("expr: $expr") if isdbg('search'); + if ($expr =~ /\$f/) { + $expr =~ s/(?:==|eq)/ = /g; + $expr =~ s/\$f10/spotteritu/g; + $expr =~ s/\$f11/spottercq/g; + $expr =~ s/\$f12/spotstate/g; + $expr =~ s/\$f13/spotterstate/g; + $expr =~ s/\$f0/freq/g; + $expr =~ s/\$f1/spotcall/g; + $expr =~ s/\$f2/time/g; + $expr =~ s/\$f3/comment/g; + $expr =~ s/\$f4/spotter/g; + $expr =~ s/\$f5/spotdxcc/g; + $expr =~ s/\$f6/spotterdxcc/g; + $expr =~ s/\$f7/origin/g; + $expr =~ s/\$f8/spotitu/g; + $expr =~ s/\$f9/spotcq/g; + $expr =~ s/\|\|/ or /g; + $expr =~ s/\&\&/ and /g; + $expr =~ s/=~\s+m\{\^([\w]+)[^\}]*\}/ like '$1%'/g; + } else { + $expr = ''; + } + my $fdays = $dayfrom ? "time <= " . ($main::systime - ($dayfrom * 86400)) : ""; + my $days = "time >= " . ($main::systime - ($dayto * 86400)); + my $trange = $fdays ? "($fdays and $days)" : $days; + $expr .= $expr ? " and $trange" : $trange; + my $s = qq{select freq,spotcall,time,comment,spotter,spotdxcc,spotterdxcc, +origin,spotitu,spotcq,spotteritu,spottercq,spotstate,spotterstate from spot +where $expr order by time desc limit $n}; + dbg("sql expr: $s") if isdbg('search'); + my $ref = $self->{dbh}->selectall_arrayref($s); + return @$ref; +} + 1; diff --git a/perl/DXSql/SQLite.pm b/perl/DXSql/SQLite.pm index 2f62461c..9447505a 100644 --- a/perl/DXSql/SQLite.pm +++ b/perl/DXSql/SQLite.pm @@ -10,6 +10,8 @@ package DXSql::SQLite; +use DXDebug; + use vars qw($VERSION $BRANCH @ISA); $VERSION = sprintf( "%d.%03d", q$Revision$ =~ /(\d+)\.(\d+)/ ); $BRANCH = sprintf( "%d.%03d", q$Revision$ =~ /\d+\.\d+\.(\d+)\.(\d+)/ || (0,0)); @@ -18,4 +20,49 @@ $main::branch += $BRANCH; @ISA = qw{DXSql}; +sub show_tables +{ + my $self = shift; + my $s = q(SELECT name FROM sqlite_master WHERE type='table' ORDER BY name); + my $sth = $self->prepare($s); + $sth->execute; + my @out; + push @out, $sth->fetchrow_array; + $sth->finish; + return @out; +} + +sub spot_create_table +{ + my $self = shift; + my $s = q{create table spot ( +rowid integer primary key, +freq real not null, +spotcall text not null, +time int not null, +comment text, +spotter text not null, +spotdxcc int, +spotterdxcc int, +origin text, +spotitu int, +spotcq int, +spotteritu int, +spottercq int, +spotstate text, +spotterstate text +)}; + $self->do($s); +} + +sub spot_add_indexes +{ + my $self = shift; + $self->do('create index spot_ix1 on spot(time desc)'); + dbg('adding spot index ix1'); + $self->do('create index spot_ix2 on spot(spotcall asc)'); + dbg('adding spot index ix2'); +} + + 1; diff --git a/perl/DXSql/mysql.pm b/perl/DXSql/mysql.pm new file mode 100644 index 00000000..5197adb1 --- /dev/null +++ b/perl/DXSql/mysql.pm @@ -0,0 +1,68 @@ +# +# Module for SQLite DXSql variants +# +# Stuff like table creates and (later) alters +# +# $Id$ +# +# Copyright (c) 2005 Dirk Koopman G1TLH +# + +package DXSql::mysql; + +use DXDebug; + +use vars qw($VERSION $BRANCH @ISA); +$VERSION = sprintf( "%d.%03d", q$Revision$ =~ /(\d+)\.(\d+)/ ); +$BRANCH = sprintf( "%d.%03d", q$Revision$ =~ /\d+\.\d+\.(\d+)\.(\d+)/ || (0,0)); +$main::build += $VERSION; +$main::branch += $BRANCH; + +@ISA = qw{DXSql}; + +sub show_tables +{ + my $self = shift; + my $s = q(show tables); + my $sth = $self->prepare($s); + $sth->execute; + my @out; + push @out, $sth->fetchrow_array; + $sth->finish; + return @out; +} + +sub spot_create_table +{ + my $self = shift; + my $s = q{create table spot ( +rowid integer auto_increment primary key , +freq real not null, +spotcall varchar(14) not null, +time int not null, +comment varchar(255), +spotter varchar(14) not null, +spotdxcc smallint, +spotterdxcc smallint, +origin varchar(14), +spotitu tinyint, +spotcq tinyint, +spotteritu tinyint, +spottercq tinyint, +spotstate char(2), +spotterstate char(2) +)}; + $self->do($s); +} + +sub spot_add_indexes +{ + my $self = shift; + $self->do('create index spot_ix1 on spot(time desc)'); + dbg('adding spot index ix1'); + $self->do('create index spot_ix2 on spot(spotcall asc)'); + dbg('adding spot index ix2'); +} + + +1; diff --git a/perl/Spot.pm b/perl/Spot.pm index 727ab07a..92f70552 100644 --- a/perl/Spot.pm +++ b/perl/Spot.pm @@ -103,6 +103,58 @@ sub init mkdir "$dirprefix", 0777 if !-e "$dirprefix"; $fp = DXLog::new($dirprefix, "dat", 'd'); $statp = DXLog::new($dirprefix, "dys", 'd'); + + # load up any old spots + if ($main::dbh) { + unless (grep $_ eq 'spot', $main::dbh->show_tables) { + dbg('initialising spot tables'); + my $t = time; + my $total; + $main::dbh->spot_create_table; + + my $now = Julian::Day->alloc(1995, 0); + my $today = Julian::Day->new(time); + my $sth = $main::dbh->spot_insert_prepare; + $main::dbh->{RaiseError} = 0; + while ($now->cmp($today) <= 0) { + my $fh = $fp->open($now); + if ($fh) { + my $count = 0; + while (<$fh>) { + chomp; + my @s = split /\^/; + if (@s < 12) { + my @a = (Prefix::cty_data($s[1]))[1..3]; + my @b = (Prefix::cty_data($s[4]))[1..3]; + push @s, $b[1] if @s < 7; + push @s, '' if @s < 8; + push @s, @a[0,1], @b[0,1] if @s < 12; + push @s, $a[2], $a[2] if @s < 14; + } + + push @s, undef while @s < 14; + pop @s while @s > 14; + + $main::dbh->spot_insert(\@s, $sth); + $count++; + } + $main::dbh->commit if $count; + $main::dbh->{RaiseError} = 0; + dbg("inserted $count spots from $now->[0] $now->[1]"); + $fh->close; + $total += $count; + } + $now = $now->add(1); + } + $main::dbh->spot_add_indexes; + $main::dbh->commit; + $main::dbh->{RaiseError} = 1; + $t = time - $t; + my $min = int($t / 60); + my $sec = $t % 60; + dbg("$total spots converted in $min:$sec"); + } + } } sub prefix @@ -139,6 +191,10 @@ sub add { my $buf = join('^', @_); $fp->writeunix($_[2], $buf); + if ($main::dbh) { + $main::dbh->spot_insert(\@_); + $main::dbh->commit; + } $totalspots++; if ($_[0] <= 30000) { $hfspots++; @@ -200,6 +256,10 @@ sub search $to = $from + $maxspots if $to - $from > $maxspots || $to - $from <= 0; + if ($main::dbh) { + return $main::dbh->spot_search($expr, $dayfrom, $dayto, $to-$from, $dxchan); + } + $expr =~ s/\$f(\d\d?)/\$ref->[$1]/g; # swap the letter n for the correct field name # $expr =~ s/\$f(\d)/\$spots[$1]/g; # swap the letter n for the correct field name @@ -298,7 +358,7 @@ sub formatl { my $t = ztime($_[2]); my $d = cldate($_[2]); - return sprintf "%8.1f %-11s %s %s %-28.28s%7s>", $_[0], $_[1], $d, $t, $_[3], "<$_[4]" ; + return sprintf "%8.1f %-11s %s %s %-28.28s%7s>", $_[0], $_[1], $d, $t, ($_[3]||''), "<$_[4]" ; } # diff --git a/perl/cluster.pl b/perl/cluster.pl index 9eba29c1..8645dbd9 100755 --- a/perl/cluster.pl +++ b/perl/cluster.pl @@ -334,6 +334,10 @@ sub AGWrestart $starttime = $systime = time; $lang = 'en' unless $lang; +unless ($DB::VERSION) { + $SIG{INT} = $SIG{TERM} = \&cease; +} + # open the debug file, set various FHs to be unbuffered dbginit(\&DXCommandmode::broadcast_debug); foreach (@debug) { -- 2.43.0