時間指定されたテーブルに対するクエリ

序

およそどのデータベースでもデフォルトになりがちなREAD COMMITなトランザクション分離レベルでは、一度コミットしたらもうそれ以前のデータは誰も見ることが出来ません。しかし、例えばOracleのFlashback Queryをはじめとして、いくつかのデータベースでは、特定の条件を満たすときコミット前を含む過去の一時点のテーブルに対してクエリを発行することができます。

今回はdockerを使って、いくつかのデータベースでこれらの機能を見ていきたいと思います。

Oracle

対象機能

Flashback Query

ややこしいので、条件など詳細は製品ドキュメントで確認してください(以下は導入部分です)。

dockerイメージのビルド

今回はここからOracleを入れます。

今回の対象はOracle Database 21c XEです(23はまだ新しく、導入しているところも少ないと思うので)。フラッシュバッククエリはかなり昔からある機能です。

$ git clone https://github.com/oracle/docker-images.git
$ cd docker-images/OracleDatabase/SingleInstance/dockerfiles
$ ./buildContainerImage.sh -v 21.3.0 -x
...
  Oracle Database container image for 'xe' version 21.3.0 is ready to be extended: 
    
    --> oracle/database:21.3.0-xe
...
$ cd ../../../..
$ 

dockerコンテナの生成

以下のようにdocker-compose.ymlを作成します。

docker-compose.yml

services:
  db:
    image: oracle/database:21.3.0-xe
    ports:
      - 1521:1521
      - 5500:5500
    volumes:
      - ./oradata:/opt/oracle/oradata
    environment:
      - ORACLE_PWD=pass

コンテナ起動

$ mkdir oradata # これは一度だけでOK
$ chmod a+w oradata # これは一度だけでOK(セキュリティ的には問題だけど今回は気にしない)
$ docker compose up -d
...
$ docker compose logs -f
...
(以下のメッセージが出るまで待ちます)
db-1  | #########################
db-1  | DATABASE IS READY TO USE!
db-1  | #########################
...
(Ctrl+C押下)
$ 

クライアントから接続

コンテナ内のsqlplusから接続

$ docker compose exec db sqlplus SYSTEM/pass@XEPDB1
...
SQL> SELECT * FROM v$version;
...
SQL> exit
...
$ 

ちゃんと動いているようです。

ホストのSQLエディタから接続

お好きなSQLエディタでいいのですが、とりあえず今回はvscodeにOracle謹製の拡張を入れたもの(vscode + Oracle Developer Tools for VS Code)から繋ぎます。Oracle ExplorerからNew Connectionして、以下のように設定します。

設定	値
Database host name	localhost
Port number	1521
Service name	XEPDB1
Role	Default
User name	SYSTEM
Password	pass

Flashback Queryの確認

test.sql

-- drop table example;

create TABLE example(
    id VARCHAR2(20) PRIMARY KEY,
    value NUMERIC(20,2)
);

INSERT INTO example VALUES('001', 100);
COMMIT;

SELECT * FROM example;

-- ID			  VALUE
-- -------------------- ----------
-- 001			    100

-- 10秒待つPL/SQLブロックの即時実行
DECLARE
    BEGIN
        DBMS_SESSION.SLEEP(10); -- oracle 19以降で使用可能
    END;
/

INSERT INTO example VALUES('002', 150);
COMMIT;

SELECT * FROM example;

-- ID			  VALUE
-- -------------------- ----------
-- 001			    100
-- 002			    150


-- 5秒前のexampleに対するクエリ(flashback query)
SELECT * FROM example
AS OF TIMESTAMP(systimestamp - interval '5' second);

-- ID			  VALUE
-- -------------------- ----------
-- 001			    100

sqlplusなら

$ docker compose exec -T db sqlplus SYSTEM/pass@XEPDB1 <test.sql

vscodeなら

1. Oracle ExplorerのSYSTEM.XEPDB1を右クリック
2. open new sql fileを選択
3. test.sqlを保存
4. Ctrl+R

で実行できます。

コンテナ停止

$ docker compose down

SQL Server

対象機能

Temporal Tables

OracleのFlashback Queryとは対照的に、追加の設定をして、追加の動作をさせないと同じような機能を実現できません。

コンテナ起動

今回はここからSQL Serverを入れます。

今回の対象はMS SQL Server 2019 Expressです(2022は一応最新なので一つ前にした)

docker-compose.yml

services: 
  db:
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1433:1433
    volumes:
      - ./data:/var/opt/mssql/data
      - ./log:/var/opt/mssql/log
      - ./secrets:/var/opt/mssql/secrets
    environment: 
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong@Passw0rd
      - MSSQL_PID=Express

$ mkdir data log secrets # 一度だけ
$ sudo chown 10001:root data log secrets # 一度だけ
$ docker compose up -d

クライアントから接続

コンテナ内から接続

$ docker compose exec db /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourStrong@Passw0rd" -No
1> SELECT @@VERSION AS 'SQL Server Version';
2> go
SQL Server Version                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU28-GDR) (KB5042749) - 15.0.4390.2 (X64) 
	Aug 12 2024 13:08:42 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>                                                                                                    

(1 rows affected)
1> exit
$

ホストのSQLエディタから接続

お好きなSQLエディタでいいのですが、とりあえず今回はvscodeにMicrosoft謹製の拡張を入れたもの(vscode + mssql for Visual Studio Code)から繋ぎます。SQL Server(左のタブ)からAdd Connectionして、以下のように設定します。

設定	値
Server name	localhost
Database name
Authentication type	SQL Login
User name	SA
Password	YourStrong@Passw0rd
Save password?	お好きなように
Profile name	お好きなように

SSL接続時、自己署名のワーニングが出るのでEnable Trustしてください。

するとConnectionsにコンテナへの接続が表示されているので、右クリックしてNew queryすると、任意のSQLを実行できます(ただしSQLを保存するとOracleの拡張と競合して開いたときにOracleの拡張が開いてしまいます)。

Temporal Tablesの確認

test.sql

-- 存在してたらテーブルをdrop
IF OBJECT_ID(N'example', N'U') IS NOT NULL
BEGIN
    -- 事前にシステムバージョン管理を外す
    ALTER TABLE example SET (SYSTEM_VERSIONING = OFF);
    ALTER TABLE example DROP PERIOD FOR SYSTEM_TIME;
    -- その後でテーブルをdrop
    DROP TABLE example;
    DROP TABLE example_history;
END
GO

-- テンポラルテーブルとしてexampleを作成
CREATE TABLE example(
    id VARCHAR(20) PRIMARY KEY,
    value NUMERIC(20,2),
    valid_from DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN, -- この列が必須(HIDDENはお好きに)
    valid_to DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN, -- この列が必須(HIDDENはお好きに)
    PERIOD FOR SYSTEM_TIME(valid_from, valid_to) -- この指定が必須
) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.example_history)); -- この指定が必須
GO

BEGIN TRANSACTION;
INSERT INTO example VALUES('001', 100);
COMMIT TRANSACTION;
GO

SELECT * FROM example;
-- id                   value                 
-- -------------------- ----------------------
-- 001                                  100.00
GO

-- 10秒待つ
WAITFOR DELAY '00:00:10';
GO

BEGIN TRANSACTION;
INSERT INTO example VALUES('002', 150);
COMMIT TRANSACTION;
GO

SELECT * FROM example;
-- id                   value                 
-- -------------------- ----------------------
-- 001                                  100.00
-- 002                                  150.00
GO

-- 5秒前のexampleに対するクエリ(temporal table)
DECLARE @fivesecago DATETIME2; -- 変数に入れないと計算した時間は指定できない
SELECT @fivesecago = DATEADD(second, -5, SYSDATETIME());
SELECT * FROM example
FOR SYSTEM_TIME AS OF @fivesecago;
-- id                   value                 
-- -------------------- ----------------------
-- 001                                  100.00
GO

sqlcmdなら

$ docker compose exec -T db /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourStrong@Passw0rd" -No <test.sql

vscodeなら

• SQL Serverタブを開く
• localhostを選択
• 右クリックして新しいクエリ
• test.sqlの内容を貼り付け
• Shift+Ctrl+E

で実行できます。

PostgreSQL

デフォルトではありませんが、拡張機能としていくつかtemporal tablesをサポートしているものがあります。

今回はデフォルトでなく比較対象として不適切ということで、試用を諦めます。

感想

ロギングされている限りデフォルトでいつでもFlashback Queryが使えるOracleは正直別格だと思います。この機能に頼って何かをすることは実際あまりないのですが、ちょっと確認みたいな用途で使えるのがお得に感じます。SQL Serverのようにわざわざテーブルの構造に手を入れてまで使うのはやや億劫ですね。

まとめ

• docker上のOracleで、時間指定クエリ(Flashback Query)を使ってみた
• docker上のSQL Serverで、時間指定クエリ(Temporal Tables)を使ってみた
• Oracleの時間指定クエリは特別なDDLなしに使用可能
• SQL Serverの時間指定クエリは特別なDDLが必要