[odb-users] Full text search in SQLite

[Boris Kolpackov]

Hi Philipp,

Philipp Maluta <iensoreus at gmail.com> writes:

> Suppose there is no particular pragma but maybe I can intrude to the
> table creation process and crewe a virtual table like
> 
> CREATE VIRTUAL TABLE data USING fts3();

ODB doesn't know anything about SQLite virtual tables or the FTS
extension (yet; maybe we will support this directly in the future).
So, yes, the way you can achieve what you want is by creating this
table yourself manually. Assuming you are using the embedded database
schema, here are the steps that you can take:

1. Place the class that you want to map to the FTS table into
   a separate header file (say data.hxx):

#pragma db object table("data") no_id
class data
{
  std::string content;
};

  Note that here we have a persistent class without object id.
  You could probably also map the implicit rowid column to object
  id if you wanted to.

2. Compile this header without the --generate-schema option. This
   way the ODB generated code won't try to create the 'data' table.

3. In your application create the data FTS table manually:

{
  odb::transaction t (db->begin ());

  // Create the standard tables.
  //
  odb::schema_catalog::create_schema (*db);

  // Create the FTS table manually.
  //
  db->execute ("CREATE VIRTUAL TABLE data USING fts3()");

  t.commit ();
}

Let me know if there are any issues.

Boris