I have a very large legacy database which I want to build Rails application around. This database does not conform to the usual conventions in that the natural primary keys are not always auto-incrementing integers. In particular, in the example below, the primary key for a small lookup table begins at zero. MySQL will not accept a value of 0 for a typical Rails auto-increment primary key. Other nonstandard database problems, such as column names, are not quite as difficult to work around. This particular case turned out to be more delicate, but the solution is fairly simple to implement. This approach is also more general in that it will work for other types of nonstandard primary keys such as strings.
One option would be to load each table line by line, look up the
corresponding legacy primary key in order to determine the new rails id.
This seems terribly inefficient, requiring a
SELECT for each
This might not be a problem for small applications, but the tables in
question contain around a hundred million observations. This option would
generate too much unnecessary work for the database server.
I had tried simply using a standard migration such as
class CreateLegacyItems ActiveRecord::Migration def self.up create_table (:legacy_items, :primary_key => "legacy_key") do |t| t.column :description, :string end end def self.down drop_table :legacy_items end end
and in the model calling
This wasn’t quite enough because I still wasn’t able to manually set an
equal to zero. With the underlying MySQL code being written with the
auto-increment option, this was being ignored.
The solution was to fool Rails into forgetting to auto-increment the
column by pretending that we were creating a join table by setting the
:id => false. I will give an example using the code for a simple
lookup table which holds several air carrier group descriptions. The model
is named CarrierGroup. I used the following migration:
class CreateCarrierGroups ActiveRecord::Migration def self.up # Create the table, set the primary key to 'code' create_table (:carrier_groups, :id => false, :primary_key => 'code') do |t| t.column :code, :integer # Primary key t.column :description, :string # Group description end # Populate the table grp = CarrierGroup.new grp.id = 0 # Set id not code! grp.description = 'Foreign Carriers' grp.save grp = CarrierGroup.new grp.id = 1 grp.description = 'Large Regional Carriers' grp.save # ... end def self.down drop_table :carrier_groups end end
I also had to manually override the primary key in the model:
class CarrierGroup ActiveRecord::Base set_primary_key 'code' end