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.

… Or what every developer should know about the implementation of exception handling:

Modern Exception Handling (EH) in C++, JAVA, Ruby, Modular-3, C# and other modern programming languages is a great tool for handling errors but unfortunately it is sometimes abused by software developers that do not quite get what exceptions are really for or are just ignorant of possible implementations.

Common abuses of EH includes using exceptions as an alternative flow control mechanism (think sophisticated “goto’s” and you got the basic idea of this antipattern”)……. Don’t do that. It will only make the code harder to read. It will also make your code slower to execute since throwing exceptions are generally very expensive operations.

Another less apparent misuse of EH is usage of try-catch(-finally), or similar constructions your language may offer, inside the control flow of hotspots (such as inside time critical loops). Don’t do that, as a the try-catch-finally construction may have overhead even when you won’t expect it.

So why are throwing exceptions expensive and why may the try-catch-finally constructions (or similar) have overhead ? Well, it all depends on the language, the implementation of your VM or compiler (and sometimes on whether you use native code or not if your language allows it). Depending on your environment, just raising one exception can be from 10-100.000 times as slow as alternatively returning a simple return code from the method. And even if you don’t raise any exception, just having a try-catch-finally in your control flow can also be moderately expensive (but usually only enough to be a problem inside hotspots).

Specifically, the case of overhead of try-catch-finally constructions when no exceptions occur is difficult to get rid of by compiler & virtual-machine implementers. Few implementations on selected chip architecture got it right and have 100% overhead-free implementations but many impose a overhead just for placing try/catch/finally constructions in your control flow. Basically this is because something like a “linked list” has to be maintained internally by the compiler or VM each time the control flow enters or exits a try-catch-finally.

For much more details about various possible implementations of exception handling and the impact on performance refer to this old thesis of mine here.

In conclusion, the morale of the story is:
* Do use exception handling for error handling only (not for control flow).
* Don’t use try-catch-finally constructs inside hot-spots (i.e. loops and such) if it can be avoided. Do the try-catch at a higher level that is called less often.
* If your particular java, c++, ruby, clr … implementation of exception handling on one chip architecture yields excellent performance even when you break the above rules you are just plain lucky. Change the version, vendor or chip architecture and you luck may desert you. Therefore don’t do it

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: