thr3ads.net - Rails - Migration Issues: Can''t update newly added column values [Feb 2008]

If this information is useful, please help other people find it:
Share via:

Kendall

2008-Feb-13 19:42 UTC

Migration Issues: Can''t update newly added column values

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rob Biedenharn

2008-Feb-13 22:29 UTC

head link

Re: Migration Issues: Can''t update newly added column values

On Feb 13, 2008, at 2:42 PM, Kendall wrote:
>
> 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
> ----------
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'')
     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

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
Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

AndyV

2008-Feb-14 19:45 UTC

head link

Re: Migration Issues: Can''t update newly added column values

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.

On Feb 13, 2:42 pm, Kendall
<zettab...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Kendall

2008-Feb-28 17:56 UTC

head link

Re: Migration Issues: Can''t update newly added column values

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.

--
Kendall Gifford
zettabyte *a t* g mail dawt cawm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Pau Cor

2008-Sep-30 04:08 UTC

head link

Re: Migration Issues: Can''t update newly added column values

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/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Possibly Parallel Threads

Search for more reasonably related threads

Rails - Feb 2008 - Migration Issues: Can't update newly added column values

Migration Issues: Can''t update newly added column values

Re: Migration Issues: Can''t update newly added column values

Re: Migration Issues: Can''t update newly added column values

Re: Migration Issues: Can''t update newly added column values

Re: Migration Issues: Can''t update newly added column values

Possibly Parallel Threads