Ruby on Rails
HowToUseLegacySchemas

Note: if your database supports views then you can alias your table names and column names to Rails conventions.

I have been working on putting a Rails face on an old application. The problem is that many of the assumptions made by Rails may not exist in the existing schema.
set_table_name ‘modulgruppen’

There are a number of techniques that can help addreses this:

Configure environment.rb to not look for pluralized table names
Using set_table_name for table names outside the rails convention
Using set_primary_key for tables whose primary key was not called id
Using views for read only data
Using PostgreSQL Rules to allow updates on views for updatable data
Using find_by_sql for arbitrary complex queries or tables with no primary key
Using before_validation_on_create for non-auto-generated primary keys.
Generating attribute accessors without column prefixes

Shown first are short examples of each technique, followed by a nice real-world example from (Seraphim) that shows many of these techniques in use.

Configure environment.rb to not look for pluralized table names

Open up config/environment.rb and scroll below the comment about “Include your application configuration below”. Add the line

ActiveRecord::Base.pluralize_table_names = false

This will allow you to use the generator scripts if you don’t have pluralized table names.

Using `set_table_name` for table names outside the rails convention

By using the “set_table_name " you can make an ”http://wiki.rubyonrails.org/rails/pages/ActiveRecord" class="existingWikiWord">ActiveRecord object refer to whatever table you want.


  class Entity < ActiveRecord::Base
    set_table_name "entity"
  end

Using `set_primary_key` for tables whose primary key was not called `id`

If your database used different names for its primary key column, you can call “set_primary_key” to define whatever column you want.


  class Entity < ActiveRecord::Base
    set_primary_key "entity_id"
  end

Using views for read-only data

If you have database tables that look like this


    create table cars (car_id serial primary key,make_id int, model_id int);
    create table makes (make_id serial primary key , make_name text);
    create table models (model_id serial primary key, model_name text);

and you’re rather just have the values directly in the car object, you can write a view like this


--  a view to describe a car on rails:-)
create view rail_cars as
  select car_id as id, *
  from cars natural join makes natural join models;

For the purposes of reading, rail_cars should now act as a well-behaved for rails object that includes the car’s make and model name.

Using PostgreSQL Rules to allow updates on views for updatable data

PostgreSQL’s “rule” system allows you to create updatable views [www.postgresql.org]

See HowtoUsePostgresViewsAsTables for detailed instructions

Using `find_by_sql` for arbitrary complex queries or tables with no primary key

If your tables have no logical primary keys you can always resort to using find_by_sql to run arbitrary complex queries that don’t require a primary key anywhere.


Post.find_by_sql ["SELECT * FROM posts WHERE author = ? AND created > ?", author_id, start_date]

Using `before_validate_on_create` for non-auto-generated primary keys.

[someone suggested this tip to me, but I haven’t tried it yet] ok I’ve seen this in many places and in the API docs but can’t get it to work. Can Somebody elaborate on this with a real code example and where to put it (e.g. active record, controller, etc.). Specifically, I have a schema with a table(s) which use the algorithm of ‘select max(ID_COLUMN)+1 from LEGACY_TABLE’ to get the next ID for the primary key. I’ve got a method for this but Rails seems to want to get the value from the sequence (which doesn’t exist).

Answer to above (2005-02-03), see this page: HowToUseNonDatabaseProvidedIDs

(BradG) Here’s how I used this so I could have ‘username’ as a primary key:


Class UserLogin < ActiveRecord::Base
  set_primary_key :username
  attr_accessor :username
  before_validation_on_create 'self.id = @username'

  # this is the only thing I really don't understand; why is this necessary?
  def username_before_type_cast; end
end

And when setting up your object, be sure to explicitly set the username, as opposed to passing it in as parameters:


user_login = UserLogin.new(...)
user_login.username = ...
user_login.save

Using composite primary keys

Rails has no built-in support for composite primary keys (e.g. primary key(col1, col2)). Would someone please explain here how to accomplish it?

Reasons to use a composite primary key include:

Use of a schema not controlled by the programmer
Join tables (I understand Rails 1.1 has some built-in support for this)
Various kinds of weak entities
Multi-valued attributes that you need to be indexable (this is of concern to me; I have a table of dictionary entries and a word_forms table where each row maps a spelling to the id of a dictionary entry. I can’t imagine a justification for a separate id for each word form in this case. Similarly, any application where you want to associate an arbitrary number of “tags” with each row of another table would benefit from a composite primary key, because again, an id field would serve no purpose.
and more

A possible solution to using composite primary keys:

Add an id column to the concerned table. Update this column with incremental values.

And use set_primary_key ‘id’ on your active record class. No need to change db defintion. Adding an id column should do the trick.

Download

composite_primary_keys.gem

from http://rubyforge.org

————————-

The above “possible solution” either isn’t a solution, or makes no sense, or both.

“Add an id column to the concerned table. … No need to change db definition.”

What? Aren’t those two statements directly contradictory to one another?

I do not consider this to be a valid solution to the “composite primary keys” problem. The design philosophy should be that Rails adapts to (valid) database designs, I should not have to alter my database design to fit rails! Composite primary keys are valid, important, and most importantly, common. How is it that we have no solution to this problem?!

In addition, it’s impossible to use :depends on a relationship with a table that has a composite primary key.

Generating attribute accessors without column prefixes

a short article on this, the code from which is below:


class << ActiveRecord::Base

  def set_column_prefix(prefix)
    column_names.each do |name|
      next if name == primary_key

      if name[/#{prefix}(.*)/e]
        a = $1

        define_method(a.to_sym) do
          read_attribute(name)
        end

        define_method("#{a}=".to_sym) do |value|
          write_attribute(name, value)
        end

        define_method("#{a}?".to_sym) do
		  self.send("#{name}?".to_sym)
        end

      end
    end
  end
end

And use it like:


class NewsSection < ActiveRecord::Base
  set_table_name 'nact_news_section'
  set_primary_key 'ns_id'
  set_column_prefix 'ns_'
end

You could use this technique to map out any kind of systematic annoyance in the column names.

——
Comment from Bob Showalter

These lines:


      if name[/#{prefix}(.*)/e]
        a = $1

Could perhaps be better written as:


      if a = name[/#{Regexp.escape(prefix)}(.*)/, 1]

Real World Example: Adjusting the models to fit the data (Seraphim)

Here’s how to adjust the models.

Here, at a publishing house, we have a legacy database that I thought I’d hook up to Rails. The relevant parts of the schema look a little like this (names changed to protect the guilty):


 CREATE TABLE people (
  p_ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  p_Title VARCHAR(255),
  p_FirstName VARCHAR(255),
  p_LastName VARCHAR(255),
  ...);

 CREATE TABLE bookentries (
  BookID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  PersonID INT(11),
  BookText TEXT,
  ...);

 CREATE TABLE books (
  ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  BookName VARCHAR(255),
  ...);

 CREATE TABLE office (
  o_ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  o_Title VARCHAR(255),
  o_Address TEXT,
  city_id INT(11),
  ...);

 CREATE TABLE city (
  city_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  city_name VARCHAR(255),
  ...);

 CREATE TABLE peoplelink (
  ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  p_ID INT(11),
  o_ID INT(11)
 );

We are collecting entries about people to go into a directory of some sort. For each person in each book, we need to be able to collect information both about them and their office, and information specific to their entry in the book.

Before we can start worrying about relationships, we need to get some basics sorted out first. Rails needs to know how to find the data in the first place – we need to tell it table names and indices. This is done by overriding the \ActiveRecord::Base.table_name() and primary_key methods thus:


class Office < ActiveRecord::Base
 set_table_name "office"
 set_primary_key "o_ID"
end

…and so on for each of the other four classes.

With that in place, we can start sorting out the relationships.

The relationships here are as follows:

office belongs to city
person has many offices
office has many people
person has many books
book has many people

Here’s how it works in Rails. We have our four basic models, Person, Office, City and Book. The Person has_and_belongs_to_many Offices, and has_and_belongs_to_many Books. The Office belongs_to a City, and the City has_many Offices.
Simplest first. To say that an Office belongs to a City, we do:

belongs_to :city, :foreign_key=> "city_id"

at the head of the office.rb file. The :foreign_key @ definition tells \ActiveRecord to look for the @city_id field in the office table. By chance, this field has the same name that ActiveRecord would have predicted for it, but it needn’t have.
The reverse relationship is:

has_many :offices, :foreign_key => 'city_id'

at the head of the city.rb file. Now, because this is a has_many relationship, the :foreign_key definition tells \ActiveRecord to look in the office table.

The many-to-any relationships aren’t that much more complicated.


 has_and_belongs_to_many :offices, :join_table => peoplelink', \
  :foreign_key => 'p_ID', :association_foreign_key => 'o_ID'

This goes into the person.rb file. The :foreign_key and :association_foreign_key definitions both refer to field names in the peoplelink table. It can be useful to define the reverse, but I’m not going to bother here. It should be fairly obvious.

To make it clear, the final relationship, between people and books, is defined like this:


 has_and_belongs_to_many :books, :join_table => 'bookentries', \
  :foreign_key => 'PersonID', :association_foreign_key => 'BookID'

That all makes sense, but does scaffold not read the FKs in the original database schema? I’m a little confused as to the entry point into a large schema that already has it’s keys defined. Elsewhere I read that you must physically define a connecting field in a one-to-many relationship for scaffold to work. Is that the case?

What is the easiest way to force downcasing of Model attributes from a table that has its columns defined in mixed or all upper case?

Keep getting Segmentation Faults with Legacy databases?

This was my problem. Rails would run perfectly with a “rails” database but when I tried to connect it to a legacy database it would crash and burn. After weeks of pullin my hair out I did this simple step.

gem uninstall mysql

I then installed the latest ruby-mysql bindings from
here
This fixed the problems with mysql4.1+ that I was having.

Static scaffolding and set_table_name

One caveat not mentioned above is scaffold’s failure to handle preexisting models. This can be seen by drafting a model that overrides set_table_name, the scaffold generator will not utilize this information. The rails devs have marked this as “won’t fix” bug, claiming the role of the generator is to /generate/.

Scaffolding and view names

In my experience, postgres views do not work with the scaffold generator.

SQLServer, Composite PKs, Find! with RecordNotFound, and some sample code

Here is some sample code that some may find useful and better yet I hope for ideas on how to do this stuff better.

I also have some sample code for calling stored procedures using the SQLServer module by Rahoul Baruah if anyone is interested in that.

Here is my email if you have questions or suggestions: cagraff@cox.net


# Note these samples are for ActiveRecord used outside of Rails with SQLServer and ODBC connection
require 'active_record'
require 'composite_primary_keys'

#-- This raises exception if record not found when using bang version of find.
#   It does not work with specialized forms however such as Person.find_by_ssn(...)
class ActiveRecord::Base
  def self.find!(*args)
    records = self.find(*args)
    #puts args.inspect
    raise ActiveRecord::RecordNotFound, "Couldn't find #{name} for given criteria." if records.blank?
    records
  end
end

#-- This is just a convienance for the SQL Server getdate() function
def getdate()
  Date.jd(DateTime.now.jd)
end


#-- Update_all samples - note that it returns rowcount affected
# similate row level locking using pc_throttle 
rowcnt = PcThrottle.update_all("pc_int = pc_int + 1",
                               "table_name IN ('rcv_exp_rcpt_header', 'rcv_exp_rcpt_line')")
# could add "if  rowcnt != 2 then raise exception,..."

rowcnt = Order.update_all("order_status = 'OP'",
                          "order_skey = #{ll_order_skey}")                          
# could add "if  rowcnt != 1 then raise exception,..."

#-- Find samples - note that bang version raises record not found
# parameterized (avoids SQL injection) and not uses bang because none returned is ok here
OrderLine.find(:all, 
               :select => "order_skey, order_line_skey, ol_quantity, pre_release_shipment_skey, select_skey",
               :conditions => ["order_skey = ? and line_no = ? and ol_status = 'AL'",
               shp.header.order_skey, l.line_no] ).each do |sk|
                  # some order_line_skey related stuff here
                  # sk.? == ?
               end

# parameterized (avoids SQL injection) and uses bang
os_hdr_rec = OsHeader.find!(:first,
                            :select => "os_header_skey",
                            :conditions => ["order_skey = ? and shipment_no = ?",
                             shp.header.order_skey, shp.header.ship_no])
#
prs_rec = PreReleaseShipment.find!(:first, 
                                   :select => "pre_release_shipment_skey, last_select_key",
                                   :conditions => ["pre_release_shipment_skey = ?", prs_skey] )
# hack to get record not found exception                              
ord_rec = Order.find_by_order_no!(header.order) or
          raise ActiveRecord::RecordNotFound, "Couldn't find order for #{header.order}."
header.order_skey = ord_rec.order_skey


#-- Aggregate method samples
shp.header.ship_no = ShipmentQueue.maximum(:shipment_no, :conditions => ["order_skey = ?", shp.header.order_skey])

#-- Insert samples
# sample that uses single primary key
# note: MUST use cmthdr.id and MUST NOT use cmtln.comment_skey
# does anyone know how to use cmtln.comment_skey = comment_skey to make consistant with below?
    cmthdr = Comment.new
    cmthdr.id = comment_skey
    cmthdr.comments = comments
    cmthdr.last_actv_dt = getdate() #Date.jd(DateTime.now.jd)
    cmthdr.timestamp = nil
    cmthdr.save!

# this does NOT work as an alternate way for inserting single primary key
# that is redefined with "set_primary_key :comment_skey" in the model
# does anyone know a why or another way? I also tried ":comment_skey => comment_skey,"
    cmthdr = CommentLine.new(
      :id => comment_skey,
      :comments => comments,
      :text => text,
      :last_actv_dt => getdate(), #Date.jd(DateTime.now.jd)
      :timestamp => nil)
    cmthdr.save!

# sample that uses composite primary key
# note: must use cmtln.comment_skey & mtln.comment_line_skey and MUST NOT use cmthdr.id
# does anyone know how to use cmthdr.id (ie. cmthdr.id = [comment_skey, comment_skey] to make consistant with above?
    cmtln = CommentLine.new
    cmtln.comment_skey = comment_skey
    cmtln.comment_line_skey = comment_line_skey
    cmtln.text = text
    cmtln.date_of_last_update = getdate() #Date.jd(DateTime.now.jd)
    cmtln.timestamp = nil
    cmtln.save!

# alternate way for inserting composite primary key
# interesting that this works for "set_primary_keys :comment_skey, :comment_line_skey"
    cmtln = CommentLine.new(
      :comment_skey => comment_skey,
      :comment_line_skey => comment_line_skey,
      :text => text,
      :date_of_last_update => getdate(), #Date.jd(DateTime.now.jd)
      :timestamp => nil)
    cmtln.save!

#                   
#-- Here are some of the the models used above
#
class Order < ActiveRecord::Base
  set_table_name "order_master"
  set_primary_key :order_skey
end

class OrderLine < ActiveRecord::Base
  set_table_name "order_line"
  set_primary_keys :order_skey, :order_line_skey
end

class Comment < ActiveRecord::Base 
  set_table_name "comment"
  set_primary_key :comment_skey
end

class CommentLine < ActiveRecord::Base
  set_table_name "comment_line"
  set_primary_keys :comment_skey, :comment_line_skey
end

Automatically translating model names and table/foreign key names from a legacy schema

If your legacy schema has table names and key names that don’t follow rails conventions, and you don’t want to fill in over your models adding set_table_name to the class and a :foreign_key to every association, you can use the inflector_translations hack found here

More details at the provided URL — vjt@openssl.it

********possible inclusion*********
=set_primary_key in test=
set_primary_key is extremely useful for legacy databases. But when you come to test your program. Can you rename the primary key for the test environment? I think this is necessary, testing my application seems to completely fail, probably because of this. I can’t see a work around.

You can rename the table name in the testing environment.

set_fixture_class instead of set_table_name.

eg set_table_name StaTus
rails class name =status
rails tries to guess table_name from class name, it would pressume tablename to be “statuses” if we hadnt set the table name

in testing
tell rails table name = StaTus by renaming the fixtures file to this
and also fixtures :StaTus at the beginning of tests.
from this information rails tries to work out the class name and pressumes it is = StaTu and so can not find the appropriate class
so “set_fixture_class :StaTus=>status”

Which is just the reverse of set_table_name

***********************************

Composite Keys:
I guess Rails should have some kind of support for it. Composite keys is a very common way to maintain uniqueness when you want to enforce it at the DB level!!!

Updated on August 22, 2008 16:05 by jlwestsr (216.106.117.150)

Ruby on RailsHowToUseLegacySchemas