APEX 22.2の検索コンポーネントを使ってコード検索アプリを作成する

 本ブログではGistにコード・スニペットを保存して、記事に貼り付けていることが多いのですが、コード・スニペットを対象とした検索ができなくて困っていました。

APEX 22.2では検索コンポーネントが新規に追加されています。これを使ってコード・スニペットを検索するアプリケーションを作ってみました。

https://apex.oracle.com/pls/apex/r/japancommunity/codesearch/home

作成したアプリケーションを公開するため、今回の作業はapex.oracle.comで実施しています。

検索コンポーネントについては、Oracle APEXのOffice Hourで開発者のCarsten Czarskiさんが解説しています。

Part 2: Marquee Features (APEX 22.2)

https://asktom.oracle.com/pls/apex/asktom.search?oh=19065

YouTubeの動画では、22分頃から解説が始まります。

https://www.youtube.com/watch?v=EEYLgNxx3Wo&t=1320s

今回のアプリケーションですが、クイックSQLの以下のモデルを使って表を作成します。

#prefix: cds
documents
    title vc4000 /nn 
    url vc400
    content clob 
    published date
    updated date

attachments
    post_id /fk documents
    embedded_url vc400
    embedded_content clob 
    raw_url vc400
    raw_content clob 
    embedded_updated date
    raw_updated date

ブログ記事の本文を保存する表CDS_DOCUMENTSに対して、複数のコード（添付ファイル）が表CDS_ATTACHMENTSとして紐づくという構造になっています。一般的によくある構造なので、応用はしやすいと思います。

以下より、アプリケーションを作成する手順を紹介します。

最初に上記のクイックSQLのモデルより、表CDS_DOCUMENTSとCDS_ATTACHMENTSを作成します。

SQLワークショップのユーティリティのクイックSQLを開き、左ペインにモデルを貼り付けます。その後SQLの生成、SQLスクリプトを保存、レビューおよび実行を順次クリックします。

DDLが生成され、レビュー画面が開きます。今回使用する元データに合わせるため、表CDS_DOCUMENTSの主キーの定義を変更します。

表CDS_DOCUMENTSの列IDの型定義は以下に変更します。主キー制約の定義は変更しません。

varchar2(32) default on null rawtohex(sys_guid())

参照制約が定義されているCDS_ATTACHMENTSの列POST_IDの型はvarchar2(32)に変更します。

以上の変更を行い、スクリプトを実行します。

２つの表と参照制約に関係する索引が１つ作成されます。

ここからはアプリケーションの作成は行いません。

アプリケーション作成ウィザードを、アプリケーション・ビルダーから起動します。

アプリケーションの名前はコード検索とします。テストに使用するデータを投入するための画面を、あらかじめアプリケーションに作成します。本題の検索画面は、アプリケーションを作成したのちに作成します。

ページの追加をクリックします。

ページの追加の追加ページを開き、複数のレポートを選択します。

表の選択画面が開きます。表CDS_DOCUMENTS、CDS_ATTACHMENTSを探してチェックを入れます。

ページの追加をクリックします。

それぞれの表について、フォーム付きの対話モード・レポートが追加されます。編集をクリックし、管理ページに切り替えます。

詳細を開き、管理ページとして設定にチェックを入れます。

管理ページに変更すると、編集ボタンの横にあるアイコンがスパナに変わります。

AttachmentsとDocumentsの双方を管理ページに変更し、アプリケーションの作成を実行します。

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

アプリケーションを実行し管理画面を開くと、表CDS_DOCUMENTSとCDS_ATTACHMENTSの編集メニューが含まれていることが確認できます。テストに使うデータはこちらから登録できます。

apex.oracle.com上のアプリケーションではこの画面を使わずに、ブログ記事本文を表CDS_DOCUMENTSの列CONTENTに、Gistのスニペットを表CDS_ATTACHMENTSのRAW_CONTENTに投入しています。

表CDS_DOCUMENTSの列CONTENTおよびCDS_ATTACHMENTSの列RAW_CONTENTにOracle Textの全文検索索引を作成します。列CONTENTはレクサーとしてJAPANESE_LEXER、列RAW_CONTENTはコードなのでBASIC_LEXERを使用します。

あらかじめお断りしておきますが、最終的にOracle Text検索は使用しません。コード中のプロシージャ名やファンクション名の検索に向いていないためです。Oracle Textについては、一つの実装例としての紹介になります。

それぞれのレクサーのプレファレンスを、ja_lexerおよびbasic_lexerとして作成します。

begin
  ctx_ddl.create_preference('ja_lexer', 'JAPANESE_LEXER');
  ctx_ddl.create_preference('basic_lexer', 'BASIC_LEXER');
end;
/

表CDS_DOCUMENTSの列CONTENTに、Oracle Text索引CDS_DOCUMENTS_CTX1を作成します。

create index cds_documents_ctx1 on cds_documents(content) 
indextype is ctxsys.context parameters('filter ctxsys.null_filter lexer ja_lexer sync(on commit)');

同様に表CDS_ATTACHMENTSの列RAW_CONTENTに、索引CDS_ATTACHMENTS_CTX1を作成します。

create index cds_attachments_ctx1 on cds_attachments(raw_content)
indextype is ctxsys.context parameters('filter ctxsys.null_filter lexer basic_lexer sync(on commit)');

以上で検索画面を作成する準備ができました。

共有コンポーネントの構成の検索を開きます。（英語だとSearch Configurationsで、これ以外は検索構成と訳されています。）

作成済みの検索構成が一覧されます。作成をクリックします。

検索構成の名前は本文検索 - Oracle Textとします。検索タイプとしてOracle Textを選択します。

次へ進みます。

ソースの表/ビューの名前として、CDS_DOCUMENTSを選択します。

次へ進みます。

主キー列はID(Varchar2)、Oracle Text索引列はCONTEXT(Clob)、タイトル列はTITLE(Varchar2)とします。アイコン・ソースとしてアイコン・クラスを選択し、アイコンCSSクラスとしてfa-file-text-oを指定します。

検索構成の作成をクリックします。

検索構成本文検索 - Oracle Textが作成されます。

設定の検索問合せ接頭辞にcontent-text、静的IDにcontent-textと設定します。列のマッピングのカスタム列1にURL(Varchar2)を選択します。

以上で変更の適用をクリックします。

同様の手順で、表CDS_ATTACHMENTSの検索構成を作成します。

名前はコード検索 - Oracle Textとします。検索タイプにOracle Textを選択します。

親となる表CDS_DOCUMENTSの列を検索結果に含めるため、ソースのソース・タイプとしてSQL問合せを選択し、以下のSQLをSQL SELECT文を入力に記述します。

select a.id, d.title, d.url, a.raw_url, a.raw_content
from cds_documents d join cds_attachments a 
  on d.id = a.post_id

主キー列はID(Number)、Oracle Text索引列はRAW_CONTENT(Clob)、タイトル列はTITLE(Varchar2)とします。アイコン・ソースとしてアイコン・クラスを選択し、アイコンCSSクラスとしてfa-file-text-oを指定します。

検索構成コード検索 - Oracle Textが作成されます。

設定の検索問合せ接頭辞にcode-text、静的IDにcode-textと設定します。列のマッピングのカスタム列1にURL(Varchar2)、カスタム列2にRAW_URL(Varchar2)を選択します。

以上で変更の適用をクリックします。

以上で検索ページに使用する検索構成が作成できました。

検索ページを作成します。このページをホームとするため、作成済みのホーム・ページを削除します。

ページ・デザイナでホーム・ページを開き、ページの削除を実行します。

カスケード削除ははいを選択し、ページを完全に削除を実行します。

ページが削除されたら、代わりになる検索ページを作成します。ページの作成を実行します。

検索ページを選択し、次へ進みます。

ページ番号は１、名前はコード検索とします。ページ・モードは標準です。

構成の検索としてコード検索 - Oracle Text、本文検索 - Oracle Textの双方をチェックします。

以上でページの作成をクリックします。

検索ページが作成されます。

今回はすべて公開されているデータを元にしているため、作成した検索ページの保護をすべて解除します。

識別の別名をhomeに変更します。

セキュリティの認証はパブリック・ページ、ディープ・リンクは有効、ページ・アクセス保護は制限なしとします。セッション管理のセッションを再結合はパブリック・セッションに対して有効に変更します。

変更を保存します。

アプリケーションを実行し、検索ページの動作を確認します。

検索ワードとしてapex_data_exportを入力します。

一件もヒットしません。

検索ワードをapex data exportとすると、複数の検索結果が得られます。

Oracle Textでは単語は_（アンダースコア）で分割されています。そのため、apex_data_exportでは検索にヒットしません。プロシージャ名を検索ワードにしたいので、検索構成を変更します。

検索構成を変更するまえに、２つページの設定を変更します。

リージョン検索結果を選択し、プロパティ・エディタの属性を開きます。

デフォルトでは文字入力の都度、検索が実行されるようになっています。データベースへの負荷を減らすため、設定の入力時に検索をOFFに変更します。

続いて、検索結果として表示されるURLをクリックできるようにします。

設定のカスタム・レイアウトをONにし、結果行テンプレートに以下を書き込みます。

カスタム列1の置換文字列は&CUSTOM_01.、カスタム列2は&CUSTOM_02.です。これらのカスタム列が現れる部分をA要素に変更しています。

結果行テンプレートの記述方法は、結果行テンプレートのオンライン・ヘルプに記載されています。

以上の変更で、検索結果として表示されるURLがクリック可能になります。テンプレート・ディレクティブの使用もできるため、検索結果の見栄えの自由度は高いでしょう。

Oracle Textの検索はコード検索の要件に合わなかったため、検索構成を新規に作成します。

新たに作成する検索構成の名前は本文検索 - 標準とします。検索タイプは標準を選択します。これ以降の指定は、検索タイプがOracle Textのときと同じです。

作成された検索構成本文検索 - 標準の設定の検索問合せ接頭辞、静的IDはcontentとします。ソースの検索可能列としてCONTENT(Clob)を選択します。この列に検索キーワードによるLIKE検索が行われます。

列のマッピングのカスタム列１としてURL(Varchar2)を選択します。

同様に検索構成コード検索 - 標準を作成します。

作成された検索構成コード検索 - 標準の設定の検索問合せ接頭辞、静的IDはcodeとします。ソースの検索可能列としてRAW_CONTENT(Clob)を選択します。

列のマッピングのカスタム列１としてURL(Varchar2)を選択します。カスタム列２としてRAW_URL(Varchar2)を選択します。

以上で、置き換える検索構成が作成されました。

検索ページを開き、ソースの検索のコード検索 - Oracle Textを選択します。

識別の名前をコード検索、検索構成をコード検索 - 標準に変更します。

同様に本文検索 - Oracle Textの識別の名前を本文検索、検索構成を本文検索 - 標準に変更します。

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

アプリケーションを実行して、apex_data_exportで検索してみます。

記事およびGistのスニペットの内容にapex_data_export（大文字小文字は無視されます）が含まれる記事が一覧されます。

検索構成に検索問合せ接頭辞が設定されています。本文検索 - 標準にはcontentが指定されているため、検索キーワードとしてcontent:apex_data_exportと入力するとブログ記事本文のみが検索対象になります。

この他にもいろいろな機能が、最初に紹介したYouTubeの動画で紹介されています。

今回作成したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/codesearch.zip

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

完

簡単なファイル管理アプリケーションの作成(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);

完

APEX 22.1で追加された行検索のトークン化の効果

 Oracle APEX 22.1よりアプリケーション定義のプロパティに、行検索のトークン化が追加されています。

オンライン・ヘルプには、以下のように説明されています。

コンポーネントで行検索が行われた場合に、検索語がどのように適用されるかを指定します。

「オン」を選択すると、検索語のそれぞれの単語が別々に処理されます。全体であれ個別であれ、列に検索語が含まれていればレコードが一致します。フレーズ全体を検索するには、検索語を二重引用符(")で囲み、"ui developer"のようにします。検索語の中の二重引用符をエスケープするには、二重引用符("")を使用します。
例1- redおよびshoesという語を含む任意のレコード検索: red shoes2- developerという語とui designerという表現全体を含む任意のレコード検索: developer "ui designer"3- authorという語と、the sky is "blue" and niceという表現全体を含む任意のレコード検索語: author "the sky is ""blue"" and nice"

「オフ」を選択すると、検索語全体が完全一致として扱われます。

Oracle APEX 22.1で新規に作成したアプリケーションでは、デフォルトでONになっています。

こちらの記事で作成した簡易ファイル管理アプリケーションを使って、行検索のトークン化の効果を確認します。

今回の動作確認では、対話モード・レポートを使用します。これ以外にスマート・フィルタ、ファセット検索、対話グリッド、ポップアップLOVなど、行検索がサポートされているすべてのコンポーネントで機能します。

テストに使用するデータとして、Oracle APEX情報サイトよりPDFで提供されているファイルをいくつかアップロードしています。

Oracle Textによる全文検索との関係

レポートにOracle Text索引列が設定されている場合は、Oracle Textによる検索が優先されます。この場合、行検索のトークン化の設定は意味を持ちません。

対話モード・レポートでは、属性の詳細のOracle Text索引列に、Oracle Text索引が作成されている列を指定します。今回のサンプル・アプリケーションでは、表SIMPLE_CONTENTSの列TITLEを指定します。

Oracle and Daysを検索語として行検索を行います。OracleとDaysを含む文書が３件検索されました。

列Abstractを非表示にしても、検索結果は変わりません。これは全文検索索引を作成する元データとして、列TITLE、ABSTRACT、そしてBLOBから自動フィルタを適用して取り出した文字列を使用しているためです。逆に列TITLE、ABSTRACT、アップロードしたファイルの内容以外はレポートに表示されていても、検索対象にはなりません。

デバッグ・ログより実際に実行されているSELECT文を確認してみます。

もっとも内側で実行されているSELECT文の、WHERE句として与えられている条件は以下になります。

contains("TITLE",:apex$f1,1)>0

バインド変数の:apex$f1に、検索語のOracle and Daysが渡されています。

行検索のトークン化がOFFの場合

Oracle Daysを検索語として行検索を行います。結果は０件になります。行検索のトークン化がOFFの場合、検索語Oracle Daysと完全一致する単語を含む行のみが検索結果となります。

同様にデバッグ・ログよりWHERE句の条件を確認します。

(

    instr( upper("TITLE"),upper(:apex$f1)) > 0 

    or 

    instr( upper("ABSTRACT"),upper(:apex$f1)) > 0

)

バインド変数の:apex$f1に、検索語のOracle Daysが渡されています。

この条件であれば、検索結果は０件になります。

行検索のトークン化がONの場合

APEX 22.1で追加されたプロパティ行検索のトークン化をONにしたときの結果です。OFFの場合と同様に検索語としてOracle Daysを与えて行検索を行います。

行検索のトークン化がONの場合、検索結果が３件になりました。

デバッグ・ログよりWHERE句の条件を確認します。

(

    instr( upper("TITLE"),upper(:apex$f1)) > 0

    or

    instr( upper("ABSTRACT"),upper(:apex$f1)) > 0

)

and

(

    instr( upper("TITLE"),upper(:apex$f2)) > 0 

    or

    instr( upper("ABSTRACT"),upper(:apex$f2)) > 0

)

行検索に与えられた検索語Oracle Daysは空白で区切られ、Oracleは:apex$f1、Daysは:apex$f2に割り当てられます。結果としてOracleとDaysが含まれている表示列があれば、検索結果に含まれます。

今回は列ABSTRACTにOracleとDaysの両方が含まれているデータが３件ありました。

Oracle Text関数

本題から外れますが、近いトピックなのでOracle Text関数も使ってみます。

ファンクションconvert_end_user_searchを作成します。

create or replace function convert_end_user_search (
    p_search in varchar2 )
    return varchar2 
is
begin
    return 'FUZZY({' || replace( p_search, '}', '\}' ) || '}, 30, 2000)';
end;

これはOracle Text関数のオンライン・ヘルプに記載のある、Oracle Text CONTAINS問合わせ演算子のFUZZY関数を使った例です。

アプリケーション定義のプロパティのOracle Text関数として、convert_end_user_searchを設定します。

対話モード・レポートの属性のOracle Text索引列にTITLEを指定し、Oracle Textによる全文検索を有効にします。

検索語としてOlacle Daysを与えて、行検索を行ってみます。Oracleではなくスペルが間違っているOlacleです。

FUZZY検索が行われるため、スペルが間違っていても、検索結果として３件返されます。

これも条件句を確認してみます。

contains("TITLE",convert_end_user_search(:apex$f1),1)>0

convert_end_user_searchが返す文字列は、:apex$f1がOlacle Daysであれば以下になります。

FUZZY({Olacle Days}, 30, 2000)

結果として、上記の条件句は以下になります。

contains("TITLE",'FUZZY({Olacle Days}, 30, 2000)',1)>0

この他にも多数の演算子があります。しかし、Oracle Textの日本語対応は充実しているとは言い難いため、ほとんど期待できないです。

Oracle APEX 22.1で追加された、行検索のトークン化についての説明は以上になります。

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

完