In response to "Chris Johnson" <cxjohnson@gmail.com>:
On Nov 28, 2007 1:45 PM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:
On Wed, 28 Nov 2007 11:48:23 +0100 "Chris Johnson" <cxjohnson@gmail.com> wrote:
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.
Well, actually I'm very much in favor of optimized SQL, because I'm very much in favor of optimized Drupal. I hate software bloat.
So what I really think -- and this is a truly unpopular position -- is that most of the SQL should be written by "core" SQL coders, and the rest of us should be accessing data through object abstractions, i.e. get me a node, get me a user. Underneath is the highly optimized, shiney, sparkly, incredibly brilliant PHP and SQL code written by the 'database access guru core team'.
Ok, I'll put the crack pipe down now.
My goal with the above suggestion was just to make it easier for people to write the standard SQL to MySQL || PGSQL || MS SQL || Oracle abstraction-layer translation stuff by helping authors avoid difficult to translate non-standard-isms. If developers can avoid creating modules which depend on unique special extension X in MySQL or special extension Y in Postgres (unless they really meant to), then we'd be better off in our question to support more than one database.
I suppose I have not made things much clearer. For some reason, I'm plagued with complicated phraseology today. :-/
I think you should smoke some more of that crack pipe. If you look at an OS (Linux or BSD or whatever) you'll see the kernel divided into machine dependent and machine independent parts. For example, you can't boot an ARM processor the same way you boot an i386 CPU, but most of the other things the kernel does can come from the same code. I expect that the right way to make Drupal _truly_ database neutral is to have database-dependent and database-independent code. You'd only see this kind of thing at the lower levels, and higher logic code shouldn't even care about it. Take, for example, that COUNT(*) is _really_ fast on MySQL + MyISAM, but is slow on just about every other DB. So, replace it with $db->count(), which does an SQL COUNT(*) on MySQL + MyISAM, but uses some other technique on other databases. The programmer no longer cares _how_ such a thing gets done, he only cares that he wants to know how many rows are in a particular query -- Bill Moran http://www.potentialtech.com