Some review and proposal about Drupal 7.x database stack

I start my Drupal + Oracle research since the end of Drupal 4.7 life cycle (around Oct 2006), based on my client's request. The project is still running, but I am not satisfy about its progress. After keep trace in Drupal database stack implementation for more than years, it is time for focusing on Drupal 7.x development.

So what are the changes since 4.7.x? And what will be happened for 7.x? I would like to share my research progress with you, and so let's brain storming for what's next :-)

Drupal 4.7.x/5.x

Fig 1.1 Drupal 5.x database stack.

The case of Drupal 4.7.x and 5.x is very similar: we support both MySQL and PostgreSQL backend, with specific database access API file (e.g. database.mysqli.inc), enclosed with database.inc, and that's all. We still have some extra abstraction build on top of this simple model, e.g. tablesort.inc and pager.inc, for some useful application layer abstraction.

During this period, core and contribute developers need to understand the differences between MySQL and PostgreSQL, since database schema must handle manually. I would like to call this as the dark age of our history: we are asking every contribute developers as both MySQL and PostgreSQL expert (but most of us coming with MySQL-only skill set), or else code was not portable. This result in a complicated PostgreSQL support, and so most contribute modules are MySQL only.

Drupal 6.x

Fig 2.1 Drupal 6.x database stack.

For our newest stable Drupal release - 6.x, Schema API is being introduced. We spend a lot of time for its development, during 6.x 1-year development life cycle. It is target to solve the schema problem as mentioned above. Contribute developers are no longer required as bi-expert nor multi-expert: we just need to know how to define our schema in a correct Schema API syntax, Schema API will handle the rest for us. IMHO, it is very powerful, and it is an important improvement for other database development.

drupal_write_record() is being introduce, too. This handy function will grep out existing cached schema, and build INSERT/UPDATE queries for us automatically. It try to handle all column type binding for normal developers, which is usually very bulky in implementation.

BTW, the work is not too elegant: all stuff are mixed into database.*.inc, and so files are even split as 2 section (groups database and schemaapi) internally. This may increase the difficulty for our core maintenance.

Most likely, this design is very similar as Moodle 1.7+ XMLDB implementation.

So what's coming next for Drupal 7.x?

Fig 3.1 Well... So what's coming next for Drupal 7.x?

The truth is: I don't know the overall schedule up to this moment. Data Architecture Design Sprint group is now hard working for this, and going to present their research progress in Boston DrupalCon 2008. Since we are going to use PHP PDO (instead of legacy PHP database connection drivers), people are also focusing on implement Data API or Active record model for a higher level of abstraction. Before they have their final decision, I would like to provide some successful case study as reference, and let's brain storming for the optimal solution :-)

Fig 3.2 Moodle 1.7+ XMLDB stack. From doc.moodle.org.

When we are talking about improve our database abstraction as more powerful and universal, Moodle 1.7+ XMLDB stack should act as a perfect example (it since Nov 2006). As Moodle core uses ADOdb internally, this possibility has been present since the beginning. Plus their XMLDB abstraction, Moodle 1.7+ is able to handle both MySQL, PostgreSQL, Oracle and MSSQL while maintaining everything working properly. I will not detail their implementation within this article, and you may refer to their introducetion, roadmap and problems document.

The most important idea of Moodle's XMLDB stack is split-brain handling. They left every layer keep focus on their duty ONLY, and so the final approach is perfect and elegant. This is also similar as the well-known OSI model: A layer is a collection of related functions that provides services to the layer above it and receives service from the layer below it.

Fig 3.3 Research progress of Siren + personal suggestion.

Well, Siren is my personal research project besides Drupal 6.x, which target to explore the possibility of multiple database support as like as Moodle, Gallery2, and eGroupWare. Moreover, prepare research progress whenever Drupal 7.x is open for public development (and that's now). Within this project, we are able to support both 5 database + totally 9 PHP database access drivers, including mysql, mysqli, pgsql, oci8, pdo_mysql, pdo_pgsql, pdo_oci, pdo_sqlite and pdo_ibm.

It sounds interesting, isn't it? So what changes I have been made in order to support this approach? Just some step forward improvement: split the handle of function calls into number of individual files, and each layer just keep focus on what they are needed for. E.g. split Schema API related functions into schema..inc, common DML into common..inc, and so on. Surly I plug some extra update to our core queries syntax; but most likely, the idea is just very strict forward. The research progress should belongs to OSI session layer when compare with OSI model.

IMHO, Data API is a must for our future, if Drupal is going to be a web service framework. Acting as a high level abstraction should be the best position for it. It should be similar as the idea of OSI presentation layer, which is one level below our core application APIs. It may implement as OOP or whatever; BTW, according to its duty and responsibility, it should be separated from PDO database access API (where PDO is much similar as OSI transport layer implementation).

One statement as conclusion: split-brain handling.

Any else suggestion?

Database driver is something only need to be implement once and forever. Once the design of logic is completed, the rest is all about the style of implementation, e.g. using OOP as ADOdb or traditional function callback as Drupal.

The design of abstraction layer is also similar, too. Once you get a complete study of the variation between each databases, and able to foresee ALL of the needs of their specific limitation, it is all done. There is no such idea of "Contribute Supported Database" within the above 2 layers, and it is what I call as "No gray area, but only black and white". Whatever you support it, or else you don't.

On the other hand, Data API is much different. It try to serve the need of our application, and so its requirement will keep on changing depend on time. Therefore a split-brain handle should be the most suitable implementation. Please correct me if you have some better idea :-)

But the truth case is: we are lack of PostgreSQL/Oracle/DB2/SQLite/etc developers, and not enough task force nor union for a complete and perfect support for them. Up to this point, I would like to proposal the idea of "Contribute Supported Database" in this way:

1. Implement ALL possible database drivers into core, and build an abstraction that cover ALL the needs of them. This is a once forever task, and the base of the rest of discussion.
2. ALL core libraries and essential modules for installation MUST implemented as universal, so basic installation MUST function for ALL databases. E.g. node, comment, users, block, etc. This is all the duty of our core developers which need to be ensure before stable release. On the other hand, if MySQL (and maybe SQLite, too) is our main focus, just keep its support as perfect as usual.

   P.S. an additional pre-requirement for this point: try to implement ALL core module as SQL-99 compatible by default, and so save a lot of time for further more follow up handling.

3. Label the optional modules as "Not supported" or "Not perfectly supported" within modules selection page, if that is the case of specific database backend; give a different level of support status; or maybe even, disable its activation checkbox by default. Don't let our second/third priority database hold our stable release!
4. left the issue open for contribute developers if they really hope to let that module function for database A/B/C/etc, and release subversions besides stable release for this purpose. E.g. if now search and forum module are not compatible with Oracle, just let it be as Oracle developers duty!

This is my ideal proposal. So how about yours? Here is the raw file of above images, please feel free to utilize it if you hope to visualize your idea as above :-)