Lab 6 - Porting to MySQL (Part 2)

Once you've got a working rails installation with Active Scaffold it's time to port everything over to MySQL.

First we will backup the data that we have.
We don't really need to since they're only fixtures at this point, but it's good to know how to do.

cd /var/webapps/WhatSayYe/
# http://github.com/adamwiggins/yaml_db - database agnostic data dumper
script/plugin install git://github.com/adamwiggins/yaml_db.git
rake db:data:dump # data gets stored in db/data.yml

You need a few libraries for Rails that don't install by default with mysql

sudo apt-get install mysql-server libmysqlclient15off libmysqlclient15-dev
sudo gem install mysql

Now MySQL should be alive and well and ready for us to make a user

mysql -u root

CREATE USER 'whatsayye'@'localhost' IDENTIFIED BY 'MySuperSecretPassphrase';
GRANT ALL PRIVILEGES
  ON `whatsayye`.*
  TO 'whatsayye'@'%';

# If you need to access mysql from a computer other than the localhost
# CREATE USER 'whatsayye'@'%' IDENTIFIED BY 'AnotherSecretSuperPassphrase';
# GRANT SELECT,INSERT,UPDATE,DELETE
#  ON `whatsayye`.*
#  TO 'whatsayye'@'localhost';

SELECT user, password, host FROM mysql.user;

If you wish to run your mysql server on one machine but access it from another you'll need to enable that like so.

#/etc/mysql/my.cnf
bind-address www.your-ip-address-or-domain-name.example.tld

sudo /etc/init.d/mysql restart

Now change the database connector in Rails from SQLite to MySQL 5

# config/database.yml
development:
  adapter: mysql
  database: whatsayye
  username: whatsayye
  password: MySuperSecretPassphrase
  pool: 5

Create the database anew

rake db:create
rake db:migrate
rake db:data:load

Violla! You have successfully migrated from SQLite to MySQL.

Unlike Symfony, however, Rails does not support many of the lower-level database functions so we'll have to add in our foreign key checking and such manually.

Install MySQL Workbench, and the other MySQL GUI Tools if you so desire.

Open MySQL Workbench and from the Database menu select Reverse Engineer.

Update the database to use foreign key constraints, cascading, etc.

From the database menu select Synchronize.

Note that you will be able to see the ALTER TABLE script before it is executed.
It would be wise of you to store it in the respective sections in the migrations (however, you will need to comment it out if you change from MySQL back to SQLite or to another database engine).

./script/generate migration add_foreign_keys

# db/migrate/20090929062303_add_foreign_keys.rb
class AddForeignKeys < ActiveRecord::Migration
  def self.up
    execute "
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

ALTER TABLE `whatsayye`.`answers` CHANGE COLUMN `answer` `answer` VARCHAR(255) NOT NULL DEFAULT NULL  , CHANGE COLUMN `question_id` `question_id` INT(11) NOT NULL DEFAULT NULL  , 
  ADD CONSTRAINT `fk_for_question`
  FOREIGN KEY (`question_id` )
  REFERENCES `whatsayye`.`questions` (`id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
, ADD UNIQUE INDEX `conceptual_key` (`answer` ASC, `question_id` ASC) 
, ADD INDEX `fk_for_question` (`question_id` ASC) ;

ALTER TABLE `whatsayye`.`questions` CHANGE COLUMN `url` `url` VARCHAR(255) NOT NULL DEFAULT NULL  , CHANGE COLUMN `question` `question` VARCHAR(255) NOT NULL DEFAULT NULL  , CHANGE COLUMN `user_id` `user_id` INT(11) NOT NULL DEFAULT NULL  , 
  ADD CONSTRAINT `fk_for_owner`
  FOREIGN KEY (`user_id` )
  REFERENCES `whatsayye`.`users` (`id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
, ADD INDEX `fk_for_owner` (`user_id` ASC) ;

ALTER TABLE `whatsayye`.`results` CHANGE COLUMN `user_id` `user_id` INT(11) NOT NULL DEFAULT NULL  , CHANGE COLUMN `answer_id` `answer_id` INT(11) NOT NULL DEFAULT NULL  , 
  ADD CONSTRAINT `fk_for_answer`
  FOREIGN KEY (`answer_id` )
  REFERENCES `whatsayye`.`answers` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, 
  ADD CONSTRAINT `fk_for_owner`
  FOREIGN KEY (`user_id` )
  REFERENCES `whatsayye`.`users` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD UNIQUE INDEX `conceptual_key` (`user_id` ASC, `question_id` ASC, `answer_id` ASC) 
, ADD INDEX `fk_for_answer` (`answer_id` ASC) 
, ADD INDEX `fk_for_owner` (`user_id` ASC) ;

ALTER TABLE `whatsayye`.`users` CHANGE COLUMN `email` `email` VARCHAR(255) NOT NULL DEFAULT NULL  
, ADD UNIQUE INDEX `conceptual_key` (`email` ASC) ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
    "
  end

  def self.down
    # Ideally this would be the exact inverse of the above
    # so that downgrading the 'migration' by 1 version would be easy
  end
end

Who would like to do this with Oracle XE or MSSQL?