[Dbix-class] retrieve_from_sql

[Emanuele Zeppieri]

> -----Original Message-----
> From: dbix-class-bounces at lists.rawmode.org 
> [mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of 
> Matt S Trout
> Sent: Tuesday, August 02, 2005 10:52 AM
> To: dbix-class at lists.rawmode.org
> Subject: Re: [Dbix-class] retrieve_from_sql
> 
> [...]
> 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 ;-)

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.

Just let me know.

> An example: join conditions in Relationship.pm are expressed 
> in abstract syntax and can be resolved to *either* the appropriate
> stuff for whatever JOIN foo ON clause you're assembling *or* a WHERE
> clause based on being handed an object for one side of the
> relationship. If I didn't use abstract syntax for that I'd have to
> either de-parse the SQL into some abstract syntax
> (ta-dah) or regex the thing (far too fragile).

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:

use SQL::Parser;
my $parser = SQL::Parser->new();
$parser->parse( $an_sql_statement );
use Data::Dumper;
print Dumper $parser->structure;

Quite some time ago I've tried it and it worked quite well except for
some problems with complex queries, but I see that its development is
quite active, so those problems maybe are now solved.
Just another option, just in case you didn't already know it...
OK, admittedly not a viable option really (maybe only when the user
resorts to raw SQL...)

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.

Once polished a little I'll post the JOINs.

Ciao,
Emanuele.