I am using any_db with a PostgreSQL backend, and have had to fix a few issues to get it to work. I am almost done the fixes, and will post them here when I get a chance to clean them up and merge them into the cvs version after that. Is anyone else using the anydb plugin though?

This issues I have found is that the GLOBALS["db"] was not getting picked up, the sql was bad for scanning for backlinks and the refs field was not getting written out.

hmmm.. I wound up having to change the any_db.php to use $res = pg_query($GLOBALS["db"], $sql); to let me open the postresql before I include the wiki. This is not a clean patch of course, but it should give some ideas as to the problem?

Warning: pg_query(): Query failed: ERROR: syntax error at end of input at character 53 in /usr/local/php4/lib/php/wiki/plugins/db/any.php on line 372 SELECT pagename AS id, meta, flags FROM ewiki WHERE

I fixed this error of the bad sql .... the problem with the backlinks not working reliably remains... here is the fix for the pg_query error, in any db:

144d143
<       if ($where != '') $where = "WHERE " . $where;
146c145
<          "SELECT pagename AS id, meta, flags FROM $this->table $where"
---
>          "SELECT pagename AS id, meta, flags FROM $this->table WHERE $where"

This change avoids adding the 'WHERE ' clause when there is no where condition.

mario: Fix included finally, but I come more and more to the conclusion that a cruft-free *PostgreSql backend was more senseful. (anydb was a nice idea, but it becomes a maintenance nightmare as it stands now; MySql and *PostgreSql are simply to different to be supported by one SQL command set)

jbw: Here is a better, more complete fix:

66c66
<       anydb_query("SELECT 1;", $GLOBALS["db"]);    // saves connection handle
---
>       anydb_handle($GLOBALS["db"]);    // saves connection handle
134a135
>       $r = array();
136c137
<       $where = array();
---
>       $where = "";
140c141
<             $where[] = "(pagename='".anydb_escape_string($id)."')";
---
>             $where .= ($where ? " OR " : "WHERE ") . "(pagename='".anydb_escape_string($id)."')";
143d143
<       $where = implode(" OR ", $where);
145c145
<          "SELECT pagename AS id, meta, flags FROM $this->table WHERE $where"
---
>          "SELECT pagename AS id, meta, flags FROM $this->table $where"
147d146
<       $r = array();
238a238
>      $pk_name = $this->table . "_pk";
241c241
<             ADD CONSTRAINT internal_id PRIMARY KEY (pagename, version);
---
>             ADD CONSTRAINT $pk_name PRIMARY KEY (pagename, version);
326c326
<       $anydb_handle = & $db;
---
>       $anydb_handle = $db;
356c356
< function anydb_query($sql, $db="") {
---
> function anydb_query($sql, $db=NULL) {
375a376
>    if (!$res) { echo $sql ; }

Want it checked into cvs? I did notice that there is an unserialize in the built in mysql code that does not belong there btw. This code could be made to resemble the main mysql some actually. I am willing to start a pgsql plugin. The one thing that should be thought out is how to handle binaries, either by using the bytea data type, or the large object support, or baseNN encoding.

This addresses all the issues I know about so far with using any.php and PostgreSQL.

BTW, what db did not support the primary key declartion in the create table?

mario: Sure, just check that in!

I can't rembember exactly, but the "SELECT 1" and the separated out primary key declaration had some reason (not from me).

For final binary support, we should probably introduce a {binary} column besides {content}, that gets used instead, whenever the db backend detects binary data (it then is responsible for not revealing the $data{"binary"} index, but copying it from/to {content}). That's my impression what a database backend should do, the core script shouldn't be bothered with taking care. Detection of binary entries simply goes by checking for the _BINARY flag in the simples case (while string or SQL failure tests are ok), only the ::GET() method cares about mapping binary back into the content field. And the ::SEARCH function need not know about a new {binary} column either.