At 6:30 PM -0700 7/11/05, Dean Arnold wrote:
RE: SQL Parse Trees (or other non-SQL query input)
Since none of (ODBC, JDBC, ADO.NET) seems compelled to
impose this concept on driver writers, I don't see why
DBI should be the vanguard.
I should emphasize that I never expected to be able to send any type
of ASTs over the pipe to the database. They would still be
interpreted by the database driver for Perl and/or a wrapper thereon,
into the database native format. Its just that, to an application,
it would appear that the ASTs were going over the pipe, as to their
effect, even though they weren't behind the scenes.
However, implementing a subclass of DBI to support it
seems technically feasible, so I'd suggest that
those of you championing this requirement implement one
on DBI v1.
I agree. Already in progress. In a week or two I should announce
"Rosetta/SQL::Routine Developer Preview #3" and look forward to
people trying it out and giving me the usual "how do I" questions.
But wait until then before making any in-depth analysis, since I have
some large updates to post first.
As the proponents of this notion
are so generous with their requirements for those of us
who develop DBI drivers and/or contribute
development efforts to the DBI itself, I'm sure they won't
object if I provide a few requirements:
1. For DBI drivers which support them, your subclass
- arbitrary numbers and levels of JOINs (including
various outer, and non-equijoins)
- arbitrarily nested subqueries (including correlated)
- HAVING and GROUP BY clauses
- ORDER and LIMIT clauses
- updatable cursors
- database-specific SQL extensions
Rosetta supports the definition of all of the above right now, except
that the last 2 are incomplete (for now). I still have to add "the
last mile", which is joining the definition to the implementation,
though that is what DR3 is for. See the top of
SQL::Routine::Language for a summary of what I claim to support.
2. It must function with current versions of 40% of DBD's
created or updated on CPAN since Jan. 1, 2003. Said 40%
- one 'exotic' driver (e.g.,
DBD::iPod or DBD::Amazon, but excluding DBD::Google,
whose syntax is too simplistic for a meaningful test)
My expectation is to support all DBDs that are themselves known to
work under the newest Perls and DBIs. Databases that already support
a feature when it is invoked as a SQL string will be supported for
that feature by Rosetta prior to those that don't and for which the
features need to be emulated.
BTW: If you need a list of DBD's meeting said requirement, let me know,
I just pulled one down.
Sure, send it over.
3. It cannot require any changes to either DBI or the
4. It must produce a database-independent conforming set of error codes
(feel free to use SQLSTATE aka $h->state)
Already done, though the current list of codes is short and pending updates.
5. It must be uploaded to CPAN, and list, and demonstrably function against,
the DBD's selected in requirement (2) above.
I can only personally test against databases that are easy to install
on Mac OS X 10.3.9 and are free-as-in-beer (eg, SQLite, MySQL); for
others, I will need assistence to prove that they work.
Once you've implemented the subclass, you'll have empirical proof
of the feasibility, and, more importantly, you'll be able to port
the subclass to DBIv2, without any additional burden on DBI
I accept your challenge.
-- Darren Duncan