ws

[infobot.git] / src / dbi.pl

diff --git a/src/dbi.pl b/src/dbi.pl
index 893d80aba79d9bb0c8869b5f30fb64342edcd815..5071404081f3196205d72c7fd966e71c7bde0e3c 100644 (file)
--- a/src/dbi.pl
+++ b/src/dbi.pl
@@ -39,8 +39,8 @@ sub sqlOpenDB {
     if ($dbh && !$dbh->err) {
        &status("Opened $type connection$hoststr");
     } else {
-       &ERROR("cannot connect$hoststr.");
-       &ERROR("since $type is not available, shutting down bot!");
+       &ERROR("Cannot connect$hoststr.");
+       &ERROR("Since $type is not available, shutting down bot!");
        &ERROR( $dbh->errstr ) if ($dbh);
        &closePID();
        &closeSHM($shm);
@@ -88,9 +88,11 @@ sub sqlSelectMany {
        return;
     }
 
-    my $where = ($where_href) ? &hashref2where($where_href) : "";
-    $query .= " WHERE $where"  if ($where);
-    $query .= "$other"         if $other;
+    if ($where_href) {
+       my $where = &hashref2where($where_href);
+       $query .= " WHERE $where" if ($where);
+    }
+    $query .= " $other"        if ($other);
 
     if (!($sth = $dbh->prepare($query))) {
        &ERROR("sqlSelectMany: prepare: $DBI::errstr");
@@ -98,10 +100,8 @@ sub sqlSelectMany {
     }
 
     &SQLDebug($query);
-    if (!$sth->execute) {
-       &ERROR("sqlSelectMany: execute: '$query'");
-       return;
-    }
+
+    return if (!$sth->execute);
 
     return $sth;
 }
@@ -130,13 +130,33 @@ sub sqlSelect {
 }
 
 #####
-#  Usage: &sqlSelectColHash($table, $select, [$where_href], [$type]);
+#  Usage: &sqlSelectColArray($table, $select, [$where_href], [$other]);
+# Return: array.
+sub sqlSelectColArray {
+    my $sth    = &sqlSelectMany(@_);
+    my @retval;
+
+    if (!defined $sth) {
+       &WARN("sqlSelect failed.");
+       return;
+    }
+
+    while (my @row = $sth->fetchrow_array) {
+       push(@retval, $row[0]);
+    }
+    $sth->finish;
+
+    return @retval;
+}
+
+#####
+#  Usage: &sqlSelectColHash($table, $select, [$where_href], [$other], [$type]);
 # Return: type = 1: $retval{ col2 }{ col1 } = 1;
 # Return: no  type: $retval{ col1 } = col2;
 #   Note: does not support $other, yet.
 sub sqlSelectColHash {
-    my ($table, $select, $where_href, $type) = @_;
-    my $sth    = &sqlSelectMany($table, $select, $where_href);
+    my ($table, $select, $where_href, $other, $type) = @_;
+    my $sth    = &sqlSelectMany($table, $select, $where_href, $other);
     if (!defined $sth) {
        &WARN("sqlSelectColhash failed.");
        return;
@@ -144,11 +164,11 @@ sub sqlSelectColHash {
     my %retval;
 
     if (defined $type and $type == 2) {
-       &DEBUG("dbgetcol: type 2!");
+       &DEBUG("sqlSelectColHash: type 2!");
        while (my @row = $sth->fetchrow_array) {
            $retval{$row[0]} = join(':', $row[1..$#row]);
        }
-       &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
+       &DEBUG("sqlSelectColHash: count => ".scalar(keys %retval) );
 
     } elsif (defined $type and $type == 1) {
        while (my @row = $sth->fetchrow_array) {
@@ -212,15 +232,15 @@ sub sqlSet {
        return;
     }
 
-    my $k = (keys %{ $where_href })[0];
+    # any column can be NULL... so just get them all.
+    my $k = join(',', keys %{ $where_href } );
     my $result = &sqlSelect($table, $k, $where_href);
-    &DEBUG("result is not defined :(") if (!defined $result);
+#    &DEBUG("result is not defined :(") if (!defined $result);
 
-    if (1 or defined $result) {
+    # this was hardwired to use sqlUpdate. sqlite does not do inserts on sqlUpdate.
+    if (defined $result) {
        &sqlUpdate($table, $data_href, $where_href);
     } else {
-       &DEBUG("doing insert...");
-
        # hack.
        my %hash = %{ $where_href };
        # add data_href values...
@@ -242,7 +262,7 @@ sub sqlUpdate {
 
     if (!defined $data_href or ref($data_href) ne "HASH") {
        &WARN("sqlSet: data_href == NULL.");
-       return;
+       return 0;
     }
 
     my $where  = &hashref2where($where_href) if ($where_href);
@@ -379,7 +399,6 @@ sub sqlRawReturn {
        return 0;
     }
 
-
     while (my @row = $sth->fetchrow_array) {
        push(@retval, $row[0]);
     }
@@ -479,7 +498,6 @@ sub hashref2array {
 sub countKeys {
     my ($table, $col) = @_;
     $col ||= "*";
-    &DEBUG("&countKeys($table, $col);");
 
     return (&sqlRawReturn("SELECT count($col) FROM $table"))[0];
 }
@@ -519,7 +537,7 @@ sub deleteTable {
 
 #####
 # Usage: &searchTable($table, $select, $key, $str);
-#  Note: searchTable does dbQuote.
+#  Note: searchTable does sqlQuote.
 sub searchTable {
     my($table, $select, $key, $str) = @_;
     my $origStr = $str;
@@ -527,7 +545,7 @@ sub searchTable {
 
     # allow two types of wildcards.
     if ($str =~ /^\^(.*)\$$/) {
-       &DEBUG("searchTable: should use dbGet(), heh.");
+       &FIXME("searchTable: can't do \"$str\"");
        $str = $1;
     } else {
        $str .= "%"     if ($str =~ s/^\^//);
@@ -540,7 +558,7 @@ sub searchTable {
     $str =~ s/\*/%/g;
     # end of string fix.
 
-    my $query = "SELECT $select FROM $table WHERE $key LIKE ". 
+    my $query = "SELECT $select FROM $table WHERE $key LIKE ".
                &sqlQuote($str);
     my $sth = $dbh->prepare($query);
 
@@ -559,7 +577,7 @@ sub searchTable {
     return @results;
 }
 
-sub dbCreateTable {
+sub sqlCreateTable {
     my($table) = @_;
     my(@path)  = ($bot_data_dir, ".","..","../..");
     my $found  = 0;
@@ -567,11 +585,8 @@ sub dbCreateTable {
 
     foreach (@path) {
        my $file = "$_/setup/$table.sql";
-       &DEBUG("dbCT: table => '$table', file => '$file'");
        next unless ( -f $file );
 
-       &DEBUG("dbCT: found!!!");
-
        open(IN, $file);
        while (<IN>) {
            chop;
@@ -585,7 +600,7 @@ sub dbCreateTable {
     if (!$found) {
        return 0;
     } else {
-       &sqlRaw("dbcreateTable($table)", $data);
+       &sqlRaw("sqlCreateTable($table)", $data);
        return 1;
     }
 }
@@ -626,10 +641,16 @@ sub checkTables {
        }
     }
 
-    foreach ( qw(factoids freshmeat rootwarn seen stats botmail) ) {
-       next if (exists $db{$_});
+    foreach ( qw(factoids factoidsmisc rootwarn seen stats botmail) ) {
+       if (exists $db{$_}) {
+           $cache{has_table}{$_} = 1;
+           next;
+       }
+
        &status("checkTables: creating new table $_...");
 
+       $cache{create_table}{$_} = 1;
+
        &sqlCreateTable($_);
     }
 }