Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On 1/19/04 10:46 PM, Harry Jackson wrote: > I am sorry if I am reading things incorrectly. > > Do you want the DBI to load default date handling facilities unless > otherwise instructed ie.(load some other handlers). No, it wouldn't load any handlers at all unless explicitly told to do so. It's just that if you ask it to load handlers for a particular column type and then don't say which module you want to use for that purpose, DBI would use some default module. IOW, existing code that uses DBI would have no additional overhead and no change in functionality because none of it use()s DBI::ColumnHandlers (or whatever it's called) at all. -John
Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
John Siracusa wrote: "ENORMOUS" in terms of memory overhead? It depends on the context, I guess. If you don't make something required, then no one can use the parse_*() and format_*() functions in DBI unless they first explicitly register column type handlers for each DBD. That's a pain. I'd rather see DateTime be the default, but have it not loaded at all if something else is specified. (If I am teaching you to suck eggs, ignore me) I would have to disagree. We assume to much about what people want, or need[0]. I am always accused of this so always try to avoid it. If we where to work on the princlple of "opt out" we would have a grossly overweight DBI. If we take my case as an example: I have never had any need for more than trivial date handling so I certainly wouldn't want it added as default (god forbid). In no unceratain terms, handling dates is a non trivial matter, it never ceases to amaze me at how many ways I find dates represented when working in different repositories. The only way I could see something like this added is if a good majority of databases' supported a common time-stamp simlar to "EPOCH" but, this is not about to happen and even if it did the representation of intervals etc would need to be standardised as well. Even where databases impliment "EPOCH" they then go on to use various types to represent of interval in various formats blah blah. As far as I can see, for the DBI to provide some common routines (API) then it must must expect at least some commonality from the underlying drivers and as far as dates are concerned I am unaware of a commmon thread in this case. > Example: use DBI; # Loads DateTime::Format::DBI by default use DBI::ColumnTypes 'dates'; # DateTime::Format::Pg will be loaded on-demand by DateTime::Format::DBI use DBD::Pg; or # Light-weight use DBI; # Loads the fictional Time::Piece::Format::DBI use DBI::ColumnTypes (dates => 'Time::Piece::DBI'); # The fictional Time::PiecePg will be loaded on-demand use DBD::Pg; There may need to be trivial wrappers around DateTime::Format::* to fit whatever API DBI comes up with for parsing/formatting column types, but the idea is the same. No date parsing/formatting modules are loaded at all unless there is at least one "registration" or use() statement of DBI::ColumnTypes (or whatever the class is called). In the absence of a specific class for the "dates" umbrella column type, the DateTime modules are loaded. That doesn't mean all those DateTime modules are "required", just that people who want this functionality will have to have *some* sort of appropriate modules loaded. I am sorry if I am reading things incorrectly. Do you want the DBI to load default date handling facilities unless otherwise instructed ie.(load some other handlers). Harry [0] Where there is any ambiguity I like to work on the principle that to "opt in" is better than to "opt out". Think maling lists.
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 Mon, Jan 19, 2004 at 07:19:05PM -0500, John Siracusa wrote:
> On 1/19/04 7:13 PM, Tim Bunce wrote:
> > On Mon, Jan 19, 2004 at 11:19:04PM +, Tim Bunce wrote:
> >> 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 },
> > }
>
> 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.
Execute is called less often than fetch and with more direct control,
unlike, for example, selectall_arrayref().
Tim.
Re: DBI version 2 - DBD-specific date parsing and formatting using DateTime
On 1/19/04 7:13 PM, Tim Bunce wrote:
> On Mon, Jan 19, 2004 at 11:19:04PM +, Tim Bunce wrote:
>> 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 },
> }
What about the other direction, allowing arbitrary code
(\&my_deflate_thingie) to run during calls like:
$sth->execute($val1, $val2, ...);
-John
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 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 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
Short answer: no. If you want to use DateTime then I'd recommend
the DateTime::Format::DBI module or something like it:
http://search.cpan.org/~cfaerber/DateTime-Format-DBI-0.031/lib/DateTime/Format/DBI.pm
Tim.
On Mon, Jan 19, 2004 at 10:15:05AM -0500, John Siracusa wrote:
> 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 4:28 AM, Matt Sergeant wrote: > 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). "ENORMOUS" in terms of memory overhead? It depends on the context, I guess. If you don't make something required, then no one can use the parse_*() and format_*() functions in DBI unless they first explicitly register column type handlers for each DBD. That's a pain. I'd rather see DateTime be the default, but have it not loaded at all if something else is specified. Example: use DBI; # Loads DateTime::Format::DBI by default use DBI::ColumnTypes 'dates'; # DateTime::Format::Pg will be loaded on-demand by DateTime::Format::DBI use DBD::Pg; or # Light-weight use DBI; # Loads the fictional Time::Piece::Format::DBI use DBI::ColumnTypes (dates => 'Time::Piece::DBI'); # The fictional Time::PiecePg will be loaded on-demand use DBD::Pg; There may need to be trivial wrappers around DateTime::Format::* to fit whatever API DBI comes up with for parsing/formatting column types, but the idea is the same. No date parsing/formatting modules are loaded at all unless there is at least one "registration" or use() statement of DBI::ColumnTypes (or whatever the class is called). In the absence of a specific class for the "dates" umbrella column type, the DateTime modules are loaded. That doesn't mean all those DateTime modules are "required", just that people who want this functionality will have to have *some* sort of appropriate modules loaded. -John
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 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 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: 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
On Mon, Jan 12, 2004 at 03:47:56PM +, Tim Bunce wrote:
> Plans:
>
> 1. Move DBI source code to http://sourceforge.net/projects/dbi/
Actually it'll be http://svn.perl.org/modules/dbi/ as I've decided
to use Subversion (http://subversion.tigris.org/) hosted by perl.org
> 2. Add a few dbi-dev people as developers who can modify the source.
>
> 3. Fork off DBI v1 for maintenance and start work on DBI v2.
Here's the list of changes I'm considering for DBI v2.
Comments welcome. I'll post it to dbi-users in a couple of days.
I'll also be posting news of the "Parrot DBDI" project ;-)
Tim.
*** Changes for DBI v2 ***
--- Changes that may impact applications:
Turning AutoCommit on should trigger rollback not commit.
(ODBC does a commit)
Always taint check the $sql for do() and prepare()
if perl is in taint mode (can't be disabled).
Default TaintIn=>1 in perl taint mode?
Default TaintOut=>1 in perl taint mode but exclude placeholders?
Remove support for "old-style" connect syntax
(where the driver name is the 4th parameter).
Remove undocumented DBI->err and DBI->errstr methods.
Remove old informix fudge in tables() (would only impact people
using very old DBD::Informix versions as it now has it's own).
Minor change to parameters of HandleError callback
(add $err and $state ahead of $return).
Increase size of com struct (requires driver rebuild)
and improve size sanity checks.
--- Additions and other changes visible to applications:
Make ShowErrorStatement=>1 the default
Define expected uft8 behaviour. Basically drivers need to set the
uft8 flag on returned strings themselves when appropriate.
The DBI I define a way for an application to indicate that
a particular column should be flagged as uft8 to help drivers
that are not able to determine that themselves.
The DBI won't support automatic character set conversions.
Change "trace level" to support "topic bits" that can be set
independantly of the current integer "level". In other words
the lowest 4 bits will be used for the "level", as now.
The next 4 bits are reserved. The next 16bits are each
associated with a particular topic defined by the DBI.
The top 8 bits are from for the driver to use.
The topics will be assigned letters which can be used to
set the trace level to make it easier to remember settings.
Ability to remove a handle from the parents cache:
$sth->uncache;
and $dbh->uncache; for connect_cached
Add discard_pending_rows() as an alias
for finish() - which will be deprecated.
$sth->{ParamTypes} eg { "1" => SQL_VARCHAR, "2" => { TYPE=>SQL_VARCHAR, ora_type=>99
}};
$h->{KidsHandles} = ref to cache (array or hash?)
of weakrefs to child handles.
DBI::Profile: some way to get count of 'executions' only, not all method calls.
So avg time is totaltime/executions not totaltime/methodcalls.
Document DbTypeSubclass (ala DBIx::AnyDBD)
Polish up and document _dbtype_names with an external interface and using get_info.
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.
In $dbh->DESTROY if !AutoCommit don't rollback&warning if count == 0.
FetchHashReuse attrib (=1 or ={}) copy from dbh to sth
and use to optimise fetchrow_hash
--- Changes that may affect driver authors
Add PERL_NO_GET_CONTEXT for multiplicity/threads?
force it for drivers?
And enable xsbypass in dispatch if possible.
Add a handle flag to say that the driver has a hash that maps error
codes into SQLSTATE values. The error event mechanism could check for
the flag and lookup the SQLSTATE value for the error from the hash.
Allow code hook as well. Maybe $dbh->{SQLSTATE_map} = code or hash ref
Add minimum subset of ODBC3 SQLSTATE values that should be supported
(and corresponding ODBC2 values?)
Add more macro hooks to Driver.xst: ping, quote etc.
Add dbh active checks to some more sth methods where reasonable.
Rework handle creation to use methods.
Maybe $h->new_child(\%override_attr)
dr::connect =>
$dbh = $drh->new_child(\%attr);
$dbh->connect(...)
& db::prepare =>
$sth = $dbh->new_child(\%attr);
$sth->prepare($statement) # once only?
$sth->close(???)# to 'reset' before a different prepare()?
need to think through prepare_cached and connect_cached
and relationship to preparse().
Sort out DBIcDBISTATE() and DBIS mess. dDBIS?
Make it cheap to get h from imp_xxh so only imp_xxh needs
to be passed around?
Define consise DBI<>DBD interface with view towards parrot.
note that parrot will use more method calls instead of
'sideways' hooks into DBIS and the driver C code.
DBI::DBD::Base module?
Update DBI::DBD with overview and (at least) recommend Driver.xst strongly.
Find XS drivers that don't use it and talk to authors.
Review drivers for handling of multiple result sets
to define common api for all.
$sth->more_results, maybe via $sth->become($sth2) later (or transplant/swap)
Re: DBI version 2 - 3 API change suggestions
Henrik Tougaard wrote:
> Better support for transactions. At some point
> I predict that the RDBMS vendors will come up
> with transaction handles, and we need somewhere
> to hold them - I foresee several concurrent
> transactions on the same database handle.
Not to mention nested transactions (or there emulation
via savepoints). The $dbh->{InTransaction} attribute
could indicate the nesting level in this case.
Steffen
Re: DBI version 2 (trunc of CHAR/VARCHAR) + another suggestion
On Wed, Jan 14, 2004 at 08:30:50AM +0100, Henrik Tougaard wrote: > Jonathan Leffler skrev: > > > H.Merijn Brand wrote: > >> Tim Bunce wrote: > A. What changes you'd like to see in the DBI API. > >> How about a new attribute: AutoTruncate > >> > >> so I can just put "grkaerhgkjehrgkaerhg" in a varcar/char (3) and it > >> will store "grk" > > > > That happens anyway in DBD::Informix - the DBMS does it. There is a > > warning raised, but the operation goes through anyway. Which DBMS > > doesn't do that automatically? Ingres? > > No, Ingres automatically truncates (and gives a warning, which > DBD::Ingres ignores, as I have nowhere to put it). > > Which triggers me to suggest somewhere to report warnings/messages > from the DBMS - it can't be $h->errstr, as that is used for (fatal) > errors. > How about $h->warning ? The long term plan is a callback. The short term plan is for drivers to call set_err() with a 0 for the err value but a meaningful errstr value (and optionally state). Applications that want to can then warn $h->errstr if defined $h->err; Tim.
Re: DBI version 2
On Wed 14 Jan 2004 04:03, Jonathan Leffler <[EMAIL PROTECTED]> wrote: > H.Merijn Brand wrote: > > Tim Bunce wrote: > >>>A. What changes you'd like to see in the DBI API. > > How about a new attribute: AutoTruncate > > > > so I can just put "grkaerhgkjehrgkaerhg" in a varcar/char (3) and it will > > store "grk" > > That happens anyway in DBD::Informix - the DBMS does it. There is a > warning raised, but the operation goes through anyway. Which DBMS > doesn't do that automatically? Ingres? Oracle and Unify -- H.Merijn BrandAmsterdam Perl Mongers (http://amsterdam.pm.org/) using perl-5.6.1, 5.8.0 & 633 on HP-UX 10.20 & 11.00, AIX 4.2, AIX 4.3, WinNT 4, Win2K pro & WinCE 2.11 often with Tk800.024 &/| DBD-Unify ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
RE: DBI version 2 (trunc of CHAR/VARCHAR) + another suggestion
Jonathan Leffler skrev: > H.Merijn Brand wrote: >> Tim Bunce wrote: A. What changes you'd like to see in the DBI API. >> How about a new attribute: AutoTruncate >> >> so I can just put "grkaerhgkjehrgkaerhg" in a varcar/char (3) and it >> will store "grk" > > That happens anyway in DBD::Informix - the DBMS does it. There is a > warning raised, but the operation goes through anyway. Which DBMS > doesn't do that automatically? Ingres? No, Ingres automatically truncates (and gives a warning, which DBD::Ingres ignores, as I have nowhere to put it). Which triggers me to suggest somewhere to report warnings/messages from the DBMS - it can't be $h->errstr, as that is used for (fatal) errors. How about $h->warning ? -- Henrik Tougaard, DBD::Ingres
Re: DBI version 2
H.Merijn Brand wrote: Tim Bunce wrote: A. What changes you'd like to see in the DBI API. How about a new attribute: AutoTruncate so I can just put "grkaerhgkjehrgkaerhg" in a varcar/char (3) and it will store "grk" That happens anyway in DBD::Informix - the DBMS does it. There is a warning raised, but the operation goes through anyway. Which DBMS doesn't do that automatically? Ingres? -- Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
RE: DBI version 2 - 3 API change suggestions
> From: Henrik Tougaard <[EMAIL PROTECTED]> > To: 'Tim Bunce' <[EMAIL PROTECTED]>, [EMAIL PROTECTED] > Subject: RE: DBI version 2 - 3 API change suggestions > > 1) > The Ingres Open/API has the possibility of doing > async db-access, ie starting a database request > and then at a later point in time either waiting > for it to complete or just checking if it is > completed yet. > It doesn't support threads (as far as I can see) > or at least the DBD-layer would have to do some > nifty footwork to support it. > I don't know what the DBI API for this kind of > functionality should be, but I think that it > should at least be considered. I'd 2nd that motion. DBD::Teradata has a FirstAvailable()/Realize() driver-specific API to do this. Presumably, ODBC's async model would the basis of DBI's API ? Threads are good, but given the number of sites I see running Perl 5.003, I reckon 5.8.2+ may not propagate quite as quickly as we might like... and some platforms don't support threads well/at all. Dean Arnold Presicient Corp. www.presicient.com
RE: CVS Repository for DBI (was RE: DBI version 2)
> > On Mon, Jan 12, 2004 at 05:53:00PM -0500, Jeff Urlwin wrote: > > > Plans: > > > > > > 1. Move DBI source code to http://sourceforge.net/projects/dbi/ > > > > Tim, > > > > On and Off I've thought about moving DBD::ODBC to a CVS > repository and > > searched for a tool that would take old tarballs of prior > releases and > > build the CVS repository from the tarballs. Maybe I don't need to, > > but it would be nice to have all the versions in a repository, each > > version/release tagged with the release. I just haven't > gotten around > > to it. Would you want to put your history in CVS or just > handle it as > > a "from here on" repository? > > Keeping the history would be nice - but it's not essential. > If I don't get it into CVS then I'd probably tar up my RCS > files and put them on CPAN (or at least backpan) "for posterity". Actually, from what I understand is that cvs import can do it (thanks to Jeffrey W. Baker, who saw this thread and commented to me, as per below: > > > > for release in DBD-ODBC-*; do > > cd $release; > > rev=`echo $release | perl -pe 's/^DBD-ODBC-//'`; > > cvsrev=`echo $rev | perl -pe 's/\./_/g`; > > cvs import -m "DBD::ODBC release $rev" dbd::odbc > > JURLWIN $cvsrev; > > cd ..; > > done; > > I didn't think IMPORT would do what I want. What I want is one > module, DBD-ODBC, which has all revisions in it. cvs import will do what you want. Each release will have its own revision tag as $cvsrev in the above script. I use this method to update my local copy when upstream sources release new revisions. Then I can merge in my local changes to the HEAD branch. In your case of course upstream and local will be the same. Naturally, someone wrote lengthy documentation for CVS already. Tracking third-party sources: http://www.loria.fr/~molli/cvs/doc/cvs_13.html#SEC98 > > > Also, I thought there was a CVS repository specifically > setup for perl > > modules, but I can't seem to find my reference to it. > Wouldn't that > > be "better"? > > Quite possibly. I don't know yet. I'm talking to Rob and Ask, > the perl.org admins, about it. Yes, Rob is the one that I had "discussed" this with a while back ... Either way, if you have DBI and DBD::Oracle hosted somewhere, I will follow suit. > > > Either way, I know I have been interested in setting up a > repository > > for DBD::ODBC, but just haven't found the time. I suppose > It's more > > useful for DBI and the more-used modules (or for the > modules with more > > regular contributions), than for DBD::ODBC. > > Perhaps. But having cvs can also lead to more regular > contributions. At least I certainly hope so :-) Me too. And, to answer your parrot question, I'll help in whatever way I can. Regards, Jeff
Re: DBI version 2
On Tue 13 Jan 2004 15:42, Tim Bunce <[EMAIL PROTECTED]> wrote:
> On Mon, Jan 12, 2004 at 04:59:38PM +0100, H.Merijn Brand wrote:
> >
> > > B. What changes you'd like to see in the DBD API
> > > (that's the DBI<->DBD interface).
> >
> > · All dbh handel offspring (statement handles and such)
> > available from the top level handle, for both status
> > and cleanup purposes
>
> Yeap. A weakref cache is on the list.
Good!
> > · Generic (DBI level) possibility to enable DBD debugging
> > I enabled $dbh->{DBDverbose}, to be the DBD counterpart
> > of DBI::trace (), and later renamed that - on Tim's
> > request - to $dbh->{uni_verbose}, but IMHO the usefulness
> > warrants a high level attribute
>
> A reorg of how TraceLevel works is on the list. You may remember me
> posting about that some months ago. Having an 8bit (max) trace level
> and then 16bits for specific topics and 8bits spare for driver topics.
I remember, but I wanted to stress this. It's important for me (and my
misbehaving database drivers - DBI and DBD do OK)
Example:
bug report: I cannot open blahdieblah, because I get an
undocumented error -273
answer: That seems to be a programming error. In this
case you can ignore it
> Tim.
--
H.Merijn BrandAmsterdam Perl Mongers (http://amsterdam.pm.org/)
using perl-5.6.1, 5.8.0 & 633 on HP-UX 10.20 & 11.00, AIX 4.2, AIX 4.3,
WinNT 4, Win2K pro & WinCE 2.11 often with Tk800.024 &/| DBD-Unify
ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
Re: DBI version 2
On Mon, Jan 12, 2004 at 04:59:38PM +0100, H.Merijn Brand wrote:
>
> > B. What changes you'd like to see in the DBD API
> > (that's the DBI<->DBD interface).
>
> · All dbh handel offspring (statement handles and such)
> available from the top level handle, for both status
> and cleanup purposes
Yeap. A weakref cache is on the list.
> · Generic (DBI level) possibility to enable DBD debugging
> I enabled $dbh->{DBDverbose}, to be the DBD counterpart
> of DBI::trace (), and later renamed that - on Tim's
> request - to $dbh->{uni_verbose}, but IMHO the usefulness
> warrants a high level attribute
A reorg of how TraceLevel works is on the list. You may remember me
posting about that some months ago. Having an 8bit (max) trace level
and then 16bits for specific topics and 8bits spare for driver topics.
Tim.
Re: CVS Repository for DBI (was RE: DBI version 2)
On Mon, Jan 12, 2004 at 05:53:00PM -0500, Jeff Urlwin wrote: > > Plans: > > > > 1. Move DBI source code to http://sourceforge.net/projects/dbi/ > > Tim, > > On and Off I've thought about moving DBD::ODBC to a CVS repository and > searched for a tool that would take old tarballs of prior releases and build > the CVS repository from the tarballs. Maybe I don't need to, but it would > be nice to have all the versions in a repository, each version/release > tagged with the release. I just haven't gotten around to it. Would you > want to put your history in CVS or just handle it as a "from here on" > repository? Keeping the history would be nice - but it's not essential. If I don't get it into CVS then I'd probably tar up my RCS files and put them on CPAN (or at least backpan) "for posterity". > Also, I thought there was a CVS repository specifically setup for perl > modules, but I can't seem to find my reference to it. Wouldn't that be > "better"? Quite possibly. I don't know yet. I'm talking to Rob and Ask, the perl.org admins, about it. > Either way, I know I have been interested in setting up a repository for > DBD::ODBC, but just haven't found the time. I suppose It's more useful for > DBI and the more-used modules (or for the modules with more regular > contributions), than for DBD::ODBC. Perhaps. But having cvs can also lead to more regular contributions. At least I certainly hope so :-) Tim.
RE: DBI version 2 - 3 API change suggestions
Tim Bunce skrev: > A. What changes you'd like to see in the DBI API. > > B. What changes you'd like to see in the DBD API > (that's the DBI<->DBD interface). > 1) The Ingres Open/API has the possibility of doing async db-access, ie starting a database request and then at a later point in time either waiting for it to complete or just checking if it is completed yet. It doesn't support threads (as far as I can see) or at least the DBD-layer would have to do some nifty footwork to support it. I don't know what the DBI API for this kind of functionality should be, but I think that it should at least be considered. 2) Better support for transactions. At some point I predict that the RDBMS vendors will come up with transaction handles, and we need somewhere to hold them - I foresee several concurrent transactions on the same database handle. 3) A way of getting all active children from a handle, eg all active $sth's in a $dbh. This is usefull when doing something potentially nasty to the $dbh (eg. committing or disconnecting) and you need to do something to all $sth's before - the only way out nnow is to error out with a suitable message to the user, and let them do whatever has to be done. > And, looking further ahead... > > C. How interested/motivated you are in working on a DBD for Parrot. I would love to find the tuits to do that. A good excuse to learn some parrot at last, after lurking on the mailinglists for years. -- Henrik Tougaard, DBD::Ingres.
Re: DBI version 2
On Mon 12 Jan 2004 16:59, "H.Merijn Brand" <[EMAIL PROTECTED]> wrote: > > A. What changes you'd like to see in the DBI API. > > Me and my colleages seem very satisfied :) How about a new attribute: AutoTruncate so I can just put "grkaerhgkjehrgkaerhg" in a varcar/char (3) and it will store "grk" -- H.Merijn BrandAmsterdam Perl Mongers (http://amsterdam.pm.org/) using perl-5.6.1, 5.8.0 & 633 on HP-UX 10.20 & 11.00, AIX 4.2, AIX 4.3, WinNT 4, Win2K pro & WinCE 2.11 often with Tk800.024 &/| DBD-Unify ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
CVS Repository for DBI (was RE: DBI version 2)
> Plans: > > 1. Move DBI source code to http://sourceforge.net/projects/dbi/ > Tim, On and Off I've thought about moving DBD::ODBC to a CVS repository and searched for a tool that would take old tarballs of prior releases and build the CVS repository from the tarballs. Maybe I don't need to, but it would be nice to have all the versions in a repository, each version/release tagged with the release. I just haven't gotten around to it. Would you want to put your history in CVS or just handle it as a "from here on" repository? Also, I thought there was a CVS repository specifically setup for perl modules, but I can't seem to find my reference to it. Wouldn't that be "better"? Either way, I know I have been interested in setting up a repository for DBD::ODBC, but just haven't found the time. I suppose It's more useful for DBI and the more-used modules (or for the modules with more regular contributions), than for DBD::ODBC. Regards, Jeff
Re: DBI version 2
Maybe a conformance test script for driver writers ? (or at least a template version...) Dean Arnold Presicient Corp. www.presicient.com
Re: DBI version 2
On Mon 12 Jan 2004 16:47, Tim Bunce <[EMAIL PROTECTED]> wrote:
> Plans:
>
> 1. Move DBI source code to http://sourceforge.net/projects/dbi/
>
> 2. Add a few dbi-dev people as developers who can modify the source.
>
> 3. Fork off DBI v1 for maintenance and start work on DBI v2.
>
> The changes I'm considering for DBI v2 are minor for the DBI API
> and only slightly more significant for the DBD API. This isn't a
> rewrite or a major change - just an opportunity for faster evolution.
>
> Compiled drivers will require at least a recompile to work,
> and all drivers will require some code changes to get the most out
> of DBI v2.
>
> I'll aim to post a list of the changes I'm proposing in a few days.
>
> This is just a 'heads up' message to get you all thinking.
>
> 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.
Me and my colleages seem very satisfied :)
> B. What changes you'd like to see in the DBD API
> (that's the DBI<->DBD interface).
· All dbh handel offspring (statement handles and such)
available from the top level handle, for both status
and cleanup purposes
· Generic (DBI level) possibility to enable DBD debugging
I enabled $dbh->{DBDverbose}, to be the DBD counterpart
of DBI::trace (), and later renamed that - on Tim's
request - to $dbh->{uni_verbose}, but IMHO the usefulness
warrants a high level attribute
> And, looking further ahead...
>
> C. How interested/motivated you are in working on a DBD for Parrot.
ENOTIME
> If you want to discuss any of those please start a new thread for each.
> Thanks.
>
> Happy New Year!
Same to you!
> Tim.
--
H.Merijn BrandAmsterdam Perl Mongers (http://amsterdam.pm.org/)
using perl-5.6.1, 5.8.0 & 633 on HP-UX 10.20 & 11.00, AIX 4.2, AIX 4.3,
WinNT 4, Win2K pro & WinCE 2.11 often with Tk800.024 &/| DBD-Unify
ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
Re: DBI version 2
On Mon, 12 Jan 2004, Tim Bunce wrote: > Plans: > > 1. Move DBI source code to http://sourceforge.net/projects/dbi/ > > 2. Add a few dbi-dev people as developers who can modify the source. Tim, Pending permission from my employer, I'd like to volunteer as maintainer for the DBI::Proxy. Let me know your thoughts? If you think it makes sense, I'll start the process of getting approval (things are a bit tense with regard to Open Source participation, as you might imagine). Steve -- Steven N. Hirsch tie-line: 446-6557 ext: 802-769-6557 Staff Engineer Methodology Integration Team ASIC Product Development IBM Microelectronics
