Forum: Rails-core (closed, excessive spam) Mysql integer data types

As I could not get a definite answer at lighthouse
(http://rails.lighthouseapp.com/projects/8994/ticke...),
whether my assumption about rails' incorrect handling of mysql integer
data types was right or not, I now turn to this mailing list.

About a month ago I wrote a patch posted here:
http://rails.lighthouseapp.com/projects/8994/ticke...

A couple of weeks later Jeremy Kemper commited this:
http://github.com/rails/rails/commit/4498aad4acda0...

However, I'm personally not sure he has done everything correct.

Example (taken from Jeremy's commit):
when 1; 'tinyint'
when 2; 'smallint'
when 3; 'mediumint'

For instance, doesn't this mean that rails will choose mediumint as data
type for storing integers with a length of 3? That should be smallint!
Smallint holds (if signed) -32768 to 32767 so it would easy hold an
integer with length of 3 and even 4. Using mediumint here just wastes
disk space and increase mysql memory consumption without any reason.

As I understand rails and mysql data types, it should rather be
something like (as I've written in my patch):

when 0..2; 'tinyint'
when 2..4; 'smallint'
when 5..6; 'mediumint'
when 7..9; 'int'
when 9..20; 'bigint'

Or am I missing something here? If I am - sorry for my newbie-ism, this
is my first time trying to contribute to open source.

- Rasmus

On N, 2008-07-10 at 16:17 +0200, Rasmus Nielsen wrote:
> 
> However, I'm personally not sure he has done everything correct.
> 
> Example (taken from Jeremy's commit):
> when 1; 'tinyint'
> when 2; 'smallint'
> when 3; 'mediumint'

These are byte lengths, not number of digits in decimal. So 3 has to be
-8388608 to 8388607, which is exactly what medium int is:
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Indeed Rails used to treat those limits as the number of decimal places
for the mysql adapter, but this was changed so all adapters would use
the same definition of integer :limit.

Ofcourse means that whatever migrations you run on the new code may need
to be changed if they specify limits for integer (though if they do you
will likely get a longer integer, not shorter, so you may not even
notice that anything is wrong).

(btw. for :decimal scale and precision still refer to the number of
decimal places, this change only affects integer :limit interpretation)

> As I understand rails and mysql data types, it should rather be 
> something like (as I've written in my patch):
> 
> when 0..2; 'tinyint'
> when 2..4; 'smallint'
> when 5..6; 'mediumint'
> when 7..9; 'int'
> when 9..20; 'bigint'
This is exactly what the code used to be like.


Regards,

--
Tarmo Tänav <tarmo@itech.ee>

Thank you for that clarification.