Index: VERSION ================================================================== --- VERSION +++ VERSION @@ -1,1 +1,1 @@ -3.6.21 +3.6.22 Index: configure ================================================================== --- configure +++ configure @@ -1,8 +1,8 @@ #! /bin/sh # Guess values for system-dependent variables and create Makefiles. -# Generated by GNU Autoconf 2.62 for sqlite 3.6.21. +# Generated by GNU Autoconf 2.62 for sqlite 3.6.22. # # Copyright (C) 1992, 1993, 1994, 1995, 1996, 1998, 1999, 2000, 2001, # 2002, 2003, 2004, 2005, 2006, 2007, 2008 Free Software Foundation, Inc. # This configure script is free software; the Free Software Foundation # gives unlimited permission to copy, distribute and modify it. @@ -741,12 +741,12 @@ SHELL=${CONFIG_SHELL-/bin/sh} # Identity of this package. PACKAGE_NAME='sqlite' PACKAGE_TARNAME='sqlite' -PACKAGE_VERSION='3.6.21' -PACKAGE_STRING='sqlite 3.6.21' +PACKAGE_VERSION='3.6.22' +PACKAGE_STRING='sqlite 3.6.22' PACKAGE_BUGREPORT='' # Factoring default headers for most tests. ac_includes_default="\ #include @@ -1485,11 +1485,11 @@ # if test "$ac_init_help" = "long"; then # Omit some internal or obsolete options to make the list less imposing. # This message is too long to be a string in the A/UX 3.1 sh. cat <<_ACEOF -\`configure' configures sqlite 3.6.21 to adapt to many kinds of systems. +\`configure' configures sqlite 3.6.22 to adapt to many kinds of systems. Usage: $0 [OPTION]... [VAR=VALUE]... To assign environment variables (e.g., CC, CFLAGS...), specify them as VAR=VALUE. See below for descriptions of some of the useful variables. @@ -1550,11 +1550,11 @@ _ACEOF fi if test -n "$ac_init_help"; then case $ac_init_help in - short | recursive ) echo "Configuration of sqlite 3.6.21:";; + short | recursive ) echo "Configuration of sqlite 3.6.22:";; esac cat <<\_ACEOF Optional Features: --disable-option-checking ignore unrecognized --enable/--with options @@ -1668,11 +1668,11 @@ fi test -n "$ac_init_help" && exit $ac_status if $ac_init_version; then cat <<\_ACEOF -sqlite configure 3.6.21 +sqlite configure 3.6.22 generated by GNU Autoconf 2.62 Copyright (C) 1992, 1993, 1994, 1995, 1996, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008 Free Software Foundation, Inc. This configure script is free software; the Free Software Foundation @@ -1682,11 +1682,11 @@ fi cat >config.log <<_ACEOF This file contains any messages produced by compilers while running configure, to aid debugging if configure makes a mistake. -It was created by sqlite $as_me 3.6.21, which was +It was created by sqlite $as_me 3.6.22, which was generated by GNU Autoconf 2.62. Invocation command line was $ $0 $@ _ACEOF @@ -13970,11 +13970,11 @@ # Save the log message, to keep $[0] and so on meaningful, and to # report actual input values of CONFIG_FILES etc. instead of their # values after options handling. ac_log=" -This file was extended by sqlite $as_me 3.6.21, which was +This file was extended by sqlite $as_me 3.6.22, which was generated by GNU Autoconf 2.62. Invocation command line was CONFIG_FILES = $CONFIG_FILES CONFIG_HEADERS = $CONFIG_HEADERS CONFIG_LINKS = $CONFIG_LINKS @@ -14023,11 +14023,11 @@ Report bugs to ." _ACEOF cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1 ac_cs_version="\\ -sqlite config.status 3.6.21 +sqlite config.status 3.6.22 configured by $0, generated by GNU Autoconf 2.62, with options \\"`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`\\" Copyright (C) 2008 Free Software Foundation, Inc. This config.status script is free software; the Free Software Foundation Index: ext/fts3/fts3.c ================================================================== --- ext/fts3/fts3.c +++ ext/fts3/fts3.c @@ -795,10 +795,11 @@ static int fulltextClose(sqlite3_vtab_cursor *pCursor){ Fts3Cursor *pCsr = (Fts3Cursor *)pCursor; sqlite3_finalize(pCsr->pStmt); sqlite3Fts3ExprFree(pCsr->pExpr); sqlite3_free(pCsr->aDoclist); + sqlite3_free(pCsr->aMatchinfo); sqlite3_free(pCsr); return SQLITE_OK; } static int fts3CursorSeek(sqlite3_context *pContext, Fts3Cursor *pCsr){ @@ -840,10 +841,11 @@ pCsr->isEof = 1; }else{ sqlite3_reset(pCsr->pStmt); fts3GetDeltaVarint(&pCsr->pNextId, &pCsr->iPrevId); pCsr->isRequireSeek = 1; + pCsr->isMatchinfoOk = 1; } return rc; } @@ -955,15 +957,31 @@ assert( iVal-*piPrev > 0 || (*piPrev==0 && iVal==0) ); *pp += sqlite3Fts3PutVarint(*pp, iVal-*piPrev); *piPrev = iVal; } +/* +** When this function is called, *ppPoslist is assumed to point to the +** start of a position-list. +*/ static void fts3PoslistCopy(char **pp, char **ppPoslist){ char *pEnd = *ppPoslist; char c = 0; + + /* The end of a position list is marked by a zero encoded as an FTS3 + ** varint. A single 0x00 byte. Except, if the 0x00 byte is preceded by + ** a byte with the 0x80 bit set, then it is not a varint 0, but the tail + ** of some other, multi-byte, value. + ** + ** The following block moves pEnd to point to the first byte that is not + ** immediately preceded by a byte with the 0x80 bit set. Then increments + ** pEnd once more so that it points to the byte immediately following the + ** last byte in the position-list. + */ while( *pEnd | c ) c = *pEnd++ & 0x80; pEnd++; + if( pp ){ int n = (int)(pEnd - *ppPoslist); char *p = *pp; memcpy(p, *ppPoslist, n); p += n; @@ -1308,10 +1326,11 @@ || mergetype==MERGE_PHRASE || mergetype==MERGE_POS_PHRASE || mergetype==MERGE_NEAR || mergetype==MERGE_POS_NEAR ); if( !aBuffer ){ + *pnBuffer = 0; return SQLITE_NOMEM; } /* Read the first docid from each doclist */ fts3GetDeltaVarint2(&p1, pEnd1, &i1); @@ -1676,10 +1695,11 @@ } fts3DoclistMerge(mergetype, 0, 0, pList, &nOut, pOut, nOut, pList, nList); sqlite3_free(pOut); pOut = pList; } + assert( nOut==0 || pOut!=0 ); } if( rc==SQLITE_OK ){ *paOut = pOut; *pnOut = nOut; @@ -1695,39 +1715,46 @@ */ static int evalFts3Expr( Fts3Table *p, /* Virtual table handle */ Fts3Expr *pExpr, /* Parsed fts3 expression */ char **paOut, /* OUT: Pointer to malloc'd result buffer */ - int *pnOut /* OUT: Size of buffer at *paOut */ + int *pnOut, /* OUT: Size of buffer at *paOut */ + int isReqPos /* Require positions in output buffer */ ){ int rc = SQLITE_OK; /* Return code */ /* Zero the output parameters. */ *paOut = 0; *pnOut = 0; if( pExpr ){ + assert( pExpr->eType==FTSQUERY_PHRASE + || pExpr->eType==FTSQUERY_NEAR + || isReqPos==0 + ); if( pExpr->eType==FTSQUERY_PHRASE ){ - int isReqPos = (pExpr->pParent && pExpr->pParent->eType==FTSQUERY_NEAR); - rc = fts3PhraseSelect(p, pExpr->pPhrase, isReqPos, paOut, pnOut); + rc = fts3PhraseSelect(p, pExpr->pPhrase, + isReqPos || (pExpr->pParent && pExpr->pParent->eType==FTSQUERY_NEAR), + paOut, pnOut + ); }else{ char *aLeft; char *aRight; int nLeft; int nRight; - if( SQLITE_OK==(rc = evalFts3Expr(p, pExpr->pRight, &aRight, &nRight)) - && SQLITE_OK==(rc = evalFts3Expr(p, pExpr->pLeft, &aLeft, &nLeft)) + if( 0==(rc = evalFts3Expr(p, pExpr->pRight, &aRight, &nRight, isReqPos)) + && 0==(rc = evalFts3Expr(p, pExpr->pLeft, &aLeft, &nLeft, isReqPos)) ){ assert( pExpr->eType==FTSQUERY_NEAR || pExpr->eType==FTSQUERY_OR || pExpr->eType==FTSQUERY_AND || pExpr->eType==FTSQUERY_NOT ); switch( pExpr->eType ){ case FTSQUERY_NEAR: { Fts3Expr *pLeft; Fts3Expr *pRight; - int mergetype = MERGE_NEAR; + int mergetype = isReqPos ? MERGE_POS_NEAR : MERGE_NEAR; int nParam1; int nParam2; char *aBuffer; if( pExpr->pParent && pExpr->pParent->eType==FTSQUERY_NEAR ){ @@ -1866,11 +1893,11 @@ rc = sqlite3Fts3ExprParse(p->pTokenizer, p->azColumn, p->nColumn, iCol, zQuery, -1, &pCsr->pExpr ); if( rc!=SQLITE_OK ) return rc; - rc = evalFts3Expr(p, pCsr->pExpr, &pCsr->aDoclist, &pCsr->nDoclist); + rc = evalFts3Expr(p, pCsr->pExpr, &pCsr->aDoclist, &pCsr->nDoclist, 0); pCsr->pNextId = pCsr->aDoclist; pCsr->iPrevId = 0; } if( rc!=SQLITE_OK ) return rc; @@ -1987,10 +2014,67 @@ */ static int fts3RollbackMethod(sqlite3_vtab *pVtab){ sqlite3Fts3PendingTermsClear((Fts3Table *)pVtab); return SQLITE_OK; } + +/* +** Load the doclist associated with expression pExpr to pExpr->aDoclist. +** The loaded doclist contains positions as well as the document ids. +** This is used by the matchinfo(), snippet() and offsets() auxillary +** functions. +*/ +int sqlite3Fts3ExprLoadDoclist(Fts3Table *pTab, Fts3Expr *pExpr){ + return evalFts3Expr(pTab, pExpr, &pExpr->aDoclist, &pExpr->nDoclist, 1); +} + +/* +** After ExprLoadDoclist() (see above) has been called, this function is +** used to iterate through the position lists that make up the doclist +** stored in pExpr->aDoclist. +*/ +char *sqlite3Fts3FindPositions( + Fts3Expr *pExpr, /* Access this expressions doclist */ + sqlite3_int64 iDocid, /* Docid associated with requested pos-list */ + int iCol /* Column of requested pos-list */ +){ + assert( pExpr->isLoaded ); + if( pExpr->aDoclist ){ + char *pEnd = &pExpr->aDoclist[pExpr->nDoclist]; + char *pCsr = pExpr->pCurrent; + + assert( pCsr ); + while( pCsriCurrentiCurrent); + pExpr->pCurrent = pCsr; + }else{ + if( pExpr->iCurrent==iDocid ){ + int iThis = 0; + if( iCol<0 ){ + /* If iCol is negative, return a pointer to the start of the + ** position-list (instead of a pointer to the start of a list + ** of offsets associated with a specific column). + */ + return pCsr; + } + while( iThis=1 ); + + if( nVal>6 ){ + sqlite3_result_error(pContext, + "wrong number of arguments to function snippet()", -1); + return; + } + if( fts3FunctionArg(pContext, "snippet", apVal[0], &pCsr) ) return; + + switch( nVal ){ + case 6: nToken = sqlite3_value_int(apVal[5]); + case 5: iCol = sqlite3_value_int(apVal[4]); + case 4: zEllipsis = (const char*)sqlite3_value_text(apVal[3]); + case 3: zEnd = (const char*)sqlite3_value_text(apVal[2]); + case 2: zStart = (const char*)sqlite3_value_text(apVal[1]); + } + if( !zEllipsis || !zEnd || !zStart ){ + sqlite3_result_error_nomem(pContext); + }else if( SQLITE_OK==fts3CursorSeek(pContext, pCsr) ){ + sqlite3Fts3Snippet2(pContext, pCsr, zStart, zEnd, zEllipsis, iCol, nToken); + } +} /* ** Implementation of the offsets() function for FTS3 */ static void fts3OffsetsFunc( @@ -2114,10 +2239,31 @@ default: sqlite3_result_error_code(pContext, rc); break; } } + +/* +** Implementation of the matchinfo() function for FTS3 +*/ +static void fts3MatchinfoFunc( + sqlite3_context *pContext, /* SQLite function call context */ + int nVal, /* Size of argument array */ + sqlite3_value **apVal /* Array of arguments */ +){ + Fts3Cursor *pCsr; /* Cursor handle passed through apVal[0] */ + + if( nVal!=1 ){ + sqlite3_result_error(pContext, + "wrong number of arguments to function matchinfo()", -1); + return; + } + + if( SQLITE_OK==fts3FunctionArg(pContext, "matchinfo", apVal[0], &pCsr) ){ + sqlite3Fts3Matchinfo(pContext, pCsr); + } +} /* ** This routine implements the xFindFunction method for the FTS3 ** virtual table. */ @@ -2131,12 +2277,14 @@ struct Overloaded { const char *zName; void (*xFunc)(sqlite3_context*,int,sqlite3_value**); } aOverload[] = { { "snippet", fts3SnippetFunc }, + { "snippet2", fts3Snippet2Func }, { "offsets", fts3OffsetsFunc }, { "optimize", fts3OptimizeFunc }, + { "matchinfo", fts3MatchinfoFunc }, }; int i; /* Iterator variable */ UNUSED_PARAMETER(pVtab); UNUSED_PARAMETER(nArg); @@ -2279,11 +2427,13 @@ ** module with sqlite. */ if( SQLITE_OK==rc && SQLITE_OK==(rc = sqlite3Fts3InitHashTable(db, pHash, "fts3_tokenizer")) && SQLITE_OK==(rc = sqlite3_overload_function(db, "snippet", -1)) + && SQLITE_OK==(rc = sqlite3_overload_function(db, "snippet2", -1)) && SQLITE_OK==(rc = sqlite3_overload_function(db, "offsets", 1)) + && SQLITE_OK==(rc = sqlite3_overload_function(db, "matchinfo", -1)) && SQLITE_OK==(rc = sqlite3_overload_function(db, "optimize", 1)) ){ return sqlite3_create_module_v2( db, "fts3", &fts3Module, (void *)pHash, hashDestroy ); Index: ext/fts3/fts3Int.h ================================================================== --- ext/fts3/fts3Int.h +++ ext/fts3/fts3Int.h @@ -68,10 +68,12 @@ /* ** Internal types used by SQLite. */ typedef unsigned char u8; /* 1-byte (or larger) unsigned integer */ typedef short int i16; /* 2-byte (or larger) signed integer */ +typedef unsigned int u32; /* 4-byte unsigned integer */ +typedef sqlite3_uint64 u64; /* 8-byte unsigned integer */ /* ** Macro used to suppress compiler warnings for unused parameters. */ #define UNUSED_PARAMETER(x) (void)(x) #endif @@ -144,10 +146,12 @@ Fts3Expr *pExpr; /* Parsed MATCH query string */ sqlite3_int64 iPrevId; /* Previous id read from aDoclist */ char *pNextId; /* Pointer into the body of aDoclist */ char *aDoclist; /* List of docids for full-text queries */ int nDoclist; /* Size of buffer at aDoclist */ + int isMatchinfoOk; /* True when aMatchinfo[] matches iPrevId */ + u32 *aMatchinfo; }; /* ** The Fts3Cursor.eSearch member is always set to one of the following. ** Actualy, Fts3Cursor.eSearch can be greater than or equal to @@ -184,18 +188,35 @@ } aToken[1]; /* One entry for each token in the phrase */ }; /* ** A tree of these objects forms the RHS of a MATCH operator. +** +** If Fts3Expr.eType is either FTSQUERY_NEAR or FTSQUERY_PHRASE and isLoaded +** is true, then aDoclist points to a malloced buffer, size nDoclist bytes, +** containing the results of the NEAR or phrase query in FTS3 doclist +** format. As usual, the initial "Length" field found in doclists stored +** on disk is omitted from this buffer. +** +** Variable pCurrent always points to the start of a docid field within +** aDoclist. Since the doclist is usually scanned in docid order, this can +** be used to accelerate seeking to the required docid within the doclist. */ struct Fts3Expr { int eType; /* One of the FTSQUERY_XXX values defined below */ int nNear; /* Valid if eType==FTSQUERY_NEAR */ Fts3Expr *pParent; /* pParent->pLeft==this or pParent->pRight==this */ Fts3Expr *pLeft; /* Left operand */ Fts3Expr *pRight; /* Right operand */ Fts3Phrase *pPhrase; /* Valid if eType==FTSQUERY_PHRASE */ + + int isLoaded; /* True if aDoclist/nDoclist are initialized. */ + char *aDoclist; /* Buffer containing doclist */ + int nDoclist; /* Size of aDoclist in bytes */ + + sqlite3_int64 iCurrent; + char *pCurrent; }; /* ** Candidate values for Fts3Query.eType. Note that the order of the first ** four values is in order of precedence when parsing expressions. For @@ -254,10 +275,13 @@ int sqlite3Fts3GetVarint(const char *, sqlite_int64 *); int sqlite3Fts3GetVarint32(const char *, int *); int sqlite3Fts3VarintLen(sqlite3_uint64); void sqlite3Fts3Dequote(char *); +char *sqlite3Fts3FindPositions(Fts3Expr *, sqlite3_int64, int); +int sqlite3Fts3ExprLoadDoclist(Fts3Table *, Fts3Expr *); + /* fts3_tokenizer.c */ const char *sqlite3Fts3NextToken(const char *, int *); int sqlite3Fts3InitHashTable(sqlite3 *, Fts3Hash *, const char *); int sqlite3Fts3InitTokenizer(Fts3Hash *pHash, const char *, sqlite3_tokenizer **, const char **, char ** @@ -266,10 +290,14 @@ /* fts3_snippet.c */ void sqlite3Fts3Offsets(sqlite3_context*, Fts3Cursor*); void sqlite3Fts3Snippet(sqlite3_context*, Fts3Cursor*, const char *, const char *, const char * ); +void sqlite3Fts3Snippet2(sqlite3_context *, Fts3Cursor *, const char *, + const char *, const char *, int, int +); +void sqlite3Fts3Matchinfo(sqlite3_context *, Fts3Cursor *); /* fts3_expr.c */ int sqlite3Fts3ExprParse(sqlite3_tokenizer *, char **, int, int, const char *, int, Fts3Expr ** ); Index: ext/fts3/fts3_expr.c ================================================================== --- ext/fts3/fts3_expr.c +++ ext/fts3/fts3_expr.c @@ -733,10 +733,11 @@ */ void sqlite3Fts3ExprFree(Fts3Expr *p){ if( p ){ sqlite3Fts3ExprFree(p->pLeft); sqlite3Fts3ExprFree(p->pRight); + sqlite3_free(p->aDoclist); sqlite3_free(p); } } /**************************************************************************** Index: ext/fts3/fts3_snippet.c ================================================================== --- ext/fts3/fts3_snippet.c +++ ext/fts3/fts3_snippet.c @@ -728,7 +728,617 @@ }else{ sqlite3_result_error_nomem(pCtx); } fts3SnippetFree(p); } + +/************************************************************************* +** Below this point is the alternative, experimental snippet() implementation. +*/ + +#define SNIPPET_BUFFER_CHUNK 64 +#define SNIPPET_BUFFER_SIZE SNIPPET_BUFFER_CHUNK*4 +#define SNIPPET_BUFFER_MASK (SNIPPET_BUFFER_SIZE-1) + +static void fts3GetDeltaPosition(char **pp, int *piPos){ + int iVal; + *pp += sqlite3Fts3GetVarint32(*pp, &iVal); + *piPos += (iVal-2); +} + +/* +** Iterate through all phrase nodes in an FTS3 query, except those that +** are part of a sub-tree that is the right-hand-side of a NOT operator. +** For each phrase node found, the supplied callback function is invoked. +** +** If the callback function returns anything other than SQLITE_OK, +** the iteration is abandoned and the error code returned immediately. +** Otherwise, SQLITE_OK is returned after a callback has been made for +** all eligible phrase nodes. +*/ +static int fts3ExprIterate( + Fts3Expr *pExpr, /* Expression to iterate phrases of */ + int (*x)(Fts3Expr *, void *), /* Callback function to invoke for phrases */ + void *pCtx /* Second argument to pass to callback */ +){ + int rc; + int eType = pExpr->eType; + if( eType==FTSQUERY_NOT ){ + rc = SQLITE_OK; + }else if( eType!=FTSQUERY_PHRASE ){ + assert( pExpr->pLeft && pExpr->pRight ); + rc = fts3ExprIterate(pExpr->pLeft, x, pCtx); + if( rc==SQLITE_OK ){ + rc = fts3ExprIterate(pExpr->pRight, x, pCtx); + } + }else{ + rc = x(pExpr, pCtx); + } + return rc; +} + +typedef struct LoadDoclistCtx LoadDoclistCtx; +struct LoadDoclistCtx { + Fts3Table *pTab; /* FTS3 Table */ + int nPhrase; /* Number of phrases so far */ +}; + +static int fts3ExprLoadDoclistsCb(Fts3Expr *pExpr, void *ctx){ + int rc = SQLITE_OK; + LoadDoclistCtx *p = (LoadDoclistCtx *)ctx; + p->nPhrase++; + if( pExpr->isLoaded==0 ){ + rc = sqlite3Fts3ExprLoadDoclist(p->pTab, pExpr); + pExpr->isLoaded = 1; + if( rc==SQLITE_OK && pExpr->aDoclist ){ + pExpr->pCurrent = pExpr->aDoclist; + pExpr->pCurrent += sqlite3Fts3GetVarint(pExpr->pCurrent,&pExpr->iCurrent); + } + } + return rc; +} + +static int fts3ExprLoadDoclists(Fts3Cursor *pCsr, int *pnPhrase){ + int rc; + LoadDoclistCtx sCtx = {0, 0}; + sCtx.pTab = (Fts3Table *)pCsr->base.pVtab; + rc = fts3ExprIterate(pCsr->pExpr, fts3ExprLoadDoclistsCb, (void *)&sCtx); + *pnPhrase = sCtx.nPhrase; + return rc; +} + +/* +** Each call to this function populates a chunk of a snippet-buffer +** SNIPPET_BUFFER_CHUNK bytes in size. +** +** Return true if the end of the data has been reached (and all subsequent +** calls to fts3LoadSnippetBuffer() with the same arguments will be no-ops), +** or false otherwise. +*/ +static int fts3LoadSnippetBuffer( + int iPos, /* Document token offset to load data for */ + u8 *aBuffer, /* Circular snippet buffer to populate */ + int nList, /* Number of position lists in appList */ + char **apList, /* IN/OUT: nList position list pointers */ + int *aiPrev /* IN/OUT: Previous positions read */ +){ + int i; + int nFin = 0; + + assert( (iPos&(SNIPPET_BUFFER_CHUNK-1))==0 ); + + memset(&aBuffer[iPos&SNIPPET_BUFFER_MASK], 0, SNIPPET_BUFFER_CHUNK); + + for(i=0; i=iPos ){ + aBuffer[iPrev&SNIPPET_BUFFER_MASK] = (u8)(i+1); + } + if( 0==((*pList)&0xFE) ){ + nFin++; + break; + } + fts3GetDeltaPosition(&pList, &iPrev); + } + + aiPrev[i] = iPrev; + apList[i] = pList; + } + + return (nFin==nList); +} + +typedef struct SnippetCtx SnippetCtx; +struct SnippetCtx { + Fts3Cursor *pCsr; + int iCol; + int iPhrase; + int *aiPrev; + int *anToken; + char **apList; +}; + +static int fts3SnippetFindPositions(Fts3Expr *pExpr, void *ctx){ + SnippetCtx *p = (SnippetCtx *)ctx; + int iPhrase = p->iPhrase++; + char *pCsr; + + p->anToken[iPhrase] = pExpr->pPhrase->nToken; + pCsr = sqlite3Fts3FindPositions(pExpr, p->pCsr->iPrevId, p->iCol); + + if( pCsr ){ + int iVal; + pCsr += sqlite3Fts3GetVarint32(pCsr, &iVal); + p->apList[iPhrase] = pCsr; + p->aiPrev[iPhrase] = iVal-2; + } + return SQLITE_OK; +} + +static void fts3SnippetCnt( + int iIdx, + int nSnippet, + int *anCnt, + u8 *aBuffer, + int *anToken, + u64 *pHlmask +){ + int iSub = (iIdx-1)&SNIPPET_BUFFER_MASK; + int iAdd = (iIdx+nSnippet-1)&SNIPPET_BUFFER_MASK; + int iSub2 = (iIdx+(nSnippet/3)-1)&SNIPPET_BUFFER_MASK; + int iAdd2 = (iIdx+(nSnippet*2/3)-1)&SNIPPET_BUFFER_MASK; + + u64 h = *pHlmask; + + anCnt[ aBuffer[iSub] ]--; + anCnt[ aBuffer[iSub2] ]--; + anCnt[ aBuffer[iAdd] ]++; + anCnt[ aBuffer[iAdd2] ]++; + + h = h >> 1; + if( aBuffer[iAdd] ){ + int j; + for(j=anToken[aBuffer[iAdd]-1]; j>=1; j--){ + h |= (u64)1 << (nSnippet-j); + } + } + *pHlmask = h; +} + +static int fts3SnippetScore(int n, int *anCnt){ + int j; + int iScore = 0; + for(j=1; j<=n; j++){ + int nCnt = anCnt[j]; + iScore += nCnt + (nCnt ? 1000 : 0); + } + return iScore; +} + +static int fts3BestSnippet( + int nSnippet, /* Desired snippet length */ + Fts3Cursor *pCsr, /* Cursor to create snippet for */ + int iCol, /* Index of column to create snippet from */ + int *piPos, /* OUT: Starting token for best snippet */ + u64 *pHlmask /* OUT: Highlight mask for best snippet */ +){ + int rc; /* Return Code */ + u8 aBuffer[SNIPPET_BUFFER_SIZE];/* Circular snippet buffer */ + int *aiPrev; /* Used by fts3LoadSnippetBuffer() */ + int *anToken; /* Number of tokens in each phrase */ + char **apList; /* Array of position lists */ + int *anCnt; /* Running totals of phrase occurences */ + int nList; + + int i; + + u64 hlmask = 0; /* Current mask of highlighted terms */ + u64 besthlmask = 0; /* Mask of highlighted terms for iBestPos */ + int iBestPos = 0; /* Starting position of 'best' snippet */ + int iBestScore = 0; /* Score of best snippet higher->better */ + SnippetCtx sCtx; + + /* Iterate through the phrases in the expression to count them. The same + ** callback makes sure the doclists are loaded for each phrase. + */ + rc = fts3ExprLoadDoclists(pCsr, &nList); + if( rc!=SQLITE_OK ){ + return rc; + } + + /* Now that it is known how many phrases there are, allocate and zero + ** the required arrays using malloc(). + */ + apList = sqlite3_malloc( + sizeof(u8*)*nList + /* apList */ + sizeof(int)*(nList) + /* anToken */ + sizeof(int)*nList + /* aiPrev */ + sizeof(int)*(nList+1) /* anCnt */ + ); + if( !apList ){ + return SQLITE_NOMEM; + } + memset(apList, 0, sizeof(u8*)*nList+sizeof(int)*nList+sizeof(int)*nList); + anToken = (int *)&apList[nList]; + aiPrev = &anToken[nList]; + anCnt = &aiPrev[nList]; + + /* Initialize the contents of the aiPrev and aiList arrays. */ + sCtx.pCsr = pCsr; + sCtx.iCol = iCol; + sCtx.apList = apList; + sCtx.aiPrev = aiPrev; + sCtx.anToken = anToken; + sCtx.iPhrase = 0; + (void)fts3ExprIterate(pCsr->pExpr, fts3SnippetFindPositions, (void *)&sCtx); + + /* Load the first two chunks of data into the buffer. */ + memset(aBuffer, 0, SNIPPET_BUFFER_SIZE); + fts3LoadSnippetBuffer(0, aBuffer, nList, apList, aiPrev); + fts3LoadSnippetBuffer(SNIPPET_BUFFER_CHUNK, aBuffer, nList, apList, aiPrev); + + /* Set the initial contents of the highlight-mask and anCnt[] array. */ + for(i=1-nSnippet; i<=0; i++){ + fts3SnippetCnt(i, nSnippet, anCnt, aBuffer, anToken, &hlmask); + } + iBestScore = fts3SnippetScore(nList, anCnt); + besthlmask = hlmask; + iBestPos = 0; + + for(i=1; 1; i++){ + int iScore; + + if( 0==(i&(SNIPPET_BUFFER_CHUNK-1)) ){ + int iLoad = i + SNIPPET_BUFFER_CHUNK; + if( fts3LoadSnippetBuffer(iLoad, aBuffer, nList, apList, aiPrev) ) break; + } + + /* Figure out how highly a snippet starting at token offset i scores + ** according to fts3SnippetScore(). If it is higher than any previously + ** considered position, save the current position, score and hlmask as + ** the best snippet candidate found so far. + */ + fts3SnippetCnt(i, nSnippet, anCnt, aBuffer, anToken, &hlmask); + iScore = fts3SnippetScore(nList, anCnt); + if( iScore>iBestScore ){ + iBestPos = i; + iBestScore = iScore; + besthlmask = hlmask; + } + } + + sqlite3_free(apList); + *piPos = iBestPos; + *pHlmask = besthlmask; + return SQLITE_OK; +} + +typedef struct StrBuffer StrBuffer; +struct StrBuffer { + char *z; + int n; + int nAlloc; +}; + +static int fts3StringAppend( + StrBuffer *pStr, + const char *zAppend, + int nAppend +){ + if( nAppend<0 ){ + nAppend = (int)strlen(zAppend); + } + + if( pStr->n+nAppend+1>=pStr->nAlloc ){ + int nAlloc = pStr->nAlloc+nAppend+100; + char *zNew = sqlite3_realloc(pStr->z, nAlloc); + if( !zNew ){ + return SQLITE_NOMEM; + } + pStr->z = zNew; + pStr->nAlloc = nAlloc; + } + + memcpy(&pStr->z[pStr->n], zAppend, nAppend); + pStr->n += nAppend; + pStr->z[pStr->n] = '\0'; + + return SQLITE_OK; +} + +static int fts3SnippetText( + Fts3Cursor *pCsr, /* FTS3 Cursor */ + const char *zDoc, /* Document to extract snippet from */ + int nDoc, /* Size of zDoc in bytes */ + int nSnippet, /* Number of tokens in extracted snippet */ + int iPos, /* Index of first document token in snippet */ + u64 hlmask, /* Bitmask of terms to highlight in snippet */ + const char *zOpen, /* String inserted before highlighted term */ + const char *zClose, /* String inserted after highlighted term */ + const char *zEllipsis, + char **pzSnippet /* OUT: Snippet text */ +){ + Fts3Table *pTab = (Fts3Table *)pCsr->base.pVtab; + int rc; /* Return code */ + int iCurrent = 0; + int iStart = 0; + int iEnd; + + sqlite3_tokenizer_module *pMod; /* Tokenizer module methods object */ + sqlite3_tokenizer_cursor *pC; /* Tokenizer cursor open on zDoc/nDoc */ + const char *ZDUMMY; /* Dummy arguments used with tokenizer */ + int DUMMY1, DUMMY2, DUMMY3; /* Dummy arguments used with tokenizer */ + + StrBuffer res = {0, 0, 0}; /* Result string */ + + /* Open a token cursor on the document. Read all tokens up to and + ** including token iPos (the first token of the snippet). Set variable + ** iStart to the byte offset in zDoc of the start of token iPos. + */ + pMod = (sqlite3_tokenizer_module *)pTab->pTokenizer->pModule; + rc = pMod->xOpen(pTab->pTokenizer, zDoc, nDoc, &pC); + while( rc==SQLITE_OK && iCurrentxNext(pC, &ZDUMMY, &DUMMY1, &iStart, &DUMMY2, &iCurrent); + } + iEnd = iStart; + + if( rc==SQLITE_OK && iStart>0 ){ + rc = fts3StringAppend(&res, zEllipsis, -1); + } + + while( rc==SQLITE_OK ){ + int iBegin; + int iFin; + rc = pMod->xNext(pC, &ZDUMMY, &DUMMY1, &iBegin, &iFin, &iCurrent); + + if( rc==SQLITE_OK ){ + if( iCurrent>=(iPos+nSnippet) ){ + rc = SQLITE_DONE; + }else{ + iEnd = iFin; + if( hlmask & ((u64)1 << (iCurrent-iPos)) ){ + if( fts3StringAppend(&res, &zDoc[iStart], iBegin-iStart) + || fts3StringAppend(&res, zOpen, -1) + || fts3StringAppend(&res, &zDoc[iBegin], iEnd-iBegin) + || fts3StringAppend(&res, zClose, -1) + ){ + rc = SQLITE_NOMEM; + } + iStart = iEnd; + } + } + } + } + assert( rc!=SQLITE_OK ); + if( rc==SQLITE_DONE ){ + rc = fts3StringAppend(&res, &zDoc[iStart], iEnd-iStart); + if( rc==SQLITE_OK ){ + rc = pMod->xNext(pC, &ZDUMMY, &DUMMY1, &DUMMY2, &DUMMY3, &iCurrent); + if( rc==SQLITE_OK ){ + rc = fts3StringAppend(&res, zEllipsis, -1); + }else if( rc==SQLITE_DONE ){ + rc = fts3StringAppend(&res, &zDoc[iEnd], -1); + } + } + } + + pMod->xClose(pC); + if( rc!=SQLITE_OK ){ + sqlite3_free(res.z); + }else{ + *pzSnippet = res.z; + } + return rc; +} + + +/* +** An instance of this structure is used to collect the 'global' part of +** the matchinfo statistics. The 'global' part consists of the following: +** +** 1. The number of phrases in the query (nPhrase). +** +** 2. The number of columns in the FTS3 table (nCol). +** +** 3. A matrix of (nPhrase*nCol) integers containing the sum of the +** number of hits for each phrase in each column across all rows +** of the table. +** +** The total size of the global matchinfo array, assuming the number of +** columns is N and the number of phrases is P is: +** +** 2 + P*(N+1) +** +** The number of hits for the 3rd phrase in the second column is found +** using the expression: +** +** aGlobal[2 + P*(1+2) + 1] +*/ +typedef struct MatchInfo MatchInfo; +struct MatchInfo { + Fts3Table *pTab; /* FTS3 Table */ + Fts3Cursor *pCursor; /* FTS3 Cursor */ + int iPhrase; /* Number of phrases so far */ + int nCol; /* Number of columns in table */ + u32 *aGlobal; /* Pre-allocated buffer */ +}; + +/* +** This function is used to count the entries in a column-list (delta-encoded +** list of term offsets within a single column of a single row). +*/ +static int fts3ColumnlistCount(char **ppCollist){ + char *pEnd = *ppCollist; + char c = 0; + int nEntry = 0; + + /* A column-list is terminated by either a 0x01 or 0x00. */ + while( 0xFE & (*pEnd | c) ){ + c = *pEnd++ & 0x80; + if( !c ) nEntry++; + } + + *ppCollist = pEnd; + return nEntry; +} + +static void fts3LoadColumnlistCounts(char **pp, u32 *aOut){ + char *pCsr = *pp; + while( *pCsr ){ + sqlite3_int64 iCol = 0; + if( *pCsr==0x01 ){ + pCsr++; + pCsr += sqlite3Fts3GetVarint(pCsr, &iCol); + } + aOut[iCol] += fts3ColumnlistCount(&pCsr); + } + pCsr++; + *pp = pCsr; +} + +/* +** fts3ExprIterate() callback used to collect the "global" matchinfo stats +** for a single query. +*/ +static int fts3ExprGlobalMatchinfoCb( + Fts3Expr *pExpr, /* Phrase expression node */ + void *pCtx /* Pointer to MatchInfo structure */ +){ + MatchInfo *p = (MatchInfo *)pCtx; + char *pCsr; + char *pEnd; + const int iStart = 2 + p->nCol*p->iPhrase; + + assert( pExpr->isLoaded ); + + /* Fill in the global hit count matrix row for this phrase. */ + pCsr = pExpr->aDoclist; + pEnd = &pExpr->aDoclist[pExpr->nDoclist]; + while( pCsraGlobal[iStart]); + } + + p->iPhrase++; + return SQLITE_OK; +} + +static int fts3ExprLocalMatchinfoCb( + Fts3Expr *pExpr, /* Phrase expression node */ + void *pCtx /* Pointer to MatchInfo structure */ +){ + MatchInfo *p = (MatchInfo *)pCtx; + int iPhrase = p->iPhrase++; + + if( pExpr->aDoclist ){ + char *pCsr; + int iOffset = 2 + p->nCol*(p->aGlobal[0]+iPhrase); + + memset(&p->aGlobal[iOffset], 0, p->nCol*sizeof(u32)); + pCsr = sqlite3Fts3FindPositions(pExpr, p->pCursor->iPrevId, -1); + if( pCsr ) fts3LoadColumnlistCounts(&pCsr, &p->aGlobal[iOffset]); + } + + return SQLITE_OK; +} + +/* +** Populate pCsr->aMatchinfo[] with data for the current row. The 'matchinfo' +** data is an array of 32-bit unsigned integers (C type u32). +*/ +static int fts3GetMatchinfo(Fts3Cursor *pCsr){ + MatchInfo g; + Fts3Table *pTab = (Fts3Table *)pCsr->base.pVtab; + if( pCsr->aMatchinfo==0 ){ + int rc; + int nPhrase; + int nMatchinfo; + + g.pTab = pTab; + g.nCol = pTab->nColumn; + g.iPhrase = 0; + rc = fts3ExprLoadDoclists(pCsr, &nPhrase); + if( rc!=SQLITE_OK ){ + return rc; + } + + nMatchinfo = 2 + 2*g.nCol*nPhrase; + + g.iPhrase = 0; + g.aGlobal = (u32 *)sqlite3_malloc(sizeof(u32)*nMatchinfo); + if( !g.aGlobal ){ + return SQLITE_NOMEM; + } + memset(g.aGlobal, 0, sizeof(u32)*nMatchinfo); + + g.aGlobal[0] = nPhrase; + g.aGlobal[1] = g.nCol; + (void)fts3ExprIterate(pCsr->pExpr, fts3ExprGlobalMatchinfoCb, (void *)&g); + + pCsr->aMatchinfo = g.aGlobal; + } + + g.pTab = pTab; + g.pCursor = pCsr; + g.nCol = pTab->nColumn; + g.iPhrase = 0; + g.aGlobal = pCsr->aMatchinfo; + + if( pCsr->isMatchinfoOk ){ + (void)fts3ExprIterate(pCsr->pExpr, fts3ExprLocalMatchinfoCb, (void *)&g); + pCsr->isMatchinfoOk = 0; + } + + return SQLITE_OK; +} + +void sqlite3Fts3Snippet2( + sqlite3_context *pCtx, /* SQLite function call context */ + Fts3Cursor *pCsr, /* Cursor object */ + const char *zStart, /* Snippet start text - "" */ + const char *zEnd, /* Snippet end text - "" */ + const char *zEllipsis, /* Snippet ellipsis text - "..." */ + int iCol, /* Extract snippet from this column */ + int nToken /* Approximate number of tokens in snippet */ +){ + int rc; + int iPos = 0; + u64 hlmask = 0; + char *z = 0; + int nDoc; + const char *zDoc; + + rc = fts3BestSnippet(nToken, pCsr, iCol, &iPos, &hlmask); + + nDoc = sqlite3_column_bytes(pCsr->pStmt, iCol+1); + zDoc = (const char *)sqlite3_column_text(pCsr->pStmt, iCol+1); + + if( rc==SQLITE_OK ){ + rc = fts3SnippetText( + pCsr, zDoc, nDoc, nToken, iPos, hlmask, zStart, zEnd, zEllipsis, &z); + } + if( rc!=SQLITE_OK ){ + sqlite3_result_error_code(pCtx, rc); + }else{ + sqlite3_result_text(pCtx, z, -1, sqlite3_free); + } +} + +void sqlite3Fts3Matchinfo(sqlite3_context *pContext, Fts3Cursor *pCsr){ + int rc = fts3GetMatchinfo(pCsr); + if( rc!=SQLITE_OK ){ + sqlite3_result_error_code(pContext, rc); + }else{ + int n = sizeof(u32)*(2+pCsr->aMatchinfo[0]*pCsr->aMatchinfo[1]*2); + sqlite3_result_blob(pContext, pCsr->aMatchinfo, n, SQLITE_TRANSIENT); + } +} #endif Index: ext/fts3/fts3_tokenizer.h ================================================================== --- ext/fts3/fts3_tokenizer.h +++ ext/fts3/fts3_tokenizer.h @@ -142,7 +142,11 @@ struct sqlite3_tokenizer_cursor { sqlite3_tokenizer *pTokenizer; /* Tokenizer for this cursor. */ /* Tokenizer implementations will typically add additional fields */ }; + +int fts3_global_term_cnt(int iTerm, int iCol); +int fts3_term_cnt(int iTerm, int iCol); + #endif /* _FTS3_TOKENIZER_H_ */ Index: ext/fts3/fts3_write.c ================================================================== --- ext/fts3/fts3_write.c +++ ext/fts3/fts3_write.c @@ -2226,12 +2226,13 @@ if( !zVal ){ return SQLITE_NOMEM; }else if( nVal==8 && 0==sqlite3_strnicmp(zVal, "optimize", 8) ){ rc = fts3SegmentMerge(p, -1); - if( rc==SQLITE_DONE || rc==SQLITE_OK ){ + if( rc==SQLITE_DONE ){ rc = SQLITE_OK; + }else{ sqlite3Fts3PendingTermsClear(p); } #ifdef SQLITE_TEST }else if( nVal>9 && 0==sqlite3_strnicmp(zVal, "nodesize=", 9) ){ p->nNodeSize = atoi(&zVal[9]); Index: ext/icu/icu.c ================================================================== --- ext/icu/icu.c +++ ext/icu/icu.c @@ -452,11 +452,11 @@ int nArg; /* Number of arguments */ int enc; /* Optimal text encoding */ void *pContext; /* sqlite3_user_data() context */ void (*xFunc)(sqlite3_context*,int,sqlite3_value**); } scalars[] = { - {"regexp",-1, SQLITE_ANY, 0, icuRegexpFunc}, + {"regexp", 2, SQLITE_ANY, 0, icuRegexpFunc}, {"lower", 1, SQLITE_UTF16, 0, icuCaseFunc16}, {"lower", 2, SQLITE_UTF16, 0, icuCaseFunc16}, {"upper", 1, SQLITE_UTF16, (void*)1, icuCaseFunc16}, {"upper", 2, SQLITE_UTF16, (void*)1, icuCaseFunc16}, Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -3415,11 +3415,10 @@ sqlite3ErrorMsg(pParse, "unable to open a temporary database " "file for storing temporary tables"); pParse->rc = rc; return 1; } - assert( (db->flags & SQLITE_InTrans)==0 || db->autoCommit ); assert( db->aDb[1].pSchema ); sqlite3PagerJournalMode(sqlite3BtreePager(db->aDb[1].pBt), db->dfltJournalMode); } return 0; Index: src/complete.c ================================================================== --- src/complete.c +++ src/complete.c @@ -1,276 +1,283 @@ -/* -** 2001 September 15 -** -** The author disclaims copyright to this source code. In place of -** a legal notice, here is a blessing: -** -** May you do good and not evil. -** May you find forgiveness for yourself and forgive others. -** May you share freely, never taking more than you give. -** -************************************************************************* -** An tokenizer for SQL -** -** This file contains C code that implements the sqlite3_complete() API. -** This code used to be part of the tokenizer.c source file. But by -** separating it out, the code will be automatically omitted from -** static links that do not use it. -*/ -#include "sqliteInt.h" -#ifndef SQLITE_OMIT_COMPLETE - -/* -** This is defined in tokenize.c. We just have to import the definition. -*/ -#ifndef SQLITE_AMALGAMATION -#ifdef SQLITE_ASCII -#define IdChar(C) ((sqlite3CtypeMap[(unsigned char)C]&0x46)!=0) -#endif -#ifdef SQLITE_EBCDIC -extern const char sqlite3IsEbcdicIdChar[]; -#define IdChar(C) (((c=C)>=0x42 && sqlite3IsEbcdicIdChar[c-0x40])) -#endif -#endif /* SQLITE_AMALGAMATION */ - - -/* -** Token types used by the sqlite3_complete() routine. See the header -** comments on that procedure for additional information. -*/ -#define tkSEMI 0 -#define tkWS 1 -#define tkOTHER 2 -#define tkEXPLAIN 3 -#define tkCREATE 4 -#define tkTEMP 5 -#define tkTRIGGER 6 -#define tkEND 7 - -/* -** Return TRUE if the given SQL string ends in a semicolon. -** -** Special handling is require for CREATE TRIGGER statements. -** Whenever the CREATE TRIGGER keywords are seen, the statement -** must end with ";END;". -** -** This implementation uses a state machine with 7 states: -** -** (0) START At the beginning or end of an SQL statement. This routine -** returns 1 if it ends in the START state and 0 if it ends -** in any other state. -** -** (1) NORMAL We are in the middle of statement which ends with a single -** semicolon. -** -** (2) EXPLAIN The keyword EXPLAIN has been seen at the beginning of -** a statement. -** -** (3) CREATE The keyword CREATE has been seen at the beginning of a -** statement, possibly preceeded by EXPLAIN and/or followed by -** TEMP or TEMPORARY -** -** (4) TRIGGER We are in the middle of a trigger definition that must be -** ended by a semicolon, the keyword END, and another semicolon. -** -** (5) SEMI We've seen the first semicolon in the ";END;" that occurs at -** the end of a trigger definition. -** -** (6) END We've seen the ";END" of the ";END;" that occurs at the end -** of a trigger difinition. -** -** Transitions between states above are determined by tokens extracted -** from the input. The following tokens are significant: -** -** (0) tkSEMI A semicolon. -** (1) tkWS Whitespace -** (2) tkOTHER Any other SQL token. -** (3) tkEXPLAIN The "explain" keyword. -** (4) tkCREATE The "create" keyword. -** (5) tkTEMP The "temp" or "temporary" keyword. -** (6) tkTRIGGER The "trigger" keyword. -** (7) tkEND The "end" keyword. -** -** Whitespace never causes a state transition and is always ignored. -** -** If we compile with SQLITE_OMIT_TRIGGER, all of the computation needed -** to recognize the end of a trigger can be omitted. All we have to do -** is look for a semicolon that is not part of an string or comment. -*/ -int sqlite3_complete(const char *zSql){ - u8 state = 0; /* Current state, using numbers defined in header comment */ - u8 token; /* Value of the next token */ - -#ifndef SQLITE_OMIT_TRIGGER - /* A complex statement machine used to detect the end of a CREATE TRIGGER - ** statement. This is the normal case. - */ - static const u8 trans[7][8] = { - /* Token: */ - /* State: ** SEMI WS OTHER EXPLAIN CREATE TEMP TRIGGER END */ - /* 0 START: */ { 0, 0, 1, 2, 3, 1, 1, 1, }, - /* 1 NORMAL: */ { 0, 1, 1, 1, 1, 1, 1, 1, }, - /* 2 EXPLAIN: */ { 0, 2, 2, 1, 3, 1, 1, 1, }, - /* 3 CREATE: */ { 0, 3, 1, 1, 1, 3, 4, 1, }, - /* 4 TRIGGER: */ { 5, 4, 4, 4, 4, 4, 4, 4, }, - /* 5 SEMI: */ { 5, 5, 4, 4, 4, 4, 4, 6, }, - /* 6 END: */ { 0, 6, 4, 4, 4, 4, 4, 4, }, - }; -#else - /* If triggers are not suppored by this compile then the statement machine - ** used to detect the end of a statement is much simplier - */ - static const u8 trans[2][3] = { - /* Token: */ - /* State: ** SEMI WS OTHER */ - /* 0 START: */ { 0, 0, 1, }, - /* 1 NORMAL: */ { 0, 1, 1, }, - }; -#endif /* SQLITE_OMIT_TRIGGER */ - - while( *zSql ){ - switch( *zSql ){ - case ';': { /* A semicolon */ - token = tkSEMI; - break; - } - case ' ': - case '\r': - case '\t': - case '\n': - case '\f': { /* White space is ignored */ - token = tkWS; - break; - } - case '/': { /* C-style comments */ - if( zSql[1]!='*' ){ - token = tkOTHER; - break; - } - zSql += 2; - while( zSql[0] && (zSql[0]!='*' || zSql[1]!='/') ){ zSql++; } - if( zSql[0]==0 ) return 0; - zSql++; - token = tkWS; - break; - } - case '-': { /* SQL-style comments from "--" to end of line */ - if( zSql[1]!='-' ){ - token = tkOTHER; - break; - } - while( *zSql && *zSql!='\n' ){ zSql++; } - if( *zSql==0 ) return state==0; - token = tkWS; - break; - } - case '[': { /* Microsoft-style identifiers in [...] */ - zSql++; - while( *zSql && *zSql!=']' ){ zSql++; } - if( *zSql==0 ) return 0; - token = tkOTHER; - break; - } - case '`': /* Grave-accent quoted symbols used by MySQL */ - case '"': /* single- and double-quoted strings */ - case '\'': { - int c = *zSql; - zSql++; - while( *zSql && *zSql!=c ){ zSql++; } - if( *zSql==0 ) return 0; - token = tkOTHER; - break; - } - default: { -#ifdef SQLITE_EBCDIC - unsigned char c; -#endif - if( IdChar((u8)*zSql) ){ - /* Keywords and unquoted identifiers */ - int nId; - for(nId=1; IdChar(zSql[nId]); nId++){} -#ifdef SQLITE_OMIT_TRIGGER - token = tkOTHER; -#else - switch( *zSql ){ - case 'c': case 'C': { - if( nId==6 && sqlite3StrNICmp(zSql, "create", 6)==0 ){ - token = tkCREATE; - }else{ - token = tkOTHER; - } - break; - } - case 't': case 'T': { - if( nId==7 && sqlite3StrNICmp(zSql, "trigger", 7)==0 ){ - token = tkTRIGGER; - }else if( nId==4 && sqlite3StrNICmp(zSql, "temp", 4)==0 ){ - token = tkTEMP; - }else if( nId==9 && sqlite3StrNICmp(zSql, "temporary", 9)==0 ){ - token = tkTEMP; - }else{ - token = tkOTHER; - } - break; - } - case 'e': case 'E': { - if( nId==3 && sqlite3StrNICmp(zSql, "end", 3)==0 ){ - token = tkEND; - }else -#ifndef SQLITE_OMIT_EXPLAIN - if( nId==7 && sqlite3StrNICmp(zSql, "explain", 7)==0 ){ - token = tkEXPLAIN; - }else -#endif - { - token = tkOTHER; - } - break; - } - default: { - token = tkOTHER; - break; - } - } -#endif /* SQLITE_OMIT_TRIGGER */ - zSql += nId-1; - }else{ - /* Operators and special symbols */ - token = tkOTHER; - } - break; - } - } - state = trans[state][token]; - zSql++; - } - return state==0; -} - -#ifndef SQLITE_OMIT_UTF16 -/* -** This routine is the same as the sqlite3_complete() routine described -** above, except that the parameter is required to be UTF-16 encoded, not -** UTF-8. -*/ -int sqlite3_complete16(const void *zSql){ - sqlite3_value *pVal; - char const *zSql8; - int rc = SQLITE_NOMEM; - -#ifndef SQLITE_OMIT_AUTOINIT - rc = sqlite3_initialize(); - if( rc ) return rc; -#endif - pVal = sqlite3ValueNew(0); - sqlite3ValueSetStr(pVal, -1, zSql, SQLITE_UTF16NATIVE, SQLITE_STATIC); - zSql8 = sqlite3ValueText(pVal, SQLITE_UTF8); - if( zSql8 ){ - rc = sqlite3_complete(zSql8); - }else{ - rc = SQLITE_NOMEM; - } - sqlite3ValueFree(pVal); - return sqlite3ApiExit(0, rc); -} -#endif /* SQLITE_OMIT_UTF16 */ -#endif /* SQLITE_OMIT_COMPLETE */ +/* +** 2001 September 15 +** +** The author disclaims copyright to this source code. In place of +** a legal notice, here is a blessing: +** +** May you do good and not evil. +** May you find forgiveness for yourself and forgive others. +** May you share freely, never taking more than you give. +** +************************************************************************* +** An tokenizer for SQL +** +** This file contains C code that implements the sqlite3_complete() API. +** This code used to be part of the tokenizer.c source file. But by +** separating it out, the code will be automatically omitted from +** static links that do not use it. +*/ +#include "sqliteInt.h" +#ifndef SQLITE_OMIT_COMPLETE + +/* +** This is defined in tokenize.c. We just have to import the definition. +*/ +#ifndef SQLITE_AMALGAMATION +#ifdef SQLITE_ASCII +#define IdChar(C) ((sqlite3CtypeMap[(unsigned char)C]&0x46)!=0) +#endif +#ifdef SQLITE_EBCDIC +extern const char sqlite3IsEbcdicIdChar[]; +#define IdChar(C) (((c=C)>=0x42 && sqlite3IsEbcdicIdChar[c-0x40])) +#endif +#endif /* SQLITE_AMALGAMATION */ + + +/* +** Token types used by the sqlite3_complete() routine. See the header +** comments on that procedure for additional information. +*/ +#define tkSEMI 0 +#define tkWS 1 +#define tkOTHER 2 +#ifndef SQLITE_OMIT_TRIGGER +#define tkEXPLAIN 3 +#define tkCREATE 4 +#define tkTEMP 5 +#define tkTRIGGER 6 +#define tkEND 7 +#endif + +/* +** Return TRUE if the given SQL string ends in a semicolon. +** +** Special handling is require for CREATE TRIGGER statements. +** Whenever the CREATE TRIGGER keywords are seen, the statement +** must end with ";END;". +** +** This implementation uses a state machine with 8 states: +** +** (0) INVALID We have not yet seen a non-whitespace character. +** +** (1) START At the beginning or end of an SQL statement. This routine +** returns 1 if it ends in the START state and 0 if it ends +** in any other state. +** +** (2) NORMAL We are in the middle of statement which ends with a single +** semicolon. +** +** (3) EXPLAIN The keyword EXPLAIN has been seen at the beginning of +** a statement. +** +** (4) CREATE The keyword CREATE has been seen at the beginning of a +** statement, possibly preceeded by EXPLAIN and/or followed by +** TEMP or TEMPORARY +** +** (5) TRIGGER We are in the middle of a trigger definition that must be +** ended by a semicolon, the keyword END, and another semicolon. +** +** (6) SEMI We've seen the first semicolon in the ";END;" that occurs at +** the end of a trigger definition. +** +** (7) END We've seen the ";END" of the ";END;" that occurs at the end +** of a trigger difinition. +** +** Transitions between states above are determined by tokens extracted +** from the input. The following tokens are significant: +** +** (0) tkSEMI A semicolon. +** (1) tkWS Whitespace. +** (2) tkOTHER Any other SQL token. +** (3) tkEXPLAIN The "explain" keyword. +** (4) tkCREATE The "create" keyword. +** (5) tkTEMP The "temp" or "temporary" keyword. +** (6) tkTRIGGER The "trigger" keyword. +** (7) tkEND The "end" keyword. +** +** Whitespace never causes a state transition and is always ignored. +** This means that a SQL string of all whitespace is invalid. +** +** If we compile with SQLITE_OMIT_TRIGGER, all of the computation needed +** to recognize the end of a trigger can be omitted. All we have to do +** is look for a semicolon that is not part of an string or comment. +*/ +int sqlite3_complete(const char *zSql){ + u8 state = 0; /* Current state, using numbers defined in header comment */ + u8 token; /* Value of the next token */ + +#ifndef SQLITE_OMIT_TRIGGER + /* A complex statement machine used to detect the end of a CREATE TRIGGER + ** statement. This is the normal case. + */ + static const u8 trans[8][8] = { + /* Token: */ + /* State: ** SEMI WS OTHER EXPLAIN CREATE TEMP TRIGGER END */ + /* 0 INVALID: */ { 1, 0, 2, 3, 4, 2, 2, 2, }, + /* 1 START: */ { 1, 1, 2, 3, 4, 2, 2, 2, }, + /* 2 NORMAL: */ { 1, 2, 2, 2, 2, 2, 2, 2, }, + /* 3 EXPLAIN: */ { 1, 3, 3, 2, 4, 2, 2, 2, }, + /* 4 CREATE: */ { 1, 4, 2, 2, 2, 4, 5, 2, }, + /* 5 TRIGGER: */ { 6, 5, 5, 5, 5, 5, 5, 5, }, + /* 6 SEMI: */ { 6, 6, 5, 5, 5, 5, 5, 7, }, + /* 7 END: */ { 1, 7, 5, 5, 5, 5, 5, 5, }, + }; +#else + /* If triggers are not supported by this compile then the statement machine + ** used to detect the end of a statement is much simplier + */ + static const u8 trans[3][3] = { + /* Token: */ + /* State: ** SEMI WS OTHER */ + /* 0 INVALID: */ { 1, 0, 2, }, + /* 1 START: */ { 1, 1, 2, }, + /* 2 NORMAL: */ { 1, 2, 2, }, + }; +#endif /* SQLITE_OMIT_TRIGGER */ + + while( *zSql ){ + switch( *zSql ){ + case ';': { /* A semicolon */ + token = tkSEMI; + break; + } + case ' ': + case '\r': + case '\t': + case '\n': + case '\f': { /* White space is ignored */ + token = tkWS; + break; + } + case '/': { /* C-style comments */ + if( zSql[1]!='*' ){ + token = tkOTHER; + break; + } + zSql += 2; + while( zSql[0] && (zSql[0]!='*' || zSql[1]!='/') ){ zSql++; } + if( zSql[0]==0 ) return 0; + zSql++; + token = tkWS; + break; + } + case '-': { /* SQL-style comments from "--" to end of line */ + if( zSql[1]!='-' ){ + token = tkOTHER; + break; + } + while( *zSql && *zSql!='\n' ){ zSql++; } + if( *zSql==0 ) return state==1; + token = tkWS; + break; + } + case '[': { /* Microsoft-style identifiers in [...] */ + zSql++; + while( *zSql && *zSql!=']' ){ zSql++; } + if( *zSql==0 ) return 0; + token = tkOTHER; + break; + } + case '`': /* Grave-accent quoted symbols used by MySQL */ + case '"': /* single- and double-quoted strings */ + case '\'': { + int c = *zSql; + zSql++; + while( *zSql && *zSql!=c ){ zSql++; } + if( *zSql==0 ) return 0; + token = tkOTHER; + break; + } + default: { +#ifdef SQLITE_EBCDIC + unsigned char c; +#endif + if( IdChar((u8)*zSql) ){ + /* Keywords and unquoted identifiers */ + int nId; + for(nId=1; IdChar(zSql[nId]); nId++){} +#ifdef SQLITE_OMIT_TRIGGER + token = tkOTHER; +#else + switch( *zSql ){ + case 'c': case 'C': { + if( nId==6 && sqlite3StrNICmp(zSql, "create", 6)==0 ){ + token = tkCREATE; + }else{ + token = tkOTHER; + } + break; + } + case 't': case 'T': { + if( nId==7 && sqlite3StrNICmp(zSql, "trigger", 7)==0 ){ + token = tkTRIGGER; + }else if( nId==4 && sqlite3StrNICmp(zSql, "temp", 4)==0 ){ + token = tkTEMP; + }else if( nId==9 && sqlite3StrNICmp(zSql, "temporary", 9)==0 ){ + token = tkTEMP; + }else{ + token = tkOTHER; + } + break; + } + case 'e': case 'E': { + if( nId==3 && sqlite3StrNICmp(zSql, "end", 3)==0 ){ + token = tkEND; + }else +#ifndef SQLITE_OMIT_EXPLAIN + if( nId==7 && sqlite3StrNICmp(zSql, "explain", 7)==0 ){ + token = tkEXPLAIN; + }else +#endif + { + token = tkOTHER; + } + break; + } + default: { + token = tkOTHER; + break; + } + } +#endif /* SQLITE_OMIT_TRIGGER */ + zSql += nId-1; + }else{ + /* Operators and special symbols */ + token = tkOTHER; + } + break; + } + } + state = trans[state][token]; + zSql++; + } + return state==1; +} + +#ifndef SQLITE_OMIT_UTF16 +/* +** This routine is the same as the sqlite3_complete() routine described +** above, except that the parameter is required to be UTF-16 encoded, not +** UTF-8. +*/ +int sqlite3_complete16(const void *zSql){ + sqlite3_value *pVal; + char const *zSql8; + int rc = SQLITE_NOMEM; + +#ifndef SQLITE_OMIT_AUTOINIT + rc = sqlite3_initialize(); + if( rc ) return rc; +#endif + pVal = sqlite3ValueNew(0); + sqlite3ValueSetStr(pVal, -1, zSql, SQLITE_UTF16NATIVE, SQLITE_STATIC); + zSql8 = sqlite3ValueText(pVal, SQLITE_UTF8); + if( zSql8 ){ + rc = sqlite3_complete(zSql8); + }else{ + rc = SQLITE_NOMEM; + } + sqlite3ValueFree(pVal); + return sqlite3ApiExit(0, rc); +} +#endif /* SQLITE_OMIT_UTF16 */ +#endif /* SQLITE_OMIT_COMPLETE */ Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -362,11 +362,11 @@ /* Collect rowids of every row to be deleted. */ sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere,0,WHERE_DUPLICATES_OK); if( pWInfo==0 ) goto delete_from_cleanup; - regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid, 0); + regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid); sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid); if( db->flags & SQLITE_CountRows ){ sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); } sqlite3WhereEnd(pWInfo); @@ -628,10 +628,9 @@ } } if( doMakeRec ){ sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol+1, regOut); sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0); - sqlite3ExprCacheAffinityChange(pParse, regBase, nCol+1); } sqlite3ReleaseTempRange(pParse, regBase, nCol+1); return regBase; } Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -225,34 +225,10 @@ } } return pColl; } -/* -** Generate the operands for a comparison operation. Before -** generating the code for each operand, set the EP_AnyAff -** flag on the expression so that it will be able to used a -** cached column value that has previously undergone an -** affinity change. -*/ -static void codeCompareOperands( - Parse *pParse, /* Parsing and code generating context */ - Expr *pLeft, /* The left operand */ - int *pRegLeft, /* Register where left operand is stored */ - int *pFreeLeft, /* Free this register when done */ - Expr *pRight, /* The right operand */ - int *pRegRight, /* Register where right operand is stored */ - int *pFreeRight /* Write temp register for right operand there */ -){ - while( pLeft->op==TK_UPLUS ) pLeft = pLeft->pLeft; - pLeft->flags |= EP_AnyAff; - *pRegLeft = sqlite3ExprCodeTemp(pParse, pLeft, pFreeLeft); - while( pRight->op==TK_UPLUS ) pRight = pRight->pLeft; - pRight->flags |= EP_AnyAff; - *pRegRight = sqlite3ExprCodeTemp(pParse, pRight, pFreeRight); -} - /* ** Generate code for a comparison operator. */ static int codeCompare( Parse *pParse, /* The parsing (and code generating) context */ @@ -1980,30 +1956,43 @@ struct yColCache *p; assert( iReg>0 ); /* Register numbers are always positive */ assert( iCol>=-1 && iCol<32768 ); /* Finite column numbers */ - /* First replace any existing entry */ + /* The SQLITE_ColumnCache flag disables the column cache. This is used + ** for testing only - to verify that SQLite always gets the same answer + ** with and without the column cache. + */ + if( pParse->db->flags & SQLITE_ColumnCache ) return; + + /* First replace any existing entry. + ** + ** Actually, the way the column cache is currently used, we are guaranteed + ** that the object will never already be in cache. Verify this guarantee. + */ +#ifndef NDEBUG for(i=0, p=pParse->aColCache; iiReg && p->iTable==iTab && p->iColumn==iCol ){ cacheEntryClear(pParse, p); p->iLevel = pParse->iCacheLevel; p->iReg = iReg; - p->affChange = 0; p->lru = pParse->iCacheCnt++; return; } +#endif + assert( p->iReg==0 || p->iTable!=iTab || p->iColumn!=iCol ); } +#endif /* Find an empty slot and replace it */ for(i=0, p=pParse->aColCache; iiReg==0 ){ p->iLevel = pParse->iCacheLevel; p->iTable = iTab; p->iColumn = iCol; p->iReg = iReg; - p->affChange = 0; p->tempReg = 0; p->lru = pParse->iCacheCnt++; return; } } @@ -2021,26 +2010,27 @@ p = &pParse->aColCache[idxLru]; p->iLevel = pParse->iCacheLevel; p->iTable = iTab; p->iColumn = iCol; p->iReg = iReg; - p->affChange = 0; p->tempReg = 0; p->lru = pParse->iCacheCnt++; return; } } /* -** Indicate that a register is being overwritten. Purge the register -** from the column cache. +** Indicate that registers between iReg..iReg+nReg-1 are being overwritten. +** Purge the range of registers from the column cache. */ -void sqlite3ExprCacheRemove(Parse *pParse, int iReg){ +void sqlite3ExprCacheRemove(Parse *pParse, int iReg, int nReg){ int i; + int iLast = iReg + nReg - 1; struct yColCache *p; for(i=0, p=pParse->aColCache; iiReg==iReg ){ + int r = p->iReg; + if( r>=iReg && r<=iLast ){ cacheEntryClear(pParse, p); p->iReg = 0; } } } @@ -2095,32 +2085,24 @@ ** is made to store the column value in register iReg, but this is ** not guaranteed. The location of the column value is returned. ** ** There must be an open cursor to pTab in iTable when this routine ** is called. If iColumn<0 then code is generated that extracts the rowid. -** -** This routine might attempt to reuse the value of the column that -** has already been loaded into a register. The value will always -** be used if it has not undergone any affinity changes. But if -** an affinity change has occurred, then the cached value will only be -** used if allowAffChng is true. */ int sqlite3ExprCodeGetColumn( Parse *pParse, /* Parsing and code generating context */ Table *pTab, /* Description of the table we are reading from */ int iColumn, /* Index of the table column */ int iTable, /* The cursor pointing to the table */ - int iReg, /* Store results here */ - int allowAffChng /* True if prior affinity changes are OK */ + int iReg /* Store results here */ ){ Vdbe *v = pParse->pVdbe; int i; struct yColCache *p; for(i=0, p=pParse->aColCache; iiReg>0 && p->iTable==iTable && p->iColumn==iColumn - && (!p->affChange || allowAffChng) ){ + if( p->iReg>0 && p->iTable==iTable && p->iColumn==iColumn ){ p->lru = pParse->iCacheCnt++; sqlite3ExprCachePinRegister(pParse, p->iReg); return p->iReg; } } @@ -2154,19 +2136,11 @@ /* ** Record the fact that an affinity change has occurred on iCount ** registers starting with iStart. */ void sqlite3ExprCacheAffinityChange(Parse *pParse, int iStart, int iCount){ - int iEnd = iStart + iCount - 1; - int i; - struct yColCache *p; - for(i=0, p=pParse->aColCache; iiReg; - if( r>=iStart && r<=iEnd ){ - p->affChange = 1; - } - } + sqlite3ExprCacheRemove(pParse, iStart, iCount); } /* ** Generate code to move content from registers iFrom...iFrom+nReg-1 ** over to iTo..iTo+nReg-1. Keep the column cache up-to-date. @@ -2194,23 +2168,28 @@ for(i=0; ipVdbe, OP_Copy, iFrom+i, iTo+i); } } +#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST) /* ** Return true if any register in the range iFrom..iTo (inclusive) ** is used as part of the column cache. +** +** This routine is used within assert() and testcase() macros only +** and does not appear in a normal build. */ static int usedAsColumnCache(Parse *pParse, int iFrom, int iTo){ int i; struct yColCache *p; for(i=0, p=pParse->aColCache; iiReg; - if( r>=iFrom && r<=iTo ) return 1; + if( r>=iFrom && r<=iTo ) return 1; /*NO_TEST*/ } return 0; } +#endif /* SQLITE_DEBUG || SQLITE_COVERAGE_TEST */ /* ** If the last instruction coded is an ephemeral copy of any of ** the registers in the nReg registers beginning with iReg, then ** convert the last instruction from OP_SCopy to OP_Copy. @@ -2327,14 +2306,12 @@ if( pExpr->iTable<0 ){ /* This only happens when coding check constraints */ assert( pParse->ckBase>0 ); inReg = pExpr->iColumn + pParse->ckBase; }else{ - testcase( (pExpr->flags & EP_AnyAff)!=0 ); inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab, - pExpr->iColumn, pExpr->iTable, target, - pExpr->flags & EP_AnyAff); + pExpr->iColumn, pExpr->iTable, target); } break; } case TK_INTEGER: { codeInteger(v, pExpr, 0, target); @@ -2447,12 +2424,12 @@ testcase( op==TK_LE ); testcase( op==TK_GT ); testcase( op==TK_GE ); testcase( op==TK_EQ ); testcase( op==TK_NE ); - codeCompareOperands(pParse, pExpr->pLeft, &r1, ®Free1, - pExpr->pRight, &r2, ®Free2); + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, inReg, SQLITE_STOREP2); testcase( regFree1==0 ); testcase( regFree2==0 ); break; @@ -2459,12 +2436,12 @@ } case TK_IS: case TK_ISNOT: { testcase( op==TK_IS ); testcase( op==TK_ISNOT ); - codeCompareOperands(pParse, pExpr->pLeft, &r1, ®Free1, - pExpr->pRight, &r2, ®Free2); + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); op = (op==TK_IS) ? TK_EQ : TK_NE; codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, inReg, SQLITE_STOREP2 | SQLITE_NULLEQ); testcase( regFree1==0 ); testcase( regFree2==0 ); @@ -2604,11 +2581,11 @@ int endCoalesce = sqlite3VdbeMakeLabel(v); assert( nFarg>=2 ); sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target); for(i=1; ia[i].pExpr, target); sqlite3ExprCachePop(pParse, 1); } sqlite3VdbeResolveLabel(v, endCoalesce); @@ -2659,11 +2636,10 @@ (char*)pDef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, (u8)nFarg); if( nFarg ){ sqlite3ReleaseTempRange(pParse, r1, nFarg); } - sqlite3ExprCacheAffinityChange(pParse, r1, nFarg); break; } #ifndef SQLITE_OMIT_SUBQUERY case TK_EXISTS: case TK_SELECT: { @@ -2700,12 +2676,12 @@ case TK_BETWEEN: { Expr *pLeft = pExpr->pLeft; struct ExprList_item *pLItem = pExpr->x.pList->a; Expr *pRight = pLItem->pExpr; - codeCompareOperands(pParse, pLeft, &r1, ®Free1, - pRight, &r2, ®Free2); + r1 = sqlite3ExprCodeTemp(pParse, pLeft, ®Free1); + r2 = sqlite3ExprCodeTemp(pParse, pRight, ®Free2); testcase( regFree1==0 ); testcase( regFree2==0 ); r3 = sqlite3GetTempReg(pParse); r4 = sqlite3GetTempReg(pParse); codeCompare(pParse, pLeft, pRight, OP_Ge, @@ -3236,12 +3212,12 @@ testcase( op==TK_GT ); testcase( op==TK_GE ); testcase( op==TK_EQ ); testcase( op==TK_NE ); testcase( jumpIfNull==0 ); - codeCompareOperands(pParse, pExpr->pLeft, &r1, ®Free1, - pExpr->pRight, &r2, ®Free2); + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, dest, jumpIfNull); testcase( regFree1==0 ); testcase( regFree2==0 ); break; @@ -3248,12 +3224,12 @@ } case TK_IS: case TK_ISNOT: { testcase( op==TK_IS ); testcase( op==TK_ISNOT ); - codeCompareOperands(pParse, pExpr->pLeft, &r1, ®Free1, - pExpr->pRight, &r2, ®Free2); + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); op = (op==TK_IS) ? TK_EQ : TK_NE; codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, dest, SQLITE_NULLEQ); testcase( regFree1==0 ); testcase( regFree2==0 ); @@ -3379,12 +3355,12 @@ testcase( op==TK_GT ); testcase( op==TK_GE ); testcase( op==TK_EQ ); testcase( op==TK_NE ); testcase( jumpIfNull==0 ); - codeCompareOperands(pParse, pExpr->pLeft, &r1, ®Free1, - pExpr->pRight, &r2, ®Free2); + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, dest, jumpIfNull); testcase( regFree1==0 ); testcase( regFree2==0 ); break; @@ -3391,12 +3367,12 @@ } case TK_IS: case TK_ISNOT: { testcase( pExpr->op==TK_IS ); testcase( pExpr->op==TK_ISNOT ); - codeCompareOperands(pParse, pExpr->pLeft, &r1, ®Free1, - pExpr->pRight, &r2, ®Free2); + r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); + r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); op = (pExpr->op==TK_IS) ? TK_NE : TK_EQ; codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, dest, SQLITE_NULLEQ); testcase( regFree1==0 ); testcase( regFree2==0 ); @@ -3739,20 +3715,22 @@ */ int sqlite3GetTempRange(Parse *pParse, int nReg){ int i, n; i = pParse->iRangeReg; n = pParse->nRangeReg; - if( nReg<=n && !usedAsColumnCache(pParse, i, i+n-1) ){ + if( nReg<=n ){ + assert( !usedAsColumnCache(pParse, i, i+n-1) ); pParse->iRangeReg += nReg; pParse->nRangeReg -= nReg; }else{ i = pParse->nMem+1; pParse->nMem += nReg; } return i; } void sqlite3ReleaseTempRange(Parse *pParse, int iReg, int nReg){ + sqlite3ExprCacheRemove(pParse, iReg, nReg); if( nReg>pParse->nRangeReg ){ pParse->nRangeReg = nReg; pParse->iRangeReg = iReg; } } Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -2303,11 +2303,45 @@ sqlite3_mutex_enter(db->mutex); sqlite3BtreeSetPageSize(db->aDb[0].pBt, 0, x, 0); sqlite3_mutex_leave(db->mutex); break; } + + /* sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, sqlite3 *db, int N) + ** + ** Enable or disable various optimizations for testing purposes. The + ** argument N is a bitmask of optimizations to be disabled. For normal + ** operation N should be 0. The idea is that a test program (like the + ** SQL Logic Test or SLT test module) can run the same SQL multiple times + ** with various optimizations disabled to verify that the same answer + ** is obtained in every case. + */ + case SQLITE_TESTCTRL_OPTIMIZATIONS: { + sqlite3 *db = va_arg(ap, sqlite3*); + int x = va_arg(ap,int); + db->flags = (x & SQLITE_OptMask) | (db->flags & ~SQLITE_OptMask); + break; + } + +#ifdef SQLITE_N_KEYWORD + /* sqlite3_test_control(SQLITE_TESTCTRL_ISKEYWORD, const char *zWord) + ** + ** If zWord is a keyword recognized by the parser, then return the + ** number of keywords. Or if zWord is not a keyword, return 0. + ** + ** This test feature is only available in the amalgamation since + ** the SQLITE_N_KEYWORD macro is not defined in this file if SQLite + ** is built using separate source files. + */ + case SQLITE_TESTCTRL_ISKEYWORD: { + const char *zWord = va_arg(ap, const char*); + int n = sqlite3Strlen30(zWord); + rc = (sqlite3KeywordCode((u8*)zWord, n)!=TK_ID) ? SQLITE_N_KEYWORD : 0; + break; + } +#endif } va_end(ap); #endif /* SQLITE_OMIT_BUILTIN_TEST */ return rc; } Index: src/mem2.c ================================================================== --- src/mem2.c +++ src/mem2.c @@ -207,10 +207,35 @@ ** Round up a request size to the next valid allocation size. */ static int sqlite3MemRoundup(int n){ return ROUND8(n); } + +/* +** Fill a buffer with pseudo-random bytes. This is used to preset +** the content of a new memory allocation to unpredictable values and +** to clear the content of a freed allocation to unpredictable values. +*/ +static void randomFill(char *pBuf, int nByte){ + unsigned int x, y, r; + x = SQLITE_PTR_TO_INT(pBuf); + y = nByte | 1; + while( nByte >= 4 ){ + x = (x>>1) ^ (-(x&1) & 0xd0000001); + y = y*1103515245 + 12345; + r = x ^ y; + *(int*)pBuf = r; + pBuf += 4; + nByte -= 4; + } + while( nByte-- > 0 ){ + x = (x>>1) ^ (-(x&1) & 0xd0000001); + y = y*1103515245 + 12345; + r = x ^ y; + *(pBuf++) = r & 0xff; + } +} /* ** Allocate nByte bytes of memory. */ static void *sqlite3MemMalloc(int nByte){ @@ -258,11 +283,12 @@ } pHdr->iSize = nByte; adjustStats(nByte, +1); pInt = (int*)&pHdr[1]; pInt[nReserve/sizeof(int)] = REARGUARD; - memset(pInt, 0x65, nReserve); + randomFill((char*)pInt, nByte); + memset(((char*)pInt)+nByte, 0x65, nReserve-nByte); p = (void*)pInt; } sqlite3_mutex_leave(mem.mutex); return p; } @@ -294,12 +320,12 @@ mem.pLast = pHdr->pPrev; } z = (char*)pBt; z -= pHdr->nTitle; adjustStats(pHdr->iSize, -1); - memset(z, 0x2b, sizeof(void*)*pHdr->nBacktraceSlots + sizeof(*pHdr) + - pHdr->iSize + sizeof(int) + pHdr->nTitle); + randomFill(z, sizeof(void*)*pHdr->nBacktraceSlots + sizeof(*pHdr) + + pHdr->iSize + sizeof(int) + pHdr->nTitle); free(z); sqlite3_mutex_leave(mem.mutex); } /* @@ -318,11 +344,11 @@ pOldHdr = sqlite3MemsysGetHeader(pPrior); pNew = sqlite3MemMalloc(nByte); if( pNew ){ memcpy(pNew, pPrior, nByteiSize ? nByte : pOldHdr->iSize); if( nByte>pOldHdr->iSize ){ - memset(&((char*)pNew)[pOldHdr->iSize], 0x2b, nByte - pOldHdr->iSize); + randomFill(&((char*)pNew)[pOldHdr->iSize], nByte - pOldHdr->iSize); } sqlite3MemFree(pPrior); } return pNew; } Index: src/os_unix.c ================================================================== --- src/os_unix.c +++ src/os_unix.c @@ -4031,20 +4031,21 @@ ** descriptor on the same path, fail, and return an error to SQLite. ** ** Even if a subsequent open() call does succeed, the consequences of ** not searching for a resusable file descriptor are not dire. */ if( 0==stat(zPath, &sStat) ){ - struct unixOpenCnt *pO; - struct unixFileId id; - id.dev = sStat.st_dev; - id.ino = sStat.st_ino; + struct unixOpenCnt *pOpen; unixEnterMutex(); - for(pO=openList; pO && memcmp(&id, &pO->fileId, sizeof(id)); pO=pO->pNext); - if( pO ){ + pOpen = openList; + while( pOpen && (pOpen->fileId.dev!=sStat.st_dev + || pOpen->fileId.ino!=sStat.st_ino) ){ + pOpen = pOpen->pNext; + } + if( pOpen ){ UnixUnusedFd **pp; - for(pp=&pO->pUnused; *pp && (*pp)->flags!=flags; pp=&((*pp)->pNext)); + for(pp=&pOpen->pUnused; *pp && (*pp)->flags!=flags; pp=&((*pp)->pNext)); pUnused = *pp; if( pUnused ){ *pp = pUnused->pNext; } } Index: src/pager.c ================================================================== --- src/pager.c +++ src/pager.c @@ -3860,13 +3860,12 @@ testcase( rc==SQLITE_NOMEM ); } TESTONLY( rc = ) addToSavepointBitvecs(pPager, pgno); testcase( rc==SQLITE_NOMEM ); sqlite3EndBenignMalloc(); - }else{ - memset(pPg->pData, 0, pPager->pageSize); } + memset(pPg->pData, 0, pPager->pageSize); IOTRACE(("ZERO %p %d\n", pPager, pgno)); }else{ assert( pPg->pPager==pPager ); rc = readDbPage(pPg); if( rc!=SQLITE_OK ){ Index: src/prepare.c ================================================================== --- src/prepare.c +++ src/prepare.c @@ -470,11 +470,11 @@ if( rc!=SQLITE_OK ) return; openedTransaction = 1; } /* Read the schema cookie from the database. If it does not match the - ** value stored as part of the in the in-memory schema representation, + ** value stored as part of the in-memory schema representation, ** set Parse.rc to SQLITE_SCHEMA. */ sqlite3BtreeGetMeta(pBt, BTREE_SCHEMA_VERSION, (u32 *)&cookie); if( cookie!=db->aDb[iDb].pSchema->schema_cookie ){ pParse->rc = SQLITE_SCHEMA; } Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -241,22 +241,21 @@ if( pTab ){ int iCol; pSchema = pTab->pSchema; cntTab++; - if( sqlite3IsRowid(zCol) ){ - iCol = -1; - }else{ - for(iCol=0; iColnCol; iCol++){ - Column *pCol = &pTab->aCol[iCol]; - if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ - if( iCol==pTab->iPKey ){ - iCol = -1; - } - break; + for(iCol=0; iColnCol; iCol++){ + Column *pCol = &pTab->aCol[iCol]; + if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ + if( iCol==pTab->iPKey ){ + iCol = -1; } + break; } + } + if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) ){ + iCol = -1; /* IMP: R-44911-55124 */ } if( iColnCol ){ cnt++; if( iCol<0 ){ pExpr->affinity = SQLITE_AFF_INTEGER; @@ -280,11 +279,11 @@ /* ** Perhaps the name is a reference to the ROWID */ if( cnt==0 && cntTab==1 && sqlite3IsRowid(zCol) ){ cnt = 1; - pExpr->iColumn = -1; + pExpr->iColumn = -1; /* IMP: R-44911-55124 */ pExpr->affinity = SQLITE_AFF_INTEGER; } /* ** If the input is of the form Z (not Y.Z or X.Y.Z) then the name Z Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -955,11 +955,11 @@ if( pS ){ /* The "table" is actually a sub-select or a view in the FROM clause ** of the SELECT statement. Return the declaration type and origin ** data for the result-set column of the sub-select. */ - if( ALWAYS(iCol>=0 && iColpEList->nExpr) ){ + if( iCol>=0 && ALWAYS(iColpEList->nExpr) ){ /* If iCol is less than zero, then the expression requests the ** rowid of the sub-select or view. This expression is legal (see ** test case misc2.2.2) - it always evaluates to NULL. */ NameContext sNC; @@ -2516,11 +2516,11 @@ ** (10) The subquery does not use aggregates or the outer query does not ** use LIMIT. ** ** (11) The subquery and the outer query do not both have ORDER BY clauses. ** -** (12) Not implemented. Subsumed into restriction (3). Was previously +** (**) Not implemented. Subsumed into restriction (3). Was previously ** a separate restriction deriving from ticket #350. ** ** (13) The subquery and outer query do not both use LIMIT ** ** (14) The subquery does not use OFFSET @@ -2590,10 +2590,11 @@ /* Check to see if flattening is permitted. Return 0 if not. */ assert( p!=0 ); assert( p->pPrior==0 ); /* Unable to flatten compound queries */ + if( db->flags & SQLITE_QueryFlattener ) return 0; pSrc = p->pSrc; assert( pSrc && iFrom>=0 && iFromnSrc ); pSubitem = &pSrc->a[iFrom]; iParent = pSubitem->iCursor; pSub = pSubitem->pSelect; @@ -3487,12 +3488,12 @@ sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ); } sqlite3VdbeAddOp4(v, OP_AggStep, 0, regAgg, pF->iMem, (void*)pF->pFunc, P4_FUNCDEF); sqlite3VdbeChangeP5(v, (u8)nArg); - sqlite3ReleaseTempRange(pParse, regAgg, nArg); sqlite3ExprCacheAffinityChange(pParse, regAgg, nArg); + sqlite3ReleaseTempRange(pParse, regAgg, nArg); if( addrNext ){ sqlite3VdbeResolveLabel(v, addrNext); sqlite3ExprCacheClear(pParse); } } @@ -3914,11 +3915,11 @@ if( pCol->iSorterColumn>=j ){ int r1 = j + regBase; int r2; r2 = sqlite3ExprCodeGetColumn(pParse, - pCol->pTab, pCol->iColumn, pCol->iTable, r1, 0); + pCol->pTab, pCol->iColumn, pCol->iTable, r1); if( r1!=r2 ){ sqlite3VdbeAddOp2(v, OP_SCopy, r2, r1); } j++; } Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -1210,10 +1210,11 @@ break; } while( zLine[n] ){ n++; } if( n>0 && zLine[n-1]=='\n' ){ n--; + if( n>0 && zLine[n-1]=='\r' ) n--; zLine[n] = 0; eol = 1; } } zLine = realloc( zLine, n+1 ); @@ -1830,14 +1831,13 @@ int (*xCallback)(void*,int,char**,char**,int*), /* Callback function */ /* (not the same as sqlite3_exec) */ struct callback_data *pArg, /* Pointer to struct callback_data */ char **pzErrMsg /* Error msg written here */ ){ - sqlite3_stmt *pStmt = NULL; - int rc = SQLITE_OK; - int rc2; - const char *zLeftover; /* Tail of unprocessed SQL */ + sqlite3_stmt *pStmt = NULL; /* Statement to execute. */ + int rc = SQLITE_OK; /* Return Code */ + const char *zLeftover; /* Tail of unprocessed SQL */ if( pzErrMsg ){ *pzErrMsg = NULL; } @@ -1914,32 +1914,19 @@ rc = sqlite3_step(pStmt); } while( rc == SQLITE_ROW ); } } - /* if the last sqlite3_step() didn't complete successfully... */ - if( (SQLITE_OK != rc) && (SQLITE_DONE != rc) ){ - if( pzErrMsg ){ - *pzErrMsg = save_err_msg(db); - } - }else{ - rc = SQLITE_OK; - } - - rc2 = sqlite3_finalize(pStmt); - /* if the last sqlite3_finalize() didn't complete successfully - ** AND we don't have a saved error from sqlite3_step ... */ - if( (SQLITE_OK != rc2) && (SQLITE_OK == rc) ){ - rc = rc2; - if( pzErrMsg ){ - *pzErrMsg = save_err_msg(db); - } - } - - if( SQLITE_OK == rc ){ + /* Finalize the statement just executed. If this fails, save a + ** copy of the error message. Otherwise, set zSql to point to the + ** next statement to execute. */ + rc = sqlite3_finalize(pStmt); + if( rc==SQLITE_OK ){ zSql = zLeftover; while( isspace(zSql[0]) ) zSql++; + }else if( pzErrMsg ){ + *pzErrMsg = save_err_msg(db); } } } /* end while */ return rc; Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -1559,11 +1559,11 @@ ** will also set or clear the busy handler. ** ** The busy callback should not take any actions which modify the ** database connection that invoked the busy handler. Any such actions ** result in undefined behavior. -** +** ** A busy handler must not close the database connection ** or [prepared statement] that invoked the busy handler. */ int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*); @@ -4922,10 +4922,11 @@ ** These parameters and their meanings are subject to change ** without notice. These values are for testing purposes only. ** Applications should not use any of these parameters or the ** [sqlite3_test_control()] interface. */ +#define SQLITE_TESTCTRL_FIRST 5 #define SQLITE_TESTCTRL_PRNG_SAVE 5 #define SQLITE_TESTCTRL_PRNG_RESTORE 6 #define SQLITE_TESTCTRL_PRNG_RESET 7 #define SQLITE_TESTCTRL_BITVEC_TEST 8 #define SQLITE_TESTCTRL_FAULT_INSTALL 9 @@ -4932,10 +4933,13 @@ #define SQLITE_TESTCTRL_BENIGN_MALLOC_HOOKS 10 #define SQLITE_TESTCTRL_PENDING_BYTE 11 #define SQLITE_TESTCTRL_ASSERT 12 #define SQLITE_TESTCTRL_ALWAYS 13 #define SQLITE_TESTCTRL_RESERVE 14 +#define SQLITE_TESTCTRL_OPTIMIZATIONS 15 +#define SQLITE_TESTCTRL_ISKEYWORD 16 +#define SQLITE_TESTCTRL_LAST 16 /* ** CAPI3REF: SQLite Runtime Status ** EXPERIMENTAL ** Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -896,41 +896,47 @@ ** A macro to discover the encoding of a database. */ #define ENC(db) ((db)->aDb[0].pSchema->enc) /* -** Possible values for the sqlite.flags and or Db.flags fields. -** -** On sqlite.flags, the SQLITE_InTrans value means that we have -** executed a BEGIN. On Db.flags, SQLITE_InTrans means a statement -** transaction is active on that particular database file. +** Possible values for the sqlite3.flags. */ -#define SQLITE_VdbeTrace 0x00000001 /* True to trace VDBE execution */ -#define SQLITE_InTrans 0x00000008 /* True if in a transaction */ -#define SQLITE_InternChanges 0x00000010 /* Uncommitted Hash table changes */ -#define SQLITE_FullColNames 0x00000020 /* Show full column names on SELECT */ -#define SQLITE_ShortColNames 0x00000040 /* Show short columns names */ -#define SQLITE_CountRows 0x00000080 /* Count rows changed by INSERT, */ +#define SQLITE_VdbeTrace 0x00000100 /* True to trace VDBE execution */ +#define SQLITE_InternChanges 0x00000200 /* Uncommitted Hash table changes */ +#define SQLITE_FullColNames 0x00000400 /* Show full column names on SELECT */ +#define SQLITE_ShortColNames 0x00000800 /* Show short columns names */ +#define SQLITE_CountRows 0x00001000 /* Count rows changed by INSERT, */ /* DELETE, or UPDATE and return */ /* the count using a callback. */ -#define SQLITE_NullCallback 0x00000100 /* Invoke the callback once if the */ +#define SQLITE_NullCallback 0x00002000 /* Invoke the callback once if the */ /* result set is empty */ -#define SQLITE_SqlTrace 0x00000200 /* Debug print SQL as it executes */ -#define SQLITE_VdbeListing 0x00000400 /* Debug listings of VDBE programs */ -#define SQLITE_WriteSchema 0x00000800 /* OK to update SQLITE_MASTER */ -#define SQLITE_NoReadlock 0x00001000 /* Readlocks are omitted when +#define SQLITE_SqlTrace 0x00004000 /* Debug print SQL as it executes */ +#define SQLITE_VdbeListing 0x00008000 /* Debug listings of VDBE programs */ +#define SQLITE_WriteSchema 0x00010000 /* OK to update SQLITE_MASTER */ +#define SQLITE_NoReadlock 0x00020000 /* Readlocks are omitted when ** accessing read-only databases */ -#define SQLITE_IgnoreChecks 0x00002000 /* Do not enforce check constraints */ -#define SQLITE_ReadUncommitted 0x00004000 /* For shared-cache mode */ -#define SQLITE_LegacyFileFmt 0x00008000 /* Create new databases in format 1 */ -#define SQLITE_FullFSync 0x00010000 /* Use full fsync on the backend */ -#define SQLITE_LoadExtension 0x00020000 /* Enable load_extension */ +#define SQLITE_IgnoreChecks 0x00040000 /* Do not enforce check constraints */ +#define SQLITE_ReadUncommitted 0x0080000 /* For shared-cache mode */ +#define SQLITE_LegacyFileFmt 0x00100000 /* Create new databases in format 1 */ +#define SQLITE_FullFSync 0x00200000 /* Use full fsync on the backend */ +#define SQLITE_LoadExtension 0x00400000 /* Enable load_extension */ +#define SQLITE_RecoveryMode 0x00800000 /* Ignore schema errors */ +#define SQLITE_ReverseOrder 0x01000000 /* Reverse unordered SELECTs */ +#define SQLITE_RecTriggers 0x02000000 /* Enable recursive triggers */ +#define SQLITE_ForeignKeys 0x04000000 /* Enforce foreign key constraints */ -#define SQLITE_RecoveryMode 0x00040000 /* Ignore schema errors */ -#define SQLITE_ReverseOrder 0x00100000 /* Reverse unordered SELECTs */ -#define SQLITE_RecTriggers 0x00200000 /* Enable recursive triggers */ -#define SQLITE_ForeignKeys 0x00400000 /* Enforce foreign key constraints */ +/* +** Bits of the sqlite3.flags field that are used by the +** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface. +** These must be the low-order bits of the flags field. +*/ +#define SQLITE_QueryFlattener 0x01 /* Disable query flattening */ +#define SQLITE_ColumnCache 0x02 /* Disable the column cache */ +#define SQLITE_IndexSort 0x04 /* Disable indexes for sorting */ +#define SQLITE_IndexSearch 0x08 /* Disable indexes for searching */ +#define SQLITE_IndexCover 0x10 /* Disable index covering table */ +#define SQLITE_OptMask 0x1f /* Mask of all disablable opts */ /* ** Possible values for the sqlite.magic field. ** The numbers are obtained at random and have no special meaning, other ** than being distinct from one another. @@ -1640,18 +1646,17 @@ #define EP_Distinct 0x0010 /* Aggregate function with DISTINCT keyword */ #define EP_VarSelect 0x0020 /* pSelect is correlated, not constant */ #define EP_DblQuoted 0x0040 /* token.z was originally in "..." */ #define EP_InfixFunc 0x0080 /* True for an infix function: LIKE, GLOB, etc */ #define EP_ExpCollate 0x0100 /* Collating sequence specified explicitly */ -#define EP_AnyAff 0x0200 /* Can take a cached column of any affinity */ -#define EP_FixedDest 0x0400 /* Result needed in a specific register */ -#define EP_IntValue 0x0800 /* Integer value contained in u.iValue */ -#define EP_xIsSelect 0x1000 /* x.pSelect is valid (otherwise x.pList is) */ +#define EP_FixedDest 0x0200 /* Result needed in a specific register */ +#define EP_IntValue 0x0400 /* Integer value contained in u.iValue */ +#define EP_xIsSelect 0x0800 /* x.pSelect is valid (otherwise x.pList is) */ -#define EP_Reduced 0x2000 /* Expr struct is EXPR_REDUCEDSIZE bytes only */ -#define EP_TokenOnly 0x4000 /* Expr struct is EXPR_TOKENONLYSIZE bytes only */ -#define EP_Static 0x8000 /* Held in memory not obtained from malloc() */ +#define EP_Reduced 0x1000 /* Expr struct is EXPR_REDUCEDSIZE bytes only */ +#define EP_TokenOnly 0x2000 /* Expr struct is EXPR_TOKENONLYSIZE bytes only */ +#define EP_Static 0x4000 /* Held in memory not obtained from malloc() */ /* ** The following are the meanings of bits in the Expr.flags2 field. */ #define EP2_MallocedToken 0x0001 /* Need to sqlite3DbFree() Expr.zToken */ @@ -2125,11 +2130,10 @@ u8 nColCache; /* Number of entries in the column cache */ u8 iColCache; /* Next entry of the cache to replace */ struct yColCache { int iTable; /* Table cursor number */ int iColumn; /* Table column number */ - u8 affChange; /* True if this register has had an affinity change */ u8 tempReg; /* iReg is a temp register that needs to be freed */ int iLevel; /* Nesting level */ int iReg; /* Reg with value of this column. 0 means none. */ int lru; /* Least recently used entry has the smallest value */ } aColCache[SQLITE_N_COLCACHE]; /* One for each column cache entry */ @@ -2644,17 +2648,17 @@ #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**, u16); void sqlite3WhereEnd(WhereInfo*); -int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, int); +int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int); void sqlite3ExprCodeMove(Parse*, int, int, int); void sqlite3ExprCodeCopy(Parse*, int, int, int); void sqlite3ExprCacheStore(Parse*, int, int, int); void sqlite3ExprCachePush(Parse*); void sqlite3ExprCachePop(Parse*, int); -void sqlite3ExprCacheRemove(Parse*, int); +void sqlite3ExprCacheRemove(Parse*, int, int); void sqlite3ExprCacheClear(Parse*); void sqlite3ExprCacheAffinityChange(Parse*, int, int); void sqlite3ExprHardCopy(Parse*,int,int); int sqlite3ExprCode(Parse*, Expr*, int); int sqlite3ExprCodeTemp(Parse*, Expr*, int*); Index: src/vacuum.c ================================================================== --- src/vacuum.c +++ src/vacuum.c @@ -84,10 +84,11 @@ Btree *pTemp; /* The temporary database we vacuum into */ char *zSql = 0; /* SQL statements */ int saved_flags; /* Saved value of the db->flags */ int saved_nChange; /* Saved value of db->nChange */ int saved_nTotalChange; /* Saved value of db->nTotalChange */ + void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */ Db *pDb = 0; /* Database to detach at end of vacuum */ int isMemDb; /* True if vacuuming a :memory: database */ int nRes; if( !db->autoCommit ){ @@ -99,12 +100,14 @@ ** restored before returning. Then set the writable-schema flag, and ** disable CHECK and foreign key constraints. */ saved_flags = db->flags; saved_nChange = db->nChange; saved_nTotalChange = db->nTotalChange; + saved_xTrace = db->xTrace; db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks; db->flags &= ~SQLITE_ForeignKeys; + db->xTrace = 0; pMain = db->aDb[0].pBt; isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain)); /* Attach the temporary database as 'vacuum_db'. The synchronous pragma @@ -281,10 +284,11 @@ end_of_vacuum: /* Restore the original value of db->flags */ db->flags = saved_flags; db->nChange = saved_nChange; db->nTotalChange = saved_nTotalChange; + db->xTrace = saved_xTrace; /* Currently there is an SQL level transaction open on the vacuum ** database. No locks are held on any other files (since the main file ** was committed at the btree level). So it safe to end the transaction ** by manually setting the autoCommit flag to true and detaching the Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -3661,19 +3661,19 @@ rc = sqlite3BtreeLast(pC->pCursor, &res); if( rc!=SQLITE_OK ){ goto abort_due_to_error; } if( res ){ - v = 1; + v = 1; /* IMP: R-61914-48074 */ }else{ assert( sqlite3BtreeCursorIsValid(pC->pCursor) ); rc = sqlite3BtreeKeySize(pC->pCursor, &v); assert( rc==SQLITE_OK ); /* Cannot fail following BtreeLast() */ if( v==MAX_ROWID ){ pC->useRandomRowid = 1; }else{ - v++; + v++; /* IMP: R-29538-34987 */ } } } #ifndef SQLITE_OMIT_AUTOINCREMENT @@ -3693,11 +3693,11 @@ REGISTER_TRACE(pOp->p3, pMem); sqlite3VdbeMemIntegerify(pMem); assert( (pMem->flags & MEM_Int)!=0 ); /* mem(P3) holds an integer */ if( pMem->u.i==MAX_ROWID || pC->useRandomRowid ){ - rc = SQLITE_FULL; + rc = SQLITE_FULL; /* IMP: R-12275-61338 */ goto abort_due_to_error; } if( vu.i+1 ){ v = pMem->u.i + 1; } @@ -3706,10 +3706,15 @@ #endif sqlite3BtreeSetCachedRowid(pC->pCursor, vuseRandomRowid ){ + /* IMPLEMENTATION-OF: R-48598-02938 If the largest ROWID is equal to the + ** largest possible integer (9223372036854775807) then the database + ** engine starts picking candidate ROWIDs at random until it finds one + ** that is not previously used. + */ assert( pOp->p3==0 ); /* We cannot be in random rowid mode if this is ** an AUTOINCREMENT table. */ v = db->lastRowid; cnt = 0; do{ @@ -3721,11 +3726,11 @@ } rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, 0, (u64)v, 0, &res); cnt++; }while( cnt<100 && rc==SQLITE_OK && res==0 ); if( rc==SQLITE_OK && res==0 ){ - rc = SQLITE_FULL; + rc = SQLITE_FULL; /* IMP: R-38219-53002 */ goto abort_due_to_error; } } pC->rowidIsValid = 0; pC->deferredMoveto = 0; Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -1039,22 +1039,25 @@ ** ** When p->explain==1, each instruction is listed. When ** p->explain==2, only OP_Explain instructions are listed and these ** are shown in a different format. p->explain==2 is used to implement ** EXPLAIN QUERY PLAN. +** +** When p->explain==1, first the main program is listed, then each of +** the trigger subprograms are listed one by one. */ int sqlite3VdbeList( Vdbe *p /* The VDBE */ ){ - int nRow; /* Total number of rows to return */ + int nRow; /* Stop when row count reaches this */ int nSub = 0; /* Number of sub-vdbes seen so far */ SubProgram **apSub = 0; /* Array of sub-vdbes */ - Mem *pSub = 0; - sqlite3 *db = p->db; - int i; - int rc = SQLITE_OK; - Mem *pMem = p->pResultSet = &p->aMem[1]; + Mem *pSub = 0; /* Memory cell hold array of subprogs */ + sqlite3 *db = p->db; /* The database connection */ + int i; /* Loop counter */ + int rc = SQLITE_OK; /* Return code */ + Mem *pMem = p->pResultSet = &p->aMem[1]; /* First Mem of result set */ assert( p->explain ); assert( p->magic==VDBE_MAGIC_RUN ); assert( db->magic==SQLITE_MAGIC_BUSY ); assert( p->rc==SQLITE_OK || p->rc==SQLITE_BUSY || p->rc==SQLITE_NOMEM ); @@ -1070,16 +1073,28 @@ ** sqlite3_column_text16() failed. */ db->mallocFailed = 1; return SQLITE_ERROR; } - /* Figure out total number of rows that will be returned by this - ** EXPLAIN program. */ + /* When the number of output rows reaches nRow, that means the + ** listing has finished and sqlite3_step() should return SQLITE_DONE. + ** nRow is the sum of the number of rows in the main program, plus + ** the sum of the number of rows in all trigger subprograms encountered + ** so far. The nRow value will increase as new trigger subprograms are + ** encountered, but p->pc will eventually catch up to nRow. + */ nRow = p->nOp; if( p->explain==1 ){ + /* The first 8 memory cells are used for the result set. So we will + ** commandeer the 9th cell to use as storage for an array of pointers + ** to trigger subprograms. The VDBE is guaranteed to have at least 9 + ** cells. */ + assert( p->nMem>9 ); pSub = &p->aMem[9]; if( pSub->flags&MEM_Blob ){ + /* On the first call to sqlite3_step(), pSub will hold a NULL. It is + ** initialized to a BLOB by the P4_SUBPROGRAM processing logic below */ nSub = pSub->n/sizeof(Vdbe*); apSub = (SubProgram **)pSub->z; } for(i=0; inOp; @@ -1098,12 +1113,16 @@ sqlite3SetString(&p->zErrMsg, db, "%s", sqlite3ErrStr(p->rc)); }else{ char *z; Op *pOp; if( inOp ){ + /* The output line number is small enough that we are still in the + ** main program. */ pOp = &p->aOp[i]; }else{ + /* We are currently listing subprograms. Figure out which one and + ** pick up the appropriate opcode. */ int j; i -= p->nOp; for(j=0; i>=apSub[j]->nOp; j++){ i -= apSub[j]->nOp; } @@ -1121,10 +1140,15 @@ pMem->n = sqlite3Strlen30(pMem->z); pMem->type = SQLITE_TEXT; pMem->enc = SQLITE_UTF8; pMem++; + /* When an OP_Program opcode is encounter (the only opcode that has + ** a P4_SUBPROGRAM argument), expand the size of the array of subprograms + ** kept in p->aMem[9].z to hold the new program - assuming this subprogram + ** has not already been seen. + */ if( pOp->p4type==P4_SUBPROGRAM ){ int nByte = (nSub+1)*sizeof(SubProgram*); int j; for(j=0; jp4.pProgram ) break; Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -3158,11 +3158,11 @@ ** range (if any). */ nConstraint = nEq; if( pRangeEnd ){ Expr *pRight = pRangeEnd->pExpr->pRight; - sqlite3ExprCacheRemove(pParse, regBase+nEq); + sqlite3ExprCacheRemove(pParse, regBase+nEq, 1); sqlite3ExprCode(pParse, pRight, regBase+nEq); sqlite3ExprCodeIsNullJump(v, pRight, regBase+nEq, addrNxt); if( zAff ){ if( sqlite3CompareAffinity(pRight, zAff[nConstraint])==SQLITE_AFF_NONE){ /* Since the comparison is to be performed with no conversions @@ -3293,11 +3293,12 @@ struct SrcList_item *origSrc; /* Original list of tables */ nNotReady = pWInfo->nLevel - iLevel - 1; pOrTab = sqlite3StackAllocRaw(pParse->db, sizeof(*pOrTab)+ nNotReady*sizeof(pOrTab->a[0])); if( pOrTab==0 ) return notReady; - pOrTab->nSrc = pOrTab->nAlloc = nNotReady + 1; + pOrTab->nAlloc = (i16)(nNotReady + 1); + pOrTab->nSrc = pOrTab->nAlloc; memcpy(pOrTab->a, pTabItem, sizeof(*pTabItem)); origSrc = pWInfo->pTabList->a; for(k=1; k<=nNotReady; k++){ memcpy(&pOrTab->a[k], &origSrc[pLevel[k].iFrom], sizeof(pOrTab->a[k])); } @@ -3334,11 +3335,11 @@ if( pSubWInfo ){ if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ int iSet = ((ii==pOrWc->nTerm-1)?-1:ii); int r; r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, - regRowid, 0); + regRowid); sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset, sqlite3VdbeCurrentAddr(v)+2, r, iSet); } sqlite3VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody); Index: test/check.test ================================================================== --- test/check.test +++ test/check.test @@ -237,47 +237,47 @@ execsql { UPDATE t4 SET x=4, y=3; SELECT * FROM t4 } } {4 3} -do_test check-4.3 { +do_test check-4.4 { execsql { UPDATE t4 SET x=12, y=2; SELECT * FROM t4 } } {12 2} -do_test check-4.4 { +do_test check-4.5 { execsql { UPDATE t4 SET x=12, y=-22; SELECT * FROM t4 } } {12 -22} -do_test check-4.5 { +do_test check-4.6 { catchsql { UPDATE t4 SET x=0, y=1; } } {1 {constraint failed}} -do_test check-4.6 { +do_test check-4.7 { execsql { SELECT * FROM t4; } } {12 -22} -do_test check-4.7 { +do_test check-4.8 { execsql { PRAGMA ignore_check_constraints=ON; UPDATE t4 SET x=0, y=1; SELECT * FROM t4; } } {0 1} -do_test check-4.8 { +do_test check-4.9 { catchsql { PRAGMA ignore_check_constraints=OFF; UPDATE t4 SET x=0, y=2; } } {1 {constraint failed}} ifcapable vacuum { - do_test check_4.9 { + do_test check_4.10 { catchsql { VACUUM } } {0 {}} } Index: test/e_fkey.test ================================================================== --- test/e_fkey.test +++ test/e_fkey.test @@ -97,12 +97,10 @@ # # EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then # foreign key definitions cannot even be parsed (attempting to specify a # foreign key definition is a syntax error). # -# /* EV: R-58428-36660 */ -# # Specifically, test that foreign key constraints cannot even be parsed # in such a build. # reset_db ifcapable !foreignkey { @@ -327,12 +325,10 @@ DELETE FROM artist WHERE artistid = 5; } } {} #------------------------------------------------------------------------- -# /* EV: R-52486-21352 */ -# # Test that the following is true fo all rows in the track table: # # trackartist IS NULL OR # EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) # @@ -386,12 +382,10 @@ test_r52486_21352 $i $sql } #------------------------------------------------------------------------- -# /* EV: R-42412-59321 */ -# # Check that a NOT NULL constraint can be added to the example schema # to prohibit NULL child keys from being inserted. # # EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter # relationship between artist and track, where NULL values are not @@ -416,11 +410,11 @@ do_test e_fkey-12.2 { catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } } {1 {track.trackartist may not be NULL}} #------------------------------------------------------------------------- -# EVIDENCE-OF: R-17902-59250 +# EVIDENCE-OF: R-16127-35442 # # Test an example from foreignkeys.html. # drop_all_tables do_test e_fkey-13.1 { @@ -460,11 +454,11 @@ INSERT INTO track VALUES(15, 'Boogie Woogie', 3); } } {} #------------------------------------------------------------------------- -# EVIDENCE-OF: R-15034-64331 +# EVIDENCE-OF: R-15958-50233 # # Test the second example from the first section of foreignkeys.html. # do_test e_fkey-14.1 { catchsql { @@ -880,14 +874,17 @@ } [list 1 $error] } } #------------------------------------------------------------------------- -# /* EV: R-47109-40581 */ -# # Test that a REFERENCING clause that does not specify parent key columns # implicitly maps to the primary key of the parent table. +# +# EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES " +# clause to a column definition creates a foreign +# key constraint that maps the column to the primary key of +# . # do_test e_fkey-23.1 { execsql { CREATE TABLE p1(a, b, PRIMARY KEY(a, b)); CREATE TABLE p2(a, b PRIMARY KEY); @@ -913,14 +910,15 @@ # is optional. # # EVIDENCE-OF: R-15417-28014 Indices are not required for child key # columns # -# /* EV: R-15741-50893 */ -# # Also test that if an index is created on the child key columns, it does # not make a difference whether or not it is a UNIQUE index. +# +# EVIDENCE-OF: R-15741-50893 The child key index does not have to be +# (and usually will not be) a UNIQUE index. # drop_all_tables do_test e_fkey-24.1 { execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)); @@ -1010,20 +1008,19 @@ [execsql { SELECT rowid FROM track WHERE trackartist = 6 }] \ [catchsql { DELETE FROM artist WHERE artistid = 6 }] } {2 1 {foreign key constraint failed}} #------------------------------------------------------------------------- -# EVIDENCE-OF: R-54172-55848 +# EVIDENCE-OF: R-47936-10044 Or, more generally: +# SELECT rowid FROM WHERE = :parent_key_value # # Test that when a row is deleted from the parent table of an FK # constraint, the child table is queried for orphaned rows. The # query is equivalent to: # # SELECT rowid FROM WHERE = :parent_key_value # -# /* EV: R-61616-46700 */ -# # Also test that when a row is inserted into the parent table, or when the # parent key values of an existing row are modified, a query equivalent # to the following is planned. In some cases it is not executed, but it # is always planned. # @@ -1070,11 +1067,11 @@ execsql {DROP TABLE child} } #------------------------------------------------------------------------- -# /* EV: R-14553-34013 */ +# EVIDENCE-OF: R-14553-34013 # # Test the example schema at the end of section 3. Also test that is # is "efficient". In this case "efficient" means that foreign key # related operations on the parent table do not provoke linear scans. # @@ -1114,13 +1111,14 @@ ########################################################################### ### SECTION 4.1: Composite Foreign Key Constraints ########################################################################### #------------------------------------------------------------------------- -# /* EV: R-41062-34431 */ -# # Check that parent and child keys must have the same number of columns. +# +# EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same +# cardinality. # foreach {tn sql err} { 1 "CREATE TABLE c(jj REFERENCES p(x, y))" {foreign key on jj should reference only one column of table p} @@ -1161,11 +1159,11 @@ catchsql {DELETE FROM p} } {1 {foreign key mismatch}} #------------------------------------------------------------------------- -# /* EV: R-24676-09859 */ +# EVIDENCE-OF: R-24676-09859 # # Test the example schema in the "Composite Foreign Key Constraints" # section. # do_test e_fkey-29.1 { @@ -1200,14 +1198,13 @@ } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- -# /* EV: R-33626-48418 */ -# -# Check that if any of the child key columns in the above schema are NULL, -# there is no requirement for a corresponding parent key. +# EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns +# (in this case songartist and songalbum) are NULL, then there is no +# requirement for a corresponding row in the parent table. # do_test e_fkey-30.1 { execsql { INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever'); INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy'); @@ -1217,21 +1214,19 @@ ########################################################################### ### SECTION 4.2: Deferred Foreign Key Constraints ########################################################################### #------------------------------------------------------------------------- -# Note: R-35290-16460 is tested below. -# -# TODO: R-30323-21917 - -#------------------------------------------------------------------------- -# /* EV: R-09323-30470 */ -# # Test that if a statement violates an immediate FK constraint, and the # database does not satisfy the FK constraint once all effects of the # statement have been applied, an error is reported and the effects of # the statement rolled back. +# +# EVIDENCE-OF: R-09323-30470 If a statement modifies the contents of the +# database so that an immediate foreign key constraint is in violation +# at the conclusion the statement, an exception is thrown and the +# effects of the statement are reverted. # drop_all_tables do_test e_fkey-31.1 { execsql { CREATE TABLE king(a, b, PRIMARY KEY(a)); @@ -1274,19 +1269,28 @@ SELECT * FROM king; } } {1 {} 2 {}} #------------------------------------------------------------------------- -# /* EV: R-49178-21358 */ -# /* EV: R-39692-12488 */ -# /* EV: R-55147-47664 */ -# /* EV: R-29604-30395 */ -# # Test that if a deferred constraint is violated within a transaction, # nothing happens immediately and the database is allowed to persist # in a state that does not satisfy the FK constraint. However attempts # to COMMIT the transaction fail until the FK constraint is satisfied. +# +# EVIDENCE-OF: R-49178-21358 By contrast, if a statement modifies the +# contents of the database such that a deferred foreign key constraint +# is violated, the violation is not reported immediately. +# +# EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not +# checked until the transaction tries to COMMIT. +# +# EVIDENCE-OF: R-55147-47664 For as long as the user has an open +# transaction, the database is allowed to exist in a state that violates +# any number of deferred foreign key constraints. +# +# EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as +# foreign key constraints remain in violation. # proc test_efkey_34 {tn isError sql} { do_test e_fkey-32.$tn " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] @@ -1305,14 +1309,18 @@ test_efkey_34 7 1 "COMMIT" test_efkey_34 8 0 "INSERT INTO ll VALUES(5)" test_efkey_34 9 0 "COMMIT" #------------------------------------------------------------------------- -# /* EV: R-56844-61705 */ -# # When not running inside a transaction, a deferred constraint is similar # to an immediate constraint (violations are reported immediately). +# +# EVIDENCE-OF: R-56844-61705 If the current statement is not inside an +# explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit +# transaction is committed as soon as the statement has finished +# executing. In this case deferred constraints behave the same as +# immediate constraints. # drop_all_tables proc test_efkey_35 {tn isError sql} { do_test e_fkey-33.$tn " catchsql {$sql} @@ -1331,18 +1339,18 @@ test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')" test_efkey_35 4 0 "INSERT INTO child VALUES('x', 'y')" #------------------------------------------------------------------------- -# /* EV: R-12782-61841 */ +# EVIDENCE-OF: R-12782-61841 # # Test that an FK constraint is made deferred by adding the following # to the definition: # # DEFERRABLE INITIALLY DEFERRED # -# /* EV: R-09005-28791 */ +# EVIDENCE-OF: R-09005-28791 # # Also test that adding any of the following to a foreign key definition # makes the constraint IMMEDIATE: # # NOT DEFERRABLE INITIALLY DEFERRED @@ -1349,16 +1357,18 @@ # NOT DEFERRABLE INITIALLY IMMEDIATE # NOT DEFERRABLE # DEFERRABLE INITIALLY IMMEDIATE # DEFERRABLE # -# /* EV: R-35290-16460 */ -# # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT # DEFERRABLE clause). # -# /* EV: R-30323-21917 */ FKs are either IMMEDIATE or DEFERRED. +# EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by +# default. +# +# EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is +# classified as either immediate or deferred. # drop_all_tables do_test e_fkey-34.1 { execsql { CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z)); @@ -1451,11 +1461,11 @@ test_efkey_29 31 "UPDATE c7 SET a = 10" 0 test_efkey_29 32 "COMMIT" 1 test_efkey_29 33 "ROLLBACK" 0 #------------------------------------------------------------------------- -# /* EV: R-35043-01546 */ +# EVIDENCE-OF: R-24499-57071 # # Test an example from foreignkeys.html dealing with a deferred foreign # key constraint. # do_test e_fkey-35.1 { @@ -1485,14 +1495,16 @@ COMMIT; } } {} #------------------------------------------------------------------------- -# /* EV: R-07223-48323 */ -# # Verify that a nested savepoint may be released without satisfying # deferred foreign key constraints. +# +# EVIDENCE-OF: R-07223-48323 A nested savepoint transaction may be +# RELEASEd while the database is in a state that does not satisfy a +# deferred foreign key constraint. # drop_all_tables do_test e_fkey-36.1 { execsql { CREATE TABLE t1(a PRIMARY KEY, @@ -1521,15 +1533,19 @@ } } {} #------------------------------------------------------------------------- -# /* EV: R-44295-13823 */ -# # Check that a transaction savepoint (an outermost savepoint opened when # the database was in auto-commit mode) cannot be released without # satisfying deferred foreign key constraints. It may be rolled back. +# +# EVIDENCE-OF: R-44295-13823 A transaction savepoint (a non-nested +# savepoint that was opened while there was not currently an open +# transaction), on the other hand, is subject to the same restrictions +# as a COMMIT - attempting to RELEASE it while the database is in such a +# state will fail. # do_test e_fkey-37.1 { execsql { SAVEPOINT one; SAVEPOINT two; @@ -1560,14 +1576,17 @@ do_test e_fkey-37.6 { execsql {ROLLBACK TO one ; RELEASE one} } {} #------------------------------------------------------------------------- -# /* EV: R-37736-42616 */ -# # Test that if a COMMIT operation fails due to deferred foreign key # constraints, any nested savepoints remain open. +# +# EVIDENCE-OF: R-37736-42616 If a COMMIT statement (or the RELEASE of a +# transaction SAVEPOINT) fails because the database is currently in a +# state that violates a deferred foreign key constraint and there are +# currently nested savepoints, the nested savepoints remain open. # do_test e_fkey-38.1 { execsql { DELETE FROM t1 WHERE a>3; SELECT * FROM t1; @@ -1621,19 +1640,23 @@ ########################################################################### ### SECTION 4.3: ON DELETE and ON UPDATE Actions ########################################################################### #------------------------------------------------------------------------- -# /* EV: R-48270-44282 */ -# # Test that configured ON DELETE and ON UPDATE actions take place when # deleting or modifying rows of the parent table, respectively. # -# /* EV: R-48124-63225 */ +# EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses +# are used to configure actions that take place when deleting rows from +# the parent table (ON DELETE), or modifying the parent key values of +# existing rows (ON UPDATE). # # Test that a single FK constraint may have different actions configured # for ON DELETE and ON UPDATE. +# +# EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have +# different actions configured for ON DELETE and ON UPDATE. # do_test e_fkey-39.1 { execsql { CREATE TABLE p(a, b PRIMARY KEY, c); CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p @@ -1670,18 +1693,21 @@ SELECT * FROM c1; } } {1 xx k0 2 xx {} 3 xx {}} #------------------------------------------------------------------------- -# /* EV: R-33326-45252 */ -# # Each foreign key in the system has an ON UPDATE and ON DELETE action, # either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". # -# /* EV: R-19803-45884 */ +# EVIDENCE-OF: R-33326-45252 The ON DELETE and ON UPDATE action +# associated with each foreign key in an SQLite database is one of "NO +# ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". # # If none is specified explicitly, "NO ACTION" is the default. +# +# EVIDENCE-OF: R-19803-45884 If an action is not explicitly specified, +# it defaults to "NO ACTION". # drop_all_tables do_test e_fkey-40.1 { execsql { CREATE TABLE parent(x PRIMARY KEY, y); @@ -1718,14 +1744,16 @@ } { do_test e_fkey-40.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes } #------------------------------------------------------------------------- -# /* EV: R-19971-54976 */ -# # Test that "NO ACTION" means that nothing happens to a child row when # it's parent row is updated or deleted. +# +# EVIDENCE-OF: R-19971-54976 Configuring "NO ACTION" means just that: +# when a parent key is modified or deleted from the database, no special +# action is taken. # drop_all_tables do_test e_fkey-41.1 { execsql { CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2)); @@ -1755,15 +1783,18 @@ do_test e_fkey-41.4 { execsql ROLLBACK } {} #------------------------------------------------------------------------- -# /* EV: R-04272-38653 */ -# # Test that "RESTRICT" means the application is prohibited from deleting # or updating a parent table row when there exists one or more child keys # mapped to it. +# +# EVIDENCE-OF: R-04272-38653 The "RESTRICT" action means that the +# application is prohibited from deleting (for ON DELETE RESTRICT) or +# modifying (for ON UPDATE RESTRICT) a parent key when there exists one +# or more child keys mapped to it. # drop_all_tables do_test e_fkey-41.1 { execsql { CREATE TABLE parent(p1, p2); @@ -1790,15 +1821,20 @@ do_test e_fkey-41.4 { catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- -# /* EV: R-37997-42187 */ -# # Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE # constraints, in that it is enforced immediately, not at the end of the # statement. +# +# EVIDENCE-OF: R-37997-42187 The difference between the effect of a +# RESTRICT action and normal foreign key constraint enforcement is that +# the RESTRICT action processing happens as soon as the field is updated +# - not at the end of the current statement as it would with an +# immediate constraint, or at the end of the current transaction as it +# would with a deferred constraint. # drop_all_tables do_test e_fkey-42.1 { execsql { CREATE TABLE parent(x PRIMARY KEY); @@ -1876,13 +1912,16 @@ SELECT * FROM child2; } } {key2} #------------------------------------------------------------------------- -# /* EV: R-24179-60523 */ -# # Test that RESTRICT is enforced immediately, even for a DEFERRED constraint. +# +# EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is +# attached to is deferred, configuring a RESTRICT action causes SQLite +# to return an error immediately if a parent key with dependent child +# keys is deleted or modified. # drop_all_tables do_test e_fkey-43.1 { execsql { CREATE TABLE parent(x PRIMARY KEY); @@ -1949,13 +1988,17 @@ COMMIT; } } {} #------------------------------------------------------------------------- -# /* EV: R-03353-05327 */ -# # Test SET NULL actions. +# +# EVIDENCE-OF: R-03353-05327 If the configured action is "SET NULL", +# then when a parent key is deleted (for ON DELETE SET NULL) or modified +# (for ON UPDATE SET NULL), the child key columns of all rows in the +# child table that mapped to the parent key are set to contain SQL NULL +# values. # drop_all_tables do_test e_fkey-44.1 { execsql { CREATE TABLE pA(x PRIMARY KEY); @@ -1988,13 +2031,15 @@ do_test e_fkey-44.5 { execsql { SELECT quote(c) FROM cB } } {NULL} #------------------------------------------------------------------------- -# /* EV: R-43054-54832 */ -# # Test SET DEFAULT actions. +# +# EVIDENCE-OF: R-43054-54832 The "SET DEFAULT" actions are similar to +# "SET NULL", except that each of the child key columns is set to +# contain the columns default value instead of NULL. # drop_all_tables do_test e_fkey-45.1 { execsql { CREATE TABLE pA(x PRIMARY KEY); @@ -2028,14 +2073,18 @@ do_test e_fkey-45.5 { execsql { SELECT quote(c) FROM cB } } {X'9999'} #------------------------------------------------------------------------- -# /* EV: R-61376-57267 */ -# /* EV: R-61809-62207 */ -# # Test ON DELETE CASCADE actions. +# +# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or +# update operation on the parent key to each dependent child key. +# +# EVIDENCE-OF: R-61809-62207 For an "ON DELETE CASCADE" action, this +# means that each row in the child table that was associated with the +# deleted parent row is also deleted. # drop_all_tables do_test e_fkey-46.1 { execsql { CREATE TABLE p1(a, b UNIQUE); @@ -2065,14 +2114,18 @@ execsql { SELECT * FROM p1 } } {} #------------------------------------------------------------------------- -# /* EV: R-61376-57267 */ -# /* EV: R-13877-64542 */ -# # Test ON UPDATE CASCADE actions. +# +# EVIDENCE-OF: R-13877-64542 For an "ON UPDATE CASCADE" action, it means +# that the values stored in each dependent child key are modified to +# match the new parent key values. +# +# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or +# update operation on the parent key to each dependent child key. # drop_all_tables do_test e_fkey-47.1 { execsql { CREATE TABLE p1(a, b UNIQUE); @@ -2107,11 +2160,11 @@ do_test e_fkey-46.5 { execsql { SELECT * FROM p1 } } {{} 6 4 11 5 10} #------------------------------------------------------------------------- -# /* EV: R-51329-33438 */ +# EVIDENCE-OF: R-65058-57158 # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. # drop_all_tables @@ -2146,14 +2199,16 @@ execsql { SELECT * FROM track } } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2} #------------------------------------------------------------------------- -# /* EV: R-53968-51642 */ -# # Verify that adding an FK action does not absolve the user of the # requirement not to violate the foreign key constraint. +# +# EVIDENCE-OF: R-53968-51642 Configuring an ON UPDATE or ON DELETE +# action does not mean that the foreign key constraint does not need to +# be satisfied. # drop_all_tables do_test e_fkey-49.1 { execsql { CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a)); @@ -2184,17 +2239,22 @@ catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- -# /* EV: R-07065-59588 */ -# /* EV: R-28220-46694 */ +# EVIDENCE-OF: R-11856-19836 # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT" # clause does not abrogate the need to satisfy the foreign key constraint # (R-28220-46694). +# +# EVIDENCE-OF: R-28220-46694 For example, if an "ON DELETE SET DEFAULT" +# action is configured, but there is no row in the parent table that +# corresponds to the default values of the child key columns, deleting a +# parent key while dependent child keys exist still causes a foreign key +# violation. # drop_all_tables do_test e_fkey-50.1 { execsql { CREATE TABLE artist( @@ -2225,11 +2285,11 @@ do_test e_fkey-50.5 { execsql { SELECT * FROM track } } {14 {Mr. Bojangles} 0} #------------------------------------------------------------------------- -# /* EV: R-09564-22170 */ +# EVIDENCE-OF: R-09564-22170 # # Check that the order of steps in an UPDATE or DELETE on a parent # table is as follows: # # 1. Execute applicable BEFORE trigger programs, @@ -2277,16 +2337,18 @@ } } {22 23 21 xxx 23} #------------------------------------------------------------------------- -# /* EV: R-27383-10246 */ -# # Verify that ON UPDATE actions only actually take place if the parent key # is set to a new value that is distinct from the old value. The default # collation sequence and affinity are used to determine if the new value # is 'distinct' from the old or not. +# +# EVIDENCE-OF: R-27383-10246 An ON UPDATE action is only taken if the +# values of the parent key are modified so that the new parent key +# values are not equal to the old. # drop_all_tables do_test e_fkey-52.1 { execsql { CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b)); @@ -2331,11 +2393,11 @@ SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 null {}} #------------------------------------------------------------------------- -# /* EV: R-58589-50781 */ +# EVIDENCE-OF: R-35129-58141 # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. This example demonstrates that ON UPDATE actions # only take place if at least one parent key column is set to a value # that is distinct from its previous value. @@ -2365,25 +2427,31 @@ ########################################################################### ### SECTION 5: CREATE, ALTER and DROP TABLE commands ########################################################################### #------------------------------------------------------------------------- -# /* EV: R-36018-21755 */ -# /* EV: R-25384-39337 */ -# # Test that parent keys are not checked when tables are created. +# +# EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key +# constraints are not checked when a table is created. +# +# EVIDENCE-OF: R-25384-39337 There is nothing stopping the user from +# creating a foreign key definition that refers to a parent table that +# does not exist, or to parent key columns that do not exist or are not +# collectively bound by a PRIMARY KEY or UNIQUE constraint. # # Child keys are checked to ensure all component columns exist. If parent # key columns are explicitly specified, SQLite checks to make sure there # are the same number of columns in the child and parent keys. (TODO: This # is tested but does not correspond to any testable statement.) # -# /* EV: R-08908-23439 */ -# # Also test that the above statements are true regardless of whether or not # foreign keys are enabled: "A CREATE TABLE command operates the same whether # or not foreign key constraints are enabled." +# +# EVIDENCE-OF: R-08908-23439 A CREATE TABLE command operates the same +# whether or not foreign key constraints are enabled. # foreach {tn zCreateTbl lRes} { 1 "CREATE TABLE t1(a, b REFERENCES t1)" {0 {}} 2 "CREATE TABLE t1(a, b REFERENCES t2)" {0 {}} 3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)" {0 {}} @@ -2408,11 +2476,14 @@ catchsql $zCreateTbl } $lRes } #------------------------------------------------------------------------- -# /* EV: R-47952-62498 */ +# EVIDENCE-OF: R-47952-62498 It is not possible to use the "ALTER TABLE +# ... ADD COLUMN" syntax to add a column that includes a REFERENCES +# clause, unless the default value of the new column is NULL. Attempting +# to do so returns an error. # proc test_efkey_6 {tn zAlter isError} { drop_all_tables do_test e_fkey-56.$tn.1 " @@ -2425,18 +2496,23 @@ test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0 test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0 test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1 #------------------------------------------------------------------------- -# /* EV: R-47080-02069 */ -# # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table # is RENAMED. # -# /* EV: R-63827-54774 */ +# EVIDENCE-OF: R-47080-02069 If an "ALTER TABLE ... RENAME TO" command +# is used to rename a table that is the parent table of one or more +# foreign key constraints, the definitions of the foreign key +# constraints are modified to refer to the parent table by its new name # # Test that these adjustments are visible in the sqlite_master table. +# +# EVIDENCE-OF: R-63827-54774 The text of the child CREATE TABLE +# statement or statements stored in the sqlite_master table are modified +# to reflect the new parent table name. # do_test e_fkey-56.1 { drop_all_tables execsql { CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b)); @@ -2473,15 +2549,20 @@ {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)} \ {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \ ] #------------------------------------------------------------------------- -# /* EV: R-14208-23986 */ -# /* EV: R-11078-03945 */ -# # Check that a DROP TABLE does an implicit DELETE FROM. Which does not # cause any triggers to fire, but does fire foreign key actions. +# +# EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when +# it is prepared, the DROP TABLE command performs an implicit DELETE to +# remove all rows from the table before dropping it. +# +# EVIDENCE-OF: R-11078-03945 The implicit DELETE does not cause any SQL +# triggers to fire, but may invoke foreign key actions or constraint +# violations. # do_test e_fkey-57.1 { drop_all_tables execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)); @@ -2539,15 +2620,16 @@ ROLLBACK; } } {{delete 1}} #------------------------------------------------------------------------- -# /* EV: R-32768-47925 */ -# # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the # DROP TABLE command fails. # +# EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is +# violated, the DROP TABLE statement fails and the table is not dropped. +# do_test e_fkey-58.1 { execsql { DELETE FROM c1; DELETE FROM c2; DELETE FROM c3; @@ -2571,14 +2653,17 @@ ROLLBACK; } } {a b a b} #------------------------------------------------------------------------- -# /* EV: R-05903-08460 */ -# # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting # to commit the transaction fails unless the violation is fixed. +# +# EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is +# violated, then an error is reported when the user attempts to commit +# the transaction if the foreign key constraint violations still exist +# at that point. # do_test e_fkey-59.1 { execsql { DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ; DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ; @@ -2603,14 +2688,15 @@ execsql { INSERT INTO p VALUES('a', 'b') } execsql COMMIT } {} #------------------------------------------------------------------------- -# /* EV: R-57242-37005 */ -# # Any "foreign key mismatch" errors encountered while running an implicit # "DELETE FROM tbl" are ignored. +# +# EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors +# encountered as part of an implicit DELETE are ignored. # drop_all_tables do_test e_fkey-60.1 { execsql { PRAGMA foreign_keys = OFF; @@ -2650,20 +2736,21 @@ execsql { DROP TABLE c2 } execsql { DELETE FROM p } } {} #------------------------------------------------------------------------- -# /* EV: R-54142-41346 */ -# # Test that the special behaviours of ALTER and DROP TABLE are only # activated when foreign keys are enabled. Special behaviours are: # # 1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL # default value. # 2. Modifying foreign key definitions when a parent table is RENAMEd. # 3. Running an implicit DELETE FROM command as part of DROP TABLE. # +# EVIDENCE-OF: R-54142-41346 The properties of the DROP TABLE and ALTER +# TABLE commands described above only apply if foreign keys are enabled. +# do_test e_fkey-61.1.1 { drop_all_tables execsql { CREATE TABLE t1(a, b) } catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 } } {1 {Cannot add a REFERENCES column with non-NULL default value}} @@ -2725,15 +2812,18 @@ ########################################################################### ### SECTION 6: Limits and Unsupported Features ########################################################################### #------------------------------------------------------------------------- -# /* EV: R-24728-13230 */ -# /* EV: R-24450-46174 */ -# # Test that MATCH clauses are parsed, but SQLite treats every foreign key # constraint as if it were "MATCH SIMPLE". +# +# EVIDENCE-OF: R-24728-13230 SQLite parses MATCH clauses (i.e. does not +# report a syntax error if you specify one), but does not enforce them. +# +# EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are +# handled as if MATCH SIMPLE were specified. # foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] { drop_all_tables do_test e_fkey-62.$zMatch.1 { execsql " @@ -2758,14 +2848,15 @@ catchsql { INSERT INTO c VALUES('a', 2, 4) } } {1 {foreign key constraint failed}} } #------------------------------------------------------------------------- -# /* EV: R-21599-16038 */ -# # Test that SQLite does not support the SET CONSTRAINT statement. And # that it is possible to create both immediate and deferred constraints. +# +# EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is +# permanently marked as deferred or immediate when it is created. # drop_all_tables do_test e_fkey-62.1 { catchsql { SET CONSTRAINTS ALL IMMEDIATE } } {1 {near "SET": syntax error}} @@ -2798,15 +2889,18 @@ COMMIT; } } {} #------------------------------------------------------------------------- -# /* EV: R-42264-30503 */ -# # Test that the maximum recursion depth of foreign key action programs is # governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH # settings. +# +# EVIDENCE-OF: R-42264-30503 The SQLITE_MAX_TRIGGER_DEPTH and +# SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable +# depth of trigger program recursion. For the purposes of these limits, +# foreign key actions are considered trigger programs. # proc test_on_delete_recursion {limit} { drop_all_tables execsql { BEGIN; @@ -2881,14 +2975,15 @@ do_test e_fkey-63.2.5 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 } {5} #------------------------------------------------------------------------- -# /* EV: R-51769-32730 */ -# # The setting of the recursive_triggers pragma does not affect foreign # key actions. +# +# EVIDENCE-OF: R-51769-32730 The PRAGMA recursive_triggers setting does +# not not affect the operation of foreign key actions. # foreach recursive_triggers_setting [list 0 1 ON OFF] { drop_all_tables execsql "PRAGMA recursive_triggers = $recursive_triggers_setting" ADDED test/fts3query.test Index: test/fts3query.test ================================================================== --- test/fts3query.test +++ test/fts3query.test @@ -0,0 +1,105 @@ +# 2009 December 20 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# This file contains tests of fts3 queries that have been useful during +# the development process as well as some that have been useful in tracking +# down bugs. They are not focused on any particular functionality. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# If this build does not include FTS3, skip the tests in this file. +# +ifcapable !fts3 { finish_test ; return } +source $testdir/fts3_common.tcl +set DO_MALLOC_TEST 0 + +do_test fts3query-1.1 { + execsql { + CREATE VIRTUAL TABLE t1 USING fts3(x); + BEGIN; + INSERT INTO t1 VALUES('The source code for SQLite is in the public'); + } +} {} + +do_select_test fts3query-1.2 { + SELECT * FROM t1; +} {{The source code for SQLite is in the public}} +do_select_test fts3query-1.3 { + SELECT * FROM t1 WHERE t1 MATCH 'sqlite' +} {{The source code for SQLite is in the public}} + +do_test fts3query-1.4 { execsql {COMMIT} } {} + +do_select_test fts3query-1.5 { + SELECT * FROM t1; +} {{The source code for SQLite is in the public}} +do_select_test fts3query-1.6 { + SELECT * FROM t1 WHERE t1 MATCH 'sqlite' +} {{The source code for SQLite is in the public}} + + +set sqlite_fts3_enable_parentheses 1 +do_test fts3query-2.1 { + execsql { + CREATE VIRTUAL TABLE zoink USING fts3; + INSERT INTO zoink VALUES('The apple falls far from the tree'); + } +} {} +do_test fts3query-2.2 { + execsql { + SELECT docid FROM zoink WHERE zoink MATCH '(apple oranges) AND apple' + } +} {} +do_test fts3query-2.3 { + execsql { + SELECT docid FROM zoink WHERE zoink MATCH 'apple AND (oranges apple)' + } +} {} +set sqlite_fts3_enable_parentheses 0 + +do_test fts3query-3.1 { + execsql { + CREATE VIRTUAL TABLE foobar using FTS3(description, tokenize porter); + INSERT INTO foobar (description) values (' + Filed under: Emerging Technologies, EV/Plug-in, Hybrid, Chevrolet, GM, + ZENN 2011 Chevy Volt - Click above for high-res image gallery There are + 16 days left in the month of December. Besides being time for most + Americans to kick their Christmas shopping sessions into high gear and + start planning their resolutions for 2010, it also means that there''s + precious little time for EEStor to "deliver functional technology" to + Zenn Motors as promised. Still, the promises held out by the secretive + company are too great for us to forget about entirely. We''d love for + EEStor''s claims to be independently verified and proven accurate, as + would just about anyone else looking to break free of petroleum in fav + '); + } +} {} + +do_test fts3query-3.2 { + execsql { SELECT docid FROM foobar WHERE description MATCH '"high sp d"' } +} {} + +proc mit {blob} { + set scan(littleEndian) i* + set scan(bigEndian) I* + binary scan $blob $scan($::tcl_platform(byteOrder)) r + return $r +} +db func mit mit + +do_test fts3query-3.3 { + execsql { SELECT mit(matchinfo(foobar)) FROM foobar WHERE foobar MATCH 'the' } +} {{1 1 3 3}} + +finish_test + Index: test/fts3rnd.test ================================================================== --- test/fts3rnd.test +++ test/fts3rnd.test @@ -145,18 +145,42 @@ set ret [list] set reg [string map {* {[^ ]*}} $zPrefix] set reg " $reg " - foreach {key value} [array get ::t1] { + foreach key [lsort -integer [array names ::t1]] { + set value $::t1($key) + set cnt [list] foreach col $value { - if {[regexp $reg " $col "]} {lappend ret $key} + if {[regexp $reg " $col "]} { lappend ret $key ; break } + } + } + + #lsort -uniq -integer $ret + set ret +} + +proc simple_token_matchinfo {zToken} { + set total(0) 0 + set total(1) 0 + set total(2) 0 + + foreach key [lsort -integer [array names ::t1]] { + set value $::t1($key) + set cnt [list] + foreach i {0 1 2} col $value { + set n [llength [lsearch -all $col $zToken]] + lappend cnt $n + incr total($i) $n + } + if {[lindex [lsort $cnt] end]} { + lappend ret $key [concat 1 3 XXX $cnt] } } - - lsort -uniq -integer $ret -} + + string map [list XXX "$total(0) $total(1) $total(2)"] $ret +} proc simple_near {termlist nNear} { set ret [list] foreach {key value} [array get ::t1] { @@ -211,10 +235,18 @@ foreach b $B { set n($b) {} } set ret [list] foreach a $A { if {[info exists n($a)]} {lappend ret $a} } return $ret } + +proc mit {blob} { + set scan(littleEndian) i* + set scan(bigEndian) I* + binary scan $blob $scan($::tcl_platform(byteOrder)) r + return $r +} +db func mit mit set sqlite_fts3_enable_parentheses 1 foreach nodesize {50 500 1000 2000} { catch { array unset ::t1 } @@ -226,11 +258,11 @@ execsql "CREATE VIRTUAL TABLE t1 USING fts3(a, b, c)" execsql "INSERT INTO t1(t1) VALUES('nodesize=$nodesize')" for {set i 0} {$i < 100} {incr i} { insert_row $i } } - for {set iTest 0} {$iTest <= 100} {incr iTest} { + for {set iTest 1} {$iTest <= 100} {incr iTest} { catchsql COMMIT set DO_MALLOC_TEST 0 set nRep 10 if {$iTest==100 && $nodesize==50} { @@ -263,12 +295,12 @@ # array for each term. # for {set i 0} {$i < 10} {incr i} { set term [random_term] do_select_test fts3rnd-1.$nodesize.$iTest.1.$i { - SELECT docid FROM t1 WHERE t1 MATCH $term - } [simple_phrase $term] + SELECT docid, mit(matchinfo(t1)) FROM t1 WHERE t1 MATCH $term + } [simple_token_matchinfo $term] } # This time, use the first two characters of each term as a term prefix # to query for. Test that querying the Tcl array produces the same results # as querying the FTS3 table for the prefix. Index: test/fuzz2.test ================================================================== --- test/fuzz2.test +++ test/fuzz2.test @@ -63,19 +63,25 @@ fuzzcatch {UPDATE OR IGNORE "AAAAAA" . "AAAAAA" SET "AAAAAA" = NOT #96} } {1} do_test fuzz2-2.12 { fuzzcatch {SELECT - #196} } {1} + +ifcapable {trigger} { # Only do the following tests if triggers are enabled + do_test fuzz2-3.0 { fuzzcatch {CREATE TRIGGER "AAAAAA" . "AAAAAA" AFTER UPDATE OF "AAAAAA" , "AAAAAA" ON "AAAAAA" . "AAAAAA" FOR EACH ROW BEGIN UPDATE AAAAAA SET "AAAAAA" = #162; END} } {1} do_test fuzz2-3.1 { 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} } {1} do_test fuzz2-3.2 { 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} } {1} + +} ;# End of ifcapable {trigger} + do_test fuzz2-4.0 { fuzzcatch {ATTACH DATABASE #168 AS whatever} } {1} do_test fuzz2-4.1 { fuzzcatch {DETACH #133} Index: test/main.test ================================================================== --- test/main.test +++ test/main.test @@ -23,24 +23,43 @@ # Tests of the sqlite_complete() function. # do_test main-1.1 { db complete {This is a test} } {0} -do_test main-1.2 { +do_test main-1.2.0 { db complete { } -} {1} -do_test main-1.3 { +} {0} +do_test main-1.2.1 { + db complete {} +} {0} +do_test main-1.3.0 { db complete { -- a comment ; } -} {1} -do_test main-1.4 { +} {0} +do_test main-1.3.1 { + db complete { + /* a comment ; */ + } +} {0} +do_test main-1.4.0 { db complete { -- a comment ; ; } +} {1} +do_test main-1.4.1 { + db complete { + /* a comment ; */ + ; + } +} {1} +do_test main-1.4.2 { + db complete { + /* a comment ; */ ; + } } {1} do_test main-1.5 { db complete {DROP TABLE 'xyz;} } {0} do_test main-1.6 { Index: test/schema.test ================================================================== --- test/schema.test +++ test/schema.test @@ -361,10 +361,12 @@ # $::STMT was prepared. So unless it has been expired, it would be # possible to run the "CREATE TABLE t4" statement and create a # duplicate table. list [sqlite3_step $::STMT] [sqlite3_finalize $::STMT] } {SQLITE_ERROR SQLITE_SCHEMA} + +ifcapable {auth} { do_test schema-13.1 { set S [sqlite3_prepare_v2 db "SELECT * FROM sqlite_master" -1 dummy] db function hello hello db function hello {} @@ -381,7 +383,9 @@ } {SQLITE_SCHEMA} do_test schema-13.3 { sqlite3_finalize $S } {SQLITE_SCHEMA} + +} finish_test Index: test/tester.tcl ================================================================== --- test/tester.tcl +++ test/tester.tcl @@ -962,22 +962,26 @@ } } # Drop all tables in database [db] proc drop_all_tables {{db db}} { - set pk [$db one "PRAGMA foreign_keys"] - $db eval "PRAGMA foreign_keys = OFF" + ifcapable trigger&&foreignkey { + set pk [$db one "PRAGMA foreign_keys"] + $db eval "PRAGMA foreign_keys = OFF" + } foreach {t type} [$db eval { SELECT name, type FROM sqlite_master WHERE type IN('table', 'view') AND name NOT like 'sqlite_%' }] { $db eval "DROP $type $t" } - $db eval " PRAGMA foreign_keys = $pk " + ifcapable trigger&&foreignkey { + $db eval "PRAGMA foreign_keys = $pk" + } } # If the library is compiled with the SQLITE_DEFAULT_AUTOVACUUM macro set # to non-zero, then set the global variable $AUTOVACUUM to 1. set AUTOVACUUM $sqlite_options(default_autovacuum) source $testdir/thread_common.tcl Index: test/tkt-3fe897352e.test ================================================================== --- test/tkt-3fe897352e.test +++ test/tkt-3fe897352e.test @@ -14,10 +14,17 @@ # fixed. # set testdir [file dirname $argv0] source $testdir/tester.tcl + +# The following tests use hex_to_utf16be() and hex_to_utf16le() which +# which are only available if SQLite is built with UTF16 support. +ifcapable {!utf16} { + finish_test + return +} do_test tkt-3fe89-1.1 { db close sqlite3 db :memory: db eval { Index: test/trace.test ================================================================== --- test/trace.test +++ test/trace.test @@ -57,32 +57,39 @@ do_test trace-2.1 { set STMT [sqlite3_prepare $DB {INSERT INTO t1 VALUES(2,3)} -1 TAIL] db trace trace_proc proc trace_proc sql { global TRACE_OUT - set TRACE_OUT $sql + lappend TRACE_OUT [string trim $sql] } set TRACE_OUT {} sqlite3_step $STMT set TRACE_OUT -} {INSERT INTO t1 VALUES(2,3)} +} {{INSERT INTO t1 VALUES(2,3)}} do_test trace-2.2 { set TRACE_OUT {} sqlite3_reset $STMT set TRACE_OUT } {} do_test trace-2.3 { sqlite3_step $STMT set TRACE_OUT -} {INSERT INTO t1 VALUES(2,3)} +} {{INSERT INTO t1 VALUES(2,3)}} do_test trace-2.4 { + set TRACE_OUT {} execsql {SELECT * FROM t1} } {1 2 2 3 2 3} do_test trace-2.5 { set TRACE_OUT -} {SELECT * FROM t1} +} {{SELECT * FROM t1}} catch {sqlite3_finalize $STMT} + +do_test trace-2.6 { + set TRACE_OUT {} + db eval VACUUM + set TRACE_OUT +} {VACUUM} # Similar tests, but this time for profiling. # do_test trace-3.1 { set rc [catch {db profile 1 2 3} msg] @@ -120,31 +127,32 @@ do_test trace-4.1 { set STMT [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL] db trace trace_proc proc profile_proc {sql tm} { global TRACE_OUT - set TRACE_OUT $sql + lappend TRACE_OUT [string trim $sql] } set TRACE_OUT {} sqlite3_step $STMT set TRACE_OUT -} {INSERT INTO t2 VALUES(2,3)} +} {{INSERT INTO t2 VALUES(2,3)}} do_test trace-4.2 { set TRACE_OUT {} sqlite3_reset $STMT set TRACE_OUT } {} do_test trace-4.3 { sqlite3_step $STMT set TRACE_OUT -} {INSERT INTO t2 VALUES(2,3)} +} {{INSERT INTO t2 VALUES(2,3)}} do_test trace-4.4 { + set TRACE_OUT {} execsql {SELECT * FROM t1} } {1 2 2 3 2 3} do_test trace-4.5 { set TRACE_OUT -} {SELECT * FROM t1} +} {{SELECT * FROM t1}} catch {sqlite3_finalize $STMT} # Trigger tracing. # ifcapable trigger { @@ -227,11 +235,11 @@ } db trace trace_proc set TRACE_OUT {} execsql {SELECT '$::t6str', [$::t6str], $::t6str, ?1, "?1", $::t6str FROM t6} } {{$::t6str} 1 {test-six y'all} {test-six y'all} 2 {test-six y'all}} -do_test trace-6.101 { +do_test trace-6.201 { set TRACE_OUT } {{SELECT '$::t6str', [$::t6str], 'test-six y''all', 'test-six y''all', "?1", 'test-six y''all' FROM t6}} finish_test ADDED test/triggerD.test Index: test/triggerD.test ================================================================== --- test/triggerD.test +++ test/triggerD.test @@ -0,0 +1,126 @@ +# 2009 December 29 +# +# The author disclaims copyright to this source code. In place of +# a legal notice', here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# Verify that when columns named "rowid", "oid", and "_rowid_" appear +# in a table as ordinary columns (not as the INTEGER PRIMARY KEY) then +# the use of these columns in triggers will refer to the column and not +# to the actual ROWID. Ticket [34d2ae1c6d08b5271ba5e5592936d4a1d913ffe3] +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +ifcapable {!trigger} { + finish_test + return +} + +# Triggers on tables where the table has ordinary columns named +# rowid, oid, and _rowid_. +# +do_test triggerD-1.1 { + db eval { + CREATE TABLE t1(rowid, oid, _rowid_, x); + CREATE TABLE log(a,b,c,d,e); + CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN + INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x); + END; + CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN + INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x); + END; + CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN + INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x); + INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x); + END; + CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x); + INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x); + END; + CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN + INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x); + END; + CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN + INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x); + END; + } +} {} +do_test triggerD-1.2 { + db eval { + INSERT INTO t1 VALUES(100,200,300,400); + SELECT * FROM log + } +} {r1 100 200 300 400 r2 100 200 300 400} +do_test triggerD-1.3 { + db eval { + DELETE FROM log; + UPDATE t1 SET rowid=rowid+1; + SELECT * FROM log + } +} {r3.old 100 200 300 400 r3.new 101 200 300 400 r4.old 100 200 300 400 r4.new 101 200 300 400} +do_test triggerD-1.4 { + db eval { + DELETE FROM log; + DELETE FROM t1; + SELECT * FROM log + } +} {r5 101 200 300 400 r6 101 200 300 400} + +# Triggers on tables where the table does not have ordinary columns named +# rowid, oid, and _rowid_. +# +do_test triggerD-2.1 { + db eval { + DROP TABLE t1; + CREATE TABLE t1(w,x,y,z); + CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN + INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x); + END; + CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN + INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x); + END; + CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN + INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x); + INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x); + END; + CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x); + INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x); + END; + CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN + INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x); + END; + CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN + INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x); + END; + } +} {} +do_test triggerD-2.2 { + db eval { + DELETE FROM log; + INSERT INTO t1 VALUES(100,200,300,400); + SELECT * FROM log; + } +} {r1 -1 -1 -1 200 r2 1 1 1 200} +do_test triggerD-2.3 { + db eval { + DELETE FROM log; + UPDATE t1 SET x=x+1; + SELECT * FROM log + } +} {r3.old 1 1 1 200 r3.new 1 1 1 201 r4.old 1 1 1 200 r4.new 1 1 1 201} +do_test triggerD-2.4 { + db eval { + DELETE FROM log; + DELETE FROM t1; + SELECT * FROM log + } +} {r5 1 1 1 201 r6 1 1 1 201} + +finish_test Index: tool/lemon.c ================================================================== --- tool/lemon.c +++ tool/lemon.c @@ -489,18 +489,20 @@ ** ** Return the offset into the action table of the new transaction. */ int acttab_insert(acttab *p){ int i, j, k, n; + int nActtab; /* Number of slots in the p->aAction[] table */ assert( p->nLookahead>0 ); /* Make sure we have enough space to hold the expanded action table ** in the worst case. The worst case occurs if the transaction set ** must be appended to the current action table */ n = p->mxLookahead + 1; - if( p->nAction + n >= p->nActionAlloc ){ + nActtab = p->nAction + n; + if( nActtab >= p->nActionAlloc ){ int oldAlloc = p->nActionAlloc; p->nActionAlloc = p->nAction + n + p->nActionAlloc + 20; p->aAction = realloc( p->aAction, sizeof(p->aAction[0])*p->nActionAlloc); if( p->aAction==0 ){ @@ -514,15 +516,15 @@ } /* Scan the existing action table looking for an offset where we can ** insert the current transaction set. Fall out of the loop when that ** offset is found. In the worst case, we fall out of the loop when - ** i reaches p->nAction, which means we append the new transaction set. + ** i reaches nActtab, which means we append the new transaction set. ** ** i is the index in p->aAction[] where p->mnLookahead is inserted. */ - for(i=p->nAction-1; i>=0; i--){ + for(i=nActtab-1; i>=0; i--){ /* First look for an existing action table entry that can be reused */ if( p->aAction[i].lookahead==p->mnLookahead ){ if( p->aAction[i].action!=p->mnAction ) continue; for(j=0; jnLookahead; j++){ k = p->aLookahead[j].lookahead - p->mnLookahead + i; @@ -541,11 +543,11 @@ } } } if( i<0 ){ /* If no reusable entry is found, look for an empty slot */ - for(i=0; inAction; i++){ + for(i=0; iaAction[i].lookahead<0 ){ for(j=0; jnLookahead; j++){ k = p->aLookahead[j].lookahead - p->mnLookahead + i; if( k<0 ) break; if( p->aAction[k].lookahead>=0 ) break; Index: tool/mkkeywordhash.c ================================================================== --- tool/mkkeywordhash.c +++ tool/mkkeywordhash.c @@ -594,8 +594,9 @@ printf(" return TK_ID;\n"); printf("}\n"); printf("int sqlite3KeywordCode(const unsigned char *z, int n){\n"); printf(" return keywordCode((char*)z, n);\n"); printf("}\n"); + printf("#define SQLITE_N_KEYWORD %d\n", nKeyword); return 0; } Index: tool/shell1.test ================================================================== --- tool/shell1.test +++ tool/shell1.test @@ -21,11 +21,11 @@ # shell1-3.*: Basic test that "dot" command can be called. # package require sqlite3 -set CLI "./sqlite" +set CLI "./sqlite3" proc do_test {name cmd expected} { puts -nonewline "$name ..." set res [uplevel $cmd] if {$res eq $expected} { @@ -45,11 +45,11 @@ proc catchsql {sql} { set rc [catch {uplevel [list db eval $sql]} msg] list $rc $msg } -proc catchcmd {db cmd} { +proc catchcmd {db {cmd ""}} { global CLI set out [open cmds.txt w] puts $out $cmd close $out set line "exec $CLI $db < cmds.txt" @@ -77,11 +77,11 @@ set rc [lindex $res 0] list $rc \ [regexp {Error: too many options: "select 4"} $res] } {1 1} # error on extra options -do_test shell1-1.3.2 { +do_test shell1-1.1.3 { set res [catchcmd "-bad FOO test.db BAD" ".quit"] set rc [lindex $res 0] list $rc \ [regexp {Error: too many options: "BAD"} $res] } {1 1} @@ -96,11 +96,11 @@ [regexp {\-version} $res] } {1 1 1 1} # -init filename read/process named file do_test shell1-1.3.1 { - catchcmd "-init FOO test.db" "" + catchcmd "-init FOO test.db" "" } {0 {}} do_test shell1-1.3.2 { set res [catchcmd "-init FOO test.db .quit BAD" ""] set rc [lindex $res 0] list $rc \ @@ -193,271 +193,270 @@ } {1 1} # -version show SQLite version do_test shell1-1.16.1 { catchcmd "-version test.db" "" -} {0 3.6.20} +} {0 3.6.22} #---------------------------------------------------------------------------- # Test cases shell1-2.*: Basic "dot" command token parsing. # # check first token handling do_test shell1-2.1.1 { - catchcmd " test.db" ".foo" + catchcmd "test.db" ".foo" } {1 {Error: unknown command or invalid arguments: "foo". Enter ".help" for help}} do_test shell1-2.1.2 { - catchcmd " test.db" ".\"foo OFF\"" + catchcmd "test.db" ".\"foo OFF\"" } {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}} do_test shell1-2.1.3 { - catchcmd " test.db" ".\'foo OFF\'" + catchcmd "test.db" ".\'foo OFF\'" } {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}} # unbalanced quotes do_test shell1-2.2.1 { - catchcmd " test.db" ".\"foo OFF" + catchcmd "test.db" ".\"foo OFF" } {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}} do_test shell1-2.2.2 { - catchcmd " test.db" ".\'foo OFF" + catchcmd "test.db" ".\'foo OFF" } {1 {Error: unknown command or invalid arguments: "foo OFF". Enter ".help" for help}} do_test shell1-2.2.3 { - catchcmd " test.db" ".explain \"OFF" + catchcmd "test.db" ".explain \"OFF" } {0 {}} do_test shell1-2.2.4 { - catchcmd " test.db" ".explain \'OFF" + catchcmd "test.db" ".explain \'OFF" } {0 {}} do_test shell1-2.2.5 { - catchcmd " test.db" ".mode \"insert FOO" + catchcmd "test.db" ".mode \"insert FOO" } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} do_test shell1-2.2.6 { - catchcmd " test.db" ".mode \'insert FOO" + catchcmd "test.db" ".mode \'insert FOO" } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} # check multiple tokens, and quoted tokens do_test shell1-2.3.1 { - catchcmd " test.db" ".explain 1" + catchcmd "test.db" ".explain 1" } {0 {}} do_test shell1-2.3.2 { - catchcmd " test.db" ".explain on" + catchcmd "test.db" ".explain on" } {0 {}} do_test shell1-2.3.3 { - catchcmd " test.db" ".explain \"1 2 3\"" + catchcmd "test.db" ".explain \"1 2 3\"" } {0 {}} do_test shell1-2.3.4 { - catchcmd " test.db" ".explain \"OFF\"" + catchcmd "test.db" ".explain \"OFF\"" } {0 {}} do_test shell1-2.3.5 { - catchcmd " test.db" ".\'explain\' \'OFF\'" + catchcmd "test.db" ".\'explain\' \'OFF\'" } {0 {}} do_test shell1-2.3.6 { - catchcmd " test.db" ".explain \'OFF\'" + catchcmd "test.db" ".explain \'OFF\'" } {0 {}} do_test shell1-2.3.7 { - catchcmd " test.db" ".\'explain\' \'OFF\'" + catchcmd "test.db" ".\'explain\' \'OFF\'" } {0 {}} # check quoted args are unquoted do_test shell1-2.4.1 { - catchcmd " test.db" ".mode FOO" + catchcmd "test.db" ".mode FOO" } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} do_test shell1-2.4.2 { - catchcmd " test.db" ".mode csv" + catchcmd "test.db" ".mode csv" } {0 {}} do_test shell1-2.4.2 { - catchcmd " test.db" ".mode \"csv\"" + catchcmd "test.db" ".mode \"csv\"" } {0 {}} #---------------------------------------------------------------------------- # Test cases shell1-3.*: Basic test that "dot" command can be called. # # .backup ?DB? FILE Backup DB (default "main") to FILE do_test shell1-3.1.1 { - catchcmd " test.db" ".backup" + catchcmd "test.db" ".backup" } {1 {Error: unknown command or invalid arguments: "backup". Enter ".help" for help}} do_test shell1-3.1.2 { - # catchcmd " test.db" ".backup FOO" - #TBD!!! this asserts currently -} {} + catchcmd "test.db" ".backup FOO" +} {0 {}} do_test shell1-3.1.3 { - catchcmd " test.db" ".backup FOO BAR" + catchcmd "test.db" ".backup FOO BAR" } {1 {Error: unknown database FOO}} do_test shell1-3.1.4 { # too many arguments - catchcmd " test.db" ".backup FOO BAR BAD" + catchcmd "test.db" ".backup FOO BAR BAD" } {1 {Error: unknown command or invalid arguments: "backup". Enter ".help" for help}} # .bail ON|OFF Stop after hitting an error. Default OFF do_test shell1-3.2.1 { - catchcmd " test.db" ".bail" + catchcmd "test.db" ".bail" } {1 {Error: unknown command or invalid arguments: "bail". Enter ".help" for help}} do_test shell1-3.2.2 { - catchcmd " test.db" ".bail ON" + catchcmd "test.db" ".bail ON" } {0 {}} do_test shell1-3.2.3 { - catchcmd " test.db" ".bail OFF" + catchcmd "test.db" ".bail OFF" } {0 {}} do_test shell1-3.2.4 { # too many arguments - catchcmd " test.db" ".bail OFF BAD" + catchcmd "test.db" ".bail OFF BAD" } {1 {Error: unknown command or invalid arguments: "bail". Enter ".help" for help}} # .databases List names and files of attached databases do_test shell1-3.3.1 { - set res [catchcmd " test.db" ".databases"] + set res [catchcmd "test.db" ".databases"] regexp {0.*main.*test\.db} $res } {1} do_test shell1-3.3.2 { # too many arguments - catchcmd " test.db" ".databases BAD" + catchcmd "test.db" ".databases BAD" } {1 {Error: unknown command or invalid arguments: "databases". Enter ".help" for help}} # .dump ?TABLE? ... Dump the database in an SQL text format # If TABLE specified, only dump tables matching # LIKE pattern TABLE. do_test shell1-3.4.1 { - set res [catchcmd " test.db" ".dump"] + set res [catchcmd "test.db" ".dump"] list [regexp {BEGIN TRANSACTION;} $res] \ [regexp {COMMIT;} $res] } {1 1} do_test shell1-3.4.2 { - set res [catchcmd " test.db" ".dump FOO"] + set res [catchcmd "test.db" ".dump FOO"] list [regexp {BEGIN TRANSACTION;} $res] \ [regexp {COMMIT;} $res] } {1 1} do_test shell1-3.4.3 { # too many arguments - catchcmd " test.db" ".dump FOO BAD" + catchcmd "test.db" ".dump FOO BAD" } {1 {Error: unknown command or invalid arguments: "dump". Enter ".help" for help}} # .echo ON|OFF Turn command echo on or off do_test shell1-3.5.1 { - catchcmd " test.db" ".echo" + catchcmd "test.db" ".echo" } {1 {Error: unknown command or invalid arguments: "echo". Enter ".help" for help}} do_test shell1-3.5.2 { - catchcmd " test.db" ".echo ON" + catchcmd "test.db" ".echo ON" } {0 {}} do_test shell1-3.5.3 { - catchcmd " test.db" ".echo OFF" + catchcmd "test.db" ".echo OFF" } {0 {}} do_test shell1-3.5.4 { # too many arguments - catchcmd " test.db" ".echo OFF BAD" + catchcmd "test.db" ".echo OFF BAD" } {1 {Error: unknown command or invalid arguments: "echo". Enter ".help" for help}} # .exit Exit this program do_test shell1-3.6.1 { - catchcmd " test.db" ".exit" + catchcmd "test.db" ".exit" } {0 {}} do_test shell1-3.6.2 { # too many arguments - catchcmd " test.db" ".exit BAD" + catchcmd "test.db" ".exit BAD" } {1 {Error: unknown command or invalid arguments: "exit". Enter ".help" for help}} # .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. do_test shell1-3.7.1 { - catchcmd " test.db" ".explain" + catchcmd "test.db" ".explain" # explain is the exception to the booleans. without an option, it turns it on. } {0 {}} do_test shell1-3.7.2 { - catchcmd " test.db" ".explain ON" + catchcmd "test.db" ".explain ON" } {0 {}} do_test shell1-3.7.3 { - catchcmd " test.db" ".explain OFF" + catchcmd "test.db" ".explain OFF" } {0 {}} do_test shell1-3.7.4 { # too many arguments - catchcmd " test.db" ".explain OFF BAD" + catchcmd "test.db" ".explain OFF BAD" } {1 {Error: unknown command or invalid arguments: "explain". Enter ".help" for help}} # .genfkey ?OPTIONS? Options are: # --no-drop: Do not drop old fkey triggers. # --ignore-errors: Ignore tables with fkey errors # --exec: Execute generated SQL immediately # See file tool/genfkey.README in the source # distribution for further information. do_test shell1-3.8.1 { - catchcmd " test.db" ".genfkey" + catchcmd "test.db" ".genfkey" } {0 {}} do_test shell1-3.8.2 { - catchcmd " test.db" ".genfkey FOO" + catchcmd "test.db" ".genfkey FOO" } {1 {unknown option: FOO}} # .header(s) ON|OFF Turn display of headers on or off do_test shell1-3.9.1 { - catchcmd " test.db" ".header" + catchcmd "test.db" ".header" } {1 {Error: unknown command or invalid arguments: "header". Enter ".help" for help}} do_test shell1-3.9.2 { - catchcmd " test.db" ".header ON" + catchcmd "test.db" ".header ON" } {0 {}} do_test shell1-3.9.3 { - catchcmd " test.db" ".header OFF" + catchcmd "test.db" ".header OFF" } {0 {}} do_test shell1-3.9.4 { # too many arguments - catchcmd " test.db" ".header OFF BAD" + catchcmd "test.db" ".header OFF BAD" } {1 {Error: unknown command or invalid arguments: "header". Enter ".help" for help}} do_test shell1-3.9.5 { - catchcmd " test.db" ".headers" + catchcmd "test.db" ".headers" } {1 {Error: unknown command or invalid arguments: "headers". Enter ".help" for help}} do_test shell1-3.9.6 { - catchcmd " test.db" ".headers ON" + catchcmd "test.db" ".headers ON" } {0 {}} do_test shell1-3.9.7 { - catchcmd " test.db" ".headers OFF" + catchcmd "test.db" ".headers OFF" } {0 {}} do_test shell1-3.9.8 { # too many arguments - catchcmd " test.db" ".headers OFF BAD" + catchcmd "test.db" ".headers OFF BAD" } {1 {Error: unknown command or invalid arguments: "headers". Enter ".help" for help}} # .help Show this message do_test shell1-3.10.1 { - set res [catchcmd " test.db" ".help"] + set res [catchcmd "test.db" ".help"] # look for a few of the possible help commands list [regexp {.help} $res] \ [regexp {.quit} $res] \ [regexp {.show} $res] } {1 1 1} do_test shell1-3.10.2 { # we allow .help to take extra args (it is help after all) - set res [catchcmd " test.db" ".help BAD"] + set res [catchcmd "test.db" ".help BAD"] # look for a few of the possible help commands list [regexp {.help} $res] \ [regexp {.quit} $res] \ [regexp {.show} $res] } {1 1 1} # .import FILE TABLE Import data from FILE into TABLE do_test shell1-3.11.1 { - catchcmd " test.db" ".import" + catchcmd "test.db" ".import" } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}} do_test shell1-3.11.2 { - catchcmd " test.db" ".import FOO" + catchcmd "test.db" ".import FOO" } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}} do_test shell1-3.11.2 { - catchcmd " test.db" ".import FOO BAR" + catchcmd "test.db" ".import FOO BAR" } {1 {Error: no such table: BAR}} do_test shell1-3.11.3 { # too many arguments - catchcmd " test.db" ".import FOO BAR BAD" + catchcmd "test.db" ".import FOO BAR BAD" } {1 {Error: unknown command or invalid arguments: "import". Enter ".help" for help}} # .indices ?TABLE? Show names of all indices # If TABLE specified, only show indices for tables # matching LIKE pattern TABLE. do_test shell1-3.12.1 { - catchcmd " test.db" ".indices" + catchcmd "test.db" ".indices" } {0 {}} do_test shell1-3.12.2 { - catchcmd " test.db" ".indices FOO" + catchcmd "test.db" ".indices FOO" } {0 {}} do_test shell1-3.12.3 { # too many arguments - catchcmd " test.db" ".indices FOO BAD" + catchcmd "test.db" ".indices FOO BAD" } {1 {Error: unknown command or invalid arguments: "indices". Enter ".help" for help}} # .mode MODE ?TABLE? Set output mode where MODE is one of: # csv Comma-separated values # column Left-aligned columns. (See .width) @@ -466,170 +465,169 @@ # line One value per line # list Values delimited by .separator string # tabs Tab-separated values # tcl TCL list elements do_test shell1-3.13.1 { - catchcmd " test.db" ".mode" + catchcmd "test.db" ".mode" } {1 {Error: unknown command or invalid arguments: "mode". Enter ".help" for help}} do_test shell1-3.13.2 { - catchcmd " test.db" ".mode FOO" + catchcmd "test.db" ".mode FOO" } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} do_test shell1-3.13.3 { - catchcmd " test.db" ".mode csv" + catchcmd "test.db" ".mode csv" } {0 {}} do_test shell1-3.13.4 { - catchcmd " test.db" ".mode column" + catchcmd "test.db" ".mode column" } {0 {}} do_test shell1-3.13.5 { - catchcmd " test.db" ".mode html" + catchcmd "test.db" ".mode html" } {0 {}} do_test shell1-3.13.6 { - catchcmd " test.db" ".mode insert" + catchcmd "test.db" ".mode insert" } {0 {}} do_test shell1-3.13.7 { - catchcmd " test.db" ".mode line" + catchcmd "test.db" ".mode line" } {0 {}} do_test shell1-3.13.8 { - catchcmd " test.db" ".mode list" + catchcmd "test.db" ".mode list" } {0 {}} do_test shell1-3.13.9 { - catchcmd " test.db" ".mode tabs" + catchcmd "test.db" ".mode tabs" } {0 {}} do_test shell1-3.13.10 { - catchcmd " test.db" ".mode tcl" + catchcmd "test.db" ".mode tcl" } {0 {}} do_test shell1-3.13.11 { # too many arguments - catchcmd " test.db" ".mode tcl BAD" + catchcmd "test.db" ".mode tcl BAD" } {1 {Error: invalid arguments: "BAD". Enter ".help" for help}} # don't allow partial mode type matches do_test shell1-3.13.12 { - catchcmd " test.db" ".mode l" + catchcmd "test.db" ".mode l" } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} do_test shell1-3.13.13 { - catchcmd " test.db" ".mode li" + catchcmd "test.db" ".mode li" } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} do_test shell1-3.13.14 { - catchcmd " test.db" ".mode lin" + catchcmd "test.db" ".mode lin" } {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} # .nullvalue STRING Print STRING in place of NULL values do_test shell1-3.14.1 { - catchcmd " test.db" ".nullvalue" + catchcmd "test.db" ".nullvalue" } {1 {Error: unknown command or invalid arguments: "nullvalue". Enter ".help" for help}} do_test shell1-3.14.2 { - catchcmd " test.db" ".nullvalue FOO" + catchcmd "test.db" ".nullvalue FOO" } {0 {}} do_test shell1-3.14.3 { # too many arguments - catchcmd " test.db" ".nullvalue FOO BAD" + catchcmd "test.db" ".nullvalue FOO BAD" } {1 {Error: unknown command or invalid arguments: "nullvalue". Enter ".help" for help}} # .output FILENAME Send output to FILENAME do_test shell1-3.15.1 { - catchcmd " test.db" ".output" + catchcmd "test.db" ".output" } {1 {Error: unknown command or invalid arguments: "output". Enter ".help" for help}} do_test shell1-3.15.2 { - catchcmd " test.db" ".output FOO" + catchcmd "test.db" ".output FOO" } {0 {}} do_test shell1-3.15.3 { # too many arguments - catchcmd " test.db" ".output FOO BAD" + catchcmd "test.db" ".output FOO BAD" } {1 {Error: unknown command or invalid arguments: "output". Enter ".help" for help}} # .output stdout Send output to the screen do_test shell1-3.16.1 { - catchcmd " test.db" ".output stdout" + catchcmd "test.db" ".output stdout" } {0 {}} do_test shell1-3.16.2 { # too many arguments - catchcmd " test.db" ".output stdout BAD" + catchcmd "test.db" ".output stdout BAD" } {1 {Error: unknown command or invalid arguments: "output". Enter ".help" for help}} # .prompt MAIN CONTINUE Replace the standard prompts do_test shell1-3.17.1 { - catchcmd " test.db" ".prompt" + catchcmd "test.db" ".prompt" } {1 {Error: unknown command or invalid arguments: "prompt". Enter ".help" for help}} do_test shell1-3.17.2 { - catchcmd " test.db" ".prompt FOO" + catchcmd "test.db" ".prompt FOO" } {0 {}} do_test shell1-3.17.3 { - catchcmd " test.db" ".prompt FOO BAR" + catchcmd "test.db" ".prompt FOO BAR" } {0 {}} do_test shell1-3.17.4 { # too many arguments - catchcmd " test.db" ".prompt FOO BAR BAD" + catchcmd "test.db" ".prompt FOO BAR BAD" } {1 {Error: unknown command or invalid arguments: "prompt". Enter ".help" for help}} # .quit Exit this program do_test shell1-3.18.1 { - catchcmd " test.db" ".quit" + catchcmd "test.db" ".quit" } {0 {}} do_test shell1-3.18.2 { # too many arguments - catchcmd " test.db" ".quit BAD" + catchcmd "test.db" ".quit BAD" } {1 {Error: unknown command or invalid arguments: "quit". Enter ".help" for help}} # .read FILENAME Execute SQL in FILENAME do_test shell1-3.19.1 { - catchcmd " test.db" ".read" + catchcmd "test.db" ".read" } {1 {Error: unknown command or invalid arguments: "read". Enter ".help" for help}} do_test shell1-3.19.2 { file delete -force FOO - catchcmd " test.db" ".read FOO" + catchcmd "test.db" ".read FOO" } {1 {Error: cannot open "FOO"}} do_test shell1-3.19.3 { # too many arguments - catchcmd " test.db" ".read FOO BAD" + catchcmd "test.db" ".read FOO BAD" } {1 {Error: unknown command or invalid arguments: "read". Enter ".help" for help}} # .restore ?DB? FILE Restore content of DB (default "main") from FILE do_test shell1-3.20.1 { - catchcmd " test.db" ".restore" + catchcmd "test.db" ".restore" } {1 {Error: unknown command or invalid arguments: "restore". Enter ".help" for help}} do_test shell1-3.20.2 { - # catchcmd " test.db" ".restore FOO" - #TBD!!! this asserts currently -} {} + catchcmd "test.db" ".restore FOO" +} {0 {}} do_test shell1-3.20.3 { - catchcmd " test.db" ".restore FOO BAR" + catchcmd "test.db" ".restore FOO BAR" } {1 {Error: unknown database FOO}} do_test shell1-3.20.4 { # too many arguments - catchcmd " test.db" ".restore FOO BAR BAD" + catchcmd "test.db" ".restore FOO BAR BAD" } {1 {Error: unknown command or invalid arguments: "restore". Enter ".help" for help}} # .schema ?TABLE? Show the CREATE statements # If TABLE specified, only show tables matching # LIKE pattern TABLE. do_test shell1-3.21.1 { - catchcmd " test.db" ".schema" + catchcmd "test.db" ".schema" } {0 {}} do_test shell1-3.21.2 { - catchcmd " test.db" ".schema FOO" + catchcmd "test.db" ".schema FOO" } {0 {}} do_test shell1-3.21.3 { # too many arguments - catchcmd " test.db" ".schema FOO BAD" + catchcmd "test.db" ".schema FOO BAD" } {1 {Error: unknown command or invalid arguments: "schema". Enter ".help" for help}} # .separator STRING Change separator used by output mode and .import do_test shell1-3.22.1 { - catchcmd " test.db" ".separator" + catchcmd "test.db" ".separator" } {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}} do_test shell1-3.22.2 { - catchcmd " test.db" ".separator FOO" + catchcmd "test.db" ".separator FOO" } {0 {}} do_test shell1-3.22.3 { # too many arguments - catchcmd " test.db" ".separator FOO BAD" + catchcmd "test.db" ".separator FOO BAD" } {1 {Error: unknown command or invalid arguments: "separator". Enter ".help" for help}} # .show Show the current values for various settings do_test shell1-3.23.1 { - set res [catchcmd " test.db" ".show"] + set res [catchcmd "test.db" ".show"] list [regexp {echo:} $res] \ [regexp {explain:} $res] \ [regexp {headers:} $res] \ [regexp {mode:} $res] \ [regexp {nullvalue:} $res] \ @@ -637,71 +635,72 @@ [regexp {separator:} $res] \ [regexp {width:} $res] } {1 1 1 1 1 1 1 1} do_test shell1-3.23.2 { # too many arguments - catchcmd " test.db" ".show BAD" + catchcmd "test.db" ".show BAD" } {1 {Error: unknown command or invalid arguments: "show". Enter ".help" for help}} # .tables ?TABLE? List names of tables # If TABLE specified, only list tables matching # LIKE pattern TABLE. do_test shell1-3.24.1 { - catchcmd " test.db" ".tables" + catchcmd "test.db" ".tables" } {0 {}} do_test shell1-3.24.2 { - catchcmd " test.db" ".tables FOO" + catchcmd "test.db" ".tables FOO" } {0 {}} do_test shell1-3.24.3 { # too many arguments - catchcmd " test.db" ".tables FOO BAD" + catchcmd "test.db" ".tables FOO BAD" } {1 {Error: unknown command or invalid arguments: "tables". Enter ".help" for help}} # .timeout MS Try opening locked tables for MS milliseconds do_test shell1-3.25.1 { - catchcmd " test.db" ".timeout" + catchcmd "test.db" ".timeout" } {1 {Error: unknown command or invalid arguments: "timeout". Enter ".help" for help}} do_test shell1-3.25.2 { - catchcmd " test.db" ".timeout zzz" + catchcmd "test.db" ".timeout zzz" # this should be treated the same as a '0' timeout } {0 {}} do_test shell1-3.25.3 { - catchcmd " test.db" ".timeout 1" + catchcmd "test.db" ".timeout 1" } {0 {}} do_test shell1-3.25.4 { # too many arguments - catchcmd " test.db" ".timeout 1 BAD" + catchcmd "test.db" ".timeout 1 BAD" } {1 {Error: unknown command or invalid arguments: "timeout". Enter ".help" for help}} # .width NUM NUM ... Set column widths for "column" mode do_test shell1-3.26.1 { - catchcmd " test.db" ".width" + catchcmd "test.db" ".width" } {1 {Error: unknown command or invalid arguments: "width". Enter ".help" for help}} do_test shell1-3.26.2 { - catchcmd " test.db" ".width xxx" + catchcmd "test.db" ".width xxx" # this should be treated the same as a '0' width for col 1 } {0 {}} do_test shell1-3.26.3 { - catchcmd " test.db" ".width xxx yyy" + catchcmd "test.db" ".width xxx yyy" # this should be treated the same as a '0' width for col 1 and 2 } {0 {}} do_test shell1-3.26.4 { - catchcmd " test.db" ".width 1 1" + catchcmd "test.db" ".width 1 1" # this should be treated the same as a '1' width for col 1 and 2 } {0 {}} # .timer ON|OFF Turn the CPU timer measurement on or off do_test shell1-3.27.1 { - catchcmd " test.db" ".timer" + catchcmd "test.db" ".timer" } {1 {Error: unknown command or invalid arguments: "timer". Enter ".help" for help}} do_test shell1-3.27.2 { - catchcmd " test.db" ".timer ON" + catchcmd "test.db" ".timer ON" } {0 {}} do_test shell1-3.27.3 { - catchcmd " test.db" ".timer OFF" + catchcmd "test.db" ".timer OFF" } {0 {}} do_test shell1-3.27.4 { # too many arguments - catchcmd " test.db" ".timer OFF BAD" + catchcmd "test.db" ".timer OFF BAD" } {1 {Error: unknown command or invalid arguments: "timer". Enter ".help" for help}} -# + +# Index: tool/shell2.test ================================================================== --- tool/shell2.test +++ tool/shell2.test @@ -19,11 +19,11 @@ # shell2-1.*: Misc. test of various tickets and reported errors. # package require sqlite3 -set CLI "./sqlite" +set CLI "./sqlite3" proc do_test {name cmd expected} { puts -nonewline "$name ..." set res [uplevel $cmd] if {$res eq $expected} { @@ -43,11 +43,11 @@ proc catchsql {sql} { set rc [catch {uplevel [list db eval $sql]} msg] list $rc $msg } -proc catchcmd {db cmd} { +proc catchcmd {db {cmd ""}} { global CLI set out [open cmds.txt w] puts $out $cmd close $out set line "exec $CLI $db < cmds.txt" @@ -79,7 +79,23 @@ set rc [catch { eval exec $CLI \":memory:\" \"select 3\" \"select 4\" } msg] list $rc \ [regexp {Error: too many options: "select 4"} $msg] } {1 1} - +# Test a problem reported on the mailing list. The shell was at one point +# returning the generic SQLITE_ERROR message ("SQL error or missing database") +# instead of the "too many levels..." message in the test below. +# +do_test shell2-1.3 { + catchcmd "-batch test.db" { + PRAGMA recursive_triggers = ON; + CREATE TABLE t5(a PRIMARY KEY, b, c); + INSERT INTO t5 VALUES(1, 2, 3); + CREATE TRIGGER au_tble AFTER UPDATE ON t5 BEGIN + UPDATE OR IGNORE t5 SET a = new.a, c = 10; + END; + + UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1; + } +} {1 {Error: near line 9: too many levels of trigger recursion}} + ADDED tool/shell3.test Index: tool/shell3.test ================================================================== --- tool/shell3.test +++ tool/shell3.test @@ -0,0 +1,123 @@ +# 2009 Dec 16 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# The focus of this file is testing the CLI shell tool. +# +# $Id: shell2.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $ +# + +# Test plan: +# +# shell3-1.*: Basic tests for running SQL statments from command line. +# shell3-2.*: Basic tests for running SQL file from command line. +# + +package require sqlite3 + +set CLI "./sqlite3" + +proc do_test {name cmd expected} { + puts -nonewline "$name ..." + set res [uplevel $cmd] + if {$res eq $expected} { + puts Ok + } else { + puts Error + puts " Got: $res" + puts " Expected: $expected" + exit + } +} + +proc execsql {sql} { + uplevel [list db eval $sql] +} + +proc catchsql {sql} { + set rc [catch {uplevel [list db eval $sql]} msg] + list $rc $msg +} + +proc catchcmd {db {cmd ""}} { + global CLI + set out [open cmds.txt w] + puts $out $cmd + close $out + set line "exec $CLI $db < cmds.txt" + set rc [catch { eval $line } msg] + list $rc $msg +} + +file delete -force test.db test.db.journal +sqlite3 db test.db + + +#---------------------------------------------------------------------------- +# shell3-1.*: Basic tests for running SQL statments from command line. +# + +# Run SQL statement from command line +do_test shell3-1.1 { + file delete -force foo.db + set rc [ catchcmd "foo.db \"CREATE TABLE t1(a);\"" ] + set fexist [file exist foo.db] + list $rc $fexist +} {{0 {}} 1} +do_test shell3-1.2 { + catchcmd "foo.db" ".tables" +} {0 t1} +do_test shell3-1.3 { + catchcmd "foo.db \"DROP TABLE t1;\"" +} {0 {}} +do_test shell3-1.4 { + catchcmd "foo.db" ".tables" +} {0 {}} +do_test shell3-1.5 { + catchcmd "foo.db \"CREATE TABLE t1(a); DROP TABLE t1;\"" +} {0 {}} +do_test shell3-1.6 { + catchcmd "foo.db" ".tables" +} {0 {}} +do_test shell3-1.7 { + catchcmd "foo.db \"CREATE TABLE\"" +} {1 {Error: near "TABLE": syntax error}} + +#---------------------------------------------------------------------------- +# shell3-2.*: Basic tests for running SQL file from command line. +# + +# Run SQL file from command line +do_test shell3-2.1 { + file delete -force foo.db + set rc [ catchcmd "foo.db" "CREATE TABLE t1(a);" ] + set fexist [file exist foo.db] + list $rc $fexist +} {{0 {}} 1} +do_test shell3-2.2 { + catchcmd "foo.db" ".tables" +} {0 t1} +do_test shell3-2.3 { + catchcmd "foo.db" "DROP TABLE t1;" +} {0 {}} +do_test shell3-2.4 { + catchcmd "foo.db" ".tables" +} {0 {}} +do_test shell3-2.5 { + catchcmd "foo.db" "CREATE TABLE t1(a); DROP TABLE t1;" +} {0 {}} +do_test shell3-2.6 { + catchcmd "foo.db" ".tables" +} {0 {}} +do_test shell3-2.7 { + catchcmd "foo.db" "CREATE TABLE" +} {1 {Error: incomplete SQL: CREATE TABLE}} + +