There was some big news this week in Joomla! CMS development with the merging of massive rewriting of queries. Why? And why rewrite? Because this makes it possible to run the Joomla! CMS on many different databases, not just MySQL and MySQLi as in the past.

As people who follow these things know, for many years there has been a goal to make Joomla! “database agnostic” which is to say to allow Joomla to run on any database. For a while the Platform has had drivers for Microsoft databases SQLSrv and SQLAzure . There are also pull requests (that is, requests to merge code into the platform)  on Postgresql, Oracle, SQLite and PDO drivers. Great work by Gabriele Pongelli (Postgres), Sudhi Seshachala and the team at Hoodooku (Microsoft databases)  and Omar Ramos (Oracle, SQLite and PDO). If you want to work on a driver for another database (db2 anyone? Drizzle?) you can just fork the platform and go for it!

What next?

So what do we do next?  First, before we get to 2.5 we need tons of testing of the CMS both on the new databases and on MySQL and MySQLi. The latter is really important, obviously, since we have to be totally certain that nothing there breaks. So we need to push hard on that.

The next issue is really for extension developers. What do you need to do to be able to say you support multiple databases?^[b] I’m going to be posting a second blog about some of the specific technical issues but the short version is:

1. Don’t use MySQL clauses directly.
2. Do use JDatabasequery to build your queries in setQuery.
3. If you must use a clause that is only available in MySQL (or any other database) in setQuery, build in alternatives and checks.

Why use other databases?

If MySQL is great and freely available, why would we even bother to support other databases for the CMS? It’s a lot of work and maintenance for the people who keep the CMS running after all. The most obvious answer is that there are lots of people who are tied to other databases because of other applications they use or they may just prefer those databases. Some people also just like other databases for the features they offer. I ask Gabriele about what makes Postgres special.

PostgreSQL is a ORDBMS, object-relational database managment system, this acronym means that is a RDBMS, as MySQL is, but has an object-oriented database model.

It’s developed entirely from its community and it’s the “world’s most advanced open source database.” Obiviously it is ACID compliant.

In release 9.0 replication and hot standby were announced, these are useful for cloud hosting, RADIUS authentication, and many many other features, for a complete list look at http://www.postgresql.org/about/featurematrix

Asked what he likes about Postgres he said

PostgreSQL has some features that MySQL includes only in specific engines, like InnoDB, but not inside MyISAM; transaction, sequences and foreign key constraint, type inheritance and a wide variety of supported type, from boolean to IPv6 and geometric types to user defined types  are the first things that comes to my mind.

An example about type inheritance can be the role, or credentials used for connect to the database: it’s possible to create a generic role (like a group) and assign it some permission, then create another role that inherits from the first (like a user) and grant or revoke other privileges to the latter.

It’s possible to define custom type casts and, for C developer, you can create your own function, types and operator to be called inside queries.

Another interesting feature is the possibility to have column constraints other than table constraint, the latter is the only present in MySQL.

Last but not least, sequence type variable is the natural replacement of big integer inside typical “id “ column, and it’s more manageable with functions like “currval” and “nextval”."

Sudhi talked about why enterprise often wants to use Microsoft and Oracle solutions.

From the enterprise standpoint the needs are different. Many enterprise companies have vested interests in leveraging more from the existing business relationships with the Microsoft software stack. They have experts, resources, applications and processes already on board running on the Microsoft stack. Likewise companies have vested interests in Oracle database. With open source companies get the freedom of picking the right database for their needs. Joomla is great in terms of some of the enterprise extensibility, ACL, large extension directory/app store. Only catch here is that though the barriers to entry/adoption of Joomla is low - enterprise companies still need resources to manage MySQL assets resources and so on when the application/services on Joomla go live. Having Joomla on enterprise class databases such as SQLServer, SQLAzure and Oracle improves the reach and adoption within enterprise. That means more opportunities for all players in Joomla ecosystem. Joomla also supports Windows Azure Cloud with built in support for Azure CDN. There are several other extensions already supporting Amazon S3, Rackspace Cloud files and so on.

It’s not that different than supporting different servers. Everyone has features that they find useful and that means not everyone is on Apache 2.  The same goes for databases.

I’m lucky to have gotten to talk a lot with Sudhi and Gabriele while they have been working, and they are really great, positive contributors. Sudhi goes to a lot of Joomla! events and I definitely recommend you attend one of his sessions. I asked Gabriele what it has been like working with the Joomla! development community and he said:

This is a fantastic community, the people that develop for Joomla are very talented, in fact behind platform source code there’s a design that I’ve never seen before for a PHP application.

I’ve some other ideas that maybe I’ll try to add inside platform in future, but now I’m only interested to add PostgreSQL support inside platform and be able to use it inside CMS.

Thanks to so many people for doing this work, including not just the three main authors of the code, but people who have helped with testing (both in applications and building unit tests) and giving other feedback. All this work has led to real improvements in the base classes too, which is how it usually works out. This also shows how great it is to be able to make progress on the Platform even when there are not immediate use cases in the CMS. Not to mention how the motivation to make things better in the CMS helps make the Platform stronger.

It also shows the power of an architectural vision which said we don’t want to make Joomla run on database X we want to make it run on any database someone is willing to write a driver for. If the approach had been just to change the code enough to work with one and not worry about the others, it would have been simpler and taken less time, but that would have been much less useful.