[BDB 11gR2 Beta] Using FTS2 with the BDB SQLite compatibility library code [#18385] [#18222]
Sandra Whitman
sandra.whitman at oracle.com
Tue Mar 9 06:18:48 PST 2010
Hi Emily,
Thanks for making the changes.
Greg just told me that he did give the fts2 patch to Liam. It sounds
like the next time a customer uses the patch it will not be with the
Beta release 5.0.11, so the patch, which is built against mainline
should apply just fine. Since we will be documenting this for general
use, maybe we want to add even a little more clarification to the
steps. Some suggestions are below. I just unpacked db-5.0.11 and I did
find:
ls -ltr sql/sqlite/configure
-r--r--r-- 1 swhitman swhitman 448512 Feb 19 13:54 sql/sqlite/configure
So either we need to change the permissions in the next release, or we
should documented the chmod step. It could be confusing otherwise.
Unless there is a reason not to, I think changing the permissions in the
build would make things easier.
Build FTS2
1. Apply the attached enable_fts2.diff to eliminate conflict definitions
and declarations. (For the Beta release, 5.0.11, the patch needs to be
changed to remove the diffs to sql/generated/sqlite3.c) This patch
replaces fts3 and fts1 source with fts2 source to avoid conflicts.
2. chmod 0555 sql/sqlite/configure
3. From the srchome directory run:
./dist/s_sql
answer "y" to all choices
This step will regenerate sql/generated/sqlite3.c with fts2 support,
fts3 and fts1 support will not be present.
4. cd <srchome>/build_unix and configure as:
../dist/configure CPPFLAGS="-DSQLITE_ENABLE_FTS2
-DSQLITE_ENABLE_BROKEN_FTS2" --enable-sql ...
5. make dbsql. No warnings or errors are expected.
6. nm .libs/libdb_sql-5.0.so. Confirm fts2 has been built in to library.
nm .libs/libdb_sql-5.0.so | grep -i fts2
The output should be like:
002222e0 d fts2Module
000301c0 t fts2Rehash
00022b10 t fts2StrCompare
00022ab0 t fts2StrHash
00035de0 t sqlite3Fts2HashInsert
7. invoke build_unix/dbsql (which you already have in the notes)
Thanks again,
Sandra
Emily Fu wrote:
> Hi Sandra,
>
>> For the documentation maybe you want to clarify the steps a little
>> with a few more details like:
>>
>> chmod 0555 sql/sqlite/configure
>
> The "chmod" here is caused by permission, might not be essential?
>
>> cd <srchome>/dist
>> ./s_sql
>> then
>> cd <srchome>/build_unix
>> ...
>>
>> maybe also mention, run with dbsql
>
> OK. I have updated this part. Thanks for your comments!
>
> Regards,
> Emily
>
>> Emily Fu wrote:
>>> Hi Sandra,
>>>
>>> Thanks for the try! That is great that you could work with it.
>>>
>>> I have documented this at the wiki page
>>> https://sleepycat.oracle.com/trac/wiki/BDBSQL.FullTextSearch.
>>>
>>> Regards
>>> Emily
>>>
>>>
>>>> -----Original Message-----
>>>> From: Sandra Whitman
>>>> Sent: 2010年3月9日 12:20
>>>> To: Emily Fu
>>>> Cc: Alexander Gorrod; Greg Burd; Sleepycat Software Support; George
>>>> Feinberg;
>>>> bdb-beta-feedback at oss.oracle.com; Sandra Whitman
>>>> Subject: Re: [BDB 11gR2 Beta] Using FTS2 with the BDB SQLite
>>>> compatibility
>>>> library code [#18385] [#18222]
>>>>
>>>> Hi Emily,
>>>>
>>>> Omitting sqlite3.c from your patch, I was able to get fts2
>>>> support, as
>>>> below. I will find out if the patch is actually needed or if Liam
>>>> figured something out without it. If he does need the patch, or if
>>>> another Beta customer did, we would need the patch built on top of
>>>> db-5.0.11 or create a patch without sqlite3.c.
>>>>
>>>> ./dbsql
>>>> Berkeley DB 11g Release 2, library version 11.2.5.0.11: (February
>>>> 19, 2010)
>>>> Enter ".help" for instructions
>>>> Enter SQL statements terminated with a ";"
>>>> dbsql> create virtual table recipe using FTS2(name, ingredients);
>>>> dbsql> insert into recipe (name, ingredients) values ('broccoli stew',
>>>> 'broccoli peppers cheese tomatoes');
>>>> dbsql> insert into recipe (name, ingredients) values ('pumpkin stew',
>>>> 'pumpkin onions garlic celery');
>>>> dbsql> insert into recipe (name, ingredients) values ('broccoli pie',
>>>> 'broccoli cheese onions flour');
>>>> dbsql> insert into recipe (name, ingredients) values ('pumpkin pie',
>>>> 'pumpkin sugar flour butter');
>>>> dbsql> select rowid, name, ingredients from recipe where name match
>>>> 'pie';
>>>> 3|broccoli pie|broccoli cheese onions flour
>>>> 4|pumpkin pie|pumpkin sugar flour butter
>>>>
>>>>
>>>> Thanks,
>>>> Sandra
>>>>
>>>> Emily Fu wrote:
>>>>
>>>>> Hi Sandra,
>>>>>
>>>>> Since fts2 is no longer supported in SQLite 3.5.X and above, when
>>>>> enable fts2 in building process, we would see compile errors in
>>>>> sqlite3.c (but not in our BDB source files). We need to manually fix
>>>>> those conflicts.
>>>>>
>>>>> I have attached a patch for the fix. Could you please try it with the
>>>>> following steps?
>>>>> 1) Edit SQLite source code to eliminate conflict definitions and
>>>>> declarations. You could just apply my patch.
>>>>> 2) Edit sql/sqlite/tool/mksqlite3c.tcl. The files' sequence listed in
>>>>> mksqlite3.tcl is not random so please take care of their order. The
>>>>> patch includes this step as well.
>>>>> 3) Run dist/s_sql. The sql/generated/sqlite3.c will be re-generated
>>>>> with fts2, but not fts3.
>>>>> 4) ../dist/configure CPPFLAGS="-DSQLITE_ENABLE_FTS2
>>>>> -DSQLITE_ENABLE_BROKEN_FTS2" --enable-sql ...
>>>>> 5) make dbsql. I guess you won't see any warning or error here.
>>>>> 6) nm .libs/libdb_sql-5.0.so. Confirm fts2 has been built in to
>>>>> library.
>>>>> 7) Try any example, such as:
>>>>>
>>>>> create virtual table recipe using FTS2(name, ingredients);
>>>>> insert into recipe (name, ingredients) values ('broccoli stew',
>>>>> 'broccoli peppers cheese tomatoes');
>>>>> insert into recipe (name, ingredients) values ('pumpkin stew',
>>>>> 'pumpkin onions garlic celery');
>>>>> insert into recipe (name, ingredients) values ('broccoli pie',
>>>>> 'broccoli cheese onions flour');
>>>>> insert into recipe (name, ingredients) values ('pumpkin pie',
>>>>> 'pumpkin
>>>>> sugar flour butter');
>>>>> select rowid, name, ingredients from recipe where name match 'pie';
>>>>>
>>>>> If working correctly, it returns:
>>>>> 3|broccoli pie|broccoli cheese onions flour
>>>>> 4|pumpkin pie|pumpkin sugar flour butter
>>>>>
>>>>> Could you please confirm the above process with the attached patch?
>>>>> Also, could you please ask what SQLite version they are using?
>>>>>
>>>>> Greg, Michael, Alex, if they are using different SQLite version from
>>>>> us, as fts2 is no longer supported and the SQLite code in BDB needs a
>>>>> little change, should we send them the patch to upgrade those
>>>>> conflict
>>>>> parts?
>>>>>
>>>>> Regards,
>>>>> Emily
>>>>>
>>>>> On 2010-3-6 11:27, Sandra Whitman wrote:
>>>>>
>>>>>> Hi Emily,
>>>>>>
>>>>>>
>>>>>> If we are going to try to build fts2 using
>>>>>> sqlite/tool/mksqlite3c.tcl, here is what I've done. The files I
>>>>>> changed are attached. If this is the way to go, I'll keep working on
>>>>>> resolving the remaining compilation errors.
>>>>>>
>>>>>>
>>>>>> 0. ../configure
>>>>>>
>>>>>>
>>>> CFLAGS=-DSQLITE_ENABLE_BROKEN_FTS2,-DSQLITE_ENABLE_FTS2,-DSQLITE_
>>>> MUTEX_PTHREADS
>>>>
>>>>>> 1. edit sql/sqlite/tool/mksqlite3c.tcl to remove fts1/fts3
>>>>>> support to
>>>>>> avoid conflicting functions, and add fts2 in.
>>>>>>
>>>>>>
>>>>>> 2. edit Makefile with includes to pickup db.h, otherwise the BDB
>>>>>> symbols are undefined.
>>>>>> TCC = gcc -DSQLITE_OS_UNIX=1 -I. -I${TOP}/src -I${TOP}/../adapter
>>>>>> TCC += -I${TOP}/../../build_unix -I${TOP}/../../dist
>>>>>>
>>>>>>
>>>>>> 3. edit in sql/sqlite/src/main.c, hash.c, util.c to add fts2 support
>>>>>> into main.c, and remove routines in hash.c and util.c that conflict
>>>>>> with fts2 versions. In hash.c and util.c I added #ifndef
>>>>>> SQLITE_ENABLE_FTS2 around the routines that conflict with fts2
>>>>>> versions
>>>>>>
>>>>>> 4. There are some errors around the routines below. Mostly they are
>>>>>> fixed by editing sqlite3.c to change SQLITE_PRIVATE/static to
>>>>>> SQLITE_API. Otherwise I needed to add a cast as follows to
>>>>>> findElementGivenHash:
>>>>>> (fts2HashElem *)findElementGivenHash
>>>>>>
>>>>>> sqlite3.c:87016: error: conflicting types for ‘sqlite3PutVarint’
>>>>>> sqlite3.c:10075: error: previous declaration of ‘sqlite3PutVarint’
>>>>>> was here
>>>>>> sqlite3.c:87049: error: conflicting types for ‘sqlite3GetVarint’
>>>>>> sqlite3.c:10077: error: previous declaration of ‘sqlite3GetVarint’
>>>>>> was here
>>>>>> sqlite3.c:93965: error: static declaration of ‘strHash’ follows
>>>>>> non-static declaration
>>>>>> sqlite3.c:19467: error: previous implicit declaration of ‘strHash’
>>>>>> was here
>>>>>> sqlite3.c:94038: warning: conflicting types for ‘insertElement’
>>>>>> sqlite3.c:94038: error: static declaration of ‘insertElement’
>>>>>> follows
>>>>>> non-static declaration
>>>>>> sqlite3.c:19528: error: previous implicit declaration of
>>>>>> ‘insertElement’ was here
>>>>>> sqlite3.c:94062: warning: conflicting types for ‘rehash’
>>>>>> sqlite3.c:94062: error: static declaration of ‘rehash’ follows
>>>>>> non-static declaration
>>>>>> sqlite3.c:19522: error: previous implicit declaration of ‘rehash’
>>>>>> was
>>>>>> here
>>>>>> sqlite3.c:94090: error: conflicting types for ‘findElementGivenHash’
>>>>>> sqlite3.c:19471: error: previous implicit declaration of
>>>>>> ‘findElementGivenHash’ was here
>>>>>> sqlite3.c:94117: warning: conflicting types for
>>>>>> ‘removeElementGivenHash’
>>>>>> sqlite3.c:94117: error: static declaration of
>>>>>> ‘removeElementGivenHash’ follows non-static declaration
>>>>>> sqlite3.c:19506: error: previous implicit declaration of
>>>>>> ‘removeElementGivenHash’ was
>>>>>>
>>>>>> These should be ultimately fixed in the source, but for now I edited
>>>>>> sqlite3.c.
>>>>>>
>>>>>>
>>>>>> 5. After all of this there are a few remaining compilation errors. A
>>>>>> few I can not figure out yet. One looks to be redefinition problem
>>>>>> between errno.h and the dbinc/errno.h.
>>>>>>
>>>>>>
>>>>>> Thanks,
>>>>>> Sandra
>>>>>>
>>>>>> Sandra Whitman wrote:
>>>>>>
>>>>>>> Hello,
>>>>>>>
>>>>>>>
>>>>>>> As of yesterday, Liam was still unable to build the fts2 support.
>>>>>>> After speaking with Alex and Emily, it looks like there are a
>>>>>>> couple
>>>>>>> of ways to do this. There are quite a few issues either way. I
>>>>>>> need some guidance at this point to determine which way is really
>>>>>>> the right way to do this.
>>>>>>>
>>>>>>> 1. My first try was basically to edit
>>>>>>> db-5.0.11/sql/sqlite/Makefile.in to build fts2 and then make in
>>>>>>> build_unix/sql. I had to add the BDB library built in build_unix
>>>>>>> to the link line for libsqlite3.so to resolve a host of BDB
>>>>>>> symbols.
>>>>>>>
>>>>>>> This results in a sqlite3 which at least has preliminary fts2
>>>>>>> support:
>>>>>>>
>>>>>>> db-5.0.11/build_unix/sql
>>>>>>> [swhitman at swhitman-lap sql]$ ./sqlite3
>>>>>>> Berkeley DB 11g Release 2, library version 11.2.5.0.11: (February
>>>>>>> 19, 2010)
>>>>>>> Enter ".help" for instructions
>>>>>>> Enter SQL statements terminated with a ";"
>>>>>>> dbsql> CREATE VIRTUAL TABLE fortune using FTS2(class, text);
>>>>>>> dbsql> insert into fortune (class, text) values ('class1',
>>>>>>> 'text1');
>>>>>>> dbsql> insert into fortune (class, text) values ('class2',
>>>>>>> 'text2');
>>>>>>> dbsql> select rowid, class, text from fortune where class match
>>>>>>> 'class1';
>>>>>>> 1|class1|text1
>>>>>>> dbsql>
>>>>>>>
>>>>>>>
>>>>>>> Alex pointed out that this method is not correct because the
>>>>>>> build_unix/sql build is not using the amalgamation, where as the
>>>>>>> build in build_unix is. Hence there is a conflict and so the above
>>>>>>> is not the proper build method.
>>>>>>>
>>>>>>>
>>>>>>> 2. Based on Alex's input I tried:
>>>>>>>
>>>>>>> $ cd dist
>>>>>>> $ ./s_sql
>>>>>>> (say yes to any permission related questions)
>>>>>>> $ cd ../build_unix
>>>>>>> $ ../dist/configure --enable-sql_compat
>>>>>>> $ make
>>>>>>>
>>>>>>> But here we've incorporated no fts2 support:
>>>>>>> ./sqlite3
>>>>>>> Berkeley DB 11g Release 2, library version 11.2.5.0.11: (February
>>>>>>> 19, 2010)
>>>>>>> Enter ".help" for instructions
>>>>>>> Enter SQL statements terminated with a ";"
>>>>>>> dbsql> CREATE VIRTUAL TABLE fortune using FTS2(class, text);
>>>>>>> Error: no such module: FTS2
>>>>>>>
>>>>>>>
>>>>>>> This might be the way to go, but how do I get fts2 in there?
>>>>>>>
>>>>>>>
>>>>>>> 3. Based on discussion with Emily I tried building with
>>>>>>> /sql/sqlite/tool/mksqlite3c.tcl editing this for fts2 support.
>>>>>>>
>>>>>>>
>>>>>>> There are like a billion errors building this way. I've cleaned up
>>>>>>> a lot, like 3/4 of a billion, but some still remain, and I'm not
>>>>>>> sure this is the way to go. The cleanup for fts2 support involves
>>>>>>> some simple and some less simple changes:
>>>>>>>
>>>>>>>
>>>>>>> 1. editing the makefile to remove fts1 and fts3 support, otherwise
>>>>>>> there are conflicting functions with the same names, and adding
>>>>>>> include paths for db.h
>>>>>>>
>>>>>>> 2. configuring with:
>>>>>>> ../configure
>>>>>>>
>>>>>>>
>>>> CFLAGS=-DSQLITE_ENABLE_BROKEN_FTS2,-DSQLITE_ENABLE_FTS2,-DSQLITE_
>>>> MUTEX_PTHREADS
>>>>
>>>>>>> 3. changing src/main.c, src/util.c, src/hash.c to ifdef to support
>>>>>>> fts2 and ifdef out functions that conflict with the fts2
>>>>>>> definitions.
>>>>>>>
>>>>>>> 4. I still get various combinations of
>>>>>>> SQLITE_PRIVATE/SQLITE_API/static errors that seem to need to be
>>>>>>> manually edited from sqlite3.c to avoid conflicts. There are also
>>>>>>> some casts that need to be added for fts2 functionality.
>>>>>>>
>>>>>>> There are still errors regarding errno.h and dbinc/errno.h which I
>>>>>>> have not yet figured out. But I want to be sure this is a
>>>>>>> reasonable approach before I spend any more time. I don't
>>>>>>> think it
>>>>>>> is.
>>>>>>>
>>>>>>>
>>>>>>> At this point, is there any of the above or something else that
>>>>>>> seems like the best way to incorporate fts2? I will work on
>>>>>>> whatever that choice is, but I am not sure at the moment which
>>>>>>> that is.
>>>>>>>
>>>>>>>
>>>>>>> Thanks very much,
>>>>>>> Sandra
>>>>>>>
>>>>>>> Alex Gorrod wrote:
>>>>>>>
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> On 4/03/2010 3:07 AM, Hexxeh wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>> I think the other problem encountered in the build was these
>>>>>>>>> functions
>>>>>>>>> that haven't been exposed:
>>>>>>>>>
>>>>>>>>> - Exposed three functions that deal with unused file
>>>>>>>>> descriptors in
>>>>>>>>> 152
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l152>
>>>>
>>>>>>>>> os_unix.c, to allow Chromium's Posix VFS implementation in
>>>>>>>>> 153
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l153>
>>>>
>>>>>>>>>
>>>> WebKit/WebCore/platform/sql/chromium/SQLiteFileSystemChromiumPosix.cpp
>>>>
>>>>>>>>> 154
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l154>
>>>>
>>>>>>>>> to correctly implement the "unused file descriptors" logic
>>>>>>>>> in the
>>>>>>>>> 155
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l155>
>>>>
>>>>>>>>> xDlOpen() method. The new functions are
>>>>>>>>> 156
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l156>
>>>>
>>>>>>>>> chromium_sqlite3_get_reusable_file_handle(),
>>>>>>>>> 157
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l157>
>>>>
>>>>>>>>> chromium_sqlite3_update_reusable_file_handle() and
>>>>>>>>> 158
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l158>
>>>>
>>>>>>>>> chromium_sqlite3_destroy_reusable_file_handle(). Also, added
>>>>>>>>>
>>>> the
>>>>
>>>>>>>>> 159
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l159>
>>>>
>>>>>>>>> chromium_sqlite3_fill_in_unix_sqlite3_file() function that
>>>>>>>>> calls
>>>>>>>>> 160
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l160>
>>>>
>>>>>>>>> fillInUnixFile(), which will be made static again as soon
>>>>>>>>> as a
>>>>>>>>> 161
>>>>>>>>>
>>>>>>>>>
>>>> </cgi-bin/gitweb.cgi?p=chromium.git;a=blob;f=third_party/sqlite/README.chr
>>>>
>>>> omium;h=6953e64e7bf0c462c10d0c7b84ff0052638f8e46;hb=HEAD#l161>
>>>>
>>>>>>>>> WebKit patch using the new function lands.
>>>>>>>>>
>>>>>>>>>
>>>>>>>> Could you clarify what this patch is used for please. I'll add
>>>>>>>> some
>>>>>>>> context. Berkeley DB SQL does not use the SQLite VFS code - it is
>>>>>>>> one of the components that has been replaced.
>>>>>>>>
>>>>>>>> So, if those functions need to be exposed so that you can modify
>>>>>>>> the behavior of SQLite (by implementing a custom VFS), then your
>>>>>>>> changes should no longer be necessary.
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Alex
>>>>>>>>
>>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> BDB-BETA-FEEDBACK mailing list
>>>>>>> BDB-BETA-FEEDBACK at oss.oracle.com
>>>>>>> http://oss.oracle.com/mailman/listinfo/bdb-beta-feedback
>>>>>>>
>>>>>>>
>>
>> _______________________________________________
>> BDB-BETA-FEEDBACK mailing list
>> BDB-BETA-FEEDBACK at oss.oracle.com
>> http://oss.oracle.com/mailman/listinfo/bdb-beta-feedback
More information about the BDB-BETA-FEEDBACK
mailing list