Oracle Database 23cの注目機能、JSON Relational Dualityを触ってみる

# はじめに

先日[Oracle Database 23cのFree-Developer Release](https://www.oracle.com/database/free/)がリリースされました。

23cの注目機能の一つにJSON Relational Duality（ブログなどでは、日本語で「JSONとリレーショナルの二面性」と記載されていますが、~~全然ピンとこないので~~この記事ではそのまま英語で記載します）があります。

この機能は、一言で言えば、「データをJSON形式でスキーマレスに扱えるドキュメント指向データベースの良さと、ACID特性を持ちデータをリレーショナルに扱うRDBMSの双方の特徴を融合したもの」です。

この後紹介していきますが、具体的には通常のリレーショナルなテーブルと、JSON Relational Duality Viewというビュー(このビューがドキュメント指向データベース(のようなもの) [^1]になっています)を作成し、そのいずれからもデータの取得、更新などが実施可能な仕組みです。一方を更新すると、もう一方のデータも自動的に更新されるというものです。　
データ更新の際には、もちろんデータの整合性が保証されます。(データ整合性の仕組みについてはこの記事で詳細は解説できませんが、軽めに触れます)

[^1]: 「のようなもの」と書いたのは、JSON形式で格納できるものの、格納先はRDBMSのビューになるので、事前にデータ構造を決める必要があるためです（厳密には「スキーマレス」ではない）

つまり、この機能を利用すると、従来のドキュメント指向データベースとRDBMSのトレードオフを考慮しなくても良いということになります。

百聞は一見にしかずなので、早速触っていきましょう。
この記事では、JSON Relational Dualityの機能や仕組み全てを網羅することはできないので、なんとなくイメージを掴んでいただけるように紹介していきます。

# 環境構築

## Oracle Database 23cのインストール

すでに様々な賢者の方々が環境構築手順を公開されているので、皆様の環境に合わせて構築してください。
この記事ではOracle Linux8にインストールした環境を使っていきます。

https://qiita.com/shakiyam/items/ca08d6a034f860f84b0b

https://qiita.com/500InternalServerError/items/45b4dd0cfc04fb1bab5e

https://qiita.com/ora_gonsuke777/items/564471e460b2b58d8aa2

## ユーザ（スキーマ）の作成

ここでは、検証用のデータベースユーザ（スキーマ）を作成していきます。

oracleユーザで以下のコマンドを実行します。
この記事では、PDB(pluggable database)を利用します。

```sh
[oracle@database23c ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Apr 14 02:16:00 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered.

SQL>
```

以下のDDLを実行します。
この記事では、JSON Relational Dualityの略称で`JRD`というスキーマを作成します。
パスワードは任意です。

```sh
SQL> CREATE USER JRD IDENTIFIED BY "<パスワード>" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
```

作成したユーザを管理者ユーザにします。

```sh
SQL> GRANT DBA TO JRD;
```

```sh
SQL> GRANT UNLIMITED TABLESPACE TO JRD;
```

これでユーザの作成は完了です。

## ORDSのインストール

JSON Relational Dualityで利用するビューには以下の手段でアクセスが可能です。

* SQL
* HTTP/S(REST API)
* MongoDB API
* ORDS(Oracle REST Data Services)
* SODA(Simple Oracle Document Access)

この記事では、SQLとORDSを利用したHTTP/S(REST API)でアクセスしたいと思います。

そこで、ORDSのインストールが必要なので、インストールしていきます。

インストールは以下のドキュメントをベースに実施してください。

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.1/ordig/installing-and-configuring-oracle-rest-data-services.html#GUID-B6661F35-3EE3-4CB3-9379-40D0B8E24635

インストール時に指定するデータベースサービス名とユーザ名はそれぞれ`FREEPDB1`、`JRD`(もしくは作成したユーザ名)にしてください。

:::note warn
**`ora-01031 insufficient privileges`エラー**
インストール時に`ora-01031 insufficient privileges`という権限エラーが発生した場合は、以下のコマンドでORDSパッケージに含まれているDDLを実行してください。
実行時はSYSDBAユーザで実行してください。
DDLは、`/opt/oracle/ords/scripts/ords_installer_privileges.sql`に存在します。（デフォルト設定でのインストールの場合)

```sh
SQL> @/opt/oracle/ords/scripts/ords_installer_privileges.sql JRD
```
:::

## テーブルとJSON Relational Duality Viewの作成

いよいよ、テーブルと`JSON Relational Duality View`を作成していきます。

今回は以下のDDLを利用します。
スキーマ名(データベースユーザ名)を変更した方は、`ORDS.ENABLE_OBJECT`プロシージャのスキーマ名を変更してください。

:::note info
このブログではDDLで作成している`race`テーブルおよびその関連テーブルやトリガーについては利用しません。
:::

```sql
drop view if exists team_dv;
drop view if exists race_dv;
drop view if exists driver_dv;
drop table if exists driver_race_map;
drop table if exists race;
drop table if exists driver;
drop table if exists team;

CREATE TABLE IF NOT EXISTS team
(team_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name    VARCHAR2(255) NOT NULL UNIQUE,
   points  INTEGER NOT NULL,
   CONSTRAINT team_pk PRIMARY KEY(team_id));

CREATE TABLE IF NOT EXISTS driver
(driver_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
 name VARCHAR2(255) NOT NULL UNIQUE,
 points INTEGER NOT NULL,
 team_id INTEGER,
CONSTRAINT driver_pk PRIMARY KEY(driver_id),
CONSTRAINT driver_fk FOREIGN KEY(team_id) REFERENCES team(team_id));

CREATE TABLE IF NOT EXISTS race
(race_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   name      VARCHAR2(255) NOT NULL UNIQUE,
   laps      INTEGER NOT NULL,
   race_date DATE,
   podium  JSON,
   CONSTRAINT   race_pk PRIMARY KEY(race_id));

CREATE TABLE IF NOT EXISTS driver_race_map
(driver_race_map_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
race_id INTEGER NOT NULL,
driver_id INTEGER NOT NULL,
position INTEGER,
CONSTRAINT driver_race_map_pk PRIMARY KEY(driver_race_map_id),
CONSTRAINT driver_race_map_fk1 FOREIGN KEY(race_id) REFERENCES race(race_id),
CONSTRAINT driver_race_map_fk2 FOREIGN KEY(driver_id) REFERENCES driver(driver_id));

CREATE OR REPLACE TRIGGER driver_race_map_trigger BEFORE INSERT ON driver_race_map
FOR EACH ROW
DECLARE
    v_points  INTEGER;
    v_team_id INTEGER;
BEGIN
SELECT team_id INTO v_team_id FROM driver WHERE driver_id = :NEW.driver_id; 
IF :NEW.position = 1 THEN
    v_points := 25;
ELSIF :NEW.position = 2 THEN
  v_points := 18;
ELSIF :NEW.position = 3 THEN
  v_points := 15;
ELSIF :NEW.position = 4 THEN
  v_points := 12;
ELSIF :NEW.position = 5 THEN
  v_points := 10;
ELSIF :NEW.position = 6 THEN
  v_points := 8;
ELSIF :NEW.position = 7 THEN
  v_points := 6;
ELSIF :NEW.position = 8 THEN
  v_points := 4;
ELSIF :NEW.position = 9 THEN
  v_points := 2;
ELSIF :NEW.position = 10 THEN
  v_points := 1;
ELSE
  v_points := 0;
END IF;
UPDATE driver SET points = points + v_points
 WHERE driver_id = :NEW.driver_id;
UPDATE team SET points = points + v_points
    WHERE team_id = v_team_id;
END;
/

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_dv AS
  SELECT JSON {'raceId' IS r.race_id,
               'name'   IS r.name,
               'laps'   IS r.laps WITH NOUPDATE,
               'date'   IS r.race_date,
               'podium' IS r.podium WITH NOCHECK,
               'result' IS
[ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id, 'position' IS drm.position,
                                UNNEST
                                  (SELECT JSON {'driverId' IS d.driver_id,
                                                  'name'     IS d.name}
                                     FROM driver d WITH NOINSERT UPDATE NODELETE
WHERE d.driver_id = drm.driver_id)} FROM driver_race_map drm WITH INSERT UPDATE DELETE
WHERE drm.race_id = r.race_id ]} FROM race r WITH INSERT UPDATE DELETE;

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {'driverId' IS d.driver_id, 'name' IS d.name,
               'points'   IS d.points,
               UNNEST
                 (SELECT JSON {'teamId' IS t.team_id,
                               'team'   IS t.name WITH NOCHECK}
                    FROM team t WITH NOINSERT NOUPDATE NODELETE
                    WHERE t.team_id = d.team_id),
               'race'     IS
[ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id, UNNEST
                                  (SELECT JSON {'raceId' IS r.race_id,
                                                'name'   IS r.name}
                                     FROM race r WITH NOINSERT NOUPDATE NODELETE
                                  WHERE r.race_id = drm.race_id),
                            'finalPosition'   IS drm.position}
FROM driver_race_map drm WITH INSERT UPDATE NODELETE
                WHERE drm.driver_id = d.driver_id ]}
FROM driver d WITH INSERT UPDATE DELETE;

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_dv AS
SELECT JSON {'teamId' IS t.team_id, 'name' IS t.name,
               'points'  IS t.points,
               'driver'  IS
[ SELECT JSON {'driverId' IS d.driver_id, 'name' IS d.name,
'points' IS d.points WITH NOCHECK} FROM driver d WITH INSERT UPDATE
WHERE d.team_id = t.team_id ]} FROM team t WITH INSERT UPDATE DELETE;

BEGIN
    ORDS.ENABLE_OBJECT(
        P_ENABLED  => TRUE,
        P_SCHEMA => 'JRD',
        P_OBJECT  =>  'DRIVER_DV',
        P_OBJECT_TYPE => 'VIEW',
        P_OBJECT_ALIAS => 'driver_dv',
        P_AUTO_REST_AUTH => FALSE
    );
    COMMIT; 
END;
/
BEGIN
    ORDS.ENABLE_OBJECT(
        P_ENABLED => TRUE,
        P_SCHEMA => 'JRD',
        P_OBJECT =>  'RACE_DV',
        P_OBJECT_TYPE => 'VIEW',
        P_OBJECT_ALIAS => 'race_dv',
        P_AUTO_REST_AUTH => FALSE
    ); 
    COMMIT;
END; 
/ 
BEGIN
    ORDS.ENABLE_OBJECT(
        P_ENABLED => TRUE,
        P_SCHEMA => 'JRD',
        P_OBJECT =>  'TEAM_DV',
        P_OBJECT_TYPE => 'VIEW',
        P_OBJECT_ALIAS => 'team_dv',
        P_AUTO_REST_AUTH => FALSE
    ); 
    COMMIT;
END;
/
```

このDDL内の以下の部分に注目します。

```sql
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {'driverId' IS d.driver_id, 'name' IS d.name,
               'points'   IS d.points,
               UNNEST
                 (SELECT JSON {'teamId' IS t.team_id,
                               'team'   IS t.name WITH NOCHECK}
                    FROM team t WITH NOINSERT NOUPDATE NODELETE
                    WHERE t.team_id = d.team_id),
               'race'     IS
[ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id, UNNEST
                                  (SELECT JSON {'raceId' IS r.race_id,
                                                'name'   IS r.name}
                                     FROM race r WITH NOINSERT NOUPDATE NODELETE
                                  WHERE r.race_id = drm.race_id),
                            'finalPosition'   IS drm.position}
FROM driver_race_map drm WITH INSERT UPDATE NODELETE
                WHERE drm.driver_id = d.driver_id ]}
FROM driver d WITH INSERT UPDATE DELETE;
```

ここで作成している`JSON RELATIONAL DUALITY VIEW`がいわゆるドキュメント指向データベース(のようなもの)[^1]のビューを作成しているDDLになります。
先ほどのDDL全体では、3つの`JSON RELATIONAL DUALITY VIEW`を作成しています。

また、この`JSON RELATIONAL DUALITY VIEW`はGraphQL構文を利用して書くこともできます。
例えば、`driver_dv`をGraphQL構文で書く場合は以下のようになります。

```sql
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
  driver @insert @update @delete
  {
    driverId : driver_id
    name     : name
    points   : points
    team @noInsert @noUpdate @noDelete @unnest
    {
      teamId : team_id
      team   : name @noCheck
    }
    race : driver_race_map @insert @update @noDelete
    [
     {
      driverRaceMapId : driver_race_map_id
      race @noInsert @noUpdate @noDelete @unnest
      {
        raceId : race_id
        name   : name
      }
      finalPosition   : position
     }
    ]
  };
```

SQLよりもかなりシンプルに書くことできますね。
DDL内にGraphQL構文を書けるというのは面白い試みだなと個人的には思います。

以上で、　テーブルおよび`JSON Relational Duality View`の作成は完了です。

# 動作確認

ここからは動作確認を行います。

今回はORDSを利用してHTTPでアクセスします。

まずはORDSを起動します。
今回はスタンドアローンモードで起動します。

```sh
[oracle@database23c ~]$ ords serve
2023-04-14T03:01:42Z INFO       ORDS has not detected the option '--config' and this will be set up to the default directory.

ORDS: Release 23.1 Production on Fri Apr 14 03:01:44 2023

Copyright (c) 2010, 2023, Oracle.

Configuration:
  /etc/ords/config/

2023-04-14T03:01:45.077Z INFO        HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
2023-04-14T03:01:45.103Z INFO        Disabling document root because the specified folder does not exist: /etc/ords/config/global/doc_root
2023-04-14T03:01:51.017Z INFO        Configuration properties for: |default|lo|
db.servicename=freepdb1
=========
java.class.version=61.0

2023-04-14T03:01:51.026Z WARNING     *** jdbc.MaxLimit in configuration |default|lo| is using a value of 20, this setting may not be sized adequately for a production environment ***
2023-04-14T03:01:51.027Z WARNING     *** jdbc.InitialLimit in configuration |default|lo| is using a value of 3, this setting may not be sized adequately for a production environment ***
```

cURLで`team_dv`ビューにアクセスします。
エンドポイントは`http://localhost:8080/ords/<スキーマ名>/<ビュー名>`になります。
ORDSインストール時にポート番号などを変更している場合は、適宜変更してください。

```sh
[opc@database23c ~]$ curl http://localhost:8080/ords/jrd/team_dv/ | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   796    0   796    0     0   1542      0 --:--:-- --:--:-- --:--:--  1542
{
  "items": [],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 0,
  "links": [
    {
      "rel": "self",
      "href": "http://localhost:8080/ords/jrd/team_dv/"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8080/ords/jrd/metadata-catalog/team_dv/"
    },
    {
      "rel": "first",
      "href": "http://localhost:8080/ords/jrd/team_dv/"
    }
  ]
}
```

まだ何もデータが入っていないので、itemsは空の状態です。

`team_dv`ビューにデータ登録します。

以下のJSONファイルを作成します。

```json:data.json
{
    "teamId": 2,
    "name": "Mercedes",
    "points": 0,
    "driver": [
        {
            "driverId": 105,
            "name": "George Russell",
            "points": 0
        },
        {
            "driverId": 106,
            "name": "Lewis Hamilton",
            "points": 0
        }
    ]
}
```

以下のコマンドを実行します。

```sh
curl -i -X POST --data-binary ＠data.json -H "Content-Type: application/json" http://localhost:8080/ords/jrd/team_dv/
```

`HTTP/1.1 201 Created`でレスポンスが返って来ればOKです。

次に登録したデータを確認してみます。

```sh
[opc@database23c ~]$ curl http://localhost:8080/ords/jrd/team_dv/ | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   796    0   796    0     0   3275      0 --:--:-- --:--:-- --:--:--  3275
{
    "items": [
      {
        "teamId": 2,
        "name": "Mercedes",
        "points": 0,
        "driver": [
          {
            "driverId": 105,
            "name": "George Russell",
            "points": 0
          },
          {
            "driverId": 106,
            "name": "Lewis Hamilton",
            "points": 0
          }
        ],
        "_metadata": {
          "etag": "536001F31A8718819AEEF28EC20D8677",
          "asof": "00000000004FE36F"
        },
        "links": [
          {
            "rel": "self",
            "href": "http://localhost:8080/ords/jrd/team_dv/2"
          }
        ]
      }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 2,
    "links": [
      {
        "rel": "self",
        "href": "http://localhost:8080/ords/jrd/team_dv/"
      },
      {
        "rel": "describedby",
        "href": "http://localhost:8080/ords/jrd/metadata-catalog/team_dv/"
      },
      {
        "rel": "first",
        "href": "http://localhost:8080/ords/jrd/team_dv/"
      }
    ]
}
```

データが登録されていることが確認できます。

ところで、先ほどのレスポンスにETagが含まれていることが確認できます。
これはOracle Databaseが自動的に生成するものです。

例えば、先ほどのデータには以下のようなETagが含まれています。

```json
      "_metadata": {
        "etag": "536001F31A8718819AEEF28EC20D8677",
        "asof": "00000000004FE36F"
      }
```

このETagはデータ整合性を担保するために利用できます。

ユーザは、このETagの値をリクエストデータに含めることで、ビューおよびその裏側で同期されているリレーショナルなテーブルで管理されている最新のデータに対して更新がされることを保証できます。

実際に試してみましょう。

ここからは`driver_dv`を利用します。

まずは現時点でのデータを取得してみます。

```sh
[opc@database23c ~]$ curl http://localhost:8080/ords/jrd/driver_dv/ | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1277    0  1277    0     0   4892      0 --:--:-- --:--:-- --:--:--  4892
{
  "items": [
    {
      "driverId": 105,
      "name": "George Russell",
      "points": 0,
      "teamId": 2,
      "team": "Mercedes",
      "race": [],
      "_metadata": {
        "etag": "A8BB1825F6218EC0D300671173540597",
        "asof": "00000000004FE44A"
      },
      "links": [
        {
          "rel": "self",
          "href": "http://localhost:8080/ords/jrd/driver_dv/105"
        }
      ]
    },
    {
      "driverId": 106,
      "name": "Lewis Hamilton",
      "points": 0,
      "teamId": 2,
      "team": "Mercedes",
      "race": [],
      "_metadata": {
        "etag": "D3FF3213793E306204BB5E5060368E41",
        "asof": "00000000004FE44A"
      },
      "links": [
        {
          "rel": "self",
          "href": "http://localhost:8080/ords/jrd/driver_dv/106"
        }
      ]
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 4,
  "links": [
    {
      "rel": "self",
      "href": "http://localhost:8080/ords/jrd/driver_dv/"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8080/ords/jrd/metadata-catalog/driver_dv/"
    },
    {
      "rel": "first",
      "href": "http://localhost:8080/ords/jrd/driver_dv/"
    }
  ]
}
```

次に以下のようなデータを作成します。
今回はdriverIdが105のデータを更新します。

その際に先ほど返却されたETagを含めます。

```json:data_2.json
{
    "_metadata": {
        "etag": "A8BB1825F6218EC0D300671173540597"
    },
    "driverId": 105,
    "name": "Max Emilian Verstappen",
    "points": 0,
    "teamId": 2,
    "race": []
}
```

```sh
curl -i -X PUT --data-binary @data_2.json -H "Content-Type: application/json" http://localhost:8080/ords/jrd/driver_dv/105 
```

`HTTP/1.1 200 OK`でレスポンスが返って来れば問題ありません。
これは先ほどリクエストデータに含めたETagがOracle Databaseが管理しているETagと一致している（最新のデータであることを示す）ため、問題なく更新できます。

あえて、誤ったETagで更新してみましょう

```json:data_3.json
{
    "_metadata": {
        "etag": "xxxxxxxx"
    },
    "driverId": 105,
    "name": "Max Emilian Verstappen",
    "points": 0,
    "teamId": 2,
    "race": []
}
```

```sh
curl -i -X PUT --data-binary @data_3.json -H "Content-Type: application/json" http://localhost:8080/ords/jrd/driver_dv/105 
```

`HTTP/1.1 412 Precondition Failed`でレスポンスが返ってきます。
エラーになったのは、Oracle Databaseが管理しているETagとリクエストデータに含まれてるETagが一致していないためです。

つまり、データを取得してからデータの更新をリクエストする間に対象データに変更があったことを示します。

このETagにより、従来のドキュメント指向データベースでは難しいと言われるデータ整合性を保証します。

最後にリレーショナルなテーブルがどのようになっているかを確認します。

```sh
[oracle@database23c ~]$ sqlplus jrd@パスワード/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Apr 14 02:16:00 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered.

SQL> select * from driver;

 DRIVER_ID
----------
NAME
--------------------------------------------------------------------------------
    POINTS    TEAM_ID
---------- ----------
       105
Max Emilian Verstappen
         0          2

       106
Lewis Hamilton
         0          2

 DRIVER_ID
----------
NAME
--------------------------------------------------------------------------------
    POINTS    TEAM_ID
---------- ----------


SQL> select * from team;

   TEAM_ID
----------
NAME
--------------------------------------------------------------------------------
    POINTS
----------
         2
Mercedes
         0


SQL> 
```

リレーショナルなテーブルの方にもデータが反映されていますね。

# まとめ

このようにJSON Relational Dualityを利用すると、ドキュメント指向データベースとリレーショナルデータベースの良いとこどりをすることができます。
このブログでご紹介したのはほんの一部ですので、興味がある方はぜひOracle Database 23cで遊んでみてください。

# 参考資料

https://github.com/oracle-samples/oracle-db-examples/tree/main/json-relational-duality