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