[Dbix-class] Re : Explicit ASTs (ping nate)

[Dami Laurent (PJ)]

Hi all,

I followed with great interest this discussion thread.

>    DWIM AST -> explicit AST -> SQL

Sounds like a very complex task, since SQL is so big, and has so many
extensions. The risk then is that the "DWIM AST" will evolve to also
become more complex (and difficult for users to understand), and the
inner complexity of the ORM layer will augment to become more like a
DBMS in itself -- in both cases, the goal of ORM as a middleware
between Perl and DBMS may be lost.

An explicit AST is interesting if we want to perform operations on the
tree ... but if the only operation is to generate SQL, I don't really
see the point : chunks of strings should be good enough, passed
directly from the DWIM API to the SQL layer. For example,
we can already pass a string "COUNT(DISTINCT foo) AS n_foo" to
SQL::Abstract, and get back rows with a $row->{n_foo} value : what
more could be done if we had a more structured syntax tree ?

The DWIM API should be very close to SQL anyway, because that's what
programmers learned and understand. Generating portable SQL is a choice
of the programmer : sometimes you really want to use proprietary SQL
extensions of your DBMS, and then you don't want the ORM to be in the
way. So I think SQL::Abstract does a very good and clever job of
balancing
between flexibility, ease of use, and added power, and for helping
to separate between prepared statements and bound values.

Nevertheless, there are some points in SQL::Abstract that need
improvement :

1) if you don't want a placeholder in some operator, you can cheat
with a ref to an empty string, but that's ugly :

   my %where = ( 
     column1             => {"<" => $some_value}, # usual placeholder
     "column2 < column3" => \"",               # embedded string
     "column4 < 'value'" => \"",               # idem
     "condition_is_true" => \"",               # idem
     );

2) arguments to the top-level call are positional :

   ->select($table, \@fields, \%where, \@order)

which is short and compact, but since arguments are also optional, you
easily get confused, or have to fill holes with undefs; furthermore,
this API is difficult to extend (see for example SQL::Abstract::Limit).
This is why, in the DBIx::DataModel ORM, I had to insert a wrapper
with named arguments interface

   ->select(-columns => .. ,
            -where   => .. ,
            -orderBy => .. ,
            -groupBy => ..    
            ...)

which then calls SQL::Abstract with appropriate positioned arguments. 
This made it (and will make it) easier to support more complex SQL 
statements, with clauses for "into", "group by", "having", "for update",
"lock", "limit", etc.


3) support for special operators like 'IN' or 'BETWEEN' that need
parentheses is hardwired in SQL::Abstract, so it doesn't work for other
operators. Intuitively, we would like to write

   my %where = ( 
     "match(title, body)" => {-against => "database"},
   );

but that produces
 
   match(title, body) AGAINST ?

instead of the correct

   match(title, body) AGAINST (?)


4) no support for nested subqueries


5) no support for additional SELECT clauses as mentioned in 2) above.

So in conclusion I think it may be more rewarding to work on improving
the DWIM API than to introduce an intermediate AST. Now we have to 
see which proposals could be good enough to be acceptee by Nate Wiger
for inclusion into SQL::Abstract.

Best regards, L. Dami