[sqlite] Constraining FTS5 results based on offsets()
Hi, For my use case I need to index some texts, and I also need be able to assign attributes to sub-sequences/spans of tokens in the texts. I want to be able to search only for keywords/phrases that occur in spans with a certain attribute. As an example, imagine we have a set of rich text documents and we want to find the locations of the "SQLite rocks" phrase in that set, but not just any instance of it- only those that have an attribute "bold". The general idea, I'm considering at the moment is as follows: 1) create a table 'spans' with columns (doc_id, attrib_id, start_tok, end_tok), where doc_id is equal to the corresponding rowid in the FTS table, and start_tok and end_tok are the 0-based offsets that delimit a span 2) issue a match (sub?)query against the FTS table and obtain a list of (rowid, list-of-offsets in that doc) 3) somehow convert the above results into (rowid, start_offset, end_offset) for each entry of the list-of-offsets 4) join the results from 3) with the "spans" table on "rowid=spans.doc_id" where "spans.attrib_id=X and start_offset >= spans.start_pos and end_offset <= spans.end_pos" Do you think this general approach makes sense, and how would you approach the problem if not? I only have a cursory knowledge of both SQL and SQLite at this point, so it's quite possible I'm missing something obvious. On the implementation side: - as far as I understand FTS5 has some clear advantages over FTS3/4. Apparently there isn't readily available offsets() function in FTS5 yet, but the API should make writing one rather straightforward - step 3) is what I wonder about the most... What would be a good way to convert a (doc_id, ) row into (doc_id, start_offset, end_offset) tuples for every entry in the list? I'd guess I will have to implement some sort of virtual table(a.k.a. "table-valued function")? Perhaps a table-valued function that takes a FTS query as its parameter that it then uses to obtain (doc_id, ), and expose each hit as (doc_id, start_offset, end_offset) rows in the virtual table? Any thoughts and ideas would be greatly appreciated. Thank you! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Constraining FTS5 results based on offsets()
On 04/08/2018 02:00 PM, miroslav.marango...@outlook.com wrote:
Hi,
For my use case I need to index some texts, and I also need be able to assign attributes to
sub-sequences/spans of tokens in the texts. I want to be able to search only for keywords/phrases
that occur in spans with a certain attribute. As an example, imagine we have a set of rich text
documents and we want to find the locations of the "SQLite rocks" phrase in that set, but
not just any instance of it- only those that have an attribute "bold".
The general idea, I'm considering at the moment is as follows:
1) create a table 'spans' with columns (doc_id, attrib_id, start_tok, end_tok),
where doc_id is equal to the corresponding rowid in the FTS table, and
start_tok and end_tok are the 0-based offsets that delimit a span
2) issue a match (sub?)query against the FTS table and obtain a list of (rowid,
list-of-offsets in that doc)
3) somehow convert the above results into (rowid, start_offset, end_offset) for
each entry of the list-of-offsets
4) join the results from 3) with the "spans" table on "rowid=spans.doc_id" where
"spans.attrib_id=X and start_offset >= spans.start_pos and end_offset <= spans.end_pos"
Do you think this general approach makes sense, and how would you approach the
problem if not? I only have a cursory knowledge of both SQL and SQLite at this
point, so it's quite possible I'm missing something obvious.
On the implementation side:
- as far as I understand FTS5 has some clear advantages over FTS3/4. Apparently
there isn't readily available offsets() function in FTS5 yet, but the API
should make writing one rather straightforward
- step 3) is what I wonder about the most... What would be a good way to convert a (doc_id,
) row into (doc_id, start_offset, end_offset) tuples for every entry in the
list? I'd guess I will have to implement some sort of virtual table(a.k.a. "table-valued
function")? Perhaps a table-valued function that takes a FTS query as its parameter that it then
uses to obtain (doc_id, ), and expose each hit as (doc_id, start_offset,
end_offset) rows in the virtual table?
Any thoughts and ideas would be greatly appreciated.
Maybe just implement an FTS5 function to do the filtering in step 4
above. So that your query looks like:
SELECT * FROM fts5tbl('SQLite+rocks'), spans
WHERE ft.rowid=spans.doc_id
AND spans.attrib_id = 'bold'
AND custom_fts5_function(fts5tbl, spans.start_tok, spans.end_tok);
where custom_fts5_function() returns true if there are any phrase hits
with token offsets between its two trailing arguments. Or whatever it is
you require.
https://sqlite.org/fts5.html#custom_auxiliary_functions
Dan.
Thank you!
___
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] Constraining FTS5 results based on offsets()
Thank you Dan!
This should be much easier to implement than a full blown virtual table, but as
far as I can tell(as I've mentioned, I'm not an expert in this) it doesn't do
exactly what I want. I need all the locations of the search phrases, subject to
the attribute constraint, in a document, but this query will only return the
spans that contain one or more instance of the phrase. Or maybe I'm missing
something?
Provided my understanding is correct, what do you think would be most the
pragmatic way to change the query, so that it returns the locations of the
phrases? What I imagine could work, even though it's admittedly ugly, is to
write another UDF, let's call it "hacky_offsets()", that has some sort of
"private" communication channel with custom_fts5_function(). So the query will
be something like:
SELECT fts5tbl.rowid, hacky_offsets()
FROM fts5tbl('SQLite+rocks'), spans
WHERE ft5tbl.rowid=spans.doc_id
AND spans.attrib_id = 'bold'
AND custom_fts5_function(fts5tbl, spans.start_tok, spans.end_tok);
When custom_fts5_function() finds instances of the phrases that fall between
start_tok/end_tok it records these instances into a location hacky_offsets()
knows about, so it can read them from there and return them to the application.
Would this even work and can you think of a more idiomatic/elegant
implementation?
Thanks!
From: sqlite-users on behalf of
Dan Kennedy
Sent: Tuesday, April 10, 2018 11:49 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Constraining FTS5 results based on offsets()
On 04/08/2018 02:00 PM, miroslav.marango...@outlook.com wrote:
> Hi,
> For my use case I need to index some texts, and I also need be able to assign
> attributes to sub-sequences/spans of tokens in the texts. I want to be able
> to search only for keywords/phrases that occur in spans with a certain
> attribute. As an example, imagine we have a set of rich text documents and we
> want to find the locations of the "SQLite rocks" phrase in that set, but not
> just any instance of it- only those that have an attribute "bold".
>
> The general idea, I'm considering at the moment is as follows:
> 1) create a table 'spans' with columns (doc_id, attrib_id, start_tok,
> end_tok), where doc_id is equal to the corresponding rowid in the FTS table,
> and start_tok and end_tok are the 0-based offsets that delimit a span
> 2) issue a match (sub?)query against the FTS table and obtain a list of
> (rowid, list-of-offsets in that doc)
> 3) somehow convert the above results into (rowid, start_offset, end_offset)
> for each entry of the list-of-offsets
> 4) join the results from 3) with the "spans" table on "rowid=spans.doc_id"
> where "spans.attrib_id=X and start_offset >= spans.start_pos and end_offset
> <= spans.end_pos"
>
> Do you think this general approach makes sense, and how would you approach
> the problem if not? I only have a cursory knowledge of both SQL and SQLite at
> this point, so it's quite possible I'm missing something obvious.
>
> On the implementation side:
> - as far as I understand FTS5 has some clear advantages over FTS3/4.
> Apparently there isn't readily available offsets() function in FTS5 yet, but
> the API should make writing one rather straightforward
>
> - step 3) is what I wonder about the most... What would be a good way to
> convert a (doc_id, ) row into (doc_id, start_offset,
> end_offset) tuples for every entry in the list? I'd guess I will have to
> implement some sort of virtual table(a.k.a. "table-valued function")? Perhaps
> a table-valued function that takes a FTS query as its parameter that it then
> uses to obtain (doc_id, ), and expose each hit as (doc_id,
> start_offset, end_offset) rows in the virtual table?
>
> Any thoughts and ideas would be greatly appreciated.
Maybe just implement an FTS5 function to do the filtering in step 4
above. So that your query looks like:
SELECT * FROM fts5tbl('SQLite+rocks'), spans
WHERE ft.rowid=spans.doc_id
AND spans.attrib_id = 'bold'
AND custom_fts5_function(fts5tbl, spans.start_tok, spans.end_tok);
where custom_fts5_function() returns true if there are any phrase hits
with token offsets between its two trailing arguments. Or whatever it is
you require.
https://sqlite.org/fts5.html#custom_auxiliary_functions
Dan.
>
> Thank you!
> ___
> 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
