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

Darren on Ruby: Migrating Rails to Tomcat / JEE, Part 1: Switching to JRuby & Apache Derby

Prologue

Complete this tutorial as a prelude to this blog. This is less about you learning RoR, but more about giving you a convenient starting point to proceed. If you already have a project in mind that you want to migrate, make a copy and work with that. Note for Windows users: When you get to the point of installing the SQLite3 gem, use version 1.2.3 as at the time of this writing, it is the most recent version compatible with Windows; gem install sqlite3-ruby -v 1.2.3 -y. For your convenience, here are some links you might find useful while completing that tutorial: http://localhost:3000
http://localhost:3000/home/index

Run it on Ruby

So you should now be at a point where you can successfully run your application on Mongrel or WEBrick using MRI. If you haven't done that already, give that a whirl and test it by going here. You should see the 'Hello, Rails!' home page. Now get you hands on the default generated index.html file and put it back in the public folder and hit refresh/F5. You should now see the 'Welcome aboard... You’re riding Ruby on Rails!' welcome page. Click on the 'About your application’s environment' link and you should see a flash message (I think that's what they call it) describing you environment; you should observe two things:

Ruby Version

1.8.6 (i386-mswin32)

Database adapter

sqlite3

Now shut down the web server.

Prepare for JRuby

1. Setup JRuby
   • Download and install JRuby 1.2.0 from here
   • Set your JRUBY_HOME environment variable and add JRUBY_HOME\bin to your PATH environment variable.
   • Checkout this blog for jgem and jrake
2. Install Rails (currently 2.3.2)
   • jgem install rails -y
3. Setup Apache Derby
   • Download and install Derby 10.4.2 from here.
   • Set your DERBY_HOME environment variable and add DERBY_HOME\bin to your PATH environment variable.
   • Copy derby.jar, derbyclient.jar and derbynet.jar in DERBY_HOME\lib to JRUBY_HOME\lib.
   • jgem install activerecord-jdbcderby-adapter-0.9.0 -y (I had an issue with version 0.9.1; something about an uninitialized constant...)
4. Prepare Development DB
   • Update the RAILS_ROOT\config\database.yml file; comment out the default development database definition (or just prepend an underscore or something) and paste in the following below it:

     development:
       adapter: jdbc
       url: jdbc:derby:db\blog_development;create=true
       driver: org.apache.derby.jdbc.EmbeddedDriver
       username: app
       password: app
     

   • Run the following commands:

     jrake db:sessions:create
     jrake db:migrate
     

Run it on JRuby

Now you should be at a point where you can run your application on Mongrel or WEBrick using JRuby. Start it up, this time running jruby script\server and test it again by going here. Click on the 'About your application’s environment' link; this time you'll observe two significant differences:

Ruby Version

1.8.6 (java)

Database adapter

jdbc

Well that's all folks - You have now migrated to JRuby!. But where are all your blogs?!

What's Next?

Next I will run through a couple of ways of migrating your SQLite3 data to Derby; when it's ready you can catch that blog here. Cheers.