時間枠の扱いをいい感じにする話 / good-time-schedule-window

# 参考URL
- https://soudai.hatenablog.com/entry/2017/04/16/152905
- https://soudai.hatenablog.com/entry/2021/07/06/215546

# PostgreSQL Conference Japan 2021 (2021-11-12)
https://www.postgresql.jp/jpug-pgcon2021

Transcript

1. 時間枠の扱いをいい感じにする話 ～ 安全に範囲を保存するために ～ PostgreSQLアンカンファレンス
2. 　時間枠の扱い
 
 
 What is it?
3. What is it? id begin_at end_at room_id 1 2021-07-06 11:30:00 2021-07-06 12:00:00 1 2 2021-07-06 11:30:00 2021-07-06 12:00:00 1 3 2021-07-06 11:30:00 2021-07-06 12:00:00 2 4 2021-07-06 12:00:00 2021-07-06 12:59:59 1 5 2021-07-06 13:00:00 2021-07-06 14:00:00 1 6 2021-07-06 10:00:00 2021-07-06 15:00:00 1
4. What is it? id begin_at end_at room_id 1 2021-07-06 11:30:00 2021-07-06 12:00:00 1 2 2021-07-06 11:30:00 2021-07-06 12:00:00 1 3 2021-07-06 11:30:00 2021-07-06 12:00:00 2 4 2021-07-06 12:00:00 2021-07-06 12:59:59 1 5 2021-07-06 13:00:00 2021-07-06 14:00:00 1 6 2021-07-06 10:00:00 2021-07-06 15:00:00 1 同じルームなのに重複してる
5. What is it? id begin_at end_at room_id 1 2021-07-06 11:30:00 2021-07-06 12:00:00 1 2 2021-07-06 11:30:00 2021-07-06 12:00:00 1 3 2021-07-06 11:30:00 2021-07-06 12:00:00 2 4 2021-07-06 12:00:00 2021-07-06 12:59:59 1 5 2021-07-06 13:00:00 2021-07-06 14:00:00 1 6 2021-07-06 10:00:00 2021-07-06 15:00:00 1 12:00:00の瞬間が重複している
6. What is it? id begin_at end_at room_id 1 2021-07-06 11:30:00 2021-07-06 12:00:00 1 2 2021-07-06 11:30:00 2021-07-06 12:00:00 1 3 2021-07-06 11:30:00 2021-07-06 12:00:00 2 4 2021-07-06 12:00:00 2021-07-06 12:59:59 1 5 2021-07-06 13:00:00 2021-07-06 14:00:00 1 6 2021-07-06 10:00:00 2021-07-06 15:00:00 1 beginもendも空いてる時間だが組み合わせると重複する
7. 　時間枠の扱い
 ↓
 むずいし、バグが出るとクリティカル
 What is it?
8. 　いい感じにするやつを紹介します
 
 ※個人の見解です
 What is it?
9. 1. 自己紹介
 2. 範囲型と排他制約
 3. チェック制約とストアドファンクション
 4. いい感じに検索する
 5. まとめ
 あじぇんだ
10. 1. 自己紹介
 2. 範囲型と排他制約
 3. チェック制約とストアドファンクション
 4. いい感じに検索する
 5. まとめ
 あじぇんだ
11. 自己紹介
 曽根 壮大（36歳）
 Have Fun Tech LLC 代表社員
 
 そ　 ね　　 たけ とも
 • 日本PostgreSQLユーザ会 勉強会分科会 担当
 • 3人の子供がいます（長女、次女、長男）
 • 技術的にはWeb/LL言語/RDBMSが好きです
 • コミュニティが好き
13. 本書きました

14. PostgreSQLカンファレンス

15. 1. 自己紹介
 2. 範囲型と排他制約
 3. チェック制約とストアドファンクション
 4. いい感じに検索する
 5. まとめ
 あじぇんだ
16. 範囲型と排他制約 https://soudai.hatenablog.com/entry/2017/04/16/152905
17. 範囲型と排他制約 id begin_at end_at room_id 1 2021-07-06 11:30:00 2021-07-06 12:00:00 1 2 2021-07-06 11:30:00 2021-07-06 12:00:00 1 3 2021-07-06 11:30:00 2021-07-06 12:00:00 2 4 2021-07-06 12:00:00 2021-07-06 12:59:59 1 5 2021-07-06 13:00:00 2021-07-06 14:00:00 1 6 2021-07-06 10:00:00 2021-07-06 15:00:00 1 こういうデータが防げる！
18. 詳細はブログ見てください
 
 
 範囲型と排他制約
19. 1. 自己紹介
 2. 範囲型と排他制約
 3. チェック制約とストアドファンクション
 4. いい感じに検索する
 5. まとめ
 あじぇんだ
20. ？？？「部屋毎に時間枠が違うんだよね」
 
 
 ルームid 1 は 10:00 - 12:00 ルームid 2 は 10:00 - 15:00 の中で予約可能、みたいなやつ チェック制約とストアドファンクション
21. チェック制約とストアドファンクション id schedule_at room_id 1 [2021-07-06 11:30:00,2021-07-06 12:00:00) 1 2 [2021-07-06 12:00:00,2021-07-06 13:00:00) 1 3 [2021-07-06 11:30:00,2021-07-06 12:00:00) 2 4 [2021-07-06 09:30:00,2021-07-06 12:00:00) 1 room_id room_at 1 [2021-07-06 10:30:00,2021-07-06 12:00:00) 2 [2021-07-06 10:00:00,2021-07-06 15:00:00)
22. チェック制約で正しいデータを守る
 
 
 チェック制約とストアドファンクション
23. チェック制約で正しいデータを守る
 ↓
 他のテーブルの値は参照できない
 チェック制約とストアドファンクション
24. チェック制約に
 
 ストアドファンクションを使う
 チェック制約とストアドファンクション
25. 検索結果を制約にする CREATE FUNCTION get_valid_room( schedule_at tstzrange, set_room_id bigint ) RETURNS int AS $$ DECLARE responsed_row_count int; BEGIN SELECT COUNT('*') INTO responsed_row_count FROM room WHERE room_at @> schedule_at AND room_id = set_room_id RETURN responsed_row_count ; END; $$ LANGUAGE plpgsql; -- ストアドファンクションをチェック制約に設定する ALTER TABLE 予約 ADD CONSTRAINT check_room CHECK (get_valid_room(schedule_at,room_id) = 1); ストアドファンクションは 戻り値がある。 その戻り値を使ってチェック制約 の条件にする `@>` は範囲の含む検索
26. チェック制約を活用すると
 
 テーブルが減らせる
 チェック制約とストアドファンクション
27. 絶対に不正なデータが入らない
 ↓
 テストやアプリケーションが楽
 チェック制約とストアドファンクション
28. 1. 自己紹介
 2. 範囲型と排他制約
 3. チェック制約とストアドファンクション
 4. いい感じに検索する
 5. まとめ
 あじぇんだ
29. ？？？「範囲型とかORMが対応してない」
 
 
 
 いい感じに検索する
30. ？？？「範囲型とかORMが対応してない」
 ↓
 なければ作れ！
 Djangoは拡張がある
 いい感じに検索する
31. django.contrib.postgresは便利
 
 https://docs.djangoproject.com/en/3.2/ref/contrib/postgres/ 排他制約や範囲型を使うために必要。 配列型やJSON型も使えるぞ！ 空間INDEXやGIN INDEXも扱えるので便利 ロック待ちの悪夢
32. ？？？「Django以外は無いんですか？」
 
 
 いい感じに検索する
33. ？？？「Django以外は無いんですか？」
 ↓
 SQLがあるじゃろ？？？
 いい感じに検索する
34. 正直、Django以外で
 
 ゴリゴリ使うのはオススメしてない
 いい感じに検索する Library作るチャンスだぞ！！
35. 1. 自己紹介
 2. 範囲型と排他制約
 3. チェック制約とストアドファンクション
 4. いい感じに検索する
 5. まとめ
 あじぇんだ
36. PostgreSQLは便利
 
 
 まとめ
37. だからこそ、正しく使うことが大切です
 
 
 まとめ
38. だからこそ、正しく使うことが大切です
 ↓
 毒にも薬にもなる
 まとめ
39. 実戦投入には自己責任で！！
 
 （自分はDjangoでだけ導入してます）
 まとめ
40. ご清聴ありがとうございました
 
 
 まとめ