- news: latest: use $who instead of $::who - need a standard!

[infobot.git] / src / db_mysql.pl

diff --git a/src/db_mysql.pl b/src/db_mysql.pl
index d5f7b43f9960b878dcf3e807531a211160e32cb4..1e97faa08ff057b1a08296db7683f1d1014fd0de 100644 (file)
--- a/src/db_mysql.pl
+++ b/src/db_mysql.pl
@@ -46,16 +46,20 @@ sub dbGet {
     my $query   = "SELECT $select FROM $table";
     $query     .= " WHERE $where" if ($where);
 
+    if (!defined $select) {
+       &WARN("dbGet: select == NULL. table => $table");
+       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");
+       &ERROR("Get: execute: '$query'");
        $sth->finish;
        return 0;
     }
@@ -74,26 +78,42 @@ 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;
 
+    &DEBUG("dbGetCol: query => '$query'.");
+
     my $sth = $dbh->prepare($query);
     &SQLDebug($query);
     if (!$sth->execute) {
-       &ERROR("GetCol => '$query'");
-       &ERROR("GetCol => $DBI::errstr");
+       &ERROR("GetCol: execute: '$query'");
+#      &ERROR("GetCol => $DBI::errstr");
        $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];
@@ -132,18 +152,45 @@ sub dbGetColInfo {
 }
 
 #####
-# Usage: &dbSet($table, $primkey, $primval, $key, $val);
+# Usage: &dbSet($table, $primhash_ref, $hash_ref);
 sub dbSet {
-    my ($table, $primkey, $primval, $key, $val) = @_;
-    my $query;
+    my ($table, $phref, $href) = @_;
+    my $where = join(' AND ', map {
+               $_."=".&dbQuote($phref->{$_})
+       } keys %{$phref}
+    );
 
-    my $result = &dbGet($table, $primkey, "$primkey='$primval'");
+    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 $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);
@@ -208,6 +255,11 @@ sub dbReplace {
        }
     }
 
+    if (0) {
+       &DEBUG("REPLACE INTO $table (".join(',',@keys).
+               ") VALUES (". join(',',@vals). ")" );
+    }
+
     &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
                ") VALUES (". join(',',@vals). ")"
     );