X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=src%2Fdb_mysql.pl;h=1b3f3dc617a23ecaaf09fe970302d11ec0c556d3;hb=884b215a800302006deeb627317334a73f5ea741;hp=658e91400e938061496c008160c6cac8c7ee5eca;hpb=2f7e57eff44a1731cf4114ef02bdd9aa292729f6;p=infobot.git

diff --git a/src/db_mysql.pl b/src/db_mysql.pl
index 658e914..1b3f3dc 100644
--- a/src/db_mysql.pl
+++ b/src/db_mysql.pl
@@ -18,20 +18,21 @@ sub openDB {
 	&status("Opened MySQL connection to $param{'SQLHost'}");
     } else {
 	&ERROR("cannot connect to $param{'SQLHost'}.");
-	&shutdown();
+	&ERROR("since mysql is not available, shutting down bot!");
 	&closePID();
+	&closeSHM($shm);
+	&closeLog();
+
 	exit 1;
     }
 }
 
 sub closeDB {
-    if (!$dbh) {
-	&WARN("closeDB: connection already closed?");
-	return 0;
-    }
+    return 0 unless ($dbh);
 
     &status("Closed MySQL connection to $param{'SQLHost'}.");
     $dbh->disconnect();
+
     return 1;
 }
 
@@ -42,24 +43,33 @@ sub dbQuote {
 }
 
 #####
-# Usage: &dbGet($table, $primkey, $primval, $select);
+# Usage: &dbGet($table, $select, $where);
 sub dbGet {
-    my ($table, $primkey, $primval, $select) = @_;
-    my $query = "SELECT $select FROM $table WHERE $primkey=". 
-		&dbQuote($primval);
+    my ($table, $select, $where) = @_;
+    my $query	= "SELECT $select FROM $table";
+    $query	.= " WHERE $where" if ($where);
+
+    if (!defined $select or $select =~ /^\s*$/) {
+	&WARN("dbGet: select == NULL.");
+	return;
+    }
+
+    if (!defined $table or $table =~ /^\s*$/) {
+	&WARN("dbGet: table == NULL.");
+	return;
+    }
 
     my $sth;
     if (!($sth = $dbh->prepare($query))) {
-	&ERROR("Get: $DBI::errstr");
+	&ERROR("Get: prepare: $DBI::errstr");
 	return;
     }
 
     &SQLDebug($query);
     if (!$sth->execute) {
-	&ERROR("Get => '$query'");
-	&ERROR("Get => $DBI::errstr");
-	&SQLDebug($DBI::errstr);
-	return;
+	&ERROR("Get: execute: '$query'");
+	$sth->finish;
+	return 0;
     }
 
     my @retval = $sth->fetchrow_array;
@@ -76,24 +86,39 @@ sub dbGet {
 }
 
 #####
-# Usage: &dbGetCol($table, $primkey, $key, [$type]);
+# Usage: &dbGetCol($table, $select, $where, [$type]);
 sub dbGetCol {
-    my ($table, $primkey, $key, $type) = @_;
-    my $query = "SELECT $primkey,$key FROM $table WHERE $key IS NOT NULL";
+    my ($table, $select, $where, $type) = @_;
+    my $query	= "SELECT $select FROM $table";
+    $query	.= " WHERE ".$where if ($where);
     my %retval;
 
     my $sth = $dbh->prepare($query);
     &SQLDebug($query);
     if (!$sth->execute) {
-	&ERROR("GetCol => '$query'");
-	&SQLDebug($DBI::errstr);
+	&ERROR("GetCol: execute: '$query'");
+	$sth->finish;
+	return;
     }
 
-    if (defined $type and $type == 1) {
+    if (defined $type and $type == 2) {
+	&DEBUG("dbgetcol: type 2!");
+	while (my @row = $sth->fetchrow_array) {
+	    $retval{$row[0]} = join(':', $row[1..$#row]);
+	}
+	&DEBUG("dbgetcol: count => ".scalar(keys %retval) );
+
+    } elsif (defined $type and $type == 1) {
 	while (my @row = $sth->fetchrow_array) {
 	    # reverse it to make it easier to count.
-	    $retval{$row[1]}{$row[0]} = 1;
+	    if (scalar @row == 2) {
+		$retval{$row[1]}{$row[0]} = 1;
+	    } elsif (scalar @row == 3) {
+		$retval{$row[1]}{$row[0]} = 1;
+	    }
+	    # what to do if there's only one or more than 3?
 	}
+
     } else {
 	while (my @row = $sth->fetchrow_array) {
 	    $retval{$row[0]} = $row[1];
@@ -105,9 +130,34 @@ sub dbGetCol {
     return %retval;
 }
 
+#####
+# Usage: &dbGetColNiceHash($table, $select, $where);
+sub dbGetColNiceHash {
+    my ($table, $select, $where) = @_;
+    $select	||= "*";
+    my $query	= "SELECT $select FROM $table";
+    $query	.= " WHERE ".$where if ($where);
+    my %retval;
+
+    my $sth = $dbh->prepare($query);
+    &SQLDebug($query);
+    if (!$sth->execute) {
+	&ERROR("GetColNiceHash: execute: '$query'");
+#	&ERROR("GetCol => $DBI::errstr");
+	$sth->finish;
+	return;
+    }
+
+    %retval = %{ $sth->fetchrow_hashref() };
+
+    $sth->finish;
+
+    return %retval;
+}
+
 ####
-# Usage: &dbGetRowInfo($table);
-sub dbGetRowInfo {
+# Usage: &dbGetColInfo($table);
+sub dbGetColInfo {
     my ($table) = @_;
 
     my $query = "SHOW COLUMNS from $table";
@@ -117,7 +167,9 @@ sub dbGetRowInfo {
     &SQLDebug($query);
     if (!$sth->execute) {
 	&ERROR("GRI => '$query'");
-	&SQLDebug($DBI::errstr);
+	&ERROR("GRI => $DBI::errstr");
+	$sth->finish;
+	return;
     }
 
     my @cols;
@@ -130,18 +182,61 @@ sub dbGetRowInfo {
 }
 
 #####
-# Usage: &dbSet($table, $primkey, $primval, $key, $val);
+# Usage: &dbSet($table, $primhash_ref, $hash_ref);
+#  Note: dbSet does dbQuote.
 sub dbSet {
-    my ($table, $primkey, $primval, $key, $val) = @_;
-    my $query;
+    my ($table, $phref, $href) = @_;
+    my $where = join(' AND ', map {
+		$_."=".&dbQuote($phref->{$_})
+	} keys %{$phref}
+    );
+
+    if (!defined $phref) {
+	&WARN("dbset: phref == NULL.");
+	return;
+    }
+
+    if (!defined $href) {
+	&WARN("dbset: href == NULL.");
+	return;
+    }
+
+    if (!defined $table) {
+	&WARN("dbset: table == NULL.");
+	return;
+    }
+
+    my $result = &dbGet($table, join(',', keys %{$phref}), $where);
+
+    my(@keys,@vals);
+    foreach (keys %{$href}) {
+	push(@keys, $_);
+	push(@vals, &dbQuote($href->{$_}) );
+    }
+
+    if (!@keys or !@vals) {
+	&WARN("dbset: keys or vals is NULL.");
+	return;
+    }
 
-    my $result = &dbGet($table,$primkey,$primval,$primkey);
+    my $query;
     if (defined $result) {
-	$query = "UPDATE $table SET $key=".&dbQuote($val).
-		" WHERE $primkey=".&dbQuote($primval);
+	my @keyval;
+	for(my$i=0; $i<scalar @keys; $i++) {
+	    push(@keyval, $keys[$i]."=".$vals[$i] );
+	}
+
+	$query = "UPDATE $table SET ".
+		join(' AND ', @keyval).
+		" WHERE ".$where;
     } else {
-	$query = "INSERT INTO $table ($primkey,$key) VALUES (".
-		&dbQuote($primval).",".&dbQuote($val).")";
+	foreach (keys %{$phref}) {
+	    push(@keys, $_);
+	    push(@vals, &dbQuote($phref->{$_}) );
+	}
+
+	$query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
+		join(',',@keys), join(',',@vals) );
     }
 
     &dbRaw("Set", $query);
@@ -151,6 +246,7 @@ sub dbSet {
 
 #####
 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
+#  Note: dbUpdate does dbQuote.
 sub dbUpdate {
     my ($table, $primkey, $primval, %hash) = @_;
     my (@array);
@@ -168,16 +264,23 @@ sub dbUpdate {
 
 #####
 # Usage: &dbInsert($table, $primkey, %hash);
+#  Note: dbInsert does dbQuote.
 sub dbInsert {
-    my ($table, $primkey, %hash) = @_;
+    my ($table, $primkey, %hash, $delay) = @_;
     my (@keys, @vals);
+    my $p	= "";
+
+    if ($delay) {
+	&DEBUG("dbI: delay => $delay");
+	$p	= " DELAYED";
+    }
 
     foreach (keys %hash) {
 	push(@keys, $_);
 	push(@vals, &dbQuote($hash{$_}));
     }
 
-    &dbRaw("Insert($table)", "INSERT INTO $table (".join(',',@keys).
+    &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
 		") VALUES (".join(',',@vals).")"
     );
 
@@ -185,20 +288,59 @@ sub dbInsert {
 }
 
 #####
-# Usage: &dbSetRow($table, @values);
-sub dbSetRow {
-    my ($table, @values) = @_;
+# Usage: &dbReplace($table, $key, %hash);
+#  Note: dbReplace does optional dbQuote.
+sub dbReplace {
+    my ($table, $key, %hash) = @_;
+    my (@keys, @vals);
+
+    foreach (keys %hash) {
+	if (s/^-//) {	# as is.
+	    push(@keys, $_);
+	    push(@vals, $hash{'-'.$_});
+	} else {
+	    push(@keys, $_);
+	    push(@vals, &dbQuote($hash{$_}));
+	}
+    }
 
-    foreach (@values) {
-	$_ = &dbQuote($_);
+    if (0) {
+	&DEBUG("REPLACE INTO $table (".join(',',@keys).
+		") VALUES (". join(',',@vals). ")" );
     }
 
-    return &dbRaw("SetRow", "INSERT INTO $table VALUES (".
+    &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
+		") VALUES (". join(',',@vals). ")"
+    );
+
+    return 1;
+}
+
+#####
+# Usage: &dbSetRow($table, $vref, $delay);
+#  Note: dbSetRow does dbQuote.
+sub dbSetRow ($@$) {
+    my ($table, $vref, $delay) = @_;
+    my $p	= ($delay) ? " DELAYED " : "";
+
+    # see 'perldoc perlreftut'
+    my @values;
+    foreach (@{ $vref }) {
+	push(@values, &dbQuote($_) );
+    }
+
+    if (!scalar @values) {
+	&WARN("dbSetRow: values array == NULL.");
+	return;
+    }
+
+    return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
 	join(",", @values) .")" );
 }
 
 #####
 # Usage: &dbDel($table, $primkey, $primval, [$key]);
+#  Note: dbDel does dbQuote
 sub dbDel {
     my ($table, $primkey, $primval, $key) = @_;
 
@@ -219,11 +361,13 @@ sub dbRaw {
 	return 0;
     }
 
+#    &DEBUG("query => '$query'.");
+
     &SQLDebug($query);
     if (!$sth->execute) {
 	&ERROR("Raw($prefix): => '$query'");
-	&ERROR("Raw($prefix): $DBI::errstr");
-	&SQLDebug($DBI::errstr);
+	# $DBI::errstr is printed as warning automatically.
+	$sth->finish;
 	return 0;
     }
 
@@ -253,31 +397,19 @@ sub dbRawReturn {
 #####
 
 #####
-# Usage: &countKeys($table);
+# Usage: &countKeys($table, [$col]);
 sub countKeys {
-    my ($table) = @_;
+    my ($table, $col) = @_;
+    $col ||= "*";
 
-    return (&dbRawReturn("SELECT count(*) FROM $table"))[0];
+    return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
 }
 
-##### NOT USED.
-# Usage: &getKeys($table,$primkey);
-sub getKeys {
-    my ($table,$primkey) = @_;
-    my @retval;
-
-    my $query	= "SELECT $primkey FROM $table";
-    my $sth	= $dbh->prepare($query);
-
-    &SQLDebug($query);
-    &WARN("ERROR: getKeys($query)") unless $sth->execute;
+# Usage: &sumKey($table, $col);
+sub sumKey {
+    my ($table, $col) = @_;
 
-    while (my @row = $sth->fetchrow_array) {
-	push(@retval, $row[0]);
-    }
-    $sth->finish;
-
-    return @retval;
+    return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
 }
 
 #####
@@ -302,7 +434,9 @@ sub deleteTable {
     &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
 }
 
+#####
 # Usage: &searchTable($table, $select, $key, $str);
+#  Note: searchTable does dbQuote.
 sub searchTable {
     my($table, $select, $key, $str) = @_;
     my $origStr = $str;
@@ -326,7 +460,10 @@ sub searchTable {
 		&dbQuote($str);
     my $sth = $dbh->prepare($query);
     &SQLDebug($query);
-    &WARN("Search($query)") unless $sth->execute;
+    if (!$sth->execute) {
+	&WARN("Search($query)");
+	return;
+    }
 
     while (my @row = $sth->fetchrow_array) {
 	push(@results, $row[0]);
@@ -336,37 +473,69 @@ sub searchTable {
     return @results;
 }
 
-####################################################################
-##### Factoid related stuff...
-#####
+sub dbCreateTable {
+    my($table)	= @_;
+    my(@path)	= ($bot_data_dir, ".","..","../..");
+    my $found	= 0;
+    my $data;
 
-#####
-# Usage: &getFactInfo($faqtoid, type);
-sub getFactInfo {
-    return &dbGet("factoids", "factoid_key", $_[0], $_[1]);
-}
+    foreach (@path) {
+	my $file = "$_/setup/$table.sql";
+	&DEBUG("dbCT: file => $file");
+	next unless ( -f $file );
 
-#####
-# Usage: &getFactoid($faqtoid);
-sub getFactoid {
-    return &getFactInfo($_[0], "factoid_value");
-}
+	&DEBUG("dbCT: found!!!");
 
-#####
-# Usage: &delFactoid($faqtoid);
-sub delFactoid {
-    my ($faqtoid) = @_;
+	open(IN, $file);
+	while (<IN>) {
+	    chop;
+	    $data .= $_;
+	}
 
-    &dbDel("factoids", "factoid_key",$faqtoid);
-    &status("DELETED $faqtoid");
+	$found++;
+	last;
+    }
 
-    return 1;
+    if (!$found) {
+	return 0;
+    } else {
+	&dbRaw("createTable($table)", $data);
+	return 1;
+    }
 }
 
-sub SQLDebug {
-    return unless (&IsParam("SQLDebug"));
+sub checkTables {
+    my $database_exists = 0;
+    foreach ( &dbRawReturn("SHOW DATABASES") ) {
+	$database_exists++ if ($_ eq $param{'DBName'});
+    }
+
+    unless ($database_exists) {
+	&status("Creating database $param{DBName}...");
+	$query = "CREATE DATABASE $param{DBName}";
+	&dbRaw("create(db $param{DBName})", $query);
+    }
+
+    # retrieve a list of db's from the server.
+    my %db;
+    foreach ($dbh->func('_ListTables')) {
+	$db{$_} = 1;
+    }
 
-    print SQLDEBUG $_[0]."\n";
+    # create database.
+    if (!scalar keys %db) {
+#	&status("Creating database $param{'DBName'}...");
+#	$query = "CREATE DATABASE $param{'DBName'}";
+#	&dbRaw("create(db $param{'DBName'})", $query);
+    }
+
+    foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
+    ) {
+	next if (exists $db{$_});
+	&status("  creating new table $_...");
+
+	&dbCreateTable($_);
+    }
 }
 
 1;