X-Git-Url: http://dxcluster.net/gitweb/gitweb.cgi?a=blobdiff_plain;f=perl%2FDXSql.pm;h=1a4fee810718c8c1ddcba4506007482f04638035;hb=b9dffeff7239952814342dad19db3a51def6fab7;hp=a327e07e220dc9a52daf5fed9124964eb2159e7a;hpb=e5a96ae00d9c44c94cff828e73cadc5352e531ef;p=spider.git

diff --git a/perl/DXSql.pm b/perl/DXSql.pm
index a327e07e..1a4fee81 100644
--- a/perl/DXSql.pm
+++ b/perl/DXSql.pm
@@ -10,16 +10,17 @@ package DXSql;
 
 use strict;
 
-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;
+use DXSql::SQLite;
+use DXSql::mysql;
+use DXDebug;
 
-our $active = 0;
+use vars qw($active);
+$active = 0;
 
 sub init
 {
+	my $dsn = shift;
+	return unless $dsn;
 	return $active if $active;
 	
 	eval { 
@@ -29,6 +30,7 @@ sub init
 		import DBI;
 		$active++;
 	}
+	undef $@;
 	return $active;
 } 
 
@@ -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;