Skip to content

Migrating instiki from one database type to another

The Rant

Instiki is one of the premier wikis for Ruby on Rails which is another way of saying the other rails-based solutions don’t look that great so far from what I’ve seen. Here is an except from the instiki website:

1. Download
2. Run “instiki” with Ruby 1.8.4 or greater. (on windows, that’s instiki.cmd)
3. Chuckle… There Is No Step Three™! :) (See note)

Yeah, nice and simple… to install. Here’s some facts that instiki DOESN’T tell you without digging around:

  • It uses sqlite as its database backend to store all wiki information (except images and file uploads).
  • It’s NOT Rails 1.2.x ready
  • It was written BEFORE ActiveRecord and was migrated to ActiveRecord later
  • You can export your data but have no way to import your data easily (more later)
  • The documentation from a USER perspective is pretty sparse
  • Active development seems to be moving at a snail’s crawl

So what does this all mean? Well, if you want to migrate your instiki instance to a different instance you’d like to think you have a couple of options.

  1. Just copy the whole application to the new server, kickstart done (This works)
  2. Dump out the wiki in some export friendly format and re-import it back in

Option #1 works quite well however if you want to switch database backends you’re stuck with option #2. However save yourself hours of time (I didn’t) and DON’T try the following things (unless you’re a rails god. In which case why are you reading this?)

  • Try exporting the wiki in textile format and importing it…. oops import is busted. A stub is there (http://localhost:2500/wiki/import) but it is definitely broken
  • Dump the sqlite database with sqlite utilities then re-import them into target database. This doesn’t work with MySQL as far as I know. I bet it’s broken with Postgresql too
  • Look at the source code
    to find where the issue might be. Don’t worry, even the changelogs say import is busted and there is no other sign of some way to input data easily
  • Write some tool to import textile in. That would require reading the APIs, right? bleh
  • Write some sort of ActiveRecord translator? Well sort of… but it’d be nice if there was a tool available. And if you’re not a rails guru you’re sort of screwed in this case.

Now I’m sure DHH (the creator of instiki) will grumble about something about this not being ‘appropriate’ for its original intentions and blah-blah. Gee that’s nice. Maybe you better put that down in the caveats, before I commit a whole bunch of data into it.

The Fix

Okay, enough ranting about half-baked Open Source Rails apps. Here’s the fix.

1. Add plugin script to instiki app

Most normal rails app will include a file called plugin inside $RAILS_ROOT/script/plugin. Unfortunately, instiki doesn’t include it. Luckily the script is really small since it just calls out to the rails framework to handle most of the hardwork. So try this:

$ echo "
#!/usr/bin/env ruby
require File.dirname(__FILE__) + '/../config/boot'
require 'commands/plugin'
" >> $INSTIKI_HOME/script/plugins
$ chmod +x $INSTIKI_HOME/script/plugin  # Make it executable
2. Install the manage_fixtures plugin

I’m afraid I don’t have time to explain what a Rails Fixture is in this post but just think of it as a portable way of representing data going into a database for rails. It works well but don’t try hundreds of megabytes of data.

I also won’t go much into rails plugins except to say it’s a way of extending a rails application with more functionality (probably for a Rails developer rather than an end user of the application). We’re going to use a really sweet tool called manage_fixtures which allows us to move around data between different databases with ease (assuming the rails app isn’t doing weird SQL crap).

$ cd $INSTIKI_HOME
$ script/plugin discover    # Hit enter a gazillion times
$ script/plugin install manage_fixtures
3. Export the data into fixtures

The following will export all your db entries as fixtures into $INSTIKI_HOME/test/fixtures. You need to set the RAILS_ENV to production because by default it will try to use the development database.

$ cd $INSTIKI_HOME         # You SHOULD be here anyways
$ RAILS_ENV='production' rake  db:fixtures:export_all
4. Change the database connection to desired

Now change the file in $INSTIKI_HOME/config/databases.yml from:

production:
  adapter: sqlite3
  database: db/production.db.sqlite3

to the following (adjust mysql parameters to suit your setup)

production:
  adapter: mysql
  host: localhost
  database: instiki
  username: root
  password:
4. Create database and the appropriate tables

The following commands will create a database named instiki (or whatever you put in databases.yml) and make sure the database schema in the database matches instiki’s schema.

$ echo "create database instiki" | mysql -u root
$ RAILS_ENV='production' rake migrate
5. Finally re-import the data from fixtures

Whew! Finally we can re-import the data into MySQL (or whatever) and continue on using instiki for something.

$  RAILS_ENV='production' rake db:fixtures:import_all

{ 2 } Comments

  1. forex trading system | February 7, 2007 at 11:44 pm | Permalink

    great article good read!

  2. Jacques Distler | February 8, 2007 at 12:41 am | Permalink

    Wow! I’ve been wondering how to migrate data to another database. For that matter, I’ve been wondering how to delete an individual page from Instiki.

    I can do that in MySQL (if worst comes to works) but I’m rather unfamiliar with SQLLite.