日日是Oracle APEX: 簡単なファイル管理アプリケーションの作成(4) - ファイルをオブジェクト・ストレージに保存する

2022年12月7日水曜日

簡単なファイル管理アプリケーションの作成(4) - ファイルをオブジェクト・ストレージに保存する

Always FreeのAutonomous Databaseのストレージは20GBなので、ちょっとファイルをアップロードすると容量が足りなくなります。ファイルを保存するために、高速なストレージを消費するのも勿体無い話ですし、それだけのために有料インスタンスにアップグレードするのも勿体無い気がします。

とういうことで、ファイルの実体をオブジェクト・ストレージに保存するようにAPEXアプリケーションを改変してみます。

アップロードされたファイルは、オブジェクト・ストレージに以下のように保存されます。

準備

今までの記事に従って、ファイル管理アプリケーションが作成済みであることを前提としています。今回はオブジェクト・ストレージを使うため、以下の記事の記載に従って準備します。

ユーザー、グループ、ポリシーなどの作成を行います。

APEXからOCIオブジェクト・ストレージを操作する(1) - APIユーザーの作成

バケットはapex_file_storageを使います。

APEXからOCIオブジェクト・ストレージを操作する(2) - バケットの作成

PL/SQL SDKを使用するため、以下の記事にあるクリデンシャルの作成とワークスペース・スキーマへの権限の付与も実施します。

APEXからオブジェクト・ストレージをPL/SQL SDKで操作する(1) - 準備

作成されるクリデンシャルはMY_OCI_CREDになります。

表SFM_CONTENTSの変更

オブジェクト・ストレージにファイルを保存すると、全文検索索引の再作成ができなくなります。そのため、索引作成のソースとなる文字列を表SFM_CONTENTSに保存しておきます。

全文検索索引のソースを保存する列TEXT_INDEX_SOURCEを表SFM_CONTENTSに追加します。

alter table sfm_contents add (text_index_source blob);

また、変更回数を記録する列VERSIONも追加します。

alter table sfm_contents add (version number default 0 not null);

クイックSQLのモデルは以下のように変わります。

# prefix: sfm
# pk: guid
contents
    title vc80 /nn
    abstract vc800
    content file
    text_index_source blob
    version num /default 0 /nn

ファイルから全文検索索引のソースとなる文字列を抽出するプロシージャSFM_CONTENTS_DOCは以下に変更します。

	create or replace procedure sfm_contents_doc(
	rid in rowid,
	tlob in out nocopy clob
	)
	is
	l_blob blob;
	l_clob clob;
	begin
	for c in (select title, abstract, text_index_source from sfm_contents where rowid = rid)
	loop
	if c.title is not null then
	dbms_lob.writeappend(tlob, length(c.title), c.title);
	end if;
	if c.abstract is not null then
	dbms_lob.writeappend(tlob, 1, ' ');
	dbms_lob.writeappend(tlob, length(c.abstract), c.abstract);
	end if;
	/*
	* ファイルのアップロード時に事前にAUTO_FILTERで文字列が抽出され、
	* 列text_index_sourceが更新されていることが前提。
	*/
	if dbms_lob.getlength(c.text_index_source) > 0 then
	dbms_lob.writeappend(tlob, 1, ' ');
	-- 表自体に圧縮が設定されていればCLOBのままでも良いかもしれない。
	l_blob := utl_compress.lz_uncompress(c.text_index_source);
	l_clob := apex_util.blob_to_clob(
	p_blob => l_blob
	,p_charset => 'AL32UTF8'
	);
	dbms_lob.copy(tlob, l_clob, length(l_clob), length(tlob)+1, 1);
	end if;
	end loop;
	end sfm_contents_doc;

view raw sfm_contents_doc.sql hosted with ❤ by GitHub

ファイルがデータベースにアップロードされたときに、ファイルから文字列を抽出し列TEXT_INDEX_SOURCEに保存していることが前提です。索引を作成する際にファイルのデータを必要としないため、ファイルがオブジェクト・ストレージにあっても全文検索索引を作ることができます。

全文検索索引のソースは文字データなので圧縮することで容量を節約していますが、表自体が圧縮されている場合は不要かもしれません。そうすると列TEXT_INDEX_SOURCEはCLOBで定義できますが、今回は手が込んでいる方がサンプルとして使い勝手が良いだろうと判断し、圧縮して保存するようにしています。

表SFM_CONTENTSに列が追加されたため、ページ番号３のフォームSfm Contentでページ・アイテムの同期化を実行すると、ページ・アイテムP3_TEXT_INDEX_SOURCEとP3_VERSIONが追加されます。このページ・アイテムにユーザーがデータを入力することは無いため、ビルド・オプションでコメント・アウトしておきます。

オブジェクト・ストレージのアクセス情報の設定

ファイルを保存するオブジェクト・ストレージに関する情報を、アプリケーション定義の置換文字列として設定します。

G_REGION - リージョン（今回の例ではus-ashburn-1）

G_NAMESPACE - オブジェクト・ストレージのネームスペース

G_BUCKET - バケット名（今回の例ではapex_file_storage）

G_CREDENTIAL - クリデンシャル（今回の例ではMY_OCI_CRED）

置換文字列の設定では前後の空白はトリムされないため、不要な空白が入っていないか注意が必要です。

プロシージャとパッケージの作成

列TEXT_INDEX_SOURCEにデータを保存するプロシージャretrieve_text_index_sourceを作成します。このデータが全文検索索引の作成時に使用されます。

	create or replace procedure retrieve_text_index_source(
	p_id in number
	)
	as
	l_index_source clob;
	l_clob clob;
	l_blob blob;
	begin
	dbms_lob.createTemporary(l_index_source, false, dbms_lob.CALL);
	for c in (select title, abstract, content from sfm_contents where id = p_id)
	loop
	if c.title is not null then
	dbms_lob.writeappend(l_index_source, length(c.title), c.title);
	end if;
	if c.abstract is not null then
	dbms_lob.writeappend(l_index_source, 1, ' ');
	dbms_lob.writeappend(l_index_source, length(c.abstract), c.abstract);
	end if;
	if dbms_lob.getlength(c.content) > 0 then
	begin
	dbms_lob.writeappend(l_index_source, 1, ' ');
	ctx_doc.policy_filter('auto_policy', c.content, l_clob, true);
	dbms_lob.copy(l_index_source, l_clob, length(l_clob), length(l_index_source)+1, 1);
	exception
	when others then
	null;
	end;
	end if;
	-- 文字列は圧縮して保存。
	l_blob := utl_compress.lz_compress(
	apex_util.clob_to_blob(
	l_index_source
	)
	);
	-- 表SFM_CONTENTSをアップデート。
	update sfm_contents set text_index_source = l_blob where id = p_id;
	end loop;
	end retrieve_text_index_source;

view raw retrieve_text_index_source.sql hosted with ❤ by GitHub

SQLワークショップのSQLコマンドに貼り付けて実行します。

次に、オブジェクト・ストレージにファイルをアップロードするプロシージャupload_fileとダウンロードするプロシージャdownload_file、および削除するプロシージャdelete_fileを実装したパッケージsfm_file_utilを作成します。

コードは以下になります。

	/**
	SFM_FILE_UTIL - オブジェクト・ストレージをバックエンド・ストアとして使用する。

	パッケージ仕様
	*/
	create or replace package sfm_file_util as
	/**
	IDで特定したSFM_CONETNTSの内容をオブジェクト・ストレージにアップロードする。
	*/
	procedure upload_file(
	p_id in number
	,p_region in varchar2
	,p_namespace in varchar2
	,p_bucket in varchar2
	,p_credential in varchar2
	);
	/**
	IDで特定したSFM_CONTENTSの内容としてオブジェクト・ストレージに保存されている
	ファイルをダウンロードする。
	*/
	procedure download_file(
	p_id in number
	,p_region in varchar2
	,p_namespace in varchar2
	,p_bucket in varchar2
	,p_credential in varchar2
	);
	/**
	IDに対応したオブジェクト・ストレージに保存されているファイルを削除する。
	*/
	procedure delete_file(
	p_id in number
	,p_region in varchar2
	,p_namespace in varchar2
	,p_bucket in varchar2
	,p_credential in varchar2
	);
	end;
	/

	/**
	パッケージ本体
	*/
	create or replace package body sfm_file_util as

	procedure upload_file(
	p_id in number
	,p_region in varchar2
	,p_namespace in varchar2
	,p_bucket in varchar2
	,p_credential in varchar2
	)
	as
	l_response dbms_cloud_oci_obs_object_storage_put_object_response_t;
	l_next_version number;
	l_object_name varchar2(32767);
	e_file_upload_exception exception;
	begin
	for c in (select content_filename, version, content, content_mimetype from sfm_contents where id = p_id)
	loop
	-- 次のバージョン番号を取得する
	l_next_version := nvl(c.version, 0) + 1;
	-- アップロードするファイル名を決める。
	l_object_name := p_id \|\| '/' \|\| l_next_version \|\| '/' \|\| utl_url.escape(c.content_filename, false, 'AL32UTF8');
	-- アップロードを実行する。
	l_response := dbms_cloud_oci_obs_object_storage.put_object(
	namespace_name => p_namespace
	,bucket_name => p_bucket
	,object_name => l_object_name
	,content_type => c.content_mimetype
	,put_object_body => c.content
	,region => p_region
	,credential_name => p_credential
	);
	if l_response.status_code <> 200 then
	raise e_file_upload_exception;
	end if;
	-- 表SFM_CONTENTSをアップデート。DBに保存されているBLOBのデータは消去する。
	update sfm_contents set version = l_next_version, content = null where id = p_id;
	end loop;
	end upload_file;

	procedure download_file(
	p_id in number
	,p_region in varchar2
	,p_namespace in varchar2
	,p_bucket in varchar2
	,p_credential in varchar2
	)
	as
	l_response dbms_cloud_oci_obs_object_storage_get_object_response_t;
	l_filename sfm_contents.content_filename%type;
	l_mime_type sfm_contents.content_mimetype%type;
	l_version sfm_contents.version%type;
	l_object_name varchar2(32767);
	l_download apex_data_export.t_export;
	e_file_download_exception exception;
	begin
	-- オブジェクト・ストレージ上の名前をl_object_nameとして取り出す。
	select version, content_filename, content_mimetype
	into l_version, l_filename, l_mime_type
	from sfm_contents
	where id = p_id;
	l_object_name := p_id \|\| '/' \|\| l_version \|\| '/' \|\| utl_url.escape(l_filename, false, 'AL32UTF8');
	-- オブジェクト・ストレージから対象ファイルを取り出す。
	l_response := dbms_cloud_oci_obs_object_storage.get_object(
	namespace_name => p_namespace
	,bucket_name => p_bucket
	,object_name => l_object_name
	,region => p_region
	,credential_name => p_credential
	);
	if l_response.status_code <> 200 then
	raise e_file_download_exception;
	end if;
	-- 取り出したファイルを、ブラウザにダウンロードする。
	l_download.file_name := l_filename;
	l_download.mime_type := l_mime_type;
	l_download.as_clob := false;
	l_download.content_blob := l_response.response_body;
	apex_data_export.download( p_export => l_download );
	apex_application.stop_apex_engine;
	end download_file;

	procedure delete_file(
	p_id in number
	,p_region in varchar2
	,p_namespace in varchar2
	,p_bucket in varchar2
	,p_credential in varchar2
	)
	as
	l_list_response dbms_cloud_oci_obs_object_storage_list_objects_response_t;
	l_response dbms_cloud_oci_obs_object_storage_delete_object_response_t;
	l_object_name varchar2(32767);
	e_file_delete_exception exception;
	begin
	/* ID以下のファイルの一覧を取得する。 */
	l_object_name := p_id \|\| '/';
	l_list_response := dbms_cloud_oci_obs_object_storage.list_objects(
	prefix => l_object_name
	,namespace_name => p_namespace
	,bucket_name => p_bucket
	,region => p_region
	,credential_name => p_credential
	);
	if l_list_response.status_code <> 200 then
	raise e_file_delete_exception;
	end if;
	/* フォルダに含まれるファイルをひとつひとつ削除する。 */
	for i in 1..l_list_response.response_body.objects.count
	loop
	l_object_name := l_list_response.response_body.objects(i).name;
	apex_debug.info(l_object_name);
	l_response := dbms_cloud_oci_obs_object_storage.delete_object(
	namespace_name => p_namespace
	,bucket_name => p_bucket
	,object_name => l_object_name
	,region => p_region
	,credential_name => p_credential
	);
	if l_response.status_code not in (200,204) then
	apex_debug.info('status_code = %s', l_response.status_code);
	raise e_file_delete_exception;
	end if;
	end loop;
	end delete_file;

	end sfm_file_util;
	/

view raw sfm_file_util.sql hosted with ❤ by GitHub

SQLワークショップのSQLスクリプトとして実行します。

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

パッケージとパッケージ本体が作成されたことを確認します。

アップロード処理の変更

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

プロシージャretrieve_text_index_sourceを呼び出すプロセスを作成します。

プロセスの作成を実行し、新規に作成されたプロセスをプロセス・フォームSfm Contentの下に配置します。

識別の名前を文字列の抽出とし、タイプにAPIの呼び出しを選択します。設定のタイプとしてPL/SQL Procedure or Function、所有者をParsing Schemaとし、プロシージャまたはファンクションとしてRETRIEVE_TEXT_INDEX_SOURCEを選択します。

サーバー側の条件のタイプとしてリクエストは値に含まれるを選択し、値にCREATE SAVEを入力します。ボタン作成および変更の適用を押したとき（ボタン削除では実行しない）に、プロセス文字列の抽出を実行します。