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

[Matija Grabnar]

Mark Lawrence wrote:
> Did you mean "Nor was it the easiest"?
No, I meant "Implementing the compile part for the NOT operator was the
hardest, because DBix::Class does not directly support not. Instead,
I had to implement De Morgan's law on the previously "compiled" hash.

>  And how readable is your code?
>   
That, naturally, is a matter of opinion. I don't want to post the whole 
thing here, since I'm writing a paper about it, but here is part of the 
grammar
that deals with the comparisons. (This is using Parse::RecDescent).

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: 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.
> 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?

And yes, I do consider the SQL::Abstract form quite readable, 
particularly when
it is written out (as you did) with one column name per line. One can 
see much more quickly what is being searched.

Another incidental feature I found about SQL::Abstract is that is easy 
to "tack on" a few more conditions to a search. In any number of my 
programs I have a part where a detailed query is prepared by some 
complicated means, and then the more general condition is simply tacked 
on to the hash just before the search.

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.
> Tell me which you would find easier to write, understand, and maintain? 
>   
I've tried pure SQL and DBIx::Class in projects large and small. So far, 
I found DBIx::Class superior.
> 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?)
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.

Of course, you could also do

    %where = (
        lname => {like => '%son%'},
        age   => {-between => [10, 20]},
    );


Which would make the whole thing even more readable.

Matija