[Dbix-class] Explicit ASTs (ping nate)

[David E. Wheeler]

On Sep 3, 2006, at 06:08, Matt S Trout wrote:

On Sep 3, 2006, at 06:09, Matt S Trout wrote:
> Err, no, that's going the wrong way -
>
>  [ -name, 'my', 'a' ] => my.a
>  [ -alias, [ -name, 'my', 'a' ], 'a' ] => my.a AS a
>
> I think.

Oh, I gotcha. Not sure about the ordering of that second one, though;  
this makes a bit more sense to me:

   [ [ -name, 'my', 'a' ], -alias => 'a' ] => my.a AS a

Although it seems arbitrary to have the sub-array only if there's an  
alias. What about:

   [ -name => [ 'a' ] ]                   => a
   [ -name => [ 'my', 'a' ] ]             => my.a
   [ -name => [ 'my', 'a' ], -as => 'a' ] => my.a AS a

That way, you always have the same data structure. (And I threw in  
the idea of -as instead of -alias, though that makes little  
difference to me.)

> > If we're going to be precise (and pedantic). No? And since there  
> > multiple keys per token, it should actually probably be:
> >
> >    [ { -name => 'my', -alias => 'a' },
> >      { -name => 'my', -alias => 'b' },
> >    ]
>
> No, not at all. The structure logically nests the way shown above -  
> that should enable things like
>
>  [ -alias,
>    [ -func, 'COUNT',
>      [ -func, 'DISTINCT',
>        [ -name, 'my', 'a' ]
>      ]
>    ],
>    'a_cnt'
>  ]

Oh, I see. That would be  'COUNT( DISTINCT( my.a ) ) AS a_cnt'. Yes,  
that works, although I still like the -alias (or -as) adjacent to its  
value ('a_cnt').

> for "COUNT(DISTINCT my.a) AS a_cnt"
> >>      from => [ [ -alias, 'my_table', 'my' ] ],
> >
> > That would have to be:
> >
> >        from => [ { -name => 'my_table', -alias => 'my' } ]
>
> More
>
>  [ [ -alias, [ -name, 'my_table' ], 'my' ] ]


Why the embedded array ref? I'm sure I'm missing something here, but  
this would work for me, too:

   [ -name => 'my_table', -alias => 'my' ]

But perhaps there's a more complex example that would make it clear  
what I'm missing?

> >       where => [ [ -op, '=', { -name => 'spork', -bind => 1 } ] ],
> >
> > No?
>
> No, definitely not, that restricts us to <column reference> =  
> <value> which is one of our big bugbears with SQL::Abstract on its  
> own - for example keeping it as a pair of arrayrefs allows
>
>  [ [ -op, '=', [ -name, 'spork' ], [ -name, 'spoon' ] ] ]
>
> to produce "spork = spoon" in the final SQL - or having a function  
> on the LHS or whatever.

Oh, I see, you want to allow <column reference> = <column reference>.  
That's reasonable.


> I agree in places, but your hashref style results in data loss as  
> expanded above so I'm pretty much certain that's wrong.

If you see data loss in my suggestion, then I'm sure you're right. I  
don't want data loss, either.

> We'll have to think carefully about how -bind works; Scalar::Defer  
> might be sufficient to the task but I think explicit support for  
> subrefs and for named bind parameters somehow will also be worthwhile.

Absolutely.

> > Well, I think that COUNT() is only in the select or group by  
> clauses, > never in where. But I'd also like to explicitly include  
> support for > MATCH(), so that one can use regular expressions in  
> WHERE clauses, > just as one can use LIKE.
>
> True re count, but the point here is more one of enabling easy  
> writing of syntax generators, and COUNT and its ilk are a  
> relatively standard syntax whereas MATCH isn't. The func_generic/ 
> func_specific stuff would be an implementation detail from an  
> external POV.

Reminds me of what Darren said about namespaces, actually. We do need  
to be able to handle both standard functions and aggregates (length 
(), uc(), lc(), count(), sum(), etc.) and also custom functions or  
platform-specific functions.

> As for supporting MATCH explicitly we'd have to do a run-through of  
> what other DBs provide by way of regex syntax, but I guess you can  
> already tell us that?

PostgreSQL and MySQL both support regular expressions, and SQLite  
does with the addition of a simple regexp() function. See:

   http://www.justatheory.com/computers/databases/sqlite/ 
add_regexen.html
   https://rt.cpan.org/Ticket/Display.html?id=18639

I don't know about other databases, but if a particular back end  
didn't support a feature, I'd just expect it to throw an exception.  
Think what happens with the LIKE operator if you have an LDAP back end.

And yes, this means that I'm thinking about the AST and the query  
syntax as applying to all kinds of things, not just RDBMSs…

Best,

David