[Dbix-class] Re: Refactoring Tangram::Expr and Tangram::Relational::Engine - looking for a SQL AST module

[Darren Duncan]

This is reply is to multiple messages, including the posting each by 
Sam Vilain and Matt S Trout, as well as a private email from the 
latter (plus a tidbit for Todd Hepler re subject "design thought").

At 4:23 PM +1200 7/29/05, Sam Vilain wrote:
>Matt S. Trout and I discussed requirements for an extension to SQL::Abstract
>and/or a new module along similar lines that could serve as a base for the
>SQL generation performed by Tangram and DBIx::Class.

As you both stated before, I believe that my new Rosetta/SQL::Routine 
framework can serve as this base.  In fact, I designed the framework 
partly for this very task, to form a common base for things like 
object relational mappers that is higher level than the common base 
of DBI.  Likewise, I anticipate that such object relational mapping 
modules won't want to expose Rosetta's API directly through their own 
API any more than they expose DBI's, though I have no problem if you 
do.  Using Rosetta is principally similar to using DBI, in how its 
API is structured; the main difference is that you use SQL ASTs 
instead of SQL strings.

>We investigated a few CPAN modules, but none of them seemed quite flexible
>enough.  SQL::Routine seemed to fit the bill, but isn't lightweight nor coded
>in a style which I personally find maintainable; however it might prove to be
>an invaluable source of details, or maybe it will prove easier to work with
>Darren to address our concerns with the module so that it is more suitable.

I am willing to do what it takes to ensure SQL::Routine et al meets 
your needs.  Ideally I can just adapt the standard module to provide 
the kind of API or hooks that you need.  Or, in a worse case 
scenario, I can assist you with maintaining a stripped down branch of 
it.  As for maintainable, well, the big thing about SQL::Routine is 
that it is heavily data defined, though the data can't be changed by 
users of the module (for simplicity).  This means heavier up front, 
but it also scales to more features much better with relatively 
little added code.  The rest of Rosetta isn't so data defined as 
SQL::Routine, so it may look more like what you're used to.

>Please note that this is a relatively small component of the overall picture,
>and that it is very comfortably cushioned away from the user APIs of either
>DBIx::Class or Tangram.  Discussion on each of the above points is encouraged.

Exactly.

>15:59 < mugwump> 1. I think we both agree that it needs to be a lightweight,
>                  "pure" SQL AST module
>15:59 < mst> yes

While SQL::Routine looks complex, it is in fact very pure, doing 
nothing but maintaining the heavily data-defined AST.  It was 
designed that way on purpose; the purity even goes to the point that 
string SQL generators etc are separate and dependent projects rather 
than being bundled or built-in.  Most of its "weight" is actually 
documentation, and most of that is in separate POD-only files.

>16:00 < mst> 3. The SQL Engine will have a callback system to interrogate its
>              hosting ORM for info and object values

This doesn't exist yet, but lets work out what you need.  For all we 
know, you may want to store some info in the SQL::Routine model that 
you would otherwise store elsewhere.  It is a unified schema plus SQL 
statement representation model.

>16:01 < mugwump> 4. it needs to support partial ASTs,

You can do that; SQL::Routine has a multi-level API, and the lowest 
level is very piecemeal oriented.

>and know how to convert
>                  natural join syntax to explicit join syntax (if I've got my
>                  terms right)
>16:01 < mst> I think so, and I definitely get your meaning. yes.

Currently, SQL::Routine has no concept of natural joins per se, and I 
have no plan to add it; you must specify within it what your join 
conditions and result columns are.  This actually isn't very hard 
except that your OO->relational model must have explicit knowledge of 
the table columns, which I believe is the norm with OO-relational 
mappers.  Its only modules that use arbitrarily defined user queries 
that may have to scan a database schema to fill in the blanks.

This said, SQL::Routine also lets you reuse and nest query/view 
definitions, so that may give you natively some of what you want.

>16:02 < mugwump> 5. it needs to be easily subclassable for SQL dialects ?

This concept is irrelevant to the SQL::Routine way of doing things. 
At its core, SQL::Routine says mainly *what* you want to do (as SQL 
in general is designed to do, being a 4th-level language).  How that 
is implemented, at the detail of SQL dialects, is the job of other 
modules that use SQL::Routine (through they can choose to 
wrap-subclass it); SQL::Routine::SQLBuilder is an example of this, 
and that is configurable for multiple SQL dialects.

>16:03 < mst> 6. it should be possibly to plug into it in multiple 
>directions to
>              (a) add additional features (if you don't need complex joins,
>              don't load them) and (b) allow SQL dialect support

This sort of pluggability matter is only relevant to other modules 
that use SQL::Routine, not SQL::Routine itself; representing those 
things in the model is cheap, and removing that support saves next to 
nothing.

>16:17 < mugwump> also, 7. it needs to support sub-selects, named parameter
>                  binding, and all other SQL constructs often overlooked, such
>                  as GROUP BY & HAVING, etc.

None of these are overlooked by SQL::Routine/Rosetta.  They were 
designed to address such common omissions in other modules.  And 
supporting them is also cheap.

At 9:15 AM +0100 7/29/05, Matt S Trout wrote:
>The other thing we've looked at is Darren Duncan's Rosetta + SQL::Routine
>libraries, which are quite complex (at least to me at first look) but
>extremely powerful. Darren e-mailed me (independently of us starting to
>look at his code) with 
>http://darrenduncan.net/OSCON/OSCON2005LightningTalk.txt
>which gives an interesting summary indeed.

For those of you not on dbi-users to have heard it before, I am 
looking for feedback on this talk, which even now I don't know if 
Mark Jason Dominus has approved it or not.  Essentially, which things 
mentioned are the most or least attention grabbing, and what can be 
summarized without losing its impact.  This currently needs to lose 
about 25% of its length for me to be able to read it aloud within 5 
minutes.

>I'm trying to persuade Darren he should subscribe to one/both lists; in the
>mean time, comments on this as well as the points in Sam's e-mail are of
>course most welcome. So get thinking!

And so I have.

At 8:49 AM +0100 7/29/05, Matt S Trout wrote (by private email):
>Your expertise and your knowledge of the somewhat formidable libraries in
>question would be very much appreciated.

I hope that they will become considerably less formidable over time.

>This one sentence: "Views are updateable" strongly suggests to me that
>Rosetta is going to be the solution of choice. Thanks very much for that
>link, it's provided me a much better overview than my attempts at getting
>my head round the docs :)

Okay, the updateable views thing works like this:

1. SQL::Routine allows you to define the use of a view in all of the 
same ways as a table, meaning, select from it or use it in larger 
queries, insert into it, update and delete against it.

2. The Rosetta::Engine::Generic module (and its helper, 
SQL::Routine::SQLBuilder), will by default try to exploit a 
database's built-in support for updateable views, which are defined 
by the SQL:2003 standard.  The better databases should support this 
internally, so not much more work for us than to work with those 
actions against a table.  MySQL v5.x has this feature, for example.

3. When a database's native support is inadequate or nonexistent, 
then Rosetta::Engine::Generic will go to the trouble to generate 
multiple or transformed SQL and issue these multiples against the 
database.

4. Anyone, even you, is welcome to make a different 
Rosetta::Engine::* module that does this better.

An Engine can do whatever it wants to satisfy the user request.  Even 
use LWP or MyApp::File or whatever, instead of a SQL database.  That 
last one's looking at you, Todd Hepler.

As a final note, I'm in the middle of another large update across the 
line, so you shouldn't actually start to use Rosetta/SQL::Routine yet 
(though you can start to study it).  I plan to post a snapshot 
tonight or tomorrow, with the balance hopefully any day thereafter. 
This will be followed by a list post "Rosetta developer preview #3". 
Until then, Generic doesn't actually connect the dots and implement 
table create or select/update/delete/etc.

-- Darren Duncan