[Zope-DB] Dynamically generate sql-query in ZSQL Method
Peter Sabaini
peter at sabaini.at
Wed Apr 7 13:50:34 EDT 2004
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
More information about the Zope-DB
mailing list