[Dbix-class] [OT][ANNOUNCE] SQL Generation with SQL::DB

[Mark Lawrence]

On Thu Sep 06, 2007 at 04:46:55PM +0200, Matija Grabnar wrote:

> cond: ident '>=' val { $return = { $item[1] => { '>=',$item[3] } } }
> cond: ident '<' val { $return = { $item[1] => { '<', $item[3] } } }
> cond: ident '>' val { $return = { $item[1] => { '>', $item[3] } } }
> cond: '(' disjunction ')' { $return = $item[2]; }
> cond: 'not' cond { $return = negate($item[2]) }
> 
> I find that quite readable. And considering that I had to re-awaken 
> 15-years old memories about syntax of grammars, *and* learn how 
> Parse::RecDescent works, I think the work was done amazingly quickly.

I agree, that is quite readable :-) I was somehow thinking you had to do
it another way.

> >It is obvious to you at first glance what the query is? I'm curious,
> >could you please compare the two versions of the following SQL:
> >
> >    WHERE lname LIKE '%son%' AND NOT ( age < 10 OR age > 20 )
> >
> >with SQL::DB:
> >
> >    where => $r->lname->like('%son%') & ! ($r->age < 10 | $r->age > 20 )
> >
> >with SQL::Abstract:
> >
> >    %where = (
> >        lname => {like => '%son%'},
> >        age   => [-and => {'>=', 10}, {'<=', 20}],
> >    );
> >  
> Well, quite frankly, I don't see why I should write SQL::DB form instead 
> of pure SQL. I mean, what does SQL::DB give you that pure SQL doesn't?

The reasons for using SQL::DB instead of pure SQL are exactly the same as
the reasons for using SQL::Abstract instead of pure SQL. Prevent a lot of
ugly string manipulation and the use of bound parameters to avoid SQL
injection attacks. They just provide different syntax / programming
models for doing so.

> For instance, I have one program where I construct a search hash based 
> on input from CGI parameters, then, just before the search, I add two 
> more global conditions: a date constraint, and a constraint which 
> reflects the user's authorization to see specific data.
> 
> Adding that to SQL::Abstract has is simple. Adding it to a SQL string 
> would not be, nor do I think it would be easy to add to SQL::DB search 
> pattern.

This example of such additions is perfectly valid in SQL::DB:

    my $expr1 = $age > 10;
    my $expr2 = $age < 20;
    my $expr3 = ($exp1 & $expr2) & ($age != 13);
    ...
    where => $expr3 | ($age == 99)

> >For an even closer approximation it is possible to do this:
> >
> >    my $age   = $r->age;
> >    my $lname = $r->lname;
> >    where => $lname->like('%son%') & ! ($age < 10 | $age > 20 )
> >  
> Which is why I mentioned that implementing NOT was a bit tricky -
> you get:
> 
>    %where = (
>        lname => {like => '%son%'},
>        age   => [-and => {'>=', 10}, {'<=', 20}],
>    );
> 
> by using De Morgan's law. (We studied De Morgan's law in high school. 
> Didn't you?)

I was referring to the syntax being an even closer approximation to the
SQL, which in my mind has some benefit. I don't quite understand how
your statement relates to that.

> So I had to make a De Morgan's law engine inside my parser. Took me all of 
> two hours, and it works just peachy.

That is my point. Relying on Perl's logic operators means that you
don't have to do the De Morgan translations. The "!" equates directly
as "NOT", and in the above case automatically applies to the combined
condition inside the brackets. A direct mapping.

Regards,
Mark.
-- 
Mark Lawrence