MySQL and Default values

I got my first taste today of dealing with the default values in MySQL.
It seems that Rails loads the default values for fields based on the
database (seems reasonable, specify in one place and let if flow
through). The problem is that if I say NOT NULL and do not specify a
default value, MySQL inserts a default value anyway. See

http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

for all the details. Obviously Rails can’t fix this since it is an
oddity with MySQL (that seems to be fixed in >5.0.2). Rails has no way
of knowing if the developer meant “not null with no default” or “not
null with a default of an empty string”. So it seems to have taking the
attitude of just doing what the database tells it. I guess the idea is
that the developer can override this if the developer knows something
more about the field. My problem is that I really don’t like invalid
data sneaking through. But I don’t want to put a bunch over overrides
all over my models either.

I would rather Rails assume I meant “not null with no default” when it
sees “not null with a default of empty string” since it seems so rare
that someone would actually want “not null with a default of empty
string” compared to just “not null with no default”. For integers you
have a much more common case of wanting “not null with a default of 0”
but I still feel that it is less common than an integer field where you
have “not null with no default”. So in effort to fix this problem I have
developed the following code which I place in a file in my lib/
directory and then require it from my environment.rb.

module ActiveRecord
module ConnectionAdapters
class MysqlColumn < Column
def default
return @default if 50002 <= sprintf(’%02d%02d%02d’,
*ActiveRecord::Base.connection.send(:version)).to_i
return @default if name.to_sym == :lock_version
case type
when :integer, :float, :decimal
@default == 0 ? nil : @default
when :datetime, :timestamp
@default == ‘0000-00-00 00:00:00’ ? nil : @default
when :date
@default == ‘0000-00-00’ ? nil : @default
when :time
@default == ‘00:00:00’ ? nil : @default
when :text, :string
@default == ‘’ ? nil : @default
else
@default
end
end
end
end

class Base
class << self
alias db_columns :columns
def columns
db_columns.collect do |c|
c.instance_variable_set ‘@default’,
default_for(c.name, c.default)
c
end
end
def default_for(field, cur_default)
cur_default
end
end
end
end

This code will try to guess what the developer meant only if we are
running MySQL < 5.0.2. It makes a special exception if the field is
“lock_version” since this is commonly an integer with a default of 0. In
addition I have a hook that a model can override to correct my guessing
if needed. Basically you override “default_for” in your model. The
method gets two arguments. The field name and the current guessed
default (or the database default if no guessing took place). This allows
a model to easily correct the database or my guessing if need be.

After setting up this code Rails will hopefully see the database as it
was specified not as MySQL interpreted my schema.

I am thinking of packaging this code up as a plugin since it seems to
work for me (just wrote it today so it is very alpha) but I was
interested in getting thoughts from others to see if another solution
might be more elegant and involve less guessing.

I look forward to feedback.

Eric

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs