CSCI E-168

Agile Web Development with Rails, 17.4 and 17.5, covers this subject.

On page 307 of the PDF, Migrating Data with Migrations, the basic scheme you might use is demonstrated.  As another example, suppose I create a users table and model:

$ ./script/generate model users first_name:string last_name:string

The migration looks like this:

class CreateUsers < ActiveRecord::Migration
 def self.up
   create_table :users do |t|
     t.string :first_name
     t.string :last_name

     t.timestamps
   end
 end

 def self.down
   drop_table :users
 end
end

After adding a number of users, it becomes clear that a full_name attribute would be very useful:

$ ./script/generate migration AddFullNameToUser full_name:string

This new migrations looks like this:

class AddFullNameToUser < ActiveRecord::Migration
  def self.up
    add_column :users, :full_name, :string
  end

  def self.down
    remove_column :users, :full_name
  end
end

Before running it, I add the line to update the users:

class AddFullNameToUser < ActiveRecord::Migration
  def self.up
    add_column :users, :full_name, :string
    User.find(:all).each do |u|
      u.full_name = u.first_name + ' ' + u.last_name
      u.save!
    end
  end

  def self.down
    remove_column :users, :full_name
  end
end

An important note here:  I did not use update_all because string concatenation is not portable between database engines (|| for Oracle, postgres and sqlite3, + for SQL Server and Sybase,  concat for MySQL, etc).

As John demonstrated in his migrations 3 example, depending on what you are trying to accomplish, the down method may not be data preserving.

Beyond the above and similar data manipulation, section 17.5 covers ways to execute fragments of native SQL or arbitrary SQL.  Using this functionality is beyond the scope of this course and of course has the significant downside of being much less portable.

Execute the command sqlite3 at the command prompt passing the file name of the database:

$ sqlite3 <database name>.sqlite3

You should see output similar to the following:

SQLite version 3.6.10
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite>

Check that the version is correct.  People running sqlite3 in emacs may need to set sql-sqlite-program.

Enter ‘.help’ to see the list of commands that provide information or set options. Input starting with a ‘.’ (period) signals non-SQL and should be one of those commands.  All other input will be interpreted as SQL and should be terminated with a ‘;’ (semi-colon).

Enter ‘.quit’ to exit.  Some other commands you will likely find useful are ‘.headers’, ‘.tables’, and ‘.schema’.

When using rails with sqlite3, this same functionality can be started with ’script/dbconsole’.