FTS4 content table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

FTS4 content table

Dudu Markovitz
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FTS4 content table

Dan Kennedy-4
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FTS4 content table

Peter Johnson
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, <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FTS4 content table

Dan Kennedy-4
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, <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FTS4 content table

Peter Johnson
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, <[hidden email]> 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, <[hidden email]> 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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users