Sunday, November 12, 2006

Rails: Generate SQL from Migrations

Unfortunately, my new client will not allow migrations to be run in production. This isn't a surprise since they have a DB group that maintains their databases. And, the DB group requires application developers to submit SQL scripts when database modifications are necessary.

At first this may sound like a pain, but it's actually quite easy to capture the SQL that migrations generate. There may be easier ways; however, it seemed simple enough to temporarily change the behavior of the execute method.
require 'find'

class SqlWriter

def self.write(path, string)
File.open(path, 'w') { |file| file << string }
end

end

class MigrationSql
class << self
def execute
connection = ActiveRecord::Base.connection
old_method = connection.class.instance_method(:execute)

define_execute(connection) { |*args| SqlWriter.write(@sql_write_path, args.first) }

root = RAILS_ROOT + "/db/migrate"
output_dir = root + "/../migration_sql"
Dir.mkdir output_dir unless File.exists? output_dir
Find.find(root) do |path|
unless path == root
require path
file = File.basename(path, ".rb")
write_sql(connection, output_dir, file, :up)
write_sql(connection, output_dir, file, :down)
end
end

define_execute(connection) { |*args| old_method.bind(self).call(*args) }
end

def write_sql(connection, output_dir, file, direction)
connection.instance_variable_set :@sql_write_path, output_dir + "/" + file + "_#{direction}.sql"
file.gsub(/^\d\d\d_/,'').camelize.constantize.send direction
end

def define_execute(connection, &block)
connection.class.send :define_method, :execute, &block
end
end
end
Now that we have the behavior necessary we can wrap the call to the execute method of the MigrationSql class in a rake task.
namespace "db" do
namespace "generate" do
desc "generate sql for migrations"
task "migration_sql" => :environment do
MigrationSql.execute
end
end
end

4 comments:

  1. Mark Van Holstyn12:50 PM

    I made a plugin last year which you may find useful. http://www.agilewebdevelopment.com/plugins/activerecord_io_mode_plugin
    Basically it alters the execute method to send the sql to whatever object it was given. Let me know if you like it!

    ReplyDelete
  2. Great! I was just thinking about writing a similar plugin when I was deep in using ActiveRecord in a legacy DB environment so that I can spool the SQL statements that would have been executed in a file. Perhaps you and Mark can consolidate your approaches.

    The idea I had was to introduce a class variable like ActiveRecord::Base.spool_file "somefile.sql" and ActiveRecord::Base.execute false/true to make ActiveRecord spool out to file and/or actually really execute on the db.

    ReplyDelete
  3. Anonymous12:04 PM

    Jay, this is a great idea and good sample code!
    However, I came up on a limitation where your approach doesn't work when the migrations need to add data in addition to just generating the create statements.
    Rails tries to do a "show fields from table" statement in that case, but since the connect.execute statement has been overridden it doesn't return any data and the migration fails.

    Aside from adding the file-write code to the initial execute method, can you think of any better approach?

    ReplyDelete
  4. Thanks.

    You could do a regex match for /show fields from/ and allow the query to go to the original execute method and behave normally.

    Sorry, I haven't had to deal with this so I don't have any working code for that situation.

    Cheers, Jay

    ReplyDelete

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