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
