- moved scripts/setup_sql.pl to src/db_mysql as &createTables()

[infobot.git] / src / db_mysql.pl

diff --git a/src/db_mysql.pl b/src/db_mysql.pl
index e8055569fba60964b27aa603b4530dec3a30e9d1..eaeb9cd0063eaa75f798a628a072070897d1fae3 100644 (file)
--- a/src/db_mysql.pl
+++ b/src/db_mysql.pl
@@ -25,10 +25,7 @@ sub openDB {
 }
 
 sub closeDB {
-    if (!$dbh) {
-       &WARN("closeDB: connection already closed?");
-       return 0;
-    }
+    return 0 unless ($dbh);
 
     &status("Closed MySQL connection to $param{'SQLHost'}.");
     $dbh->disconnect();
@@ -58,7 +55,8 @@ sub dbGet {
     if (!$sth->execute) {
        &ERROR("Get => '$query'");
        &ERROR("Get => $DBI::errstr");
-       return;
+       $sth->finish;
+       return 0;
     }
 
     my @retval = $sth->fetchrow_array;
@@ -86,6 +84,7 @@ sub dbGetCol {
     if (!$sth->execute) {
        &ERROR("GetCol => '$query'");
        &ERROR("GetCol => $DBI::errstr");
+       $sth->finish;
        return;
     }
 
@@ -106,8 +105,8 @@ sub dbGetCol {
 }
 
 ####
-# Usage: &dbGetRowInfo($table);
-sub dbGetRowInfo {
+# Usage: &dbGetColInfo($table);
+sub dbGetColInfo {
     my ($table) = @_;
 
     my $query = "SHOW COLUMNS from $table";
@@ -118,6 +117,8 @@ sub dbGetRowInfo {
     if (!$sth->execute) {
        &ERROR("GRI => '$query'");
        &ERROR("GRI => $DBI::errstr");
+       $sth->finish;
+       return;
     }
 
     my @cols;
@@ -169,31 +170,56 @@ 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).")"
     );
 
     return 1;
 }
 
+#####
+# Usage: &dbReplace($table, $primkey, $primval, %hash);
+sub dbReplace {
+    my ($table, $primkey, $primval, %hash) = @_;
+    my (@keys, @vals);
+
+    foreach (keys %hash) {
+       push(@keys, $_);
+       push(@vals, &dbQuote($hash{$_}));
+    }
+
+    &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
+               ") VALUES (". join(',',@vals). ")"
+    );
+
+    return 1;
+}
+
 #####
 # 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) .")" );
 }
 
@@ -222,7 +248,8 @@ sub dbRaw {
     &SQLDebug($query);
     if (!$sth->execute) {
        &ERROR("Raw($prefix): => '$query'");
-       &ERROR("Raw($prefix): $DBI::errstr");
+#      &ERROR("Raw($prefix): $DBI::errstr");
+       $sth->finish;
        return 0;
     }
 
@@ -252,11 +279,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.
@@ -357,7 +392,7 @@ sub delFactoid {
     my ($faqtoid) = @_;
 
     &dbDel("factoids", "factoid_key",$faqtoid);
-    &status("DELETED $faqtoid");
+    &status("DELETED '$faqtoid'");
 
     return 1;
 }
@@ -370,4 +405,55 @@ 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);
+       $data = <IN>;
+
+       $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", "karma", "rootwarn", "seen",
+    ) {
+       next if (exists $db{$_});
+       &status("  creating new table $_...");
+
+       &dbCreateTable($_);
+    }
+}
+
 1;