single line for now

[infobot.git] / src / db_pgsql.pl

diff --git a/src/db_pgsql.pl b/src/db_pgsql.pl
index 3e32930b7fd40a2935dbecd065eeb03c9e2f46d4..f7d91ae3016c69ae02e699ae58c636c33c6fb541 100644 (file)
--- a/src/db_pgsql.pl
+++ b/src/db_pgsql.pl
@@ -7,21 +7,30 @@
 
 if (&IsParam("useStrict")) { use strict; }
 
+#####
+# openDB($dbname, $sqluser, $sqlpass, $nofail);
 sub openDB {
-    my $connectstr="dbi:Pg:dbname=$param{DBName};";
-    $connectstr.=";host=$param{SQLHost}" if(defined $param{'SQLHost'});
-    $dbh = DBI->connect($connectstr, $param{'SQLUser'}, $param{'SQLPass'});
+    my($dbname, $sqluser, $sqlpass, $nofail) = @_;
+    my $connectstr = "dbi:Pg:dbname=$dbname;";
+    my $hoststr           = "";
+    if (exists $param{'SQLHost'} and $param{'SQLHost'}) {
+       $hoststr     = " to $param{'SQLHost'}";
+       $connectstr .= ";host=$param{SQLHost}";
+    }
+    $dbh = DBI->connect($connectstr, $sqluser, $sqlpass);
 
-    if (!$dbh->err) {
-       &status("Opened PgSQL connection to $param{'SQLHost'}");
+    if ($dbh and !$dbh->err) {
+       &status("Opened pgSQL connection$hoststr");
     } else {
-       &ERROR("cannot connect to $param{'SQLHost'}.");
-       &ERROR("pgSQL: ".$dbh->errstr);
+       &ERROR("cannot connect$hoststr.");
+       &ERROR("pgSQL: ".$dbh->errstr) if ($dbh);
 
        &closePID();
        &closeSHM($shm);
        &closeLog();
 
+       return 0 if ($nofail);
+
        exit 1;
     }
 }
@@ -29,7 +38,7 @@ sub openDB {
 sub closeDB {
     return 0 unless ($dbh);
 
-    &status("Closed pgSQL connection to $param{'SQLHost'}.");
+    &status("Closed pgSQL connection.");
     $dbh->disconnect();
 
     return 1;
@@ -147,11 +156,7 @@ sub dbGetColNiceHash {
        return;
     }
 
-    # todo: get column names, do $hash{$primkey}{blah} = ...
-    while (my @row = $sth->fetchrow_array) {
-       # todo: reverse it to make it easier to count.
-    }
-
+    %retval = %{ $sth->fetchrow_hashref() };
     $sth->finish;
 
     return %retval;
@@ -162,8 +167,8 @@ sub dbGetColNiceHash {
 sub dbGetColInfo {
     my ($table) = @_;
 
-#    my $query = "SELECT * FROM $table LIMIT 1;";
-    my $query = "SHOW COLUMNS from $table";
+    my $query = "SELECT * FROM $table LIMIT 1;";
+#    my $query = "SHOW COLUMNS from $table";
     my %retval;
 
     my $sth = $dbh->prepare($query);
@@ -175,18 +180,10 @@ sub dbGetColInfo {
        return;
     }
 
-    if (0) {
-       %retval=%{$sth->fetchrow_hashref()};
-       return keys %retval;
-    }
-
-    my @cols;
-    while (my @row = $sth->fetchrow_array) {
-       push(@cols, $row[0]);
-    }
+    %retval = %{ $sth->fetchrow_hashref() };
     $sth->finish;
 
-    return @cols;
+    return keys %retval;
 }
 
 #####
@@ -257,21 +254,15 @@ sub dbUpdate {
 #####
 # Usage: &dbInsert($table, $primkey, $primval, %hash);
 sub dbInsert {
-    my ($table, $primkey, $primval, %hash, $delay) = @_;
+    my ($table, $primkey, $primval, %hash) = @_;
     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 $p INTO $table (".join(',',@keys).
+    &dbRaw("Insert($table)", "INSERT INTO $table (".join(',',@keys).
                ") VALUES (".join(',',@vals).")"
     );
 
@@ -279,13 +270,15 @@ sub dbInsert {
 }
 
 #####
-# Usage: &dbReplace($table, %hash);
+# Usage: &dbReplace($table, $key, %hash);
 #  Note: dbReplace does optional dbQuote.
 sub dbReplace {
-    my ($table, %hash) = @_;
+    my ($table, $key, %hash) = @_;
     my (@keys, @vals);
-    my $iquery = "INSERT INTO $table ";
-    my $uquery = "UPDATE $table SET ";
+    my $where  = "WHERE $key=".&dbQuote($hash{$key});
+    my $squery = "SELECT $key FROM $table $where;";
+    my $iquery = "INSERT INTO $table ";
+    my $uquery = "UPDATE $table SET ";
 
     foreach (keys %hash) {
        if (s/^-//) {   # as is.
@@ -297,19 +290,22 @@ sub dbReplace {
        }
        $uquery .= "$keys[-1] = $vals[-1], ";
     }
-    $uquery = ~s/, $/;/;
+    $uquery =~ s/, $/ $where;/;
     $iquery .= "(". join(',',@keys) .") VALUES (". join(',',@vals) .");";
 
-    &DEBUG($query) if (0);
+    &DEBUG($squery) if (0);
 
-    if(!&dbRaw("Replace($table)", $iquery)) {
+    if(&dbRawReturn($squery)) {
        &dbRaw("Replace($table)", $uquery);
-    }
+    } else {
+       &dbRaw("Replace($table)", $iquery);
+     }
+
 
     return 1;
 }
 
-#####
+##### MADE REDUNDANT BY LEAR.
 # Usage: &dbSetRow($table, $vref, $delay);
 #  Note: dbSetRow does dbQuote.
 sub dbSetRow ($@$) {
@@ -407,7 +403,7 @@ sub sumKey {
 sub randKey {
     my ($table, $select) = @_;
     my $rand   = int(rand(&countKeys($table) - 1));
-    my $query  = "SELECT $select FROM $table LIMIT $rand,1";
+    my $query  = "SELECT $select FROM $table LIMIT 1,$rand";
 
     my $sth    = $dbh->prepare($query);
     &SQLDebug($query);
@@ -463,34 +459,6 @@ sub searchTable {
     return @results;
 }
 
-####################################################################
-##### Factoid related stuff...
-#####
-
-#####
-# Usage: &getFactInfo($faqtoid, $type);
-#  Note: getFactInfo does dbQuote
-sub getFactInfo {
-    return &dbGet("factoids", $_[1], "factoid_key=".&dbQuote($_[0]) );
-}
-
-#####
-# Usage: &getFactoid($faqtoid);
-sub getFactoid {
-    return &getFactInfo($_[0], "factoid_value");
-}
-
-#####
-# Usage: &delFactoid($faqtoid);
-sub delFactoid {
-    my ($faqtoid) = @_;
-
-    &dbDel("factoids", "factoid_key",$faqtoid);
-    &status("DELETED '$faqtoid'");
-
-    return 1;
-}
-
 #####
 #
 sub checkTables {