[Dbix-class] abstract syntax (extract from a conversation)

[Darren Duncan]

At 7:17 AM -0500 7/21/07, Brandon Black wrote:
<snip>
>It might be important
>to allow for values to specified as (possibly named, as opposed to the
>ordering-sensitive ?'s common in SQL) placeholders in both the DWIM
>and explicit AST, allowing the DWIM -> explicit -> SQL conversion to
>be cached and re-used with different variables, like (and probably in
>conjunction with) prepare_cached, but at a higher level.
>
>Then it's just a matter of implementation details like having RS's
>cache their converted explicit AST and SQL representations when
>executed via some new syntax,
<snip>

I agree, and in fact consider it essential to have separate prepare 
plus execute stages, with placeholders.  Not that we can't have a 
wrapper function to combine both stages as per DBI's "do", but use of 
that should be strictly a short-hand.

Conceptually speaking, the query specification from the user is a 
definition of a routine that optionally takes arguments and that 
optionally returns values.  Preparing the query is akin to compiling 
code in a normal programming language, which often can just be done 
once in advance and the compilation cached, for possibly multiple 
invocations later.  Any input data that may change for invocations of 
the query is represented as a routine parameter.

I also strongly believe that parameters/placeholders should be named, 
and only named, in our AST.  If a back-end requires positional, then 
the AST compiler over that back-end can handle the mapping and 
remember it for execute time, and users won't ever have to care. 
Named is easier to use, easier to use the same argument several 
places in a query.

Named also strongly supports speed, because the query compiler can 
rearrange the query for better speed without breaking any alignment 
of placeholders.

Getting back to that thing about plethora of objects or whatever, I 
should also emphasize that when we can cache compiled queries, it 
doesn't matter as much whether the compilation takes longer, since 
we're just doing it once, if we get other benefits of flexibility or 
correctness from doing so.

Generally speaking, the speed of the execute stage is the most important.

On a tangent, if the caching in question is multi-stage, with 
implementations taking care of some stages, then from the user's 
point of view, a query can even be compiled once at program startup, 
as if it were part of the Perl program, or otherwise appropriately 
earlier, even before any database connections are established; in a 
mod_perl environment, eg, at Apache server startup prior to process 
fork.

Suffice it to say that things become a lot easier for us if we don't 
expect our AST compiler to return a SQL string (ala SQL::Abstract), 
but rather return an object representing the compiled AST, which 
could have a SQL string behind the scenes, or alternately several of 
them plus Perl code to make them cooperate like one.  Letting some 
preconceived limitations go is a step to advancement.

-- Darren Duncan