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

[Mark Lawrence]

On Mon Sep 10, 2007 at 05:20:34PM +0200, Emanuele Zeppieri wrote:

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

No need to apologise Emanuele. If we are not communicating then it
means a) I've still got some work to with regards my explanations,
b) the code needs improvement and the feedback is valuable, and c) the
resulting discussions are anyway most likely useful to lurkers who
aren't sure if this thing is worth them spending any time on.

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

For the simple case above I see no gain or loss.

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

I think the static case is relevant to the discussion (when comparing
SQLDB with SQLA) for reasons other than just the WHERE clause or WHERE
clauses generated from web forms. JOINS & nested SELECTs are obvious
examples.

UPDATE is another: What if I want the database to add the value of 1 to
every row of a certain column? With SQLA all I have to play with is:

    $SQLA->update(’people’, \%data);

As far as I know, there is no way to put the summation into \%data.
Alternatively:

    $SQLDB->query(
        update => [$person->age],
        set    => [$person->age->set($persion->age + 1)],
    );

I admit the syntax in this case is not the most elegant, but at least
the database is doing the work instead of forcing me to SELECT the value
first so Perl can add 1 to the value before UPDATE. That gets really
inefficient, really quickly.

(As an aside I would love some feedback from anyone on whether this
 syntax looks better. No duplication of "set":

    $SQLDB->query(
        update => [ $person->age->set( $person->age + 1 ) ],
    );

or maybe:

    $SQLDB->query(
        update => [ $person->set_age( $person->age + 1 ) ],
    );
)


> 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

That is one heck of a form.

> 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 };

I hope the above is not real (complete) code. Is there not the
possibility for SQL injection with funny form keys here? What if
instead of this:

    age               => '<= 30'

I were to submit this?

    "id > '0' OR age" => '<= 30'

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

[ SQLA implementation removed ]

> 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
> };


[ following paragraph moved from above ]

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

Just so I'm clear, you are talking about converting/parsing externally
written strings into something Perl can hit an SQL database with? If
that is the case then I'll happy hand this round to you. That is
certainly not one of the design goals I had in mind.

To me, the primary goal of a Perl to SQL interface is about making
the power of the SQL database accessable to the Perl programmer.  
This was the motivation for writing SQL::DB in the first place
since I have not (yet) needed or looked into the requirements for
converting and storing externally generated string queries...

If you have requirements more complicated than inserting values into
known or almost-known queries, or if you have to convert from some
arbitrary external syntax like "age='<30 >60 =40'" or "<almost SQL>"
into SQL, then, as you've already mentioned, one really should be
using a parser. And if you have a proper parser then you can build an
AST and can output whatever structure you like.

While SQL::Abstract may make it easy to produce SQL from a data
structure (which for this use is an obvious strength) I would claim
that for its use as a *Perl* to SQL interface it is not as
strong as SQL::DB. By the same logic, I would also claim (as you do)
that SQL::DB is not suited to the task of structure => SQL.

And to be honest I think that the scope of most applications *is*
mostly about known or internally-derived queries. There aren't too
many LAMP-style (or other style) apps I'm aware of (RT aside) that have
to somehow parse query logic. Search any ORM list for questions on
how to build custom queries and compare that with questions on parsing
external strings to see where what the common requirements are.

For my situation it comes down to the fact that I want to
stop using my databases as relatively simple persistence stores, and
start making full use of them as query engines. SQL::DB helps me with
that.

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

Exactly!

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

Point 1 proved no gain or loss to me, and in point 2 you say:

> 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 

Onwards... (you've been very thorough :-)

> 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.
> 
> Well, with SQL::Abstract this would be trivial: it's just sufficient to 
> manage an array.

[ parser implementation removed ]

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

I think the difference is that SQL::DB doesn't let you build the AST in
the first place (or at least it doesn't make sense to do so). Queries
are built *during* any parsing. But exactly how that parsing looks
like would have to be based on what sort of input structure you give it.
Sorry if this is not quite a solid reply, but I don't quite have the
effort available right now to code up what I think it would look like.

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

Hmm... I think this is a good point, although once again I've never had
to store, retrieve and manipulate a query, but I can see how that would
be useful. I think the SQL::DB answer would be to store the inputs, not
the abstract representation. The query only exists while it is being
built.

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

I agree!

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

Thanks for the time and effort you've put into you mails. Has really
helped me to clarify what SQLDB does and doesn't do.

Cheers,
Mark.
-- 
Mark Lawrence