[Dbix-class] retrieve_from_sql

[Matt S Trout]

On Wed, Aug 03, 2005 at 12:31:24PM +0200, Emanuele Zeppieri wrote:
> > [...]
> > But seriously, lob some joins at me. I'm collecting them for when I
> > implement. Weird ones that span multiple tables and multiple 
> > join types with multiple conditions on each join are good.
> 
> Sure I'll do it. Working with some DB2 and Oracle so-called DBAs, I can
> collect some of the weirdest SQL queries ever seen ;-)

Excellent.
 
> Seriously, I can do even more: if you prefer a more formal approach, I
> can supply also some quite complete SQL grammar definition files, that
> can provide a good reference to check if some SQL construct is not
> handled.

In that direction you might be better off looking at Darren Duncan's
Rosetta + SQL::Routine code; he's already gone the formal grammar approach
so we're likely to use that for an implementation at some stage.

> I perfectly see your point: writing (and maintaining) an cross DBMSes
> SQL parser is a pain in the ass.
> I'm just afraid that providing an abstract syntax complexity-wise is
> ultimately an isomorphic problem, that is, not much easier.
> 
> SQL::Parser though does exactly that sort of deparsing into Perl data
> structures:

Must have a look at that, if only because I'm looking for a lighter-than-
SQL::Routine AST syntax to use in a $ORM <-> Rosetta module.
 
> A third option could be (and this is the last time I'll mention it,
> promised ;-) to request the user to explicitly supply the necessary
> metadata when she/he uses raw SQL.

Funnily enough, I was working out how to implement this exact thing as a
displacement activity this morning to avoid getting out of bed :)

Well, ::Abstract syntax supports \$scalar to dump $scalar straight into
the SQL; metadata is all aggregated in the attrs hash so

$class->retrieve_from_sql($sql, @bind, \%attrs);

could pretty much map onto

$class->retrieve_from_cond(\$sql, { %attrs, _bind => \@bind });

or whatever (there's no retrieve_from_cond, syntax for e.g. only :)

> Once polished a little I'll post the JOINs.

List and wiki, please *cracks whip*

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