排他的リソースに対するスケジュールの重複判定をSQLでシュッとやる
突然ですが、排他的にしか利用できないリソースに対するスケジュールの重複判定の問題を考えてみようと思います。 典型的には「ある会議室では同時に複数の会議を開催できない」というようなスケジューリングの問題です。
ある既存のスケジュールを下の図のピンクで示します。
すると、そこに対して新規にスケジュールを追加するときのパターンは既存のスケジュールの始点と終点のまたぎ方によって分けられ、黄色と青色を合わせて全部で6パターンあります。 *1
このうち、黄色はピンクと重複している部分があるもの(invalidなスケジュール)、青色は重複していないもの(validなスケジュール)です。
新規に追加するスケジュールがinvaidな黄色の場合にエラーを返したいのですが、4パターンもあって場合分けが大変そうです。
というわけで、青色のパターンから考えます。
青色になる条件
黄色と違って、青色になる条件は比較的簡単です。
- 新規追加するスケジュールの終点が既存のスケジュールの始点よりも前にある
- 新規追加するスケジュールの始点が既存のスケジュールの終点よりも後にある
のいずれかですね。
したがって、新規追加するスケジュールの始点と終点をそれぞれSn
, En
、既存のスケジュールの始点と終点をそれぞれSe
, Ee
とすると、青色になる条件はEn < Se OR Sn > Ee
となります。
黄色になる条件
青色になる条件がわかったので、元々求めたかった黄色になる条件を求めます。
新規追加するスケジュールは黄色か青色のいずれかになるので、青色にならなければ黄色です。
したがって、さっき調べた青色になる条件を使うとNOT (En < Se OR Sn > Ee)
と書けます。
ド・モルガンの法則を使って変形するとNOT (En < Se) AND NOT(Sn > Ee)
。
もう一歩整理してEn > Se AND Sn < Ee
です。
シンプルになりました。
SQLで
この条件を使って、これから挿入しようとするスケジュールが既存のスケジュールと重複していないかどうか調べるSQLを書くことができます。 *2
select * from rooms inner join events on rooms.id = events.room_id where roooms.id = ROOM_ID and En > events.start_at and Sn < events.end_at ;
さいごに
このような重複判定をApp側で複雑に場合分けしてバリデーションしていたコードがあったのでこのエントリを書きました。
手続きで温かみある感じではなく、スパッと宣言的に判定していきたい気持ちです。