Tuesday, April 10, 2007

Rails: Generating an Oracle DDL without Oracle installed

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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.