I've created a FTS table backed by a VIEW using the 'content' argument, but it isn't producing the results that I would expect:
CREATE TABLE tracks(id 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 id, 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);
-- this works and prints:
-- xxx|aaa|rrr
-- yyy|aaa|rrr
SELECT * FROM t;
-- this doesn't print anything:
SELECT * FROM t WHERE t MATCH 'xxx';
I would expect the second SELECT statement to return xxx|aaa|rrr
, but it doesn't return anything.
Aucun commentaire:
Enregistrer un commentaire