Jay Fields' Thoughts

I've touched on this topic a few times. I previously listed the pains of using Migrations. And, in my last post I gave an example of our solution. But, I never went further into why we made the choice.

For my current team, the choice was a fairly easy one. It's a large team and we were churning out migrations very quickly. Of course, the numbering became an issue, but keeping a consistent view of the database became a larger issue. There was no one place to get a clear view of what the table looked like (in code).

Another issue with Migrations is that the self.down methods are never really tested. Sure, you might go down then up with each build, but do you test the application at every step down and up? Without running all the tests at each down, it's impossible to know that the self.down did exactly what you expected.

Also, how often do you need to step up or down? We found that we wanted to drop the entire db with each build to ensure that we didn't have any data dependencies. Therefore, going back a version or 2 never seemed valuable.

Most importantly, we were eventually going to need to generate SQL and send it to the database group. This meant that migrations were only ever going to be run by our team. And, if we needed a view of the database at any given date, we could just look in Subversion for that copy of our schema.

Given the above thoughts, we decided to create one schema file per release. The schema file uses the same syntax as migrations and even allows us to specify the schema version (very much like the idea of using one migration per release).

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

  create_table :accounts, :force => true do |t|
    t.column :first_name, :string
    t.column :last_name, :string
    t.column :username, :string
    t.column :password, :string
    t.column :email, :string
    t.column :company, :string
  end

  ...
end

And, in case you missed it in the previous post, the task to run it is very simple.

task :migrate => :environment do
  ActiveRecord::Base.establish_connection(environment)
  require File.dirname(__FILE__) + '/../../db/schema/release_1.rb'
end

Migrations are fantastic, but if you don't need them you shouldn't live with the overhead.

Labels: migration, rails, ruby, schema

At my current project we are developing on Mac Minis and deploying to linux boxes. A fairly large problem with developing on Mac Minis is that there's no driver for Oracle that runs on the Intel Mac Minis.

We (painfully at times) address this problem by running Postgres locally and Oracle on our CI boxes. This works for us, but recently we ran into some pain. We needed to create SQL scripts from our schema definitions.

We store our schema definitions in the same format as a migration, but we put them all in one block, similar to the code below.

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

  create_table :accounts, :force => true do |t|
    t.column :first_name, :string
    t.column :last_name, :string
    t.column :username, :string
    t.column :password, :string
    t.column :email, :string
    t.column :company, :string
  end

  ...
end

And, we define our db:migrate to simply require this file.

task :migrate => :environment do
  ActiveRecord::Base.establish_connection(environment)
  require File.dirname(__FILE__) + '/../../db/schema/release_1.rb'
end

Since we run against Oracle on our CI boxes we could generate the DDL as a build artifact, but each time we make a change to the DDL we would need to check-in to see the changes for Oracle. This wasn't the most efficient use of our time, so we decided to get the OracleAdapter working on our MacMinis, despite not having the OCI8 drivers or oracle installed.

The code isn't the cleanest I've written, but it's also not as bad as I expected. It works for generating DDLs; however, it won't work if you have any code that requires a trip to the database to get table information (SomeActiveRecordSubClass.create is a good example of code that requires a trip).

namespace :db do
  namespace :generate do
    namespace :oracle do
      desc "generate sql ddl"
      task :ddl do
      $:.unshift File.dirname(__FILE__) + "../../../tools/fake_oci8"
      Rake::Task[:environment].execute
      file = File.open(RAILS_ROOT + "/db/oracle_ddl.sql", 'w')

      ActiveRecord::Base.instance_eval do
        def connection 
          ActiveRecord::ConnectionAdapters::OracleAdapter.new nil
        end
      end

      class ActiveRecord::Schema < ActiveRecord::Migration
        def self.define (info={}, &block)
          instance_eval(&block)
        end
      end

      ActiveRecord::Base.connection.class.class_eval do
        define_method :execute do |*args| 
          file << "#{args.first};\n"
        end
      end

      Rake::Task[:"db:migrate"].execute
      file.close
    end
  end
end

Much like a previous entry, I'm stealing the execute method to get the generated SQL. This time, I've also stolen the AR::Base.connection method and put an OracleAdapter in there. The change to AR::Schema is required because the original method updates the schema_info table at the end. Since I'm not using migrations, the update is unnecessary.

The result: An Oracle specific DDL generated without actually connecting to an Oracle database.

Labels: ddl, migration, oracle, rails, ruby