ActiveRecord / Database schemas and migrations

This is for information regarding the creation of, limitations of, etc. database schemas, ConnectionAdapters, tables, column types, etc. Most of this is done through the use of migrations, although some of these topics affect ActiveRecord in general, since your database schema can affect all aspects of ActiveRecord.

Unsorted

http://www.perham.net/mike/log/?p=462 Mutterings » Blog Archive » Rails Migrations

http://wiki.rubyonrails.org/rails/pages/Foreign+Key+Schema+Dumper+Plugin Foreign Key Schema Dumper Plugin in Ruby on Rails

Data / one-time / non-sequential migrations

...

Data migrations

The tangled web: data migrations (http://blog.brightredglow.com/2006/9/6/the-tangled-web-data-migrations). Retrieved on 2007-05-11 11:18.

Recently we had an application that needed a bunch of preloaded data. Unfortunately, it wasn’t a one-shot loading of data once the structure was mostly solidified. I needed to try out stuff and wanted the super useful migrations to be my tool. Previously, this had been done with some ad hoc rake tasks, which turned out to be, frankly, a mess. The single biggest challenge working with the data was the fact that the state of the data would be changing independent of the migrations. There was no way to ensure the state between one migration and the next. I decided that the one thing I could assert before and after a migration was that certain records with certain id’s did not exist (pre-) and then did exist (post-migration) (reverse that for migrating down). It turned out this would be good enough for what I needed. ... This capture method adds callbacks for after_create and after_destroy to keep track of the id’s. This is written out to a file in a data subdirectory of the db/migrate directory. These files can be ignored by Subversion so each developer can run the migrations on their own databases without clashing. ... This has been useful for adding and removing data while trying out different table structures. Obviously, it’s a rather targeted solution. I’ll be packaging it as a plugin and posting it to PLANET ARGON’s community site soon.

Problem: Old migrations can stop working when you make changes to the code (models)

(Includes subproblem: A migration may use a feature of a model that you later want to delete)

Should old migrations be updated to work with the new code? [1] says yes.

Migrating in two dimensions (http://scottstuff.net/blog/articles/2005/10/31/migrating-in-two-dimensions). Retrieved on 2007-05-11 11:18.

The big problem is that we’ve been using migrations wrong the whole time, and we just realized it. There are probably a dozen bugs in Typo’s bug tracker that boil down to “I fell behind the trunk and now rake migrate throws exceptions and I can’t upgrade anymore.” The problem is that migrations are designed to run against an earlier version of your database, but they use the current version of your code. The first time that this caused problems was with the migration from Typo 2.0 to 2.5–we’d added two new fields to articles. Migration number 7 added the permalink field and a before_save hook to make sure that all saved articles have permalinks. Then migration number 9 added GUIDs and a second before_save hook to fill the guid field. Both migrations did Articles.find(:all).each { |a| a.save } to update each Article and populate the new fields. This worked great for developers who frequently upgraded. A few days after the GUID migration went in, though, we started getting weird bug reports–users who tried to do both upgrades at the same time found that migration number 7 was dying. What was happening was that migration number 7 added the new permalink field to articles, but when it went to run the save loop both before_save hooks ran, and Typo tried to add a GUID to each article. However, the guid field didn’t exist yet, so the migration threw a bunch of exceptions and died. This caused a bunch of grumbling on the Typo IRC channel. We threw around a bunch of possible fixes. Our favorite was separating migrations into two parts–a schema change part and a data change part. First we’d run all of the schema changes, and then update all of the data. As a work-around, we added a hack that checked the current schema version and disabled specific before_save filters for older versions. We managed to keep this little bandaid working until a couple weeks ago, when a huge set of new migrations went it; they renamed the articles table and merged several other tables into the new contents table using STI. And, again, we found that older migrations broke when users tried to upgrade from Typo 2.5.6 to the current dev tree. Unlink the permalink/guid case, this time there was no simple workaround. We couldn’t just add a couple if statements in a filter and make it all go away. The fundamental problem is that we were using the wrong mental model for migrations. I saw migrations as a one-dimensional thing–a list of steps for migrating old data into the new format. In this view, the migration for going from schema version 6 to schema version 7 is constant–once it’s been written, the only reason to change it is if a bug turns up in the logic for that migration. Otherwise, the migration code should remain unchanged over time. And that’s the problem–migrations aren’t one-dimensional. They are (and need to be) two dimensional–the schema version is one dimension and the code version is the other. Individual migrations exist to migrate from a specific old schema version to the current version, using the current code. Each migration should change over time to adapt to the changes in the code. So, the right fix for the permalink migration that caused so many problems wasn’t to add a bunch of logic to before_save. Instead, we should have deleted the entire save loop from the migration, and trusted the GUID migration to update both fields. If that wasn’t good enough, then we should have added a new migration at the end to do permalink cleanup after the GUIDs were added. Once I came to grips with this, the migration changes needed to allow 2.5.x users to upgrade to the current trunk were pretty simple, and took about 5 minutes to write and test. Or was I the only person in the Rails universe who thought about migrations this way?

Database connection adapters / RDBMSs

MySQL

How ActiveRecord column types map to MySQL database types

SQLite

SQLite is great because it spares you the work of having to create a new database (and configure users and permissions) every time you make a new app. It also lets you distribute your app and people can pretty much use it right "out of the box" (assuming they have SQLite installed on their system).

http://wiki.rubyonrails.org/rails/pages/HowtoUseSQLite HowtoUseSQLite in Ruby on Rails

This is the config/database.yml setup that I like to use when I'm just getting started on a project and I don't want to bother setting up a MySQL database:

development:
  adapter: sqlite3
  database: db/development.sqlite

test:
  adapter: sqlite3
  database: db/test.sqlite

production:
  adapter: sqlite3
  database: db/production.sqlite

On the other hand, it could easily be argued that if you're going to end up migrating to MySQL eventually, then you may as well just get it over with at the beginning.

In any case, though, I will usually end up migrating to MySQL/PostgreSQL eventually!

Here are some reasons to switch from SQLite to MySQL:

• If you need to share the same database between multiple applications.
• Compatibility/sameness with production: If you plan on deploying anytime soon, then it's best to develop using the same database that you'll be deploying to, in case there are any behavioral differences or feature differences between the databases. (ActiveRecord tries to make everything behave the same on all RDBMS's, but it can't make everything the same...)

So... How do you migrate your data over to a MySQL database, assuming you've accumulated some useful data in your SQLite database that you don't want to lose?

Migrating a SQLite database to MySQL edit

sqlite3 db/development.sqlite '.dump'

sqlite3 db/development.sqlite '.dump' | mysql -u tyler -p... tyler

sqlite3 db/development.sqlite '.dump' > dump.sql
vim dump.sql  # Make any necessary changes
mysql -u tyler -p... tyler < dump.sql

BEGIN TRANSACTION;
COMMIT;

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"schema_info" VALUES(0)' at line 1

cat dump.sql | sed 's#^INSERT INTO "\(.*\)"#INSERT INTO `\1`#' > dump2.sql

cat dump2.sql | ./sqlite_dump_to_mysql.rb > dump3.sql

CREATE INDEX `index_nested_set_menu_items_on_name` ON menu_items (`name`);

CREATE TABLE menu_items (..., `name` text DEFAULT NULL, ...);

CREATE TABLE menu_items (..., `name` varchar(255) DEFAULT NULL, ...);

mysql -u tyler -p... tyler < dump3.sql

mysql> alter table table_name modify id int(11) not null auto_increment;

Mysql::Error: Duplicate entry '0' for key 1: INSERT INTO foo_table (`url`, `user_id`) VALUES('http://example.com', 0)

What if I already have a database schema?? Can I still use migrations??

That's okay. Yep, you can. Just not to create your initial tables/columns...

http://greenprogrammer.blogspot.com/2006/04/further-adventures-along-our-trail.html

Use rake db:schema:dump one time when you want to start using migrations from a pre-existing SQL DDL generated schema. At this point you will be at version 0. Generate your next model or generate a new migration which will be version 1. If you back out via rake migrate VERSION=0 you will be at the last point before you started using migrations.

Here's how I do it:

• rake db:schema:dump
• script/generate all your models (using --skip-migration). (If you forget to use --skip-migrations, just delete any migrations that it generates (if you don't it will throw an error when you migrate, saying the table already exists).)
• rake migrate. It may look like nothing happened, but this will actually create a new table in your database called schema_info:

# select * from schema_info;
 version
---------
       0
(1 row)

• You can use script/console and interact with your models now.

You can also take the schema that db:schema:dump generates and throw it into your migration_000.rb.

Can I use a primary key other than 'id'?

Yep. Let's say you want the primary key of your users table to be user_id...

• In your model, set_primary_key :user_id
• In your fixture, make sure you specify user_id: 1 rather than id: 1 (which is what Rails will generate for you)

How do I add support for DB-specific data types in ActiveRecord?

http://ghouston.blogspot.com/2006/07/let-activerecord-support-enterprise.html Greg Houston: Let ActiveRecord support Enterprise Databases.

valid column types

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html :

 :primary_key, :string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean

:decimal

PragDave (2006-07-14). Decimal Support in Rails (http://pragdave.pragprog.com/pragdave/2006/07/decimal_support.html). Retrieved on 2007-05-11 11:18.

A couple of blog posts ago, I commented on the dangers of converting database decimal columns into Ruby floats. And, five months early, Santa delivers. In the Rails trunk, numeric and decimal database columns with a scale factor are now converted into Ruby BigDecimal objects. If the scale factor is zero, they instead become integers. Migrations now support decimal columns too, with the addition of two new attributes, precision and scale.

   add_column :orders, :price,
              :decimal, :precision => 8, :scale => 2

valid methods

http://api.rubyonrails.org/classes/ActiveRecord/Migration.html

• create_table(name, options)
• drop_table(name)
• rename_table(old_name, new_name)
• add_column(table_name, column_name, type, options) A default value can be specified by passing an options hash like { :default => 11 }.
• rename_column(table_name, column_name, new_column_name)
• change_column(table_name, column_name, type, options)
• remove_column(table_name, column_name)
• add_index(table_name, column_names, index_type, index_name)
• remove_index(table_name, index_name)

how to wrap it in a transaction/commit

  def self.up
    execute "begin;"
      add_column 'table', 'column', :string
      ...
    execute "commit"
  end

Better, if it works...

  def self.up
    ActiveRecord::Migration.transaction {
      create_table :crawls do |t|
        t.column :created_at, :datetime, :null => false
      end
      add_column :snapshots, :crawl_id, :integer
      foreign_key :snapshots, :crawl_id, :crawls
    }
  end

Migrations/Postgresql: how to see the result of a query

Before I switched to using Rails migrations, I did migrations using plain Postgresql SQL commands. This had the advantage that it would tell you how many rows you updated when you did an update/delete.

How to get this information with Rails migrations?

The execute command returns a PGresult object (http://ruby.scripting.ca/postgres/rdoc/classes/PGresult.html), which you can then query to get Postgresql's output.

  def self.up
    puts execute("update orders set ...").cmdstatus
  end

UPDATE 244

Don't forget to add indexes!

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#M000612

add_index(:suppliers, :name)
add_index(:accounts, [:branch_id, :party_id], :unique => true)

ActiveRecord  edit   (Category  edit)

How do I do migrations outside of Rails?

http://blogs.pragprog.com/cgi-bin/pragdave.cgi/Tech/Ruby/MigrationsOutsideRails.rdoc

    ActiveRecord::Schema.define do
      create_table children, :force => true do |t|
        t.column :parent_id, :integer
        t.column :name,      :string
        t.column :position,  :integer
      end
    end

add_column with :null => false didn't work

add_column :my_table, :first_opened_time, :datetime, { :default => 'now', :null => false }

== AddFirstOpenedTime: migrating ========================================
-- execute("begin")
   -> 0.0003s
-- add_column(:my_table, :first_opened_time, :datetime, {:default=>"now", :null=>false})
rake aborted!
PGError: ERROR:  column "first_opened_time" contains null values
: ALTER TABLE my_table ALTER first_opened_time SET NOT NULL

Complaint: You have to use :allow_nil => true when you do validates_length_of :maximum

Otherwise you'll get "value is too long" validation errors when you try to set it to nil.

I can see why it might be intuitive for :minimum validations, but not for :maximum validations!

Can I use a composite (multi-column) primary key?

Sure. Just use http://rubyforge.org/projects/compositekeys .

http://www.pjhyett.com/posts/208-composite-primary-keys-are-good Composite Primary Keys are Good

class AddViewsTable < ActiveRecord::Migration
  def self.up
    create_table :views, :primary_key => [:topic_id, :user_id] do |t|
      t.column :topic_id, :integer
      t.column :user_id,  :integer
      t.column :post_id,  :integer
    end
  end
end

Caveat: ActiveRecord doesn't respect PostgreSQL's default values

# I had a table that had a not-null constraint on its date_time field. So when I did a plain a = MyModel.new; a.save it said 
ActiveRecord::StatementInvalid: PGError: ERROR:  null value in column "date_time" violates not-null constraint

# To solve this, I created a before_save method in my model that would initialize the column to Time.now() if it was nil. (The column was set up in PostgreSQL to have a default value of Now(), but ActiveRecord doesn't look at / respect that default. It tries to set it to nil, which translates to Null in SQL, which overrides PostgreSQL's default value.)

This works:

  def before_save
    self.date_time = Time.now() if date_time.nil?
  end

So does this:

  def before_save
    write_attribute(:date_time, Time.now()) if date_time.nil?
  end

Schemas  edit   (Category  edit)

Question: How do I ask an ActiveRecord ConnectionAdapter what its connection information (host, database name, etc.) is?

Why would you want to do that?

Well, suppose you want to put a sanity check in some important script, just to make absolutely sure that you are running it on the correct host or the correct database or something...

How to do it:

/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/postgresql_adapter.rb

      3 module ActiveRecord
      4   class Base
      5     # Establishes a connection to the database that's used by all Active Record objects
      6     def self.postgresql_connection(config) # :nodoc:
...
      9       config = config.symbolize_keys
     10       host     = config[:host]
     11       port     = config[:port] || 5432
     12       username = config[:username].to_s
     13       password = config[:password].to_s
...
     16 
     17       if config.has_key?(:database)
     18         database = config[:database]
     19       else
     20         raise ArgumentError, "No database specified. Missing argument: database."
     21       end
     22 
     23       pga = ConnectionAdapters::PostgreSQLAdapter.new(
     24         PGconn.connect(host, port, "", "", database, username, password), logger, config
     25       )
...
     32     end
     33   end

     50     class PostgreSQLAdapter < AbstractAdapter
     51       def adapter_name
     52         'PostgreSQL'
     53       end
     54 
     55       def initialize(connection, logger, config = {})
     56         super(connection, logger)
     57         @config = config
     58         @async = config[:allow_concurrency]
     59         configure_connection
     60       end

So it's stored in @config. I don't see any accessors for getting that out, so I guess we'll just have to cheat and use instance_variable_get(:@config):

p SomeModel.connection.instance_variable_get(:@config)
# {:username=>"...", :allow_concurrency=>false, :database=>"...", :password=>"...", :adapter=>"postgresql", :host=>"..."}