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?

Lab 6 - Ruby on Rails

You will be creating a "scaffold" backend with "fixtures" for your database.
Do it with our example database to become familiar with the process.

Install what needs installing:
Note that the Ubuntu package manager is out of date for rails so we need to grab rubygems and rails from ruby.

sudo apt-get install vim-ruby ruby-full git-core sqlite3 sqlitebrowser mysql-server libsqlite3-dev
sudo apt-get remove rails rubygems
wget http://rubyforge.org/frs/download.php/60718/rubygems-1.3.5.tgz
tar xf rubygems-*.tgz
cd rubygems-*/
chmod a+x setup.rb
sudo ruby setup.rb
cd /usr/bin
sudo ln -s gem1.8 gem
cd -
sudo gem update --system
sudo gem install rails --include-dependencies
sudo gem install sqlite3-ruby
# download mysql workbench

Setup the site skeleton:

mkdir /var/webapps
sudo chown `whoami`:`whoami` /var/webapps
cd /var/webapps
rails WhatSayYe
cd WhatSayYe
git init
# Just so that these folders are not empty
touch log/.gitignore
touch doc/.gitignore
touch tmp/.gitignore
cat - > .gitignore << EOF
log/*.log
tmp/**/*
doc/api
doc/app
EOF

Create the site scaffold

# User
script/generate scaffold User email:string crypted_password:string password_salt:string persistence_token:string
  # has_many questions
  # has_many answers through results

# Question
script/generate scaffold Question url:string question:string user:references 
  # belongs_to user

# Answer
script/generate scaffold Answer answer:string question:references
  # belongs_to question

# Result
script/generate scaffold Result user:references answer:references 
  # Exclusively a join table

Instantiate the database:

rake db:create
rake db:migrate

Now establish the relationships and the label in the models:

# app/models/user.rb
class User < ActiveRecord::Base
  has_many :questions
  def to_label
    return email
  end
  def to_s
    return to_label
  end

end

# app/models/question.rb
class Question < ActiveRecord::Base
  belongs_to :user
  has_many :answers
  has_many :results, :through => :answers
  def to_label
    return question
  end
  def to_s
    return to_label
  end

end

# app/models/answer.rb
class Answer < ActiveRecord::Base
  belongs_to :question
  has_many :results
  def count
    return results.length
  end
  def to_label
    return answer
  end
  def to_s
    return to_label
  end
end

# app/models/result.rb
class Result < ActiveRecord::Base
  belongs_to :user
  belongs_to :answer
  def to_label
    return "#{user} chose '#{answer}'"
  end
  def to_s
    return to_label
  end
end

Create 'fixtures' of sample data

# test/fixtures/users.yml
# Read about fixtures at http://ar.rubyonrails.org/classes/Fixtures.html
coolaj86:
  email: coolaj86@nomail.com
bretswan:
  email: bret.swan@nomail.com

# test/fixtures/questions.yml
everything:
  url: "everything"
  question: "What is the answer to Life, the Universe, and Everything?"
  user: coolaj86

swallow:
  url: "swallow"
  question: "What is the average airspeed velocity of an unladen swallow?"
  user: bretswan

# test/fixtures/answers.yml
love:
  answer: "to love"
  question: everything

forty_two:
  answer: "42"
  question: everything

learn:
  answer: "to learn"
  question: everything

meters:
  answer: "11 meters per second"
  question: swallow

miles:
  answer: "24 miles per hour"
  question: swallow

species:
  answer: "African or European swallow?"
  question: swallow

coconut:
  answer: "It's not a question of velocity! It's a simple question of weight ratios! A five ounce bird could not carry a one pound coconut."
  question: swallow

# test/fixtures/results.yml
swallow_1:
  user: coolaj86
  answer: species

swallow_2:
  user: bretswan
  answer: coconut

everything_1:
  user: coolaj86
  answer: forty_two

everything_2:
  user: bretswan
  answer: forty_two

Now it should be fair to give it a test run:

rake db:fixtures:load
script/server
# view at http://localhost:3000/users
# view at http://localhost:3000/questions
# view at http://localhost:3000/answers
# view at http://localhost:3000/results

Switch to Active Scaffold

cd /var/webapps/WhatSayYe
script/plugin install git://github.com/activescaffold/active_scaffold.git
script/plugin install git://github.com/ewildgoose/render_component.git -r rails-2.3
#http://activescaffold.com/

#
# This must be added to each of the following
#
# app/views/layouts/users.html.erb
# app/views/layouts/questions.html.erb
# app/views/layouts/answers.html.erb
# app/views/layouts/results.html.erb
<%= javascript_include_tag :defaults %>
<%= active_scaffold_includes %>

#
# Replace your controllers like so
#
# app/controllers/users_controller.rb
class UsersController < ApplicationController
  active_scaffold :user do |config|
    config.columns.exclude :created_at
    config.columns.exclude :updated_at
    config.columns.exclude :password_salt
    config.columns.exclude :crypted_password
    config.columns.exclude :persistence_token
  end
end

# app/controllers/questions_controller.rb
class QuestionsController < ApplicationController
  active_scaffold :questions do |config|
    config.columns.exclude :created_at
    config.columns.exclude :updated_at
  end
end

# app/controllers/answers_controller.rb
class AnswersController < ApplicationController
  active_scaffold :answer do |config|
    config.columns.exclude :created_at
    config.columns.exclude :updated_at
  end
end

# app/controllers/results_controller.rb
class ResultsController < ApplicationController
  active_scaffold :result do |config|
    config.columns.exclude :created_at
    config.columns.exclude :updated_at
  end
end

We'll cover the MySQL setup in lab.

In progress... still...
* setup mysql
* switch to mysql with the diagram
* customize active scaffold
* create jsonic web service
* create 3.0 web app
* switch as to admin backend
* switch webapp to frontend


#script/plugin install git://github.com/activescaffold/active_scaffold.git

#http://railscasts.com/episodes/81-fixtures-in-rails-2-0
# Fixtures

Why do we use IDs? Conceptual vs Logical Diagrams

Some of you seem to be confused as to the difference between a conceptual primary key and a logical primary key - why we use 'id' if the id is actually the email address or SSN.

Here you can see a visualization of each of relationships in our database conceptually and logically.

In the logical diagram, notice that there is a unique index on each of the conceptual primary keys and that the logical 'id's reduces the complexity (and therefore margin of error) of the relationship.

Conceptual Relationship Visualizations

Note that the key for the questions table isn't the question text and the user's e-mail. It is the user's primary key (which happens to be the e-mail) and the question text.

The key for the answers table is only two keys, not three - the primary key of the questions table (which happens to be a composite key) and the answer text.

Likewise, the key for the results table is only two keys, not four.

Logical Relationship Visualizations

Note the unique index on question_text, user_id in the questions table keeps the same constraints on the table that the conceptual key did.

Questions?

Lab 3 - Project Description

As soon as everyone has turned in their labs this template will be populated with the best-of-breed documentation we get.

Project: What Say Ye? - "Quick Polls for Quick People"

• Homepage: http://www.whatsayye.com

Description:
A wiki-style poll web application built in rails.

Business Rules:

• a
• b
• c

Assumptions:

• a
• b
• c

EER Diagram:

Data Dictionary:

Table1

• a
• b
• c

Copyright 2009 AJ ONeal (and a class full of other contributors)

All documentation is licensed under the GNU FDL v1.2 or later.

All code and psuedo-code is licensed under the GPL v2 or later as applicable.
All material may also be available under another license.

Prevent XSS with an API key!

Ruby on Rails usually isn't susceptible to XSS / Cross Site Scripting Attacks because it uses an authenticity_token.

However, with our site we're trying to rid ourselves of all of this web 1.0 crap and stick with an architecture that is friendly to 3rd party web applications.

Following the mantra of "my interface sucks", we must assume that the user may have a 3rd party interface and will never access our own and we have to provide a way to prevent XSS without relying on the Same Origin Policy at all.

Flickr does this by using an API key. And what better way to test our API than to use it on our own site?

If we actually get around to that, these links looks useful:
http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/4bf83a14fc82432f
http://stackoverflow.com/questions/350299/rails-plugin-for-api-key-secret-key-signing

Lab 2 Creating a database with SQLite

Here's my version of what we drew up on the board.

CREATE TABLE "answers" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "answer" varchar(255), "question_id" integer, "created_at" datetime, "updated_at" datetime);
CREATE TABLE "questions" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "url" varchar(255), "question" varchar(255), "user_id" integer, "created_at" datetime, "updated_at" datetime);
CREATE TABLE "results" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "user_id" integer, "question_id" integer, "answer_id" integer, "created_at" datetime, "updated_at" datetime);
CREATE TABLE "schema_migrations" ("version" varchar(255) NOT NULL);
CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "email" varchar(255), "crypted_password" varchar(255), "password_salt" varchar(255), "persistence_token" varchar(255), "created_at" datetime, "updated_at" datetime);
CREATE UNIQUE INDEX "unique_schema_migrations" ON "schema_migrations" ("version");

• Save this as file named whatsayye-schema.sql
• Create the database: sqlite3 whatsayye.sqlite3 < whatsayye-schema.sql
• Use sqlite3 or sqlitebrowser to add / edit data

Notice: This has an extra relationship in it (one result belongs to one question) that we found out in a later lab that we don't need.

Abstract

Here we shall document bits and pieces of things for IT350 (Database Design) at BYU.

The sample project for the class is WhatSayYe.com

Copyright 2009 AJ ONeal (and a class full of other contributors)

All documentation is licensed under the GNU FDL v1.2 or later.

All code and psuedo-code is licensed under the GPL v2 or later.
All material may also be available under another license.