FTS4 CONTENT OPTION

  Normally, in order to create a full-text index on a dataset, the FTS4
  module stores a copy of all indexed documents in a specially created
  database table.

  As of SQLite version 3.7.9, FTS4 supports a new option - "content" -
  designed to extend FTS4 to support the creation of full-text indexes where:

    * The indexed documents are not stored within the SQLite database
      at all (a "contentless" FTS4 table), or

    * The indexed documents are stored in a database table created and
      managed by the user (an "external content" FTS4 table).

  Because the indexed documents themselves are usually much larger than
  the full-text index, the content option can sometimes be used to achieve
  significant space savings.

CONTENTLESS FTS4 TABLES

  In order to create an FTS4 table that does not store a copy of the indexed
  documents at all, the content option should be set to an empty string.
  For example, the following SQL creates such an FTS4 table with three
  columns - "a", "b", and "c":

    CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c);

  Data can be inserted into such an FTS4 table using an INSERT statements.
  However, unlike ordinary FTS4 tables, the user must supply an explicit
  integer docid value. For example:

    -- This statement is Ok:
    INSERT INTO t1(docid, a, b, c) VALUES(1, 'a b c', 'd e f', 'g h i');

    -- This statement causes an error, as no docid value has been provided:
    INSERT INTO t1(a, b, c) VALUES('j k l', 'm n o', 'p q r');

  It is not possible to UPDATE or DELETE a row stored in a contentless FTS4
  table. Attempting to do so is an error.

  Contentless FTS4 tables also support SELECT statements. However, it is
  an error to attempt to retrieve the value of any table column other than
  the docid column. The auxiliary function matchinfo() may be used, but
  snippet() and offsets() may not. For example:

    -- The following statements are Ok:
    SELECT docid FROM t1 WHERE t1 MATCH 'xxx';
    SELECT docid FROM t1 WHERE a MATCH 'xxx';
    SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'xxx';

    -- The following statements all cause errors, as the value of columns
    -- other than docid are required to evaluate them.
    SELECT * FROM t1;
    SELECT a, b FROM t1 WHERE t1 MATCH 'xxx';
    SELECT docid FROM t1 WHERE a LIKE 'xxx%';
    SELECT snippet(t1) FROM t1 WHERE t1 MATCH 'xxx';

  Errors related to attempting to retrieve column values other than docid
  are runtime errors that occur within sqlite3_step(). In some cases, for
  example if the MATCH expression in a SELECT query matches zero rows, there
  may be no error at all even if a statement does refer to column values
  other than docid.

EXTERNAL CONTENT FTS4 TABLES

  An "external content" FTS4 table is similar to a contentless table, except
  that if evaluation of a query requires the value of a column other than
  docid, FTS4 attempts to retrieve that value from a table (or view, or
  virtual table) nominated by the user (hereafter referred to as the "content
  table"). The FTS4 module never writes to the content table, and writing
  to the content table does not affect the full-text index. It is the
  responsibility of the user to ensure that the content table and the
  full-text index are consistent.

  An external content FTS4 table is created by setting the content option
  to the name of a table (or view, or virtual table) that may be queried by
  FTS4 to retrieve column values when required. If the nominated table does
  not exist, then an external content table behaves in the same way as
  a contentless table. For example:

    CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
    CREATE VIRTUAL TABLE t3 USING fts4(content="t2", a, c);

  Assuming the nominated table does exist, then its columns must be the same
  as or a superset of those defined for the FTS table.

  When a users query on the FTS table requires a column value other than
  docid, FTS attempts to read this value from the corresponding column of
  the row in the content table with a rowid value equal to the current FTS
  docid. Or, if such a row cannot be found in the content table, a NULL
  value is used instead. For example:

    CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c, d);
    CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);

    INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
    INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
    INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;

    -- The following query returns a single row with two columns containing
    -- the text values "i j" and "k l".
    --
    -- The query uses the full-text index to discover that the MATCH
    -- term matches the row with docid=3. It then retrieves the values
    -- of columns b and c from the row with rowid=3 in the content table
    -- to return.
    --
    SELECT * FROM t3 WHERE t3 MATCH 'k';

    -- Following the UPDATE, the query still returns a single row, this
    -- time containing the text values "xxx" and "yyy". This is because the
    -- full-text index still indicates that the row with docid=3 matches
    -- the FTS4 query 'k', even though the documents stored in the content
    -- table have been modified.
    --
    UPDATE t2 SET b = 'xxx', c = 'yyy' WHERE rowid = 3;
    SELECT * FROM t3 WHERE t3 MATCH 'k';

    -- Following the DELETE below, the query returns one row containing two
    -- NULL values. NULL values are returned because FTS is unable to find
    -- a row with rowid=3 within the content table.
    --
    DELETE FROM t2;
    SELECT * FROM t3 WHERE t3 MATCH 'k';

  When a row is deleted from an external content FTS4 table, FTS4 needs to
  retrieve the column values of the row being deleted from the content table.
  This is so that FTS4 can update the full-text index entries for each token
  that occurs within the deleted row to indicate that that row has been
  deleted. If the content table row cannot be found, or if it contains values
  inconsistent with the contents of the FTS index, the results can be difficult
  to predict. The FTS index may be left containing entries corresponding to the
  deleted row, which can lead to seemingly nonsensical results being returned
  by subsequent SELECT queries. The same applies when a row is updated, as
  internally an UPDATE is the same as a DELETE followed by an INSERT.

  Instead of writing separately to the full-text index and the content table,
  some users may wish to use database triggers to keep the full-text index
  up to date with respect to the set of documents stored in the content table.
  For example, using the tables from earlier examples:

    CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN
      DELETE FROM t3 WHERE docid=old.rowid;
    END;
    CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN
      DELETE FROM t3 WHERE docid=old.rowid;
    END;

    CREATE TRIGGER t2_bu AFTER UPDATE ON t2 BEGIN
      INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
    END;
    CREATE TRIGGER t2_bd AFTER INSERT ON t2 BEGIN
      INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
    END;

  The DELETE trigger must be fired before the actual delete takes place
  on the content table. This is so that FTS4 can still retrieve the original
  values in order to update the full-text index. And the INSERT trigger must
  be fired after the new row is inserted, so as to handle the case where the
  rowid is assigned automatically within the system. The UPDATE trigger must
  be split into two parts, one fired before and one after the update of the
  content table, for the same reasons.

  FTS4 features a special command similar to the 'optimize' command that
  deletes the entire full-text index and rebuilds it based on the current
  set of documents in the content table. Assuming again that "t3" is the
  name of the external content FTS4 table, the command is:

    INSERT INTO t3(t3) VALUES('rebuild');

  This command may also be used with ordinary FTS4 tables, although it may
  only be useful if the full-text index has somehow become corrupt. It is an
  error to attempt to rebuild the full-text index maintained by a contentless
  FTS4 table.

1. OVERVIEW

  This README file describes the syntax of the arguments that may be passed to
  the FTS3 MATCH operator used for full-text queries. For example, if table
  "t1" is an Fts3 virtual table, the following SQL query:

    SELECT * FROM t1 WHERE <col> MATCH <full-text query>

  may be used to retrieve all rows that match a specified for full-text query.
  The text "<col>" should be replaced by either the name of the fts3 table
  (in this case "t1"), or by the name of one of the columns of the fts3
  table. <full-text-query> should be replaced by an SQL expression that
  computes to a string containing an Fts3 query.

  If the left-hand-side of the MATCH operator is set to the name of the
  fts3 table, then by default the query may be matched against any column
  of the table. If it is set to a column name, then by default the query
  may only match the specified column. In both cases this may be overriden
  as part of the query text (see sections 2 and 3 below).

  As of SQLite version 3.6.8, Fts3 supports two slightly different query
  formats; the standard syntax, which is used by default, and the enhanced
  query syntax which can be selected by compiling with the pre-processor
  symbol SQLITE_ENABLE_FTS3_PARENTHESIS defined.

    -DSQLITE_ENABLE_FTS3_PARENTHESIS

2. STANDARD QUERY SYNTAX

  When using the standard Fts3 query syntax, a query usually consists of a
  list of terms (words) separated by white-space characters. To match a
  query, a row (or column) of an Fts3 table must contain each of the specified
  terms. For example, the following query:

    <col> MATCH 'hello world'

  matches rows (or columns, if <col> is the name of a column name) that
  contain at least one instance of the token "hello", and at least one
  instance of the token "world". Tokens may be grouped into phrases using
  quotation marks. In this case, a matching row or column must contain each
  of the tokens in the phrase in the order specified, with no intervening
  tokens. For example, the query:

    <col> MATCH '"hello world" joe"

  matches the first of the following two documents, but not the second or
  third:

    "'Hello world', said Joe."
    "One should always greet the world with a cheery hello, thought Joe."
    "How many hello world programs could their be?"

  As well as grouping tokens together by phrase, the binary NEAR operator
  may be used to search for rows that contain two or more specified tokens
  or phrases within a specified proximity of each other. The NEAR operator
  must always be specified in upper case. The word "near" in lower or mixed
  case is treated as an ordinary token. For example, the following query:

    <col> MATCH 'engineering NEAR consultancy'

  matches rows that contain both the "engineering" and "consultancy" tokens
  in the same column with not more than 10 other words between them. It does
  not matter which of the two terms occurs first in the document, only that
  they be seperated by only 10 tokens or less. The user may also specify
  a different required proximity by adding "/N" immediately after the NEAR
  operator, where N is an integer. For example:

    <col> MATCH 'engineering NEAR/5 consultancy'

  searches for a row containing an instance of each specified token seperated
  by not more than 5 other tokens. More than one NEAR operator can be used
  in as sequence. For example this query:

    <col> MATCH 'reliable NEAR/2 engineering NEAR/5 consultancy'

  searches for a row that contains an instance of the token "reliable"
  seperated by not more than two tokens from an instance of "engineering",
  which is in turn separated by not more than 5 other tokens from an
  instance of the term "consultancy". Phrases enclosed in quotes may
  also be used as arguments to the NEAR operator.

  Similar to the NEAR operator, one or more tokens or phrases may be
  separated by OR operators. In this case, only one of the specified tokens
  or phrases must appear in the document. For example, the query:

    <col> MATCH 'hello OR world'

  matches rows that contain either the term "hello", or the term "world",
  or both. Note that unlike in many programming languages, the OR operator
  has a higher precedence than the AND operators implied between white-space
  separated tokens. The following query matches documents that contain the
  term 'sqlite' and at least one of the terms 'fantastic' or 'impressive',
  not those that contain both 'sqlite' and 'fantastic' or 'impressive':

    <col> MATCH 'sqlite fantastic OR impressive'

  Any token that is part of an Fts3 query expression, whether or not it is
  part of a phrase enclosed in quotes, may have a '*' character appended to
  it. In this case, the token matches all terms that begin with the characters
  of the token, not just those that exactly match it. For example, the
  following query:

    <col> MATCH 'sql*'

  matches all rows that contain the term "SQLite", as well as those that
  contain "SQL".

  A token that is not part of a quoted phrase may be preceded by a '-'
  character, which indicates that matching rows must not contain the
  specified term. For example, the following:

    <col> MATCH '"database engine" -sqlite'

  matches rows that contain the phrase "database engine" but do not contain
  the term "sqlite". If the '-' character occurs inside a quoted phrase,
  it is ignored. It is possible to use both the '-' prefix and the '*' postfix
  on a single term. At this time, all Fts3 queries must contain at least
  one term or phrase that is not preceded by the '-' prefix.

  Regardless of whether or not a table name or column name is used on the
  left hand side of the MATCH operator, a specific column of the fts3 table
  may be associated with each token in a query by preceding a token with
  a column name followed by a ':' character. For example, regardless of what
  is specified for <col>, the following query requires that column "col1"
  of the table contains the term "hello", and that column "col2" of the
  table contains the term "world". If the table does not contain columns
  named "col1" and "col2", then an error is returned and the query is
  not run.

    <col> MATCH 'col1:hello col2:world'

  It is not possible to associate a specific table column with a quoted
  phrase or a term preceded by a '-' operator. A '*' character may be
  appended to a term associated with a specific column for prefix matching.

3. ENHANCED QUERY SYNTAX

  The enhanced query syntax is quite similar to the standard query syntax,
  with the following four differences:

  1) Parenthesis are supported. When using the enhanced query syntax,
     parenthesis may be used to overcome the built-in precedence of the
     supplied binary operators. For example, the following query:

       <col> MATCH '(hello world) OR (simple example)'

     matches documents that contain both "hello" and "world", and documents
     that contain both "simple" and "example". It is not possible to forumlate
     such a query using the standard syntax.

  2) Instead of separating tokens and phrases by whitespace, an AND operator
     may be explicitly specified. This does not change query processing at
     all, but may be used to improve readability. For example, the following
     query is handled identically to the one above:

       <col> MATCH '(hello AND world) OR (simple AND example)'

     As with the OR and NEAR operators, the AND operator must be specified
     in upper case. The word "and" specified in lower or mixed case is
     handled as a regular token.

  3) The '-' token prefix is not supported. Instead, a new binary operator,
     NOT, is included. The NOT operator requires that the query specified
     as its left-hand operator matches, but that the query specified as the
     right-hand operator does not. For example, to query for all rows that
     contain the term "example" but not the term "simple", the following
     query could be used:

       <col> MATCH 'example NOT simple'

     As for all other operators, the NOT operator must be specified in
     upper case. Otherwise it will be treated as a regular token.

  4) Unlike in the standard syntax, where the OR operator has a higher
     precedence than the implicit AND operator, when using the enhanced
     syntax implicit and explict AND operators have a higher precedence
     than OR operators. Using the enhanced syntax, the following two
     queries are equivalent:

       <col> MATCH 'sqlite fantastic OR impressive'
       <col> MATCH '(sqlite AND fantastic) OR impressive'

     however, when using the standard syntax, the query:

       <col> MATCH 'sqlite fantastic OR impressive'

     is equivalent to the enhanced syntax query:

       <col> MATCH 'sqlite AND (fantastic OR impressive)'

     The precedence of all enhanced syntax operators, in order from highest
     to lowest, is:

       NEAR       (highest precedence, tightest grouping)
       NOT
       AND
       OR         (lowest precedence, loosest grouping)

  Using the advanced syntax, it is possible to specify expressions enclosed
  in parenthesis as operands to the NOT, AND and OR operators. However both
  the left and right hand side operands of NEAR operators must be either
  tokens or phrases. Attempting the following query will return an error:

    <col> MATCH 'sqlite NEAR (fantastic OR impressive)'

  Queries of this form must be re-written as:

    <col> MATCH 'sqlite NEAR fantastic OR sqlite NEAR impressive'

1. FTS3 Tokenizers

  When creating a new full-text table, FTS3 allows the user to select
  the text tokenizer implementation to be used when indexing text
  by specifying a "tokenize" clause as part of the CREATE VIRTUAL TABLE
  statement:

    CREATE VIRTUAL TABLE <table-name> USING fts3(
      <columns ...> [, tokenize <tokenizer-name> [<tokenizer-args>]]
    );

  The built-in tokenizers (valid values to pass as <tokenizer name>) are
  "simple", "porter" and "unicode".

  <tokenizer-args> should consist of zero or more white-space separated
  arguments to pass to the selected tokenizer implementation. The
  interpretation of the arguments, if any, depends on the individual
  tokenizer.

2. Custom Tokenizers

  FTS3 allows users to provide custom tokenizer implementations. The
  interface used to create a new tokenizer is defined and described in
  the fts3_tokenizer.h source file.

  Registering a new FTS3 tokenizer is similar to registering a new
  virtual table module with SQLite. The user passes a pointer to a
  structure containing pointers to various callback functions that
  make up the implementation of the new tokenizer type. For tokenizers,
  the structure (defined in fts3_tokenizer.h) is called
  "sqlite3_tokenizer_module".

  FTS3 does not expose a C-function that users call to register new
  tokenizer types with a database handle. Instead, the pointer must
  be encoded as an SQL blob value and passed to FTS3 through the SQL
  engine by evaluating a special scalar function, "fts3_tokenizer()".
  The fts3_tokenizer() function may be called with one or two arguments,
  as follows:

    SELECT fts3_tokenizer(<tokenizer-name>);
    SELECT fts3_tokenizer(<tokenizer-name>, <sqlite3_tokenizer_module ptr>);

  Where <tokenizer-name> is a string identifying the tokenizer and
  <sqlite3_tokenizer_module ptr> is a pointer to an sqlite3_tokenizer_module
  structure encoded as an SQL blob. If the second argument is present,
  it is registered as tokenizer <tokenizer-name> and a copy of it
  returned. If only one argument is passed, a pointer to the tokenizer
  implementation currently registered as <tokenizer-name> is returned,
  encoded as a blob. Or, if no such tokenizer exists, an SQL exception
  (error) is raised.

  SECURITY: If the fts3 extension is used in an environment where potentially
    malicious users may execute arbitrary SQL (i.e. gears), they should be
    prevented from invoking the fts3_tokenizer() function.  The
    fts3_tokenizer() function is disabled by default. It is only enabled
    by SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER. Do not enable it in
    security sensitive environments.

  See "Sample code" below for an example of calling the fts3_tokenizer()
  function from C code.

3. ICU Library Tokenizers

  If this extension is compiled with the SQLITE_ENABLE_ICU pre-processor
  symbol defined, then there exists a built-in tokenizer named "icu"
  implemented using the ICU library. The first argument passed to the
  xCreate() method (see fts3_tokenizer.h) of this tokenizer may be
  an ICU locale identifier. For example "tr_TR" for Turkish as used
  in Turkey, or "en_AU" for English as used in Australia. For example:

    "CREATE VIRTUAL TABLE thai_text USING fts3(text, tokenizer icu th_TH)"

  The ICU tokenizer implementation is very simple. It splits the input
  text according to the ICU rules for finding word boundaries and discards
  any tokens that consist entirely of white-space. This may be suitable
  for some applications in some locales, but not all. If more complex
  processing is required, for example to implement stemming or
  discard punctuation, this can be done by creating a tokenizer
  implementation that uses the ICU tokenizer as part of its implementation.

  When using the ICU tokenizer this way, it is safe to overwrite the
  contents of the strings returned by the xNext() method (see
  fts3_tokenizer.h).

4. Sample code.

  The following two code samples illustrate the way C code should invoke
  the fts3_tokenizer() scalar function:

      int registerTokenizer(
        sqlite3 *db,
        char *zName,
        const sqlite3_tokenizer_module *p
      ){
        int rc;
        sqlite3_stmt *pStmt;
        const char zSql[] = "SELECT fts3_tokenizer(?, ?)";

        rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
        if( rc!=SQLITE_OK ){
          return rc;
        }

        sqlite3_bind_text(pStmt, 1, zName, -1, SQLITE_STATIC);
        sqlite3_bind_blob(pStmt, 2, &p, sizeof(p), SQLITE_STATIC);
        sqlite3_step(pStmt);

        return sqlite3_finalize(pStmt);
      }

      int queryTokenizer(
        sqlite3 *db,
        char *zName,
        const sqlite3_tokenizer_module **pp
      ){
        int rc;
        sqlite3_stmt *pStmt;
        const char zSql[] = "SELECT fts3_tokenizer(?)";

        *pp = 0;
        rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
        if( rc!=SQLITE_OK ){
          return rc;
        }

        sqlite3_bind_text(pStmt, 1, zName, -1, SQLITE_STATIC);
        if( SQLITE_ROW==sqlite3_step(pStmt) ){
          if( sqlite3_column_type(pStmt, 0)==SQLITE_BLOB ){
            memcpy(pp, sqlite3_column_blob(pStmt, 0), sizeof(*pp));
          }
        }

        return sqlite3_finalize(pStmt);
      }

This folder contains source code to the second full-text search
extension for SQLite.  While the API is the same, this version uses a
substantially different storage schema from fts1, so tables will need
to be rebuilt.