Migration Issues: Can't update newly added column values

[Kendall]

Hello everyone, just wondering if anyone can help me with a migrations
question here.

Using:
rails 2.0.2
ruby 1.8.6 (2007-09-24 patchlevel 111) [i386-mswin]
mysql 5.0.45
Platform:
windows xp

The problem:
----------
I created a migration that is designed to add a column named
"deleted_at" (timestamp) and then remove a column named
"is_deleted" (boolean). The idea is to upgrade from merely tracking
whether a record has been non-destructively deleted to tracking both
if and when it has been non-destructively deleted. NULL values in
"deleted_at" indicate a normal, non-deleted record.

The trick and problem is to keep existing records' is/isn't deleted
state valid while doing the migration. In order to accomplish this, I
tried to add code after the "add_column" that creates "deleted_at" and
before the "remove_column" that removes the "is_deleted" column to do
this. The code I wrote checks all records and sets "deleted_at" to
"Time.now" if the "is_deleted" field is true. Or, at least that's the
idea.

Unfortunately, the code to update the field has no effect.

Can someone tell me why?

For completeness, I created a simple test rails application (bugtest)
to demonstrate. The applicable files/code are as follows:

----------
bugtest/db/migrate/001_create_options.rb <--- Original table
definition
----------
class CreateOptions < ActiveRecord::Migration
def self.up
create_table :options do |t|
t.string :name
t.boolean :is_deleted, :default => false, :null => false
t.timestamps
end
end

def self.down
drop_table :options
end
end

----------
bugtest/db/migrate/002_add_deleted_at_to_options.rb <--- Upgrade
migration that doesn't completely work
----------
class AddDeletedAtToOptions < ActiveRecord::Migration

# Create temp model to use...
class Option < ActiveRecord::Base; end

#
# Add `deleted_at` column, set to Time.now if `is_deleted` is true,
# then remove `is_deleted`:
#
def self.up
add_column(:options, :deleted_at, :timestamp, :default => nil)
say "IN TABLE option: Setting `deleted_at` to now if `is_deleted`
set." do
Option.reset_column_information
Option.find(:all).each do |option|
option.update_attribute(:deleted_at, Time.now) if
option[:is_deleted]
end
end
remove_column(:options, :is_deleted)
end

#
# Add `is_deleted` column, set to true if `deleted_at` isn't NULL,
# then remove `deleted_at`
#
def self.down
add_column(:options, :is_deleted, :boolean, :default =>
false, :null => false)
say "IN TABLE option: Setting `is_deleted` to true if `deleted_at`
set." do
Option.reset_column_information
Option.find(:all).each do |option|
option.update_attribute(:is_deleted, true) unless
option[:deleted_at] == nil
end
end
remove_column(:options, :deleted_at)
end
end

----------
bugtest/test/fixtures/options.yml <--- Test records that demonstrate
the issue
----------
one:
name: "Record Not Deleted"
is_deleted: false

two:
name: "Deleted Record"
is_deleted: true

----------
Sequence of commands executed and observations:
----------
> rake db:create
> rake db:migrate VERSION=1
> rake db:fixtures:load

Check out data in database, looks good. One record marked as deleted
and one isn't.

> rake db:migrate

Command reports everything working. Checked database, however, and the
"deleted_at" field for both records is NULL. I expected the record
that originally had "is_deleted" set to true to have a timestamp value
in "deleted_at" instead of NULL.

----------
Well, there you have it. Any help or enlightenment is greatly
appreciated.

--
Kendall Gifford
zettabyte *a t* g mail dawt cawm

[Rob Biedenharn]

Does this version work for you?

class AddDeletedAtToOptions < ActiveRecord::Migration

class Option < ActiveRecord::Base; end

# Add `deleted_at` column, set to Time.now if `is_deleted` is true,
# then remove `is_deleted`:
#
def self.up
add_column(:options, :deleted_at, :timestamp, :default => nil)
say "IN TABLE option: Setting `deleted_at` to now if `is_deleted`
set." do
Option.reset_column_information

Option.update_all(['deleted_at = ?', Time.now], ['is_deleted
= ?', true])

end
remove_column(:options, :is_deleted)
end

# Add `is_deleted` column, set to true if `deleted_at` isn't NULL,
# then remove `deleted_at`
#
def self.down
add_column(:options, :is_deleted, :boolean, :default =>
false, :null => false)
say "IN TABLE option: Setting `is_deleted` to true if
`deleted_at` set." do
Option.reset_column_information

Option.update_all(['is_deleted = ?', true], 'deleted_at IS NOT
NULL')

Sorry, I haven't tried it, but it is similar to some of the migrations
that I've had to created.

-Rob

Rob Biedenharn http://agileconsultingllc.com
R...@AgileConsultingLLC.com

[AndyV]

How about something like this:

def self.up
add_column(:options, :deleted_at, :timestamp, :default => nil)
say "IN TABLE option: Setting `deleted_at` to now if

`is_deleted`." do
Option.update_all("deleted_at = '#{Time.now}' ",
"is_deleted==1")

end
remove_column(:options, :is_deleted)
end

There's no need to iterate over the objects in Ruby -- delegate that
to the DB.

[Kendall]

I don't know if anyone cares, but just for closure (and for those who
may have a similar issue and search the newsgroup), here is an update
with my issue.

I tried both recommendations made previously by both Rob and AndyV to
no avail. In both cases, the migration ran, reporting success.
However, the values in the `deleted_at` column were NULL, even for the
record that should have had a timestamp. I don't understand why this
works the way it does. I'd think that either the migration would fail
on the "Option.update_all(...)" line due to an SQL error on the
database or something (if the created column hadn't actually been
created yet) or that the actual migration would truly work as
expected. If anyone figures this out (or already knows what's going
on) feel free to let us know for curiosity sake.

As for me, I ended up just sidestepping the problem. Instead of
continuing research on the issue (like reading/tracing ActiveRecord
code) I just rewrote my actual fixtures to reflect the latest schema
and I load them after all migrations have run. I can get away with
this since I'm still in the early stages of development and have no
'live' non-fixture data to worry about.

Thanks to those who tried to help.

[Pau Cor]

Kendall wrote:
> I don't know if anyone cares, but just for closure (and for those who
> may have a similar issue and search the newsgroup), here is an update
> with my issue.

In case anyone finds this which Googling the same problem (like I did)
this blog post has the answer:
http://espaceblogs.blogspot.com/2007/05/update-newly-added-column-in-migration.html

Essentially you need to call reset_column_information on your model. For
example

add_column 'my_models', 'new_column'
MyModel.reset_column_information
my_model = MyModel.first
my_model.new_column = 'this will save'
my_model.save
--
Posted via http://www.ruby-forum.com/.