SQLite

  zSchema = sqlite3_mprintf("CREATE TABLE x");
  for(i=0; i<nColumn; i++){
    zNext = sqlite3_mprintf("%s%s%Q", zSchema, zSep, azColumn[i]);
    sqlite3_free(zSchema);
    zSchema = zNext;
    zSep = ",";
  }
  zNext = sqlite3_mprintf("%s,%Q HIDDEN", zSchema, zTableName);
  sqlite3_free(zSchema);
  zSchema = zNext;
  zNext = sqlite3_mprintf("%s,docid HIDDEN)", zSchema);
  sqlite3_free(zSchema);
  return zNext;
}

/*
** Build a new sqlite3_vtab structure that will describe the
** fulltext index defined by spec.

out:
  clearTableSpec(&spec);
  return rc;
}

/* Decide how to handle an SQL query. */
static int fulltextBestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
  fulltext_vtab *v = (fulltext_vtab *)pVTab;
  int i;
  TRACE(("FTS3 BestIndex\n"));

  for(i=0; i<pInfo->nConstraint; ++i){
    const struct sqlite3_index_constraint *pConstraint;
    pConstraint = &pInfo->aConstraint[i];
    if( pConstraint->usable ) {
      if( (pConstraint->iColumn==-1 || pConstraint->iColumn==v->nColumn+1) &&
          pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ ){
        pInfo->idxNum = QUERY_DOCID;      /* lookup by docid */
        TRACE(("FTS3 QUERY_DOCID\n"));
      } else if( pConstraint->iColumn>=0 && pConstraint->iColumn<=v->nColumn &&
                 pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH ){
        /* full-text search */
        pInfo->idxNum = QUERY_FULLTEXT + pConstraint->iColumn;
        TRACE(("FTS3 QUERY_FULLTEXT %d\n", pConstraint->iColumn));
      } else continue;

      pInfo->aConstraintUsage[i].argvIndex = 1;

    sqlite3_value *pVal = sqlite3_column_value(c->pStmt, idxCol+1);
    sqlite3_result_value(pContext, pVal);
  }else if( idxCol==v->nColumn ){
    /* The extra column whose name is the same as the table.
    ** Return a blob which is a pointer to the cursor
    */
    sqlite3_result_blob(pContext, &c, sizeof(c), SQLITE_TRANSIENT);
  }else if( idxCol==v->nColumn+1 ){
    /* The docid column, which is an alias for rowid. */
    sqlite3_value *pVal = sqlite3_column_value(c->pStmt, 0);
    sqlite3_result_value(pContext, pVal);
  }
  return SQLITE_OK;
}

/* This is the xRowid method.  The SQLite core calls this routine to
** retrieve the rowid for the current row of the result set.  fts3
** exposes %_content.docid as the rowid for the virtual table.  The

    rc = index_delete(v, sqlite3_value_int64(ppArg[0]));
  } else if( sqlite3_value_type(ppArg[0]) != SQLITE_NULL ){
    /* An update:
     * ppArg[0] = old rowid
     * ppArg[1] = new rowid
     * ppArg[2..2+v->nColumn-1] = values
     * ppArg[2+v->nColumn] = value for magic column (we ignore this)
     * ppArg[2+v->nColumn+1] = value for docid
     */
    sqlite_int64 rowid = sqlite3_value_int64(ppArg[0]);
    if( sqlite3_value_type(ppArg[1]) != SQLITE_INTEGER ||
        sqlite3_value_int64(ppArg[1]) != rowid ){
      rc = SQLITE_ERROR;  /* we don't allow changing the rowid */
    }else if( sqlite3_value_type(ppArg[2+v->nColumn+1]) != SQLITE_INTEGER ||
              sqlite3_value_int64(ppArg[2+v->nColumn+1]) != rowid ){
      rc = SQLITE_ERROR;  /* we don't allow changing the docid */
    }else{
      assert( nArg==2+v->nColumn+2);
      rc = index_update(v, rowid, &ppArg[2]);
    }
  } else {
    /* An insert:
     * ppArg[1] = requested rowid
     * ppArg[2..2+v->nColumn-1] = values
     * ppArg[2+v->nColumn] = value for magic column (we ignore this)
     * ppArg[2+v->nColumn+1] = value for docid
     */
    sqlite3_value *pRequestDocid = ppArg[2+v->nColumn+1];
    assert( nArg==2+v->nColumn+2);
    if( SQLITE_NULL != sqlite3_value_type(pRequestDocid) &&
        SQLITE_NULL != sqlite3_value_type(ppArg[1]) ){
      /* TODO(shess) Consider allowing this to work if the values are
      ** identical.  I'm inclined to discourage that usage, though,
      ** given that both rowid and docid are special columns.  Better
      ** would be to define one or the other as the default winner,
      ** but should it be fts3-centric (docid) or SQLite-centric
      ** (rowid)?
      */
      rc = SQLITE_ERROR;
    }else{
      if( SQLITE_NULL == sqlite3_value_type(pRequestDocid) ){
        pRequestDocid = ppArg[1];
      }
      rc = index_insert(v, pRequestDocid, &ppArg[2], pRowid);
    }
  }

  return rc;
}

static int fulltextSync(sqlite3_vtab *pVtab){
  TRACE(("FTS3 xSync()\n"));

# 2007 August 20
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  This
# script tests for the fts2 rowid-versus-vacuum problem (ticket #2566).
#
# $Id: fts3b.test,v 1.3 2007/09/13 18:14:49 shess Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
ifcapable !fts3 {

# Test that the column doesn't conflict with inserts that don't name
# columns.
do_test fts3b-3.3 {
  execsql {
    INSERT INTO t3 VALUES ('another test');
  }
} {}

# fts3 adds a new implicit column, docid, which acts as an alias for
# rowid.

db eval {
  CREATE VIRTUAL TABLE t4 USING fts3(c);
  INSERT INTO t4 (c) VALUES('this is a test');
  INSERT INTO t4 (c) VALUES('that was a test');
  INSERT INTO t4 (c) VALUES('this is fun');
  DELETE FROM t4 WHERE c = 'that was a test';
}

# Test that docid is present and identical to rowid.
do_test fts3b-4.1 {
  execsql {
    SELECT rowid FROM t4 WHERE rowid <> docid;
  }
} {}

# Test that docid is hidden.
do_test fts3b-4.2 {
  execsql {
    SELECT * FROM t4 WHERE rowid = 1;
  }
} {{this is a test}}

# Test that docid can be selected.
do_test fts3b-4.3 {
  execsql {
    SELECT docid, * FROM t4 WHERE rowid = 1;
  }
} {1 {this is a test}}

# Test that docid can be used in WHERE.
do_test fts3b-4.4 {
  execsql {
    SELECT docid, * FROM t4 WHERE docid = 1;
  }
} {1 {this is a test}}

# Test that the column doesn't conflict with inserts that don't name
# columns.  [Yes, this is the same as fts3b-3.3, here just in case the
# goals of that test change.]
do_test fts3b-4.5 {
  execsql {
    INSERT INTO t4 VALUES ('another test');
  }
} {}

# Test that the docid can be forced on insert.
do_test fts3b-4.6 {
  execsql {
    INSERT INTO t4 (docid, c) VALUES (10, 'yet another test');
    SELECT * FROM t4 WHERE docid = 10;
  }
} {{yet another test}}

# Test that rowid can also be forced.
do_test fts3b-4.7 {
  execsql {
    INSERT INTO t4 (docid, c) VALUES (12, 'still testing');
    SELECT * FROM t4 WHERE docid = 12;
  }
} {{still testing}}

# If an insert tries to set both docid and rowid, require an error.
do_test fts3b-4.8 {
  catchsql {
    INSERT INTO t4 (rowid, docid, c) VALUES (14, 15, 'bad test');
    SELECT * FROM t4 WHERE docid = 14;
  }
} {1 {SQL logic error or missing database}}

# Don't allow update of docid, to match rowid behaviour.
do_test fts3b-4.9 {
  catchsql {
    UPDATE t4 SET docid = 14 WHERE docid = 12;
  }
} {1 {SQL logic error or missing database}}

finish_test