2022年12月6日火曜日

簡単なファイル管理アプリケーションの作成(3) - Oracle Textによる全文検索索引

 表SFM_CONTENTSのBLOB列CONTENTに、Oracle Textによる全文検索索引を作成します。Oracle APEXでOracle Textを利用する場合、ユーザー・データストアを構成するのが一般的です。

以下にユーザー・データストアを使った全文検索索引の実装手順を紹介します。


CTX_DDLの実行権限


作成済みのAutonomous Databaseのデータベース・アクションに、管理者ユーザーADMINで接続します。

SQLの実行画面を開き、Oracle APEXのワークスペース・スキーマにCTX_DDLパッケージの実行権限を与えます。

grant execute on ctx_ddl to <APEXワークスペース・スキーマ>;

Oracle APEX 22.1以降でAutonomous Database上にワークスペースを作成している場合は、ワークスペース・スキーマ名はワークスペース名にWKSP_が接頭辞として追加されます。

ワークスペース名がAPEXDEVであれば、以下のようになります。

grant execute on ctx_ddl to wksp_apexdev;


APEXのワークスペースからCTX_DDLパッケージの実行が可能になりました。


Oracle Text全文検索索引の作成



日本語なので、レクサーとしてJAPANESE_LEXERを使うためのプリファレンスをja_lexerとして作成します。
begin
  ctx_ddl.create_preference('ja_lexer', 'JAPANESE_LEXER');
end;
/
BLOBとして保存されたデータより、文字データを抽出するフィルタを設定します。

以下のSQLを実行し、auto_policyというポリシーを作成します。このポリシーを指定することにより、BLOBにAUTO_FILTER(AUTO_FILTERについての説明はこちら)を適用します。
begin
  ctx_ddl.create_preference('auto_filter', 'AUTO_FILTER');
  ctx_ddl.create_policy('auto_policy', 'auto_filter');
end
/
ユーザー・データストアに登録するプロシージャsfm_contents_docを作成します。

列TITLEとABSTRACT、それと列CONTENTからAUTO_FILTERを適用して抽出された文字データを連結してCLOB(文字データ)として返します。AUTO FILTERの適用に失敗した場合（CTX_DOC.POLICY_FILTERで例外が発生した場合）はどうしようもないので、無視して検索対象のデータから外しています。
create or replace procedure sfm_contents_doc(
    rid in rowid,
    tlob in out nocopy clob
)
is
    l_clob clob;
begin
    for c in (select title, abstract, content 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;
        if dbms_lob.getlength(c.content) > 0 then
            begin
                dbms_lob.writeappend(tlob, 1, ' ');
                ctx_doc.policy_filter('auto_policy', c.content, l_clob, true);
                dbms_lob.copy(tlob, l_clob, length(l_clob), length(tlob)+1, 1);
            exception
            when others then
                null;
                -- record_sfm_filter_exception(rid, sqlcode, sqlerrm);
            end;
        end if;
    end loop;
end sfm_contents_doc;
ユーザー・データストアsfm_contents_docを作成します。

先ほど作成したプロシージャsfm_contents_docにより、索引を作成する元になる文字データがCLOB形式で返されます。OWNERの部分は、作成したプロシージャsfm_contents_docの所有者であるAPEXのワークスペース・スキーマ名になります。以下の例ではWKSP_APEXDEVを指定しています。
begin
    ctx_ddl.create_preference('sfm_contents_doc', 'user_datastore'); 
    ctx_ddl.set_attribute('sfm_contents_doc', 'procedure', 'WKSP_APEXDEV.sfm_contents_doc'); 
    ctx_ddl.set_attribute('sfm_contents_doc', 'output_type', 'CLOB');
end;
作成したユーザー・データストアを使って全文検索索引SFM_CONTENTS_SIDXを作成します。 
CREATE INDEX "SFM_CONTENTS_SIDX" ON "SFM_CONTENTS" ("TITLE") 
INDEXTYPE IS "CTXSYS"."CONTEXT"  PARAMETERS ('filter ctxsys.null_filter lexer ja_lexer datastore sfm_contents_doc sync(on commit)');
以上で全文検索索引が作成されました。

上記のスクリプトをまとめます。


SQLワークショップのSQLスクリプトを使って実行できます。


エラーが発生した場合などに、作成済みのポリシーやプリファレンスの削除を行なうスクリプトです。


ユーザー・データストアのソースとなる文字列の確認に使用できるスクリプトです。

SQLワークショップのSQLコマンドより実行します。


検索対象の文字列がファイルに含まれているにもかかわらず、ヒットしないときはAUTO_FILTERの処理に失敗している場合があります。


レポートのOracle Text検索列の設定



対話モード・レポートを含むページをページ・デザイナで開き、対話モード・レポートのリージョンの属性を選択し、詳細のカテゴリに含まれるOracle Text索引列を、全文検索索引SFM_CONTENTS_SIDXを作成した列TITLEにします。


Oracle Text索引列としてTITLEを選択しています。しかし、ユーザー・データストアに使用しているプロシージャは、TITLE、ABSTRACT、CONTENTを合成した文字列を返しています。その上で全文検索索引を作成しているため、TITLE、ABSTRACT、CONTENTに含まれる文字列が全文検索の対象となります。

以上で簡単なファイル管理アプリケーションができました。

今回作成したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/simple-file-manager.zip

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

補足

AUTO_FILTERでの例外の捕捉には自律トランザクションが必要でしょう。以下のようなコードをnullの部分に挿入する必要がありそうです。文字列の抽出に失敗するデータを見つけられなかったので動作は検証できていません。

nullの部分に以下を記述します。

record_sfm_filter_exception(rid, sqlcode, sqlerrm);


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