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

[Ian Bicking]

On Apr 6, 2004, at 6:35 PM, Peter Sabaini wrote:
> 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...

Oh, where to start?

There's several ways to write a query, depending on how many of the Z 
SQL features you use (e.g., dtml-and, dtml-sqltest, dtml-sqlgroup, 
etc).  They all look quite different, and some will feel very foreign 
to someone who knows SQL but not Z SQL.

It uses DTML, or DTML-like constructs.  Most of the things that are bad 
about DTML for templates are also bad for DTML generating SQL.  You 
could write entire books about why DTML is bad.

It usually looks like normal SQL, but is far enough away from SQL to be 
difficult to work with in source form, since it uses <dtml-sqlvar> 
instead of like parameters like ? or even %s.  It's usually a long way 
from Z SQL source to something you can feed to EXPLAIN.

It is difficult to debug.  There's one largely hidden feature in being 
able to pass src__ in as a keyword argument to see what the SQL source 
actually is.  That's about all the debugging you get.  AFAIK, there's 
not good log of the SQL queries sent, how long they took, etc.

Connection paths are pretty much hard-coded.  You can't tell a method 
to use a different connection.  This would be useful if you wanted to 
use database-level security, and so you need two separate connections 
with separate users.  You might be able to do this with acquisition, 
but whole books could be written on why acquisition is bad.  Actually, 
it would probably be a chapter in the DTML book.

It has crufty parameters.  Put "a, b, c" in , and you get parameters 
with names like "a,", "b," and "c".  Default parsing seems wonky too.

It doesn't signal errors properly -- if you forget to pass in a keyword 
argument, it's pretty much ignored.  Or if you pass in extra arguments. 
  Why have a parameter list at all?

It doesn't deal with failures very well.  Why not give me the SQL in 
the exception when there's an error?  It doesn't deal with any of the 
hard parts of failure in general, connection failures, concurrency 
conflicts, etc.

You can't nest or factor your methods well.  This is largely DTML's 
fault, but Z SQL makes it worse since all methods are concrete.  You 
can't save values, go through control structures, etc., except using 
plain DTML constructs which have their own issues.  For instance, 
consider turning a list of items into SQL for use with IN, like 
(<dtml-in items prefix=loop><dtml-unless 
loop_start>,</dtml-unless><dtml-sqlvar loop_item type=int></dtml-in>).  
That's lame, *and* you can't abstract out the lameness.  Well, maybe 
with the use of src__ and other stuff, but that's it's own lameness.  
(Hmm... I see now there's an option to dtml-sqlvar to handle this 
particular case... so many options, but I can't use any of them from 
the outside, nest dtml expressions, etc)

Oh, on the subject, every issue is dealt with using another dtml-sqlvar 
option or another tag, there's not a good set of orthogonal functions.

SQL Brains are cool, if a still little under powered.  But they don't 
see updates unless you go twiddle all sorts of forms.

You can't set any attributes on result rows.  Even if you know why it 
might not be a good idea.  Even if you try really hard.  (Though you 
can add a dummy column to your query, and then reassign the column)  
This is annoying particularly when using brains.

Caching is stupid.  You can cache, but you can't invalidate the cache.  
There's a product out there that provides a cacheable SQL method, but 
it's a whole separate product and item type, and you can't easily go 
back and forth, or even upgrade.

All methods return result objects with multiple rows.  It's common to 
see:

   <dtml-in "get_some_object(id=whatever)"><dtml-var 
some_column>...</dtml-in>

When in fact you only expect their to be one row in the result.  This 
is where DTML's suckiness gets involved (dtml-in/dtml-with, all the 
flattened namespaces, etc).

You start needing all sorts of methods for little things.  Like, say 
you want to fetch a value from a sequence, like "SELECT 
nextval('sequence_name') as seq".  Do you create a new method for this? 
  What an annoying process!  (ZMI partially at fault)  And you get 
lameness like <dtml-in get_sequence_name><dtml-var seq></dtml-in>  Oh, 
it hurts me just to type this!  Or container.get_sequence_name()[0][0]. 
  This leads to code being highly coupled.  You want to put a Python 
Script in the middle for some reason?  Now you have to return something 
like [[id]].  Or [{'seq': id'}]?  Only one way to find out... oh, the 
coupled pain!  (This is why I keep a firm Python Script barrier between 
my Z SQL methods and my "good code" -- I don't want Z SQL cruftiness to 
infect everything else with it's lameness)

No positional parameters.  Seems like an okay idea, except for all the 
methods that are fetch-row-with-this-id, where positional is better.  
(Something else for the Python Script barrier)

SQL methods are often tightly bound to specific scripts, templates, 
etc.  But it's hard to express this, and they are just more cruft in 
the ZMI.  If something is tightly coupled, I want to know it, I want to 
know that changes effect only one place, or that I shouldn't use it 
from the outside.  At least inline SQL gives you this.

dtml-sqlvar doesn't have any good types.  type=date please?  Some 
attempt at making database more compatible would be nice.  Strings and 
floats are easy.  Solving difficult problems is actually useful, just 
solving easy problems is a programming cop-out.

Doesn't do anything with database introspection.  Again, I know it's 
hard, but hard is useful.  And it's not that hard.

The test pane is really lame.  I won't go into all of why it's lame.  
It could be useful, but it's only distracting.  (I might *think* I can 
just test a quick query in it, but I'm always wrong)

I think that's it.  I'd say it felt good to vent, except I'm going to 
have to work with Z SQL methods again tomorrow, and venting doesn't 
make up for that.

--
Ian Bicking | ianb at colorstudy.com | http://blog.ianbicking.org