Wording looks great, thanks Dan. FWIW I believe the same docs issue applies to the FTS5 module.
Docs are hard, I often find myself re-reading paragraphs and then figuring out that I'm just a big dummy and didn't 'get it' the first time. The FTS docs are actually pretty great, they introduce some new concepts that are unique to that module and so those nonconanical patterns cause people to make their own assumptions regarding how they are implemented, which was the case here. Thanks again. - Peter On Mon, 2 Jul 2018, 10:02 AM Dan Kennedy, <danielk1...@gmail.com> wrote: > On 07/01/2018 05:20 PM, Peter Johnson wrote: > > I recall you must still enumerate the column names of the fields you wish > > to link to the contentless table when creating the virtual table. > > > > If I remember correctly, failing to do so will result in 'no such column' > > errors. > > > > Last time I looked at the docs I got caught with the same gotcha, the > docs > > show an example query of how the contentless content is loaded but > > (possibly?) don't make it 100% clear whether you need to specify those > > columns in the vtable definition in order to allow them to be returned in > > the result rows. > > Fair enough. Added a sentence here to try to make things clearer: > > https://sqlite.org/docsrc/info/ea0f0b4c4af09bf9 > > Dan. > > > > > > > -P > > > > On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy, <danielk1...@gmail.com> wrote: > > > >> On 06/30/2018 10:24 PM, Dudu Markovitz wrote: > >>> Hi > >>> > >>> according to the documentation - > >>> 6.2.2. External Content FTS4 Tables > >>> > >>> An "external content" FTS4 table is similar to a contentless table, > >> except > >>> that if evaluation of a query requires the value of a column other than > >>> docid, FTS4 attempts to retrieve that value from a table (or view, or > >>> virtual table) nominated by the user (hereafter referred to as the > >> "content > >>> table"). > >>> > >>> However I see no example for a query on the fts table that implicitly > >>> retrieves values from the content table, nor could I find any in other > >>> place. > >>> All my attempts to write such a query ended up with "Error: no such > >>> column". > >>> Am I missing something or is it a problem in the documentation? > >> I don't think I understand the question. What do you want it to do? > >> > >> The SELECT query in the second block of code in that section: > >> > >> https://sqlite.org/fts3.html#_external_content_fts4_tables_ > >> > >> is: > >> > >> SELECT * FROM t3 WHERE t3 MATCH 'k' > >> > >> t3 is the FTS4 table. The query does a lookup on the FTS4 index to find > >> matches for token 'k', then automatically retrieves values from the > >> content table (t2) for each matched row. The first instance of the > >> SELECT in the block of code shows everything working as expected, the > >> subsequent two show the counter-intuitive effects of allowing the FTS > >> index to get out of sync with the content table. > >> > >> Dan. > >> > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
