Good day, Michael. Thank you for taking the time to respond. Per my (possibly ignorant) understanding of the FTS docs, this construct can be described as such:
Construct: MATCH 'bug report API_Version:374 OR API_Version:UAR' Verbose: Perform an FTS search on the phrase 'bug report', and return only records that contain '374' or 'UAR' in the API_Version column. My table is structured as such: CREATE VIRTUAL TABLE RIM_API_FTS_DB USING FTS4 (Target_Name,Target_Context,Target_Description,Target_Content,Target_Link,Pa rent_Short,Parent_Name,Parent_Link,API_Version)...; The inclusion of proper quotes, as you have suggested below, I believe, would break SQLite's expression parser preventing it from functioning as intended. However, that is pure speculation, until such time as I am able to test and either support/refute the theory. That will have to wait until this PM when I am back home and able to test... Regarding your query about rank(matchinfo(blah)), rank is a UDF (User Defined Function) that I implemented in the host language that I am leveraging to make use of the DBMS. I had to write the function (it is modeled off of the Appendix A content on the FTS3/FTS4 page) in my host language, setup a callback therein to get the runtime-machine address for the function's entry point, and then I called sqlite3_create_function, to register the callback with the SQLite3 dll. The host language code for my implementation of what I just described can be viewed here: http://sqlite.pastebin.com/sQwatkR7 I strongly suspect that the SQLite devs felt that each user's preference for implementing a ranking function would be so dynamic that coding it into the dll would be futile. As such, with the UDF functionality that the API brings to the table, it is easily implemented (once an understanding of the dynamics regarding callbacks in the host language is attained). Again, thank you. I'll respond to this thread once I am able to test. -Tod Wulff Director Gov't Programs - MRO Sales Director - Avionics Aerodynamics, Inc. (KPTK) -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Wednesday, December 29, 2010 10:29 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] FTS4 snippets contains unintended content? I have to admit I'm an FTS neophyte so I'm trying to understand what's going on here. Here's the SQL: create virtual table data using fts4(content text); insert into data values('we got bug report API_Version:374 right here'); insert into data values('we got bug report API_Version:375 right here'); insert into data values('we got API_Version:UAR right here'); insert into data values('we got API_Version:UAS right here'); With or without quotes this works...you can see that each term matches if you treat _ and : as whitespace sqlite> select offsets(data) from data where data match '"bug report sqlite> API_Version:374"'; 0 0 7 3 0 1 11 6 0 2 18 3 0 3 22 7 0 4 30 3 sqlite> select offsets(data) from data where data match 'bug report sqlite> API_Version:374'; 0 0 7 3 0 1 11 6 0 2 18 3 0 3 22 7 0 4 30 3 Now...purportedly FTS ignores all non-alphanumeric characters in the index. So now we try sqlite> select offsets(data) from data where data match 'bug report sqlite> API_Version 374'; 0 0 7 3 0 1 11 6 0 2 18 3 0 3 22 7 0 4 30 3 And indeed...nothing changes... Now for the slightly more complex queries sqlite> select offsets(data) from data where data match 'bug report API sqlite> Version:374 OR API_Version:UAR'; 0 0 7 3 0 1 11 6 0 2 18 3 0 5 18 3 0 3 22 7 0 6 22 7 0 4 30 3 0 2 7 3 0 5 7 3 0 3 11 7 0 6 11 7 0 7 19 3 sqlite> select offsets(data) from data where data match '(bug report API sqlite> Version:374) OR "API_Version:UAR"'; 0 0 7 3 0 1 11 6 0 2 18 3 0 3 22 7 0 4 30 3 0 2 7 3 0 5 7 3 0 3 11 7 0 6 11 7 0 7 19 3 sqlite> select offsets(data) from data where data match '(bug report API sqlite> Version:374) OR (API_Version:UAR)'; 0 0 7 3 0 1 11 6 0 2 18 3 0 5 18 3 0 3 22 7 0 6 22 7 0 4 30 3 0 2 7 3 0 5 7 3 0 3 11 7 0 6 11 7 0 7 19 3 sqlite> select offsets(data) from data where data match 'bug API sqlite> report'; 0 0 7 3 0 2 11 6 0 1 18 3 0 0 7 3 0 2 11 6 0 1 18 3 sqlite> select offsets(data) from data where data match "bug API sqlite> report"; 0 0 7 3 0 2 11 6 0 1 18 3 0 0 7 3 0 2 11 6 0 1 18 3 sqlite> sqlite> select offsets(data) from data where data match '"bug API sqlite> report"'; select offsets(data) from data where data match '"bug sqlite> report API"'; 0 0 7 3 0 1 11 6 0 2 18 3 0 0 7 3 0 1 11 6 0 2 18 3 But how do we match so that we only get one offsets, for the phrase, instead of one for each word? If quotes are a "phrase" shouldn't there be only one match? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Tod Wulff Sent: Wed 12/29/2010 2:53 AM To: 'General Discussion of SQLite Database' Subject: EXTERNAL:[sqlite] FTS4 snippets contains unintended content? Good day. Anyone seeing additional content returned with the snippet text when using the OR (or AND?) MATCH Syntax - i.e. the field contents of the field immediately prior to the snippet(blah) clause? SELECT Target_Name, Target_Content, Target_Description, Target_Link, Parent_Short, Parent_Name, Parent_Link, API_Version, rowid, snippet(RIM_API_FTS_DB, ' ', ' ', '...', -3, -10) FROM RIM_API_FTS_DB WHERE RIM_API_FTS_DB MATCH 'bug report API_Version:374 OR API_Version:UAR' ORDER BY rank(matchinfo(RIM_API_FTS_DB), 50, 10, 25, 15, 0, 0, 0, 0, 0 ) DESC LIMIT 10 OFFSET 0; In the snippet col in each result row, I get the ...[snippet text]... XXX ß Where XXX is the content of the field called out in the MATCH clause. It only shows up when I use the OR MATCH Syntax. I haven't tested with AND yet. [03:41] <Schema> 1: [3.7.4] Release History Of SQLite ( http://j.mp/fHI3HG ) ...generator bug introduced in version 3.6.14 . This bug ...status() interface, in order to report out the amount of... 374 [03:41] <Schema> 2: [3.7.4] SQLite Older News ( http://j.mp/fX8ErX ) ...single notable bug was fixed (ticket #3929). This bug cause...still be considered alpha. Please report any problems. The file... 374 [03:41] <Schema> 3: [3.7.4] How SQLite Is Tested ( http://j.mp/gdqXN1 ) ...Whenever a bug is reported against SQLite, that bug is...0 (all statistics in the report are against that release... 374 [03:41] <Schema> 4: [3.7.4] About SQLite ( http://j.mp/gw9S60 ) ...Interface Spec Development Timeline Report a Bug Wiki SQLite is... 374 [03:41] <Schema> 5: [UAR] SQLite Changes From Version 3.5.9 To 3.6.0 ( http://j.mp/f6M2jc ) ...allows the xAccess() method to report failures. In association with...IN operators is technically a bug fix, not a design... UAR Please chime in if you have seen this. Thanks, in advance. -t _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
