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

[Matt S Trout]

On Fri, Jul 29, 2005 at 06:42:04PM -0700, Darren Duncan wrote:
> >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.

The more I look at it the more I see what you mean - I don't think the overhead
will be that great, although in the DBIx::Class spirit of "use only what you
need" we should still provide an (albeit less featureful) plain DBI storage
engine as well - that would also be handy for people who already have plain
DBI code and want to share dbhs etc., I suspect.

I'm also agreed entirely with the data defined architecture; I don't think
a stripped down branch could easily be nearly as capable per line of code
as the full concept. If it becomes a bottleneck, we can always profile
and optimise later.
 
> >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.

I'm more and more starting to think that what we really want is a common
(between Tangram and DBIx::Class) layer above Rosetta that provides a
reasonable simply interface to the ORM and has callbacks registered to handle
converting and inferring stuff on the way down - possibly call the class
Rosetta::ORM or something like that.
 
> >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.

Actually, I'd guess the layer above would be responsible for converting
natural joins into explicit joins - so long as Rosetta can handle
LEFT/RIGHT/OUTER/INNER JOIN etc. (which I assume it can) then that should
be sufficient.
 
> >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.

Sorted then, so far as I'm concerned. Sam may have more comments though.
 
> >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.

Yes, I'd come to this conclusion already. Assuming the Rosetta::ORM (or
whatever it ends up called) module is nicely extensible it won't become
an issue.

> 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.

It's fine; any API sufficiently powerful for our purposes was bound to be
fairly big, and we can provide a more specialised but easier API around it.

Also bear in mind (and several people are probably going to scream and run
away but it needs to be said) I'm not actually a database specialist by
any means; mostly I'm interested in systems automation (hence BAST) and in
things that minimise repeated/boilerplate code. Join-and-search in ::Sweet
originally came about because I was doing a search function for an
e-commerce system and realised the intelligence in that direction should
really be in the ORM - so I asked for a day's delay from the client, took
the day off and wrote it. I'm hoping we've got plenty of people who *are*
RDBMS specialists to kick me if I make any glaring errors - and to submit
failing tests so once I've fixed it nobody else can make the same mistake
again later :)
 
> 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.

Right. Perfect.
 
> 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.

And we should be able to patch it for non-standard views if required.

> 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.

Which is nice, because I *really* wasn't looking forward to figuring out
how to write that myself :)

> 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.

Indeed.

> 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.

Ah, right. Well, I don't expect to get time to look at it myself for a few
days anyway, and even then I think there's quite a lot of POD reading and
thinking involved before I'd like to even try to start implementing stuff.

Plus I imagine this discussion won't peter out until close to then anyway :)

-- 
     Matt S Trout           Website: http://www.shadowcatsystems.co.uk
  Technical Director        E-mail:  mst (at) shadowcatsystems.co.uk
Shadowcat Systems Ltd.