Postgres support for database upgrade 3

This is still a work in progress, but here's a new patch against the current CVS head. New to this patch are support for Postgres installation (as opposed to just upgrading) and some Postgres fixes in event.module.

This patch still doesn't address the problem that Postgres's time data type doesn't support negative values. The interval data type looks much better suited, but I haven't tried it out yet.

I switched over to using interval in Postgres, and it's working just about fine! The only problem I encountered is that if users don't have a timezone_id set, a join fails and no time shows up for the events. Going to the user edit page to set a timezone brings up another problem:

UPDATE d_users SET timezone = '58|-21600'...

That timezone won't fit into the varchar(8) that users.timezone is declared as. That said, the timezone_id still appeared to get set correctly.

Anyway, this patch is looking pretty good now, though I have still not tested it thoroughly.

With this patch, I can now fully uninstall and reinstall the module successfully. The only problems that came up this time around were again default zero timezones and problems changing the timezone as described above.

I looked through most of the queries to see if I could figure out what usually needs to get changed to make a MySQL-dialect query work with Postgres. Here's what I've observed so far:

INTERVAL %d SECOND          -> '%d seconds'
INTERVAL foo SECOND         -> foo
INTERVAL foo HOUR_SECOND    -> foo
%d SECOND                   -­> '%d seconds'
INTERVAL foo                -> foo
offset                      -> "offset"

How about a simple function, say _event_rewrite_sql($query)? It would encapsulate the translation logic, doing the switch($GLOBALS['db_type']) and regex work to massage the queries. Perhaps this would be better done in one of the db_ functions, but I presume it's better to get it working here first.

This approach seems to be more or less working for me. There's a problem with the calendar; it shows the events I add as starting on the 2nd of the month regardless of their actual start date. I think this is unrelated to my change, just because (using the Devel module) the queries seem to be returning the correct data.

The translation function I have so far looks like:

/**
 * Prepares a SQL query that uses time intervals for execution against
 * the MySQL or Postgres database. Queries are assumed to be MySQL-ready
 * and will be translated to Postgres dialect if necessary.
 *
 * @return string containing a query ready for execution
 */
function _event_rewrite_sql($query) {
  $translated=$query;

  if($GLOBALS['db_type']=="pgsql") {
    $translated=preg_replace('/(INTERVAL )?%d SECOND/', "'%d seconds'", $translated);
    $translated=preg_replace('/INTERVAL ([^ ]+)( (HOUR_)?SECOND)?/', '\1', $translated);
    $translated=preg_replace('/ offset /', ' "offset" ', $translated);
  }

  return $translated;
}

That's fine; I can whip up a patch that inlines that function and also drops a comment where you suggested. (It will probably be too verbose; please edit freely!)

The problem with the calendar appears in both the calendar block and at /event in the month, table, and list views at least. The upcoming events block shows the correct events but the parentheses that should contain the time-until are empty. I should reinstall the latest version before I scratch my head too much more about those problems.

I thought that user update query was odd, too. Whatever was going on is not going on now, and I was able to get my timezone and timezone_id set without going into the database.

Here's a patch against the latest event-5.x-2.x-dev (08-May-2007 12:12).

This time around, I installed the module from scratch and used CCK instead of basicevent. The problems with labeling the title and body fields disappeared, of course, but the problem with the calendar page and block persists (events start to appear on the 2nd of the month, but the days on which they start and end are properly labeled with the time).