On Thu, May 14, 2009 at 8:10 AM, Karun Dambiec <karun(a)fastmail.fm> wrote:
From searching the mailing list archives, I have found
that Database.php
does things relating to generating queries, and not just abstraction of
the
database(http://article.gmane.org/gmane.science.linguistics.wikipedia.techn….
Yep. Not much point in abstracting the database function calls if
your SQL is a syntax error on half the databases. :)
To improve database support, I would like to suggest a
database
abstraction layer such as ADODB.
Are there any disadvantages that would result from doing work on using a
database abstraction layer such as Adodb? Or advantages that are gained
from the current methods of accessing databases.
Well, here's an example. We just had a case where the code generated
a query like this:
(SELECT ...) UNION (SELECT ...) UNION (SELECT ...) ORDER BY ...
It turns out that this breaks in Oracle. The needed syntax there is apparently:
SELECT * FROM ((SELECT ...) UNION (SELECT ...) UNION (SELECT ...)) ORDER BY ...
So this was changed
<http://www.mediawiki.org/wiki/Special:Code/MediaWiki/50478>. The
only problem is, the changed syntax uses a subquery, which doesn't
work in MySQL 4, and it had to be reverted
<http://www.mediawiki.org/wiki/Special:Code/MediaWiki/50483>. *No*
raw SQL is going to get you something that works both on MySQL 4.0 and
Oracle here (AFAIK). You need an abstraction layer that not only
passes the queries to the databases, but also generates SQL that will
actually *work* on those databases. The solution here would probably
be to add a function to the abstraction layers that looks like
return '(' . implode( ') UNION (', $queries ) . ')';
by default, and
return 'SELECT * FROM ((' . implode( ') UNION (', $queries ) .
'))';
for Oracle. How would this be achieved by just using ADOdb, without
query-generation logic? Keep in mind that we can't be purists here
about standard SQL or whatever -- the code must work on MySQL 4.0, and
it must be *efficient* on MySQL 4.0, at any cost, since that's what
Wikipedia runs.
It's all very well for ADOdb to claim to support zillions of DBs, but
is there any large web application that actually *works* on all those
DBs, just by using ADOdb and without lots of other DB-specific logic?
I strongly suspect not.