Creating tables & models on the fly (dynamically) in Ruby on Rails 3

Question

Bear with me on this one.

In an app I'm working on users are able to upload CSV files into the system, with any headers they like and any columns in the data. The CSV is then used to generate a table in the database and the data written to it, it can then be accessed through the system for various uses, searches, sorts updates etc.

The old (and now defunct system) was in PHP and handled this fine, although quite messily with lots of raw sql to create the tables and the framework supported magic-models (if the table existed so did the object without a class being defined in a model file)

The new version is being written in RoR3, and I am yet to figure out a way to do this. I've managed to sort out the table creation by calling migration tools inside a model (not very Rails-y I know, but needs must...), but I cannot find a way to link to the new table once it's created to write in the data, build relationships or anything else.

What I'm hoping for is either,

a) someone on here has a better way of doing this than creating tables and models on the fly (a warning here, these files can contain 100'000's of records and different fields so a single table option doesn't work so well) i.e a better database design for this issue.

or

b) can tell me how to sort out the model issue.

I've looked at Dr Nic's Magic Model gem for RoR but it doesn't seem to work in RoR3 unless I'm doing something wrong

Sorry for the wall of text, look forward to any suggestions

Thanks in advance

Answer 1

OK, i got a solution i think, but if is nice thats different thing.

Basically you create a table on the fly by executing SQL thru Rail ActiveRecord. Next use a Model and change its name (Model.table_name).

Something like this:

    // SQL Create table
    sql = "CREATE TABLE my_table (id int(11) NOT NULL AUTO_INCREMENT, code varchar(3) NOT NULL)"
    ActiveRecord::Base.connection.execute(sql)

Then with a model you can change the table name on the fly, like:

MyModel.table_name = "my_table"
records = MyModel.all

Ok, one of your problems is the model logic & associations. You are kind of limited, but maybe you can workaround that.

Not really best practices i guess, but if you need this. I think it might work!

Answer 2

I have implemented app which is used to upload a csv file and convert the file into a active record model. you can checkout this repository.

Visit https://github.com/Athul92/Salary_Sheet_Comparison/blob/master/app/models/makemodel.rb

here is a small idea about how it was achieved:

   class Makemodel < ActiveRecord::Migration
     def self.import(file,project_name,file_name,start_row,end_row,unique,last_column)
       spreadsheet = open_spreadsheet(file)
       header = spreadsheet.row(start_row.to_i)
       i=0
       header.count.times do
         unless header[i].nil?
           header[i]= header[i].gsub(/[^0-9A-Za-z]/, '').downcase
         end
         i+=1
       end
       name = "#{project_name.downcase}"+"#{file_name.downcase}"
       create_table name.pluralize do |t|
         header.each do |head|
           t.string head
         end
       end
       model_file = File.join("app", "models", name.singularize+".rb")
       model_name = name.singularize.capitalize
       File.open(model_file, "w+") do |f|
         f << "class #{model_name} < ActiveRecord::Base\nend"
       end

       ((start_row.to_i+1)..end_row.to_i).each do |i|
         row = Hash[[header, spreadsheet.row(i)].transpose]
         #should find a logic to find the model class that is being created
         product = Object.const_get(name.capitalize).new
         product.attributes = row.to_hash
         product.save!
       end
     end

     def self.open_spreadsheet(file)
       case File.extname(file.original_filename)
         when ".csv" then Csv.new(file.path, nil, :ignore)
         when ".xls" then Roo::Excel.new(file.path)
         when ".xlsx" then Roo::Excelx.new(file.path)
         else raise "Unknown file type: #{file.original_filename}"
       end
     end
   end

There are also some glitches with this it is difficult to add association and also validations