[Zope-DB] Dynamically generate sql-query in ZSQL Method

[Ian Bicking]

garry saddington wrote:
> Now you've got me confused. Being relatively new to Zope this has left me 
> thinking whether I am doing things correctly. i am writing my SQL in a ZSQL 
> method, using DTML for logic and for gathering variables to input to my ZSQL 
> methods.

I would strongly recommend you use Page Templates instead of DTML.  It's 
easy to mix the two, so you don't have to move all your pages away from 
DTML at once.  I'm not actually a huge fan of the Page Template syntax 
-- it's fine, but a bit wordy -- but Page Templates are more explicit 
and predictable, and it's these semantics differences that I find very 
important.  It's very hard to figure out where anything comes from in DTML.

I also insulate every Z SQL method with a Python Script -- each Z SQL 
method is called by a single Python Script, which essentially wraps the 
method.  If I expect only a single row of output, for example, the 
Python Script returns just that row, not the entire result set.  I also 
typically have several asserts (for checks I can't phrase as database 
constraints), and usually the script grows more functionality over time. 
  It feels silly at first, because most scripts start out as one-liners 
that don't do anything, but that usually changes over time.

> I have a database application running from Zope with Postgresql for the last 6 
> months, programmed as above and have had not one problem with it. Does this 
> discussion mean that I should not do it this way or will things get messy 
> when I add modules to the application, which I plan to.

It's my experience maintaining applications that other people wrote, 
that maintanence is very difficult in an in-ZODB (i.e., not 
productized), DTML/Z SQL application.  So I think caution is warranted, 
even if it seems to be working well for you so far.

   Ian