dynamically create new tables

[danpberger]

Hi, I'm working on a tool to allow users to upload a .csv file. I'm pretty sure I can get the code to read the .csv file into an existing table, but what I'd like to do is find a way to dynamically create a new table first. I imagine I'd upload the .csv file, create a new table by creating a new migration within the controller and then read that .csv file into the new table. The point of doing this is to allow us to upload these files for later data manipulation. But I can't find any info on how to do this. Any ideas how I can dynamically create a new table?

[ReggieB]

If I was doing this, I'd start with a few design points:

1. I would not keep this volatile data in the same place as my main application data - separate database/schema. That way I'd avoid any chance that data generated on the fly would interfere with core application data.
2. I'd use a factory to build objects to manipulate the data. The generation of these objects would be based on a template.
3. The factory would also hold the information to create new tables, store CSV files and populate the tables with data from the CSV files.
4. I wouldn't bother storing the code to create new tables in the form of migrations. It would probably be simpler to generate and store raw SQL.
5. I'd use duck typing to create the objects used to manipulate the data. That is rather than creating a new class for each dataset/table, I'd create a single template class that could be modified to work with any of the datasets.

The next bit is code that I'm creating on the fly - that is I've not thoroughly tested it. I'm providing it to give you an idea of what I'm trying to achieve rather than final code.

It would be easiest to start with the template class. This will need to be based on ActiveRecord::Base, have code to point it at the alternative data location and set the data table name. I'd store this as lib/data_template.rb

class DataTemplate < ActiveRecord::Base
  establish_connection "data_#{ENV["RAILS_ENV"]}".to_sym
  
  def self.alter_table_name(object_name)
    set_table_name object_name.pluralize
  end

You would have to update database.yml to have new entries for data_development, data_production, data_test:

data_production:
  adapter: mysql
  database: data_production
  username: user_name
  password: password
  host: localhost

data_development:
  adapter: mysql
  database: data_development
  username: user_name
  password: password
  host: localhost

data_test:
  adapter: mysql
  database: data_test
  username: user_name
  password: password
  host: localhost

Now the factory class. This would be a standard Rails model and the data it holds (the information needed to generate a new object) would be stored in a table created via a normal migration. This table would need fields such as: object_name, creation_sql, csv_file_name. You could create methods that would store and retrieve the csv file, create a new table and populate the table with data from the csv file. However the key methods are the ones that would take the object_name and use it to build objects used to manipulate the data:

class ObjectFactory < ActiveRecord::Base
  def build_new_object
    DataTemplate.alter_table_name(object_name)
    DataTemplate.new
  end

  def build_and_find(*args)
    DataTemplate.alter_table_name(object_name)
    DataTemplate.find(args)
  end

  def self.build_new(object_name)
    object_factory = find_by_object_name(object_name)
    object_factory.build_new_object
  end

  def self.build_and_return_all(object_name)
    object_factory = find_by_object_name(object_name)
    object_factory.build_and_find(:all)
  end

  def self.build_and_return_one(object_name, item_id)
    object_factory = find_by_object_name(object_name)
    object_factory.build_and_find(item_id)
  end

end

You could then use the code like this:

# Horses have been uploaded from a CSV file 
# and have populated a new table called horses. 

#create a new horse
@horse = ObjectFactory.build_new("horse")

#get all horses
@horses = ObjectFactory.build_and_return_all("horse")

#get a particular horse with id 43
@horse = ObjectFactory.build_and_return_one("horse", 43)