Re: DBI v2 - The Plan and How You Can Help
Juerd
> > Oh drat - not the DBI connection string discussion again!
> There are certainly database-specific things to be worked around. An
> improvement to the current DSN scheme would be a URI, as discussed in
> the past. The leading dbi: on every DSN is redundant, so a URI might
> look like this:
> driver://user:pass@host:port/instance
I think URIs are the right way to go, and one of the very few things PHP
(though be it with PEAR) did right.
http://pear.php.net/manual/en/package.database.db.intro-dsn.php
It would be fun if we could just steal that design and build on top of
it, for compatibility, but also because other people have already
thought about it and proven that it works.
Juerd
--
http://convolution.nl/maak_juerd_blij.html
http://convolution.nl/make_juerd_happy.html
http://convolution.nl/gajigu_juerd_n.html
Sam Vilain
>>> Column 3 is a BYTEA column in Pg and needs special peppering to work.
> What sort of "peppering" ? DBI provides SQL_BLOB, and SQL_CLOB
> type descriptors (as well as SQL_BLOB_LOCATOR and SQL_CLOB_LOCATOR), so
> presumably DBD::Pg (or any other DBD supporting LOBs) provides the
> logic to map from
> $sth->bind_param(3, $somelob, SQL_CLOB);
>>> SOME_DATE_COLUMN is the database native date type. On Oracle you'll
>>> need to convert the ? to a 'TO_DATE(?)'.
> Er, why ? I haven't used DBD::Oracle lately, but assuming you
> $sth->bind_param(1, '2005-07-13', SQL_DATE),
> I'd assume DBD::Oracle would be smart enough to communicate that
That bind_param peppering is precisely what I'm talking about, thanks
for demonstrating my point. This requirement to use "bind_param" to
explicitly tell the DBI which placeholders correspond to which types
is rarely mentioned on any introductions to DBI, and as a result, very
few people carry this out in practice or are prepared to do the
necessary re-work to code bases to perform it.
So, DBD drivers need to hope that the database driver is smart enough to
pull apart the query, match it against the schema and automatically
setup the type correctly. Perhaps many C database access libraries
provide enough information to do this, and pardon my ignorance for never
having written or worked on a DBD to this level - but I'm guessing that
such query introspection isn't always possible.
And, in a sense, requiring that the DBD is able to introspect the query
and DTRT is an "extra restriction" that DBD authors need to conform to,
setting the bar for conformance so high that it is practically impossible
to write portable database access code.
Please note that I'm not suggesting that we do away with the existing
interface, for people who don't care about writing portable database
code. But I'd like to be able to write, for instance;
use v6;
use DBI-2;
my $query = SQL {
SELECT
*
FROM
FOO
LEFT JOIN BAR
ON BAR.FOOID = FOO.ID
};
if ($one) {
$query &&= SQL::WhereClause {
ONE = $one
};
}
my $dbh = DBI.connect(:source("myapp"));
my $sth = $dbh.prepare($query);
my $resultset = $sth.execute();
for =$resultset -> @row {
...
}
So what's happening here?
Well, the SQL construct marks the beginning of a segment of code that
happens to be in an alternate grammar, corresponding to some level of
ANSI SQL. This builds an object which corresponds to that query. In
fact, this can happen at compile time! The SQL { } is actually a
closure that returns a SQL object when it is called.
The later SQL::WhereClause is the same; the variable isn't merely
interpolated, but is closed over, and included as if it were a
placeholder. The &&= assignment operator uses the overloaded &&
operator on the SQL object, which sees it is being given a query
fragment, and adds it into the appropriate point on the SQL AST.
This should all be quite transparent - of course, an optional (but
portable) method of writing database queries. And it's all deliciously
Perlish, yielding less fussing around with buggy code stitching together
queries, and more clean expression of queries using a standard language.
Of course it will be entirely possible to layer support for this sort of
thing atop any DBI interface; but this *is* a version 2, we do have
prototypes for the mechanics of all this stuff - and Done Right™, it
could actually fulfil the goal of DBI - being able to write
Database-driven applications that are truly independant of the database
product in use. DBI v1 cuts the porting time down to next to nothing;
but we can get it even closer!
Sorry to "bang on" about this for so long, I'm sure you're all getting
sick of it by now- I had hoped that the original suggestion was just
going to be acknowledged as a valid way to enhance portability and
flexibility and considered without too much rehashing of what to some
is an old topic.
Sam.
Kiran Kumar
Tim Bunce
> At 4:04 PM +0100 8/16/05, Tim Bunce wrote:
> >I was a little dissapointed that there wasn't greater focus on using
> >Perl6 features - especially as it would have helped kick-start my own
> >understanding of Perl6 topics that I expect to be significant (such as
> >Roles and Pairs, to pick two at random). Perhaps the community of
> >Perl6+DBI users is too small at this point.
>
> One way that the Perl 6 thought process can be started is in
> considering the design principles laid out in Damian's new Best
> Practices book. I said to Damian at OSCON that I thought the
> practices he was putting forward were intended to get people thinking
> now in Perl 5 about ways of doing things that will be the natural way
> of doing them in Perl 6; he said something along the lines that I had
> good insight. So these practices are probably some good things to
> keep in mind as we move forward.
Yeap. I'm awaiting delivery of that one, plus several others including
MJDs Higher Order Perl.
> Now, speaking specifically in Perl 6 terms ...
>
> I suggest that DBI v2 has a more formal separation between interface
> and implementation. The parts of DBI can be grouped into these
> categories:
>
> 1. Role definitions for the public behaviour/API that DBI-using apps see.
> 2. Role definitions for the behaviour/API that DBI drivers/engines must have.
> 3. Class definitions that implement #1 and invoke #2.
> 4. Class definitions having a generic implementation of #2 or parts
> thereof, which a driver/engine can complete or override.
> 5. Basic utility classes that exist to the side of the above, which
> such as DBI drivers can optionally use to do some common things
> without rolling their own.
> 6. A basic test suite.
I agree entirely - except for the word "basic" in item 6 :)
One of the key things missing from DBI 1 was a test suite that could be
reused to test/validate different drivers.
Note that what you've described is essentially just what JDBC is.
Only JDBC has a comprehensive driver test/validate suite.
At the moment I'm thinking in terms of a Parrot-level DBDI modeled on
JDBC, with a thin Perl6-specific DBI layered on top. Other languages
targeting Parrot would have their own thin language adaption layers.
> I also recommend expelling some parts of the DBI distro into their
> own distros and/or leaving them to third parties. A prime example is
> the proxy server/client stuff; that should be a separate project.
I'd like to see someone do a stateless proxy sometime (as I've
outlined previously) and I'll be ensuring there's a serializable RowSet
object available - but, yes, such things should be separate.
Tim.