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

[Charlie Clark]

On 2004-06-29 at 12:52:53 [+0200], "Smith, Neil (Worcester)" 
<Neil.Smith at npower.com> wrote:
> Say you've got a database table like:
> 
>     create table products (
>         product   varchar2(60),
>         colour    varchar2(20),
>         size      varchar2(10),
>         available number(5)
>     )
> 
> and you want to have a method to search it allowing people to specify any or
> none of the columns.
> 
> At the moment with the sqlgroup/sqltest/and stuff, you'd have a SQL method:
> 
>     <params>
>     product
>     colour
>     size
>     available
>     </params>
>     select * from products
>     <dtml-sqlgroup where>
>     <dtml-sqltest product type=nb optional>
>     <dtml-and>
>     <dtml-sqltest colour type=nb optional>
>     <dtml-and>
>     <dtml-sqltest size type=nb optional>
>     <dtml-and>
>     <dtml-sqltest available op=gt type=int optional>
>     </dtml-sqlgroup>
> 
> Now if you left all the params null to get a complete list of products it
> would render
> 
>     select * from products
> 
> If you wanted a large thingamyjig, you'd just specify those parameters and
> it would render
> 
>     select * from products
>     where
>     product = 'thingamyjig'
>     and
>     size = 'large'
> 
> Looking for products where there are more than a 1000 in stock, just put
> 1000 in stock and null in the others would give:
> 
>     select * from products
>     where
>     available > 1000
> 
> etc..
> 
> Putting dtml-if's in to do the equivalent isn't trivial.  Try it and see,
> then think about doing it with a table where you wanted to allow searching
> on many more columns.


    select * from products
	WHERE 1 = 1
	<dtml-if product>
	AND product =  <dtml-sqlvar product type=nb optional>
	</dtml-if>
	<dtml-if colour>
    AND	colour = <dtml-sqlvar colour type=nb optional>
    </dtml-if>
	<dtml-if size>
	AND size = <dtml-sqlvar size type=nb optional>
	</dtml-if>
    <dtml-if>
	AND	available >  <dtml-sqlvar available type=int optional>
    </dtml-if>
 
This isn't much longer has the advantage of being virtually pure SQL so 
imminently more readable.

> My other example was just trying to put the logic into the SQL rather than
> having any dtml-... statements.  Ignoring the number column, it would need
> to be:
> 
>     <params>
>     product
>     colour
>     size
>     available
>     </params>
>     select * from products
>     where (
>         <dtml-sqlvar product type=string> is null or
>         product = <dtml-sqlvar product type=string>
>     ) and (
>         <dtml-sqlvar colour type=string> is null or
>         colour = <dtml-sqlvar colour type=string>
>     ) and (
>         <dtml-sqlvar size type=string> is null or
>         size = <dtml-sqlvar size type=string>
>     )
... 
> which works, but isn't nice.

Indeed. Don't go that way.


> however it's not worth thinking about too much as I'd only wanted to show
> that trying to do this in SQL got nasty and I think that's fairly apparent
> already.
> 
> Of course all this is wrong anyway as we should have bound parameters rather
> than generating new SQL statements each time and polluting the SQL cache.
> If that was changed, then all this would be a bit different too.

Exactly. This actually where we want to start out now that the proof of 
concept has been done. So let's thing about how different this would be.

Charlie