Hi, I haven't checked this forum for almost as long as since I first started this thread so it was a surprise when I spotted it bumped to the top just now :D
I did actually start working on a PostgreSQL driver after I posted almost a year ago, but I dropped the project once I realised that despite the modular DB driver architecture, many other parts of the codebase make significant assumptions based on MySQL. I had initially hoped that I could just make a pgsql driver class, a schema creation script and perhaps be able to rewrite some of the SQL sufficiently closer to the ISO/ANSI standard that it would work for both and/or use the MySQL compatibility functions for PostgreSQL. Unfortunately, I just kept bumping into one issue after another and I concluded that the amount of work required just to get PostgreSQL working with Joomla! was close to a level of effort that could produce a more completely DB-agnostic implementation, which would be of much greater value only that it needed really to happen as a collaborative effort because of the numerous changes across the whole codebase and the potential impact on performance.
Though I might have been prepared to make all the necessary code changes myself, I felt that it needed to be driven by somebody who is already part of the development team and trusted to push some amount of refactoring through the codebase without too much fuss. I did have in mind that once 1.5 was finished there might be an opportunity to either get the core developers on-board or to temporarily fork the whole lot myself, but 1.5 has stayed "just around the corner" for such a long time that I eventually forgot all about this completely.
Anyway, I did start making some notes during my initial investigations into the issue, which I have managed to dig up. They are basically just a few bullet points on some specific technical issues that I encountered early-on (I soon gave up writing all the issues down). I expect the Joomla! code has changed somewhat since January, but I guess I might as well post these notes here anyway for whatever use they may or may not be to somebody else thinking of taking up the challenge - perhaps just as a quick sample of the kinds of problems that come up...
(NB: This is copy/pasted verbatim from notes which were only ever intended as mind-joggers for myself, so it may not make a lot of sense)
PostgreSQL in Joomla!
- PostgreSQL does not support collations (relies on the underlying operating system)
- PostgreSQL does not use error codes (for JError)
- Difficult to implement JDatabase::getTableCreate - what is this for? Doesn't appear to be used.
- Creation of indexes other than those used for column constraints cannot be done within the CREATE TABLE statement in PostgreSQL
(syntax of UNIQUE constraints differs in that the constraint cannot be named in PostgreSQL) * check
- Index names in PostgreSQL must be unique for the catalog schema (e.g. idx_section used in both jos_categories and jos_content)
- MySQL requires mode ANSI_QUOTES to use SQL standard double quotes for identifiers,
but this may cause issues for single-quoted strings
(PostgreSQL supports the double-quoted identifers as per the standard)
- The optional display width for data-types in MySQL (e.g. INT(11) is not supported by PostgreSQL) - is this really necessary?
- "0000-00-00 00:00:00" is not a valid timestamp (either in the SQL standard or PostgreSQL)
- CREATE DATABASE in JInstallationHelper::createDatabase uses syntax CHARACTER SET `utf8` which is ENCODING 'UTF8' in PostgreSQL
- JInstallationHelper::setDBCharset will try to change the database charset and collation using MySQL specific syntax
(the function checks for UTF8 support as the only criterion for assuming the operation is possible and the command is appropriate)
- JInstallationHelper::backupDatabase uses syntax RENAME TABLE x TO y which is ALTER TABLE x RENAME TO y for PostgreSQL
- JInstallationHelper::splitSql is designed to use the non standard-compliant # comment markers for stripping comments
- JAJAXHandler::dbcollate assumes MySQL for listing collations (SHOW COLLATION LIKE 'utf8%')
- JAJAXHandler::dbcollate uses JDatabase::getVersion and tests MySQL version to determine UTF8 support
(there is already a function JDatabase::hasUTF which does the same thing)
SQL Standard Notes
- How much can be achieved by moving closer to ISO standard SQL syntax? Worth bothering?
- Syntax for CREATE DATABASE is not defined in the SQL standard
- Syntax for CREATE INDEX is not defined in the SQL standard
For Further Consideration
- Use of time zones?
Replaced in joomla.sql
"# " with "-- "
"`" with """
"int(1)" wth "int"
"int(11)" with "int"
"int(10)" with "int"
"int(9)" with "int"
"INTEGER(1)" with "INTEGER"
"tinyint(1)" with "tinyint"
"tinyint(3)" with "tinyint"
"tinyint(4)" with "tinyint"
"mediumtext" with "text"
"UNSIGNED " with ""
"int NOT NULL auto_increment" with "SERIAL"
"INTEGER NOT NULL AUTO_INCREMENT" with "SERIAL"
"bigint(20) NOT NULL auto_increment" with "bigserial"
"datetime" with "timestamp"
"tinyint" with "smallint"
"0000-00-00 00:00:00" with "epoch" (epoch = Unix system time zero)
"0000-00-00" with "epoch"
"TYPE=MyISAM CHARACTER SET "utf8" COLLATE "utf8_general_ci" AUTO_INCREMENT=1 " with ""
Changes in installation/includes/classes.php
- Support for PostgreSQL not checked in pre-installation tests
- PostgreSQL needs to be added to files list:
$files = array ('mysql', 'mysqli', 'postgresql', );
- pg_connect function needs to be added to the detectDB map:
$map = array ( 'mysql_connect' => 'mysql', 'mysqli_connect' => 'mysqli',
'mssql_connect' => 'mssql', 'pg_connect' => 'postgresql');