Share
Embed
NULL嫌いのUPDATEしないDB設計 #DBSekkeiNight / DB design without updating
DB設計したいNight #6 正規化 [online]
https://dbnight.connpass.com/event/177859/
Takumi Shotoku
June 04, 2020
Transcript
39 slides
-
自己紹介 • 名前: 神速 • 会社: メドピア株式会社 • GitHub: @sinsoku
(画像右上) • Twitter: @sinsoku_listy (画像右下) • Rails歴: 7年くらい • 好きなDB: PostgreSQL, Redis • 嫌いなもの: NULL 2 -
NULLの特性と問題 • 四則演算できない • 比較できない • 集合関数で無視される • ORDER BY
はDB依存 • 3値論理 5 -
四則演算 NULLは伝搬する。 NULL + 1 => NULL NULL - 1
=> NULL NULL * 1 => NULL NULL / 1 => NULL 6 -
比較できない NULL > 1 => NULL NULL = NULL =>
NULL NULL != NULL => NULL IS NULLかIS NOT NULLを使うとチェックできる。 1 IS NULL => f 1 IS NOT NULL => t 7 -
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 -
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 -
3値論理 • TRUE, FALSE, NULLの3値が入るカラム • SELECT COUNT(*) WHERE users.active
!= TRUE • !=だとNULLのレコードが含まれない • NULLの考慮が漏れていてバグの原因になる 11 -
テーブル books create_table :books do |t| t.references :author, foreign_key: {
to_table: :users } t.string :title t.integer :price t.timestamps end 14 -
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 -
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 -
バリデーションのすり抜け 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 -
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 -
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 -
記事を下書き、公開、非公開する機能 • 記事を下書きする • 記事を公開する • 公開者、公開日時を記録する • 記事を非公開にする •
非公開者、非公開日時を記録する 23 -
ありそうなテーブル設計 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 -
ありそうなモデル 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 -
ありそうなコントローラー 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 -
この設計の問題点 • NOT NULL制約がついていない • バリデーションが複雑になる • 認可のgem(Punditなど)と相性が悪い • Fat
Controllerになりがち • UPDATEはデッドロックの危険がある 27 -
テーブルを分ける 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 -
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 -
モデルを分ける class Article < ApplicationRecord def published? !article_publication.nil? && article_archive.nil?
end def published_at article_publication&.created_at end end 33 -
モデルに対応したコントローラーを作る 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 -
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 -
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