There are many more problems than you mentioned. It seems that the PostgreSQL support philosophy was "let's just point all the code to the pgsql driver in the DSN and it will work." This is definetly wrong. I went through the trouble of patching most of the PostgeSQL related bugs this morning, when I installed the software after reading it on Slashdot but sadly, these patches break the MySQL functionality.

It seem that the approach described above is a minor and easily fixable architecture flaw in this piece of software. The assumption that has to be made is that MySQL is not compatible with PostgreSQL on many levels and that logic has to be separated. This can become a bit tricky because a most of the queries are built by a function.

Anyways, attached are my patches... well more like drop-in replacements to the latest source code that fix PostgreSQL bugs.

On 10/13/05, Bennett, Steve s.bennett@lancaster.ac.uk wrote:

Hi list,

I've just started playing with RoundCube, looks great so far..!

I can't see a list archive, so maybe some of this has already been discussed, but I'm seeing some issues using RoundCube with a pgsql database:

Some of the queries use backticks to quote column names - pgsql uses double quotes. Given that "default" is a bit of a tricky name to use for a column, is it worth renaming it, rather than doing lots of tricky code to make sure the quotes are right for the database? The same probably goes for column names like "language" and "reply-to".

My install of PostgreSQL (v8.0.1) doesn't have a function named unix_timestamp(). I hacked one up as: CREATE OR REPLACE FUNCTION unix_timestamp(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS ' BEGIN RETURN round(EXTRACT( EPOCH FROM $1 )); END; ' LANGUAGE 'plpgsql';

I get errors if I leave caching enabled - I've just turned it off for now, but I'm guessing that the serialised data contains something that pgsql doesn't like.

Amongst the spam and assorted junk that is my inbox, I have a message with no Date header. RoundCube generated errors for that too. The following seems to do the trick:

--- program/include/main.inc.orig +++ program/include/main.inc @@ -890,7 +890,9 @@ { global $CONFIG, $sess_user_lang;

• if (is_numeric($date))

• if ($date == "")
• return "";
• elseif (is_numeric($date))

$ts = $date; else $ts = strtotime($date);

Cheers,

Steve Bennett

These types of issues are exactly why you use a database abstraction layer (so you don't have to fork your code). I agree with John (although I am unfamiliar with PEAR::DB) that there must be a way to deal with this in the db abstraction. I have used the adodb library for php pretty extensively and I know it contains this functionality (no... I am not suggestioning that anyone change db abstraction layers, one should work just as well as the other)

rgds, j.

On Thu, 13 Oct 2005 18:18:16 -0500, John Arbash Meinel john@arbash-meinel.com wrote:

Bennett, Steve wrote:

...

Hi list,

I've just started playing with RoundCube, looks great so far..!

I can't see a list archive, so maybe some of this has already been discussed, but I'm seeing some issues using RoundCube with a pgsql database:

Some of the queries use backticks to quote column names - pgsql uses double quotes. Given that "default" is a bit of a tricky name to use for a column, is it worth renaming it, rather than doing lots of tricky code to make sure the quotes are right for the database? The same probably goes for column names like "language" and "reply-to".

There already is a function used to quote input strings, which should be overloaded based on database. Probably we could just overload it to do something similar for column names.

Changing names would be a non-compatible change, so you would have to check the database to see what version it was running. Which could be done (the schema is pretty simple), but it probably isn't optimal.

...

My install of PostgreSQL (v8.0.1) doesn't have a function named unix_timestamp(). I hacked one up as: CREATE OR REPLACE FUNCTION unix_timestamp(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS ' BEGIN RETURN round(EXTRACT( EPOCH FROM $1 )); END; ' LANGUAGE 'plpgsql';

I'm not sure if we need exactly unix_timestamp. But I was wondering what was wrong with:

xxx::timestamp

to cast it to a timestamp without a timezone.

...

I get errors if I leave caching enabled - I've just turned it off for now, but I'm guessing that the serialised data contains something that pgsql doesn't like.

It has a constraint between cache and session, but on exit it deletes the session (and the constraint isn't setup to ON DELETE CASCADE).

I talked to Thomas earlier today (I just started using it), and I'll probably commit some patches to give it better postgres support.

John =:->

...

Amongst the spam and assorted junk that is my inbox, I have a message with no Date header. RoundCube generated errors for that too. The following seems to do the trick:

--- program/include/main.inc.orig +++ program/include/main.inc @@ -890,7 +890,9 @@ { global $CONFIG, $sess_user_lang;

• if (is_numeric($date))

• if ($date == "")
• return "";
• elseif (is_numeric($date)) $ts = $date; else $ts = strtotime($date);

Cheers,

Steve Bennett