Ian Bicking: the old part of his blog

Z SQL Rant

I was going to just write this to the Zope-DB list, but what better place to rant than a blog?!? Rants are fun to write and fun to read. So I give you a big rant...

Why Z SQL Methods suck:

To really understand why Z SQL Methods suck so much, you probably have to understand them in a societal context. I won't really provide that, but I will note that the ZODB has not known the neglect that Zope RDBMS support has known. RDBMS users are second class in the Zope world. But instead I will present you a list, with no particular order:

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.

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. Well, it would probably be a chapter in the DTML book.

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 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 poorly documented feature where you can 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 no good log of the SQL queries sent, how long they took, how many queries there were, etc.

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. A little error checking would be nice.

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 keyword 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 and imply not just SQL generation, but also SQL execution. You can't save values (e.g., get an ID from a sequence then do an insert), or use 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., so I can't build higher level structures)

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

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

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 you can't easily switch types, or even easily 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>...

when in fact you only expect there 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 each sequence? 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 place of the Z SQL method for some reason? Now you have to return something like [[id]]. Or [{'seq': id'}]? Only way to find out is to seek all references... 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 wrapper)

SQL methods are often tightly bound to specific scripts, templates, etc. But it's hard to express binding, and they are just more cruft to search through 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 a database compatibility layer 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 tab 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.) Didn't anyone use the test pane before calling it finished?

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. Pity me!

Created 06 Apr '04
Modified 14 Dec '04


Hi Ian,

if you're seeking what sux on zope, i think you will spend a lot of time. I really think, you should look at what is working fine because:
- ZClass
- ZODB index
- default security ..
sux ..

so ?

Bye Bye
# Jkx

A trick I use to get fast at the generated SQL query: I put a "dtml-raise string" around the SQL stuff directly in the ZSQL method. That throws an exception with the SQL that will be sent to the database as the error value.

Another option is turning on logging in your database (easily done with PostgreSQL).
# Georg Bauer

you didn't even mention the way it's inferior to the existing python db api (though, to be fair, it predates the dbapi) ... no smart formatting of types, no manual control over transaction boundaries, and as you mentioned, no .fetchone() . I'm amazed that it hasn't been scrapped for a thin wrapper around the dbapi.
# Ethan Fremen

One way to get around the "hardcoded connection" problem would be to allow a TALES expression to be evaluated to specify the connection object. I remember suggesting this a while back, and promptly forgetting about it because I don't really use ZSQL Methods.

I agree with your assessment. I am apparently going to be using SQL on a project pretty soon, so maybe I can help try to fix some of the lower-hanging problems whilst I do that if my needs become more than trivial.
# Chris McDonough

ZSQL does indeed really hurt one's soul to use, in its current state (for anything behind the most basic lookup). It hurts me to say this even, because I think it really is such a great idea: to apply a template-like solution to the problems of generating dynamic SQL. I find the concept much nicer than programmatic SQL generation (leaving aside the topic of ORMs).

But in this case unfortunately we are stuck with DTML. And a rather limited DTML toolkit at that, as has bee noted. I could even live with the extreme ugliness of DTML if only it had the more robust expressiveness required for doing more complicated SQL generation --- as has been ranted about many places. And, as also noted, the extreme neglect that ZSQL has received. There are some good patches out there that are completely ignored.

I have been trying to think of (and i'm not the first) a more TAL-like approach, applied to SQL. It's not so easy since SQL is a rather different beast than markup. Also one does (in my opinion) need to employ logic and conditionals in your SQL templates, where they should try to be avoided as much as possible in presentation templates.

Ah well.. maybe someday someone will think of/do something.

# Tim Middleton

entire books on why dtml is bad ? how so ? maybe a page sounds logical. Then again every technology has its drawbacks. man is not a perfect animal and is not known to create perfect systems.

SQL methods can defenitely use some improvements though. Maybe a SQL method next gen project is overdue !

# sathya rangaswamy