Darren on Ruby: Migrating Rails to Tomcat / JEE, Part 2: Migrating Your Data From SQLite3 to Derby

The Series So Far

Part 1: Switching to JRuby & Apache Derby showed you how to migrate from the Ruby+SQLite3 technology stack to JRuby+Derby.

Now we will take a look at migrating the important stuff: your data. I found two ways to do a data migration: the easy way and the hard way. And unfortunately for me, I found the hard way first.

Update, 01/05/2009: You Don't Really Need to Migrate to Derby

You can continue to use SQLite3 if it so pleases you. You just need to install the necessary gems i.e. jdbc-adapter:

jgem install activerecord-jdbcsqlite3-adapter -y

The other thing is that it seems that SQLite3 allows you to use names for entities that are keywords in Derby e.g. min or max. Check here for a list of keywords used by Derby.

Do It The Easy Way, Do It The Ruby Way

To facilitate the easy way, we need to install the AR_Fixtures Rails plugin (not quite sure why this isn't a Gem, thus making it available to all Rails apps).

ruby script\plugin install http://topfunky.net/svn/plugins/ar_fixtures

This plugin gives us the db:fixtures:dump Rake task. As you'll see next it understands the MODEL and RAILS_ENV commandline/environment variables. It can't dump the entire database, strangely enough.

For this first step you need to go back to RAILS_ROOT\config\database.yml, disable (prepend an underscore) the jdbc development spec and re-enable the sqlite3 development spec. Then run the following commands:

rake db:fixtures:dump MODEL=Post
rake db:fixtures:dump MODEL=Comment
rake db:fixtures:dump MODEL=Tag

Take a look in RAILS_ROOT\test\fixtures; you should see posts.yml, comments.yml and tags.yml.

Now change you RAILS_ROOT\config\database.yml file to point to the jdbc development spec again, then run:

jrake db:migrate
jrake db:fixtures:load

You should now be good to go - start up your app using JRuby and test the blog data was migrated by going here.

Do It The Hard Way, Do It The Way I Did It When I Didn't Know The Easy Way

...Also The Way To Do It If Your Data Isn't Entirely Managed By ActiveRecord

1. Export SQLite3 Data:
   • Run sqlite3 development.sqlite3 .dump >> development_dump.sql in RAILS_ROOT\db
2. Tidy-up SQL Dump:
   • Open development_dump.sql in a text editor and make the following changes:
   • Remove any DDL or DML commands to do with the sqlite_sequence table
   • Remove any DDL or DML commands to do with the schema_migrations table, including the index
   • Unquote all table names e.g. change CREATE TABLE "posts" ... to CREATE TABLE posts ...
   • Replace AUTOINCREMENT NOT NULL with NOT NULL GENERATED BY DEFAULT AS IDENTITY
   • Replace datetime with timestamp
   • Replace integer\([0-9]*\) with integer
   • Replace text with long varchar
   • Replace DEFAULT ([0-9]*|NULL) NULL with DEFAULT NULL
   • Also be sure that any varchar(255) table columns only have that many characters, or else the import will complain and truncate your data (in this case, your precious blogs).
     If varchar(255) is not big enough, just switch it to long varchar
3. Import into Derby:
   • Run ij (the Derby command-line client) in RAILS_ROOT\db
   • Run the following commands in ij:

     connect 'jdbc:derby:blog_development;create=true' as dev;
     run 'development_dump.sql';
     exit;
     

That should be it; you should have seen each model-table being created followed by the corresponding blog data going into Derby. You might see some 'Table already exists' errors when the DDL statements execute; these will occur if you previously ran jrake db:migrate, which creates the tables for you - just ignore them. Run the server again using JRuby and you should be able to access your blogs again.

What's Next?

Next I will step through the procedure to package your Rails application to a Web ARchive file, ready to deploy to Tomcat, Jetty or any JEE/Servlet container of your choice. You can catch that blog here

Cheers