Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On 18 Jan 2004, at 17:14, John Siracusa wrote: 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 :) 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. I've been using DateTime in production now for a year. While I think it's a great module for certain tasks, the biggest downfall is its size. It's ENORMOUS. Even compared to the DBI (last time I checked anyway). I'd be against mandating it, but would be happy with a flexible system whereby you could have an accessor mapping function, so you could get back DateTime objects for dates, or Time::Piece objects, or whatever alternative you can come up with (and apply the same logic to other data types). Matt. smime.p7s Description: S/MIME cryptographic signature
Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On 1/18/04 6:33 PM, Tim Bunce wrote:
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.
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.)
These are all good suggestions, but to implement them you'll need an API
similar to the one I described. Sure, it could be internal or non-standard,
but that'd miss the point of my proposal. Not all parsing/formatting can be
conveniently done at bind columns time. Indeed, not every DBI use case
even calls for binding columns. Whether or not what you described is
implemented, I think we still need a user-visible API like the one I
described that can be use outside of bind_columns() on any data at any time,
so long as there's a $dbh available.
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.
Having DBI twiddle fetched values for me is useful, but I'd also like the
lower-overhead option of doing it myself only when I need to via explicit
calls to parse_*() and format_*()functions. I'd also much rather deal with
DateTime objects as the interchange format, not ISO date strings, especially
when date math and comparisons are called for.
DateTime objects are heavyweight too, so maybe ISO dates could be used as
the interchange format in the bind-columns-driven DBD-independent system
that you describe, but then I'd like a DBD-specific parsing/formatting API
to be built on top of that :) That's something each DBD author would have
to add, but a common internal interchange format (ISO dates) might make
that easier. The DBDs would also have to implement the keyword parsing,
pass-through, and translation.
-John
Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On 1/19/04 2:22 PM, Tim Bunce wrote: Short answer: no. Can I please have the long answer? :) I really think this type of thing is common enough that, at the very least, there should be convenient hooks for parsing and formatting dates (or possibly any column types). Passing code refs to bind_columns() calls is not what I'd consider convenient. If you want to use DateTime then I'd recommend the DateTime::Format::DBI module or something like it: See my subsequent post. This is part of the plan, but I don't want to have to (continue) to do it manually, either inline or in yet another DBI wrapper. -John
Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On Mon, Jan 19, 2004 at 02:32:17PM -0500, John Siracusa wrote: On 1/19/04 2:22 PM, Tim Bunce wrote: Short answer: no. Can I please have the long answer? :) I have no time, I've a plane to catch. Perhaps others can share there views and examples. Passing code refs to bind_columns() calls is not what I'd consider convenient. When I said But I do think a column type system is needed to provide the hooks that'll enable you to do what you want I'm thinking in terms of some way to say use this code ref by default for all fields with a TYPE value of SQL_DATE, for example. Tim.
Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On Mon, Jan 19, 2004 at 11:19:04PM +, Tim Bunce wrote:
On Mon, Jan 19, 2004 at 02:32:17PM -0500, John Siracusa wrote:
On 1/19/04 2:22 PM, Tim Bunce wrote:
Short answer: no.
Can I please have the long answer? :)
I have no time, I've a plane to catch. Perhaps others can share
there views and examples.
Passing code
refs to bind_columns() calls is not what I'd consider convenient.
When I said But I do think a column type system is needed to
provide the hooks that'll enable you to do what you want I'm
thinking in terms of some way to say use this code ref by default
for all fields with a TYPE value of SQL_DATE, for example.
Just to flesh that out a little more... something like:
# Override types for which you don't want the default of SQL_VARCHAR
$dbh-{BindColumnTypes} = {
SQL_DATE = SQL_DATE,
SQL_DATETIME = { TYPE = SQL_DATETIME, OnFetch = \my_inflate_thingy },
}
Tim.
Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On 1/19/04 7:41 PM, Tim Bunce wrote:
On Mon, Jan 19, 2004 at 07:19:05PM -0500, John Siracusa wrote:
What about the other direction, allowing arbitrary code
(\my_deflate_thingie) to run during calls like:
$sth-execute($val1, $val2, ...);
I'm much less inclined to do that. Smart thingies can stringify.
Yeah, but stringify to what? Probably not the db-specific thingies they
need to become since they have little or no way of knowing what context
they're in (i.e. which DBD) when they're asked to stringify.
Execute is called less often than fetch and with more direct control,
unlike, for example, selectall_arrayref().
I think we have two different goals here :) I'm trying to foster more
database independence by allowing the same end-user code to transparently
inflate and deflate (to use your terms) column values. The idea is
that, when Joe Progammer changes from MySQL to Postgres, he doesn't have too
pore over all his code replacing all his \my_inflate_thingie hooks, calls
to deflate_mysql_datetime(), and so on.
In the case of the bind_columns() hooks, it looks like the changes would
probably only have to be done in a few places (which is a few more than I'd
like, but anyway... ;)
In the case of calls to execute() and string-assembled SQL, the conversion
task is much more daunting. Imagine if a user changing from MySQL to
Postgres also had to go through all his code and change all the calls of
mysql_quote_int() and mysql_quote_datetime() and so on to
Pg::TypeQuote-quote('int', $val), PG::TypeQuote-quote('timestamp', $val),
etc.
Thankfully there's $dbh-quote() which Does the Right Thing for each
database when called from a $dbh of a given type.
What I'm looking for is a similar family of functions for coercing (probably
a better term than inflating or deflating) column values on both input
and output in a transparently database-independent way. That is, without
the DBI user having to know or do much more than he has to know or do when
quoting values with $dbh-quote()
Maybe you think that type of thing doesn't belong in DBI because it is too
high-level or substantially more complex than quoting, but it's a common
enough task that there should be *some* standardized framework for doing it.
While DateTime::Format::DBI is a nice step in the right direction, it's not
even half proper a solution without a transparent, bi-directional interface
with DBI. Ideally, this DBI column coercion interface would define its own
API rather than molding itself to the vagaries of DateTime::Format::DBI.
That way, DBI users could choose whatever column value coercion module(s)
they wanted--or use none at all and incur no additional overhead vs. plain
old DBI today.
Maybe a wrapper (or subclass?) really is the right solution. But IMO this
stuff is not so far outside of DBIs traditional domain. Things like
selectall_arrayref() are already pretty high-level, and $dbh-quote() is the
model of what could be achieved, albeit on a larger scale this time.
-John
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: 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 ExecuteCount0
: Drivers are expected to implement InTransaction if they can.
: In $dbh-DESTROY if !AutoCommit don't rollbackwarning
: unless InTransaction is true.
Thanks.
Tim.
