Table Definition - Better binary support w/MySQL

I work with Rails purely on MySQL, so I can't say how this is handled
with Oracle, DB2, etc. However, when you create table columns with
rails, you may specify :string, :text, or :binary, with or without
:limits.

In the case of :string, setting the limit uses varchar(N) appropriately
up until 65,532, at which point it switches over to mediumtext as it
should. With :text, it will use tinytext up until a limit of 255 - if
you specify :limit => 500, for instance, it is smart enough to switch
to text.

However, when using :binary, limits of under 255 get tinyblob, then
switch over to blob. What I would propose is that with the :binary
type, when limits are specified, VARBINARY (equivalent to VARCHAR)
should be used, as it saves space, and is the most appropriate type.

However, when using :binary, limits of under 255 get tinyblob, then
switch over to blob. What I would propose is that with the :binary
type, when limits are specified, VARBINARY (equivalent to VARCHAR)
should be used, as it saves space, and is the most appropriate type.

Sounds fine by me. Has varbinary always been around?

It's been around since at least 3.23[1].

-ryan

[1]: http://dev.mysql.com/doc/refman/4.1/en/binary-varbinary.html

> Sounds fine by me. Has varbinary always been around?

It's been around since at least 3.23[1].

I learn something every day. This is great. Please do patch.

DHH wrote:

I learn something every day. This is great. Please do patch.

I recently made to windows to mac switch at home, and I'm having
trouble with darwin/macports/rails at the moment. Nonetheless, at work
I froze rails, made the change, and the results seem to be
satisfactory.

Type 'varbinary' gets used appropriately for limits up to 65,532, then
gets switched over to mediumblob.

I don't really have time to write unit tests for this or do the whole
process at work, but can whenever I get my macports situation fixed at
home.

Here's a unix diff of my changed
activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:

109c109
< :binary => { :name => "varbinary" },

Hey Sean,
can you enter this patch here:

http://dev.rubyonrails.org/

posting it in google groups probably won't see your change make it into
the code base.

Adam