Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On Sun, Jan 18, 2004 at 12:14:22PM -0500, John Siracusa wrote: > On 1/12/04 10:47 AM, Tim Bunce wrote: > > And this is what I'd like you to be thinking about > > (mostly directed at driver authors): > > > > A. What changes you'd like to see in the DBI API. > > This topic came up before, when DateTime was just getting off the ground. > DateTime is a lot more mature now, and I still think it's a good idea :) It is. Or at least the need is an important one and worth considering. Thanks for the reminder. My preference is that after doing: $sth->bind_column(1, undef, SQL_DATE); $sth->bind_column(2, undef, SQL_DATETIME); $sth->bind_column(3, undef, SQL_TIMESTAMP); the driver should ensure that it returns values for those three column in the corresponding international standard formats (as per ODBC). Similarly, when doing: $sth->bind_param(1, $foo, SQL_DATE); $sth->bind_param(2, $bar, SQL_DATETIME); $sth->bind_param(3, $baz, SQL_TIMESTAMP); the driver should expect to find the values given to it already in the correct standard format for the specified type. The driver can then convert if it needs to, but few will as the standard format is recognized by almost all db's. (I should also go look in the archives at what's been said before on this topic.) Beyond that I'd like to add a way to have a callback fired when a value is fetched. Something like: $sth->bind_column(1, undef, { OnFetch => $code_ref }); and then you could do whatever you wanted. (I don't see a significant need for a corresponding hook for bind_param and/or execute.) I've been trying to avoid having the DBI take over responsibility for setting each fetched field value, the drivers do that currently, but I think it's enevitable. For example, few compiled drivers use the right code to handle perl 'magic' so if you bind_column to a tied value it doesn't work - the STORE method of the tie doesn't get called. > Taken to the next level (a la Perl 6 and Larry-think :) DBI 2 could > implement all of this as part of a generic DBD-specific column type > formatting/parsing system. But I just care about dates right now :) I don't think a column type formatting/parsing system is needed. But I do thing a "column type system" is needed to provide the hooks that'll enable you to do what you want. It needs more thinking though and, once the DBI takes over setting each fetched field values, it can be added in without drivers having to change at all. > I mentioned earlier that I've written a lot of DBI wrappers, and that the > wrappers usually have some per-dbh flag that indicates whether or not I'm in > the middle of a transaction. This is the type of thing that should be added > to DBI 2 (see earlier thread). Ah. I hadn't filled out the ExecuteCount description all the way: : Count do()'s and execute()s (that aren't called by do()s) in imp_dbh. : Make available as an ExecuteCount attribute. : Reset count in commit/rollback. : Add InTransaction attribute that defaults to: !AutoCommit && ExecuteCount>0 : Drivers are expected to implement InTransaction if they can. : In $dbh->DESTROY if !AutoCommit don't rollback&warning : unless InTransaction is true. Thanks. Tim.
Re: Conformance test script for drivers
Yes, I'd like it to be distributed with the DBI and used to form the bulk of the tests for both the DBI and drivers. Tim. On Sun, Jan 18, 2004 at 01:20:11PM -0500, Jeff Urlwin wrote: > May I suggest that we make this either part of DBI or DBI::DriverTest and > that it's available via Subversion or CVS so that many can contribute? I > may be able to contribute some of my tests, for example, but I certainly do > not have time to be responsible for it. > > Regards, > > Jeff > > > > -Original Message- > > From: Tim Bunce [mailto:[EMAIL PROTECTED] > > Sent: Saturday, January 17, 2004 9:17 AM > > To: DBI developers > > Subject: Re: Conformance test script for drivers > > > > > > On Fri, Jan 16, 2004 at 07:48:15PM -0500, Thomas A. Lowery wrote: > > > > Any volunteers to work on this? > > > > > > I will. > > > > Wonderful! Many thanks Tom. > > > > It's hard to overestimate how important this will be. > > Especially with DBI v2, parrot, and perl6 on the horizon. > > > > Tim. > > > >
RE: Conformance test script for drivers
May I suggest that we make this either part of DBI or DBI::DriverTest and that it's available via Subversion or CVS so that many can contribute? I may be able to contribute some of my tests, for example, but I certainly do not have time to be responsible for it. Regards, Jeff > -Original Message- > From: Tim Bunce [mailto:[EMAIL PROTECTED] > Sent: Saturday, January 17, 2004 9:17 AM > To: DBI developers > Subject: Re: Conformance test script for drivers > > > On Fri, Jan 16, 2004 at 07:48:15PM -0500, Thomas A. Lowery wrote: > > > Any volunteers to work on this? > > > > I will. > > Wonderful! Many thanks Tom. > > It's hard to overestimate how important this will be. > Especially with DBI v2, parrot, and perl6 on the horizon. > > Tim. >
Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On 1/12/04 10:47 AM, Tim Bunce wrote: > And this is what I'd like you to be thinking about > (mostly directed at driver authors): > > A. What changes you'd like to see in the DBI API. This topic came up before, when DateTime was just getting off the ground. DateTime is a lot more mature now, and I still think it's a good idea :) I mentioned earlier that I've written a lot of DBI wrappers, and that the wrappers usually have some per-dbh flag that indicates whether or not I'm in the middle of a transaction. This is the type of thing that should be added to DBI 2 (see earlier thread). Along those lines, all of my DBI wrappers have also had a uniform API for DB-specific date parsing and formatting. My most recent DBI wrapper uses DateTime as the interchange format for dates. Like the transaction flag, this should be part of DBI. Here's my motivation. Once I get a date from a database, I usually want to do something with it: compare it to another date (from the same database, another database, or somewhere else entirely), do date math, etc. This is a pain because the string I get from the database is often db-specific. When comparing it to anything, I have to convert both dates to some common format. To "avoid the trouble", I have seen (and, sadly, written) code that just assumes the date values will string-compare as expected. Bad idea! When I add a date to a database, that date may come from many different sources and be in all kinds of formats. It's a pain to have to maintain code that can parse and format all the different kinds of dates for all the different databases, so I end up just implementing the subset I actually need to use. This doesn't foster code reuse, and in fact, I've done it myself several times already. Bleh. I think this functionality should be added to DBI, specifically to each DBD. The requirements are pretty simple: For each date/time column type supported by a particular database, the DBD should provide a family of functions that can take a string and return a DateTime object, and vice versa. Example: CREATE TABLE foo ( date DATE, # e.g. '2004-01-02' created DATETIME, # e.g. '2004-01-02 12:34:56' modified TIMESTAMP, # e.g. '20040102123456.789123' ... ); ... $sth = $dbh->prepare('SELECT * FROM foo'); $sth->execute; $row = $sth->fetchrow_hashref; # Get DateTime objects from the column values $date = $dbh->parse_date($row->{'date'}); $created = $dbh->parse_datetime($row->{'created'}); $modified = $dbh->parse_timestamp($row->{'modified'}); ... $sth = $dbh->prepare('INSERT INTO foo (date, created, modified) ' . ' VALUES (?, ?, ?)'); $sth->execute($dbh->format_date('20050102'), $dbh->format_datetime($created), $dbh->format_timestamp_keyword('now')); # see below That's the functionality, anyway. The exact API is up for grabs. As far as I've seen, DateTime can handle all the "weird" dates like the infinities, and can support any timezone stuff that the DBs use. DateTime also handles date math, comparisons, and has an extensible formatting/parsing system. The trickiest part is getting the DBD parsing functions to handle the various kinds of keywords supported by databases: 'now', 'today', 'allballs', '-infinity', etc. To that end, it may also be useful to have a family of functions that translates between equivalent keywords. For example: # If the DBD supports the 'allballs' keywords, this is a # pass-through. If not, '00:00:00' may be returned. $kw = $dbh->format_time_keyword('allballs'); The DBD's quote() function would also need to know how to handle the various keywords that may be returned by the parse_*_keyword() functions (e.g. 'now' is quoted, but NOW() isn't) In all cases, if there is something a DBD doesn't know how to do, it should be a fatal error. This proposal doesn't add any overhead to a DBD (except perhaps to quote(), but there could be a separate function for quoting date/time keywords if that is a concern) since all of the formatting and parsing functions must be used explicitly. I think it would go a long way towards making DBI even more database-independent, it'd save a lot of DBI users a lot of time and effort, and it'd encourage better code by making correct comparisons an date math even easier than doing it "the wrong way." Taken to the next level (a la Perl 6 and Larry-think :) DBI 2 could implement all of this as part of a generic DBD-specific column type formatting/parsing system. But I just care about dates right now :) -John
Re: Conformance test script for drivers
On Sat, Jan 17, 2004 at 04:51:36PM +0100, H.Merijn Brand wrote: > On Sat 17 Jan 2004 15:16, Tim Bunce <[EMAIL PROTECTED]> wrote: > > On Fri, Jan 16, 2004 at 07:48:15PM -0500, Thomas A. Lowery wrote: > > > > Any volunteers to work on this? > > > > > > I will. > > > > Wonderful! Many thanks Tom. > > Yes. Great. > > Send the prototype to me [...] Before we get that far there should be some discussion on the design first. Tim.