はじめに
Oracle Database 23c Free がリリースされました。
その中で興味深い機能の１つである、 JSON Relational Dualityを試してみます。
JSON Relational Duality
はリレーショナル表とJSONドキュメント双方のメリットを生かすソリューションですが、データベース管理者への依存度が高い機能であるとも言えると思います。
ただし、SQL言語で実装・操作ができる事でデータ属性・実装・変更・管理など開発にかかわる様々な場面でデータベースの機能を利用可能だと考えられます。
情報元はマニュアルのカーレースを題材にしたもので、どなたでも簡単に試す事ができると思います。
表およびJSON Relational Duality Viewsの作成
RACE、DRIVER、TEAM、DRIVER_RACE_MAP 表とJSON Relational Duality View(driver_dv、race_dv、team_dv)を作成します。今回は特に、TEAM、DRIVER、team_dv を対象にします。
各表の作成
CREATE TABLE team(team_idINTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
name VARCHAR2(255) NOT NULL UNIQUE,
points INTEGER NOT NULL,
CONSTRAINT team_pkPRIMARY KEY(team_id));
CREATE TABLE driver(driver_idINTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
name VARCHAR2(255) NOT NULL UNIQUE,
points INTEGER NOT NULL,
team_id INTEGER,
CONSTRAINT driver_pkPRIMARY KEY(driver_id),
CONSTRAINT driver_fkFOREIGN KEY(team_id) REFERENCESteam(team_id));
CREATE TABLE race(race_idINTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
name VARCHAR2(255) NOT NULL UNIQUE,
laps INTEGER NOT NULL,
race_date DATE,
podiumJSON,
CONSTRAINT race_pkPRIMARY KEY(race_id));
CREATE TABLE driver_race_map(driver_race_map_idINTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
race_id INTEGER NOT NULL,
driver_id INTEGER NOT NULL,
position INTEGER,
CONSTRAINT driver_race_map_ukUNIQUE(race_id, driver_id),
CONSTRAINT driver_race_map_pkPRIMARY KEY(driver_race_map_id),
CONSTRAINT driver_race_map_fk1FOREIGN KEY(race_id)
REFERENCESrace(race_id),
CONSTRAINT driver_race_map_fk2FOREIGN KEY(driver_id)
REFERENCESdriver(driver_id));
JSON Relational Duality Viewの作成
CREATE JSON RELATIONAL DUALITY VIEW team_dv AS
SELECT JSON {'teamId' : t.team_id,
'name' : t.name,
'points' : t.points,
'driver' :
[ SELECT JSON {'driverId' : d.driver_id,
'name' : d.name,
'points' : d.points WITH NOCHECK}
FROM driver d WITH INSERT UPDATE
WHERE d.team_id = t.team_id ]}
FROM team t WITH INSERT UPDATE DELETE;
CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {'driverId' : d.driver_id,
'name' : d.name,
'points' : d.points,
'teamInfo' :
(SELECT JSON {'teamId' : t.team_id,
'name' : t.name WITH NOCHECK}
FROM team t WITH NOINSERT NOUPDATE NODELETE
WHERE t.team_id = d.team_id),
'race' :
[ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
'raceInfo' :
(SELECT JSON {'raceId' : r.race_id,
'name' : r.name}
FROM race r WITH NOINSERT NOUPDATE NODELETE
WHERE r.race_id = drm.race_id),
'finalPosition' : 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 JSON RELATIONAL DUALITY VIEW race_dv AS
SELECT JSON {'raceId' : r.race_id,
'name' : r.name,
'laps' : r.laps WITH NOUPDATE,
'date' : r.race_date,
'podium' : r.podium WITH NOCHECK,
'result' :
[ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
'position' : drm.position,
'driverInfo' :
(SELECT JSON {'driverId' : d.driver_id,
'name' : 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;
JSON Relational Duality Viewへのデータ挿入と構成している表の内容確認
INSERT INTO team_dv VALUES ('{"teamId" : 301,
"name" : "Red Bull",
"points" : 0,
"driver" : [ {"driverId" : 101,
"name" : "Max Verstappen",
"points" : 0},
{"driverId" : 102,
"name" : "Sergio Perez",
"points" : 0} ]}');
Commit;
1行挿入しました。
TEAM表の検索
select * from team;
TEAM_ID NAME POINTS
---------- ---------------------------------------- ----------
301 Red Bull 0
DRIVER表の検索
select * from driver;
DRIVER_ID NAME POINTS TEAM_ID
---------- ---------------------------------------- ---------- ----------
101 Max Verstappen 0 301
102 Sergio Perez 0 301
JSON Relational Duality View (TEAM_DV) の検索
sselect * from team_dv;
DATA
--------------------------------------------------------------------------------
{"_metadata":{"etag":"9F0F855A39F4AE8FECD7E6A123855FD1","asof":"0000000001EA949A
"},"teamId":301,"name":"Red Bull","points":0,"driver":[{"driverId":101,"name":"M
ax Verstappen","points":0},{"driverId":102,"name":"Sergio Perez","points":0}]}
最後に
今回のSQL操作の範囲とイメージです。
一部の処理を試す形に限定していますが、リレーショナル表とJSONドキュメント双方のメリットを生かす意味に少し触れる事が出来た気がする一方で、Object設計に対する重要性も感じました。また、JSON Relational Duality に関する情報がどのように管理されるのか、性能や領域管理なども気になるところです。