Re: [sqlite] FTS4 content table
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, 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, 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
Re: [sqlite] FTS4 content table
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, 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
Re: [sqlite] FTS4 content table
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. -P On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy, 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
Re: [sqlite] FTS4 content table
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] FTS4 content table
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? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
