/ Check-in [541e2b48]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the OS-X branch to include all trunk changes through version 3.6.22.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | apple-osx
Files: files | file ages | folders
SHA1: 541e2b488e4e40706e457c6d3b5647d53da02ce9
User & Date: drh 2010-01-06 13:12:48
Context
2010-01-19
23:50
robustness fixes for preventing a finalized statement from being reused check-in: a7a0c8d6 user: adam tags: apple-osx
2010-01-06
13:12
Update the OS-X branch to include all trunk changes through version 3.6.22. check-in: 541e2b48 user: drh tags: apple-osx
13:07
Fix an issue with lemon generating incorrect grammars. This issue does not effect SQLite. check-in: 077a6bee user: drh tags: trunk
2009-12-23
18:06
Fix the main.mk makefile so that the sqlrr extension is built into the amalgamation correctly. check-in: a3204d8a user: drh tags: apple-osx
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -3.6.21
            1  +3.6.22

Changes to configure.

     1      1   #! /bin/sh
     2      2   # Guess values for system-dependent variables and create Makefiles.
     3         -# Generated by GNU Autoconf 2.62 for sqlite 3.6.21.
            3  +# Generated by GNU Autoconf 2.62 for sqlite 3.6.22.
     4      4   #
     5      5   # Copyright (C) 1992, 1993, 1994, 1995, 1996, 1998, 1999, 2000, 2001,
     6      6   # 2002, 2003, 2004, 2005, 2006, 2007, 2008 Free Software Foundation, Inc.
     7      7   # This configure script is free software; the Free Software Foundation
     8      8   # gives unlimited permission to copy, distribute and modify it.
     9      9   ## --------------------- ##
    10     10   ## M4sh Initialization.  ##
................................................................................
   739    739   MFLAGS=
   740    740   MAKEFLAGS=
   741    741   SHELL=${CONFIG_SHELL-/bin/sh}
   742    742   
   743    743   # Identity of this package.
   744    744   PACKAGE_NAME='sqlite'
   745    745   PACKAGE_TARNAME='sqlite'
   746         -PACKAGE_VERSION='3.6.21'
   747         -PACKAGE_STRING='sqlite 3.6.21'
          746  +PACKAGE_VERSION='3.6.22'
          747  +PACKAGE_STRING='sqlite 3.6.22'
   748    748   PACKAGE_BUGREPORT=''
   749    749   
   750    750   # Factoring default headers for most tests.
   751    751   ac_includes_default="\
   752    752   #include <stdio.h>
   753    753   #ifdef HAVE_SYS_TYPES_H
   754    754   # include <sys/types.h>
................................................................................
  1483   1483   #
  1484   1484   # Report the --help message.
  1485   1485   #
  1486   1486   if test "$ac_init_help" = "long"; then
  1487   1487     # Omit some internal or obsolete options to make the list less imposing.
  1488   1488     # This message is too long to be a string in the A/UX 3.1 sh.
  1489   1489     cat <<_ACEOF
  1490         -\`configure' configures sqlite 3.6.21 to adapt to many kinds of systems.
         1490  +\`configure' configures sqlite 3.6.22 to adapt to many kinds of systems.
  1491   1491   
  1492   1492   Usage: $0 [OPTION]... [VAR=VALUE]...
  1493   1493   
  1494   1494   To assign environment variables (e.g., CC, CFLAGS...), specify them as
  1495   1495   VAR=VALUE.  See below for descriptions of some of the useful variables.
  1496   1496   
  1497   1497   Defaults for the options are specified in brackets.
................................................................................
  1548   1548     --build=BUILD     configure for building on BUILD [guessed]
  1549   1549     --host=HOST       cross-compile to build programs to run on HOST [BUILD]
  1550   1550   _ACEOF
  1551   1551   fi
  1552   1552   
  1553   1553   if test -n "$ac_init_help"; then
  1554   1554     case $ac_init_help in
  1555         -     short | recursive ) echo "Configuration of sqlite 3.6.21:";;
         1555  +     short | recursive ) echo "Configuration of sqlite 3.6.22:";;
  1556   1556      esac
  1557   1557     cat <<\_ACEOF
  1558   1558   
  1559   1559   Optional Features:
  1560   1560     --disable-option-checking  ignore unrecognized --enable/--with options
  1561   1561     --disable-FEATURE       do not include FEATURE (same as --enable-FEATURE=no)
  1562   1562     --enable-FEATURE[=ARG]  include FEATURE [ARG=yes]
................................................................................
  1666   1666       cd "$ac_pwd" || { ac_status=$?; break; }
  1667   1667     done
  1668   1668   fi
  1669   1669   
  1670   1670   test -n "$ac_init_help" && exit $ac_status
  1671   1671   if $ac_init_version; then
  1672   1672     cat <<\_ACEOF
  1673         -sqlite configure 3.6.21
         1673  +sqlite configure 3.6.22
  1674   1674   generated by GNU Autoconf 2.62
  1675   1675   
  1676   1676   Copyright (C) 1992, 1993, 1994, 1995, 1996, 1998, 1999, 2000, 2001,
  1677   1677   2002, 2003, 2004, 2005, 2006, 2007, 2008 Free Software Foundation, Inc.
  1678   1678   This configure script is free software; the Free Software Foundation
  1679   1679   gives unlimited permission to copy, distribute and modify it.
  1680   1680   _ACEOF
  1681   1681     exit
  1682   1682   fi
  1683   1683   cat >config.log <<_ACEOF
  1684   1684   This file contains any messages produced by compilers while
  1685   1685   running configure, to aid debugging if configure makes a mistake.
  1686   1686   
  1687         -It was created by sqlite $as_me 3.6.21, which was
         1687  +It was created by sqlite $as_me 3.6.22, which was
  1688   1688   generated by GNU Autoconf 2.62.  Invocation command line was
  1689   1689   
  1690   1690     $ $0 $@
  1691   1691   
  1692   1692   _ACEOF
  1693   1693   exec 5>>config.log
  1694   1694   {
................................................................................
 13968  13968   
 13969  13969   exec 6>&1
 13970  13970   
 13971  13971   # Save the log message, to keep $[0] and so on meaningful, and to
 13972  13972   # report actual input values of CONFIG_FILES etc. instead of their
 13973  13973   # values after options handling.
 13974  13974   ac_log="
 13975         -This file was extended by sqlite $as_me 3.6.21, which was
        13975  +This file was extended by sqlite $as_me 3.6.22, which was
 13976  13976   generated by GNU Autoconf 2.62.  Invocation command line was
 13977  13977   
 13978  13978     CONFIG_FILES    = $CONFIG_FILES
 13979  13979     CONFIG_HEADERS  = $CONFIG_HEADERS
 13980  13980     CONFIG_LINKS    = $CONFIG_LINKS
 13981  13981     CONFIG_COMMANDS = $CONFIG_COMMANDS
 13982  13982     $ $0 $@
................................................................................
 14021  14021   $config_commands
 14022  14022   
 14023  14023   Report bugs to <bug-autoconf@gnu.org>."
 14024  14024   
 14025  14025   _ACEOF
 14026  14026   cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1
 14027  14027   ac_cs_version="\\
 14028         -sqlite config.status 3.6.21
        14028  +sqlite config.status 3.6.22
 14029  14029   configured by $0, generated by GNU Autoconf 2.62,
 14030  14030     with options \\"`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`\\"
 14031  14031   
 14032  14032   Copyright (C) 2008 Free Software Foundation, Inc.
 14033  14033   This config.status script is free software; the Free Software Foundation
 14034  14034   gives unlimited permission to copy, distribute and modify it."
 14035  14035   

Changes to ext/fts3/fts3.c.

   793    793   ** on the xClose method of the virtual table interface.
   794    794   */
   795    795   static int fulltextClose(sqlite3_vtab_cursor *pCursor){
   796    796     Fts3Cursor *pCsr = (Fts3Cursor *)pCursor;
   797    797     sqlite3_finalize(pCsr->pStmt);
   798    798     sqlite3Fts3ExprFree(pCsr->pExpr);
   799    799     sqlite3_free(pCsr->aDoclist);
          800  +  sqlite3_free(pCsr->aMatchinfo);
   800    801     sqlite3_free(pCsr);
   801    802     return SQLITE_OK;
   802    803   }
   803    804   
   804    805   static int fts3CursorSeek(sqlite3_context *pContext, Fts3Cursor *pCsr){
   805    806     if( pCsr->isRequireSeek ){
   806    807       pCsr->isRequireSeek = 0;
................................................................................
   838    839       }
   839    840     }else if( pCsr->pNextId>=&pCsr->aDoclist[pCsr->nDoclist] ){
   840    841       pCsr->isEof = 1;
   841    842     }else{
   842    843       sqlite3_reset(pCsr->pStmt);
   843    844       fts3GetDeltaVarint(&pCsr->pNextId, &pCsr->iPrevId);
   844    845       pCsr->isRequireSeek = 1;
          846  +    pCsr->isMatchinfoOk = 1;
   845    847     }
   846    848     return rc;
   847    849   }
   848    850   
   849    851   
   850    852   /*
   851    853   ** The buffer pointed to by argument zNode (size nNode bytes) contains the
................................................................................
   953    955     sqlite3_int64 iVal              /* Write this value to the list */
   954    956   ){
   955    957     assert( iVal-*piPrev > 0 || (*piPrev==0 && iVal==0) );
   956    958     *pp += sqlite3Fts3PutVarint(*pp, iVal-*piPrev);
   957    959     *piPrev = iVal;
   958    960   }
   959    961   
          962  +/*
          963  +** When this function is called, *ppPoslist is assumed to point to the 
          964  +** start of a position-list.
          965  +*/
   960    966   static void fts3PoslistCopy(char **pp, char **ppPoslist){
   961    967     char *pEnd = *ppPoslist;
   962    968     char c = 0;
          969  +
          970  +  /* The end of a position list is marked by a zero encoded as an FTS3 
          971  +  ** varint. A single 0x00 byte. Except, if the 0x00 byte is preceded by
          972  +  ** a byte with the 0x80 bit set, then it is not a varint 0, but the tail
          973  +  ** of some other, multi-byte, value.
          974  +  **
          975  +  ** The following block moves pEnd to point to the first byte that is not 
          976  +  ** immediately preceded by a byte with the 0x80 bit set. Then increments
          977  +  ** pEnd once more so that it points to the byte immediately following the
          978  +  ** last byte in the position-list.
          979  +  */
   963    980     while( *pEnd | c ) c = *pEnd++ & 0x80;
   964    981     pEnd++;
          982  +
   965    983     if( pp ){
   966    984       int n = (int)(pEnd - *ppPoslist);
   967    985       char *p = *pp;
   968    986       memcpy(p, *ppPoslist, n);
   969    987       p += n;
   970    988       *pp = p;
   971    989     }
................................................................................
  1306   1324     assert( mergetype==MERGE_OR     || mergetype==MERGE_POS_OR 
  1307   1325          || mergetype==MERGE_AND    || mergetype==MERGE_NOT
  1308   1326          || mergetype==MERGE_PHRASE || mergetype==MERGE_POS_PHRASE
  1309   1327          || mergetype==MERGE_NEAR   || mergetype==MERGE_POS_NEAR
  1310   1328     );
  1311   1329   
  1312   1330     if( !aBuffer ){
         1331  +    *pnBuffer = 0;
  1313   1332       return SQLITE_NOMEM;
  1314   1333     }
  1315   1334   
  1316   1335     /* Read the first docid from each doclist */
  1317   1336     fts3GetDeltaVarint2(&p1, pEnd1, &i1);
  1318   1337     fts3GetDeltaVarint2(&p2, pEnd2, &i2);
  1319   1338   
................................................................................
  1674   1693         if( ii==pPhrase->nToken-1 && !isReqPos ){
  1675   1694           mergetype = MERGE_PHRASE;
  1676   1695         }
  1677   1696         fts3DoclistMerge(mergetype, 0, 0, pList, &nOut, pOut, nOut, pList, nList);
  1678   1697         sqlite3_free(pOut);
  1679   1698         pOut = pList;
  1680   1699       }
         1700  +    assert( nOut==0 || pOut!=0 );
  1681   1701     }
  1682   1702   
  1683   1703     if( rc==SQLITE_OK ){
  1684   1704       *paOut = pOut;
  1685   1705       *pnOut = nOut;
  1686   1706     }else{
  1687   1707       sqlite3_free(pOut);
................................................................................
  1693   1713   ** Evaluate the full-text expression pExpr against fts3 table pTab. Store
  1694   1714   ** the resulting doclist in *paOut and *pnOut.
  1695   1715   */
  1696   1716   static int evalFts3Expr(
  1697   1717     Fts3Table *p,                   /* Virtual table handle */
  1698   1718     Fts3Expr *pExpr,                /* Parsed fts3 expression */
  1699   1719     char **paOut,                   /* OUT: Pointer to malloc'd result buffer */
  1700         -  int *pnOut                      /* OUT: Size of buffer at *paOut */
         1720  +  int *pnOut,                     /* OUT: Size of buffer at *paOut */
         1721  +  int isReqPos                    /* Require positions in output buffer */
  1701   1722   ){
  1702   1723     int rc = SQLITE_OK;             /* Return code */
  1703   1724   
  1704   1725     /* Zero the output parameters. */
  1705   1726     *paOut = 0;
  1706   1727     *pnOut = 0;
  1707   1728   
  1708   1729     if( pExpr ){
         1730  +    assert( pExpr->eType==FTSQUERY_PHRASE 
         1731  +         || pExpr->eType==FTSQUERY_NEAR 
         1732  +         || isReqPos==0
         1733  +    );
  1709   1734       if( pExpr->eType==FTSQUERY_PHRASE ){
  1710         -      int isReqPos = (pExpr->pParent && pExpr->pParent->eType==FTSQUERY_NEAR);
  1711         -      rc = fts3PhraseSelect(p, pExpr->pPhrase, isReqPos, paOut, pnOut);
         1735  +      rc = fts3PhraseSelect(p, pExpr->pPhrase, 
         1736  +          isReqPos || (pExpr->pParent && pExpr->pParent->eType==FTSQUERY_NEAR),
         1737  +          paOut, pnOut
         1738  +      );
  1712   1739       }else{
  1713   1740         char *aLeft;
  1714   1741         char *aRight;
  1715   1742         int nLeft;
  1716   1743         int nRight;
  1717   1744   
  1718         -      if( SQLITE_OK==(rc = evalFts3Expr(p, pExpr->pRight, &aRight, &nRight))
  1719         -       && SQLITE_OK==(rc = evalFts3Expr(p, pExpr->pLeft, &aLeft, &nLeft))
         1745  +      if( 0==(rc = evalFts3Expr(p, pExpr->pRight, &aRight, &nRight, isReqPos))
         1746  +       && 0==(rc = evalFts3Expr(p, pExpr->pLeft, &aLeft, &nLeft, isReqPos))
  1720   1747         ){
  1721   1748           assert( pExpr->eType==FTSQUERY_NEAR || pExpr->eType==FTSQUERY_OR     
  1722   1749               || pExpr->eType==FTSQUERY_AND  || pExpr->eType==FTSQUERY_NOT
  1723   1750           );
  1724   1751           switch( pExpr->eType ){
  1725   1752             case FTSQUERY_NEAR: {
  1726   1753               Fts3Expr *pLeft;
  1727   1754               Fts3Expr *pRight;
  1728         -            int mergetype = MERGE_NEAR;
         1755  +            int mergetype = isReqPos ? MERGE_POS_NEAR : MERGE_NEAR;
  1729   1756               int nParam1;
  1730   1757               int nParam2;
  1731   1758               char *aBuffer;
  1732   1759              
  1733   1760               if( pExpr->pParent && pExpr->pParent->eType==FTSQUERY_NEAR ){
  1734   1761                 mergetype = MERGE_POS_NEAR;
  1735   1762               }
................................................................................
  1864   1891       }
  1865   1892   
  1866   1893       rc = sqlite3Fts3ExprParse(p->pTokenizer, p->azColumn, p->nColumn, 
  1867   1894           iCol, zQuery, -1, &pCsr->pExpr
  1868   1895       );
  1869   1896       if( rc!=SQLITE_OK ) return rc;
  1870   1897   
  1871         -    rc = evalFts3Expr(p, pCsr->pExpr, &pCsr->aDoclist, &pCsr->nDoclist);
         1898  +    rc = evalFts3Expr(p, pCsr->pExpr, &pCsr->aDoclist, &pCsr->nDoclist, 0);
  1872   1899       pCsr->pNextId = pCsr->aDoclist;
  1873   1900       pCsr->iPrevId = 0;
  1874   1901     }
  1875   1902   
  1876   1903     if( rc!=SQLITE_OK ) return rc;
  1877   1904     return fts3NextMethod(pCursor);
  1878   1905   }
................................................................................
  1985   2012   ** Implementation of xRollback(). Discard the contents of the pending-terms
  1986   2013   ** hash-table. Any changes made to the database are reverted by SQLite.
  1987   2014   */
  1988   2015   static int fts3RollbackMethod(sqlite3_vtab *pVtab){
  1989   2016     sqlite3Fts3PendingTermsClear((Fts3Table *)pVtab);
  1990   2017     return SQLITE_OK;
  1991   2018   }
         2019  +
         2020  +/*
         2021  +** Load the doclist associated with expression pExpr to pExpr->aDoclist.
         2022  +** The loaded doclist contains positions as well as the document ids.
         2023  +** This is used by the matchinfo(), snippet() and offsets() auxillary
         2024  +** functions.
         2025  +*/
         2026  +int sqlite3Fts3ExprLoadDoclist(Fts3Table *pTab, Fts3Expr *pExpr){
         2027  +  return evalFts3Expr(pTab, pExpr, &pExpr->aDoclist, &pExpr->nDoclist, 1);
         2028  +}
         2029  +
         2030  +/*
         2031  +** After ExprLoadDoclist() (see above) has been called, this function is
         2032  +** used to iterate through the position lists that make up the doclist
         2033  +** stored in pExpr->aDoclist.
         2034  +*/
         2035  +char *sqlite3Fts3FindPositions(
         2036  +  Fts3Expr *pExpr,                /* Access this expressions doclist */
         2037  +  sqlite3_int64 iDocid,           /* Docid associated with requested pos-list */
         2038  +  int iCol                        /* Column of requested pos-list */
         2039  +){
         2040  +  assert( pExpr->isLoaded );
         2041  +  if( pExpr->aDoclist ){
         2042  +    char *pEnd = &pExpr->aDoclist[pExpr->nDoclist];
         2043  +    char *pCsr = pExpr->pCurrent;
         2044  +
         2045  +    assert( pCsr );
         2046  +    while( pCsr<pEnd ){
         2047  +      if( pExpr->iCurrent<iDocid ){
         2048  +        fts3PoslistCopy(0, &pCsr);
         2049  +        fts3GetDeltaVarint(&pCsr, &pExpr->iCurrent);
         2050  +        pExpr->pCurrent = pCsr;
         2051  +      }else{
         2052  +        if( pExpr->iCurrent==iDocid ){
         2053  +          int iThis = 0;
         2054  +          if( iCol<0 ){
         2055  +            /* If iCol is negative, return a pointer to the start of the
         2056  +            ** position-list (instead of a pointer to the start of a list
         2057  +            ** of offsets associated with a specific column).
         2058  +            */
         2059  +            return pCsr;
         2060  +          }
         2061  +          while( iThis<iCol ){
         2062  +            fts3ColumnlistCopy(0, &pCsr);
         2063  +            if( *pCsr==0x00 ) return 0;
         2064  +            pCsr++;
         2065  +            pCsr += sqlite3Fts3GetVarint32(pCsr, &iThis);
         2066  +          }
         2067  +          if( iCol==iThis ) return pCsr;
         2068  +        }
         2069  +        return 0;
         2070  +      }
         2071  +    }
         2072  +  }
         2073  +
         2074  +  return 0;
         2075  +}
  1992   2076   
  1993   2077   /*
  1994   2078   ** Helper function used by the implementation of the overloaded snippet(),
  1995   2079   ** offsets() and optimize() SQL functions.
  1996   2080   **
  1997   2081   ** If the value passed as the third argument is a blob of size
  1998   2082   ** sizeof(Fts3Cursor*), then the blob contents are copied to the 
................................................................................
  2052   2136     }
  2053   2137     if( !zEllipsis || !zEnd || !zStart ){
  2054   2138       sqlite3_result_error_nomem(pContext);
  2055   2139     }else if( SQLITE_OK==fts3CursorSeek(pContext, pCsr) ){
  2056   2140       sqlite3Fts3Snippet(pContext, pCsr, zStart, zEnd, zEllipsis);
  2057   2141     }
  2058   2142   }
         2143  +
         2144  +/*
         2145  +** Implementation of the snippet2() function for FTS3
         2146  +*/
         2147  +static void fts3Snippet2Func(
         2148  +  sqlite3_context *pContext,      /* SQLite function call context */
         2149  +  int nVal,                       /* Size of apVal[] array */
         2150  +  sqlite3_value **apVal           /* Array of arguments */
         2151  +){
         2152  +  Fts3Cursor *pCsr;               /* Cursor handle passed through apVal[0] */
         2153  +  const char *zStart = "<b>";
         2154  +  const char *zEnd = "</b>";
         2155  +  const char *zEllipsis = "<b>...</b>";
         2156  +  int iCol = -1;
         2157  +  int nToken = 10;
         2158  +
         2159  +  /* There must be at least one argument passed to this function (otherwise
         2160  +  ** the non-overloaded version would have been called instead of this one).
         2161  +  */
         2162  +  assert( nVal>=1 );
         2163  +
         2164  +  if( nVal>6 ){
         2165  +    sqlite3_result_error(pContext, 
         2166  +        "wrong number of arguments to function snippet()", -1);
         2167  +    return;
         2168  +  }
         2169  +  if( fts3FunctionArg(pContext, "snippet", apVal[0], &pCsr) ) return;
         2170  +
         2171  +  switch( nVal ){
         2172  +    case 6: nToken = sqlite3_value_int(apVal[5]);
         2173  +    case 5: iCol = sqlite3_value_int(apVal[4]);
         2174  +    case 4: zEllipsis = (const char*)sqlite3_value_text(apVal[3]);
         2175  +    case 3: zEnd = (const char*)sqlite3_value_text(apVal[2]);
         2176  +    case 2: zStart = (const char*)sqlite3_value_text(apVal[1]);
         2177  +  }
         2178  +  if( !zEllipsis || !zEnd || !zStart ){
         2179  +    sqlite3_result_error_nomem(pContext);
         2180  +  }else if( SQLITE_OK==fts3CursorSeek(pContext, pCsr) ){
         2181  +    sqlite3Fts3Snippet2(pContext, pCsr, zStart, zEnd, zEllipsis, iCol, nToken);
         2182  +  }
         2183  +}
  2059   2184   
  2060   2185   /*
  2061   2186   ** Implementation of the offsets() function for FTS3
  2062   2187   */
  2063   2188   static void fts3OffsetsFunc(
  2064   2189     sqlite3_context *pContext,      /* SQLite function call context */
  2065   2190     int nVal,                       /* Size of argument array */
................................................................................
  2112   2237         sqlite3_result_text(pContext, "Index already optimal", -1, SQLITE_STATIC);
  2113   2238         break;
  2114   2239       default:
  2115   2240         sqlite3_result_error_code(pContext, rc);
  2116   2241         break;
  2117   2242     }
  2118   2243   }
         2244  +
         2245  +/*
         2246  +** Implementation of the matchinfo() function for FTS3
         2247  +*/
         2248  +static void fts3MatchinfoFunc(
         2249  +  sqlite3_context *pContext,      /* SQLite function call context */
         2250  +  int nVal,                       /* Size of argument array */
         2251  +  sqlite3_value **apVal           /* Array of arguments */
         2252  +){
         2253  +  Fts3Cursor *pCsr;               /* Cursor handle passed through apVal[0] */
         2254  +
         2255  +  if( nVal!=1 ){
         2256  +    sqlite3_result_error(pContext,
         2257  +        "wrong number of arguments to function matchinfo()", -1);
         2258  +    return;
         2259  +  }
         2260  +
         2261  +  if( SQLITE_OK==fts3FunctionArg(pContext, "matchinfo", apVal[0], &pCsr) ){
         2262  +    sqlite3Fts3Matchinfo(pContext, pCsr);
         2263  +  }
         2264  +}
  2119   2265   
  2120   2266   /*
  2121   2267   ** This routine implements the xFindFunction method for the FTS3
  2122   2268   ** virtual table.
  2123   2269   */
  2124   2270   static int fts3FindFunctionMethod(
  2125   2271     sqlite3_vtab *pVtab,            /* Virtual table handle */
................................................................................
  2129   2275     void **ppArg                    /* Unused */
  2130   2276   ){
  2131   2277     struct Overloaded {
  2132   2278       const char *zName;
  2133   2279       void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
  2134   2280     } aOverload[] = {
  2135   2281       { "snippet", fts3SnippetFunc },
         2282  +    { "snippet2", fts3Snippet2Func },
  2136   2283       { "offsets", fts3OffsetsFunc },
  2137   2284       { "optimize", fts3OptimizeFunc },
         2285  +    { "matchinfo", fts3MatchinfoFunc },
  2138   2286     };
  2139   2287     int i;                          /* Iterator variable */
  2140   2288   
  2141   2289     UNUSED_PARAMETER(pVtab);
  2142   2290     UNUSED_PARAMETER(nArg);
  2143   2291     UNUSED_PARAMETER(ppArg);
  2144   2292   
................................................................................
  2277   2425     /* Create the virtual table wrapper around the hash-table and overload 
  2278   2426     ** the two scalar functions. If this is successful, register the
  2279   2427     ** module with sqlite.
  2280   2428     */
  2281   2429     if( SQLITE_OK==rc 
  2282   2430      && SQLITE_OK==(rc = sqlite3Fts3InitHashTable(db, pHash, "fts3_tokenizer"))
  2283   2431      && SQLITE_OK==(rc = sqlite3_overload_function(db, "snippet", -1))
         2432  +   && SQLITE_OK==(rc = sqlite3_overload_function(db, "snippet2", -1))
  2284   2433      && SQLITE_OK==(rc = sqlite3_overload_function(db, "offsets", 1))
         2434  +   && SQLITE_OK==(rc = sqlite3_overload_function(db, "matchinfo", -1))
  2285   2435      && SQLITE_OK==(rc = sqlite3_overload_function(db, "optimize", 1))
  2286   2436     ){
  2287   2437       return sqlite3_create_module_v2(
  2288   2438           db, "fts3", &fts3Module, (void *)pHash, hashDestroy
  2289   2439       );
  2290   2440     }
  2291   2441   

Changes to ext/fts3/fts3Int.h.

    66     66   # define ALWAYS(x) (x)
    67     67   # define NEVER(X)  (x)
    68     68   /*
    69     69   ** Internal types used by SQLite.
    70     70   */
    71     71   typedef unsigned char u8;         /* 1-byte (or larger) unsigned integer */
    72     72   typedef short int i16;            /* 2-byte (or larger) signed integer */
           73  +typedef unsigned int u32;         /* 4-byte unsigned integer */
           74  +typedef sqlite3_uint64 u64;       /* 8-byte unsigned integer */
    73     75   /*
    74     76   ** Macro used to suppress compiler warnings for unused parameters.
    75     77   */
    76     78   #define UNUSED_PARAMETER(x) (void)(x)
    77     79   #endif
    78     80   
    79     81   typedef struct Fts3Table Fts3Table;
................................................................................
   142    144     u8 isRequireSeek;               /* True if must seek pStmt to %_content row */
   143    145     sqlite3_stmt *pStmt;            /* Prepared statement in use by the cursor */
   144    146     Fts3Expr *pExpr;                /* Parsed MATCH query string */
   145    147     sqlite3_int64 iPrevId;          /* Previous id read from aDoclist */
   146    148     char *pNextId;                  /* Pointer into the body of aDoclist */
   147    149     char *aDoclist;                 /* List of docids for full-text queries */
   148    150     int nDoclist;                   /* Size of buffer at aDoclist */
          151  +  int isMatchinfoOk;              /* True when aMatchinfo[] matches iPrevId */
          152  +  u32 *aMatchinfo;
   149    153   };
   150    154   
   151    155   /*
   152    156   ** The Fts3Cursor.eSearch member is always set to one of the following.
   153    157   ** Actualy, Fts3Cursor.eSearch can be greater than or equal to
   154    158   ** FTS3_FULLTEXT_SEARCH.  If so, then Fts3Cursor.eSearch - 2 is the index
   155    159   ** of the column to be searched.  For example, in
................................................................................
   182    186       int n;                   /* Number of bytes in buffer pointed to by z */
   183    187       int isPrefix;            /* True if token ends in with a "*" character */
   184    188     } aToken[1];               /* One entry for each token in the phrase */
   185    189   };
   186    190   
   187    191   /*
   188    192   ** A tree of these objects forms the RHS of a MATCH operator.
          193  +**
          194  +** If Fts3Expr.eType is either FTSQUERY_NEAR or FTSQUERY_PHRASE and isLoaded
          195  +** is true, then aDoclist points to a malloced buffer, size nDoclist bytes, 
          196  +** containing the results of the NEAR or phrase query in FTS3 doclist
          197  +** format. As usual, the initial "Length" field found in doclists stored
          198  +** on disk is omitted from this buffer.
          199  +**
          200  +** Variable pCurrent always points to the start of a docid field within
          201  +** aDoclist. Since the doclist is usually scanned in docid order, this can
          202  +** be used to accelerate seeking to the required docid within the doclist.
   189    203   */
   190    204   struct Fts3Expr {
   191    205     int eType;                 /* One of the FTSQUERY_XXX values defined below */
   192    206     int nNear;                 /* Valid if eType==FTSQUERY_NEAR */
   193    207     Fts3Expr *pParent;         /* pParent->pLeft==this or pParent->pRight==this */
   194    208     Fts3Expr *pLeft;           /* Left operand */
   195    209     Fts3Expr *pRight;          /* Right operand */
   196    210     Fts3Phrase *pPhrase;       /* Valid if eType==FTSQUERY_PHRASE */
          211  +
          212  +  int isLoaded;              /* True if aDoclist/nDoclist are initialized. */
          213  +  char *aDoclist;            /* Buffer containing doclist */
          214  +  int nDoclist;              /* Size of aDoclist in bytes */
          215  +
          216  +  sqlite3_int64 iCurrent;
          217  +  char *pCurrent;
   197    218   };
   198    219   
   199    220   /*
   200    221   ** Candidate values for Fts3Query.eType. Note that the order of the first
   201    222   ** four values is in order of precedence when parsing expressions. For 
   202    223   ** example, the following:
   203    224   **
................................................................................
   252    273   /* fts3.c */
   253    274   int sqlite3Fts3PutVarint(char *, sqlite3_int64);
   254    275   int sqlite3Fts3GetVarint(const char *, sqlite_int64 *);
   255    276   int sqlite3Fts3GetVarint32(const char *, int *);
   256    277   int sqlite3Fts3VarintLen(sqlite3_uint64);
   257    278   void sqlite3Fts3Dequote(char *);
   258    279   
          280  +char *sqlite3Fts3FindPositions(Fts3Expr *, sqlite3_int64, int);
          281  +int sqlite3Fts3ExprLoadDoclist(Fts3Table *, Fts3Expr *);
          282  +
   259    283   /* fts3_tokenizer.c */
   260    284   const char *sqlite3Fts3NextToken(const char *, int *);
   261    285   int sqlite3Fts3InitHashTable(sqlite3 *, Fts3Hash *, const char *);
   262    286   int sqlite3Fts3InitTokenizer(Fts3Hash *pHash, 
   263    287     const char *, sqlite3_tokenizer **, const char **, char **
   264    288   );
   265    289   
   266    290   /* fts3_snippet.c */
   267    291   void sqlite3Fts3Offsets(sqlite3_context*, Fts3Cursor*);
   268    292   void sqlite3Fts3Snippet(sqlite3_context*, Fts3Cursor*, 
   269    293     const char *, const char *, const char *
   270    294   );
          295  +void sqlite3Fts3Snippet2(sqlite3_context *, Fts3Cursor *, const char *,
          296  +  const char *, const char *, int, int
          297  +);
          298  +void sqlite3Fts3Matchinfo(sqlite3_context *, Fts3Cursor *);
   271    299   
   272    300   /* fts3_expr.c */
   273    301   int sqlite3Fts3ExprParse(sqlite3_tokenizer *, 
   274    302     char **, int, int, const char *, int, Fts3Expr **
   275    303   );
   276    304   void sqlite3Fts3ExprFree(Fts3Expr *);
   277    305   #ifdef SQLITE_TEST
   278    306   int sqlite3Fts3ExprInitTestInterface(sqlite3 *db);
   279    307   #endif
   280    308   
   281    309   #endif /* _FTSINT_H */

Changes to ext/fts3/fts3_expr.c.

   731    731   /*
   732    732   ** Free a parsed fts3 query expression allocated by sqlite3Fts3ExprParse().
   733    733   */
   734    734   void sqlite3Fts3ExprFree(Fts3Expr *p){
   735    735     if( p ){
   736    736       sqlite3Fts3ExprFree(p->pLeft);
   737    737       sqlite3Fts3ExprFree(p->pRight);
          738  +    sqlite3_free(p->aDoclist);
   738    739       sqlite3_free(p);
   739    740     }
   740    741   }
   741    742   
   742    743   /****************************************************************************
   743    744   *****************************************************************************
   744    745   ** Everything after this point is just test code.

Changes to ext/fts3/fts3_snippet.c.

   726    726         sqlite3_result_error_nomem(pCtx);
   727    727       }
   728    728     }else{
   729    729       sqlite3_result_error_nomem(pCtx);
   730    730     }
   731    731     fts3SnippetFree(p);
   732    732   }
          733  +
          734  +/*************************************************************************
          735  +** Below this point is the alternative, experimental snippet() implementation.
          736  +*/
          737  +
          738  +#define SNIPPET_BUFFER_CHUNK  64
          739  +#define SNIPPET_BUFFER_SIZE   SNIPPET_BUFFER_CHUNK*4
          740  +#define SNIPPET_BUFFER_MASK   (SNIPPET_BUFFER_SIZE-1)
          741  +
          742  +static void fts3GetDeltaPosition(char **pp, int *piPos){
          743  +  int iVal;
          744  +  *pp += sqlite3Fts3GetVarint32(*pp, &iVal);
          745  +  *piPos += (iVal-2);
          746  +}
          747  +
          748  +/*
          749  +** Iterate through all phrase nodes in an FTS3 query, except those that
          750  +** are part of a sub-tree that is the right-hand-side of a NOT operator.
          751  +** For each phrase node found, the supplied callback function is invoked.
          752  +**
          753  +** If the callback function returns anything other than SQLITE_OK, 
          754  +** the iteration is abandoned and the error code returned immediately.
          755  +** Otherwise, SQLITE_OK is returned after a callback has been made for
          756  +** all eligible phrase nodes.
          757  +*/
          758  +static int fts3ExprIterate(
          759  +  Fts3Expr *pExpr,                /* Expression to iterate phrases of */
          760  +  int (*x)(Fts3Expr *, void *),   /* Callback function to invoke for phrases */
          761  +  void *pCtx                      /* Second argument to pass to callback */
          762  +){
          763  +  int rc;
          764  +  int eType = pExpr->eType;
          765  +  if( eType==FTSQUERY_NOT ){
          766  +    rc = SQLITE_OK;
          767  +  }else if( eType!=FTSQUERY_PHRASE ){
          768  +    assert( pExpr->pLeft && pExpr->pRight );
          769  +    rc = fts3ExprIterate(pExpr->pLeft, x, pCtx);
          770  +    if( rc==SQLITE_OK ){
          771  +      rc = fts3ExprIterate(pExpr->pRight, x, pCtx);
          772  +    }
          773  +  }else{
          774  +    rc = x(pExpr, pCtx);
          775  +  }
          776  +  return rc;
          777  +}
          778  +
          779  +typedef struct LoadDoclistCtx LoadDoclistCtx;
          780  +struct LoadDoclistCtx {
          781  +  Fts3Table *pTab;                /* FTS3 Table */
          782  +  int nPhrase;                    /* Number of phrases so far */
          783  +};
          784  +
          785  +static int fts3ExprLoadDoclistsCb(Fts3Expr *pExpr, void *ctx){
          786  +  int rc = SQLITE_OK;
          787  +  LoadDoclistCtx *p = (LoadDoclistCtx *)ctx;
          788  +  p->nPhrase++;
          789  +  if( pExpr->isLoaded==0 ){
          790  +    rc = sqlite3Fts3ExprLoadDoclist(p->pTab, pExpr);
          791  +    pExpr->isLoaded = 1;
          792  +    if( rc==SQLITE_OK && pExpr->aDoclist ){
          793  +      pExpr->pCurrent = pExpr->aDoclist;
          794  +      pExpr->pCurrent += sqlite3Fts3GetVarint(pExpr->pCurrent,&pExpr->iCurrent);
          795  +    }
          796  +  }
          797  +  return rc;
          798  +}
          799  +
          800  +static int fts3ExprLoadDoclists(Fts3Cursor *pCsr, int *pnPhrase){
          801  +  int rc;
          802  +  LoadDoclistCtx sCtx = {0, 0};
          803  +  sCtx.pTab = (Fts3Table *)pCsr->base.pVtab;
          804  +  rc = fts3ExprIterate(pCsr->pExpr, fts3ExprLoadDoclistsCb, (void *)&sCtx);
          805  +  *pnPhrase = sCtx.nPhrase;
          806  +  return rc;
          807  +}
          808  +
          809  +/*
          810  +** Each call to this function populates a chunk of a snippet-buffer 
          811  +** SNIPPET_BUFFER_CHUNK bytes in size.
          812  +**
          813  +** Return true if the end of the data has been reached (and all subsequent
          814  +** calls to fts3LoadSnippetBuffer() with the same arguments will be no-ops), 
          815  +** or false otherwise.
          816  +*/
          817  +static int fts3LoadSnippetBuffer(
          818  +  int iPos,                       /* Document token offset to load data for */
          819  +  u8 *aBuffer,                    /* Circular snippet buffer to populate */
          820  +  int nList,                      /* Number of position lists in appList */
          821  +  char **apList,                  /* IN/OUT: nList position list pointers */
          822  +  int *aiPrev                     /* IN/OUT: Previous positions read */
          823  +){
          824  +  int i;
          825  +  int nFin = 0;
          826  +
          827  +  assert( (iPos&(SNIPPET_BUFFER_CHUNK-1))==0 );
          828  +
          829  +  memset(&aBuffer[iPos&SNIPPET_BUFFER_MASK], 0, SNIPPET_BUFFER_CHUNK);
          830  +
          831  +  for(i=0; i<nList; i++){
          832  +    int iPrev = aiPrev[i];
          833  +    char *pList = apList[i];
          834  +
          835  +    if( !pList ){
          836  +      nFin++;
          837  +      continue;
          838  +    }
          839  +
          840  +    while( iPrev<(iPos+SNIPPET_BUFFER_CHUNK) ){
          841  +      if( iPrev>=iPos ){
          842  +        aBuffer[iPrev&SNIPPET_BUFFER_MASK] = (u8)(i+1);
          843  +      }
          844  +      if( 0==((*pList)&0xFE) ){
          845  +        nFin++;
          846  +        break;
          847  +      }
          848  +      fts3GetDeltaPosition(&pList, &iPrev); 
          849  +    }
          850  +
          851  +    aiPrev[i] = iPrev;
          852  +    apList[i] = pList;
          853  +  }
          854  +
          855  +  return (nFin==nList);
          856  +}
          857  +
          858  +typedef struct SnippetCtx SnippetCtx;
          859  +struct SnippetCtx {
          860  +  Fts3Cursor *pCsr;
          861  +  int iCol;
          862  +  int iPhrase;
          863  +  int *aiPrev;
          864  +  int *anToken;
          865  +  char **apList;
          866  +};
          867  +
          868  +static int fts3SnippetFindPositions(Fts3Expr *pExpr, void *ctx){
          869  +  SnippetCtx *p = (SnippetCtx *)ctx;
          870  +  int iPhrase = p->iPhrase++;
          871  +  char *pCsr;
          872  +
          873  +  p->anToken[iPhrase] = pExpr->pPhrase->nToken;
          874  +  pCsr = sqlite3Fts3FindPositions(pExpr, p->pCsr->iPrevId, p->iCol);
          875  +
          876  +  if( pCsr ){
          877  +    int iVal;
          878  +    pCsr += sqlite3Fts3GetVarint32(pCsr, &iVal);
          879  +    p->apList[iPhrase] = pCsr;
          880  +    p->aiPrev[iPhrase] = iVal-2;
          881  +  }
          882  +  return SQLITE_OK;
          883  +}
          884  +
          885  +static void fts3SnippetCnt(
          886  +  int iIdx, 
          887  +  int nSnippet, 
          888  +  int *anCnt, 
          889  +  u8 *aBuffer,
          890  +  int *anToken,
          891  +  u64 *pHlmask
          892  +){
          893  +  int iSub =  (iIdx-1)&SNIPPET_BUFFER_MASK;
          894  +  int iAdd =  (iIdx+nSnippet-1)&SNIPPET_BUFFER_MASK;
          895  +  int iSub2 = (iIdx+(nSnippet/3)-1)&SNIPPET_BUFFER_MASK;
          896  +  int iAdd2 = (iIdx+(nSnippet*2/3)-1)&SNIPPET_BUFFER_MASK;
          897  +
          898  +  u64 h = *pHlmask;
          899  +
          900  +  anCnt[ aBuffer[iSub]  ]--;
          901  +  anCnt[ aBuffer[iSub2] ]--;
          902  +  anCnt[ aBuffer[iAdd]  ]++;
          903  +  anCnt[ aBuffer[iAdd2] ]++;
          904  +
          905  +  h = h >> 1;
          906  +  if( aBuffer[iAdd] ){
          907  +    int j;
          908  +    for(j=anToken[aBuffer[iAdd]-1]; j>=1; j--){
          909  +      h |= (u64)1 << (nSnippet-j);
          910  +    }
          911  +  }
          912  +  *pHlmask = h;
          913  +}
          914  +
          915  +static int fts3SnippetScore(int n, int *anCnt){
          916  +  int j;
          917  +  int iScore = 0;
          918  +  for(j=1; j<=n; j++){
          919  +    int nCnt = anCnt[j];
          920  +    iScore += nCnt + (nCnt ? 1000 : 0);
          921  +  }
          922  +  return iScore;
          923  +}
          924  +
          925  +static int fts3BestSnippet(
          926  +  int nSnippet,                   /* Desired snippet length */
          927  +  Fts3Cursor *pCsr,               /* Cursor to create snippet for */
          928  +  int iCol,                       /* Index of column to create snippet from */
          929  +  int *piPos,                     /* OUT: Starting token for best snippet */
          930  +  u64 *pHlmask                    /* OUT: Highlight mask for best snippet */
          931  +){
          932  +  int rc;                         /* Return Code */
          933  +  u8 aBuffer[SNIPPET_BUFFER_SIZE];/* Circular snippet buffer */
          934  +  int *aiPrev;                    /* Used by fts3LoadSnippetBuffer() */
          935  +  int *anToken;                   /* Number of tokens in each phrase */
          936  +  char **apList;                  /* Array of position lists */
          937  +  int *anCnt;                     /* Running totals of phrase occurences */
          938  +  int nList;
          939  +
          940  +  int i;
          941  +
          942  +  u64 hlmask = 0;                 /* Current mask of highlighted terms */
          943  +  u64 besthlmask = 0;             /* Mask of highlighted terms for iBestPos */
          944  +  int iBestPos = 0;               /* Starting position of 'best' snippet */
          945  +  int iBestScore = 0;             /* Score of best snippet higher->better */
          946  +  SnippetCtx sCtx;
          947  +
          948  +  /* Iterate through the phrases in the expression to count them. The same
          949  +  ** callback makes sure the doclists are loaded for each phrase.
          950  +  */
          951  +  rc = fts3ExprLoadDoclists(pCsr, &nList);
          952  +  if( rc!=SQLITE_OK ){
          953  +    return rc;
          954  +  }
          955  +
          956  +  /* Now that it is known how many phrases there are, allocate and zero
          957  +  ** the required arrays using malloc().
          958  +  */
          959  +  apList = sqlite3_malloc(
          960  +      sizeof(u8*)*nList +         /* apList */
          961  +      sizeof(int)*(nList) +       /* anToken */
          962  +      sizeof(int)*nList +         /* aiPrev */
          963  +      sizeof(int)*(nList+1)       /* anCnt */
          964  +  );
          965  +  if( !apList ){
          966  +    return SQLITE_NOMEM;
          967  +  }
          968  +  memset(apList, 0, sizeof(u8*)*nList+sizeof(int)*nList+sizeof(int)*nList);
          969  +  anToken = (int *)&apList[nList];
          970  +  aiPrev = &anToken[nList];
          971  +  anCnt = &aiPrev[nList];
          972  +
          973  +  /* Initialize the contents of the aiPrev and aiList arrays. */
          974  +  sCtx.pCsr = pCsr;
          975  +  sCtx.iCol = iCol;
          976  +  sCtx.apList = apList;
          977  +  sCtx.aiPrev = aiPrev;
          978  +  sCtx.anToken = anToken;
          979  +  sCtx.iPhrase = 0;
          980  +  (void)fts3ExprIterate(pCsr->pExpr, fts3SnippetFindPositions, (void *)&sCtx);
          981  +
          982  +  /* Load the first two chunks of data into the buffer. */
          983  +  memset(aBuffer, 0, SNIPPET_BUFFER_SIZE);
          984  +  fts3LoadSnippetBuffer(0, aBuffer, nList, apList, aiPrev);
          985  +  fts3LoadSnippetBuffer(SNIPPET_BUFFER_CHUNK, aBuffer, nList, apList, aiPrev);
          986  +
          987  +  /* Set the initial contents of the highlight-mask and anCnt[] array. */
          988  +  for(i=1-nSnippet; i<=0; i++){
          989  +    fts3SnippetCnt(i, nSnippet, anCnt, aBuffer, anToken, &hlmask);
          990  +  }
          991  +  iBestScore = fts3SnippetScore(nList, anCnt);
          992  +  besthlmask = hlmask;
          993  +  iBestPos = 0;
          994  +
          995  +  for(i=1; 1; i++){
          996  +    int iScore;
          997  +
          998  +    if( 0==(i&(SNIPPET_BUFFER_CHUNK-1)) ){
          999  +      int iLoad = i + SNIPPET_BUFFER_CHUNK;
         1000  +      if( fts3LoadSnippetBuffer(iLoad, aBuffer, nList, apList, aiPrev) ) break;
         1001  +    }
         1002  +
         1003  +    /* Figure out how highly a snippet starting at token offset i scores
         1004  +    ** according to fts3SnippetScore(). If it is higher than any previously
         1005  +    ** considered position, save the current position, score and hlmask as 
         1006  +    ** the best snippet candidate found so far.
         1007  +    */
         1008  +    fts3SnippetCnt(i, nSnippet, anCnt, aBuffer, anToken, &hlmask);
         1009  +    iScore = fts3SnippetScore(nList, anCnt);
         1010  +    if( iScore>iBestScore ){
         1011  +      iBestPos = i;
         1012  +      iBestScore = iScore;
         1013  +      besthlmask = hlmask;
         1014  +    }
         1015  +  }
         1016  +
         1017  +  sqlite3_free(apList);
         1018  +  *piPos = iBestPos;
         1019  +  *pHlmask = besthlmask;
         1020  +  return SQLITE_OK;
         1021  +}
         1022  +
         1023  +typedef struct StrBuffer StrBuffer;
         1024  +struct StrBuffer {
         1025  +  char *z;
         1026  +  int n;
         1027  +  int nAlloc;
         1028  +};
         1029  +
         1030  +static int fts3StringAppend(
         1031  +  StrBuffer *pStr, 
         1032  +  const char *zAppend, 
         1033  +  int nAppend
         1034  +){
         1035  +  if( nAppend<0 ){
         1036  +    nAppend = (int)strlen(zAppend);
         1037  +  }
         1038  +
         1039  +  if( pStr->n+nAppend+1>=pStr->nAlloc ){
         1040  +    int nAlloc = pStr->nAlloc+nAppend+100;
         1041  +    char *zNew = sqlite3_realloc(pStr->z, nAlloc);
         1042  +    if( !zNew ){
         1043  +      return SQLITE_NOMEM;
         1044  +    }
         1045  +    pStr->z = zNew;
         1046  +    pStr->nAlloc = nAlloc;
         1047  +  }
         1048  +
         1049  +  memcpy(&pStr->z[pStr->n], zAppend, nAppend);
         1050  +  pStr->n += nAppend;
         1051  +  pStr->z[pStr->n] = '\0';
         1052  +
         1053  +  return SQLITE_OK;
         1054  +}
         1055  +
         1056  +static int fts3SnippetText(
         1057  +  Fts3Cursor *pCsr,               /* FTS3 Cursor */
         1058  +  const char *zDoc,               /* Document to extract snippet from */
         1059  +  int nDoc,                       /* Size of zDoc in bytes */
         1060  +  int nSnippet,                   /* Number of tokens in extracted snippet */
         1061  +  int iPos,                       /* Index of first document token in snippet */
         1062  +  u64 hlmask,                     /* Bitmask of terms to highlight in snippet */
         1063  +  const char *zOpen,              /* String inserted before highlighted term */
         1064  +  const char *zClose,             /* String inserted after highlighted term */
         1065  +  const char *zEllipsis,
         1066  +  char **pzSnippet                /* OUT: Snippet text */
         1067  +){
         1068  +  Fts3Table *pTab = (Fts3Table *)pCsr->base.pVtab;
         1069  +  int rc;                         /* Return code */
         1070  +  int iCurrent = 0;
         1071  +  int iStart = 0;
         1072  +  int iEnd;
         1073  +
         1074  +  sqlite3_tokenizer_module *pMod; /* Tokenizer module methods object */
         1075  +  sqlite3_tokenizer_cursor *pC;   /* Tokenizer cursor open on zDoc/nDoc */
         1076  +  const char *ZDUMMY;             /* Dummy arguments used with tokenizer */
         1077  +  int DUMMY1, DUMMY2, DUMMY3;     /* Dummy arguments used with tokenizer */
         1078  +
         1079  +  StrBuffer res = {0, 0, 0};   /* Result string */
         1080  +
         1081  +  /* Open a token cursor on the document. Read all tokens up to and 
         1082  +  ** including token iPos (the first token of the snippet). Set variable
         1083  +  ** iStart to the byte offset in zDoc of the start of token iPos.
         1084  +  */
         1085  +  pMod = (sqlite3_tokenizer_module *)pTab->pTokenizer->pModule;
         1086  +  rc = pMod->xOpen(pTab->pTokenizer, zDoc, nDoc, &pC);
         1087  +  while( rc==SQLITE_OK && iCurrent<iPos ){
         1088  +    rc = pMod->xNext(pC, &ZDUMMY, &DUMMY1, &iStart, &DUMMY2, &iCurrent);
         1089  +  }
         1090  +  iEnd = iStart;
         1091  +
         1092  +  if( rc==SQLITE_OK && iStart>0 ){
         1093  +    rc = fts3StringAppend(&res, zEllipsis, -1);
         1094  +  }
         1095  +
         1096  +  while( rc==SQLITE_OK ){
         1097  +    int iBegin;
         1098  +    int iFin;
         1099  +    rc = pMod->xNext(pC, &ZDUMMY, &DUMMY1, &iBegin, &iFin, &iCurrent);
         1100  +
         1101  +    if( rc==SQLITE_OK ){
         1102  +      if( iCurrent>=(iPos+nSnippet) ){
         1103  +        rc = SQLITE_DONE;
         1104  +      }else{
         1105  +        iEnd = iFin;
         1106  +        if( hlmask & ((u64)1 << (iCurrent-iPos)) ){
         1107  +          if( fts3StringAppend(&res, &zDoc[iStart], iBegin-iStart)
         1108  +           || fts3StringAppend(&res, zOpen, -1)
         1109  +           || fts3StringAppend(&res, &zDoc[iBegin], iEnd-iBegin)
         1110  +           || fts3StringAppend(&res, zClose, -1)
         1111  +          ){
         1112  +            rc = SQLITE_NOMEM;
         1113  +          }
         1114  +          iStart = iEnd;
         1115  +        }
         1116  +      }
         1117  +    }
         1118  +  }
         1119  +  assert( rc!=SQLITE_OK );
         1120  +  if( rc==SQLITE_DONE ){
         1121  +    rc = fts3StringAppend(&res, &zDoc[iStart], iEnd-iStart);
         1122  +    if( rc==SQLITE_OK ){
         1123  +      rc = pMod->xNext(pC, &ZDUMMY, &DUMMY1, &DUMMY2, &DUMMY3, &iCurrent);
         1124  +      if( rc==SQLITE_OK ){
         1125  +        rc = fts3StringAppend(&res, zEllipsis, -1);
         1126  +      }else if( rc==SQLITE_DONE ){
         1127  +        rc = fts3StringAppend(&res, &zDoc[iEnd], -1);
         1128  +      }
         1129  +    }
         1130  +  }
         1131  +
         1132  +  pMod->xClose(pC);
         1133  +  if( rc!=SQLITE_OK ){
         1134  +    sqlite3_free(res.z);
         1135  +  }else{
         1136  +    *pzSnippet = res.z;
         1137  +  }
         1138  +  return rc;
         1139  +}
         1140  +
         1141  +
         1142  +/*
         1143  +** An instance of this structure is used to collect the 'global' part of
         1144  +** the matchinfo statistics. The 'global' part consists of the following:
         1145  +**
         1146  +**   1. The number of phrases in the query (nPhrase).
         1147  +**
         1148  +**   2. The number of columns in the FTS3 table (nCol).
         1149  +**
         1150  +**   3. A matrix of (nPhrase*nCol) integers containing the sum of the
         1151  +**      number of hits for each phrase in each column across all rows
         1152  +**      of the table.
         1153  +**
         1154  +** The total size of the global matchinfo array, assuming the number of
         1155  +** columns is N and the number of phrases is P is:
         1156  +**
         1157  +**   2 + P*(N+1)
         1158  +**
         1159  +** The number of hits for the 3rd phrase in the second column is found
         1160  +** using the expression:
         1161  +**
         1162  +**   aGlobal[2 + P*(1+2) + 1]
         1163  +*/
         1164  +typedef struct MatchInfo MatchInfo;
         1165  +struct MatchInfo {
         1166  +  Fts3Table *pTab;                /* FTS3 Table */
         1167  +  Fts3Cursor *pCursor;            /* FTS3 Cursor */
         1168  +  int iPhrase;                    /* Number of phrases so far */
         1169  +  int nCol;                       /* Number of columns in table */
         1170  +  u32 *aGlobal;                   /* Pre-allocated buffer */
         1171  +};
         1172  +
         1173  +/*
         1174  +** This function is used to count the entries in a column-list (delta-encoded
         1175  +** list of term offsets within a single column of a single row).
         1176  +*/
         1177  +static int fts3ColumnlistCount(char **ppCollist){
         1178  +  char *pEnd = *ppCollist;
         1179  +  char c = 0;
         1180  +  int nEntry = 0;
         1181  +
         1182  +  /* A column-list is terminated by either a 0x01 or 0x00. */
         1183  +  while( 0xFE & (*pEnd | c) ){
         1184  +    c = *pEnd++ & 0x80;
         1185  +    if( !c ) nEntry++;
         1186  +  }
         1187  +
         1188  +  *ppCollist = pEnd;
         1189  +  return nEntry;
         1190  +}
         1191  +
         1192  +static void fts3LoadColumnlistCounts(char **pp, u32 *aOut){
         1193  +  char *pCsr = *pp;
         1194  +  while( *pCsr ){
         1195  +    sqlite3_int64 iCol = 0;
         1196  +    if( *pCsr==0x01 ){
         1197  +      pCsr++;
         1198  +      pCsr += sqlite3Fts3GetVarint(pCsr, &iCol);
         1199  +    }
         1200  +    aOut[iCol] += fts3ColumnlistCount(&pCsr);
         1201  +  }
         1202  +  pCsr++;
         1203  +  *pp = pCsr;
         1204  +}
         1205  +
         1206  +/*
         1207  +** fts3ExprIterate() callback used to collect the "global" matchinfo stats
         1208  +** for a single query.
         1209  +*/
         1210  +static int fts3ExprGlobalMatchinfoCb(
         1211  +  Fts3Expr *pExpr,                /* Phrase expression node */
         1212  +  void *pCtx                      /* Pointer to MatchInfo structure */
         1213  +){
         1214  +  MatchInfo *p = (MatchInfo *)pCtx;
         1215  +  char *pCsr;
         1216  +  char *pEnd;
         1217  +  const int iStart = 2 + p->nCol*p->iPhrase;
         1218  +
         1219  +  assert( pExpr->isLoaded );
         1220  +
         1221  +  /* Fill in the global hit count matrix row for this phrase. */
         1222  +  pCsr = pExpr->aDoclist;
         1223  +  pEnd = &pExpr->aDoclist[pExpr->nDoclist];
         1224  +  while( pCsr<pEnd ){
         1225  +    while( *pCsr++ & 0x80 );
         1226  +    fts3LoadColumnlistCounts(&pCsr, &p->aGlobal[iStart]);
         1227  +  }
         1228  +
         1229  +  p->iPhrase++;
         1230  +  return SQLITE_OK;
         1231  +}
         1232  +
         1233  +static int fts3ExprLocalMatchinfoCb(
         1234  +  Fts3Expr *pExpr,                /* Phrase expression node */
         1235  +  void *pCtx                      /* Pointer to MatchInfo structure */
         1236  +){
         1237  +  MatchInfo *p = (MatchInfo *)pCtx;
         1238  +  int iPhrase = p->iPhrase++;
         1239  +
         1240  +  if( pExpr->aDoclist ){
         1241  +    char *pCsr;
         1242  +    int iOffset = 2 + p->nCol*(p->aGlobal[0]+iPhrase);
         1243  +
         1244  +    memset(&p->aGlobal[iOffset], 0, p->nCol*sizeof(u32));
         1245  +    pCsr = sqlite3Fts3FindPositions(pExpr, p->pCursor->iPrevId, -1);
         1246  +    if( pCsr ) fts3LoadColumnlistCounts(&pCsr, &p->aGlobal[iOffset]);
         1247  +  }
         1248  +
         1249  +  return SQLITE_OK;
         1250  +}
         1251  +
         1252  +/*
         1253  +** Populate pCsr->aMatchinfo[] with data for the current row. The 'matchinfo'
         1254  +** data is an array of 32-bit unsigned integers (C type u32).
         1255  +*/
         1256  +static int fts3GetMatchinfo(Fts3Cursor *pCsr){
         1257  +  MatchInfo g;
         1258  +  Fts3Table *pTab = (Fts3Table *)pCsr->base.pVtab;
         1259  +  if( pCsr->aMatchinfo==0 ){
         1260  +    int rc;
         1261  +    int nPhrase;
         1262  +    int nMatchinfo;
         1263  +
         1264  +    g.pTab = pTab;
         1265  +    g.nCol = pTab->nColumn;
         1266  +    g.iPhrase = 0;
         1267  +    rc = fts3ExprLoadDoclists(pCsr, &nPhrase);
         1268  +    if( rc!=SQLITE_OK ){
         1269  +      return rc;
         1270  +    }
         1271  +
         1272  +    nMatchinfo = 2 + 2*g.nCol*nPhrase;
         1273  +
         1274  +    g.iPhrase = 0;
         1275  +    g.aGlobal = (u32 *)sqlite3_malloc(sizeof(u32)*nMatchinfo);
         1276  +    if( !g.aGlobal ){ 
         1277  +      return SQLITE_NOMEM;
         1278  +    }
         1279  +    memset(g.aGlobal, 0, sizeof(u32)*nMatchinfo);
         1280  +
         1281  +    g.aGlobal[0] = nPhrase;
         1282  +    g.aGlobal[1] = g.nCol;
         1283  +    (void)fts3ExprIterate(pCsr->pExpr, fts3ExprGlobalMatchinfoCb, (void *)&g);
         1284  +
         1285  +    pCsr->aMatchinfo = g.aGlobal;
         1286  +  }
         1287  +
         1288  +  g.pTab = pTab;
         1289  +  g.pCursor = pCsr;
         1290  +  g.nCol = pTab->nColumn;
         1291  +  g.iPhrase = 0;
         1292  +  g.aGlobal = pCsr->aMatchinfo;
         1293  +
         1294  +  if( pCsr->isMatchinfoOk ){
         1295  +    (void)fts3ExprIterate(pCsr->pExpr, fts3ExprLocalMatchinfoCb, (void *)&g);
         1296  +    pCsr->isMatchinfoOk = 0;
         1297  +  }
         1298  +
         1299  +  return SQLITE_OK;
         1300  +}
         1301  +
         1302  +void sqlite3Fts3Snippet2(
         1303  +  sqlite3_context *pCtx,          /* SQLite function call context */
         1304  +  Fts3Cursor *pCsr,               /* Cursor object */
         1305  +  const char *zStart,             /* Snippet start text - "<b>" */
         1306  +  const char *zEnd,               /* Snippet end text - "</b>" */
         1307  +  const char *zEllipsis,          /* Snippet ellipsis text - "<b>...</b>" */
         1308  +  int iCol,                       /* Extract snippet from this column */
         1309  +  int nToken                      /* Approximate number of tokens in snippet */
         1310  +){
         1311  +  int rc;
         1312  +  int iPos = 0;
         1313  +  u64 hlmask = 0;
         1314  +  char *z = 0;
         1315  +  int nDoc;
         1316  +  const char *zDoc;
         1317  +
         1318  +  rc = fts3BestSnippet(nToken, pCsr, iCol, &iPos, &hlmask);
         1319  +
         1320  +  nDoc = sqlite3_column_bytes(pCsr->pStmt, iCol+1);
         1321  +  zDoc = (const char *)sqlite3_column_text(pCsr->pStmt, iCol+1);
         1322  +
         1323  +  if( rc==SQLITE_OK ){
         1324  +    rc = fts3SnippetText(
         1325  +        pCsr, zDoc, nDoc, nToken, iPos, hlmask, zStart, zEnd, zEllipsis, &z);
         1326  +  }
         1327  +  if( rc!=SQLITE_OK ){
         1328  +    sqlite3_result_error_code(pCtx, rc);
         1329  +  }else{
         1330  +    sqlite3_result_text(pCtx, z, -1, sqlite3_free);
         1331  +  }
         1332  +}
         1333  +
         1334  +void sqlite3Fts3Matchinfo(sqlite3_context *pContext, Fts3Cursor *pCsr){
         1335  +  int rc = fts3GetMatchinfo(pCsr);
         1336  +  if( rc!=SQLITE_OK ){
         1337  +    sqlite3_result_error_code(pContext, rc);
         1338  +  }else{
         1339  +    int n = sizeof(u32)*(2+pCsr->aMatchinfo[0]*pCsr->aMatchinfo[1]*2);
         1340  +    sqlite3_result_blob(pContext, pCsr->aMatchinfo, n, SQLITE_TRANSIENT);
         1341  +  }
         1342  +}
   733   1343   
   734   1344   #endif

Changes to ext/fts3/fts3_tokenizer.h.

   140    140     /* Tokenizer implementations will typically add additional fields */
   141    141   };
   142    142   
   143    143   struct sqlite3_tokenizer_cursor {
   144    144     sqlite3_tokenizer *pTokenizer;       /* Tokenizer for this cursor. */
   145    145     /* Tokenizer implementations will typically add additional fields */
   146    146   };
          147  +
          148  +int fts3_global_term_cnt(int iTerm, int iCol);
          149  +int fts3_term_cnt(int iTerm, int iCol);
          150  +
   147    151   
   148    152   #endif /* _FTS3_TOKENIZER_H_ */

Changes to ext/fts3/fts3_write.c.

  2224   2224     const char *zVal = (const char *)sqlite3_value_text(pVal);
  2225   2225     int nVal = sqlite3_value_bytes(pVal);
  2226   2226   
  2227   2227     if( !zVal ){
  2228   2228       return SQLITE_NOMEM;
  2229   2229     }else if( nVal==8 && 0==sqlite3_strnicmp(zVal, "optimize", 8) ){
  2230   2230       rc = fts3SegmentMerge(p, -1);
  2231         -    if( rc==SQLITE_DONE || rc==SQLITE_OK ){
         2231  +    if( rc==SQLITE_DONE ){
  2232   2232         rc = SQLITE_OK;
         2233  +    }else{
  2233   2234         sqlite3Fts3PendingTermsClear(p);
  2234   2235       }
  2235   2236   #ifdef SQLITE_TEST
  2236   2237     }else if( nVal>9 && 0==sqlite3_strnicmp(zVal, "nodesize=", 9) ){
  2237   2238       p->nNodeSize = atoi(&zVal[9]);
  2238   2239       rc = SQLITE_OK;
  2239   2240     }else if( nVal>11 && 0==sqlite3_strnicmp(zVal, "maxpending=", 9) ){

Changes to ext/icu/icu.c.

   450    450     struct IcuScalar {
   451    451       const char *zName;                        /* Function name */
   452    452       int nArg;                                 /* Number of arguments */
   453    453       int enc;                                  /* Optimal text encoding */
   454    454       void *pContext;                           /* sqlite3_user_data() context */
   455    455       void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
   456    456     } scalars[] = {
   457         -    {"regexp",-1, SQLITE_ANY,          0, icuRegexpFunc},
          457  +    {"regexp", 2, SQLITE_ANY,          0, icuRegexpFunc},
   458    458   
   459    459       {"lower",  1, SQLITE_UTF16,        0, icuCaseFunc16},
   460    460       {"lower",  2, SQLITE_UTF16,        0, icuCaseFunc16},
   461    461       {"upper",  1, SQLITE_UTF16, (void*)1, icuCaseFunc16},
   462    462       {"upper",  2, SQLITE_UTF16, (void*)1, icuCaseFunc16},
   463    463   
   464    464       {"lower",  1, SQLITE_UTF8,         0, icuCaseFunc16},

Changes to src/build.c.

  3413   3413                                    &db->aDb[1].pBt);
  3414   3414       if( rc!=SQLITE_OK ){
  3415   3415         sqlite3ErrorMsg(pParse, "unable to open a temporary database "
  3416   3416           "file for storing temporary tables");
  3417   3417         pParse->rc = rc;
  3418   3418         return 1;
  3419   3419       }
  3420         -    assert( (db->flags & SQLITE_InTrans)==0 || db->autoCommit );
  3421   3420       assert( db->aDb[1].pSchema );
  3422   3421       sqlite3PagerJournalMode(sqlite3BtreePager(db->aDb[1].pBt),
  3423   3422                               db->dfltJournalMode);
  3424   3423     }
  3425   3424     return 0;
  3426   3425   }
  3427   3426   

Changes to src/complete.c.

    36     36   /*
    37     37   ** Token types used by the sqlite3_complete() routine.  See the header
    38     38   ** comments on that procedure for additional information.
    39     39   */
    40     40   #define tkSEMI    0
    41     41   #define tkWS      1
    42     42   #define tkOTHER   2
           43  +#ifndef SQLITE_OMIT_TRIGGER
    43     44   #define tkEXPLAIN 3
    44     45   #define tkCREATE  4
    45     46   #define tkTEMP    5
    46     47   #define tkTRIGGER 6
    47     48   #define tkEND     7
           49  +#endif
    48     50   
    49     51   /*
    50     52   ** Return TRUE if the given SQL string ends in a semicolon.
    51     53   **
    52     54   ** Special handling is require for CREATE TRIGGER statements.
    53     55   ** Whenever the CREATE TRIGGER keywords are seen, the statement
    54     56   ** must end with ";END;".
    55     57   **
    56         -** This implementation uses a state machine with 7 states:
           58  +** This implementation uses a state machine with 8 states:
    57     59   **
    58         -**   (0) START     At the beginning or end of an SQL statement.  This routine
           60  +**   (0) INVALID   We have not yet seen a non-whitespace character.
           61  +**
           62  +**   (1) START     At the beginning or end of an SQL statement.  This routine
    59     63   **                 returns 1 if it ends in the START state and 0 if it ends
    60     64   **                 in any other state.
    61     65   **
    62         -**   (1) NORMAL    We are in the middle of statement which ends with a single
           66  +**   (2) NORMAL    We are in the middle of statement which ends with a single
    63     67   **                 semicolon.
    64     68   **
    65         -**   (2) EXPLAIN   The keyword EXPLAIN has been seen at the beginning of 
           69  +**   (3) EXPLAIN   The keyword EXPLAIN has been seen at the beginning of 
    66     70   **                 a statement.
    67     71   **
    68         -**   (3) CREATE    The keyword CREATE has been seen at the beginning of a
           72  +**   (4) CREATE    The keyword CREATE has been seen at the beginning of a
    69     73   **                 statement, possibly preceeded by EXPLAIN and/or followed by
    70     74   **                 TEMP or TEMPORARY
    71     75   **
    72         -**   (4) TRIGGER   We are in the middle of a trigger definition that must be
           76  +**   (5) TRIGGER   We are in the middle of a trigger definition that must be
    73     77   **                 ended by a semicolon, the keyword END, and another semicolon.
    74     78   **
    75         -**   (5) SEMI      We've seen the first semicolon in the ";END;" that occurs at
           79  +**   (6) SEMI      We've seen the first semicolon in the ";END;" that occurs at
    76     80   **                 the end of a trigger definition.
    77     81   **
    78         -**   (6) END       We've seen the ";END" of the ";END;" that occurs at the end
           82  +**   (7) END       We've seen the ";END" of the ";END;" that occurs at the end
    79     83   **                 of a trigger difinition.
    80     84   **
    81     85   ** Transitions between states above are determined by tokens extracted
    82     86   ** from the input.  The following tokens are significant:
    83     87   **
    84     88   **   (0) tkSEMI      A semicolon.
    85         -**   (1) tkWS        Whitespace
           89  +**   (1) tkWS        Whitespace.
    86     90   **   (2) tkOTHER     Any other SQL token.
    87     91   **   (3) tkEXPLAIN   The "explain" keyword.
    88     92   **   (4) tkCREATE    The "create" keyword.
    89     93   **   (5) tkTEMP      The "temp" or "temporary" keyword.
    90     94   **   (6) tkTRIGGER   The "trigger" keyword.
    91     95   **   (7) tkEND       The "end" keyword.
    92     96   **
    93     97   ** Whitespace never causes a state transition and is always ignored.
           98  +** This means that a SQL string of all whitespace is invalid.
    94     99   **
    95    100   ** If we compile with SQLITE_OMIT_TRIGGER, all of the computation needed
    96    101   ** to recognize the end of a trigger can be omitted.  All we have to do
    97    102   ** is look for a semicolon that is not part of an string or comment.
    98    103   */
    99    104   int sqlite3_complete(const char *zSql){
   100    105     u8 state = 0;   /* Current state, using numbers defined in header comment */
   101    106     u8 token;       /* Value of the next token */
   102    107   
   103    108   #ifndef SQLITE_OMIT_TRIGGER
   104    109     /* A complex statement machine used to detect the end of a CREATE TRIGGER
   105    110     ** statement.  This is the normal case.
   106    111     */
   107         -  static const u8 trans[7][8] = {
          112  +  static const u8 trans[8][8] = {
   108    113                        /* Token:                                                */
   109         -     /* State:       **  SEMI  WS  OTHER EXPLAIN  CREATE  TEMP  TRIGGER  END  */
   110         -     /* 0   START: */ {    0,  0,     1,      2,      3,    1,       1,   1,  },
   111         -     /* 1  NORMAL: */ {    0,  1,     1,      1,      1,    1,       1,   1,  },
   112         -     /* 2 EXPLAIN: */ {    0,  2,     2,      1,      3,    1,       1,   1,  },
   113         -     /* 3  CREATE: */ {    0,  3,     1,      1,      1,    3,       4,   1,  },
   114         -     /* 4 TRIGGER: */ {    5,  4,     4,      4,      4,    4,       4,   4,  },
   115         -     /* 5    SEMI: */ {    5,  5,     4,      4,      4,    4,       4,   6,  },
   116         -     /* 6     END: */ {    0,  6,     4,      4,      4,    4,       4,   4,  },
          114  +     /* State:       **  SEMI  WS  OTHER  EXPLAIN  CREATE  TEMP  TRIGGER  END */
          115  +     /* 0 INVALID: */ {    1,  0,     2,       3,      4,    2,       2,   2, },
          116  +     /* 1   START: */ {    1,  1,     2,       3,      4,    2,       2,   2, },
          117  +     /* 2  NORMAL: */ {    1,  2,     2,       2,      2,    2,       2,   2, },
          118  +     /* 3 EXPLAIN: */ {    1,  3,     3,       2,      4,    2,       2,   2, },
          119  +     /* 4  CREATE: */ {    1,  4,     2,       2,      2,    4,       5,   2, },
          120  +     /* 5 TRIGGER: */ {    6,  5,     5,       5,      5,    5,       5,   5, },
          121  +     /* 6    SEMI: */ {    6,  6,     5,       5,      5,    5,       5,   7, },
          122  +     /* 7     END: */ {    1,  7,     5,       5,      5,    5,       5,   5, },
   117    123     };
   118    124   #else
   119         -  /* If triggers are not suppored by this compile then the statement machine
          125  +  /* If triggers are not supported by this compile then the statement machine
   120    126     ** used to detect the end of a statement is much simplier
   121    127     */
   122         -  static const u8 trans[2][3] = {
          128  +  static const u8 trans[3][3] = {
   123    129                        /* Token:           */
   124    130        /* State:       **  SEMI  WS  OTHER */
   125         -     /* 0   START: */ {    0,  0,     1, },
   126         -     /* 1  NORMAL: */ {    0,  1,     1, },
          131  +     /* 0 INVALID: */ {    1,  0,     2, },
          132  +     /* 1   START: */ {    1,  1,     2, },
          133  +     /* 2  NORMAL: */ {    1,  2,     2, },
   127    134     };
   128    135   #endif /* SQLITE_OMIT_TRIGGER */
   129    136   
   130    137     while( *zSql ){
   131    138       switch( *zSql ){
   132    139         case ';': {  /* A semicolon */
   133    140           token = tkSEMI;
................................................................................
   155    162         }
   156    163         case '-': {   /* SQL-style comments from "--" to end of line */
   157    164           if( zSql[1]!='-' ){
   158    165             token = tkOTHER;
   159    166             break;
   160    167           }
   161    168           while( *zSql && *zSql!='\n' ){ zSql++; }
   162         -        if( *zSql==0 ) return state==0;
          169  +        if( *zSql==0 ) return state==1;
   163    170           token = tkWS;
   164    171           break;
   165    172         }
   166    173         case '[': {   /* Microsoft-style identifiers in [...] */
   167    174           zSql++;
   168    175           while( *zSql && *zSql!=']' ){ zSql++; }
   169    176           if( *zSql==0 ) return 0;
................................................................................
   239    246           }
   240    247           break;
   241    248         }
   242    249       }
   243    250       state = trans[state][token];
   244    251       zSql++;
   245    252     }
   246         -  return state==0;
          253  +  return state==1;
   247    254   }
   248    255   
   249    256   #ifndef SQLITE_OMIT_UTF16
   250    257   /*
   251    258   ** This routine is the same as the sqlite3_complete() routine described
   252    259   ** above, except that the parameter is required to be UTF-16 encoded, not
   253    260   ** UTF-8.

Changes to src/delete.c.

   360    360       int regRowid;                   /* Actual register containing rowids */
   361    361   
   362    362       /* Collect rowids of every row to be deleted.
   363    363       */
   364    364       sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);
   365    365       pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere,0,WHERE_DUPLICATES_OK);
   366    366       if( pWInfo==0 ) goto delete_from_cleanup;
   367         -    regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid, 0);
          367  +    regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid);
   368    368       sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid);
   369    369       if( db->flags & SQLITE_CountRows ){
   370    370         sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   371    371       }
   372    372       sqlite3WhereEnd(pWInfo);
   373    373   
   374    374       /* Delete every item whose key was written to the list during the
................................................................................
   626    626         sqlite3VdbeAddOp3(v, OP_Column, iCur, idx, regBase+j);
   627    627         sqlite3ColumnDefault(v, pTab, idx, -1);
   628    628       }
   629    629     }
   630    630     if( doMakeRec ){
   631    631       sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol+1, regOut);
   632    632       sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
   633         -    sqlite3ExprCacheAffinityChange(pParse, regBase, nCol+1);
   634    633     }
   635    634     sqlite3ReleaseTempRange(pParse, regBase, nCol+1);
   636    635     return regBase;
   637    636   }

Changes to src/expr.c.

   223    223       if( !pColl ){
   224    224         pColl = sqlite3ExprCollSeq(pParse, pRight);
   225    225       }
   226    226     }
   227    227     return pColl;
   228    228   }
   229    229   
   230         -/*
   231         -** Generate the operands for a comparison operation.  Before
   232         -** generating the code for each operand, set the EP_AnyAff
   233         -** flag on the expression so that it will be able to used a
   234         -** cached column value that has previously undergone an
   235         -** affinity change.
   236         -*/
   237         -static void codeCompareOperands(
   238         -  Parse *pParse,    /* Parsing and code generating context */
   239         -  Expr *pLeft,      /* The left operand */
   240         -  int *pRegLeft,    /* Register where left operand is stored */
   241         -  int *pFreeLeft,   /* Free this register when done */
   242         -  Expr *pRight,     /* The right operand */
   243         -  int *pRegRight,   /* Register where right operand is stored */
   244         -  int *pFreeRight   /* Write temp register for right operand there */
   245         -){
   246         -  while( pLeft->op==TK_UPLUS ) pLeft = pLeft->pLeft;
   247         -  pLeft->flags |= EP_AnyAff;
   248         -  *pRegLeft = sqlite3ExprCodeTemp(pParse, pLeft, pFreeLeft);
   249         -  while( pRight->op==TK_UPLUS ) pRight = pRight->pLeft;
   250         -  pRight->flags |= EP_AnyAff;
   251         -  *pRegRight = sqlite3ExprCodeTemp(pParse, pRight, pFreeRight);
   252         -}
   253         -
   254    230   /*
   255    231   ** Generate code for a comparison operator.
   256    232   */
   257    233   static int codeCompare(
   258    234     Parse *pParse,    /* The parsing (and code generating) context */
   259    235     Expr *pLeft,      /* The left operand */
   260    236     Expr *pRight,     /* The right operand */
................................................................................
  1978   1954     int minLru;
  1979   1955     int idxLru;
  1980   1956     struct yColCache *p;
  1981   1957   
  1982   1958     assert( iReg>0 );  /* Register numbers are always positive */
  1983   1959     assert( iCol>=-1 && iCol<32768 );  /* Finite column numbers */
  1984   1960   
  1985         -  /* First replace any existing entry */
         1961  +  /* The SQLITE_ColumnCache flag disables the column cache.  This is used
         1962  +  ** for testing only - to verify that SQLite always gets the same answer
         1963  +  ** with and without the column cache.
         1964  +  */
         1965  +  if( pParse->db->flags & SQLITE_ColumnCache ) return;
         1966  +
         1967  +  /* First replace any existing entry.
         1968  +  **
         1969  +  ** Actually, the way the column cache is currently used, we are guaranteed
         1970  +  ** that the object will never already be in cache.  Verify this guarantee.
         1971  +  */
         1972  +#ifndef NDEBUG
  1986   1973     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
         1974  +#if 0 /* This code wold remove the entry from the cache if it existed */
  1987   1975       if( p->iReg && p->iTable==iTab && p->iColumn==iCol ){
  1988   1976         cacheEntryClear(pParse, p);
  1989   1977         p->iLevel = pParse->iCacheLevel;
  1990   1978         p->iReg = iReg;
  1991         -      p->affChange = 0;
  1992   1979         p->lru = pParse->iCacheCnt++;
  1993   1980         return;
  1994   1981       }
         1982  +#endif
         1983  +    assert( p->iReg==0 || p->iTable!=iTab || p->iColumn!=iCol );
  1995   1984     }
         1985  +#endif
  1996   1986   
  1997   1987     /* Find an empty slot and replace it */
  1998   1988     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  1999   1989       if( p->iReg==0 ){
  2000   1990         p->iLevel = pParse->iCacheLevel;
  2001   1991         p->iTable = iTab;
  2002   1992         p->iColumn = iCol;
  2003   1993         p->iReg = iReg;
  2004         -      p->affChange = 0;
  2005   1994         p->tempReg = 0;
  2006   1995         p->lru = pParse->iCacheCnt++;
  2007   1996         return;
  2008   1997       }
  2009   1998     }
  2010   1999   
  2011   2000     /* Replace the last recently used */
................................................................................
  2019   2008     }
  2020   2009     if( ALWAYS(idxLru>=0) ){
  2021   2010       p = &pParse->aColCache[idxLru];
  2022   2011       p->iLevel = pParse->iCacheLevel;
  2023   2012       p->iTable = iTab;
  2024   2013       p->iColumn = iCol;
  2025   2014       p->iReg = iReg;
  2026         -    p->affChange = 0;
  2027   2015       p->tempReg = 0;
  2028   2016       p->lru = pParse->iCacheCnt++;
  2029   2017       return;
  2030   2018     }
  2031   2019   }
  2032   2020   
  2033   2021   /*
  2034         -** Indicate that a register is being overwritten.  Purge the register
  2035         -** from the column cache.
         2022  +** Indicate that registers between iReg..iReg+nReg-1 are being overwritten.
         2023  +** Purge the range of registers from the column cache.
  2036   2024   */
  2037         -void sqlite3ExprCacheRemove(Parse *pParse, int iReg){
         2025  +void sqlite3ExprCacheRemove(Parse *pParse, int iReg, int nReg){
  2038   2026     int i;
         2027  +  int iLast = iReg + nReg - 1;
  2039   2028     struct yColCache *p;
  2040   2029     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  2041         -    if( p->iReg==iReg ){
         2030  +    int r = p->iReg;
         2031  +    if( r>=iReg && r<=iLast ){
  2042   2032         cacheEntryClear(pParse, p);
  2043   2033         p->iReg = 0;
  2044   2034       }
  2045   2035     }
  2046   2036   }
  2047   2037   
  2048   2038   /*
................................................................................
  2093   2083   ** Generate code that will extract the iColumn-th column from
  2094   2084   ** table pTab and store the column value in a register.  An effort
  2095   2085   ** is made to store the column value in register iReg, but this is
  2096   2086   ** not guaranteed.  The location of the column value is returned.
  2097   2087   **
  2098   2088   ** There must be an open cursor to pTab in iTable when this routine
  2099   2089   ** is called.  If iColumn<0 then code is generated that extracts the rowid.
  2100         -**
  2101         -** This routine might attempt to reuse the value of the column that
  2102         -** has already been loaded into a register.  The value will always
  2103         -** be used if it has not undergone any affinity changes.  But if
  2104         -** an affinity change has occurred, then the cached value will only be
  2105         -** used if allowAffChng is true.
  2106   2090   */
  2107   2091   int sqlite3ExprCodeGetColumn(
  2108   2092     Parse *pParse,   /* Parsing and code generating context */
  2109   2093     Table *pTab,     /* Description of the table we are reading from */
  2110   2094     int iColumn,     /* Index of the table column */
  2111   2095     int iTable,      /* The cursor pointing to the table */
  2112         -  int iReg,        /* Store results here */
  2113         -  int allowAffChng /* True if prior affinity changes are OK */
         2096  +  int iReg         /* Store results here */
  2114   2097   ){
  2115   2098     Vdbe *v = pParse->pVdbe;
  2116   2099     int i;
  2117   2100     struct yColCache *p;
  2118   2101   
  2119   2102     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  2120         -    if( p->iReg>0 && p->iTable==iTable && p->iColumn==iColumn
  2121         -           && (!p->affChange || allowAffChng) ){
         2103  +    if( p->iReg>0 && p->iTable==iTable && p->iColumn==iColumn ){
  2122   2104         p->lru = pParse->iCacheCnt++;
  2123   2105         sqlite3ExprCachePinRegister(pParse, p->iReg);
  2124   2106         return p->iReg;
  2125   2107       }
  2126   2108     }  
  2127   2109     assert( v!=0 );
  2128   2110     if( iColumn<0 ){
................................................................................
  2152   2134   }
  2153   2135   
  2154   2136   /*
  2155   2137   ** Record the fact that an affinity change has occurred on iCount
  2156   2138   ** registers starting with iStart.
  2157   2139   */
  2158   2140   void sqlite3ExprCacheAffinityChange(Parse *pParse, int iStart, int iCount){
  2159         -  int iEnd = iStart + iCount - 1;
  2160         -  int i;
  2161         -  struct yColCache *p;
  2162         -  for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  2163         -    int r = p->iReg;
  2164         -    if( r>=iStart && r<=iEnd ){
  2165         -      p->affChange = 1;
  2166         -    }
  2167         -  }
         2141  +  sqlite3ExprCacheRemove(pParse, iStart, iCount);
  2168   2142   }
  2169   2143   
  2170   2144   /*
  2171   2145   ** Generate code to move content from registers iFrom...iFrom+nReg-1
  2172   2146   ** over to iTo..iTo+nReg-1. Keep the column cache up-to-date.
  2173   2147   */
  2174   2148   void sqlite3ExprCodeMove(Parse *pParse, int iFrom, int iTo, int nReg){
................................................................................
  2192   2166     int i;
  2193   2167     if( NEVER(iFrom==iTo) ) return;
  2194   2168     for(i=0; i<nReg; i++){
  2195   2169       sqlite3VdbeAddOp2(pParse->pVdbe, OP_Copy, iFrom+i, iTo+i);
  2196   2170     }
  2197   2171   }
  2198   2172   
         2173  +#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST)
  2199   2174   /*
  2200   2175   ** Return true if any register in the range iFrom..iTo (inclusive)
  2201   2176   ** is used as part of the column cache.
         2177  +**
         2178  +** This routine is used within assert() and testcase() macros only
         2179  +** and does not appear in a normal build.
  2202   2180   */
  2203   2181   static int usedAsColumnCache(Parse *pParse, int iFrom, int iTo){
  2204   2182     int i;
  2205   2183     struct yColCache *p;
  2206   2184     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  2207   2185       int r = p->iReg;
  2208         -    if( r>=iFrom && r<=iTo ) return 1;
         2186  +    if( r>=iFrom && r<=iTo ) return 1;    /*NO_TEST*/
  2209   2187     }
  2210   2188     return 0;
  2211   2189   }
         2190  +#endif /* SQLITE_DEBUG || SQLITE_COVERAGE_TEST */
  2212   2191   
  2213   2192   /*
  2214   2193   ** If the last instruction coded is an ephemeral copy of any of
  2215   2194   ** the registers in the nReg registers beginning with iReg, then
  2216   2195   ** convert the last instruction from OP_SCopy to OP_Copy.
  2217   2196   */
  2218   2197   void sqlite3ExprHardCopy(Parse *pParse, int iReg, int nReg){
................................................................................
  2325   2304       }
  2326   2305       case TK_COLUMN: {
  2327   2306         if( pExpr->iTable<0 ){
  2328   2307           /* This only happens when coding check constraints */
  2329   2308           assert( pParse->ckBase>0 );
  2330   2309           inReg = pExpr->iColumn + pParse->ckBase;
  2331   2310         }else{
  2332         -        testcase( (pExpr->flags & EP_AnyAff)!=0 );
  2333   2311           inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab,
  2334         -                                 pExpr->iColumn, pExpr->iTable, target,
  2335         -                                 pExpr->flags & EP_AnyAff);
         2312  +                                 pExpr->iColumn, pExpr->iTable, target);
  2336   2313         }
  2337   2314         break;
  2338   2315       }
  2339   2316       case TK_INTEGER: {
  2340   2317         codeInteger(v, pExpr, 0, target);
  2341   2318         break;
  2342   2319       }
................................................................................
  2445   2422         assert( TK_NE==OP_Ne );
  2446   2423         testcase( op==TK_LT );
  2447   2424         testcase( op==TK_LE );
  2448   2425         testcase( op==TK_GT );
  2449   2426         testcase( op==TK_GE );
  2450   2427         testcase( op==TK_EQ );
  2451   2428         testcase( op==TK_NE );
  2452         -      codeCompareOperands(pParse, pExpr->pLeft, &r1, &regFree1,
  2453         -                                  pExpr->pRight, &r2, &regFree2);
         2429  +      r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree1);
         2430  +      r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, &regFree2);
  2454   2431         codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op,
  2455   2432                     r1, r2, inReg, SQLITE_STOREP2);
  2456   2433         testcase( regFree1==0 );
  2457   2434         testcase( regFree2==0 );
  2458   2435         break;
  2459   2436       }
  2460   2437       case TK_IS:
  2461   2438       case TK_ISNOT: {
  2462   2439         testcase( op==TK_IS );
  2463   2440         testcase( op==TK_ISNOT );
  2464         -      codeCompareOperands(pParse, pExpr->pLeft, &r1, &regFree1,
  2465         -                                  pExpr->pRight, &r2, &regFree2);
         2441  +      r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree1);
         2442  +      r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, &regFree2);
  2466   2443         op = (op==TK_IS) ? TK_EQ : TK_NE;
  2467   2444         codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op,
  2468   2445                     r1, r2, inReg, SQLITE_STOREP2 | SQLITE_NULLEQ);
  2469   2446         testcase( regFree1==0 );
  2470   2447         testcase( regFree2==0 );
  2471   2448         break;
  2472   2449       }
................................................................................
  2602   2579         */
  2603   2580         if( pDef->flags & SQLITE_FUNC_COALESCE ){
  2604   2581           int endCoalesce = sqlite3VdbeMakeLabel(v);
  2605   2582           assert( nFarg>=2 );
  2606   2583           sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
  2607   2584           for(i=1; i<nFarg; i++){
  2608   2585             sqlite3VdbeAddOp2(v, OP_NotNull, target, endCoalesce);
  2609         -          sqlite3ExprCacheRemove(pParse, target);
         2586  +          sqlite3ExprCacheRemove(pParse, target, 1);
  2610   2587             sqlite3ExprCachePush(pParse);
  2611   2588             sqlite3ExprCode(pParse, pFarg->a[i].pExpr, target);
  2612   2589             sqlite3ExprCachePop(pParse, 1);
  2613   2590           }
  2614   2591           sqlite3VdbeResolveLabel(v, endCoalesce);
  2615   2592           break;
  2616   2593         }
................................................................................
  2657   2634         }
  2658   2635         sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target,
  2659   2636                           (char*)pDef, P4_FUNCDEF);
  2660   2637         sqlite3VdbeChangeP5(v, (u8)nFarg);
  2661   2638         if( nFarg ){
  2662   2639           sqlite3ReleaseTempRange(pParse, r1, nFarg);
  2663   2640         }
  2664         -      sqlite3ExprCacheAffinityChange(pParse, r1, nFarg);
  2665   2641         break;
  2666   2642       }
  2667   2643   #ifndef SQLITE_OMIT_SUBQUERY
  2668   2644       case TK_EXISTS:
  2669   2645       case TK_SELECT: {
  2670   2646         testcase( op==TK_EXISTS );
  2671   2647         testcase( op==TK_SELECT );
................................................................................
  2698   2674       ** Z is stored in pExpr->pList->a[1].pExpr.
  2699   2675       */
  2700   2676       case TK_BETWEEN: {
  2701   2677         Expr *pLeft = pExpr->pLeft;
  2702   2678         struct ExprList_item *pLItem = pExpr->x.pList->a;
  2703   2679         Expr *pRight = pLItem->pExpr;
  2704   2680   
  2705         -      codeCompareOperands(pParse, pLeft, &r1, &regFree1,
  2706         -                                  pRight, &r2, &regFree2);
         2681  +      r1 = sqlite3ExprCodeTemp(pParse, pLeft, &regFree1);
         2682  +      r2 = sqlite3ExprCodeTemp(pParse, pRight, &regFree2);
  2707   2683         testcase( regFree1==0 );
  2708   2684         testcase( regFree2==0 );
  2709   2685         r3 = sqlite3GetTempReg(pParse);
  2710   2686         r4 = sqlite3GetTempReg(pParse);
  2711   2687         codeCompare(pParse, pLeft, pRight, OP_Ge,
  2712   2688                     r1, r2, r3, SQLITE_STOREP2);
  2713   2689         pLItem++;
................................................................................
  3234   3210         testcase( op==TK_LT );
  3235   3211         testcase( op==TK_LE );
  3236   3212         testcase( op==TK_GT );
  3237   3213         testcase( op==TK_GE );
  3238   3214         testcase( op==TK_EQ );
  3239   3215         testcase( op==TK_NE );
  3240   3216         testcase( jumpIfNull==0 );
  3241         -      codeCompareOperands(pParse, pExpr->pLeft, &r1, &regFree1,
  3242         -                                  pExpr->pRight, &r2, &regFree2);
         3217  +      r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree1);
         3218  +      r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, &regFree2);
  3243   3219         codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op,
  3244   3220                     r1, r2, dest, jumpIfNull);
  3245   3221         testcase( regFree1==0 );
  3246   3222         testcase( regFree2==0 );
  3247   3223         break;
  3248   3224       }
  3249   3225       case TK_IS:
  3250   3226       case TK_ISNOT: {
  3251   3227         testcase( op==TK_IS );
  3252   3228         testcase( op==TK_ISNOT );
  3253         -      codeCompareOperands(pParse, pExpr->pLeft, &r1, &regFree1,
  3254         -                                  pExpr->pRight, &r2, &regFree2);
         3229  +      r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree1);
         3230  +      r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, &regFree2);
  3255   3231         op = (op==TK_IS) ? TK_EQ : TK_NE;
  3256   3232         codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op,
  3257   3233                     r1, r2, dest, SQLITE_NULLEQ);
  3258   3234         testcase( regFree1==0 );
  3259   3235         testcase( regFree2==0 );
  3260   3236         break;
  3261   3237       }
................................................................................
  3377   3353         testcase( op==TK_LT );
  3378   3354         testcase( op==TK_LE );
  3379   3355         testcase( op==TK_GT );
  3380   3356         testcase( op==TK_GE );
  3381   3357         testcase( op==TK_EQ );
  3382   3358         testcase( op==TK_NE );
  3383   3359         testcase( jumpIfNull==0 );
  3384         -      codeCompareOperands(pParse, pExpr->pLeft, &r1, &regFree1,
  3385         -                                  pExpr->pRight, &r2, &regFree2);
         3360  +      r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree1);
         3361  +      r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, &regFree2);
  3386   3362         codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op,
  3387   3363                     r1, r2, dest, jumpIfNull);
  3388   3364         testcase( regFree1==0 );
  3389   3365         testcase( regFree2==0 );
  3390   3366         break;
  3391   3367       }
  3392   3368       case TK_IS:
  3393   3369       case TK_ISNOT: {
  3394   3370         testcase( pExpr->op==TK_IS );
  3395   3371         testcase( pExpr->op==TK_ISNOT );
  3396         -      codeCompareOperands(pParse, pExpr->pLeft, &r1, &regFree1,
  3397         -                                  pExpr->pRight, &r2, &regFree2);
         3372  +      r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree1);
         3373  +      r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, &regFree2);
  3398   3374         op = (pExpr->op==TK_IS) ? TK_NE : TK_EQ;
  3399   3375         codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op,
  3400   3376                     r1, r2, dest, SQLITE_NULLEQ);
  3401   3377         testcase( regFree1==0 );
  3402   3378         testcase( regFree2==0 );
  3403   3379         break;
  3404   3380       }
................................................................................
  3737   3713   /*
  3738   3714   ** Allocate or deallocate a block of nReg consecutive registers
  3739   3715   */
  3740   3716   int sqlite3GetTempRange(Parse *pParse, int nReg){
  3741   3717     int i, n;
  3742   3718     i = pParse->iRangeReg;
  3743   3719     n = pParse->nRangeReg;
  3744         -  if( nReg<=n && !usedAsColumnCache(pParse, i, i+n-1) ){
         3720  +  if( nReg<=n ){
         3721  +    assert( !usedAsColumnCache(pParse, i, i+n-1) );
  3745   3722       pParse->iRangeReg += nReg;
  3746   3723       pParse->nRangeReg -= nReg;
  3747   3724     }else{
  3748   3725       i = pParse->nMem+1;
  3749   3726       pParse->nMem += nReg;
  3750   3727     }
  3751   3728     return i;
  3752   3729   }
  3753   3730   void sqlite3ReleaseTempRange(Parse *pParse, int iReg, int nReg){
         3731  +  sqlite3ExprCacheRemove(pParse, iReg, nReg);
  3754   3732     if( nReg>pParse->nRangeReg ){
  3755   3733       pParse->nRangeReg = nReg;
  3756   3734       pParse->iRangeReg = iReg;
  3757   3735     }
  3758   3736   }

Changes to src/main.c.

  2301   2301         sqlite3 *db = va_arg(ap, sqlite3*);
  2302   2302         int x = va_arg(ap,int);
  2303   2303         sqlite3_mutex_enter(db->mutex);
  2304   2304         sqlite3BtreeSetPageSize(db->aDb[0].pBt, 0, x, 0);
  2305   2305         sqlite3_mutex_leave(db->mutex);
  2306   2306         break;
  2307   2307       }
         2308  +
         2309  +    /*  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, sqlite3 *db, int N)
         2310  +    **
         2311  +    ** Enable or disable various optimizations for testing purposes.  The 
         2312  +    ** argument N is a bitmask of optimizations to be disabled.  For normal
         2313  +    ** operation N should be 0.  The idea is that a test program (like the
         2314  +    ** SQL Logic Test or SLT test module) can run the same SQL multiple times
         2315  +    ** with various optimizations disabled to verify that the same answer
         2316  +    ** is obtained in every case.
         2317  +    */
         2318  +    case SQLITE_TESTCTRL_OPTIMIZATIONS: {
         2319  +      sqlite3 *db = va_arg(ap, sqlite3*);
         2320  +      int x = va_arg(ap,int);
         2321  +      db->flags = (x & SQLITE_OptMask) | (db->flags & ~SQLITE_OptMask);
         2322  +      break;
         2323  +    }
         2324  +
         2325  +#ifdef SQLITE_N_KEYWORD
         2326  +    /* sqlite3_test_control(SQLITE_TESTCTRL_ISKEYWORD, const char *zWord)
         2327  +    **
         2328  +    ** If zWord is a keyword recognized by the parser, then return the
         2329  +    ** number of keywords.  Or if zWord is not a keyword, return 0.
         2330  +    ** 
         2331  +    ** This test feature is only available in the amalgamation since
         2332  +    ** the SQLITE_N_KEYWORD macro is not defined in this file if SQLite
         2333  +    ** is built using separate source files.
         2334  +    */
         2335  +    case SQLITE_TESTCTRL_ISKEYWORD: {
         2336  +      const char *zWord = va_arg(ap, const char*);
         2337  +      int n = sqlite3Strlen30(zWord);
         2338  +      rc = (sqlite3KeywordCode((u8*)zWord, n)!=TK_ID) ? SQLITE_N_KEYWORD : 0;
         2339  +      break;
         2340  +    }
         2341  +#endif 
  2308   2342   
  2309   2343     }
  2310   2344     va_end(ap);
  2311   2345   #endif /* SQLITE_OMIT_BUILTIN_TEST */
  2312   2346     return rc;
  2313   2347   }

Changes to src/mem2.c.

   205    205   
   206    206   /*
   207    207   ** Round up a request size to the next valid allocation size.
   208    208   */
   209    209   static int sqlite3MemRoundup(int n){
   210    210     return ROUND8(n);
   211    211   }
          212  +
          213  +/*
          214  +** Fill a buffer with pseudo-random bytes.  This is used to preset
          215  +** the content of a new memory allocation to unpredictable values and
          216  +** to clear the content of a freed allocation to unpredictable values.
          217  +*/
          218  +static void randomFill(char *pBuf, int nByte){
          219  +  unsigned int x, y, r;
          220  +  x = SQLITE_PTR_TO_INT(pBuf);
          221  +  y = nByte | 1;
          222  +  while( nByte >= 4 ){
          223  +    x = (x>>1) ^ (-(x&1) & 0xd0000001);
          224  +    y = y*1103515245 + 12345;
          225  +    r = x ^ y;
          226  +    *(int*)pBuf = r;
          227  +    pBuf += 4;
          228  +    nByte -= 4;
          229  +  }
          230  +  while( nByte-- > 0 ){
          231  +    x = (x>>1) ^ (-(x&1) & 0xd0000001);
          232  +    y = y*1103515245 + 12345;
          233  +    r = x ^ y;
          234  +    *(pBuf++) = r & 0xff;
          235  +  }
          236  +}
   212    237   
   213    238   /*
   214    239   ** Allocate nByte bytes of memory.
   215    240   */
   216    241   static void *sqlite3MemMalloc(int nByte){
   217    242     struct MemBlockHdr *pHdr;
   218    243     void **pBt;
................................................................................
   256    281       if( mem.nTitle ){
   257    282         memcpy(z, mem.zTitle, mem.nTitle);
   258    283       }
   259    284       pHdr->iSize = nByte;
   260    285       adjustStats(nByte, +1);
   261    286       pInt = (int*)&pHdr[1];
   262    287       pInt[nReserve/sizeof(int)] = REARGUARD;
   263         -    memset(pInt, 0x65, nReserve);
          288  +    randomFill((char*)pInt, nByte);
          289  +    memset(((char*)pInt)+nByte, 0x65, nReserve-nByte);
   264    290       p = (void*)pInt;
   265    291     }
   266    292     sqlite3_mutex_leave(mem.mutex);
   267    293     return p; 
   268    294   }
   269    295   
   270    296   /*
................................................................................
   292    318     }else{
   293    319       assert( mem.pLast==pHdr );
   294    320       mem.pLast = pHdr->pPrev;
   295    321     }
   296    322     z = (char*)pBt;
   297    323     z -= pHdr->nTitle;
   298    324     adjustStats(pHdr->iSize, -1);
   299         -  memset(z, 0x2b, sizeof(void*)*pHdr->nBacktraceSlots + sizeof(*pHdr) +
   300         -                  pHdr->iSize + sizeof(int) + pHdr->nTitle);
          325  +  randomFill(z, sizeof(void*)*pHdr->nBacktraceSlots + sizeof(*pHdr) +
          326  +                pHdr->iSize + sizeof(int) + pHdr->nTitle);
   301    327     free(z);
   302    328     sqlite3_mutex_leave(mem.mutex);  
   303    329   }
   304    330   
   305    331   /*
   306    332   ** Change the size of an existing memory allocation.
   307    333   **
................................................................................
   316    342     void *pNew;
   317    343     assert( mem.disallow==0 );
   318    344     pOldHdr = sqlite3MemsysGetHeader(pPrior);
   319    345     pNew = sqlite3MemMalloc(nByte);
   320    346     if( pNew ){
   321    347       memcpy(pNew, pPrior, nByte<pOldHdr->iSize ? nByte : pOldHdr->iSize);
   322    348       if( nByte>pOldHdr->iSize ){
   323         -      memset(&((char*)pNew)[pOldHdr->iSize], 0x2b, nByte - pOldHdr->iSize);
          349  +      randomFill(&((char*)pNew)[pOldHdr->iSize], nByte - pOldHdr->iSize);
   324    350       }
   325    351       sqlite3MemFree(pPrior);
   326    352     }
   327    353     return pNew;
   328    354   }
   329    355   
   330    356   /*

Changes to src/os_unix.c.

  4029   4029     ** For this reason, if an error occurs in the stat() call here, it is
  4030   4030     ** ignored and -1 is returned. The caller will try to open a new file
  4031   4031     ** descriptor on the same path, fail, and return an error to SQLite.
  4032   4032     **
  4033   4033     ** Even if a subsequent open() call does succeed, the consequences of
  4034   4034     ** not searching for a resusable file descriptor are not dire.  */
  4035   4035     if( 0==stat(zPath, &sStat) ){
  4036         -    struct unixOpenCnt *pO;
  4037         -    struct unixFileId id;
  4038         -    id.dev = sStat.st_dev;
  4039         -    id.ino = sStat.st_ino;
         4036  +    struct unixOpenCnt *pOpen;
  4040   4037   
  4041   4038       unixEnterMutex();
  4042         -    for(pO=openList; pO && memcmp(&id, &pO->fileId, sizeof(id)); pO=pO->pNext);
  4043         -    if( pO ){
         4039  +    pOpen = openList;
         4040  +    while( pOpen && (pOpen->fileId.dev!=sStat.st_dev
         4041  +                     || pOpen->fileId.ino!=sStat.st_ino) ){
         4042  +       pOpen = pOpen->pNext;
         4043  +    }
         4044  +    if( pOpen ){
  4044   4045         UnixUnusedFd **pp;
  4045         -      for(pp=&pO->pUnused; *pp && (*pp)->flags!=flags; pp=&((*pp)->pNext));
         4046  +      for(pp=&pOpen->pUnused; *pp && (*pp)->flags!=flags; pp=&((*pp)->pNext));
  4046   4047         pUnused = *pp;
  4047   4048         if( pUnused ){
  4048   4049           *pp = pUnused->pNext;
  4049   4050         }
  4050   4051       }
  4051   4052       unixLeaveMutex();
  4052   4053     }

Changes to src/pager.c.

  3858   3858           if( pgno<=pPager->dbOrigSize ){
  3859   3859             TESTONLY( rc = ) sqlite3BitvecSet(pPager->pInJournal, pgno);
  3860   3860             testcase( rc==SQLITE_NOMEM );
  3861   3861           }
  3862   3862           TESTONLY( rc = ) addToSavepointBitvecs(pPager, pgno);
  3863   3863           testcase( rc==SQLITE_NOMEM );
  3864   3864           sqlite3EndBenignMalloc();
  3865         -      }else{
  3866         -        memset(pPg->pData, 0, pPager->pageSize);
  3867   3865         }
         3866  +      memset(pPg->pData, 0, pPager->pageSize);
  3868   3867         IOTRACE(("ZERO %p %d\n", pPager, pgno));
  3869   3868       }else{
  3870   3869         assert( pPg->pPager==pPager );
  3871   3870         rc = readDbPage(pPg);
  3872   3871         if( rc!=SQLITE_OK ){
  3873   3872           goto pager_acquire_err;
  3874   3873         }

Changes to src/prepare.c.

   468    468           db->mallocFailed = 1;
   469    469         }
   470    470         if( rc!=SQLITE_OK ) return;
   471    471         openedTransaction = 1;
   472    472       }
   473    473   
   474    474       /* Read the schema cookie from the database. If it does not match the 
   475         -    ** value stored as part of the in the in-memory schema representation,
          475  +    ** value stored as part of the in-memory schema representation,
   476    476       ** set Parse.rc to SQLITE_SCHEMA. */
   477    477       sqlite3BtreeGetMeta(pBt, BTREE_SCHEMA_VERSION, (u32 *)&cookie);
   478    478       if( cookie!=db->aDb[iDb].pSchema->schema_cookie ){
   479    479         pParse->rc = SQLITE_SCHEMA;
   480    480       }
   481    481   
   482    482       /* Close the transaction, if one was opened. */

Changes to src/resolve.c.

   239    239           pTab = pParse->pTriggerTab;
   240    240         }
   241    241   
   242    242         if( pTab ){ 
   243    243           int iCol;
   244    244           pSchema = pTab->pSchema;
   245    245           cntTab++;
   246         -        if( sqlite3IsRowid(zCol) ){
   247         -          iCol = -1;
   248         -        }else{
   249         -          for(iCol=0; iCol<pTab->nCol; iCol++){
   250         -            Column *pCol = &pTab->aCol[iCol];
   251         -            if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
   252         -              if( iCol==pTab->iPKey ){
   253         -                iCol = -1;
   254         -              }
   255         -              break;
          246  +        for(iCol=0; iCol<pTab->nCol; iCol++){
          247  +          Column *pCol = &pTab->aCol[iCol];
          248  +          if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
          249  +            if( iCol==pTab->iPKey ){
          250  +              iCol = -1;
   256    251               }
          252  +            break;
   257    253             }
          254  +        }
          255  +        if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) ){
          256  +          iCol = -1;        /* IMP: R-44911-55124 */
   258    257           }
   259    258           if( iCol<pTab->nCol ){
   260    259             cnt++;
   261    260             if( iCol<0 ){
   262    261               pExpr->affinity = SQLITE_AFF_INTEGER;
   263    262             }else if( pExpr->iTable==0 ){
   264    263               testcase( iCol==31 );
................................................................................
   278    277   #endif /* !defined(SQLITE_OMIT_TRIGGER) */
   279    278   
   280    279       /*
   281    280       ** Perhaps the name is a reference to the ROWID
   282    281       */
   283    282       if( cnt==0 && cntTab==1 && sqlite3IsRowid(zCol) ){
   284    283         cnt = 1;
   285         -      pExpr->iColumn = -1;
          284  +      pExpr->iColumn = -1;     /* IMP: R-44911-55124 */
   286    285         pExpr->affinity = SQLITE_AFF_INTEGER;
   287    286       }
   288    287   
   289    288       /*
   290    289       ** If the input is of the form Z (not Y.Z or X.Y.Z) then the name Z
   291    290       ** might refer to an result-set alias.  This happens, for example, when
   292    291       ** we are resolving names in the WHERE clause of the following command:

Changes to src/select.c.

   953    953   
   954    954         assert( pTab && pExpr->pTab==pTab );
   955    955         if( pS ){
   956    956           /* The "table" is actually a sub-select or a view in the FROM clause
   957    957           ** of the SELECT statement. Return the declaration type and origin
   958    958           ** data for the result-set column of the sub-select.
   959    959           */
   960         -        if( ALWAYS(iCol>=0 && iCol<pS->pEList->nExpr) ){
          960  +        if( iCol>=0 && ALWAYS(iCol<pS->pEList->nExpr) ){
   961    961             /* If iCol is less than zero, then the expression requests the
   962    962             ** rowid of the sub-select or view. This expression is legal (see 
   963    963             ** test case misc2.2.2) - it always evaluates to NULL.
   964    964             */
   965    965             NameContext sNC;
   966    966             Expr *p = pS->pEList->a[iCol].pExpr;
   967    967             sNC.pSrcList = pS->pSrc;
................................................................................
  2514   2514   **        aggregates.
  2515   2515   **
  2516   2516   **  (10)  The subquery does not use aggregates or the outer query does not
  2517   2517   **        use LIMIT.
  2518   2518   **
  2519   2519   **  (11)  The subquery and the outer query do not both have ORDER BY clauses.
  2520   2520   **
  2521         -**  (12)  Not implemented.  Subsumed into restriction (3).  Was previously
         2521  +**  (**)  Not implemented.  Subsumed into restriction (3).  Was previously
  2522   2522   **        a separate restriction deriving from ticket #350.
  2523   2523   **
  2524   2524   **  (13)  The subquery and outer query do not both use LIMIT
  2525   2525   **
  2526   2526   **  (14)  The subquery does not use OFFSET
  2527   2527   **
  2528   2528   **  (15)  The outer query is not part of a compound select or the
................................................................................
  2588   2588     struct SrcList_item *pSubitem;   /* The subquery */
  2589   2589     sqlite3 *db = pParse->db;
  2590   2590   
  2591   2591     /* Check to see if flattening is permitted.  Return 0 if not.
  2592   2592     */
  2593   2593     assert( p!=0 );
  2594   2594     assert( p->pPrior==0 );  /* Unable to flatten compound queries */
         2595  +  if( db->flags & SQLITE_QueryFlattener ) return 0;
  2595   2596     pSrc = p->pSrc;
  2596   2597     assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
  2597   2598     pSubitem = &pSrc->a[iFrom];
  2598   2599     iParent = pSubitem->iCursor;
  2599   2600     pSub = pSubitem->pSelect;
  2600   2601     assert( pSub!=0 );
  2601   2602     if( isAgg && subqueryIsAgg ) return 0;                 /* Restriction (1)  */
................................................................................
  3485   3486           pColl = pParse->db->pDfltColl;
  3486   3487         }
  3487   3488         sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
  3488   3489       }
  3489   3490       sqlite3VdbeAddOp4(v, OP_AggStep, 0, regAgg, pF->iMem,
  3490   3491                         (void*)pF->pFunc, P4_FUNCDEF);
  3491   3492       sqlite3VdbeChangeP5(v, (u8)nArg);
  3492         -    sqlite3ReleaseTempRange(pParse, regAgg, nArg);
  3493   3493       sqlite3ExprCacheAffinityChange(pParse, regAgg, nArg);
         3494  +    sqlite3ReleaseTempRange(pParse, regAgg, nArg);
  3494   3495       if( addrNext ){
  3495   3496         sqlite3VdbeResolveLabel(v, addrNext);
  3496   3497         sqlite3ExprCacheClear(pParse);
  3497   3498       }
  3498   3499     }
  3499   3500     for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
  3500   3501       sqlite3ExprCode(pParse, pC->pExpr, pC->iMem);
................................................................................
  3912   3913           for(i=0; i<sAggInfo.nColumn; i++){
  3913   3914             struct AggInfo_col *pCol = &sAggInfo.aCol[i];
  3914   3915             if( pCol->iSorterColumn>=j ){
  3915   3916               int r1 = j + regBase;
  3916   3917               int r2;
  3917   3918   
  3918   3919               r2 = sqlite3ExprCodeGetColumn(pParse, 
  3919         -                               pCol->pTab, pCol->iColumn, pCol->iTable, r1, 0);
         3920  +                               pCol->pTab, pCol->iColumn, pCol->iTable, r1);
  3920   3921               if( r1!=r2 ){
  3921   3922                 sqlite3VdbeAddOp2(v, OP_SCopy, r2, r1);
  3922   3923               }
  3923   3924               j++;
  3924   3925             }
  3925   3926           }
  3926   3927           regRecord = sqlite3GetTempReg(pParse);

Changes to src/shell.c.

  1208   1208         zLine[n] = 0;
  1209   1209         eol = 1;
  1210   1210         break;
  1211   1211       }
  1212   1212       while( zLine[n] ){ n++; }
  1213   1213       if( n>0 && zLine[n-1]=='\n' ){
  1214   1214         n--;
         1215  +      if( n>0 && zLine[n-1]=='\r' ) n--;
  1215   1216         zLine[n] = 0;
  1216   1217         eol = 1;
  1217   1218       }
  1218   1219     }
  1219   1220     zLine = realloc( zLine, n+1 );
  1220   1221     return zLine;
  1221   1222   }
................................................................................
  1828   1829     sqlite3 *db,                                /* An open database */
  1829   1830     const char *zSql,                           /* SQL to be evaluated */
  1830   1831     int (*xCallback)(void*,int,char**,char**,int*),   /* Callback function */
  1831   1832                                                 /* (not the same as sqlite3_exec) */
  1832   1833     struct callback_data *pArg,                 /* Pointer to struct callback_data */
  1833   1834     char **pzErrMsg                             /* Error msg written here */
  1834   1835   ){
  1835         -  sqlite3_stmt *pStmt = NULL;
  1836         -  int rc = SQLITE_OK;
  1837         -  int rc2;
  1838         -  const char *zLeftover;      /* Tail of unprocessed SQL */
         1836  +  sqlite3_stmt *pStmt = NULL;     /* Statement to execute. */
         1837  +  int rc = SQLITE_OK;             /* Return Code */
         1838  +  const char *zLeftover;          /* Tail of unprocessed SQL */
  1839   1839   
  1840   1840     if( pzErrMsg ){
  1841   1841       *pzErrMsg = NULL;
  1842   1842     }
  1843   1843   
  1844   1844     while( zSql[0] && (SQLITE_OK == rc) ){
  1845   1845       rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover);
................................................................................
  1912   1912           }else{
  1913   1913             do{
  1914   1914               rc = sqlite3_step(pStmt);
  1915   1915             } while( rc == SQLITE_ROW );
  1916   1916           }
  1917   1917         }
  1918   1918   
  1919         -      /* if the last sqlite3_step() didn't complete successfully... */
  1920         -      if( (SQLITE_OK != rc) && (SQLITE_DONE != rc) ){ 
  1921         -        if( pzErrMsg ){
  1922         -          *pzErrMsg = save_err_msg(db);
  1923         -        }
  1924         -      }else{
  1925         -        rc = SQLITE_OK;
  1926         -      }
  1927         -
  1928         -      rc2 = sqlite3_finalize(pStmt);
  1929         -      /* if the last sqlite3_finalize() didn't complete successfully 
  1930         -      ** AND we don't have a saved error from sqlite3_step ... */
  1931         -      if( (SQLITE_OK != rc2) && (SQLITE_OK == rc) ){
  1932         -        rc = rc2;
  1933         -        if( pzErrMsg ){
  1934         -          *pzErrMsg = save_err_msg(db);
  1935         -        }
  1936         -      }
  1937         -
  1938         -      if( SQLITE_OK == rc ){ 
         1919  +      /* Finalize the statement just executed. If this fails, save a 
         1920  +      ** copy of the error message. Otherwise, set zSql to point to the
         1921  +      ** next statement to execute. */
         1922  +      rc = sqlite3_finalize(pStmt);
         1923  +      if( rc==SQLITE_OK ){
  1939   1924           zSql = zLeftover;
  1940   1925           while( isspace(zSql[0]) ) zSql++;
         1926  +      }else if( pzErrMsg ){
         1927  +        *pzErrMsg = save_err_msg(db);
  1941   1928         }
  1942   1929       }
  1943   1930     } /* end while */
  1944   1931   
  1945   1932     return rc;
  1946   1933   }
  1947   1934   

Changes to src/sqlite.h.in.

  1557   1557   ** [database connection].  Setting a new busy handler clears any
  1558   1558   ** previously set handler.)^  ^Note that calling [sqlite3_busy_timeout()]
  1559   1559   ** will also set or clear the busy handler.
  1560   1560   **
  1561   1561   ** The busy callback should not take any actions which modify the
  1562   1562   ** database connection that invoked the busy handler.  Any such actions
  1563   1563   ** result in undefined behavior.
  1564         -**
         1564  +** 
  1565   1565   ** A busy handler must not close the database connection
  1566   1566   ** or [prepared statement] that invoked the busy handler.
  1567   1567   */
  1568   1568   int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*);
  1569   1569   
  1570   1570   /*
  1571   1571   ** CAPI3REF: Set A Busy Timeout
................................................................................
  4920   4920   ** as the first argument to [sqlite3_test_control()].
  4921   4921   **
  4922   4922   ** These parameters and their meanings are subject to change
  4923   4923   ** without notice.  These values are for testing purposes only.
  4924   4924   ** Applications should not use any of these parameters or the
  4925   4925   ** [sqlite3_test_control()] interface.
  4926   4926   */
         4927  +#define SQLITE_TESTCTRL_FIRST                    5
  4927   4928   #define SQLITE_TESTCTRL_PRNG_SAVE                5
  4928   4929   #define SQLITE_TESTCTRL_PRNG_RESTORE             6
  4929   4930   #define SQLITE_TESTCTRL_PRNG_RESET               7
  4930   4931   #define SQLITE_TESTCTRL_BITVEC_TEST              8
  4931   4932   #define SQLITE_TESTCTRL_FAULT_INSTALL            9
  4932   4933   #define SQLITE_TESTCTRL_BENIGN_MALLOC_HOOKS     10
  4933   4934   #define SQLITE_TESTCTRL_PENDING_BYTE            11
  4934   4935   #define SQLITE_TESTCTRL_ASSERT                  12
  4935   4936   #define SQLITE_TESTCTRL_ALWAYS                  13
  4936   4937   #define SQLITE_TESTCTRL_RESERVE                 14
         4938  +#define SQLITE_TESTCTRL_OPTIMIZATIONS           15
         4939  +#define SQLITE_TESTCTRL_ISKEYWORD               16
         4940  +#define SQLITE_TESTCTRL_LAST                    16
  4937   4941   
  4938   4942   /*
  4939   4943   ** CAPI3REF: SQLite Runtime Status
  4940   4944   ** EXPERIMENTAL
  4941   4945   **
  4942   4946   ** ^This interface is used to retrieve runtime status information
  4943   4947   ** about the preformance of SQLite, and optionally to reset various

Changes to src/sqliteInt.h.

   894    894   
   895    895   /*
   896    896   ** A macro to discover the encoding of a database.
   897    897   */
   898    898   #define ENC(db) ((db)->aDb[0].pSchema->enc)
   899    899   
   900    900   /*
   901         -** Possible values for the sqlite.flags and or Db.flags fields.
   902         -**
   903         -** On sqlite.flags, the SQLITE_InTrans value means that we have
   904         -** executed a BEGIN.  On Db.flags, SQLITE_InTrans means a statement
   905         -** transaction is active on that particular database file.
          901  +** Possible values for the sqlite3.flags.
   906    902   */
   907         -#define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */
   908         -#define SQLITE_InTrans        0x00000008  /* True if in a transaction */
   909         -#define SQLITE_InternChanges  0x00000010  /* Uncommitted Hash table changes */
   910         -#define SQLITE_FullColNames   0x00000020  /* Show full column names on SELECT */
   911         -#define SQLITE_ShortColNames  0x00000040  /* Show short columns names */
   912         -#define SQLITE_CountRows      0x00000080  /* Count rows changed by INSERT, */
          903  +#define SQLITE_VdbeTrace      0x00000100  /* True to trace VDBE execution */
          904  +#define SQLITE_InternChanges  0x00000200  /* Uncommitted Hash table changes */
          905  +#define SQLITE_FullColNames   0x00000400  /* Show full column names on SELECT */
          906  +#define SQLITE_ShortColNames  0x00000800  /* Show short columns names */
          907  +#define SQLITE_CountRows      0x00001000  /* Count rows changed by INSERT, */
   913    908                                             /*   DELETE, or UPDATE and return */
   914    909                                             /*   the count using a callback. */
   915         -#define SQLITE_NullCallback   0x00000100  /* Invoke the callback once if the */
          910  +#define SQLITE_NullCallback   0x00002000  /* Invoke the callback once if the */
   916    911                                             /*   result set is empty */
   917         -#define SQLITE_SqlTrace       0x00000200  /* Debug print SQL as it executes */
   918         -#define SQLITE_VdbeListing    0x00000400  /* Debug listings of VDBE programs */
   919         -#define SQLITE_WriteSchema    0x00000800  /* OK to update SQLITE_MASTER */
   920         -#define SQLITE_NoReadlock     0x00001000  /* Readlocks are omitted when 
          912  +#define SQLITE_SqlTrace       0x00004000  /* Debug print SQL as it executes */
          913  +#define SQLITE_VdbeListing    0x00008000  /* Debug listings of VDBE programs */
          914  +#define SQLITE_WriteSchema    0x00010000  /* OK to update SQLITE_MASTER */
          915  +#define SQLITE_NoReadlock     0x00020000  /* Readlocks are omitted when 
   921    916                                             ** accessing read-only databases */
   922         -#define SQLITE_IgnoreChecks   0x00002000  /* Do not enforce check constraints */
   923         -#define SQLITE_ReadUncommitted 0x00004000 /* For shared-cache mode */
   924         -#define SQLITE_LegacyFileFmt  0x00008000  /* Create new databases in format 1 */
   925         -#define SQLITE_FullFSync      0x00010000  /* Use full fsync on the backend */
   926         -#define SQLITE_LoadExtension  0x00020000  /* Enable load_extension */
          917  +#define SQLITE_IgnoreChecks   0x00040000  /* Do not enforce check constraints */
          918  +#define SQLITE_ReadUncommitted 0x0080000  /* For shared-cache mode */
          919  +#define SQLITE_LegacyFileFmt  0x00100000  /* Create new databases in format 1 */
          920  +#define SQLITE_FullFSync      0x00200000  /* Use full fsync on the backend */
          921  +#define SQLITE_LoadExtension  0x00400000  /* Enable load_extension */
          922  +#define SQLITE_RecoveryMode   0x00800000  /* Ignore schema errors */
          923  +#define SQLITE_ReverseOrder   0x01000000  /* Reverse unordered SELECTs */
          924  +#define SQLITE_RecTriggers    0x02000000  /* Enable recursive triggers */
          925  +#define SQLITE_ForeignKeys    0x04000000  /* Enforce foreign key constraints  */
   927    926   
   928         -#define SQLITE_RecoveryMode   0x00040000  /* Ignore schema errors */
   929         -#define SQLITE_ReverseOrder   0x00100000  /* Reverse unordered SELECTs */
   930         -#define SQLITE_RecTriggers    0x00200000  /* Enable recursive triggers */
   931         -#define SQLITE_ForeignKeys    0x00400000  /* Enforce foreign key constraints  */
          927  +/*
          928  +** Bits of the sqlite3.flags field that are used by the
          929  +** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface.
          930  +** These must be the low-order bits of the flags field.
          931  +*/
          932  +#define SQLITE_QueryFlattener 0x01        /* Disable query flattening */
          933  +#define SQLITE_ColumnCache    0x02        /* Disable the column cache */
          934  +#define SQLITE_IndexSort      0x04        /* Disable indexes for sorting */
          935  +#define SQLITE_IndexSearch    0x08        /* Disable indexes for searching */
          936  +#define SQLITE_IndexCover     0x10        /* Disable index covering table */
          937  +#define SQLITE_OptMask        0x1f        /* Mask of all disablable opts */
   932    938   
   933    939   /*
   934    940   ** Possible values for the sqlite.magic field.
   935    941   ** The numbers are obtained at random and have no special meaning, other
   936    942   ** than being distinct from one another.
   937    943   */
   938    944   #define SQLITE_MAGIC_OPEN     0xa029a697  /* Database is open */
................................................................................
  1638   1644   #define EP_Resolved   0x0004  /* IDs have been resolved to COLUMNs */
  1639   1645   #define EP_Error      0x0008  /* Expression contains one or more errors */
  1640   1646   #define EP_Distinct   0x0010  /* Aggregate function with DISTINCT keyword */
  1641   1647   #define EP_VarSelect  0x0020  /* pSelect is correlated, not constant */
  1642   1648   #define EP_DblQuoted  0x0040  /* token.z was originally in "..." */
  1643   1649   #define EP_InfixFunc  0x0080  /* True for an infix function: LIKE, GLOB, etc */
  1644   1650   #define EP_ExpCollate 0x0100  /* Collating sequence specified explicitly */
  1645         -#define EP_AnyAff     0x0200  /* Can take a cached column of any affinity */
  1646         -#define EP_FixedDest  0x0400  /* Result needed in a specific register */
  1647         -#define EP_IntValue   0x0800  /* Integer value contained in u.iValue */
  1648         -#define EP_xIsSelect  0x1000  /* x.pSelect is valid (otherwise x.pList is) */
         1651  +#define EP_FixedDest  0x0200  /* Result needed in a specific register */
         1652  +#define EP_IntValue   0x0400  /* Integer value contained in u.iValue */
         1653  +#define EP_xIsSelect  0x0800  /* x.pSelect is valid (otherwise x.pList is) */
  1649   1654   
  1650         -#define EP_Reduced    0x2000  /* Expr struct is EXPR_REDUCEDSIZE bytes only */
  1651         -#define EP_TokenOnly  0x4000  /* Expr struct is EXPR_TOKENONLYSIZE bytes only */
  1652         -#define EP_Static     0x8000  /* Held in memory not obtained from malloc() */
         1655  +#define EP_Reduced    0x1000  /* Expr struct is EXPR_REDUCEDSIZE bytes only */
         1656  +#define EP_TokenOnly  0x2000  /* Expr struct is EXPR_TOKENONLYSIZE bytes only */
         1657  +#define EP_Static     0x4000  /* Held in memory not obtained from malloc() */
  1653   1658   
  1654   1659   /*
  1655   1660   ** The following are the meanings of bits in the Expr.flags2 field.
  1656   1661   */
  1657   1662   #define EP2_MallocedToken  0x0001  /* Need to sqlite3DbFree() Expr.zToken */
  1658   1663   #define EP2_Irreducible    0x0002  /* Cannot EXPRDUP_REDUCE this Expr */
  1659   1664   
................................................................................
  2123   2128     int iCacheLevel;     /* ColCache valid when aColCache[].iLevel<=iCacheLevel */
  2124   2129     int iCacheCnt;       /* Counter used to generate aColCache[].lru values */
  2125   2130     u8 nColCache;        /* Number of entries in the column cache */
  2126   2131     u8 iColCache;        /* Next entry of the cache to replace */
  2127   2132     struct yColCache {
  2128   2133       int iTable;           /* Table cursor number */
  2129   2134       int iColumn;          /* Table column number */
  2130         -    u8 affChange;         /* True if this register has had an affinity change */
  2131   2135       u8 tempReg;           /* iReg is a temp register that needs to be freed */
  2132   2136       int iLevel;           /* Nesting level */
  2133   2137       int iReg;             /* Reg with value of this column. 0 means none. */
  2134   2138       int lru;              /* Least recently used entry has the smallest value */
  2135   2139     } aColCache[SQLITE_N_COLCACHE];  /* One for each column cache entry */
  2136   2140     u32 writeMask;       /* Start a write transaction on these databases */
  2137   2141     u32 cookieMask;      /* Bitmask of schema verified databases */
................................................................................
  2642   2646   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  2643   2647   Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *);
  2644   2648   #endif
  2645   2649   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  2646   2650   void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
  2647   2651   WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**, u16);
  2648   2652   void sqlite3WhereEnd(WhereInfo*);
  2649         -int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, int);
         2653  +int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int);
  2650   2654   void sqlite3ExprCodeMove(Parse*, int, int, int);
  2651   2655   void sqlite3ExprCodeCopy(Parse*, int, int, int);
  2652   2656   void sqlite3ExprCacheStore(Parse*, int, int, int);
  2653   2657   void sqlite3ExprCachePush(Parse*);
  2654   2658   void sqlite3ExprCachePop(Parse*, int);
  2655         -void sqlite3ExprCacheRemove(Parse*, int);
         2659  +void sqlite3ExprCacheRemove(Parse*, int, int);
  2656   2660   void sqlite3ExprCacheClear(Parse*);
  2657   2661   void sqlite3ExprCacheAffinityChange(Parse*, int, int);
  2658   2662   void sqlite3ExprHardCopy(Parse*,int,int);
  2659   2663   int sqlite3ExprCode(Parse*, Expr*, int);
  2660   2664   int sqlite3ExprCodeTemp(Parse*, Expr*, int*);
  2661   2665   int sqlite3ExprCodeTarget(Parse*, Expr*, int);
  2662   2666   int sqlite3ExprCodeAndCache(Parse*, Expr*, int);

Changes to src/vacuum.c.

    82     82     int rc = SQLITE_OK;     /* Return code from service routines */
    83     83     Btree *pMain;           /* The database being vacuumed */
    84     84     Btree *pTemp;           /* The temporary database we vacuum into */
    85     85     char *zSql = 0;         /* SQL statements */
    86     86     int saved_flags;        /* Saved value of the db->flags */
    87     87     int saved_nChange;      /* Saved value of db->nChange */
    88     88     int saved_nTotalChange; /* Saved value of db->nTotalChange */
           89  +  void (*saved_xTrace)(void*,const char*);  /* Saved db->xTrace */
    89     90     Db *pDb = 0;            /* Database to detach at end of vacuum */
    90     91     int isMemDb;            /* True if vacuuming a :memory: database */
    91     92     int nRes;
    92     93   
    93     94     if( !db->autoCommit ){
    94     95       sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
    95     96       return SQLITE_ERROR;
................................................................................
    97     98   
    98     99     /* Save the current value of the database flags so that it can be 
    99    100     ** restored before returning. Then set the writable-schema flag, and
   100    101     ** disable CHECK and foreign key constraints.  */
   101    102     saved_flags = db->flags;
   102    103     saved_nChange = db->nChange;
   103    104     saved_nTotalChange = db->nTotalChange;
          105  +  saved_xTrace = db->xTrace;
   104    106     db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
   105    107     db->flags &= ~SQLITE_ForeignKeys;
          108  +  db->xTrace = 0;
   106    109   
   107    110     pMain = db->aDb[0].pBt;
   108    111     isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
   109    112   
   110    113     /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
   111    114     ** can be set to 'off' for this file, as it is not recovered if a crash
   112    115     ** occurs anyway. The integrity of the database is maintained by a
................................................................................
   279    282     rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
   280    283   
   281    284   end_of_vacuum:
   282    285     /* Restore the original value of db->flags */
   283    286     db->flags = saved_flags;
   284    287     db->nChange = saved_nChange;
   285    288     db->nTotalChange = saved_nTotalChange;
          289  +  db->xTrace = saved_xTrace;
   286    290   
   287    291     /* Currently there is an SQL level transaction open on the vacuum
   288    292     ** database. No locks are held on any other files (since the main file
   289    293     ** was committed at the btree level). So it safe to end the transaction
   290    294     ** by manually setting the autoCommit flag to true and detaching the
   291    295     ** vacuum database. The vacuum_db journal file is deleted when the pager
   292    296     ** is closed by the DETACH.

Changes to src/vdbe.c.

  3659   3659         v = sqlite3BtreeGetCachedRowid(pC->pCursor);
  3660   3660         if( v==0 ){
  3661   3661           rc = sqlite3BtreeLast(pC->pCursor, &res);
  3662   3662           if( rc!=SQLITE_OK ){
  3663   3663             goto abort_due_to_error;
  3664   3664           }
  3665   3665           if( res ){
  3666         -          v = 1;
         3666  +          v = 1;   /* IMP: R-61914-48074 */
  3667   3667           }else{
  3668   3668             assert( sqlite3BtreeCursorIsValid(pC->pCursor) );
  3669   3669             rc = sqlite3BtreeKeySize(pC->pCursor, &v);
  3670   3670             assert( rc==SQLITE_OK );   /* Cannot fail following BtreeLast() */
  3671   3671             if( v==MAX_ROWID ){
  3672   3672               pC->useRandomRowid = 1;
  3673   3673             }else{
  3674         -            v++;
         3674  +            v++;   /* IMP: R-29538-34987 */
  3675   3675             }
  3676   3676           }
  3677   3677         }
  3678   3678   
  3679   3679   #ifndef SQLITE_OMIT_AUTOINCREMENT
  3680   3680         if( pOp->p3 ){
  3681   3681           /* Assert that P3 is a valid memory cell. */
................................................................................
  3691   3691             pMem = &aMem[pOp->p3];
  3692   3692           }
  3693   3693   
  3694   3694           REGISTER_TRACE(pOp->p3, pMem);
  3695   3695           sqlite3VdbeMemIntegerify(pMem);
  3696   3696           assert( (pMem->flags & MEM_Int)!=0 );  /* mem(P3) holds an integer */
  3697   3697           if( pMem->u.i==MAX_ROWID || pC->useRandomRowid ){
  3698         -          rc = SQLITE_FULL;
         3698  +          rc = SQLITE_FULL;   /* IMP: R-12275-61338 */
  3699   3699             goto abort_due_to_error;
  3700   3700           }
  3701   3701           if( v<pMem->u.i+1 ){
  3702   3702             v = pMem->u.i + 1;
  3703   3703           }
  3704   3704           pMem->u.i = v;
  3705   3705         }
  3706   3706   #endif
  3707   3707   
  3708   3708         sqlite3BtreeSetCachedRowid(pC->pCursor, v<MAX_ROWID ? v+1 : 0);
  3709   3709       }
  3710   3710       if( pC->useRandomRowid ){
         3711  +      /* IMPLEMENTATION-OF: R-48598-02938 If the largest ROWID is equal to the
         3712  +      ** largest possible integer (9223372036854775807) then the database
         3713  +      ** engine starts picking candidate ROWIDs at random until it finds one
         3714  +      ** that is not previously used.
         3715  +      */
  3711   3716         assert( pOp->p3==0 );  /* We cannot be in random rowid mode if this is
  3712   3717                                ** an AUTOINCREMENT table. */
  3713   3718         v = db->lastRowid;
  3714   3719         cnt = 0;
  3715   3720         do{
  3716   3721           if( cnt==0 && (v&0xffffff)==v ){
  3717   3722             v++;
................................................................................
  3719   3724             sqlite3_randomness(sizeof(v), &v);
  3720   3725             if( cnt<5 ) v &= 0xffffff;
  3721   3726           }
  3722   3727           rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, 0, (u64)v, 0, &res);
  3723   3728           cnt++;
  3724   3729         }while( cnt<100 && rc==SQLITE_OK && res==0 );
  3725   3730         if( rc==SQLITE_OK && res==0 ){
  3726         -        rc = SQLITE_FULL;
         3731  +        rc = SQLITE_FULL;   /* IMP: R-38219-53002 */
  3727   3732           goto abort_due_to_error;
  3728   3733         }
  3729   3734       }
  3730   3735       pC->rowidIsValid = 0;
  3731   3736       pC->deferredMoveto = 0;
  3732   3737       pC->cacheStatus = CACHE_STALE;
  3733   3738     }

Changes to src/vdbeaux.c.

  1037   1037   ** running the code, it invokes the callback once for each instruction.
  1038   1038   ** This feature is used to implement "EXPLAIN".
  1039   1039   **
  1040   1040   ** When p->explain==1, each instruction is listed.  When
  1041   1041   ** p->explain==2, only OP_Explain instructions are listed and these
  1042   1042   ** are shown in a different format.  p->explain==2 is used to implement
  1043   1043   ** EXPLAIN QUERY PLAN.
         1044  +**
         1045  +** When p->explain==1, first the main program is listed, then each of
         1046  +** the trigger subprograms are listed one by one.
  1044   1047   */
  1045   1048   int sqlite3VdbeList(
  1046   1049     Vdbe *p                   /* The VDBE */
  1047   1050   ){
  1048         -  int nRow;                            /* Total number of rows to return */
         1051  +  int nRow;                            /* Stop when row count reaches this */
  1049   1052     int nSub = 0;                        /* Number of sub-vdbes seen so far */
  1050   1053     SubProgram **apSub = 0;              /* Array of sub-vdbes */
  1051         -  Mem *pSub = 0;
  1052         -  sqlite3 *db = p->db;
  1053         -  int i;
  1054         -  int rc = SQLITE_OK;
  1055         -  Mem *pMem = p->pResultSet = &p->aMem[1];
         1054  +  Mem *pSub = 0;                       /* Memory cell hold array of subprogs */
         1055  +  sqlite3 *db = p->db;                 /* The database connection */
         1056  +  int i;                               /* Loop counter */
         1057  +  int rc = SQLITE_OK;                  /* Return code */
         1058  +  Mem *pMem = p->pResultSet = &p->aMem[1];  /* First Mem of result set */
  1056   1059   
  1057   1060     assert( p->explain );
  1058   1061     assert( p->magic==VDBE_MAGIC_RUN );
  1059   1062     assert( db->magic==SQLITE_MAGIC_BUSY );
  1060   1063     assert( p->rc==SQLITE_OK || p->rc==SQLITE_BUSY || p->rc==SQLITE_NOMEM );
  1061   1064   
  1062   1065     /* Even though this opcode does not use dynamic strings for
................................................................................
  1068   1071     if( p->rc==SQLITE_NOMEM ){
  1069   1072       /* This happens if a malloc() inside a call to sqlite3_column_text() or
  1070   1073       ** sqlite3_column_text16() failed.  */
  1071   1074       db->mallocFailed = 1;
  1072   1075       return SQLITE_ERROR;
  1073   1076     }
  1074   1077   
  1075         -  /* Figure out total number of rows that will be returned by this 
  1076         -  ** EXPLAIN program.  */
         1078  +  /* When the number of output rows reaches nRow, that means the
         1079  +  ** listing has finished and sqlite3_step() should return SQLITE_DONE.
         1080  +  ** nRow is the sum of the number of rows in the main program, plus
         1081  +  ** the sum of the number of rows in all trigger subprograms encountered
         1082  +  ** so far.  The nRow value will increase as new trigger subprograms are
         1083  +  ** encountered, but p->pc will eventually catch up to nRow.
         1084  +  */
  1077   1085     nRow = p->nOp;
  1078   1086     if( p->explain==1 ){
         1087  +    /* The first 8 memory cells are used for the result set.  So we will
         1088  +    ** commandeer the 9th cell to use as storage for an array of pointers
         1089  +    ** to trigger subprograms.  The VDBE is guaranteed to have at least 9
         1090  +    ** cells.  */
         1091  +    assert( p->nMem>9 );
  1079   1092       pSub = &p->aMem[9];
  1080   1093       if( pSub->flags&MEM_Blob ){
         1094  +      /* On the first call to sqlite3_step(), pSub will hold a NULL.  It is
         1095  +      ** initialized to a BLOB by the P4_SUBPROGRAM processing logic below */
  1081   1096         nSub = pSub->n/sizeof(Vdbe*);
  1082   1097         apSub = (SubProgram **)pSub->z;
  1083   1098       }
  1084   1099       for(i=0; i<nSub; i++){
  1085   1100         nRow += apSub[i]->nOp;
  1086   1101       }
  1087   1102     }
................................................................................
  1096   1111       p->rc = SQLITE_INTERRUPT;
  1097   1112       rc = SQLITE_ERROR;
  1098   1113       sqlite3SetString(&p->zErrMsg, db, "%s", sqlite3ErrStr(p->rc));
  1099   1114     }else{
  1100   1115       char *z;
  1101   1116       Op *pOp;
  1102   1117       if( i<p->nOp ){
         1118  +      /* The output line number is small enough that we are still in the
         1119  +      ** main program. */
  1103   1120         pOp = &p->aOp[i];
  1104   1121       }else{
         1122  +      /* We are currently listing subprograms.  Figure out which one and
         1123  +      ** pick up the appropriate opcode. */
  1105   1124         int j;
  1106   1125         i -= p->nOp;
  1107   1126         for(j=0; i>=apSub[j]->nOp; j++){
  1108   1127           i -= apSub[j]->nOp;
  1109   1128         }
  1110   1129         pOp = &apSub[j]->aOp[i];
  1111   1130       }
................................................................................
  1119   1138         pMem->z = (char*)sqlite3OpcodeName(pOp->opcode);  /* Opcode */
  1120   1139         assert( pMem->z!=0 );
  1121   1140         pMem->n = sqlite3Strlen30(pMem->z);
  1122   1141         pMem->type = SQLITE_TEXT;
  1123   1142         pMem->enc = SQLITE_UTF8;
  1124   1143         pMem++;
  1125   1144   
         1145  +      /* When an OP_Program opcode is encounter (the only opcode that has
         1146  +      ** a P4_SUBPROGRAM argument), expand the size of the array of subprograms
         1147  +      ** kept in p->aMem[9].z to hold the new program - assuming this subprogram
         1148  +      ** has not already been seen.
         1149  +      */
  1126   1150         if( pOp->p4type==P4_SUBPROGRAM ){
  1127   1151           int nByte = (nSub+1)*sizeof(SubProgram*);
  1128   1152           int j;
  1129   1153           for(j=0; j<nSub; j++){
  1130   1154             if( apSub[j]==pOp->p4.pProgram ) break;
  1131   1155           }
  1132   1156           if( j==nSub && SQLITE_OK==sqlite3VdbeMemGrow(pSub, nByte, 1) ){

Changes to src/where.c.

  3156   3156   
  3157   3157       /* Load the value for the inequality constraint at the end of the
  3158   3158       ** range (if any).
  3159   3159       */
  3160   3160       nConstraint = nEq;
  3161   3161       if( pRangeEnd ){
  3162   3162         Expr *pRight = pRangeEnd->pExpr->pRight;
  3163         -      sqlite3ExprCacheRemove(pParse, regBase+nEq);
         3163  +      sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
  3164   3164         sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3165   3165         sqlite3ExprCodeIsNullJump(v, pRight, regBase+nEq, addrNxt);
  3166   3166         if( zAff ){
  3167   3167           if( sqlite3CompareAffinity(pRight, zAff[nConstraint])==SQLITE_AFF_NONE){
  3168   3168             /* Since the comparison is to be performed with no conversions
  3169   3169             ** applied to the operands, set the affinity to apply to pRight to 
  3170   3170             ** SQLITE_AFF_NONE.  */
................................................................................
  3291   3291       if( pWInfo->nLevel>1 ){
  3292   3292         int nNotReady;                 /* The number of notReady tables */
  3293   3293         struct SrcList_item *origSrc;     /* Original list of tables */
  3294   3294         nNotReady = pWInfo->nLevel - iLevel - 1;
  3295   3295         pOrTab = sqlite3StackAllocRaw(pParse->db,
  3296   3296                               sizeof(*pOrTab)+ nNotReady*sizeof(pOrTab->a[0]));
  3297   3297         if( pOrTab==0 ) return notReady;
  3298         -      pOrTab->nSrc = pOrTab->nAlloc = nNotReady + 1;
         3298  +      pOrTab->nAlloc = (i16)(nNotReady + 1);
         3299  +      pOrTab->nSrc = pOrTab->nAlloc;
  3299   3300         memcpy(pOrTab->a, pTabItem, sizeof(*pTabItem));
  3300   3301         origSrc = pWInfo->pTabList->a;
  3301   3302         for(k=1; k<=nNotReady; k++){
  3302   3303           memcpy(&pOrTab->a[k], &origSrc[pLevel[k].iFrom], sizeof(pOrTab->a[k]));
  3303   3304         }
  3304   3305       }else{
  3305   3306         pOrTab = pWInfo->pTabList;
................................................................................
  3332   3333                           WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE |
  3333   3334                           WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
  3334   3335           if( pSubWInfo ){
  3335   3336             if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  3336   3337               int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
  3337   3338               int r;
  3338   3339               r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
  3339         -                                         regRowid, 0);
         3340  +                                         regRowid);
  3340   3341               sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset,
  3341   3342                                    sqlite3VdbeCurrentAddr(v)+2, r, iSet);
  3342   3343             }
  3343   3344             sqlite3VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody);
  3344   3345   
  3345   3346             /* The pSubWInfo->untestedTerms flag means that this OR term
  3346   3347             ** contained one or more AND term from a notReady table.  The

Changes to test/check.test.

   235    235   } {1 10}
   236    236   do_test check-4.3 {
   237    237     execsql {
   238    238       UPDATE t4 SET x=4, y=3;
   239    239       SELECT * FROM t4
   240    240     }
   241    241   } {4 3}
   242         -do_test check-4.3 {
          242  +do_test check-4.4 {
   243    243     execsql {
   244    244       UPDATE t4 SET x=12, y=2;
   245    245       SELECT * FROM t4
   246    246     }
   247    247   } {12 2}
   248         -do_test check-4.4 {
          248  +do_test check-4.5 {
   249    249     execsql {
   250    250       UPDATE t4 SET x=12, y=-22;
   251    251       SELECT * FROM t4
   252    252     }
   253    253   } {12 -22}
   254         -do_test check-4.5 {
          254  +do_test check-4.6 {
   255    255     catchsql {
   256    256       UPDATE t4 SET x=0, y=1;
   257    257     }
   258    258   } {1 {constraint failed}}
   259         -do_test check-4.6 {
          259  +do_test check-4.7 {
   260    260     execsql {
   261    261       SELECT * FROM t4;
   262    262     }
   263    263   } {12 -22}
   264         -do_test check-4.7 {
          264  +do_test check-4.8 {
   265    265     execsql {
   266    266       PRAGMA ignore_check_constraints=ON;
   267    267       UPDATE t4 SET x=0, y=1;
   268    268       SELECT * FROM t4;
   269    269     }
   270    270   } {0 1}
   271         -do_test check-4.8 {
          271  +do_test check-4.9 {
   272    272     catchsql {
   273    273       PRAGMA ignore_check_constraints=OFF;
   274    274       UPDATE t4 SET x=0, y=2;
   275    275     }
   276    276   } {1 {constraint failed}}
   277    277   ifcapable vacuum {
   278         -  do_test check_4.9 {
          278  +  do_test check_4.10 {
   279    279       catchsql {
   280    280         VACUUM
   281    281       }
   282    282     } {0 {}}
   283    283   }
   284    284   
   285    285   do_test check-5.1 {

Changes to test/e_fkey.test.

    95     95   #-------------------------------------------------------------------------
    96     96   # Test the effects of defining OMIT_FOREIGN_KEY.
    97     97   #
    98     98   # EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then
    99     99   # foreign key definitions cannot even be parsed (attempting to specify a
   100    100   # foreign key definition is a syntax error).
   101    101   #
   102         -# /* EV: R-58428-36660 */
   103         -#
   104    102   # Specifically, test that foreign key constraints cannot even be parsed 
   105    103   # in such a build.
   106    104   #
   107    105   reset_db
   108    106   ifcapable !foreignkey {
   109    107     do_test e_fkey-3.1 {
   110    108       execsql { CREATE TABLE p(i PRIMARY KEY) }
................................................................................
   325    323     execsql { 
   326    324       UPDATE track SET trackartist = NULL WHERE trackid = 1;
   327    325       DELETE FROM artist WHERE artistid = 5;
   328    326     }
   329    327   } {}
   330    328   
   331    329   #-------------------------------------------------------------------------
   332         -# /* EV: R-52486-21352 */
   333         -#
   334    330   # Test that the following is true fo all rows in the track table:
   335    331   #
   336    332   #   trackartist IS NULL OR 
   337    333   #   EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
   338    334   #
   339    335   # EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every
   340    336   # row in the track table, the following expression evaluates to true:
................................................................................
   384    380     set t   [expr int(rand()*50)]
   385    381     set sql [subst [lindex $Template [expr int(rand()*6)]]]
   386    382   
   387    383     test_r52486_21352 $i $sql
   388    384   }
   389    385   
   390    386   #-------------------------------------------------------------------------
   391         -# /* EV: R-42412-59321 */
   392         -#
   393    387   # Check that a NOT NULL constraint can be added to the example schema
   394    388   # to prohibit NULL child keys from being inserted.
   395    389   #
   396    390   # EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter
   397    391   # relationship between artist and track, where NULL values are not
   398    392   # permitted in the trackartist column, simply add the appropriate "NOT
   399    393   # NULL" constraint to the schema.
................................................................................
   414    408     }
   415    409   } {}
   416    410   do_test e_fkey-12.2 {
   417    411     catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
   418    412   } {1 {track.trackartist may not be NULL}}
   419    413   
   420    414   #-------------------------------------------------------------------------
   421         -# EVIDENCE-OF: R-17902-59250
          415  +# EVIDENCE-OF: R-16127-35442
   422    416   #
   423    417   # Test an example from foreignkeys.html.
   424    418   #
   425    419   drop_all_tables
   426    420   do_test e_fkey-13.1 {
   427    421     execsql {
   428    422       CREATE TABLE artist(
................................................................................
   458    452       INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
   459    453       UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
   460    454       INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
   461    455     }
   462    456   } {}
   463    457   
   464    458   #-------------------------------------------------------------------------
   465         -# EVIDENCE-OF: R-15034-64331
          459  +# EVIDENCE-OF: R-15958-50233
   466    460   #
   467    461   # Test the second example from the first section of foreignkeys.html.
   468    462   #
   469    463   do_test e_fkey-14.1 {
   470    464     catchsql {
   471    465       DELETE FROM artist WHERE artistname = 'Frank Sinatra';
   472    466     }
................................................................................
   878    872       do_test e_fkey-22.$fk.[incr i] {
   879    873         catchsql $sql
   880    874       } [list 1 $error]
   881    875     }
   882    876   }
   883    877   
   884    878   #-------------------------------------------------------------------------
   885         -# /* EV: R-47109-40581 */
   886         -#
   887    879   # Test that a REFERENCING clause that does not specify parent key columns
   888    880   # implicitly maps to the primary key of the parent table.
          881  +#
          882  +# EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES <parent-table>"
          883  +# clause to a column definition creates a foreign
          884  +# key constraint that maps the column to the primary key of
          885  +# <parent-table>.
   889    886   # 
   890    887   do_test e_fkey-23.1 {
   891    888     execsql {
   892    889       CREATE TABLE p1(a, b, PRIMARY KEY(a, b));
   893    890       CREATE TABLE p2(a, b PRIMARY KEY);
   894    891       CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1);
   895    892       CREATE TABLE c2(a, b REFERENCES p2);
................................................................................
   911    908   #-------------------------------------------------------------------------
   912    909   # Test that an index on on the child key columns of an FK constraint
   913    910   # is optional.
   914    911   #
   915    912   # EVIDENCE-OF: R-15417-28014 Indices are not required for child key
   916    913   # columns
   917    914   #
   918         -# /* EV: R-15741-50893 */
   919         -#
   920    915   # Also test that if an index is created on the child key columns, it does
   921    916   # not make a difference whether or not it is a UNIQUE index.
          917  +#
          918  +# EVIDENCE-OF: R-15741-50893 The child key index does not have to be
          919  +# (and usually will not be) a UNIQUE index.
   922    920   #
   923    921   drop_all_tables
   924    922   do_test e_fkey-24.1 {
   925    923     execsql {
   926    924       CREATE TABLE parent(x, y, UNIQUE(y, x));
   927    925       CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
   928    926       CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
................................................................................
  1008   1006   do_test e_fkey-25.7 {
  1009   1007     concat \
  1010   1008       [execsql { SELECT rowid FROM track WHERE trackartist = 6 }]   \
  1011   1009       [catchsql { DELETE FROM artist WHERE artistid = 6 }]
  1012   1010   } {2 1 {foreign key constraint failed}}
  1013   1011   
  1014   1012   #-------------------------------------------------------------------------
  1015         -# EVIDENCE-OF: R-54172-55848
         1013  +# EVIDENCE-OF: R-47936-10044 Or, more generally:
         1014  +# SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
  1016   1015   #
  1017   1016   # Test that when a row is deleted from the parent table of an FK 
  1018   1017   # constraint, the child table is queried for orphaned rows. The
  1019   1018   # query is equivalent to:
  1020   1019   #
  1021   1020   #   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
  1022   1021   #
  1023         -# /* EV: R-61616-46700 */
  1024         -#
  1025   1022   # Also test that when a row is inserted into the parent table, or when the 
  1026   1023   # parent key values of an existing row are modified, a query equivalent
  1027   1024   # to the following is planned. In some cases it is not executed, but it
  1028   1025   # is always planned.
  1029   1026   #
  1030   1027   #   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
  1031   1028   #
................................................................................
  1068   1065     do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete
  1069   1066     do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update
  1070   1067   
  1071   1068     execsql {DROP TABLE child}
  1072   1069   }
  1073   1070   
  1074   1071   #-------------------------------------------------------------------------
  1075         -# /* EV: R-14553-34013 */
         1072  +# EVIDENCE-OF: R-14553-34013
  1076   1073   #
  1077   1074   # Test the example schema at the end of section 3. Also test that is
  1078   1075   # is "efficient". In this case "efficient" means that foreign key
  1079   1076   # related operations on the parent table do not provoke linear scans.
  1080   1077   #
  1081   1078   drop_all_tables
  1082   1079   do_test e_fkey-27.1 {
................................................................................
  1112   1109   
  1113   1110   
  1114   1111   ###########################################################################
  1115   1112   ### SECTION 4.1: Composite Foreign Key Constraints
  1116   1113   ###########################################################################
  1117   1114   
  1118   1115   #-------------------------------------------------------------------------
  1119         -# /* EV: R-41062-34431 */
  1120         -#
  1121   1116   # Check that parent and child keys must have the same number of columns.
         1117  +#
         1118  +# EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same
         1119  +# cardinality.
  1122   1120   #
  1123   1121   foreach {tn sql err} {
  1124   1122     1 "CREATE TABLE c(jj REFERENCES p(x, y))" 
  1125   1123       {foreign key on jj should reference only one column of table p}
  1126   1124   
  1127   1125     2 "CREATE TABLE c(jj REFERENCES p())" {near ")": syntax error}
  1128   1126   
................................................................................
  1159   1157       CREATE TABLE c(a REFERENCES p);
  1160   1158     }
  1161   1159     catchsql {DELETE FROM p}
  1162   1160   } {1 {foreign key mismatch}}
  1163   1161   
  1164   1162   
  1165   1163   #-------------------------------------------------------------------------
  1166         -# /* EV: R-24676-09859 */
         1164  +# EVIDENCE-OF: R-24676-09859
  1167   1165   #
  1168   1166   # Test the example schema in the "Composite Foreign Key Constraints" 
  1169   1167   # section.
  1170   1168   #
  1171   1169   do_test e_fkey-29.1 {
  1172   1170     execsql {
  1173   1171       CREATE TABLE album(
................................................................................
  1198   1196     catchsql {
  1199   1197       INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever');
  1200   1198     }
  1201   1199   } {1 {foreign key constraint failed}}
  1202   1200   
  1203   1201   
  1204   1202   #-------------------------------------------------------------------------
  1205         -# /* EV: R-33626-48418 */
  1206         -#
  1207         -# Check that if any of the child key columns in the above schema are NULL,
  1208         -# there is no requirement for a corresponding parent key.
         1203  +# EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns
         1204  +# (in this case songartist and songalbum) are NULL, then there is no
         1205  +# requirement for a corresponding row in the parent table.
  1209   1206   #
  1210   1207   do_test e_fkey-30.1 {
  1211   1208     execsql {
  1212   1209       INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever');
  1213   1210       INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy');
  1214   1211     }
  1215   1212   } {}
  1216   1213   
  1217   1214   ###########################################################################
  1218   1215   ### SECTION 4.2: Deferred Foreign Key Constraints
  1219   1216   ###########################################################################
  1220   1217   
  1221   1218   #-------------------------------------------------------------------------
  1222         -# Note: R-35290-16460 is tested below.
  1223         -#
  1224         -# TODO: R-30323-21917
  1225         -
  1226         -#-------------------------------------------------------------------------
  1227         -# /* EV: R-09323-30470 */
  1228         -#
  1229   1219   # Test that if a statement violates an immediate FK constraint, and the
  1230   1220   # database does not satisfy the FK constraint once all effects of the
  1231   1221   # statement have been applied, an error is reported and the effects of
  1232   1222   # the statement rolled back.
         1223  +#
         1224  +# EVIDENCE-OF: R-09323-30470 If a statement modifies the contents of the
         1225  +# database so that an immediate foreign key constraint is in violation
         1226  +# at the conclusion the statement, an exception is thrown and the
         1227  +# effects of the statement are reverted.
  1233   1228   #
  1234   1229   drop_all_tables
  1235   1230   do_test e_fkey-31.1 {
  1236   1231     execsql {
  1237   1232       CREATE TABLE king(a, b, PRIMARY KEY(a));
  1238   1233       CREATE TABLE prince(c REFERENCES king, d);
  1239   1234     }
................................................................................
  1272   1267     execsql {
  1273   1268       COMMIT;
  1274   1269       SELECT * FROM king;
  1275   1270     }
  1276   1271   } {1 {} 2 {}}
  1277   1272   
  1278   1273   #-------------------------------------------------------------------------
  1279         -# /* EV: R-49178-21358 */
  1280         -# /* EV: R-39692-12488 */
  1281         -# /* EV: R-55147-47664 */
  1282         -# /* EV: R-29604-30395 */
  1283         -#
  1284   1274   # Test that if a deferred constraint is violated within a transaction,
  1285   1275   # nothing happens immediately and the database is allowed to persist
  1286   1276   # in a state that does not satisfy the FK constraint. However attempts
  1287   1277   # to COMMIT the transaction fail until the FK constraint is satisfied.
         1278  +#
         1279  +# EVIDENCE-OF: R-49178-21358 By contrast, if a statement modifies the
         1280  +# contents of the database such that a deferred foreign key constraint
         1281  +# is violated, the violation is not reported immediately.
         1282  +#
         1283  +# EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not
         1284  +# checked until the transaction tries to COMMIT.
         1285  +#
         1286  +# EVIDENCE-OF: R-55147-47664 For as long as the user has an open
         1287  +# transaction, the database is allowed to exist in a state that violates
         1288  +# any number of deferred foreign key constraints.
         1289  +#
         1290  +# EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as
         1291  +# foreign key constraints remain in violation.
  1288   1292   #
  1289   1293   proc test_efkey_34 {tn isError sql} {
  1290   1294     do_test e_fkey-32.$tn "
  1291   1295       catchsql {$sql}
  1292   1296     " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
  1293   1297   }
  1294   1298   drop_all_tables
................................................................................
  1303   1307   test_efkey_34  5 1 "COMMIT"
  1304   1308   test_efkey_34  6 0   "INSERT INTO ll VALUES(10)"
  1305   1309   test_efkey_34  7 1 "COMMIT"
  1306   1310   test_efkey_34  8 0   "INSERT INTO ll VALUES(5)"
  1307   1311   test_efkey_34  9 0 "COMMIT"
  1308   1312   
  1309   1313   #-------------------------------------------------------------------------
  1310         -# /* EV: R-56844-61705 */
  1311         -#
  1312   1314   # When not running inside a transaction, a deferred constraint is similar
  1313   1315   # to an immediate constraint (violations are reported immediately).
         1316  +#
         1317  +# EVIDENCE-OF: R-56844-61705 If the current statement is not inside an
         1318  +# explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit
         1319  +# transaction is committed as soon as the statement has finished
         1320  +# executing. In this case deferred constraints behave the same as
         1321  +# immediate constraints.
  1314   1322   #
  1315   1323   drop_all_tables
  1316   1324   proc test_efkey_35 {tn isError sql} {
  1317   1325     do_test e_fkey-33.$tn "
  1318   1326       catchsql {$sql}
  1319   1327     " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
  1320   1328   }
................................................................................
  1329   1337   } {}
  1330   1338   test_efkey_35 2 1 "INSERT INTO child  VALUES('x', 'y')"
  1331   1339   test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')"
  1332   1340   test_efkey_35 4 0 "INSERT INTO child  VALUES('x', 'y')"
  1333   1341   
  1334   1342   
  1335   1343   #-------------------------------------------------------------------------
  1336         -# /* EV: R-12782-61841 */
         1344  +# EVIDENCE-OF: R-12782-61841
  1337   1345   #
  1338   1346   # Test that an FK constraint is made deferred by adding the following
  1339   1347   # to the definition:
  1340   1348   #
  1341   1349   #   DEFERRABLE INITIALLY DEFERRED
  1342   1350   #
  1343         -# /* EV: R-09005-28791 */
         1351  +# EVIDENCE-OF: R-09005-28791
  1344   1352   #
  1345   1353   # Also test that adding any of the following to a foreign key definition 
  1346   1354   # makes the constraint IMMEDIATE:
  1347   1355   #
  1348   1356   #   NOT DEFERRABLE INITIALLY DEFERRED
  1349   1357   #   NOT DEFERRABLE INITIALLY IMMEDIATE
  1350   1358   #   NOT DEFERRABLE
  1351   1359   #   DEFERRABLE INITIALLY IMMEDIATE
  1352   1360   #   DEFERRABLE
  1353   1361   #
  1354         -# /* EV: R-35290-16460 */
  1355         -#
  1356   1362   # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT
  1357   1363   # DEFERRABLE clause).
  1358   1364   #
  1359         -# /* EV: R-30323-21917 */  FKs are either IMMEDIATE or DEFERRED.
         1365  +# EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by
         1366  +# default.
         1367  +#
         1368  +# EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is
         1369  +# classified as either immediate or deferred.
  1360   1370   #
  1361   1371   drop_all_tables
  1362   1372   do_test e_fkey-34.1 {
  1363   1373     execsql {
  1364   1374       CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z));
  1365   1375       CREATE TABLE c1(a, b, c,
  1366   1376         FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED
................................................................................
  1449   1459   test_efkey_29 31 "UPDATE c5 SET a = 10"                    1
  1450   1460   test_efkey_29 31 "UPDATE c6 SET a = 10"                    1
  1451   1461   test_efkey_29 31 "UPDATE c7 SET a = 10"                    0
  1452   1462   test_efkey_29 32 "COMMIT"                                  1
  1453   1463   test_efkey_29 33 "ROLLBACK"                                0
  1454   1464   
  1455   1465   #-------------------------------------------------------------------------
  1456         -# /* EV: R-35043-01546 */
         1466  +# EVIDENCE-OF: R-24499-57071
  1457   1467   #
  1458   1468   # Test an example from foreignkeys.html dealing with a deferred foreign 
  1459   1469   # key constraint.
  1460   1470   #
  1461   1471   do_test e_fkey-35.1 {
  1462   1472     drop_all_tables
  1463   1473     execsql {
................................................................................
  1483   1493     execsql {
  1484   1494       INSERT INTO artist VALUES(5, 'Bing Crosby');
  1485   1495       COMMIT;
  1486   1496     }
  1487   1497   } {}
  1488   1498   
  1489   1499   #-------------------------------------------------------------------------
  1490         -# /* EV: R-07223-48323 */
  1491         -#
  1492   1500   # Verify that a nested savepoint may be released without satisfying 
  1493   1501   # deferred foreign key constraints.
         1502  +#
         1503  +# EVIDENCE-OF: R-07223-48323 A nested savepoint transaction may be
         1504  +# RELEASEd while the database is in a state that does not satisfy a
         1505  +# deferred foreign key constraint.
  1494   1506   #
  1495   1507   drop_all_tables
  1496   1508   do_test e_fkey-36.1 {
  1497   1509     execsql {
  1498   1510       CREATE TABLE t1(a PRIMARY KEY,
  1499   1511         b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED
  1500   1512       );
................................................................................
  1519   1531       UPDATE t1 SET a = 5 WHERE a = 4;
  1520   1532       COMMIT;
  1521   1533     }
  1522   1534   } {}
  1523   1535   
  1524   1536   
  1525   1537   #-------------------------------------------------------------------------
  1526         -# /* EV: R-44295-13823 */
  1527         -#
  1528   1538   # Check that a transaction savepoint (an outermost savepoint opened when
  1529   1539   # the database was in auto-commit mode) cannot be released without
  1530   1540   # satisfying deferred foreign key constraints. It may be rolled back.
         1541  +#
         1542  +# EVIDENCE-OF: R-44295-13823 A transaction savepoint (a non-nested
         1543  +# savepoint that was opened while there was not currently an open
         1544  +# transaction), on the other hand, is subject to the same restrictions
         1545  +# as a COMMIT - attempting to RELEASE it while the database is in such a
         1546  +# state will fail.
  1531   1547   #
  1532   1548   do_test e_fkey-37.1 {
  1533   1549     execsql {
  1534   1550       SAVEPOINT one;
  1535   1551         SAVEPOINT two;
  1536   1552           INSERT INTO t1 VALUES(6, 7);
  1537   1553         RELEASE two;
................................................................................
  1558   1574     catchsql {RELEASE one}
  1559   1575   } {1 {foreign key constraint failed}}
  1560   1576   do_test e_fkey-37.6 {
  1561   1577     execsql {ROLLBACK TO one ; RELEASE one}
  1562   1578   } {}
  1563   1579   
  1564   1580   #-------------------------------------------------------------------------
  1565         -# /* EV: R-37736-42616 */
  1566         -#
  1567   1581   # Test that if a COMMIT operation fails due to deferred foreign key 
  1568   1582   # constraints, any nested savepoints remain open.
         1583  +#
         1584  +# EVIDENCE-OF: R-37736-42616 If a COMMIT statement (or the RELEASE of a
         1585  +# transaction SAVEPOINT) fails because the database is currently in a
         1586  +# state that violates a deferred foreign key constraint and there are
         1587  +# currently nested savepoints, the nested savepoints remain open.
  1569   1588   #
  1570   1589   do_test e_fkey-38.1 {
  1571   1590     execsql {
  1572   1591       DELETE FROM t1 WHERE a>3;
  1573   1592       SELECT * FROM t1;
  1574   1593     }
  1575   1594   } {1 1 2 2 3 3}
................................................................................
  1619   1638   } {1 1 2 2 3 3 4 4 5 5}
  1620   1639   
  1621   1640   ###########################################################################
  1622   1641   ### SECTION 4.3: ON DELETE and ON UPDATE Actions
  1623   1642   ###########################################################################
  1624   1643   
  1625   1644   #-------------------------------------------------------------------------
  1626         -# /* EV: R-48270-44282 */
  1627         -#
  1628   1645   # Test that configured ON DELETE and ON UPDATE actions take place when
  1629   1646   # deleting or modifying rows of the parent table, respectively.
  1630   1647   #
  1631         -# /* EV: R-48124-63225 */
         1648  +# EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses
         1649  +# are used to configure actions that take place when deleting rows from
         1650  +# the parent table (ON DELETE), or modifying the parent key values of
         1651  +# existing rows (ON UPDATE).
  1632   1652   #
  1633   1653   # Test that a single FK constraint may have different actions configured
  1634   1654   # for ON DELETE and ON UPDATE.
         1655  +#
         1656  +# EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have
         1657  +# different actions configured for ON DELETE and ON UPDATE.
  1635   1658   #
  1636   1659   do_test e_fkey-39.1 {
  1637   1660     execsql {
  1638   1661       CREATE TABLE p(a, b PRIMARY KEY, c);
  1639   1662       CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p 
  1640   1663         ON UPDATE SET DEFAULT
  1641   1664         ON DELETE SET NULL
................................................................................
  1668   1691       CREATE UNIQUE INDEX pi ON p(c);
  1669   1692       REPLACE INTO p VALUES(5, 'k5', 'III');
  1670   1693       SELECT * FROM c1;
  1671   1694     }
  1672   1695   } {1 xx k0 2 xx {} 3 xx {}}
  1673   1696   
  1674   1697   #-------------------------------------------------------------------------
  1675         -# /* EV: R-33326-45252 */
  1676         -#
  1677   1698   # Each foreign key in the system has an ON UPDATE and ON DELETE action,
  1678   1699   # either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
  1679   1700   #
  1680         -# /* EV: R-19803-45884 */
         1701  +# EVIDENCE-OF: R-33326-45252 The ON DELETE and ON UPDATE action
         1702  +# associated with each foreign key in an SQLite database is one of "NO
         1703  +# ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
  1681   1704   #
  1682   1705   # If none is specified explicitly, "NO ACTION" is the default.
         1706  +#
         1707  +# EVIDENCE-OF: R-19803-45884 If an action is not explicitly specified,
         1708  +# it defaults to "NO ACTION".
  1683   1709   # 
  1684   1710   drop_all_tables
  1685   1711   do_test e_fkey-40.1 {
  1686   1712     execsql {
  1687   1713       CREATE TABLE parent(x PRIMARY KEY, y);
  1688   1714       CREATE TABLE child1(a, 
  1689   1715         b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT
................................................................................
  1716   1742     8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
  1717   1743     9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
  1718   1744   } {
  1719   1745     do_test e_fkey-40.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes
  1720   1746   }
  1721   1747   
  1722   1748   #-------------------------------------------------------------------------
  1723         -# /* EV: R-19971-54976 */
  1724         -#
  1725   1749   # Test that "NO ACTION" means that nothing happens to a child row when
  1726   1750   # it's parent row is updated or deleted.
         1751  +#
         1752  +# EVIDENCE-OF: R-19971-54976 Configuring "NO ACTION" means just that:
         1753  +# when a parent key is modified or deleted from the database, no special
         1754  +# action is taken.
  1727   1755   #
  1728   1756   drop_all_tables
  1729   1757   do_test e_fkey-41.1 {
  1730   1758     execsql {
  1731   1759       CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2));
  1732   1760       CREATE TABLE child(c1, c2, 
  1733   1761         FOREIGN KEY(c1, c2) REFERENCES parent
................................................................................
  1753   1781     catchsql COMMIT
  1754   1782   } {1 {foreign key constraint failed}}
  1755   1783   do_test e_fkey-41.4 {
  1756   1784     execsql ROLLBACK
  1757   1785   } {}
  1758   1786   
  1759   1787   #-------------------------------------------------------------------------
  1760         -# /* EV: R-04272-38653 */
  1761         -#
  1762   1788   # Test that "RESTRICT" means the application is prohibited from deleting
  1763   1789   # or updating a parent table row when there exists one or more child keys
  1764   1790   # mapped to it.
         1791  +#
         1792  +# EVIDENCE-OF: R-04272-38653 The "RESTRICT" action means that the
         1793  +# application is prohibited from deleting (for ON DELETE RESTRICT) or
         1794  +# modifying (for ON UPDATE RESTRICT) a parent key when there exists one
         1795  +# or more child keys mapped to it.
  1765   1796   #
  1766   1797   drop_all_tables
  1767   1798   do_test e_fkey-41.1 {
  1768   1799     execsql {
  1769   1800       CREATE TABLE parent(p1, p2);
  1770   1801       CREATE UNIQUE INDEX parent_i ON parent(p1, p2);
  1771   1802       CREATE TABLE child1(c1, c2, 
................................................................................
  1788   1819     catchsql { DELETE FROM parent WHERE p1 = 'a' }
  1789   1820   } {1 {foreign key constraint failed}}
  1790   1821   do_test e_fkey-41.4 {
  1791   1822     catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' }
  1792   1823   } {1 {foreign key constraint failed}}
  1793   1824   
  1794   1825   #-------------------------------------------------------------------------
  1795         -# /* EV: R-37997-42187 */
  1796         -# 
  1797   1826   # Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE
  1798   1827   # constraints, in that it is enforced immediately, not at the end of the 
  1799   1828   # statement.
         1829  +#
         1830  +# EVIDENCE-OF: R-37997-42187 The difference between the effect of a
         1831  +# RESTRICT action and normal foreign key constraint enforcement is that
         1832  +# the RESTRICT action processing happens as soon as the field is updated
         1833  +# - not at the end of the current statement as it would with an
         1834  +# immediate constraint, or at the end of the current transaction as it
         1835  +# would with a deferred constraint.
  1800   1836   #
  1801   1837   drop_all_tables
  1802   1838   do_test e_fkey-42.1 {
  1803   1839     execsql {
  1804   1840       CREATE TABLE parent(x PRIMARY KEY);
  1805   1841       CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT);
  1806   1842       CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION);
................................................................................
  1874   1910     execsql { 
  1875   1911       REPLACE INTO parent VALUES('key2');
  1876   1912       SELECT * FROM child2;
  1877   1913     }
  1878   1914   } {key2}
  1879   1915   
  1880   1916   #-------------------------------------------------------------------------
  1881         -# /* EV: R-24179-60523 */
  1882         -# 
  1883   1917   # Test that RESTRICT is enforced immediately, even for a DEFERRED constraint.
         1918  +#
         1919  +# EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is
         1920  +# attached to is deferred, configuring a RESTRICT action causes SQLite
         1921  +# to return an error immediately if a parent key with dependent child
         1922  +# keys is deleted or modified.
  1884   1923   #
  1885   1924   drop_all_tables
  1886   1925   do_test e_fkey-43.1 {
  1887   1926     execsql {
  1888   1927       CREATE TABLE parent(x PRIMARY KEY);
  1889   1928       CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT
  1890   1929         DEFERRABLE INITIALLY DEFERRED
................................................................................
  1947   1986     execsql {
  1948   1987       UPDATE child2 SET c = NULL;
  1949   1988       COMMIT;
  1950   1989     }
  1951   1990   } {}
  1952   1991   
  1953   1992   #-------------------------------------------------------------------------
  1954         -# /* EV: R-03353-05327 */
  1955         -#
  1956   1993   # Test SET NULL actions.
         1994  +#
         1995  +# EVIDENCE-OF: R-03353-05327 If the configured action is "SET NULL",
         1996  +# then when a parent key is deleted (for ON DELETE SET NULL) or modified
         1997  +# (for ON UPDATE SET NULL), the child key columns of all rows in the
         1998  +# child table that mapped to the parent key are set to contain SQL NULL
         1999  +# values.
  1957   2000   #
  1958   2001   drop_all_tables
  1959   2002   do_test e_fkey-44.1 {
  1960   2003     execsql {
  1961   2004       CREATE TABLE pA(x PRIMARY KEY);
  1962   2005       CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL);
  1963   2006       CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL);
................................................................................
  1986   2029     }
  1987   2030   } {X'8765'}
  1988   2031   do_test e_fkey-44.5 {
  1989   2032     execsql { SELECT quote(c) FROM cB }
  1990   2033   } {NULL}
  1991   2034   
  1992   2035   #-------------------------------------------------------------------------
  1993         -# /* EV: R-43054-54832 */
  1994         -#
  1995   2036   # Test SET DEFAULT actions.
         2037  +#
         2038  +# EVIDENCE-OF: R-43054-54832 The "SET DEFAULT" actions are similar to
         2039  +# "SET NULL", except that each of the child key columns is set to
         2040  +# contain the columns default value instead of NULL.
  1996   2041   #
  1997   2042   drop_all_tables
  1998   2043   do_test e_fkey-45.1 {
  1999   2044     execsql {
  2000   2045       CREATE TABLE pA(x PRIMARY KEY);
  2001   2046       CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT);
  2002   2047       CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT);
................................................................................
  2026   2071     }
  2027   2072   } {X'0000' X'9999' X'8765'}
  2028   2073   do_test e_fkey-45.5 {
  2029   2074     execsql { SELECT quote(c) FROM cB }
  2030   2075   } {X'9999'}
  2031   2076   
  2032   2077   #-------------------------------------------------------------------------
  2033         -# /* EV: R-61376-57267 */
  2034         -# /* EV: R-61809-62207 */
  2035         -#
  2036   2078   # Test ON DELETE CASCADE actions.
         2079  +#
         2080  +# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
         2081  +# update operation on the parent key to each dependent child key.
         2082  +#
         2083  +# EVIDENCE-OF: R-61809-62207 For an "ON DELETE CASCADE" action, this
         2084  +# means that each row in the child table that was associated with the
         2085  +# deleted parent row is also deleted.
  2037   2086   #
  2038   2087   drop_all_tables
  2039   2088   do_test e_fkey-46.1 {
  2040   2089     execsql {
  2041   2090       CREATE TABLE p1(a, b UNIQUE);
  2042   2091       CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d);
  2043   2092       INSERT INTO p1 VALUES(NULL, NULL);
................................................................................
  2063   2112   } {{} {}}
  2064   2113   do_test e_fkey-46.4 {
  2065   2114     execsql { SELECT * FROM p1 }
  2066   2115   } {}
  2067   2116   
  2068   2117   
  2069   2118   #-------------------------------------------------------------------------
  2070         -# /* EV: R-61376-57267 */
  2071         -# /* EV: R-13877-64542 */
  2072         -#
  2073   2119   # Test ON UPDATE CASCADE actions.
         2120  +#
         2121  +# EVIDENCE-OF: R-13877-64542 For an "ON UPDATE CASCADE" action, it means
         2122  +# that the values stored in each dependent child key are modified to
         2123  +# match the new parent key values.
         2124  +#
         2125  +# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
         2126  +# update operation on the parent key to each dependent child key.
  2074   2127   #
  2075   2128   drop_all_tables
  2076   2129   do_test e_fkey-47.1 {
  2077   2130     execsql {
  2078   2131       CREATE TABLE p1(a, b UNIQUE);
  2079   2132       CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d);
  2080   2133       INSERT INTO p1 VALUES(NULL, NULL);
................................................................................
  2105   2158     }
  2106   2159   } {{} {} 4 11 5 10}
  2107   2160   do_test e_fkey-46.5 {
  2108   2161     execsql { SELECT * FROM p1 }
  2109   2162   } {{} 6 4 11 5 10}
  2110   2163   
  2111   2164   #-------------------------------------------------------------------------
  2112         -# /* EV: R-51329-33438 */
         2165  +# EVIDENCE-OF: R-65058-57158
  2113   2166   #
  2114   2167   # Test an example from the "ON DELETE and ON UPDATE Actions" section 
  2115   2168   # of foreignkeys.html.
  2116   2169   #
  2117   2170   drop_all_tables
  2118   2171   do_test e_fkey-48.1 {
  2119   2172     execsql {
................................................................................
  2144   2197   } {2 {Frank Sinatra} 100 {Dean Martin}}
  2145   2198   do_test e_fkey-48.4 {
  2146   2199     execsql { SELECT * FROM track }
  2147   2200   } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2}
  2148   2201   
  2149   2202   
  2150   2203   #-------------------------------------------------------------------------
  2151         -# /* EV: R-53968-51642 */
  2152         -#
  2153   2204   # Verify that adding an FK action does not absolve the user of the 
  2154   2205   # requirement not to violate the foreign key constraint.
         2206  +#
         2207  +# EVIDENCE-OF: R-53968-51642 Configuring an ON UPDATE or ON DELETE
         2208  +# action does not mean that the foreign key constraint does not need to
         2209  +# be satisfied.
  2155   2210   #
  2156   2211   drop_all_tables
  2157   2212   do_test e_fkey-49.1 {
  2158   2213     execsql {
  2159   2214       CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a));
  2160   2215       CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c',
  2161   2216         FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT
................................................................................
  2182   2237   } {ONE two three}
  2183   2238   do_test e_fkey-49.4 {
  2184   2239     catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' }
  2185   2240   } {1 {foreign key constraint failed}}
  2186   2241   
  2187   2242   
  2188   2243   #-------------------------------------------------------------------------
  2189         -# /* EV: R-07065-59588 */
  2190         -# /* EV: R-28220-46694 */
         2244  +# EVIDENCE-OF: R-11856-19836
  2191   2245   #
  2192   2246   # Test an example from the "ON DELETE and ON UPDATE Actions" section 
  2193   2247   # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT"
  2194   2248   # clause does not abrogate the need to satisfy the foreign key constraint
  2195   2249   # (R-28220-46694).
         2250  +#
         2251  +# EVIDENCE-OF: R-28220-46694 For example, if an "ON DELETE SET DEFAULT"
         2252  +# action is configured, but there is no row in the parent table that
         2253  +# corresponds to the default values of the child key columns, deleting a
         2254  +# parent key while dependent child keys exist still causes a foreign key
         2255  +# violation.
  2196   2256   #
  2197   2257   drop_all_tables
  2198   2258   do_test e_fkey-50.1 {
  2199   2259     execsql {
  2200   2260       CREATE TABLE artist(
  2201   2261         artistid    INTEGER PRIMARY KEY, 
  2202   2262         artistname  TEXT
................................................................................
  2223   2283     execsql { SELECT * FROM artist }
  2224   2284   } {0 {Unknown Artist}}
  2225   2285   do_test e_fkey-50.5 {
  2226   2286     execsql { SELECT * FROM track }
  2227   2287   } {14 {Mr. Bojangles} 0}
  2228   2288   
  2229   2289   #-------------------------------------------------------------------------
  2230         -# /* EV: R-09564-22170 */
         2290  +# EVIDENCE-OF: R-09564-22170
  2231   2291   #
  2232   2292   # Check that the order of steps in an UPDATE or DELETE on a parent 
  2233   2293   # table is as follows:
  2234   2294   #
  2235   2295   #   1. Execute applicable BEFORE trigger programs,
  2236   2296   #   2. Check local (non foreign key) constraints,
  2237   2297   #   3. Update or delete the row in the parent table,
................................................................................
  2275   2335       UPDATE parent SET x = 22;
  2276   2336       SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
  2277   2337     }
  2278   2338   } {22 23 21 xxx 23}
  2279   2339   
  2280   2340   
  2281   2341   #-------------------------------------------------------------------------
  2282         -# /* EV: R-27383-10246 */
  2283         -#
  2284   2342   # Verify that ON UPDATE actions only actually take place if the parent key
  2285   2343   # is set to a new value that is distinct from the old value. The default
  2286   2344   # collation sequence and affinity are used to determine if the new value
  2287   2345   # is 'distinct' from the old or not.
         2346  +#
         2347  +# EVIDENCE-OF: R-27383-10246 An ON UPDATE action is only taken if the
         2348  +# values of the parent key are modified so that the new parent key
         2349  +# values are not equal to the old.
  2288   2350   #
  2289   2351   drop_all_tables
  2290   2352   do_test e_fkey-52.1 {
  2291   2353     execsql {
  2292   2354       CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b));
  2293   2355       CREATE TABLE apollo(c, d, 
  2294   2356         FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE
................................................................................
  2329   2391     execsql {
  2330   2392       UPDATE zeus SET b = NULL;
  2331   2393       SELECT typeof(c), c, typeof(d), d FROM apollo;
  2332   2394     }
  2333   2395   } {integer 1 null {}}
  2334   2396   
  2335   2397   #-------------------------------------------------------------------------
  2336         -# /* EV: R-58589-50781 */
         2398  +# EVIDENCE-OF: R-35129-58141
  2337   2399   #
  2338   2400   # Test an example from the "ON DELETE and ON UPDATE Actions" section 
  2339   2401   # of foreignkeys.html. This example demonstrates that ON UPDATE actions
  2340   2402   # only take place if at least one parent key column is set to a value 
  2341   2403   # that is distinct from its previous value.
  2342   2404   #
  2343   2405   drop_all_tables
................................................................................
  2363   2425   } {null}
  2364   2426   
  2365   2427   ###########################################################################
  2366   2428   ### SECTION 5: CREATE, ALTER and DROP TABLE commands
  2367   2429   ###########################################################################
  2368   2430   
  2369   2431   #-------------------------------------------------------------------------
  2370         -# /* EV: R-36018-21755 */
  2371         -# /* EV: R-25384-39337 */
  2372         -# 
  2373   2432   # Test that parent keys are not checked when tables are created.
         2433  +#
         2434  +# EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key
         2435  +# constraints are not checked when a table is created.
         2436  +#
         2437  +# EVIDENCE-OF: R-25384-39337 There is nothing stopping the user from
         2438  +# creating a foreign key definition that refers to a parent table that
         2439  +# does not exist, or to parent key columns that do not exist or are not
         2440  +# collectively bound by a PRIMARY KEY or UNIQUE constraint.
  2374   2441   #
  2375   2442   # Child keys are checked to ensure all component columns exist. If parent
  2376   2443   # key columns are explicitly specified, SQLite checks to make sure there
  2377   2444   # are the same number of columns in the child and parent keys. (TODO: This
  2378   2445   # is tested but does not correspond to any testable statement.)
  2379   2446   #
  2380         -# /* EV: R-08908-23439 */
  2381         -#
  2382   2447   # Also test that the above statements are true regardless of whether or not
  2383   2448   # foreign keys are enabled:  "A CREATE TABLE command operates the same whether
  2384   2449   # or not foreign key constraints are enabled."
         2450  +#
         2451  +# EVIDENCE-OF: R-08908-23439 A CREATE TABLE command operates the same
         2452  +# whether or not foreign key constraints are enabled.
  2385   2453   # 
  2386   2454   foreach {tn zCreateTbl lRes} {
  2387   2455     1 "CREATE TABLE t1(a, b REFERENCES t1)"                            {0 {}}
  2388   2456     2 "CREATE TABLE t1(a, b REFERENCES t2)"                            {0 {}}
  2389   2457     3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)"          {0 {}}
  2390   2458     4 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}
  2391   2459     5 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}
................................................................................
  2406   2474       drop_all_tables
  2407   2475       execsql {PRAGMA foreign_keys = ON}
  2408   2476       catchsql $zCreateTbl
  2409   2477     } $lRes
  2410   2478   }
  2411   2479   
  2412   2480   #-------------------------------------------------------------------------
  2413         -# /* EV: R-47952-62498 */
         2481  +# EVIDENCE-OF: R-47952-62498 It is not possible to use the "ALTER TABLE
         2482  +# ... ADD COLUMN" syntax to add a column that includes a REFERENCES
         2483  +# clause, unless the default value of the new column is NULL. Attempting
         2484  +# to do so returns an error.
  2414   2485   #
  2415   2486   proc test_efkey_6 {tn zAlter isError} {
  2416   2487     drop_all_tables 
  2417   2488   
  2418   2489     do_test e_fkey-56.$tn.1 "
  2419   2490       execsql { CREATE TABLE tbl(a, b) }
  2420   2491       [list catchsql $zAlter]
................................................................................
  2423   2494   }
  2424   2495   
  2425   2496   test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0
  2426   2497   test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0
  2427   2498   test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1
  2428   2499   
  2429   2500   #-------------------------------------------------------------------------
  2430         -# /* EV: R-47080-02069 */
  2431         -#
  2432   2501   # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table
  2433   2502   # is RENAMED.
  2434   2503   #
  2435         -# /* EV: R-63827-54774 */
         2504  +# EVIDENCE-OF: R-47080-02069 If an "ALTER TABLE ... RENAME TO" command
         2505  +# is used to rename a table that is the parent table of one or more
         2506  +# foreign key constraints, the definitions of the foreign key
         2507  +# constraints are modified to refer to the parent table by its new name
  2436   2508   #
  2437   2509   # Test that these adjustments are visible in the sqlite_master table.
         2510  +#
         2511  +# EVIDENCE-OF: R-63827-54774 The text of the child CREATE TABLE
         2512  +# statement or statements stored in the sqlite_master table are modified
         2513  +# to reflect the new parent table name.
  2438   2514   #
  2439   2515   do_test e_fkey-56.1 {
  2440   2516     drop_all_tables
  2441   2517     execsql {
  2442   2518       CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b));
  2443   2519   
  2444   2520       CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
................................................................................
  2471   2547     {CREATE TABLE "p"(a REFERENCES "p", b, PRIMARY KEY(b))}                   \
  2472   2548     {CREATE TABLE c1(c, d REFERENCES "p" ON UPDATE CASCADE)}                  \
  2473   2549     {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)}  \
  2474   2550     {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \
  2475   2551   ]
  2476   2552   
  2477   2553   #-------------------------------------------------------------------------
  2478         -# /* EV: R-14208-23986 */
  2479         -# /* EV: R-11078-03945 */
  2480         -#
  2481   2554   # Check that a DROP TABLE does an implicit DELETE FROM. Which does not
  2482   2555   # cause any triggers to fire, but does fire foreign key actions.
         2556  +#
         2557  +# EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when
         2558  +# it is prepared, the DROP TABLE command performs an implicit DELETE to
         2559  +# remove all rows from the table before dropping it.
         2560  +#
         2561  +# EVIDENCE-OF: R-11078-03945 The implicit DELETE does not cause any SQL
         2562  +# triggers to fire, but may invoke foreign key actions or constraint
         2563  +# violations.
  2483   2564   #
  2484   2565   do_test e_fkey-57.1 {
  2485   2566     drop_all_tables
  2486   2567     execsql {
  2487   2568       CREATE TABLE p(a, b, PRIMARY KEY(a, b));
  2488   2569   
  2489   2570       CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET NULL);
................................................................................
  2537   2618         DELETE FROM p;
  2538   2619         SELECT * FROM log;
  2539   2620       ROLLBACK;
  2540   2621     }
  2541   2622   } {{delete 1}}
  2542   2623   
  2543   2624   #-------------------------------------------------------------------------
  2544         -# /* EV: R-32768-47925 */
  2545         -#
  2546   2625   # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the
  2547   2626   # DROP TABLE command fails.
  2548   2627   #
         2628  +# EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is
         2629  +# violated, the DROP TABLE statement fails and the table is not dropped.
         2630  +#
  2549   2631   do_test e_fkey-58.1 {
  2550   2632     execsql { 
  2551   2633       DELETE FROM c1;
  2552   2634       DELETE FROM c2;
  2553   2635       DELETE FROM c3;
  2554   2636     }
  2555   2637     execsql { INSERT INTO c5 VALUES('a', 'b') }
................................................................................
  2569   2651       SELECT * FROM p;
  2570   2652       SELECT * FROM c5;
  2571   2653       ROLLBACK;
  2572   2654     }
  2573   2655   } {a b a b}
  2574   2656   
  2575   2657   #-------------------------------------------------------------------------
  2576         -# /* EV: R-05903-08460 */
  2577         -#
  2578   2658   # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting
  2579   2659   # to commit the transaction fails unless the violation is fixed.
         2660  +#
         2661  +# EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is
         2662  +# violated, then an error is reported when the user attempts to commit
         2663  +# the transaction if the foreign key constraint violations still exist
         2664  +# at that point.
  2580   2665   #
  2581   2666   do_test e_fkey-59.1 {
  2582   2667     execsql { 
  2583   2668       DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ;
  2584   2669       DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ;
  2585   2670       DELETE FROM c7 
  2586   2671     }
................................................................................
  2601   2686   } {1 {foreign key constraint failed}}
  2602   2687   do_test e_fkey-59.5 {
  2603   2688     execsql { INSERT INTO p VALUES('a', 'b') }
  2604   2689     execsql COMMIT
  2605   2690   } {}
  2606   2691   
  2607   2692   #-------------------------------------------------------------------------
  2608         -# /* EV: R-57242-37005 */
  2609         -#
  2610   2693   # Any "foreign key mismatch" errors encountered while running an implicit
  2611   2694   # "DELETE FROM tbl" are ignored.
         2695  +#
         2696  +# EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors
         2697  +# encountered as part of an implicit DELETE are ignored.
  2612   2698   #
  2613   2699   drop_all_tables
  2614   2700   do_test e_fkey-60.1 {
  2615   2701     execsql {
  2616   2702       PRAGMA foreign_keys = OFF;
  2617   2703   
  2618   2704       CREATE TABLE p(a PRIMARY KEY, b REFERENCES nosuchtable);
................................................................................
  2648   2734   } {1 {foreign key mismatch}}
  2649   2735   do_test e_fkey-60.6 {
  2650   2736     execsql { DROP TABLE c2 }
  2651   2737     execsql { DELETE FROM p }
  2652   2738   } {}
  2653   2739   
  2654   2740   #-------------------------------------------------------------------------
  2655         -# /* EV: R-54142-41346 */
  2656         -#
  2657   2741   # Test that the special behaviours of ALTER and DROP TABLE are only
  2658   2742   # activated when foreign keys are enabled. Special behaviours are:
  2659   2743   #
  2660   2744   #   1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL 
  2661   2745   #      default value.
  2662   2746   #   2. Modifying foreign key definitions when a parent table is RENAMEd.
  2663   2747   #   3. Running an implicit DELETE FROM command as part of DROP TABLE.
  2664   2748   #
         2749  +# EVIDENCE-OF: R-54142-41346 The properties of the DROP TABLE and ALTER
         2750  +# TABLE commands described above only apply if foreign keys are enabled.
         2751  +#
  2665   2752   do_test e_fkey-61.1.1 {
  2666   2753     drop_all_tables
  2667   2754     execsql { CREATE TABLE t1(a, b) }
  2668   2755     catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 }
  2669   2756   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
  2670   2757   do_test e_fkey-61.1.2 {
  2671   2758     execsql { PRAGMA foreign_keys = OFF }
................................................................................
  2723   2810   } {}
  2724   2811   
  2725   2812   ###########################################################################
  2726   2813   ### SECTION 6: Limits and Unsupported Features
  2727   2814   ###########################################################################
  2728   2815   
  2729   2816   #-------------------------------------------------------------------------
  2730         -# /* EV: R-24728-13230 */
  2731         -# /* EV: R-24450-46174 */
  2732         -#
  2733   2817   # Test that MATCH clauses are parsed, but SQLite treats every foreign key
  2734   2818   # constraint as if it were "MATCH SIMPLE".
         2819  +#
         2820  +# EVIDENCE-OF: R-24728-13230 SQLite parses MATCH clauses (i.e. does not
         2821  +# report a syntax error if you specify one), but does not enforce them.
         2822  +#
         2823  +# EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are
         2824  +# handled as if MATCH SIMPLE were specified.
  2735   2825   #
  2736   2826   foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] {
  2737   2827     drop_all_tables
  2738   2828     do_test e_fkey-62.$zMatch.1 {
  2739   2829       execsql "
  2740   2830         CREATE TABLE p(a, b, c, PRIMARY KEY(b, c));
  2741   2831         CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch);
................................................................................
  2756   2846       # Check that the FK is enforced properly if there are no NULL values 
  2757   2847       # in the child key columns.
  2758   2848       catchsql { INSERT INTO c VALUES('a', 2, 4) }
  2759   2849     } {1 {foreign key constraint failed}}
  2760   2850   }
  2761   2851   
  2762   2852   #-------------------------------------------------------------------------
  2763         -# /* EV: R-21599-16038 */
  2764         -#
  2765   2853   # Test that SQLite does not support the SET CONSTRAINT statement. And
  2766   2854   # that it is possible to create both immediate and deferred constraints.
         2855  +#
         2856  +# EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is
         2857  +# permanently marked as deferred or immediate when it is created.
  2767   2858   #
  2768   2859   drop_all_tables
  2769   2860   do_test e_fkey-62.1 {
  2770   2861     catchsql { SET CONSTRAINTS ALL IMMEDIATE }
  2771   2862   } {1 {near "SET": syntax error}}
  2772   2863   do_test e_fkey-62.2 {
  2773   2864     catchsql { SET CONSTRAINTS ALL DEFERRED }
................................................................................
  2796   2887     execsql { 
  2797   2888       DELETE FROM cd;
  2798   2889       COMMIT;
  2799   2890     }
  2800   2891   } {}
  2801   2892   
  2802   2893   #-------------------------------------------------------------------------
  2803         -# /* EV: R-42264-30503 */
  2804         -#
  2805   2894   # Test that the maximum recursion depth of foreign key action programs is
  2806   2895   # governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH
  2807   2896   # settings.
         2897  +#
         2898  +# EVIDENCE-OF: R-42264-30503 The SQLITE_MAX_TRIGGER_DEPTH and
         2899  +# SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable
         2900  +# depth of trigger program recursion. For the purposes of these limits,
         2901  +# foreign key actions are considered trigger programs.
  2808   2902   #
  2809   2903   proc test_on_delete_recursion {limit} {
  2810   2904     drop_all_tables
  2811   2905     execsql { 
  2812   2906       BEGIN;
  2813   2907       CREATE TABLE t0(a PRIMARY KEY, b);
  2814   2908       INSERT INTO t0 VALUES('x0', NULL);
................................................................................
  2879   2973     test_on_update_recursion 6
  2880   2974   } {1 {too many levels of trigger recursion}}
  2881   2975   do_test e_fkey-63.2.5 {
  2882   2976     sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
  2883   2977   } {5}
  2884   2978   
  2885   2979   #-------------------------------------------------------------------------
  2886         -# /* EV: R-51769-32730 */
  2887         -#
  2888   2980   # The setting of the recursive_triggers pragma does not affect foreign
  2889   2981   # key actions.
         2982  +#
         2983  +# EVIDENCE-OF: R-51769-32730 The PRAGMA recursive_triggers setting does
         2984  +# not not affect the operation of foreign key actions.
  2890   2985   #
  2891   2986   foreach recursive_triggers_setting [list 0 1 ON OFF] {
  2892   2987     drop_all_tables
  2893   2988     execsql "PRAGMA recursive_triggers = $recursive_triggers_setting"
  2894   2989   
  2895   2990     do_test e_fkey-64.$recursive_triggers_setting.1 {
  2896   2991       execsql {

Added test/fts3query.test.

            1  +# 2009 December 20
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file contains tests of fts3 queries that have been useful during
           13  +# the development process as well as some that have been useful in tracking
           14  +# down bugs. They are not focused on any particular functionality.
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +# If this build does not include FTS3, skip the tests in this file.
           21  +#
           22  +ifcapable !fts3 { finish_test ; return }
           23  +source $testdir/fts3_common.tcl
           24  +set DO_MALLOC_TEST 0
           25  +
           26  +do_test fts3query-1.1 {
           27  +  execsql {
           28  +    CREATE VIRTUAL TABLE t1 USING fts3(x);
           29  +    BEGIN;
           30  +      INSERT INTO t1 VALUES('The source code for SQLite is in the public');
           31  +  }
           32  +} {}
           33  +
           34  +do_select_test fts3query-1.2 {
           35  +  SELECT * FROM t1;
           36  +} {{The source code for SQLite is in the public}}
           37  +do_select_test fts3query-1.3 {
           38  +  SELECT * FROM t1 WHERE t1 MATCH 'sqlite'
           39  +} {{The source code for SQLite is in the public}}
           40  +
           41  +do_test fts3query-1.4 { execsql {COMMIT} } {}
           42  +
           43  +do_select_test fts3query-1.5 {
           44  +  SELECT * FROM t1;
           45  +} {{The source code for SQLite is in the public}}
           46  +do_select_test fts3query-1.6 {
           47  +  SELECT * FROM t1 WHERE t1 MATCH 'sqlite'
           48  +} {{The source code for SQLite is in the public}}
           49  +
           50  +
           51  +set sqlite_fts3_enable_parentheses 1
           52  +do_test fts3query-2.1 {
           53  +  execsql {
           54  +    CREATE VIRTUAL TABLE zoink USING fts3;
           55  +    INSERT INTO zoink VALUES('The apple falls far from the tree');
           56  +  }
           57  +} {}
           58  +do_test fts3query-2.2 {
           59  +  execsql {
           60  +    SELECT docid FROM zoink WHERE zoink MATCH '(apple oranges) AND apple'
           61  +  }
           62  +} {}
           63  +do_test fts3query-2.3 {
           64  +  execsql {
           65  +    SELECT docid FROM zoink WHERE zoink MATCH 'apple AND (oranges apple)'
           66  +  }
           67  +} {}
           68  +set sqlite_fts3_enable_parentheses 0
           69  +
           70  +do_test fts3query-3.1 {
           71  +  execsql {
           72  +    CREATE VIRTUAL TABLE foobar using FTS3(description, tokenize porter);
           73  +    INSERT INTO foobar (description) values ('
           74  +      Filed under: Emerging Technologies, EV/Plug-in, Hybrid, Chevrolet, GM, 
           75  +      ZENN 2011 Chevy Volt - Click above for high-res image gallery There are 
           76  +      16 days left in the month of December. Besides being time for most 
           77  +      Americans to kick their Christmas shopping sessions into high gear and
           78  +      start planning their resolutions for 2010, it also means that there''s
           79  +      precious little time for EEStor to "deliver functional technology" to
           80  +      Zenn Motors as promised. Still, the promises held out by the secretive
           81  +      company are too great for us to forget about entirely. We''d love for
           82  +      EEStor''s claims to be independently verified and proven accurate, as
           83  +      would just about anyone else looking to break free of petroleum in fav
           84  +    '); 
           85  +  }
           86  +} {}
           87  +
           88  +do_test fts3query-3.2 {
           89  +  execsql { SELECT docid FROM foobar WHERE description MATCH '"high sp d"' }
           90  +} {}
           91  +
           92  +proc mit {blob} {
           93  +  set scan(littleEndian) i*
           94  +  set scan(bigEndian) I*
           95  +  binary scan $blob $scan($::tcl_platform(byteOrder)) r
           96  +  return $r
           97  +}
           98  +db func mit mit
           99  +
          100  +do_test fts3query-3.3 {
          101  +  execsql { SELECT mit(matchinfo(foobar)) FROM foobar WHERE foobar MATCH 'the' }
          102  +} {{1 1 3 3}}
          103  +
          104  +finish_test
          105  +

Changes to test/fts3rnd.test.

   143    143   
   144    144   proc simple_phrase {zPrefix} {
   145    145     set ret [list]
   146    146   
   147    147     set reg [string map {* {[^ ]*}} $zPrefix]
   148    148     set reg " $reg "
   149    149   
   150         -  foreach {key value} [array get ::t1] {
          150  +  foreach key [lsort -integer [array names ::t1]] {
          151  +    set value $::t1($key)
          152  +    set cnt [list]
   151    153       foreach col $value {
   152         -      if {[regexp $reg " $col "]} {lappend ret $key}
          154  +      if {[regexp $reg " $col "]} { lappend ret $key ; break }
          155  +    }
          156  +  }
          157  +
          158  +  #lsort -uniq -integer $ret
          159  +  set ret
          160  +}
          161  +
          162  +proc simple_token_matchinfo {zToken} {
          163  +  set total(0) 0
          164  +  set total(1) 0
          165  +  set total(2) 0
          166  +
          167  +  foreach key [lsort -integer [array names ::t1]] {
          168  +    set value $::t1($key)
          169  +    set cnt [list]
          170  +    foreach i {0 1 2} col $value {
          171  +      set n [llength [lsearch -all $col $zToken]]
          172  +      lappend cnt $n
          173  +      incr total($i) $n
          174  +    }
          175  +    if {[lindex [lsort $cnt] end]} {
          176  +      lappend ret $key [concat 1 3 XXX $cnt]
   153    177       }
   154    178     }
   155         -
   156         -  lsort -uniq -integer $ret
   157         -}
          179  +  
          180  +  string map [list XXX "$total(0) $total(1) $total(2)"] $ret
          181  +} 
   158    182   
   159    183   proc simple_near {termlist nNear} {
   160    184     set ret [list]
   161    185   
   162    186     foreach {key value} [array get ::t1] {
   163    187       foreach v $value {
   164    188   
................................................................................
   209    233   }
   210    234   proc setop_and {A B} {
   211    235     foreach b $B { set n($b) {} }
   212    236     set ret [list]
   213    237     foreach a $A { if {[info exists n($a)]} {lappend ret $a} }
   214    238     return $ret
   215    239   }
          240  +
          241  +proc mit {blob} {
          242  +  set scan(littleEndian) i*
          243  +  set scan(bigEndian) I*
          244  +  binary scan $blob $scan($::tcl_platform(byteOrder)) r
          245  +  return $r
          246  +}
          247  +db func mit mit
   216    248   
   217    249   set sqlite_fts3_enable_parentheses 1
   218    250   
   219    251   foreach nodesize {50 500 1000 2000} {
   220    252     catch { array unset ::t1 }
   221    253   
   222    254     # Create the FTS3 table. Populate it (and the Tcl array) with 100 rows.
................................................................................
   224    256     db transaction {
   225    257       catchsql { DROP TABLE t1 }
   226    258       execsql "CREATE VIRTUAL TABLE t1 USING fts3(a, b, c)"
   227    259       execsql "INSERT INTO t1(t1) VALUES('nodesize=$nodesize')"
   228    260       for {set i 0} {$i < 100} {incr i} { insert_row $i }
   229    261     }
   230    262     
   231         -  for {set iTest 0} {$iTest <= 100} {incr iTest} {
          263  +  for {set iTest 1} {$iTest <= 100} {incr iTest} {
   232    264       catchsql COMMIT
   233    265   
   234    266       set DO_MALLOC_TEST 0
   235    267       set nRep 10
   236    268       if {$iTest==100 && $nodesize==50} { 
   237    269         set DO_MALLOC_TEST 1 
   238    270         set nRep 2
................................................................................
   261    293       # the database for the set of documents containing each of these terms
   262    294       # is the same as the result obtained by scanning the contents of the Tcl 
   263    295       # array for each term.
   264    296       #
   265    297       for {set i 0} {$i < 10} {incr i} {
   266    298         set term [random_term]
   267    299         do_select_test fts3rnd-1.$nodesize.$iTest.1.$i {
   268         -        SELECT docid FROM t1 WHERE t1 MATCH $term
   269         -      } [simple_phrase $term]
          300  +        SELECT docid, mit(matchinfo(t1)) FROM t1 WHERE t1 MATCH $term
          301  +      } [simple_token_matchinfo $term]
   270    302       }
   271    303   
   272    304       # This time, use the first two characters of each term as a term prefix
   273    305       # to query for. Test that querying the Tcl array produces the same results
   274    306       # as querying the FTS3 table for the prefix.
   275    307       #
   276    308       for {set i 0} {$i < $nRep} {incr i} {

Changes to test/fuzz2.test.

    61     61   } {1}
    62     62   do_test fuzz2-2.11 {
    63     63     fuzzcatch {UPDATE OR IGNORE "AAAAAA" . "AAAAAA" SET "AAAAAA" = NOT #96}
    64     64   } {1}
    65     65   do_test fuzz2-2.12 {
    66     66     fuzzcatch {SELECT - #196}
    67     67   } {1}
           68  +
           69  +ifcapable {trigger} {  # Only do the following tests if triggers are enabled
           70  +
    68     71   do_test fuzz2-3.0 {
    69     72     fuzzcatch {CREATE TRIGGER "AAAAAA" . "AAAAAA" AFTER UPDATE OF "AAAAAA" , "AAAAAA" ON "AAAAAA" . "AAAAAA" FOR EACH ROW BEGIN UPDATE AAAAAA SET "AAAAAA" = #162;  END}
    70     73   } {1}
    71     74   do_test fuzz2-3.1 {
    72     75     fuzzcatch {CREATE TRIGGER IF NOT EXISTS "AAAAAA" UPDATE ON "AAAAAA" . AAAAAA FOR EACH ROW BEGIN DELETE FROM "AAAAAA" ; INSERT INTO AAAAAA ( "AAAAAA" ) SELECT DISTINCT "AAAAAA" "AAAAAA" , #167 AAAAAA , "AAAAAA" . * ORDER BY "AAAAAA" ASC , x'414141414141' BETWEEN RAISE ( FAIL , "AAAAAA" ) AND AAAAAA ( * ) NOT NULL DESC LIMIT AAAAAA ; REPLACE INTO AAAAAA ( AAAAAA ) VALUES ( AAAAAA ( * ) ) ; END}
    73     76   } {1}
    74     77   do_test fuzz2-3.2 {
    75     78     fuzzcatch {CREATE TEMP TRIGGER IF NOT EXISTS AAAAAA . "AAAAAA" BEFORE UPDATE OF "AAAAAA" ON AAAAAA . "AAAAAA" BEGIN SELECT ALL * , #175 "AAAAAA" FROM "AAAAAA" . AAAAAA;  END}
    76     79   } {1}
           80  +
           81  +} ;# End of ifcapable {trigger}
           82  +
    77     83   do_test fuzz2-4.0 {
    78     84     fuzzcatch {ATTACH DATABASE #168 AS whatever}
    79     85   } {1}
    80     86   do_test fuzz2-4.1 {
    81     87     fuzzcatch {DETACH #133}
    82     88   } {1}
    83     89   do_test fuzz2-5.0 {

Changes to test/main.test.

    21     21   ifcapable {complete} {
    22     22   
    23     23   # Tests of the sqlite_complete() function.
    24     24   #
    25     25   do_test main-1.1 {
    26     26     db complete {This is a test}
    27     27   } {0}
    28         -do_test main-1.2 {
           28  +do_test main-1.2.0 {
    29     29     db complete {
    30     30     }
    31         -} {1}
    32         -do_test main-1.3 {
           31  +} {0}
           32  +do_test main-1.2.1 {
           33  +  db complete {}
           34  +} {0}
           35  +do_test main-1.3.0 {
    33     36     db complete {
    34     37        -- a comment ;
    35     38     }
    36         -} {1}
    37         -do_test main-1.4 {
           39  +} {0}
           40  +do_test main-1.3.1 {
           41  +  db complete {
           42  +     /* a comment ; */
           43  +  }
           44  +} {0}
           45  +do_test main-1.4.0 {
    38     46     db complete {
    39     47        -- a comment ;
    40     48        ;
    41     49     }
           50  +} {1}
           51  +do_test main-1.4.1 {
           52  +  db complete {
           53  +     /* a comment ; */
           54  +     ;
           55  +  }
           56  +} {1}
           57  +do_test main-1.4.2 {
           58  +  db complete {
           59  +     /* a comment ; */ ;
           60  +  }
    42     61   } {1}
    43     62   do_test main-1.5 {
    44     63     db complete {DROP TABLE 'xyz;}
    45     64   } {0}
    46     65   do_test main-1.6 {
    47     66     db complete {DROP TABLE 'xyz';}
    48     67   } {1}

Changes to test/schema.test.

   359    359   
   360    360     # The schema cookie now has the same value as it did when SQL statement
   361    361     # $::STMT was prepared. So unless it has been expired, it would be
   362    362     # possible to run the "CREATE TABLE t4" statement and create a
   363    363     # duplicate table.
   364    364     list [sqlite3_step $::STMT] [sqlite3_finalize $::STMT]
   365    365   } {SQLITE_ERROR SQLITE_SCHEMA}
          366  +
          367  +ifcapable {auth} {
   366    368   
   367    369   do_test schema-13.1 {
   368    370     set S [sqlite3_prepare_v2 db "SELECT * FROM sqlite_master" -1 dummy]
   369    371     db function hello hello
   370    372     db function hello {}
   371    373     db auth auth
   372    374     proc auth {args} {
................................................................................
   379    381   do_test schema-13.2 {
   380    382     sqlite3_step $S
   381    383   } {SQLITE_SCHEMA}
   382    384   
   383    385   do_test schema-13.3 {
   384    386     sqlite3_finalize $S
   385    387   } {SQLITE_SCHEMA}
          388  +
          389  +}
   386    390   
   387    391   finish_test

Changes to test/tester.tcl.

   960    960       close $t
   961    961       close $f
   962    962     }
   963    963   }
   964    964   
   965    965   # Drop all tables in database [db]
   966    966   proc drop_all_tables {{db db}} {
   967         -  set pk [$db one "PRAGMA foreign_keys"]
   968         -  $db eval "PRAGMA foreign_keys = OFF"
          967  +  ifcapable trigger&&foreignkey {
          968  +    set pk [$db one "PRAGMA foreign_keys"]
          969  +    $db eval "PRAGMA foreign_keys = OFF"
          970  +  }
   969    971     foreach {t type} [$db eval {
   970    972       SELECT name, type FROM sqlite_master 
   971    973       WHERE type IN('table', 'view') AND name NOT like 'sqlite_%'
   972    974     }] {
   973    975       $db eval "DROP $type $t"
   974    976     }
   975         -  $db eval " PRAGMA foreign_keys = $pk "
          977  +  ifcapable trigger&&foreignkey {
          978  +    $db eval "PRAGMA foreign_keys = $pk"
          979  +  }
   976    980   }
   977    981   
   978    982   
   979    983   # If the library is compiled with the SQLITE_DEFAULT_AUTOVACUUM macro set
   980    984   # to non-zero, then set the global variable $AUTOVACUUM to 1.
   981    985   set AUTOVACUUM $sqlite_options(default_autovacuum)
   982    986   
   983    987   source $testdir/thread_common.tcl

Changes to test/tkt-3fe897352e.test.

    12     12   #
    13     13   # This file implements tests to verify that ticket [3fe897352e8d8] has been
    14     14   # fixed.  
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
           19  +
           20  +# The following tests use hex_to_utf16be() and hex_to_utf16le() which 
           21  +# which are only available if SQLite is built with UTF16 support.
           22  +ifcapable {!utf16} {
           23  +  finish_test
           24  +  return
           25  +}
    19     26   
    20     27   do_test tkt-3fe89-1.1 {
    21     28     db close
    22     29     sqlite3 db :memory:
    23     30     db eval {
    24     31       PRAGMA encoding=UTF8;
    25     32       CREATE TABLE t1(x);

Changes to test/trace.test.

    55     55   db close
    56     56   sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
    57     57   do_test trace-2.1 {
    58     58     set STMT [sqlite3_prepare $DB {INSERT INTO t1 VALUES(2,3)} -1 TAIL]
    59     59     db trace trace_proc
    60     60     proc trace_proc sql {
    61     61       global TRACE_OUT
    62         -    set TRACE_OUT $sql
           62  +    lappend TRACE_OUT [string trim $sql]
    63     63     }
    64     64     set TRACE_OUT {}
    65     65     sqlite3_step $STMT
    66     66     set TRACE_OUT
    67         -} {INSERT INTO t1 VALUES(2,3)}
           67  +} {{INSERT INTO t1 VALUES(2,3)}}
    68     68   do_test trace-2.2 {
    69     69     set TRACE_OUT {}
    70     70     sqlite3_reset $STMT
    71     71     set TRACE_OUT 
    72     72   } {}
    73     73   do_test trace-2.3 {
    74     74     sqlite3_step $STMT
    75     75     set TRACE_OUT
    76         -} {INSERT INTO t1 VALUES(2,3)}
           76  +} {{INSERT INTO t1 VALUES(2,3)}}
    77     77   do_test trace-2.4 {
           78  +  set TRACE_OUT {}
    78     79     execsql {SELECT * FROM t1}
    79     80   } {1 2 2 3 2 3}
    80     81   do_test trace-2.5 {
    81     82     set TRACE_OUT
    82         -} {SELECT * FROM t1}
           83  +} {{SELECT * FROM t1}}
    83     84   catch {sqlite3_finalize $STMT}
           85  +
           86  +do_test trace-2.6 {
           87  +  set TRACE_OUT {}
           88  +  db eval VACUUM
           89  +  set TRACE_OUT
           90  +} {VACUUM}
    84     91   
    85     92   # Similar tests, but this time for profiling.
    86     93   # 
    87     94   do_test trace-3.1 {
    88     95     set rc [catch {db profile 1 2 3} msg]
    89     96     lappend rc $msg
    90     97   } {1 {wrong # args: should be "db profile ?CALLBACK?"}}
................................................................................
   118    125   db close
   119    126   sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
   120    127   do_test trace-4.1 {
   121    128     set STMT [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL]
   122    129     db trace trace_proc
   123    130     proc profile_proc {sql tm} {
   124    131       global TRACE_OUT
   125         -    set TRACE_OUT $sql
          132  +    lappend TRACE_OUT [string trim $sql]
   126    133     }
   127    134     set TRACE_OUT {}
   128    135     sqlite3_step $STMT
   129    136     set TRACE_OUT
   130         -} {INSERT INTO t2 VALUES(2,3)}
          137  +} {{INSERT INTO t2 VALUES(2,3)}}
   131    138   do_test trace-4.2 {
   132    139     set TRACE_OUT {}
   133    140     sqlite3_reset $STMT
   134    141     set TRACE_OUT 
   135    142   } {}
   136    143   do_test trace-4.3 {
   137    144     sqlite3_step $STMT
   138    145     set TRACE_OUT
   139         -} {INSERT INTO t2 VALUES(2,3)}
          146  +} {{INSERT INTO t2 VALUES(2,3)}}
   140    147   do_test trace-4.4 {
          148  +  set TRACE_OUT {}
   141    149     execsql {SELECT * FROM t1}
   142    150   } {1 2 2 3 2 3}
   143    151   do_test trace-4.5 {
   144    152     set TRACE_OUT
   145         -} {SELECT * FROM t1}
          153  +} {{SELECT * FROM t1}}
   146    154   catch {sqlite3_finalize $STMT}
   147    155   
   148    156   # Trigger tracing.
   149    157   #
   150    158   ifcapable trigger {
   151    159     do_test trace-5.1 {
   152    160       db eval {
................................................................................
   225    233        CREATE TABLE t6([$::t6str],"?1");
   226    234        INSERT INTO t6 VALUES(1,2);
   227    235     }
   228    236     db trace trace_proc
   229    237     set TRACE_OUT {}
   230    238     execsql {SELECT '$::t6str', [$::t6str], $::t6str, ?1, "?1", $::t6str FROM t6}
   231    239   } {{$::t6str} 1 {test-six y'all} {test-six y'all} 2 {test-six y'all}}
   232         -do_test trace-6.101 {
          240  +do_test trace-6.201 {
   233    241     set TRACE_OUT
   234    242   } {{SELECT '$::t6str', [$::t6str], 'test-six y''all', 'test-six y''all', "?1", 'test-six y''all' FROM t6}}
   235    243   
   236    244   
   237    245   finish_test

Added test/triggerD.test.

            1  +# 2009 December 29
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice', here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Verify that when columns named "rowid", "oid", and "_rowid_" appear
           13  +# in a table as ordinary columns (not as the INTEGER PRIMARY KEY) then
           14  +# the use of these columns in triggers will refer to the column and not
           15  +# to the actual ROWID.  Ticket [34d2ae1c6d08b5271ba5e5592936d4a1d913ffe3]
           16  +#
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +ifcapable {!trigger} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +# Triggers on tables where the table has ordinary columns named
           26  +# rowid, oid, and _rowid_.
           27  +#
           28  +do_test triggerD-1.1 {
           29  +  db eval {
           30  +    CREATE TABLE t1(rowid, oid, _rowid_, x);
           31  +    CREATE TABLE log(a,b,c,d,e);
           32  +    CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
           33  +      INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
           34  +    END;
           35  +    CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
           36  +      INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
           37  +    END;
           38  +    CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
           39  +      INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
           40  +      INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
           41  +    END;
           42  +    CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
           43  +      INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
           44  +      INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
           45  +    END;
           46  +    CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
           47  +      INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
           48  +    END;
           49  +    CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
           50  +      INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
           51  +    END;
           52  +  }
           53  +} {}
           54  +do_test triggerD-1.2 {
           55  +  db eval {
           56  +    INSERT INTO t1 VALUES(100,200,300,400);
           57  +    SELECT * FROM log
           58  +  }
           59  +} {r1 100 200 300 400 r2 100 200 300 400}
           60  +do_test triggerD-1.3 {
           61  +  db eval {
           62  +    DELETE FROM log;
           63  +    UPDATE t1 SET rowid=rowid+1;
           64  +    SELECT * FROM log
           65  +  }
           66  +} {r3.old 100 200 300 400 r3.new 101 200 300 400 r4.old 100 200 300 400 r4.new 101 200 300 400}
           67  +do_test triggerD-1.4 {
           68  +  db eval {
           69  +    DELETE FROM log;
           70  +    DELETE FROM t1;
           71  +    SELECT * FROM log
           72  +  }
           73  +} {r5 101 200 300 400 r6 101 200 300 400}
           74  +
           75  +# Triggers on tables where the table does not have ordinary columns named
           76  +# rowid, oid, and _rowid_.
           77  +#
           78  +do_test triggerD-2.1 {
           79  +  db eval {
           80  +    DROP TABLE t1;
           81  +    CREATE TABLE t1(w,x,y,z);
           82  +    CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
           83  +      INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
           84  +    END;
           85  +    CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
           86  +      INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
           87  +    END;
           88  +    CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
           89  +      INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
           90  +      INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
           91  +    END;
           92  +    CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
           93  +      INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
           94  +      INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
           95  +    END;
           96  +    CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
           97  +      INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
           98  +    END;
           99  +    CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
          100  +      INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
          101  +    END;
          102  +  }
          103  +} {}
          104  +do_test triggerD-2.2 {
          105  +  db eval {
          106  +    DELETE FROM log;
          107  +    INSERT INTO t1 VALUES(100,200,300,400);
          108  +    SELECT * FROM log;
          109  +  }
          110  +} {r1 -1 -1 -1 200 r2 1 1 1 200}
          111  +do_test triggerD-2.3 {
          112  +  db eval {
          113  +    DELETE FROM log;
          114  +    UPDATE t1 SET x=x+1;
          115  +    SELECT * FROM log
          116  +  }
          117  +} {r3.old 1 1 1 200 r3.new 1 1 1 201 r4.old 1 1 1 200 r4.new 1 1 1 201}
          118  +do_test triggerD-2.4 {
          119  +  db eval {
          120  +    DELETE FROM log;
          121  +    DELETE FROM t1;
          122  +    SELECT * FROM log
          123  +  }
          124  +} {r5 1 1 1 201 r6 1 1 1 201}
          125  +
          126  +finish_test

Changes to tool/lemon.c.

   487    487   ** into the current action table.  Then reset the transaction set back
   488    488   ** to an empty set in preparation for a new round of acttab_action() calls.
   489    489   **
   490    490   ** Return the offset into the action table of the new transaction.
   491    491   */
   492    492   int acttab_insert(acttab *p){
   493    493     int i, j, k, n;
          494  +  int nActtab;     /* Number of slots in the p->aAction[] table */
   494    495     assert( p->nLookahead>0 );
   495    496   
   496    497     /* Make sure we have enough space to hold the expanded action table
   497    498     ** in the worst case.  The worst case occurs if the transaction set
   498    499     ** must be appended to the current action table
   499    500     */
   500    501     n = p->mxLookahead + 1;
   501         -  if( p->nAction + n >= p->nActionAlloc ){
          502  +  nActtab = p->nAction + n;
          503  +  if( nActtab >= p->nActionAlloc ){
   502    504       int oldAlloc = p->nActionAlloc;
   503    505       p->nActionAlloc = p->nAction + n + p->nActionAlloc + 20;
   504    506       p->aAction = realloc( p->aAction,
   505    507                             sizeof(p->aAction[0])*p->nActionAlloc);
   506    508       if( p->aAction==0 ){
   507    509         fprintf(stderr,"malloc failed\n");
   508    510         exit(1);
................................................................................
   512    514         p->aAction[i].action = -1;
   513    515       }
   514    516     }
   515    517   
   516    518     /* Scan the existing action table looking for an offset where we can
   517    519     ** insert the current transaction set.  Fall out of the loop when that
   518    520     ** offset is found.  In the worst case, we fall out of the loop when
   519         -  ** i reaches p->nAction, which means we append the new transaction set.
          521  +  ** i reaches nActtab, which means we append the new transaction set.
   520    522     **
   521    523     ** i is the index in p->aAction[] where p->mnLookahead is inserted.
   522    524     */
   523         -  for(i=p->nAction-1; i>=0; i--){
          525  +  for(i=nActtab-1; i>=0; i--){
   524    526       /* First look for an existing action table entry that can be reused */
   525    527       if( p->aAction[i].lookahead==p->mnLookahead ){
   526    528         if( p->aAction[i].action!=p->mnAction ) continue;
   527    529         for(j=0; j<p->nLookahead; j++){
   528    530           k = p->aLookahead[j].lookahead - p->mnLookahead + i;
   529    531           if( k<0 || k>=p->nAction ) break;
   530    532           if( p->aLookahead[j].lookahead!=p->aAction[k].lookahead ) break;
................................................................................
   539    541         if( n==p->nLookahead ){
   540    542           break;  /* Same as a prior transaction set */
   541    543         }
   542    544       }
   543    545     }
   544    546     if( i<0 ){
   545    547       /* If no reusable entry is found, look for an empty slot */
   546         -    for(i=0; i<p->nAction; i++){
          548  +    for(i=0; i<nActtab; i++){
   547    549         if( p->aAction[i].lookahead<0 ){
   548    550           for(j=0; j<p->nLookahead; j++){
   549    551             k = p->aLookahead[j].lookahead - p->mnLookahead + i;
   550    552             if( k<0 ) break;
   551    553             if( p->aAction[k].lookahead>=0 ) break;
   552    554           }
   553    555           if( j<p->nLookahead ) continue;

Changes to tool/mkkeywordhash.c.

   592    592     printf("    }\n");
   593    593     printf("  }\n");
   594    594     printf("  return TK_ID;\n");
   595    595     printf("}\n");
   596    596     printf("int sqlite3KeywordCode(const unsigned char *z, int n){\n");
   597    597     printf("  return keywordCode((char*)z, n);\n");
   598    598     printf("}\n");
          599  +  printf("#define SQLITE_N_KEYWORD %d\n", nKeyword);
   599    600   
   600    601     return 0;
   601    602   }

Changes to tool/shell1.test.

    19     19   #   shell1-1.*: Basic command line option handling.
    20     20   #   shell1-2.*: Basic "dot" command token parsing.
    21     21   #   shell1-3.*: Basic test that "dot" command can be called.
    22     22   #
    23     23   
    24     24   package require sqlite3
    25     25   
    26         -set CLI "./sqlite"
           26  +set CLI "./sqlite3"
    27     27   
    28     28   proc do_test {name cmd expected} {
    29     29     puts -nonewline "$name ..."
    30     30     set res [uplevel $cmd]
    31     31     if {$res eq $expected} {
    32     32       puts Ok
    33     33     } else {
................................................................................
    43     43   }
    44     44   
    45     45   proc catchsql {sql} {
    46     46     set rc [catch {uplevel [list db eval $sql]} msg]
    47     47     list $rc $msg
    48     48   }
    49     49   
    50         -proc catchcmd {db cmd} {
           50  +proc catchcmd {db {cmd ""}} {
    51     51     global CLI
    52     52     set out [open cmds.txt w]
    53     53     puts $out $cmd
    54     54     close $out
    55     55     set line "exec $CLI $db < cmds.txt"
    56     56     set rc [catch { eval $line } msg]
    57     57     list $rc $msg
................................................................................
    75     75   do_test shell1-1.1.2 {
    76     76     set res [catchcmd "-bad test.db \"select 3\" \"select 4\"" ""]
    77     77     set rc [lindex $res 0]
    78     78     list $rc \
    79     79          [regexp {Error: too many options: "select 4"} $res]
    80     80   } {1 1}
    81     81   # error on extra options
    82         -do_test shell1-1.3.2 {
           82  +do_test shell1-1.1.3 {
    83     83     set res [catchcmd "-bad FOO test.db BAD" ".quit"]
    84     84     set rc [lindex $res 0]
    85     85     list $rc \
    86     86          [regexp {Error: too many options: "BAD"} $res]
    87     87   } {1 1}
    88     88   
    89     89   # -help
................................................................................
    94     94          [regexp {Usage} $res] \
    95     95          [regexp {\-init} $res] \
    96     96          [regexp {\-version} $res]
    97     97   } {1 1 1 1}
    98     98   
    99     99   # -init filename       read/process named file
   100    100   do_test shell1-1.3.1 {
   101         -  catchcmd "-init FOO test.db" "" 
          101  +  catchcmd "-init FOO test.db" ""
   102    102   } {0 {}}
   103    103   do_test shell1-1.3.2 {
   104    104     set res [catchcmd "-init FOO test.db .quit BAD" ""]
   105    105     set rc [lindex $res 0]
   106    106     list $rc \
   107    107          [regexp {Error: too many options: "BAD"} $res]
   108    108   } {1 1}
................................................................................
   191    191     list $rc \
   192    192          [regexp {Error: missing argument for option: -nullvalue} $res]
   193    193   } {1 1}
   194    194   
   195    195   # -version             show SQLite version
   196    196   do_test shell1-1.16.1 {
   197    197     catchcmd "-version test.db" "" 
   198         -} {0 3.6.20}
          198  +} {0 3.6.22}
   199    199   
   200    200   #----------------------------------------------------------------------------
   201    201   # Test cases shell1-2.*: Basic "dot" command token parsing.
   202    202   #
   203    203   
   204    204   # check first token handling
   205    205   do_test shell1-2.1.1 {
   206         -  catchcmd " test.db" ".foo" 
          206  +  catchcmd "test.db" ".foo" 
   207    207   } {1 {Error: unknown command or invalid arguments:  "foo". Enter ".help" for help}}
   208    208   do_test shell1-2.1.2 {
   209         -  catchcmd " test.db" ".\"foo OFF\""
          209  +  catchcmd "test.db" ".\"foo OFF\""
   210    210   } {1 {Error: unknown command or invalid arguments:  "foo OFF". Enter ".help" for help}}
   211    211   do_test shell1-2.1.3 {
   212         -  catchcmd " test.db" ".\'foo OFF\'"
          212  +  catchcmd "test.db" ".\'foo OFF\'"
   213    213   } {1 {Error: unknown command or invalid arguments:  "foo OFF". Enter ".help" for help}}
   214    214   
   215    215   # unbalanced quotes
   216    216   do_test shell1-2.2.1 {
   217         -  catchcmd " test.db" ".\"foo OFF"
          217  +  catchcmd "test.db" ".\"foo OFF"
   218    218   } {1 {Error: unknown command or invalid arguments:  "foo OFF". Enter ".help" for help}}
   219    219   do_test shell1-2.2.2 {
   220         -  catchcmd " test.db" ".\'foo OFF"
          220  +  catchcmd "test.db" ".\'foo OFF"
   221    221   } {1 {Error: unknown command or invalid arguments:  "foo OFF". Enter ".help" for help}}
   222    222   do_test shell1-2.2.3 {
   223         -  catchcmd " test.db" ".explain \"OFF"
          223  +  catchcmd "test.db" ".explain \"OFF"
   224    224   } {0 {}}
   225    225   do_test shell1-2.2.4 {
   226         -  catchcmd " test.db" ".explain \'OFF"
          226  +  catchcmd "test.db" ".explain \'OFF"
   227    227   } {0 {}}
   228    228   do_test shell1-2.2.5 {
   229         -  catchcmd " test.db" ".mode \"insert FOO"
          229  +  catchcmd "test.db" ".mode \"insert FOO"
   230    230   } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
   231    231   do_test shell1-2.2.6 {
   232         -  catchcmd " test.db" ".mode \'insert FOO"
          232  +  catchcmd "test.db" ".mode \'insert FOO"
   233    233   } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
   234    234   
   235    235   # check multiple tokens, and quoted tokens
   236    236   do_test shell1-2.3.1 {
   237         -  catchcmd " test.db" ".explain 1"
          237  +  catchcmd "test.db" ".explain 1"
   238    238   } {0 {}}
   239    239   do_test shell1-2.3.2 {
   240         -  catchcmd " test.db" ".explain on"
          240  +  catchcmd "test.db" ".explain on"
   241    241   } {0 {}}
   242    242   do_test shell1-2.3.3 {
   243         -  catchcmd " test.db" ".explain \"1 2 3\""
          243  +  catchcmd "test.db" ".explain \"1 2 3\""
   244    244   } {0 {}}
   245    245   do_test shell1-2.3.4 {
   246         -  catchcmd " test.db" ".explain \"OFF\""
          246  +  catchcmd "test.db" ".explain \"OFF\""
   247    247   } {0 {}}
   248    248   do_test shell1-2.3.5 {
   249         -  catchcmd " test.db" ".\'explain\' \'OFF\'"
          249  +  catchcmd "test.db" ".\'explain\' \'OFF\'"
   250    250   } {0 {}}
   251    251   do_test shell1-2.3.6 {
   252         -  catchcmd " test.db" ".explain \'OFF\'"
          252  +  catchcmd "test.db" ".explain \'OFF\'"
   253    253   } {0 {}}
   254    254   do_test shell1-2.3.7 {
   255         -  catchcmd " test.db" ".\'explain\' \'OFF\'"
          255  +  catchcmd "test.db" ".\'explain\' \'OFF\'"
   256    256   } {0 {}}
   257    257   
   258    258   # check quoted args are unquoted
   259    259   do_test shell1-2.4.1 {
   260         -  catchcmd " test.db" ".mode FOO"
          260  +  catchcmd "test.db" ".mode FOO"
   261    261   } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
   262    262   do_test shell1-2.4.2 {
   263         -  catchcmd " test.db" ".mode csv"
          263  +  catchcmd "test.db" ".mode csv"
   264    264   } {0 {}}
   265    265   do_test shell1-2.4.2 {
   266         -  catchcmd " test.db" ".mode \"csv\""
          266  +  catchcmd "test.db" ".mode \"csv\""
   267    267   } {0 {}}
   268    268   
   269    269   
   270    270   #----------------------------------------------------------------------------
   271    271   # Test cases shell1-3.*: Basic test that "dot" command can be called.
   272    272   #
   273    273   
   274    274   # .backup ?DB? FILE      Backup DB (default "main") to FILE
   275    275   do_test shell1-3.1.1 {
   276         -  catchcmd " test.db" ".backup"
          276  +  catchcmd "test.db" ".backup"
   277    277   } {1 {Error: unknown command or invalid arguments:  "backup". Enter ".help" for help}}
   278    278   do_test shell1-3.1.2 {
   279         -  # catchcmd " test.db" ".backup FOO"
   280         -  #TBD!!! this asserts currently
   281         -} {}
          279  +  catchcmd "test.db" ".backup FOO"
          280  +} {0 {}}
   282    281   do_test shell1-3.1.3 {
   283         -  catchcmd " test.db" ".backup FOO BAR"
          282  +  catchcmd "test.db" ".backup FOO BAR"
   284    283   } {1 {Error: unknown database FOO}}
   285    284   do_test shell1-3.1.4 {
   286    285     # too many arguments
   287         -  catchcmd " test.db" ".backup FOO BAR BAD"
          286  +  catchcmd "test.db" ".backup FOO BAR BAD"
   288    287   } {1 {Error: unknown command or invalid arguments:  "backup". Enter ".help" for help}}
   289    288   
   290    289   # .bail ON|OFF           Stop after hitting an error.  Default OFF
   291    290   do_test shell1-3.2.1 {
   292         -  catchcmd " test.db" ".bail"
          291  +  catchcmd "test.db" ".bail"
   293    292   } {1 {Error: unknown command or invalid arguments:  "bail". Enter ".help" for help}}
   294    293   do_test shell1-3.2.2 {
   295         -  catchcmd " test.db" ".bail ON"
          294  +  catchcmd "test.db" ".bail ON"
   296    295   } {0 {}}
   297    296   do_test shell1-3.2.3 {
   298         -  catchcmd " test.db" ".bail OFF"
          297  +  catchcmd "test.db" ".bail OFF"
   299    298   } {0 {}}
   300    299   do_test shell1-3.2.4 {
   301    300     # too many arguments
   302         -  catchcmd " test.db" ".bail OFF BAD"
          301  +  catchcmd "test.db" ".bail OFF BAD"
   303    302   } {1 {Error: unknown command or invalid arguments:  "bail". Enter ".help" for help}}
   304    303   
   305    304   # .databases             List names and files of attached databases
   306    305   do_test shell1-3.3.1 {
   307         -  set res [catchcmd " test.db" ".databases"]
          306  +  set res [catchcmd "test.db" ".databases"]
   308    307     regexp {0.*main.*test\.db} $res
   309    308   } {1}
   310    309   do_test shell1-3.3.2 {
   311    310     # too many arguments
   312         -  catchcmd " test.db" ".databases BAD"
          311  +  catchcmd "test.db" ".databases BAD"
   313    312   } {1 {Error: unknown command or invalid arguments:  "databases". Enter ".help" for help}}
   314    313   
   315    314   # .dump ?TABLE? ...      Dump the database in an SQL text format
   316    315   #                          If TABLE specified, only dump tables matching
   317    316   #                          LIKE pattern TABLE.
   318    317   do_test shell1-3.4.1 {
   319         -  set res [catchcmd " test.db" ".dump"]
          318  +  set res [catchcmd "test.db" ".dump"]
   320    319     list [regexp {BEGIN TRANSACTION;} $res] \
   321    320          [regexp {COMMIT;} $res]
   322    321   } {1 1}
   323    322   do_test shell1-3.4.2 {
   324         -  set res [catchcmd " test.db" ".dump FOO"]
          323  +  set res [catchcmd "test.db" ".dump FOO"]
   325    324     list [regexp {BEGIN TRANSACTION;} $res] \
   326    325          [regexp {COMMIT;} $res]
   327    326   } {1 1}
   328    327   do_test shell1-3.4.3 {
   329    328     # too many arguments
   330         -  catchcmd " test.db" ".dump FOO BAD"
          329  +  catchcmd "test.db" ".dump FOO BAD"
   331    330   } {1 {Error: unknown command or invalid arguments:  "dump". Enter ".help" for help}}
   332    331   
   333    332   # .echo ON|OFF           Turn command echo on or off
   334    333   do_test shell1-3.5.1 {
   335         -  catchcmd " test.db" ".echo"
          334  +  catchcmd "test.db" ".echo"
   336    335   } {1 {Error: unknown command or invalid arguments:  "echo". Enter ".help" for help}}
   337    336   do_test shell1-3.5.2 {
   338         -  catchcmd " test.db" ".echo ON"
          337  +  catchcmd "test.db" ".echo ON"
   339    338   } {0 {}}
   340    339   do_test shell1-3.5.3 {
   341         -  catchcmd " test.db" ".echo OFF"
          340  +  catchcmd "test.db" ".echo OFF"
   342    341   } {0 {}}
   343    342   do_test shell1-3.5.4 {
   344    343     # too many arguments
   345         -  catchcmd " test.db" ".echo OFF BAD"
          344  +  catchcmd "test.db" ".echo OFF BAD"
   346    345   } {1 {Error: unknown command or invalid arguments:  "echo". Enter ".help" for help}}
   347    346   
   348    347   # .exit                  Exit this program
   349    348   do_test shell1-3.6.1 {
   350         -  catchcmd " test.db" ".exit"
          349  +  catchcmd "test.db" ".exit"
   351    350   } {0 {}}
   352    351   do_test shell1-3.6.2 {
   353    352     # too many arguments
   354         -  catchcmd " test.db" ".exit BAD"
          353  +  catchcmd "test.db" ".exit BAD"
   355    354   } {1 {Error: unknown command or invalid arguments:  "exit". Enter ".help" for help}}
   356    355   
   357    356   # .explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
   358    357   do_test shell1-3.7.1 {
   359         -  catchcmd " test.db" ".explain"
          358  +  catchcmd "test.db" ".explain"
   360    359     # explain is the exception to the booleans.  without an option, it turns it on.
   361    360   } {0 {}}
   362    361   do_test shell1-3.7.2 {
   363         -  catchcmd " test.db" ".explain ON"
          362  +  catchcmd "test.db" ".explain ON"
   364    363   } {0 {}}
   365    364   do_test shell1-3.7.3 {
   366         -  catchcmd " test.db" ".explain OFF"
          365  +  catchcmd "test.db" ".explain OFF"
   367    366   } {0 {}}
   368    367   do_test shell1-3.7.4 {
   369    368     # too many arguments
   370         -  catchcmd " test.db" ".explain OFF BAD"
          369  +  catchcmd "test.db" ".explain OFF BAD"
   371    370   } {1 {Error: unknown command or invalid arguments:  "explain". Enter ".help" for help}}
   372    371   
   373    372   # .genfkey ?OPTIONS?     Options are:
   374    373   #                          --no-drop: Do not drop old fkey triggers.
   375    374   #                          --ignore-errors: Ignore tables with fkey errors
   376    375   #                          --exec: Execute generated SQL immediately
   377    376   #                        See file tool/genfkey.README in the source
   378    377   #                        distribution for further information.
   379    378   do_test shell1-3.8.1 {
   380         -  catchcmd " test.db" ".genfkey"
          379  +  catchcmd "test.db" ".genfkey"
   381    380   } {0 {}}
   382    381   do_test shell1-3.8.2 {
   383         -  catchcmd " test.db" ".genfkey FOO"
          382  +  catchcmd "test.db" ".genfkey FOO"
   384    383   } {1 {unknown option: FOO}}
   385    384   
   386    385   # .header(s) ON|OFF      Turn display of headers on or off
   387    386   do_test shell1-3.9.1 {
   388         -  catchcmd " test.db" ".header"
          387  +  catchcmd "test.db" ".header"
   389    388   } {1 {Error: unknown command or invalid arguments:  "header". Enter ".help" for help}}
   390    389   do_test shell1-3.9.2 {
   391         -  catchcmd " test.db" ".header ON"
          390  +  catchcmd "test.db" ".header ON"
   392    391   } {0 {}}
   393    392   do_test shell1-3.9.3 {
   394         -  catchcmd " test.db" ".header OFF"
          393  +  catchcmd "test.db" ".header OFF"
   395    394   } {0 {}}
   396    395   do_test shell1-3.9.4 {
   397    396     # too many arguments
   398         -  catchcmd " test.db" ".header OFF BAD"
          397  +  catchcmd "test.db" ".header OFF BAD"
   399    398   } {1 {Error: unknown command or invalid arguments:  "header". Enter ".help" for help}}
   400    399   
   401    400   do_test shell1-3.9.5 {
   402         -  catchcmd " test.db" ".headers"
          401  +  catchcmd "test.db" ".headers"
   403    402   } {1 {Error: unknown command or invalid arguments:  "headers". Enter ".help" for help}}
   404    403   do_test shell1-3.9.6 {
   405         -  catchcmd " test.db" ".headers ON"
          404  +  catchcmd "test.db" ".headers ON"
   406    405   } {0 {}}
   407    406   do_test shell1-3.9.7 {
   408         -  catchcmd " test.db" ".headers OFF"
          407  +  catchcmd "test.db" ".headers OFF"
   409    408   } {0 {}}
   410    409   do_test shell1-3.9.8 {
   411    410     # too many arguments
   412         -  catchcmd " test.db" ".headers OFF BAD"
          411  +  catchcmd "test.db" ".headers OFF BAD"
   413    412   } {1 {Error: unknown command or invalid arguments:  "headers". Enter ".help" for help}}
   414    413   
   415    414   # .help                  Show this message
   416    415   do_test shell1-3.10.1 {
   417         -  set res [catchcmd " test.db" ".help"]
          416  +  set res [catchcmd "test.db" ".help"]
   418    417     # look for a few of the possible help commands
   419    418     list [regexp {.help} $res] \
   420    419          [regexp {.quit} $res] \
   421    420          [regexp {.show} $res]
   422    421   } {1 1 1}
   423    422   do_test shell1-3.10.2 {
   424    423     # we allow .help to take extra args (it is help after all)
   425         -  set res [catchcmd " test.db" ".help BAD"]
          424  +  set res [catchcmd "test.db" ".help BAD"]
   426    425     # look for a few of the possible help commands
   427    426     list [regexp {.help} $res] \
   428    427          [regexp {.quit} $res] \
   429    428          [regexp {.show} $res]
   430    429   } {1 1 1}
   431    430   
   432    431   # .import FILE TABLE     Import data from FILE into TABLE
   433    432   do_test shell1-3.11.1 {
   434         -  catchcmd " test.db" ".import"
          433  +  catchcmd "test.db" ".import"
   435    434   } {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
   436    435   do_test shell1-3.11.2 {
   437         -  catchcmd " test.db" ".import FOO"
          436  +  catchcmd "test.db" ".import FOO"
   438    437   } {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
   439    438   do_test shell1-3.11.2 {
   440         -  catchcmd " test.db" ".import FOO BAR"
          439  +  catchcmd "test.db" ".import FOO BAR"
   441    440   } {1 {Error: no such table: BAR}}
   442    441   do_test shell1-3.11.3 {
   443    442     # too many arguments
   444         -  catchcmd " test.db" ".import FOO BAR BAD"
          443  +  catchcmd "test.db" ".import FOO BAR BAD"
   445    444   } {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
   446    445   
   447    446   # .indices ?TABLE?       Show names of all indices
   448    447   #                          If TABLE specified, only show indices for tables
   449    448   #                          matching LIKE pattern TABLE.
   450    449   do_test shell1-3.12.1 {
   451         -  catchcmd " test.db" ".indices"
          450  +  catchcmd "test.db" ".indices"
   452    451   } {0 {}}
   453    452   do_test shell1-3.12.2 {
   454         -  catchcmd " test.db" ".indices FOO"
          453  +  catchcmd "test.db" ".indices FOO"
   455    454   } {0 {}}
   456    455   do_test shell1-3.12.3 {
   457    456     # too many arguments
   458         -  catchcmd " test.db" ".indices FOO BAD"
          457  +  catchcmd "test.db" ".indices FOO BAD"
   459    458   } {1 {Error: unknown command or invalid arguments:  "indices". Enter ".help" for help}}
   460    459   
   461    460   # .mode MODE ?TABLE?     Set output mode where MODE is one of:
   462    461   #                          csv      Comma-separated values
   463    462   #                          column   Left-aligned columns.  (See .width)
   464    463   #                          html     HTML <table> code
   465    464   #                          insert   SQL insert statements for TABLE
   466    465   #                          line     One value per line
   467    466   #                          list     Values delimited by .separator string
   468    467   #                          tabs     Tab-separated values
   469    468   #                          tcl      TCL list elements
   470    469   do_test shell1-3.13.1 {
   471         -  catchcmd " test.db" ".mode"
          470  +  catchcmd "test.db" ".mode"
   472    471   } {1 {Error: unknown command or invalid arguments:  "mode". Enter ".help" for help}}
   473    472   do_test shell1-3.13.2 {
   474         -  catchcmd " test.db" ".mode FOO"
          473  +  catchcmd "test.db" ".mode FOO"
   475    474   } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
   476    475   do_test shell1-3.13.3 {
   477         -  catchcmd " test.db" ".mode csv"
          476  +  catchcmd "test.db" ".mode csv"
   478    477   } {0 {}}
   479    478   do_test shell1-3.13.4 {
   480         -  catchcmd " test.db" ".mode column"
          479  +  catchcmd "test.db" ".mode column"
   481    480   } {0 {}}
   482    481   do_test shell1-3.13.5 {
   483         -  catchcmd " test.db" ".mode html"
          482  +  catchcmd "test.db" ".mode html"
   484    483   } {0 {}}
   485    484   do_test shell1-3.13.6 {
   486         -  catchcmd " test.db" ".mode insert"
          485  +  catchcmd "test.db" ".mode insert"
   487    486   } {0 {}}
   488    487   do_test shell1-3.13.7 {
   489         -  catchcmd " test.db" ".mode line"
          488  +  catchcmd "test.db" ".mode line"
   490    489   } {0 {}}
   491    490   do_test shell1-3.13.8 {
   492         -  catchcmd " test.db" ".mode list"
          491  +  catchcmd "test.db" ".mode list"
   493    492   } {0 {}}
   494    493   do_test shell1-3.13.9 {
   495         -  catchcmd " test.db" ".mode tabs"
          494  +  catchcmd "test.db" ".mode tabs"
   496    495   } {0 {}}
   497    496   do_test shell1-3.13.10 {
   498         -  catchcmd " test.db" ".mode tcl"
          497  +  catchcmd "test.db" ".mode tcl"
   499    498   } {0 {}}
   500    499   do_test shell1-3.13.11 {
   501    500     # too many arguments
   502         -  catchcmd " test.db" ".mode tcl BAD"
          501  +  catchcmd "test.db" ".mode tcl BAD"
   503    502   } {1 {Error: invalid arguments:  "BAD". Enter ".help" for help}}
   504    503   
   505    504   # don't allow partial mode type matches
   506    505   do_test shell1-3.13.12 {
   507         -  catchcmd " test.db" ".mode l"
          506  +  catchcmd "test.db" ".mode l"
   508    507   } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
   509    508   do_test shell1-3.13.13 {
   510         -  catchcmd " test.db" ".mode li"
          509  +  catchcmd "test.db" ".mode li"
   511    510   } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
   512    511   do_test shell1-3.13.14 {
   513         -  catchcmd " test.db" ".mode lin"
          512  +  catchcmd "test.db" ".mode lin"
   514    513   } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
   515    514   
   516    515   # .nullvalue STRING      Print STRING in place of NULL values
   517    516   do_test shell1-3.14.1 {
   518         -  catchcmd " test.db" ".nullvalue"
          517  +  catchcmd "test.db" ".nullvalue"
   519    518   } {1 {Error: unknown command or invalid arguments:  "nullvalue". Enter ".help" for help}}
   520    519   do_test shell1-3.14.2 {
   521         -  catchcmd " test.db" ".nullvalue FOO"
          520  +  catchcmd "test.db" ".nullvalue FOO"
   522    521   } {0 {}}
   523    522   do_test shell1-3.14.3 {
   524    523     # too many arguments
   525         -  catchcmd " test.db" ".nullvalue FOO BAD"
          524  +  catchcmd "test.db" ".nullvalue FOO BAD"
   526    525   } {1 {Error: unknown command or invalid arguments:  "nullvalue". Enter ".help" for help}}
   527    526   
   528    527   # .output FILENAME       Send output to FILENAME
   529    528   do_test shell1-3.15.1 {
   530         -  catchcmd " test.db" ".output"
          529  +  catchcmd "test.db" ".output"
   531    530   } {1 {Error: unknown command or invalid arguments:  "output". Enter ".help" for help}}
   532    531   do_test shell1-3.15.2 {
   533         -  catchcmd " test.db" ".output FOO"
          532  +  catchcmd "test.db" ".output FOO"
   534    533   } {0 {}}
   535    534   do_test shell1-3.15.3 {
   536    535     # too many arguments
   537         -  catchcmd " test.db" ".output FOO BAD"
          536  +  catchcmd "test.db" ".output FOO BAD"
   538    537   } {1 {Error: unknown command or invalid arguments:  "output". Enter ".help" for help}}
   539    538   
   540    539   # .output stdout         Send output to the screen
   541    540   do_test shell1-3.16.1 {
   542         -  catchcmd " test.db" ".output stdout"
          541  +  catchcmd "test.db" ".output stdout"
   543    542   } {0 {}}
   544    543   do_test shell1-3.16.2 {
   545    544     # too many arguments
   546         -  catchcmd " test.db" ".output stdout BAD"
          545  +  catchcmd "test.db" ".output stdout BAD"
   547    546   } {1 {Error: unknown command or invalid arguments:  "output". Enter ".help" for help}}
   548    547   
   549    548   # .prompt MAIN CONTINUE  Replace the standard prompts
   550    549   do_test shell1-3.17.1 {
   551         -  catchcmd " test.db" ".prompt"
          550  +  catchcmd "test.db" ".prompt"
   552    551   } {1 {Error: unknown command or invalid arguments:  "prompt". Enter ".help" for help}}
   553    552   do_test shell1-3.17.2 {
   554         -  catchcmd " test.db" ".prompt FOO"
          553  +  catchcmd "test.db" ".prompt FOO"
   555    554   } {0 {}}
   556    555   do_test shell1-3.17.3 {
   557         -  catchcmd " test.db" ".prompt FOO BAR"
          556  +  catchcmd "test.db" ".prompt FOO BAR"
   558    557   } {0 {}}
   559    558   do_test shell1-3.17.4 {
   560    559     # too many arguments
   561         -  catchcmd " test.db" ".prompt FOO BAR BAD"
          560  +  catchcmd "test.db" ".prompt FOO BAR BAD"
   562    561   } {1 {Error: unknown command or invalid arguments:  "prompt". Enter ".help" for help}}
   563    562   
   564    563   # .quit                  Exit this program
   565    564   do_test shell1-3.18.1 {
   566         -  catchcmd " test.db" ".quit"
          565  +  catchcmd "test.db" ".quit"
   567    566   } {0 {}}
   568    567   do_test shell1-3.18.2 {
   569    568     # too many arguments
   570         -  catchcmd " test.db" ".quit BAD"
          569  +  catchcmd "test.db" ".quit BAD"
   571    570   } {1 {Error: unknown command or invalid arguments:  "quit". Enter ".help" for help}}
   572    571   
   573    572   # .read FILENAME         Execute SQL in FILENAME
   574    573   do_test shell1-3.19.1 {
   575         -  catchcmd " test.db" ".read"
          574  +  catchcmd "test.db" ".read"
   576    575   } {1 {Error: unknown command or invalid arguments:  "read". Enter ".help" for help}}
   577    576   do_test shell1-3.19.2 {
   578    577     file delete -force FOO
   579         -  catchcmd " test.db" ".read FOO"
          578  +  catchcmd "test.db" ".read FOO"
   580    579   } {1 {Error: cannot open "FOO"}}
   581    580   do_test shell1-3.19.3 {
   582    581     # too many arguments
   583         -  catchcmd " test.db" ".read FOO BAD"
          582  +  catchcmd "test.db" ".read FOO BAD"
   584    583   } {1 {Error: unknown command or invalid arguments:  "read". Enter ".help" for help}}
   585    584   
   586    585   # .restore ?DB? FILE     Restore content of DB (default "main") from FILE
   587    586   do_test shell1-3.20.1 {
   588         -  catchcmd " test.db" ".restore"
          587  +  catchcmd "test.db" ".restore"
   589    588   } {1 {Error: unknown command or invalid arguments:  "restore". Enter ".help" for help}}
   590    589   do_test shell1-3.20.2 {
   591         -  # catchcmd " test.db" ".restore FOO"
   592         -  #TBD!!! this asserts currently
   593         -} {}
          590  +  catchcmd "test.db" ".restore FOO"
          591  +} {0 {}}
   594    592   do_test shell1-3.20.3 {
   595         -  catchcmd " test.db" ".restore FOO BAR"
          593  +  catchcmd "test.db" ".restore FOO BAR"
   596    594   } {1 {Error: unknown database FOO}}
   597    595   do_test shell1-3.20.4 {
   598    596     # too many arguments
   599         -  catchcmd " test.db" ".restore FOO BAR BAD"
          597  +  catchcmd "test.db" ".restore FOO BAR BAD"
   600    598   } {1 {Error: unknown command or invalid arguments:  "restore". Enter ".help" for help}}
   601    599   
   602    600   # .schema ?TABLE?        Show the CREATE statements
   603    601   #                          If TABLE specified, only show tables matching
   604    602   #                          LIKE pattern TABLE.
   605    603   do_test shell1-3.21.1 {
   606         -  catchcmd " test.db" ".schema"
          604  +  catchcmd "test.db" ".schema"
   607    605   } {0 {}}
   608    606   do_test shell1-3.21.2 {
   609         -  catchcmd " test.db" ".schema FOO"
          607  +  catchcmd "test.db" ".schema FOO"
   610    608   } {0 {}}
   611    609   do_test shell1-3.21.3 {
   612    610     # too many arguments
   613         -  catchcmd " test.db" ".schema FOO BAD"
          611  +  catchcmd "test.db" ".schema FOO BAD"
   614    612   } {1 {Error: unknown command or invalid arguments:  "schema". Enter ".help" for help}}
   615    613   
   616    614   # .separator STRING      Change separator used by output mode and .import
   617    615   do_test shell1-3.22.1 {
   618         -  catchcmd " test.db" ".separator"
          616  +  catchcmd "test.db" ".separator"
   619    617   } {1 {Error: unknown command or invalid arguments:  "separator". Enter ".help" for help}}
   620    618   do_test shell1-3.22.2 {
   621         -  catchcmd " test.db" ".separator FOO"
          619  +  catchcmd "test.db" ".separator FOO"
   622    620   } {0 {}}
   623    621   do_test shell1-3.22.3 {
   624    622     # too many arguments
   625         -  catchcmd " test.db" ".separator FOO BAD"
          623  +  catchcmd "test.db" ".separator FOO BAD"
   626    624   } {1 {Error: unknown command or invalid arguments:  "separator". Enter ".help" for help}}
   627    625   
   628    626   # .show                  Show the current values for various settings
   629    627   do_test shell1-3.23.1 {
   630         -  set res [catchcmd " test.db" ".show"]
          628  +  set res [catchcmd "test.db" ".show"]
   631    629     list [regexp {echo:} $res] \
   632    630          [regexp {explain:} $res] \
   633    631          [regexp {headers:} $res] \
   634    632          [regexp {mode:} $res] \
   635    633          [regexp {nullvalue:} $res] \
   636    634          [regexp {output:} $res] \
   637    635          [regexp {separator:} $res] \
   638    636          [regexp {width:} $res]
   639    637   } {1 1 1 1 1 1 1 1}
   640    638   do_test shell1-3.23.2 {
   641    639     # too many arguments
   642         -  catchcmd " test.db" ".show BAD"
          640  +  catchcmd "test.db" ".show BAD"
   643    641   } {1 {Error: unknown command or invalid arguments:  "show". Enter ".help" for help}}
   644    642   
   645    643   # .tables ?TABLE?        List names of tables
   646    644   #                          If TABLE specified, only list tables matching
   647    645   #                          LIKE pattern TABLE.
   648    646   do_test shell1-3.24.1 {
   649         -  catchcmd " test.db" ".tables"
          647  +  catchcmd "test.db" ".tables"
   650    648   } {0 {}}
   651    649   do_test shell1-3.24.2 {
   652         -  catchcmd " test.db" ".tables FOO"
          650  +  catchcmd "test.db" ".tables FOO"
   653    651   } {0 {}}
   654    652   do_test shell1-3.24.3 {
   655    653     # too many arguments
   656         -  catchcmd " test.db" ".tables FOO BAD"
          654  +  catchcmd "test.db" ".tables FOO BAD"
   657    655   } {1 {Error: unknown command or invalid arguments:  "tables". Enter ".help" for help}}
   658    656   
   659    657   # .timeout MS            Try opening locked tables for MS milliseconds
   660    658   do_test shell1-3.25.1 {
   661         -  catchcmd " test.db" ".timeout"
          659  +  catchcmd "test.db" ".timeout"
   662    660   } {1 {Error: unknown command or invalid arguments:  "timeout". Enter ".help" for help}}
   663    661   do_test shell1-3.25.2 {
   664         -  catchcmd " test.db" ".timeout zzz"
          662  +  catchcmd "test.db" ".timeout zzz"
   665    663     # this should be treated the same as a '0' timeout
   666    664   } {0 {}}
   667    665   do_test shell1-3.25.3 {
   668         -  catchcmd " test.db" ".timeout 1"
          666  +  catchcmd "test.db" ".timeout 1"
   669    667   } {0 {}}
   670    668   do_test shell1-3.25.4 {
   671    669     # too many arguments
   672         -  catchcmd " test.db" ".timeout 1 BAD"
          670  +  catchcmd "test.db" ".timeout 1 BAD"
   673    671   } {1 {Error: unknown command or invalid arguments:  "timeout". Enter ".help" for help}}
   674    672   
   675    673   # .width NUM NUM ...     Set column widths for "column" mode
   676    674   do_test shell1-3.26.1 {
   677         -  catchcmd " test.db" ".width"
          675  +  catchcmd "test.db" ".width"
   678    676   } {1 {Error: unknown command or invalid arguments:  "width". Enter ".help" for help}}
   679    677   do_test shell1-3.26.2 {
   680         -  catchcmd " test.db" ".width xxx"
          678  +  catchcmd "test.db" ".width xxx"
   681    679     # this should be treated the same as a '0' width for col 1
   682    680   } {0 {}}
   683    681   do_test shell1-3.26.3 {
   684         -  catchcmd " test.db" ".width xxx yyy"
          682  +  catchcmd "test.db" ".width xxx yyy"
   685    683     # this should be treated the same as a '0' width for col 1 and 2
   686    684   } {0 {}}
   687    685   do_test shell1-3.26.4 {
   688         -  catchcmd " test.db" ".width 1 1"
          686  +  catchcmd "test.db" ".width 1 1"
   689    687     # this should be treated the same as a '1' width for col 1 and 2
   690    688   } {0 {}}
   691    689   
   692    690   # .timer ON|OFF          Turn the CPU timer measurement on or off
   693    691   do_test shell1-3.27.1 {
   694         -  catchcmd " test.db" ".timer"
          692  +  catchcmd "test.db" ".timer"
   695    693   } {1 {Error: unknown command or invalid arguments:  "timer". Enter ".help" for help}}
   696    694   do_test shell1-3.27.2 {
   697         -  catchcmd " test.db" ".timer ON"
          695  +  catchcmd "test.db" ".timer ON"
   698    696   } {0 {}}
   699    697   do_test shell1-3.27.3 {
   700         -  catchcmd " test.db" ".timer OFF"
          698  +  catchcmd "test.db" ".timer OFF"
   701    699   } {0 {}}
   702    700   do_test shell1-3.27.4 {
   703    701     # too many arguments
   704         -  catchcmd " test.db" ".timer OFF BAD"
          702  +  catchcmd "test.db" ".timer OFF BAD"
   705    703   } {1 {Error: unknown command or invalid arguments:  "timer". Enter ".help" for help}}
   706    704   
   707         -# 
          705  +
          706  +#

Changes to tool/shell2.test.

    17     17   # Test plan:
    18     18   #
    19     19   #   shell2-1.*: Misc. test of various tickets and reported errors.
    20     20   #
    21     21   
    22     22   package require sqlite3
    23     23   
    24         -set CLI "./sqlite"
           24  +set CLI "./sqlite3"
    25     25   
    26     26   proc do_test {name cmd expected} {
    27     27     puts -nonewline "$name ..."
    28     28     set res [uplevel $cmd]
    29     29     if {$res eq $expected} {
    30     30       puts Ok
    31     31     } else {
................................................................................
    41     41   }
    42     42   
    43     43   proc catchsql {sql} {
    44     44     set rc [catch {uplevel [list db eval $sql]} msg]
    45     45     list $rc $msg
    46     46   }
    47     47   
    48         -proc catchcmd {db cmd} {
           48  +proc catchcmd {db {cmd ""}} {
    49     49     global CLI
    50     50     set out [open cmds.txt w]
    51     51     puts $out $cmd
    52     52     close $out
    53     53     set line "exec $CLI $db < cmds.txt"
    54     54     set rc [catch { eval $line } msg]
    55     55     list $rc $msg
................................................................................
    77     77   # Ticket [f5cb008a65].
    78     78   do_test shell2-1.2.1 {
    79     79     set rc [catch { eval exec $CLI \":memory:\" \"select 3\" \"select 4\" } msg]
    80     80     list $rc \
    81     81          [regexp {Error: too many options: "select 4"} $msg]
    82     82   } {1 1}
    83     83   
           84  +# Test a problem reported on the mailing list. The shell was at one point
           85  +# returning the generic SQLITE_ERROR message ("SQL error or missing database")
           86  +# instead of the "too many levels..." message in the test below.
           87  +#
           88  +do_test shell2-1.3 {
           89  +  catchcmd "-batch test.db" {
           90  +    PRAGMA recursive_triggers = ON;
           91  +    CREATE TABLE t5(a PRIMARY KEY, b, c);
           92  +    INSERT INTO t5 VALUES(1, 2, 3);
           93  +    CREATE TRIGGER au_tble AFTER UPDATE ON t5 BEGIN
           94  +      UPDATE OR IGNORE t5 SET a = new.a, c = 10;
           95  +    END;
           96  +
           97  +    UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1;
           98  +  }
           99  +} {1 {Error: near line 9: too many levels of trigger recursion}}
    84    100   
    85    101   

Added tool/shell3.test.

            1  +# 2009 Dec 16
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# The focus of this file is testing the CLI shell tool.
           13  +#
           14  +# $Id: shell2.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
           15  +#
           16  +
           17  +# Test plan:
           18  +#
           19  +#   shell3-1.*: Basic tests for running SQL statments from command line.
           20  +#   shell3-2.*: Basic tests for running SQL file from command line.
           21  +#
           22  +
           23  +package require sqlite3
           24  +
           25  +set CLI "./sqlite3"
           26  +
           27  +proc do_test {name cmd expected} {
           28  +  puts -nonewline "$name ..."
           29  +  set res [uplevel $cmd]
           30  +  if {$res eq $expected} {
           31  +    puts Ok
           32  +  } else {
           33  +    puts Error
           34  +    puts "  Got: $res"
           35  +    puts "  Expected: $expected"
           36  +    exit
           37  +  }
           38  +}
           39  +
           40  +proc execsql {sql} {
           41  +  uplevel [list db eval $sql]
           42  +}
           43  +
           44  +proc catchsql {sql} {
           45  +  set rc [catch {uplevel [list db eval $sql]} msg]
           46  +  list $rc $msg
           47  +}
           48  +
           49  +proc catchcmd {db {cmd ""}} {
           50  +  global CLI
           51  +  set out [open cmds.txt w]
           52  +  puts $out $cmd
           53  +  close $out
           54  +  set line "exec $CLI $db < cmds.txt"
           55  +  set rc [catch { eval $line } msg]
           56  +  list $rc $msg
           57  +}
           58  +
           59  +file delete -force test.db test.db.journal
           60  +sqlite3 db test.db
           61  +
           62  +
           63  +#----------------------------------------------------------------------------
           64  +#   shell3-1.*: Basic tests for running SQL statments from command line.
           65  +#
           66  +
           67  +# Run SQL statement from command line
           68  +do_test shell3-1.1 {
           69  +  file delete -force foo.db
           70  +  set rc [ catchcmd "foo.db \"CREATE TABLE t1(a);\"" ]
           71  +  set fexist [file exist foo.db]
           72  +  list $rc $fexist
           73  +} {{0 {}} 1}
           74  +do_test shell3-1.2 {
           75  +  catchcmd "foo.db" ".tables"
           76  +} {0 t1}
           77  +do_test shell3-1.3 {
           78  +  catchcmd "foo.db \"DROP TABLE t1;\""
           79  +} {0 {}}
           80  +do_test shell3-1.4 {
           81  +  catchcmd "foo.db" ".tables"
           82  +} {0 {}}
           83  +do_test shell3-1.5 {
           84  +  catchcmd "foo.db \"CREATE TABLE t1(a); DROP TABLE t1;\""
           85  +} {0 {}}
           86  +do_test shell3-1.6 {
           87  +  catchcmd "foo.db" ".tables"
           88  +} {0 {}}
           89  +do_test shell3-1.7 {
           90  +  catchcmd "foo.db \"CREATE TABLE\""
           91  +} {1 {Error: near "TABLE": syntax error}}
           92  +
           93  +#----------------------------------------------------------------------------
           94  +#   shell3-2.*: Basic tests for running SQL file from command line.
           95  +#
           96  +
           97  +# Run SQL file from command line
           98  +do_test shell3-2.1 {
           99  +  file delete -force foo.db
          100  +  set rc [ catchcmd "foo.db" "CREATE TABLE t1(a);" ]
          101  +  set fexist [file exist foo.db]
          102  +  list $rc $fexist
          103  +} {{0 {}} 1}
          104  +do_test shell3-2.2 {
          105  +  catchcmd "foo.db" ".tables"
          106  +} {0 t1}
          107  +do_test shell3-2.3 {
          108  +  catchcmd "foo.db" "DROP TABLE t1;"
          109  +} {0 {}}
          110  +do_test shell3-2.4 {
          111  +  catchcmd "foo.db" ".tables"
          112  +} {0 {}}
          113  +do_test shell3-2.5 {
          114  +  catchcmd "foo.db" "CREATE TABLE t1(a); DROP TABLE t1;"
          115  +} {0 {}}
          116  +do_test shell3-2.6 {
          117  +  catchcmd "foo.db" ".tables"
          118  +} {0 {}}
          119  +do_test shell3-2.7 {
          120  +  catchcmd "foo.db" "CREATE TABLE"
          121  +} {1 {Error: incomplete SQL: CREATE TABLE}}
          122  +
          123  +