SQLite

*/
void sqlite3Fts3DoclistPrev(
  int bDescIdx,                   /* True if the doclist is desc */
  char *aDoclist,                 /* Pointer to entire doclist */
  int nDoclist,                   /* Length of aDoclist in bytes */
  char **ppIter,                  /* IN/OUT: Iterator pointer */
  sqlite3_int64 *piDocid,         /* IN/OUT: Docid pointer */
  int *pnList,                    /* OUT: List length pointer */
  u8 *pbEof                       /* OUT: End-of-file flag */
){
  char *p = *ppIter;

  assert( nDoclist>0 );
  assert( *pbEof==0 );
  assert( p || *piDocid==0 );

      char *pSave = p;
      fts3ReversePoslist(aDoclist, &p);
      *pnList = (int)(pSave - p);
    }
    *ppIter = p;
  }
}

/*
** Iterate forwards through a doclist.
*/
void sqlite3Fts3DoclistNext(
  int bDescIdx,                   /* True if the doclist is desc */
  char *aDoclist,                 /* Pointer to entire doclist */
  int nDoclist,                   /* Length of aDoclist in bytes */
  char **ppIter,                  /* IN/OUT: Iterator pointer */
  sqlite3_int64 *piDocid,         /* IN/OUT: Docid pointer */
  u8 *pbEof                       /* OUT: End-of-file flag */
){
  char *p = *ppIter;

  assert( nDoclist>0 );
  assert( *pbEof==0 );
  assert( p || *piDocid==0 );
  assert( !p || (p>=aDoclist && p<=&aDoclist[nDoclist]) );

  if( p==0 ){
    p = aDoclist;
    p += sqlite3Fts3GetVarint(p, piDocid);
  }else{
    fts3PoslistCopy(0, &p);
    if( p>=&aDoclist[nDoclist] ){
      *pbEof = 1;
    }else{
      sqlite3_int64 iVar;
      p += sqlite3Fts3GetVarint(p, &iVar);
      *piDocid += ((bDescIdx ? -1 : 1) * iVar);
    }
  }

  *ppIter = p;
}

/*
** Attempt to move the phrase iterator to point to the next matching docid. 
** If an error occurs, return an SQLite error code. Otherwise, return 
** SQLITE_OK.
**
** If there is no "next" entry and no error occurs, then *pbEof is set to

** for 'X' is requested, the buffer returned may contain:
**
**     0x04 0x05 0x03 0x01   or   0x04 0x05 0x03 0x00
**
** This function works regardless of whether or not the phrase is deferred,
** incremental, or neither.
*/
int sqlite3Fts3EvalPhrasePoslist(
  Fts3Cursor *pCsr,               /* FTS3 cursor object */
  Fts3Expr *pExpr,                /* Phrase to return doclist for */
  int iCol,                       /* Column to return position list for */
  char **ppOut                    /* OUT: Pointer to position list */
){
  Fts3Phrase *pPhrase = pExpr->pPhrase;
  Fts3Table *pTab = (Fts3Table *)pCsr->base.pVtab;
  char *pIter;
  int iThis;
  sqlite3_int64 iDocid;

  /* If this phrase is applies specifically to some column other than 
  ** column iCol, return a NULL pointer.  */
  *ppOut = 0;
  assert( iCol>=0 && iCol<pTab->nColumn );
  if( (pPhrase->iColumn<pTab->nColumn && pPhrase->iColumn!=iCol) ){
    return SQLITE_OK;
  }

  iDocid = pExpr->iDocid;
  pIter = pPhrase->doclist.pList;
  if( iDocid!=pCsr->iPrevId || pExpr->bEof ){
    int bDescDoclist = pTab->bDescIdx;      /* For DOCID_CMP macro */
    int bOr = 0;
    u8 bEof = 0;
    Fts3Expr *p;

    /* Check if this phrase descends from an OR expression node. If not, 
    ** return NULL. Otherwise, the entry that corresponds to docid 
    ** pCsr->iPrevId may lie earlier in the doclist buffer. */
    for(p=pExpr->pParent; p; p=p->pParent){
      if( p->eType==FTSQUERY_OR ) bOr = 1;
    }
    if( bOr==0 ) return SQLITE_OK;

    /* This is the descendent of an OR node. In this case we cannot use
    ** an incremental phrase. Load the entire doclist for the phrase
    ** into memory in this case.  */
    if( pPhrase->bIncr ){
      int rc = SQLITE_OK;
      int bEofSave = pExpr->bEof;
      fts3EvalRestart(pCsr, pExpr, &rc);
      while( rc==SQLITE_OK && !pExpr->bEof ){
        fts3EvalNextRow(pCsr, pExpr, &rc);
        if( bEofSave==0 && pExpr->iDocid==iDocid ) break;
      }
      pIter = pPhrase->doclist.pList;
      assert( rc!=SQLITE_OK || pPhrase->bIncr==0 );
      if( rc!=SQLITE_OK ) return rc;
    }

    if( pExpr->bEof ){
      pIter = 0;
      iDocid = 0;
    }
    bEof = (pPhrase->doclist.nAll==0);
    assert( bDescDoclist==0 || bDescDoclist==1 );
    assert( pCsr->bDesc==0 || pCsr->bDesc==1 );

    if( pCsr->bDesc==bDescDoclist ){
      int dummy;
      while( (pIter==0 || DOCID_CMP(iDocid, pCsr->iPrevId)>0 ) && bEof==0 ){
        sqlite3Fts3DoclistPrev(
            bDescDoclist, pPhrase->doclist.aAll, pPhrase->doclist.nAll, 
            &pIter, &iDocid, &dummy, &bEof
        );
      }
    }else{
      while( (pIter==0 || DOCID_CMP(iDocid, pCsr->iPrevId)<0 ) && bEof==0 ){
        sqlite3Fts3DoclistNext(
            bDescDoclist, pPhrase->doclist.aAll, pPhrase->doclist.nAll, 
            &pIter, &iDocid, &bEof
        );
      }
    }

    if( bEof || iDocid!=pCsr->iPrevId ) pIter = 0;
  }
  if( pIter==0 ) return SQLITE_OK;

  if( *pIter==0x01 ){
    pIter++;
    pIter += sqlite3Fts3GetVarint32(pIter, &iThis);
  }else{
    iThis = 0;
  }
  while( iThis<iCol ){
    fts3ColumnlistCopy(0, &pIter);
    if( *pIter==0x00 ) return 0;
    pIter++;
    pIter += sqlite3Fts3GetVarint32(pIter, &iThis);
  }

  *ppOut = ((iCol==iThis)?pIter:0);
  return SQLITE_OK;
}

/*
** Free all components of the Fts3Phrase structure that were allocated by
** the eval module. Specifically, this means to free:
**
**   * the contents of pPhrase->doclist, and

void sqlite3Fts3EvalPhraseCleanup(Fts3Phrase *);

int sqlite3Fts3MsrIncrStart(
    Fts3Table*, Fts3MultiSegReader*, int, const char*, int);
int sqlite3Fts3MsrIncrNext(
    Fts3Table *, Fts3MultiSegReader *, sqlite3_int64 *, char **, int *);
int sqlite3Fts3EvalPhrasePoslist(Fts3Cursor *, Fts3Expr *, int iCol, char **); 
int sqlite3Fts3MsrOvfl(Fts3Cursor *, Fts3MultiSegReader *, int *);
int sqlite3Fts3MsrIncrRestart(Fts3MultiSegReader *pCsr);

int sqlite3Fts3DeferredTokenList(Fts3DeferredToken *, char **, int *);

#endif /* !SQLITE_CORE || SQLITE_ENABLE_FTS3 */
#endif /* _FTSINT_H */

** This function is an fts3ExprIterate() callback used by fts3BestSnippet().
** Each invocation populates an element of the SnippetIter.aPhrase[] array.
*/
static int fts3SnippetFindPositions(Fts3Expr *pExpr, int iPhrase, void *ctx){
  SnippetIter *p = (SnippetIter *)ctx;
  SnippetPhrase *pPhrase = &p->aPhrase[iPhrase];
  char *pCsr;
  int rc;

  pPhrase->nToken = pExpr->pPhrase->nToken;

  rc = sqlite3Fts3EvalPhrasePoslist(p->pCsr, pExpr, p->iCol, &pCsr);
  assert( rc==SQLITE_OK || pCsr==0 );
  if( pCsr ){
    int iFirst = 0;
    pPhrase->pList = pCsr;
    fts3GetDeltaPosition(&pCsr, &iFirst);
    assert( iFirst>=0 );
    pPhrase->pHead = pCsr;
    pPhrase->pTail = pCsr;
    pPhrase->iHead = iFirst;
    pPhrase->iTail = iFirst;
  }else{
    assert( rc!=SQLITE_OK || (
       pPhrase->pList==0 && pPhrase->pHead==0 && pPhrase->pTail==0 
    ));
  }

  return rc;
}

/*
** Select the fragment of text consisting of nFragment contiguous tokens 
** from column iCol that represent the "best" snippet. The best snippet
** is the snippet with the highest score, where scores are calculated
** by adding:

** array that are different for each row returned by the query.
*/
static int fts3ExprLocalHitsCb(
  Fts3Expr *pExpr,                /* Phrase expression node */
  int iPhrase,                    /* Phrase number */
  void *pCtx                      /* Pointer to MatchInfo structure */
){
  int rc = SQLITE_OK;
  MatchInfo *p = (MatchInfo *)pCtx;
  int iStart = iPhrase * p->nCol * 3;
  int i;

  for(i=0; i<p->nCol && rc==SQLITE_OK; i++){
    char *pCsr;
    rc = sqlite3Fts3EvalPhrasePoslist(p->pCursor, pExpr, i, &pCsr);
    if( pCsr ){
      p->aMatchinfo[iStart+i*3] = fts3ColumnlistCount(&pCsr);
    }else{
      p->aMatchinfo[iStart+i*3] = 0;
    }
  }

  return rc;
}

static int fts3MatchinfoCheck(
  Fts3Table *pTab, 
  char cArg,
  char **pzErr
){

  }

  for(iCol=0; iCol<pInfo->nCol; iCol++){
    int nLcs = 0;                 /* LCS value for this column */
    int nLive = 0;                /* Number of iterators in aIter not at EOF */

    for(i=0; i<pInfo->nPhrase; i++){
      int rc;
      LcsIterator *pIt = &aIter[i];
      rc = sqlite3Fts3EvalPhrasePoslist(pCsr, pIt->pExpr, iCol, &pIt->pRead);
      if( rc!=SQLITE_OK ) return rc;
      if( pIt->pRead ){
        pIt->iPos = pIt->iPosOffset;
        fts3LcsIteratorAdvance(&aIter[i]);
        nLive++;
      }
    }

*/
static int fts3ExprTermOffsetInit(Fts3Expr *pExpr, int iPhrase, void *ctx){
  TermOffsetCtx *p = (TermOffsetCtx *)ctx;
  int nTerm;                      /* Number of tokens in phrase */
  int iTerm;                      /* For looping through nTerm phrase terms */
  char *pList;                    /* Pointer to position list for phrase */
  int iPos = 0;                   /* First position in position-list */
  int rc;

  UNUSED_PARAMETER(iPhrase);
  rc = sqlite3Fts3EvalPhrasePoslist(p->pCsr, pExpr, p->iCol, &pList);
  nTerm = pExpr->pPhrase->nToken;
  if( pList ){
    fts3GetDeltaPosition(&pList, &iPos);
    assert( iPos>=0 );
  }

  for(iTerm=0; iTerm<nTerm; iTerm++){
    TermOffset *pT = &p->aTerm[p->iTerm++];
    pT->iOff = nTerm-iTerm-1;
    pT->pList = pList;
    pT->iPos = iPos;
  }

  return rc;
}

/*
** Implementation of offsets() function.
*/
void sqlite3Fts3Offsets(
  sqlite3_context *pCtx,          /* SQLite function call context */

** Remove the memory data structures associated with the given
** Table.  No changes are made to disk by this routine.
**
** This routine just deletes the data structure.  It does not unlink
** the table data structure from the hash table.  But it does destroy
** memory structures of the indices and foreign keys associated with 
** the table.
**
** The db parameter is optional.  It is needed if the Table object 
** contains lookaside memory.  (Table objects in the schema do not use
** lookaside memory, but some ephemeral Table objects do.)  Or the
** db parameter can be used with db->pnBytesFreed to measure the memory
** used by the Table object.
*/
void sqlite3DeleteTable(sqlite3 *db, Table *pTable){
  Index *pIndex, *pNext;
  TESTONLY( int nLookaside; ) /* Used to verify lookaside not used for schema */

  assert( !pTable || pTable->nRef>0 );

  /* Do not delete the table until the reference count reaches zero. */
  if( !pTable ) return;
  if( ((!db || db->pnBytesFreed==0) && (--pTable->nRef)>0) ) return;

  /* Record the number of outstanding lookaside allocations in schema Tables
  ** prior to doing any free() operations.  Since schema Tables do not use
  ** lookaside, this number should not change. */
  TESTONLY( nLookaside = (db && (pTable->tabFlags & TF_Ephemeral)==0) ?
                         db->lookaside.nOut : 0 );

  /* Delete all indices associated with this table. */
  for(pIndex = pTable->pIndex; pIndex; pIndex=pNext){
    pNext = pIndex->pNext;
    assert( pIndex->pSchema==pTable->pSchema );
    if( !db || db->pnBytesFreed==0 ){
      char *zName = pIndex->zName; 

#ifndef SQLITE_OMIT_CHECK
  sqlite3ExprListDelete(db, pTable->pCheck);
#endif
#ifndef SQLITE_OMIT_VIRTUALTABLE
  sqlite3VtabClear(db, pTable);
#endif
  sqlite3DbFree(db, pTable);

  /* Verify that no lookaside memory was used by schema tables */
  assert( nLookaside==0 || nLookaside==db->lookaside.nOut );
}

/*
** Unlink the given table from the hash tables and the delete the
** table structure with all its indices and foreign keys.
*/
void sqlite3UnlinkAndDeleteTable(sqlite3 *db, int iDb, const char *zTabName){

        LPCSTR,LPBOOL))aSyscall[57].pCurrent)

  { "WriteFile",               (SYSCALL)WriteFile,               0 },

#define osWriteFile ((BOOL(WINAPI*)(HANDLE,LPCVOID,DWORD,LPDWORD, \
        LPOVERLAPPED))aSyscall[58].pCurrent)

#if SQLITE_OS_WINRT
  { "CreateEventExW",          (SYSCALL)CreateEventExW,          0 },
#else
  { "CreateEventExW",          (SYSCALL)0,                       0 },
#endif

#define osCreateEventExW ((HANDLE(WINAPI*)(LPSECURITY_ATTRIBUTES,LPCWSTR, \
        DWORD,DWORD))aSyscall[59].pCurrent)

#else
  { "SetFilePointerEx",        (SYSCALL)0,                       0 },
#endif

#define osSetFilePointerEx ((BOOL(WINAPI*)(HANDLE,LARGE_INTEGER, \
        PLARGE_INTEGER,DWORD))aSyscall[62].pCurrent)

#if SQLITE_OS_WINRT
  { "GetFileInformationByHandleEx", (SYSCALL)GetFileInformationByHandleEx, 0 },
#else
  { "GetFileInformationByHandleEx", (SYSCALL)0,                  0 },
#endif

#define osGetFileInformationByHandleEx ((BOOL(WINAPI*)(HANDLE, \
        FILE_INFO_BY_HANDLE_CLASS,LPVOID,DWORD))aSyscall[63].pCurrent)

defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X).      {A = X;}
%type init_deferred_pred_opt {int}
init_deferred_pred_opt(A) ::= .                       {A = 0;}
init_deferred_pred_opt(A) ::= INITIALLY DEFERRED.     {A = 1;}
init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE.    {A = 0;}

conslist_opt(A) ::= .                         {A.n = 0; A.z = 0;}
conslist_opt(A) ::= COMMA(X) conslist.        {A = X;}

conslist ::= conslist tconscomma tcons.
conslist ::= tcons.
tconscomma ::= COMMA.            {pParse->constraintName.n = 0;}
tconscomma ::= .
tcons ::= CONSTRAINT nm(X).      {pParse->constraintName = X;}
tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
                                 {sqlite3AddPrimaryKey(pParse,X,R,I,0);}
tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
                                 {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0,0,0);}
tcons ::= CHECK LP expr(E) RP onconf.
                                 {sqlite3AddCheckConstraint(pParse,E.pExpr);}
tcons ::= FOREIGN KEY LP idxlist(FA) RP

  rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
  if( rc==SQLITE_SCHEMA ){
    sqlite3_finalize(*ppStmt);
    rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
  }
  sqlite3BtreeLeaveAll(db);
  sqlite3_mutex_leave(db->mutex);
  assert( rc==SQLITE_OK || *ppStmt==0 );
  return rc;
}

/*
** Rerun the compilation of a statement after a schema change.
**
** If the statement is successfully recompiled, return SQLITE_OK. Otherwise,

** integer is the delay.  If either integer is negative, then the setting
** is not changed but instead the prior value of that setting is written
** into the array entry, allowing the current retry settings to be
** interrogated.  The zDbName parameter is ignored.
**
** <li>[[SQLITE_FCNTL_PERSIST_WAL]]
** ^The [SQLITE_FCNTL_PERSIST_WAL] opcode is used to set or query the
** persistent [WAL | Write Ahead Log] setting.  By default, the auxiliary
** write ahead log and shared memory files used for transaction control
** are automatically deleted when the latest connection to the database
** closes.  Setting persistent WAL mode causes those files to persist after
** close.  Persisting the files is useful when other processes that do not
** have write permission on the directory containing the database file want
** to read the database file, as the WAL and shared memory files must exist
** in order for the database to be readable.  The fourth parameter to

** [[SQLITE_DBSTATUS_CACHE_WRITE]] ^(<dt>SQLITE_DBSTATUS_CACHE_WRITE</dt>
** <dd>This parameter returns the number of dirty cache entries that have
** been written to disk. Specifically, the number of pages written to the
** wal file in wal mode databases, or the number of pages written to the
** database file in rollback mode databases. Any pages written as part of
** transaction rollback or database recovery operations are not included.
** If an IO or other error occurs while writing a page to disk, the effect
** on subsequent SQLITE_DBSTATUS_CACHE_WRITE requests is undefined.)^ ^The
** highwater mark associated with SQLITE_DBSTATUS_CACHE_WRITE is always 0.
** </dd>
** </dl>
*/
#define SQLITE_DBSTATUS_LOOKASIDE_USED       0
#define SQLITE_DBSTATUS_CACHE_USED           1
#define SQLITE_DBSTATUS_SCHEMA_USED          2

          u64 dummy;
          iOff += sqlite3GetVarint(&aData[iOff], &dummy);
        }
        if( nPayload>(u32)p->nMxPayload ) p->nMxPayload = nPayload;
        getLocalPayload(nUsable, p->flags, nPayload, &nLocal);
        pCell->nLocal = nLocal;
        assert( nLocal>=0 );
        assert( nPayload>=(u32)nLocal );
        assert( nLocal<=(nUsable-35) );
        if( nPayload>(u32)nLocal ){
          int j;
          int nOvfl = ((nPayload - nLocal) + nUsable-4 - 1) / (nUsable - 4);
          pCell->nLastOvfl = (nPayload-nLocal) - (nOvfl-1) * (nUsable-4);
          pCell->nOvfl = nOvfl;
          pCell->aOvfl = sqlite3_malloc(sizeof(u32)*nOvfl);

    return 1;
  }
  if( pIdx->onError!=OE_None && i==pIdx->nColumn
      && (wsFlags & WHERE_COLUMN_NULL)==0
      && !referencesOtherTables(pOrderBy, pMaskSet, j, base) 
  ){
    Column *aCol = pIdx->pTable->aCol;


    /* All terms of this index match some prefix of the ORDER BY clause,
    ** the index is UNIQUE, and no terms on the tail of the ORDER BY
    ** refer to other tables in a join. So, assuming that the index entries
    ** visited contain no NULL values, then this index delivers rows in
    ** the required order.
    **

  }
}

#-------------------------------------------------------------------------
# Test that FTS3 tables may be read/written by different versions of 
# SQLite. 
#
ifcapable fts3 {
  set contents {
    CREATE VIRTUAL TABLE t1 USING fts3(a, b);
  }
  foreach {num doc} {
    one "jk zm jk eczkjblu urvysbnykk sk gnl jk ttvgf hmjf"
    two "jk bnhc jjrxpjkb mjpavjuhw fibokdry igju jk zm zm xh"
    three "wxe ogttbykvt uhzq xr iaf zf urvysbnykk aayxpmve oacaxgjoo mjpavjuhw"
    four "gazrt jk ephknonq myjp uenvbm wuvajhwqz jk zm xnxhf nvfasfh"
    five "zm aayxpmve csjqxhgj xnxhf xr jk aayxpmve xnxhf zm zm"
    six "sokcyf zm ogyavjvv jk zm fibokdry zm jk igju igju"
    seven "vgsld bvgimjik xuprtlyle jk akmikrqyt jk aayxpmve hkfoudzftq ddjj"
    eight "zm uhzq ovkyevlgv zk uenvbm csjqxhgj jk vgsld pgybs jk"
    nine  "zm agmckuiu zexh fibokdry jk uhzq bu tugflixoex xnxhf sk"
  } {
    append contents "INSERT INTO t1 VALUES('$num', '$doc');"
  }
  do_allbackcompat_test {
    if {[code1 {set ::sqlite_options(fts3)}]
     && [code2 {set ::sqlite_options(fts3)}]
    } {
  
      do_test backcompat-3.1 { sql1 $contents } {}
  
      foreach {n q} {
        1    "SELECT * FROM t1 ORDER BY a, b"
        2    "SELECT rowid FROM t1 WHERE a MATCH 'five'"
        3    "SELECT * FROM t1 WHERE a MATCH 'five'"
        4    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'jk'"
        5    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'tug* OR eight'"
      } {
        do_test backcompat-3.2 [list sql1 $q] [sql2 $q]
      }
  
      do_test backcompat-3.3 { sql1 {
        INSERT INTO t1 SELECT * FROM t1;
        INSERT INTO t1 SELECT * FROM t1;
        INSERT INTO t1 SELECT * FROM t1;
        INSERT INTO t1 SELECT * FROM t1;
        INSERT INTO t1 SELECT * FROM t1;
        INSERT INTO t1 SELECT * FROM t1;
        INSERT INTO t1 SELECT * FROM t1;
        INSERT INTO t1 SELECT * FROM t1;
      } } {}
  
      foreach {n q} {
        1    "SELECT * FROM t1 ORDER BY a, b"
        2    "SELECT rowid FROM t1 WHERE a MATCH 'five'"
        3    "SELECT * FROM t1 WHERE a MATCH 'five'"
        4    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'jk'"
        5    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'tug* OR eight'"
      } {
        do_test backcompat-3.4 [list sql1 $q] [sql2 $q]
      }
  
      set alphabet "a b c d e f g h i j k l m n o p q r s t u v w x y z 1 2 3 4"
      for {set i 0} {$i < 900} {incr i} {
        set term "[lindex $alphabet [expr $i/30]][lindex $alphabet [expr $i%30]] "
        sql1 "INSERT INTO t1 VALUES($i, '[string repeat $term 14]')"
      }
  
      foreach {n q} {
        1    "SELECT * FROM t1 ORDER BY a, b"
        2    "SELECT rowid FROM t1 WHERE a MATCH 'five'"
        3    "SELECT * FROM t1 WHERE a MATCH 'five'"
        4    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'jk'"
        5    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'tug* OR eight'"
  
        6    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'aa'"
        7    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH '44'"
        8    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'a*'"
      } {
        do_test backcompat-3.5 [list sql1 $q] [sql2 $q]
      }
  
      do_test backcompat-3.6 { 
        sql1 "SELECT optimize(t1) FROM t1 LIMIT 1" 
      } {{Index optimized}}
  
      foreach {n q} {
        1    "SELECT * FROM t1 ORDER BY a, b"
        2    "SELECT rowid FROM t1 WHERE a MATCH 'five'"
        3    "SELECT * FROM t1 WHERE a MATCH 'five'"
        4    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'jk'"
        5    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'tug* OR eight'"
  
        6    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'aa'"
        7    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH '44'"
        8    "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'a*'"
      } {
        do_test backcompat-3.7 [list sql1 $q] [sql2 $q]
      }
    }
  }
}

#-------------------------------------------------------------------------
# Test that Rtree tables may be read/written by different versions of 
# SQLite. 
#
ifcapable rtree {
  set contents {
    CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2, y1, y2);
  }
  foreach {id x1 x2 y1 y2} {
    1    -47.64 43.87    33.86 34.42        2    -21.51 17.32    2.05 31.04
    3    -43.67 -38.33    -19.79 3.43       4    32.41 35.16    9.12 19.82
    5    33.28 34.87    14.78 28.26         6    49.31 116.59    -9.87 75.09
    7    -14.93 34.51    -17.64 64.09       8    -43.05 23.43    -1.19 69.44
    9    44.79 133.56    28.09 80.30        10    -2.66 81.47    -41.38 -10.46
    11    -42.89 -3.54    15.76 71.63       12    -3.50 84.96    -11.64 64.95
    13    -45.69 26.25    11.14 55.06       14    -44.09 11.23    17.52 44.45
    15    36.23 133.49    -19.38 53.67      16    -17.89 81.54    14.64 50.61
    17    -41.97 -24.04    -39.43 28.95     18    -5.85 7.76    -6.38 47.02
    19    18.82 27.10    42.82 100.09       20    39.17 113.45    26.14 73.47
    21    22.31 103.17    49.92 106.05      22    -43.06 40.38    -1.75 76.08
    23    2.43 57.27    -14.19 -3.83        24    -47.57 -4.35    8.93 100.06
    25    -37.47 49.14    -29.11 8.81       26    -7.86 75.72    49.34 107.42
    27    1.53 45.49    20.36 49.74         28    -48.48 32.54    28.81 54.45
    29    2.67 39.77    -4.05 13.67         30    4.11 62.88    -47.44 -5.72
    31    -21.47 51.75    37.25 116.09      32    45.59 111.37    -6.43 43.64
    33    35.23 48.29    23.54 113.33       34    16.61 68.35    -14.69 65.97
    35    13.98 16.60    48.66 102.87       36    19.74 23.84    31.15 77.27
    37    -27.61 24.43    7.96 94.91        38    -34.77 12.05    -22.60 -6.29
    39    -25.83 8.71    -13.48 -12.53      40    -17.11 -1.01    18.06 67.89
    41    14.13 71.72    -3.78 39.25        42    23.75 76.00    -16.30 8.23
    43    -39.15 28.63    38.12 125.88      44    48.62 86.09    36.49 102.95
    45    -31.39 -21.98    2.52 89.78       46    5.65 56.04    15.94 89.10
    47    18.28 95.81    46.46 143.08       48    30.93 102.82    -20.08 37.36
    49    -20.78 -3.48    -5.58 35.46       50    49.85 90.58    -24.48 46.29
  } {
  if {$x1 >= $x2 || $y1 >= $y2} { error "$x1 $x2 $y1 $y2" }
    append contents "INSERT INTO t1 VALUES($id, $x1, $x2, $y1, $y2);"
  }
  set queries {
    1    "SELECT id FROM t1 WHERE x1>10 AND x2<44"
    2    "SELECT id FROM t1 WHERE y1<100"
    3    "SELECT id FROM t1 WHERE y1<100 AND x1>0"
    4    "SELECT id FROM t1 WHERE y1>10 AND x1>0 AND x2<50 AND y2<550"
  }
  do_allbackcompat_test {
    if {[code1 {set ::sqlite_options(fts3)}]
     && [code2 {set ::sqlite_options(fts3)}]
    } {
  
      do_test backcompat-4.1 { sql1 $contents } {}
  
      foreach {n q} $::queries {
        do_test backcompat-4.2.$n [list sql1 $q] [sql2 $q]
      }
  
      do_test backcompat-4.3 { sql1 {
        INSERT INTO t1 SELECT id+100, x1+10.0, x2+10.0, y1-10.0, y2-10.0 FROM t1;
      } } {}
  
      foreach {n q} $::queries {
        do_test backcompat-4.4.$n [list sql1 $q] [sql2 $q]
      }
  
      do_test backcompat-4.5 { sql2 {
        INSERT INTO t1 SELECT id+200, x1+20.0, x2+20.0, y1-20.0, y2-20.0 FROM t1;
      } } {}
  
      foreach {n q} $::queries {
        do_test backcompat-4.6.$n [list sql1 $q] [sql2 $q]
      }
  
    }
  }
}

finish_test

      }
      default {
        error "bad option \"$k\": must be -deferred"
      }
    }
  }

  get_near_results $tbl $match $deferred aHit
  get_near_results $tbl [string map {AND OR} $match] $deferred aMatchinfo

  set matchinfo_asc [list]
  foreach docid [lsort -integer -incr [array names aHit]] {
    lappend matchinfo_asc $docid $aMatchinfo($docid)
  }
  set matchinfo_desc [list]
  foreach docid [lsort -integer -decr [array names aHit]] {
    lappend matchinfo_desc $docid $aMatchinfo($docid)
  }

  set title "(\"$match\" -> [llength [array names aHit]] rows)"

  do_execsql_test $tn$title.1 "
    SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid ASC
  " [lsort -integer -incr [array names aHit]] 

  do_execsql_test $tn$title.2 "
    SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid DESC
  " [lsort -integer -decr [array names aHit]] 

  do_execsql_test $tn$title.3 "
    SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl 
    WHERE $tbl MATCH '$match' ORDER BY docid DESC
  " $matchinfo_desc

  do_execsql_test $tn$title.4 "

    execsql { INSERT INTO t1 VALUES($a, $b, $c, $d) }
  }

  do_fts3query_test 6.$tn.1 t1 {b:G}
  do_fts3query_test 6.$tn.2 t1 {b:G AND c:I}
  do_fts3query_test 6.$tn.3 t1 {b:G NEAR c:I}
  do_fts3query_test 6.$tn.4 t1 {a:C OR b:G OR c:K OR d:C}

  do_fts3query_test 6.$tn.5 t1 {a:G OR b:G}

  catchsql { COMMIT }
}

foreach {tn create} {
  1    "fts4(x)"

  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {64 ok}
do_test pager1.4.2.4 {
  faultsim_restore_and_reopen
  hexio_write test.db-journal [expr [file size test.db-journal]-30] 123456
  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {4 ok}
do_test pager1.4.2.5 {
  faultsim_restore_and_reopen
  hexio_write test.db-journal [expr [file size test.db-journal]-30] 123456
  foreach f [glob test.db-mj*] { forcedelete $f }
  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {4 ok}
}

    INSERT INTO t1 VALUES(1,2,3);
    SELECT * FROM t1;
  }
} {1 2 3}
do_test schema5-1.4 {
  catchsql {INSERT INTO t1 VALUES(10,11,12);}
} {1 {constraint two failed}}
do_test schema5-1.5 {
  db eval {
    DROP TABLE t1;
    CREATE TABLE t1(a,b,c,
       UNIQUE(a) CONSTRAINT one,
       PRIMARY KEY(b,c) CONSTRAINT two
    );
    INSERT INTO t1 VALUES(1,2,3);
  }
} {}
do_test schema5-1.6 {
  catchsql {INSERT INTO t1 VALUES(1,3,4)}
} {1 {column a is not unique}}
do_test schema5-1.7 {
  catchsql {INSERT INTO t1 VALUES(10,2,3)}
} {1 {columns b, c are not unique}}



    

finish_test

do_test shell1-3.2.4 {
  # too many arguments
  catchcmd "test.db" ".bail OFF BAD"
} {1 {Error: unknown command or invalid arguments:  "bail". Enter ".help" for help}}

# .databases             List names and files of attached databases
do_test shell1-3.3.1 {
  catchcmd "-csv test.db" ".databases"
} {/0 +.*main +.*test.db.*/}

do_test shell1-3.3.2 {
  # too many arguments
  catchcmd "test.db" ".databases BAD"
} {1 {Error: unknown command or invalid arguments:  "databases". Enter ".help" for help}}

# .dump ?TABLE? ...      Dump the database in an SQL text format
#                          If TABLE specified, only dump tables matching

  foreach f [glob -nocomplain test.db*] { forcedelete $f }
  sqlite3 db $file
}

# If the library is compiled with the SQLITE_DEFAULT_AUTOVACUUM macro set
# to non-zero, then set the global variable $AUTOVACUUM to 1.
set AUTOVACUUM $sqlite_options(default_autovacuum)

# Make sure the FTS enhanced query syntax is disabled.
set sqlite_fts3_enable_parentheses 0

source $testdir/thread_common.tcl
source $testdir/malloc_common.tcl

# 2012 May 11
#
# 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 file implements tests to verify that ticket [bdc6bbbb38] has been
# fixed.  
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix tkt-bdc6bbbb38

# If SQLITE_ENABLE_FTS3 is defined, omit this file.
ifcapable !fts3 { finish_test ; return }
set sqlite_fts3_enable_parentheses 1

foreach {tn idxdir} {1 ASC 2 DESC} {
  execsql { DROP TABLE IF EXISTS t2 }

  do_execsql_test $tn.1.1 "CREATE VIRTUAL TABLE t2 USING fts4(x, order=$idxdir)"
  do_execsql_test $tn.1.2 { INSERT INTO t2 VALUES('a b c') }

  do_execsql_test $tn.1.3 {
    SELECT offsets(t2) FROM t2 WHERE t2 MATCH 'a AND d OR b' ORDER BY docid ASC
  } {
    {0 0 0 1 0 2 2 1}
  }
  do_execsql_test $tn.1.4 {
    SELECT snippet(t2,'[',']') FROM t2 WHERE t2 MATCH 'a AND d OR b' 
    ORDER BY docid ASC
  } {
    {[a] [b] c}
  }
  do_execsql_test $tn.1.5 { INSERT INTO t2 VALUES('a c d') }
  do_execsql_test $tn.1.6 {
    SELECT offsets(t2) FROM t2 WHERE t2 MATCH 'a AND d OR b' ORDER BY docid ASC
  } {
    {0 0 0 1 0 2 2 1}
    {0 0 0 1 0 1 4 1}
  }
  do_execsql_test $tn.1.7 {
    SELECT snippet(t2,'[',']') FROM t2 WHERE t2 MATCH 'a AND d OR b'
    ORDER BY docid ASC
  } {
    {[a] [b] c}
    {[a] c [d]}
  }

  execsql { DROP TABLE IF EXISTS t3 }
  do_execsql_test $tn.2.1 "CREATE VIRTUAL TABLE t3 USING fts4(x, order=$idxdir)"
  do_execsql_test $tn.2.2 { INSERT INTO t3 VALUES('a c d') }
  do_execsql_test $tn.2.3 {
    SELECT offsets(t3) FROM t3 WHERE t3 MATCH 'a AND d OR b' ORDER BY docid DESC
  } {
    {0 0 0 1 0 1 4 1}
  }
  do_execsql_test $tn.2.4 {
    SELECT snippet(t3,'[',']') FROM t3 WHERE t3 MATCH 'a AND d OR b'
      ORDER BY docid DESC
  } {
    {[a] c [d]}
  }
  do_execsql_test $tn.2.5 { 
    INSERT INTO t3 VALUES('a b c');
  }
  do_execsql_test $tn.2.6 {
    SELECT offsets(t3) FROM t3 WHERE t3 MATCH 'a AND d OR b' ORDER BY docid DESC
  } {
    {0 0 0 1 0 2 2 1}
    {0 0 0 1 0 1 4 1}
  }
  do_execsql_test $tn.2.7 {
    SELECT snippet(t3,'[',']') FROM t3 WHERE t3 MATCH 'a AND d OR b'
      ORDER BY docid DESC
  } {
    {[a] [b] c}
    {[a] c [d]}
  }
}

set sqlite_fts3_enable_parentheses 0
finish_test

  set blob
}

proc logcksum {ckv1 ckv2 blob} {
  upvar $ckv1 c1
  upvar $ckv2 c2

  # Since the magic number at the start of the -wal file header is
  # 931071618 that indicates that the content should always be read as
  # little-endian.
  # 
  set scanpattern i*


  binary scan $blob $scanpattern values
  foreach {v1 v2} $values {
    set c1 [expr {($c1 + $v1 + $c2)&0xFFFFFFFF}]
    set c2 [expr {($c2 + $v2 + $c1)&0xFFFFFFFF}]
  }
}

  set nInt [expr {$nHdr/4}]

  if {[llength $args]>2} {
    error {wrong # args: should be "set_tvfs_hdr fileName ?val1? ?val2?"}
  }

  set blob [tvfs shm $file]
  if {$::tcl_platform(byteOrder)=="bigEndian"} {set fmt I} {set fmt i}

  if {[llength $args]} {
    set ia [lindex $args 0]
    set ib $ia
    if {[llength $args]==2} {
      set ib [lindex $args 1]
    }
    binary scan $blob a[expr $nHdr*2]a* dummy tail
    set blob [binary format ${fmt}${nInt}${fmt}${nInt}a* $ia $ib $tail]
    tvfs shm $file $blob
  }

  binary scan $blob ${fmt}${nInt} ints
  return $ints
}

proc incr_tvfs_hdr {file idx incrval} {
  set ints [set_tvfs_hdr $file]
  set v [lindex $ints $idx]
  incr v $incrval