使用SQLite FTS3与整数列

Question

我希望使用SQLite FTS3（实际上是FTS4）来索引一个拥有整数列的表格，概念上类似于这样：

CREATE VIRTUAL TABLE whole (document INTEGER, page INTEGER, content TEXT, 
    UNIQUE(document, page)) USING fts4();

我知道FTS3将除rowid以外的所有列视为TEXT，因此我需要使用两个表：

CREATE VIRTUAL TABLE data USING fts4();
CREATE TABLE metadata(document INTEGER, page INTEGER, UNIQUE(document, page));

我希望能够查询文档或给定文档中的页面。

SELECT DISTINCT document FROM metadata NATURAL JOIN data WHERE content MATCH 'foo';
SELECT page FROM metadata NATURAL JOIN data 
    WHERE document = 123 AND content MATCH 'foo';

我认为NATURAL JOIN需要确保行标保持同步，但最好的方法是什么？我应该使用FOREIGN KEY或其他约束吗？子查询比联接更好吗？

我想要对已经在数据库中的文档和页面进行插入以覆盖文本内容。这是否可以通过SQL进行编程或者是否必须检查信息表中的行是否已存在？

我还想删除给定文档的两个表中的数据，有没有一种方法可以用单个语句实现？

非常感谢任何建议，但由于我是SQL新手，尤其欢迎代码示例！

更新：我不确定如何在此处创建外键约束。如果我选择将metadata作为父表（在没有双向约束的情况下这将是我的首选）:

PRAGMA foreign_keys = ON;
CREATE TABLE metadata (document INTEGER, page INTEGER);
CREATE VIRTUAL TABLE data USING fts4(content TEXT, docid REFERENCES metadata);

我遇到了 Error: vtable constructor failed: data 错误（并不奇怪，因为docid是rowid的别名，但是除rowid之外，所有列都必须是TEXT类型，所以我不能使用另一列）。

而如果我尝试倒过来：

PRAGMA foreign_keys = ON;
CREATE VIRTUAL TABLE data USING fts4();
CREATE TABLE metadata (document INTEGER, page INTEGER, docid REFERENCES data);

表格构建成功，但如果我尝试：

INSERT INTO data (docid, content) VALUES (123, 'testing');
INSERT INTO metadata (docid, document, page) VALUES (123, 12, 23);

我遇到了错误：外键不匹配。

- hatfinch

---

你成功创建了带有外键的虚拟表吗？ - Maragues

@Maragues 不行，你也不能真正强制执行相同的约束条件——请参见我下面的答案。据我所知，虚拟表实际上只是带有 SQL 接口的程序。 - hatfinch

Answer 1

简而言之，当涉及到FTS3时，强制保持一致性相当困难。

SQLite虚拟表不允许触发器，而FTS3表会忽略约束和亲和性。

到目前为止，我所能做的最好的办法是：

CREATE TABLE metadata (document INTEGER, page INTEGER, UNIQUE(document, page));
CREATE VIRTUAL TABLE data USING fts4();

CREATE VIEW whole AS SELECT metadata.rowid AS rowid, document, page, content 
    FROM metadata JOIN data ON metadata.rowid = data.rowid;

CREATE TRIGGER whole_insert INSTEAD OF INSERT ON whole
BEGIN
  INSERT INTO metadata (document, page) VALUES (NEW.document, NEW.page);
  INSERT INTO data (rowid, content) VALUES (last_insert_rowid(), NEW.content);
END;

CREATE TRIGGER whole_delete INSTEAD OF DELETE ON whole
BEGIN
  DELETE FROM metadata WHERE rowid = OLD.rowid;
  DELETE FROM data WHERE rowid = OLD.rowid;
END;

为了保持一致性，我可以（使用PRAGMA recursive_triggers = NO）创建触发器，在直接操作metadata和data表时引发异常，但这对于我的目的来说可能有点过度（同样，我不需要whole表的UPDATE触发器）。

- hatfinch

Answer 2

我认为大多数数据库管理员都会同意，如果你使用SQL，应该充分利用它提供的所有功能。

外键是我推荐的通用路线，它们在这里有文档记录。

一般来说，在SQL数据库中，您永远不应该手动执行一致性。特别是在像这样适合外键的情况下。

对于DELETE FROM案例，SQLite不支持"cascade"关键字，例如MS SQL，但它具有触发器，可以让您拥有此行为。SQLite触发器的文档可以在这里找到。

最后，我建议跳过自然连接。

- 禪 師 無

---

显然，SQLite现在支持级联删除，但这并没有什么用，因为我无法让外键约束起作用（请参见上面的更新）。附言：请确认一下，当您说“跳过自然连接”时，是否意味着使用子选择？ - hatfinch

FTS表不支持除了docid/rowid之外的任何外键或索引。 - Greyson