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

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 2

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.