TINYINT(8) -> BOOLEAN problem

[Leslie Viljoen]

Is this the best place to file bugs (or ask questions about)
activerecord-sqlserver-adapter? The adapter is turning TINYINT columns
(even 8 bit ones) into BOOLEAN values and I need to turn that off.

I detailed the problem here but had no response: http://railsforum.com/viewtopic.php?id=34814
There is a bug tracker on Rubyforge but so little activity that I
doubt it is being used.

Looking through the code, I see that column names are selected using
without_type_conversion, which is what would be needed for normal
selects - but doing all selects that way would surely break the
adapter's handling of other types.

[Ken Collins]

> Is this the best place to file bugs (or ask questions about)
> activerecord-sqlserver-adapter? The adapter is turning TINYINT columns
> (even 8 bit ones) into BOOLEAN values and I need to turn that off.

It is the right place!

I've been pretty busy lately, but this is the best place to get help
from all around. Have you made a ticket (github issues) yet and or
tried to write a test patch to confirm/solve the issue?

> I detailed the problem here but had no response: http://railsforum.com/viewtopic.php?id=34814
> There is a bug tracker on Rubyforge but so little activity that I
> doubt it is being used.

Correct. No Rubyforge usage other than just mirroring the gem and no
lighthouse anymore. Please use the github issues page.

> Looking through the code, I see that column names are selected using
> without_type_conversion, which is what would be needed for normal
> selects - but doing all selects that way would surely break the
> adapter's handling of other types.

I'll take a look in a bit. If you look thru some of the recent commit
history, you can see where I recently put in some sanity tests for
biginit too where another user was having issues. This would show you
a good place to add tests and begin a patch for tinyint too.
Actually... here

http://github.com/rails-sqlserver/2000-2005-adapter/commit/0c6ba87f9289c78e29aa32bd3af20f94ea01d449

FYI, if I look search the ActiveRecord source, it seems there is a
change log mentions that make all tinyint(1) columns act like boolean.
And the mysql adapter has support for parsing the simplified type,
back and forth for all tinyint sizes. We can do this too, should be
real easy and just a few implementation lines. Wanna take a stab at
it, using the mysql adapter as a guide? If not I can get to this
sometime soon.

- Ken

[Leslie Viljoen]

> I'll take a look in a bit. If you look thru some of the recent commit  
> history, you can see where I recently put in some sanity tests for  
> biginit too where another user was having issues. This would show you  
> a good place to add tests and begin a patch for tinyint too.  
> Actually... here
>

> http://github.com/rails-sqlserver/2000-2005-adapter/commit/0c6ba87f92...

>
> FYI, if I look search the ActiveRecord source, it seems there is a  
> change log mentions that make all tinyint(1) columns act like boolean.  
> And the mysql adapter has support for parsing the simplified type,  
> back and forth for all tinyint sizes. We can do this too, should be  
> real easy and just a few implementation lines. Wanna take a stab at  
> it, using the mysql adapter as a guide? If not I can get to this  
> sometime soon.

Ah, I'll look at the logs for the tinyint/boolean change. I did try
forcing the code to use DBI's convert_types=false but all the debug
code I put in showed DBI returning booleans in the selects. So if I
can't get DBI to return the numbers, I can't make activerecord
interpret them correctly.

I'll look at the logs for more insight, thanks for the response!

[Ken Collins]

I'd stay away from DBI stuff if possible. That's too low level and
couples the adapter to it. The big question is if you add it to the
special types in the test case schema... can you get the columns
objects to reflect their correct #sql_type... IE... as long as SQL
Server knows what the underlying type is (and it should) then ruby can
cast via the normal abstract adapter means. Unless DBI is munging it.

- Ken

I'll hop on this soon too.

[Adam Meehan]

The typecast of tinyint to boolean occurs in DBI
http://github.com/erikh/ruby-dbi/blob/b612f864a446f01bee84ff9acc4e61656c1d8e07/lib/dbi/typeutil.rb#L64

Its bitten me badly before on a legacy database. It changed from
version 0.2 to 0.4. Patch that method in your project and if its
backward compatible see if the DBI guys will take it.

Adam

[Adam Meehan]

The typecast of tinyint to boolean occurs in DBI
http://github.com/erikh/ruby-dbi/blob/b612f864a446f01bee84ff9acc4e61656c1d8e07/lib/dbi/typeutil.rb#L64

Its bitten me badly before on a legacy database. It changed from
version 0.2 to 0.4. Patch that method in your project and if its
backward compatible see if the DBI guys will take it.

Adam

On Oct 10, 7:31 am, Ken Collins <k...@metaskills.net> wrote:

[Leslie Viljoen]

On Oct 12, 12:14 pm, Adam Meehan <adam.mee...@gmail.com> wrote:
> The typecast of tinyint to boolean occurs in DBIhttp://github.com/erikh/ruby-dbi/blob/b612f864a446f01bee84ff9acc4e616...

>
> Its bitten me badly before on a legacy database. It changed from
> version 0.2 to 0.4. Patch that method in your project and if its
> backward compatible see if the DBI guys will take it.

Ah, thanks for that link!

Yes, if you look at my original question you can see DBI changing
things:
http://railsforum.com/viewtopic.php?id=34814
..unless DBI::convert_types is set to false.

I see the sqlserver-adapter playing around with that setting in order
to load column names - so it is possible to get the right values out
of DBI. It's just that I would expect the setting to have effects on
other types, affecting other conversions and breaking a lot of
existing code. Not sure.

I have mailed erikh, if he agrees I will try to patch it (or is this
not technically a regression that needs rolling back?).

[Leslie Viljoen]

Aha!

Here is the change (back then it was in dbi.rb):
http://github.com/erikh/ruby-dbi/commit/e7f7fcbff57f79da9ff5b99c702ea9d77c06cedb

The change was on April 25 2008. I will try a patch where tinyint it
just moved up to the integer department.

Les

[Leslie Viljoen]

I see Rails 2.3.4 is using DBI 0.4.1.
Here's the patch and it seems to work for my very simple test -
haven't run any other tests.
For this file on my system: /usr/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/
dbi/typeutil.rb

--- typeutil.rb 2009-10-13 09:25:02.000000000 +0200
+++ typeutil_les.rb 2009-10-13 09:27:50.000000000 +0200
@@ -63,13 +63,13 @@
#
def self.type_name_to_module(type_name)
case type_name
- when /^int(?:\d+|eger)?$/i
+ when /^int(?:\d+|eger)?$/i, /^tinyint$/i
DBI::Type::Integer
when /^varchar$/i, /^character varying$/i
DBI::Type::Varchar
when /^(?:float|real)$/i
DBI::Type::Float
- when /^bool(?:ean)?$/i, /^tinyint$/i
+ when /^bool(?:ean)?$/i
DBI::Type::Boolean
when /^time(?:stamp(?:tz)?)?$/i
DBI::Type::Timestamp

[Leslie Viljoen]

Track progress on this issue here:
http://rubyforge.org/tracker/index.php?func=detail&aid=27283&group_id=234&atid=967

[Ken Collins]

Github issues please :)

[Ken Collins]

FYI, I'm working on the fix for this. There is no need to patch DBI
unless it is useful for all DB's. Currently on the adapter we alias
method chain the type_name_to_mudule method for a few other reasons
too. See here.

http://github.com/rails-sqlserver/2000-2005-adapter/blob/master/lib/active_record/connection_adapters/sqlserver_adapter/core_ext/dbi.rb#L64

This is what I will be basing my fix on.


- Ken

[Ken Collins]

This will most likely be in a new version today. All tests passing.
Want to try it out?

http://github.com/rails-sqlserver/2000-2005-adapter/commit/dfc57d3d68d10e58e7654126669ac5570d7dbe92