ActiveRecord uses confusing defaults

jeroen · September 8, 2006, 1:47pm

Hi!

I have an AR model called Submission
In my submissions table (mysql 5) I have a column called postcode
(varchar, not null) and a column street (varchar, not null)

s = Submission.new
s.postcode # => 0

now I would expect this to return nil

s.street # => ‘’
I would expect this to return nil

Is there a reason for this?

Jeroen

jeroen · September 8, 2006, 2:42pm

On 9/8/06, Jeroen H. [email protected] wrote:

now I would expect this to return nil

s.street # => ‘’
I would expect this to return nil

Is there a reason for this?

Jeroen

It goes by the defaults set in your database. What’s your schema look
like?

Here’s a snippet from one of my apps:

create_table “contents”, :force => true do |t|
t.column “article_id”, :integer
t.column “comments_count”, :integer, :default => 0
end

Content.new.article_id
=> nil
Content.new.comments_count
=> 0

–
Rick O.
http://weblog.techno-weenie.net
http://mephistoblog.com

jeroen · September 8, 2006, 2:44pm

Jeroen H. wrote:

s.postcode # => 0

now I would expect this to return nil

s.street # => ‘’
I would expect this to return nil
Varchar columns are mapped to String and Ints are mapped to Integer. In
ruby, 0, “” and false are not the same as nil (like in PHP for example).

Therefore, to check if the return of a method is blank, you can do this:

if s.street.blank? # if street is 0, “” or false
puts “street is blank”
end

I think that rails also has a neat feature where you can do this:

if s.street?
puts “street is blank”
end

Incidentally, ‘street’ is also a method, not a variable so as far as I
know it would never be nil.

Hope that helps,

Steve

jeroen · September 8, 2006, 3:27pm

Rick O. wrote:

On 9/8/06, Jeroen H. [email protected] wrote:

now I would expect this to return nil

s.street # => ‘’
I would expect this to return nil

Is there a reason for this?

Jeroen

It goes by the defaults set in your database. What’s your schema look
like?

Here’s a snippet from one of my apps:

create_table “contents”, :force => true do |t|
t.column “article_id”, :integer
t.column “comments_count”, :integer, :default => 0
end

Content.new.article_id
=> nil
Content.new.comments_count
=> 0

Hey that’s interesting.

t.column "email", :string, :default => "", :null => false
t.column "fname", :string, :default => "", :null => false
t.column "lname", :string, :default => "", :null => false
t.column "street", :string
t.column "city", :string, :default => "", :null => false
t.column "shirt_type", :string, :default => "m", :null => false
t.column "shirt_size", :string, :default => "l", :null => false
t.column "shirt_text", :text, :default => "", :null => false
t.column "processed_at", :datetime
t.column "approved_at", :datetime
t.column "created_at", :datetime, :null => false
t.column "updated_at", :datetime, :null => false
t.column "housenr", :integer, :default => 0, :null => false
t.column "housenr_ext", :string

I have all these default that I did not put in there myself. Look at one
of my migrations files:

  t.column "email", :string, :null => false
  t.column "fname", :string, :null => false
  t.column "lname", :string, :null => false
  t.column "address", :string, :null => false
  t.column "postcode", :string, :null => false, :limit => 6
  t.column "city", :string, :null => false
  t.column "shirt_type", :string, :default => "m", :null => false
  t.column "shirt_size", :string, :default => "l", :null => false
  t.column "shirt_text", :text, :null => false
  # processed = sent for fulfillment
  t.column "processed_at", :datetime
  t.column "approved_at", :datetime
  # timestamps
  t.column "created_at", :datetime, :null => false
  t.column "updated_at", :datetime, :null => false

Do I have to explicitly set defaults to nil (null?)

Jeroen

jeroen · September 8, 2006, 4:08pm

Jeroen H. wrote:

Rick O. wrote:

On 9/8/06, Jeroen H. [email protected] wrote:

now I would expect this to return nil

s.street # => ‘’
I would expect this to return nil

Is there a reason for this?

Here’s the info from MySql

So I’m pretty sure I’m never using “” as my defaults yet a rake
db:schema:dump thinks that I am (see previous post)

Please not that I am not using the native mysql gem as that gave me even
more obscure problems on Win32.

Jeroen

jeroen · September 8, 2006, 6:17pm

Andrew S. wrote:

I’m not sure if this is what you are after (haven’t woken up), but I
wrote a
little plugin that would nil empty strings before saving the record.
I’ve
been using this for over a year without issue.

Yeah I’m using something similar myself, very useful. But newly created
objects just shouldn’t return 0 or “” when I have no such defaults in my
database. Either I’m doing something wrong or there’s some oddity in AR.

Could you, just as a test, create this table a a corresponding empty
model and tell me what value Submission.new.postcode returns?

CREATE TABLE submissions (
id int(11) NOT NULL auto_increment,
email varchar(255) NOT NULL,
fname varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
street varchar(255) NOT NULL,
housenr int(11) NOT NULL,
housenr_ext varchar(255) default NULL,
postcode varchar(6) NOT NULL,
city varchar(255) NOT NULL,
shirt_type varchar(255) NOT NULL default ‘m’,
shirt_size varchar(255) NOT NULL default ‘l’,
shirt_text text NOT NULL,
processed_at datetime default NULL,
approved_at datetime default NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Jeroen

p.s. I won’t be reading this forum again untill tomorrow probably

jeroen · September 8, 2006, 11:27pm

street varchar(255) NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I will run this test tomorrow morning and let you know.

–
Andrew S.

jeroen · September 8, 2006, 5:42pm

I’m not sure if this is what you are after (haven’t woken up), but I
wrote a
little plugin that would nil empty strings before saving the record.
I’ve
been using this for over a year without issue.

**Note This is an extract of my model_utils plugin so double check it
for
errors:

module ModelUtils
def self.included(base)
base.send :include, Stone::ModelUtils::InstanceMethods

  base.before_validation{|model|
    model.nil_empty_fields
  }
end

model InstanceMethods
  def nil_empty_fields
    self.attributes.each do |key, value|
      if not value.nil? and value.kind_of?(String)
        self[key] = nil if value.length == 0
      end
    end
  end

end #end instance methods

end

ActiveRecord::Base.send :include, ModelUtils

Hope this helps…

–
Andrew S.

jeroen · September 9, 2006, 12:55am

Hey that’s interesting.

t.column "email", :string, :default => "", :null => false

So my point was, setting the column default to “” means it’s default
upon AR model initialization. If you set this up with :null => false
(or NOT NULL), your DB probably won’t let you save NULL to the
attribute. Quit setting these confusing defaults, and you’ll stop
seeing them in AR.

–
Rick O.
http://weblog.techno-weenie.net
http://mephistoblog.com

jeroen · September 9, 2006, 2:24pm

Jeroen wrote:

class AddTestTable < ActiveRecord::Migration
def self.up
create_table “contents”, :force => true do |t|
t.column “email”, :string, :null => false
end
end

def self.down
drop_table “contents”
end
end

$ script/generate model Content

$ script/console

Loading development environment.

Content.new.email
=> “”

I just dont understand why it defaults to “” and not to nil. Nowhere did
I set the default to “” in my migrations file.

It has to default to something.

You’ve prevented it from defaulting to NULL (:null => false). So for an
string field, it defaults to ‘’; for a numeric field it defaults to 0.

If you WANT to assign nil to a field, you need to remove the “:null =>
false” from your DB specifications.

–Al Evans

jeroen · September 9, 2006, 9:22pm

Al Evans wrote:

Jeroen wrote:

class AddTestTable < ActiveRecord::Migration
def self.up
create_table “contents”, :force => true do |t|
t.column “email”, :string, :null => false
end
end

def self.down
drop_table “contents”
end
end

$ script/generate model Content

$ script/console

Loading development environment.

Content.new.email
=> “”

I just dont understand why it defaults to “” and not to nil. Nowhere did
I set the default to “” in my migrations file.

It has to default to something.

Well in Mysql I can set an integer column to NOT NULL and not specify
a default value. It’s a valid SQL schema. I think AR should copy this
behaviour and not force a default zero, but I seem to stand alone in
this. It’s no biggy really, just wanted to see what the idea behind this
AR behaviour was. Thanks!

Jeroen

jeroen · September 9, 2006, 12:27pm

Rick O. wrote:

Hey that’s interesting.
t.column "email", :string, :default => "", :null => false
So my point was, setting the column default to “” means it’s default
upon AR model initialization. If you set this up with :null => false
(or NOT NULL), your DB probably won’t let you save NULL to the
attribute. Quit setting these confusing defaults, and you’ll stop
seeing them in AR.

Okay I’m almost starting to feel stupid here, but I am not setting
these defaults myself. Please take one more look at the stuff below.

Just ran another test with a blank database:

class AddTestTable < ActiveRecord::Migration
def self.up
create_table “contents”, :force => true do |t|
t.column “email”, :string, :null => false
end
end

def self.down
drop_table “contents”
end
end

$ script/generate model Content

$ script/console

Loading development environment.

Content.new.email
=> “”

I just dont understand why it defaults to “” and not to nil. Nowhere did
I set the default to “” in my migrations file.

Now if I do $rake db:schema:dump here’s what the generated schema looks
like:

This file is autogenerated. Instead of editing this file, please use

the

migrations feature of ActiveRecord to incrementally modify your

database, and

then regenerate this schema definition.

ActiveRecord::Schema.define(:version => 1) do

create_table “contents”, :force => true do |t|
t.column “email”, :string, :default => “”, :null => false
end

end

All of a sudden there is a :default=>""

Okay, now I add another column, here’s the migration:

class AddIntCol < ActiveRecord::Migration
def self.up
add_column :contents, :mynum, :integer, :null => false
end

def self.down
end
end

Now in script/console

Content.new
=> #<Content:0x2449804 @attributes={“mynum”=>0, “email”=>""},
@new_record=true>

Why is mynum set to 0 ?

$ rake db:schema:dump

create_table “contents”, :force => true do |t|
t.column “email”, :string, :default => “”, :null => false
t.column “mynum”, :integer, :default => 0, :null => false
end

Why the :default => 0 ?

As you can see, it’s not in MySql:

mysql> show fields from contents;
±------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| email | varchar(255) | NO | | | |
| mynum | int(11) | NO | | | |
±------±-------------±-----±----±--------±---------------+
3 rows in set (0.26 sec)

Again, I apologize if this is normal and this is how things should work,
it just seems odd to me.

Jeroen

jeroen · September 10, 2006, 3:29am

Jeroen,

You aren’t going nuts - it’s just AR trying to make it’s best
interpretation
of what it gets and this may very well be an edge case that could be up
for
discussion.

First, the migrations are simply used to create your table - they bear
not
on the actual model classes used later on. AR makes a call to “show
fields”
on the database and creates the column classes from there. Where you are
running into an issue is that show fields returns a blank string for
both of
your example fields and AR makes it’s best guess from there based on the
field type. For the string field - AR would have no choice but to do as
it
does because a blank string is a perfectly valid option for a string
field
default and therefore it needs to handle that case in that way. The
integer
column is a different story and you raise what I think would be an
interesting idea that since a blank string is not a zero - then AR
should
not be making that leap in it’s handling of the default values. You
might
want to see what “show fields” would give back if you specifically gave
your
columns a nil (null) default value.

The short term solution to your problem should you need the nil values
would
be to override the columns method of the base class and put in the
default
values you wish for the columns

For example (completely untested so don’t be surprised if there’s bugs)

Content.rb

class Content < ActiveRecord::Base

def columns
unless @columns
super
@columns.map! { |col|
if col.name == ‘email’
col = MysqlColumn.new(col.name, #default value#, col.sql_type,
col.null)
end
col
}
end
@columns
end

Obviously the #default value# would get replaced by the exact value you
wanted for the column. Note this is pretty brute force but it should
work
just fine for you if you needed it. It also locks the model into MySQL
due
to the need for the MysqlColumn class. It also just doesn’t have that
smooth
look of rails

Hope this helps you understand how it works and why

John W Higgins
[email protected]

On 9/9/06, Jeroen H. <[email protected] > wrote:

end
Loading development environment.
Well in Mysql I can set an integer column to NOT NULL and not specify

[email protected]

jeroen · September 10, 2006, 11:08am

John H. wrote:

Jeroen,

You aren’t going nuts - it’s just AR trying to make it’s best
interpretation
of what it gets and this may very well be an edge case that could be up
for
discussion.
[…]
Hope this helps you understand how it works and why

John W Higgins

Thanks for the explenations John / Al. I think the biggest thing for me
is that it’s counter untuitive (for me) when it comes to validations.
When you have validates_presence_of on NOT NULL columns the default
newly created AR model will pass through these validations, because the
AR defaults are there (0 for instance). But when you do the same on a
column that can be NULL the same validatons will fail because the AR
defaults are nil. What I could do is care even less about the database
and make every column nullable and hope my AR rules will prevent NULLS
from being saved, but that doesn’t sit right with me really.

Jeroen

jeroen · September 9, 2006, 11:09pm

Jeroen H. wrote:

Al Evans wrote:

It has to default to something.

Well in Mysql I can set an integer column to NOT NULL and not specify
a default value. It’s a valid SQL schema. I think AR should copy this
behaviour and not force a default zero, but I seem to stand alone in
this. It’s no biggy really, just wanted to see what the idea behind this
AR behaviour was. Thanks!

This is true in a way – you don’t have to specify a default value.
However, the column will have one anyway. Observe:

mysql> CREATE TABLE foo (
-> int1 int(11) NOT NULL,
-> int2 int(11),
-> str1 varchar(255),
-> str2 varchar(255) NOT NULL
-> )
-> ;
Query OK, 0 rows affected (0.04 sec)

mysql> show fields from foo;
±------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±------+
| int1 | int(11) | NO | | | |
| int2 | int(11) | YES | | NULL | |
| str1 | varchar(255) | YES | | NULL | |
| str2 | varchar(255) | NO | | | |
±------±-------------±-----±----±--------±------+
4 rows in set (0.01 sec)

mysql> INSERT INTO foo () VALUES();
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from foo;
±-----±-----±-----±-----+
| int1 | int2 | str1 | str2 |
±-----±-----±-----±-----+
| 0 | NULL | NULL | |
±-----±-----±-----±-----+
1 row in set (0.00 sec)

Even though no default values are set for int1 and str2, int1 is 0 and
str2 is ‘’. Those are the default defaults:-)

It has nothing to do with ActiveRecord.

–Al Evans

jeroen · September 13, 2006, 2:48am

I think you raise a very valid point. What surprises me is that I don’t
remember AR acting up this way in older versions. Am I correct?
Regardless… this is not an acceptable behavior. Defaults should be
set at the table definition level if needed, not provided automagically
w/o the option of preventing this behavior.

As you pointed out, this renders validates_presence_of useless in many
cases.

Dado

jeroen · September 14, 2006, 6:16pm

  base.send :include, Stone::ModelUtils::InstanceMethods

should be

base.send :include, ModelUtils::InstanceMethods

–
Andrew S.