Re: DBI v2 - The Plan and How You Can Help
On Tue, Aug 16, 2005 at 03:58:54PM -0400, John Siracusa wrote:
On 8/16/05, Tim Bunce [EMAIL PROTECTED] 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.
I'm afraid that DBI2 for Perl 6 will fall into the trap that I sometimes
feel like DBI1 fell into: the curse of being designed before the idioms and
Best Practices of API design in the language have been established.
I think it'll take years, and much actual production experience building
Perl 6 modules before the community learns what works and what doesn't for a
Perl 6 API (let alone implementation). So trying to pin down a properly
Perl-6-ish API before Perl 6 is even through the language design process
strikes me as a Very Bad Idea.
I remember the early years of Perl 5 development, when a new feature was
added there'd be a period of over-zealous use followed by a hangover as
all the problems and edge-cases became apparent.
With Perl 6 there's going to be some almighty hangovers :)
That could explain why there were so few Perl 6 related suggestions: no one
knows how to design a good Perl 6 API yet, and any guess is very likely to
be wrong. Instead, suggestions have focused on what we do know: DBI in Perl
5 and Perl 5 API design. In that spirit, here's my suggestion: no more
configuration through magic/tied hashes. (e.g., $dbh-{'AutoCommit'} = 1)
(Probably goes without saying, but I wanted to make sure someone said it ;)
Hey, it seemed like a good idea at the time :)
(Actually it's still a good idea in many ways, especially in relation to
its behaviour for unknown driver-private attributes and DBI version skew.
But it does need rethinking for DBI2.)
Anyway, it maybe worthwhile to have a DBI 1.99 first, and then maybe a 1.999
after that. Basically, get one or two willing DBD authors who will help you
to test and then throw away the first two attempts at a Perl 6 DBI API.
Then at least you'll have some confidence when you commit to a DBI 2.0
API...which will be several years after Perl 6 is released, I hope.
It'll be DBI 2 as DBI 1 still has a very long life ahead of it, but
it'll be DBI 2.0.00xxx for quite a while :)
Of course, *someone* has to go first so we can all learn what works best
in Perl 6. I'm just saying that maybe DBI, which took the bullet in Perl 5
to some degree, is under no obligation to do so again. (This assumes that
we'll have some way to use Perl 5 DBI within Perl 6 to tide us over, of
course...)
I'm in no great rush as one of my core assumptions is that DBI v1 will
be available in Perl 6 via either Ponie or direct embedding of libperl5.so.
Tim.
Re: DBI v2 - The Plan and How You Can Help
On Tue, Aug 16, 2005 at 01:16:19PM -0700, Darren Duncan wrote: 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.
Re: DBI v2 - The Plan and How You Can Help
On Tue, Aug 16, 2005 at 12:12:02PM -0700, Dean Arnold wrote: Tim Bunce wrote: And nobody mentioned JDBC as a potential model. Odd that. I was sorely tempted to do so (and did mention it a few times in my posts, along w/ ODBC and ADO.NET), but there are some things about JDBC which rub me the wrong way (e.g., explicit set/get methods for every data type no true binding support; the lame bulk interface; etc.). I'm not crazy about all the DataSource business, either. I think all those are fixable for Perl/Parrot. Same for the painful need for try catch every few lines. But the threading support, the Factory pattern presented by Statement classes, the nice separation of metadata from statements/resultsets/connections, and XA would certainly be nice models to follow. That's what I'm thinking. Not only nice but also well proven and widely understood. One area of concern I have is the ability to subclass. I've been struggling w/ trying to subclass+extend JDBC the same way I subclass DBI for some things, and haven't found any app-neutral solutions just yet (trying to wrap another JDBC driver and expose its driver specific methods seems to require a lot of extra heavy lifting). There are lots of people who have problems or complaints about subclassing the DBI :) Tim.
Re: DBI v2 - The Plan and How You Can Help
On 8/17/05 5:39 AM, Tim Bunce wrote: On Tue, Aug 16, 2005 at 03:58:54PM -0400, John Siracusa wrote: I think it'll take years, and much actual production experience building Perl 6 modules before the community learns what works and what doesn't for a Perl 6 API (let alone implementation). So trying to pin down a properly Perl-6-ish API before Perl 6 is even through the language design process strikes me as a Very Bad Idea. I remember the early years of Perl 5 development, when a new feature was added there'd be a period of over-zealous use followed by a hangover as all the problems and edge-cases became apparent. Early years? Just look at inside-out objects or MI with NEXT today! Maybe it never ends...for some people, anyway ;) With Perl 6 there's going to be some almighty hangovers :) Understatement of the week :) Anyway, it maybe worthwhile to have a DBI 1.99 first, and then maybe a 1.999 after that. Basically, get one or two willing DBD authors who will help you to test and then throw away the first two attempts at a Perl 6 DBI API. Then at least you'll have some confidence when you commit to a DBI 2.0 API...which will be several years after Perl 6 is released, I hope. It'll be DBI 2 as DBI 1 still has a very long life ahead of it, but it'll be DBI 2.0.00xxx for quite a while :) I just meant that there should be several, possibly very different, attempts at DBI2 before the real DBI2 API is pinned down. Making the experiments have a 1.99x version helps to prevent people from thinking this is DBI2! when it's really just the first or second prototype. As for the actual 1.x DBI reaching 1.99, well, all I can say is to start using that hundredths place! :) -John
Re: DBI v2 - The Plan and How You Can Help
On Sat, Jul 09, 2005 at 10:25:32PM +1000, Adam Kennedy wrote: In particular, the DBI must not mandate impossible levels of support from the drivers. It will benefit you nothing if the DBI is immaculate and wonderful and incredibly all-singing and all-dancing, but no-one can write a driver for it because the requirements cannot be met by the actual DBMS that Perl + DBI needs to work with. I concur. Like CPAN as a whole, DBI's strength is in it's complete and near universal coverage of all databases, and insanely great (and occasisionally greatly insane) drivers that do strange and wonderful things. If we start sacrificing drivers by raising the bar too high, DBI as a whole suffers. Anyone proposing new features for DBI needs to be extremely careful of CYJ syndrome. Can't You Just (or sometimes Could You Just) syndrome is described here. http://c2.com/cgi/wiki?CouldYouJust http://www.oreillynet.com/pub/wlg/3593 http://c2.com/cgi/wiki?JustIsaDangerousWord Go read them now. I'll wait... That's a significant part of what happened to perl5-porters in The Bad Years. Many more talkers than doers and much use of we could do ... when the doing would clearly have to be done by someone else. I have an increasing suspicion that having open design processes like the Tim's call for comments plays a big part in it as well. Did I ever say we'd have an open design process? :-) I just called for suggestions, proposals, and random thoughts. It's my job to mix those in with my own random thoughts and try to distill something reasonably coherent and Practical. Then we'll go round the loop a few (dozen) times kicking the tires and mixing metaphors till enough people are happy enough. (I get the casting vote on behalf of the silent majority :) 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. And nobody mentioned JDBC as a potential model. Odd that. Still, I'm sure things will liven up once I've put an initial sketch together... Tim.
Re: DBI v2 - The Plan and How You Can Help
On 8/16/05, Tim Bunce [EMAIL PROTECTED] 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.
I'm afraid that DBI2 for Perl 6 will fall into the trap that I sometimes
feel like DBI1 fell into: the curse of being designed before the idioms and
Best Practices of API design in the language have been established.
I think it'll take years, and much actual production experience building
Perl 6 modules before the community learns what works and what doesn't for a
Perl 6 API (let alone implementation). So trying to pin down a properly
Perl-6-ish API before Perl 6 is even through the language design process
strikes me as a Very Bad Idea.
That could explain why there were so few Perl 6 related suggestions: no one
knows how to design a good Perl 6 API yet, and any guess is very likely to
be wrong. Instead, suggestions have focused on what we do know: DBI in Perl
5 and Perl 5 API design. In that spirit, here's my suggestion: no more
configuration through magic/tied hashes. (e.g., $dbh-{'AutoCommit'} = 1)
(Probably goes without saying, but I wanted to make sure someone said it ;)
Anyway, it maybe worthwhile to have a DBI 1.99 first, and then maybe a 1.999
after that. Basically, get one or two willing DBD authors who will help you
to test and then throw away the first two attempts at a Perl 6 DBI API.
Then at least you'll have some confidence when you commit to a DBI 2.0
API...which will be several years after Perl 6 is released, I hope.
Of course, *someone* has to go first so we can all learn what works best
in Perl 6. I'm just saying that maybe DBI, which took the bullet in Perl 5
to some degree, is under no obligation to do so again. (This assumes that
we'll have some way to use Perl 5 DBI within Perl 6 to tide us over, of
course...)
-John
Re: DBI v2 - The Plan and How You Can Help
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. 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 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. -- Darren Duncan
Re: DBI v2 - The Plan and How You Can Help
Tim Bunce wrote: And nobody mentioned JDBC as a potential model. Odd that. I was sorely tempted to do so (and did mention it a few times in my posts, along w/ ODBC and ADO.NET), but there are some things about JDBC which rub me the wrong way (e.g., explicit set/get methods for every data type; no true binding support; the lame bulk interface; etc.). I'm not crazy about all the DataSource business, either. But the threading support, the Factory pattern presented by Statement classes, the nice separation of metadata from statements/resultsets/connections, and XA would certainly be nice models to follow. One area of concern I have is the ability to subclass. I've been struggling w/ trying to subclass+extend JDBC the same way I subclass DBI for some things, and haven't found any app-neutral solutions just yet (trying to wrap another JDBC driver and expose its driver specific methods seems to require a lot of extra heavy lifting). Still, I'm sure things will liven up once I've put an initial sketch together... Tim. Dean Arnold Presicient Corp.
Re: DBI v2 - The Plan and How You Can Help
On Thu, Jul 07, 2005 at 08:32:47AM -0500, Jones Robert TTMS Contractor wrote: When I go to the donation page and attempt to make a donation, the drop-down box does not give DBI as a valid recipient. Is it possible several people may not have donated as they noticed the same results, or maybe they did and it all went into the Perl Development Fund instead? The Perl Foundation default donation page doesn't list the DBI Development Fund (for various reasons). To get that option you can use http://dbi.perl.org/donate/ which will redirect you[1] Thank you. Tim. [1] https://donate.perlfoundation.org/index.pl?node=Contribution%20Infoselfund=102 -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Friday, July 01, 2005 7:06 PM To: perl6-language@perl.org; dbi-users@perl.org Subject: DBI v2 - The Plan and How You Can Help Once upon a time I said: http://groups-beta.google.com/group/perl.dbi.users/msg/caf189d7b404a003?dmode=sourcehl=en and wrote http://search.cpan.org/~timb/DBI/Roadmap.pod which yielded: https://donate.perlfoundation.org/index.pl?node=Fund+Drive+Det ailsselfund=102 (A little over $500 of that I effectively put in myself.) My *sincere* thanks to all those who donated to the fund, especially individuals. I had hoped for more corporate response with less from individuals and I'm touched by the personal generosity shown. I've not drawn any money from it yet and doubt that I will myself. (I'm considering suggesting that the Perl Foundation make payments from the fund to people making specific contributions to the DBI. I'm thinking especially of work on a comprehensive test harness. But I'll see how the developments below pan out before making specific arrangements.) So, that lead to: http://groups-beta.google.com/group/perl.dbi.dev/browse_frm/th read/ef14a9fc0a37441f/fb8fe20a86723da0#fb8fe20a86723da0 Which sums up fairly well where I'm at: DBI v1 will rumble on for Perl 5 and DBI v2 will be implemented for Perl 6. --- digression --- At this point I'd like to make a slight digression to highlight the amazing work going on in the Perl 6 community at the moment. Especially Autrijus' Pugs project which has brought Perl 6 to life. Literally. Take a look at: http://pugscode.org/talks/yapc/slide1.html http://use.perl.org/~autrijus/journal and especially: http://use.perl.org/~autrijus/journal/24898 Yes, that really is Perl 6 code using the DBI being executed by Pugs. That's great, and I was truly delighted to see it because it takes the pressure off the need to get a DBI working for Perl 6 - because it already is working for Perl 6. At least for Pugs. (The Ponie project is also likely to provide access to Perl 5 DBI from Perl 6 by enabling future versions of Perl 5 to run on Parrot.) --- digression --- I have recently come to an arrangement that will enable me to put some worthwhile development time into DBI (still very much part-time, but enough to give it focus and move forward). My initial goals are: 1. to work on a more detailed specification for the DBI v2 API that takes advantage of appropriate features of Perl 6. 2. to work on a more detailed specification for the DBDI API http://groups-beta.google.com/group/perl.perl6.internals/msg/c fcbd9ca7ee6ab4 3. to work on tools to automate building Parrot NCI interfaces to libraries (such as database client libraries, obviously :) But I'm hoping you'll join in and help out. I've kept an eye on Perl 6 and Parrot developments but I'm no expert in either. What I'd like *you* to do is make proposals (ideally fairly detailed proposals, but vague ideas are okay) for what a Perl 6 DBI API should look like. Keep in mind that the role of the DBI is to provide a consistent interface to databases at a fairly low level. To provide a *foundation* upon which higher level interfaces (such as Class::DBI, Tangram, Alzabo etc. in Perl 5) can be built. So, if you have an interest in the DBI and Perl 6, put your thinking cap on, kick back and dream a little dream of how the DBI could be. How to make best use of the new features in Perl 6 to make life easier. Then jot down the details and email them to me (or to dbi-users@perl.org if you want to kick them around in public for a while first). I'm about to fly off for two weeks vacation (in a few hours), blissfully absent of any hi-tech gear beyond a mobile phone. When I get back I'll gather up your emails and try to distill them into a coherent whole. Have fun! Tim.
Re: DBI v2 - The Plan and How You Can Help
We could have an option to do Bulk Inserts ..
Re: DBI v2 - The Plan and How You Can Help
On 7/14/05, Sam Vilain [EMAIL PROTECTED] wrote: Of course it will be entirely possible to layer support for this sort of thing atop any DBI interface; Exactly my point. Please be so kind as to implement your ideas in a DBI extension. Time and community will prove whether you are right by using your extension or not. Fact is, that there are quite some drivers which will never be able to adhere to your wishes. Blocking them implementing a DBI2 driver seems (to me) to be a larger problem, compared with the need of using a DBI2 subclass and not DBI2 directly. Jochen -- What are the first steps on the moon, compared to your child's?
RE: DBI v2 - The Plan and How You Can Help
Sorry, instead of implicit 'commit', I mean to say implicit conversion. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Reidy, Ron Sent: Wednesday, July 13, 2005 9:06 AM To: Sam Vilain; Dean Arnold Cc: dbi-users@perl.org; dbi-dev@perl.org; perl6-language@perl.org Subject: RE: DBI v2 - The Plan and How You Can Help -Original Message- From: Sam Vilain [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 5:04 PM To: Dean Arnold Cc: dbi-users@perl.org; dbi-dev@perl.org; perl6-language@perl.org Subject: Re: DBI v2 - The Plan and How You Can Help Dean Arnold wrote: RE: LOBs and SQL Parse Trees: having recently implemented LOB support for a JDBC driver (and soon for a DBD), I can assure you that SQL parse trees are unneeded to support them. For databases or this; SELECT * FROM FOO WHERE SOME_DATE_COLUMN ? SOME_DATE_COLUMN is the database native date type. On Oracle you'll need to convert the ? to a 'TO_DATE(?)'. No you do not. The SQL engine will perform an implicit commit of the data. - Ron Reidy Lead DBA Array BioPharma, Inc. Sam. This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system. This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: DBI v2 - The Plan and How You Can Help
-Original Message- From: Sam Vilain [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 5:04 PM To: Dean Arnold Cc: dbi-users@perl.org; dbi-dev@perl.org; perl6-language@perl.org Subject: Re: DBI v2 - The Plan and How You Can Help Dean Arnold wrote: RE: LOBs and SQL Parse Trees: having recently implemented LOB support for a JDBC driver (and soon for a DBD), I can assure you that SQL parse trees are unneeded to support them. For databases or this; SELECT * FROM FOO WHERE SOME_DATE_COLUMN ? SOME_DATE_COLUMN is the database native date type. On Oracle you'll need to convert the ? to a 'TO_DATE(?)'. No you do not. The SQL engine will perform an implicit commit of the data. - Ron Reidy Lead DBA Array BioPharma, Inc. Sam. This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
Re: DBI v2 - The Plan and How You Can Help
Dean Arnold wrote:
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.
Re: DBI v2 - The Plan and How You Can Help
RE: Placeholders: since DBIv1 already supports both forms of PH's, I see no reason to deprecate or abandon either form. Furthermore, to my knowledge, none of (ODBC, JDBC, ADO.NET) has abandonded or deprecated the ? form, so I don't see the need for DBI to. RE: LOBs and SQL Parse Trees: having recently implemented LOB support for a JDBC driver (and soon for a DBD), I can assure you that SQL parse trees are unneeded to support them. For databases robust enough to support LOBs, they'll almost always provide sufficient metadata info and/or SQL syntax to manipulate them; only databases which don't support LOBs have that difficulty. Furthermore, a quick review of the current DBI will indicate that Mssr. Bunce has already implemented some stub methods for generalized support. 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. 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. Feel free to use DBIx::Chart to bootstrap your project. 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 must support - 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 2. It must function with current versions of 40% of DBD's created or updated on CPAN since Jan. 1, 2003. Said 40% must include - DBD::ODBC - DBD::Oracle - DBD::Pg - DBD::MySQL - DBD::CSV - one 'exotic' driver (e.g., DBD::iPod or DBD::Amazon, but excluding DBD::Google, whose syntax is too simplistic for a meaningful test) (FWIW: Past experience (e.g., execute_array()) leads me to believe Mssr. Bunce's requirements are likely much higher than 40%, so choose wisely, grasshopper) BTW: If you need a list of DBD's meeting said requirement, let me know, I just pulled one down. 3. It cannot require any changes to either DBI or the selected DBD's. 4. It must produce a database-independent conforming set of error codes (feel free to use SQLSTATE aka $h-state) 5. It must be uploaded to CPAN, and list, and demonstrably function against, the DBD's selected in requirement (2) above. 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 developers. Regards, Dean Arnold Presicient Corp.
Re: DBI v2 - The Plan and How You Can Help
BTW: If you need a list of DBD's meeting said requirement, let me know, I just pulled one down. Sure, send it over. [ ] DBD-ADO-2.94.tar.gz 31-Jan-2005 02:4041k GZIP compressed docume [ ] DBD-ASAny-1.13.tar.gz 31-Oct-2003 15:0030k GZIP compressed docume [ ] DBD-Amazon-0.10.tar.gz 23-May-2005 15:4158k GZIP compressed docume [ ] DBD-AnyData-0.08.tar.gz 19-Apr-2004 03:1620k GZIP compressed docume [ ] DBD-CSV-0.22.tar.gz 31-Mar-2005 18:0636k GZIP compressed docume [ ] DBD-Chart-0.81.tar.gz 26-Jan-2005 19:59 212k GZIP compressed docume [ ] DBD-DB2-0.78.tar.gz 19-Sep-2004 10:3475k GZIP compressed docume [ ] DBD-File-0.34.tar.gz21-Jun-2005 01:14 8k GZIP compressed docume [ ] DBD-Google-0.11.tar.gz 04-Mar-2004 18:5120k GZIP compressed docume [ ] DBD-Informix-2005.01.. 14-Mar-2005 19:01 267k GZIP compressed docume [ ] DBD-Ingres-0.51.tar.gz 12-Jan-2004 06:1846k GZIP compressed docume [ ] DBD-InterBase-0.43.t.. 25-Feb-2004 04:3078k GZIP compressed docume [ ] DBD-LDAP-0.06.tar.gz12-Mar-2004 21:4825k GZIP compressed docume [ ] DBD-Log-0.22.tar.gz 27-May-2005 06:5114k GZIP compressed docume [ ] DBD-MaxDB-7_5_00_26... 18-Apr-2005 08:3879k GZIP compressed docume [ ] DBD-Mimer-1.00.tar.gz 25-Nov-2003 15:5171k GZIP compressed docume [ ] DBD-Mock-0.27.tar.gz11-Jul-2005 11:3634k GZIP compressed docume [ ] DBD-Multiplex-1.96.t.. 25-Jan-2005 17:30 9k GZIP compressed docume [ ] DBD-ODBC-1.13.tar.gz08-Nov-2004 10:1595k GZIP compressed docume [ ] DBD-Oracle-1.16.tar.gz 22-Oct-2004 05:17 230k GZIP compressed docume [ ] DBD-Pg-1.43.tar.gz 23-Jun-2005 08:09 128k GZIP compressed docume [ ] DBD-PgPP-0.05.readme09-May-2004 08:06 3k [ ] DBD-PgPP-0.05.tar.gz13-May-2004 12:5616k GZIP compressed docume [ ] DBD-PgSPI-0.02.tar.gz 06-Dec-2004 00:3021k GZIP compressed docume [ ] DBD-Redbase-0.22.tar.gz 21-Oct-2003 22:5128k GZIP compressed docume [ ] DBD-SQLite-1.09.tar.gz 20-Jun-2005 11:42 464k GZIP compressed docume [ ] DBD-SQLite2-0.33.tar.gz 10-Sep-2004 11:50 355k GZIP compressed docume [ ] DBD-Sprite-0.56.tar.gz 12-Jun-2005 21:5286k GZIP compressed docume [ ] DBD-Sybase-1.05.tar.gz 19-Dec-2004 05:01 183k GZIP compressed docume [ ] DBD-TSM-0.04.readme 22-Mar-2005 16:05 2k [ ] DBD-TSM-0.04.tar.gz 23-Jun-2005 16:32 9k GZIP compressed docume [ ] DBD-Teradata-1.20.ta.. 17-Sep-2004 19:2736k GZIP compressed docume [ ] DBD-Trini-0.01.tar.gz 15-Jul-2003 03:1821k GZIP compressed docume [ ] DBD-Unify-0.31.tgz 16-Mar-2004 11:0731k GZIP compressed tar ar [ ] DBD-XBase-0.241.tar.gz 21-Nov-2003 09:25 109k GZIP compressed docume [ ] DBD-Yaswi-0.01.tar.gz 21-Feb-2005 19:46 4k GZIP compressed docume [ ] DBD-iPod-0.01.tar.gz06-Jan-2005 02:4113k GZIP compressed docume [ ] DBD-mysql-3.0002.tar.gz 11-Jul-2005 12:49 127k GZIP compressed docume [ ] DBD-mysql-AutoTypes-.. 02-Mar-2004 06:03 3k GZIP compressed docume [ ] DBD-mysql-SimpleMySQ.. 28-Apr-2004 16:39 4k GZIP compressed docume [ ] DBD-mysqlPP-0.04.tar.gz 24-Jan-2003 06:14 7k GZIP compressed docume - Dean
Re: DBI v2 - The Plan and How You Can Help
Dean Arnold wrote: RE: LOBs and SQL Parse Trees: having recently implemented LOB support for a JDBC driver (and soon for a DBD), I can assure you that SQL parse trees are unneeded to support them. For databases Great! Perhaps you can shed some light on how to do it for this, then. SQL command; INSERT INTO FOO (?, ?, ?, ?); Column 3 is a BYTEA column in Pg and needs special peppering to work. or this; SELECT * FROM FOO WHERE SOME_DATE_COLUMN ? SOME_DATE_COLUMN is the database native date type. On Oracle you'll need to convert the ? to a 'TO_DATE(?)'. Sam.
Re: DBI v2 - The Plan and How You Can Help
I have an additional reply to the following ... At 10:25 PM +1000 7/9/05, Adam Kennedy wrote: In any case, I still propose that DBI2 split the driver interface into Roles. The main DBI2::Role::Transport role does ONLY what DBI does best now. That is, connecting to the database, preparing and sending queries, and fetching the results. Forget the current -tables interface. Drivers can OPTIONALLY implement the DBI2::Role::Schema interface to handle interrogation of the database schema. The current way we handle it is really messy by my standards, and could use a dedicated interface. As you invent major new features for DBI2, implement them roles. DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy Beverly Hills feature), etc etc. Exactly what these roles should be I don't want to state with any certainty. That's the sort of thing that Tim, with his complete understanding of the issues, should be doing on his own. One of the reasons I like this idea is that it lets DBI define multiple Roles that may be mutually exclusive and accomplish the same task in different ways. For example, the idea of whether the interface will accept SQL AST objects in addition to or instead of SQL strings. The standard DBI2::Role::Transport will take SQL strings and only SQL strings, as the current DBI does. There can be an additional Role for each AST. In that situation, there are several case scenarios depending on the type of database being used and what the driver maker is willing to support. 1. The first case is the common database server that always takes its input as string SQL. In this case, the standard string-taking Role will be the most native fit for it, and any AST-supporting Roles would have to be, in some fashion, implemented as wrappers on top of this, converting their contents to string SQL for the database. It is assumed that the AST in question would come with its own set of SQL generators, either in the same distribution or a separate distribution that is an extension to it. If the DBI driver writer chooses to implement the relevant AST-taking Role, it would presumably use the AST's own SQL generator to implement that support, so the driver writer hardly has any work to do of their own. Or, if the DBI driver writer does not choose to implement the Role, then users of said AST would use it as or with a DBI wrapper that invokes the SQL generating functionality itself and passes the result to DBI2::Role::Transport. 2. The second case is a usually-embedded database server or library that does not take string SQL as its native input. In this case, some sort of AST-supporting Role would be the most native fit for it, since it is probably easier for that DBI driver to extract the information it needs from the AST than to parse string SQL; the DBI2::Role::Transport Role would have to be, in some fashion, implemented as a wrapper on top of this, which parses string SQL into an AST. Most likely, an externally destributed SQL parser would be the best choice, so it can be improved independently of the driver, as with any SQL generators. (SQL::Statement is an example of this already being done.) Now, if support for a string-SQL interface is mandatory for all DBI drivers, then it is possible that all AST-taking Roles can be implemented on top of the string-taking Role. As is the case with DBI v1. But, for the sake of those drivers that want ASTs internally, having a way for them to get that input natively, through officially sanctioned appropriate Roles, that lets those drivers save themselves a lot of work and the system is potentially much faster and easier to improve, without sacrificing compatability. A separate advantage of this Roles thing is backwards compatability due to a multi-versioned API. If we wish to implement a significant API or implementation change in DBI after DBI 2 is launched, we can be free to do so without breaking older programs because they will by definition be writing against a specific API version. We just have to maintain a Role for each distinct version that maps what the application expects to how things work in the background. I liken this to a new feature in Mac OS X starting with 10.4 Tiger; versioned KPIs (kernal programming interfaces); from now on, it is a lot easier for Apple to release significantly changed kernals in newer Mac OS X versions without breaking any applications or drivers, because those specify a versioned API which continues to exist as a wrapper. See http://arstechnica.com/reviews/os/macosx-10.4.ars/4 for what I'm talking about. However it's done, I'm thinking something akin to that would be good for DBI. -- Darren Duncan
Re: DBI v2 - The Plan and How You Can Help
driver://user:[EMAIL PROTECTED]:port/instance I haven't been following this too closely, so my apologies if already mentioned. This connect string is very much like the new Easy Connect Naming method in Oracle 10g. eg. sqlplus scott/[EMAIL PROTECTED]:port/service Note that it is not 'instance', but 'service'. Jared
Re: DBI v2 - The Plan and How You Can Help
No - you don't seem to understand. The challenge-response protocol can ask someone for the RSA key fob number this time, their mother's maiden name the next time, their employee number the time after that, and nothing on the fourth occasion. You cannot predict what the extra information requested is going to be - so you can't provide the extra information in the initial connection attempt because you don't know which extra information is going to be needed. That's what provides the security - the unpredictability of the question, so that it is hard to pre-programme the answer. Ah but you can know in advance! :) You may not know the actual result per instance, but you CAN know the decision process you'll need to go through. Which you can provide as a parameter in the form of a CODE reference. :) i.e. a callback But that's a minor point and overall I completely agree with your general ideas. Adam K
Re: DBI v2 - The Plan and How You Can Help
On Sat, 2005-07-09 at 12:42 +0200, Jochen Wiedmann wrote: Jonathan Leffler wrote: I dunno which DBMS support prepare without a database connection, but I would expect all the mainstream databases to require a database connection. +1 I'm also far from convinced that there's any significant benefit in separating the 'create a database handle' from the 'connect to database server' part. +1 Not to mention the effect, that one major charm of DBI is its simplicity: Connect, Execute for updates, inserts, or deletes and Connect, Execute, Fetch for select. I can't see an advantage in overly extending the interface. Personally I tend to agree with you. I haven't read the whole thread, but I'm not yet convinced that the DBI needs to change that much. Certainly the Sybase driver won't be able to support many of the proposed functionality, or won't benefit from the changes (i.e. no speed gain, no improved flexibility, etc). Michael -- Michael Peppler - [EMAIL PROTECTED] - http://www.peppler.org/ Sybase DBA/Developer Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html
Re: DBI v2 - The Plan and How You Can Help
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: I agree. 1. For DBI drivers which support them, your subclass must support - 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% must include - DBD::ODBC - DBD::Oracle - DBD::Pg - DBD::MySQL - DBD::CSV - 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 selected DBD's. Yes. 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 developers. I accept your challenge. -- Darren Duncan
Re: DBI v2 - The Plan and How You Can Help
Darren Duncan wrote: 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. Indeed. I think the principle bug to fix is getting away from this notion that all you need to do is do a little bit of template-based query building, use the DBI and magically expect all database portability problems to go away. And then, recommend an approach that *is* portable. Take your excellent Rosetta infrastructure, pull the API to pieces, simplify the documentation, then condone it as another simple and effective way to write new database driven applications. And hopefully simplify the DBDs that necessarily need to do SQL parsing along the way. So, everyone who is still happy to code to a particular database's SQL language can continue to do so, but we'll eventually move the Cargo Cult away from the situation we're in today where there is a vague promise of portability but so many caveats that it's practically impossible to write portable code. Sam.
Re: DBI v2 - The Plan and How You Can Help
Oh drat - not the DBI connection string discussion again! On 7/4/05, Darren Duncan [EMAIL PROTECTED] wrote: 5. All details used to construct a connection handle should be completely decomposed rather than shoved into an ungainly data source. Examples of what should be distinct (not all being applicable at once) are: 1. the DBI driver module to use; 2. the internet server IP address or domain name and port; 3. the locally defined server device socket; 4. the locally defined service (eg, ODBC or SQL*Net) name; 5. the file system file name; 6. the file system directory name; 7. some other detail if any for fully in-RAM databases; 8. the authorization identifier / user name; 9. the password; 10. some other authorization credential, or channel encryption details, or whatever else; 11. what kind of database or what database product is being used, if known. If the DBI driver talks to a client-configurable DBI proxy server, then, it should be possible to nest a set of the above settings (eg, as a hash-ref) as one part of the main settings given to the proxy client. Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases - and you've missed out the other bit that applies - the database name, and optionally database server name hosting it (either 'dbase' or '[EMAIL PROTECTED]'). These are not the same as 2, 3, 4, 5, 6, 7, 10, or 11, and I can't think how any of them (except perhaps 10) could be applied to an IDS connection. Oh, and if you're going to enhance the connection, please ensure you cover challenge-response protocols (where you send an initial username/password and the authentication server comes back with a question such as what is the number on your RSA key fob at the moment, and the hapless user has to type that information in, and the connection management code has to deal with this - callbacks and the like. So, as was discussed emphatically and exhaustively (in January 2005 in dbi-dev under 'Proposing an API for the extension for simplifying database connections'), this is not readily going to fly with the existing DBMS - specifically, not with IDS. Closed-source DBMS are *not* necessarily going to adapt to meet the needs of Perl and DBI. You can argue that's their loss - you may even be right. But you'll be limiting the acceptability of Perl + DBI in some respects. You'll also be annoying the hell out of me if you can't define a connection string that will work with Informix (I'm not too worried about the challenge-response stuff, though Informix can handle that). Oh - and DBI v2 should have support for scrollable cursors. -- Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org I don't suffer from insanity - I enjoy every minute of it.
Re: DBI v2 - The Plan and How You Can Help
In particular, the DBI must not mandate impossible levels of support from the drivers. It will benefit you nothing if the DBI is immaculate and wonderful and incredibly all-singing and all-dancing, but no-one can write a driver for it because the requirements cannot be met by the actual DBMS that Perl + DBI needs to work with. I concur. Like CPAN as a whole, DBI's strength is in it's complete and near universal coverage of all databases, and insanely great (and occasisionally greatly insane) drivers that do strange and wonderful things. If we start sacrificing drivers by raising the bar too high, DBI as a whole suffers. Anyone proposing new features for DBI needs to be extremely careful of CYJ syndrome. Can't You Just (or sometimes Could You Just) syndrome is described here. http://c2.com/cgi/wiki?CouldYouJust http://www.oreillynet.com/pub/wlg/3593 http://c2.com/cgi/wiki?JustIsaDangerousWord Go read them now. I'll wait... This sort of behaviour can play a big part in ending up with second system problems. I have an increasing suspicion that having open design processes like the Tim's call for comments plays a big part in it as well. People are free to comment on things that 1) They won't have to implement themselves and (in some cases, but not you Duncan) 2) They think they know what they are talking about, but really have no idea what it means underneath the surface. In any case, I still propose that DBI2 split the driver interface into Roles. The main DBI2::Role::Transport role does ONLY what DBI does best now. That is, connecting to the database, preparing and sending queries, and fetching the results. Forget the current -tables interface. Drivers can OPTIONALLY implement the DBI2::Role::Schema interface to handle interrogation of the database schema. The current way we handle it is really messy by my standards, and could use a dedicated interface. As you invent major new features for DBI2, implement them roles. DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy Beverly Hills feature), etc etc. Exactly what these roles should be I don't want to state with any certainty. That's the sort of thing that Tim, with his complete understanding of the issues, should be doing on his own. But I _would_ certainly like to see schema/table stuff separated from the base connection/query functionality. While I'm on the topic of DBI, one other feature I'd like to see would be something like better support for large objects or various types. In my personal DBI wrapper I've been using plain SCALAR refs as parameters to signify a BLOB, so I can pass by reference without a copy of a potentially large memory chunk, and then having to do driver-specific translation to bind variables (Oracle) or what have you. Some base way of default method defining a BLOB object (whether provided in memory, or pointing at a file handle to pull the data from at commit time) in would be really nice. Even if the way I have to pass the blobs to each driver differs, I'd like to be at least be able to say, This is a DBI2::Data::BLOB object (or something functionally equivalent). Adam K
Re: DBI v2 - The Plan and How You Can Help
On Sat, 2005-07-09 at 01:22 -0700, Jonathan Leffler wrote: Oh drat - not the DBI connection string discussion again! On 7/4/05, Darren Duncan [EMAIL PROTECTED] wrote: 5. All details used to construct a connection handle should be completely decomposed rather than shoved into an ungainly data source. Examples of what should be distinct (not all being applicable at once) are: 1. the DBI driver module to use; 2. the internet server IP address or domain name and port; 3. the locally defined server device socket; 4. the locally defined service (eg, ODBC or SQL*Net) name; 5. the file system file name; 6. the file system directory name; 7. some other detail if any for fully in-RAM databases; 8. the authorization identifier / user name; 9. the password; 10. some other authorization credential, or channel encryption details, or whatever else; 11. what kind of database or what database product is being used, if known. If the DBI driver talks to a client-configurable DBI proxy server, then, it should be possible to nest a set of the above settings (eg, as a hash-ref) as one part of the main settings given to the proxy client. Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases - and you've missed out the other bit that applies - the database name, and optionally database server name hosting it (either 'dbase' or '[EMAIL PROTECTED]'). These are not the same as 2, 3, 4, 5, 6, 7, 10, or 11, and I can't think how any of them (except perhaps 10) could be applied to an IDS connection. 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:[EMAIL PROTECTED]:port/instance Oh, and if you're going to enhance the connection, please ensure you cover challenge-response protocols (where you send an initial username/password and the authentication server comes back with a question such as what is the number on your RSA key fob at the moment, and the hapless user has to type that information in, and the connection management code has to deal with this - callbacks and the like. Seconded, with request for support of SSL client certificate authentication. -jwb
Re: DBI v2 - The Plan and How You Can Help
On 7/9/05, Darren Duncan [EMAIL PROTECTED] wrote:
At 1:22 AM -0700 7/9/05, Jonathan Leffler wrote:
On 7/4/05, Darren Duncan [EMAIL PROTECTED] wrote:
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly data
source. Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.
Only 1, 8 and 9 (driver name, user ID, password) apply to Informix
databases -
Re-read that paragraph. It says 'not all being applicable at once'.
Yes - I realized that.
and you've missed out the other bit that applies - the database
name, and optionally database server name hosting it (either 'dbase'
or '[EMAIL PROTECTED]'). These are not the same as 2, 3, 4, 5, 6, 7, 10,
or 11, and I can't think how any of them (except perhaps 10) could
be applied to an IDS connection.
I think that 2 thru 4 cover this, though I used more generic language
I don't. The server name in IDS is not a host name or domain name, nor is it
a port number (so 2 is not applicable), nor is it the locally named socket
(so 3 is not applicable), nor is it the locally defined service (so 4 is not
applicable).
All of these are identified by, but are distinct from, the IDS server name.
There is a mechanism (configuration file) that hides all the gory details
from the user. Users should not need to know sordid details like port
numbers, or whether the host is on an IPv4 or IPv6 network, etc.
Further, within an IDS instance, there are multiple databases that can be
separately connected to - '[EMAIL PROTECTED]', '[EMAIL PROTECTED]', '
[EMAIL PROTECTED]', '[EMAIL PROTECTED]'. You must be able to specify the
database
within the server instance.
Now, in fact, server1 and server2 could be alternative names for the same
hunk of disk space, supervised by the same IDS instance but with different
connection properties - such as encrypted vs unencrypted - but that is
probably just too confusing. Equally, and more normally, server1 could be on
a wholly different machine from server2.
[Concrete example: I have many IDS instances running on my machine at work.
One instance has 4 names:
anubis_17, anubis_17_tcp, anubis_17_str, anubis_17_shm. The first two are
both network connections - albeit usually using loopback since I usually
work on the same machine. The third uses a STREAMS pipe; the fourth uses
shared memory. All allow me to connect to the same set of databases, which
includes 'sysmaster', 'sysutils', 'stores', 'logged', 'unlogged',
'mode_ansi'. Each of those databases has its own independent set of system
catalogs. I can connect to '[EMAIL PROTECTED]' or '[EMAIL PROTECTED]' and
edit the same data - it's the same database, identified by different server
names and different connection properties. I also have another IDS instance,
running an older version of IDS, with server names anubis_23, anubis_23_tcp,
anubis_23_str, anubis_23_shm - and the same set of databases, but only
because some of them are standard and the others I keep there to make
testing DBD::Informix easier. I also have an entry configured for a database
server called 'smartpts' that is based in Lenexa, KS - I work in Menlo Park,
CA - about 1800 miles away. I can run $db1 = DBI-connect('
dbi:Informix:[EMAIL PROTECTED]'); $db2 = DBI-connect('
dbi:Informix:[EMAIL PROTECTED]'); to connect to those databases. I can also, in
general, do distributed queries between the two databases without explicitly
connecting to the other. And note that I can switch between TCP and SHM
connections on the local machine (which is
anubis.menlo.ibm.comhttp://anubis.menlo.ibm.com)
simply by changing the server name - I don't have to do any other
modifications to the connection string.]
Please note that for Informix, the database name (optionally with server
name) is all you need to specify. (There's an environment variable that
specifies the default server name if you omit it.) Specifying a 'host name'
won't work; there is no mechanism for connecting by specifying a host name -
except as part of the configuration file. Specifying a port number (or
service name) won't work; there is no mechanism for connecting by specifying
a port
Re: DBI v2 - The Plan and How You Can Help
Jonathan Leffler wrote: I dunno which DBMS support prepare without a database connection, but I would expect all the mainstream databases to require a database connection. +1 I'm also far from convinced that there's any significant benefit in separating the 'create a database handle' from the 'connect to database server' part. +1 Not to mention the effect, that one major charm of DBI is its simplicity: Connect, Execute for updates, inserts, or deletes and Connect, Execute, Fetch for select. I can't see an advantage in overly extending the interface. Jochen
Re: DBI v2 - The Plan and How You Can Help
Jeffrey W. Baker skribis 2005-07-09 11:27 (-0700): 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:[EMAIL PROTECTED]: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
Re: DBI v2 - The Plan and How You Can Help
At 10:25 PM +1000 7/9/05, Adam Kennedy wrote: In any case, I still propose that DBI2 split the driver interface into Roles. The main DBI2::Role::Transport role does ONLY what DBI does best now. That is, connecting to the database, preparing and sending queries, and fetching the results. Forget the current -tables interface. Drivers can OPTIONALLY implement the DBI2::Role::Schema interface to handle interrogation of the database schema. The current way we handle it is really messy by my standards, and could use a dedicated interface. As you invent major new features for DBI2, implement them roles. DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy Beverly Hills feature), etc etc. Exactly what these roles should be I don't want to state with any certainty. That's the sort of thing that Tim, with his complete understanding of the issues, should be doing on his own. That sounds like a great idea and a good starting place on which to build certain design issues. In fact, I have already been implementing something similar to that in my 'Rosetta' database access library for over a year now. It has a concept of feature support lists where each Rosetta Engine/driver must take a standard fine-ish-grained checklist and programmatically declare which list items it officially supports. An application can see what an Engine/driver claims to support before trying to use it, and will know whether or not the Engine/driver can meet its needs or not. An Engine's/driver's declaration can be read by invoking the features() method of some Rosetta Interface objects. An additional use for this feature is that a comprehensive common test suite for all Engines/drivers can consult features() prior to running its tests so that it can skip any tests that an Engine/driver doesn't claim to support; it will only invoke and pass/fail features that the Engine/driver claims to support. See the Rosetta::Details documentation section 'FEATURE SUPPORT VALIDATION' for the main summary of feature support lists. But I _would_ certainly like to see schema/table stuff separated from the base connection/query functionality. I agree. When you get down to it, schema/table/etc reverse engineering is a very complicated and involved process. Fetching lists of tables or columns etc should no more be built in than SQL parsing or generating. In short, anything that can normally be fetched or changed using ordinary SQL statements should be left out of the DBI core; let wrappers do that stuff through the SQL pipe that DBI provides. This said, it is still very useful for DBI to provide a function for fetching a list of auto-detectable data sources, so that should stay. On a similar note, utility functions like quote() should be left out of the DBI core, and left to either a separate module or someone's wrapper, since it's firmly related to SQL generation. If people want DBI itself to handle stuff like that for them, they should use host parameters for the literals in question. While I'm on the topic of DBI, one other feature I'd like to see would be something like better support for large objects or various types. I second that. Have API methods for fetching or storing by-the-chunk pieces of LOBs that one could not do with a traditional bind_param() etc since the whole thing won't fit in RAM at once. -- Darren Duncan
Re: DBI v2 - The Plan and How You Can Help
At 12:35 AM -0700 7/9/05, Jonathan Leffler wrote: I dunno which DBMS support prepare without a database connection, but I would expect all the mainstream databases to require a database connection. IBM DB2 does; IBM Informix Dynamic Server (IDS) does; someone else commented on this and said Oracle does; I would expect both Sybase and MS SQL Server to need the DB connection too. Probably PostgreSQL; not sure about MySQL. The only systems that might not need the DB connection are those that aren't using a separate SQL-based database server. See my subsequent email/s where I left prepare() requiring an open database connection, and a separate function (for example, 'compile'), is split out and handles any prep work that can be done without an open connection. Since you mention that some systems don't have the separate SQL-based database server, there are indeed some drivers that can take advantage of a separate 'compile' then, so they benefit from a split; the worst that happens with the others is a no-op. Some drivers gain, and no one loses anything. I'm also far from convinced that there's any significant benefit in separating the 'create a database handle' from the 'connect to database server' part. The overhead of creating the handle - as distinct from make the connection to the DBMS - is negligible. There's nothing of any significance that can be done with the unconnected handle either - at least, for the mainstream SQL DBMS. So, the pre-fork creation of an unconnected handle provides negligible savings. Similar comments apply to the statement handles - in IDS, at any rate, there's nothing useful that can be done for statements until you've got a database handle. Or, at the least, you'd be moving away from the ODBC paradigm, and the ESQL/C paradgm too. A main benefit of separating out the handle creation and database connection is the secondary effect whereby you can pre-associate statement handles with it that are expensive to create, such as because they generate SQL. Generating SQL and other such things is separate from the database and the speed advantage from separating this out as I described is not lost by the fact of what database servers don't support, since the databases are never given that work. A primary effect benefit is any time you want to open the same connection more than once; you can configure it once when making the handle, and not again no matter how many times you open/close/open the connection, or re-open a connection that dies. Moreover, every open from the first to subsequent ones are done in the same way. Certainly, one can make a DBI wrapper that adds some of those advantages, but given their simplicity and universal desirability, its better to build them in; any addition of complexity over the current DBI is negligible, or it may in fact be less complex than the current DBI. As a general comment on DBI v2; we need to beware of the second-system effect (Brooks Mythical Man Month). In particular, the DBI must not mandate impossible levels of support from the drivers. It will benefit you nothing if the DBI is immaculate and wonderful and incredibly all-singing and all-dancing, but no-one can write a driver for it because the requirements cannot be met by the actual DBMS that Perl + DBI needs to work with. What you say is fair enough, but I never proposed anything impossible or difficult; everything that I am proposing here is easy and simple. -- Darren Duncan
Re: DBI v2 - The Plan and How You Can Help
Still late to the party - another one bullet point item... On 7/4/05, Darren Duncan [EMAIL PROTECTED] wrote: 4. All host parameters should be named (like :foo) rather than positional (like ?), meeting with the SQL:2003 standard. The named format is a lot easier to use and flexible, making programmers a lot less error prone, more powerful, and particularly more resource efficient when the same parameter is conceptually used multiple times in a SQL statement (it only has to be bound once). If anyone wants to use positional format, it could easily be emulated on top of this. Or, if native positional support is still important, then it should be a parallel option that can be used at the same time as named in any particular SQL statement. See the native API of SQLite 3 for one example that (I believe) supports both in parallel. This also means that execute() et al should take arguments in a hash rather than an array. Can you explain which parts of the SQL:2003 mandate this notation? I've had a moderately good poke around my copy of ISO/IEC 9075-2:2003 (SQL/Foundation) and cannot find this. I'd like a few section numbers listed which describe this. The various places I've looked include: 19.6 (prepare statement), 9 (Additional Common Rules), 6.4 (value specification and target specification). I could have missed something in these places - or I could be looking in the wrong place. The IDS (IBM Informix Dynamic SQL) syntax has a number of places where :digits can appear with a meaning other than placeholder, and there are also a number of places where :identifier can appear with a meaning other than placeholder. So, it would be extremely difficult to add :identifier notation into IDS. (One of the bits I had to remove from DBD::Informix was code from DBD::Oracle that simulated :identifier notation - because it breaks too much else.) This ties in with my previous comment, too; don't try to demand too much of the drivers, or the driver writers. As long as you've got a good surrogate system in DBI that can simulate those accurately for DBMS that only support '?' (positional) placeholders, then DBI v2 can do what the heck it likes. But as soon as it is inaccurate - translates things that should not be translated - or cannot do the translation automatically, then you will lose drivers (or, more accurately, driver writers). I'm all in favour of looking at the big picture and trying to see where you want to go. However, you must also keep an eye out for the marshes between where you are and where you want to go; don't let DBI be sunk by ignoring the realities of the available DBMS. -- Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org I don't suffer from insanity - I enjoy every minute of it.
Re: DBI v2 - The Plan and How You Can Help
At 1:03 AM -0700 7/9/05, Jonathan Leffler wrote: Can you explain which parts of the SQL:2003 mandate this notation? I've had a moderately good poke around my copy of ISO/IEC 9075-2:2003 (SQL/Foundation) and cannot find this. I'd like a few section numbers listed which describe this. The various places I've looked include: 19.6 (prepare statement), 9 (Additional Common Rules), 6.4 (value specification and target specification). I could have missed something in these places - or I could be looking in the wrong place. Yes, I can quote some, in different places. Look at these: SQL:2003, 4.29 Host parameters (pp90,91,92) SQL:2003, 5.4 Names and identifiers (pp151,152) SQL:2003 Foundation page 152 says: host parameter name ::= colonidentifier See that last line in particular. I don't see how it could be more clear. This is my main source for the SQL:2003 documentation: http://www.wiscorp.com/SQLStandards.html Or specifically: http://www.wiscorp.com/sql/sql_2003_standard.zip (warning, large file) That page is run by someone leading / high up in the SQL standards group. Note that those urls are printed in my SQL::Routine::Language POD file on CPAN, and the lines with specific pages and section names are in source/reference comments/documentation for the build_identifier_host_parameter_name() function in my SQL::Routine::SQLBuilder module. I do tend to give sources to back up anything important I do, which is good for third party validation. The IDS (IBM Informix Dynamic SQL) syntax has a number of places where :digits can appear with a meaning other than placeholder, and there are also a number of places where :identifier can appear with a meaning other than placeholder. So, it would be extremely difficult to add :identifier notation into IDS. (One of the bits I had to remove from DBD::Informix was code from DBD::Oracle that simulated :identifier notation - because it breaks too much else.) A variety of databases, such as Oracle, already have support for just the format I described, which I believe was also in SQL:1999. In fact, I think that Oracle's own extensions of earlier SQL standards had a lot of influence on later SQL standards such as this, though I can think of multiple differences too. This ties in with my previous comment, too; don't try to demand too much of the drivers, or the driver writers. As long as you've got a good surrogate system in DBI that can simulate those accurately for DBMS that only support '?' (positional) placeholders, then DBI v2 can do what the heck it likes. But as soon as it is inaccurate - translates things that should not be translated - or cannot do the translation automatically, then you will lose drivers (or, more accurately, driver writers). I don't see how :foo is significantly more difficult than ? to simulate accurately. I'm all in favour of looking at the big picture and trying to see where you want to go. However, you must also keep an eye out for the marshes between where you are and where you want to go; don't let DBI be sunk by ignoring the realities of the available DBMS. At the same time, don't let today's technology limit what you do in preparation for tomorrow. DBI v2 is meant to be a forward-looking plan, as Perl 6 is, so we have to consider things that are reasonable for inclusion; even if it isn't common for native database support now, that isn't to say that native support won't come later, and when it does, we'll already be ready, or they'll even take what DBI does as a cue for what they can add. -- Darren Duncan
Re: DBI v2 - The Plan and How You Can Help
At 1:22 AM -0700 7/9/05, Jonathan Leffler wrote: On 7/4/05, Darren Duncan mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: 5. All details used to construct a connection handle should be completely decomposed rather than shoved into an ungainly data source. Examples of what should be distinct (not all being applicable at once) are: 1. the DBI driver module to use; 2. the internet server IP address or domain name and port; 3. the locally defined server device socket; 4. the locally defined service (eg, ODBC or SQL*Net) name; 5. the file system file name; 6. the file system directory name; 7. some other detail if any for fully in-RAM databases; 8. the authorization identifier / user name; 9. the password; 10. some other authorization credential, or channel encryption details, or whatever else; 11. what kind of database or what database product is being used, if known. If the DBI driver talks to a client-configurable DBI proxy server, then, it should be possible to nest a set of the above settings (eg, as a hash-ref) as one part of the main settings given to the proxy client. Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases - Re-read that paragraph. It says 'not all being applicable at once'. and you've missed out the other bit that applies - the database name, and optionally database server name hosting it (either 'dbase' or '[EMAIL PROTECTED]'). These are not the same as 2, 3, 4, 5, 6, 7, 10, or 11, and I can't think how any of them (except perhaps 10) could be applied to an IDS connection. I think that 2 thru 4 cover this, though I used more generic language. Oh, and if you're going to enhance the connection, please ensure you cover challenge-response protocols (where you send an initial username/password and the authentication server comes back with a question such as what is the number on your RSA key fob at the moment, and the hapless user has to type that information in, and the connection management code has to deal with this - callbacks and the like. I mentioned those generically in 8 thru 10; the latter is some other authorization credential etc. So, as was discussed emphatically and exhaustively (in January 2005 in dbi-dev under 'Proposing an API for the extension for simplifying database connections'), this is not readily going to fly with the existing DBMS - specifically, not with IDS. Closed-source DBMS are *not* necessarily going to adapt to meet the needs of Perl and DBI. You can argue that's their loss - you may even be right. But you'll be limiting the acceptability of Perl + DBI in some respects. You'll also be annoying the hell out of me if you can't define a connection string that will work with Informix (I'm not too worried about the challenge-response stuff, though Informix can handle that). If a database only accepts input in string form, then the DBI driver can generate one out of the decomposed information it is given. No trouble here, really; no trouble at all. The whole point of a DBI driver is to map between the DBI interface and how the database product natively does things. It is true that some things can only be done by changing the database product itself, but other things can be done in the DBI driver, with no support from closed-source DBMS necessary. -- Darren Duncan
Re: DBI v2 - The Plan and How You Can Help
Jared Still wrote: I use a (Perl) password server for this. Passwords are stored encrypted in a configuration file. Clients authenticate with the server, and receive a requested password (encrypted) across the network, if the client is entitled. The user authentication is rudimentary, but it works. SSH certificates would be better, just need to do it. This is more of the direction I'm going; i.e., I don't want a DBI-specific password file as a requirement since I'm looking at a central way of storing a variety of passwords, DBI and non-DBI. I think the file idea is fine as an environment- or configuration-driven _option_. -- Steve Sapovits [EMAIL PROTECTED]
Re: DBI v2 - The Plan and How You Can Help
Late to the ball - and only picking up on one issue... On 7/4/05, Darren Duncan [EMAIL PROTECTED] wrote: 2. Always separate out any usage stages that can be performed apart from the database itself. This allows an application to do those stages more efficiently, consuming fewer resources of both itself and the database. For example, a pre-forked Apache process can declare all of the database and statement handles that it plans to use, and do as much of the prepare()-type work that can be done internally as possible, prior to forking; all of that work can be done just once, saving CPU, and only one instance of it consumes RAM. All actual invocations of a database, the open()/connect() and execute() happen after forking, and at that point all of the database-involving work is consolidated. Or even when you have a single process, most of the work you have to do, including any SQL generation et al, can be more easily be pre-performed and the results cached for multiple later uses. Some DBI wrappers may do a lot of work with SQL generation et al and be slow, but if this work is mainly preparatory, they can still be used in a high-speed environment as that work tends to only need doing once. Most of the prep work of a DBI wrapper can be done effectively prior to ever opening the database connection. I dunno which DBMS support prepare without a database connection, but I would expect all the mainstream databases to require a database connection. IBM DB2 does; IBM Informix Dynamic Server (IDS) does; someone else commented on this and said Oracle does; I would expect both Sybase and MS SQL Server to need the DB connection too. Probably PostgreSQL; not sure about MySQL. The only systems that might not need the DB connection are those that aren't using a separate SQL-based database server. I'm also far from convinced that there's any significant benefit in separating the 'create a database handle' from the 'connect to database server' part. The overhead of creating the handle - as distinct from make the connection to the DBMS - is negligible. There's nothing of any significance that can be done with the unconnected handle either - at least, for the mainstream SQL DBMS. So, the pre-fork creation of an unconnected handle provides negligible savings. Similar comments apply to the statement handles - in IDS, at any rate, there's nothing useful that can be done for statements until you've got a database handle. Or, at the least, you'd be moving away from the ODBC paradigm, and the ESQL/C paradgm too. As a general comment on DBI v2; we need to beware of the second-system effect (Brooks Mythical Man Month). In particular, the DBI must not mandate impossible levels of support from the drivers. It will benefit you nothing if the DBI is immaculate and wonderful and incredibly all-singing and all-dancing, but no-one can write a driver for it because the requirements cannot be met by the actual DBMS that Perl + DBI needs to work with. -- Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org I don't suffer from insanity - I enjoy every minute of it.
Re: DBI v2 - The Plan and How You Can Help
Jonathan, while you are well-meaning in your comments, you are mis-reading what I have said multiple times and are therefore making a straw man argument against it. Regarding this point: 5. All details used to construct a connection handle should be completely decomposed rather than shoved into an ungainly data source. Examples of what should be distinct (not all being applicable at once) are: snip That's the key part of my proposal right there. I don't care about exactly what break-down of pieces we collectively come up with, as long as they are unambiguous, reasonably atomic, account for all the possible needs, and have reasonable names. That 11-point list of mine was only meant to be an example of what breaking up a data source string can give us; it was not meant to be exhaustive and there is no point in arguing at length against what it missed and how horrible it is. No doubt, an IDS and/or components thereof should also be in the list of connection parameters. I'm not arguing against what one needs for Informix, either explicitly or implicitly. All this said, thank you for all the information about Informix how you set up your systems, and how many levels of hierarchy there can be with a database; eg, a server has 1+ databases, each of which has 1+ catalogs (and presumably each of those has 1+ schemas and each of those 1+ tables and other schema objects). I will probably find it helpful when I plan various DBI-using tasks. -- Darren Duncan
Re: DBI v2 - The Plan and How You Can Help
Sam Vilain wrote: Maxim Sloyko wrote: But this is not the point. The point was that usage of some file with passwords by *DEFAULT* is not the way to go, IMHO. It raises more problems than it solves. Can you give an example of such a problem that wasn't already there? Just to be clear, the file would only need to contain passwords if the DBD requires them. Sam. May be it is just me, but having a bunch of config files is not very good. Config files should be for program, not for separate modules of that program. They are hard to manage, when there are many of them. Besides, DBI is a high level abstraction and it is not a good idea to tie it to some file. May be it is better to let DBD:: modules choose? For example, you can pass some parameter to the driver in connect string, which tells it, where passwords are stored. Something like this is impleneted in DBD::Oracle, where you can just pass 'sid' parameter to the driver, the rest configuration parameters (except passwords and usernames) are read from oracle config file, from the section to which that sid parameter points. I don't mind if you implement this .dbi feature though, I just want it to be invisible :) i.e. don't check this file, if I explicitly supply username and password (this is obvious, right?) and show some warnings if don't. Say, make a connect parameter use_dot_dbi, which is zero by default. -- Maxim Sloyko
RE: DBI v2 - The Plan and How You Can Help
When I go to the donation page and attempt to make a donation, the drop-down box does not give DBI as a valid recipient. Is it possible several people may not have donated as they noticed the same results, or maybe they did and it all went into the Perl Development Fund instead? -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Friday, July 01, 2005 7:06 PM To: perl6-language@perl.org; dbi-users@perl.org Subject: DBI v2 - The Plan and How You Can Help Once upon a time I said: http://groups-beta.google.com/group/perl.dbi.users/msg/caf189d 7b404a003?dmode=sourcehl=en and wrote http://search.cpan.org/~timb/DBI/Roadmap.pod which yielded: https://donate.perlfoundation.org/index.pl?node=Fund+Drive+Det ailsselfund=102 (A little over $500 of that I effectively put in myself.) My *sincere* thanks to all those who donated to the fund, especially individuals. I had hoped for more corporate response with less from individuals and I'm touched by the personal generosity shown. I've not drawn any money from it yet and doubt that I will myself. (I'm considering suggesting that the Perl Foundation make payments from the fund to people making specific contributions to the DBI. I'm thinking especially of work on a comprehensive test harness. But I'll see how the developments below pan out before making specific arrangements.) So, that lead to: http://groups-beta.google.com/group/perl.dbi.dev/browse_frm/th read/ef14a9fc0a37441f/fb8fe20a86723da0#fb8fe20a86723da0 Which sums up fairly well where I'm at: DBI v1 will rumble on for Perl 5 and DBI v2 will be implemented for Perl 6. --- digression --- At this point I'd like to make a slight digression to highlight the amazing work going on in the Perl 6 community at the moment. Especially Autrijus' Pugs project which has brought Perl 6 to life. Literally. Take a look at: http://pugscode.org/talks/yapc/slide1.html http://use.perl.org/~autrijus/journal and especially: http://use.perl.org/~autrijus/journal/24898 Yes, that really is Perl 6 code using the DBI being executed by Pugs. That's great, and I was truly delighted to see it because it takes the pressure off the need to get a DBI working for Perl 6 - because it already is working for Perl 6. At least for Pugs. (The Ponie project is also likely to provide access to Perl 5 DBI from Perl 6 by enabling future versions of Perl 5 to run on Parrot.) --- digression --- I have recently come to an arrangement that will enable me to put some worthwhile development time into DBI (still very much part-time, but enough to give it focus and move forward). My initial goals are: 1. to work on a more detailed specification for the DBI v2 API that takes advantage of appropriate features of Perl 6. 2. to work on a more detailed specification for the DBDI API http://groups-beta.google.com/group/perl.perl6.internals/msg/c fcbd9ca7ee6ab4 3. to work on tools to automate building Parrot NCI interfaces to libraries (such as database client libraries, obviously :) But I'm hoping you'll join in and help out. I've kept an eye on Perl 6 and Parrot developments but I'm no expert in either. What I'd like *you* to do is make proposals (ideally fairly detailed proposals, but vague ideas are okay) for what a Perl 6 DBI API should look like. Keep in mind that the role of the DBI is to provide a consistent interface to databases at a fairly low level. To provide a *foundation* upon which higher level interfaces (such as Class::DBI, Tangram, Alzabo etc. in Perl 5) can be built. So, if you have an interest in the DBI and Perl 6, put your thinking cap on, kick back and dream a little dream of how the DBI could be. How to make best use of the new features in Perl 6 to make life easier. Then jot down the details and email them to me (or to dbi-users@perl.org if you want to kick them around in public for a while first). I'm about to fly off for two weeks vacation (in a few hours), blissfully absent of any hi-tech gear beyond a mobile phone. When I get back I'll gather up your emails and try to distill them into a coherent whole. Have fun! Tim.
Re: DBI v2 - The Plan and How You Can Help
Maxim Sloyko wrote: I don't think this solves the problem, because what I usually want is the user to be able to use the application, but unable to see the DB password. So the user should have read permission set for the file, but on the other hand he shouldn't. It's not not a problem for Web App, though. Storing passwords encrypted, decrypting before using doesn't cover this? I've played around with Crypt::CBC (and different ciphers) for this sort of thing but admittedly have not applied this to any production systems yet. You still have a key somewhere to hide/obscure. You can also use Perl source filters to totally encrypt the source -- something else I've done but not in production. Just some things you may want to look at ... -- Steve Sapovits [EMAIL PROTECTED]
Re: DBI v2 - The Plan and How You Can Help
Maxim Sloyko wrote: But this is not the point. The point was that usage of some file with passwords by *DEFAULT* is not the way to go, IMHO. It raises more problems than it solves. Can you give an example of such a problem that wasn't already there? Just to be clear, the file would only need to contain passwords if the DBD requires them. Sam.
Re: DBI v2 - The Plan and How You Can Help
Darren Duncan wrote:
Okay, considering that using the same name prepare() like this may
confuse some people, here is a refined solution that uses 3 methods
instead; please disregard any contrary statements that I previously made:
I think I'm beginning to like it.
Allow me to suggest one or two further refinements...
# Opt 1: A user that wants the most control can do this (new feature):
my $sth1 = $dbh.compile( $sql_or_ast ); # always sans connection
$sth1.prepare(); # always with connection, even if DBD doesn't use it
$sth1.execute(); # always with connection
To me, the compiled form of the STH is related to the driver, but
re-usable between connections; you should be able to use something like;
my $sth1 = DBD::SQLite.compile( $sql_or_ast );
$sth1 = DBI.compile( :statement($sql_or_ast), :driverSQLite );
This would give you a STH which is divorced from the actual DB connection
instance. Because you constructed it like this, without reference to a
(possibly unconnected) connection object, then $sth1.prepare is not
available.
You'd then need to use something like;
$sth1.prepare($dbh);
$dbh.prepare($sth1);
Note I also think what you wrote should work, too.
The new feature is if you decide to use compile(); you then give that
method the arguments you would have given to prepare(), and you invoke
prepare() on the result with no arguments; each DBD would decide for
itself how the work is divided between compile() and prepare() with the
limitation that compile() is not allowed to access the database; ideally
the DBD would place as much work there as is possible, which would vary
between Oracle/Pg/etc.
Agreed.
In particular, I don't think that the DB driver should automatically
get a chance to interfere with SQL::Statement; if they want to do that,
then they should specialise SQL::Statement. IMHO.
I am operating under the assumption here that while the new DBI is
designed to effectively support wrapper modules, the wrapper modules
would also be altered from their current DBI-1-geared designs to
accomodate DBI-2.
But still, what do you mean by interfere?
Well, when you parse the statement into an AST, the flavour of SQL will
affect how it is parsed and what is allowed. Eg, Oracle has significant
features in some comments (query hints). It also has quirky and somewhat
useless keywords like CONNECT BY.
So, when you ask a DBH connected to a driver to parse something, then it
will use that driver's SQL dialect, if one exists, but I still want to be
able to deal with SQL ASTs without implying a SQL flavour.
Either way, you don't want most applications dealing with this complexity
at all, really.
I am operating under the assumption that this system should work if
there are no external config files that the DBI/DBD would read, and the
application would provide that information; if its in a file, the
application would read it in, or would explicitly tell DBI where it is.
Or at least it should be possible for this to happen, even if a DBD
defaults to look in a default location when it doesn't get the
equivalent from the application.
Absolutely, that must work. But it would still be nice to be able to
config this without digging through the application to see where the
password is written.
Unless there is a design flaw in DBI, we should not have to update that
module just because a new driver came into existence whose name has not
yet been hard-coded into DBI.
See this block for example, from DBI.pm v1.48:
my $dbd_prefix_registry = {
ad_ = { class = 'DBD::AnyData',},
[...]
yaswi_ = { class = 'DBD::Yaswi',},
};
I mean, what's up with that? I assume DBI 1 has this for legacy app
backwards compatability, but DBI version 2 should never have to
accomodate such abhorrent computer programming practices in its core.
Such a great word, abhorrent. So fitting for this case. It sure does
look like an (overprematuremisguided)-optimisation to avoid using the
full module name in an internal hash or something like that. But then
maybe (Iwenone(Gaia)) are missing some context there.
Sam.
Re: DBI v2 - The Plan and How You Can Help
4. All host parameters should be named (like :foo) rather than positional (like ?), meeting with the SQL:2003 standard. The named format is a lot easier to use and flexible, making programmers a lot less error prone, more powerful, and particularly more resource efficient when the same parameter is conceptually used multiple times in a SQL statement (it only has to be bound once). If anyone wants to use positional format, it could easily be emulated on top of this. Or, if native positional support is still important, then it should be a parallel option that can be used at the same time as named in any particular SQL statement. See the native API of SQLite 3 for one example that (I believe) supports both in parallel. This also means that execute() et al should take arguments in a hash rather than an array. Yes, native positional support is still important. positions make it very easy to do SQL math. To express it in overly simplistic code $foo = [ a = ?, foo ]; $bar = [ b = ?, bar ]; $baz = $foo and $bar; # $baz now is [ a = ? and b = ?, foo, bar ]; Bearing mind a situation with an arbitrary number and complexity of these sql fragments to be added together, doing this sort of thing using named placeholders would be a nightmare. and we'd just end up with every second person implementing a ? to :p1, :p2 scheme in their database layers. If you want positional placeholders, it needs to be an additional feature, not a replacement for positional placeholders.
Re: DBI v2 - The Plan and How You Can Help
At 6:14 PM +1200 7/5/05, Sam Vilain wrote: I think I'm beginning to like it. Allow me to suggest one or two further refinements... my $sth1 = $dbh.compile( $sql_or_ast ); # always sans connection $sth1.prepare(); # always with connection, even if DBD doesn't use it $sth1.execute(); # always with connection FYI, I'm not stuck on the name 'compile'; the method could be called something else. In fact, I still think 'prepare' is more broadly descriptive considering the wide range of things that could be happening inside that method; I see it as ultimately getting ready for the execute() anyway. The problem is that the word 'prepare' is commonly associated with something different, so we're stuck having to find a different word. To me, the compiled form of the STH is related to the driver, but re-usable between connections; you should be able to use something like; my $sth1 = DBD::SQLite.compile( $sql_or_ast ); $sth1 = DBI.compile( :statement($sql_or_ast), :driverSQLite ); This would give you a STH which is divorced from the actual DB connection instance. Yes, that is true. However, I like for all application calls to go through the Interface module (DBI) since that is what the Interface is for. It is also more practical to go by way of the DBI module because it can do things for you like certain kinds of validation on application input and driver output, so the application or driver respectively doesn't have to do those tests for robustness. Each DBI driver can worry less about that its input is correct and focus more on its actual work. (FYI, a common input/output validation is one of the main things that the 'Rosetta' module does for its own drivers and users. More so in next release v0.46.) Because you constructed it like this, without reference to a (possibly unconnected) connection object, then $sth1.prepare is not available. I wouldn't worry about that; the proposed DBI would elegantly handle (throw an exception) attempts to invoke methods that depend on an open connection when there is none. People who like to check first will also have a $dbh.is_open() method available to them. I also see the situation as no worse than the current DBI v1 where you can invoke such things as fetchrow_arrayref() on a $sth that hasn't been executed yet. Oh, and I have another DBI v2 suggestion to add, also following Rosetta design: 1. A $sth should not contain any methods for fetching the result of an executed statement; rather, execute() should return an object when successful that represents its result; you invoke fetching methods on that object. For example: my $rlh = $sth-execute(); my $rowset = $rlh-fetchrow_arrayref(); This approach is a lot more flexible. Well, when you parse the statement into an AST, the flavour of SQL will affect how it is parsed and what is allowed. Eg, Oracle has significant features in some comments (query hints). It also has quirky and somewhat useless keywords like CONNECT BY. So, when you ask a DBH connected to a driver to parse something, then it will use that driver's SQL dialect, if one exists, but I still want to be able to deal with SQL ASTs without implying a SQL flavour. You still can. The point of an abstract syntax tree is that the original SQL dialect isn't very important to what it contains (and this is how SQL::Routine is); by contrast, a concrete syntax tree cares very much about the original syntax and preserves it to the last detail. I don't see a problem here. ... Except that those AST-like-modules which embed raw SQL fragments (like where-clauses) as a matter of course aren't actually that abstract and could pose a problem; lots of DBI wrappers are this way, unfortunately (but not mine). I am operating under the assumption that this system should work if there are no external config files that the DBI/DBD would read, and the application would provide that information; if its in a file, the application would read it in, or would explicitly tell DBI where it is. Or at least it should be possible for this to happen, even if a DBD defaults to look in a default location when it doesn't get the equivalent from the application. Absolutely, that must work. But it would still be nice to be able to config this without digging through the application to see where the password is written. Er, I hope you didn't interpret this as the application source code. My point was that the database connection details can be part of the general application configuration file, which is still data and not code. This way, each application can have distinct database settings if it wants to. Such a great word, abhorrent. So fitting for this case. It sure does look like an (overprematuremisguided)-optimisation to avoid using the full module name in an internal hash or something like that. But then maybe (Iwenone(Gaia)) are missing some context there. Well, I have also decided that I can
Re: DBI v2 - The Plan and How You Can Help
- optional treatment of the statements as an AST, similar in concept to SQL::Routine, or Tangram::Expr. Death to SQL templating systems! I suspect during this process people are going to want a lot of things that layer on top of what we currently see as DBI. Personally I think Tim got it right to initially only handle connectivity and preparation stuff in DBI, and not try to deal with schemas or relational-mapping or any of that other gumpf. I see the issues with the table methods in DBI as an example of this. What you want in many situations is something much more details that just a list of table names. So with that in mind. I'd like to see DBI done as a set of multiple interfaces that each driver can choose to support or not support. For example, the connectivity layer. This currently works very very well and has allowed huge numbers of different interfaces to almost every database in existance. Every driver, as they do now, should implement support for this layer. On top of that, if you really must interact with the database to query table names and so on, lets see that done as a separate layer. DBI2::Schema or something. drivers could then specifically advertise support or non-support for that API. And we could get an interface somewhat richer than the current raw hashes one. Adam K
Re: DBI v2 - The Plan and How You Can Help
Sam Vilain wrote: However, making it in a file in $HOME/.xxx means that the sysadmin can set it up to be mode 400 or something like that, to ensure other users can't access it if someone forgot to set the permissions right on the application code (or, hopefully, configuration file). I don't think this solves the problem, because what I usually want is the user to be able to use the application, but unable to see the DB password. So the user should have read permission set for the file, but on the other hand he shouldn't. It's not not a problem for Web App, though. -- Maxim Sloyko
Re: DBI v2 - The Plan and How You Can Help
- support for automatically pulling database DSN information from a ~/.dbi (or similar) file. This is constantly re-invented poorly. Let's just do a connect by logical application name and let the SysAdmins sort out which DB that connects to, in a standard way. This reminds me one one thing I hate about DB access, and that is having the DB password stored in plain text. Of course there are ways to provide some concealment, but nothing particularly good or integrated into the access. If the connecting by logical application name could also include some level of security access, that would be a big improvement. R.
Re: DBI v2 - The Plan and How You Can Help
Richard Nuttall wrote: - support for automatically pulling database DSN information from a ~/.dbi (or similar) file. This is constantly re-invented poorly. Let's just do a connect by logical application name and let the SysAdmins sort out which DB that connects to, in a standard way. This reminds me one one thing I hate about DB access, and that is having the DB password stored in plain text. Of course there are ways to provide some concealment, but nothing particularly good or integrated into the access. If the connecting by logical application name could also include some level of security access, that would be a big improvement. R. Which is why major DBMSs are increasingly relying on SSO based solutions. (e.g., Kerberos/LDAP authentication). Not certain if DBI is the proper level to implement that, (probably needs to be down at the DBD = DBMS level). And in a standard way may still be wishful thinking. Also, I'm not sold on the idea that a ~/.dbi file is particularly secure in that regard. Not neccesarily opposed, just not convinced its the right solution. (I don't like cleartext passwords either, but due to the variance in DBMS's authentication methods, I don't know if DBI can solve that problem). - Dean
Re: DBI v2 - The Plan and How You Can Help
Richard Nuttall wrote: - support for automatically pulling database DSN information from a ~/.dbi (or similar) file. This is constantly re-invented poorly. Let's just do a connect by logical application name and let the SysAdmins sort out which DB that connects to, in a standard way. This reminds me one one thing I hate about DB access, and that is having the DB password stored in plain text. Sadly, there is really nothing that can be done about this, other than casual obscuring of the real password like CVS does in ~/.cvspass However, making it in a file in $HOME/.xxx means that the sysadmin can set it up to be mode 400 or something like that, to ensure other users can't access it if someone forgot to set the permissions right on the application code (or, hopefully, configuration file). Of course, for more secure access schemes like kerberos, etc, the file is really just being a little registry of available data sources. On a similar note could be allowing overriding the data source used by an application by setting an environment variable. That way, the SysAdmin has got lots of options when it comes to managing different production levels - Oracle has this with TWO_TASK, and while it's a PITA when it's not there (no doubt why DBD::Oracle allows this to be specified in the DSN string), it's also useful for what it's intended for - switching databases from an operational perspective. Sam.
Re: DBI v2 - The Plan and How You Can Help
Tim et al, Following are some ideas I have for the new DBI, that were thought about greatly as I was both working on Rosetta/SQL::Routine and writing Perl 6 under Pugs. These are all language-independent and should be implemented at the Parrot-DBI level for all Parrot-hosted languages to take advantage of, rather than just in the Perl 6 specific additions. I believe in them strongly enough that they are in the core of how Rosetta et al operates (partly released, partly pending). 0. There were a lot of good ideas in other people's replies to this topic and I won't repeat them here, for the most part. 1. Always use distinct functions/methods to separate the declaration and destruction of a resource handle / object from any of its activities. With a database connection handle, both the open/connect() and close/disconnect() are $dbh methods; the $dbh itself is created separately, such as with a DBI.new_connection() function. With a statement handle, the prepare() is also a $sth method like with execute() et al; the $sth itself is created separately, such as with a $dbh.new_statement() method. If new handle types are created, such as a separate one for cursors, they would likewise be declared and used separately. With this separation, you can re-use the resource handles more easily, and you don't have to re-supply static descriptive configuration details each time you use it, but rather only when the handle is declared. At the very least, such static details for a connection handle include what DBI implementor/driver module to use; as well, these details include what database product is being used, and locating details for the database, whether internet address or local service name or on-disk file name and so on. This can optionally include the authorization identifier / user name and password, or those details can be provided at open() time instead if they are likely to be variable. 2. Always separate out any usage stages that can be performed apart from the database itself. This allows an application to do those stages more efficiently, consuming fewer resources of both itself and the database. For example, a pre-forked Apache process can declare all of the database and statement handles that it plans to use, and do as much of the prepare()-type work that can be done internally as possible, prior to forking; all of that work can be done just once, saving CPU, and only one instance of it consumes RAM. All actual invocations of a database, the open()/connect() and execute() happen after forking, and at that point all of the database-involving work is consolidated. Or even when you have a single process, most of the work you have to do, including any SQL generation et al, can be more easily be pre-performed and the results cached for multiple later uses. Some DBI wrappers may do a lot of work with SQL generation et al and be slow, but if this work is mainly preparatory, they can still be used in a high-speed environment as that work tends to only need doing once. Most of the prep work of a DBI wrapper can be done effectively prior to ever opening the database connection. 3. Redefine prepare() and execute() such that the first is expressly for activities that can be done apart from a database (and hence can also be done for a connection handle that is closed at the time) while all activities that require database interaction are deferred to the second. Under this new scheme, when a database has native prepared statements support that you want to leverage, the database will be invoked to prepare said statements the first time you run execute(), and then the result of this is cached by DBI or the driver for all subsequent execute() to use. In that case, any input errors detected by the database will be thrown at execute() time regardless of their nature; only input errors detected by the DBD module itself would be thrown at prepare() time. (Note that module-caught input errors are much more likely when the module itself is handling SQL in AST form, whereas database-caught input errors are much more likely when SQL is always maintained in the program as string form.) Note also that the deferal to execute() time of error detection is what tends to happen already with any databases that don't have native prepared statement support or for whom the DBI driver doesn't use them; these won't be affected by the official definition change. Now I realize that it may be critically important for an application to know at prepare() time about statically-determinable errors, such as mal-formed SQL syntax, where error detection is handled just by the database. For their benefit, the prepare()+execute() duality could be broken up into more methods, either all used in sequence or some alternately to each other, so users get their errors when they want them. But regardless of the solution, it should permit for all
Re: DBI v2 - The Plan and How You Can Help
Darren Duncan wrote:
3. Redefine prepare() and execute() such that the first is expressly for
activities that can be done apart from a database (and hence can also be
done for a connection handle that is closed at the time) while all
activities that require database interaction are deferred to the second.
That would be nice, but there are some DBDs for which you need the database
on hand for $dbh.prepare() to work. In particular, DBD::Oracle.
I think that what you are asking for can still work, though;
# this module creates lots of SQL::Statement derived objects, without
# necessarily loading DBI.
use MyApp::Queries %queries;
# not connect, so doesn't connect
my $db = DBI.new( :source(myapp) );
# prepare the objects as far as possible
my %sths;
for %queries.kv - $query_id, $query_ast_or_template {
%sths{$query_id} = $db.prepare($query_ast_or_template);
}
# connect
$db.connect;
# now proceed as normal
my $sth = %sthssome_query_id;
$sth.execute( :param(foo), :this(that) );
So, effectively the prepare can happen at any time, and it's up to the
DBD to decide whether to actually do anything with it immediately or not.
ie, on Pg the STHs would be built before the DB is connected, and on Oracle
they are built the first time they are used (and then cached).
Now I realize that it may be critically important for an application to
know at prepare() time about statically-determinable errors, such as
mal-formed SQL syntax, where error detection is handled just by the
database. For their benefit, the prepare()+execute() duality could be
broken up into more methods, either all used in sequence or some
alternately to each other, so users get their errors when they want
them. But regardless of the solution, it should permit for all
database-independent preparation to be separated out.
OK, so we have these stages;
1. (optional) generate an AST from SQL
2. (optional) generate SQL string from an AST
3. generate a handle for the statement, sans connection
4. prepare handle for execution, with connection
5. execute statement
I think these all fit into;
1. SQL::Statement.new(:sql(...));
2. $statement.as_sql;
3. $dbh.prepare($statement) or $dbh.prepare($statement, :nodb);
4. $dbh.prepare($statement) or $sth.prepare while connected
5. $sth.execute
In particular, I don't think that the DB driver should automatically
get a chance to interfere with SQL::Statement; if they want to do that,
then they should specialise SQL::Statement. IMHO.
Perhaps you have some other examples that don't fit this?
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly data
source.
I interpret this as asking that the detailed parameters to the DBI
connection are expanded into named options rather than simply bundled into
a string.
That, I agree with, and I guess it would be useful occasionally to be
able to specify all that rather than just setting it up once and labelling
those connection parameters with a source that comes from ~/.dbi.
Particularly for writing gui dialogs for interactive database utilities.
Either way, you don't want most applications dealing with this complexity
at all, really.
6. DBI drivers should always be specified by users with their actual
package name, such as 'DBD::SQLite', and not some alternate or
abbreviated version that either leaves the 'DBD::' out or is spelled
differently. Similarly, the DBI driver loader should simply try to load
exactly the driver name it is given, without munging of any type. This
approach is a lot more simple, flexible and lacks the cludges of the
current DBI. DBI driver implementers can also name their module
anything they want, and don't have to name it 'DBD::*'. A DBI driver
should not have to conform to anything except a specific API by which it
is called, which includes its behaviour upon initialization, invocation,
and destruction.
Is this useful?
I can't see a reason that the DBI.new() / DBI.connect() call shouldn't be
flexible in what it accepts;
$dbh = DBI.new( :driverRosetta ); # means DBD::Rosetta
$dbh = DBI.new( :driverRosetta::Emulate::DBD ); # specify full package
$dbh = DBI.new( :driver(Rosetta::Emulate::DBD) ); # pass type object
$dbh = DBI.new( :driver(DBD::SQLite.new(:foobar)) ); # pass driver object
Sam.
Re: DBI v2 - The Plan and How You Can Help
Okay, considering that using the same name prepare() like this may confuse some people, here is a refined solution that uses 3 methods instead; please disregard any contrary statements that I previously made: # Opt 1: A user that wants the most control can do this (new feature): my $sth1 = $dbh.compile( $sql_or_ast ); # always sans connection $sth1.prepare(); # always with connection, even if DBD doesn't use it $sth1.execute(); # always with connection # Opt 2: If they want less control, they do this (same as old DBI): my $sth2 = $dbh.prepare( $sql_or_ast ); # combines Opt 1's comp/prep $sth2.execute(); # same as Opt 1 # Opt 3: Alternately, there is this (akin to my older suggestion): my $sth3 = $dbh.compile( $sql_or_ast ); # same as Opt 1 $sth3.execute(); # combines Opt 1's prep/exec # Opt 4: Even less control (akin to old DBI's do): $dbh.execute( $sql_or_ast ); # combines Opt 1's comp/prep/exec In this model, when you use just prepare() and execute(), they behave identically to the old DBI, including that they require an open connection. So no mystery there. The new feature is if you decide to use compile(); you then give that method the arguments you would have given to prepare(), and you invoke prepare() on the result with no arguments; each DBD would decide for itself how the work is divided between compile() and prepare() with the limitation that compile() is not allowed to access the database; ideally the DBD would place as much work there as is possible, which would vary between Oracle/Pg/etc. Invoking just compile() then execute() will cause the execute() to do what prepare() normally does against a database, and cache the prepared handle. In option 4, I renamed the old DBI's do() to execute() for consistency with the other examples; but this execute() is different in that it caches the prepared statement handle. In any event, with all 4 examples, execute() gives you the same result regardless of what is called before it. At 2:49 PM +1200 7/5/05, Sam Vilain wrote: In particular, I don't think that the DB driver should automatically get a chance to interfere with SQL::Statement; if they want to do that, then they should specialise SQL::Statement. IMHO. I am operating under the assumption here that while the new DBI is designed to effectively support wrapper modules, the wrapper modules would also be altered from their current DBI-1-geared designs to accomodate DBI-2. But still, what do you mean by interfere? 5. All details used to construct a connection handle should be completely decomposed rather than shoved into an ungainly data source. I interpret this as asking that the detailed parameters to the DBI connection are expanded into named options rather than simply bundled into a string. That, I agree with, and I guess it would be useful occasionally to be able to specify all that rather than just setting it up once and labelling those connection parameters with a source that comes from ~/.dbi. Particularly for writing gui dialogs for interactive database utilities. I see the act of storing all the data as a single string at any time to be a messy affair to be avoided. The application doesn't have to know about the complexity to pass around a hash of values any more than it does with a string; but when the application wants to know the details, dealing with a hash is easier. Either way, you don't want most applications dealing with this complexity at all, really. I am operating under the assumption that this system should work if there are no external config files that the DBI/DBD would read, and the application would provide that information; if its in a file, the application would read it in, or would explicitly tell DBI where it is. Or at least it should be possible for this to happen, even if a DBD defaults to look in a default location when it doesn't get the equivalent from the application. 6. DBI drivers should always be specified by users with their actual package name, such as 'DBD::SQLite', and not some alternate or abbreviated version that either leaves the 'DBD::' out or is spelled differently. Similarly, the DBI driver loader should simply try to load exactly the driver name it is given, without munging of any type. This approach is a lot more simple, flexible and lacks the cludges of the current DBI. DBI driver implementers can also name their module anything they want, and don't have to name it 'DBD::*'. A DBI driver should not have to conform to anything except a specific API by which it is called, which includes its behaviour upon initialization, invocation, and destruction. Is this useful? I can't see a reason that the DBI.new() / DBI.connect() call shouldn't be flexible in what it accepts; $dbh = DBI.new( :driverRosetta ); # means DBD::Rosetta $dbh = DBI.new( :driverRosetta::Emulate::DBD ); # specify full package $dbh = DBI.new(
Re: DBI v2 - The Plan and How You Can Help
Hey Tim.
I've kept an eye on Perl 6 and Parrot developments but I'm no expert in
either. What I'd like *you* to do is make proposals (ideally fairly
detailed proposals, but vague ideas are okay) for what a Perl 6 DBI API
should look like.
Keep in mind that the role of the DBI is to provide a consistent
interface to databases at a fairly low level. To provide a *foundation*
upon which higher level interfaces (such as Class::DBI, Tangram, Alzabo
etc. in Perl 5) can be built.
OK, well based on my experience, here's a few things that would be nice;
- optional treatment of the statements as an AST, similar in concept to
SQL::Routine, or Tangram::Expr. Death to SQL templating systems!
Ideally there should be no need for a module like DBD::CSV or even,
eventually, DBD::SQLite to actually generate SQL strings for queries;
the AST is enough for them to go away and run the query.
It should be possible to use either, though - and use /named/
placeholder arguments for either, something like:
use DBI-2;
my $dbi = DBI-connect(...);
my $sth = $dbi-prepare(select * from foo where bar = :bar);
$sth-execute(:bar($bar));
my $table = $dbi-table(foo);
my $sth = $dbi-select
(:all
:from($table),
:where($table.bar == $dbh.placeholder(bar))
);
$sth-execute(:bar($bar));
Virtually every major DB abstraction tends to build this, whether or
not they know they're doing it ;).
- support for automatically pulling database DSN information from a
~/.dbi (or similar) file. This is constantly re-invented poorly.
Let's just do a connect by logical application name and let the
SysAdmins sort out which DB that connects to, in a standard way.
- object-oriented modelling of core database objects, so that schema
operations can be made portable, a la Rosetta. This should really
just constitute re-thinking the existing interfaces, and leaving it
up to the DBD authors to finish them up.
This may also open the door for unifying the way that relationships
such as foreign key constraints, etc are dealt with.
- make it easier to get ACID right; steal Tangram's `tx_do` transaction
interface; though I'd suggest a name including the perl6-ish `try'
$dbh.tx_try {
# ... transaction ...
};
in fact, if you were to wrap the closure in an atomic{ } block, then
you could probably easily support replaying the transaction in the
event of a forced rollback (so long as the code in the closure has no
side effects ... g)
Also there is the nested transction interface;
$dbh.tx_start;
$dbh.tx_start;
$dbh.tx_commit;
$dbh.tx_commit; # commit happens here
Some databases can even support nested transactions internally via
SQL SAVEPOINTS.
- asynchronous/event-based processing of queries, so that we don't need
yet another sub-process when in POE-like environments (which might
include a standard Perl 6 event programming system).
In terms of making things Perl6-ish, you probably want to look at technology
such as coroutines for cursors, and continuations for exception handling, for
their implications to writing transactional applications.
Perl 6 will be able to serialise continuations and probably also coroutines,
so it should be possible for a continuation serialised with an active cursor
in a coroutine to automatically resume itself once the continuation is thaw'ed.
This might happen by simply leaving the transaction open (I hear screams!) in
a single threaded context, or by rolling back, replaying the transaction's
queries on the continuation resume and checking all the database responses
match what was previously read. At the first sign of discrepancies, the
next database operation would throw a fake ROLLBACK; which would even be
caught and the application transaction replayed from the beginning, if they
used .tx_try :retry(3), :{ ... }
This would make some of the old problems, such as object versioning,
potentially a lot easier to solve.
Allow me to illustrate with some application code;
my $mvc;
if ($mvc.controller.action eq edit) {
my $id = $mvc.controller.edit_id;
my $object;
$dbi.tx_try {
$object = $fetch_query.select_only(:id($id));
$mvc.view(:state(edit), :object($object))
until $mvc.controller.action eq commit;
$update_query.execute(:id($id));
CATCH {
$mvc.view(:state(error),
:message(object modified by another!));
}
}, :retry(3);
$mvc.view(:state(edit), :object($object));
}
So, depending on the MVC system in use, when you wrote $mvc.view(), it
will have done one of the following things;
Session-based continuations:
1. Serialise the continuation - when it comes across the DBH with an
active transaction, it creates
DBI v2 - The Plan and How You Can Help
Once upon a time I said: http://groups-beta.google.com/group/perl.dbi.users/msg/caf189d7b404a003?dmode=sourcehl=en and wrote http://search.cpan.org/~timb/DBI/Roadmap.pod which yielded: https://donate.perlfoundation.org/index.pl?node=Fund+Drive+Detailsselfund=102 (A little over $500 of that I effectively put in myself.) My *sincere* thanks to all those who donated to the fund, especially individuals. I had hoped for more corporate response with less from individuals and I'm touched by the personal generosity shown. I've not drawn any money from it yet and doubt that I will myself. (I'm considering suggesting that the Perl Foundation make payments from the fund to people making specific contributions to the DBI. I'm thinking especially of work on a comprehensive test harness. But I'll see how the developments below pan out before making specific arrangements.) So, that lead to: http://groups-beta.google.com/group/perl.dbi.dev/browse_frm/thread/ef14a9fc0a37441f/fb8fe20a86723da0#fb8fe20a86723da0 Which sums up fairly well where I'm at: DBI v1 will rumble on for Perl 5 and DBI v2 will be implemented for Perl 6. --- digression --- At this point I'd like to make a slight digression to highlight the amazing work going on in the Perl 6 community at the moment. Especially Autrijus' Pugs project which has brought Perl 6 to life. Literally. Take a look at: http://pugscode.org/talks/yapc/slide1.html http://use.perl.org/~autrijus/journal and especially: http://use.perl.org/~autrijus/journal/24898 Yes, that really is Perl 6 code using the DBI being executed by Pugs. That's great, and I was truly delighted to see it because it takes the pressure off the need to get a DBI working for Perl 6 - because it already is working for Perl 6. At least for Pugs. (The Ponie project is also likely to provide access to Perl 5 DBI from Perl 6 by enabling future versions of Perl 5 to run on Parrot.) --- digression --- I have recently come to an arrangement that will enable me to put some worthwhile development time into DBI (still very much part-time, but enough to give it focus and move forward). My initial goals are: 1. to work on a more detailed specification for the DBI v2 API that takes advantage of appropriate features of Perl 6. 2. to work on a more detailed specification for the DBDI API http://groups-beta.google.com/group/perl.perl6.internals/msg/cfcbd9ca7ee6ab4 3. to work on tools to automate building Parrot NCI interfaces to libraries (such as database client libraries, obviously :) But I'm hoping you'll join in and help out. I've kept an eye on Perl 6 and Parrot developments but I'm no expert in either. What I'd like *you* to do is make proposals (ideally fairly detailed proposals, but vague ideas are okay) for what a Perl 6 DBI API should look like. Keep in mind that the role of the DBI is to provide a consistent interface to databases at a fairly low level. To provide a *foundation* upon which higher level interfaces (such as Class::DBI, Tangram, Alzabo etc. in Perl 5) can be built. So, if you have an interest in the DBI and Perl 6, put your thinking cap on, kick back and dream a little dream of how the DBI could be. How to make best use of the new features in Perl 6 to make life easier. Then jot down the details and email them to me (or to dbi-users@perl.org if you want to kick them around in public for a while first). I'm about to fly off for two weeks vacation (in a few hours), blissfully absent of any hi-tech gear beyond a mobile phone. When I get back I'll gather up your emails and try to distill them into a coherent whole. Have fun! Tim.
