[Zope-DB] Z sql Method and using 'OR'

Jim Penny jpenny at universal-fasteners.com
Wed May 5 18:04:58 EDT 2004


On Wed, 5 May 2004 16:20:15 -0500
"Laura McCord" <Laura.McCord at doucet-austin.com> wrote:

Just be very, very, very careful.  You are getting into places that have
very sharp knives.  It is easy to get cut, and to bleed to death before
you can figure out just where you got cut.

Worse, I suspect that you are doing too little validation, and too late.
I really need to complete a tutorial.

But, look at the two proposed bodies and think about what you are going
to grok six months from now...

COMPARE
...
<dtml-sqlgroup where> users.userid = users.userid
<dtml-and>
<dtml-if username>
 <dtml-sqltest username column="users.username" type="string"
  optional="Yes">
</dtml-if> 
 <dtml-and> 
 <dtml-if useroffice> 
  <dtml-sqltest useroffice column="users.useroffice" type="string"
  optional="Yes"> 
 </dtml-if>
</dtml-sqlgroup>

WITH 
...
where
<dtml-if username>
  username = <dtml-sqlvar name="username" type="string"> 
</dtml-if>
<dtml-if "username and useroffice"> and </dtml-if>
<dtml-if useroffice> 
  useroffice = <dtml-sqlvar name="useroffice" type="string">
</dtml-if>

Now, this second form does have problems, especially if you have many
possible alternatives, where any subset may be filled in.

But, if you can arrange things so that you know that one item, say
username, will ALWAYS be filled in, you can write it like:

AND ALSO
...
where username=<dtml-sqlvar name="username" type="string"> 
<dtml-if useroffice>
  and useroffice = <dtml-sqlvar name="useroffice" type="string">
</dtml-if>

Note that the conjunction has moved inside the dtml-if guard.
I claim this is MUCH easier to read.  

In general, it is easy to introduce a dummy clause in SQL which
permits you to have a static first clause, with very little loss of
efficiency and a large gain in readability.

Jim Penny



More information about the Zope-DB mailing list