SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Nov 2008
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

  2. #2
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    # 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)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •