Any suggestions for a ruby solution for populating a sqlite database from a mysql source

Question

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 for the sqlite database will be very similar to the mysql database, but not identical. The sqlite databases will be populated with a subset of the schema and data stored in the master mysql database. The data subset will be determined by parameters passed in the http request. This is not intended as a maintenance operation, but a delivery of a subset of the mysql db to client applications as a sqlite db.

I was thinking of trying to modify taps or writing a metal app using sequel to connect to both databases to transfer the data.

Any other suggestions would be appreciated?

Answer 1

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.

Answer 2

Perhaps you could try the yaml_db plugin and because it uses a rake task you could add a cron that did this:

rake db:data:dump RAILS_ENV=production && rake db:data:load RAILS_ENV=lite

Setting up the 'lite' environment which uses the sqlite db.

I geuss also if you wanted to make some modifications to the data you could modify the plugin a little.

Answer 3

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.

Answer 4

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.