PostgreSQL frontend support

that's really a feature request... :) and i think it would affect only Hosting (for now, we could also add support for postgres in the backend, but that's a whole different story).

It looks like the database initialisation fails, could you provide us with the errors that happen when you enable the hosting module? If the install profile doesn't show the errors, try uninstalling and reinstalling the hosting module.

Nope, this is not a dupe of #269520: Move to PDO for all database access which concerns only the backend managing mysql OR postgresql servers.

This issue here is about having the frontend (a regular drupal with almost regular modules) running on a PostgreSQL backend and should be fairly easy to implement. It doesn't depend on PDO.

This is still in .4.

The issue is that you're using column names that are restricted. You need to quote 'user' in all queries for it to work. Simply changing the column name to something less-restrictive would be the solution as it uses best practices in Drupal. This would also be an issue with Oracle IIRC.

http://www.postgresql.org/docs/8.1/static/sql-keywords-appendix.html

I can try to work on a patch if nobody else has.

mradcliffe , we'd happily commit that patch.

the only instance is see is in hosting_client_user , the user column should be renamed to 'uid'

Okay, I have a patch that should suffice for the hosting module frontend stuff.

Basic summary of changes:

• hosting_client_user: user -> uid
  • user is a reserved word in postgresql (and oracle).
• hosting_site: ssl -> ssl_toggle
  • ssl is a reserved word in mysql, but not in other sql systems. there's a bit of a hack with $attributes->ssl = $attributes->ssl_toggle, probably want to change more code???
• package: h.nid as 'package'
  • mysql-only syntax. I removed the 'as' clause and modified everything to just use ->nid
• Added basic updates to the two table changes for mysql/mysqli (untested)

Is the verify stuff part of the backend (i.e. look at the pdo/backend task)?

edit: removed that last question, easily answered.

This is really a request for review, thanks for your submission!

Some problems with the patch:

+  switch($GLOBALS['db_type']) {
+    case 'mysql':
+    case 'mysqli':
+      $ret[] = update_sql("ALTER TABLE {hosting_client_user} change column user uid");

Why this switch? Can't we just run this request anyways? It will not affect pgsql... Otherwise, isn't there a command specifically for that in the schema API?

+        return user_access('view client', $account) && db_fetch_array(db_query("SELECT user FROM {hosting_client_user} WHERE uid=%d and client=%d", $user->uid, $node->nid));

SELECT user here will probably fail, i guess it should be select uid?

+      VALUES (%d, %d, '%s', %d, '%f', %d, %d)", 

Floats need to be quoted in pgsql?

+  switch($GLOBALS['db_type']) {
+    case 'mysql':
+    case 'mysqli':
+      $ret[] = update_sql("ALTER TABLE {hosting_client_user} change column user uid");

This is not how Drupal 6 db schema changes are made, read up on the schema api or check the last few updates in some of our .install files to find the fomula.

I'm really paranoid about $db_type ;-) But, yeah, it shouldn't be necessary. If for some reason it was run by postgresql it would spew a couple of errors.

My bad on forgetting about db_change_field().

The float needs to change because the schema says that 'version' is a text field. I was in the middle of turning that into '%s' and must have forgotten. Postgresql is strict about types and typecasting. I'm sure version field is supposed to be a text field for '6.x-dev' instances so it should be '%s'.

I forgot to update cvs, re-rolled. It shouldn't have mattered as it was able to merge.

version_code is an integer in the database, but is treated as a float in php, because php has a ridiculously small max_int_count but floats as big as you need.

it's only to get it into the database and is never used that way again.

so you found a bug there , it should be :

       rid, package_id, filename,
       schema_version, version, version_code, status)
-      VALUES (%d, %d, '%s', %d, %f, '%s', %d)", 
+      VALUES (%d, %d, '%s', %d, '%s', %f, %d)", 
       $instance->rid, $instance->package_id, $instance->filename, 
       $instance->schema_version, $instance->version, $instance->version_code, $instance->status);

but you need the %f for php to even be able to print the number.

The update_N() status lacks a comment.

The above comment by Adrian is unclear to me at this point: is this a bug with the patch on our side?

I also have yet to hear anyone has tested this patch on mysql, let alone pgsql (have you?).

Otherwise the patch looks sound, I'm just a bit worried about changing the fields like this without proper testing.

I'm maintaining a branch in git, at this point, the patch is here:

http://git.koumbit.net/?p=drupal/modules/hosting/.git;a=commitdiff_plain...

doesnt apply anymore.

New features need to be implemented in Aegir 3.x, then we can consider back-porting to Aegir 2.x.