[ruby-dbi-users] Proposing patch for sqlite3 driver (very, very long)
erik at hollensbe.org
Thu Oct 2 02:30:57 EDT 2008
On Wednesday 01 October 2008 17:54:10 KUBO Takehiro wrote:
> On Wed, Oct 1, 2008 at 8:55 PM, Jarl Friis <jarl at gavia.dk> wrote:
> > So all-in-all I consider my proposal at least as good as the current
> > implementation. Yet I agree that it could be nice with an even better
> > solution. The low-level pg-driver accepts a DateTime, could that be used
> How about the following solution?
> 1. execute 'SHOW DateStyle;' just after establishing a connection.
> 2. scan the result to check the date style.
> - ISO
> - SQL
> - PostgreSQL
> - German
> 3. If the date style is "SQL" or "PostgreSQL", scan it again to check
> substyle - European
> - NonEuropean or US
> 4. make strftime formats according to the date style and substyle and
> set them to the
> connection as instance variables.
> 5. convert Datetime, Date and Time to string by the strftime formats.
This is very close to what I was thinking, but I didn't have the specifics
down. Thanks, Kubo.
The problem is still a problem however, and that's due to how coarse DBI's
handling of inbound parameter conversion (dbh.do, sth.execute, etc) is done.
I knew this was a less than optimal solution, but I didn't realize it would
be such an impediment for issues like this. It looks like DBI 0.4.1 needs to
be released with a solution to this problem.
The rest of this email is damn long; a full description of what I think the
fundamental issues are and some solutions... It's probably not an easy read
even for those interested, and there's no code; this is mostly brainstorming
at this point. I hope to code up a prototype in the next day or two to
further demonstrate this. However, I would appreciate all feedback on the
The problem is threefold:
1) Part of the benefit of the conversion map is that it can be redefined for
user-specific needs. Redefining it at this point would potentially clobber
any user-defined modifications.
2) Inbound conversion maps (outbound is significantly different and actually
depends on the data being returned, in combination with the mappings between
database type and ruby type) are static and connection agnostic. Therefore,
while unlikely, the possibility that in this scenario a conversion would get
overwritten on a second connection to a second database with a different date
3) This one was evidenced to me last night by the mysql bug reported by
Georgios Moschovitis; He was using Time.now to represent a full timestamp.
While Time can do this nearly as well as DateTime (or better, depending on
who you talk to and what you need), DateTime does not inherit from Time, and
the rather rudimentary solution I'm using to determine what goes in causes
him to get only %H:%M:%S instead of the full timestamp.
What I'd like to do at this point is discuss a few options. In RFC parlance,
here are what I consider the MUST's:
1) Types are not only polymorphic between DBDs, but between database
connections within the DBD
2) A ruby type may represent multiple database types
3) No limitation should be placed on the user to use the predefined type; the
types should merely make the mundane possible and not limit the user from
doing the extravagant with their own custom types. I'm specifically thinking
of postgres, but in reality there are tons of specialized types (and probably
a few standard ones) we do not support among all the known supported
Before I go into possible solutions, I should note that nothing is set in
stone, feel free to reject all these ideas and propose your own solution,
just keep in mind the 3 things above and that solutions have to serve a
multitude of consumers, not a specific subset of them; DBI is deliberately
defined to mandate as little as possible to allow people to work as they feel
Now, for what I consider as the SHOULD's (again, RFC parlance):
1) It would be ideal if inbound (currently the majority of DBI::TypeUtil) and
the outbound (DBI::Type::*) were unified, if only in namespace and API. This
would make a number of external interfaces much easier to handle.
2) Binding should be able to take extensive advantage of any approach; one
thing that has been requested a number of times is outbound parameter
binding, and part of that would have to take advantage of the type system
3) It should not be rocket science to write a type handling class, even if the
standard ones are necessarily complex.
Now that the bulleted lists are gone... Here's an idea.
To summarize (ok, I promise, last list):
- DBI::Type contains an interface inherited from by all types, which contains
methods intended to dispatch to the appropriate conversion based on
ColumnInfo data. These conversions cover both inbound and outbound
- DBI supplies baseline types and expects DBDs to provide any variants from
those baseline types, and those variants should inherit from their DBI
counterparts, if any, or at worst DBI::Type itself.
- dbh holds a conversion map of database types to DBI::Type::* classes. This
map should be mutable by the end user.
The long-winded version:
The DBI::Type::* namespace could be extended by DBD, ergo,
DBI::Type::Timestamp could be subclassed as DBI::Type::MySQL::Timestamp, and
it would inherit from a base DBI::Type::Integer implementation, similar to
how almost all of the DBI::Type classes currently inherit from
DBI::Type::Null. These classes would be the default type classes for each
DBD, would be defined *with* the DBD (with the base parent classes provided
by DBI as a fallback). These types would be mapped to the database handle at
connection time, and be mutable; the types will be defined in the DBD only
for isolation and maintenance purposes.
These classes, if not intended to fall back to their parent, would implement
at least two class methods, #to_type and #from_type, which would convert to a
ruby type and from it to a database-compatible representation, respectively.
These would be generic (in the context of the DBD) routines intended for
execution only if a more specific conversion fails to exist.
DBI::Type, which all DBI::Type::* classes would inherit from, would implement
#parse and #coerce, which would act as dispatchers.
In #parse's case, it would take both the object and the corresponding
ColumnInfo object for the column; the type_name (which I'm fairly certain is
provided by all DBDs) would be used as a method name to be called on for a
specific conversion of the object, otherwise, the fallback would be used (in
DBI::Type::Mysql::Integer's case this would be D::T::M::Integer.to_type, or
more likely D::T::Integer.to_type, as mysql's integers aren't anything
special). To get more specific, DBI::Type::Pg::Timestamp could overload
#parse to delegate to 'dmy', 'mdy', 'iso' and so methods, which would
properly parse into a DateTime object. this data could be gathered with the
ColumnInfo per-execute (allowing for situations where it's been changed) or
on connection. Likewise, other Timestamp classes could use the base #parse to
delegate to methods that handle types with the timezone and without, which
are fairly common, and even handle integer types, for those who prefer to
store epochs and whatnot.
#coerce would be similar to this in function, but getting the information used
to delegate would be different; the obvious problem being that we don't have
the information at the point where coercion needs to happen, or at least,
most of it. The dbh mapping will need to contain an optional default
conversion that is used for #coerce, and a convenient way for users to alter
that conversion from the pre-packaged ones. cloning the class to something
anonymous w/ a change to the default conversion should work, this can be done
in DBI::Type as well.
I'll try and have prototypes for this sometime tomorrow to clarify confusion,
but please, poke/ask/criticize/flame, especially if there are faults that I
haven't accounted for. Understanding how the DBI::Type namespace currently
works will greatly help your comprehension; it's documented.
More information about the ruby-dbi-users