X-Git-Url: http://www.dxcluster.org/gitweb/gitweb.cgi?a=blobdiff_plain;f=perl%2FDXSql.pm;h=242ab0a8d54dbb3120b33ae5af6570f106a563e3;hb=refs%2Fheads%2Fstaging;hp=5d45eb94ea68108ba6b2e023f7edb32906981893;hpb=dc305f992d75a6b35edda9e1aefab510a3ed617e;p=spider.git

diff --git a/perl/DXSql.pm b/perl/DXSql.pm
index 5d45eb94..242ab0a8 100644
--- a/perl/DXSql.pm
+++ b/perl/DXSql.pm
@@ -1,7 +1,7 @@
 #
 # The master SQL module
 #
-# $Id$
+#
 #
 # Copyright (c) 2006 Dirk Koopman G1TLH
 #
@@ -12,16 +12,13 @@ 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));
-$main::build += $VERSION;
-$main::branch += $BRANCH;
-
-our $active = 0;
+use vars qw($active);
+$active = 0;
 
 sub init
 {
+	my $dsn = shift;
+	return unless $dsn;
 	return $active if $active;
 	
 	eval { 
@@ -63,7 +60,8 @@ sub connect
 	my $dbh;
 	eval {
 		no strict 'refs';
-		$dbh = DBI->connect($dsn, $user, $passwd, {AutoCommit => 0}); 
+		$dbh = DBI->connect($dsn, $user, $passwd);
+		dbg "DXSql $dsn " . $dbh? "connected" : "NOT connected" if isdbg('dxsql');
 	};
 	unless ($dbh) {
 		$active = 0;
@@ -87,16 +85,19 @@ sub do
 	eval { $self->{dbh}->do($s); }; 
 }
 
+sub begin_work
+{
+	$_[0]->{dbh}->begin_work;
+}
+
 sub commit
 {
 	$_[0]->{dbh}->commit;
-	$_[0]->{dbh}->{AutoCommit} = 0;
 }
 
 sub rollback
 {
 	$_[0]->{dbh}->rollback;
-	$_[0]->{dbh}->{AutoCommit} = 0;
 }
 
 sub quote
@@ -112,7 +113,7 @@ sub prepare
 sub spot_insert_prepare
 {
 	my $self = shift;
-	return $self->prepare('insert into spot values(?' . ',?' x 14 . ')');
+	return $self->prepare('insert into spot values(?' . ',?' x 15 . ')');
 }
 
 sub spot_insert
@@ -122,6 +123,8 @@ sub spot_insert
 	my $sth = shift;
 	
 	if ($sth) {
+		push @$spot, undef while  @$spot < 15;
+		pop @$spot while @$spot > 15;
 		eval {$sth->execute(undef, @$spot)};
 	} else {
 		my $s = "insert into spot values(NULL,";
@@ -138,7 +141,8 @@ sub spot_insert
 		$s .= $spot->[10] . ',';
 		$s .= $spot->[11] . ',';
 		$s .= (length $spot->[12] ? $self->quote($spot->[12]) : 'NULL') . ',';
-		$s .= (length $spot->[13] ? $self->quote($spot->[13]) : 'NULL') . ')';
+		$s .= (length $spot->[13] ? $self->quote($spot->[13]) : 'NULL') . ',';
+		$s .= (length $spot->[14] ? $self->quote($spot->[14]) : 'NULL') . ')';
 		eval {$self->do($s)};
 	}
 }
@@ -146,32 +150,37 @@ sub spot_insert
 sub spot_search
 {
 	my $self = shift;
-	my $expr = shift;
-	my $dayfrom = shift;
-	my $dayto = shift;
-	my $n = shift;
-	my $dxchan = shift;
+	my ($expr, $dayfrom, $dayto, $from, $to, $hint, $dofilter, $dxchan) = @_;
+	$dayfrom = 0 if !$dayfrom;
+	$dayto = $Spot::maxdays unless $dayto;
+	$dayto = $dayfrom + $Spot::maxdays if $dayto < $dayfrom;
+	my $today = Julian::Day->new(time());
+	my $fromdate = $today->sub($dayfrom);
+	my $todate = $fromdate->sub($dayto);
+	$from = 0 unless $from;
+	$to = $Spot::defaultspots unless $to;
 	
-	dbg("expr: $expr") if isdbg('search');
-	if ($expr =~ /\$f/) {
+	dbg("DXSql expr: $expr") if isdbg('search');
+	if ($expr =~ /\$r->/) {
 		$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/\$r->\[10\]/spotteritu/g;
+		$expr =~ s/\$r->\[11\]/spottercq/g;
+		$expr =~ s/\$r->\[12\]/spotstate/g;
+		$expr =~ s/\$r->\[13\]/spotterstate/g;
+		$expr =~ s/\$r->\[14\]/ipaddr/g;
+		$expr =~ s/\$r->\[0\]/freq/g;
+		$expr =~ s/\$r->\[1\]/spotcall/g;
+		$expr =~ s/\$r->\[2\]/time/g;
+		$expr =~ s/\$r->\[3\]/comment/g;
+		$expr =~ s/\$r->\[4\]/spotter/g;
+		$expr =~ s/\$r->\[5\]/spotdxcc/g;
+		$expr =~ s/\$r->\[6\]/spotterdxcc/g;
+		$expr =~ s/\$r->\[7\]/origin/g;
+		$expr =~ s/\$r->\[8\]/spotitu/g;
+		$expr =~ s/\$r->\[9\]/spotcq/g;
 		$expr =~ s/\|\|/ or /g;
 		$expr =~ s/\&\&/ and /g;
-		$expr =~ s/=~\s+m\{\^([%\w]+)[^\}]*\}/ like '$1'/g;
+		$expr =~ s/=~\s*m\{\^([%\w]+)[^\}]*\}/ like '$1\%'/g;
 	} else {
 		$expr = '';
 	}  
@@ -180,9 +189,9 @@ sub spot_search
 	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');
+origin,spotitu,spotcq,spotteritu,spottercq,spotstate,spotterstate,ipaddr from spot
+where $expr limit $to};
+    dbg("DXSql expr: $s") if isdbg('search');
 	my $ref = $self->{dbh}->selectall_arrayref($s);
 	return @$ref;
 }