はじめに
2018年5月にGAとなった MariaDB Server 10.3 では，すべてのデータ作成/更新/削除履歴を記録可能なテンポラル・テーブル(System-Versioned Tables)が新機能として追加されました。
テンポラル・テーブルにより以下のオペレーションが可能となり，より高度なセキュリティ，データ復旧性などが得られます。
- すべてのデータ変更の監査
- 過去の任意時点でのデータのリカバリ
- 偶発的なデータ変更/削除や人為的エラー，アプリケーション エラーからの復旧
今回は簡単なサンプルデータを用いてテンポラルテーブルを解説したいと思います。
テスト環境
- MariaDB Server 10.3.12
- CentOS 7.6.1810
サンプルテーブル作成
従業員リストのようなテーブルを作成し，Linuxのdateコマンドでシステム時間を変更しながらサンプルデータをINSERTします。
|
1 2 3 |
create table employees (id int, name varchar(50) ) WITH SYSTEM VERSIONING; |
テンポラルテーブルを用いるには，上記のように，WITH SYSTEM VERSIONING を付与します。なお，CREATE TABLEだけでなく，ALTER TABLE でも利用可能です。
サンプルデータINSERT
以下の日付でサンプルデータをINSERTします。システムの日時は，例えば2017-01-01 9:00の場合は，以下のコマンドで変更することが可能です。(chronyd は disable としています)。
|
1 2 |
date 010109002017 # date MMDDhhmmCCYY |
2017-01-01:
Johnが入社
|
1 |
INSERT INTO employees VALUES (1, 'John'); |
2017-03-01:
Mariaが入社
|
1 |
INSERT INTO employees VALUES (2, 'Maria'); |
2018-06-01:
Bobが入社
|
1 |
INSERT INTO employees VALUES (3, 'Bob'); |
2018-06-30:
John(id=1)が退職
|
1 |
delete from employees where id=1; |
2018-12-24:
Eveが入社
|
1 |
INSERT INTO employees VALUES (4, 'Eve'); |
2018-12-31:
Bob(id=3)が退職
|
1 |
delete from employees where id=3; |
2019-01-01:
Tomが入社
|
1 |
INSERT INTO employees VALUES (5, 'Tom'); |
サンプルクエリ
全レコードのSELECT
全履歴を参照するには，FOR SYSTEM_TIME ALL を付与してSELECTを実行します。
|
1 2 3 4 5 6 7 8 9 10 11 |
MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME ALL; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 | | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | | 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 | | 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 | | 5 | Tom | 2019-01-01 09:00:12.955820 | 2038-01-19 12:14:07.999999 | +------+-------+----------------------------+----------------------------+ |
System Versioned Table では，row_start と row_end が隠しカラムとして追加され，各レコードがいつからいつまで存在していたか記録されます。
2017-01-01時点での全社員リスト
任意時点でのレコードを抽出するには，FOR SYSTEM_TIME AS OF TIMESTAMP '日時'をSELECTに付与します。
|
1 2 3 4 5 6 7 |
MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2017-01-01 10:00'; +------+------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+------+----------------------------+----------------------------+ | 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 | +------+------+----------------------------+----------------------------+ |
2018-01-01時点での全社員リスト
|
1 2 3 4 5 6 7 8 |
MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2018-01-01 10:00'; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 | | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | +------+-------+----------------------------+----------------------------+ |
2018-06-30時点での全社員リスト
John が 2018-06-30 17:00 で退職しますが，10:00の時点では社員数が3となります。
|
1 2 3 4 5 6 7 8 9 |
MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2018-06-30 10:00'; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 | | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | | 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 | +------+-------+----------------------------+----------------------------+ |
2018-12-31時点での全社員リスト
John が退職済で，2018-12-24 に Eve が入社していますので，社員数は 3 となります。
|
1 2 3 4 5 6 7 8 9 |
MariaDB [test]> SELECT *, row_start, row_end FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2018-12-31 10:00'; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | | 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 | | 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 | +------+-------+----------------------------+----------------------------+ |
最新の社員リスト
2018年いっぱいで Bob が退職し，2019-01-01 に Tom が入社していますので，社員数は 3 となります。
|
1 2 3 4 5 6 7 8 |
MariaDB [test]> SELECT *, row_start, row_end FROM employees; +------+-------+----------------------------+----------------------------+ | id | name | row_start | row_end | +------+-------+----------------------------+----------------------------+ | 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 | | 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 | | 5 | Tom | 2019-01-01 09:00:12.955820 | 2038-01-19 12:14:07.999999 | +------+-------+----------------------------+----------------------------+ |
まとめ
MariaDB Server 10.3 で新たに導入されたテンポラル・テーブルについて解説いたしました。複雑なSQL文を用いたり，アプリケーション側で日時の演算を行うことなく，任意の過去の時点でのレコードを簡単に参照可能であることが確認できました。
商用データベースでは以前から同様の機能を備えているものもあり，MariaDBへの移行を検討されている方にとって有益な新機能と考えます。
