DB support as contribute: is it a good idea?

Abstract

Drupal 6.0 is revamped with Schema API, so what's next for Drupal 7.x? PDO for sure! With this powerful data-access abstraction layer, workload will much reduced for DB abstraction layer designers and developers, and finally benefit our contribute developers and end users.

By the way, together with the decision of Drupal 7.x + PDO, there is also some voice about moving PostgreSQL (and so other potential databases support, e.g. Oracle, DB2, MSSQL, etc) support away from core, but contribute; on the other hand, add official SQLite support into Drupal core, together with MySQL.

Is this really a good idea? Or even if it is possible? As an existing Drupal + PostgreSQL users, what will this affect your daily work? As a potential customer of Drupal + Oracle/DB2/MSSQL/etc, is this a good new for you, or just an evil? I would like to provide some brief idea for you within this article.

Background

After more than 6 years of PostgreSQL supporting in Drupal core, PostgreSQL users are going to have a revamped experience with Drupal 6.0. Drupal 6.0 will ship with Schema API, which simplify most of the variation between databases handling - DDL. This useful improvement will not only benefit our existing core and contribute developers, but also simplify most workload for other database driver development, besides MySQL and PostgreSQL. With Drupal 6.0, we can simply foresee the expand of existing market sharing, together with the explore of hidden customers.

As Drupal 6.0 is ready to launch, it is also a good timing to plan for Drupal 7.x. As we will only support PHP 5.2.x or above in Drupal 7.x, it is for sure that we will revamp our existing DB abstraction layer with the help of PDO. It does simplify and standardize a lot of function call implementation, together with some useful new features. If you are a DB abstraction layer designer and developer, moving your work to PDO will save you a lot of time.

On the other hand, some people also think it is a good timing for adding Oracle, SQLite, DB2 and MSSQL support into Drupal core. Most of this request are halted for 2~3 years.

Some of the developers are hard working with the future plan for how Drupal 7.x DB handling should looks like. BTW, the idea is most likely for expand Drupal usability and functionality, but not about maintain nor expand different databases supporting: some developers even think we should only official support MySQL and SQLite with Drupal 7.x + PDO, but move away PostgreSQL (nor other potential databases) supporting as contribute level.

What PDO Does and Doesn't?

For finding out why this brain storming happen, we need to have some idea about what PDO does and doesn't:

• PDO is a data-access abstraction layer, but not provide a database abstraction. E.g., this means we can simply forget the function API differences between mysql_connect(), mysqli_connect() and also pg_connect(): we just need to construct a new-standardized PDO object with different DSN (Data Source Name). Moreover, we can forget those complicated programming logic differences between pg_escape_bytea(), OCI-Lob->load() and db2_lob_read(), but simply using standard Stream API as PDO LOB (Large OBject) handling.

  BTW, these are most likely abstracted by Drupal 6.x DB API, e.g. db_connect(), db_query(), db_decode_blob(), etc.

• PDO provide prepared statements and variable binding, even it is not supported by database, e.g. MySQL. With PDO variable binding, we may use ? or named-variable (leading with :, e.g. :value) within prepare statement. This seems to be a good idea for replace our existing user-space printf-syntax (e.g. %d, %f, %s, %b and %%) as PDO's C/C++ implementation, and simply forget the relationship between data, data type and database column type. This also give a great hope to Drupal 7.x Data API research and development.

  So isn't it perfect? No, it is not... First of all, most databases require special LOB handle during variable binding (specific PDO::PARAM_LOB manually), e.g. PostgreSQL, Oracle, DB2, etc. Only MySQL and SQLite are able to handle variable binding without type specified. Also, there is no spotlight performance different, even we swap queries variable binding from user-space printf-syntax to PDO, which is proved by benchmarking result.

• PDO isn't a database abstraction layer, it doesn't abstract and standardize ANY database variation. E.g., PDO doesn't solve the problem of reserved words conflict, it don't abstract any SQL function name variation, it also have not duty with resolve the maximum string size limitation across most databases.

  Let's take Oracle LOB handling as an example: PDO give none of help to Oracle LOB INSERT/UPDATE handling, we still need to use RETURNING + Schema API. According to my research progress and case of Oracle, using standardized function call is the only benefit of using PDO, but didn't help or simplify any programming logic or implementation.

IMHO, PDO is very useful for DB abstraction layer designer and developer: it do simplify most of the DB function call variation; BTW, when we are talking about "PDO can improve database abstraction and so software with PDO can support more databases", it is not really truth. PDO don't have duty with this ;-(

How about Drupal + PostgreSQL (or other databases) support as contribute?

It is for sure that MySQL own around 90-95% of our existing market sharing; it is not question that SQLite is a manifest typing database engine; based on the above PDO stuff information, MySQL and SQLite are both very suitable with PDO lossy-form variable binding technology (able to use ? and named-variable binding without type specified). But when we are talking about "How many percentage of PDO technologies are utilized by MySQL and SQLite?" and "How many percentage of technologies that PDO NOT covered are used by MySQL and SQLite?", I guess the answer may just below 70%...

So what will happen if we seem MySQL and SQLite as our first priority core supported database, and ONLY focus with them? For sure that we will miss out the rest of 30%, which means the basic elements needed by other databases, within Drupal database abstraction layer. This also means asking other database drivers implement as contribute is just a nonsense, if we only support a subset of requirement within core.

On the other hand, writing MySQL-specific queries also generate a lot of incompatible issues for PostgreSQL during Drupal 6.x development life cycle. People also claim that PostgreSQL issues slow down our Drupal 6.x release. BTW, there is still a lot of incompatible issues even PostgreSQL is now seems as our "Official Supported Database" in Drupal 6.x, we can simply foresee about the case if we just seems it as contribute supported. It is better say that: we are NOT going to support PostgreSQL anymore! And so the similar case for other databases driver development: we will have NO WAY to implement other database drivers as contribute support!

PDO + PostgreSQL + LOB handling should be a good example of this assumption. In case of pdo_pgsql, we need to specify PDO::PARAM_LOB manually during INSERT/UPDATE variable binding, and must use Stream API for BLOB decode. Both of these handling are NOT required by MySQL and SQLite. If our Drupal 7.x Data API design are target for MySQL and SQLite ONLY, it is for sure that NO WAY for PostgreSQL driver implement as contribute: we will have no hook for it!

As a simple conclusion, multiple database support MUST build inside core, with a complete research of what are they needed for. There is no shortcut, and no gray area. The support of database is just simply a TRUE/FALSE question. Please don't be faked by the beautiful wording of "Contribute Supported Database": it is just a sweet poison candy, that is not the truth case, and I have NEVER seen a successful story about this :-p

Oh my god! So what should I do for it?

If you are an existing Drupal + PostgreSQL user, it is strongly suggest to keep you eye focus on the Data Architecture Design Sprint group, comment your needs and wish to them, or else you may be fired away without notice. You may also have a look about issues related to PostgreSQL, give a hand to it if possible, and comment them as a Wiki collection. IMHO, it is a critical timing for Drupal + PostgreSQL besides the past 6 years support period. It is time for YOU to take YOUR action, or else will be too late.

If you are a potential customer or user of Drupal + Oracle/SQLite/DB2/MSSQL/etc, beside the above suggestion for PostgreSQL, I would also like to invite you to give a hand on some existing issues, review and comment them, and finally record your work in another Wiki tasklist. According to some research founding, we would able to enrich our database supporting without a critical pain of revamp, based on our existing Drupal 6.x DB API implementation. Most research are now completed, so issues review and comment your point of view should be your highest priority. Please remember that: comment is power, and YOUR review is very important for speeding up the core support of your target databases.

My humble conclusion

Every developers have their own background, their own wish, their own needs, and also their own rose garden. I would not like to comment this "PostgreSQL as Contribute" idea as silly; but at least, it is not my cup of tea.

On the other hand, supporting more database won't be a conflict with the research and develop of Drupal's Schema API or Data API: they are target for a better developer and user experience, serving database abstraction should be one of their core duties. Asking for better abstraction and functionality but reduce the number of supported database, or even close the door for other database should be count as loss focus.

From my point of view, more choice is always better than just giving me a single solution. The use and choose of database should depend on the needed of each individual case of clients and users, but not forced by developers. If we are already close to our target, there is no point for rollback our 6-years progress on tomorrow.

A beautiful-sweet-poisoned candy? No thanks!