How can I copy a mySQL Database in ruby on rails?

Question

We are making a Ruby On Rails webapp where every customer gets their own database.
The database needs to be created after they fill out a form on our website.

We have a template database that has all of the tables and columns that we need to copy. How can I do this in programatically from ruby on rails?

Answer 1

From any controller, you can define the following method.

 def copy_template_database
     template_name = "customerdb1" # Database to copy from
     new_name = "temp" #database to create & copy to

        #connect to template database to copy.  Note that this will override any previous
        #connections for all Models that inherit from ActiveRecord::Base
        ActiveRecord::Base.establish_connection({:adapter => "mysql", :database => template_name, :host => "olddev",
        :username => "root", :password => "password" })

     sql_connection = ActiveRecord::Base.connection 
        sql_connection.execute("CREATE DATABASE #{new_name} CHARACTER SET latin1 COLLATE latin1_general_ci")
     tables = sql_connection.select_all("Show Tables")
        #the results are an array of hashes, ie:
        # [{"table_from_customerdb1" => "customers"},{"table_from_customerdb1" => "employees},...]
     table_names = Array.new
     tables.each { |hash| hash.each_value { |name| table_names << name }}

     table_names.each { |name| 
      sql_connection.execute("CREATE TABLE #{new_name}.#{name} LIKE #{template_name}.#{name}")
      sql_connection.execute("INSERT INTO #{new_name}.#{name} SELECT * FROM #{template_name}.#{name}")
     }
        #This statement is optional.  It connects ActiveRecord to the new database
     ActiveRecord::Base.establish_connection({:adapter => "mysql", :database => new_name, :host => "olddev",
        :username => "root", :password => "password" })
    end

Note that I do not know for sure if this will keep foriegn key integrity. I think it depends a lot on how the template Database is created.

Answer 2

You could put your template schema creation code into a script which contains all of the required table/index/view/procedure creation statements, call it "template_schema.sql" or whatever and then just run the script on the database of your choice (from Ruby, if that's what you're after) and you're done.

The best approach is probably to have each database object in a separate file under source control (to make it easy to track changes on individual objects) and then have them merged into a single file as part of the deployment.

Answer 3

I'm not sure what you mean but you can use ruby's command line functionality to dump the template database, create a new database and re-import it using the mysqldump program:

> mysqldump -uroot -proot templateDB > dump.sql
> mysql -uroot -proot --execute="CREATE DATABASE newDB"
> mysql -uroot -proot newDB < dump.sql

Here is a good description of invoking command line options from Ruby.

Answer 4

By using yaml_db

You need to install plugin, dump any rails database (including mysql) into data.yml file using rake task, change connection string to point to new database and then finaly load data.yml into any new database (including mysql) using another rake task. Very straightforward.