[Zope-DB] Relational Databases in Zope 2 and Zope 3

[Matthew T. Kromer]

Tres Seaver wrote:

> Stephan Richter wrote:
>
>> On Monday 28 June 2004 07:50, Charlie Clark wrote:
>>
>>> 1) ZSQL - use property sheets and positional parameters. Personally I'd
>>> like to drop DTML support in ZSQL altogether. Jim Fulton made the
>>> suggestion that <dtml-sqlvar> parsing could be modified to return 
>>> '?' and a
>>> tuple element to preserve backwards compatability but we think this 
>>> would
>>> no longer be necessary. It might, however, be worthwhile to have 
>>> something
>>> like ZPT for ZSQL to encourage reuse and embedding in applications.
>>
>>
>>
>> I disagree that ZPT would be a better choice, since it requires 
>> well-formed XML and is slower. I think DTML tags are still the way to 
>> go, but we should try to drop the "dtml-" prefix.
>>
>> Also, we can switch to DTML 2, once it is done.
>
>
> I think you are missing Charlie's point, which is that letting the 
> Zope application do variable interpoloation into the SQL string is a 
> huge performance hit, given a backend which knows about "prepared" SQL 
> statements.  The work he mentioned earlier was precisely on such 
> statements (from my reading, anyway).  He wrote "something like ZPT", 
> which I took to mean "a more constrained templating language buillt 
> around SQL".
>
> Tres.


Tres is absolutely right -- you do NOT want to have the application 
layer preparing SQL statements to text; instead you want to lean on a 
relatively rich driver API that understands what the drivers can 
parameterize.  Unfortunately for the Python DBAPI, this means that the 
client (Zope) side needs to be rather flexible in how it constructs 
parameterized statements, since various DB layers provide different 
mechanisms (question mark vs numbered vs colon etc).  Trying to out-fox 
the DB adapter usually just means creating grief  -- you start wending 
your way down baking a full fledged SQL parser into Zope and that's pure 
madness when it comes to not supporting what vendors do to their various 
SQL dialects.

Ultimately what you want is something that bursts the SQL string into 
the constant bits, and the parameterized bits, with enough markup on the 
parameterized bits that the driver adapter can set it up for handoff to 
the DB API without coercing everything into a string or some other 
sub-par accomodation.