[Dbix-class] Explicit ASTs (ping nate)

[Nate Wiger]

Sam Vilain wrote:
> Matt S Trout wrote:
>>    foo = 'yay' AND bar LIKE 'wooho%'
>>
>> becoming
>>
>>    [ -op, 'AND',
>>      [ -op, '=', [ -name, 'foo' ], [ -bind, 'yay' ] ],
>>      [ -op, 'LIKE', [ -name, 'bar' ], [ -bind, 'wooho%' ] ],
>>    ],
> 
> Would it hurt *that* much to bless these fragments, to make it easier in
> places?

I'm curious about that myself. If we want to really be able to supply 
callbacks, relations, etc, it seems that we need something like this 
(and this is completely off the top of my head after a long weekend on 
vacation):

    foo = 'yay' AND bar LIKE 'wooho%'

becoming:

    $s->and(
       $s->eq(   $s->bind( $s->name('foo'), 'yay') ),
       $s->like( $s->bind( $s->name('bar'), 'wooho%') ),
    ),

and a function call like

    COUNT( * )

would become

    $s->count('*')

 From there, a person could subclass SQL::Abstract and provide their own 
custom site "count()" function that did something special. Or an XPath 
version/etc. Or a $s->name() that does the weird quoting MySQL wants.

An intermediate parsing tree is a good idea, but then again I could see 
as much benefit from being able to just insert into a node using Perl 
methods, kinda like how JS handles the XML DOM (although less crappy). 
Otherwise we pretty much have to rewrite SQL to support all the myriad 
variations.

Here's one expanded example I could think of:

    SELECT d.id, d.breed FROM dog_breeds d
     WHERE d.size > 100 AND d.color IN ('brown','black','grey')
     LIMIT 100;

That might be something like:

    $s->select(
      $s->table('dog_breeds', 'd'),  # dog_breeds d
      $s->list(                      # for commas
        $s->name('id', 'd'),         # d.id, d.breed
        $s->name('breed', 'd'),      # now we know d = d
      ),
      $s->where(                     # will give "WHERE"
        $s->and(
          $s->where(\%data_struct),
          $s->in($s->name('color', 'd'), @members),
        ),
      ),
    );

Ooops, forgot the limit:

    if (my $l = $cgi->param('limit')) {
       $s->append($s->limit($l));
    }

And we really wanted an extra column back:

    my $n = $s->find('from');
    $n->append($s->name('color', 'd'));

And why don't we get all the d members to set types:

    my @m = $s->members('d');
    for (@m) {
       if ($_->name eq 'id') {
           $_->type('integer');
       }
    }

Finally, render the SQL:

    my($stmt, @bind) = $s->render;

Just a thought. Great thread BTW.

-Nate