使用SQLite FTS3与INTEGER列

我想使用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));

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

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

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

简而言之,在涉及FTS3的情况下执行一致性相当困难.

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

使用SQLite FTS3与INTEGER列

猜你在找的Sqlite相关文章