會議室預定系統實踐(輕鬆解放開發)-PostgreSQLtsrange(時間範圍型別)+排他約束

德哥發表於2017-12-23
SQL型別

標籤

PostgreSQL , tsrange , 範圍 , exclude using , 排他約束 , btree_gist , 會議室預定 , 時間重疊 , 空間重疊

背景

PostgreSQL 範圍、陣列、空間型別(range, array, geometry)，都有交叉屬性，例如時間範圍：7點到9點，8點到9點，這兩個內容是有重疊部分的。例如陣列型別：[1,2,3]和[2,4,5]是有交叉部分的。例如空間型別也有交叉的屬性。

那麼在設計時，實際上業務上會有這樣的約束，不允許物件有相交。

例如會議室預定系統，不允許兩個人預定的會議室時間交叉，否則就有可能一個會議室在某個時間段被多人共享了，業務上是不允許的。

那麼如何做到這樣的約束呢？

PostgreSQL 提供了exclude約束，可以實現這個需求。

exclude 約束的語法

  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |    
    
exclude_element in an EXCLUDE constraint is:    
    
{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

exclude 約束常用的操作符

範圍、陣列、空間型別的相交操作符如下：

postgres=# do &&    
                                          List of operators    
   Schema   | Name | Left arg type | Right arg type | Result type |           Description                
------------+------+---------------+----------------+-------------+----------------------------------    
 pg_catalog | &&   | anyarray      | anyarray       | boolean     | overlaps    
 pg_catalog | &&   | anyrange      | anyrange       | boolean     | overlaps    
 pg_catalog | &&   | box           | box            | boolean     | overlaps    
 pg_catalog | &&   | circle        | circle         | boolean     | overlaps    
 pg_catalog | &&   | inet          | inet           | boolean     | overlaps (is subnet or supernet)    
 pg_catalog | &&   | polygon       | polygon        | boolean     | overlaps    
 pg_catalog | &&   | tinterval     | tinterval      | boolean     | overlaps    
 pg_catalog | &&   | tsquery       | tsquery        | tsquery     | AND-concatenate    
 public     | &&   | integer[]     | integer[]      | boolean     | overlaps    
(9 rows)

會議室預定系統的例子

1、建立btree_gist外掛.

postgres=# create extension btree_gist;    
CREATE EXTENSION

2、建立會議室預定表

postgres=# create table t_meeting (    
  roomid int,   -- 會議室ID    
  who int,      -- 誰定了這個會議室    
  ts tsrange,   -- 時間範圍    
  desc text,    -- 會議內容描述    
  exclude using gist (roomid with = , ts with &&)   -- 排他約束，同一個會議室，不允許有時間範圍交叉的記錄    
);    
CREATE TABLE

3、預定會議室，如果同一個會議室輸入的時間不允許預定（有交叉），則自動報錯。實現強約束。

postgres=# insert into t_meeting values (1, 1, $$[`2017-01-01 07:00:00`, `2017-01-01 08:00:00`)$$);    
INSERT 0 1    
    
postgres=# insert into t_meeting values (1,1,$$[`2017-01-01 07:00:00`, `2017-01-01 08:00:00`)$$);    
ERROR:  conflicting key value violates exclusion constraint "t_meeting_roomid_ts_excl"    
DETAIL:  Key (roomid, ts)=(1, ["2017-01-01 07:00:00","2017-01-01 08:00:00")) conflicts with existing key (roomid, ts)=(1, ["2017-01-01 07:00:00","2017-01-01 08:00:00")).    
    
postgres=# insert into t_meeting values (2,1,$$[`2017-01-01 07:00:00`, `2017-01-01 08:00:00`)$$);    
INSERT 0 1    
    
postgres=# insert into t_meeting values (1,1,$$[`2017-01-01 09:00:00`, `2017-01-01 10:00:00`)$$);    
INSERT 0 1    
    
postgres=# insert into t_meeting values (1,1,$$[`2017-01-01 09:00:00`, `2017-01-01 11:00:00`)$$);    
ERROR:  conflicting key value violates exclusion constraint "t_meeting_roomid_ts_excl"    
DETAIL:  Key (roomid, ts)=(1, ["2017-01-01 09:00:00","2017-01-01 11:00:00")) conflicts with existing key (roomid, ts)=(1, ["2017-01-01 09:00:00","2017-01-01 10:00:00")).    
    
postgres=# insert into t_meeting values (1,1,$$[`2017-01-01 08:00:00`, `2017-01-01 09:00:00`)$$);    
INSERT 0 1    

postgres=# select * from t_meeting order by roomid, ts;    
 roomid | who |                      ts                           
--------+-----+-----------------------------------------------    
      1 |   1 | ["2017-01-01 07:00:00","2017-01-01 08:00:00")    
      1 |   1 | ["2017-01-01 08:00:00","2017-01-01 09:00:00")    
      1 |   1 | ["2017-01-01 09:00:00","2017-01-01 10:00:00")    
      2 |   1 | ["2017-01-01 07:00:00","2017-01-01 08:00:00")    
(4 rows)

4、查詢某個時間段還有哪些會議室能預定

會議室ID表，假設有50個會議室。

create table t_room (roomid int primary key);    
    
insert into t_room select generate_series(1,50);

假設使用者要預定 某一天：7點到9點的會議室，這樣操作即可：

select roomid from t_room    
except    
select roomid from t_meeting where ts && $$[`2017-01-01 07:00:00`, `2017-01-01 09:00:00`)$$;    
 roomid     
--------    
     14    
      3    
      4    
     16    
     42    
     50    
     19    
     13    
     40    
     46    
     18    
     34    
     39    
      7    
     35    
     43    
     23    
     36    
     29    
     30    
     28    
      8    
     24    
     32    
     10    
     33    
      9    
     45    
     22    
     49    
     48    
     38    
     37    
      5    
     12    
     31    
     11    
     27    
     20    
     44    
     41    
      6    
     21    
     15    
     47    
     17    
     26    
     25    
(48 rows)    
    
    
postgres=# explain (analyze,verbose,timing,costs,buffers) select roomid from t_room    
except    
select roomid from t_meeting where ts && $$[`2017-01-01 07:00:00`, `2017-01-01 09:00:00`)$$;    
                                                                     QUERY PLAN                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------    
 HashSetOp Except  (cost=0.00..77.28 rows=2550 width=8) (actual time=0.074..0.085 rows=48 loops=1)    
   Output: "*SELECT* 1".roomid, (0)    
   Buffers: shared hit=3    
   ->  Append  (cost=0.00..70.88 rows=2562 width=8) (actual time=0.013..0.058 rows=53 loops=1)    
         Buffers: shared hit=3    
         ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..61.00 rows=2550 width=8) (actual time=0.012..0.029 rows=50 loops=1)    
               Output: "*SELECT* 1".roomid, 0    
               Buffers: shared hit=1    
               ->  Seq Scan on public.t_room  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.016 rows=50 loops=1)    
                     Output: t_room.roomid    
                     Buffers: shared hit=1    
         ->  Subquery Scan on "*SELECT* 2"  (cost=1.44..9.88 rows=12 width=8) (actual time=0.018..0.019 rows=3 loops=1)    
               Output: "*SELECT* 2".roomid, 1    
               Buffers: shared hit=2    
               ->  Bitmap Heap Scan on public.t_meeting  (cost=1.44..9.76 rows=12 width=4) (actual time=0.018..0.018 rows=3 loops=1)    
                     Output: t_meeting.roomid    
                     Recheck Cond: (t_meeting.ts && `["2017-01-01 07:00:00","2017-01-01 09:00:00")`::tsrange)    
                     Heap Blocks: exact=1    
                     Buffers: shared hit=2    
                     ->  Bitmap Index Scan on t_meeting_roomid_ts_excl  (cost=0.00..1.44 rows=12 width=0) (actual time=0.010..0.010 rows=4 loops=1)    
                           Index Cond: (t_meeting.ts && `["2017-01-01 07:00:00","2017-01-01 09:00:00")`::tsrange)    
                           Buffers: shared hit=1    
 Planning time: 0.123 ms    
 Execution time: 0.172 ms    
(24 rows)

速度槓槓的。開發也方便了。

小結

使用PostgreSQL，時間範圍型別、exclude約束，很好的幫助業務系統實現會議室預定的強約束。

使用except語法，很方便的找到需要預定的時間段還有那些會議室是空閒的。

開不開心，解放開發人員的大腦。

參考

https://www.postgresql.org/docs/10/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE


相關文章