Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
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: |
541e2b488e4e40706e457c6d3b5647d5 |
User & Date: | drh 2010-01-06 13:12:48 |
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 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, ®Free1, 2453 - pExpr->pRight, &r2, ®Free2); 2429 + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); 2430 + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); 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, ®Free1, 2465 - pExpr->pRight, &r2, ®Free2); 2441 + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); 2442 + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); 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, ®Free1, 2706 - pRight, &r2, ®Free2); 2681 + r1 = sqlite3ExprCodeTemp(pParse, pLeft, ®Free1); 2682 + r2 = sqlite3ExprCodeTemp(pParse, pRight, ®Free2); 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, ®Free1, 3242 - pExpr->pRight, &r2, ®Free2); 3217 + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); 3218 + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); 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, ®Free1, 3254 - pExpr->pRight, &r2, ®Free2); 3229 + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); 3230 + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); 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, ®Free1, 3385 - pExpr->pRight, &r2, ®Free2); 3360 + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); 3361 + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); 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, ®Free1, 3397 - pExpr->pRight, &r2, ®Free2); 3372 + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); 3373 + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); 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 +