[BA] I can't say for sure that I know how Zope handles it, but my bet is on the first solution: that you're left with common-denominator SQL power. Sure, it's functional, but I'd be interested to see the actual queries performed and the resulting performance.
[AL] I don't fully understand how Zope handles it yet either, but I can confirm that you would lose that bet.
There is no SQL abstraction layer in Zope like Ben's talking about. You still need to write DB-specific SQL to gain results out of a query.
To my knowledge, the only system that abstracts this layer out well is Apple's WebObjects which I believe can use Oracle, Sybase and Informix as its object persistence layer without requring that you write any SQL. We don't do this. We instead use the ZODB as our object persistence layer, and we let people connect to RDB's and use that RDB's specific set of SQL commands. To get anything useful out of the RDB's, you need to write system-specific SQL.
Zope does abstract out the ability to process results of SQL queries. If you've got a Zope system with connections to different databases (for example if you're grabbing stuff out of an Oracle DB and a Postgres DB within the same Zope instance), you create database connections to the two DB's, you create "SQL methods" for each query for each DB, and then in Python or DTML you pass arguments in to the "SQL method" that houses each query, and you can process its results generically. You needn't worry about where the data came from. Here's an example:
SQL method "getstatus" query:
SELECT status
FROM customer
WHERE id = <dtml-sqlvar custid>
DTML method to process the results from this query:
Customer status: <dtml-var "getstatus(custid=1)">
The DTML method passes the value of custid in to the SQL method, gets the result back, and displays it.
This is the extent of Zope's SQL abstraction. You can process query results independent of the data source by creating a "database connection" to a specific DB (Oracle, Postgres, MySQL, Solid, ODBC, and Sybase) then creating "SQL methods" against these database connections that accept arguments and return results. The content of the SQL methods is necessarily database specific.
