MySQLの外部キー制約をRuby on Railsでやってみた

[SQLアンチパターン](https://www.oreilly.co.jp/books/9784873115894/)に載っていたものをRailsで実装してみる。
RailsとMySQL関連はインストール済みである事を前提としている。

## 環境
Ruby: 2.6.0-preview2
Rails: 5.2.0
MySQL: 8.0

## 適当にアプリを作る

```bash
rails new bug_reports -d mysql
```

`Gemfile`に2つ書き足す。

```Gemfile
gem 'therubyracer'
gem 'bcrypt'
```

上記gemを入れてからとりあえずデータベースを作成する。

```bash
bundle update
rake db:create
```

## 各モデルを作る
### 元のDDL
本に記載されていた、ベースになるDDLは以下の通り。

```sql
CREATE TABLE Accounts (
  account_id        SERIAL PRIMARY KEY,
  account_name      VARCHAR(20),
  first_name        VARCHAR(20),
  last_name         VARCHAR(20),
  email             VARCHAR(100),
  password_hash     CHAR(64),
  portrait_image    BLOB,
  hourly_rate       NUMERIC(9,2)
);

CREATE TABLE BugStatus (
  status            VARCHAR(20) PRIMARY KEY
);

CREATE TABLE Bugs (
  bug_id SERIAL     PRIMARY KEY,
  date_reported     DATE NOT NULL,
  summary           VARCHAR(80),
  description       VARCHAR(1000),
  resolution        VARCHAR(1000),
  reported_by       BIGINT UNSIGNED NOT NULL,
  assigned_to       BIGINT UNSIGNED,
  verified_by       BIGINT UNSIGNED,
  status            VARCHAR(20) NOT NULL DEFAULT 'NEW',
  priority          VARCHAR(20),
  hours             NUMERIC(9,2),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (verified_by) REFERENCES Accounts(account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE ON DELETE SET DEFAULT
);
```

### 枠の生成
これらっぽいモデルを作る。

```bash
rails g scaffold BugStatus status:string{20}
rails g scaffold Account account_id:primary_key account_name:string{20} first_name:string{20} last_name:string{20} email:string{100} password:digest portrait_image:binary 'hourly_rate:decimal{9,2}'
rails g scaffold Bug bug_id:primary_key date_reported:date summary:string{80} description:string{1000} resolution:string{1000} reported_by:bigint assigned_to:bigint verified_by:bigint status:string{20} priority:string{20} 'hours:decimal{9,2}'
```

decimalをシングルクォーテーションで囲っている理由は下記参照。
[Rails g modelの際のdecimal型のフィールドについての注意点](https://qiita.com/noriyotcp/items/6284ae00a6362e8b218b)
`password_hash`の列名が`password`となっているが、`bcrypt`の仕組みに従ったかたちにしている。


### マイグレーションの加工
その後、生成された各マイグレーションを加工する。

```rb:db/migrate/20180623154725_create_bug_statuses.rb
class CreateBugStatuses < ActiveRecord::Migration[5.2]
  def change
    create_table :bug_statuses, id: false do |t|
      t.string :status, limit: 20, null: false
    end
    execute "ALTER TABLE bug_statuses ADD PRIMARY KEY (status);"
  end
end
```

`bug_statuses`は`id: false`と`execute ADD PRIMARY KEY`と`null: false`を変更。
なぜ`primary_key`を使わずに`execute`で`ADD PRIMARY KEY`を実行しているかについては、下記記事を参照。
[Rails4 db:migrateでid以外のカラムにプライマリキーの設定を行う &#8211; PAYFORWARD](https://blog.takanabe.tokyo/2014/11/30/47/)

```rb:db/migrate/20180623163759_create_accounts.rb
class CreateAccounts < ActiveRecord::Migration[5.2]
  def change
    create_table :accounts, id: false do |t|
      t.primary_key :account_id
      t.string :account_name, limit: 20
      t.string :first_name, limit: 20
      t.string :last_name, limit: 20
      t.string :email, limit: 100
      t.string :password_digest
      t.binary :portrait_image
      t.decimal :hourly_rate, precision: 9, scale: 2
    end
  end
end
```

`accounts`は`id: false`のみ変更。

```rb:db/migrate/20180623164335_create_bugs.rb
class CreateBugs < ActiveRecord::Migration[5.2]
  def change
    create_table :bugs, id: false do |t|
      t.primary_key :bug_id
      t.date :date_reported, null: false
      t.string :summary, limit: 80
      t.string :description, limit: 1000
      t.string :resolution, limit: 1000
      t.bigint :reported_by, null: false
      t.bigint :assigned_to
      t.bigint :verified_by
      t.string :status, limit: 20, null: false, default: "NEW"
      t.string :priority, limit: 20
      t.decimal :hours, precision: 9, scale: 2
    end
    add_foreign_key :bugs, :accounts, column: :reported_by, primary_key: :account_id, on_update: :cascade, on_delete: :restrict
    add_foreign_key :bugs, :accounts, column: :assigned_to, primary_key: :account_id, on_update: :cascade, on_delete: :restrict
    add_foreign_key :bugs, :accounts, column: :verified_by, primary_key: :account_id, on_update: :cascade, on_delete: :restrict
    add_foreign_key :bugs, :bug_statuses, column: :status, primary_key: :status, on_update: :cascade, on_delete: :restrict
  end
end
```

`bugs`は`id: false`と`add_foreign_key`と`null: false`を変更している。
書籍中は`status`列は`SET DEFAULT`になっているが、[MySQLには無い](https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html)ので割愛する。

## テーブルの生成
マイグレーションを実行してテーブルを生成する。

```bash
rake db:migrate
```

## モデルに外部キーを設定する
同じモデルから複数のキーで同じモデルを参照しているため、belongs_to/has_manyを少し工夫している。

```rb:app/models/bug.rb
class Bug < ApplicationRecord
  belongs_to :reporter, class_name: "Account", foreign_key: "reported_by"
  belongs_to :assignee, class_name: "Account", foreign_key: "assigned_to", required: false
  belongs_to :verifier, class_name: "Account", foreign_key: "verified_by", required: false
end
```

```rb:app/models/account.rb
class Account < ApplicationRecord
  has_secure_password
  has_many :reported, class_name: "Bug", primary_key: "account_id", foreign_key: "reported_by"
  has_many :assigned, class_name: "Bug", primary_key: "account_id", foreign_key: "assigned_to"
  has_many :verfied, class_name: "Bug", primary_key: "account_id", foreign_key: "verified_by"
end
```

## データを作ってみる
Railsのコンソールを起動する。

```bash
rails c
```

以下のようなデータを作る。

```rb
BugStatus.create(status: "opened")
BugStatus.create(status: "fixed")
BugStatus.create(status: "updated")
Account.create(account_id: 1, account_name: "def", first_name: "tarow", last_name: "okamoto", email: "okamoto@example.com", password: "abc", portrait_image: "none", hourly_rate: 3.4)
Bug.create(bug_id: 1, date_reported: "2018-06-23", summary: "hoge", description: "fugahoge", resolution: "nya", reported_by: 1, assigned_to: nil, verified_by: nil, status: "opened", priority: "important", hours: 1.2)
```

## データを変更してみる
データを変更した時の値の変化を確認する。

```rb
bs = BugStatus.find("opened")
bs.status = "open"
bs.save
Bug.find(1).status
# => "open"
# opened から open に自動的に更新されている

a = Account.find(1)
a.account_id = 10
a.save
Bug.find(1).reporter.id
# => 10
# 1 から 10 に自動的に更新されている

BugStatus.find("open").destroy
# => ActiveRecord::StatementInvalid (Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails (`bug_reports_development`.`bugs`, CONSTRAINT `fk_rails_2002328f52` FOREIGN KEY (`status`) REFERENCES `bug_statuses` (`status`) ON UPDATE CASCADE): DELETE FROM `bug_statuses` WHERE `bug_statuses`.`status` = 'open')
# bugs テーブルに値が使用されている列があるため、ON DELETE RESTRICT制約に従って削除できない
```

## before/afterフィルタの挙動
AccountモデルとBugモデルにそれぞれフィルタを仕掛けてみる。

```rb
  before_save :before_save
  after_save :after_save
  before_destroy :before_destroy
  after_destroy :after_destroy
  def before_save
    pp "before_save:#{self}"
  end
  def after_save
    pp "after_save:#{self}"
  end
  def before_destroy
    pp "before_destroy:#{self}"
  end
  def after_destroy
    pp "after_destroy:#{self}"
  end
```

予想通りではあるが、カスケードされた側（bugsテーブル側）のbefore/afterフィルタは動作しないので、自前で対応する必要がありそうだ。

```rb
a = Account.find(10)
a.account_id = 1
a.save
# "before_save:#<Account:0x0000000004a77688>"
#   Account Update (0.7ms)  UPDATE `accounts` SET `account_id` = 1 WHERE `accounts`.`account_id` = 10
# "after_save:#<Account:0x0000000004a77688>"
```