Index: src/Process.pl
===================================================================
--- src/Process.pl (revision 1306)
+++ src/Process.pl (working copy)
@@ -316,7 +316,7 @@
$karma--;
}
- &sqlReplace("stats", {
+ &sqlSet("stats", {'nick' => $term}, {
nick => $term,
type => "karma",
'time' => time(),
Index: src/Factoids/DBCommon.pl
===================================================================
--- src/Factoids/DBCommon.pl (revision 1306)
+++ src/Factoids/DBCommon.pl (working copy)
@@ -143,7 +143,7 @@
&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;
}
Index: src/Factoids/Update.pl
===================================================================
--- src/Factoids/Update.pl (revision 1306)
+++ src/Factoids/Update.pl (working copy)
@@ -96,7 +96,7 @@
&performAddressedReply("okay");
- &sqlReplace("factoids", {
+ &sqlInsert("factoids", {
created_by => $nuh,
created_time => time(), # modified time.
factoid_key => $lhs,
@@ -178,8 +178,7 @@
$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 @@
$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,
Index: src/Misc.pl
===================================================================
--- src/Misc.pl (revision 1306)
+++ src/Misc.pl (working copy)
@@ -669,8 +669,7 @@
$i += $cmdstats{$type};
- &sqlReplace("stats", {
- nick => $type,
+ &sqlSet("stats", {'nick' => $type}, {
type => "cmdstats",
'time' => time(),
counter => $i,
Index: src/dbi.pl
===================================================================
--- src/dbi.pl (revision 1306)
+++ src/dbi.pl (working copy)
@@ -30,7 +30,12 @@
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 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 @@
}
#####
-# 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,11 +324,30 @@
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 @@
}
# 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) ) {
Index: src/Modules/botmail.pl
===================================================================
--- src/Modules/botmail.pl (revision 1306)
+++ src/Modules/botmail.pl (working copy)
@@ -90,9 +90,10 @@
# 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 @@
return;
}
- &::sqlReplace("botmail", {
+ &::sqlInsert("botmail", {
'dstwho' => lc $recipient,
'srcwho' => lc $::who,
'srcuh' => $::nuh,
Index: src/IRC/IrcHooks.pl
===================================================================
--- src/IRC/IrcHooks.pl (revision 1306)
+++ src/IRC/IrcHooks.pl (working copy)
@@ -926,8 +926,7 @@
# 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,
Index: src/IRC/Schedulers.pl
===================================================================
--- src/IRC/Schedulers.pl (revision 1306)
+++ src/IRC/Schedulers.pl (working copy)
@@ -557,8 +557,7 @@
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'},
Index: ChangeLog
===================================================================
--- ChangeLog (revision 1306)
+++ ChangeLog (working copy)
@@ -1,4 +1,25 @@
+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
* CMD: is now cmd:
Index: INSTALL.pgsql
===================================================================
--- INSTALL.pgsql (revision 1306)
+++ INSTALL.pgsql (working copy)
@@ -4,5 +4,34 @@
- 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
Index: setup/pgsql/pgsql-schema.sql
===================================================================
--- setup/pgsql/pgsql-schema.sql (revision 0)
+++ setup/pgsql/pgsql-schema.sql (revision 0)
@@ -0,0 +1,157 @@
+SET client_encoding = 'UNICODE';
+SET check_function_bodies = false;
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+GRANT ALL ON SCHEMA public TO PUBLIC;
+
+SET search_path = public, pg_catalog;
+
+CREATE TABLE factoids (
+ factoid_key character varying(64) NOT NULL,
+ factoid_value text NOT NULL,
+ created_by character varying(80),
+ modified_by character varying(80),
+ locked_by character varying(80),
+ requested_time numeric(11,0) DEFAULT 0 NOT NULL,
+ requested_count numeric(5,0) DEFAULT 0 NOT NULL,
+ locked_time numeric(11,0) DEFAULT 0 NOT NULL,
+ created_time numeric(11,0) DEFAULT 0 NOT NULL,
+ modified_time numeric(11,0) DEFAULT 0 NOT NULL,
+ requested_by character varying(80) DEFAULT 'nobody'::character varying NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE factoids FROM PUBLIC;
+
+CREATE TABLE botmail (
+ srcwho character varying(20) NOT NULL,
+ dstwho character varying(20) NOT NULL,
+ srcuh character varying(80) NOT NULL,
+ msg text NOT NULL,
+ "time" numeric DEFAULT 0 NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE botmail FROM PUBLIC;
+
+SET SESSION AUTHORIZATION 'blootbot';
+
+CREATE TABLE connections (
+ server character varying(30) NOT NULL,
+ port integer DEFAULT 6667 NOT NULL,
+ nick character varying(20) NOT NULL,
+ nickservpass character varying(8) NOT NULL,
+ ircname character varying(20) DEFAULT 'blootbot experimental bot'::character varying NOT NULL,
+ timeadded timestamp without time zone DEFAULT now()
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE connections FROM PUBLIC;
+
+CREATE TABLE freshmeat (
+ projectname_short character varying(64) NOT NULL,
+ latest_version character varying(32) DEFAULT 'none'::character varying NOT NULL,
+ license character varying(32),
+ url_homepage character varying(128),
+ desc_short character varying(96) NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE freshmeat FROM PUBLIC;
+
+CREATE TABLE news (
+ channel character varying(16) NOT NULL,
+ id numeric DEFAULT 0 NOT NULL,
+ "key" character varying(16) NOT NULL,
+ value text NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE news FROM PUBLIC;
+
+CREATE TABLE onjoin (
+ nick character varying(20) NOT NULL,
+ channel character varying(16) NOT NULL,
+ message character varying(255) NOT NULL,
+ modified_by character varying(20) DEFAULT 'nobody'::character varying NOT NULL,
+ modified_time numeric DEFAULT 0 NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE onjoin FROM PUBLIC;
+
+CREATE TABLE rootwarn (
+ nick character varying(20) NOT NULL,
+ attempt numeric,
+ "time" integer NOT NULL,
+ host character varying(80) NOT NULL,
+ channel character varying(20) NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE rootwarn FROM PUBLIC;
+
+CREATE TABLE seen (
+ nick character varying(20) NOT NULL,
+ "time" numeric NOT NULL,
+ channel character varying(20) NOT NULL,
+ host character varying(80) NOT NULL,
+ message text NOT NULL,
+ hehcount numeric DEFAULT 0::numeric NOT NULL,
+ messagecount numeric DEFAULT 0::numeric NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE seen FROM PUBLIC;
+
+CREATE TABLE stats (
+ nick character varying(20) NOT NULL,
+ "type" character varying(8) NOT NULL,
+ channel character varying(16) DEFAULT 'PRIVATE'::character varying NOT NULL,
+ counter numeric DEFAULT 0,
+ "time" numeric DEFAULT 0 NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE stats FROM PUBLIC;
+
+CREATE TABLE uptime (
+ uptime numeric DEFAULT 0 NOT NULL,
+ endtime numeric DEFAULT 0,
+ string character varying(128) NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE uptime FROM PUBLIC;
+
+CREATE TABLE karma (
+ nick character varying(20) DEFAULT ''::character varying NOT NULL,
+ karma numeric
+);
+
+REVOKE ALL ON TABLE karma FROM PUBLIC;
+
+CREATE INDEX factoids_idx_fvalue ON factoids USING hash (factoid_value);
+
+ALTER TABLE ONLY factoids
+ ADD CONSTRAINT factoids_pkey_fkey PRIMARY KEY (factoid_key);
+
+ALTER TABLE ONLY botmail
+ ADD CONSTRAINT botmail_pkey PRIMARY KEY (srcwho, dstwho);
+
+ALTER TABLE ONLY connections
+ ADD CONSTRAINT connections_pkey PRIMARY KEY (server, port, nick);
+
+ALTER TABLE ONLY freshmeat
+ ADD CONSTRAINT freshmeat_pkey PRIMARY KEY (projectname_short, latest_version);
+
+ALTER TABLE ONLY news
+ ADD CONSTRAINT news_pkey PRIMARY KEY (channel, id, "key");
+
+ALTER TABLE ONLY onjoin
+ ADD CONSTRAINT onjoin_pkey PRIMARY KEY (nick, channel);
+
+ALTER TABLE ONLY rootwarn
+ ADD CONSTRAINT rootwarn_pkey PRIMARY KEY (nick);
+
+ALTER TABLE ONLY seen
+ ADD CONSTRAINT seen_pkey PRIMARY KEY (nick, channel);
+
+ALTER TABLE ONLY stats
+ ADD CONSTRAINT stats_pkey PRIMARY KEY (nick, "type", channel);
+
+ALTER TABLE ONLY uptime
+ ADD CONSTRAINT uptime_pkey PRIMARY KEY (uptime);
+
+ALTER TABLE ONLY karma
+ ADD CONSTRAINT karma_pkey PRIMARY KEY (nick);