On Thu, 14 May 2009 09:50 -0400, "Aryeh Gregor" <simetrical+wikil...@gmail.com> wrote: > Yep. Not much point in abstracting the database function calls if > your SQL is a syntax error on half the databases. :) >
In terms of performance, it could possibly be better to write queries for each database type. > 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 ) . '))'; An option could be to abstract the queries more for each database type, and not need to use the same query for MySQL, PostGreSQL and Oracle. That way the optimum query for each database type could be used to retrieve the information. > > 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. A potential problem of using something like ADODB is the overhead it adds, although from their website it is smaller than other database abstraction layers. > > 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. > There are some web applications that use ADoDB such as Mambo, Joomla, etc, but I have never tried installing them with a database other than one of the ones their website states is supported. So I have not been able to evaluate that it would work on all databases supported by ADoDB. _______________________________________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
