Any Suggestions For A Ruby Solution For Populating A Sqlite Database From A Mysql Source

I have a Rails application that uses MySQL. The application needs to populate sqlite databases to be distributed to client applications from the master mysql database. The schema

Solution 1:

Disclaimer: I'm the Sequel maintainer.

Sequel would probably be the easiest way to do this. The bin/sequel tool can copy a MySQL database to an SQLite database, so if you look at the source code to that (http://github.com/jeremyevans/sequel/blob/205d8a04842c143736de76d645a2a955a1ca641f/bin/sequel), you can get an idea of how to do this with Sequel.

Because of the way things are constructed, it's probably easiest to just copy the schema directly, and then drop unneeded columns. Alternatively, I'd just write the code to create the SQLite database schema I wanted.

The code to copy the data can probably use a modified version of what's in bin/sequel.

Solution 2:

If you're working on a similar model on both databases you should notice the "attributes" method of ActiveResource returns a hash of that model's attributes, and "attributes=" receives an attribute hash (it doesn't have to include ALL the model's attributes). Keeping that in mind, you could take advantage of ActiveRecord::Base.establish_connection method to have both models connect to the different databases. That way you could do something like:

MysqlModel.establish_connection(YAML.load_file(path_to_database_yml))
SqliteMode.establish_connection(YAML.load_file(path_to_other_database_yml))

MysqlModel.all.each do |m|
  SqliteModel.create(m.attributes)
end

If the models don't share all fields, or need some processing before assigning it to the Sqlite model, you could build a method that receives the original attributes hash and process it to return the proper attributes hash for the Sqlite model.

Solution 3:

Is your rails app creating the sqlite db on demand for a client, or is this process an out of band maintenance job?

If it's a maintenance job, I would write a shell script to dump the mysql db, do any replacements on the dump to make it sqlite specific, and finally import that to a sqlite db.