Full-text Search with SQLite

Do you know what version of SQLite supports that function? Cloverleaf 6.1 embeds SQLite 3.7.10.

Wrong syntax Hai.  You have:

CREATE VIRTUAL TABLE data USING fts3();

Try it like this:

CREATE VIRTUAL TABLE docs USING fts3(title, body);

You have to define columns.  Here is what I get

C:healthvisioncis5.8integratorlevel_3tclprocsLIB>sqlite test.db

SQLite version 3.6.4

Enter “.help” for instructions

Enter SQL statements terminated with a “;”

sqlite> CREATE VIRTUAL TABLE docs USING fts3(title, body);

sqlite> .schema

CREATE VIRTUAL TABLE docs USING fts3(title, body);

CREATE TABLE docs_content(  docid INTEGER PRIMARY KEY,c0title,c1body);

CREATE TABLE docs_segdir(  level integer,  idx integer,  start_block integer,  leaves_end_block integer,  end_block integer,  root blob,  primary key(level, idx));

CREATE TABLE docs_segments(  blockid INTEGER PRIMARY KEY,  block blob);

sqlite>

I don’t think he had it wrong, Charlie.

Ah.  So you are logged in as a cloverleaf user on a unix-like system.  In that case, you should NOT use sqlite3.

When you logged in with all of the setup associated with being a Cloverleaf user, you use sqlite to access Cloverleaf related SQLite stuff.  If you wanted to access your system’s sqlite shell, you’d probably use sqlite3 (or an absolute path.)

### ‘which’ shows the difference: I’m logged in as a Cloverleaf user ###

$ which sqlite

/opt/cloverleaf/cis6.0/integrator/tcl/bin/sqlite

$ which sqlite3

/usr/bin/sqlite3

If you’re on RedHat, it’s very likely that the Cloverleaf SQLite is ahead of your system SQLite, possibly by as much as a version number!

Okay, I’m flummoxed.  I wonder if the SQLite in cl 5.7 was compiled without fts3?  It’s a config switch to the compiler, and it’s not automatic.  fts3 has been there since SQLite 3.5, but I don’t know when it started being the default in Cloverleaf.

Max or Charlie?

What Cloverleaf version Hai?  We do not install sqlite under the /usr directory.

If I run: ‘which sqlite’ on my Linux box, I get

/hci/cis6.0/integrator/tcl/bin/sqlite

Check under $HCIROOT/tc/bin

As for when the fts was compiled in, I don’t know since I have never had a reason to use it.  I have versions back to 5.8 and it is there.  If you are still running 5.7 you are well overdue for an upgrade  🙂