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

[Jim Abramson]

I would recommend that if your ZSQL Method stuff isn't broken, don't fix it.

It is difficult to do complex things with a transactional rdbms in Zope. Along with
Ian's long list of salient points, I'd add (right up at the top) the complete 
absence of control over transactions - a total nightmare if you have some event
that you need to trigger a number of write operations that aren't encapsulated in
a single statement / procedure call.

If you need to do this in Zope, the only sane way in my opinion is to externalize - 
write standalone python that takes a reference to your database connection object
as one of its params, and use it from zope as an external method or product. You can
run, test, twiddle this all you want from outside zope, and say goodbye to the
zsql/zmi/dtml hassles (while saying hello to the annoyance of having external, as
well as internal, assets to manage). You can even pull in database connections from
some other place than zope, and say goodbye to the nail-biting builtin transaction
mgmt that zope inflicts. The trade-off is more coding overhead, but you save countless
hours of hair-pulling trying figure out what zope is doing to your database and 
result data.

But of course, you needn't bother with this if zsql methods are doing the trick for
you! Despite its known flakiness, ZSQL is stable, and certainly known initmately
by many good folks (on this list for example), and if you've been running an app for
six months without problems, you probably don't need anything else.

Jim

> -----Original Message-----
> From: garry saddington [mailto:garry at joydiv.fsnet.co.uk]
> Sent: Wednesday, April 07, 2004 12:00 PM
> To: zope-db at zope.org
> Subject: Re: [Zope-DB] Dynamically generate sql-query in ZSQL Method
> 
> 
> On Wednesday 07 April 2004 12:35 am, Peter Sabaini wrote:
> > Ian Bicking wrote:
> > > On Apr 6, 2004, at 3:23 PM, Jim Penny wrote:
> > >> Ian has told you how to do it.  Now, I will tell you, 
> don't do it.
> > >
> > > I would disagree with you -- not for some principled 
> reason, but for a
> > > very practical reason: Z SQL Methods suck.  A lot.  A huge, huge
> > > amount.  They are horrible.  Generating your own SQL 
> inline sucks.  A
> > > lot.  It is horrible.  *Sometimes* the inline method is 
> less horrible.
> > > These are the unfortunate choices foisted upon us by the 
> environment.
> > >
> > > (Actually, it's Z SQL plus the ZMI plus DTML that is the 
> real poison,
> > > IMHO -- they all work together to make things extra unpleasant)
> >
> > Out of curiosity -- what is so horrible about ZSQL Methods? 
> I use them
> > quite extensively (though mostly to talk to stored 
> procedures) and may
> > have some nits but dont find them _that_ horrible...
> >
> > > Note also that well designed SQL generation in Python 
> isn't horrible.  A
> > > Python script like:
> > >
> > > ## select_all
> > > ## parameters: table_name
> > > # re isn't available in the default Zope installation; 
> I'd actually
> > > usually # do this with an extension method...
> > > import re
> > > assert re.search(r'^[a-zA-Z_][a-zA-Z0-9_]*$', 
> table_name), "Invalid
> > > table name: %s" % table_name
> > > return container.raw_sql(sql='SELECT * FROM %s' % table_name)
> > >
> > >
> > > That isn't so bad.  In fact, I would assert it is much 
> better than the Z
> > > SQL analog.
> >
> > Personally, I'd rather encapsulate this in a stored 
> procedure, or, if
> > this isn't possible, in a Python class if I really _had_ to 
> dynamically
> > determine a table name.
> >
> >   - peter.
> 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 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.
> regards
> garry
> 
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>