[ruby-dbi-users] Proposing patch for sqlite3 driver (very, very long)

[Erik Hollensbe]

On Wednesday 01 October 2008 17:54:10 KUBO Takehiro wrote:
> Hi,
>
> 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 
subject.

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 
format.

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 
databases.

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 
appropriate.

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 
conversions.

- 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.

-Erik