From 5cb59d152fcc09e974ceaf0c213b39f202b810a2 Mon Sep 17 00:00:00 2001
From: simonraven <simonraven@c11ca15a-4712-0410-83d8-924469b57eb5>
Date: Sat, 10 Mar 2007 19:44:48 +0000
Subject: [PATCH] massive add-in for postgres support, contributed by troubled
git-svn-id: https://svn.code.sf.net/p/infobot/code/trunk/blootbot@1307 c11ca15a-4712-0410-83d8-924469b57eb5
---
ChangeLog | 21 +++++++++++++
INSTALL.pgsql | 31 +++++++++++++++++-
src/Factoids/DBCommon.pl | 2 +-
src/Factoids/Update.pl | 8 ++---
src/IRC/IrcHooks.pl | 3 +-
src/IRC/Schedulers.pl | 3 +-
src/Misc.pl | 3 +-
src/Modules/botmail.pl | 7 +++--
src/Process.pl | 2 +-
src/dbi.pl | 68 +++++++++++++++++++++++++++++++++++-----
10 files changed, 124 insertions(+), 24 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index baed181..18c95c4 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,24 @@
+2007-03-07 14:06 troubled
+
+ * setup/pgsql/: Added pgsql specific dir. To be completed later
+ * setup/pgsql/pgsql-schema.sql: Import this file into an precreated
+ database as per blootbot.config and pgsql should be good to go
+ * INSTALL.pgsql: Changes to reflect semi-function pgsql progress
+
+2007-03-06 00:00 troubled
+
+ * src/Process.pl: sqlReplace switched to sqlSet to fix pgsql.
+ sqlReplace should be removed since its not pg friendly.
+ There is no matching REPLACE INTO in pgsql.
+ * src/Factoids/DBCommon.pl: &setFactInfo during ~unlock set
+ time to NULL which Pgsql wants "0" since NOT NULL
+ * src/Factoids/Update.pl: see Process.pl change
+ * src/Misc.pl: see Process.pl change
+ * src/dbi.pl: Added working PostgreSQL support! yay! ;)
+ * src/Modules/botmail.pl: &sqlReplace -> &sqlSet; removed
+ &sqlQuote (data already quoted and broke 1 msg only)
+ * src/IRC/IrcHooks.pl: see Process.pl change
+ * src/IRC/Schedulers.pl: see Process.pl change
2005-02-18 00:00 timriker
diff --git a/INSTALL.pgsql b/INSTALL.pgsql
index 1810d5f..9510400 100644
--- a/INSTALL.pgsql
+++ b/INSTALL.pgsql
@@ -4,5 +4,34 @@ Method of installation.
- Debian: (apt-get install postgresql)
- Debian: (apt-get install libpgperl)
-SUPPORT FOR PGSQL IS CURRENTLY BROKEN! You'll have to use one of the other
+---
+OLD: SUPPORT FOR PGSQL IS CURRENTLY BROKEN! You'll have to use one of the other
databases instead.
+---
+
+Actually, I have implemented pgsql support. It works just fine, but it assumes
+that you have precreated the tables for now. To help with this, I have
+included a sql file under setup/pgsql/pgsql-schema.sql. Simply psql <dbname>
+and the type:
+
+dbname#=> BEGIN;
+dbname#=> \i path/to/setup/pgsql/pgsql-schema.sql
+.......
+dbname#=> COMMIT;
+
+If everything went fine, you should have working Pgsql tables needed for blootbot.
+Type "\d" to check if they were created.
+
+In the future I will try to get things working a little smoother. But for now
+this should be considered "near production" quality. :)
+
+TODO:
+-----
+ - Auto create tables if they dont exist
+ - Modify setup.pl to do pgsql work
+ - Pgsql db conversions?
+
+
+
+----
+troubled@freenode
diff --git a/src/Factoids/DBCommon.pl b/src/Factoids/DBCommon.pl
index 1d869ab..c672255 100644
--- a/src/Factoids/DBCommon.pl
+++ b/src/Factoids/DBCommon.pl
@@ -143,7 +143,7 @@ sub CmdUnLock {
&performReply("unlocking factoid \002$faqtoid\002");
&setFactInfo($faqtoid,"locked_by", "");
- &setFactInfo($faqtoid,"locked_time", "");
+ &setFactInfo($faqtoid,"locked_time", "0"); # pgsql complains if NOT NULL set. So set 0 which is the default
return 1;
}
diff --git a/src/Factoids/Update.pl b/src/Factoids/Update.pl
index 400012e..fcb6ca2 100644
--- a/src/Factoids/Update.pl
+++ b/src/Factoids/Update.pl
@@ -96,7 +96,7 @@ sub update {
&performAddressedReply("okay");
- &sqlReplace("factoids", {
+ &sqlInsert("factoids", {
created_by => $nuh,
created_time => time(), # modified time.
factoid_key => $lhs,
@@ -178,8 +178,7 @@ sub update {
$count{'Update'}++;
&status("update: <$who> \'$lhs\' =$mhs=> \'$rhs\'; was \'$exists\'");
- &sqlReplace("factoids", {
- factoid_key => $lhs,
+ &sqlSet("factoids", {'factoid_key' => $lhs}, {
modified_by => $nuh,
modified_time => time(),
factoid_value => $rhs,
@@ -212,8 +211,7 @@ sub update {
$count{'Update'}++;
&status("update: <$who> \'$lhs\' =$mhs=> \'$rhs\'; was \'$exists\'");
- &sqlReplace("factoids", {
- factoid_key => $lhs,
+ &sqlSet("factoids", {'factoid_key' => $lhs}, {
modified_by => $nuh,
modified_time => time(),
factoid_value => $rhs,
diff --git a/src/IRC/IrcHooks.pl b/src/IRC/IrcHooks.pl
index d105d8e..c158f3b 100644
--- a/src/IRC/IrcHooks.pl
+++ b/src/IRC/IrcHooks.pl
@@ -926,8 +926,7 @@ sub on_public {
# don't allow ppl to cheat the stats :-)
if (defined $t && $time - $t > 60) {
- &sqlReplace("stats", {
- nick => $who,
+ &sqlSet("stats", {'nick' => $who}, {
type => $x,
channel => $c,
time => $time,
diff --git a/src/IRC/Schedulers.pl b/src/IRC/Schedulers.pl
index 8d8f9e2..b31a95c 100644
--- a/src/IRC/Schedulers.pl
+++ b/src/IRC/Schedulers.pl
@@ -557,8 +557,7 @@ sub seenFlush {
if ($param{'DBType'} =~ /^(mysql|pgsql|sqlite(2)?)$/i) {
foreach $nick (keys %seencache) {
- my $retval = &sqlReplace("seen", {
- nick => lc $seencache{$nick}{'nick'},
+ my $retval = &sqlSet("seen", {'nick' => lc $seencache{$nick}{'nick'}}, {
time => $seencache{$nick}{'time'},
host => $seencache{$nick}{'host'},
channel => $seencache{$nick}{'chan'},
diff --git a/src/Misc.pl b/src/Misc.pl
index bf95b60..c768a53 100644
--- a/src/Misc.pl
+++ b/src/Misc.pl
@@ -669,8 +669,7 @@ sub closeStats {
$i += $cmdstats{$type};
- &sqlReplace("stats", {
- nick => $type,
+ &sqlSet("stats", {'nick' => $type}, {
type => "cmdstats",
'time' => time(),
counter => $i,
diff --git a/src/Modules/botmail.pl b/src/Modules/botmail.pl
index 846d611..97d4e52 100644
--- a/src/Modules/botmail.pl
+++ b/src/Modules/botmail.pl
@@ -90,9 +90,10 @@ sub add {
# only support 1 botmail with unique dstwho/srcwho to have same
# functionality as botmail from infobot.
+ # Note: I removed the &::sqlQuote reference. Seems to be working and inserting fine without it here. -- troubled
my %hash = &::sqlSelectRowHash("botmail", "*", {
- srcwho => &::sqlQuote(lc $::who),
- dstwho => &::sqlQuote(lc $recipient)
+ srcwho => lc $::who,
+ dstwho => lc $recipient
} );
if (scalar (keys %hash) > 1) {
@@ -100,7 +101,7 @@ sub add {
return;
}
- &::sqlReplace("botmail", {
+ &::sqlInsert("botmail", {
'dstwho' => lc $recipient,
'srcwho' => lc $::who,
'srcuh' => $::nuh,
diff --git a/src/Process.pl b/src/Process.pl
index 36cffd4..f9cd46c 100644
--- a/src/Process.pl
+++ b/src/Process.pl
@@ -316,7 +316,7 @@ sub process {
$karma--;
}
- &sqlReplace("stats", {
+ &sqlSet("stats", {'nick' => $term}, {
nick => $term,
type => "karma",
'time' => time(),
diff --git a/src/dbi.pl b/src/dbi.pl
index bb3c396..9a1ca6b 100644
--- a/src/dbi.pl
+++ b/src/dbi.pl
@@ -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.");
@@ -300,9 +306,9 @@ sub sqlInsert {
}
#####
-# 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.");
@@ -318,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;
}
@@ -635,6 +660,35 @@ 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 c.relname FROM pg_catalog.pg_class c \
+ LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \
+ WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog','pg_toast') and
+ pg_catalog.pg_table_is_visible(c.oid)";
+
+ 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 connections factoids rootwarn seen stats onjoin) ) {
--
2.39.5