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);