Custom Schema during Database Migration from SQLite to MySQL using Taps

Migrating data from one DBMS to another one can be tricky business.

Here’s a nice way to migrate data from an SQLite3 file to a MySQL database using taps. Well, the nice way is actually well documented here: Taps for Easy Database Transfers.

So this post is actually about working around a simple problem: SQLite doesn’t have a scope for decimal columns, but MySQL does. When taps rebuilds the database schema in MySQL it simply assumes those decimal columns had scope 0, which makes them integer columns. The data migration will thus result in rounded values in all those columns!

And here’s how you can workaround this problem for Ruby On Rails projects. Replace the all-uppercase placeholders with your info.


# Start the taps server on the original SQLite DB
taps server sqlite://DATABASE_FILE 'TAP_USER' 'TAP_PW'

# Create the database as per database.yml, assuming proper privileges
rake db:create

# Manually load the schema
rake db:schema:load

# Pull the data, but skip the schema and don't copy the schema_migrations table
taps pull -e schema_migrations -s 'mysql://USER:PW@localhost/DATABASE' http://TAP_USER:TAP_PW@localhost:5000

Be aware that taps uses the default encoding for it’s MySQL connections, so you might have to set something like this in your ~/.my.cnf

[client]
default-character-set=utf8

Hope this helps:-)

Advertisements

One thought on “Custom Schema during Database Migration from SQLite to MySQL using Taps

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s