2021年2月4日木曜日

2つのフォームで親子関係の表を更新する

複数の表をひとつのフォームや対話グリッドにて操作する方法については、以前にこちらの記事を書きました。今回はそうではなく、親子関係がある2つの表があり、それぞれにフォーム・リージョンを同じページ上に作成し、操作を行います。


スキーマの作成


以下をモデルとして、クイックSQLにて表を作成します。親となる表DAC_DOCUMENTSに紐づく複数のアタッチメントが表DAC_ATTACHMENTSに保存されます。

# prefix: dac
# semantics: default
# prefixPKwithTname: true
documents
    document_name vc80

attachments
    document_id /fk documents
    attachment_name vc80
    attachment file 

SQLの生成を行い、生成されたSQLのレビューおよび実行をすることにより表を2つ作成します。アプリケーションは別途作成するため、クイックSQLからは作成しません。


アプリケーションの作成


続いてアプリケーション作成ウィザードを起動し、空のアプリケーション(グローバル・ページ、ホーム、ログイン・ページのみを含む)を作成します。アプリケーションの名称は任意です。今回は親子関係の表操作としました。


最初に表DAC_DOCUMENTSを対象としたレポートとフォームのページを作成します。最初に作成するページには、標準的な構成を適用します。その後、ページ作成ウィザードにて作成されたフォームのページに、表DAC_ATTACHMENTSを操作するコンポーネントを追加していきます。

アプリケーション・ビルダーよりページの作成をクリックし、ページ作成ウィザードを起動します。


コンポーネントに含まれるフォームを選択します。


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


レポート・タイプは対話モード・レポート、フォーム・ページ・モードは標準を選びます。それ以外は任意ですが、これ以降の本記事の内容を読み替える必要が無くなるため、スクリーンショット通りの設定が望ましいです。特にフォーム・ページ番号は3にしましょう。ページ・アイテム名が変わってしまいます。次に進みます。


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


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


主キー型を主キー列の選択とし、主キー列としてDAC_DOCUMENT_ID(Number)を選択します。作成をクリックします。


標準的な設定により、表DAC_DOCUMENTSを扱う対話モード・レポートとフォームのページが作成されました。実行して動作を確認してみましょう。


データが登録されていないので、対話モード・レポートにデータが見つかりません。と表示されています。作成をクリックします。


フォームのページが開きます。Document Nameを指定し、作成をクリックします。今回はこのフォームのページに、アタッチメントの一覧を表示する対話グリッドのリージョンと、アタッチメントの作成/編集/削除を行うフォームのリージョンを追加します。


作成した行がレポートに表示されます。



以上で、準備は完了です。

アタッチメントを操作するフォーム・リージョンの追加


ドキュメント編集のページ(ページ番号は3のはず)を開いて、フォーム・リージョンをドキュメント編集の下に追加します。


追加したリージョンの名前をアタッチメント編集とします。フォームを追加した時点でヘッダーの前に作成されるプロセスの名前を、初期化フォームアタッチメント編集に変更します。ソースの表名にはDAC_ATTACHEMENTSを選択します。


フォームに含まれるページ・アイテムのプロパティを調整します。

ページ・アイテムP3_DAC_ATTACHMENT_IDを選択します。こちらは主キーであり、利用者が入力することがないため、タイプを非表示にします。また、動的アクションによって設定するため、保護された値をOFFにします。また、主キーについてはONにします。


P3_DOCUMENT_IDはアタッチメントを親表の行に紐づけるために使用します。親表の主キーDAC_DOCUMENT_IDを参照します。こちらも利用者が直接指定することはないので、タイプを非表示にします。この値も動的アクションにて設定するため、保護された値をOFFにします。


P3_ATTACHMENTを選択します。このアイテムはファイルをバイナリ・データ(BLOB)として保存します。アップロードするファイルを選択するため、タイプをファイル参照...にします。記憶域タイプはBLOB column specified in Item Source attributeを選びます。Item Sourceはプロパティのソースの設定であり、このページ・アイテムにはデフォルトで列ATTACHMENTが設定されています。保存するファイルのそれぞれの属性であるMIMEタイプ列、ファイル名列、文字セット列、BLOB最終更新列として、ATTACHMENT_MIMETYPE、ATTACHMENT_FILENAME、ATTACHMENT_CHARSET、ATTACHMENT_LASTUPDを指定します。


アップロードするファイルの属性情報は利用者が指定することはないので、すべてのページ・アイテムを選択し、サーバー側の条件のタイプをなしに設定します。このように設定することにより、これらのページ・アイテムがページ上に生成されなくなります。


続けて、アタッチメントの登録、更新、削除を行うボタンを作成します。

登録のためのボタンを作成します。ボタン名はB_CREATE_ATTACH、名前は登録とします。データベース・アクションはSQL INSERT操作を指定します。


更新のためのボタンを作成します。ボタン名はB_UPDATE_ATTACH、名前を更新とします。データベース・アクションはSQL UPDATE操作を指定します。


削除のためのボタンを作成します。ボタン名はB_DELETE_ATTACH、名前を削除とします。データベース・アクションはSQL DELETE操作を指定します。


一旦、ページへの変更を保存し、ページを実行してみます。アタッチメント編集というフォーム・リージョンが追加されていることが確認できます。


フォームが作成されたので、登録、更新、削除の実際の処理を実装していきます。アタッチメント編集のリージョンのテンプレートをInline Dialogに変更します。アタッチメントの操作を行う際にダイアログとして開くことにより、同時にドキュメントを操作をできないよう制限します。


ドキュメント編集のリージョンに、Inline Dialogになったアタッチメント編集を開くボタンを追加します。ボタン名をB_OPEN_ATTACH、名前をアタッチメントの追加とし、動作のアクションを動的アクションで定義に設定します。


ボタンB_OPEN_ATTACHに動的アクションを追加します。追加する動的アクションの名前は、アタッチメント編集を開くとします。タイミングについてはデフォルトでボタンのクリックになります。このボタンからアタッチメント編集のリージョンを開いた際には、つねにファイルの登録作業のみを行うようにします。


Trueアクションを追加します。見やすくなるように左ペインを動的アクション・ビューに切り替えます。

アクションとしてクリアを作成します。選択タイプはアイテムとし、クリアするアイテムとして、P3_DAC_ATTACHMENT_ID、P3_DOCUMENT_IDの2つを指定します。表DAC_ATTACHEMENTSの主キーとなるDAC_ATTACHMENT_IDの値と、表DAC_DOCUMENTSの主キーを参照しているDOCUMENT_IDを未設定とすることで、それぞれレコードの保存時に新規で生成されます。


登録を行うボタンであるB_CREATE_ATTACHは(アクションを)表示にします。


更新を行うボタンであるB_UPDATE_ATTACHは(アクションを)非表示にします。


削除を行うボタンであるB_DELETE_ATTACHも(アクションを)非表示にします。


準備ができたので、アタッチメント編集のリージョンを開きます。アクションはリージョンを開く、選択タイプとしてリージョンを選び、リージョンにアタッチメント編集を指定します。


これで、アタッチメントの追加をクリックすることで、アタッチメントを登録するダイアログを開く動的アクションが完成しました。実際にページを動かしてみましょう。

アタッチメントの追加をクリックすると、アタッチメント編集のダイアログが開くところまで確認できます。


アタッチメントの操作を行うプロセスの追加


表DAC_DOCUMENTSを操作するプロセスはそのまま使います。プロセスのプロパティである挿入後に主キーを返すがONになっていることを確認します。表DAC_DOCUMENTSへの新規行の挿入と同時に表DAC_ATTACHMENTSに行を挿入する場合、このプロセスから返されるページ・アイテムP3_DAC_DOCUMENT_IDを表DAC_ATTACHMENTSの列DOCUMENT_IDとして設定します。

すでに表DAC_DOCUMENTSに行が存在し、操作が表DAC_ATTACHMENTSへの行の追加、更新、削除に限定される場合、このプロセスが呼び出されないようにサーバー側の条件を設定します。タイプはファンクション本体とし、以下のPL/SQLファンクション本体を設定します。

CREATE, SAVE, DELETEのボタンはドキュメントの操作を行うボタンで、B_CREATE_ATTACHはアタッチメントの追加ですが、P3_DAC_DOCUMENT_IDすなわち親のレコードが存在しないときだけは、親表であるDAC_DOCUMENTSへの行の挿入を行う、という条件です。
begin
    if :REQUEST in ('CREATE', 'SAVE', 'DELETE') then
        return true;
    end if;
    if :REQUEST = 'B_CREATE_ATTACH' and :P3_DAC_DOCUMENT_ID is null then
        return true;
    end if;
    return false;
end;

次に新規にプロセスを作成します。名前をドキュメントIDの指定とし、タイプはコードを実行、実行するPL/SQLコードは以下になります。
:P3_DOCUMENT_ID := :P3_DAC_DOCUMENT_ID;
表DAC_DOCUMENTSを処理するプロセスが使用した主キーが必ず、表DAC_ATTACHMENTSのDOCUMENT_IDになります。


フォーム・リージョンであるアタッチメント編集の処理を行うプロセスを追加します。新規にプロセスを作成します。名前をプロセス・フォームアタッチメント編集とし、タイプはフォーム - 行の自動処理(DML)を選択します。フォーム・リージョンはアタッチメント編集です。このプロセスはドキュメント編集の際には実行しないように、サーバー側の条件として、タイプをリクエストは値に含まれない、値はCREATE SAVE DELETEの3つのボタンを指定します。


アタッチメントが削除された際に、P3_DAC_ATTACHMENT_IDにキーとなる値が設定されたままだと、ページの再ロード時にORA-1403 - データが見つかりません。というエラーが発生するため、ページ・アイテムP3_DAC_ATTACHMENT_IDを初期化します。

プロセスを作成し、名前をアタッチメント削除の対応とします。タイプはセッション・ステートのクリアを選択します。設定のタイプはClear Items、アイテムとしてはP3_DAC_ATTACHMENT_IDを選択します。アタッチメントが削除されたときのみ実行するので、サーバー側の条件として、ボタン押下時にB_DELETE_ATTACHを設定します。


最後にドキュメントの作成、編集、削除を行ったときは、対話モード・レポートのページに戻り、アタッチメントの操作ではフォームのページを開き続けるようにします。

ブランチのページに移動2を選択し、サーバー側の条件として、タイプにリクエストは値に含まれるを選択し、値としてCREATE SAVE DELETEを設定します。

以上でアタッチメントを追加するプロセスの実装が完了しました。

アタッチメントの追加を行ってみます。フォームのページを実行します。レポートから呼び出していないので、ドキュメントは新規登録になります。Document Nameを指定し、アタッチメントの追加をクリックします。開いたダイアログにて、Attachment NameおよびアップロードするファイルをAttachmentとして選択します。以上の指定を行い、ダイアログ上の登録ボタンをクリックします。


行が作成されました。とメッセージが表示されますが、実際にアタッチメントが登録されたのか確認できません。

続いて、ドキュメントに紐づくアタッチメントをリストする対話グリッドを作成します。


アタッチメントをリストする対話グリッドの追加


新たに対話グリッドのリージョンを、アタッチメント編集の下に追加します。


名前はアタッチメント一覧とし、ソースのタイプは表/ビュー、表名にDAC_ATTACHMENTSを指定します。WHERE句に以下を設定し、送信するページ・アイテムとして、P3_DAC_DOCUMENT_IDを設定します。
DOCUMENT_ID = :P3_DAC_DOCUMENT_ID

認識された列のプロパティを調整します。

列DAC_ATTACHMENT_IDを選択し、タイプを非表示、主キーをONに変更します。


列DOCUMENT_IDのタイプを非表示にします。


対話グリッドではBLOBは扱えず、含まれるとエラーが発生します。列ATTACHMENTを選択し、サーバー側の条件のタイプをなしに設定することにより、取り扱い対象から外します。


列の調整は以上です。

作成した対話グリッドに動的アクションを設定可能にするため、対話グリッドを編集可能に変更します。

対話グリッドのAttributesを開き、編集の有効をONにします。ただし、編集を有効にする理由は動的アクションを登録するためだけで、操作はすでに登録済みのアタッチメント編集のフォーム・リージョンにて実施します。実行可能な操作の行の追加、行の更新、行の削除はすべてチェックを外します。


編集を有効にすることで追加された列APEX$ROW_SELECTORを選択し、設定の複数選択の有効化をOFFにします。対話グリッド上で選択可能な行を1行のみに制限し、その1行が選択されたときに、動的アクションによってアタッチメント編集のダイアログを開きます。


対話グリッドの編集操作は行わないので、列APEX$ROW_ACTIONを選択し、サーバー側の条件をなしにします。


アタッチメント一覧から、特定のアタッチメントを選択したときにダイアログを開く動的アクションを作成します。アタッチメント一覧の上でコンテキスト・メニューを表示させ、動的アクションの作成を実行します。


作成した動的アクションの名前は、アタッチメントの選択とします。タイミングのイベントは選択の変更[対話グリッド]、選択タイプはリージョン、リージョンはアタッチメント一覧とします。対話グリッド上で選択されている行が変更されたときに、登録されたTrueアクションの実行が行われます。


複数のTrueアクションを登録します。

最初に選択されたアタッチメントの情報をフォーム・リージョンであるアタッチメント編集のページ・アイテムに設定するために、JavaScriptコードを実行します。アクションとしてJavaScriptコードの実行を選択し、設定のコードに以下のJavaScriptコードを設定します。
let model = this.data.model;
let record = this.data.selectedRecords[0];
let attachment_id = model.getValue(record, "DAC_ATTACHMENT_ID");
let document_id = model.getValue(record, "DOCUMENT_ID");
let attachment_name = model.getValue(record, "ATTACHMENT_NAME");
$s("P3_DAC_ATTACHMENT_ID", attachment_id);
$s("P3_DOCUMENT_ID", document_id);
$s("P3_ATTACHMENT_NAME", attachment_name);

登録を行うボタンであるB_CREATE_ATTACHは(アクションを)非表示にします。


更新を行うボタンであるB_UPDATE_ATTACHは(アクションを)表示にします。


削除を行うボタンであるB_DELETE_ATTACHは(アクションを)表示にします。


準備ができたので、アタッチメント編集のリージョンを開きます。アクションはリージョンを開く、選択タイプとしてリージョンを選び、リージョンにアタッチメント編集を指定します。


対話グリッドが最初に開いたときに、デフォルトで先頭行が選択されることを抑制するために、Attributesの詳細に含まれるJavaScript初期化コードに以下を設定します。
function(config){
	  config.initialSelection = false;
		return config;
}

これで、アタッチメント一覧からアタッチメントを選択することで、アタッチメントの更新と削除を行うダイアログを開く動的アクションが完成しました。

以上ですべての実装が完了です。動作を確認してみましょう。最初に載せてあるGIF動画のような操作が可能になっているはずです。

ラベルやテンプレート・オプションなど、見栄えに関する部分には一切手を入れていませんし、また、ページ・アイテムの初期化など、調整できる点は多々あります。それでも、このような動作をするアプリケーションを作る方法について、感じが掴めたのではないでしょうか。

今回作成したアプリケーションのエクスポートをこちらに置きました。
https://github.com/ujnak/apexapps/blob/master/exports/parent-child-table-ops.sql

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

謝辞

対話グリッドを開いた際に、行の選択を行わない設定について、OndoorのVikas Pandeyさんの記事 3 Ways to Unselect Default Checkbox in Interactive Grid Oracle APEXを参考にさせていただきました。
https://blogs.ontoorsolutions.com/post/3-ways-to-unselect-all-checkbox-in-ig/

選択した対話グリッドの列の値を取り出し、ページ・アイテムに設定する方法については、YouTubeのOracle Apex & DatabaseチャンネルのSelect specific row from interactive grid in oracle apexを参考にさせていただきました。
https://www.youtube.com/watch?v=hBDlZYIzIeo

とても助かりました。ありがとうございます。

完

2021年2月2日火曜日

ブロックチェーン表の行への署名をブラウザにて実施する

ブロックチェーン表への挿入操作をブラウザから実施し、その操作によって挿入した行の署名をブラウザのWeb Cryptography APIを使って行う方法を試してみました。その作業記録です。

以下、作業手順です。

ブロックチェーン表BC_BROWSER_TRANSACTIONSを、以下のDDLで作成しました。

create blockchain table BC_BROWSER_TRANSACTIONS
(
    browser_transaction_id number generated by default on null as identity,
    transaction_name varchar2(80),
    transaction_volume number,
    register_date date
)
no drop until 31 days idle no delete locked HASHING USING "SHA2_512" version "v1";

鍵の生成には以下のコマンドを使っています。

% openssl genrsa -out private.pem 2048

Generating RSA private key, 2048 bit long modulus

..........................+++

..............................................+++

e is 65537 (0x10001)

%

証明書の元になるCSRを生成します。

% openssl req -new -days 365 -key private.pem -out cert.csr

You are about to be asked to enter information that will be incorporated

into your certificate request.

What you are about to enter is what is called a Distinguished Name or a DN.

There are quite a few fields but you can leave some blank

For some fields there will be a default value,

If you enter '.', the field will be left blank.

-----

Country Name (2 letter code) []:JP

State or Province Name (full name) []:Tokyo

Locality Name (eg, city) []:Minato-ku

Organization Name (eg, company) []:*******

Organizational Unit Name (eg, section) []:******

Common Name (eg, fully qualified host name) []:****.****.*****

Email Address []:****.*****@******.com


Please enter the following 'extra' attributes

to be sent with your certificate request

A challenge password []:

%

データベースが署名の検証に使用する証明書を作ります。

% openssl x509 -in cert.csr -out cert.crt -req -signkey private.pem -days 365

Signature ok

subject=/C=JP/ST=Tokyo/L=Minato-ku/O=Oracle/OU=Cloud/CN=apex.oracle.com/emailAddress=yuji.nakakoshi@oracle.com

Getting Private key

% 

Web Cryptography APIで扱えるように、秘密鍵をPKCS8形式に変換します。

% openssl pkcs8 -topk8 -nocrypt -in private.pem -outform DER -out private.der

% 

バイナリを16進数表現に変換します。

% xxd -ps private.der > private.hex 

%

変換された16進数表現を改行なしの1行にします。

% while read l

while> do

while> echo -n $l

while> done < private.hex > private.l

%

以下のPL/SQLスクリプトをOracle APEXのSQLワークショップに含まれるSQLコマンドより実行し、作成した証明書をデータベースに登録します。httpsにてアクセスできる場所に証明書を配置します。

declare
l_cert blob;
l_cert_guid raw(16);
begin
l_cert := apex_web_service.make_rest_request_b(
p_url => 'https://どこかにファイルを配置します/cert.crt',
p_http_method => 'GET'
);
dbms_user_certs.add_certificate(l_cert, l_cert_guid);
dbms_output.put_line('Certificate GUID = ' || l_cert_guid);
end;

ハッシュを取り出すファンクションBC_GET_HASHを作成します。

create or replace function bc_get_hash
(p_browser_transaction_id in number) 
return varchar2
as  
  l_inst_id binary_integer; 
  l_chain_id binary_integer; 
  l_sequence_no binary_integer; 
  l_row_data blob; 
  l_raw raw(2000); 
begin 
  select orabctab_inst_id$, orabctab_chain_id$, orabctab_seq_num$ 
  into l_inst_id, l_chain_id, l_sequence_no 
  from bc_browser_transactions where browser_transaction_id = p_browser_transaction_id;
  --
  dbms_blockchain_table.get_bytes_for_row_signature( 
      'APEXDEV', 
      'BC_BROWSER_TRANSACTIONS', 
      l_inst_id, 
      l_chain_id, 
      l_sequence_no, 
      1, 
      l_row_data 
  ); 
  l_raw := dbms_lob.substr(l_row_data, dbms_lob.getlength(l_row_data));
  return rawtohex(l_raw);
end bc_get_hash;

次に、外部で生成された署名を引数として、それをブロックチェーン表に加えるプロシージャBC_SIGN_ROWを作成します。

create or replace procedure bc_sign_row 
(p_browser_transaction_id in number,
 p_signature in varchar2
) 
as  
  l_inst_id binary_integer; 
  l_chain_id binary_integer; 
  l_sequence_no binary_integer; 
  sign_raw raw(2000); 
  cert_guid raw(16) := hextoraw('証明書のGUID'); 
begin 
  select orabctab_inst_id$, orabctab_chain_id$, orabctab_seq_num$ 
  into l_inst_id, l_chain_id, l_sequence_no 
  from bc_browser_transactions where browser_transaction_id = p_browser_transaction_id; 
  sign_raw := hextoraw(p_signature);
  dbms_blockchain_table.sign_row( 
      'APEXDEV', 
      'BC_BROWSER_TRANSACTIONS', 
      l_inst_id, 
      l_chain_id, 
      l_sequence_no, 
      NULL, 
      sign_raw, 
      cert_guid, 
      DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_RSA_SHA2_512 
   ); 
end bc_sign_row;

ブラウザで署名をつけるために使用するコードは以下です。

// 16進数表現からバイナリに変換する
// 参照先: https://stackoverflow.com/questions/38987784/how-to-convert-a-hexadecimal-string-to-uint8array-and-back-in-javascript
const fromHexString = hexString =>
  new Uint8Array(hexString.match(/.{1,2}/g).map(byte => parseInt(byte, 16)));
// バイナリから16進数表現に変換する
// 参照先: https://stackoverflow.com/questions/40031688/javascript-arraybuffer-to-hex
function buf2hex(buffer) { // buffer is an ArrayBuffer
    return Array.prototype.map.call(new Uint8Array(buffer), x => ('00' + x.toString(16)).slice(-2)).join('');
}

// 16進数表現のhashHexよりバイナリのhashDataを生成する
var hashHex = '95E144F09593033ABFC70AA78922ECDC6108A5E005BE30C10228E357414992216E8DCEF7A3A5163614FD553239DCA7EC57B8E28A8644EEC8AE19EFE40A1F5F6D';
var hashData = fromHexString(hashHex);
// 16進数表現のprvHexよりバイナリのprvDataを生成する
var prvHex = '16進数表記の秘密鍵のデータ';
var prvData = fromHexString(prvHex);

// 署名をつける
// 参照先: http://blog.livedoor.jp/k_urushima/archives/1759093.html
window.crypto.subtle.importKey(
    "pkcs8", prvData, 
    { name: "RSASSA-PKCS1-v1_5", hash: {name: "SHA-512"} },
    true, ["sign"]
).then(
    function(prvKey) {
      return window.crypto.subtle.sign("RSASSA-PKCS1-v1_5", prvKey, hashData);
    }
).then(
    function(sigVal) {
      console.log(buf2hex(sigVal));
    }
);

Oracle APEXのアプリケーションに組み込む前に、動作の確認を行います。

最初にブロックチェーン表BC_BROWSER_TRANSACTIONS表に一行挿入し、主キーであるBROWSER_TRANSACTION_IDを取得します。

declare
l_id number;
begin
insert into bc_browser_transactions(transaction_name, transaction_volume, register_date) values('Test Tx', 4000, sysdate)
returning browser_transaction_id into l_id;
dbms_output.put_line('BROWSER_TRANSCTION_ID = ' || l_id);
end;

以下のように表示されます。

BROWSER_TRANSCTION_ID = 2

次に、ファンクションBC_GET_HASHを呼び出して、行のハッシュ値を取得します。

begin
dbms_output.put_line(bc_get_hash(2));
end;

以下のようにハッシュ値が返されます。

82FE46794AE7052DAB42886E57C7E2C557CF98234989B6067330A1EC1E8D963CE0A4665534181756EB3D618BD7FE1253F64968D3DFF13F0E7AF9C463F5480D4E

このハッシュ値をhashHex、秘密鍵であるファイルprivate.lをprvHexに与えて、ブラウザの開発者ツールで、先ほどのJavaScriptを実行します。(httpsのページが開いている状態で開発ツールを動かす必要があります)。

コンソールに出力された署名をコピーします。


プロシージャBC_SIGN_ROWを、BROWSER_TRANSACTION_IDの数値とコピーした署名を引数にして呼び出します。

begin
bc_sign_row(2,
'981e9759d8 --- 電子署名の16進数表現 --- 5b2a38893989b2296cf'
);
end;

以下が表示されます。
文が処理されました。
付加された署名の検証を行います。

declare
l_num number;
begin
dbms_blockchain_table.verify_rows(
schema_name => 'APEXDEV',
table_name => 'BC_BROWSER_TRANSACTIONS',
number_of_rows_verified => l_num
);
dbms_output.put_line('Number of rows verified = ' || l_num);
end;

エラーが発生せず、以下のような出力が得られれば、設定の完了です。
Number of rows verified = 2
ビルディング・ブロックの準備ができたので、これからOracle APEXのアプリケーションを作成します。

まず、空のアプリケーションを作成します。名前をBrowser Transactionとしました。この作業については、スクリーンショットの添付は省きます。

続いて、ブロックチェーン表に1行書き込むフォームのページを作成します。アプリケーション・ビルダーより、ページの作成を実行します。


ページ・タイプがコンポーネントのフォームをクリックします。


フォーム付きレポートをクリックします。


ページ属性の設定を行います。レポート・タイプは対話モード・レポート、フォーム・ページ・モードは標準にします。その他の設定項目は任意です。次に進みます。


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


データ・ソースの表/ビューの名前として、表BC_BROWSER_TRANSACTIONSを選択します。すべての列をレポートの表示対象のままにし、次に進みます。


フォームの主キー列として、BROWSER_TRANSACTION_ID(Number)を選択します。作成を実行します。


これでレポートとフォームのページが作成されました。作成したアプリケーションを実行して、動作を確認してみます。最初にレポート・ページを開きます。

挿入済みの行を確認した後、作成をクリックします。


フォームが開くので、それぞれフォームに値を指定し、作成を実行します。


行が挿入されたことを確認します。


これで、通常の表への行の挿入については問題なく実装されていることが確認できました。これから、1行挿入時にブラウザ側で署名をつけるようにします。

フォーム・リージョンを含むページ(ページ番号は3であることを前提とします)をページ・デザイナで開き、ページ・アイテムP3_HASHを追加します。タイプは非表示にします。このページ・アイテムは動的アクションによって値を設定するため、保護された値をOFFにします。


同様に、ページ・アイテムP3_SIGNATUREを追加します。タイプは非表示です。また、保護された値はOFFにします。


主キーの項目であるP3_BROWSER_TRANSACTION_IDについても、動的アクションにて値を設定するため、保護された値をOFFにします。


ボタンCREATEの動作のアクションを、ページの送信から動的アクションで定義に変更します。


動的アクションの名前を作成のクリックとします。発火のタイミングについては、デフォルトでCREATEボタンがクリックされたときになります。


最初にサーバー側のコードを実行し、表BC_BROWSER_TRANSACTIONSに行を挿入します。挿入した結果として取得されるBROWSER_TRANSACTION_IDおよび行のハッシュ値をフォームに返します。結果として、ページ・アイテムP3_BROWSER_TRANSACTION_IDとP3_HASHに値が設定されます。

PL/SQLコードは以下になります。
declare
    l_id number;
begin
    begin
        insert into bc_browser_transactions
        (
            transaction_name, 
            transaction_volume,
            register_date
        )
        values
        (
            :P3_TRANSACTION_NAME,
            :P3_TRANSACTION_VOLUME,
            :P3_REGISTER_DATE
        )
        returning browser_transaction_id into l_id;
        commit;
    end;
    :P3_HASH := bc_get_hash(l_id);
    :P3_BROWSER_TRANSACTION_ID := l_id;
end;
送信するアイテムは、P3_TRANSACTION_NAME, P3_TRANSACTION_VOLUME, P3_REGISTER_DATEです。

戻すアイテムはP3_BROWSER_TRANSACTION_ID, P3_HASHです。

動作に影響はないはずですが、変更イベントの禁止はONとしておきます。


すでに動作を検証したJavaScriptのコードがあります。一部をページのプロパティのファンクションおよびグローバル変数の宣言に記述します。
// 16進数表現からバイナリに変換する
// 参照先: https://stackoverflow.com/questions/38987784/how-to-convert-a-hexadecimal-string-to-uint8array-and-back-in-javascript
const fromHexString = hexString =>
  new Uint8Array(hexString.match(/.{1,2}/g).map(byte => parseInt(byte, 16)));
// バイナリから16進数表現に変換する
// 参照先: https://stackoverflow.com/questions/40031688/javascript-arraybuffer-to-hex
function buf2hex(buffer) { // buffer is an ArrayBuffer
    return Array.prototype.map.call(new Uint8Array(buffer), x => ('00' + x.toString(16)).slice(-2)).join('');
}
// 16進数表現のprvHexよりバイナリのprvDataを生成する
var prvHex = '16進数表記の秘密鍵のデータ';
var prvData = fromHexString(prvHex);

Oracle APEXのアプリケーションはサーバー側にコードが保存されるので、このコードではサーバー側に秘密鍵の情報が保存されてしまいます。しかし、JavaScript自体の実行は手元のブラウザで行われるので、このprvHexのデータをローカルから取得するように書き換えることで、ユーザーそれぞれの秘密鍵を使用した署名ができるでしょう。秘密鍵の持ち方については、検討が必要です。

サーバー側でのPL/SQLコードの実行に続いて、JavaScriptコードの実行を行います。実行するJavaScriptのコードは以下になります。
// 署名をつける
// 参照先: http://blog.livedoor.jp/k_urushima/archives/1759093.html
window.crypto.subtle.importKey(
    "pkcs8", prvData, 
    { name: "RSASSA-PKCS1-v1_5", hash: {name: "SHA-512"} },
    true, ["sign"]
).then(
    function(prvKey) {
      return window.crypto.subtle.sign("RSASSA-PKCS1-v1_5", prvKey, fromHexString($v('P3_HASH')));
    }
).then(
    function(sigVal) {
        apex.page.submit( {
            request: "CREATE",
            set: {
                "P3_BROWSER_TRANSACTION_ID": $v("P3_BROWSER_TRANSACTION_ID"),
                "P3_SIGNATURE": buf2hex(sigVal)
            },
            showWait: false
        })
    }
);


Web Cryptography APIを使用し、秘密鍵をインポートして、ブラウザ側でブロックチェーン 表から取得したハッシュ値(P3_HASH)に署名を行います。生成した署名(P3_SIGNATURE)と主キーであるP3_BROWSER_TRANSACTION_IDを引数として、apex.page.submitを呼び出し、サーバーに送信します。

ページのプロセスによって、生成した署名をブロックチェーン表に登録します。ボタンCREATEを押したときに起動されるプロセスですが、すでに表BC_BROWSER_TRANSACTIONSへの行の挿入は完了しており、単に署名だけをプロセスで追加します。

フォームを作成した時に作成されるタイプがフォーム - 行の自動処理(DML)となっているプロセスがあります。名前はページ名に依存しますが、プロセス・フォームで始まり、ページ名が続きます。

こちらのプロセスの設定のターゲット・タイプをPL/SQL Codeに変更します。そして、挿入/更新/削除するPL/SQLコードとして、以下を指定します。
begin
  bc_sign_row(:P3_BROWSER_TRANSACTION_ID, :P3_SIGNATURE);
end;
失われた更新の防止、および、行のロックは両方とも不要なので、OFFおよびNoにします。サーバー側の条件として、ボタン押下時にCREATEを選びます。


フォームについてはこれで完成です。最後にレポート上で署名の有無を確認するため、列ORABCTAB_SIGNATURE_CERT$を検索される列に追加します。レポートのページをページ・デザイナで開きます。

ソースのタイプをSQL問合せに変更し、以下のSQLを設定します。
select BROWSER_TRANSACTION_ID,
       TRANSACTION_NAME,
       TRANSACTION_VOLUME,
       REGISTER_DATE,
       ORABCTAB_SIGNATURE_CERT$
from BC_BROWSER_TRANSACTIONS

以上で今回確認したかった、Web Cryptography APIを使用したブロックチェーン表への電子署名の生成については完了です。

このまま実用できる、というものではありませんが、ブラウザ側で電子署名をつけることができる、というのは面白いと思います。Oracle APEXによるアプリケーション作成の一助になれば幸いです。

謝辞

JavaScriptのコードについては、ほとんどWebから見つけたものを使っています。参考にさせていただいたサイトについては、コードの内のコメントとしてURLを記載しています。本当にありがとうございました。

完

CSVに含まれる任意の文字列のパース

 CSVに含まれる一行の中に、説明書きとして以下のようなのがあります。

1.20 from tokyo to london

これをパースしてデータベースに保存したいんだけど、と同僚より相談を受けました。JavaScriptだとできるんだけど、PL/SQLは書いたことがない、とのことでした。

それだと、何から始めて良いのかわからないので、ざくっとOracle APEXでサンプルを作って、こうやるのだよ、と伝えました。その作業記録です。

まず、スキーマをクイックSQLで定義しました。

# prefix: szx
# semantics: default
raw_data
    body file

csv_data
    raw_data_id /fk raw_data
    category vc4000
    description vc4000

data_transfers
    csv_data_id /fk csv_data 
    fare num
    from_place vc255
    to_place vc255

regexps
    category vc80
    regexp_str vc4000
    insert_str vc4000
    var_num num
    seq num

CSVファイルはSZX_ROW_DATA表のBLOBにアップロードします。このクイックSQLの定義から、特別な指定をせずにスキーマの生成からアプリケーションの作成までを行います。

CSVファイルをアップロードする際に、ファイル名やファイルのタイプなどもデータベースにきちんと登録させるために、フォームに含まれるBLOBのページ・アイテムに、MIMETYPE列、ファイル名列、文字セット列、BLOB最終更新列を設定します。

CSVファイルをアップロードしたのちに、カンマで分割する処理をアップロードしたファイルの一覧ページに追加します。

PL/SQLコードでは、APEX_DATA_PARSER.PARSEを使用しています。

begin
  -- id number should be changed or supplied as a parameter
  for c in (select id, body from szx_raw_data) 
  loop
    delete from szx_csv_data where raw_data_id = c.id;
    insert into szx_csv_data(raw_data_id, category, description)
    select c.id, col001, col002 from 
      apex_data_parser.parse(
        p_content => c.body,
        p_file_name => 'data.csv'
      );
  end loop;
end;

次にDESCRIPTIONの列をパースする処理を、CSVファイルのパース結果の一覧画面に追加します。

PL/SQLコードでは、regexp_replaceを使用しています。

declare
  l_matched varchar2(4000);
  l_cursor integer;
  l_rows_processed integer;
  l_idx varchar2(4);
  l_vals apex_t_varchar2;
  l_idxstr varchar2(16);
begin
  for c1 in (select * from szx_csv_data)
  loop
    -- dbms_output.put_line(c1.category || ' = ' || c1.description);
    for c2 in (select * from szx_regexps where category = c1.category order by seq)
    loop
        l_idxstr := '';
        for i in 1..c2.var_num
        loop
             l_idxstr := l_idxstr || '\' || i || ':';
        end loop;
        -- dbms_output.put_line(c2.regexp_str || ' = ' || c2.insert_str || ' = ' || l_idxstr);
        l_matched := regexp_replace(c1.description, c2.regexp_str, l_idxstr);
        if l_matched <> c1.description then
          -- dbms_output.put_line('MATCHED!!!');
          l_cursor := dbms_sql.open_cursor;
          dbms_sql.parse(l_cursor, c2.insert_str, dbms_sql.native);
          dbms_sql.bind_variable(l_cursor, ':1', c1.id);
          l_vals := apex_string.split(l_matched, ':');
          for i in 2..c2.var_num
          loop
            l_idx := ':' || i;
            dbms_sql.bind_variable(l_cursor, l_idx, l_vals(i-1));
          end loop;
          l_rows_processed := dbms_sql.execute(l_cursor);
          dbms_sql.close_cursor(l_cursor);
        end if;
    end loop;
  end loop;
  commit;
end;

正規表現と、それが一致した時に実行するINSERT文を表に登録します。

登録する正規表現その1

カテゴリ: fare

正規表現: ([0-9\.]+)\s+from\s+([A-Za-z ]+)\s+to\s+([A-Za-z ]+)

INSERT文: insert into szx_data_transfers(csv_data_id, fare, from_place, to_place) values(:1, :2, :3, :4)

バインド変数の数: 4

登録する正規表現その2

カテゴリ: fare

正規表現: ([0-9\.]+)\s+to\s+([A-Za-z ]+)\s+from\s+([A-Za-z ]+)

INSERT文: insert into szx_data_transfers(csv_data_id, fare, from_place, to_place) values(:1, :2, :4, :3)

バインド変数の数: 4

以上です。テストには以下のCSVファイルをしました。

fare, 1.200     from tokyo  to  london

fare, 3200.00   from beijing to tokyo

fare, 4500.00   to london from new york

fare, 900.00    to new york from beijing

アプリケーションのエクスポートを、こちらに置いています。

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

完

2021年1月31日日曜日

最初に開くページの変更

Oracle APEXにベースとなるパスの指定のみでアクセスすると、アプリケーション・ビルダーのサインイン画面が呼び出されます。例えば、以下のようなURLです。

https://localhost:8080/ords/xepdb1/

一般のアプリケーションの利用者にはあまり見せたくない画面がデフォルトというのも...と、何か対処がないものか、と色々試してみました。

最終的にこれが一番簡単だろうと思った手順です。

そもそも、Oracle APEXにベース・パス以下無指定でアクセスしたときは、apexというプロシージャが呼び出されるようになっています。これはデータベースにはパブリック・シノニムとして作成されています。実体はOracle APEXのスキーマ(APEX_200200といったバージョン番号の付いたスキーマ)に存在するプロシージャapexです。このプロシージャの中で、ベース・パス以下が無指定の場合は、アプリケーション・ビルダーが呼び出されています。

ですので、アプリケーション・ビルダーを呼び出さないようするプロシージャを作成し、それをパブリック・シノニムの実体に変更すれば対応できそうです。

試してみました。

CREATE OR REPLACE EDITIONABLE PROCEDURE "ADMIN"."MYAPEX" (p_session in number default null)
as
begin
    SYS.OWA_UTIL.REDIRECT_URL('https://apex.oracle.com/ja/');
end;
/

GRANT EXECUTE ON "ADMIN"."MYAPEX" TO "ORDS_PUBLIC_USER";

CREATE OR REPLACE PUBLIC SYNONYM APEX FOR ADMIN.MYAPEX;

プロシージャMYAPEXをスキーマADMINに作成し、パブリック・シノニムを置き換えています。GRANT文はORDS_PUBLIC_USERのみですが、環境によってはAPEX_PUBLIC_USERへも実行権限を与える必要があるでしょう。

デフォルトで開く画面が、https://apex.oracle.com/ja/になることは、確認できました。

アプリケーション・ビルダーは、アプリケーション番号を指定することでアクセスできます。

https://localhost:8080/ords/xepdb1/f?p=4550

パブリック・シノニムの変更はサポートされる手法ではないですが、問題が起きるとしたら置き換えたプロシージャが原因(つまり自分で記述したコード)でしょうし、容易に元に戻せるので、見つけた方法の中では一番利用可能だと思いました。

完