[Zope-DB] How can I dynamically generate a Z Search Interface?

[Jim Penny]

On Sat, Sep 07, 2002 at 08:25:39PM +0200, Dieter Maurer wrote:
> Jim Penny writes:
>  > On Fri, Sep 06, 2002 at 08:33:00PM +0200, Dieter Maurer wrote:
>  > ...
>  > > Database adapters can be called to get a database connection.
>  > > 
>  > > A database connection has a "query" method to send SQL commands to
>  > > its database.
>  > > 
>  > > Going this route lets you leave the well documented path.
>  > > You will probably need to read sources and experiment...
>  > 
>  > Moreover, you will lose some things and gain nothing.  
> I disagree...
> 
>  > You gain nothing, because you will still have to worry about SQL 
>  > Injection problems, that is, you will still have to properly quote 
>  > arguments.  Mostly this is easy, but if you use IN clauses in your 
>  > queries, it can be a bit tricky.  
> This is true, when he calls the External Method with the generated
> SQL. But, I expect that he will generate the SQL in the External
> Method. Then, the input is innocious: a selections of fields
> that should be output. They can easily be quoted or checked for
> validity.
> 

Well, not really.  dtml-sqlquote will not work for input like:

select first_name, last_name from foo where first_name in 
  ('john', 'pete', 'dieter')

(here assuming that string """('john', 'pete', 'dieter')""" is passed
as single parameter.  While the substrings can be sql_quoted individually 
during the string construction phase, they cannot be easily quoted in 
the ZSQL method or external method.)

>  > More importantly, you lose two things.  Fist, you will probably lose
>  > connection caching (or have to do it yourself).  For many databases,
>  > this is a biggy, as this can take tenths of seconds.
> No, we are still above this layer.
> 
>    We use the same interface used by Z SQL Methods.
>    We just bypass Z SQL Methods.

OK, you may well be right, that approach did not occur to me.  I have
used straight DB API 2.0 before, and I thought that was what you were
referring to.

> 
>  > Second, you lose testability (and thus, most probably, reliability).
> I agree, that Z SQL Methods allow for nice testing.
> 
> But, it is also not difficult to test the External Method.

Ahh, but in the realm that Tena seems to want to operate in, it is.
S/He has the idea, that appears to occur to every newbie (and I was no
exception), that it is a real problem to define all those dozens of 
ZSQL methods that are used in a folder, and that life would be much 
better if only he could define a single efficient, secure, understanble, 
and completely general way of creating a dynamic ZSQL method that 
generates the body on the fly and therefor frees all the clutter.

Alas, my experience is that it does not work that way.  You can replace
everything with a single insecure ZSQL method, or equivalently with a
single insecure External Method.  And, not only do you lose on security,
you also lose on auditability and/or debugging.  If you use straight
python DBAPI, you also probably lose on speed. 

And, as far as I can tell, it is in fact much harder to debug the
external method - the SQL is being dynamically generated, and if the
template is as flexible as what I think this fellow wants, essentially
completely free form.  So, the generated string used to call the SQL
method is in RAM somewhere, and unless your database supports query
logging, may be difficult to recover.  (Or you have to write your own
query logging facility.)

This is not to say that ZSQL methods are perfect.  They are not.  Nor
am I saying that ZSQL methods cannot be abused.  I have
seen at least one horror that relied on triply nested dtml-sqlgroup
clauses.  I thought it to be anathema.  Emailing the author, he had the
same idea this fellow appears to have, that he should develop one very 
complicated but very general method that did everything he could think 
of.  But he could no longer understand what his own method did!

In fact, my advice is that this is a dead end.  Develop using many 
simple ZSQL methods.  "It is a gift to be simple."  But, "be as 
simple as possible, but no simpler."

That is, I am not saying that it is not possible to do what is being
requested - after all, I was stupid enough to write a HOWTO on this - 
but I am saying that it is "unzopish", and probably ultimately, unwise.

Jim

> 
> 
> Dieter
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db
>