# PostgreSQL extension periods 介紹
ANSI SQL 2011 裡引進了 periods and tables with SYSTEM VERSIONING 的觀念,後來在ANSI SQL 2016中更進一步.
目前各主流RDBMS,有程度不一的支持與進展.
PostgreSQL 方面目前有 periods 這個 extension,提供了資料型態與函數等相結合的支持.
### 網站
[github](https://github.com/xocolatl/periods)
## 安裝
```shell=
git clone https://github.com/xocolatl/periods
cd periods
make
make install
```
```sql=
create extension periods with schema public;
commit;
```
安裝後會建立 periods 這個 schema, 若之前未安裝 btree_gist extension, 可以用
```sql
create extension periods with schema public cascade;
```
這樣會一併安裝.
## Period 介紹
A period is a definition on a table which specifies a name and two columns. The period’s name cannot be the same as any column name of the table.
其實就是另外兩個日期時間欄位整合起來的欄位.在PG中有 daterange 這類的資料型態,我們先往下看.
常見的用處例如 例如民宿預約,PG原本有 btree_gist , 使用 exclude
類似這樣的語法
EXCLUDE USING gist (id WITH =, valid WITH &&)
實際也是使用這樣的方式,只是封裝為 period.符合ANSI SQL規定.
若使用ANSI SQL 標準語法應該是這樣
```sql=
create table room_booking (
guest text not null
, room_number int not null
, checkin date not null
, checkout date not null
, period for stay (checkin, checkout)
);
```
目前PG這樣是無法直接執行的! 因為 periods extension 尚未整併為語法,要用間接的方式.
```sql=
create table room_booking (
guest text not null
, room_number int not null
, checkin date not null
, checkout date not null
);
select periods.add_period('room_booking', 'stay', 'checkin', 'checkout');
--建立 Unique constraints
select periods.add_unique_key('room_booking', ARRAY['room_number'], 'stay');
add_unique_key
-------------------------------
room_booking_room_number_stay
commit;
```
### 測試
```sql=
insert into room_booking values
('Alice', 112, '2021-05-10', '2021-05-14');
commit;
insert into room_booking values
('Bob', 112, '2021-05-13', '2021-05-15');
ERROR: 23P01: conflicting key value violates exclusion constraint "room_booking_room_number_daterange_excl"
DETAIL: Key (room_number, daterange(checkin, checkout, '[)'::text))=(112, [2021-05-13,2021-05-15)) conflicts with existing key (room_number, daterange(checkin, checkout, '[)'::text))=(112, [2021-05-10,2021-05-14)).
CONSTRAINT NAME: room_booking_room_number_daterange_excl
```
可以看到這樣就不能overlap !
## SYSTEM-VERSIONED TABLES
這是主要要介紹的功能.直接來看實際的例子吧!
```sql=
create table sysver (
val text
, flap boolean
);
select periods.add_system_time_period('sysver', excluded_column_names => ARRAY['flap']);
-- flap 欄位會排除,不做紀錄.
select periods.add_system_versioning('sysver',
history_table_name => 'sysver_history',
view_name => 'sysver_view',
function_as_of_name => 'sysver_as_of',
function_between_name => 'sysver_between',
function_between_symmetric_name => 'sysver_between_symmetric',
function_from_to_name => 'sysver_from_to');
NOTICE: 00000: history table "sysver_history" created for "sysver", be sure to index it properly
LOCATION: exec_stmt_raise, pl_exec.c:3889
add_system_versioning
-----------------------
(1 row)
commit;
```
這樣就建立的 sysver 的 history table, sysver_history,
還有一些函數.來看實際的應用吧.
```sql=
insert into sysver (val, flap) values ('hello', false);
commit;
select *
from sysver;
val | flap | system_time_start | system_time_end
-------+------+-------------------------------+-----------------
hello | f | 2021-05-22 15:11:02.689252+08 | infinity
select *
from sysver_history;
val | flap | system_time_start | system_time_end
-----+------+-------------------+-----------------
(0 rows)
```
目前很單純的輸入一筆.
```sql=
update sysver
set val = 'world';
commit;
select *
from sysver;
val | flap | system_time_start | system_time_end
-------+------+-------------------------------+-----------------
world | f | 2021-05-22 15:15:55.621058+08 | infinity
select *
from sysver_history;
val | flap | system_time_start | system_time_end
-------+------+-------------------------------+-------------------------------
hello | f | 2021-05-22 15:11:02.689252+08 | 2021-05-22 15:15:55.621058+08
```
update 以後,觀察 sysver 與 sysver_history 的內容.
接著改變幾次flap,觀察sysver_history 會不會受到影響?
```sql=
update sysver set flap = not flap;
update sysver set flap = not flap;
update sysver set flap = not flap;
update sysver set flap = not flap;
update sysver set flap = not flap;
commit;
select *
from sysver;
val | flap | system_time_start | system_time_end
-------+------+-------------------------------+-----------------
world | t | 2021-05-22 15:15:55.621058+08 | infinity
select *
from sysver_history;
val | flap | system_time_start | system_time_end
-------+------+-------------------------------+-------------------------------
hello | f | 2021-05-22 15:11:02.689252+08 | 2021-05-22 15:15:55.621058+08
```
接著做 delete
```sql=
delete from sysver;
select val
from sysver;
val
-----
(0 rows)
select *
from sysver_history
order by system_time_start;
val | flap | system_time_start | system_time_end
-------+------+-------------------------------+-------------------------------
hello | f | 2021-05-22 15:11:02.689252+08 | 2021-05-22 15:15:55.621058+08
world | t | 2021-05-22 15:15:55.621058+08 | 2021-05-22 15:23:07.876518+08
(2 rows)
```
這樣目前 sysver 及 sysver_history 都可以觀察到.接著來做
temporal queries, 目前還未支持語法,只能先用 function的方式.
```sql=
select val
from sysver_as_of('2021-05-22 15:12:02+08')
order by system_time_start;
val
-------
hello
select val
from sysver_as_of('2021-05-22 15:15:56+08')
order by system_time_start;
val
-------
world
select val
from sysver_between('2021-05-22 15:12:02+08', '2021-05-22 15:15:56+08')
order by system_time_start;
val
-------
hello
world
(2 rows)
select val
from sysver_between('2021-05-22 15:15:56+08', '2021-05-22 15:12:02+08')
order by system_time_start;
val
-----
(0 rows)
select val
from sysver_from_to('2021-05-22 15:12:02+08', '2021-05-22 15:15:56+08')
order by system_time_start;
val
-------
hello
world
(2 rows)
```
## 結語
今天先介紹 periods 這個 extension, 比之前的 extension temporal_tables 有更高的整體性,也更貼近 ANSI SQL 的規定.
相信在不久的將來,大家可以很方便使用ANSI SQL 語法來做查詢.
Sign in with Wallet
Connect another wallet