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)');
以上で全文検索索引が作成されました。

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

-- 日本語レクサーja_lexerを作成する。
begin
ctx_ddl.create_preference('ja_lexer', 'JAPANESE_LEXER');
end;
/
-- 自動フィルタ処理を作成する。
begin
ctx_ddl.create_preference('auto_filter', 'AUTO_FILTER');
ctx_ddl.create_policy('auto_policy', 'auto_filter');
end;
/
-- 全文検索索引のソースとなるユーザー・ストアのプロシージャを作成する。
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を作成する。
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;
/
-- 全文検索索引を作成する。
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)');
view raw createctxindex.sql hosted with ❤ by GitHub

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


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

begin
ctx_ddl.drop_preference('sfm_contents_doc');
ctx_ddl.drop_policy('auto_policy');
ctx_ddl.drop_preference('auto_filter');
ctx_ddl.drop_preference('ja_lexer');
end;
/
view raw dropctxprefs.sql hosted with ❤ by GitHub

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

declare
l_clob clob;
l_c number := 0;
begin
for c in (select id, rowid from sfm_contents order by id desc)
loop
l_c := l_c + 1;
if l_c > 100 then
exit;
end if;
dbms_lob.createtemporary(l_clob, TRUE);
sfm_contents_doc(c.rowid, l_clob);
dbms_output.put_line('ID: ' || c.id || ' ' || substr(l_clob, 1, 2000));
dbms_lob.freetemporary(l_clob);
end loop;
end;
view raw check_index_source.sql hosted with ❤ by GitHub
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);

-- 例外を記録する表
create table sfm_ctx_filter_exception_log(
rid rowid not null,
error_code number,
error_message varchar2(4000),
occur_date timestamp default systimestamp
);
-- 例外を記録するプロシージャ、要自律トランザクション
create or replace procedure record_sfm_filter_exception
(
p_rid in rowid
,p_error_code in number
,p_error_message in varchar2
)
is
pragma autonomous_transaction;
begin
insert into sfm_ctx_filter_exception_log(rid, error_code, error_message)
values(p_rid, p_error_code, p_error_message);
commit;
end record_sfm_filter_exception;
view raw ctx_filter_exception_log.sql hosted with ❤ by GitHub

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