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] );
237 $query = "UPDATE $table SET ".
238 join(' AND ', @keyval).
241 foreach (keys %{$phref}) {
243 push(@vals, &dbQuote($phref->{$_}) );
246 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
247 join(',',@keys), join(',',@vals) );
250 &dbRaw("Set", $query);
256 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
257 # Note: dbUpdate does dbQuote.
259 my ($table, $primkey, $primval, %hash) = @_;
262 foreach (keys %hash) {
263 push(@array, "$_=".&dbQuote($hash{$_}) );
266 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
267 " WHERE $primkey=".&dbQuote($primval)
274 # Usage: &dbInsert($table, $primkey, %hash);
275 # Note: dbInsert does dbQuote.
277 my ($table, $primkey, %hash, $delay) = @_;
282 &DEBUG("dbI: delay => $delay");
286 foreach (keys %hash) {
288 push(@vals, &dbQuote($hash{$_}));
291 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
292 ") VALUES (".join(',',@vals).")"
299 # Usage: &dbReplace($table, $key, %hash);
300 # Note: dbReplace does optional dbQuote.
302 my ($table, $key, %hash) = @_;
305 foreach (keys %hash) {
306 if (s/^-//) { # as is.
308 push(@vals, $hash{'-'.$_});
311 push(@vals, &dbQuote($hash{$_}));
316 &DEBUG("REPLACE INTO $table (".join(',',@keys).
317 ") VALUES (". join(',',@vals). ")" );
320 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
321 ") VALUES (". join(',',@vals). ")"
328 # Usage: &dbSetRow($table, $vref, $delay);
329 # Note: dbSetRow does dbQuote.
331 my ($table, $vref, $delay) = @_;
332 my $p = ($delay) ? " DELAYED " : "";
334 # see 'perldoc perlreftut'
336 foreach (@{ $vref }) {
337 push(@values, &dbQuote($_) );
340 if (!scalar @values) {
341 &WARN("dbSetRow: values array == NULL.");
345 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
346 join(",", @values) .")" );
350 # Usage: &dbDel($table, $primkey, $primval, [$key]);
351 # Note: dbDel does dbQuote
353 my ($table, $primkey, $primval, $key) = @_;
355 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
362 # Usage: &dbRaw($prefix,$rawquery);
364 my ($prefix,$query) = @_;
367 if (!($sth = $dbh->prepare($query))) {
368 &ERROR("Raw($prefix): $DBI::errstr");
372 # &DEBUG("query => '$query'.");
375 if (!$sth->execute) {
376 &ERROR("Raw($prefix): => '$query'");
377 # $DBI::errstr is printed as warning automatically.
387 # Usage: &dbRawReturn($rawquery);
392 my $sth = $dbh->prepare($query);
394 &ERROR("RawReturn => '$query'.") unless $sth->execute;
395 while (my @row = $sth->fetchrow_array) {
396 push(@retval, $row[0]);
403 ####################################################################
404 ##### Misc DBI stuff...
408 # Usage: &countKeys($table, [$col]);
410 my ($table, $col) = @_;
412 &DEBUG("&countKeys($table, $col)");
414 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
417 # Usage: &sumKey($table, $col);
419 my ($table, $col) = @_;
421 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
425 # Usage: &randKey($table, $select);
427 my ($table, $select) = @_;
428 my $rand = int(rand(&countKeys($table) - 1));
429 my $query = "SELECT $select FROM $table LIMIT $rand,1";
431 my $sth = $dbh->prepare($query);
433 &WARN("randKey($query)") unless $sth->execute;
434 my @retval = $sth->fetchrow_array;
441 # Usage: &deleteTable($table);
443 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
447 # Usage: &searchTable($table, $select, $key, $str);
448 # Note: searchTable does dbQuote.
450 my($table, $select, $key, $str) = @_;
454 # allow two types of wildcards.
455 if ($str =~ /^\^(.*)\$$/) {
456 &DEBUG("searchTable: should use dbGet(), heh.");
459 $str .= "%" if ($str =~ s/^\^//);
460 $str = "%".$str if ($str =~ s/\$$//);
461 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
465 $str =~ s/\?/_/g; # '.' should be supported, too.
466 $str =~ s/\*/%/g; # for sqlite.
469 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
471 my $sth = $dbh->prepare($query);
472 &DEBUG("query => '$query'.");
474 if (!$sth->execute) {
475 &WARN("Search($query)");
479 while (my @row = $sth->fetchrow_array) {
480 push(@results, $row[0]);
489 my(@path) = ($bot_data_dir, ".","..","../..");
494 my $file = "$_/setup/$table.sql";
495 &DEBUG("dbCT: file => $file");
496 next unless ( -f $file );
498 &DEBUG("dbCT: found!!!");
513 &dbRaw("createTable($table)", $data);
519 # retrieve a list of tables's from the server.
521 foreach (&dbRawReturn("SELECT name FROM sqlite_master WHERE type='table'"))
526 foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats") {
527 next if (exists $db{$_});
528 &status("checkTables: creating $_...");