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

[Mark Lawrence]

On Fri Sep 07, 2007 at 09:09:28AM +0200, Emanuele Zeppieri wrote:
> Mark Lawrence wrote:
> 
> >I'm not sure why people keep thinking SQL::DB requires more string
> >manipulation than SQL::Abstract. Going back to one of the examples
> >I first posted:
> >
> >  $schema->query(
> >      select   => [$track->title, $cd->year],
> >      from     => [$track, $cd],
> >      distinct => 1,
> >      where    => ($track->length > 248) & ! ($cd->year < 1997),
> >      union    => $query2,
> >  );
> >
> >The arguments to the query() method is a LIST of keyword / value
> >pairs (also a data structure). The keywords are not used as strings
> >in the generated SQL, but are commands to SQL::DB about what type
> >of data structure follows.
> >
> >  my @query = (
> >    construct_a => $data,
> >    construct_b => $data,
> >    construct_c => $data,
> >  );
> >
> >It is in fact the same principle as SQL::Abstract but with a
> >different syntax. Nothing more, nothing less.
> 
> By the above example, it does not seem so. Not at all.
> 
> The most important thing is the WHERE clause, and it seems that it's 
> implemented by a Perl expression: so how do you suppose to manipulate 
> such an expression *by code* other than by string operations?
> 
> (Forget the rest, just show how do you build a complex where clause by 
> code without string concatenations and such.)

I'm not quite sure I understand what you are asking. Do we agree that at
the final output stage, the various values must be concatenated together
to produce an SQL statement? This is the basic internal workings of
whatever abstraction tool is being used.

If that is so, then you are asking how the following is evaluated?

    ($track->length > 248) & ! ($cd->year < 1997)

By the rules of precedence the items within the brackets are looked at
first (by Perl).

    ($track->length > 248)

$track->length is an object based on SQL::DB::Expr. Since we have
overloaded the ">" operator to the "gt" method, what Perl does is call
the following:

    SQL::DB::Expr::gt($track->length, 248);

The return value from this is another SQL::DB::Expr object that has a
string value "track.length > ?" and a bound parameter "248". The same
process happens for the other bracketed expression. So what we have is
this:

    $expr1 & ! $expr2

I think "!" has higher precedence, and "!" is overloaded to
SQL::DB::Expr::not so Perl calls:

   SQL::DB::Expr::not($expr2);

Which returns another SQL::DB::Expr object ($expr3) with a string value
of "NOT (cd.year < ?)" and a bound value of "1997". So what we now have
is:

    $expr1 & $expr3

"&" has been overloaded by an "and" method so Perl calls:

    SQL::DB::Expr::and($expr1, $expr3);

Which returns yet another Expr object with a string value of
"(track.length > ?) AND NOT (cd.year < ?)" and two bind values of
(248, 1997).

There is certainly some string manipulation to produce the string
values, but the evaluation of the expression is all Perl logic. The
finer details are inside SQL::DB::Expr.

My apologies if I haven't answered your question.

Cheers,
Mark.
-- 
Mark Lawrence