Have Geek Brain

Dynamic table names for Active Record models

I have an interesting problem with Active Record and I'm not really sure what the cleanest solution is. The legacy database I am integrating with has a strange wrinkle in its schema where one logical table has been "split" into multiple physical tables. Each table has the same structure, but contains data about different elements.

I am not very clear on this (as you can tell!). Let me try and explain with a specific example. Let's say we have a Car that has one or more wheels. Usually we will represent this with a car table and a wheel table:

CREATE TABLE cars (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255),
  ;etc
)

CREATE TABLE wheels (
  `id` int(11) NOT NULL auto_increment,
  `car_id` int(11) NOT NULL,
  `color` varchar(255),
  ;etc
)

So far so good. But with the "partioning" strategy that is in my legacy in the database, it looks more like:

CREATE TABLE cars (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255),
  ;etc
)

CREATE TABLE car_to_wheel_table_map (
  `car_id` int(11) NOT NULL,
  `wheel_table` varchar(255)
)

CREATE TABLE wheels_for_fords (
  `id` int(11) NOT NULL auto_increment,
  `car_id` int(11) NOT NULL,
  `color` varchar(255)
)

CREATE TABLE wheels_for_buicks (
  `id` int(11) NOT NULL auto_increment,
  `car_id` int(11) NOT NULL,
  `color` varchar(255)
)

CREATE TABLE wheels_for_toyotas (
  `id` int(11) NOT NULL auto_increment,
  `car_id` int(11) NOT NULL,
  `color` varchar(255)
)

So, here we have a set of tables wheel_for_x and car_to_wheel_table_map, which contains a mapping of car_id to special wheels_for_x, which contain wheels for a particular car. If I want to find a set of wheels for a car. First I need to figure out which wheel table to use through the car_to_wheel_table_map table and then look at the wheel table specified in the car_to_wheel_table_map.

Firstly, can someone enlighten me if there is a standard name for this method?

Second, does anyone have any guidance as to how I can make this work in Active Record in its purest form. The way I see it, I can either the Wheel Model where the table name can be defined for each instance, or I can dynamically create the model classes at runtime with the correct table name as specified in the mapping table.

EDIT : Please note that changing the schema to be closer to what AR wants is not an option. Various legacy codebases rely on this schema and cannot really be changed.

0


a source to share


7 replies


Splitting database tables is a fairly common practice. I would be surprised if someone hasn't done this before. How about ActsAsPartitionable? http://revolutiononrails.blogspot.com/2007/04/plugin-release-actsaspartitionable.html



Another possibility: can your DBMS pretend that partitions are one big table? I think MySQL supports this.

+2


a source


You can do it here. Basics (up to 70 lines of code):

  • create a has_many for each car type
  • define a "wheel" method that uses the table name in the association to get the correct wheels.


Let me know if you have any questions.

#!/usr/bin/env ruby
%w|rubygems active_record irb|.each {|lib| require lib}
ActiveSupport::Inflector.inflections.singular("toyota", "toyota")
CAR_TYPES = %w|ford buick toyota|

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
  :adapter => "sqlite3",
  :database => ":memory:"
)

ActiveRecord::Schema.define do
  create_table :cars do |t|
    t.string :name
  end

  create_table :car_to_wheel_table_map, :id => false do |t|
    t.integer :car_id
    t.string :wheel_table
  end

  CAR_TYPES.each do |car_type|
    create_table "wheels_for_#{car_type.pluralize}" do |t|
      t.integer :car_id
      t.string :color
    end
  end
end

CAR_TYPES.each do |car_type|
  eval <<-END
    class #{car_type.classify}Wheel < ActiveRecord::Base
      set_table_name "wheels_for_#{car_type.pluralize}"
      belongs_to :car
    end
  END
end

class Car < ActiveRecord::Base
  has_one :car_wheel_map

  CAR_TYPES.each do |car_type|
    has_many "#{car_type}_wheels"
  end

  delegate :wheel_table, :to => :car_wheel_map

  def wheels
    send("#{wheel_table}_wheels")
  end
end

class CarWheelMap < ActiveRecord::Base
  set_table_name "car_to_wheel_table_map"
  belongs_to :car
end


rav4 = Car.create(:name => "Rav4")
rav4.create_car_wheel_map(:wheel_table => "toyota")
rav4.wheels.create(:color => "red")

fiesta = Car.create(:name => "Fiesta")
fiesta.create_car_wheel_map(:wheel_table => "ford")
fiesta.wheels.create(:color => "green")

IRB.start if __FILE__ == $0
+1


a source


How about this? (here's the gist: http://gist.github.com/111041 )

#!/usr/bin/env ruby
%w|rubygems active_record irb|.each {|lib| require lib}
ActiveSupport::Inflector.inflections.singular("toyota", "toyota")

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
  :adapter => "sqlite3",
  :database => ":memory:"
)

ActiveRecord::Schema.define do
  create_table :cars do |t|
    t.string :name
  end

  create_table :car_to_wheel_table_map, :id => false do |t|
    t.integer :car_id
    t.string :wheel_table
  end

  create_table :wheels_for_fords do |t|
    t.integer :car_id
    t.string :color
  end

  create_table :wheels_for_toyotas do |t|
    t.integer :car_id
    t.string :color
  end
end

class Wheel < ActiveRecord::Base
  set_table_name nil
  belongs_to :car
end

class CarWheelMap < ActiveRecord::Base
  set_table_name "car_to_wheel_table_map"
  belongs_to :car
end

class Car < ActiveRecord::Base
  has_one :car_wheel_map
  delegate :wheel_table, :to => :car_wheel_map

  def wheels
    @wheels ||= begin
      the_klass = "#{wheel_table.classify}Wheel"
      eval <<-END
        class #{the_klass} < ActiveRecord::Base
          set_table_name "wheels_for_#{wheel_table.pluralize}"
          belongs_to :car
        end
      END

      self.class.send(:has_many, "#{wheel_table}_wheels")
      send "#{wheel_table}_wheels"
    end
  end
end

rav4 = Car.create(:name => "Rav4")
rav4.create_car_wheel_map(:wheel_table => "toyota")

fiesta = Car.create(:name => "Fiesta")
fiesta.create_car_wheel_map(:wheel_table => "ford")

rav4.wheels.create(:color => "red")
fiesta.wheels.create(:color => "green")

# IRB.start if __FILE__ == $0
+1


a source


I would make this link to a custom function in the model:

has_one :cat_to_wheel_table_map

def wheels
  Wheel.find_by_sql("SELECT * FROM #{cat_to_wheel_table_map.wheel_table} WHERE car_id == #{id}")
end

Maybe you can do it using a link with: finder_sql, but I'm not sure how to pass arguments to it. I used the Wheel model, which you have to determine if you want your data to be mapped to ActiveRecord. You can probably make this model from one of the existing wheel tables.

And I haven't tested this;).

0


a source


Sorry, I know your problems. Whoever thought of splitting tables in your database should have been fired by your boss and hired by mine; -)

Anyway, the solution (via RailsForum): http://railsforum.com/viewtopic.php?id=674

- use Dr. Nic Magic.

Greetings

0


a source


Assumption 1: You know what type of car you're looking at, so you can tell if it's a ford or a gimmick.

Lets put the make of the car in the attribute called (of all things). You should normalize this later, but simplify it for now.

CREATE TABLE cars (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255),
  'make' varchar(255),
  #ect
)


class Wheel < ActiveRecord::Base
   def find_by_make(iMake)
       select("wheels_for_#{iMake}.*").from("wheels_for_#{iMake}");
   end
 #...
end

You can add some protection there to check and change the order of using iMake. You can also do something to make your table exist.

Now we write to the table. I'm not sure how this would work. I just read about it. Perhaps this is also something simple.

0


a source


Why not just put all wheels on one table and use the standard: has_many? You can do this on migration:

  • create a table of new wheels.
  • transfer data from other tables to a newly created table
  • delete old tables
-2


a source






More articles:


All Articles