-
Notifications
You must be signed in to change notification settings - Fork 198
ClockingIT database migration guide
This guide is intended to provide basic instructions for database migration process from ClockingIT 0.9.33 to the latest jobsworth version at master branch.
There's also a new automatic script to migrate the data from hosted in the jobsworth-installer project. You can migrate the data directly to a new version of jobsworth installed in your system.
The guide gives only the idea of how to migrate so any additional information would be appreciated.
- Chats and forums have gone in jobsworth so you lose them after migration.
- You may also lose content of project_files table because some of the migration scripts need project files to reside in file system.
- Ubuntu 12.04 (it's better to do all the stuff in virtual machine because many Ruby versions and its dependencies may be used during migration).
- rvm, ruby 1.9.3, ruby 1.8.7 and other dependencies installed like described in Installation-of-jobsworth-v2.1-on-Ubuntu-12.04.
- ClockingIT database dump obtained from "Preferences/Company Settings" page of hosted version.
The migration process consists of the following steps:
- step 1: Setup jobsworth
- step 2: Checkout jobsworth to 0.9.33 version (or around) and restore database from dump
- step 3: Iterate through commits (from 0.9.33 to master version) which were changed db/migration dir and apply migration scripts to the database. On each iteration you should setup jobsworth configs, correct ruby and gems dependencies for "rake db:migrate" run. The latter is the hardest part of the migration because of version mismatches between ruby dependencies (and because I am not ruby specialist at all :).
- step 4: Dump migrated database for further use on production environment.
The core of migration process is commits iteration strategy. I tried different ones during migration:
-
Iterate through release tags.
-
Iterate till "rake db:migrate" gives migration error, then find migration script which gave the error, find the latest commit for the script and then checkout to the commit and rerun "rake db:migrate". Something like this:
at branch master:
$ rake db:migrate Run database migrations if required. == CopyTaskOwnerToWatcher: migrating ========================================= -- Copying task creators to watchers. rake aborted! An error has occurred, all later migrations canceled: Mysql2::Error: Unknown column 'tasks.type' in 'where clause': SELECT `tasks`.* FROM `tasks` WHERE (tasks.type != 'Template')
find commits on which the migration added
$ git grep CopyTaskOwnerToWatcher db/migrate db/migrate/20081207130300_copy_task_owner_to_watcher.rb:class CopyTaskOwnerToWatcher < ActiveRecord::Migration
$ git log --pretty=oneline db/migrate/20081207130300_copy_task_owner_to_watcher.rb 1a8e1a44b02937292291ac65067db3fb5c4ab994 fix 'Soon-to-be-deprecated ActiveRecord calls' codes : part 5 1d1e66400e127a1701c9724aafca5fda538b74e9 adding file perm changes fa3087c1df9afc5358a65fa8e936e466d1792cbc Instead of owners being notified for every message added to a task, the owner is now added to the default list of people to notify.
switch to the last (newest) one
$ git checkout fa3087c1df9afc5358a65fa8e936e466d1792cbc
update environments
...
and rerun migrate
$ rake db:migrate
But these two strategies didn't lead me to success.
So I manually picked some commits from the set:
$ git log bc80aca96e5591377d10a4555975507e5acee6f6.. --reverse --pretty=oneline db/migrate/
- Iterate through "stable" db/migrate/ state commits
My internal criteria was to pick commits of stable db/migrate/ states (something after merges, end of feature implementation, etc.), so scripts in db/migrate/ have no changes in further history.
The final commit list which I used for successful migration is:
bc80aca96e5591377d10a4555975507e5acee6f6 2a3bc2ad39a82a2c2723b54561f124d38133a475 705274bef308fb3acc2f419ba978800ccab95e8b 5bd73d4a0adf5ebe5d71540da0b8575114382fde edd96e44532267c0967a4473ef38946e09f239b6 8396d923596ddb580431ca9529bf7d122a25f767 06f5df8d5eafd8512f28de8c377c8daa33ebf471 4adc8acec9302abd7bf3625dc652ed0f9f436a47 da85bb40d9b56a94b324b6ec08e6b48cc7ba1a48 master
$ sudo su
$ git clone https://github.com/ari/jobsworth /usr/local/www/jobsworth $ cd /usr/local/www/jobsworth
$ ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock $ source /usr/local/rvm/scripts/rvm $ export RAILS_ENV=production $ rvm use 1.8.7
$ git checkout bc80aca96e5591377d10a4555975507e5acee6f6
It will create all nesessary directories and database and may ask you to install some missing gems. Since we need jobsworth here only for database migration the default database name, user and password may be used (dbname: cit, username: cit, password: cit)
$ ./setup.rb
$ echo "CREATE DATABASE cit DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; GRANT ALL ON cit.* TO 'cit'@'localhost' IDENTIFIED BY 'cit'; FLUSH PRIVILEGES;" | mysql -u root -p $ rake db:schema:load $ rake db:migrate
Now you have database named cit without any data. You'll need now to import ClockingIT backup into the database. But ClockingIT schema differs from schema given at bc80ac commit (I didn't find the right one), so edit backup dump by hand before restoring it. The difference is in number of columns of "companies" table: jobsworth@bc80ac hasn't the last column in the table.
Create backup of backup:
$ cp ClockingIT-backup.sql ~/ClockingIT-backup2.sql
and edit ClockingIT-backup2.sql to achieve something like this:
- INSERT INTO `companies` VALUES ('69706','company','admmin@company.com','admin','2010-02-17 09:04:21.260945','2010-02-17 09:04:21.260945','company','1','1','1','1','0','2012-10-08 10:18:21.907188');
+ INSERT INTO `companies` VALUES ('69706','company','admmin@company.com','admin','2010-02-17 09:04:21.260945','2010-02-17 09:04:21.260945','company','1','1','1','1','0');
Apply the patched backup
$ mysql -p -u cit cit < ~/ClockingIT-backup2.sql
If you still experience problems with columns mismatch then discover error table columns in mysql shell using "DESCRIBE table_name;" command and compare number of columns with ClockingIT-backup2.sql. Then "DROP DATABASE cit", edit the dump and go back to 2.3 step.
Unfortunately I can't provide you with exact commands of environment setup during commits iteration especially for old commits because it heavily depends on your environment. But the main idea is to look into RELEASE NOTES, README and setup.rb, switch to correct ruby version, setup/update ruby gems, recreate configs in config/ dir (setup.rb would do that for you), and then run "rake db:migrate".
$ git checkout 2a3bc2ad39a82a2c2723b54561f124d38133a475
Run setup.rb and follow instructions. DON'T initialize database schema, so say "n" on "Initialize database schema [n]: ".
$ ./setup.rb
Update gems (note "rvm use 1.8.7" at 1.3 step)
$ gem update
Run migration process
$ rake db:migrate
Revert db/schema.rb from previous session
$ git checkout db/schema.rb
Checkout to next commit
$ git checkout 705274bef308fb3acc2f419ba978800ccab95e8b
Run setup.rb and follow instructions. DON'T initialize database schema, so say "n" on "Initialize database schema [n]: ".
$ ./setup.rb
Update gems
$ gem update
Run migration process
$ rake db:migrate
Revert db/schema.rb from previous session
$ git checkout db/schema.rb
Checkout to next commit
$ git checkout 5bd73d4a0adf5ebe5d71540da0b8575114382fde
Run setup.rb and follow instructions. DON'T initialize database schema, so say "n" on "Initialize database schema [n]: ".
$ ./setup.rb
Update gems
$ gem update $ gem install -v=2.3.12 rails $ rake gems:install
Run migration process (it was successful for me only for 2.3.12 rails version)
$ RAILS_GEM_VERSION=2.3.12 rake db:migrate
Revert db/schema.rb from previous session
$ git checkout db/schema.rb
Checkout to next commit
$ git checkout edd96e44532267c0967a4473ef38946e09f239b6
Run setup.rb and follow instructions. DON'T initialize database schema, so say "n" on "Initialize database schema [n]: ".
$ ./setup.rb
Update gems
$ rake gems:install
Run migration process
$ RAILS_GEM_VERSION=2.3.12 rake db:migrate
Revert db/schema.rb from previous session
$ git checkout db/schema.rb
Checkout to next commit
$ git checkout 8396d923596ddb580431ca9529bf7d122a25f767
Switch to ruby 1.9.3
$ rvm use 1.9.3
Run setup.rb and follow instructions. DON'T initialize database schema, so say "n" on "Initialize database schema [n]: ".
$ ./setup.rb
On this step I had problem running rake db:migrate. It was aborted with error: "cannot load such file -- lib/localization" So I commented out localization in config/environment.rb:
#require File.join(File.dirname(__FILE__), '../lib/localization.rb') #Localization.load
and moved out localization rake files:
$ mv lib/tasks/localization.rake lib/tasks/localization.rake.bak $ mv lib/localization.rb -> lib/localization.rb.bak
After that I installed some missing gems for ruby 1.9.3:
$ gem install -v=2.3.12 rails $ gem install -v=1.2.3 test-unit $ gem install mysql $ rake gems:install
Run migration process
$ RAILS_GEM_VERSION=2.3.12 rake db:migrate
Revert all previous changes
$ git checkout lib/tasks/localization.rake $ git checkout lib/localization.rb $ git checkout db/schema.rb
Checkout to next commit
$ git checkout 06f5df8d5eafd8512f28de8c377c8daa33ebf471
Run setup.rb and follow instructions. DON'T initialize database schema, so say "n" on "Initialize database schema [n]: ".
$ ./setup.rb
I had dependencies problems on this step with ffi 1.0.6 and RedCloth 4.2.7. So I patched Gemfile.lock:
--- a/Gemfile.lock
+++ b/Gemfile.lock
@@ -1,7 +1,7 @@
GEM
remote: http://rubygems.org/
specs:
- RedCloth (4.2.7)
+ RedCloth (4.2.9)
ZenTest (4.5.0)
abstract (1.0.0)
actionmailer (3.0.7)
@@ -51,7 +51,7 @@ GEM
xpath (~> 0.1.2)
celerity (0.8.9)
childprocess (0.1.8)
- ffi (~> 1.0.6)
+ ffi (>= 1.1.5)
ci_reporter (1.6.4)
builder (>= 2.1.2)
configuration (1.2.0)
@@ -77,7 +77,7 @@ GEM
abstract (>= 1.0.0)
exception_notification_rails3 (1.2.0)
faker (0.3.1)
- ffi (1.0.7)
+ ffi (1.1.5)
rake (>= 0.8.7)
gchartrb (0.8)
gherkin (2.3.7)
Update gems:
$ bundle install
Patch migration script:
--- a/db/migrate/20100914011439_create_email_addresses.rb +++ b/db/migrate/20100914011439_create_email_addresses.rb @@ -1,3 +1,5 @@ +require 'migration_helpers' +
Run migration process
$ rake db:migrate
Revert all previous changes
$ git checkout Gemfile.lock $ git checkout db/migrate/20100914011439_create_email_addresses.rb $ git checkout db/schema.rb
Checkout to next commit
$ git checkout 4adc8acec9302abd7bf3625dc652ed0f9f436a47
Run migration process
$ ./update.sh
Revert db/schema.rb from previous session
$ git checkout db/schema.rb
Checkout to next commit
$ git checkout da85bb40d9b56a94b324b6ec08e6b48cc7ba1a48
Run migration process
$ ./update.sh
Revert db/schema.rb from previous session
$ git checkout db/schema.rb
Checkout to master branch
$ git checkout master
Run migration process
$ ./update.sh
$ mysqldump --skip-triggers --compact --no-create-info -c cit > ~./jobsworth-dump.sql
And you're done :)