2022年7月20日水曜日

format osonとjson_transformを使用したAPEXアプリ

 Connor McDonaldさんが、Juice up your JSON on Autonomousという記事を書かれています。その記事で、Autonomous DatabaseであればJSONドキュメントはテキスト形式ではなく、OSON（バイナリ形式）で保存できると紹介されています。OSONについては、オラクルの公式ブログに簡単な紹介記事が載っています。一般的にバイナリ形式による保存はテキスト形式より、高速で効率も高いです。

JSONドキュメントをOSONで保存するには、Oracle Database 21cで追加されたデータ・タイプJSONを指定する必要があると思っていたので、Autonomous Databaseの19cで使えるとは知りませんでした。確かにマニュアルにも以下のように記載されています。

JSON開発者ガイド Release 19
3 JSONデータの格納および管理の概要

  • 高速な問合せおよび更新パフォーマンスが必要な場合は、最適化されたバイナリ形式OSONでBLOB記憶域を使用します。このためには、条件is json FORMAT OSONでチェック制約を使用します。

    リリース19cでは、OSON形式のBLOBは、Oracle Autonomousデータベースでのみサポートされます。


折角なので、FORMAT OSONとファンクションJSON_TRANSFORMを使ったAPEXアプリケーションを作成し、使い方を確認してみます。以前に書いた記事「Oracle APEXでJSONを使う」と同じアプリケーションを作成します。

アプリケーション自体は、特に変哲のない動作をします。


最初に表FF_EMPを作成します。

SQLワークショップのユーティリティのクイックSQLに以下のモデルを与え、SQLの生成、SQLスクリプトを保存、レビューおよび実行をクリックします。
# prefix: ff
emp
    empno num(4,0) /nn /pk
    ename vc50
    job vc50
    hiredate
    sal num
    flex json

レビューおよび実行の画面に、クイックSQLのモデルから生成されたDDLが表示されます。

以下の列FLEXの定義を
flex              clob check (flex is json)

OSONで保存するように修正します。
flex              blob check (flex is json format oson)

OSONで保存されるようにDDLを修正したのち、実行します。


確認画面が開くので、即時実行をクリックします。


DDLの実行が成功し、表FF_EMPが作成されます。アプリケーションの作成をクリックします。


確認画面が開きます。再度、アプリケーションの作成をクリックします。


アプリケーション作成ウィザードが起動します。

アプリケーションの名前をJSON Flexfieldの実装とします。表FF_EMPのフォーム付き対話モード・レポートのページが、アプリケーションに含まれていることを確認します。これ以上、必要なページや機能はありません。他は変更せず、アプリケーションの作成をクリックします。


アプリケーションが作成されました。これからの変更は、ページ番号２の対話モード・レポートとページ番号３のフォームに実施します。


表FF_EMPの列FLEXには、以下の例のようにshoeSizeとskillsを保存します。

{"shoeSize":12, "skills":["oracle", "c", "java"]}

対話モード・レポートを編集します。

列FLEXに保存されている、JSONドキュメントの属性shoeSizeとskillsをレポートに表示します。

ページ・デザイナにて、ページ番号２の対話モード・レポートのページを開きます。

リージョンFf EmpのソースのタイプをSQL問合せに変更し、SQL問合せに以下を記述します。

select
e.empno
, e.ename
, e.job
, e.hiredate
, e.sal
, ef.shoeSize
, listagg(ef.skill,',') skills
from ff_emp e,
json_table(e.flex, '$'
columns (
shoeSize number path '$.shoeSize',
nested path '$.skills[*]'
columns (skill varchar2(20 char) path '$')
)) as ef
group by e.empno, e.ename, e.job, e.hiredate, e.sal, ef.shoeSize
view raw ff_emp_select.sql hosted with ❤ by GitHub


Oracle APEXはサロゲート・キーが主キーに選択されていると想定して、ウィザードがレポートやフォームを作成しているように見受けられます。サロゲート・キーであればレポートやフォームにその値を表示する意味がないため、デフォルトでタイプが非表示になります。

列EMPNOは主キーなので、タイプは非表示となっています。列EMPNOもレポートに表示されるように、識別のタイプをプレーン・テキストに変更します。


対話モード・レポートの変更は以上になります。

ページ・デザイナにて、ページ番号３のフォームを開きます。

リージョンFf EmpのソースのタイプをSQL問合せに変更し、SQL問合せに以下を記述します。

select
e.empno
, e.ename
, e.job
, e.hiredate
, e.sal
, e.flex.shoeSize
, e.flex.skills
from ff_emp e
view raw ffemp_form_source.sql hosted with ❤ by GitHub


ページ・アイテムP3_EMPNOは主キーとして認識されているため、識別のタイプが非表示になっています。従業員番号は自動採番としていないため、画面から入力する必要があります。

ページ・アイテムP3_EMPNOを選択し、識別のタイプを数値フィールドに変更します。ラベルはEmpnoとします。ソースの主キーがONになっていることを確認します。


ページ・アイテムP3_SHOESIZEは、列FLEXのJSONドキュメントの属性shoeSizeとして保存されます。

ページ・アイテムP3_SHOESIZEを選択し、識別のタイプを数値フィールドに変更します。


ページ・アイテムP3_SKILLSは、列FLEXのJSONドキュメントの属性skillsに文字列の配列として保存されます。

ページ・アイテムP3_SKILLSを選択し、識別のタイプとしてチェック・ボックス・グループを選択します。LOVのタイプとして静的値を選択します。


静的値として、以下を指定します。表示値と戻り値のペアは、以下の画面の通りでなくてもアプリケーションの動作に影響はありません。そのため、別の値を設定することもできます。


フォームのソースから取り出される列SKILLSは、JSON配列です。つまり[ "c","sql","python" ]といった形式になります。ページ・アイテムのチェック・ボックス・グループは、コロン:で区切られた文字列c:sql:pythonを受け付けます。

そのため、JSON配列をチェック・ボックス・グループが受け付ける形式に変換する必要があります。

ページが初期化された後に、ページ・アイテムP3_SKILLSに読み込まれたJSON配列をAPEXで解釈できる配列に変換し、P3_SKILLSの内容を入れ替えます。

プロセス初期化フォームFf Empの直下に、新たにプロセスを作成します。

識別の名前をJSON配列からAPEX配列への変換とします。タイプとしてコードの実行を選択します。ソースのPL/SQLコードとして以下を記述します。

declare
l_json_array json_array_t;
l_apex_array apex_t_varchar2;
begin
l_json_array := json_array_t.parse(:P3_SKILLS);
for i in 0..l_json_array.get_size()-1
loop
apex_string.push(l_apex_array, l_json_array.get_string(i));
end loop;
:P3_SKILLS := apex_string.join(l_apex_array, ':');
end;
view raw json-array-to-apex-array.sql hosted with ❤ by GitHub

サーバー側の条件のタイプとして、アイテムはNULLではないを選択し、アイテムにP3_SKILLSを指定します。P3_SKILLSに値が設定されている場合に限り、配列の変換を実施します。


ボタンを押下しフォームが送信されたときに、ページ・アイテムP3_SKILLSの値をデータベースに保存します。この場合は先ほどとは反対に、コロン区切りの値をJSON配列に変換する必要があります。

プロセス・ビューを開き、プロセスプロセス・フォームFf Empの上に、新たにプロセスを作成します。

識別の名前をAPEX配列からJSON配列への変換とします。タイプとしてコードの実行を選択します。ソースのPL/SQLコードとして以下を記述します。

declare
l_json_array json_array_t := new json_array_t();
l_apex_array apex_t_varchar2;
begin
l_apex_array := apex_string.split(:P3_SKILLS,':');
for i in 1..l_apex_array.count
loop
l_json_array.append(l_apex_array(i));
end loop;
:P3_SKILLS := l_json_array.to_string;
end;
view raw apex-array-to-json-array.sql hosted with ❤ by GitHub


フォームFf Empのソースは、表FF_EMPの指定からSELECT文に変更されています。その変更に合わせて、プロセスプロセス・フォームFf Empの設定も変更します。

設定のターゲット・タイプをPL/SQL Codeに変更し、挿入/更新/削除するPL/SQLコードに以下を記述します。

begin
case
when :APEX$ROW_STATUS = 'C' then
insert into ff_emp(empno, ename, job, hiredate, sal, flex)
values(:P3_EMPNO, :P3_ENAME, :P3_JOB, :P3_HIREDATE, :P3_SAL, '{}');
update ff_emp
set flex = json_transform(flex, set '$.shoeSize' = :P3_SHOESIZE,
'$.skills' = :P3_SKILLS format json)
where empno = :P3_EMPNO;
when :APEX$ROW_STATUS = 'U' then
update ff_emp
set ename = :P3_ENAME
, job = :P3_JOB
, hiredate = :P3_HIREDATE
, sal = :P3_SAL
, flex = json_transform(flex, set '$.shoeSize' = :P3_SHOESIZE,
'$.skills' = :P3_SKILLS format json)
where empno = :P3_EMPNO;
when :APEX$ROW_STATUS = 'D' then
delete from ff_emp where empno = :P3_EMPNO;
end case;
end;
view raw ff-emp-op.sql hosted with ❤ by GitHub



失われた更新の防止と行のロックは、設定の変更にかかわらず機能します。

以上でアプリケーションは完成です。アプリケーションを実行すると、記事の先頭のGIF動画のような動作をします。

今回作成したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/json-flexfield-json-transport.sql

参考までに、以前の記事の手順で作成したアプリケーションのエクスポートはこちらです。
https://github.com/ujnak/apexapps/blob/master/exports/json-flexfield-blob.sql

ファンクションjson_transformを使用することにより（毎回JSONドキュメントを丸ごと更新するより）、ひとつのプロセス（タイプがフォーム - 行の自動処理(DML))で処理が完結しています。また、失われた更新の防止も有効になっています。

Oracle APEXのアプリケーションは基本的に表ありきで、スキーマ定義に従ってレポートやフォームを作成します。そのため、アプリケーションを変更する際に、スキーマ定義の変更も必要になるケースが多々あります。

しかし、利用頻度が少ないアプリケーションはさておき、アプリケーションの使用中にスキーマ定義を変更するのは、あまり安全な操作ではありません。JSONドキュメントであれば、スキーマ定義は無いようなもので、アプリケーション側の裁量でデータを保存する方法を決めることができます。

今回の例から説明すると、列FLEXには属性shoeSizeとskillsを保存していますが、表FF_EMPの持つ他の列ENAME、JOB、HIREDATE、SALも列FLEXに保存することもできます。（主キー列EMPNOのみ表FF_EMPに残しておきます。）

その場合の、対話モード・レポートのソースは以下になります。

select
e.empno
, ef.ename
, ef.job
, ef.hiredate
, ef.sal
, ef.shoeSize
, listagg(ef.skill,',') skills
from ff_emp e,
json_table(e.flex, '$'
columns (
ename varchar2(50 char) path '$.ename'
, job varchar2(50 char) path '$.job'
, hiredate date path '$.hiredate'
, sal number path '$.sal'
, shoeSize number path '$.shoeSize'
, nested path '$.skills[*]'
columns (skill varchar2(20 char) path '$')
)) as ef
group by e.empno, ef.ename, ef.job, ef.hiredate, ef.sal, ef.shoeSize
view raw report-ff-emp-source.sql hosted with ❤ by GitHub

フォームのソースは以下に変わります。

select
e.empno
, e.flex.ename
, e.flex.job
, e.flex.hiredate
, e.flex.sal
, e.flex.shoeSize
, e.flex.skills
from ff_emp e
view raw form-ff-emp-source.sql hosted with ❤ by GitHub

プロセスの挿入/更新/削除するPL/SQLコードは以下になります。

begin
case
when :APEX$ROW_STATUS = 'C' then
insert into ff_emp(empno, flex) values(:P3_EMPNO, '{}');
update ff_emp
set flex = json_transform(flex
, set
'$.ename' = :P3_EMPNO
, '$.job' = :P3_JOB
, '$.hiredate' = to_date(:P3_HIREDATE)
, '$.sal' = :P3_SAL
, '$.shoeSize' = :P3_SHOESIZE
, '$.skills' = :P3_SKILLS format json)
where empno = :P3_EMPNO;
when :APEX$ROW_STATUS = 'U' then
update ff_emp
set flex = json_transform(flex
, set
'$.ename' = :P3_EMPNO
, '$.job' = :P3_JOB
, '$.hiredate' = to_date(:P3_HIREDATE)
, '$.sal' = :P3_SAL
, '$.shoeSize' = :P3_SHOESIZE
, '$.skills' = :P3_SKILLS format json)
where empno = :P3_EMPNO;
when :APEX$ROW_STATUS = 'D' then
delete from ff_emp where empno = :P3_EMPNO;
end case;
end;
view raw ff-emp-op-all.sql hosted with ❤ by GitHub

データベースの表定義などを変更せずに、アプリケーション側で自由に列の追加や削除ができたり、アプリケーションの稼働中に気兼ねなく列の追加や削除ができるのは確かに便利です。ただし、データベースに保存されているデータの品質は間違いなく落ちるし、データの活用も難しくなるでしょう。

すべてをリレーショナル形式で保存しなければいけない、ということもありませんし、すべてをJSON（スキーマレス）で保存しなければいけない、ということもありません。

変更頻度が多い、高いアジリティが求められている場合はJSON列を使ってみる、データの品質に重きを置く場合は、できるだけ定義された表にデータを保存する、という感じでバランスを取るのが良さそうに思います。

ほとんどの列をJSONに保存したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/json-flexfield-all-json.sql

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完
投稿者 時刻:
ラベル: , ,