major re-organisation of the different SQL files; cleanup and fixing of the postgres...

[infobot.git] / src / dbi.pl

diff --git a/src/dbi.pl b/src/dbi.pl
index f5565539924fc0eefa1dc70711de4be974384f72..76954e70366df98f4393f550be4c988f7fa5cd4a 100644 (file)
--- 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";
@@ -30,7 +30,12 @@ sub sqlOpenDB {
     my $hoststr = "";
     # SQLHost should be unset for SQLite
     if (exists $param{'SQLHost'} and $param{'SQLHost'}) {
-       $dsn    .= ":$param{SQLHost}";
+       # PostgreSQL requires ";" and keyword "host". See perldoc Pg -- troubled
+       if ($type eq "Pg") {
+               $dsn    .= ";host=$param{SQLHost}";
+       } else {
+               $dsn    .= ":$param{SQLHost}";
+       }
        $hoststr = " to $param{'SQLHost'}";
     }
     # SQLite ignores $user and $pass
@@ -278,6 +283,7 @@ sub sqlUpdate {
 sub sqlInsert {
     my ($table, $data_href, $other) = @_;
     # note: if $other == 1, add "DELAYED" to function instead.
+    # note: ^^^ doesnt actually do anything lol. Need code to s/1/DELAYED/ below -- troubled
 
     if (!defined $data_href or ref($data_href) ne "HASH") {
        &WARN("sqlInsert: data_href == NULL.");
@@ -293,18 +299,16 @@ sub sqlInsert {
        return;
     }
 
-    &sqlRaw("Insert($table)", sprintf(
+    return &sqlRaw("Insert($table)", sprintf(
        "INSERT %s INTO %s (%s) VALUES (%s)",
        ($other || ""), $table, join(',',@k), join(',',@v)
     ) );
-
-    return 1;
 }
 
 #####
-# Usage: &sqlReplace($table, $data_href);
+# Usage: &sqlReplace($table, $data_href, [$pkey]);
 sub sqlReplace {
-    my ($table, $data_href) = @_;
+    my ($table, $data_href, $pkey) = @_;
 
     if (!defined $data_href or ref($data_href) ne "HASH") {
        &WARN("sqlReplace: data_href == NULL.");
@@ -320,10 +324,29 @@ sub sqlReplace {
        return;
     }
 
-    &sqlRaw("Replace($table)", sprintf(
-       "REPLACE INTO %s (%s) VALUES (%s)",
-       $table, join(',',@k), join(',',@v)
-    ) );
+
+    if ($param{'DBType'} =~ /^pgsql$/i) {
+       # OK, heres the scoop. There is currently no REPLACE INTO in Pgsql.
+       # However, the bot already seems to search for factoids before insert
+       # anyways. Perhaps we could change this to a generic INSERT INTO so
+       # we can skip the seperate sql? -- troubled to: TimRiker
+       # PGSql syntax: UPDATE table SET key = 'value', key2 = 'value2' WHERE key = 'value'
+
+#      &sqlRaw("Replace($table)", sprintf(
+#              "INSERT INTO %s (%s) VALUES (%s)",
+#              $table, join(',',@k), join(',',@v)
+#      ));
+       &WARN("DEBUG: ($pkey = ) " . sprintf(
+                "REPLACE INTO %s (%s) VALUES (%s)",
+                $table, join(',',@k), join(',',@v)
+        )); 
+
+    } else {
+       &sqlRaw("Replace($table)", sprintf(
+               "REPLACE INTO %s (%s) VALUES (%s)",
+               $table, join(',',@k), join(',',@v)
+       ));
+    }
 
     return 1;
 }
@@ -514,9 +537,12 @@ sub sumKey {
 # Usage: &randKey($table, $select);
 sub randKey {
     my ($table, $select) = @_;
-    my $rand   = int(rand(&countKeys($table) - 1));
+    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;
@@ -575,18 +601,20 @@ sub searchTable {
 }
 
 sub sqlCreateTable {
-    my($table) = @_;
+    my($table, $dbtype)        = @_;
     my(@path)  = ($bot_data_dir, ".","..","../..");
     my $found  = 0;
     my $data;
+    $dbtype = lc $dbtype;
 
     foreach (@path) {
-       my $file = "$_/setup/$table.sql";
+       my $file = "$_/setup/$dbtype/$table.sql";
        next unless ( -f $file );
 
        open(IN, $file);
        while (<IN>) {
            chop;
+           next if $_ =~ /^--/;
            $data .= $_;
        }
 
@@ -626,7 +654,7 @@ sub checkTables {
        &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'") ) {
@@ -634,9 +662,36 @@ sub checkTables {
        }
 
        # create database not needed for SQLite
+
+    } elsif ($param{DBType} =~ /^pgsql$/i) {
+       # $sql_showDB = SQL to select the DB list
+       # $sql_showTBL = SQL to select all tables for the current connection
+
+       my $sql_showDB = "SELECT datname FROM pg_database";
+       my $sql_showTBL = "SELECT tablename FROM pg_tables \
+               WHERE schemaname = 'public'";
+
+       foreach ( &sqlRawReturn($sql_showDB) ) {
+               $database_exists++ if ($_ eq $param{'DBName'});
+       }
+
+       unless ($database_exists) {
+               &status("Creating PostgreSQL database $param{'DBName'}");
+               &status("(actually, not really, please read the INSTALL file)");
+       }
+
+        # retrieve a list of db's from the server. This code is from mysql above, please check -- troubled
+        my @tables = map {s/^\`//; s/\`$//; $_;} &sqlRawReturn($sql_showTBL);
+        if ($#tables == -1){
+            @tables = $dbh->tables;
+        }
+        &status("Tables: ".join(',',@tables));
+        @db{@tables} = (1) x @tables;
+ 
+
     }
 
-    foreach ( qw(botmail factoids rootwarn seen stats) ) {
+    foreach ( qw(botmail connections factoids rootwarn seen stats onjoin) ) {
        if (exists $db{$_}) {
            $cache{has_table}{$_} = 1;
            next;
@@ -646,7 +701,7 @@ sub checkTables {
 
        $cache{create_table}{$_} = 1;
 
-       &sqlCreateTable($_);
+       &sqlCreateTable($_, $param{DBType});
     }
 }