Rails Migrations - Directly Import SQL

Recently a project i was working on required to integration of triggers and stored procedures. To my knowledge rails migrations do not have to ability to create and manage stored procedures. And why would it really? Stored Procedures and Triggers are database specific and not all Database engines support them.

Now, you can execute database specific queries directly through the connection like this:

ActiveRecord::Base.connection().execute( "call #{some custom query};" )

and in many instances this may be all you need.

However an issue (bug?) in ActiveRecord means that ActiveRecord does not understand the syntax used to set the delimiter.. a requirement for creating stored procedures and triggers.

In an effort to work around ActiveRecord limitations and still stay as DRY as possible by managing the application schema through migrations I created a monkey patch to the ActiveRecord MySQL connection adapter to load the sql commands directly to MySQL

It’s super simple.. here’s how..

Add the following code to your environment, initializers, or separately in the lib folder. If you take the lib folder approach don’t forget to require the file..

class ActiveRecord::ConnectionAdapters::MysqlAdapter
 def import_sql(file)
   conf = ActiveRecord::Base.configurations[RAILS_ENV]   
   sql_file = File.join(File.join(File.dirname(__FILE__), "sql" ), "#{file}.sql")
   cmd_line = "mysql -h "+conf["host"]+" -D "+conf["database"]+ " --user="+conf["username"]+" --password="+conf["password"]+" < "+sql_file     raise Exception, "Error executing " + cmd_line unless system(cmd_line)       
 end 
end 

Then.. create your sql commands.. i chose to store these commands in the db/ directory in a new folder called sql/

Create one file per command.. That is, one file to create your trigger and one file to remove your trigger.

I leave the practice of creating the actual sql syntax to you..

Then.. create your sql commands.. i chose to store these commands in the db/ directory in a new folder called sql/

Create one file per command.. That is, one file to create your trigger and one file to remove your trigger.

I leave the practice of creating the actual sql syntax to you..And this is a sample migration that used our new sql importing mechanism.

class AddTriggers < ActiveRecord::Migration
 def self.triggers
  %w( insert update )
 end
 def self.up    
     triggers.each do |trigger|
       import_sql("t_#{trigger}_up.sql")
     end   
 end 
 def self.down  
     triggers.each do |trigger|
       import_sql("t_#{trigger}_down.sql")
     end   
 end
end