2 # The master SQL module
6 # Copyright (c) 2006 Dirk Koopman G1TLH
22 return $active if $active;
41 return undef unless $active;
43 my ($style) = $dsn =~ /^dbi:(\w+):/;
44 my $newclass = "DXSql::$style";
45 eval "require $newclass";
50 return bless {}, $newclass;
63 $dbh = DBI->connect($dsn, $user, $passwd);
76 $self->{dbh}->disconnect;
84 eval { $self->{dbh}->do($s); };
89 $_[0]->{dbh}->begin_work;
99 $_[0]->{dbh}->rollback;
104 return $_[0]->{dbh}->quote($_[1]);
109 return $_[0]->{dbh}->prepare($_[1]);
112 sub spot_insert_prepare
115 return $self->prepare('insert into spot values(?' . ',?' x 15 . ')');
125 push @$spot, undef while @$spot < 15;
126 pop @$spot while @$spot > 15;
127 eval {$sth->execute(undef, @$spot)};
129 my $s = "insert into spot values(NULL,";
130 $s .= sprintf("%.1f,", $spot->[0]);
131 $s .= $self->quote($spot->[1]) . "," ;
132 $s .= $spot->[2] . ',';
133 $s .= (length $spot->[3] ? $self->quote($spot->[3]) : 'NULL') . ',';
134 $s .= $self->quote($spot->[4]) . ',';
135 $s .= $spot->[5] . ',';
136 $s .= $spot->[6] . ',';
137 $s .= (length $spot->[7] ? $self->quote($spot->[7]) : 'NULL') . ',';
138 $s .= $spot->[8] . ',';
139 $s .= $spot->[9] . ',';
140 $s .= $spot->[10] . ',';
141 $s .= $spot->[11] . ',';
142 $s .= (length $spot->[12] ? $self->quote($spot->[12]) : 'NULL') . ',';
143 $s .= (length $spot->[13] ? $self->quote($spot->[13]) : 'NULL') . ',';
144 $s .= (length $spot->[14] ? $self->quote($spot->[14]) : 'NULL') . ')';
145 eval {$self->do($s)};
158 dbg("expr: $expr") if isdbg('search');
159 if ($expr =~ /\$f/) {
160 $expr =~ s/(?:==|eq)/ = /g;
161 $expr =~ s/\$f10/spotteritu/g;
162 $expr =~ s/\$f11/spottercq/g;
163 $expr =~ s/\$f12/spotstate/g;
164 $expr =~ s/\$f13/spotterstate/g;
165 $expr =~ s/\$f14/ipaddr/g;
166 $expr =~ s/\$f0/freq/g;
167 $expr =~ s/\$f1/spotcall/g;
168 $expr =~ s/\$f2/time/g;
169 $expr =~ s/\$f3/comment/g;
170 $expr =~ s/\$f4/spotter/g;
171 $expr =~ s/\$f5/spotdxcc/g;
172 $expr =~ s/\$f6/spotterdxcc/g;
173 $expr =~ s/\$f7/origin/g;
174 $expr =~ s/\$f8/spotitu/g;
175 $expr =~ s/\$f9/spotcq/g;
176 $expr =~ s/\|\|/ or /g;
177 $expr =~ s/\&\&/ and /g;
178 $expr =~ s/=~\s+m\{\^([%\w]+)[^\}]*\}/ like '$1'/g;
182 my $fdays = $dayfrom ? "time <= " . ($main::systime - ($dayfrom * 86400)) : "";
183 my $days = "time >= " . ($main::systime - ($dayto * 86400));
184 my $trange = $fdays ? "($fdays and $days)" : $days;
185 $expr .= $expr ? " and $trange" : $trange;
186 my $s = qq{select freq,spotcall,time,comment,spotter,spotdxcc,spotterdxcc,
187 origin,spotitu,spotcq,spotteritu,spottercq,spotstate,spotterstate,ipaddr from spot
188 where $expr order by time desc limit $n};
189 dbg("sql expr: $s") if isdbg('search');
190 my $ref = $self->{dbh}->selectall_arrayref($s);