[BDB 11gR2 Beta] Using FTS2 with the BDB SQLite compatibility library code [#18385] [#18222]
Emily Fu
emily.fu at oracle.com
Mon Mar 8 21:13:45 PST 2010
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