On Wed, 28 Nov 2007 11:48:23 +0100 "Chris Johnson" <cxjohnson@gmail.com> wrote:
It's easier to find work-arounds and translations from standard SQL to MySQL syntax, and do the same thing to translate to another reasonable database (Posgres, Oracle, MSSQL), than it is to reverse translate MySQL (or other database) specific features to other databases.
Agree.
Standard SQL is the sort of the greatest common factor (in the mathematical sense). Even more significantly, hundreds of MySQL people have spent significant effort in coming up with standard SQL equivalents in MySQL, so most of that work is already done for us.
Maybe there is an open-source tool which can validate standard SQL which could be used. Then our DB abstraction could require a verifiable standard SQL syntax which would then be translated (where necessary) into the MySQL, Oracle, Postgres, etc. equivalents. This might only be for CRUD operations (data manipulation statements) and not for DDL, for which we already have a reasonable API and which are much less standardized.
On the long run this doesn't give space for optimisations. People should write SQL with standards in mind and not MySQL in mind but the DB abstraction layer should leave a door open for optimisation. There should be a default path that doesn't impact on performances (including both mysql and pgsql code) that let module writers write custom code for a particular DB *if* they are willing to do so. There was a thread that share some aspect with this problem about "numbers of loaded module and performance" and guidelines for module developers.
I myself always use MySQL for my Drupal installs -- because it will work. Thus, I never test them with Postgres, even though I run Postgres on several systems, am very comfortable with using it, etc. I just don't have the time to hassle with discovering some obscure SQL bug resulting from database incompatabilitilies when I'm just trying to make a site work.
I use pgsql for drupal and till now I installed stable versions of drupal where most stuff was working out of the box in core (there was a minor issue in length of watchdog messages where MySQL silently trim stuff while pg complains) and fixes for the modules I use were trivial. I've to learn how to use the drupal "patch/issues" infrastructure to give more chances the patch I made get into modules/core smoother. I still have to read carefully all the coding standards.
So unless we actually push the issue of supporting multiple databases as a community, the vast majority of developers will never make the effort. And with each passing release spent in that mode, the harder it will become to reverse it. We will end up supporting only one database, MySQL, de facto.
Absolutely agree. But it is a problem of chicken and egg. dev don't care cos there isn't enough abstraction in DB layer and the code is too mysqlish, the code continue to be that way because most dev are surmounted/ignore how to really deal with multiple DB. I sympathise with Chris Johnson. I understand the frustration of core dev when they have to deal with the latitancy/scarcity of pg dev trying to improve/give suggestions etc... about performance on pg. But again this is a problem of chicken and egg. Now everything that is going to change/add work to the MySQL side is seen as an annoyance because it is actually complicated to improve stuff for pg without degrading performances for MySQL. In the light of the fact that MySQL is going to have the same features pg had for years, I would think it would be a good bet to improve the abstraction layer for BOTH so to be able to use the most advanced features that BOTH will have. Anyone that didn't deal with MyISAM only know there are places where some of these features can improve performances and code writing *a lot*. I think most people here know there is no silver bullet when you've to deal with flexibility and performance and DB abstraction layers are not an exception. -- Ivan Sergio Borgonovo http://www.webthatworks.it