日日是Oracle APEX

2024年3月4日月曜日

Oracle LiveLabsのExploring Operational Property Graphs in 23c Freeを手元の環境で実施する

Oracle LiveLabsにOracle Database 23cに実装されたSQL/PGQの演習が提供されています。

Operational Property Graphs Example with SQL/PGQ in 23c
https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3659

この演習のLab 1から4までは演習環境の準備で、実際の演習はLab 5から始まります。演習環境はOracle Cloudに構築することを前提としているため、準備のハードルは低くはありません。

先日、Oracle Database 23cとOracle REST Data Servicesのコンテナ・イメージを使って、Oracle Database 23c FreeでOracle APEXが動く環境を作りました（記事その１とその２）。その環境を使ってProperty GraphのLiveLabsを実施してみました。その手順を紹介します。

LiveLabsの演習ではOracle SQL Developerを使用していますが、本記事ではOracle SQL Developer Extension for VS Codeを代わりに使用します。

コンテナ・イメージから作成したOracle APEXの環境では、以下のURLからORDSのランディング・ページにアクセスします。

http://localhost:8181/ords/

コンテナ・イメージから作成したOracle APEXの環境で演習を実施するため、Lab 1から4の作業はすべてスキップします。

Lab 5は演習に使用するデータベース・スキーマの準備、Lab 6が実際にProperty Graphを操作するSQLの実行を行います。Oracle APEXのアプリケーションを使った演習は、Lab 6に続くBonus Labとして提供されています。

今回はBonus Labまでを行なうので、Lab 5、6で使用するスキーマをOracle APEXのワークスペースに紐づけて作成します。

ワークスペースを作成する

Oracle APEXの管理サービスにサインインし、新規にワークスペースを作成します。

http://localhost:8181/ords/apex_admin

管理ユーザー名とパスワードは、Oracle APEXのインストール時にスクリプト@apxchpwdを実行して決めた値になります。

作成済みのワークスペースが存在しない場合、サインイン後にワークスペースの作成を求められます。

ワークスペース名を指定します。今回の作業ではAPEXDEVとしました。

次へ進みます。

スキーマは新規に作成するため、既存のスキーマの再利用にいいえを選択します。

今回はスキーマ名はAPEXDEVとしました。このスキーマ名はOracle SQL Developer Extension for VS Code（長いので以降VS Codeとします）からデータベースに接続する際のユーザー名として指定します。Autonomous Database上にワークスペースを作成する場合は、スキーマ名はワークスペース名に接頭辞としてWKSP_が付加された名前がデフォルトになります。Oracle APEXを手作業でインストールしている場合はそのような制限はありませんが、ワークスペース名とそのワークスペースに紐づくデフォルトのスキーマ名は異なっている方が、データベースに直接接続可能なユーザー名を推測しにくくなるため、より安全だと言えます。

スキーマのパスワードを設定します。VS Codeから接続する際に、このパスワードを指定します。領域割当て制限(MB)は10000（10GB）とします。Oracle Database 23c Freeのユーザー・データの制限は12GBなので、ほぼ制限なしの設定になります。

VS CodeからはSQLNet経由でデータベースに接続します。そのため、Autonomous Databaseとは異なり、データベースのユーザーに対してREST対応SQLを有効にする必要はありません。

次へ進みます。

ワークスペースの管理者のユーザー名を指定します。今回の例ではadminとしています。管理者のパスワードと電子メールを指定します。管理者宛の電子メールを受け取るには、送信に使用するメール・サーバーを構成する必要があります。電子メールの構成については割愛します。

次へ進みます。

設定内容を確認し、ワークスペースの作成を実行します。

ワークスペースが作成されました。同時にスキーマも作成されています。

LiveLabsのLab 5とLab 6は、VS Codeから作成したスキーマに接続して実施します。

Lab 5 Configure setup materials and toolsの実施

VS Codeにデータベースへの接続を追加します。

接続名は任意、ロールはデフォルトです。ユーザー名、パスワードにAPEXのワークスペースとともに作成したスキーマ名、パスワードを指定します。パスワードの保存にチェックを入れています。ホスト名はlocalhost、サービス名はfreepdb1です。

テストを実施し設定を確認した上で、接続を保存します。

保存された接続をクリックすると、データベースに接続されます。

以上で後続の作業をVS Codeから実施できるようになりました。

（Task 1 - 3）グラフのセットアップに使用するマテリアル23cfree-property-graph.zipをダウンロードします。マテリアルは以下のURLからダウンロードします。

https://objectstorage.us-ashburn-1.oraclecloud.com/p/VEKec7t0mGwBkJX92Jn0nMptuXIlEpJ5XJA-A6C9PymRgY2LhKbjWqHeB5rVBbaV/n/c4u04/b/livelabsfiles/o/data-management-library-files/23cfree-property-graph.zip

wgetはインストールされていない場合もあるため、以下ではcurlを使ってダウンロードしています。

% curl -OL https://objectstorage.us-ashburn-1.oraclecloud.com/p/VEKec7t0mGwBkJX92Jn0nMptuXIlEpJ5XJA-A6C9PymRgY2LhKbjWqHeB5rVBbaV/n/c4u04/b/livelabsfiles/o/data-management-library-files/23cfree-property-graph.zip

% Total % Received % Xferd Average Speed Time Time Time Current

Dload Upload Total Spent Left Speed

100 815k 100 815k 0 0 403k 0 0:00:02 0:00:02 --:--:-- 403k

（Task 1 - 4）ダウンロードしたマテリアル23cfree-property-graph.zipを解凍します。

unzip -o 23cfree-property-graph.zip

% unzip -o 23cfree-property-graph.zip

Archive: 23cfree-property-graph.zip

inflating: graph/license.txt

inflating: graph/.DS_Store

inflating: graph/CreateKeys.sql

inflating: graph/BANK_ACCOUNTS.csv

inflating: graph/license.txt

inflating: graph/f106.sql

inflating: graph/BANK_TRANSFERS.csv

inflating: graph/BANK_ACCOUNTS.csv

inflating: graph/BANK_TRANSFERS.csv

inflating: graph/CreateKeys.sql

inflating: graph/f106.sql

展開した23cfree-property-graph.zipに含まれているgraphs/CreateKeys.sqlに、以下の２行が記載されています。

load bank_transfers /home/oracle/examples/graph/BANK_TRANSFERS.csv;
load bank_accounts /home/oracle/examples/graph/BANK_ACCOUNTS.csv;

ZIPファイルの展開先が/home/oracle/examplesとは限りません。CreateKeys.sqlの存在するディレクトリに移動してから実行することを前提として、以下の２行に書き直します。

load bank_transfers ./BANK_TRANSFERS.csv;
load bank_accounts ./BANK_ACCOUNTS.csv;

CSVを表にロードするloadコマンドは、SQLclの組み込みコマンドです。そのため、CreateKeys.sqlはSQLclより実行します。

接続上でコンテキスト・メニューを表示し、SQLclを開きます。

ターミナルでSQLclが開始します。パスワードを入力しデータベースにSQLclで接続します。

（Task 2 - 7）最初にcdコマンドでCreateKeys.sqlがあるディレクトリに移動し、続いてCreateKeys.sqlを実行します。

@CreateKeys.sql

表BANK_TRANSFERに5001行、BANK_ACCOUNTSに1000行のデータがロードされていることを確認します。

以上でスキーマのセットアップは完了です。

この後の作業でSQLclは使用しないので、SQLclはexitで終了します。

Lab 6 Operational Property Graphs Example with SQL/PGQ in 23 の実施

VS CodeでSQLワークシートを開いて、LiveLabsで案内されているSQLをコピペして実行することによりLab 6の演習を進めることができます。

Task 1 - 2：プロパティ・グラフBANK_GRAPHを作成します。表BANK_ACCOUNTSを頂点、BANK_TRANSFERSを辺として使用します。

CREATE PROPERTY GRAPH文を実行します。

Task 1 - 3：プロパティ・グラフBANK_GRAPHを確認します。

ビューUSER_PROPERTY_GRAPHSを検索します。

Task 1 - 4：プロパティ・グラフBANK_GRAPHを作成したDDLを表示します。

DBMS_METADATA.GET_DDLを実行します。

Task 1 - ５：プロパティ・グラフBANK_GRAPHの構成要素（頂点と辺）を確認します。

ビューUSER_PG_ELEMENTSを検索します。

Task 1 - 6：プロパティ・グラフBANK_GRAPHが持つプロパティとそのラベルを確認します。

ビューUSER_PG_LABEL_PROPERTIESを検索します。

Task 2からはプロパティ・グラフBANK_GRAPHをSQL/PGQを使って検索しています。

Task 2 - 1：最も送金を多く受け付けている（金額ではなく回数）上位10行を、受付回数の多い順番でリストします。

Task 2 - 2：送金を中継している回数が多い上位１0行を、中継回数の多い順番でリストします。

Task 2 - 3：今までの問い合わせの両方でアカウントID387が（先頭で）検索されています。アカウントID387からの送金を１から３ホップ内で受け付けた銀行とその送金を検索します。

Task 2 - 4：送金が送金元に３ホップで循環している送金を検索します。送金元の銀行から媒介している銀行へ送金され、媒介している銀行から送金元へ送金されている取引です。

Task 2 - 5：３ホップではなく４ホップのケース（中間に２行を経由する）を検索します。

Task 2 - 6：５ホップのケースを検索します。

Task 2 - 7：３から５回のホップで送金元に戻る送金を行なっている銀行を、１０行リストします。

Task 2 - 8：３から５回のホップで送金元に戻る送金を行なっている銀行の上位10行を、回数の多い順番でリストします。

Task 2 - 9：表BANK_TRANSFERSに６つの送金を追加します。

Task 2 - 10：Task 2 - 1で実行した問い合わせ（送金を受け付けた回数が多い上位10行）を再実行します。アカウントID387、934、135の順番が 135、934、387に変わっています。

Task 2 - 11：Task 2 - 5で４ホップで循環している送金を検索しました。４ホップで循環している送金元のアカウントIDが39である件数を数えます。

結果は０件です。

Task 2 - 12：表BANK_TRANSFERSに３つの送金を追加します。

Task 2 - 13：Task 2 - 11の検索を再実行します。送金元のアカウントIDが39で、送金が４ホップで送金元に循環している件数です。

循環している送金の件数が0回から5回に変わります。

Task 2 - 14：送金元のアカウントIDが39で、４ホップで送金が循環している５つの送金について、送金経路を一覧します。

Task 2 - 15：最後に演習で挿入した９行の送金を表BANK_TRANSFERSより削除します。CSVからデータをロードした直後の状態に戻るため、再度演習を行なうことができます。

Lab 6の演習は以上で終了です。

Bonus Lab Integration with APEXの実施

Bonus Labの実際の作業は、23cfree-property-graph.zipに含まれているAPEXアプリケーションf106.sqlのインポートです。以下に接続し、作成済みのワークスペースにサインインします。

http://localhost:8181/ords/apex/

管理者ユーザーによる初回接続時は、パスワードの変更を求められます。

アプリケーション・ビルダーを開きます。

アプリケーションf106.sqlのインポート作業を始めます。

インポートをクリックします。

インポートするファイルとしてf106.sqlを選択します。

次へ進みます。

アプリケーションのインストールを実行します。

サポートするオブジェクトのインストールを実行します。

アプリケーションSQL Property Graph in Oracle Database 23cのインストールが完了しました。

アプリケーションを実行します。

アプリケーションに管理者ユーザーでサインインします。

インポートしたアプリケーションには、Property Graph queries with SQLとUsing the GraphVis Pluginの２枚のページが含まれています。

これまでの作業を実施していれば、Pre-requisitesはすべて満たした状態になっています。

Property Graph queries with SQLのページでは、主にLab 6にて実施した演習の内容が実装されています。

最初の２つのリージョンでは、送金の多い上位10行を検索しています。

続くリージョンでは、循環している送金を一覧しています。データを更新した後に確認できるよう、Refreshボタンが実装されています。

表BANK_TRANSFERSに送金を５行追加するリージョンがあります。

追加したデータを削除し表BANK_TRANSFERSを最初の状態に戻す機能は、APEXアプリケーションには実装されていないようです。Task 2 - 15のDELETE文をSQLコマンドから実行することにより、表BANK_TRANSFERSを最初の状態に戻すことができます。

Using Graph Visualization Pluginのページでは、いくつかのSELECT文の結果をGraph Visualization Pluginを使って表示しています。

Lab 6のTask 2 - 2の送金を中継している回数が多い上位10行の取引を表示しています。

送金が４ホップで循環している経路を表示しています。

Lab 6のTask 2 - 14に近い検索ですが、アカウントID39で6ホップで循環している送金を表示しています。

Oracle LiveLabsのExploring Operation Property Graphs in 23c Freeの演習の紹介は以上になります。

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

完

2024年3月1日金曜日

対話グリッドで数独を実装する

Oracle APEXの対話グリッドで数独を実装してみました。問題を入れると解答を見つけるというものではなく対話グリッドで9x9のマス目を作成して、そのマス目に入っている数値が数独のルールに従っているかどうか、検証を行なうアプリケーションです。

作成したアプリケーションは以下のように動作します。

以下より、アプリケーションの作成手順を紹介します。

最初に以下のパッケージSUDOKUを作成します。数独の検証ロジックを実装しています。また、数独の9x9のマトリックスの数値はパッケージ変数G_MATRIXに : （コロン）で区切った文字列として保存します。G_ERRORSには重複している値を保存します。

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

アプリケーションの名前は数独とします。デフォルトで追加されているホーム・ページを削除し、代わりに対話グリッドのページを追加します。

対話グリッドの名前は数独、ソースとしてSQL問合せを選択し以下のSELECT文を記述します。編集を許可を選択します。

アプリケーションの作成をクリックして、これから開発するアプリケーションを作成します。

ページ・デザイナで対話グリッドのページを開きます。

アプリケーション作成ウィザードが対話グリッドの列IDを主キーとして認識していません。列IDを選択し、識別のタイプを非表示、ソースの主キーをオンに変更します。

列FUを選択し識別のタイプを非表示にし、設定の保護された値をオフにします。この列は変更のない対話グリッドの行をサーバーに送信するために、ページの送信前に動的アクションから値を変更するために使用します。利用者はこの列を見る必要がないため非表示にし、動的アクションからの更新を許可するために保護された値をオフにしています。

対話グリッドのプロパティの属性を開きます。

編集の実行可能な操作から行の追加と行の削除を外します。可能な操作は行の更新に限ります。

ツールバーより検索列の選択、検索フィールド、それと保存ボタンを外します。対話グリッドの保存からは、呼び出される検証やプロセスが編集可能リージョンとして（保存ボタンのある対話グリッドである）数独が紐づけられているものに限られます。また、呼び出される検証やプロセスは、変更された行毎に複数回呼び出されます。今回は対話グリッドには紐づかず、送信時に一度だけ呼び出される検証やプロセスの実行が必要なため、別にボタンを作成してページの送信を行います。

対話グリッドをJavaScriptから扱うため、詳細の静的IDとしてSUDOKUを設定します。

9x9の配列に入力した数値を保存するページ・アイテムを作成します。

識別の名前はP1_MATRIX、タイプはテキスト・フィールドです。ラベルは配列とします。

ページ・アイテムP1_MATRIXを初期化するプロセスを作成します。

レンダリング前のヘッダーの前にプロセスを作成します。識別の名前はP1_MATRIXの初期化、タイプはコードの実行とします。ソースのPL/SQLコードとして以下を記述します。

検証を実行するボタンを作成します。

作成したボタンはページ・アイテムP1_MATRIXの下に配置します。識別のボタン名はVALIDATE、ラベルは検証とします。外観のホットをオン、テンプレート・オプションのWidthはStretch、Spacing BottomにLargeを選択します。

動作のアクションはデフォルトのページの送信とします。

左ペインでプロセス・ビューを開き、検証を作成します。

識別の名前はG_MATRIXの初期化とします。検証の編集可能リージョンは未指定（- 選択 -のまま）にし、ページ送信時に１回だけ実行するようにします。タイプに式を選び、PL/SQL式として以下を記述します。

sudoku.initialize(:P1_MATRIX)

パッケージ変数G_MATRIXにページ・アイテムP1_MATRIXの値を設定しています。

ファンクションsudoku.initializeは検証として実行されますが、必ずtrueを返します。そのため検証に失敗することはありません。しかし、エラー・メッセージは必須であるため適当な値（今回は . ピリオド）を設定しておきます。

サーバー側の条件のボタン押下時にVALIDATEを設定します。

検証に常に実行というフラグがあります。今回はこれをオフにしています。

この設定はボタンの動作の検証の実行に対応しています。常に実行がオンのときは、ボタンの検証の実行がオフであっても、検証が実行されます。

続いて実行される検証を作成します。

識別の名前はG_MATRIXを更新とします。対話グリッドで変更された行を受信し、パッケージ変数G_MATRIXを更新します。結果として数独の配列の値が最新の状態になります。この検証も常に成功します。

検証の編集可能リージョンは数独とします。この検証は対話グリッドで変更された行の数だけ繰り返し実行されます。タイプとしてファンクション本体(ブールを返す)を選択し、PL/SQLファンクション本体として以下を記述します。

エラー・メッセージおよびサーバー側の条件は、先ほどの検証と同様に設定します。

続いて実行される検証を作成します。

識別の名前は数独の検証とします。検証の編集可能リージョンは未指定（- 選択 -のまま）にし、ページ送信時に１回だけ実行するようにします。タイプに式を選び、PL/SQL式として以下を記述します。

sudoku.validate_all()

数独の配列を検証します。検証結果はパッケージ変数G_ERRORSに保存し、このファンクション自体はつねにtrueを返します。

エラー・メッセージおよびサーバー側の条件は、先ほどの検証と同様に設定します。

検証結果を画面に反映させる検証を作成します。

識別の名前はC1の検証とします。検証の編集可能リージョンとして数独を設定します。この検証は対話グリッドが送信する行ごとに実行されます。タイプに式を選択し、PL/SQL式として以下を記述します。

sudoku.is_valid(1, :ID)

第１引数は列の位置、第２引数が行の位置です。その位置にある値が数独のルールに違反しているときにfalseが返されます。

この検証結果は画面に表示します。エラー・メッセージは重複しています。とします。表示位置はフィールド上でインライン表示、関連付けられた列にC1を設定します。エラー・メッセージは、その対話グリッド上のフィールドに表示されます。

サーバー側の条件のボタン押下時にVALIDATEを設定します。

検証のサーバー側の条件に実行スコープの設定があります。作成済および変更済の行またはすべての送信済の行の２通りを選ぶことができますが、リージョンが対話グリッドの場合、対話グリッド自体が作成済および変更済の行しか送信しないため、この設定に意味はありません。このような設定になっている理由については、下記のFR-2608に説明があります。

エンハンスメント・リクエストがAPEX IdeasのFR-2608 Interactive Grid: Execution Scope = "All Submitted Rows" should submit all rows, not just new/modified rows として上がっており、ステータスが将来実装予定（ROADMAP）になっています。

作成した検証を重複させ、検証C2の検証を作成します。名前、PL/SQL式、関連付けられた列に1が割り当たっている部分を2に変更します。

同様の作業を列C9までを対象として繰り返します。

最終的に列の検証が９つ、全部で１２の検証が作成されます。

プロセス数独 - 対話グリッド・データの保存を選択し、対話グリッドのデータをページ・アイテムP1_MATRIXに保存するように変更します。

識別のタイプをコードの実行に変更し、編集可能リージョンを未選択（- 選択 -に戻す）にします。ソースのPL/SQLコードとして以下を記述します。

sudoku.save(:P1_MATRIX);

成功メッセージは検証できました。とします。サーバー側の条件のボタン押下時にVALIDATEを設定します。

ここで一旦、アプリケーションの動作を確認してみます。

同じ列で数値が重複している場合は、両方のフィールドともにエラーが通知されます。

しかし行が異なる場合は、エラーが通知されない場合があります。

数独の配列G_MATRIXは初期のデータをページ・アイテムP1_MATRIXから取り出し、対話グリッドから送信された値で更新しているため、9x9の配列のデータをすべて保持しています。しかし、検証C1の検証からC9の検証までは、対話グリッドが送信する行、つまり変更された行だけを対象に検証が呼び出されます。結果として、エラー自体は検出されていても（パッケージ変数G_ERRORSには保存されている）、変更がされていない行ではエラーが通知されません。

検証を実行するためには、変更の有無にかかわらずすべての行をサーバーに送信する必要があります。

Oracle Corporationに所属しており、対話グリッドを開発したJohn Snydersさんが、彼のブログで対話グリッドの検証について記事を書かれています。

Interactive Grid Validation

https://hardlikesoftware.com/weblog/2022/04/25/interactive-grid-validation/

この記事の中で紹介されているAPEXアプリケーションIG Validateの、EMP Validate 6（ページ番号８）にページ送信前に変更のされていない行を見つけて非表示の列を更新する（ことにより送信対象にする）JavaScriptの実装が含まれています。

この実装を導入します。

左ペインで動的アクション・ビューを開き、動的アクションを作成します。

識別の名前はIGの全行を更新するとします。タイミングのイベントにカスタムを選択し、カスタム・イベントにapexpagesubmitを設定します。選択タイプにJavaScript式を選択し、JavaScript式としてapex.gPageContext$を設定します。