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問合せに以下を記述します。



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

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


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

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

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



ページ・アイテム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コードとして以下を記述します。


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


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

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

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



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

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




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

以上でアプリケーションは完成です。アプリケーションを実行すると、記事の先頭の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に残しておきます。）

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


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


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


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

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

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

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

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

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