Forum OpenACS Q&A: Response to Article on ACS x Zope - DB abstraction
Let's put it very simply: Oracle has outer joins. Postgres does not. If you have some sort of DB abstraction where you supposedly have nothing to change when you switch from Oracle to Postgres, it means one of three things:
- your DB abstraction never uses outer joins, so you're less efficient on Oracle, and in fact you're stuck with the lowest common denominator of SQL.
- your Postgres query breaks because it doesn't know outer joins.
- you've written a complete SQL replacement that encompasses all the concepts of SQL (tables, joins, references, aggregates) that gets automagically transformed to the correct underlying RDBMS-specific version of SQL.
There is a reason for using an RDBMS, and that is the efficient storage and retrieval of data. Adding abstraction on top of that can only make things less efficient. It's not an easy compromise to make to simply give up efficiency for compatibility. OpenACS is trying to see how best to approach it, but the answer is far from simple.