2 # dbi.pl: DBI (mysql/pgsql/sqlite) database frontend.
4 # Version: v0.2c (19991224)
6 # Notes: based on db_mysql.pl
12 use vars qw($dbh $shm $bot_data_dir);
17 # &openDB($dbname, $dbtype, $sqluser, $sqlpass, $nofail);
19 my ($db, $type, $user, $pass, $no_fail) = @_;
20 # this is a mess. someone fix it, please.
21 if ($type =~ /^SQLite$/i) {
22 $db = "dbname=$db.sqlite";
23 } elsif ($type =~ /^pg/i) {
28 my $dsn = "DBI:$type:$db";
30 # SQLHost should be unset for SQLite
31 if (exists $param{'SQLHost'} and $param{'SQLHost'}) {
32 $dsn .= ":$param{SQLHost}";
33 $hoststr = " to $param{'SQLHost'}";
35 # SQLite ignores $user and $pass
36 $dbh = DBI->connect($dsn, $user, $pass);
38 if ($dbh && !$dbh->err) {
39 &status("Opened $type connection$hoststr");
41 &ERROR("cannot connect$hoststr.");
42 &ERROR("since $type is not available, shutting down bot!");
43 &ERROR( $dbh->errstr ) if ($dbh);
48 return 0 if ($no_fail);
55 return 0 unless ($dbh);
57 my $x = $param{SQLHost};
58 my $hoststr = ($x) ? " to $x" : "";
60 &status("Closed DBI connection$hoststr.");
67 # Usage: &dbQuote($str);
69 return $dbh->quote($_[0]);
73 # Usage: &dbGet($table, $select, $where);
75 my ($table, $select, $where) = @_;
76 my $query = "SELECT $select FROM $table";
77 $query .= " WHERE $where" if ($where);
79 if (!defined $select or $select =~ /^\s*$/) {
80 &WARN("dbGet: select == NULL.");
84 if (!defined $table or $table =~ /^\s*$/) {
85 &WARN("dbGet: table == NULL.");
90 if (!($sth = $dbh->prepare($query))) {
91 &ERROR("Get: prepare: $DBI::errstr");
97 &ERROR("Get: execute: '$query'");
102 my @retval = $sth->fetchrow_array;
106 if (scalar @retval > 1) {
108 } elsif (scalar @retval == 1) {
116 # Usage: &dbGetCol($table, $select, $where, [$type]);
118 my ($table, $select, $where, $type) = @_;
119 my $query = "SELECT $select FROM $table";
120 $query .= " WHERE ".$where if ($where);
123 my $sth = $dbh->prepare($query);
125 if (!$sth->execute) {
126 &ERROR("GetCol: execute: '$query'");
131 if (defined $type and $type == 2) {
132 &DEBUG("dbgetcol: type 2!");
133 while (my @row = $sth->fetchrow_array) {
134 $retval{$row[0]} = join(':', $row[1..$#row]);
136 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
138 } elsif (defined $type and $type == 1) {
139 while (my @row = $sth->fetchrow_array) {
140 # reverse it to make it easier to count.
141 if (scalar @row == 2) {
142 $retval{$row[1]}{$row[0]} = 1;
143 } elsif (scalar @row == 3) {
144 $retval{$row[1]}{$row[0]} = 1;
146 # what to do if there's only one or more than 3?
150 while (my @row = $sth->fetchrow_array) {
151 $retval{$row[0]} = $row[1];
161 # Usage: &dbGetColNiceHash($table, $select, $where);
162 sub dbGetColNiceHash {
163 my ($table, $select, $where) = @_;
165 my $query = "SELECT $select FROM $table";
166 $query .= " WHERE ".$where if ($where);
170 if (!($sth = $dbh->prepare($query))) {
171 &ERROR("GetColNiceHash: prepare: $DBI::errstr");
175 if (!$sth->execute) {
176 &ERROR("GetColNiceHash: execute: '$query'");
177 # &ERROR("GetCol => $DBI::errstr");
182 # FIXME this dies hard if there are no results
183 %retval = %{ $sth->fetchrow_hashref };
191 # Usage: &dbGetColInfo($table);
195 my $query = "SHOW COLUMNS from $table";
196 if ($param{DBType} =~ /^pg/i) {
197 $query = "SELECT * FROM $table LIMIT 1";
202 my $sth = $dbh->prepare($query);
204 if (!$sth->execute) {
205 &ERROR("GRI => '$query'");
206 &ERROR("GRI => $DBI::errstr");
212 while (my @row = $sth->fetchrow_array) {
213 push(@cols, $row[0]);
220 ##### NOTE: not used yet.
221 # Usage: &dbSelectHashref($select, $from, $where, $other)
222 sub dbSelectHashref {
223 my $c = dbSelectManyHash(@_);
224 my $H = $c->fetchrow_hashref;
229 ##### NOTE: not used yet.
230 # Usage: &dbSelectHashref($select, $from, $where, $other)
231 sub dbSelectManyHash {
232 my($select, $from, $where, $other) = @_;
235 $sql = "SELECT $select ";
236 $sql .= "FROM $from " if $from;
237 $sql .= "WHERE $where " if $where;
238 $sql .= "$other" if $other;
241 my $c = $dbh->prepare($sql);
242 # $c->execute or print "\n<P><B>SQL Hashref Error</B><BR>\n";
244 unless ($c->execute) {
254 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
255 # Note: dbSet does dbQuote.
257 my ($table, $phref, $href) = @_;
258 my $where = join(' AND ', map {
259 $_."=".&dbQuote($phref->{$_})
263 if (!defined $phref) {
264 &WARN("dbset: phref == NULL.");
268 if (!defined $href) {
269 &WARN("dbset: href == NULL.");
273 if (!defined $table) {
274 &WARN("dbset: table == NULL.");
279 foreach (keys %{$href}) {
281 push(@vals, &dbQuote($href->{$_}) );
284 if (!@keys or !@vals) {
285 &WARN("dbset: keys or vals is NULL.");
289 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
292 if (defined $result) {
294 for(my$i=0; $i<scalar @keys; $i++) {
295 push(@keyval, $keys[$i]."=".$vals[$i] );
298 $query = "UPDATE $table SET ".
302 foreach (keys %{$phref}) {
304 push(@vals, &dbQuote($phref->{$_}) );
307 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
308 join(',',@keys), join(',',@vals) );
311 &dbRaw("Set", $query);
317 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
318 # Note: dbUpdate does dbQuote.
320 my ($table, $primkey, $primval, %hash) = @_;
323 foreach (keys %hash) {
324 push(@array, "$_=".&dbQuote($hash{$_}) );
327 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
328 " WHERE $primkey=".&dbQuote($primval)
335 # Usage: &dbInsert($table, $primkey, %hash);
336 # Note: dbInsert does dbQuote.
338 my ($table, $primkey, %hash, $delay) = @_;
343 &DEBUG("dbI: delay => $delay");
347 foreach (keys %hash) {
349 push(@vals, &dbQuote( $hash{$_} ));
352 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
353 ") VALUES (".join(',',@vals).")"
360 # Usage: &dbReplace($table, $key, %hash);
361 # Note: dbReplace does optional dbQuote.
363 my ($table, $key, %hash) = @_;
366 foreach (keys %hash) {
367 if (s/^-//) { # as is.
369 push(@vals, $hash{'-'.$_});
372 push(@vals, &dbQuote( $hash{$_} ));
376 # hrm... does pgsql support REPLACE?
377 # if not, well... fuck it.
378 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
379 ") VALUES (". join(',',@vals). ")"
386 # Usage: &dbSetRow($table, $vref, $delay);
387 # Note: dbSetRow does dbQuote.
389 my ($table, $vref, $delay) = @_;
390 my $p = ($delay) ? " DELAYED " : "";
392 # see 'perldoc perlreftut'
394 foreach (@{ $vref }) {
395 push(@values, &dbQuote($_) );
398 if (!scalar @values) {
399 &WARN("dbSetRow: values array == NULL.");
403 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
404 join(",", @values) .")" );
408 # Usage: &dbDel($table, $primkey, $primval, [$key]);
409 # Note: dbDel does dbQuote
411 my ($table, $primkey, $primval, $key) = @_;
413 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
420 # Usage: &dbRaw($prefix,$rawquery);
422 my ($prefix,$query) = @_;
425 if (!($sth = $dbh->prepare($query))) {
426 &ERROR("Raw($prefix): !prepare => '$query'");
431 if (!$sth->execute) {
432 &ERROR("Raw($prefix): !execute => '$query'");
442 # Usage: &dbRawReturn($rawquery);
447 my $sth = $dbh->prepare($query);
449 # what happens when it can't execute it? does it throw heaps more
450 # error lines? if so. follow dbRaw()'s style.
451 &ERROR("RawReturn => '$query'.") unless $sth->execute;
452 while (my @row = $sth->fetchrow_array) {
453 push(@retval, $row[0]);
460 ####################################################################
461 ##### Misc DBI stuff...
465 # Usage: &countKeys($table, [$col]);
467 my ($table, $col) = @_;
469 &DEBUG("&countKeys($table, $col);");
471 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
475 # Usage: &sumKey($table, $col);
477 my ($table, $col) = @_;
479 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
483 # Usage: &randKey($table, $select);
485 my ($table, $select) = @_;
486 my $rand = int(rand(&countKeys($table) - 1));
487 my $query = "SELECT $select FROM $table LIMIT $rand,1";
488 if ($param{DBType} =~ /^pg/i) {
489 $query =~ s/$rand,1/1,$rand/;
492 my $sth = $dbh->prepare($query);
494 &WARN("randKey($query)") unless $sth->execute;
495 my @retval = $sth->fetchrow_array;
502 # Usage: &deleteTable($table);
504 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
508 # Usage: &searchTable($table, $select, $key, $str);
509 # Note: searchTable does dbQuote.
511 my($table, $select, $key, $str) = @_;
515 # allow two types of wildcards.
516 if ($str =~ /^\^(.*)\$$/) {
517 &DEBUG("searchTable: should use dbGet(), heh.");
520 $str .= "%" if ($str =~ s/^\^//);
521 $str = "%".$str if ($str =~ s/\$$//);
522 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
526 $str =~ s/\?/_/g; # '.' should be supported, too.
530 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
532 my $sth = $dbh->prepare($query);
535 if (!$sth->execute) {
536 &WARN("Search($query)");
541 while (my @row = $sth->fetchrow_array) {
542 push(@results, $row[0]);
551 my(@path) = ($bot_data_dir, ".","..","../..");
556 my $file = "$_/setup/$table.sql";
557 &DEBUG("dbCT: table => '$table', file => '$file'");
558 next unless ( -f $file );
560 &DEBUG("dbCT: found!!!");
575 &dbRaw("dbcreateTable($table)", $data);
581 my $database_exists = 0;
584 if ($param{DBType} =~ /^mysql$/i) {
585 my $sql = "SHOW DATABASES";
586 foreach ( &dbRawReturn($sql) ) {
587 $database_exists++ if ($_ eq $param{'DBName'});
590 unless ($database_exists) {
591 &status("Creating database $param{DBName}...");
592 my $query = "CREATE DATABASE $param{DBName}";
593 &dbRaw("create(db $param{DBName})", $query);
596 # retrieve a list of db's from the server.
597 foreach ($dbh->func('_ListTables')) {
601 } elsif ($param{DBType} =~ /^SQLite$/i) {
603 # retrieve a list of db's from the server.
604 foreach ( &dbRawReturn("SELECT name FROM sqlite_master WHERE type='table'") ) {
609 if (!scalar keys %db) {
610 &status("Creating database $param{'DBName'}...");
611 my $query = "CREATE DATABASE $param{'DBName'}";
612 &dbRaw("create(db $param{'DBName'})", $query);
616 foreach ( qw(factoids freshmeat rootwarn seen stats botmail) ) {
617 next if (exists $db{$_});
618 &status("checkTables: creating new table $_...");