Index: Makefile.in ================================================================== --- Makefile.in +++ Makefile.in @@ -1185,10 +1185,26 @@ $(TCLSH_CMD) $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c sqlite3_analyzer$(TEXE): sqlite3_analyzer.c $(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS) +CHECKER_DEPS =\ + $(TOP)/tool/mkccode.tcl \ + sqlite3.c \ + $(TOP)/src/tclsqlite.c \ + $(TOP)/ext/repair/sqlite3_checker.tcl \ + $(TOP)/ext/repair/checkindex.c \ + $(TOP)/ext/repair/checkfreelist.c \ + $(TOP)/ext/misc/btreeinfo.c \ + $(TOP)/ext/repair/sqlite3_checker.c.in + +sqlite3_checker.c: $(CHECKER_DEPS) + $(TCLSH_CMD) $(TOP)/tool/mkccode.tcl $(TOP)/ext/repair/sqlite3_checker.c.in >$@ + +sqlite3_checker$(TEXE): sqlite3_checker.c + $(LTLINK) sqlite3_checker.c -o $@ $(LIBTCL) $(TLIBS) + dbdump$(TEXE): $(TOP)/ext/misc/dbdump.c sqlite3.lo $(LTLINK) -DDBDUMP_STANDALONE -o $@ \ $(TOP)/ext/misc/dbdump.c sqlite3.lo $(TLIBS) showdb$(TEXE): $(TOP)/tool/showdb.c sqlite3.lo Index: Makefile.msc ================================================================== --- Makefile.msc +++ Makefile.msc @@ -1489,10 +1489,11 @@ # TESTPROGS = \ testfixture.exe \ $(SQLITE3EXE) \ sqlite3_analyzer.exe \ + sqlite3_checker.exe \ sqldiff.exe \ dbhash.exe # Databases containing fuzzer test cases # @@ -2195,10 +2196,27 @@ sqlite3_analyzer.exe: sqlite3_analyzer.c $(LIBRESOBJS) $(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_analyzer.c \ /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS) +CHECKER_DEPS =\ + $(TOP)/tool/mkccode.tcl \ + sqlite3.c \ + $(TOP)/src/tclsqlite.c \ + $(TOP)/ext/repair/sqlite3_checker.tcl \ + $(TOP)/ext/repair/checkindex.c \ + $(TOP)/ext/repair/checkfreelist.c \ + $(TOP)/ext/misc/btreeinfo.c \ + $(TOP)/ext/repair/sqlite3_checker.c.in + +sqlite3_checker.c: $(CHECKER_DEPS) + $(TCLSH_CMD) $(TOP)\tool\mkccode.tcl $(TOP)\ext\repair\sqlite3_checker.c.in > $@ + +sqlite3_checker.exe: sqlite3_checker.c $(LIBRESOBJS) + $(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_checker.c \ + /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS) + dbdump.exe: $(TOP)\ext\misc\dbdump.c $(SQLITE3C) $(SQLITE3H) $(LTLINK) $(NO_WARN) -DDBDUMP_STANDALONE $(TOP)\ext\misc\dbdump.c $(SQLITE3C) \ /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) testloadext.lo: $(TOP)\src\test_loadext.c Index: ext/misc/btreeinfo.c ================================================================== --- ext/misc/btreeinfo.c +++ ext/misc/btreeinfo.c @@ -59,11 +59,13 @@ ** Show the names of all WITHOUT ROWID tables: ** ** SELECT name FROM sqlite_btreeinfo ** WHERE type='table' AND NOT hasRowid; */ -#include +#if !defined(SQLITEINT_H) +#include "sqlite3ext.h" +#endif SQLITE_EXTENSION_INIT1 #include #include /* Columns available in this virtual table */ ADDED ext/repair/checkindex.c Index: ext/repair/checkindex.c ================================================================== --- /dev/null +++ ext/repair/checkindex.c @@ -0,0 +1,828 @@ +/* +** 2017 October 27 +** +** 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. +** +************************************************************************* +*/ + +#include "sqlite3ext.h" +SQLITE_EXTENSION_INIT1 + +#ifndef SQLITE_AMALGAMATION +# include +# include +# include +# include +# define ALWAYS(X) 1 +# define NEVER(X) 0 + typedef unsigned char u8; + typedef unsigned short u16; + typedef unsigned int u32; +#define get4byte(x) ( \ + ((u32)((x)[0])<<24) + \ + ((u32)((x)[1])<<16) + \ + ((u32)((x)[2])<<8) + \ + ((u32)((x)[3])) \ +) +#endif + +typedef struct CidxTable CidxTable; +typedef struct CidxCursor CidxCursor; + +struct CidxTable { + sqlite3_vtab base; /* Base class. Must be first */ + sqlite3 *db; +}; + +struct CidxCursor { + sqlite3_vtab_cursor base; /* Base class. Must be first */ + sqlite3_int64 iRowid; + sqlite3_stmt *pStmt; +}; + +typedef struct CidxColumn CidxColumn; +struct CidxColumn { + char *zExpr; /* Text for indexed expression */ + int bDesc; /* True for DESC columns, otherwise false */ + int bKey; /* Part of index, not PK */ +}; + +typedef struct CidxIndex CidxIndex; +struct CidxIndex { + int nCol; /* Elements in aCol[] array */ + CidxColumn aCol[1]; /* Array of indexed columns */ +}; + +static void *cidxMalloc(int *pRc, int n){ + void *pRet = 0; + assert( n!=0 ); + if( *pRc==SQLITE_OK ){ + pRet = sqlite3_malloc(n); + if( pRet ){ + memset(pRet, 0, n); + }else{ + *pRc = SQLITE_NOMEM; + } + } + return pRet; +} + +static void cidxCursorError(CidxCursor *pCsr, const char *zFmt, ...){ + va_list ap; + va_start(ap, zFmt); + assert( pCsr->base.pVtab->zErrMsg==0 ); + pCsr->base.pVtab->zErrMsg = sqlite3_vmprintf(zFmt, ap); + va_end(ap); +} + +/* +** Connect to then incremental_index_check virtual table. +*/ +static int cidxConnect( + sqlite3 *db, + void *pAux, + int argc, const char *const*argv, + sqlite3_vtab **ppVtab, + char **pzErr +){ + int rc = SQLITE_OK; + CidxTable *pRet; + +#define IIC_ERRMSG 0 +#define IIC_CURRENT_KEY 1 +#define IIC_INDEX_NAME 2 +#define IIC_AFTER_KEY 3 + rc = sqlite3_declare_vtab(db, + "CREATE TABLE xyz(" + " errmsg TEXT, current_key TEXT," + " index_name HIDDEN, after_key HIDDEN" + ")" + ); + pRet = cidxMalloc(&rc, sizeof(CidxTable)); + if( pRet ){ + pRet->db = db; + } + + *ppVtab = (sqlite3_vtab*)pRet; + return rc; +} + +/* +** Disconnect from or destroy an incremental_index_check virtual table. +*/ +static int cidxDisconnect(sqlite3_vtab *pVtab){ + CidxTable *pTab = (CidxTable*)pVtab; + sqlite3_free(pTab); + return SQLITE_OK; +} + +/* +** xBestIndex method. +*/ +static int cidxBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pInfo){ + int iIdxName = -1; + int iAfterKey = -1; + int i; + + for(i=0; inConstraint; i++){ + struct sqlite3_index_constraint *p = &pInfo->aConstraint[i]; + if( p->usable==0 ) continue; + if( p->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue; + + if( p->iColumn==IIC_INDEX_NAME ){ + iIdxName = i; + } + if( p->iColumn==IIC_AFTER_KEY ){ + iAfterKey = i; + } + } + + if( iIdxName<0 ){ + pInfo->estimatedCost = 1000000000.0; + }else{ + pInfo->aConstraintUsage[iIdxName].argvIndex = 1; + pInfo->aConstraintUsage[iIdxName].omit = 1; + if( iAfterKey<0 ){ + pInfo->estimatedCost = 1000000.0; + }else{ + pInfo->aConstraintUsage[iAfterKey].argvIndex = 2; + pInfo->aConstraintUsage[iAfterKey].omit = 1; + pInfo->estimatedCost = 1000.0; + } + } + + return SQLITE_OK; +} + +/* +** Open a new btreeinfo cursor. +*/ +static int cidxOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ + CidxCursor *pRet; + int rc = SQLITE_OK; + + pRet = cidxMalloc(&rc, sizeof(CidxCursor)); + + *ppCursor = (sqlite3_vtab_cursor*)pRet; + return rc; +} + +/* +** Close a btreeinfo cursor. +*/ +static int cidxClose(sqlite3_vtab_cursor *pCursor){ + CidxCursor *pCsr = (CidxCursor*)pCursor; + sqlite3_finalize(pCsr->pStmt); + pCsr->pStmt = 0; + sqlite3_free(pCsr); + return SQLITE_OK; +} + +/* +** Move a btreeinfo cursor to the next entry in the file. +*/ +static int cidxNext(sqlite3_vtab_cursor *pCursor){ + CidxCursor *pCsr = (CidxCursor*)pCursor; + int rc = sqlite3_step(pCsr->pStmt); + if( rc!=SQLITE_ROW ){ + rc = sqlite3_finalize(pCsr->pStmt); + pCsr->pStmt = 0; + if( rc!=SQLITE_OK ){ + sqlite3 *db = ((CidxTable*)pCsr->base.pVtab)->db; + cidxCursorError(pCsr, "Cursor error: %s", sqlite3_errmsg(db)); + } + }else{ + pCsr->iRowid++; + rc = SQLITE_OK; + } + return rc; +} + +/* We have reached EOF if previous sqlite3_step() returned +** anything other than SQLITE_ROW; +*/ +static int cidxEof(sqlite3_vtab_cursor *pCursor){ + CidxCursor *pCsr = (CidxCursor*)pCursor; + return pCsr->pStmt==0; +} + +static char *cidxMprintf(int *pRc, const char *zFmt, ...){ + char *zRet = 0; + va_list ap; + va_start(ap, zFmt); + zRet = sqlite3_vmprintf(zFmt, ap); + if( *pRc==SQLITE_OK ){ + if( zRet==0 ){ + *pRc = SQLITE_NOMEM; + } + }else{ + sqlite3_free(zRet); + zRet = 0; + } + va_end(ap); + return zRet; +} + +static sqlite3_stmt *cidxPrepare( + int *pRc, CidxCursor *pCsr, const char *zFmt, ... +){ + sqlite3_stmt *pRet = 0; + char *zSql; + va_list ap; /* ... printf arguments */ + va_start(ap, zFmt); + + zSql = sqlite3_vmprintf(zFmt, ap); + if( *pRc==SQLITE_OK ){ + if( zSql==0 ){ + *pRc = SQLITE_NOMEM; + }else{ + sqlite3 *db = ((CidxTable*)pCsr->base.pVtab)->db; + *pRc = sqlite3_prepare_v2(db, zSql, -1, &pRet, 0); + if( *pRc!=SQLITE_OK ){ + cidxCursorError(pCsr, "SQL error: %s", sqlite3_errmsg(db)); + } + } + } + sqlite3_free(zSql); + va_end(ap); + + return pRet; +} + +static void cidxFinalize(int *pRc, sqlite3_stmt *pStmt){ + int rc = sqlite3_finalize(pStmt); + if( *pRc==SQLITE_OK ) *pRc = rc; +} + +char *cidxStrdup(int *pRc, const char *zStr){ + char *zRet = 0; + if( *pRc==SQLITE_OK ){ + int n = strlen(zStr); + zRet = cidxMalloc(pRc, n+1); + if( zRet ) memcpy(zRet, zStr, n+1); + } + return zRet; +} + +static void cidxFreeIndex(CidxIndex *pIdx){ + if( pIdx ){ + int i; + for(i=0; inCol; i++){ + sqlite3_free(pIdx->aCol[i].zExpr); + } + sqlite3_free(pIdx); + } +} + +static int cidx_isspace(char c){ + return c==' ' || c=='\t' || c=='\r' || c=='\n'; +} + +static int cidx_isident(char c){ + return c<0 + || (c>='0' && c<='9') || (c>='a' && c<='z') + || (c>='A' && c<='Z') || c=='_'; +} + +#define CIDX_PARSE_EOF 0 +#define CIDX_PARSE_COMMA 1 /* "," */ +#define CIDX_PARSE_OPEN 2 /* "(" */ +#define CIDX_PARSE_CLOSE 3 /* ")" */ + +static int cidxFindNext( + const char *zIn, + const char **pzOut, + int *pbDoNotTrim /* OUT: True if prev is -- comment */ +){ + const char *z = zIn; + + while( 1 ){ + if( z[0]=='-' && z[1]=='-' ){ + z += 2; + while( z[0]!='\n' ){ + if( z[0]=='\0' ) return CIDX_PARSE_EOF; + z++; + } + while( cidx_isspace(*z) ) z++; + *pbDoNotTrim = 1; + }else{ + *pzOut = z; + switch( *z ){ + case '\0': + return CIDX_PARSE_EOF; + case '(': + return CIDX_PARSE_OPEN; + case ')': + return CIDX_PARSE_CLOSE; + case ',': + return CIDX_PARSE_COMMA; + + case '"': + case '\'': + case '`': { + char q = *z; + z++; + while( *z ){ + if( *z==q ){ + z++; + if( *z!=q ) break; + } + z++; + } + break; + } + + case '[': + while( *z++!=']' ); + break; + + case '/': + if( z[1]=='*' ){ + z += 2; + while( z[0]!='*' || z[1]!='/' ){ + if( z[1]=='\0' ) return CIDX_PARSE_EOF; + z++; + } + z += 2; + break; + } + + default: + z++; + break; + } + *pbDoNotTrim = 0; + } + } + + assert( 0 ); + return -1; +} + +static int cidxParseSQL(CidxCursor *pCsr, CidxIndex *pIdx, const char *zSql){ + const char *z = zSql; + const char *z1; + int e; + int rc = SQLITE_OK; + int nParen = 1; + int bDoNotTrim = 0; + CidxColumn *pCol = pIdx->aCol; + + e = cidxFindNext(z, &z, &bDoNotTrim); + if( e!=CIDX_PARSE_OPEN ) goto parse_error; + z1 = z+1; + z++; + while( nParen>0 ){ + e = cidxFindNext(z, &z, &bDoNotTrim); + if( e==CIDX_PARSE_EOF ) goto parse_error; + if( (e==CIDX_PARSE_COMMA || e==CIDX_PARSE_CLOSE) && nParen==1 ){ + const char *z2 = z; + if( pCol->zExpr ) goto parse_error; + + if( bDoNotTrim==0 ){ + while( cidx_isspace(z[-1]) ) z--; + if( !sqlite3_strnicmp(&z[-3], "asc", 3) && 0==cidx_isident(z[-4]) ){ + z -= 3; + while( cidx_isspace(z[-1]) ) z--; + }else + if( !sqlite3_strnicmp(&z[-4], "desc", 4) && 0==cidx_isident(z[-5]) ){ + z -= 4; + while( cidx_isspace(z[-1]) ) z--; + } + while( cidx_isspace(z1[0]) ) z1++; + } + + pCol->zExpr = cidxMprintf(&rc, "%.*s", z-z1, z1); + pCol++; + z = z1 = z2+1; + } + if( e==CIDX_PARSE_OPEN ) nParen++; + if( e==CIDX_PARSE_CLOSE ) nParen--; + z++; + } + + return rc; + + parse_error: + cidxCursorError(pCsr, "Parse error in: %s", zSql); + return SQLITE_ERROR; +} + +static int cidxLookupIndex( + CidxCursor *pCsr, /* Cursor object */ + const char *zIdx, /* Name of index to look up */ + CidxIndex **ppIdx, /* OUT: Description of columns */ + char **pzTab /* OUT: Table name */ +){ + int rc = SQLITE_OK; + char *zTab = 0; + CidxIndex *pIdx = 0; + + sqlite3_stmt *pFindTab = 0; + sqlite3_stmt *pInfo = 0; + + /* Find the table for this index. */ + pFindTab = cidxPrepare(&rc, pCsr, + "SELECT tbl_name, sql FROM sqlite_master WHERE name=%Q AND type='index'", + zIdx + ); + if( rc==SQLITE_OK && sqlite3_step(pFindTab)==SQLITE_ROW ){ + const char *zSql = (const char*)sqlite3_column_text(pFindTab, 1); + zTab = cidxStrdup(&rc, (const char*)sqlite3_column_text(pFindTab, 0)); + + pInfo = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx); + if( rc==SQLITE_OK ){ + int nAlloc = 0; + int iCol = 0; + + while( sqlite3_step(pInfo)==SQLITE_ROW ){ + const char *zName = (const char*)sqlite3_column_text(pInfo, 2); + const char *zColl = (const char*)sqlite3_column_text(pInfo, 4); + CidxColumn *p; + if( zName==0 ) zName = "rowid"; + if( iCol==nAlloc ){ + int nByte = sizeof(CidxIndex) + sizeof(CidxColumn)*(nAlloc+8); + pIdx = (CidxIndex*)sqlite3_realloc(pIdx, nByte); + nAlloc += 8; + } + p = &pIdx->aCol[iCol++]; + p->bDesc = sqlite3_column_int(pInfo, 3); + p->bKey = sqlite3_column_int(pInfo, 5); + if( zSql==0 || p->bKey==0 ){ + p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl); + }else{ + p->zExpr = 0; + } + pIdx->nCol = iCol; + } + cidxFinalize(&rc, pInfo); + } + + if( rc==SQLITE_OK && zSql ){ + rc = cidxParseSQL(pCsr, pIdx, zSql); + } + } + + cidxFinalize(&rc, pFindTab); + if( rc==SQLITE_OK && zTab==0 ){ + rc = SQLITE_ERROR; + } + + if( rc!=SQLITE_OK ){ + sqlite3_free(zTab); + cidxFreeIndex(pIdx); + }else{ + *pzTab = zTab; + *ppIdx = pIdx; + } + + return rc; +} + +static int cidxDecodeAfter( + CidxCursor *pCsr, + int nCol, + const char *zAfterKey, + char ***pazAfter +){ + char **azAfter; + int rc = SQLITE_OK; + int nAfterKey = strlen(zAfterKey); + + azAfter = cidxMalloc(&rc, sizeof(char*)*nCol + nAfterKey+1); + if( rc==SQLITE_OK ){ + int i; + char *zCopy = (char*)&azAfter[nCol]; + char *p = zCopy; + memcpy(zCopy, zAfterKey, nAfterKey+1); + for(i=0; i='0' && *p<='9') + || *p=='.' || *p=='+' || *p=='-' || *p=='e' || *p=='E' + ){ + p++; + } + } + + while( *p==' ' ) p++; + if( *p!=(i==(nCol-1) ? '\0' : ',') ){ + goto parse_error; + } + *p++ = '\0'; + } + } + + *pazAfter = azAfter; + return rc; + + parse_error: + sqlite3_free(azAfter); + *pazAfter = 0; + cidxCursorError(pCsr, "%s", "error parsing after value"); + return SQLITE_ERROR; +} + +static char *cidxWhere( + int *pRc, CidxColumn *aCol, char **azAfter, int iGt, int bLastIsNull +){ + char *zRet = 0; + const char *zSep = ""; + int i; + + for(i=0; i"), + azAfter[iGt] + ); + }else{ + zRet = cidxMprintf(pRc, "%z%s(%s) IS NOT NULL", zRet, zSep,aCol[iGt].zExpr); + } + + return zRet; +} + +#define CIDX_CLIST_ALL 0 +#define CIDX_CLIST_ORDERBY 1 +#define CIDX_CLIST_CURRENT_KEY 2 +#define CIDX_CLIST_SUBWHERE 3 +#define CIDX_CLIST_SUBEXPR 4 + +/* +** This function returns various strings based on the contents of the +** CidxIndex structure and the eType parameter. +*/ +static char *cidxColumnList( + int *pRc, /* IN/OUT: Error code */ + const char *zIdx, + CidxIndex *pIdx, /* Indexed columns */ + int eType /* True to include ASC/DESC */ +){ + char *zRet = 0; + if( *pRc==SQLITE_OK ){ + const char *aDir[2] = {"", " DESC"}; + int i; + const char *zSep = ""; + + for(i=0; inCol; i++){ + CidxColumn *p = &pIdx->aCol[i]; + assert( pIdx->aCol[i].bDesc==0 || pIdx->aCol[i].bDesc==1 ); + switch( eType ){ + + case CIDX_CLIST_ORDERBY: + zRet = cidxMprintf(pRc, "%z%s%d%s", zRet, zSep, i+1, aDir[p->bDesc]); + zSep = ","; + break; + + case CIDX_CLIST_CURRENT_KEY: + zRet = cidxMprintf(pRc, "%z%squote(i%d)", zRet, zSep, i); + zSep = "||','||"; + break; + + case CIDX_CLIST_SUBWHERE: + if( p->bKey==0 ){ + zRet = cidxMprintf(pRc, "%z%s%s IS i.i%d", zRet, + zSep, p->zExpr, i + ); + zSep = " AND "; + } + break; + + case CIDX_CLIST_SUBEXPR: + if( p->bKey==1 ){ + zRet = cidxMprintf(pRc, "%z%s%s IS i.i%d", zRet, + zSep, p->zExpr, i + ); + zSep = " AND "; + } + break; + + default: + assert( eType==CIDX_CLIST_ALL ); + zRet = cidxMprintf(pRc, "%z%s(%s) AS i%d", zRet, zSep, p->zExpr, i); + zSep = ", "; + break; + } + } + } + + return zRet; +} + +/* +** Position a cursor back to the beginning. +*/ +static int cidxFilter( + sqlite3_vtab_cursor *pCursor, + int idxNum, const char *idxStr, + int argc, sqlite3_value **argv +){ + int rc = SQLITE_OK; + CidxCursor *pCsr = (CidxCursor*)pCursor; + const char *zIdxName = 0; + const char *zAfterKey = 0; + + if( argc>0 ){ + zIdxName = (const char*)sqlite3_value_text(argv[0]); + if( argc>1 ){ + zAfterKey = (const char*)sqlite3_value_text(argv[1]); + } + } + + if( zIdxName ){ + char *zTab = 0; + char *zCurrentKey = 0; + char *zOrderBy = 0; + char *zSubWhere = 0; + char *zSubExpr = 0; + char *zSrcList = 0; + + char **azAfter = 0; + CidxIndex *pIdx = 0; + + rc = cidxLookupIndex(pCsr, zIdxName, &pIdx, &zTab); + + zOrderBy = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ORDERBY); + zCurrentKey = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_CURRENT_KEY); + zSubWhere = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBWHERE); + zSubExpr = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBEXPR); + zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL); + + if( rc==SQLITE_OK && zAfterKey ){ + rc = cidxDecodeAfter(pCsr, pIdx->nCol, zAfterKey, &azAfter); + } + + if( rc || zAfterKey==0 ){ + pCsr->pStmt = cidxPrepare(&rc, pCsr, + "SELECT (SELECT %s FROM %Q AS t WHERE %s), %s " + "FROM (SELECT %s FROM %Q ORDER BY %s) AS i", + zSubExpr, zTab, zSubWhere, zCurrentKey, + zSrcList, zTab, zOrderBy + ); + /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */ + }else{ + const char *zSep = ""; + char *zSql; + int i; + + zSql = cidxMprintf(&rc, + "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (", + zSubExpr, zTab, zSubWhere, zCurrentKey + ); + for(i=pIdx->nCol-1; i>=0; i--){ + int j; + if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue; + for(j=0; j<2; j++){ + char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j); + zSql = cidxMprintf(&rc, "%z" + "%sSELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)", + zSql, zSep, zSrcList, zTab, zWhere, zOrderBy + ); + zSep = " UNION ALL "; + if( pIdx->aCol[i].bDesc==0 ) break; + } + } + zSql = cidxMprintf(&rc, "%z) AS i", zSql); + + /* printf("SQL: %s\n", zSql); */ + pCsr->pStmt = cidxPrepare(&rc, pCsr, "%z", zSql); + } + + sqlite3_free(zTab); + sqlite3_free(zCurrentKey); + sqlite3_free(zOrderBy); + sqlite3_free(zSubWhere); + sqlite3_free(zSubExpr); + sqlite3_free(zSrcList); + cidxFreeIndex(pIdx); + sqlite3_free(azAfter); + } + + if( pCsr->pStmt ){ + assert( rc==SQLITE_OK ); + rc = cidxNext(pCursor); + } + pCsr->iRowid = 1; + return rc; +} + +/* +** Return a column value. +*/ +static int cidxColumn( + sqlite3_vtab_cursor *pCursor, + sqlite3_context *ctx, + int iCol +){ + CidxCursor *pCsr = (CidxCursor*)pCursor; + assert( iCol>=IIC_ERRMSG && iCol<=IIC_AFTER_KEY ); + if( iCol==IIC_ERRMSG ){ + const char *zVal = 0; + if( sqlite3_column_type(pCsr->pStmt, 0)==SQLITE_INTEGER ){ + if( sqlite3_column_int(pCsr->pStmt, 0)==0 ){ + zVal = "row data mismatch"; + } + }else{ + zVal = "row missing"; + } + sqlite3_result_text(ctx, zVal, -1, SQLITE_STATIC); + }else if( iCol==IIC_CURRENT_KEY ){ + sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pStmt, 1)); + } + return SQLITE_OK; +} + +/* Return the ROWID for the sqlite_btreeinfo table */ +static int cidxRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ + *pRowid = 0; + return SQLITE_OK; +} + +/* +** Register the virtual table modules with the database handle passed +** as the only argument. +*/ +static int ciInit(sqlite3 *db){ + static sqlite3_module cidx_module = { + 0, /* iVersion */ + 0, /* xCreate */ + cidxConnect, /* xConnect */ + cidxBestIndex, /* xBestIndex */ + cidxDisconnect, /* xDisconnect */ + 0, /* xDestroy */ + cidxOpen, /* xOpen - open a cursor */ + cidxClose, /* xClose - close a cursor */ + cidxFilter, /* xFilter - configure scan constraints */ + cidxNext, /* xNext - advance a cursor */ + cidxEof, /* xEof - check for end of scan */ + cidxColumn, /* xColumn - read data */ + cidxRowid, /* xRowid - read data */ + 0, /* xUpdate */ + 0, /* xBegin */ + 0, /* xSync */ + 0, /* xCommit */ + 0, /* xRollback */ + 0, /* xFindMethod */ + 0, /* xRename */ + 0, /* xSavepoint */ + 0, /* xRelease */ + 0, /* xRollbackTo */ + }; + return sqlite3_create_module(db, "incremental_index_check", &cidx_module, 0); +} + +/* +** Extension load function. +*/ +#ifdef _WIN32 +__declspec(dllexport) +#endif +int sqlite3_checkindex_init( + sqlite3 *db, + char **pzErrMsg, + const sqlite3_api_routines *pApi +){ + SQLITE_EXTENSION_INIT2(pApi); + return ciInit(db); +} ADDED ext/repair/sqlite3_checker.c.in Index: ext/repair/sqlite3_checker.c.in ================================================================== --- /dev/null +++ ext/repair/sqlite3_checker.c.in @@ -0,0 +1,85 @@ +/* +** Read an SQLite database file and analyze its space utilization. Generate +** text on standard output. +*/ +#define TCLSH_INIT_PROC sqlite3_checker_init_proc +#define SQLITE_ENABLE_DBPAGE_VTAB 1 +#undef SQLITE_THREADSAFE +#define SQLITE_THREADSAFE 0 +#undef SQLITE_ENABLE_COLUMN_METADATA +#define SQLITE_OMIT_DECLTYPE 1 +#define SQLITE_OMIT_DEPRECATED 1 +#define SQLITE_OMIT_PROGRESS_CALLBACK 1 +#define SQLITE_OMIT_SHARED_CACHE 1 +#define SQLITE_DEFAULT_MEMSTATUS 0 +#define SQLITE_MAX_EXPR_DEPTH 0 +INCLUDE sqlite3.c +INCLUDE $ROOT/src/tclsqlite.c +INCLUDE $ROOT/ext/misc/btreeinfo.c +INCLUDE $ROOT/ext/repair/checkindex.c +INCLUDE $ROOT/ext/repair/checkfreelist.c + +/* +** Decode a pointer to an sqlite3 object. +*/ +int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb){ + struct SqliteDb *p; + Tcl_CmdInfo cmdInfo; + if( Tcl_GetCommandInfo(interp, zA, &cmdInfo) ){ + p = (struct SqliteDb*)cmdInfo.objClientData; + *ppDb = p->db; + return TCL_OK; + }else{ + *ppDb = 0; + return TCL_ERROR; + } + return TCL_OK; +} + +/* +** sqlite3_imposter db main rootpage {CREATE TABLE...} ;# setup an imposter +** sqlite3_imposter db main ;# rm all imposters +*/ +static int sqlite3_imposter( + void *clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + sqlite3 *db; + const char *zSchema; + int iRoot; + const char *zSql; + + if( objc!=3 && objc!=5 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB SCHEMA [ROOTPAGE SQL]"); + return TCL_ERROR; + } + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; + zSchema = Tcl_GetString(objv[2]); + if( objc==3 ){ + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, zSchema, 0, 1); + }else{ + if( Tcl_GetIntFromObj(interp, objv[3], &iRoot) ) return TCL_ERROR; + zSql = Tcl_GetString(objv[4]); + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, zSchema, 1, iRoot); + sqlite3_exec(db, zSql, 0, 0, 0); + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, zSchema, 0, 0); + } + return TCL_OK; +} + +#include + +const char *sqlite3_checker_init_proc(Tcl_Interp *interp){ + Tcl_CreateObjCommand(interp, "sqlite3_imposter", + (Tcl_ObjCmdProc*)sqlite3_imposter, 0, 0); + sqlite3_auto_extension((void(*)(void))sqlite3_btreeinfo_init); + sqlite3_auto_extension((void(*)(void))sqlite3_checkindex_init); + sqlite3_auto_extension((void(*)(void))sqlite3_checkfreelist_init); + return +BEGIN_STRING +INCLUDE $ROOT/ext/repair/sqlite3_checker.tcl +END_STRING +; +} ADDED ext/repair/sqlite3_checker.tcl Index: ext/repair/sqlite3_checker.tcl ================================================================== --- /dev/null +++ ext/repair/sqlite3_checker.tcl @@ -0,0 +1,243 @@ +# This TCL script is the main driver script for the sqlite3_checker utility +# program. +# + +# Special case: +# +# sqlite3_checker --test FILENAME ARGS +# +# uses FILENAME in place of this script. +# +if {[lindex $argv 0]=="--test" && [llength $argv]>1} { + set ::argv0 [lindex $argv 1] + set argv [lrange $argv 2 end] + source $argv0 + exit 0 +} + +# Emulate a TCL shell +# +proc tclsh {} { + set line {} + while {![eof stdin]} { + if {$line!=""} { + puts -nonewline "> " + } else { + puts -nonewline "% " + } + flush stdout + append line [gets stdin] + if {[info complete $line]} { + if {[catch {uplevel #0 $line} result]} { + puts stderr "Error: $result" + } elseif {$result!=""} { + puts $result + } + set line {} + } else { + append line \n + } + } +} + +# Do an incremental integrity check of a single index +# +proc check_index {idxname batchsize} { + set i 0 + set more 1 + set nerr 0 + set pct 00.0 + set max [db one {SELECT nEntry FROM sqlite_btreeinfo('main') + WHERE name=$idxname}] + puts -nonewline "$idxname: $i of $max rows ($pct%)\r" + flush stdout + while {$more} { + set more 0 + db eval {SELECT errmsg, current_key AS key + FROM incremental_index_check($idxname) + WHERE after_key=$key + LIMIT $batchsize} { + set more 1 + if {$errmsg!=""} { + incr nerr + puts "$idxname: key($key): $errmsg" + } + incr i + } + set x [format {%.1f} [expr {($i*100.0)/$max}]] + if {$x!=$pct} { + puts -nonewline "$idxname: $i of $max rows ($pct%)\r" + flush stdout + set pct $x + } + } + puts "$idxname: $nerr errors out of $i entries" +} + +# Print a usage message on standard error, then quit. +# +proc usage {} { + set argv0 [file rootname [file tail [info nameofexecutable]]] + puts stderr "Usage: $argv0 OPTIONS database-filename" + puts stderr { +Do sanity checking on a live SQLite3 database file specified by the +"database-filename" argument. + +Options: + + --batchsize N Number of rows to check per transaction + + --freelist Perform a freelist check + + --index NAME Run a check of the index NAME + + --summary Print summary information about the database + + --table NAME Run a check of all indexes for table NAME + + --tclsh Run the built-in TCL interpreter (for debugging) + + --version Show the version number of SQLite +} + exit 1 +} + +set file_to_analyze {} +append argv {} +set bFreelistCheck 0 +set bSummary 0 +set zIndex {} +set zTable {} +set batchsize 1000 +set bAll 1 +set argc [llength $argv] +for {set i 0} {$i<$argc} {incr i} { + set arg [lindex $argv $i] + if {[regexp {^-+tclsh$} $arg]} { + tclsh + exit 0 + } + if {[regexp {^-+version$} $arg]} { + sqlite3 mem :memory: + puts [mem one {SELECT sqlite_version()||' '||sqlite_source_id()}] + mem close + exit 0 + } + if {[regexp {^-+freelist$} $arg]} { + set bFreelistCheck 1 + set bAll 0 + continue + } + if {[regexp {^-+summary$} $arg]} { + set bSummary 1 + set bAll 0 + continue + } + if {[regexp {^-+batchsize$} $arg]} { + incr i + if {$i>=$argc} { + puts stderr "missing argument on $arg" + exit 1 + } + set batchsize [lindex $argv $i] + continue + } + if {[regexp {^-+index$} $arg]} { + incr i + if {$i>=$argc} { + puts stderr "missing argument on $arg" + exit 1 + } + set zIndex [lindex $argv $i] + set bAll 0 + continue + } + if {[regexp {^-+table$} $arg]} { + incr i + if {$i>=$argc} { + puts stderr "missing argument on $arg" + exit 1 + } + set zTable [lindex $argv $i] + set bAll 0 + continue + } + if {[regexp {^-} $arg]} { + puts stderr "Unknown option: $arg" + usage + } + if {$file_to_analyze!=""} { + usage + } else { + set file_to_analyze $arg + } +} +if {$file_to_analyze==""} usage + +# If a TCL script is specified on the command-line, then run that +# script. +# +if {[file extension $file_to_analyze]==".tcl"} { + source $file_to_analyze + exit 0 +} + +set root_filename $file_to_analyze +regexp {^file:(//)?([^?]*)} $file_to_analyze all x1 root_filename +if {![file exists $root_filename]} { + puts stderr "No such file: $root_filename" + exit 1 +} +if {![file readable $root_filename]} { + puts stderr "File is not readable: $root_filename" + exit 1 +} + +if {[catch {sqlite3 db $file_to_analyze} res]} { + puts stderr "Cannot open datababase $root_filename: $res" + exit 1 +} + +if {$bFreelistCheck || $bAll} { + puts -nonewline "freelist-check: " + flush stdout + puts [db one {SELECT checkfreelist('main')}] +} +if {$bSummary} { + set scale 0 + set pgsz [db one {PRAGMA page_size}] + db eval {SELECT nPage*$pgsz AS sz, name, tbl_name + FROM sqlite_btreeinfo + WHERE type='index' + ORDER BY 1 DESC, name} { + if {$scale==0} { + if {$sz>10000000} { + set scale 1000000.0 + set unit MB + } else { + set scale 1000.0 + set unit KB + } + } + puts [format {%7.1f %s index %s of table %s} \ + [expr {$sz/$scale}] $unit $name $tbl_name] + } +} +if {$zIndex!=""} { + check_index $zIndex $batchsize +} +if {$zTable!=""} { + foreach idx [db eval {SELECT name FROM sqlite_master + WHERE type='index' AND rootpage>0 + AND tbl_name=$zTable}] { + check_index $idx $batchsize + } +} +if {$bAll} { + set allidx [db eval {SELECT name FROM sqlite_btreeinfo('main') + WHERE type='index' AND rootpage>0 + ORDER BY nEntry}] + foreach idx $allidx { + check_index $idx $batchsize + } +} ADDED ext/repair/test/README.md Index: ext/repair/test/README.md ================================================================== --- /dev/null +++ ext/repair/test/README.md @@ -0,0 +1,13 @@ +To run these tests, first build sqlite3_checker: + + +> make sqlite3_checker + + +Then run the "test.tcl" script using: + + +> ./sqlite3_checker --test $path/test.tcl + + +Optionally add the full pathnames of individual *.test modules ADDED ext/repair/test/checkfreelist01.test Index: ext/repair/test/checkfreelist01.test ================================================================== --- /dev/null +++ ext/repair/test/checkfreelist01.test @@ -0,0 +1,92 @@ +# 2017-10-11 + +set testprefix checkfreelist + +do_execsql_test 1.0 { + PRAGMA page_size=1024; + CREATE TABLE t1(a, b); +} + +do_execsql_test 1.2 { SELECT checkfreelist('main') } {ok} +do_execsql_test 1.3 { + WITH s(i) AS ( + SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000 + ) + INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s; + DELETE FROM t1 WHERE rowid%3; + PRAGMA freelist_count; +} {6726} + +do_execsql_test 1.4 { SELECT checkfreelist('main') } {ok} +do_execsql_test 1.5 { + WITH freelist_trunk(i, d, n) AS ( + SELECT 1, NULL, sqlite_readint32(data, 32) FROM sqlite_dbpage WHERE pgno=1 + UNION ALL + SELECT n, data, sqlite_readint32(data) + FROM freelist_trunk, sqlite_dbpage WHERE pgno=n + ) + SELECT i FROM freelist_trunk WHERE i!=1; +} { + 10009 9715 9343 8969 8595 8222 7847 7474 7102 6727 6354 5982 5608 5234 + 4860 4487 4112 3740 3367 2992 2619 2247 1872 1499 1125 752 377 5 +} + +do_execsql_test 1.6 { SELECT checkfreelist('main') } {ok} + +proc set_int {blob idx newval} { + binary scan $blob I* ints + lset ints $idx $newval + binary format I* $ints +} +db func set_int set_int + +proc get_int {blob idx} { + binary scan $blob I* ints + lindex $ints $idx +} +db func get_int get_int + +do_execsql_test 1.7 { + BEGIN; + UPDATE sqlite_dbpage + SET data = set_int(data, 1, get_int(data, 1)-1) + WHERE pgno=4860; + SELECT checkfreelist('main'); + ROLLBACK; +} {{free-list count mismatch: actual=6725 header=6726}} + +do_execsql_test 1.8 { + BEGIN; + UPDATE sqlite_dbpage + SET data = set_int(data, 5, (SELECT * FROM pragma_page_count)+1) + WHERE pgno=4860; + SELECT checkfreelist('main'); + ROLLBACK; +} {{leaf page 10092 is out of range (child 3 of trunk page 4860)}} + +do_execsql_test 1.9 { + BEGIN; + UPDATE sqlite_dbpage + SET data = set_int(data, 5, 0) + WHERE pgno=4860; + SELECT checkfreelist('main'); + ROLLBACK; +} {{leaf page 0 is out of range (child 3 of trunk page 4860)}} + +do_execsql_test 1.10 { + BEGIN; + UPDATE sqlite_dbpage + SET data = set_int(data, get_int(data, 1)+1, 0) + WHERE pgno=5; + SELECT checkfreelist('main'); + ROLLBACK; +} {{leaf page 0 is out of range (child 247 of trunk page 5)}} + +do_execsql_test 1.11 { + BEGIN; + UPDATE sqlite_dbpage + SET data = set_int(data, 1, 249) + WHERE pgno=5; + SELECT checkfreelist('main'); + ROLLBACK; +} {{leaf count out of range (249) on trunk page 5}} ADDED ext/repair/test/checkindex01.test Index: ext/repair/test/checkindex01.test ================================================================== --- /dev/null +++ ext/repair/test/checkindex01.test @@ -0,0 +1,311 @@ +# 2017-10-11 +# +set testprefix checkindex + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b); + CREATE INDEX i1 ON t1(a); + INSERT INTO t1 VALUES('one', 2); + INSERT INTO t1 VALUES('two', 4); + INSERT INTO t1 VALUES('three', 6); + INSERT INTO t1 VALUES('four', 8); + INSERT INTO t1 VALUES('five', 10); + + CREATE INDEX i2 ON t1(a DESC); +} {} + +proc incr_index_check {idx nStep} { + set Q { + SELECT errmsg, current_key FROM incremental_index_check($idx, $after) + LIMIT $nStep + } + + set res [list] + while {1} { + unset -nocomplain current_key + set res1 [db eval $Q] + if {[llength $res1]==0} break + set res [concat $res $res1] + set after [lindex $res end] + } + + return $res +} + +proc do_index_check_test {tn idx res} { + uplevel [list do_execsql_test $tn.1 " + SELECT errmsg, current_key FROM incremental_index_check('$idx'); + " $res] + + uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]] + uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]] + uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]] +} + + +do_execsql_test 1.2 { + SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1'); +} { + 1 'five',5 + 1 'four',4 + 1 'one',1 + 1 'three',3 + 1 'two',2 +} + +do_index_check_test 1.3 i1 { + {} 'five',5 + {} 'four',4 + {} 'one',1 + {} 'three',3 + {} 'two',2 +} + +do_index_check_test 1.4 i2 { + {} 'two',2 + {} 'three',3 + {} 'one',1 + {} 'four',4 + {} 'five',5 +} + +do_test 1.5 { + set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t1' }] + sqlite3_imposter db main $tblroot {CREATE TABLE xt1(a,b)} + db eval { + UPDATE xt1 SET a='six' WHERE rowid=3; + DELETE FROM xt1 WHERE rowid = 5; + } + sqlite3_imposter db main +} {} + +do_index_check_test 1.6 i1 { + {row missing} 'five',5 + {} 'four',4 + {} 'one',1 + {row data mismatch} 'three',3 + {} 'two',2 +} + +do_index_check_test 1.7 i2 { + {} 'two',2 + {row data mismatch} 'three',3 + {} 'one',1 + {} 'four',4 + {row missing} 'five',5 +} + +#-------------------------------------------------------------------------- +do_execsql_test 2.0 { + + CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c, d); + + INSERT INTO t2 VALUES(1, NULL, 1, 1); + INSERT INTO t2 VALUES(2, 1, NULL, 1); + INSERT INTO t2 VALUES(3, 1, 1, NULL); + + INSERT INTO t2 VALUES(4, 2, 2, 1); + INSERT INTO t2 VALUES(5, 2, 2, 2); + INSERT INTO t2 VALUES(6, 2, 2, 3); + + INSERT INTO t2 VALUES(7, 2, 2, 1); + INSERT INTO t2 VALUES(8, 2, 2, 2); + INSERT INTO t2 VALUES(9, 2, 2, 3); + + CREATE INDEX i3 ON t2(b, c, d); + CREATE INDEX i4 ON t2(b DESC, c DESC, d DESC); + CREATE INDEX i5 ON t2(d, c DESC, b); +} {} + +do_index_check_test 2.1 i3 { + {} NULL,1,1,1 + {} 1,NULL,1,2 + {} 1,1,NULL,3 + {} 2,2,1,4 + {} 2,2,1,7 + {} 2,2,2,5 + {} 2,2,2,8 + {} 2,2,3,6 + {} 2,2,3,9 +} + +do_index_check_test 2.2 i4 { + {} 2,2,3,6 + {} 2,2,3,9 + {} 2,2,2,5 + {} 2,2,2,8 + {} 2,2,1,4 + {} 2,2,1,7 + {} 1,1,NULL,3 + {} 1,NULL,1,2 + {} NULL,1,1,1 +} + +do_index_check_test 2.3 i5 { + {} NULL,1,1,3 + {} 1,2,2,4 + {} 1,2,2,7 + {} 1,1,NULL,1 + {} 1,NULL,1,2 + {} 2,2,2,5 + {} 2,2,2,8 + {} 3,2,2,6 + {} 3,2,2,9 +} + +#-------------------------------------------------------------------------- +do_execsql_test 3.0 { + + CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID; + CREATE INDEX t3wxy ON t3(w, x, y); + CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC); + + INSERT INTO t3 VALUES(NULL, NULL, NULL, 1); + INSERT INTO t3 VALUES(NULL, NULL, NULL, 2); + INSERT INTO t3 VALUES(NULL, NULL, NULL, 3); + + INSERT INTO t3 VALUES('a', NULL, NULL, 4); + INSERT INTO t3 VALUES('a', NULL, NULL, 5); + INSERT INTO t3 VALUES('a', NULL, NULL, 6); + + INSERT INTO t3 VALUES('a', 'b', NULL, 7); + INSERT INTO t3 VALUES('a', 'b', NULL, 8); + INSERT INTO t3 VALUES('a', 'b', NULL, 9); + +} {} + +do_index_check_test 3.1 t3wxy { + {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 + {} 'a',NULL,NULL,4 {} 'a',NULL,NULL,5 {} 'a',NULL,NULL,6 + {} 'a','b',NULL,7 {} 'a','b',NULL,8 {} 'a','b',NULL,9 +} +do_index_check_test 3.2 t3wxy2 { + {} 'a','b',NULL,7 {} 'a','b',NULL,8 {} 'a','b',NULL,9 + {} 'a',NULL,NULL,4 {} 'a',NULL,NULL,5 {} 'a',NULL,NULL,6 + {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 +} + +#-------------------------------------------------------------------------- +# Test with an index that uses non-default collation sequences. +# +do_execsql_test 4.0 { + CREATE TABLE t4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT); + INSERT INTO t4 VALUES(1, 'aaa', 'bbb'); + INSERT INTO t4 VALUES(2, 'AAA', 'CCC'); + INSERT INTO t4 VALUES(3, 'aab', 'ddd'); + INSERT INTO t4 VALUES(4, 'AAB', 'EEE'); + + CREATE INDEX t4cc ON t4(c1 COLLATE nocase, c2 COLLATE nocase); +} + +do_index_check_test 4.1 t4cc { + {} 'aaa','bbb',1 + {} 'AAA','CCC',2 + {} 'aab','ddd',3 + {} 'AAB','EEE',4 +} + +do_test 4.2 { + set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t4' }] + sqlite3_imposter db main $tblroot \ + {CREATE TABLE xt4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT)} + + db eval { + UPDATE xt4 SET c1='hello' WHERE rowid=2; + DELETE FROM xt4 WHERE rowid = 3; + } + sqlite3_imposter db main +} {} + +do_index_check_test 4.3 t4cc { + {} 'aaa','bbb',1 + {row data mismatch} 'AAA','CCC',2 + {row missing} 'aab','ddd',3 + {} 'AAB','EEE',4 +} + +#-------------------------------------------------------------------------- +# Test an index on an expression. +# +do_execsql_test 5.0 { + CREATE TABLE t5(x INTEGER PRIMARY KEY, y TEXT, UNIQUE(y)); + INSERT INTO t5 VALUES(1, '{"x":1, "y":1}'); + INSERT INTO t5 VALUES(2, '{"x":2, "y":2}'); + INSERT INTO t5 VALUES(3, '{"x":3, "y":3}'); + INSERT INTO t5 VALUES(4, '{"w":4, "z":4}'); + INSERT INTO t5 VALUES(5, '{"x":5, "y":5}'); + + CREATE INDEX t5x ON t5( json_extract(y, '$.x') ); + CREATE INDEX t5y ON t5( json_extract(y, '$.y') DESC ); +} + +do_index_check_test 5.1.1 t5x { + {} NULL,4 {} 1,1 {} 2,2 {} 3,3 {} 5,5 +} + +do_index_check_test 5.1.2 t5y { + {} 5,5 {} 3,3 {} 2,2 {} 1,1 {} NULL,4 +} + +do_index_check_test 5.1.3 sqlite_autoindex_t5_1 { + {} {'{"w":4, "z":4}',4} + {} {'{"x":1, "y":1}',1} + {} {'{"x":2, "y":2}',2} + {} {'{"x":3, "y":3}',3} + {} {'{"x":5, "y":5}',5} +} + +do_test 5.2 { + set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t5' }] + sqlite3_imposter db main $tblroot \ + {CREATE TABLE xt5(a INTEGER PRIMARY KEY, c1 TEXT);} + db eval { + UPDATE xt5 SET c1='{"x":22, "y":11}' WHERE rowid=1; + DELETE FROM xt5 WHERE rowid = 4; + } + sqlite3_imposter db main +} {} + +do_index_check_test 5.3.1 t5x { + {row missing} NULL,4 + {row data mismatch} 1,1 + {} 2,2 + {} 3,3 + {} 5,5 +} + +do_index_check_test 5.3.2 sqlite_autoindex_t5_1 { + {row missing} {'{"w":4, "z":4}',4} + {row data mismatch} {'{"x":1, "y":1}',1} + {} {'{"x":2, "y":2}',2} + {} {'{"x":3, "y":3}',3} + {} {'{"x":5, "y":5}',5} +} + +#------------------------------------------------------------------------- +# +do_execsql_test 6.0 { + CREATE TABLE t6(x INTEGER PRIMARY KEY, y, z); + CREATE INDEX t6x1 ON t6(y, /* one,two,three */ z); + CREATE INDEX t6x2 ON t6(z, -- hello,world, + y); + + CREATE INDEX t6x3 ON t6(z -- hello,world + , y); + + INSERT INTO t6 VALUES(1, 2, 3); + INSERT INTO t6 VALUES(4, 5, 6); +} + +do_index_check_test 6.1 t6x1 { + {} 2,3,1 + {} 5,6,4 +} +do_index_check_test 6.2 t6x2 { + {} 3,2,1 + {} 6,5,4 +} +do_index_check_test 6.2 t6x3 { + {} 3,2,1 + {} 6,5,4 +} ADDED ext/repair/test/test.tcl Index: ext/repair/test/test.tcl ================================================================== --- /dev/null +++ ext/repair/test/test.tcl @@ -0,0 +1,67 @@ +# Run this script using +# +# sqlite3_checker --test $thisscript $testscripts +# +# The $testscripts argument is optional. If omitted, all *.test files +# in the same directory as $thisscript are run. +# +set NTEST 0 +set NERR 0 + + +# Invoke the do_test procedure to run a single test +# +# The $expected parameter is the expected result. The result is the return +# value from the last TCL command in $cmd. +# +# Normally, $expected must match exactly. But if $expected is of the form +# "/regexp/" then regular expression matching is used. If $expected is +# "~/regexp/" then the regular expression must NOT match. If $expected is +# of the form "#/value-list/" then each term in value-list must be numeric +# and must approximately match the corresponding numeric term in $result. +# Values must match within 10%. Or if the $expected term is A..B then the +# $result term must be in between A and B. +# +proc do_test {name cmd expected} { + if {[info exists ::testprefix]} { + set name "$::testprefix$name" + } + + incr ::NTEST + puts -nonewline $name... + flush stdout + + if {[catch {uplevel #0 "$cmd;\n"} result]} { + puts -nonewline $name... + puts "\nError: $result" + incr ::NERR + } else { + set ok [expr {[string compare $result $expected]==0}] + if {!$ok} { + puts "\n! $name expected: \[$expected\]\n! $name got: \[$result\]" + incr ::NERR + } else { + puts " Ok" + } + } + flush stdout +} + +# +# do_execsql_test TESTNAME SQL RES +# +proc do_execsql_test {testname sql {result {}}} { + uplevel [list do_test $testname [list db eval $sql] [list {*}$result]] +} + +if {[llength $argv]==0} { + set dir [file dirname $argv0] + set argv [glob -nocomplain $dir/*.test] +} +foreach testfile $argv { + file delete -force test.db + sqlite3 db test.db + source $testfile + catch {db close} +} +puts "$NERR errors out of $NTEST tests" Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -462,10 +462,11 @@ # TESTPROGS = \ testfixture$(EXE) \ sqlite3$(EXE) \ sqlite3_analyzer$(EXE) \ + sqlite3_checker$(EXE) \ sqldiff$(EXE) \ dbhash$(EXE) # Databases containing fuzzer test cases # @@ -784,10 +785,26 @@ tclsh $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c sqlite3_analyzer$(EXE): sqlite3_analyzer.c $(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) +CHECKER_DEPS =\ + $(TOP)/tool/mkccode.tcl \ + sqlite3.c \ + $(TOP)/src/tclsqlite.c \ + $(TOP)/ext/repair/sqlite3_checker.tcl \ + $(TOP)/ext/repair/checkindex.c \ + $(TOP)/ext/repair/checkfreelist.c \ + $(TOP)/ext/misc/btreeinfo.c \ + $(TOP)/ext/repair/sqlite3_checker.c.in + +sqlite3_checker.c: $(CHECKER_DEPS) + tclsh $(TOP)/tool/mkccode.tcl $(TOP)/ext/repair/sqlite3_checker.c.in >$@ + +sqlite3_checker$(TEXE): sqlite3_checker.c + $(TCCX) $(TCL_FLAGS) sqlite3_checker.c -o $@ $(LIBTCL) $(THREADLIB) + dbdump$(EXE): $(TOP)/ext/misc/dbdump.c sqlite3.o $(TCCX) -DDBDUMP_STANDALONE -o dbdump$(EXE) \ $(TOP)/ext/misc/dbdump.c sqlite3.o $(THREADLIB) # Rules to build the 'testfixture' application. DELETED test/checkfreelist.test Index: test/checkfreelist.test ================================================================== --- test/checkfreelist.test +++ /dev/null @@ -1,123 +0,0 @@ -# 2017-10-11 -# -# The author disclaims copyright to this source code. In place of -# a legal notice, here is a blessing: -# -# May you do good and not evil. -# May you find forgiveness for yourself and forgive others. -# May you share freely, never taking more than you give. -# -#*********************************************************************** -# This file implements regression tests for SQLite library. The -# focus of this file is testing the checkfreelist extension. -# - -set testdir [file dirname $argv0] -source $testdir/tester.tcl -set testprefix checkfreelist - -ifcapable !vtab||!compound { - finish_test - return -} - -if {[file exists ../checkfreelist.so]==0} { - finish_test - return -} - -do_execsql_test 1.0 { - CREATE TABLE t1(a, b); -} - -db enable_load_extension 1 -do_execsql_test 1.1 { - SELECT load_extension('../checkfreelist.so'); -} {{}} - -do_execsql_test 1.2 { SELECT checkfreelist('main') } {ok} -do_execsql_test 1.3 { - WITH s(i) AS ( - SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000 - ) - INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s; - DELETE FROM t1 WHERE rowid%3; - PRAGMA freelist_count; -} {6726} - -do_execsql_test 1.4 { SELECT checkfreelist('main') } {ok} -do_execsql_test 1.5 { - WITH freelist_trunk(i, d, n) AS ( - SELECT 1, NULL, sqlite_readint32(data, 32) FROM sqlite_dbpage WHERE pgno=1 - UNION ALL - SELECT n, data, sqlite_readint32(data) - FROM freelist_trunk, sqlite_dbpage WHERE pgno=n - ) - SELECT i FROM freelist_trunk WHERE i!=1; -} { - 10010 9716 9344 8970 8596 8223 7848 7475 7103 6728 6355 5983 5609 5235 - 4861 4488 4113 3741 3368 2993 2620 2248 1873 1500 1126 753 378 5 -} - -do_execsql_test 1.6 { SELECT checkfreelist('main') } {ok} - -proc set_int {blob idx newval} { - binary scan $blob I* ints - lset ints $idx $newval - binary format I* $ints -} -db func set_int set_int - -proc get_int {blob idx} { - binary scan $blob I* ints - lindex $ints $idx -} -db func get_int get_int - -do_execsql_test 1.7 { - BEGIN; - UPDATE sqlite_dbpage - SET data = set_int(data, 1, get_int(data, 1)-1) - WHERE pgno=4861; - SELECT checkfreelist('main'); - ROLLBACK; -} {{free-list count mismatch: actual=6725 header=6726}} - -do_execsql_test 1.8 { - BEGIN; - UPDATE sqlite_dbpage - SET data = set_int(data, 5, (SELECT * FROM pragma_page_count)+1) - WHERE pgno=4861; - SELECT checkfreelist('main'); - ROLLBACK; -} {{leaf page 10093 is out of range (child 3 of trunk page 4861)}} - -do_execsql_test 1.9 { - BEGIN; - UPDATE sqlite_dbpage - SET data = set_int(data, 5, 0) - WHERE pgno=4861; - SELECT checkfreelist('main'); - ROLLBACK; -} {{leaf page 0 is out of range (child 3 of trunk page 4861)}} - -do_execsql_test 1.10 { - BEGIN; - UPDATE sqlite_dbpage - SET data = set_int(data, get_int(data, 1)+1, 0) - WHERE pgno=5; - SELECT checkfreelist('main'); - ROLLBACK; -} {{leaf page 0 is out of range (child 247 of trunk page 5)}} - -do_execsql_test 1.11 { - BEGIN; - UPDATE sqlite_dbpage - SET data = set_int(data, 1, 249) - WHERE pgno=5; - SELECT checkfreelist('main'); - ROLLBACK; -} {{leaf count out of range (249) on trunk page 5}} - -finish_test -