Hi, I have a table questions with lots of columns. Out of them 6 columns represent text content and the rest are technical (FKs, update timestamp, etc)
I need to build the FTS5 search index for this table to only index the content columns (that 6). The DB is only built once on the server and later used as read-only in the client app (which downloads it from the server) It works fine unless I try to use the external content table feature (which I need to reduce the table size and also to be able to make JOIN queries to get the meta info from the original table for each matching row. The original table (called questions) has the PK column called id. This is the column I want to use as rowid for the search index and for joining the tables. Here's how I'm trying to create and populate the search table: DROP TABLE IF EXISTS search; CREATE VIRTUAL TABLE search USING fts5(question, answer, altAnswers, comments, authors, sources, tokenize = "snowball russian english unicode61", content=questions, content_rowid=id); INSERT INTO search SELECT id, question, answer, altAnswers, comments, authors, sources FROM questions WHERE obsolete IS NULL; At this point INSERT fails because I'm trying to insert 7 columns into the table which has 6. I also tried creating id as UNINDEXED column in the search table and that passed but the search results were silly, matching completely unrelated tokens. So what is the proper way to insert all rows from questions into the search table? Thanks in advance _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
