Software Development & Beyond

This is a tale about Ruby On Rails, custom stored procedures for MySql 5 and how Rails 1.2.3 is not only opinionated against stored procedures but also actually incompatible with creating and sometimes calling (mysql) stored procedures. The tales does not end with a truly happy ending but some “hacks” are mentioned that I have found useful.

Background
The rails framework developers, being of the opinion that complexity is best located in the code and not in the database, does not advocate using stored procedures as a abstraction layer between the database and the application. Instead dynamic sql generated from RoR code is used. For typical application databases, this approach works very well indeed.

Examples where the traditional rails way of database thinking sometimes fails short are projects involving multiple (some non-rails) applications, projects involving legacy databases, projects where the lifetime of the data will exceed the life of the application code and finally specialized projects where a non-trivial amount of handcrafted sql code is needed (typically for reporting, statistical analysis, data mining, scheduled database maintenance tasks and other sql that are not related to the O-R mapping handled by ActiveRecord).

For the latter cases, some usage of stored procedures together with the rest of the traditional database arsenal (foreign key constrains (*), Triggers, views etc) can be useful at times… all depending on the specific project needs of cause.

(*) Actually, from experience, I highly recommend always defining foreign key constrains (even in “application” databases). This is sadly NOT the current way of thinking among rails developers and consequently rails support is lacking - but that is another story.

Defining stored procedures in MySql

Since version 5 of the MySql database you can define a simple no-arg stored procedure like this:

DELIMITER $$

CREATE PROCEDURE mydb.my_stored_procedure()
BEGIN
– INSERT CODE HERE
END $$

DELIMITER ;

Notice the importance of overriding the default “;” delimiter when defining your typical stored procedure! This is different from creating stored procedure in Sql Server for instance.

Calling stored procedures from Rails:

Begin by upgradíng your standard ruby mysql driver to the native “C” driver (gem install mysql) as the standard pure-ruby mysql driver doesn’t support all the needed MySql 5 features.

From rails, our MySQL procedure can f.x. be called using execute(”CALL my_stored_procedure()“) in a migration . Unfortunately, it might not always work. If your stored procedure return multiple result sets, calling the procedure will fail unless you pass the Mysql::CLIENT_MULTI_RESULTS flag when establishing the database connection. Unfortunately, active record does not allow specifying client flags for your database connection. For workaround instructions, that involve patching the MySql Adaptor, see the wiki entry here or the outdated bug report with patch here.

Creating your stored procedures in rails migrations:

The brave adventurer may try to create a Mysql 5 stored procedures like this in a rails migration:

class CreateDatabaseObjects < ActiveRecord::Migration

def self.up
sql_directory = File.join(File.dirname(__FILE__), “sql” )

begin
f=File.open(File.join(sql_directory, “my_stored_procedure.sql”), “r”)
sql = f.readlines.join
execute(sql)
ensure
f.close unless f==nil
end
end

def self.down
execute “DROP PROCEDURE my_stored_procedure”
end

end

Unfortunately, I have found that the obvious approach above does not work with the newest stable version of ruby 1.8.6 and rails 1.2.3 on my Windows installation (meaningless error). Hacking the MySql Adaptor to include the Mysql::CLIENT_MULTI_STATEMENTS client flag does not improve the situation (which is rather strange).

However, I did succeed in creating a workaround. Not a pretty hack or a truely happy resolution but it works:

class CreateDatabaseObjects < ActiveRecord::Migration

def self.up
sql_directory = File.join(File.dirname(__FILE__), “sql” )

# Hack: Invoke database cmd tool subprocess to create our mysql stored procedure.
conf = ActiveRecord::Base.configurations[RAILS_ENV]
sql_file = File.join(sql_directory, “my_stored_procedure.sql”)
cmd_line=”mysql -h “+conf[”host”]+” -D “+conf[”database”]+ ” -u “+conf[”username”]+” -p”+conf[”password”]+” <”+sql_file
if !system(cmd_line)
raise Exception, “Error executing “+cmd_line
end

end

def self.down
execute “DROP PROCEDURE my_stored_procedure”
end

end

- The end -

This entry was posted on Saturday, March 31st, 2007 at 5:13 pm and is filed under Ruby On Rails, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.