sqlite - SQLite FTS 表可以由 View 支持吗？

Question

我使用“内容”参数创建了一个由 VIEW 支持的 FTS 表，但它没有产生我期望的结果:

CREATE TABLE tracks(docid INTEGER PRIMARY KEY, track_name varchar, ref_aid integer, ref_rid integer);
CREATE TABLE artists(artist_name varchar, aid integer);
CREATE TABLE releases(release_name varchar, rid integer);

CREATE VIEW v AS
SELECT docid, track_name, artist_name, release_name
FROM tracks, artists, releases
WHERE ref_aid = aid AND ref_rid = rid;

CREATE VIRTUAL TABLE t USING fts4(content="v", track_name, artist_name, release_name);

INSERT INTO tracks VALUES(0, 'xxx', 1, 1);
INSERT INTO tracks VALUES(1, 'yyy', 1, 1);
INSERT INTO artists VALUES('aaa', 1);
INSERT INTO releases VALUES('rrr', 1);

INSERT INTO t (docid, track_name, artist_name, release_name)
SELECT docid, track_name, artist_name, release_name
FROM v;

-- this prints 0 as expected:
SELECT docid FROM t WHERE t MATCH 'xxx';

-- this prints ||, indicating that it failed to look up the content in the view:
SELECT * FROM t WHERE t MATCH 'xxx';

我希望第二个 SELECT 语句返回 xxx|aaa|rrr，但它没有返回任何内容。

Answer 1

外部内容表需要有一个名为 rowid 的列，以便可以检索数据。这有效:

CREATE TABLE tracks(rowid INTEGER PRIMARY KEY, track_name varchar, ref_aid integer, ref_rid integer);
CREATE TABLE artists(artist_name varchar, aid integer);
CREATE TABLE releases(release_name varchar, rid integer);

CREATE VIEW v AS
SELECT rowid, track_name, artist_name, release_name
FROM tracks, artists, releases
WHERE ref_aid = aid AND ref_rid = rid;

CREATE VIRTUAL TABLE t USING fts4(content="v", track_name, artist_name, release_name);

INSERT INTO tracks VALUES(0, 'xxx', 1, 1);
INSERT INTO tracks VALUES(1, 'yyy', 1, 1);
INSERT INTO artists VALUES('aaa', 1);
INSERT INTO releases VALUES('rrr', 1);

INSERT INTO t (rowid, track_name, artist_name, release_name)
SELECT rowid, track_name, artist_name, release_name
FROM v;

-- this prints 0 as expected:
SELECT docid FROM t WHERE t MATCH 'xxx';

-- this prints xxx|aaa|rrr as expected:
SELECT * FROM t WHERE t MATCH 'xxx';