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/

Ecad9d801d79f6c6e5df93094690685e?s=128

Takumi Shotoku

June 04, 2020
Tweet

Transcript

  1. 2.

    自己紹介 • 名前: 神速 • 会社: メドピア株式会社 • GitHub: @sinsoku

    (画像右上) • Twitter: @sinsoku_listy (画像右下) • Rails歴: 7年くらい • 好きなDB: PostgreSQL, Redis • 嫌いなもの: NULL 2
  2. 6.

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

    => NULL NULL * 1 => NULL NULL / 1 => NULL 6
  3. 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
  4. 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
  5. 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
  6. 11.

    3値論理 • TRUE, FALSE, NULLの3値が入るカラム • SELECT COUNT(*) WHERE users.active

    != TRUE • !=だとNULLのレコードが含まれない • NULLの考慮が漏れていてバグの原因になる 11
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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