Hi, 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: dummy_object_in_fixture: id: 1 value: <%= 2**64 - 1 %> 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... Thanks for your help
On Oct 29, 1:18 pm, Foon <nicolas.gai...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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) ? Fred> > 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... > > Thanks for your help
Quoting Foon <nicolas.gaiffe-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > Hi, > > 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. >In creating the test DB, the unsigned is lost. Rails doesn''t support unsigned.> > 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... >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. Jeffrey
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
Foon wrote:> 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 > endYou 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 JeffreyBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.