はじめに
「添付した Excel の中身まで検索できますか」という質問をよく受けます。
答えは 「RDBMS と添付ファイルの保存先次第」 です。
そして条件を満たしていない環境が、実はかなり多いのではないかと思います。
今回はこの機能の仕組みを追いながら、どういう条件で動き、
どういう条件で動かないのかをコードで確認していきます。
バージョン 1.5.7.0 を対象にしています
SearchDocuments は条件式を1つ増やすだけのスイッチ
添付ファイルの中身を検索するかどうかは、Search.json の SearchDocuments で制御します。
既定は false です。
このパラメータ、コード全体で参照箇所が2つしかありません。
Indexes.cs の2つのメソッドだけです。
private static string FullTextWhere(
ISqlObjectFactory factory,
string name,
string itemsTableName = "Items",
bool negative = false)
{
var item = factory.SqlCommandText.CreateFullTextWhereItem(itemsTableName, name, negative);
var binary = factory.SqlCommandText.CreateFullTextWhereBinary(itemsTableName, name, negative);
return Parameters.Search.SearchDocuments
? $"({item} or {binary})"
: item;
}
やっていることはこれだけです。WHERE 句が item から (item or binary) になります。
binary 側は Binaries テーブルを ReferenceId で結んだ相関サブクエリになります。
プリザンター自身はテキスト抽出をしていない
ここが本質です。
第2回で見たとおり、Items.FullText に入る添付ファイルの情報は
ファイル名か GUID だけでした。ファイルの中身は入りません。
そして Office 文書や PDF から本文テキストを取り出すコードは、
プリザンター本体には存在しません。
つまり添付ファイルの中身の検索は、
バイナリ列に対する RDBMS 側の全文検索機能に丸投げされています。
だから RDBMS ごとの実力差がそのまま出ます。
既定で IFilter を使う「つもり」にはなっている
SQL Server にはバイナリ列を全文検索する仕組みがあります。
ドキュメントフィルタ（IFilter）を使ってバイナリからテキストを抽出するものです。
プリザンターがこれを前提に作られていることは、コードから読み取れます。
根拠1：索引は既定で作られる
CreateFullText.sql は Items だけでなく Binaries にも索引を張ります。
CREATE FULLTEXT INDEX ON [Binaries]
([Bin] TYPE COLUMN Extension Language 'Japanese')
KEY INDEX #PKBinaries#
ON ftx;
しかもこの DDL は、SearchDocuments の値とはまったく無関係に実行されます。
CodeDefiner を流すたびに、索引の有無を確認して無ければ作ります。
TYPE COLUMN Extension は「この行のバイナリをどのフィルタで読むかは
Extension 列を見て決めてください」という指定です。
わざわざ IFilter 連携を書いているのですから、設計上は使う前提だと分かります。
根拠2：Extension の値が IFilter の期待する形
Extension 列には何が入るのでしょうか。
Extension = Path.GetExtension(Name ?? FileName);
Path.GetExtension() は先頭のドット込みで ".pdf" を返します。
これは SQL Server の sys.fulltext_document_types.document_type の表記と一致します。
偶然ではなく、TYPE COLUMN に渡すための形です。
根拠3：それなのに使うスイッチは既定 OFF
SearchDocuments は false です。
つまり既定状態は「索引は作るが、検索には使わない」という位置にいます。
既定 OFF なのに索引だけ作る、の副作用
この組み合わせには、地味ですが無視できない影響があります。
SearchDocuments が false でも Binaries の全文索引は存在するので、
- 添付をアップロードするたびに、SQL Server 側で IFilter が呼ばれてテキスト抽出が走ります。
誰も使わない索引のためにコストだけ払っている状態です - 対応するフィルタがない拡張子や壊れたファイルがあると、
フルテキストクロールのエラーが SQL Server のログに溜まります
逆に言えば、SearchDocuments を true にした瞬間に検索できるようになるのは、
裏でずっと索引が育っていたからです。
RDBMS 別の実力差
CreateFullTextWhereBinary() の実装を3つ並べてみます。
SQL Server
public string CreateFullTextWhereBinary(
string itemsTableName,
string paramName,
bool negative)
{
return(negative
? $"(not exists(select * from \"Binaries\" where \"Binaries\".\"ReferenceId\"=\"{itemsTableName}\".\"ReferenceId\" and contains(\"Bin\", @{paramName}#CommandCount#)))"
: $"(exists(select * from \"Binaries\" where \"Binaries\".\"ReferenceId\"=\"{itemsTableName}\".\"ReferenceId\" and contains(\"Bin\", @{paramName}#CommandCount#)))");
}
Bin 列に対する contains() です。索引もあるので、これは実用的に動きます。
PostgreSQL
public string CreateFullTextWhereBinary(
string itemsTableName,
string paramName,
bool negative)
{
return (negative
? $"(not exists(select * from \"Binaries\" where \"Binaries\".\"ReferenceId\"=\"{itemsTableName}\".\"ReferenceId\" and (encode(\"Bin\", 'escape') %> @{paramName}#CommandCount#)))"
: $"(exists(select * from \"Binaries\" where \"Binaries\".\"ReferenceId\"=\"{itemsTableName}\".\"ReferenceId\" and (encode(\"Bin\", 'escape') %> @{paramName}#CommandCount#)))");
}
encode("Bin", 'escape') でバイナリを文字列化してからトライグラム比較しています。
これが何を意味するか考えてみましょう。
| ファイル形式 | 結果 |
|---|---|
.txt / .csv
|
バイト列がそのままテキストなので拾える可能性がある |
.docx / .xlsx / .pptx
|
ZIP 圧縮された XML なので原理的に拾えない |
.pdf |
テキストが圧縮ストリームに入るのでほぼ拾えない |
| 画像・動画 | そもそもテキストを持たない |
さらに CreateFullText.sql（PostgreSQL）を見ると、索引の対象は Items だけです。
create index if not exists "ftx" on "Items" using gin ("FullText" gin_trgm_ops);
Binaries には索引がないので、この条件は全件走査になります。
添付ファイルが増えるほど重くなります。
MySQL
public string CreateFullTextWhereBinary(
string itemsTableName,
string paramName,
bool negative)
{
return "0=1";
}
固定文字列 "0=1" を返します。常に偽なので、完全に未対応です。
SearchDocuments を true にしても、(item or 0=1) になるだけで何も変わりません。
比較表
| 生成される条件 |
Binaries の索引 |
実質 | |
|---|---|---|---|
| SQL Server | contains("Bin", @p) |
あり | 実用的に使える |
| PostgreSQL | encode("Bin", 'escape') %> @p |
なし | 動くが全件走査、形式も限定的 |
| MySQL | "0=1" |
— | 非対応 |
どの拡張子が読めるかは環境依存
SQL Server を使っていても、すべての形式が読めるわけではありません。
TYPE COLUMN で選ばれる IFilter が、その環境にインストールされている必要があります。
| 形式 | 拡張子 | 状況 |
|---|---|---|
| プレーンテキスト・HTML・XML |
.txt .htm .html .xml
|
標準で読める |
| Office 文書 |
.docx .xlsx .pptx .doc .xls .ppt
|
SQL Server のバージョンや環境により差がある |
.pdf |
別途 IFilter の導入が必須（Adobe や Foxit が提供） | |
| 画像・圧縮 |
.png .zip など |
テキストを持たないため不可 |
環境によって差が出るので、断定せずに自環境で確認するのが確実です。
select document_type, path
from sys.fulltext_document_types
order by document_type;
このクエリで .pdf が出てこなければ、PDF の中身は検索できません。
IFilter を後から導入した場合は、登録と再クロールが必要です。
exec sp_fulltext_service 'load_os_resources', 1;
exec sp_fulltext_service 'restart_all_fdhosts';
alter fulltext index on [Binaries] start full population;
最大の落とし穴：Local モードでは動かない
ここが今回いちばん重要なところです。
SQL Server を使い、IFilter も完璧に入れて、SearchDocuments も true にした。
それでもまったくヒットしないという状況があります。
原因は添付ファイルの保存先です。Attachment.SqlStatement() を見てください。
var provider = BinaryStorageProviderFactory.Create(providerName);
// ...
var bin = provider != null ? default : GetBin(context);
statements.Add(Rds.UpdateOrInsertBinaries(
param: Rds.BinariesParam()
.TenantId(context.TenantId)
.ReferenceId(referenceId, _using: referenceId != 0)
.ReferenceId(raw: Def.Sql.Identity, _using: referenceId == 0)
.Guid(Guid)
.Title(Name ?? FileName)
.BinaryType("Attachments")
.Bin(bin, _using: provider == null)
.Bin(raw: "NULL", _using: provider != null)
.FileName(Name ?? FileName)
.Extension(Extension)
.Size(Size)
.ContentType(ContentType),
where: Rds.BinariesWhere().Guid(Guid)));
注目すべきはこの2行です。
.Bin(bin, _using: provider == null)
.Bin(raw: "NULL", _using: provider != null)
provider が非 null、つまり外部保存のとき、
Bin 列に明示的に NULL を書きに行っています。
全文索引が張られているのは Binaries.Bin です。
その列が空なのですから、索引には何も入りません。
provider が非 null になる条件
保存先の決定ロジックを見てみましょう。
public static string BinaryStorageProvider(Column column = null)
{
if (Parameters.BinaryStorage.UseStorageSelect && column != null)
{
var selected = string.IsNullOrEmpty(column?.BinaryStorageProvider)
? Parameters.BinaryStorage.DefaultBinaryStorageProvider
: column?.BinaryStorageProvider;
// ...
return selected;
}
else
{
if (!Parameters.BinaryStorage.IsStoreExternal())
{
return ParameterAccessor.Parts.BinaryStorageProviderNames.DataBase;
}
return Parameters.BinaryStorage.Provider == ParameterAccessor.Parts.BinaryStorageProviderNames.Local
? ParameterAccessor.Parts.BinaryStorageProviderNames.LocalFolder
: Parameters.BinaryStorage.Provider;
}
}
IsStoreExternal() の判定はこうなっています。
public bool IsStoreExternal()
{
return IsLocal() || IsAzureBlob();
}
public bool IsLocal(string provider)
{
var p = !string.IsNullOrEmpty(provider) ? provider : Provider;
return p == BinaryStorageProviderNames.Local || p == BinaryStorageProviderNames.LocalFolder;
}
つまり BinaryStorage.json の Provider が
Local / LocalFolder / AzureBlob のいずれかなら、外部保存になります。
BinaryStorage.json の Provider を Local にしている環境では、
添付ファイルの中身の全文検索は動きません。
SQL Server で IFilter が完璧に入っていても無反応です。
さらに厄介な「混在」パターン
保存先の話には、もう少し複雑なパターンがあります。
| ケース | 何が起きるか |
|---|---|
途中で Rds から Local に変更 |
NULL 化は新規追加の添付にしか走らないため、変更前の添付は検索でき、変更後はできない
|
UseStorageSelect: true |
項目ごとに保存先を選べるので、同じサイト内で検索できる項目とできない項目が並ぶ |
AutoDataBaseOrLocalFolder |
サイズで振り分けるため、大きいファイルほど検索できない |
TemporaryBinaryStorageProvider: "Rds" |
一旦 DB に入れてから外部へ移す経路。ただし条件付き |
保存先を後から変更した場合
Bin に NULL を書く処理は、Added == true のブロックの中にあります。
public void SqlStatement(
Context context,
SiteSettings ss,
Column column,
List<SqlStatement> statements,
long referenceId,
bool verUp)
{
if (Added == true)
{
// ここで Bin の書き込み / NULL 化が行われる
}
つまり新しく追加された添付ファイルにしか適用されません。
既存の行は触られないので Bin の値が残ります。
結果として、保存先を切り替えた環境では
「古い添付は中身で検索できるが、新しい添付は検索できない」という
一貫しない状態になります。原因の切り分けが難しいパターンです。
サイズによる振り分け
AutoDataBaseOrLocalFolder を使うと、サイズで保存先が変わります。
public static string BinaryStorageProvider(Column column, long size)
{
var binaryStorageProvider = BinaryStorageProvider(column);
switch (binaryStorageProvider)
{
case ParameterAccessor.Parts.BinaryStorageProviderNames.AutoDataBaseOrLocalFolder:
if (size > column?.LimitSize * 1024L * 1024L)
{
return Parameters.BinaryStorage.IsAzureBlob()
? ParameterAccessor.Parts.BinaryStorageProviderNames.AzureBlob
: ParameterAccessor.Parts.BinaryStorageProviderNames.LocalFolder;
}
return ParameterAccessor.Parts.BinaryStorageProviderNames.DataBase;
default:
return binaryStorageProvider;
}
}
上限を超えたファイルが外部保存になるので、
大きいファイルほど検索できないことになります。
中身を検索したいのは大きい資料のほうだと思うので、逆説的な挙動です。
一時的に DB を経由するパターン
TemporaryBinaryStorageProvider を Rds にしていると、
一旦 DB に入れてから外部へ移す経路を通ります。
if (Parameters.BinaryStorage.TemporaryBinaryStorageProvider == ParameterAccessor.Parts.BinaryStorageProviderNames.Rds
&& context.Api != true)
{
if (provider != null)
{
MoveBinFromRdsToExternal(
context: context,
provider: provider);
}
statements.Add(Rds.UpdateBinaries(
param: Rds.BinariesParam()
// ...
.Bin(raw: "NULL", _using: provider != null)
条件に context.Api != true が入っているところがポイントです。
API 経由のアップロードはこの経路を通りません。
画面からのアップロードと API からのアップロードで処理経路が違うということです。
Local モードの保存実体は拡張子なしの GUID
「外部保存でも、フォルダを別のツールでクロールすればいいのでは」と考えるかもしれません。
そこで実体がどう置かれるかを確認しておきます。
private static string ResolvePath(string objectName)
{
ValidateObjectName(objectName);
var relevantPath = objectName.Replace('/', Path.DirectorySeparatorChar);
return Path.Combine(Directories.BinaryStorage(), relevantPath);
}
そして渡される objectName はこの形です。
provider.Upload(
objectName: $"Attachments/{Guid}",
data: bin,
contentType: ContentType);
ベースディレクトリは以下で決まります。
public static string BinaryStorage()
{
var path = Parameters.BinaryStorage.Path;
return path.IsNullOrEmpty()
? Path.Combine(Environments.CurrentDirectoryPath, "App_Data", "BinaryStorage")
: path;
}
したがって実体は App_Data/BinaryStorage/Attachments/{GUID} に、
拡張子なしの GUID 名で置かれます。
元のファイル名・拡張子・どのレコードの添付なのかという情報は、
すべて Binaries テーブル側にしかありません。
フォルダだけを見ても形式の判定もレコードへの紐付けもできない、ということです。
言い換えると、Local モードに切り替えた時点で SQL Server の IFilter は仕事のしようがなくなります。
バイナリも拡張子情報も DB 側に届いていないからです。
ヒットしても「なぜヒットしたか」は画面に出ない
条件が揃って添付の中身で検索できるようになったとします。
ここでもうひとつ、混乱しやすい挙動があります。
第5回で見たとおり、横断検索の結果表示はタイトルと Body しか描画しません。
ハイライトの対象もその2つだけです。
つまり添付ファイルの中身でヒットした場合、
画面上にキーワードがどこにも見えない検索結果が並びます。
利用者から「検索がおかしい」と言われがちな挙動ですが、
仕様どおりです。理由を説明できるようにしておくとよいでしょう。
診断してみましょう
自環境が「添付の中身を検索できる状態か」を確かめる方法をまとめます。
保存先の内訳を数える
まずこれを実行してみてください。多くの環境ではこれだけで答えが出ます。
select
case when "Bin" is null then 'External' else 'DataBase' end as "Storage",
count(*)
from "Binaries"
where "BinaryType" = 'Attachments'
group by case when "Bin" is null then 'External' else 'DataBase' end;
External しかないなら、その環境では添付の中身は検索できません。
両方が出てきたら混在状態です。
Binaries の索引を確認する
select
t.name as table_name,
c.name as column_name
from sys.fulltext_index_columns fic
inner join sys.tables t on fic.object_id = t.object_id
inner join sys.columns c
on fic.object_id = c.object_id
and fic.column_id = c.column_id
where t.name = 'Binaries';
チェックリスト
| # | 確認項目 |
|---|---|
| 1 | RDBMS が SQL Server である（PostgreSQL は限定的、MySQL は非対応） |
| 2 | SQL Server に Full-Text Search 機能と、対象拡張子の IFilter が入っている |
| 3 | 添付の保存先がデータベースである（Bin が NULL でない） |
| 4 |
Search.json の SearchDocuments を true にして再起動した |
3番目でつまずく環境が多いはずです。
UseStorageSelect や AutoDataBaseOrLocalFolder を使っている場合は、
項目単位・サイズ単位での確認が必要になります。
なお、この機能は Items.FullText とは別系統なので、
「検索インデックスの再構築」を実行しても関係ありません。
索引を育てるのは RDBMS 側の仕事です。
まとめ
今回の要点です。
-
SearchDocumentsは WHERE 句を(item or binary)に変えるだけのスイッチで、参照箇所は2つだけです - プリザンター自身はテキスト抽出をしていません。バイナリ列に対する RDBMS 側の機能に委ねています
- SQL Server の DDL には
TYPE COLUMN Extensionが書かれ、Extensionには".pdf"形式が入るので、
IFilter 前提の設計であることが分かります - それでも
SearchDocumentsの既定はfalseなので、「索引は作るが検索には使わない」状態です。
そのため使わない索引のために IFilter が呼ばれるコストを払っています - 実力差は大きく、SQL Server は実用的、PostgreSQL は限定的、MySQL は完全に非対応です
- 保存先が
Local/AzureBlobだとBinにNULLが入るため、条件が揃っていても動きません - 保存先の切替・項目単位の指定・サイズによる振り分けで、検索できる添付とできない添付が混在します
- Local モードの実体は拡張子なしの GUID 名なので、フォルダ単体では形式も紐付けも分かりません
- 添付の中身でヒットしても、画面にはキーワードが表示されません
ここまでで限界が見えました。
次回は最終回として、本体を改変せずにこの限界をどこまで越えられるかを扱います。