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

[Emanuele Zeppieri]

Mark Lawrence wrote:

>> So I misunderstood before, but that's probably even more limited than 
>> expected.
> 
> Care to explain why you think so?

Yes of course.

First of all sorry for my late.

Then you're perfectly right Mark, I've been way too vague, cryptic and 
confused (it was also because I tried to be brief, while it probably 
made you waste even more time. I apologize also for that).

So please let me explain in this message what I meant in my previous 
messages, step by step (through some examples), and let me also show my 
conclusions on SQL::DB (after a deeper examination).

These were my points (more or less in chronological order from message one).

1. No gain in expressiveness in the dynamic case
(Over SQL::Abstract)
------------------------------------------------

You show that the equivalent of the following SQL::Abstract paradigm 
(showed by Demi Laurent):

>   foreach my $field ($form->param) {
>     my @vals = $form->param($field) or next;
>     $search_criteria{$field} = @vals > 1 ? {-in => \@vals} : $vals[0];
>   }

in SQL::DB would be:

>     foreach my $field ($form->param) {
>         my @vals = $form->param($field) or next;
>         $expr    = $expr & ( @vals > 1 ? $table->$field->in(@vals)
>                                        : $table->$field == $vals[0] );
>     }

This is admittedly not that dramatic, but I don't see any significant 
gain in expressiveness over SQL::Abstract.

So the only situation I can see where an SQL::DB where clause offers 
more expressiveness over a Perl data structure, is when such clause is 
(fully) known at design time.
But in this case I would simply put my queries in some catalog (as they 
are known): that's why I think the /static/ case is not really relevant 
(as i said in my first message).

So let's examine some more /dynamic/ examples, where the SQL::Abstract 
advantage should be more evident.

2. More dynamic cases
---------------------

Let's suppose to have a form which permits the user to enter not only 
the field values, but also the relational (comparison) operators through 
which they must be compared (so for each field the comparison operator 
is not known until runtime).

The RT query builder is a perfect example of that:
https://rt.cpan.org/Search/Build.html
(login requested)

For clarity and simplicity, let's assume our program gets a data 
structure filled like this (the M$ Access form filter is another example 
of this; it would permit to put in the search fields exactly the strings 
used as values in the following hash - more on that later):

$form = {
     name   => '= Mark',
     age    => '<= 30' ,
     height => '> 180' ,
     weight => '< 80'
};

which is intended to produce the following SQL where clause:

(name = 'Mark') AND (age <= 30) AND (height > 180) AND (weight < 80)

To get this, through SQL::Abstract we can simply do:

my %abstr_where
     = map { $_ => { split /\s+/, $form->{$_} } } keys %{ $form };

How would you that with SQL::DB?
The easiest thing I can think of is an hash with all the possible 
relational operators as keys and as values the refs to the subs which 
execute the actual subexpressions.
It seems a significant additional work for the user.

Also, this is one of the situations where simply concatenating the hash 
values into your expression could help (just to explain why I did 
mention that - more on that later).

Now a slightly more complicated example.
Let's assume our form could be like this:

my $form = {
     name   => 'Mark',             # '=' is implicit!
     age    => '<30   > 60   =40',
     height => ' > 180 '           # spaces are optional anywhere.
};

which is intended to produce:

( (age < 30) OR (age > 60) OR (age = 40) ) AND (height > 180)
AND (name = 'Mark')

This is again along the syntax permitted by the above mentioned form 
filter function in M$ Access.

With SQL::Abstract it would be something like:

my $rel_op = qr/<=|>=|<|>|=/; # Let's limit to that.
my %abstr_where;
while ( my ($field, $cond) = each %{ $form } ) {
     my @atomic_conds = grep /\S/, split /($rel_op|\s+)/, $cond;
     if ( @atomic_conds == 1 ) {
         $atomic_conds[1] = $atomic_conds[0]; $atomic_conds[0] = '='
     }
     my @abstr_predicate;
     push @abstr_predicate, { shift @atomic_conds, shift @atomic_conds }
         while @atomic_conds;
     $abstr_where{$field} = ['-or', @abstr_predicate]
}

(Note that we've had to take care for the implicit '=' operator and for 
the optional spaces between the operator and the value and around the 
predicate, to reproduce the fuzziness of the Access form filter).

Well, this last example was really unnecessary, since the delta in 
difficulty between SQL::Abstract and SQL::DB should be about the same as 
the previous example, but it was to show you another example where 
simple string manipulations could help, and to introduce the next point.

3. An even more dynamic case
----------------------------

Really, the Access form filter permits to fill the field values with 
arbitrarily complex expressions, like this:

my $form = {
     age    => '( >30 OR name=Frank ) AND ( <60 OR height>180 )',
     weight => 80
};

Here the string manipulations would help a lot: you could just slightly 
pre-process the expressions and concatenate them into the full 
expression (if you didn't care of the fragility: more on that later).

Well, since this was the only situation I saw where SQL::DB could have a 
possible advantage over SQL::Abstract (which is useless here), I thought 
SQL::DB was aimed exactly at that, that's why I insisted on string 
manipulations.
At first I thought your module was just aimed to match such situations, 
where you get from the outside structured data together with ready made 
expressions (and that's why I was warning you against string 
manipulations ;-)

(It may seem a contrived example, but I'm fully involved with that, 
since I'm writing right now (for work) a module to reproduce the Access 
form filter (with even some more magic (which is called SQL::WateryPoo, 
for  a reason ;-)
As a side note, I had to write a full boolean expressions parser (with 
Parse::Yapp) since anything quicker resulted way too fragile. Just to 
remark that I'm not supporting simple string manipulations at all ;-)

4. Dynamic query manipulation.
------------------------------

Let's go back to a more common situation, where again IMHO SQL::Abstract 
has a significant advantage over SQL::DB (even greater than with the 
examples in points 1 and 2 before).

Let's assume you permit the user to perform some manipulations of the 
where clause he's creating, such as add a predicate at a time, remove 
any predicate, move predicates up/down, nest predicates (and un-nest 
them), change the topmost logical aggregator etc.

Again, this is exactly what the above mentioned RT query builder 
permits: look at the "Current search" block on the right (of course it's 
server side Perl, not javascript).

Well, with SQL::Abstract this would be trivial: it's just sufficient to 
manage an array.

Let's assume we start with the following array (kept server-side):

my @tmp_abstr_where = (
     'name'   => {'=' , 'Mark'}, # predicate 0
     'age'    => {'<=', 30    }, # predicate 1
     'height' => {'>=', 180   }, # ...
     'weight' => {'<' , 80    }
);

adding another predicate it's just a matter of a push()/unshift().

Removing a predicate (given the /predicate index/ in $i) it's just:

splice @tmp_abstr_where, $i*2, 2;

Swapping two consecutive predicates (given the index $i of the first 
one), to move them up/down:

my $i2 = $i * 2;
( @tmp_abstr_where[$i2, $i2+1], @tmp_abstr_where[$i2+2, $i2+3] )
     = ( @tmp_abstr_where[$i2+2, $i2+3], @tmp_abstr_where[$i2, $i2+1] );

Nesting two consecutive predicates (given the index $i of the first one):

my $op = '-or'; # For example.
splice @tmp_abstr_where, $i*2, 4,
     ( $op, [ @tmp_abstr_where[$i*2..$i*2+3] ] );

Un-nesting them back:

splice @tmp_abstr_where, $i*2, 2, @{ $tmp_abstr_where[$i*2+1] };

Then, when you've done, you simply do:

my $op = '-and'; # For example.
@abstr_where = ( $op => \@tmp_abstr_where );

($stmt, @bind) = SQL::Abstract->new->where(\@abstr_where);

And changing the topmost logical aggregator is just:

my $op = $new_op;
@abstr_where = ( $op => \@tmp_abstr_where );

Well, you got the idea (more sophisticated manipulations are just as easy).

Now I see no easy way to do anything like this with SQL::DB (except for 
adding a new predicate).

Granted, you could manage an array where you put the atomic predicates, 
to assemble them together at the end, but if you end up with a nested 
structure like this:

-and => [
     ...
     -or => [
         ...
         -and => [
             ...
             -or => [...]
             -or => [...]
         ]
     ]
]

you should parse/visit the structure to evaluate the subexpressions in 
the correct order (whereas SQL::Abstract does this for you).

This is exactly what I meant when I asked you:

> If you have both &'s and |'s in your (full) expression,
> how can you find the correct order to evaluate the various subexpressions?

It could seem unfair to use a data structure which matches a compatible 
SQL::Abstract data structure, but that's what you naturally end up if 
you want to keep an /abstract/ representation of your queries.

Using an abstract representation like that would also permit you to 
easily serialize/save them, retrieve them later and let the user 
manipulate them etc.

In a word, it seems that SQL::DB is not very good to offer on abstract 
representation of the queries, IMHO.

(As a side note, at work I've developed a query builder that permits the 
above mentioned operations by the user and much more. For that, even 
SQL::Abstract wasn't enough, so I wrote my own module 
(SQL::SimpleAbstraction, not ready for public consuption yet, but it 
*will be* open-sourced if someone is interested).
It's even less expressive than SQL::Abstract (it only uses dumb nested 
arrays and a lengthy hash for the atomic predicate), but it permits such 
  manipulations very easily (they are already in the API).

5. Conclusions
--------------

SQL::DB is cute, is very perlish and it is written in a very clean and 
structured way (both the docs and the code), and I've certainly greatly 
underestimated it (I apologize for that) and I largely missed its point.

Nonetheless, I don't see any significant advantage over SQL::Abstract, 
or rather, I see only disadvantages (albeit slight in the most common 
cases), as shown in points 1, 2 and 4 above.

Yet, it has a broader scope than SQL::Abstract and space to evolve.

Cheers,
Emanuele.