The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

dynamically create new tables

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?

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.

I'd use a factory to build objects to manipulate the data. The generation of these objects would be based on a template.

The factory would also hold the information to create new tables, store CSV files and populate the tables with data from the CSV files.

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.

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

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:

# 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)