Jody Winston wrote:
I need some help with the Zen of Zope and SQL. I've tried several different approaches and I have not been happy with either result.
The goal of this Zope project is to allow individuals with different roles (e.g. views) to perform queries of objects in a database. These queries will run from simple queries, such as how many times a company has used a service, to complex queries to build final reports.
Zope was selected over a report generating product since Zope provides security, a web based management system, different ways to submit and to retrieve data, and Zope didn't tie us to a single, closed, data source.
Very good reasons.
The first approach was to have the most frequently used queries for each role stored along with some code to render them. This was too static and inflexible for the "power" users. The advantages were that the data was always insync with the SQL database since it was always generate from the database and that we knew how long the queries would take.
Right.
The next approach was to used the primary keys in the table to generate trees of the data. This approach allowed quick navigation and retrieval of the data. The disadvantage was that selecting the data by it's primary key does not allow us to select data that the role was interested in.
The third approach was to build ZClasses that had the same attributes as a row of the data in a given table. The attributes of a given instance were then populated from the results of an ad-hoc query. The GUI for querying the data was changed to be a form with multiple selection fields for every attribute in the class. ZClasses allowed the results of the queries to be stored in ZODB. The traversal of the results, a tree, can then be traversed and examined with the normal Zope mechanisms. This feature comes at the cost that the results of the queries will become out of sync with the database. The ad-hoc queries from this approach will not be as fast as optimized queries.
Ok, I think I see what your getting at here, and to tell you the truth you have actually hit the nail on the head on a new feature that will be in a post 2.0 release, the ability to select ZClasses as the 'plugable brain' to a ZSQL query. See, ZSQL queries, and Catalog queries, return what are called brain objects. brains are simply instances of a certain class that represent a 'result row' of a query. Often they are an object who's attributes are the results of the query. For ZSQL method in 2.0, you can specify such a class written in Python in the filesystem. After 2.0, you will see the feature of being able to either select a python class on the filesystem, or a ZClass. In either case, what your looking for are plugable brains, and for 2.0, you'll have to write it in Python. Plugable brains are especially intersting in regard to giving each result object it's own managment interface, in the least.
I'm starting to lean towards a hybrid approach of canned queries for the 80% solution with forms for the other 20%. What's the general thoughts on this approach? Will the use of the ZClasses help when the underlying data is in a SQL database?
Another issue is disconnected access from the main database/Zope server. A portion of the data from the database, either from ZODB or the SQL database, needs to be replicated from the server to the client at the client's request. The client then will become a its own server until the client reconnects with the main database/Zope server. Currently, we do not need to rectify the differences between the two Zope instances, but I can see that day coming. Any ideas on how to support this need with Zope?
Sychronization is a deep issue. In many cases the needs for sychronization can be solved with a scalable central solution. ZEO is such a system, but doesn't currently do unconected work. It will, however, keep a slew of 'Zope database clients' in sync with one central object database. BTW, for those of you who have been wondering what ZEO is about, there will be some information soon. In a nutshell, it is a client/server implimentation of our database software, which let's a number of Zope clients, really full blown in memory Zope processes, use a central object database over TCP/IP. We have a working alpha version at the moment, and it requires more baking, but it is very, very neat. Watching the demo work is very convincing. Multiple remote 'Zope' instances on various machines can access and synchronize a central database server; when an DTML document is edited in California is is seen here in Virginia. There is intelligent client-side caching, a clean and fast protocol, and many other good features. When used with tunelling SSL, it comes in handy as a very secure synchronization system. ZEO will be a commercial product. We expect it to be able to scale as well as any other dynamic object system. As a bonus, the operation of the client/server database is transparent to the managers of the Zope system, so everybody learns and uses the same interface into the same database, which is identical to non-ZEO Zope systems. ZEO works at the database level, and is transparent to the higher level Zope API, thus, all existing Zope applications will work out of the box with ZEO. -Michel
Jody Winston
_______________________________________________ Zope maillist - Zope@zope.org http://www.zope.org/mailman/listinfo/zope
(To receive general Zope announcements, see: http://www.zope.org/mailman/listinfo/zope-announce
For developer-specific issues, zope-dev@zope.org - http://www.zope.org/mailman/listinfo/zope-dev )