Forum OpenACS Q&A: Response to Article on ACS x Zope

Collapse
Posted by Albert Langer on
[CM]
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.

[AL]
My understanding is that Ben is talking about something quite different from WebObjects.

To clarify that, I have posted some notes about Web Objects in a separate topic, "Apple WebObjects", on this bboard:

http://openacs.org/forums/message-view?message_id=15107

Can we all agree that something like WebObjects is beyond the scope of what OpenACS is after for data abstraction and beyond the scope of what Zope intends to provide?

From my perspective the use of ZODB as an object persistent layer is fine for many things but completely unacceptable for Ecommerce OLTP transactions. For those you need not only to get things out of RDBMs, but also put them into RDBMs and the application is really an RDBM application.

I don't buy the idea that Ecommerce based on ZODB alone without an RDBM is viable, for reasons explained in 1) and 2) of:

http://lists.codeit.com/pipermail/zcommerce/2000-June/000257.html

BTW If you are aware of anybody else attempting Ecommerce OLTP with an OODBMs please provide a reference. Otherwise please confirm that Zcommerce efforts to do so using ZODB without an RDBM should be considered "experimental".

Despite that, I still believe Zope could be useful together with an RDBM based Ecommerce module and I am interested in whether it is possible for the existing ACS Ecommerce module to be made more "stand alone" and used with both OpenACS and Zope, or whether development would inevitably need to "fork".

I claimed that:

For access to RDBMs databases via SQL, Zope provides pretty much exactly the
sort of data abstraction facilities that Ben was talking about in:
http://openacs.org/doc/sql-abstraction.html

Based on that view I am proposing that OpenACS people and Zope people should get together in work on data abstraction.

Your comments below, and those from Jimmie Houchin, seem to indicate that I'm wrong about that.

Given that you know far more about Zope than I do, I'll have to accept your view. But before abandoning the idea I'd like to get it clear whether we are really talking about the same thing.

[CM]
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.

[AL]
Above is my understanding of what "Z SQL" offers.

But I thought the "Dynamic Data Objects" were intended to provide rather more.

Could you please comment more specifically on the following excerpt from my previous posting and explain what I have misunderstood:

**************
The next level currently requires some vOOdOO to understand "ZPatterns". See
especially the section on "Dynamic Data Objects" in the contents map at:

http://www.zope.org/Members/pje/Wikis/ZPatterns/HomePage/map

Suffice to say that it is NOT one of the three options that are possible
without "very OO database OO" but a fourth option made possible by OO. Yes, you
still need RDBM specific (or site specific) SQL for optimization, because there
is NO WAY that can be avoided. But it hides this behind umpteen layers of
framework code so that it is completely insulated from the application domain
queries and updates and UI templates written by non-SQL developers. This makes
life much harder for people implementing the framework (ie core Zope
developers), just as life is hard for people writing the internals of
Postgresql. But it makes life much easier for people customizing UIs, people
customizing queries and updates based on application domain models and for
people optimizing RDBMs engine access, because they can all keep out of each
other's way. (If it's the same bunny doing them all, at least they can think
about just one thing at a time).

***************

What I had in mind is that Ben's document refers to a simple template for inserting parameters, into SQL much like what "Z SQL" provides, but with tags like {Oracle, Postgresql} which indicate whether a particular SQL statement is suitable for use with a particular RDBM. This would simplify maintenance of multiple ports by reusing whatever can be reused and providing sufficient isolation from the rest of the application for SQL development and Tcl development to be less tightly coupled.

It seemed to me that although the "ZPatterns" framework is currently used only for multiple login authentication (eg from LDAP directory or RDBM instead of Zope folders in the ZODB), it is intended to be far more widely applicable and could be used to provide data abstraction of the sort Ben is after, by people who understand the problems of porting between different RDBMs as well as for providing application Domain Models, Roles and facades for people using the ZODB.