+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
- 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
&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;
}
&performAddressedReply("okay");
- &sqlReplace("factoids", {
+ &sqlInsert("factoids", {
created_by => $nuh,
created_time => time(), # modified time.
factoid_key => $lhs,
$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,
$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,
# 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,
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'},
$i += $cmdstats{$type};
- &sqlReplace("stats", {
- nick => $type,
+ &sqlSet("stats", {'nick' => $type}, {
type => "cmdstats",
'time' => time(),
counter => $i,
# 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) {
return;
}
- &::sqlReplace("botmail", {
+ &::sqlInsert("botmail", {
'dstwho' => lc $recipient,
'srcwho' => lc $::who,
'srcuh' => $::nuh,
$karma--;
}
- &sqlReplace("stats", {
+ &sqlSet("stats", {'nick' => $term}, {
nick => $term,
type => "karma",
'time' => time(),
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
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.");
}
#####
-# 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.");
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;
}
}
# 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) ) {
projects / infobot.git / commitdiff