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

[Peter Sabaini]

Ian Bicking wrote:
> 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?

Ok, not to over-defend ZSQL methods, but still...

> 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.

I must admit since I mainly use stored proc., the issue comes rarley up 
for me. I use dtml-sqlvar, passed in from a Python product and thats it.


> 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.

Right; thats one reason I make do with dtml-sqlvar in an environment I 
can control, ie. Python products. No acquisition save for getting at the 
db connection, no namespace games etc.

> 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.

Yes of course, but ZSQL methods would have to be much more database 
specific to support this fully

> 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.

Thats true. There are workarounds though: for instance some Dbs come 
with their own profiler / trace utility (MSSQL and Oracle I think)

For other Dbs, theres cheap way out: patch the DA to append the rendered 
SQL to a log file. I did this for a Interbase DA (I dont have the source 
at hand, but its not too hard to find)

> 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.

I dont think it would scale well to have a whole lot of different 
connection objects -- say if you wanted to give every user their own 
connection

I think it would be not to error prone to have a few connections eg. one 
for anon users and one for management access and acquire them

> 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.

Oh well, I guess I've gotten used to it :-)

> 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?

AFAIK they signal missing parameters quite clearly? Youre right with the 
extra parameters

> 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.

Ok, thats a point

> 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)

IMHO one has to remember that its really necessaary to keep complexity 
out of DTML -- be it DTML Methods or ZSQL. Do complicated stuff either 
in a stored procedure or in Products

> 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.

IMO thats the realm of O-R mapping libraries (which of course come with 
their own problems)

Have you tried APE? I only played around with it a little but so far it 
looks great

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

Yep

> 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.

Thats the price to pay for C coded result objects. If you dont need 
that, you can get yourself the data  wrapped in dictionaries()

> 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.

Thats true. I often end up wrapping my results in custom objects and 
then caching them in (some sort of) RAM Cache

Dieter Maurer did a product to make ZSQL Methods more cacheable IIRC, 
could be worth a shot

> 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).

Use Python --

row = zsql(param=val)[0] ?


> 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)

True, there tend to be a lot of little ZSQL Methods, but its around 5 
lines of code in a Product. I can live with that.

> 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)

I dont quite follow you there? Why is positional better?

> 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.

As I've mentioned, I place all my ZSQL methods in Product code, so thats 
  not much of problem for me

> 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.

Tell that to the folks who wrote the Python DP API Spec.

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

You're of course right -- I'd say go right ahead and do a Db specific 
introspecting DA-with-ZSQL methods :-)

> 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)

True

> 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.

I think you might be just overusing ZSQL methods, ie. do too much logic 
with DTML -- which is of course plenty  of reason to get angry ;-)

I'm a happier man since I started using Python Products, and that 
includes ZSQL Methods :-)

That being said, I'm really looking forward to doing something with APE 
once I get the time (which wont be any time soon :/)

cheers,
peter.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3216 bytes
Desc: S/MIME Cryptographic Signature
Url : http://mail.zope.org/pipermail/zope-db/attachments/20040407/51e27209/smime.bin