[Zope3-dev] Re: [Zope-DB] Relational Databases in Zope 2 and Zope 3

[Scot McSweeney-Roberts]

Charlie Clark wrote:

>On 2004-06-29 at 10:22:14 [+0200], "Smith, Neil (Worcester)" 
><Neil.Smith at npower.com> wrote:
>  
>
>>The point of the dtml-sqlgroup and dtml-sqltest is surely to provide a nice
>>way of handling the case where you don't pass in the parameter, so it
>>doesn't render the WHERE, AND, = etc.  And it does this quite well.
>>    
>>
>
>Maybe it does. I've never found cause to use it and scratched my head a lot 
>trying to look at it. I'd hate to maintain such stuff something which Ian 
>Bicking seems to complain about.
> 
>  
>

I've found cause to use this in almost every other ZSQL method I write. 
For me, it's one of ZSQL Methods most usefull features.

>I think what might be the biggest problem is the lack of good examples on 
>this. The stuff in Zope book does not seem to me to be written with real 
>world experience so maybe you've got a concrete example with the resultant 
>SQL for us to discuss?
>
>  
>


I can give an example. I have a report where users can use various items 
on a form to narrow down the rows returned. Any criteria left blank 
don't affect the amount of rows returned. Here are the arguments:

start_date end_date programme_title programme_ar seg event_description 
event_ar c_number event_type duration tc_in tc_out slide_no cf_no 
clock_no notes

and here's the ZSQL method

SET DATEFORMAT dmy
SELECT
  e.event_id,
  e.tx_date,
  e.programme_title,
  e.programme_ar,
  ar1.short_name 'prog_aspect',
  e.seg,
  e.event_description,
  e.event_ar,
  ar2.short_name 'event_aspect',
  e.c_number,
  e.event_type,
  et.note 'type',
  e.duration,
  e.tc_in,
  e.tc_out,
  e.slide_no,
  e.cf_no,
  e.clock_no,
  e.notes
FROM
 events e
LEFT JOIN aspect_ratios ar1 ON
  e.programme_ar = ar1.ar_id
LEFT JOIN aspect_ratios ar2 ON
  e.event_ar = ar2.ar_id
LEFT JOIN event_types et ON
  e.event_type = et.event_type_id
<dtml-sqlgroup where>
 <dtml-sqltest start_date op="ge" type="nb" column="tx_date" optional>
 <dtml-and>
 <dtml-sqltest end_date op="le" type="nb" column="tx_date" optional>
 <dtml-and>
 <dtml-sqltest programme_title op="like" type="nb" optional>
 <dtml-and>
 <dtml-sqltest seg op="like" type="nb" optional>
 <dtml-and>
 <dtml-sqltest event_description op="like" type="nb" optional>
 <dtml-and>
 <dtml-sqltest event_ar op="eq" type="int" optional>
 <dtml-and>
 <dtml-sqltest programme_ar op="eq" type="int" optional>
 <dtml-and>
 <dtml-sqltest c_number op="like" type="nb" optional>
 <dtml-and>
 <dtml-sqltest event_type op="eq" type="int" optional>
 <dtml-and>
 <dtml-sqltest duration op="like" type="nb" optional>
 <dtml-and>
 <dtml-sqltest tc_in op="like" type="nb" optional>
 <dtml-and>
 <dtml-sqltest tc_out op="like" type="nb" optional>
 <dtml-and>
 <dtml-sqltest slide_no op="like" type="nb" optional>
 <dtml-and>
 <dtml-sqltest clock_no op="like" type="nb" optional>
</dtml-sqlgroup>



And that's a simple example. I've had to do similar things before 
outside of Zope and it wasn't pretty, in Zope it was very straightforward.


cheers

Scot



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.zope.org/pipermail/zope-db/attachments/20040629/452ea413/attachment.htm