仮想空間で学ぶPostgreSQL

この記事は[PostgreSQL Advent Calendar 2022](https://qiita.com/advent-calendar/2022/postgresql)の18日目の記事です。

# はじめに
[昨年のAdvent Calendar](https://qiita.com/kingtomo1122/items/c137b2d6b53f16168cea)で散財(粘土とかロボットアームとか)してしまい、かみさんに凄く文句をいわれました。
そこで、今年は仮想空間に逃げて遊ぶことにしました。デジタルツインです！

DBのデモとかも一般的に地味なのが多いので、少しでも分かりやすくなるとよいな。

## デジタルツインとは
[WikiPedia](https://ja.wikipedia.org/wiki/%E3%83%87%E3%82%B8%E3%82%BF%E3%83%AB%E3%83%84%E3%82%A4%E3%83%B3)より：
> デジタルツイン(英: Digital twin)とは、さまざまな目的で使用できる物理的資産、プロセス、人、場所、システムおよびデバイスのデジタル複製を指す。

PostgreSQLを物理空間と見立て、仮想空間にデータベースオブジェクトを可視化して、PostgreSQLの挙動をお勉強しましょう。


# PostgreSQL(物理空間)の登場人物
以下のcyabudaiテーブルを用意して、PostgreSQLの追記型の挙動を可視化していきます。

## テーブル構成
| テーブル名 | 列名 | データ型 | 備考 |
|:----------------:|:-----------------:|:------------------:|:----:|
| cyabudai | id | INTEGER | Primary key
|       | value | char(2000) | Index作成済

## ユーザ
物理空間を操作したときと仮想空間を操作したときでユーザを分ける必要がありそうだったので、仮想空間操作時のユーザとして「unity」さんを作成しました。
物理空間操作時は面倒なので、postgresユーザを使います。
どちらもスーパーユーザ権限。

```powershell
PS D:\work\2022\AdventCalendar\script> createuser -P unity
```

## トリガ関数
物理空間を操作したら仮想空間のオブジェクトを移動するために、トリガ関数を用意しました。
Unityのオブジェクトは[Wolfram+UnityLink](https://www.wolfram.com/language/12/built-in-interface-to-unity-game-engine/learn-unitylink.html.ja)で操作できそうなので、とりあえずインストールしてみました。
関数はPLPythonで書き、外部コマンドとしてwolframscriptを呼び出す形にしました。


## 初期構築スクリプト
DB：pgdtを作って、次のトリガ関数読み込んでおきます。

```sql:create_pgdt.sql
CREATE DATABASE pgdt;
\connect pgdt
CREATE EXTENSION plpython3u;
\i 03_truncate_trigger.sql
```


## TRUNCATE実行時のトリガ関数
TRUNCATE実行時に呼び出すトリガ関数を定義します。
TRUNCATE実行したユーザにより、処理を変えてます。unityユーザの場合は「何もしない」、それ以外のユーザの場合はwolframscriptでオブジェクトを移動させます。

```python:PLPythonトリガ関数
CREATE OR REPLACE FUNCTION unity_operation() RETURNS trigger AS $unity_operation$
import subprocess
res = plpy.execute("SELECT current_user")
current_user = res[0]["current_user"]
if current_user == "unity":
	return None

command = "wolframscript.exe -wstpserver -f D:\work\2022\AdventCalendar\02_wolframscripts\05_cyabudaitruncate .wls"
proc = subprocess.Popen(command, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
stdout, stderr = proc.communicate()
ret = proc.returncode
print(stdout)
$unity_operation$ LANGUAGE plpython3u;
```



# Unity(仮想空間)の登場人物
以下のオブジェクトを用意して、物理空間のデータと対応させてます。

## オブジェクト構成

| オブジェクト名 | 形状 | 物理空間との対応 | 備考 |
|:----------------:|:-----------------:|:------------------:|:----:|
| cyabudai | ちゃぶ台 | テーブル | 元は丸太 |
| rice | ごはん | データのINSERT/UPDATE | id=1の想定 |
| miso | 噌汁 | データのINSERT/UPDATE | id=2の想定 |
| beer | ビール | データのINSERT/UPDATE | id=3の想定 |
| fish | お魚 | データのINSERT/UPDATE | id=4の想定 |
| humberg | ハンバーグ | データのINSERT/UPDATE | id=5の想定 |
| fire | 焚火 | データの削除 |  |


## 衝突判定
オブジェクト同士が衝突したらアクションを行うように、OnCollisionEnterメソッドで愚直にSQLを発行したりします。

```csharp:オブジェクトにアタッチしたスクリプト(抜粋)
...
    void OnCollisionEnter(Collision collision)
    {
        GameObject go = collision.gameObject;
        if(go.CompareTag("important")) return;

        string insert_sql = "";
        Vector3 ctid_pos;
        if (go.CompareTag("rice"))
        {
            if (flg[0] == true)
            {
                go.SetActive(true);
                return;
            }
            insert_sql = "INSERT INTO cyabudai VALUES (1, 'rice') ON CONFLICT (id) DO UPDATE SET value = excluded.value RETURNING (ctid)";
            flg[0] = true;
        }
...
```
　
## wolframscript
トリガ関数の中で呼び出してたスクリプトです。
仮想空間のcyabudaiのTransform(位置や角度)を変更するだけです。

```shell:wolfscript
#!/usr/bin/env wolframscript
cyabudai = FindUnityGameObject["cyabudai"] 
cyabudaitransform = cyabudai["Transform"]
Print[cyabudaitransform[["position"]]]
```
　
　
# 実践
諸々用意できたので、お勉強していきます。

## 仮想空間の挙動（１）
![pgdt_01.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/151699/cb2da342-0bb8-e17d-873b-87654657752a.gif)

1. 丸太をクリックするとちゃぶ台がでてきます。
1. ごはん、味噌汁をちゃぶ台に配膳してます。（適当においても位置調整されます）

## 物理空間の挙動（１）
上記挙動の裏（物理空間）では、何が起きてるかログを見てみます。
```
2022-12-18 15:51:53.705 JST [23884] LOG:  duration: 507.161 ms  statement: CREATE TABLE IF NOT EXISTS cyabudai(id INT PRIMARY KEY, value CHAR(2000))
2022-12-18 15:51:54.069 JST [29376] LOG:  duration: 81.651 ms  statement: CREATE INDEX IF NOT EXISTS idx_value ON cyabudai(value);
2022-12-18 15:51:54.348 JST [13268] LOG:  duration: 21.136 ms  statement: CREATE TRIGGER unity_operation_truncate BEFORE TRUNCATE ON cyabudai FOR EACH STATEMENT EXECUTE FUNCTION unity_operation();
2022-12-18 15:52:03.233 JST [22652] LOG:  duration: 71.091 ms  statement: INSERT INTO cyabudai VALUES (1, 'rice') ON CONFLICT (id) DO UPDATE SET value = excluded.value RETURNING (ctid)
2022-12-18 15:52:13.201 JST [27076] LOG:  duration: 6.595 ms  statement: INSERT INTO cyabudai VALUES (2, 'miso') ON CONFLICT (id) DO UPDATE SET value = excluded.value RETURNING (ctid)
```
1. CREATE TABLE文を発行してます。
1. INSERT文を発行してます。

ちゃんと2件のデータが入ってます。
```sql
PS D:\work\2022\AdventCalendar\03_psqlscripts> psql -U postgres pgdt
psql (15.1)
"help"でヘルプを表示します。

pgdt=# select ctid, id, text(value) from cyabudai;
 ctid  | id | text
-------+----+------
 (0,1) |  1 | rice
 (0,2) |  2 | miso
(2 行)
```

## 仮想空間の挙動（２）
![pgdt_02.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/151699/ee35c67e-ecc1-9326-6d36-f8c357bca0db.gif)

1. ごはんを持ち上げてます。
1. ごはんを再度ちゃぶ台においてますが、元の位置に戻りません。
ごはんは左手前にないと落ち着かない派です（；＿；

## 物理空間の挙動（２）
上記挙動の裏（物理空間）では、何が起きてるかログを見てみます。
```
2022-12-18 15:56:37.114 JST [28564] LOG:  duration: 2.631 ms  statement: INSERT INTO cyabudai VALUES (1, 'rice') ON CONFLICT (id) DO UPDATE SET value = excluded.value RETURNING (ctid)
```

1. この時点では何も実施しません。
1. UPDATE文(INSERT ... ON CONFLICT)を発行してます。

ちゃんとごはんはUPDATEされてますが、ctidが(0,1)から(0,3)になってます。元の位置(0,1)には旧データが残ったままということです。
```sql
pgdt=# select ctid, id, text(value) from cyabudai;
 ctid  | id | text
-------+----+------
 (0,2) |  2 | miso
 (0,3) |  1 | rice
(2 行)
```


## 仮想空間の挙動（３）
![pgdt_03.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/151699/ed0e44bd-e824-beea-b875-92267dc55c8f.gif)

1. ちゃぶ台返しをします。
1. ごはんや味噌汁は元の位置にもどり、綺麗なちゃぶ台が復活します。

## 物理空間の挙動（３）
上記挙動の裏（物理空間）では、何が起きてるかログを見てみます。
```
2022-12-18 17:00:49.267 JST [13412] LOG:  duration: 206.626 ms  statement: TRUNCATE cyabudai
2022-12-18 17:00:49.968 JST [24792] LOG:  duration: 309.302 ms  statement: TRUNCATE cyabudai
```

1. TRUNCATE文を発行してます。
1. トリガ関数を実行してますが、unityユーザで実行のため何も起こりません。


ビールがないからといって、ちゃぶ台返ししてはいけません。


現場からは以上です。

# まとめ
今年は、PostgreSQLの内部挙動をデジタルツインで表現することを行いました。
学びとしては、
1. 物理空間⇔仮想空間のデジタルツインを構築できた。まぁ、ツインというにはかなり恣意的なんで、デジタルブラザーくらいかな・・・。
1. RETURNING句がMERGE文だと使えないことを知った（[MERGE文](https://www.postgresql.org/docs/15/sql-merge.html)）。ctidを取得したいだけに2回クエリ発行するのも面倒だったので、今回はINSERT ... ON CONFLICTで対応した。
1. wolframscriptでUnityオブジェクト操作も楽々できた。

これからも楽しくPostgreSQLライフを送りたいと思います！