[BDB 11gR2 Beta] Using FTS2 with the BDB SQLite compatibility library code [#18385] [#18222]
Sandra Whitman
sandra.whitman at oracle.com
Mon Mar 8 21:04:39 PST 2010
For the documentation maybe you want to clarify the steps a little with
a few more details like:
chmod 0555 sql/sqlite/configure
cd <srchome>/dist
./s_sql
then
cd <srchome>/build_unix
...
maybe also mention, run with dbsql
Thanks,
Sandra
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
>>>>>
>>>>>
More information about the BDB-BETA-FEEDBACK
mailing list