Upgrade to Pro — share decks privately, control downloads, hide ads and more …

NULL嫌いのUPDATEしないDB設計 #DBSekkeiNight / DB design without updating

NULL嫌いのUPDATEしないDB設計 #DBSekkeiNight / DB design without updating

DB設計したいNight #6 正規化 [online]
https://dbnight.connpass.com/event/177859/

Takumi Shotoku

June 04, 2020
Tweet

More Decks by Takumi Shotoku

Other Decks in Technology

Transcript

  1. NULL嫌いのUPDATEしないDB設計
    DB設計したいNight 2020/06/04(木)
    神速(@sinsoku)
    1

    View Slide

  2. 自己紹介
    • 名前: 神速
    • 会社: メドピア株式会社
    • GitHub: @sinsoku (画像右上)
    • Twitter: @sinsoku_listy (画像右下)
    • Rails歴: 7年くらい
    • 好きなDB: PostgreSQL, Redis
    • 嫌いなもの: NULL
    2

    View Slide

  3. 話すこと
    1. NULLについて
    2. NULLの進入を防ぐ
    3. 具体的な設計例
    4. おまけ(時間あれば)
    3

    View Slide

  4. 1. NULLについて
    4

    View Slide

  5. NULLの特性と問題
    • 四則演算できない
    • 比較できない
    • 集合関数で無視される
    • ORDER BY はDB依存
    • 3値論理
    5

    View Slide

  6. 四則演算
    NULLは伝搬する。
    NULL + 1 => NULL
    NULL - 1 => NULL
    NULL * 1 => NULL
    NULL / 1 => NULL
    6

    View Slide

  7. 比較できない
    NULL > 1 => NULL
    NULL = NULL => NULL
    NULL != NULL => NULL
    IS NULLかIS NOT NULLを使うとチェックできる。
    1 IS NULL => f
    1 IS NOT NULL => t
    7

    View Slide

  8. 集合関数で無視される
    • NULLの行は無視される
    • 1, 2, NULLをAVG(*)すると1.5になる
    8

    View Slide

  9. PostgreSQL(v12.3)の並び順
    • ORDER BY users.ageするとNULLが最後
    • 1, 2, NULLの並び
    • NULLを先頭にする方法は2つある
    • ORDER BY users.age NULLS FIRST
    • ORDER BY users.age IS NULL DESC, users.age
    9

    View Slide

  10. MySQL(v8.0.19)の並び順
    • ORDER BY users.ageするとNULLが最初
    • NULL, 1, 2の並び
    • NULLを最後にする方法はIS NULLを使う
    • ORDER BY users.age IS NULL, users.age ASC
    10

    View Slide

  11. 3値論理
    • TRUE, FALSE, NULLの3値が入るカラム
    • SELECT COUNT(*) WHERE users.active != TRUE
    • !=だとNULLのレコードが含まれない
    • NULLの考慮が漏れていてバグの原因になる
    11

    View Slide

  12. http://mickindex.sakura.ne.jp/database/db_getout_null.html
    12

    View Slide

  13. 2. NULLの進入を防ぐ
    13

    View Slide

  14. テーブル books
    create_table :books do |t|
    t.references :author, foreign_key: { to_table: :users }
    t.string :title
    t.integer :price
    t.timestamps
    end
    14

    View Slide

  15. ActiveRecordのバリデーション
    バリデーションでNULLが入るのを防ぐ。
    class Book
    # ActiveRecord v5.0͔ΒσϑΥϧτͰ `required: true` ʹͳΔ
    belongs_to :author, class_name: 'User'
    validates :title, presence: true
    validates :price, presence: true
    end
    15

    View Slide

  16. ActiveRecord::RecordInvalid
    irb(main):001:0> User.first.books.create!
    User Load (0.9ms) SELECT "users".* FROM "users" ORDER BY \
    "users"."id" ASC LIMIT $1 [["LIMIT", 1]]
    (0.2ms) BEGIN
    User Load (0.3ms) SELECT "users".* FROM "users" WHERE \
    "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
    (0.2ms) ROLLBACK
    Traceback (most recent call last):
    1: from (irb):1
    ActiveRecord::RecordInvalid (Validation failed: \
    Title can't be blank, Price can't be blank)
    16

    View Slide

  17. バリデーションのすり抜け
    ActiveRecordの一部のメソッド(update_all など)はバリデー
    ションをスキップする。
    irb(main):001:0> Book.update_all(title: nil)
    Book Update All (1.4ms) UPDATE "books" SET "title" = $1 [["title", nil]]
    => 1
    バッチ処理などで踏みがち。
    17

    View Slide

  18. NOT NULL制約
    マイグレーションで null: false を指定するとNOT NULL制約を
    つけられる。
    create_table :books do |t|
    t.references :author, null: false, foreign_key: { to_table: :users }
    t.string :title, null: false
    t.integer :price, null: false
    t.timestamps
    end
    18

    View Slide

  19. ActiveRecord::NotNullViolation
    update_allでもNOT NULL制約でエラーになる。
    irb(main):001:0> Book.update_all(title: nil)
    Book Update All (1.3ms) UPDATE "books" SET "title" = $1 [["title", nil]]
    Traceback (most recent call last):
    1: from (irb):1
    ActiveRecord::NotNullViolation (PG::NotNullViolation: ERROR: \
    null value in column "title" violates not-null constraint)
    DETAIL: Failing row contains (1, 1, null, 0, 2020-06-04 08:24:26.588829, \
    2020-06-04 08:24:26.588829).
    19

    View Slide

  20. 全てのNULLを
    生まれる前に消し去りたい
    20

    View Slide

  21. どうすれば全てのカラムに
    null: falseをつけられるか?
    21

    View Slide

  22. 3. 具体的な設計例
    22

    View Slide

  23. 記事を下書き、公開、非公開する機能
    • 記事を下書きする
    • 記事を公開する
    • 公開者、公開日時を記録する
    • 記事を非公開にする
    • 非公開者、非公開日時を記録する
    23

    View Slide

  24. ありそうなテーブル設計
    create_table :articles do |t|
    t.string :title, null: false
    t.integer :status, null: false, default: 0
    t.references :publisher, foreign_key: { to_table: :users }
    t.datetime, :published_at
    t.references, :archiver, foreign_key: { to_table: :users }
    t.datetime, :archived_at
    t.timestamps
    end
    24

    View Slide

  25. ありそうなモデル
    class Article < ApplicationRecord
    belongs_to :publisher, class_name: 'User', optional: true
    belongs_to :archiver, class_name: 'User', optional: true
    enum status: { draft: 0, published: 1, archived: 2 }
    validates :title, presence: true
    validates :publisher_id, presence: true, unless: :draft?
    validates :archiver_id, presence: true, if: :archived?
    def publish_by(user)
    update(publisher: user, published_at: Time.current)
    end
    end
    25

    View Slide

  26. ありそうなコントローラー
    class ArticlesController < ApplicationController
    # POST /articles/:id/publish
    def publish
    if @article.publish_by(current_user)
    redirect_to xxx_path, notice: 'ެ։͠·ͨ͠ɻ'
    else
    render :xxx
    end
    end
    end
    26

    View Slide

  27. この設計の問題点
    • NOT NULL制約がついていない
    • バリデーションが複雑になる
    • 認可のgem(Punditなど)と相性が悪い
    • Fat Controllerになりがち
    • UPDATEはデッドロックの危険がある
    27

    View Slide

  28. NOT NULL制約をつけた設計
    28

    View Slide

  29. テーブルを分ける
    create_table :articles do |t|
    t.string :title, null: false
    end
    29

    View Slide

  30. テーブルを分ける
    create_table :article_publications do |t|
    opts = { null: false, foreign_key: true }
    t.references :article, index: { unique: true }, **opts
    t.references :user, index: true, **opts
    t.timestamps
    end
    create_table :article_archives do |t|
    opts = { null: false, foreign_key: true }
    t.references :article, index: { unique: true }, **opts
    t.references :user, index: true, **opts
    t.timestamps
    end
    30

    View Slide

  31. モデルを分ける
    31

    View Slide

  32. class Article < ApplicationRecord
    has_one :article_publication
    has_one :article_archive
    scope :published, -> {
    left_joins(:article_publication, :article_archive)
    .where.not(article_publications: { id: nil })
    .where(article_archives: { id: nil })
    }
    scope :archived, -> {
    left_joins(:article_archive)
    .where.not(article_archives: { id: nil })
    }
    end
    32

    View Slide

  33. モデルを分ける
    class Article < ApplicationRecord
    def published?
    !article_publication.nil? && article_archive.nil?
    end
    def published_at
    article_publication&.created_at
    end
    end
    33

    View Slide

  34. モデルに対応したコントローラーを作る
    class ArticlePublicationsController < ApplicationController
    # POST /articles/:article_id/publications
    def create
    article = Article.find(params[:article_id])
    if @article.create_article_publication(user: current_user)
    redirect_to xxx_path, notice: 'ެ։͠·ͨ͠ɻ'
    else
    render :xxx
    end
    end
    end
    34

    View Slide

  35. まとめ
    • できるだけNOT NULLをつける
    • コントローラーがシンプルになる
    • パフォーマンスが悪くなってから status を作る
    35

    View Slide

  36. おまけ(時間あれば)
    AcitveRecord v6.1の新機能の紹介
    36

    View Slide

  37. where.missing(:author)1
    論理削除を扱うときに便利そう。
    User.left_joins(:user_archive).where(user_archives: { id: nil })

    User.where.missing(:user_archive)
    activerecord-missing2ですぐ使うことも可能。
    2 https://github.com/yujideveloper/activerecord-missing
    1 https://github.com/rails/rails/pull/34727
    37

    View Slide

  38. check_constraint3
    schema.rbで検査制約をサポート。
    add_check_constraint :products, "price > 0", name: "price_check"
    remove_check_constraint :products, name: "price_check"
    3 https://github.com/rails/rails/pull/31323
    38

    View Slide

  39. ご静聴ありがとうございました
    We are hiring4
    4 https://medpeer.co.jp/recruit/
    39

    View Slide