sql - 将 SQLite FTS3 与 INTEGER 列一起使用

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 要求我确保 rowid 保持同步，但最好的方法是什么？我应该使用 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);

我收到错误:外键不匹配。

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 触发器)。