2 # db_sqlite.pl: SQLite database frontend.
3 # Author: Tim Riker <Tim@Rikers.org>
4 # Version: 0.1 (20021101)
11 if (&::IsParam("useStrict")) { use strict; }
14 # &openDB($dbname, $sqluser, $sqlpass, $nofail);
16 my ($db, $user, $pass, $no_fail) = @_;
17 my $dsn = "DBI:SQLite:dbname=$db.sqlite";
18 $dbh = DBI->connect($dsn,$user,$pass);
21 &::status("Opened SQLite connection $dsn");
23 &::ERROR("cannot connect $dsn.");
24 &::ERROR("since SQLite is not available, shutting down bot!");
36 return 0 unless ($dbh);
39 $hoststr = " to $param{'SQLHost'}" if (exists $param{'SQLHost'});
41 &::status("Closed SQLite connection$hoststr.");
48 # Usage: &dbQuote($str);
50 return $dbh->quote($_[0]);
54 # Usage: &dbGet($table, $select, $where);
56 my ($table, $select, $where) = @_;
57 my $query = "SELECT $select FROM $table";
58 $query .= " WHERE $where" if ($where);
60 if (!defined $select or $select =~ /^\s*$/) {
61 &::WARN("dbGet: select == NULL.");
65 if (!defined $table or $table =~ /^\s*$/) {
66 &::WARN("dbGet: table == NULL.");
71 if (!($sth = $dbh->prepare($query))) {
72 &::ERROR("Get: prepare: $DBI::errstr");
78 &::ERROR("Get: execute: '$query'");
83 my @retval = $sth->fetchrow_array;
87 if (scalar @retval > 1) {
89 } elsif (scalar @retval == 1) {
97 # Usage: &dbGetCol($table, $select, $where, [$type]);
99 my ($table, $select, $where, $type) = @_;
100 my $query = "SELECT $select FROM $table";
101 $query .= " WHERE ".$where if ($where);
104 my $sth = $dbh->prepare($query);
106 if (!$sth->execute) {
107 &::ERROR("GetCol: execute: '$query'");
112 if (defined $type and $type == 2) {
113 &DEBUG("dbgetcol: type 2!");
114 while (my @row = $sth->fetchrow_array) {
115 $retval{$row[0]} = join(':', $row[1..$#row]);
117 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
119 } elsif (defined $type and $type == 1) {
120 while (my @row = $sth->fetchrow_array) {
121 # reverse it to make it easier to count.
122 if (scalar @row == 2) {
123 $retval{$row[1]}{$row[0]} = 1;
124 } elsif (scalar @row == 3) {
125 $retval{$row[1]}{$row[0]} = 1;
127 # what to do if there's only one or more than 3?
131 while (my @row = $sth->fetchrow_array) {
132 $retval{$row[0]} = $row[1];
142 # Usage: &dbGetColNiceHash($table, $select, $where);
143 sub dbGetColNiceHash {
144 my ($table, $select, $where) = @_;
146 my $query = "SELECT $select FROM $table";
147 $query .= " WHERE ".$where if ($where);
150 my $sth = $dbh->prepare($query);
152 if (!$sth->execute) {
153 &::ERROR("GetColNiceHash: execute: '$query'");
154 # &::ERROR("GetCol => $DBI::errstr");
159 %retval = %{ $sth->fetchrow_hashref() };
167 # Usage: &dbGetColInfo($table);
171 my $query = "SHOW COLUMNS from $table";
174 my $sth = $dbh->prepare($query);
176 if (!$sth->execute) {
177 &::ERROR("GRI => '$query'");
178 &::ERROR("GRI => $DBI::errstr");
184 while (my @row = $sth->fetchrow_array) {
185 push(@cols, $row[0]);
193 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
194 # Note: dbSet does dbQuote.
196 my ($table, $phref, $href) = @_;
197 my $where = join(' AND ', map {
198 $_."=".&dbQuote($phref->{$_})
202 if (!defined $phref) {
203 &::WARN("dbset: phref == NULL.");
207 if (!defined $href) {
208 &::WARN("dbset: href == NULL.");
212 if (!defined $table) {
213 &::WARN("dbset: table == NULL.");
217 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
220 foreach (keys %{$href}) {
222 push(@vals, &dbQuote($href->{$_}) );
225 if (!@keys or !@vals) {
226 &::WARN("dbset: keys or vals is NULL.");
231 if (defined $result) {
233 for(my$i=0; $i<scalar @keys; $i++) {
234 push(@keyval, $keys[$i]."=".$vals[$i] );
236 $query = "UPDATE $table SET " . join(', ', @keyval) . " WHERE ".$where;
237 &dbRaw("Update", $query);
240 foreach (keys %{$phref}) {
242 push(@vals, &dbQuote($phref->{$_}) );
245 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
246 join(',',@keys), join(',',@vals) );
247 &dbRaw("Set", $query);
254 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
255 # Note: dbUpdate does dbQuote.
257 my ($table, $primkey, $primval, %hash) = @_;
260 foreach (keys %hash) {
261 push(@keyval, "$_=".&dbQuote($hash{$_}) );
264 &dbRaw("Update", "UPDATE $table SET ".join(', ', @keyval).
265 " WHERE $primkey=".&dbQuote($primval)
272 # Usage: &dbInsert($table, $primkey, %hash);
273 # Note: dbInsert does dbQuote.
275 my ($table, $primkey, %hash, $delay) = @_;
280 &DEBUG("dbI: delay => $delay");
284 foreach (keys %hash) {
286 push(@vals, &dbQuote($hash{$_}));
289 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
290 ") VALUES (".join(',',@vals).")"
297 # Usage: &dbReplace($table, $key, %hash);
298 # Note: dbReplace does optional dbQuote.
300 my ($table, $key, %hash) = @_;
303 foreach (keys %hash) {
304 if (s/^-//) { # as is.
306 push(@vals, $hash{'-'.$_});
309 push(@vals, &dbQuote($hash{$_}));
314 &DEBUG("REPLACE INTO $table (".join(',',@keys).
315 ") VALUES (". join(',',@vals). ")" );
318 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
319 ") VALUES (". join(',',@vals). ")"
326 # Usage: &dbSetRow($table, $vref, $delay);
327 # Note: dbSetRow does dbQuote.
329 my ($table, $vref, $delay) = @_;
330 my $p = ($delay) ? " DELAYED " : "";
332 # see 'perldoc perlreftut'
334 foreach (@{ $vref }) {
335 push(@values, &dbQuote($_) );
338 if (!scalar @values) {
339 &::WARN("dbSetRow: values array == NULL.");
343 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
344 join(",", @values) .")" );
348 # Usage: &dbDel($table, $primkey, $primval, [$key]);
349 # Note: dbDel does dbQuote
351 my ($table, $primkey, $primval, $key) = @_;
353 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
360 # Usage: &dbRaw($prefix,$rawquery);
362 my ($prefix,$query) = @_;
365 if (!($sth = $dbh->prepare($query))) {
366 &::ERROR("Raw($prefix): $DBI::errstr");
371 if (!$sth->execute) {
372 &::ERROR("Raw($prefix): => '$query'");
373 # $DBI::errstr is printed as warning automatically.
383 # Usage: &dbRawReturn($rawquery);
388 my $sth = $dbh->prepare($query);
390 &::ERROR("RawReturn => '$query'.") unless $sth->execute;
391 while (my @row = $sth->fetchrow_array) {
392 push(@retval, $row[0]);
399 ####################################################################
400 ##### Misc DBI stuff...
404 # Usage: &countKeys($table, [$col]);
406 my ($table, $col) = @_;
408 &DEBUG("&countKeys($table, $col)");
410 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
413 # Usage: &sumKey($table, $col);
415 my ($table, $col) = @_;
417 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
421 # Usage: &randKey($table, $select);
423 my ($table, $select) = @_;
424 my $rand = int(rand(&countKeys($table) - 1));
425 my $query = "SELECT $select FROM $table LIMIT $rand,1";
427 my $sth = $dbh->prepare($query);
429 &::WARN("randKey($query)") unless $sth->execute;
430 my @retval = $sth->fetchrow_array;
437 # Usage: &deleteTable($table);
439 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
443 # Usage: &searchTable($table, $select, $key, $str);
444 # Note: searchTable does dbQuote.
446 my($table, $select, $key, $str) = @_;
450 # allow two types of wildcards.
451 if ($str =~ /^\^(.*)\$$/) {
452 &DEBUG("searchTable: should use dbGet(), heh.");
455 $str .= "%" if ($str =~ s/^\^//);
456 $str = "%".$str if ($str =~ s/\$$//);
457 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
461 $str =~ s/\?/_/g; # '.' should be supported, too.
462 $str =~ s/\*/%/g; # for sqlite.
465 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
467 my $sth = $dbh->prepare($query);
469 if (!$sth->execute) {
470 &::WARN("Search($query)");
474 while (my @row = $sth->fetchrow_array) {
475 push(@results, $row[0]);
484 my(@path) = ($bot_data_dir, ".","..","../..");
489 my $file = "$_/setup/$table.sql";
490 &DEBUG("dbCT: file => $file");
491 next unless ( -f $file );
493 &DEBUG("dbCT: found!!!");
508 &dbRaw("createTable($table)", $data);
514 # retrieve a list of tables's from the server.
516 foreach (&dbRawReturn("SELECT name FROM sqlite_master WHERE type='table'"))
521 foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats") {
522 next if (exists $db{$_});
523 &::status("checkTables: creating $_...");