edin.no-ip.com

Support multiple database backend is always a long term approach for a successful OOS CMS development, but what makes it become such difficult and unfriendly?

Need not to mention once and once again, an open source project which able to duel with different database backend, especial a CMS project as powerful and flexible as Drupal, is always better than a single backend solution. Most likely, a powerful open source database as MySQL may solve most of our daily needs, but that's not means it is suitable to duel with ALL different cases and requirement, e.g. its clustering (or replication) may not be the best choice among the market, based on technical, stability and usability concern. Concern is sometime even political but not technical, which is not able to judge by developers: what if your boss or client request for an Oracle backend CMS system? You may try to choose Drupal based on its powerful feature and flexibility, but may also face a dark age as me based on its lack of official Oracle supporting; or proposing a MySQL backend solution, and so they will run away or you will be fired ;p

BTW, back to the main topic: what makes this ideal solution become such difficult for reality? The reason is a bit complicated, but able to summarize as following:

1. We don't need it for daily. E.g. even I am keep on promoting Oracle support to Drupal core, this personal blog is also running on top of MySQL server. We don't always need other database supporting, unless we are forced to face with such situation
2. Based on the above reason, we have not much idea about the needs of other database development. I face the same difficulty when starting my development with Oracle/DB2/MSSQL driver development. Due to the lack of understanding, I have a dark age during the first phase of development, based on different database specific restriction, e.g. special LOBs value handling, limitation in length of constraint name, reserved words conflict, different syntax of similar SQL functions, etc
3. Moreover, as we usually consider the covering area of database abstraction layer, based on single database backend concern, so the level of abstraction is always bias or not enough. E.g. sometimes we may consider about some bias handling due to performance boost of some database based on its special supporting, but it will usually result as a fancy, lossy and risky abstraction for most of the other database, which is always conflict with the idea of "abstraction": trying to standardize the differences between different database, provide a single and unique API for normal non-database-abstraction-layer developers
4. Finally, all of the above reason result as a difficult of development and maintenance. We will always need to review some handling once and once again, due to the not indeed enough of concern about different database requirement. As no one like to do so, and so we usually support for a give up of other database supporting, but single database backend solution

So how to overcome these difficulties? The main point is: giving a complete and indeed studying to most database that we are able to touch with, reference to as many of successful projects as possibile, always design our database abstraction as neutral with no bias as we can, and finally provide a complete and handy solution to our developers. Otherwise, our abstraction handling will most likely become a silly joke...

An indeed studying and research is always the base of database abstraction development, and the basic requirement of related developers. We will never understand some crazy and special database specific limitation and requirement, unless we are actually PLAYING with it. Judging the needs based on single point of view will always result as bias with no question. BTW, that is not the duty of most developers: it is only the duty of database abstraction layer developers, others just need to employ their research founding and follow it.

Reference to other successful story is always a short-cut to successful. We don't need to build our solution from scratch, but just clone the others handling based on their research founding. E.g. ADOdb is a great project that currently support for totally 19 different database, Gallery2 is now supporting for 5 database backend with no pain. They give me a lot of good ideas when developing Oracle/DB2/MSSQL drivers.

Design with no bias is another important pre-requirement of database abstraction development. We need to first forget about the issue of existing market sharing, total running cost, technical support for maintenance (and some other else), but just focus on the technical requirement of different databases. The main idea of an "abstraction" is about standardize the different database and so result as a single and handy API for normal developers, so don't put it as a political issue. Tweaking the performance or promoting the use of single database backend is absolutely NOT the duty of database abstraction with not question. Moreover, design with no bias will usually result as a progress of the database that we are mainly focusing on: abstraction and performance can sometime consider as no conflict, it is just solution dependent :)

Finally, we need a complete concern and design for most database with handy API implementation, plus some other sort of supporting in programming level. We need to hide most database variation by abstraction layer based on functionality and usability concern, provide enough hints about how to writing SQL as portable, and simplify some complicated database level handling by programming level implementation, e.g. access checking and query rewriting. According to the help of these supporting, most developers will able to develop cross-database-compatible SQL with no difficulties.

Multiple database supporting is not such evil, if we are able to keep both of our mind and doors open. We don't need to ask everyone as cross database professional, that is only the duty of database abstraction layer developers. BTW, asking multiple database support away from core but act as contribute module will never able to solve the problem, according to its bias and lack of concern and consideration. On the other hand, multiple database supporting will able to give a hand on exploration of project possibility and hidden market sharing. It is just one step more for successful, don't give up the successful of our existing progress but keep it in rolling :)