2021年2月10日水曜日

制約の違反に対応したエラー・メッセージを定義する

オランダ在住のRoel Hartmanさん(ODTUGのボード・メンバーです)が彼のブログに以下の記事を書いていました。

Stop using validations for checking constraints!

記事の内容は、データベースの表に設定している制約に違反したときに発生するエラー・メッセージを画面に表示する際には、Oracle APEXが提供している(アプリケーション定義に含まれる)エラー処理ファンクションを活用しましょう、というものです。関連するドキュメントの記載としては、パッケージAPEX_ERRORの説明になります。

彼の記事の中で面白いなと思ったのは、出力するメッセージの取得にAPEX_LANG.MESSAGEを使用しているところです。このファンクションは元々はアプリケーションの翻訳に使用するものです。翻訳で使用する方法については、以前にこのような記事を書きました。もちろん、アプリケーションの翻訳をしなくても、キーとなる値を指定して、メッセージとする文字列を取得することができます。

実際に実装して効果を確認してみましょう。


確認用アプリケーションの作成


Oracle APEXのワークスペースにサインインし、SQLワークショップからユーティリティのクイックSQLを開きます。

以下のクイックSQLのモデルを使って、表AET_EMPとAET_EMP_ATTRIBUTESを作成します。表AET_EMP_ATTRIBUTESには、各種の制約を付加しておきます。これらの制約に違反したときに表示されるエラー・メッセージを、Oracle Databaseが返すメッセージから、利用者向けに分かりやすいメッセージに変更することが本記事のゴールです。

# prefix: aet
# prefixPKwithTname: true
# semantics: default
# language: ja
emp /insert 5
  emp_id /pk /values 1,2,3,4,5
  emp_name

emp_attributes
  emp_attr_id /pk 
  emp_id /fk emp
  national_id num /unique
  attribute_name vc20 /nn
  attribute_value vc80
  gender vc1 /check M,F 
  age num /between 0 and 120

制約としては、主キー、外部キー、一意、NULL不可、チェック、範囲指定を設定しています。必要なディレクティブはモデルに含んでいるので、クイックSQLでSQLの生成、SQLスクリプトの保存、レビューおよび実行、即時実行とボタンをクリックしていけば、今回使用する表が作成されます。

続いてアプリケーション・ビルダーより、空のアプリケーションを作成します。アプリケーションの作成より新規アプリケーションを選び、アプリケーションの名前だけを指定して、アプリケーションの作成を実行します。ここではアプリケーションの名前を、エラー処理の確認としています。

空のアプリケーションが作成されたら、確認に使用するフォームと対話グリッドのページを作成します。最初にフォームのページを作成します。アプリケーション・ビルダーの画面より、ページの作成を開始します。

コンポーネントのフォームを選択します。

フォーム付きレポートを選択します。

レポート・タイプを対話モード・レポート、フォーム・ページ・モードをモーダル・ダイアログとします。それ以外は任意の値を設定できます。次に進みます。

ナビゲーションのプリファレンスとして新規ナビゲーション・メニュー・エントリの作成を選択します。次に進みます。


データ・ソースはローカル・データベース、ソース・タイプを表とし、表/ビューの名前としてAET_EMP_ATTRIBUTES(表)を選択します。次に進みます。


主キー制約でのエラーも発生させるため、主キー型としてデータベースで管理(ROWID)を選択します。作成を実行します。


以上でレポートとフォームの2つのページが作成されます。続いて、対話グリッドのページを作成します。フォームの作成と同様に、ページの作成を開始し、フォームの選択に続いて対話グリッドを選びます。


ページ名は任意ですが、ここではグリッド編集とします。次に進みます。


ナビゲーションのプリファレンスとして新規ナビゲーション・メニュー・エントリの作成を選択します。次に進みます。


データ・ソースとしてローカル・データベース、編集が有効をON、ソース・タイプを表とし、表/ビューの名前としてAET_EMP_ATTRIBUTES(表)を選択します。こちらも主キー制約のエラーを発生させるため、あえて主キー列にROWIDを選択します。作成を実行します。


対話グリッドが作成されます。作成されたアプリケーションを実行し、表の制約に違反したデータを投入し、エラーの表示を確認してみましょう。

すでに作成済みの行を重複させて保存を実行すると、ORA-00001: 一意制約(DBCLOUD_TEST.AET_EMP_ATTRIBU_EMP_ATTR_ID_PK)に反しています、が発生しました。


Ageに400を指定して作成を実行すると、ORA-02290: チェック制約(DBCLOUD_TEST.AET_EMP_ATTRIBUTES_AGE_BET)に違反しました、が発生しました。



以上で確認用アプリケーションは完成です。

エラー処理ファンクションの設定


マニュアルに記載されている例を元に、エラー処理ファンクションを定義します。コードに含まれているコメントが日本語マニュアルでも翻訳されていないので、処理内容がわかるように日本語に翻訳しました。




元の記事ではapex_lang.create_messageの引数であるp_languageにapex_util.get_preference('FSP_LANGUAGE_PREFERENCE')を与えていますが、これはアプリケーション言語の導出元がアプリケーション・プリファレンス(FSP_LANGUAGE_PREFERENCEを使用)になっていて初めて有効なので、アプリケーションのプライマリ言語になっているときはNULLを返します。日本では大抵、プライマリ言語として日本語(ja)を設定しているため、単にjaを指定する様に変更しました。


定義したファンクションを、アプリケーション定義のエラー処理ファンクションとして設定します。


アプリケーションを実行して、再度、制約に関するエラーを発生させます。最初は制約の名前が表示されます。


再度、エラーを発生させると、次はTODOと表示されます。


テキスト・メッセージの登録


アプリケーション・ビルダーを開いて、共有コンポーネントのグローバリゼーションに含まれるテキスト・メッセージを開きます。

エラーの原因となった制約名にTODOというテキストが登録されていることが確認できます。

このTODOを適切なメッセージに置き換えます。今回は制約AET_EMP_ATTRIBU_EMP_ATTR_ID_PKにたいして、従業員属性のIDとなる値は一意である必要があります。とテキストを設定します。変更の適用を行います。

再度、同じ操作を行うと、エラー・メッセージが変更されていることが確認できます。

フォームを使って同じデータを作成しようとしても、表示されるエラー・メッセージは同じです。


以上でエラー処理ファンクションを使った、エラー・メッセージの変更方法の紹介は完了です。

他にも設定した制約があります。演習を行うつもりで、メッセージを設定してみてください。


発生したエラーの確認方法


ひとつ追加で説明すると、Oracle APEXのアプリケーションで発生したエラーはすべて記録されます。

右上の管理者アイコンよりメニューを開き、アクティビティのモニターを呼び出します。


左下にあるアプリケーション・エラーを開きます。


いつ、誰が、どのアプリケーションの、どのページで、どのようなエラーが発生したのか、などの情報が記録されているので、利用者からの報告が無くても、作成した機能の不具合の有無を確認することが可能です。


データベースのスキーマに定義した制約に対応したエラー・メッセージが定義済みであれば、アプリケーションを開発する人にとっては、相当な負担の軽減になるかと思います。是非、活用の検討をしてみてください。

確認のために作成したアプリケーションのエクスポートをこちらに置いておきます。
https://github.com/ujnak/apexapps/blob/master/exports/apexerrormessage.sql

テキスト・メッセージはエクスポートに含まれていません。これは仕様です。

完

2021年2月6日土曜日

データベースに証明書の登録と削除を行うアプリを作る

 ブロックチェーン表を扱うアプリを作る際に、証明書の登録と削除が手間だったので、簡単に行うアプリを作ってみました。レポートとフォームを使っています。Oracle APEXのフォーム・リージョンで可能なことは、表の操作だけではない、という例にもなっています。

既存のアプリケーションにページを追加します。

アプリケーション・ビルダーよりページの作成を実行します。

コンポーネントのフォームを選択します。


フォーム付きレポートを選択します。


レポート・タイプは対話モード・レポート、フォーム・ページ・モードはモーダル・ダイアログを選びます。その他の入力項目は任意です。以下ではレポート・ページ名に証明書一覧、フォーム・ページ名に証明書と指定しています。また、ブレッドクラムもBreadcrumbを選択することで、追加しています。ページ番号はレポートが4、フォームが5になっています。すでに4、5のページが存在する場合は異なるページ番号になります。その場合は、特にフォームについて、以降の作業のページ・アイテム名のP5_で始まる部分を置き換えるようにしてください。設定を行った後、次に進みます。


ナビゲーションのプリファレンスは新規ナビゲーション・メニュー・エントリの作成を選択します。次に進みます。


データ・ソースはローカル・データベースとし、ソース・タイプにSQL問合せを選択します。SQL SELECT文を入力には、以下のSQLを設定します。次に進みます。
select
    certificate_guid,
    user_name,
    distinguished_name,
    certificate
from user_certificates

主キー列としてCERTIFICATE_GUID (Varchar2)を選択します。作成をクリックします。


レポートとフォームのページが作成されます。作成された証明書一覧のレポート・ページを実行します。


USER_CERTIFICATESビューの内容が一覧されていることが確認できます。


作成をクリックし、フォームを開きます。USER_CERTIFICATESビューに対しては、どのような操作もできません。そのため、PL/SQLコードで作成や削除の処理を行うようにします。開発者ツール・バーより、ページの編集5をクリックし、ページ・デザイナを呼び出します。


ローカルにある証明書のファイルを指定するページ・アイテムはP5_CERTIFICATEです。このページ・アイテムを選択し、タイプをファイル参照...に設定します。また、ビューのBLOB列にファイルをアップロードすることはできません。記憶域タイプをTable APEX_APPLIATION_TEMP_FILESに設定し、一時的なファイル・アップロードのためにOracle APEXが用意している表を利用します。

ページ・アイテムのP5_USER_NAMEとP5_DISTINGUISED_NAMEは画面入力の対象ではないので、両方を選択してタイプを表示のみに変更します。

続いてプロセス・ビューを開き、フォームに対応するプロセス(ここではプロセス・フォーム証明書)を選択します。設定のターゲット・タイプをPL/SQL Codeに変更し、挿入/更新/削除するPL/SQLコードに以下を設定します。フォームのページ番号が5でない場合は、P5の部分を変更してください。

declare
    l_cert blob;
    l_cert_guid raw(16);
begin
    case :APEX$ROW_STATUS
    when 'C' then
        select blob_content into l_cert
        from apex_application_temp_files
        where name = :P5_CERTIFICATE;
        dbms_user_certs.add_certificate(l_cert, l_cert_guid);
        :P5_CERTIFICATE_GUID := l_cert_guid;
    when 'D' then
        dbms_user_certs.drop_certificate(:P5_CERTIFICATE_GUID);
    end case;
end;

APEX_APPLICATION_TEMP_FILESにアップロードされた証明書のデータ(列BLOB_CONTENT)を取り出し、DBMS_USER_CERTS.ADD_CERTIFICATEプロシージャに渡すことで、証明書をデータベースに登録しています。返却されるCERTIFICATE_GUIDの値は対応するページ・アイテムP5_CERTIFICATE_GUIDに設定します。削除要求はP5_CERTIFICATE_GUIDをDBMS_USER_CERTS.DROP_CERTIFICATEプロシージャに渡して実行しています。

トランザクションに関係なく行われる処理であるため、失われた更新の防止はOFF、行のロックもNoに設定します。

以上で、基本的な動作については実装が完了しました。先頭のGIF動画のような操作ができるようになっています。

以上で証明書の登録と削除を行うアプリケーションの作り方の紹介は完了です。ブラウザにて署名を作る部分も含んだアプリケーションのエクスポートを以下に置きました。ブロックチェーン表を作成するDDLは含んでいません。

https://github.com/ujnak/apexapps/blob/master/exports/blockchainapp.sql

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

完

ファイルの内容をページ・アイテムに設定する

アイテム・タイプがファイルのアップロードのページ・アイテムで選択したファイルの内容を、JavaScriptのFile APIを使って読み出し、異なるページ・アイテムに設定します。

実装例を紹介するために、空のAPEXアプリケーションを作成します。名前はFile APIとします。


アプリケーションが作成されます。

実装はすべてホーム・ページに対して行います。ページ・デザイナでホーム・ページを開きます。

最初にファイルを選択するページ・アイテムを作成します。

名前をP1_FILE、タイプをファイルのアップロードにします。ラベルはファイルとします。


続けて、ファイルの内容を保持するページ・アイテムを作成します。名前をP1_CONTENT、タイプをテキスト領域にします。ラベルは内容とします。


P1_FILEにファイルが選択されたときに、その内容を読み出す処理は動的アクションにて実装します。

P1_FILEに動的アクションを作成します。

名前をonChange P1_FILE、とします。タイミングはデフォルトでアイテムのP1_FILEが変更されたときに発火するようになっています。


TRUEアクションとしてJavaScriptコードの実行を選択します。設定のコードには以下を記述します。

影響を受ける要素の選択タイプにアイテム、アイテムとしてP1_CONTENTを選択します。


ファイルの内容が秘匿性が高い場合は、ページ・アイテムのプロパティを調整します。利用者に情報そのものを見せる必要はないので、タイプは非表示にします。タイプを非表示にしても、動的アクションによって値が設定できるよう、設定の保護された値をOFFにします。詳細の保存されていない変更の警告は無視します。ページ移動時に警告なしでP1_CONTENTの内容を破棄します。

セッション・ステートのストレージはリクエストごと(メモリーのみ)を選択します。


ページの送信時(フォームのページで送信を行ったとき)にページ・アイテムの値はブラウザからサーバーに送信されます。セッションごと(永続)、ユーザーごと(永続)を選択している場合は、受信したページ・アイテムの値をデータベースに保存します。結果としてサーバー側で内容を参照できるようになります。リクエストごと(メモリーのみ)の場合は、データベースに保存しません。通常は異なるページに配置されているページ・アイテムの値を参照するコードは書かない(そのような場合はアプリケーション・アイテムを使用する)ので、リクエストごと(メモリーのみ)でデータベースに保存する必要はありません。

Oracle APEXではページ・アイテムをセッション・ステートに保存する際に暗号化するオプションも提供しています。セキュリティのセッション・ステートに暗号化された値を保存です。

この設定はデフォルトでオンです。


ファイルの内容をページ・アイテムに設定する方法の紹介は以上になります。

簡単なアプリケーションですが、エクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/sample-file-api.zip

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

 追記

当初、想定していた要件についてはlocalStorageを使うのが正解でした。

完

2021年2月5日金曜日

コンポーネントの表示制御について

コンポーネントの表示/非表示を制御する方法には、いくつか種類があります。それぞれ、簡単に紹介しようと思います。紹介する機能は、以下の4つです。

  • ビルド・オプション
  • 認可スキーム
  • サーバー側の条件
  • 動的アクションとクライアント側の条件

より正確には表示/非表示に限らず、コンポーネントの有効化/無効化を行います。目で見て分かりやすい有効化/無効化の効果が表示/非表示なので、それを例にとっています。表示されないプロセスやその他のコンポーネントにたいしても、同様に効果があります。


ビルド・オプション


ビルド・オプションについては以前に記事を書いています。ここでは簡単に設定について紹介します。

ビルド・オプションは共有コンポーネントのアプリケーション・ロジックに含まれるビルド・オプションを開いて作成します。


今回は海外仕様というビルド・オプションを作成しています。


表示、非表示の制御はステータスによって決まります。コンポーネントのビルド・オプションとして、海外仕様が設定されている場合、このステータスが含めるであれば、リージョンは表示されます。逆に除外であれば、表示されません。


ほとんどのコンポーネントは、ビルド・オプションの設定を持っています。プロパティの構成に含まれるビルド・オプションにて、コンポーネントにビルド・オプションを設定します。


コンポーネントがリージョン、ページ・アイテムやボタンであれば、画面表示に関わるものなので表示/非表示が分かりやすい効果になります。プロセスなどのコンポーネントであれば、実行がされません。

ビルド・オプションにて除外されている場合は、そのコンポーネントがアプリケーション自体に存在しない、という扱いになります。


認可スキーム


認可スキームについても、以前に記事を書いています。こちらも簡単に設定の紹介をします。

認可スキームはは共有コンポーネントのセキュリティに含まれる認可スキームを開いて作成します。


実体が分かりやすいリーダー権限を参照することで、認可スキームについて確認します。


リーダー権限は、スキーム・タイプがブールを戻すPL/SQLファンクションになっています。他にも選択可能なスキーム・タイプはありますが、還元すると認可スキームは、真偽値を返すPL/SQLのファンクションです。


認可スキームで重要なのは、評価ポイントです。ビルド・オプションはステータスを含める、もしくは、除外にすると、その時点でアプリケーション全体の構成が変わります。認可スキームは、以下の4種類の評価ポイントがあり、その都度、評価されます。
  • セッションごとに1回
  • ページ・ビューごとに1回
  • コンポーネントごとに1回
  • 常時(キャッシュなし)
検証結果はキャッシュされるため(次に説明するサーバー側の条件に比べて)、パフォーマンス面で有利です。コンポーネントを有効にするか無効にするかを評価する際に、セッション・ステートに依存することが無ければ、認可スキームの利用を検討すべきです。

認可スキームの設定も、ほぼ全てのコンポーネントに含まれます。プロパティのセキュリティに含まれる認可スキームです。


認可スキームは、上記のコンポーネントへの設定以外にも、対話グリッドやフォームが行う操作の認可にも使われます。むしろ、こちらの使い方が本来の用途です。

サーバー側の条件


それぞれのコンポーネントを有効にするか無効にするか、そのコンポーネントが呼び出される際に評価する条件を設定できます。サーバー側の条件も還元すると真偽値を返すPL/SQLファンクションです。例えば、次のようなPL/SQLファンクション本体を設定すると、午前中だけリージョンが表示されます。

begin
if to_char(current_timestamp, 'AM') = '午前' then
return true;
end if;
return false;
end;


認可スキームとは異なり、コンポーネントが呼び出される度に評価されるため、その評価にセッション・ステートを使うことができます。セッション・ステートとは、APEXのセッションに紐づけられてデータベースに保存されている、アプリケーション・アイテムとページ・アイテムの値です。

セッション・ステートとして保持されている値は、開発者ツール・バーにあるセッションをクリックし、表示されるデバッグ画面より、ビューとしてセッション・ステートを選択し、設定をクリックすることで確認できます。


以下はデバッグ画面です。


ちなみに、Oracle APEXのアプリケーションは、セッションに紐づくデータをセッション・ステートとしてデータベースに保存(つまりディスクに保存)しているか、一時的な値であればブラウザ側で保持した状態になっていて、それ以外には一時的であってもデータは保持していません。そのため、データベースを再起動しても、Oracle APEXのセッションは継続します。

サーバー側の条件は一番融通のきく設定なので、ビルド・オプションや認可スキームが適切であっても、ついついサーバー側の条件を使いがちです。サーバー側の条件を設定する前に、ビルド・オプションや認可スキームが向いていないか、検討してみましょう。

動的アクション


動的アクションによっても、コンポーネントの表示/非表示を制御できます。今までの設定は、評価でfalseが返ってくると、(表示に関連するコンポーネントであれば) HTML自体が生成されませんでした。

動的アクションの場合は、HTML自体は生成されていることが前提です。この違いはセキュリティの観点では重要です。ブラウザの開発者ツールを使って参照されると困るのであればサーバー側の設定などを使用し、動的アクションによる制御は使わないようにしましょう。

例えば、ページのロード時に、ブラウザがMac以外で動作している場合はリージョンを非表示にする動的アクションは以下のように定義します。

タイミングのイベントをページのロードとしています。クライアント側の条件として、タイプにJavaScript式を選択し、以下の式を設定しています。

window.navigator.userAgent.indexOf("Macintosh") == -1


HTTPヘッダーのUser-AgentにMacintoshが含まれていなければ、Trueアクションとして登録されたアクションを実行します。上記では、非表示がTrueアクションです。

アクションとして非表示を選択し、選択タイプをリージョン、非表示にするリージョンは動的アクション - Macなら表示を設定しています。


リージョンの表示・非表示に関していうと、非表示のアクションは対象となるHTML要素にstyle="display: none;"を追加しています。表示のアクションは、その設定を取り除いています。

最初の画面にあるボタンのクリックにより、ビルド・オプション、認可スキーム、サーバー側の条件、動的アクションそれぞれでリージョンの表示・非表示の動きを確認するアプリケーションのエクスポートを以下に置きました。(説明に使っている例はアプリケーションには含んでいません)。
https://github.com/ujnak/apexapps/blob/master/exports/component-controls.sql

Oracle APEXのアプリケーション開発の一助になれば幸いです。

完

カスケードLOVを使う

タイプが選択リストまたはポップアップLOVのページ・アイテムにたいして、カスケードLOVのプロパティを指定することにより、リストされる一覧を、親となる別の選択リストやページ・アイテムの値にしたがって変更することができます。


カスケードLOVのテストに使用した表とテストデータは、以下のクイックSQLのモデルから生成しています。
# prefix: clov
# language: ja
# genpk: false
projects /insert 3
    id num /nn
    name

dept /insert 9
    id num /nn
    name

emp /insert 27
    id num /nn
    project_id num /nn /values 1,2,3
    dept_id num /nn /values 1,2,3,4,5,6,7,8,9
    name
プロジェクトの表CLOV_PROJECTSと担当部署の表CLOV_DEPTがあり、担当職員の表CLOV_EMPはプロジェクトと担当部署の双方を参照しています。カスケードLOVのテストに使用できるデータを生成するために参照制約の定義は行っていません。通常は外部キー参照の制約は付加します。

確認に使用したアプリケーションのエクスポートをこちらに置きました。クイックSQLで表を作成した後、アプリケーションをインポートすると手元で動作確認も可能です。
https://github.com/ujnak/apexapps/blob/master/exports/cascading-lov-test.sql

プロジェクトと担当部署はタイプが選択リストである通常のページ・アイテムの設定になります。担当職員の選択リストにカスケードLOVの設定を行います。


LOVのSQL問合せに、条件句を含んだSELECT文を設定します。
select name d, id r from clov_emp 
where dept_id = :P1_DEPT
and project_id = :P1_PROJECT
カスケードLOVの親アイテムとして、条件句に含まれるページ・アイテムP1_PROJECT、P1_DEPTの選択リストを設定します。これだけで親アイテムの値が変更されたときに、このページ・アイテムP1_EMPの内容がリフレッシュされます。


親が必要がONになっているので、P1_PROJECT、P1_DEPTのどちらかがNULLの場合は(値が選択されていない)、P1_EMPのリフレッシュを行いません。LOVのSQL問合せが、条件句に含まれているページ・アイテムがNULLであっても結果を返す場合は、親が必要をOFFにして、必ずリフレッシュを実施させます。

親アイテムに設定されたページ・アイテムは送信するアイテムに含める必要はありません。これらのページ・アイテムはそのままLOVのSQL問合せに送信されます。それ以外のページ・アイテム、例えば、テキスト・フィールドなどをLOVのSQL問合せに使用する際には、送信するアイテムに、そのページ・アイテムを指定します。

プロジェクトの選択リストをテキスト・フィールドに変更してみます。先ほどはページ番号が1でしたが、この例ではページ番号は2です。


この場合、ページ・アイテムP2_PROJECTは親アイテムの指定から外し、送信するアイテムとして指定します。


親アイテムとして設定されていないため、ページ・アイテムP2_PROJECTを変更しても自動的にはP2_EMPのリストはリフレッシュされません。そのため、動的アクションを設定します。

動的アクションが起動されるタイミングは、ページ・アイテムP2_PROJECTが変更されたときです。


実行されるアクションはアイテムP2_EMPのリフレッシュです。


基本的にカスケードLOVとなる選択リストのSQLにはページ・アイテムが含まれるはずなので、共有コンポーネントにすることはないでしょう。親アイテムは通常の選択リストなので、共有コンポーネントのLOVを使うことができます。

以上でページ・アイテムが選択リスト、またはポップアップLOVの場合に設定できる、カスケードLOVの設定についての説明は終了です。

Oracle APEXのアプリケーション開発の一助になれば幸いです。

完