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 -

Danish companies tend to be on the conservative side when it comes to new technology, despite all benefits. So was the case 10 years ago when I got started on the Java platform and so is the case now for Ruby / Ruby On Rails.

Therefore, if you are a software developer here in Denmark with a strong interest in Ruby / RoR you will currently find local work possibilities lacking (*). However, I expect this situation to change in the mid/long term so developers should get prepared (e.x: start you own RoR / Ruby projects, find foreign clients or do both like me :-)).

One way that will help you get started is to join one of the new Danish RoR / Ruby user groups. The groups that I know of are:

aarhus.rb - Ruby/Rails user group situated in Århus. Meets at the start of every month at different locations hosted by members. Next meeting is 28. September 16.00-18.00, 2006 at Mjølner Informatics, Helsingforsgade 27, 8200 Århus N.. See the link for the associated google group.
Copenhagen.rb - Ruby/Rails user group for greater Copenhagen area. Meets about every 1-2 months at different locations hosted by members. Next meeting is 19. September, 17.30-?, 2006 at Kraftvaerk, Vesterbrogade 74, 4. sal, 1620 København V.. See link for details.

(* NOTE) If your (Danish) company do actually use Ruby / RoR or are interested in learning how to leverage the benefits, please let me know.

I just completed a great training course for Ruby On Rails (RoR), a revolutionary framework for web developing. The course was in Boston (MA) and was held by the authors of the notable book Agile Web Development with Rails.

I personally have quite some experience with ‘traditional’ web development using ASP.NET and J2EE. Having experimented with RoR myself and gone through additional training, I can now say for sure that RoR is definitely easier and more productive than these (and at the same time making it easy to get great quality and maintainability). RoR also compares very favourably against PHP according to the many PHP developers I meet during the course.

According to the guys behind the Rails Studio training course for Ruby On Rails: “Nothing says ‘I’m ready to write killer Rails apps’ better than a Rails Studio alumni button”. So here is the button I got: