Re: [sqlite] FTS3 Snippet function on two column MATCHes
On 8/18/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > P Kishor wrote: > > > > > I would like to generate Snippets from MATCHes in two columns, > > however, I get the following error: "unable to use function MATCH in > > the requested context" with the following query -- > > > > SELECT poem_id, context > > FROM poems a JOIN ( > >SELECT > >rowid, > >Snippet(fts_poems, '', '', > '…') AS context > >FROM fts_poems > >WHERE poem MATCH ? OR history MATCH ? > > ) b ON a.poem_id = b.rowid > > > > > > Does this work for you? > > SELECT poem_id, context > FROM poems as a > JOIN ( > SELECT > rowid, > Snippet(fts_poems, '', '', '…') AS > context > FROM fts_poems > WHERE rowid in > ( > select rowid from fts_poems where poem MATCH ? > union > select rowid from fts_poems where history MATCH ? > ) > ) as b ON a.poem_id = b.rowid; > > It runs each match in a separate subquery and doesn't generate an error > when prepared by sqlite. Sorry for the tardy response. Unfortunately, your suggestion does not work. I believe, as pointed out in another email by Nicholas Brandon, your suggestion is incorrect. On the other hand, the following worked -- SELECT poem_id, context FROM poems a JOIN ( SELECT rowid, Snippet(fts_poems, '', '', '…') AS context FROM fts_poems WHERE fts_poems MATCH ? ) b ON a.poem_id = b.rowid In other words, I have to match on the table name, which seems really counter-intuitive. And, while it works for me in this instance, because I have only two columns FTS-indexed, and I am trying to match on those two columns, I can't imagine how the above would work if I had, say, 3 columns indexed and wanted to search in only two of them. I guess I would have to use the col:term kind of syntax. FTS is really a brilliant addition to SQLite, and it really needs to be documented more comprehensively and clearly, all the way from compiling, setting up, indexing, and searching. The bits and pieces are there, but they need to be brought together. I will try to do my bit by improving the documentation and put it up there -- hopefully it will be of help. > > HTH > Dennis Cote > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
> > I would like to generate Snippets from MATCHes in two > columns, however, I get the following error: "unable to use > function MATCH in the requested context" with the following query -- > > SELECT poem_id, context > FROM poems a JOIN ( > SELECT > rowid, > Snippet(fts_poems, '', > '', '…') AS context > FROM fts_poems > WHERE poem MATCH ? OR history MATCH ? > ) b ON a.poem_id = b.rowid > The query above does not use the syntax of MATCH when using FTS. See the extract below from http://www.sqlite.org/cvstrac/wiki?p=FtsOne: Any term in a query string may be preceded by the name of a particular column to use for matching that term: sqlite> select name, ingredients from recipe where recipe match 'name:pie ingredients:onions'; broccoli pie|broccoli cheese onions flour sqlite> The following are entirely equivalent: sqlite> select name from recipe where ingredients match 'sugar'; sqlite> select name from recipe where recipe match 'ingredients:sugar'; When a specific column name appears to the left of the MATCH operator, that column is used for matching any term without an explicit column qualifier. Thus, the following are equivalent: sqlite> select name from recipe where recipe match 'name:pie ingredients:onions'; sqlite> select name from recipe where name match 'pie ingredients:onions'; This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
P Kishor wrote: > > I would like to generate Snippets from MATCHes in two columns, > however, I get the following error: "unable to use function MATCH in > the requested context" with the following query -- > > SELECT poem_id, context > FROM poems a JOIN ( > SELECT > rowid, > Snippet(fts_poems, '', '', '…') > AS context > FROM fts_poems > WHERE poem MATCH ? OR history MATCH ? > ) b ON a.poem_id = b.rowid > Does this work for you? SELECT poem_id, context FROM poems as a JOIN ( SELECT rowid, Snippet(fts_poems, '', '', '…') AS context FROM fts_poems WHERE rowid in ( select rowid from fts_poems where poem MATCH ? union select rowid from fts_poems where history MATCH ? ) ) as b ON a.poem_id = b.rowid; It runs each match in a separate subquery and doesn't generate an error when prepared by sqlite. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
On Fri, Aug 15, 2008 at 9:13 PM, Alexandre Courbot <[EMAIL PROTECTED]> wrote: >> I would like to generate Snippets from MATCHes in two columns, >> however, I get the following error: "unable to use function MATCH in >> the requested context" with the following query -- > > I think you ran into the same problem as I did: > > http://www.nabble.com/Fts3-and-JOIN-sometimes-results-in-inability-to-use-MATCH-operator-td18851478.html > > Unfortunately there is no solution right now. I've discussed that on > the development mailing list as well where I have been confirmed this > is a bug. I have also opened a trac ticket about it: > > http://www.sqlite.org/cvstrac/tktview?tn=3281,3 > > There is a workaround, which is to used nested queries instead of > joins (i.e. "where x in (select ...)" instead of "join"). I've met the > Snippet function for the first time in your mail and failed to find > any documentation about (mind to give me a pointer here? it looks > interesting) so I cannot convert your example query but I'm confident > it can be fixed this way. http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex The above is the only location known to me where Snippet is documented (other than inside the source code files perhaps). > > Alex. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
On Fri, Aug 15, 2008 at 9:13 PM, Alexandre Courbot <[EMAIL PROTECTED]> wrote: >> I would like to generate Snippets from MATCHes in two columns, >> however, I get the following error: "unable to use function MATCH in >> the requested context" with the following query -- > > I think you ran into the same problem as I did: > > http://www.nabble.com/Fts3-and-JOIN-sometimes-results-in-inability-to-use-MATCH-operator-td18851478.html > > Unfortunately there is no solution right now. I've discussed that on > the development mailing list as well where I have been confirmed this > is a bug. I have also opened a trac ticket about it: > > http://www.sqlite.org/cvstrac/tktview?tn=3281,3 > > There is a workaround, which is to used nested queries instead of > joins (i.e. "where x in (select ...)" instead of "join"). I've met the > Snippet function for the first time in your mail and failed to find > any documentation about (mind to give me a pointer here? it looks > interesting) so I cannot convert your example query but I'm confident > it can be fixed this way. http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex The above is the only location known to me where Snippet is documented (other than inside the source code files perhaps). > > Alex. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
> I would like to generate Snippets from MATCHes in two columns, > however, I get the following error: "unable to use function MATCH in > the requested context" with the following query -- I think you ran into the same problem as I did: http://www.nabble.com/Fts3-and-JOIN-sometimes-results-in-inability-to-use-MATCH-operator-td18851478.html Unfortunately there is no solution right now. I've discussed that on the development mailing list as well where I have been confirmed this is a bug. I have also opened a trac ticket about it: http://www.sqlite.org/cvstrac/tktview?tn=3281,3 There is a workaround, which is to used nested queries instead of joins (i.e. "where x in (select ...)" instead of "join"). I've met the Snippet function for the first time in your mail and failed to find any documentation about (mind to give me a pointer here? it looks interesting) so I cannot convert your example query but I'm confident it can be fixed this way. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 Snippet function on two column MATCHes
I have the following tables CREATE TABLE poems (poem_id, poem, history); CREATE VIRTUAL TABLE fts_poems USING fts3 (poem, history); INSERT INTO fts_poems (rowid, poem, history) SELECT poem_id, poem, history FROM poems; The following works -- SELECT poem_id, context FROM poems a JOIN ( SELECT rowid, Snippet(fts_poems, '', '', '…') AS context FROM fts_poems WHERE poem MATCH ? ) b ON a.poem_id = b.rowid I would like to generate Snippets from MATCHes in two columns, however, I get the following error: "unable to use function MATCH in the requested context" with the following query -- SELECT poem_id, context FROM poems a JOIN ( SELECT rowid, Snippet(fts_poems, '', '', '…') AS context FROM fts_poems WHERE poem MATCH ? OR history MATCH ? ) b ON a.poem_id = b.rowid The Snippet query syntax seems wrong and awkward to me because the Snippet function requires the table name rather than the more intuitive (to me) column name on which the MATCH is being done. What am I doing wrong? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
