Rails and Legacy Databases

Rails and Legacy Databases

Rails and Legacy Databases

Content

• Purpose
• One shot modifications
• Simple automation
• The anti-RoR database

Purpose

This document aims at describing how RoR can be tuned to work with legacy databases.

Working with legacy databases is very important as it allow RoR to easily be deployed in conjunction with other legacy systems. I also personnally write some Rails app to overcome several limitations of legacy applications, and made my day simpler ;)

One shot modifications

One shot modifications are the kind of modification that you do model per model or private key per private key.

For example, let’s suppose that your legacy database use a table named FooBar, and that you’d like to map the model Client onto this table. By using the set_table_name class method you can define this:

1  
2     class Client < ActiveRecord::Base
3       set_table_name "FooBar" 
4     end
5   

The same goes for the private key. In RoR, the convention says that the primary key should be named id and should be auto-incremented. In many legacy databases the primary key won’t be named id. By using the set_primary_key you can redefine the name if the primary key to be used:

1  
2     class Client < ActiveRecord::Base
3       set_table_name "FooBar" 
4       set_primary_key "ref" 
5     end
6   

Note that although you set the new name of the primary key as being in our example ref, you’ll still use the method id in you ruby code, to reference the primary key. Note also that by redefining the primary key, you also implicitely say to RoR: “OK. When creating a new item in the table, don’t bother finding a value for the primary key, I’ll do it” ... So you’ll have to produce this value when creating and saving a new item in your db.

Similarly several legacy databases does not rely on auto-incremented primary key, but on sequences to get a new primary key. Using the set_sequence_name method you can specify the name of the sequence table RoR will have to use:

1  
2     class Client < ActiveRecord::Base
3       set_table_name "FooBar" 
4       set_primary_key "ref" 
5       set_sequence_name "FooBarSequences" 
6     end
7   

Simple automation

Some legacy databases also follows almost the same conventions as RoR, but name the table differently.

For example, the Mantis bug tracker, uses the schema explicited here

As you can see a lot of conventions are the same as RoR’s one:

• table and column names are in lowercase
• primary keys are named id and auto-incremented
• foreign keys look like <identifier>_id
• join tables does not have any id column

We can note some difference though:

• the table name does not follow the same convention: it always begin with mantis_ and ends in _table (for example mantis_bug_table). Moreover the “real” name of the table is not pluralized (the table is named mantis_project_table and not mantis_project*s*_table).
• Join tables do not follow the RoR naming convention.

As example, we’re going to try to model the mantis_project_table, mantis_bug_table, mantis_user_table and mantis_bug_monitor_table.

First of all, the administrative tasks: we’re going to connect to a MySQL database, hosted on a machine named mantisserver, with the username mantisread, no password and on a database named mantis.

 1  
 2       #!/usr/bin/env ruby
 3  
 4       require 'rubygems'
 5       require 'active_record'
 6  
 7       ActiveRecord::Base.establish_connection( :adapter  => "mysql",
 8                                                :host     => "mantisserver",
 9                                                :username => "mantisread",
10                                                :password => "",
11                                                :database => "mantis" )       
12     

Now let’s teach to ActiveRecord that all of our table names will follow the convention that model name is surrounded by mantis_ and _table. and that the named is not pluralized:

1  
2    ActiveRecord::Base.table_name_prefix = "mantis_" 
3    ActiveRecord::Base.table_name_suffix = "_table" 
4    ActiveRecord::Base.pluralize_table_names = false

We can now create the Project model:

1  
2    class Project < ActiveRecord::Base
3    end

and try to see if it works (I removed irb’s output for sake of clarity):

1  
2    hal> irb --noinspect
3    irb(main):001:0> load 'mantis_access.rb'
4    irb(main):003:0> p = Project.find 1
5    irb(main):004:0> puts p.name
6    mantis
7    irb(main):005:0> 

We can now create our other models:

1  
2    class Bug < ActiveRecord::Base; end      
3    class User < ActiveRecord::Base; end

Note that we do not create any BugMonitor model as the mantis_bug_monitor_table is used as a join table.

So now, we have to add relationship between our various models:

• A Project has many Bugs:

1  
2    class Project < ActiveRecord::Base
3      has_many :bugs
4    end
5    class Bug < ActiveRecord::Base
6      belongs_to :project
7    end

• a Bug is potentially monitored by several Users, and a User eventually monitores several Bugs. So it seems that we’re going to use our beloved has_and_belongs_to_many method. Note that we need to specify to RoR the name of the join table, has Mantis does not follow the same convention. Note also that this table name is not impacted by the prefix and suffix we set at the beginning.

 1  
 2    class Bug < ActiveRecord::Base
 3      belongs_to :project
 4      has_and_belongs_to_many :users, 
 5                              :join_table => :mantis_bug_monitor_table
 6    end
 7  
 8    class User < ActiveRecord::Base
 9      has_and_belongs_to_many :bugs, 
10                              :join_table => :mantis_bug_monitor_table
11    end

We can now play with our beloved irb to get various information:

• related to a given bug (8254 for example)

1  
2    bug = Bug.find 8254
3    puts "Bug #{bug.id} (#{bug.summary}), project '#{bug.project.name}' is monitored by:" 
4    bug.users.each do |u|
5      puts "\t#{u.username}" 
6    end          

• all bugs monitored by a given user:

1  
2    u = User.find_by_username "jdoe" 
3    puts "User #{u.username} monitors the following bugs:" 
4    u.bugs.each do |bug|
5      puts "#{bug.id} (project '#{bug.project.name}') : '#{bug.summary}'" 
6    end

The anti-RoR database

We just saw that some legacy databases are just easy for RoR to play with. Unfortunately not all, databases are on this side. Let’s take an exemple: The RoR-Killer database. Although this database follows conventions, these conventions do not just quite match RoR’s ones:

• All names are in CamelCase
• Primary key is named Id instead of id
• Primary key is not autoincremented and rely on sequence tables
• Table names are not pluralized.
• Join table do have an Id column.

So to work on a real example, let’s create a simple rails application, that will mimic the Mantis tables we just saw:

 1  
 2    hal> rails antiror
 3              create
 4              create  app/controllers
 5              create  app/helpers
 6              create  app/models
 7              create  app/views/layouts
 8              ...
 9              create  log/server.log
10              create  log/production.log
11              create  log/development.log
12              create  log/test.log
13    hal> 

We also need to create our database. Here is the SQL schema used (sqlite in my example):

 1  
 2    CREATE TABLE Bug (
 3            "Id" INTEGER PRIMARY KEY NOT NULL, 
 4            "Summary" varchar(255), 
 5            "ProjectId" INTEGER
 6    );
 7  
 8    CREATE TABLE Bug_User (
 9            "Id" INTEGER PRIMARY KEY NOT NULL, 
10            "BugId" INTEGER, 
11            "UserId" INTEGER
12    );
13  
14    CREATE TABLE Project (
15            "Id" INTEGER PRIMARY KEY NOT NULL, 
16            "Name" varchar(30)
17    );
18  
19    CREATE TABLE User (
20            "Id" INTEGER PRIMARY KEY NOT NULL,
21            "Username" varchar(30)
22    );

So let’s go on for the juicy parts: we need to teach to RoRo to adhere our conventions ;) To host our modifications, we’re going to create a file, named activerecord-perverted.rb and located in the lib directory. We also have to add the following line at the end of the boot/environment.rb file:

1  
2    require 'lib/activerecord-perverted'

Teaching RoR about our table names

We have to teach to RoR that our table names are the same as our model names (i.e. the Project model is serialized in the Project table). We also need to teach it our table are not pluralized.

By adding:

1  
2    module ActiveRecord
3      class Base
4        pluralize_table_names = false
5      end
6    end

to our lib/activerecord-perverted, we explicitely indicate that our tables not pluralized.

We then need to say to RoR: “Use the model name as the table name”. This can be translated into “Use the class of our (current) model as the table name”, taking into account the Single Inheritance Table model of RoR (see ActiveRecord::Base for further information).

We’re going te redefine the table_name class method of ActiveRecord to get our class name. However instead of using self.class we have to use the function class_name_of_active_record_descendant, as to support SIT(Single Table Inheritance), we need to have the name of the _first descendant of ActiveRecord::Base class_.

 1  
 2    module ActiveRecord
 3      class Base    
 4        pluralize_table_names = false
 5  
 6        class << self
 7  
 8          def reset_table_name
 9            class_name_of_active_record_descendant(self)
10          end
11  
12        end
13  
14      end
15    end

We’re redefining the class method reset_table_name as this method is called the first time the table_name method is called, and redefines the table_name method to return the table name as a String.

Teaching RoR our new primary key name convention.

This is achieved once again by the set_primary_key class function. So let’s add it to our ActiveRecord::Base class:

 1  
 2    module ActiveRecord
 3      class Base    
 4        pluralize_table_names = false
 5  
 6        class << self
 7  
 8          def reset_table_name
 9            class_name_of_active_record_descendant(self)
10          end
11  
12        end
13  
14      end
15  
16      set_primary_key "Id"   
17  
18    end

Now depending our your database type you have nothing to do _regarding the primary key_. For example SQLite does not need anything to work nicely. But if you use mySQL it won’t work as is: you won’t be able to create more than one project for example, due to the fact that the primary key is not defined as auto-incremented but also that we explicitely set a new primary key (set_primary_key).

So we need to allocate ourselves a new key.

Dealing with non autoincremented primary keys.

The database I’m trying to interface with relies on sequences to get a distincs value for each Id. The idea is that for each table Foo we have a sequence table FooSequence, whise only column is named id and is a integer.

Before each requests a new value is retrieved from the sequence table and used as the primary key.

So the SQL for our sequence tables is:

 1  
 2    CREATE TABLE BugSequence (
 3            "id" INTEGER NOT NULL DEFAULT 0, 
 4    );
 5  
 6    CREATE TABLE Bug_UserSequence (
 7            "id" INTEGER NOT NULL DEFAULT 0, 
 8    );
 9  
10    CREATE TABLE ProjectSequence (
11            "id" INTEGER NOT NULL DEFAULT 0, 
12    );
13  
14    CREATE TABLE UserSequence (
15            "id" INTEGER NOT NULL DEFAULT 0, 
16    );

By default RoR relies on an auto-incremented primary key, and as such does not want to prefetch the primary key value before any creation (except in the Firebird case). The idea is to force this prefetch for mySQL and to furnish to RoR a way to get the next key value.

The good news is that RoR supports (although not directly) our sequences: we need to indicate it the name of our sequences, the fact that we need to retrieve a new primary key value before creating a new row, and to describe how to compute this new value.

To define the name of the sequence table we’re going to work on we need to redifine reset_sequence_name (the same way we redifined reset_table_name) in our beloved ActiveRecord::Base:

 1  
 2    module ActiveRecord
 3      class Base    
 4        pluralize_table_names = false
 5  
 6        class << self
 7  
 8          def reset_table_name
 9            class_name_of_active_record_descendant(self)
10          end
11  
12          def reset_sequence_name
13            "#{table_name}Sequence" 
14          end
15  
16        end
17  
18      end
19  
20      set_primary_key "Id"   
21  
22    end

We now need to re-open the MysqlAdapter and re-define the prefetch_primary_key? method:

 1  
 2    module ActiveRecord
 3      module ConnectionAdapters
 4        class MysqlAdapter
 5  
 6          def prefetch_primary_key?(table_name = nil)
 7            true
 8          end
 9      end
10    end

The documentation for prefetch_primary_key? says that if it’s set to true, then the next_sequence_value function will be called to get a new value… so it seems we need this one too:

 1  
 2    module ActiveRecord
 3      module ConnectionAdapters
 4        class MysqlAdapter
 5  
 6          def prefetch_primary_key?(table_name = nil)
 7            true
 8          end
 9  
10          def next_sequence_value(sequence_name)
11            def next_sequence_value(sequence_name)
12              sql  = "UPDATE #{ sequence_name} SET Id=LAST_INSERT_ID(Id+1);" 
13              update(sql, "#{sequence_name} Update")
14              select_value("SELECT Id from #{ sequence_name}",'Id')
15            end
16          end
17  
18      end
19    end

We’re basically telling to mySQL to get a new value based on the last one …

Dealing with Id in the join table.

RoR conventions forbid the use of a column named as the primary key in a join table (this is due to the fact that RoR includes all attributes of a join table in one of the two joinees, leading, in the case one of this attributes is named as the primary key, to shadowing the primary key of this model).

To overcome this situation we have to resort to Rails 1.1 Join Model: in this case the Join Table is considered as a plain model of its own, and each of the joinees is said to look for the other one through the Join Model.

To be continued

---

Generated on Wed Mar 01 23:33:28 CET 2006