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

[Smith, Neil (Worcester)]

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.

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>
    )

So for the examples as above you'd get:

all products:

    select * from products
    where (
        '' is null or product = ''
    ) and (
        '' is null or colour = ''
    ) and (
        '' is null or size = ''
    )

large thingamyjigs:

    select * from products
    where (
        'thingamyjig' is null or product = 'thingamyjig'
    ) and (
        '' is null or colour = ''
    ) and (
        'large' is null or size = 'large'
    )

which works, but isn't nice.  

I guess for the numeric columns you'd have to do something like

    ) and (
        '<dtml-sqlvar available type=int>' is null or
        available > to_number('<dtml-sqlvar available type=int>')
    )

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.

-- 
Neil


-----Original Message-----
From: Charlie Clark [mailto:charlie at egenix.com]
Sent: 29 June 2004 10:54
To: zope-db at zope.org; Neil.Smith at npower.com
Subject: RE: [Zope3-dev] Re: [Zope-DB] Relational Databases in Zope 2
and Zope 3



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.
 
> If you remove it, then you'll need either an exponential number of SQL
> methods and some logic before it to decide which to call, or you'll have
to
> fill the SQL method with even more confusing dtml-if statements, or you'll
> have to have your SQL something like:
> 
> WHERE (
>    <dtml-sqlvar parameter_1 type=..> is null or
>    column_1 = <dtml-sqlvar parameter_1 type=..>
> ) and (
>    <dtml-sqlvar parameter_2 type=..> is null or
>    column_2 = <dtml-sqlvar parameter_2 type=..>
> )

Interesting example. This says to me that the model is bad: NULLs are 
indicative of this. It's also intriguing that to see the relation variables 
passed in as relation values. I think this will probably cause an error on 
rendering. Okay, so nit-picking aside: you are right to say that <dtml-if>s 
can cause problems on larger methods. I find that readibility is still there

but I get problems when I might only want to return a certain value based on

something I'm passing in. Working on my larger ZSQL methods has reminded me 
of working on HTML pages which is why I find ZPT attractive.

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?

Charlie





This e-mail is provided for general information purposes only and does not constitute investment or transactional advice. For  the avoidance of doubt the contents of this email are subject to contract and will not constitute a legally binding contract.

The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it.
    
If you have received this e-mail in error, please notify postmaster at npower.com (UK 01384 275454) and delete it immediately from your system.
    
Neither Npower nor any of the other companies in the RWE Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.
Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277.  This e-mail may be sent on behalf of a member of the RWE Innogy group of companies.
**********************************************************************