342915 - Update to version of SQLite that supports full-text indexing

Status: RESOLVED DUPLICATE of bug 413589

(Core :: SQLite and Embedded Database Bindings, enhancement)

Description

[Pam Greene]

SQLite doesn't yet support full-text indexing, but it plans to (http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex).  When it does, we'll need to incorporate that version into our tree in order to index full pages.

Comment 1

[Bill McGonigle (not currently reading bugmail; please contact directly)]

It looks like fts1 went in October and fts2 went in January as an 'experimental' module (not yet in the main build as of 2007/02/23).  They're recommending not deploying fts1 as fts2 performance is much better.

http://www.sqlite.org/cvstrac/wiki?p=FtsTwo

Comment 2

[Pam Greene]

Unfortunately, I don't think I'll have the time to lead this effort.

Comment 3

[Steffen Wilberg]

sqlite has been updated to 3.3.17 in bug 341137. But fts2 is still disabled (there's an #ifdef SQLITE_ENABLE_FTS2), and the actual fts2 code (http://www.sqlite.org/cvstrac/dir?d=sqlite/ext/fts2) is not even in the tree.

Comment 4

[Shawn Wilsher :sdwilsh]

Does this require any compile time defines?

Comment 5

[Dietrich Ayala (:dietrich)]

I believe so. Also, iirc the amalgamation patch doesn't contain fts2, we'll probably need to patch that in locally.

Comment 6

[Kunal Kumar D Jain]

Attachment: This is the amalgamated version of fts2 with the makefile

goes to db/sqlite3/src

Comment 7

[Kunal Kumar D Jain]

Two points:
1) FTS2 runs only when Shared Cached Mode is disabled.
2) The patch has so far got me only segmentation fault when trying to execute select statement, insertion and creation of table went alright. I'll debug as soon as possible

Comment 8

[Shawn Wilsher :sdwilsh]

Comment on attachment 277199 [details]
This is the amalgamated version of fts2 with the makefile

what type of file did you attach?  that's clearly not a patch ;)

Comment 9

[Kunal Kumar D Jain]

I am not familiar with the process. I just attached all the source and the makefile.

(In reply to comment #8)
> (From update of attachment 277199 [details])
> what type of file did you attach?  that's clearly not a patch ;)
> 

Comment 10

[Steffen Wilberg]

Comment on attachment 277199 [details]
This is the amalgamated version of fts2 with the makefile

A patch is a text file containing the differences between files.
This is, as the filename says, a .tar.bz2 file.
And the uncompressed fts2amal.c is rather large: 1.3 MB.

Comment 11

[Kunal Kumar D Jain]

Attachment: Test case for https://bugzilla.mozilla.org/attachment.cgi?id=277199 and it seemed to have passed

Comment 12

[Shawn Wilsher :sdwilsh]

fyi, for storage you should be using xpcshell test cases unless you can't actually test it with that.  The C++ test cases are not well maintained, and do not get run automatically after checkins.

Comment 13

[Kunal Kumar D Jain]

Attachment: Test case for https://bugzilla.mozilla.org/attachment.cgi?id=277199 and it seemed to have passed

Comment 14

[Kunal Kumar D Jain]

Attachment: Makefile to run the patch 278700

Comment 15

[Dietrich Ayala (:dietrich)]

Attachment: patchified - w/ xpcshell tests

here's the amalgamated FTS2 module in patch form, with the tests converted to js. per Kunal, it's large because:

Kunal Jain: it is 30,000 lines
Kunal Jain: more than 50% of it comments
Kunal Jain: grep "\*\*" fts2amal.c | wc -l
Kunal Jain: 19483
Kunal Jain: about 20,000 lines comment

however, turns out that shared cache mode and external modules don't jive:

*** CHECK FAILED: unable to create fts2 virtual table: Cannot use virtual tables in shared-cache mode

where we set shared cache mode:

http://lxr.mozilla.org/mozilla/source/storage/src/mozStorageService.cpp#90

Comment 16

[Shawn Wilsher :sdwilsh]

hrm, so I think we really wanted shared cache at one point, but I wonder if we really have multiple DB connections pointing to the same database...

Also, are there instructions on how to generate the amalgation with an upgraded sqlite?  I *really* don't want to make it hard to upgrade again because we may end up like before being way behind because upgrading was difficult.

Comment 17

[Dietrich Ayala (:dietrich)]

(In reply to comment #16)
> hrm, so I think we really wanted shared cache at one point, but I wonder if we
> really have multiple DB connections pointing to the same database...
> 
> Also, are there instructions on how to generate the amalgation with an upgraded
> sqlite?  I *really* don't want to make it hard to upgrade again because we may
> end up like before being way behind because upgrading was difficult.
> 

Kunal, can you please detail how you created the amalgamated fts2 file?

Comment 18

[Kunal Kumar D Jain]

There is a mkfts2amal.tcl file coming with the sqlite distribution. So add the following entry to the makefile,

fts2amal.c:     target_source $(TOP)/ext/fts2/mkfts2amal.tcl
        tcl $(TOP)/ext/fts2/mkfts2amal.tcl

And run, make fts2amal.c to generate the amalgamated version. The logic behind this is that mkfts2amal.tcl requires presence of all the sources in a directory called tsrc, which target_source creates.

IIRC, I did not make any change elsewhere.

Comment 19

[Shawn Wilsher :sdwilsh]

could you kindly update the readme file then?
http://mxr.mozilla.org/seamonkey/source/db/sqlite3/README.MOZILLA

Comment 20

[Kunal Kumar D Jain]

Attachment: fts2 w xpcshell test, README.MOZILLA updated with comments on making future upgrades

The patch is huge and hence compressed

Comment 21

[Shawn Wilsher :sdwilsh]

So, we recently updated sqlite recently to 3.5.4.  Would you be willing to update this patch to trunk?

Comment 22

[Steffen Wilberg]

Parallel work happens in bug 413589.