Topic: Dynamic table creation and access

I have a problem that I have been trying to solve and I am hoping someone else will have some insight.  I apologize in advance for this post because it is long, and I don't know if I can really explain the situation, but I'll sure try.

I need to find some way to dynamically create and reference tables.  Here is an example of what I am trying to do:

Let's say I'm programming a site that maintains to-do lists for different clients.  For reasons out of my control each clients to-do list must be stored in it's own table.  The client with id=1 would store all of their to-do's in a table called todos_1, client 2 would have theirs in todos_2 and so on.  Each time a new client is added, a new table will be created for there to-do's. While a client is logged in the Todo class needs to reference the todos_<client_id> table rather than the default todos table.  If possible I would like to create the table in the migration style (ruby code rather than SQL for database portability reasons.

I think I can just clone the model class, and change the table name in order to access the different tables. For example:

class TodoController < ApplicationController
def index
    Todo_client = Todo.clone
    Todo_client.table_name = "todos_#{params[:client_id]}"
    @todos = Todo_client.find(:all)
  end
end

I'm not sure if I actually need to clone the model because I don't really know how the server handles multiple instances, but this just seems safer.  If anyone can knows otherwise, please let me know.

The real problem comes to table creation.  I would like to create the table for each client when they create their account and are assigned a client_id.  So when some one signs up and gets client_id 25, the table todo_25 would be created.  I would like to use the same (or similar) create_table method that migrations use, but I don't know how to access it from within a model.  Some one please help!!  I would really appreciate any suggestions.

Re: Dynamic table creation and access

I was able to find some help in the post http://railsforum.com/viewtopic.php?id=674.  From that I learned that I could create a table with the following code (which I put in my model).

def self.create_table
  begin
    ActiveRecord::Schema.define do
      create_table "todo_#{@client_id}" do |t|
        t.column :subject, :string
        t.column :notes, :text
        t.column :milestone_id, :integer
        t.column :created_on, :datetime
        t.column :updated_on, :datetime
      end
    end
    return true
  rescue Exception => err
    return err.message
  end
end

Now to make things really difficult.  I would like to be able to use has_many, belongs_to, etc. constraints with other tables that have been created this same way.  For example:

If each client has milestones stored in their own tables (milestones_client_id) and tasks belong to a milestone.  How could I relate these two things from with in the model.  Any creative ideas.  I know that this isn't an ideal set up for a database, but the table structure really is out of my control.  Again, any insight would be helpful.

Last edited by ajpierson43 (2006-10-06 17:57:55)

Re: Dynamic table creation and access

Does anyone know what the effect of adding
set_table_name "todos_#{session[:client_id]}" to the todo model and set_table_name "milestones_#{session[project_id]}" to the milestone model would be?  Does each session (session isn't really the right word, I mean each instance of the program running on the webserver) have it's own objects?  Is it possible for the Todo model to have different names in multiple concurrent sessions?  I'm sorry if these are elementary questions, but this is my first rails project (and first exposure to ruby) and I just don't quite know how it all works yet.

Re: Dynamic table creation and access

ajpierson43 wrote:

Does each session (session isn't really the right word, I mean each instance of the program running on the webserver) have it's own objects?

Yes, for this same reason setting $global variables in Rails is often a bad idea because they won't be shared across multiple fcgi (or whatever you are using) processes. The answer for global variables is usually to write it to the database or something, but that doesn't exactly apply to generating/altering classes dynamically. You just need to make sure that any change you make such as altering a class takes place on all processes.

Railscasts - Free Ruby on Rails Screencasts

Re: Dynamic table creation and access

Thank you for your quick reply!  Just to confirm that I understood your post, you think it should be safe to change the table name because the change made in one process will be isolated from a change made in any other running processes?

Re: Dynamic table creation and access

Calling set_table_name on a class will only change the class in that process, so it is isolated from the other running processes, yes.

As for it being safe, I'm not sure I understand why it would be. I don't consider it a good thing that one process is different from another because you never know which process a request is going to get sent to. It's basically a random thing. There isn't a 1 to 1 mapping between each process and user session. A given server may have 8 fcgi processes running to handling the 200 requests coming in.

Edit: After rereading your original post, it appears you can solve this problem without dynamically creating tables or models. All you need to do is have one clients table and one todos table which contains a client_id column. The Client has_many :todos and the Todo belongs_to :client.

Edit 2: Okay, after rereading it again, I see you need to store each todo list in a separate table. That does make things difficult...

Last edited by ryanb (2006-10-06 19:58:01)

Railscasts - Free Ruby on Rails Screencasts

Re: Dynamic table creation and access

Darn, I thought that was too good to be true.  Back to the drawing board I guess.

Re: Dynamic table creation and access

I have found a solution using a modified version of the dr_nic_magic_models gem.  If anyone is interested, contact me and I'll let you know what I did, but I'm sure this is a specialized enough situation that I'm alone in needing this.  If anyone has any ideas for converting the gem to a library though, I'd be interested to hear about it.

Re: Dynamic table creation and access

ajpierson43 wrote:

I have found a solution using a modified version of the dr_nic_magic_models gem.  If anyone is interested, contact me and I'll let you know what I did, but I'm sure this is a specialized enough situation that I'm alone in needing this.

Hi ajpierson43,

although I won't be currently needing this, I think it would be great if you post your solution anyway.

Someone else may need it in a couple of months and personally I find it to be the most frustrating experience when my exact question can be found on google, but the answer is not posted. #mad#

Anyway, just my two cents, I'm glad though that you found your answer.

Re: Dynamic table creation and access

boyles,

Thanks for your comment.  I agree, nothing bugs me more than that.  When the solution is ironed our, I'll post it.

Re: Dynamic table creation and access

You may want to consider posting it in the tutorials forum, that way you get a chance at winning the iPod shuffle. smile

Railscasts - Free Ruby on Rails Screencasts

Re: Dynamic table creation and access

I'm considering something similar and would like to see the solution posted too.

The only problem I can think of when using dynamic tablenames is that you have to make sure to set it to the correct tablename based on session data before each access. Not sure if this gives much of an overhead in Rails.

Re: Dynamic table creation and access

I'm sorry it took me so long to respond.  The truth is, I don't have this problem ironed out exactly as I want it yet, and I've been trying to get it perfect before I post it.  I've decided I may never get it perfect, so here's what I have:

First of all, when the client is created, I create the tables.  I put the code for creating and deleting the tables in it's own class, so I will post both here.

module Foo
  class Client < ActiveRecord::Base
    before_destroy :delete_tables
   
    def after_create
      create_tables
    end
   
    def <=>(client)
      self.name <=> client.name
    end
   
    def create_tables
      tm = Foo::TableManager.new :client_id => id
      tm.create_tables
     
      #load Magic Models
      MagicModels::Schema.load_magic_classes(id)
    end
   
    def delete_tables
      tm = Foo::TableManager.new :client_id => client_id
      tm.delete_tables
      #TODO - Remove constants that were created for models that go with these tables     
    end
   
    # this is just a way to dynamically access constants within this class
    # I would rather just extend Foo::Base and get the constants
    # But I havn't figured out how to do it yet because this class must exist before the tables are created,
    # and the tables need to exist before Foo::Base can be extended.
    def clientTodo
    "Client#{id}Todo".constantize
    end
   
    def cilentMilestone
      "Client#{id}Milestone".constantize
    end
  end

  class TableManager
    def logger
      @@logger ||= Foo::Logger
    end
   
    def initialize(options)
      raise InvalidArgumentError, 'Cliend doesn't exist for the given ID: #{options[:client_id]}" unless options[:client_id].nil?       
      prefix = "client#{options[:client_id]}"
      @todo_table_name = "#{prefix}_todos"
      @milestone_table_name = "#{prefix}_milestones"
    end
   
    def create_tables
      begin
        # For some reason class variables weren't working in the table definitions, so I had to set these
        todo_table = @todo_table_name
        milestone_table = @milestone_table_name
       
        ActiveRecord::Schema.define do
         
          create_table todo_table do |t|
            t.column :name, :string, :null => false
            t,column :milstone_id, :integer
            t.column :notes, :text
            t.column :due_date, :datetime
            t.column :created_on, :datetime
            t.column :updated_on, :datetime
          end
         
          create_table milestone_table do |t|
            t.column :name, :string, :null => false
            t.column :date, :datetime
          end
        end
        return true
      rescue Exception => err
        logger.error "Error creating table \n #{err.message}"
       
        return false
      end
    end
   
    def delete_tables
      begin
        table_array = Array.new
        table_array << @todo_table_name
        table_array << @milestone_table_name
       
        ActiveRecord::Schema.define do
          table_array.each do |table|
            drop_table table
          end
        end
        return true
      rescue Exception => err
        logger.error "Error deleting table \n #{err.message}"
        return false
      end
    end 
  end
end


Rather than always changing the table name in the model, I decided I would create a class for each of the instances (class Client1Todo for table client1_todos, Client2Todo for client2_todos, etc.)  Rather  than physically writing the class for each one, I modified dr_nic_magic_models to fit my needs.  I will post most of that code here.  I'm still working on this code, so it's still a little confusing.  What is does is amazing though, Dr Nic is a genius.  It dynamically creates the model, and I have that model just including a few modules to give it some additional functionality.  It also figures out constraints and validations based on the schema.

module MagicModels
 
  class Schema
    class << self
     
      @models = nil   
     
      def logger
        @@logger ||= MagicModels::Logger
      end
     
      def models
        load_schema if @models.nil?
        @models
      end
     
      def fks_on_table(table_name)
        load_schema if @models.nil?
        @fks_by_table[table_name.to_s] || []
      end
     
      # This should be a last resort if a class can't be found.  Called when ConstMissing is called
      # Classes should be loaded when the server is started and as clients are added.
      def find_magic_class(class_id)
        class_name = class_id.to_s
        #logger.debug "find_magic_class - start => #{class_name}"
       
        #only process classes that fit the semantics of a magic model (Client(n)Class)
        return false unless class_name =~ /^Client\d+(Todo|Milestone)/
        logger.debug "#{class_name} is a MagicClass"
        # Break the table name into its components
        # Client1Todo => client_id = 1, type = Todo
        client_id, type = /Client(\d+)(.*)/.match(class_name).captures
        logger.debug "attempting to load all magic classes for client id #{client_id}"
        if load_magic_classes(client_id)
          # All models related to the client should now be loaded, but this returns the specific one that was requested
          return class_name.constantize if Module.constants.include? class_name
        else
          logger.error "Unable to load MagicClass #{class_name}"
        end
      end
     
      # Loads classes associated with a client
      def load_magic_classes(client_id)
        raise "No database connection" unless (@conn = ActiveRecord::Base.connection)
       
        #load the client so that we know it is a valid client
        begin
          #client = Clent.find(client_id)
          #logger.debug "loading classes for #{client.name}"
        rescue
          logger.error "Error loading client.  Make sure that they exist"
          return false
        end
       
        prefix = "client#{client_id}"
       
       
       
        @models = ModelHash.new
        @tables = Hash.new
        @fks_by_table = Hash.new   
        @link_tables = Hash.new       
       
        all_tables = @conn.tables
        tables = []

        #find all pertinent tables
        all_tables.each do |table|
          next unless table =~ /^client#{client_id}_(todos|milestones)/
          tables << table
         
        end
        klasses = []
        tables.each do |table_name|
          class_name = table_name.classify
          client_id, type = /client(\d+)_(.*)/.match(table_name).captures
          base_module_name = "Foo::#{type.classify}"
         
          logger.debug "Got a model table: #{table_name} => class #{class_name}"
         
          @models[class_name] = table_name
          @tables[table_name] = class_name
         
          # create by MAGIC!
          # create our class!
          class_def = <<-end_eval
             
              class #{class_name} < ActiveRecord::Base
                CLIENT_ID = '#{client_id}'
                TYPE = '#{type.singularize}'               
                include #{base_module_name}
                set_table_name('#{table_name}')
              end
            end_eval
          logger.debug("###########################################\n#{class_def}\n##################################")
          eval(class_def, TOPLEVEL_BINDING)           
          klass = class_name.constantize
          # magic up some validation         
          klass.send(:extend, MagicModels::Validations)
          klass.send(:generate_validations)
          klasses << klass
        end
       
        # include Foo::Base to the classes after they are all loaded
        # all tables must already be created before this can be extended
        # because it genrates constants based on the classes that use those tables
        klasses.each do |klass|
          klass.extend Foo::Base
        end
               
       
        # Process FKs?
        if @conn.supports_fetch_foreign_keys?         
         
          tables.each do |table_name|
            logger.debug "Getting FKs for #{table_name}"
            @fks_by_table[table_name] = Array.new                       
            @conn.foreign_key_constraints(table_name).each do |fk|
              logger.debug "Got one: #{fk}"
              @fks_by_table[table_name].push(fk)             
            end # do each fk
           
          end # each table
        end           
        # Try to work out our link tables now...
        @models.keys.sort.each{|klass| process_table(@models[klass.to_s])}
        #@link_tables.keys.sort.each{|table_name| process_link_table(table_name) if @@link_tables[table_name]}       
       
       
        return true
      end
     
      def process_table(table_name)
       
        #logger.debug "Processing model table #{table_name}"
       
        prefix, type = /(client\d+)_(.*)/.match(table_name).captures
       
        # ok, so let's look at the foreign keys on the table...
        belongs_to_klass = @tables[table_name].constantize rescue return
     
        processed_columns = Hash.new
       
        fks_on_table(table_name).each do |fk|       
          logger.debug "Found FK column by suffix _id [#{fk.foreign_key}]"           
          has_some_klass = Inflector.classify(fk.reference_table).constantize rescue next
        processed_columns[fk.foreign_key] = { :has_some_klass => has_some_klass }
          processed_columns[fk.foreign_key].merge! add_has_some_belongs_to(belongs_to_klass, fk.foreign_key, has_some_klass) rescue next         
        end   
       
        column_names = @conn.columns(table_name).map{ |x| x.name}
        column_names.each do |column_name|       
          next if not column_name =~ /_id$/
          logger.debug "Found FK column by suffix _id [#{column_name}]"
          if processed_columns.key?(column_name)
            logger.debug "Skipping, already processed"
            next
          end
          begin
            has_some_klass = Inflector.classify("#{prefix}_#{column_name.sub(/_id$/,'').pluralize}").constantize
          rescue
            has_some_klass = Inflector.classify("#{column_name.sub(/_id$/,'').pluralize}").constantize rescue next
          end
        processed_columns[column_name] = { :has_some_klass => has_some_klass }       
          processed_columns[column_name].merge! add_has_some_belongs_to(belongs_to_klass, column_name, has_some_klass) rescue next
        end
       
        #TODO: what if same classes in table?
       
        # is this a link table with attributes? (has_many through?)
        return if processed_columns.keys.length < 2
       
        processed_columns.keys.each do |key1|
          processed_columns.keys.each do |key2|
            next if key1 == key2
            logger.debug "\n*** #{processed_columns[key1][:has_some_class]}.send 'has_many', #{processed_columns[key2][:belongs_to_name].to_s.pluralize.to_sym}, :through => #{processed_columns[key2][:has_some_name]}\n\n"
            processed_columns[key1][:has_some_class].send 'has_many', processed_columns[key2][:belongs_to_name].to_s.pluralize.to_sym, :through => processed_columns[key2][:has_some_name].to_sym
          end             
        end
       
      end
     
     
      def add_has_some_belongs_to(belongs_to_klass, belongs_to_fk, has_some_klass)
       
        logger.debug "Trying to add a #{belongs_to_klass} belongs_to #{has_some_klass}..."
       
        # so this is a belongs_to & has_some style relationship...
        # is it a has_many, or a has_one? Well, let's assume a has_one has a unique index on the column please... good db design, haha!     
        unique = belongs_to_klass.get_unique_index_columns.include?(belongs_to_fk)
        belongs_to_name = belongs_to_fk.sub(/_id$/, '').to_sym
       
        logger.debug "\n*** #{belongs_to_klass}.send 'belongs_to', #{belongs_to_name}, :class_name => #{has_some_klass}, :foreign_key => #{belongs_to_fk}\n"
        belongs_to_klass.send(:belongs_to, belongs_to_name, :class_name => has_some_klass.to_s, :foreign_key => belongs_to_fk.to_sym)
       
        # work out if we need a prefix
        #has_some_name = ((unique ? belongs_to_klass.table_name.singularize : belongs_to_klass.table_name.pluralize) + (belongs_to_name.to_s == has_some_klass.table_name.singularize ? "" : "_as_"+belongs_to_name.to_s)).downcase.to_sym
        logger.debug "#{belongs_to_name.to_s} == #{has_some_klass::TYPE.singularize}"
        has_some_name = ((unique ? belongs_to_klass::TYPE.singularize : belongs_to_klass::TYPE.pluralize) + (belongs_to_name.to_s == has_some_klass::TYPE.singularize ? "" : "_as_"+belongs_to_name.to_s)).downcase.to_sym
        method = unique ? :has_one : :has_many         
        logger.debug "\n*** #{has_some_klass}.send(#{method}, #{has_some_name}, :class_name => #{belongs_to_klass.to_s}, :foreign_key => #{belongs_to_fk.to_sym})\n\n"
        has_some_klass.send(method, has_some_name, :class_name => belongs_to_klass.to_s, :foreign_key => belongs_to_fk.to_sym)
       
        return { :method => method, :belongs_to_name => belongs_to_name, :has_some_name => has_some_name, :has_some_class => has_some_klass  }
       
      end
     
      def process_link_table(table_name)
       
        logger.debug "Processing link table #{table_name}"
       
        classes_map = Hash.new
        column_names = @conn.columns(table_name).map{ |x| x.name}
       
        # use foreign keys first
        fks_on_table(table_name).each do |fk|
          logger.debug "Processing fk: #{fk}"
          klass = Inflector.classify(fk.reference_table).constantize rescue logger.debug("Cannot find model #{class_name} for table #{fk.reference_table}") && return
          classes_map[fk.foreign_key] = klass             
        end
       
        logger.debug "Got #{classes_map.keys.length} references from FKs"
       
        if classes_map.keys.length < 2
         
          #Fall back on good ol _id recognition
         
          column_names.each do |column_name|
           
            # check we haven't processed by fks already
            next if ! classes_map[column_name].nil?
            referenced_table = column_name.sub(/_id$/, '')
           
            begin           
              klass = Inflector.classify(referenced_table).constantize                             
              # fall back on FKs here
              if ! klass.nil?               
                classes_map[column_name] = klass               
              end
            rescue
            end   
          end
        end
       
        # not detected the link table?
        logger.debug "Got #{classes_map.keys.length} references"
        logger.debug "Cannot detect both tables referenced in link table" && return if classes_map.keys.length != 2
       
        logger.debug "Adding habtm relationship"
       
        logger.debug "\n*** #{classes_map[column_names[0]]}.send 'has_and_belongs_to_many', #{column_names[1].sub(/_id$/,'').pluralize.to_sym}, :class_name => #{classes_map[column_names[1]].to_s}, :join_table => #{table_name.to_sym}\n"
        logger.debug "\n*** #{classes_map[column_names[1]]}.send 'has_and_belongs_to_many', #{column_names[0].sub(/_id$/,'').pluralize.to_sym}, :class_name => #{classes_map[column_names[0]].to_s}, :join_table => #{table_name.to_sym}\n\n"       
       
        classes_map[column_names[0]].send 'has_and_belongs_to_many', column_names[1].sub(/_id$/,'').pluralize.to_sym, :class_name => classes_map[column_names[1]].to_s, :join_table => table_name.to_sym
        classes_map[column_names[1]].send 'has_and_belongs_to_many', column_names[0].sub(/_id$/,'').pluralize.to_sym, :class_name => classes_map[column_names[0]].to_s, :join_table => table_name.to_sym
       
      end       
    end
  end
 
  class ModelHash < Hash
    def unenquire(class_id)
      @enquired ||= {}
      @enquired[class_id = class_id.to_s] = false
    end
   
    def enquired?(class_id)
      @enquired ||= {}
      @enquired[class_id.to_s]
    end
   
    def [](class_id)
      enquired?(class_id = class_id.to_s)
      @enquired[class_id] = true
      super(class_id)
    end
  end
end


Here are the modules I extend.  The Todo module would be included for Client1Todo, whereas the Milestone module would be included in Client1Milestone.  Both would extend Base.  This allows the class Client1Todo to be referenced by simply Todo in any classes for client 1.

module Foo    
  Logger = RAILS_DEFAULT_LOGGER rescue Logger.new(STDERR)
 
  class FooError < StandardError #:nodoc:
  end
  class InvalidFormatError < FooError #:nodoc:
  end
  class InvalidArgumentError < FooError #:nodoc:
  end

  def self.append_features(base) #:nodoc:
    super
    base.extend(ClassMethods)
    base.helper_method :fooTodo, :fooMilestone
  end
 
  # these methods will be avaialble in ActionController::Base (and all subclasses)
  # plus ApplicationHelper (and all subclasses) so they can be used in the views in needed
  # This is made possible by the append_features function
  module ClassMethods
    def fooTodo(client_id = session[:client])
      "Client#{client_id}Todo".constantize
    end
   
    def fooMilestone(client_id = session[:client])
      "Client#{client_id}Milestone".constantize
    end
   
    module_function :fooTodo, :fooMilestone
  end
 
  module Base
    def Base.extended(mod)
      mod.const_set :Todo, Foo::ClassMethods.fooTodo(mod::CLIENT_ID)
      mod.const_set :Milestone, Foo::ClassMethods.fooMilestone(mod::CLIENT_ID)
    end
  end

  module Todo
  # add any functionality you want here
  end

  module Milestone
    def Milestone.included(klass)
      klass.before_destroy :destroy_todos
      #this could also include additional validations not picked up by MagicModels
      # klass.validates......
    end
   
    def destroy_todos
      Todo.destroy_all("milestone_id = #{id}")
     
    end
  end
end


Sorry it's a bit messy, but it's a good fit for my situation.  Hope it is helpful for you.