don't lart myself

[infobot.git] / src / dbi.pl

diff --git a/src/dbi.pl b/src/dbi.pl
index f9f731930b5b39d9db54e997fe69506be77895f4..bb3c396c9b38df6a134d1074b73cbdeae7777999 100644 (file)
--- a/src/dbi.pl
+++ b/src/dbi.pl
@@ -19,7 +19,7 @@ package main;
 sub sqlOpenDB {
     my ($db, $type, $user, $pass, $no_fail) = @_;
     # this is a mess. someone fix it, please.
-    if ($type =~ /^SQLite$/i) {
+    if ($type =~ /^SQLite(2)?$/i) {
        $db = "dbname=$db.sqlite";
     } elsif ($type =~ /^pg/i) {
        $db = "dbname=$db";
@@ -88,12 +88,11 @@ sub sqlSelectMany {
        return;
     }
 
-    $query .= " WHERE" if (($where_href) || ($other));
     if ($where_href) {
        my $where = &hashref2where($where_href);
-       $query .= " $where" if ($where);
+       $query .= " WHERE $where" if ($where);
     }
-    $query .= " $other"                if $other;
+    $query .= " $other"        if ($other);
 
     if (!($sth = $dbh->prepare($query))) {
        &ERROR("sqlSelectMany: prepare: $DBI::errstr");
@@ -101,10 +100,8 @@ sub sqlSelectMany {
     }
 
     &SQLDebug($query);
-    if (!$sth->execute) {
-       &ERROR("sqlSelectMany: execute: '$query'");
-       return;
-    }
+
+    return if (!$sth->execute);
 
     return $sth;
 }
@@ -240,7 +237,8 @@ sub sqlSet {
     my $result = &sqlSelect($table, $k, $where_href);
 #    &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 {
        # hack.
@@ -264,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);
@@ -295,12 +293,10 @@ sub sqlInsert {
        return;
     }
 
-    &sqlRaw("Insert($table)", sprintf(
+    return &sqlRaw("Insert($table)", sprintf(
        "INSERT %s INTO %s (%s) VALUES (%s)",
        ($other || ""), $table, join(',',@k), join(',',@v)
     ) );
-
-    return 1;
 }
 
 #####
@@ -516,12 +512,12 @@ sub sumKey {
 # Usage: &randKey($table, $select);
 sub randKey {
     my ($table, $select) = @_;
-    my $rand   = int(rand(&countKeys($table) - 1));
-    my $query  = "SELECT $select FROM $table LIMIT $rand,1";
-    if ($param{DBType} =~ /^pg/i) {
-       $query =~ s/$rand,1/1,$rand/;
+    my $rand   = int(rand(&countKeys($table)));
+    my $query  = "SELECT $select FROM $table LIMIT 1 OFFSET $rand";
+    if ($param{DBType} =~ /^mysql$/i) {
+       # WARN: only newer MySQL supports "LIMIT limit OFFSET offset"
+       $query = "SELECT $select FROM $table LIMIT $rand,1";
     }
-
     my $sth    = $dbh->prepare($query);
     &SQLDebug($query);
     &WARN("randKey($query)") unless $sth->execute;
@@ -560,7 +556,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);
 
@@ -624,29 +620,33 @@ sub checkTables {
        }
 
        # retrieve a list of db's from the server.
-       foreach ($dbh->func('_ListTables')) {
-           $db{$_} = 1;
+       my @tables = map {s/^\`//; s/\`$//; $_;} $dbh->func('_ListTables');
+       if ($#tables == -1){
+           @tables = $dbh->tables;
        }
+       &status("Tables: ".join(',',@tables));
+       @db{@tables} = (1) x @tables;
 
-    } elsif ($param{DBType} =~ /^SQLite$/i) {
+    } elsif ($param{DBType} =~ /^SQLite(2)?$/i) {
 
        # retrieve a list of db's from the server.
        foreach ( &sqlRawReturn("SELECT name FROM sqlite_master WHERE type='table'") ) {
            $db{$_} = 1;
        }
 
-       # create database.
-       if (!scalar keys %db) {
-           &status("Creating database $param{'DBName'}...");
-           my $query = "CREATE DATABASE $param{'DBName'}";
-           &sqlRaw("create(db $param{'DBName'})", $query);
-       }
+       # create database not needed for SQLite
     }
 
-    foreach ( qw(factoids rootwarn seen stats botmail) ) {
-       next if (exists $db{$_});
+    foreach ( qw(botmail connections factoids rootwarn seen stats onjoin) ) {
+       if (exists $db{$_}) {
+           $cache{has_table}{$_} = 1;
+           next;
+       }
+
        &status("checkTables: creating new table $_...");
 
+       $cache{create_table}{$_} = 1;
+
        &sqlCreateTable($_);
     }
 }