PostgreSQL - Status? Can I Help?

[wdaniels]

Hi, I've been searching through the forums for information about progress getting Joomla! to work with a PostgreSQL database and as far as I can gather there is still some work to be done here.  I'm a developer and have some free time on my hands.  I'd love to help get Joomla! working with PostgreSQL if somebody can let me know the status of this effort.  What can I do to help?

[Jinx]

Hi and welcome to the Joomla! forums. Currently i'm not aware of anyone who is working on a postgres database adapter so you would need to start from scratch. The best way to go would be to have a look at the 1.5 database package and the mysql and mysqli adapters in the 1.5 framework. If you have any further questions just let us know.

[Spoutnik]

Hi,

Is there any update about this feature?
If not, I should be able to help! I'd like to use joomla, but definitely not with mysql.
Is there a more specific forum/post about postrgres connectivity development?

thanks
++

[ianmac]

The framework has been laid in place to be able to take advantage of adapters to work with other databases.  As far as I know, nobody has taken up the task of writing one for postres.

If you wanted to work on this I'm sure that there would be people who would support your effort.  As Jinx said, you want to start by looking at the JDatabase class and then the JDatabaseMySQL and JDatabaseMySQLI classes (these three classes are stored in the libraries/joomla/database/database.php, libraries/joomla/database/database/mysql.php and libraries/joomla/database/database/mysqli.php files respectively).

What you need to do is create a class for postgres (JDatabasePostgres) that would look like the JDatabaseMySQL classes but implement the methods using the Postgres functions instead.

Hope this helps and looking forward to seeing your contribution!

For further discussion, the best place might be the 1.5 Dev forum, though a moderator or someone from the Development Working group might have another suggestion.

Ian

[rupal arora]

hi,
thanks things like these provides us with the much needed knowledge and help keeping us informed about upcoming things.

best regards
Rupal Arora


(Wizzie: MODNOTE: Manual signature removed. Signatures must be setup in your Profile. Forum Rules http://forum.joomla.org/index.php/topic,65.0.html)

[perryclark]

This topic appears to be dead.  Is that truly the case?  If so, let's get going on this again as it's a great project.  What's been done if anything?  I'm interested in helping out anyway I can.  I work for a PostgreSQL centric company and hosting our community website on MySQL is a little... well... just something not right about that. 

Yes, the boss has requested that PostgreSQL Joomla! become a priority.  So, wake up eveybody, lets get this thread going again.

Thanks,

Perry Clark
xTuple.org

[billitch]

I believe we share the same point of view about mySQL and Postgres   
My company also needs joomla with PostgreSQL, are you interested in sharing the work too ?

[billitch]

ianmac: This database class layout seems great but in 1.0.13 (stable) there are other files than the concerned ones that contain calls to mysql_* functions so I expect bugs from them. These files are :

• components/com_user/user.php
• includes/phpInputFilter/class.inputfilter.php
• includes/feedcreator.class.php
• installation/index.php

[perryclark]

Yes, I believe we're prepared to help with this conversion.  It's something our organization needs/wants.  Have you found anyone else that has expressed interest?  Anyone on the Joomla dev team have any input?

- Perry

[perryclark]

Before I get carried away and talking about stuff I don't know about...

Can we talk about making Joomla able to hook into any user db?

The reason I'm asking this is that eventually I need to get to a single user db for Joomla, phpBB, mantis users.  A true single user db - not bridged.  For me, that means that the one db (postgres) will be our erp/accounting system - OpenMFG and it's OpenSource sibling PostBooks. 

Anyone have any feelings on this?

Should we start by writing a spec?

- Perry - xtuple.com

[wdaniels]

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

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)

Code: Select all

PostgreSQL in Joomla!
=====================

General Issues
--------------

 - 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');

[billitch]

Well if we are to put some DB abstraction into Joomla, why not use what has already been done : http://pear.php.net/package/MDB2

I never used it but it looks well maintained and stable with many database drivers (mysql, pgsql, mssql, sqlite)

[scrap2do]

Well if your going to do it have a look at  http://adodb.sourceforge.net/ it has a better abstraction layer. And, if I remember correctly either Joomla's database layer is built somewhat from this lib or this lib is built somewhat from Joomla. At any rate, I haven't looked at 1.5 very much but I did do an estimate for making this work with 1.0.13 and it looks like about 20 -30 hours for the integration +/- 20% . From what little I have seen of 1.5 it should be a lot less difficult. 1.5 is a lot more organized and better commented than 1.0.x also with the way the classes are setup it would need less testing than a hack on 1.0.x.

Just my thoughts

[billitch]

ADOdb looks really great ! Joomla already has some strange database abstraction (for MySQL/MySQLi), but if we are to give it a true abstraction layer maybe we should just completely replace it.

Another thing is that if we start working on this we should decide which version to work on. Joomla 1.5 though only in RC status should be fine by me.

[scrap2do]

I'll help ....

As far as versions go I would rather start with 1.0.x that way if there are problem we can identify them. With the massive amount of work being done to 1.5 it would be hard to say wether our database layer was causing the problem or not.

Or we can do both at the same time.

I can help with the Microsoft SQL Server, Access, ODBC and some Oracle. Also can help with documentation, install/uninstall.

I cast 1 vote for 1.0.x -> 1.5
I cast 1 vote for adodb

[billitch]

After taking a quick look at Joomla 1.5 bug tracker, I must agree that 1.0 is a wiser choice.
So it will be 1.0 and ADOdb for me too.

[billitch]

If no one has started working on this I might give it a try.
Please e-mail / pm me if you have something to say about it.. !

edit: hum.. I should have reas this first : http://forum.joomla.org/viewtopic.php?f=326&t=195747

[mike177]

Hello,
What is the status of this effort, please?

Did anyone ever get Joomla 1.5 to work on postgres?

Thanks,
Mike

[clio75]

Did anyone ever get Joomla 1.5 to work on postgres?

I'm using Joomla, but I like to use Postgre and not mysql.

If any have some info about this subject, Please replay

Br
Clio75

[psi-jack]

Now, I'm actually really working on a PostgreSQL implementation in Joomla 1.5, because they have SOME of the needed infrastructure in place. But as someone earlier pointed out there's still a lot of conflicting points, and re-made functions, especially in the installation portion of Joomla.

Ian pointed out, the driver infrastructures' there, and it is, hopefully most of Joomla API uses it, but the portion that doesn't, and it's the big brick in the wall causing most to stop trying, is the installation of Joomla, because they didn't put those in the driver but in the installer instead.


What I need to know, if anyone can peek and poke or whom knows directly, what else is there to look into besides the driver in the joomla library database classes, and the installation?

[gobezu]

Looking really forward to any progress on this project!

[dark_ixion]

I downloaded the latest stable version of Joomla and converted most of it to work with PostgreSQL. However, it was more of a conversion than adding support for PostgreSQL as most of the module code is specifically in MySQL syntax (e.g. CONCAT_WS keyword and backticks hardcoded into SQL queries). It also doesn't quite run properly. I've got it creating all the tables and populating them with sample data, but the main page only contains "Home" on the Main Menu section, and nothing else. And for some reason it says the "admin" account doesn't have administative access. It's a start anyway.

I think all database calls should be constructed in a way that are database-agnostic, and would call on the appropriate database library to perform any specific functions. For example, $db->concatenate("column_a","column_b") to call the appropriate constructor for that syntax instead of writing it directly into where it's being called from.