Rails and Legacy Databases
Content
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 modelClient
onto this table. By using theset_table_name
class method you can define this:1 2 class Client < ActiveRecord::Base 3 set_table_name "FooBar" 4 end 5The 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 namedid
. By using theset_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 6Note that although you set the new name of the primary key as being in our example
ref
, you’ll still use the methodid
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 7Simple 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
columnWe can note some difference though:
- the table name does not follow the same convention: it always begin with
mantis_
and ends in_table
(for examplemantis_bug_table
). Moreover the “real” name of the table is not pluralized (the table is namedmantis_project_table
and notmantis_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
andmantis_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 usernamemantisread
, no password and on a database namedmantis
.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" ) 12Now 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 = falseWe can now create the
Project
model:1 2 class Project < ActiveRecord::Base 3 endand 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; endNote that we do not create any
BugMonitor
model as themantis_bug_monitor_table
is used as a join table.So now, we have to add relationship between our various models:
- A
Project
has manyBugs
: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 severalUsers
, and aUser
eventually monitores severalBugs
. So it seems that we’re going to use our belovedhas_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 endWe 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 endThe 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 CamelCasePrimary key is namedId
instead ofid
Primary key is not autoincremented and rely on sequence tablesTable names are not pluralized.Join table do have anId
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 thelib
directory. We also have to add the following line at the end of theboot/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 theProject
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 endto 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 ofActiveRecord
to get our class name. However instead of usingself.class
we have to use the functionclass_name_of_active_record_descendant
, as to support SIT(Single Table Inheritance), we need to have the name of the _first descendant ofActiveRecord::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 endWe’re redefining the class method
reset_table_name
as this method is called the first time thetable_name
method is called, and redefines thetable_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 ourActiveRecord::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 endNow 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 tableFoo
we have a sequence tableFooSequence
, whise only column is namedid
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 redifinedreset_table_name
) in our belovedActiveRecord::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 endWe now need to re-open the
MysqlAdapter
and re-define theprefetch_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 endThe documentation for
prefetch_primary_key?
says that if it’s set totrue
, then thenext_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 endWe’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
转载于:https://www.cnblogs.com/lexus/archive/2012/02/21/2361085.html