FTS5提供了支持全文搜索的虚拟表。换句话说,您无法在现有表的列上创建全文索引。相反,您可以创建一个 FTS5 虚拟表并从原始表复制相关数据以建立索引。为了避免两次存储相同的数据,您可以将其设为外部内容表,但您仍然必须确保 FTS5 表保持同步(手动或通过触发器)。
您可以创建一个通用的自定义 DDL 构造来处理创建镜像另一个表的 FTS5 虚拟表:
class CreateFtsTable(DDLElement):
"""Represents a CREATE VIRTUAL TABLE ... USING fts5 statement, for indexing
a given table.
"""
def __init__(self, table, version=5):
self.table = table
self.version = version
@compiles(CreateFtsTable)
def compile_create_fts_table(element, compiler, **kw):
"""
"""
tbl = element.table
version = element.version
preparer = compiler.preparer
sql_compiler = compiler.sql_compiler
tbl_name = preparer.format_table(tbl)
vtbl_name = preparer.quote(tbl.name + "_idx")
text = "\nCREATE VIRTUAL TABLE "
text += vtbl_name + " "
text += "USING fts" + str(version) + "("
separator = "\n"
pk_column, = tbl.primary_key
columns = [col for col in tbl.columns if col is not pk_column]
for column in columns:
text += separator
separator = ", \n"
text += "\t" + preparer.format_column(column)
if not isinstance(column.type, String):
text += " UNINDEXED"
text += separator
text += "\tcontent=" + sql_compiler.render_literal_value(
tbl.name, String())
text += separator
text += "\tcontent_rowid=" + sql_compiler.render_literal_value(
pk_column.name, String())
text += "\n)\n\n"
return text
给定的实现有点天真,默认情况下索引所有文本列。创建的虚拟表通过添加隐式命名_idx
在原始表名之后。
但仅此还不够,如果您想自动保持表与触发器同步,并且由于您只为一个表添加索引,您可以选择在迁移脚本中使用文本 DDL 构造:
def upgrade():
ddl = [
"""
CREATE VIRTUAL TABLE person_idx USING fts5(
name,
thumb UNINDEXED,
content='person',
content_rowid='id'
)
""",
"""
CREATE TRIGGER person_ai AFTER INSERT ON person BEGIN
INSERT INTO person_idx (rowid, name, thumb)
VALUES (new.id, new.name, new.thumb);
END
""",
"""
CREATE TRIGGER person_ad AFTER DELETE ON person BEGIN
INSERT INTO person_idx (person_idx, rowid, name, thumb)
VALUES ('delete', old.id, old.name, old.thumb);
END
""",
"""
CREATE TRIGGER person_au AFTER UPDATE ON person BEGIN
INSERT INTO person_idx (person_idx, rowid, name, thumb)
VALUES ('delete', old.id, old.name, old.thumb);
INSERT INTO person_idx (rowid, name, thumb)
VALUES (new.id, new.name, new.thumb);
END
"""
]
for stmt in ddl:
op.execute(sa.DDL(stmt))
如果您的人员表包含现有数据,请记住将这些数据也插入到创建的虚拟表中以建立索引。
为了实际使用创建的虚拟表,您可以创建一个非主要映射器 for Person
:
person_idx = db.Table('person_idx', db.metadata,
db.Column('rowid', db.Integer(), primary_key=True),
db.Column('name', db.Text()),
db.Column('thumb', db.Text()))
PersonIdx = db.mapper(
Person, person_idx, non_primary=True,
properties={
'id': person_idx.c.rowid
}
)
并使用例如 MATCH 进行全文查询:
db.session.query(PersonIdx).\
filter(PersonIdx.c.name.op("MATCH")("john")).\
all()
请注意,结果是一个列表Person
对象。PersonIdx
只是一个Mapper.
正如 Victor K. 所指出的,不推荐使用非主要映射器,新的替代方案是使用aliased()。设置基本相同,但rowid
to id
创建时需要进行映射person_idx
Table
使用key
的参数Column
:
person_idx = db.Table('person_idx', db.metadata,
db.Column('rowid', db.Integer(), key='id', primary_key=True),
db.Column('name', db.Text()),
db.Column('thumb', db.Text()))
并创建别名而不是新的映射器:
PersonIdx = db.aliased(Person, person_idx, adapt_on_names=True)
别名的工作方式更像映射类,因为您不通过访问映射属性.c
,但直接:
db.session.query(PersonIdx).\
filter(PersonIdx.name.op("MATCH")("john")).\
all()