2 # dbi.pl: DBI (mysql/pgsql/sqlite) database frontend.
4 # Version: v0.9a (20021124)
6 # Notes: based on db_mysql.pl
7 # overhauled to be 31337.
13 use vars qw($dbh $shm $bot_data_dir);
18 # &sqlOpenDB($dbname, $dbtype, $sqluser, $sqlpass, $nofail);
20 my ($db, $type, $user, $pass, $no_fail) = @_;
21 # this is a mess. someone fix it, please.
22 if ($type =~ /^SQLite$/i) {
23 $db = "dbname=$db.sqlite";
24 } elsif ($type =~ /^pg/i) {
29 my $dsn = "DBI:$type:$db";
31 # SQLHost should be unset for SQLite
32 if (exists $param{'SQLHost'} and $param{'SQLHost'}) {
33 $dsn .= ":$param{SQLHost}";
34 $hoststr = " to $param{'SQLHost'}";
36 # SQLite ignores $user and $pass
37 $dbh = DBI->connect($dsn, $user, $pass);
39 if ($dbh && !$dbh->err) {
40 &status("Opened $type connection$hoststr");
42 &ERROR("cannot connect$hoststr.");
43 &ERROR("since $type is not available, shutting down bot!");
44 &ERROR( $dbh->errstr ) if ($dbh);
49 return 0 if ($no_fail);
56 return 0 unless ($dbh);
58 my $x = $param{SQLHost};
59 my $hoststr = ($x) ? " to $x" : "";
61 &status("Closed DBI connection$hoststr.");
68 # Usage: &sqlQuote($str);
70 return $dbh->quote($_[0]);
74 # Usage: &sqlSelectMany($table, $select, [$where_href], [$other]);
75 # Return: $sth (Statement handle object)
77 my($table, $select, $where_href, $other) = @_;
78 my $query = "SELECT $select FROM $table";
81 if (!defined $select or $select =~ /^\s*$/) {
82 &WARN("sqlSelectMany: select == NULL.");
86 if (!defined $table or $table =~ /^\s*$/) {
87 &WARN("sqlSelectMany: table == NULL.");
91 $query .= " WHERE ".&hashref2where($where_href) if ($where_href);
92 $query .= "$other" if $other;
94 if (!($sth = $dbh->prepare($query))) {
95 &ERROR("sqlSelectMany: prepare: $DBI::errstr");
100 if (!$sth->execute) {
101 &ERROR("sqlSelectMany: execute: '$query'");
110 # Usage: &sqlSelect($table, $select, [$where_href, [$other]);
111 # Return: scalar if one element, array if list of elements.
112 # Note: Suitable for one column returns, that is, one column in $select.
113 # Todo: Always return array?
115 my $sth = &sqlSelectMany(@_);
116 my @retval = $sth->fetchrow_array;
120 if (scalar @retval > 1) {
122 } elsif (scalar @retval == 1) {
130 # Usage: &sqlSelectColHash($table, $select, [$where_href], [$type]);
131 # Return: type = 1: $retval{ col2 }{ col1 } = 1;
132 # Return: no type: $retval{ col1 } = col2;
133 # Note: does not support $other, yet.
134 sub sqlSelectColHash {
135 my ($table, $select, $where_href, $type) = @_;
136 my $sth = &sqlSelectMany($table, $select, $where_href);
139 if (defined $type and $type == 2) {
140 &DEBUG("dbgetcol: type 2!");
141 while (my @row = $sth->fetchrow_array) {
142 $retval{$row[0]} = join(':', $row[1..$#row]);
144 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
146 } elsif (defined $type and $type == 1) {
147 while (my @row = $sth->fetchrow_array) {
148 # reverse it to make it easier to count.
149 if (scalar @row == 2) {
150 $retval{$row[1]}{$row[0]} = 1;
151 } elsif (scalar @row == 3) {
152 $retval{$row[1]}{$row[0]} = 1;
154 # what to do if there's only one or more than 3?
158 while (my @row = $sth->fetchrow_array) {
159 $retval{$row[0]} = $row[1];
169 # Usage: &sqlSelectRowHash($table, $select, [$where_href]);
170 # Return: $hash{ col } = value;
171 # Note: useful for returning only one/first row of data.
172 sub sqlSelectRowHash {
173 my $sth = &sqlSelectMany(@_);
174 my $retval = $sth->fetchrow_hashref();
185 # End of SELECT functions.
189 # Usage: &sqlSet($table, $data_href, $where_href);
190 # Return: 1 for success, undef for failure.
192 my ($table, $data_href, $where_href) = @_;
194 if (!defined $table or $table =~ /^\s*$/) {
195 &WARN("sqlSet: table == NULL.");
199 if (!defined $data_href or ref($data_href) ne "HASH") {
200 &WARN("sqlSet: data_href == NULL.");
204 my $where = &hashref2where($where_href) if ($where_href);
205 my $update = &hashref2update($data_href) if ($data_href);
206 my (@k,@v) = &hashref2array($data_href);
209 &WARN("sqlSet: keys or vals is NULL.");
213 my $result = &sqlGet($table, join(',', @k), $where);
214 if (defined $result) {
215 &sqlUpdate($table, $data_href, $where_href);
217 &sqlInsert($table, $data_href);
224 # Usage: &sqlUpdate($table, $data_href, $where_href);
226 my ($table, $data_href, $where_href) = @_;
228 if (!defined $data_href or ref($data_href) ne "HASH") {
229 &WARN("sqlSet: data_href == NULL.");
233 my $where = &hashref2where($where_href) if ($where_href);
234 my $update = &hashref2update($data_href) if ($data_href);
236 &sqlRaw("Update", "UPDATE $table SET $update WHERE $where");
242 # Usage: &sqlInsert($table, $data_href, $other);
244 my ($table, $data_href, $other) = @_;
245 # note: if $other == 1, add "DELAYED" to function instead.
247 if (!defined $data_href or ref($data_href) ne "HASH") {
248 &WARN("sqlInsert: data_href == NULL.");
252 my (@k,@v) = &hashref2array($data_href);
254 &WARN("sqlInsert: keys or vals is NULL.");
258 &sqlRaw("Insert($table)", sprintf(
259 "INSERT %s INTO %s (%s) VALUES (%s)",
260 $other, $table, join(',',@k), join(',',@v)
267 # Usage: &sqlReplace($table, $data_href);
269 my ($table, $data_href) = @_;
271 if (!defined $data_href or ref($data_href) ne "HASH") {
272 &WARN("sqlReplace: data_href == NULL.");
276 my (@k,@v) = &hashref2array($data_href);
278 &WARN("sqlReplace: keys or vals is NULL.");
282 &sqlRaw("Replace($table)", sprintf(
283 "REPLACE INTO %s (%s) VALUES (%s)",
284 $table, join(',',@k), join(',',@v)
291 # Usage: &sqlDelete($table, $where_href);
293 my ($table, $where_href) = @_;
295 if (!defined $where_href or ref($where_href) ne "HASH") {
296 &WARN("sqlDelete: where_href == NULL.");
300 my $where = &hashref2where($where_href);
302 &sqlRaw("Delete", "DELETE FROM $table WHERE $where");
308 # Usage: &sqlRaw($prefix, $query);
309 # Return: 1 for success, 0 for failure.
311 my ($prefix, $query) = @_;
314 if (!defined $query or $query =~ /^\s*$/) {
315 &WARN("sqlRaw: query == NULL.");
319 if (!($sth = $dbh->prepare($query))) {
320 &ERROR("Raw($prefix): !prepare => '$query'");
325 if (!$sth->execute) {
326 &ERROR("Raw($prefix): !execute => '$query'");
337 # Usage: &sqlRawReturn($query);
344 if (!defined $query or $query =~ /^\s*$/) {
345 &WARN("sqlRawReturn: query == NULL.");
349 if (!($sth = $dbh->prepare($query))) {
350 &ERROR("RawReturn: !prepare => '$query'");
355 if (!$sth->execute) {
356 &ERROR("RawReturn: !execute => '$query'");
362 while (my @row = $sth->fetchrow_array) {
363 push(@retval, $row[0]);
371 ####################################################################
372 ##### Misc DBI stuff...
378 if (ref($href) ne "HASH") {
379 &WARN("hashref2where: href is not HASH ref.");
383 my %hash = %{ $href };
384 foreach (keys %hash) {
387 if (s/^-//) { # as is.
389 delete $hash{'-'.$_};
391 $hash{$_} = &sqlQuote($v);
395 return join(' AND ', map { $_."=".$hash{$_} } keys %hash );
401 if (ref($href) ne "HASH") {
402 &WARN("hashref2update: href is not HASH ref.");
407 foreach (keys %{ $href }) {
409 my $v = ${ $href }{$_};
411 # is there a better way to do this?
412 if ($k =~ s/^-//) { # as is.
421 return join(', ', map { $_ => $hash{$_} } sort keys %hash);
427 if (ref($href) ne "HASH") {
428 &WARN("hashref2update: href is not HASH ref.");
433 foreach (keys %{ $href }) {
435 my $v = ${ $href }{$_};
437 # is there a better way to do this?
438 if ($k =~ s/^-//) { # as is.
452 # Usage: &countKeys($table, [$col]);
454 my ($table, $col) = @_;
456 &DEBUG("&countKeys($table, $col);");
458 return (&sqlRawReturn("SELECT count($col) FROM $table"))[0];
462 # Usage: &sumKey($table, $col);
464 my ($table, $col) = @_;
466 return (&sqlRawReturn("SELECT sum($col) FROM $table"))[0];
470 # Usage: &randKey($table, $select);
472 my ($table, $select) = @_;
473 my $rand = int(rand(&countKeys($table) - 1));
474 my $query = "SELECT $select FROM $table LIMIT $rand,1";
475 if ($param{DBType} =~ /^pg/i) {
476 $query =~ s/$rand,1/1,$rand/;
479 my $sth = $dbh->prepare($query);
481 &WARN("randKey($query)") unless $sth->execute;
482 my @retval = $sth->fetchrow_array;
489 # Usage: &deleteTable($table);
491 &sqlRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
495 # Usage: &searchTable($table, $select, $key, $str);
496 # Note: searchTable does dbQuote.
498 my($table, $select, $key, $str) = @_;
502 # allow two types of wildcards.
503 if ($str =~ /^\^(.*)\$$/) {
504 &DEBUG("searchTable: should use dbGet(), heh.");
507 $str .= "%" if ($str =~ s/^\^//);
508 $str = "%".$str if ($str =~ s/\$$//);
509 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
513 $str =~ s/\?/_/g; # '.' should be supported, too.
517 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
519 my $sth = $dbh->prepare($query);
522 if (!$sth->execute) {
523 &WARN("Search($query)");
528 while (my @row = $sth->fetchrow_array) {
529 push(@results, $row[0]);
538 my(@path) = ($bot_data_dir, ".","..","../..");
543 my $file = "$_/setup/$table.sql";
544 &DEBUG("dbCT: table => '$table', file => '$file'");
545 next unless ( -f $file );
547 &DEBUG("dbCT: found!!!");
562 &sqlRaw("dbcreateTable($table)", $data);
568 my $database_exists = 0;
571 if ($param{DBType} =~ /^mysql$/i) {
572 my $sql = "SHOW DATABASES";
573 foreach ( &sqlRawReturn($sql) ) {
574 $database_exists++ if ($_ eq $param{'DBName'});
577 unless ($database_exists) {
578 &status("Creating database $param{DBName}...");
579 my $query = "CREATE DATABASE $param{DBName}";
580 &sqlRaw("create(db $param{DBName})", $query);
583 # retrieve a list of db's from the server.
584 foreach ($dbh->func('_ListTables')) {
588 } elsif ($param{DBType} =~ /^SQLite$/i) {
590 # retrieve a list of db's from the server.
591 foreach ( &sqlRawReturn("SELECT name FROM sqlite_master WHERE type='table'") ) {
596 if (!scalar keys %db) {
597 &status("Creating database $param{'DBName'}...");
598 my $query = "CREATE DATABASE $param{'DBName'}";
599 &sqlRaw("create(db $param{'DBName'})", $query);
603 foreach ( qw(factoids freshmeat rootwarn seen stats botmail) ) {
604 next if (exists $db{$_});
605 &status("checkTables: creating new table $_...");