I have been working on making my sqlite DB more dynamic through the use of views. This has helped in all cases except the full text search tables. According to the SQLite FTS4 documentation (https://www.sqlite.org/fts3.html#section_6_2_2), there is a way to reference a table as the content table when creating a full text search virtual table. Unfortunately the user is still responsible for populating and maintaining the virtual table, which doesn't make much sense but it is what it is.
The way I initially populated the fts4 table and had it working was this:
CREATE VIRTUAL TABLE term_search USING fts4(id,name,origin,content,refs, created);
INSERT INTO term_search SELECT id,name,origin,text,refs,created FROM full_text_dict;
This worked well initially but is non-dynamic.
I attempted to create the 'external-content' fts4 table like this:
CREATE VIRTUAL TABLE term_search USING fts4 (content="full_text_dict",name, origin,content,refs,created);
INSERT INTO term_search (docid,name,origin,content,refs,created) SELECT id,name,origin,text,refs,created FROM full_text_dict;
SQLite allows me to do this, however when I attempt to query the table like so I get:
SELECT * FROM term_search WHERE content MATCH 'data';
Error: SQL logic error or missing database
I get the same error when I attempt to run the integrity-check command:
INSERT INTO term_search(term_search) VALUES('integrity-check');
I feel like I am missing something but cannot determine what it is from the documentation. Any Ideas? Thanks in advance.
EDIT: I should mention that the content table I am using is a view. Supposedly it should still work but I am wondering if it might be a bug.