Forum OpenACS Q&A: Response to Article on ACS x Zope
The ZPatterns stuff is the brainchild of Phillip Eby, a developer at Verio (the hosting provider). It (among other things) is a mechanism to provide medium-independent "propertysheet" support. "Propertysheets" in Zope are rectangular sets of typed data that can be attached to Zope objects arbitrarily, and as such lend themselves very well to storage in an RDB table. And I think this was probably what Philip had in mind in providing Dynamic Data Objects. Although to be honest, I might be totally wrong... I can't really tell what the intent is now. I've heard it's a good set of ideas, but personally I'm stumped most of the time when I read his stuff because he names things very opaquely and seems to explain things in such a way that you *do* need to community college C++ degree to begin to grasp it. I think it's one of those projects that needs to gain some traction and obtain a critical mass of real-world examples in order to make its ultimate intent clear to mortals like me. Until then, I can't speak much for it, although I do recommend you continue looking at it. (Maybe you can write some code using it that would explain it to me!)
ZPatterns is not part of the Zope distribution.
I think Ben had in mind at least in his most recent posting an abstraction layer that allowed RDB-savvy people to write SQL-like queries that would automagically be translated into their equivalent optimized Oracle- or Postgres-specific SQL dialect at runtime. I don't think anybody in Zopeland has the intent of writing such a beast.
You're probably right in assuming that the ZPatterns stuff is about as close as it gets to wanting to do something like that. But Zope propertysheets are generally by nature pretty small fixed sets of data (maybe 5 - 12 properties per object). You would need to declare some sort of table structure for a particular object type, create the tables to store the propertysheet data for those kinds of objects, then do the right voodoo to tell the objects to obtain their properties out of SQL instead of out of the ZODB. This could probably be abstracted in such a way that people don't need to write any SQL, but it would never approach the sort of complexity that I think Ben is talking about because it would probably practically necessitate an approach something along the lines of defining a table to hold propertysheet data for each different object type. If you wanted to do ad hoc queries against an existing set of data in multiple tables (ones that might require unions, outer joins, or insert selects) would need to be (just as painfully) manually constructed in the SQL dialect of your choice. Personally, I'd probably try to keep it simple and create views on existing tables that would let me operate as if I were doing so on a single table.
Somehow I get the feeling that in this conversation we're all talking an entirely different language than one another. I assume that Ben's concerned about complicated SQL abstraction between Oracle and Postgres because he wants to get rid of Oracle dependencies in ACS. I think Albert's talking about being able to abstract the SQL out enough to not have to think about it at all when constructing an application. I'm probably just plain not thinking about it at all.
I looked at Ben's sql-abstraction page... I don't know any tcl, but I think I get the gist.
It would seem to me to make sense to abstract out both 1) the access mechanism for querying and the 2) resultset that comes back from the query. In Zope we do the first part by creating a "method" that contains the actual query (which can be as DB-specific or generic as you like), connecting it to a database connection (a pool of database handles for a specific DB type). You can then pass in arguments to the method that have counterpart template definitions within the query itself (ala CUSTID = <dtml-sqlvar abc>), and the method passes the query in to the DB and obtains its results.
The second part (returning the results of the query to the caller) is handled in Zope by wrapping the resultset in a class instance that lets us process those results generically. It would probably be good to do something like this in ACS by defining a datatype that could hold arbitrary common elements that could be processed in tcl code generically.
Though it's not a trivial engineering task, it seems like the biggest problem isn't implementing it, but encouraging people to use it. From what I understand there's a large body of existing code that does not use any sort of abstraction like this. Convincing folks of the usefulness of writing against your interface instead of yanking the stuff right out of the tables in their code is probably the hardest task.