Re: [sqlite] Incorporating FTS into existing database schema
On Thu, Aug 28, 2008 at 8:15 PM, Andreas Ntaflos <[EMAIL PROTECTED]> wrote: > Notice the snippet bit: it takes the virtual table name (fts_paper in the case > of your examples) instead of the column name as an argument, which I find > curious. Your query would fail with the following error message: > > SQL error: illegal first argument to html_snippet The snippet thing is because fts needs to pass a bit of magic between the results of the MATCH and the snippet function. At creation, fts3 adds a hidden column with the same name as the table, and then it feeds the bit of magic out through that. There's no way SQLite proper can see this, and fts3 has no way to see the table aliases (the snippet implementation sees the value passed, not the name of the value passed). snippet() can only operate off of the results of the MATCH, so specifying the column could be ambiguous (what if you passed a column which didn't participate in the MATCH?). So, in the examples you gave, b.paper_text will be passing in the TEXT from that column in the row, and snippet() doesn't have the info it needs to operate. Passing b doesn't work, because b is a table, and you cannot pass tables to functions. fts_paper is passing the magic hidden column, NOT the table named fts_paper, it could also be written b.fts_paper. I almost wonder if it wouldn't make sense to add an additional hidden column called match, and maybe another called all. Then you could write things like: SELECT docid, snippet(match) FROM fts_table WHERE all MATCH 'foo'; SELECT t.docid, snippet(t.match) FROM fts_table AS t WHERE t.all MATCH 'foo'; I think that would be less surprising to people, because using a table-named column is ambiguous. When it works, it looks like you managed to use the table name, but when it doesn't, it just makes it harder to figure out what really _is_ going on. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
> > Any advice will be greatly appreciated. If there is any FM I > should R kindly point me to it :) > FTS information is difficult to find. Try http://www.sqlite.org/cvstrac/wiki?p=FtsOne I can't remember how I came across this link because I can never find it on the SQLite website. Ignore references to FTS1 as the SQL syntax is the same for FTS3 (I believe it is only the internals that have changed). One tip is to read the document a number of times. There are a number of important but subtle concepts that you need to grasp to effectively use FTS. In particular understand how the tokeniser works. Words with hyphens or UTF8 may not work as you might expect. 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] Incorporating FTS into existing database schema
On Wednesday 27 August 2008 16:39:05 P Kishor wrote: > So, now help me and the rest of the community making a nice set of FTS > notes that contain all the info, from compilation to usage. ;-) Once I finish the application in question and can say with certainty that I used FTS well and know all the steps hopefully I'll get right on it :) Somewhat related and probably interesting to all who use your notes as examples, the query: SELECT a.paper_id, a.paper_name, snippet(b.paper_text) FROM paper a JOIN fts_paper b ON a.paper_id = b.rowid WHERE b.paper_text MATCH 'automatically'; should really be: SELECT a.paper_id, a.paper_name, snippet(fts_paper) FROM paper a JOIN fts_paper b ON a.paper_id = b.rowid WHERE b.paper_text MATCH 'automatically'; Notice the snippet bit: it takes the virtual table name (fts_paper in the case of your examples) instead of the column name as an argument, which I find curious. Your query would fail with the following error message: SQL error: illegal first argument to html_snippet > Hopefully we can convince DRH to include it somewhere prominent in the > SQLite website instead of the scattered pieces on the wiki. That would be nice. FTS is a real killer-feature that can make many things much, much easier. It even seems to work well with Unicode, at least for the German language with Umlauts and the Eszett (ß) and also symbols defined in HTML (quotation marks, mathematical symbols, Greek letters etc). Love it :) Andreas -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
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] Incorporating FTS into existing database schema
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] Incorporating FTS into existing database schema
On 8/27/08, Andreas Ntaflos <[EMAIL PROTECTED]> wrote: > On Wednesday 27 August 2008 16:11:28 P Kishor wrote: > > See my notes at > > > > http://www.punkish.org/?p=Why_File_When_You_Can_Full-Text_Search > > > > I should get down to write a new and more detailed version and then > > putting it on the SQLite wiki. FTS documentation is woefully > > dismembered and scattered. > > > Puneet, > > thank you very much for the quick and most valuable reply. Your notes seem to > be exactly what I need to start (and even finish) incorporating FTS into my > application. > > Again, thank you! So, now help me and the rest of the community making a nice set of FTS notes that contain all the info, from compilation to usage. ;-) Hopefully we can convince DRH to include it somewhere prominent in the SQLite website instead of the scattered pieces on the wiki. > > Andreas > > -- > > Andreas Ntaflos > Vienna, Austria > > GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 > -- 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] Incorporating FTS into existing database schema
On Wednesday 27 August 2008 16:11:28 P Kishor wrote: > See my notes at > > http://www.punkish.org/?p=Why_File_When_You_Can_Full-Text_Search > > I should get down to write a new and more detailed version and then > putting it on the SQLite wiki. FTS documentation is woefully > dismembered and scattered. Puneet, thank you very much for the quick and most valuable reply. Your notes seem to be exactly what I need to start (and even finish) incorporating FTS into my application. Again, thank you! Andreas -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
On 8/27/08, Andreas Ntaflos <[EMAIL PROTECTED]> wrote: > Hi list, > > this is my first post to this list and the first time I am using SQLite > (3.6.1 > from source on Debian and Ubuntu) in a program of mine so I obviously don't > have much experience with it. I am glad for the documentation (otherwise I > would have missed that FOREIGN KEY constraints are ignored and have to be > added using triggers) but I am unclear on a few things regarding FTS3 (or > full text search in general). > > In particular I'd like to know the best practise on incorporating FTS3 into > an > existing database schema, possibly already populated with content. There are > some fields of some of the tables in the database that should be be full text > searchable. How to go about that? > > Obviously I have to create some virtual tables that hold the text that will > be > searched. But how do I link the virtual tables to the "real" tables so that I > know the table and the row in which a field with matching content was found? > Do I, every time I insert text into a field, or change the text in a field of > the real table, have to programmatically or manually update or change the > text in the corresponding virtual table as well? > > As you can see I am not very familiar with how these things are supposed to > work. The documentation [1, 2] on the website is fine for explaining FTS > itself but I couldn't find anything on how to incorporate FTS into a real > application. > > Any advice will be greatly appreciated. If there is any FM I should R kindly > point me to it :) See my notes at http://www.punkish.org/?p=Why_File_When_You_Can_Full-Text_Search I should get down to write a new and more detailed version and then putting it on the SQLite wiki. FTS documentation is woefully dismembered and scattered. > > Thanks in advance, > > Andreas > > [1] http://www.sqlite.org/cvstrac/wiki?p=FtsUsage > [2] http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex > -- > Andreas Ntaflos > Vienna, Austria > > GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- 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
