- modified db_mysql to allow eleet usage of dbSet

[infobot.git] / src / db_mysql.pl

diff --git a/src/db_mysql.pl b/src/db_mysql.pl
index 754bcdab70513de474e122caec07c0e1f1fb0fdf..38b12296f6a88aa3c78156692fe0d60dfba52ea4 100644 (file)
--- a/src/db_mysql.pl
+++ b/src/db_mysql.pl
@@ -18,6 +18,7 @@ sub openDB {
        &status("Opened MySQL connection to $param{'SQLHost'}");
     } else {
        &ERROR("cannot connect to $param{'SQLHost'}.");
+       &ERROR("since mysql is not available, shutting down bot!");
        &shutdown();
        &closePID();
        exit 1;
@@ -39,22 +40,21 @@ 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);
 
     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;
     }
@@ -82,8 +82,8 @@ sub dbGetCol {
     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;
     }
@@ -105,8 +105,8 @@ sub dbGetCol {
 }
 
 ####
-# Usage: &dbGetRowInfo($table);
-sub dbGetRowInfo {
+# Usage: &dbGetColInfo($table);
+sub dbGetColInfo {
     my ($table) = @_;
 
     my $query = "SHOW COLUMNS from $table";
@@ -131,18 +131,45 @@ sub dbGetRowInfo {
 }
 
 #####
-# 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, join(',', keys %{$phref}), $where);
+
+    my(@keys,@vals);
+    foreach (keys %{$href}) {
+       push(@keys, $_);
+       push(@vals, &dbQuote($href->{$_}) );
+    }
 
-    my $result = &dbGet($table,$primkey,$primval,$primkey);
+    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);
@@ -170,15 +197,21 @@ sub dbUpdate {
 #####
 # Usage: &dbInsert($table, $primkey, %hash);
 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).")"
     );
 
@@ -186,20 +219,23 @@ sub dbInsert {
 }
 
 #####
-# Usage: &dbReplace($table, $primkey, $primval, %hash);
+# Usage: &dbReplace($table, %hash);
 sub dbReplace {
-    my ($table, $primkey, $primval, %hash) = @_;
+    my ($table, %hash) = @_;
     my (@keys, @vals);
 
     foreach (keys %hash) {
-       &DEBUG("hash{$_} => $hash{$_}");
-       push(@keys, $_);
-       push(@vals, &dbQuote($hash{$_}));
+       if (s/^-//) {   # as is.
+           push(@keys, $_);
+           push(@vals, $hash{'-'.$_});
+       } else {
+           push(@keys, $_);
+           push(@vals, &dbQuote($hash{$_}));
+       }
     }
 
     &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
-               ") VALUES (".join(',',@vals).") WHERE $primkey=".
-               &dbQuote($primval)
+               ") VALUES (". join(',',@vals). ")"
     );
 
     return 1;
@@ -207,14 +243,15 @@ sub dbReplace {
 
 #####
 # Usage: &dbSetRow($table, @values);
-sub dbSetRow {
-    my ($table, @values) = @_;
+sub dbSetRow ($@$) {
+    my ($table, @values, $delay) = @_;
+    my $p      = ($delay) ? " DELAYED " : "";
 
     foreach (@values) {
        $_ = &dbQuote($_);
     }
 
-    return &dbRaw("SetRow", "INSERT INTO $table VALUES (".
+    return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
        join(",", @values) .")" );
 }
 
@@ -240,10 +277,12 @@ sub dbRaw {
        return 0;
     }
 
+#    &DEBUG("query => '$query'.");
+
     &SQLDebug($query);
     if (!$sth->execute) {
        &ERROR("Raw($prefix): => '$query'");
-#      &ERROR("Raw($prefix): $DBI::errstr");
+       # $DBI::errstr is printed as warning automatically.
        $sth->finish;
        return 0;
     }
@@ -274,11 +313,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];
+}
+
+# Usage: &sumKey($table, $col);
+sub sumKey {
+    my ($table, $col) = @_;
+
+    return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
 }
 
 ##### NOT USED.
@@ -364,7 +411,7 @@ sub searchTable {
 #####
 # Usage: &getFactInfo($faqtoid, type);
 sub getFactInfo {
-    return &dbGet("factoids", "factoid_key", $_[0], $_[1]);
+    return &dbGet("factoids", $_[1], "factoid_key='$_[0]'");
 }
 
 #####
@@ -392,4 +439,58 @@ sub SQLDebug {
     print SQLDEBUG $_[0]."\n";
 }
 
+sub dbCreateTable {
+    my($table) = @_;
+    my(@path)  = (".","..","../..");
+    my $found  = 0;
+    my $data;
+
+    foreach (@path) {
+       my $file = "$_/setup/$table.sql";
+       &DEBUG("dbCT: file => $file");
+       next unless ( -f $file );
+
+       &DEBUG("found!!!");
+
+       open(IN, $file);
+       while (<IN>) {
+           chop;
+           $data .= $_;
+       }
+
+       $found++;
+       last;
+    }
+
+    if (!$found) {
+       return 0;
+    } else {
+       &dbRaw("create($table)", $data);
+       return 1;
+    }
+}
+
+sub checkTables {
+    # retrieve a list of db's from the server.
+    my %db;
+    foreach ($dbh->func('_ListTables')) {
+       $db{$_} = 1;
+    }
+
+    # 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;