I am working on a project for which I have to store unsigned integer
values up to 2^64 - 1. Even bigger values would be valuable but 2^64-1
is the minimum required.
My Rails project uses a MySQL database. And guess what ? It supports
unsigned bigint data type whose maximum value is 2^64 - 1.
Sounds good, except that Rails migration doesn’t handle unsigned
integer datatypes very well. After some googling, I came up with a
solution to this issue:
t.column :value, ‘bigint unsigned’
Fine. Both my test and development databases are now ready to store
values from 0 to 2^64 - 1. Really ? Yes they are. Manual SQL requests
proved it.
Back to Rails writing a unit test to check that one of my calculation
method returns the expected result. This result is part of my fixtures
and is defined as follows:
And here comes the trouble : my model’s function returns the expected
value (2^64 - 1) but the test fails nevertheless since the value
stored in my test database is 9223372036854775807 (which equals 2^63 -
1, i.e. the max value for a signed bigint) instead of
18446744073709551615 (which equals 2^64 - 1, i.e. the max value for an
unsigned bigint) which I actually typed in in my fixtures.
Back to MySQL Query Browser to check my database and it appears that
running my tests changed my column datatype from unsigned bigint to
signed bigint !?
Has anyone ever heard of such a behaviour ? What is it due to and how
could I finally use unsigned bigint in my app and its tests ?
I tried to persist values as big as 2^64 - 1 through the “ruby script/
console” utility on my development database and it works as a charm.
Which is good news but confuses me even more…
Back to MySQL Query Browser to check my database and it appears that
running my tests changed my column datatype from unsigned bigint to
signed bigint !?
Has anyone ever heard of such a behaviour ? What is it due to and how
could I finally use unsigned bigint in my app and its tests ?
I imagine it is because of the ruby schema dumper not handling bigints
properly. Have you tried setting the schema dumper to :sql (in
environment.rb) ?
I will opt for the string column storage solution since I definitely
need to have 64-bit unsigned integers. Moreover, that would enable my
applicaton to handle even larger integers would that become a
requirement, which is very likely.
Regarding my app, each value will be converted to int in the following
fashion, making the conversion transparent and fluid:
def after_initialize
self.value = value.to_i
end
No conversion to string is required while persisting the instance as
Rails take care of it by its own. Not that bad.
Thanks for your help Fred and Jeffrey
integer datatypes very well. After some googling, I came up with a
solution to this issue:
t.column :value, ‘bigint unsigned’
Fine. Both my test and development databases are now ready to store
values from 0 to 2^64 - 1. Really ? Yes they are. Manual SQL requests
proved it.
In creating the test DB, the unsigned is lost. Rails doesn’t support
unsigned.
Which is good news but confuses me even more…
Again, the development DB is ‘bigint unsigned’, the test DB is ‘bigint’
(defaulting to signed). Try your console example again with:
Ruby script/console test
How to fix it in a Rails way fashion? I don’t know, I simply switched
from 64
bit hash codes to 63 bit ones. The doubling of collision frequency is
not a
problem. If you really have to have 64 unsigned integers, store them in
a
string column type and convert.
I will opt for the string column storage solution since I definitely
need to have 64-bit unsigned integers. Moreover, that would enable my
applicaton to handle even larger integers would that become a
requirement, which is very likely.
Regarding my app, each value will be converted to int in the following
fashion, making the conversion transparent and fluid:
def after_initialize
self.value = value.to_i
end
You could make this slightly more Railsy with composed_of, too.
No conversion to string is required while persisting the instance as
Rails take care of it by its own. Not that bad.
Thanks for your help Fred and Jeffrey