使用SQLite FTS3与INTEGER列

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));

我想能够查询文档或给定文档中的页面：

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新手,代码示例特别赞赏！

更新：对我来说,如何在这里创建一个外键约束是不清楚的.如果我选择元数据作为父表(这将是我的偏好,在没有双向约束的情况下)：

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

我收到错误：vtable构造函数失败：数据(不出意料之中,因为docid是rowid的别名,但当然我不能使用另一列,因为除rowid之外的所有列必须是TEXT).

而如果我尝试其他方式：

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

桌子的建造成功,但如果我尝试：

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

我得到错误：外键不匹配.

SQLite虚拟表不允许触发器,FTS3表忽略约束和亲和力.

我迄今为止所能做的最好的事情如下：

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

CREATE VIEW whole AS SELECT metadata.rowid AS rowid,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)创建触发器来引发对元数据和数据表的直接操作的异常,但这对我的目的来说可能是过度的(同样,我不需要整个表的UPDATE触发器).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!