X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=src%2Fdbi.pl;h=99d115e4d95d81a3f471faeaff568be27e2f4d39;hb=4a83aa7e972adb3187deed477d570d46412b9327;hp=d3c5aabf94422dd7c4e2a555939225f39d324202;hpb=7d976846e05943670ef2564fbf70124d181a8f87;p=infobot.git

diff --git a/src/dbi.pl b/src/dbi.pl
index d3c5aab..99d115e 100644
--- 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;
 }
@@ -167,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) {
@@ -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);
@@ -500,7 +498,6 @@ sub hashref2array {
 sub countKeys {
     my ($table, $col) = @_;
     $col ||= "*";
-    &DEBUG("&countKeys($table, $col);");
 
     return (&sqlRawReturn("SELECT count($col) FROM $table"))[0];
 }
@@ -517,12 +514,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;
@@ -540,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;
@@ -548,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/^\^//);
@@ -561,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);
 
@@ -588,11 +585,8 @@ sub sqlCreateTable {
 
     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;
@@ -628,29 +622,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 freshmeat rootwarn seen stats botmail) ) {
-	next if (exists $db{$_});
+    foreach ( qw(botmail connections factoids rootwarn seen stats) ) {
+	if (exists $db{$_}) {
+	    $cache{has_table}{$_} = 1;
+	    next;
+	}
+
 	&status("checkTables: creating new table $_...");
 
+	$cache{create_table}{$_} = 1;
+
 	&sqlCreateTable($_);
     }
 }