/ Check-in [0c5d18a0]
Login

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

Overview
Comment:Add the checkindex.c extension and the sqlite3_checker utility program used for doing live validation of large databases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0c5d18a01ec77f784d5434c5465ab8da9a0c365a58d4bd8551872ca90aaf42d6
User & Date: drh 2017-11-01 19:44:19
Context
2017-11-01
19:50
Make sure the JSON1 extension is available to sqlite3_checker.exe check-in: a946a8ed user: drh tags: trunk
19:44
Add the checkindex.c extension and the sqlite3_checker utility program used for doing live validation of large databases. check-in: 0c5d18a0 user: drh tags: trunk
19:22
Adjust page numbers in the ext/repair/test/checkfreelist01.test module due to the fact that the pending-byte page is no longer shifted down to a low-numbered page but is in its rightful place. Closed-Leaf check-in: c1641aff user: drh tags: checkindex
2017-10-31
14:56
Update the built procedures for the sqlite3_analyzer utility to allow it to be linked with an external sqlite3.o library. Automatically detect a missing dbstat extension and report the compile-time error. check-in: bb39744f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

  1183   1183   
  1184   1184   sqlite3_analyzer.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/spaceanal.tcl $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in
  1185   1185   	$(TCLSH_CMD) $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c
  1186   1186   
  1187   1187   sqlite3_analyzer$(TEXE): sqlite3_analyzer.c
  1188   1188   	$(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS)
  1189   1189   
         1190  +CHECKER_DEPS =\
         1191  +  $(TOP)/tool/mkccode.tcl \
         1192  +  sqlite3.c \
         1193  +  $(TOP)/src/tclsqlite.c \
         1194  +  $(TOP)/ext/repair/sqlite3_checker.tcl \
         1195  +  $(TOP)/ext/repair/checkindex.c \
         1196  +  $(TOP)/ext/repair/checkfreelist.c \
         1197  +  $(TOP)/ext/misc/btreeinfo.c \
         1198  +  $(TOP)/ext/repair/sqlite3_checker.c.in
         1199  +
         1200  +sqlite3_checker.c:	$(CHECKER_DEPS)
         1201  +	$(TCLSH_CMD) $(TOP)/tool/mkccode.tcl $(TOP)/ext/repair/sqlite3_checker.c.in >$@
         1202  +
         1203  +sqlite3_checker$(TEXE):	sqlite3_checker.c
         1204  +	$(LTLINK) sqlite3_checker.c -o $@ $(LIBTCL) $(TLIBS)
         1205  +
  1190   1206   dbdump$(TEXE): $(TOP)/ext/misc/dbdump.c sqlite3.lo
  1191   1207   	$(LTLINK) -DDBDUMP_STANDALONE -o $@ \
  1192   1208              $(TOP)/ext/misc/dbdump.c sqlite3.lo $(TLIBS)
  1193   1209   
  1194   1210   showdb$(TEXE):	$(TOP)/tool/showdb.c sqlite3.lo
  1195   1211   	$(LTLINK) -o $@ $(TOP)/tool/showdb.c sqlite3.lo $(TLIBS)
  1196   1212   

Changes to Makefile.msc.

  1487   1487   
  1488   1488   # executables needed for testing
  1489   1489   #
  1490   1490   TESTPROGS = \
  1491   1491     testfixture.exe \
  1492   1492     $(SQLITE3EXE) \
  1493   1493     sqlite3_analyzer.exe \
         1494  +  sqlite3_checker.exe \
  1494   1495     sqldiff.exe \
  1495   1496     dbhash.exe
  1496   1497   
  1497   1498   # Databases containing fuzzer test cases
  1498   1499   #
  1499   1500   FUZZDATA = \
  1500   1501     $(TOP)\test\fuzzdata1.db \
................................................................................
  2193   2194   sqlite3_analyzer.c:	$(SQLITE3C) $(SQLITE3H) $(TOP)\src\tclsqlite.c $(TOP)\tool\spaceanal.tcl $(TOP)\tool\mkccode.tcl $(TOP)\tool\sqlite3_analyzer.c.in $(SQLITE_TCL_DEP)
  2194   2195   	$(TCLSH_CMD) $(TOP)\tool\mkccode.tcl $(TOP)\tool\sqlite3_analyzer.c.in > $@
  2195   2196   
  2196   2197   sqlite3_analyzer.exe:	sqlite3_analyzer.c $(LIBRESOBJS)
  2197   2198   	$(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_analyzer.c \
  2198   2199   		/link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)
  2199   2200   
         2201  +CHECKER_DEPS =\
         2202  +  $(TOP)/tool/mkccode.tcl \
         2203  +  sqlite3.c \
         2204  +  $(TOP)/src/tclsqlite.c \
         2205  +  $(TOP)/ext/repair/sqlite3_checker.tcl \
         2206  +  $(TOP)/ext/repair/checkindex.c \
         2207  +  $(TOP)/ext/repair/checkfreelist.c \
         2208  +  $(TOP)/ext/misc/btreeinfo.c \
         2209  +  $(TOP)/ext/repair/sqlite3_checker.c.in
         2210  +
         2211  +sqlite3_checker.c:	$(CHECKER_DEPS)
         2212  +	$(TCLSH_CMD) $(TOP)\tool\mkccode.tcl $(TOP)\ext\repair\sqlite3_checker.c.in > $@
         2213  +
         2214  +sqlite3_checker.exe:	sqlite3_checker.c $(LIBRESOBJS)
         2215  +	$(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_checker.c \
         2216  +		/link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)
         2217  +
  2200   2218   dbdump.exe:	$(TOP)\ext\misc\dbdump.c $(SQLITE3C) $(SQLITE3H)
  2201   2219   	$(LTLINK) $(NO_WARN) -DDBDUMP_STANDALONE $(TOP)\ext\misc\dbdump.c $(SQLITE3C) \
  2202   2220   		/link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS)
  2203   2221   
  2204   2222   testloadext.lo:	$(TOP)\src\test_loadext.c
  2205   2223   	$(LTCOMPILE) $(NO_WARN) -c $(TOP)\src\test_loadext.c
  2206   2224   

Changes to ext/misc/btreeinfo.c.

    57     57   **       ORDER BY nEntry DESC, name;
    58     58   **
    59     59   ** Show the names of all WITHOUT ROWID tables: 
    60     60   **
    61     61   **      SELECT name FROM sqlite_btreeinfo
    62     62   **       WHERE type='table' AND NOT hasRowid;
    63     63   */
    64         -#include <sqlite3ext.h>
           64  +#if !defined(SQLITEINT_H)
           65  +#include "sqlite3ext.h"
           66  +#endif
    65     67   SQLITE_EXTENSION_INIT1
    66     68   #include <string.h>
    67     69   #include <assert.h>
    68     70   
    69     71   /* Columns available in this virtual table */
    70     72   #define BINFO_COLUMN_TYPE         0
    71     73   #define BINFO_COLUMN_NAME         1

Added ext/repair/checkindex.c.

            1  +/*
            2  +** 2017 October 27
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +#include "sqlite3ext.h"
           15  +SQLITE_EXTENSION_INIT1
           16  +
           17  +#ifndef SQLITE_AMALGAMATION
           18  +# include <string.h>
           19  +# include <stdio.h>
           20  +# include <stdlib.h>
           21  +# include <assert.h>
           22  +# define ALWAYS(X)  1
           23  +# define NEVER(X)   0
           24  +  typedef unsigned char u8;
           25  +  typedef unsigned short u16;
           26  +  typedef unsigned int u32;
           27  +#define get4byte(x) (        \
           28  +    ((u32)((x)[0])<<24) +    \
           29  +    ((u32)((x)[1])<<16) +    \
           30  +    ((u32)((x)[2])<<8) +     \
           31  +    ((u32)((x)[3]))          \
           32  +)
           33  +#endif
           34  +
           35  +typedef struct CidxTable CidxTable;
           36  +typedef struct CidxCursor CidxCursor;
           37  +
           38  +struct CidxTable {
           39  +  sqlite3_vtab base;              /* Base class.  Must be first */
           40  +  sqlite3 *db;
           41  +};
           42  +
           43  +struct CidxCursor {
           44  +  sqlite3_vtab_cursor base;       /* Base class.  Must be first */
           45  +  sqlite3_int64 iRowid;
           46  +  sqlite3_stmt *pStmt;
           47  +};
           48  +
           49  +typedef struct CidxColumn CidxColumn;
           50  +struct CidxColumn {
           51  +  char *zExpr;                    /* Text for indexed expression */
           52  +  int bDesc;                      /* True for DESC columns, otherwise false */
           53  +  int bKey;                       /* Part of index, not PK */
           54  +};
           55  +
           56  +typedef struct CidxIndex CidxIndex;
           57  +struct CidxIndex {
           58  +  int nCol;                       /* Elements in aCol[] array */
           59  +  CidxColumn aCol[1];             /* Array of indexed columns */
           60  +};
           61  +
           62  +static void *cidxMalloc(int *pRc, int n){
           63  +  void *pRet = 0;
           64  +  assert( n!=0 );
           65  +  if( *pRc==SQLITE_OK ){
           66  +    pRet = sqlite3_malloc(n);
           67  +    if( pRet ){
           68  +      memset(pRet, 0, n);
           69  +    }else{
           70  +      *pRc = SQLITE_NOMEM;
           71  +    }
           72  +  }
           73  +  return pRet;
           74  +}
           75  +
           76  +static void cidxCursorError(CidxCursor *pCsr, const char *zFmt, ...){
           77  +  va_list ap;
           78  +  va_start(ap, zFmt);
           79  +  assert( pCsr->base.pVtab->zErrMsg==0 );
           80  +  pCsr->base.pVtab->zErrMsg = sqlite3_vmprintf(zFmt, ap);
           81  +  va_end(ap);
           82  +}
           83  +
           84  +/*
           85  +** Connect to then incremental_index_check virtual table.
           86  +*/
           87  +static int cidxConnect(
           88  +  sqlite3 *db,
           89  +  void *pAux,
           90  +  int argc, const char *const*argv,
           91  +  sqlite3_vtab **ppVtab,
           92  +  char **pzErr
           93  +){
           94  +  int rc = SQLITE_OK;
           95  +  CidxTable *pRet;
           96  +
           97  +#define IIC_ERRMSG        0
           98  +#define IIC_CURRENT_KEY   1
           99  +#define IIC_INDEX_NAME    2
          100  +#define IIC_AFTER_KEY     3
          101  +  rc = sqlite3_declare_vtab(db,
          102  +      "CREATE TABLE xyz("
          103  +      " errmsg TEXT, current_key TEXT,"
          104  +      " index_name HIDDEN, after_key HIDDEN"
          105  +      ")"
          106  +  );
          107  +  pRet = cidxMalloc(&rc, sizeof(CidxTable));
          108  +  if( pRet ){
          109  +    pRet->db = db;
          110  +  }
          111  +
          112  +  *ppVtab = (sqlite3_vtab*)pRet;
          113  +  return rc;
          114  +}
          115  +
          116  +/*
          117  +** Disconnect from or destroy an incremental_index_check virtual table.
          118  +*/
          119  +static int cidxDisconnect(sqlite3_vtab *pVtab){
          120  +  CidxTable *pTab = (CidxTable*)pVtab;
          121  +  sqlite3_free(pTab);
          122  +  return SQLITE_OK;
          123  +}
          124  +
          125  +/*
          126  +** xBestIndex method.
          127  +*/
          128  +static int cidxBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pInfo){
          129  +  int iIdxName = -1;
          130  +  int iAfterKey = -1;
          131  +  int i;
          132  +
          133  +  for(i=0; i<pInfo->nConstraint; i++){
          134  +    struct sqlite3_index_constraint *p = &pInfo->aConstraint[i];
          135  +    if( p->usable==0 ) continue;
          136  +    if( p->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
          137  +
          138  +    if( p->iColumn==IIC_INDEX_NAME ){
          139  +      iIdxName = i;
          140  +    }
          141  +    if( p->iColumn==IIC_AFTER_KEY ){
          142  +      iAfterKey = i;
          143  +    }
          144  +  }
          145  +
          146  +  if( iIdxName<0 ){
          147  +    pInfo->estimatedCost = 1000000000.0;
          148  +  }else{
          149  +    pInfo->aConstraintUsage[iIdxName].argvIndex = 1;
          150  +    pInfo->aConstraintUsage[iIdxName].omit = 1;
          151  +    if( iAfterKey<0 ){
          152  +      pInfo->estimatedCost = 1000000.0;
          153  +    }else{
          154  +      pInfo->aConstraintUsage[iAfterKey].argvIndex = 2;
          155  +      pInfo->aConstraintUsage[iAfterKey].omit = 1;
          156  +      pInfo->estimatedCost = 1000.0;
          157  +    }
          158  +  }
          159  +
          160  +  return SQLITE_OK;
          161  +}
          162  +
          163  +/*
          164  +** Open a new btreeinfo cursor.
          165  +*/
          166  +static int cidxOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          167  +  CidxCursor *pRet;
          168  +  int rc = SQLITE_OK;
          169  +
          170  +  pRet = cidxMalloc(&rc, sizeof(CidxCursor));
          171  +
          172  +  *ppCursor = (sqlite3_vtab_cursor*)pRet;
          173  +  return rc;
          174  +}
          175  +
          176  +/*
          177  +** Close a btreeinfo cursor.
          178  +*/
          179  +static int cidxClose(sqlite3_vtab_cursor *pCursor){
          180  +  CidxCursor *pCsr = (CidxCursor*)pCursor;
          181  +  sqlite3_finalize(pCsr->pStmt);
          182  +  pCsr->pStmt = 0;
          183  +  sqlite3_free(pCsr);
          184  +  return SQLITE_OK;
          185  +}
          186  +
          187  +/*
          188  +** Move a btreeinfo cursor to the next entry in the file.
          189  +*/
          190  +static int cidxNext(sqlite3_vtab_cursor *pCursor){
          191  +  CidxCursor *pCsr = (CidxCursor*)pCursor;
          192  +  int rc = sqlite3_step(pCsr->pStmt);
          193  +  if( rc!=SQLITE_ROW ){
          194  +    rc = sqlite3_finalize(pCsr->pStmt);
          195  +    pCsr->pStmt = 0;
          196  +    if( rc!=SQLITE_OK ){
          197  +      sqlite3 *db = ((CidxTable*)pCsr->base.pVtab)->db;
          198  +      cidxCursorError(pCsr, "Cursor error: %s", sqlite3_errmsg(db));
          199  +    }
          200  +  }else{
          201  +    pCsr->iRowid++;
          202  +    rc = SQLITE_OK;
          203  +  }
          204  +  return rc;
          205  +}
          206  +
          207  +/* We have reached EOF if previous sqlite3_step() returned
          208  +** anything other than SQLITE_ROW;
          209  +*/
          210  +static int cidxEof(sqlite3_vtab_cursor *pCursor){
          211  +  CidxCursor *pCsr = (CidxCursor*)pCursor;
          212  +  return pCsr->pStmt==0;
          213  +}
          214  +
          215  +static char *cidxMprintf(int *pRc, const char *zFmt, ...){
          216  +  char *zRet = 0;
          217  +  va_list ap;
          218  +  va_start(ap, zFmt);
          219  +  zRet = sqlite3_vmprintf(zFmt, ap);
          220  +  if( *pRc==SQLITE_OK ){
          221  +    if( zRet==0 ){
          222  +      *pRc = SQLITE_NOMEM;
          223  +    }
          224  +  }else{
          225  +    sqlite3_free(zRet);
          226  +    zRet = 0;
          227  +  }
          228  +  va_end(ap);
          229  +  return zRet;
          230  +}
          231  +
          232  +static sqlite3_stmt *cidxPrepare(
          233  +  int *pRc, CidxCursor *pCsr, const char *zFmt, ...
          234  +){
          235  +  sqlite3_stmt *pRet = 0;
          236  +  char *zSql;
          237  +  va_list ap;                     /* ... printf arguments */
          238  +  va_start(ap, zFmt);
          239  +
          240  +  zSql = sqlite3_vmprintf(zFmt, ap);
          241  +  if( *pRc==SQLITE_OK ){
          242  +    if( zSql==0 ){
          243  +      *pRc = SQLITE_NOMEM;
          244  +    }else{
          245  +      sqlite3 *db = ((CidxTable*)pCsr->base.pVtab)->db;
          246  +      *pRc = sqlite3_prepare_v2(db, zSql, -1, &pRet, 0);
          247  +      if( *pRc!=SQLITE_OK ){
          248  +        cidxCursorError(pCsr, "SQL error: %s", sqlite3_errmsg(db));
          249  +      }
          250  +    }
          251  +  }
          252  +  sqlite3_free(zSql);
          253  +  va_end(ap);
          254  +
          255  +  return pRet;
          256  +}
          257  +
          258  +static void cidxFinalize(int *pRc, sqlite3_stmt *pStmt){
          259  +  int rc = sqlite3_finalize(pStmt);
          260  +  if( *pRc==SQLITE_OK ) *pRc = rc;
          261  +}
          262  +
          263  +char *cidxStrdup(int *pRc, const char *zStr){
          264  +  char *zRet = 0;
          265  +  if( *pRc==SQLITE_OK ){
          266  +    int n = strlen(zStr);
          267  +    zRet = cidxMalloc(pRc, n+1);
          268  +    if( zRet ) memcpy(zRet, zStr, n+1);
          269  +  }
          270  +  return zRet;
          271  +}
          272  +
          273  +static void cidxFreeIndex(CidxIndex *pIdx){
          274  +  if( pIdx ){
          275  +    int i;
          276  +    for(i=0; i<pIdx->nCol; i++){
          277  +      sqlite3_free(pIdx->aCol[i].zExpr);
          278  +    }
          279  +    sqlite3_free(pIdx);
          280  +  }
          281  +}
          282  +
          283  +static int cidx_isspace(char c){
          284  +  return c==' ' || c=='\t' || c=='\r' || c=='\n';
          285  +}
          286  +
          287  +static int cidx_isident(char c){
          288  +  return c<0 
          289  +    || (c>='0' && c<='9') || (c>='a' && c<='z') 
          290  +    || (c>='A' && c<='Z') || c=='_';
          291  +}
          292  +
          293  +#define CIDX_PARSE_EOF   0
          294  +#define CIDX_PARSE_COMMA 1      /*  "," */
          295  +#define CIDX_PARSE_OPEN  2      /*  "(" */
          296  +#define CIDX_PARSE_CLOSE 3      /*  ")" */
          297  +
          298  +static int cidxFindNext(
          299  +  const char *zIn, 
          300  +  const char **pzOut,
          301  +  int *pbDoNotTrim                /* OUT: True if prev is -- comment */
          302  +){
          303  +  const char *z = zIn;
          304  +
          305  +  while( 1 ){
          306  +    if( z[0]=='-' && z[1]=='-' ){
          307  +      z += 2;
          308  +      while( z[0]!='\n' ){
          309  +        if( z[0]=='\0' ) return CIDX_PARSE_EOF;
          310  +        z++;
          311  +      }
          312  +      while( cidx_isspace(*z) ) z++;
          313  +      *pbDoNotTrim = 1;
          314  +    }else{
          315  +      *pzOut = z;
          316  +      switch( *z ){
          317  +        case '\0':
          318  +          return CIDX_PARSE_EOF;
          319  +        case '(':
          320  +          return CIDX_PARSE_OPEN;
          321  +        case ')':
          322  +          return CIDX_PARSE_CLOSE;
          323  +        case ',':
          324  +          return CIDX_PARSE_COMMA;
          325  +  
          326  +        case '"': 
          327  +        case '\'': 
          328  +        case '`': {
          329  +          char q = *z;
          330  +          z++;
          331  +          while( *z ){
          332  +            if( *z==q ){
          333  +              z++;
          334  +              if( *z!=q ) break;
          335  +            }
          336  +            z++;
          337  +          }
          338  +          break;
          339  +        }
          340  +  
          341  +        case '[':
          342  +          while( *z++!=']' );
          343  +          break;
          344  +  
          345  +        case '/':
          346  +          if( z[1]=='*' ){
          347  +            z += 2;
          348  +            while( z[0]!='*' || z[1]!='/' ){
          349  +              if( z[1]=='\0' ) return CIDX_PARSE_EOF;
          350  +              z++;
          351  +            }
          352  +            z += 2;
          353  +            break;
          354  +          }
          355  +  
          356  +        default:
          357  +          z++;
          358  +          break;
          359  +      }
          360  +      *pbDoNotTrim = 0;
          361  +    }
          362  +  }
          363  +
          364  +  assert( 0 );
          365  +  return -1;
          366  +}
          367  +
          368  +static int cidxParseSQL(CidxCursor *pCsr, CidxIndex *pIdx, const char *zSql){
          369  +  const char *z = zSql;
          370  +  const char *z1;
          371  +  int e;
          372  +  int rc = SQLITE_OK;
          373  +  int nParen = 1;
          374  +  int bDoNotTrim = 0;
          375  +  CidxColumn *pCol = pIdx->aCol;
          376  +
          377  +  e = cidxFindNext(z, &z, &bDoNotTrim);
          378  +  if( e!=CIDX_PARSE_OPEN ) goto parse_error;
          379  +  z1 = z+1;
          380  +  z++;
          381  +  while( nParen>0 ){
          382  +    e = cidxFindNext(z, &z, &bDoNotTrim);
          383  +    if( e==CIDX_PARSE_EOF ) goto parse_error;
          384  +    if( (e==CIDX_PARSE_COMMA || e==CIDX_PARSE_CLOSE) && nParen==1 ){
          385  +      const char *z2 = z;
          386  +      if( pCol->zExpr ) goto parse_error;
          387  +
          388  +      if( bDoNotTrim==0 ){
          389  +        while( cidx_isspace(z[-1]) ) z--;
          390  +        if( !sqlite3_strnicmp(&z[-3], "asc", 3) && 0==cidx_isident(z[-4]) ){
          391  +          z -= 3;
          392  +          while( cidx_isspace(z[-1]) ) z--;
          393  +        }else
          394  +          if( !sqlite3_strnicmp(&z[-4], "desc", 4) && 0==cidx_isident(z[-5]) ){
          395  +            z -= 4;
          396  +            while( cidx_isspace(z[-1]) ) z--;
          397  +          }
          398  +        while( cidx_isspace(z1[0]) ) z1++;
          399  +      }
          400  +
          401  +      pCol->zExpr = cidxMprintf(&rc, "%.*s", z-z1, z1);
          402  +      pCol++;
          403  +      z = z1 = z2+1;
          404  +    }
          405  +    if( e==CIDX_PARSE_OPEN ) nParen++;
          406  +    if( e==CIDX_PARSE_CLOSE ) nParen--;
          407  +    z++;
          408  +  }
          409  +
          410  +  return rc;
          411  +
          412  + parse_error:
          413  +  cidxCursorError(pCsr, "Parse error in: %s", zSql);
          414  +  return SQLITE_ERROR;
          415  +}
          416  +
          417  +static int cidxLookupIndex(
          418  +  CidxCursor *pCsr,               /* Cursor object */
          419  +  const char *zIdx,               /* Name of index to look up */
          420  +  CidxIndex **ppIdx,              /* OUT: Description of columns */
          421  +  char **pzTab                    /* OUT: Table name */
          422  +){
          423  +  int rc = SQLITE_OK;
          424  +  char *zTab = 0;
          425  +  CidxIndex *pIdx = 0;
          426  +
          427  +  sqlite3_stmt *pFindTab = 0;
          428  +  sqlite3_stmt *pInfo = 0;
          429  +    
          430  +  /* Find the table for this index. */
          431  +  pFindTab = cidxPrepare(&rc, pCsr, 
          432  +      "SELECT tbl_name, sql FROM sqlite_master WHERE name=%Q AND type='index'",
          433  +      zIdx
          434  +  );
          435  +  if( rc==SQLITE_OK && sqlite3_step(pFindTab)==SQLITE_ROW ){
          436  +    const char *zSql = (const char*)sqlite3_column_text(pFindTab, 1);
          437  +    zTab = cidxStrdup(&rc, (const char*)sqlite3_column_text(pFindTab, 0));
          438  +
          439  +    pInfo = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx);
          440  +    if( rc==SQLITE_OK ){
          441  +      int nAlloc = 0;
          442  +      int iCol = 0;
          443  +
          444  +      while( sqlite3_step(pInfo)==SQLITE_ROW ){
          445  +        const char *zName = (const char*)sqlite3_column_text(pInfo, 2);
          446  +        const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);
          447  +        CidxColumn *p;
          448  +        if( zName==0 ) zName = "rowid";
          449  +        if( iCol==nAlloc ){
          450  +          int nByte = sizeof(CidxIndex) + sizeof(CidxColumn)*(nAlloc+8);
          451  +          pIdx = (CidxIndex*)sqlite3_realloc(pIdx, nByte);
          452  +          nAlloc += 8;
          453  +        }
          454  +        p = &pIdx->aCol[iCol++];
          455  +        p->bDesc = sqlite3_column_int(pInfo, 3);
          456  +        p->bKey = sqlite3_column_int(pInfo, 5);
          457  +        if( zSql==0 || p->bKey==0 ){
          458  +          p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl);
          459  +        }else{
          460  +          p->zExpr = 0;
          461  +        }
          462  +        pIdx->nCol = iCol;
          463  +      }
          464  +      cidxFinalize(&rc, pInfo);
          465  +    }
          466  +
          467  +    if( rc==SQLITE_OK && zSql ){
          468  +      rc = cidxParseSQL(pCsr, pIdx, zSql);
          469  +    }
          470  +  }
          471  +
          472  +  cidxFinalize(&rc, pFindTab);
          473  +  if( rc==SQLITE_OK && zTab==0 ){
          474  +    rc = SQLITE_ERROR;
          475  +  }
          476  +  
          477  +  if( rc!=SQLITE_OK ){
          478  +    sqlite3_free(zTab);
          479  +    cidxFreeIndex(pIdx);
          480  +  }else{
          481  +    *pzTab = zTab;
          482  +    *ppIdx = pIdx;
          483  +  }
          484  +
          485  +  return rc;
          486  +}
          487  +
          488  +static int cidxDecodeAfter(
          489  +  CidxCursor *pCsr, 
          490  +  int nCol, 
          491  +  const char *zAfterKey, 
          492  +  char ***pazAfter
          493  +){
          494  +  char **azAfter;
          495  +  int rc = SQLITE_OK;
          496  +  int nAfterKey = strlen(zAfterKey);
          497  +
          498  +  azAfter = cidxMalloc(&rc, sizeof(char*)*nCol + nAfterKey+1);
          499  +  if( rc==SQLITE_OK ){
          500  +    int i;
          501  +    char *zCopy = (char*)&azAfter[nCol];
          502  +    char *p = zCopy;
          503  +    memcpy(zCopy, zAfterKey, nAfterKey+1);
          504  +    for(i=0; i<nCol; i++){
          505  +      while( *p==' ' ) p++;
          506  +
          507  +      /* Check NULL values */
          508  +      if( *p=='N' ){
          509  +        if( memcmp(p, "NULL", 4) ) goto parse_error;
          510  +        p += 4;
          511  +      }
          512  +
          513  +      /* Check strings and blob literals */
          514  +      else if( *p=='X' || *p=='\'' ){
          515  +        azAfter[i] = p;
          516  +        if( *p=='X' ) p++;
          517  +        if( *p!='\'' ) goto parse_error;
          518  +        p++;
          519  +        while( 1 ){
          520  +          if( *p=='\0' ) goto parse_error;
          521  +          if( *p=='\'' ){
          522  +            p++;
          523  +            if( *p!='\'' ) break;
          524  +          }
          525  +          p++;
          526  +        }
          527  +      }
          528  +
          529  +      /* Check numbers */
          530  +      else{
          531  +        azAfter[i] = p;
          532  +        while( (*p>='0' && *p<='9') 
          533  +            || *p=='.' || *p=='+' || *p=='-' || *p=='e' || *p=='E'
          534  +        ){
          535  +          p++;
          536  +        }
          537  +      }
          538  +
          539  +      while( *p==' ' ) p++;
          540  +      if( *p!=(i==(nCol-1) ? '\0' : ',') ){
          541  +        goto parse_error;
          542  +      }
          543  +      *p++ = '\0';
          544  +    }
          545  +  }
          546  +
          547  +  *pazAfter = azAfter;
          548  +  return rc;
          549  +
          550  + parse_error:
          551  +  sqlite3_free(azAfter);
          552  +  *pazAfter = 0;
          553  +  cidxCursorError(pCsr, "%s", "error parsing after value");
          554  +  return SQLITE_ERROR;
          555  +}
          556  +
          557  +static char *cidxWhere(
          558  +  int *pRc, CidxColumn *aCol, char **azAfter, int iGt, int bLastIsNull
          559  +){
          560  +  char *zRet = 0;
          561  +  const char *zSep = "";
          562  +  int i;
          563  +
          564  +  for(i=0; i<iGt; i++){
          565  +    zRet = cidxMprintf(pRc, "%z%s(%s) IS %s", zRet, 
          566  +        zSep, aCol[i].zExpr, (azAfter[i] ? azAfter[i] : "NULL")
          567  +    );
          568  +    zSep = " AND ";
          569  +  }
          570  +
          571  +  if( bLastIsNull ){
          572  +    zRet = cidxMprintf(pRc, "%z%s(%s) IS NULL", zRet, zSep, aCol[iGt].zExpr);
          573  +  }
          574  +  else if( azAfter[iGt] ){
          575  +    zRet = cidxMprintf(pRc, "%z%s(%s) %s %s", zRet, 
          576  +        zSep, aCol[iGt].zExpr, (aCol[iGt].bDesc ? "<" : ">"), 
          577  +        azAfter[iGt]
          578  +    );
          579  +  }else{
          580  +    zRet = cidxMprintf(pRc, "%z%s(%s) IS NOT NULL", zRet, zSep,aCol[iGt].zExpr);
          581  +  }
          582  +
          583  +  return zRet;
          584  +}
          585  +
          586  +#define CIDX_CLIST_ALL         0
          587  +#define CIDX_CLIST_ORDERBY     1
          588  +#define CIDX_CLIST_CURRENT_KEY 2
          589  +#define CIDX_CLIST_SUBWHERE    3
          590  +#define CIDX_CLIST_SUBEXPR     4
          591  +
          592  +/*
          593  +** This function returns various strings based on the contents of the
          594  +** CidxIndex structure and the eType parameter.
          595  +*/
          596  +static char *cidxColumnList(
          597  +  int *pRc,                       /* IN/OUT: Error code */
          598  +  const char *zIdx,
          599  +  CidxIndex *pIdx,                /* Indexed columns */
          600  +  int eType                       /* True to include ASC/DESC */
          601  +){
          602  +  char *zRet = 0;
          603  +  if( *pRc==SQLITE_OK ){
          604  +    const char *aDir[2] = {"", " DESC"};
          605  +    int i;
          606  +    const char *zSep = "";
          607  +
          608  +    for(i=0; i<pIdx->nCol; i++){
          609  +      CidxColumn *p = &pIdx->aCol[i];
          610  +      assert( pIdx->aCol[i].bDesc==0 || pIdx->aCol[i].bDesc==1 );
          611  +      switch( eType ){
          612  +
          613  +        case CIDX_CLIST_ORDERBY:
          614  +          zRet = cidxMprintf(pRc, "%z%s%d%s", zRet, zSep, i+1, aDir[p->bDesc]);
          615  +          zSep = ",";
          616  +          break;
          617  +
          618  +        case CIDX_CLIST_CURRENT_KEY:
          619  +          zRet = cidxMprintf(pRc, "%z%squote(i%d)", zRet, zSep, i);
          620  +          zSep = "||','||";
          621  +          break;
          622  +
          623  +        case CIDX_CLIST_SUBWHERE:
          624  +          if( p->bKey==0 ){
          625  +            zRet = cidxMprintf(pRc, "%z%s%s IS i.i%d", zRet, 
          626  +                zSep, p->zExpr, i
          627  +            );
          628  +            zSep = " AND ";
          629  +          }
          630  +          break;
          631  +
          632  +        case CIDX_CLIST_SUBEXPR:
          633  +          if( p->bKey==1 ){
          634  +            zRet = cidxMprintf(pRc, "%z%s%s IS i.i%d", zRet, 
          635  +                zSep, p->zExpr, i
          636  +            );
          637  +            zSep = " AND ";
          638  +          }
          639  +          break;
          640  +
          641  +        default:
          642  +          assert( eType==CIDX_CLIST_ALL );
          643  +          zRet = cidxMprintf(pRc, "%z%s(%s) AS i%d", zRet, zSep, p->zExpr, i);
          644  +          zSep = ", ";
          645  +          break;
          646  +      }
          647  +    }
          648  +  }
          649  +
          650  +  return zRet;
          651  +}
          652  +
          653  +/* 
          654  +** Position a cursor back to the beginning.
          655  +*/
          656  +static int cidxFilter(
          657  +  sqlite3_vtab_cursor *pCursor, 
          658  +  int idxNum, const char *idxStr,
          659  +  int argc, sqlite3_value **argv
          660  +){
          661  +  int rc = SQLITE_OK;
          662  +  CidxCursor *pCsr = (CidxCursor*)pCursor;
          663  +  const char *zIdxName = 0;
          664  +  const char *zAfterKey = 0;
          665  +
          666  +  if( argc>0 ){
          667  +    zIdxName = (const char*)sqlite3_value_text(argv[0]);
          668  +    if( argc>1 ){
          669  +      zAfterKey = (const char*)sqlite3_value_text(argv[1]);
          670  +    }
          671  +  }
          672  +
          673  +  if( zIdxName ){
          674  +    char *zTab = 0;
          675  +    char *zCurrentKey = 0;
          676  +    char *zOrderBy = 0;
          677  +    char *zSubWhere = 0;
          678  +    char *zSubExpr = 0;
          679  +    char *zSrcList = 0;
          680  +
          681  +    char **azAfter = 0;
          682  +    CidxIndex *pIdx = 0;
          683  +
          684  +    rc = cidxLookupIndex(pCsr, zIdxName, &pIdx, &zTab);
          685  +
          686  +    zOrderBy = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ORDERBY);
          687  +    zCurrentKey = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_CURRENT_KEY);
          688  +    zSubWhere = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBWHERE);
          689  +    zSubExpr = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBEXPR);
          690  +    zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL);
          691  +
          692  +    if( rc==SQLITE_OK && zAfterKey ){
          693  +      rc = cidxDecodeAfter(pCsr, pIdx->nCol, zAfterKey, &azAfter);
          694  +    }
          695  +
          696  +    if( rc || zAfterKey==0 ){
          697  +      pCsr->pStmt = cidxPrepare(&rc, pCsr, 
          698  +          "SELECT (SELECT %s FROM %Q AS t WHERE %s), %s "
          699  +          "FROM (SELECT %s FROM %Q ORDER BY %s) AS i",
          700  +          zSubExpr, zTab, zSubWhere, zCurrentKey, 
          701  +          zSrcList, zTab, zOrderBy
          702  +      );
          703  +      /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */
          704  +    }else{
          705  +      const char *zSep = "";
          706  +      char *zSql;
          707  +      int i;
          708  +
          709  +      zSql = cidxMprintf(&rc, 
          710  +          "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (",
          711  +          zSubExpr, zTab, zSubWhere, zCurrentKey
          712  +      );
          713  +      for(i=pIdx->nCol-1; i>=0; i--){
          714  +        int j;
          715  +        if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue;
          716  +        for(j=0; j<2; j++){
          717  +          char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j);
          718  +          zSql = cidxMprintf(&rc, "%z"
          719  +              "%sSELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)",
          720  +              zSql, zSep, zSrcList, zTab, zWhere, zOrderBy
          721  +          );
          722  +          zSep = " UNION ALL ";
          723  +          if( pIdx->aCol[i].bDesc==0 ) break;
          724  +        }
          725  +      }
          726  +      zSql = cidxMprintf(&rc, "%z) AS i", zSql);
          727  +
          728  +      /* printf("SQL: %s\n", zSql); */
          729  +      pCsr->pStmt = cidxPrepare(&rc, pCsr, "%z", zSql);
          730  +    }
          731  +
          732  +    sqlite3_free(zTab);
          733  +    sqlite3_free(zCurrentKey);
          734  +    sqlite3_free(zOrderBy);
          735  +    sqlite3_free(zSubWhere);
          736  +    sqlite3_free(zSubExpr);
          737  +    sqlite3_free(zSrcList);
          738  +    cidxFreeIndex(pIdx);
          739  +    sqlite3_free(azAfter);
          740  +  }
          741  +
          742  +  if( pCsr->pStmt ){
          743  +    assert( rc==SQLITE_OK );
          744  +    rc = cidxNext(pCursor);
          745  +  }
          746  +  pCsr->iRowid = 1;
          747  +  return rc;
          748  +}
          749  +
          750  +/* 
          751  +** Return a column value.
          752  +*/
          753  +static int cidxColumn(
          754  +  sqlite3_vtab_cursor *pCursor, 
          755  +  sqlite3_context *ctx, 
          756  +  int iCol
          757  +){
          758  +  CidxCursor *pCsr = (CidxCursor*)pCursor;
          759  +  assert( iCol>=IIC_ERRMSG && iCol<=IIC_AFTER_KEY );
          760  +  if( iCol==IIC_ERRMSG ){
          761  +    const char *zVal = 0;
          762  +    if( sqlite3_column_type(pCsr->pStmt, 0)==SQLITE_INTEGER ){
          763  +      if( sqlite3_column_int(pCsr->pStmt, 0)==0 ){
          764  +        zVal = "row data mismatch";
          765  +      }
          766  +    }else{
          767  +      zVal = "row missing";
          768  +    }
          769  +    sqlite3_result_text(ctx, zVal, -1, SQLITE_STATIC);
          770  +  }else if( iCol==IIC_CURRENT_KEY ){
          771  +    sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pStmt, 1));
          772  +  }
          773  +  return SQLITE_OK;
          774  +}
          775  +
          776  +/* Return the ROWID for the sqlite_btreeinfo table */
          777  +static int cidxRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){
          778  +  *pRowid = 0;
          779  +  return SQLITE_OK;
          780  +}
          781  +
          782  +/*
          783  +** Register the virtual table modules with the database handle passed
          784  +** as the only argument.
          785  +*/
          786  +static int ciInit(sqlite3 *db){
          787  +  static sqlite3_module cidx_module = {
          788  +    0,                            /* iVersion */
          789  +    0,                            /* xCreate */
          790  +    cidxConnect,                  /* xConnect */
          791  +    cidxBestIndex,                /* xBestIndex */
          792  +    cidxDisconnect,               /* xDisconnect */
          793  +    0,                            /* xDestroy */
          794  +    cidxOpen,                     /* xOpen - open a cursor */
          795  +    cidxClose,                    /* xClose - close a cursor */
          796  +    cidxFilter,                   /* xFilter - configure scan constraints */
          797  +    cidxNext,                     /* xNext - advance a cursor */
          798  +    cidxEof,                      /* xEof - check for end of scan */
          799  +    cidxColumn,                   /* xColumn - read data */
          800  +    cidxRowid,                    /* xRowid - read data */
          801  +    0,                            /* xUpdate */
          802  +    0,                            /* xBegin */
          803  +    0,                            /* xSync */
          804  +    0,                            /* xCommit */
          805  +    0,                            /* xRollback */
          806  +    0,                            /* xFindMethod */
          807  +    0,                            /* xRename */
          808  +    0,                            /* xSavepoint */
          809  +    0,                            /* xRelease */
          810  +    0,                            /* xRollbackTo */
          811  +  };
          812  +  return sqlite3_create_module(db, "incremental_index_check", &cidx_module, 0);
          813  +}
          814  +
          815  +/*
          816  +** Extension load function.
          817  +*/
          818  +#ifdef _WIN32
          819  +__declspec(dllexport)
          820  +#endif
          821  +int sqlite3_checkindex_init(
          822  +  sqlite3 *db, 
          823  +  char **pzErrMsg, 
          824  +  const sqlite3_api_routines *pApi
          825  +){
          826  +  SQLITE_EXTENSION_INIT2(pApi);
          827  +  return ciInit(db);
          828  +}

Added ext/repair/sqlite3_checker.c.in.

            1  +/*
            2  +** Read an SQLite database file and analyze its space utilization.  Generate
            3  +** text on standard output.
            4  +*/
            5  +#define TCLSH_INIT_PROC sqlite3_checker_init_proc
            6  +#define SQLITE_ENABLE_DBPAGE_VTAB 1
            7  +#undef SQLITE_THREADSAFE
            8  +#define SQLITE_THREADSAFE 0
            9  +#undef SQLITE_ENABLE_COLUMN_METADATA
           10  +#define SQLITE_OMIT_DECLTYPE 1
           11  +#define SQLITE_OMIT_DEPRECATED 1
           12  +#define SQLITE_OMIT_PROGRESS_CALLBACK 1
           13  +#define SQLITE_OMIT_SHARED_CACHE 1
           14  +#define SQLITE_DEFAULT_MEMSTATUS 0
           15  +#define SQLITE_MAX_EXPR_DEPTH 0
           16  +INCLUDE sqlite3.c
           17  +INCLUDE $ROOT/src/tclsqlite.c
           18  +INCLUDE $ROOT/ext/misc/btreeinfo.c
           19  +INCLUDE $ROOT/ext/repair/checkindex.c
           20  +INCLUDE $ROOT/ext/repair/checkfreelist.c
           21  +
           22  +/*
           23  +** Decode a pointer to an sqlite3 object.
           24  +*/
           25  +int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb){
           26  +  struct SqliteDb *p;
           27  +  Tcl_CmdInfo cmdInfo;
           28  +  if( Tcl_GetCommandInfo(interp, zA, &cmdInfo) ){
           29  +    p = (struct SqliteDb*)cmdInfo.objClientData;
           30  +    *ppDb = p->db;
           31  +    return TCL_OK;
           32  +  }else{
           33  +    *ppDb = 0;
           34  +    return TCL_ERROR;
           35  +  }
           36  +  return TCL_OK;
           37  +}
           38  +
           39  +/*
           40  +**   sqlite3_imposter db main rootpage {CREATE TABLE...}  ;# setup an imposter
           41  +**   sqlite3_imposter db main                             ;# rm all imposters
           42  +*/
           43  +static int sqlite3_imposter(
           44  +  void *clientData,
           45  +  Tcl_Interp *interp,
           46  +  int objc,
           47  +  Tcl_Obj *CONST objv[]
           48  +){
           49  +  sqlite3 *db;
           50  +  const char *zSchema;
           51  +  int iRoot;
           52  +  const char *zSql;
           53  +
           54  +  if( objc!=3 && objc!=5 ){
           55  +    Tcl_WrongNumArgs(interp, 1, objv, "DB SCHEMA [ROOTPAGE SQL]");
           56  +    return TCL_ERROR;
           57  +  }
           58  +  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
           59  +  zSchema = Tcl_GetString(objv[2]);
           60  +  if( objc==3 ){
           61  +    sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, zSchema, 0, 1);
           62  +  }else{
           63  +    if( Tcl_GetIntFromObj(interp, objv[3], &iRoot) ) return TCL_ERROR;
           64  +    zSql = Tcl_GetString(objv[4]);
           65  +    sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, zSchema, 1, iRoot);
           66  +    sqlite3_exec(db, zSql, 0, 0, 0);
           67  +    sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, zSchema, 0, 0);
           68  +  }
           69  +  return TCL_OK;
           70  +}
           71  +
           72  +#include <stdio.h>
           73  +
           74  +const char *sqlite3_checker_init_proc(Tcl_Interp *interp){
           75  +  Tcl_CreateObjCommand(interp, "sqlite3_imposter", 
           76  +                       (Tcl_ObjCmdProc*)sqlite3_imposter, 0, 0);
           77  +  sqlite3_auto_extension((void(*)(void))sqlite3_btreeinfo_init);
           78  +  sqlite3_auto_extension((void(*)(void))sqlite3_checkindex_init);
           79  +  sqlite3_auto_extension((void(*)(void))sqlite3_checkfreelist_init);
           80  +  return
           81  +BEGIN_STRING
           82  +INCLUDE $ROOT/ext/repair/sqlite3_checker.tcl
           83  +END_STRING
           84  +;
           85  +}

Added ext/repair/sqlite3_checker.tcl.

            1  +# This TCL script is the main driver script for the sqlite3_checker utility
            2  +# program.
            3  +#
            4  +
            5  +# Special case:
            6  +#
            7  +#      sqlite3_checker --test FILENAME ARGS
            8  +#
            9  +# uses FILENAME in place of this script.
           10  +#
           11  +if {[lindex $argv 0]=="--test" && [llength $argv]>1} {
           12  +  set ::argv0 [lindex $argv 1]
           13  +  set argv [lrange $argv 2 end]
           14  +  source $argv0
           15  +  exit 0
           16  +}
           17  +
           18  +# Emulate a TCL shell
           19  +#
           20  +proc tclsh {} {
           21  +  set line {}
           22  +  while {![eof stdin]} {
           23  +    if {$line!=""} {
           24  +      puts -nonewline "> "
           25  +    } else {
           26  +      puts -nonewline "% "
           27  +    }
           28  +    flush stdout
           29  +    append line [gets stdin]
           30  +    if {[info complete $line]} {
           31  +      if {[catch {uplevel #0 $line} result]} {
           32  +        puts stderr "Error: $result"
           33  +      } elseif {$result!=""} {
           34  +        puts $result
           35  +      }
           36  +      set line {}
           37  +    } else {
           38  +      append line \n
           39  +    }
           40  +  }
           41  +}
           42  +
           43  +# Do an incremental integrity check of a single index
           44  +#
           45  +proc check_index {idxname batchsize} {
           46  +  set i 0
           47  +  set more 1
           48  +  set nerr 0
           49  +  set pct 00.0
           50  +  set max [db one {SELECT nEntry FROM sqlite_btreeinfo('main')
           51  +                    WHERE name=$idxname}]
           52  +  puts -nonewline "$idxname: $i of $max rows ($pct%)\r"
           53  +  flush stdout
           54  +  while {$more} {
           55  +    set more 0
           56  +    db eval {SELECT errmsg, current_key AS key
           57  +               FROM incremental_index_check($idxname)
           58  +              WHERE after_key=$key
           59  +              LIMIT $batchsize} {
           60  +      set more 1
           61  +      if {$errmsg!=""} {
           62  +        incr nerr
           63  +        puts "$idxname: key($key): $errmsg"
           64  +      }
           65  +      incr i
           66  +    }
           67  +    set x [format {%.1f} [expr {($i*100.0)/$max}]]
           68  +    if {$x!=$pct} {
           69  +      puts -nonewline "$idxname: $i of $max rows ($pct%)\r"
           70  +      flush stdout
           71  +      set pct $x
           72  +    }
           73  +  }
           74  +  puts "$idxname: $nerr errors out of $i entries"
           75  +}
           76  +
           77  +# Print a usage message on standard error, then quit.
           78  +#
           79  +proc usage {} {
           80  +  set argv0 [file rootname [file tail [info nameofexecutable]]]
           81  +  puts stderr "Usage: $argv0 OPTIONS database-filename"
           82  +  puts stderr {
           83  +Do sanity checking on a live SQLite3 database file specified by the
           84  +"database-filename" argument.
           85  +
           86  +Options:
           87  +
           88  +   --batchsize N     Number of rows to check per transaction
           89  +
           90  +   --freelist        Perform a freelist check
           91  +
           92  +   --index NAME      Run a check of the index NAME
           93  +
           94  +   --summary         Print summary information about the database
           95  +
           96  +   --table NAME      Run a check of all indexes for table NAME
           97  +
           98  +   --tclsh           Run the built-in TCL interpreter (for debugging)
           99  +
          100  +   --version         Show the version number of SQLite
          101  +}
          102  +  exit 1
          103  +}
          104  +
          105  +set file_to_analyze {}
          106  +append argv {}
          107  +set bFreelistCheck 0
          108  +set bSummary 0
          109  +set zIndex {}
          110  +set zTable {}
          111  +set batchsize 1000
          112  +set bAll 1
          113  +set argc [llength $argv]
          114  +for {set i 0} {$i<$argc} {incr i} {
          115  +  set arg [lindex $argv $i]
          116  +  if {[regexp {^-+tclsh$} $arg]} {
          117  +    tclsh
          118  +    exit 0
          119  +  }
          120  +  if {[regexp {^-+version$} $arg]} {
          121  +    sqlite3 mem :memory:
          122  +    puts [mem one {SELECT sqlite_version()||' '||sqlite_source_id()}]
          123  +    mem close
          124  +    exit 0
          125  +  }
          126  +  if {[regexp {^-+freelist$} $arg]} {
          127  +    set bFreelistCheck 1
          128  +    set bAll 0
          129  +    continue
          130  +  }
          131  +  if {[regexp {^-+summary$} $arg]} {
          132  +    set bSummary 1
          133  +    set bAll 0
          134  +    continue
          135  +  }
          136  +  if {[regexp {^-+batchsize$} $arg]} {
          137  +    incr i
          138  +    if {$i>=$argc} {
          139  +      puts stderr "missing argument on $arg"
          140  +      exit 1
          141  +    }
          142  +    set batchsize [lindex $argv $i]
          143  +    continue
          144  +  }
          145  +  if {[regexp {^-+index$} $arg]} {
          146  +    incr i
          147  +    if {$i>=$argc} {
          148  +      puts stderr "missing argument on $arg"
          149  +      exit 1
          150  +    }
          151  +    set zIndex [lindex $argv $i]
          152  +    set bAll 0
          153  +    continue
          154  +  }
          155  +  if {[regexp {^-+table$} $arg]} {
          156  +    incr i
          157  +    if {$i>=$argc} {
          158  +      puts stderr "missing argument on $arg"
          159  +      exit 1
          160  +    }
          161  +    set zTable [lindex $argv $i]
          162  +    set bAll 0
          163  +    continue
          164  +  }
          165  +  if {[regexp {^-} $arg]} {
          166  +    puts stderr "Unknown option: $arg"
          167  +    usage
          168  +  }
          169  +  if {$file_to_analyze!=""} {
          170  +    usage
          171  +  } else {
          172  +    set file_to_analyze $arg
          173  +  }
          174  +}
          175  +if {$file_to_analyze==""} usage
          176  +
          177  +# If a TCL script is specified on the command-line, then run that
          178  +# script.
          179  +#
          180  +if {[file extension $file_to_analyze]==".tcl"} {
          181  +  source $file_to_analyze
          182  +  exit 0
          183  +}
          184  +
          185  +set root_filename $file_to_analyze
          186  +regexp {^file:(//)?([^?]*)} $file_to_analyze all x1 root_filename
          187  +if {![file exists $root_filename]} {
          188  +  puts stderr "No such file: $root_filename"
          189  +  exit 1
          190  +}
          191  +if {![file readable $root_filename]} {
          192  +  puts stderr "File is not readable: $root_filename"
          193  +  exit 1
          194  +}
          195  +
          196  +if {[catch {sqlite3 db $file_to_analyze} res]} {
          197  +  puts stderr "Cannot open datababase $root_filename: $res"
          198  +  exit 1
          199  +}
          200  +
          201  +if {$bFreelistCheck || $bAll} {
          202  +  puts -nonewline "freelist-check: "
          203  +  flush stdout
          204  +  puts [db one {SELECT checkfreelist('main')}]
          205  +}
          206  +if {$bSummary} {
          207  +  set scale 0
          208  +  set pgsz [db one {PRAGMA page_size}]
          209  +  db eval {SELECT nPage*$pgsz AS sz, name, tbl_name
          210  +             FROM sqlite_btreeinfo
          211  +            WHERE type='index'
          212  +            ORDER BY 1 DESC, name} {
          213  +    if {$scale==0} {
          214  +      if {$sz>10000000} {
          215  +        set scale 1000000.0
          216  +        set unit MB
          217  +      } else {
          218  +        set scale 1000.0
          219  +        set unit KB
          220  +      }
          221  +    }
          222  +    puts [format {%7.1f %s index %s of table %s} \
          223  +            [expr {$sz/$scale}] $unit $name $tbl_name]
          224  +  }
          225  +}
          226  +if {$zIndex!=""} {
          227  +  check_index $zIndex $batchsize
          228  +}
          229  +if {$zTable!=""} {
          230  +  foreach idx [db eval {SELECT name FROM sqlite_master
          231  +                         WHERE type='index' AND rootpage>0
          232  +                           AND tbl_name=$zTable}] {
          233  +    check_index $idx $batchsize
          234  +  }
          235  +}
          236  +if {$bAll} {
          237  +  set allidx [db eval {SELECT name FROM sqlite_btreeinfo('main')
          238  +                        WHERE type='index' AND rootpage>0
          239  +                        ORDER BY nEntry}]
          240  +  foreach idx $allidx {
          241  +    check_index $idx $batchsize
          242  +  }
          243  +}

Added ext/repair/test/README.md.

            1  +To run these tests, first build sqlite3_checker:
            2  +
            3  +
            4  +>     make sqlite3_checker
            5  +
            6  +
            7  +Then run the "test.tcl" script using:
            8  +
            9  +
           10  +>     ./sqlite3_checker --test $path/test.tcl
           11  +
           12  +
           13  +Optionally add the full pathnames of individual *.test modules

Added ext/repair/test/checkfreelist01.test.

            1  +# 2017-10-11
            2  +
            3  +set testprefix checkfreelist
            4  +
            5  +do_execsql_test 1.0 {
            6  +  PRAGMA page_size=1024;
            7  +  CREATE TABLE t1(a, b);
            8  +}
            9  +
           10  +do_execsql_test 1.2 { SELECT checkfreelist('main') } {ok}
           11  +do_execsql_test 1.3 {
           12  +  WITH s(i) AS (
           13  +    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000
           14  +  )
           15  +  INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s;
           16  +  DELETE FROM t1 WHERE rowid%3;
           17  +  PRAGMA freelist_count;
           18  +} {6726}
           19  +
           20  +do_execsql_test 1.4 { SELECT checkfreelist('main') } {ok}
           21  +do_execsql_test 1.5 {
           22  +  WITH freelist_trunk(i, d, n) AS (
           23  +    SELECT 1, NULL, sqlite_readint32(data, 32) FROM sqlite_dbpage WHERE pgno=1
           24  +      UNION ALL
           25  +    SELECT n, data, sqlite_readint32(data) 
           26  +    FROM freelist_trunk, sqlite_dbpage WHERE pgno=n
           27  +  )
           28  +  SELECT i FROM freelist_trunk WHERE i!=1;
           29  +} {
           30  +  10009 9715 9343 8969 8595 8222 7847 7474 7102 6727 6354 5982 5608 5234
           31  +  4860 4487 4112 3740 3367 2992 2619 2247 1872 1499 1125 752 377 5
           32  +}
           33  +
           34  +do_execsql_test 1.6 { SELECT checkfreelist('main') } {ok}
           35  +
           36  +proc set_int {blob idx newval} {
           37  +  binary scan $blob I* ints
           38  +  lset ints $idx $newval
           39  +  binary format I* $ints
           40  +}
           41  +db func set_int set_int
           42  +
           43  +proc get_int {blob idx} {
           44  +  binary scan $blob I* ints
           45  +  lindex $ints $idx
           46  +}
           47  +db func get_int get_int
           48  +
           49  +do_execsql_test 1.7 {
           50  +  BEGIN;
           51  +    UPDATE sqlite_dbpage 
           52  +      SET data = set_int(data, 1, get_int(data, 1)-1) 
           53  +      WHERE pgno=4860;
           54  +    SELECT checkfreelist('main');
           55  +  ROLLBACK;
           56  +} {{free-list count mismatch: actual=6725 header=6726}}
           57  +
           58  +do_execsql_test 1.8 {
           59  +  BEGIN;
           60  +    UPDATE sqlite_dbpage 
           61  +      SET data = set_int(data, 5, (SELECT * FROM pragma_page_count)+1)
           62  +      WHERE pgno=4860;
           63  +    SELECT checkfreelist('main');
           64  +  ROLLBACK;
           65  +} {{leaf page 10092 is out of range (child 3 of trunk page 4860)}}
           66  +
           67  +do_execsql_test 1.9 {
           68  +  BEGIN;
           69  +    UPDATE sqlite_dbpage 
           70  +      SET data = set_int(data, 5, 0)
           71  +      WHERE pgno=4860;
           72  +    SELECT checkfreelist('main');
           73  +  ROLLBACK;
           74  +} {{leaf page 0 is out of range (child 3 of trunk page 4860)}}
           75  +
           76  +do_execsql_test 1.10 {
           77  +  BEGIN;
           78  +    UPDATE sqlite_dbpage 
           79  +      SET data = set_int(data, get_int(data, 1)+1, 0)
           80  +      WHERE pgno=5;
           81  +    SELECT checkfreelist('main');
           82  +  ROLLBACK;
           83  +} {{leaf page 0 is out of range (child 247 of trunk page 5)}}
           84  +
           85  +do_execsql_test 1.11 {
           86  +  BEGIN;
           87  +    UPDATE sqlite_dbpage 
           88  +      SET data = set_int(data, 1, 249)
           89  +      WHERE pgno=5;
           90  +    SELECT checkfreelist('main');
           91  +  ROLLBACK;
           92  +} {{leaf count out of range (249) on trunk page 5}}

Added ext/repair/test/checkindex01.test.

            1  +# 2017-10-11
            2  +#
            3  +set testprefix checkindex
            4  +
            5  +do_execsql_test 1.0 {
            6  +  CREATE TABLE t1(a, b);
            7  +  CREATE INDEX i1 ON t1(a);
            8  +  INSERT INTO t1 VALUES('one', 2);
            9  +  INSERT INTO t1 VALUES('two', 4);
           10  +  INSERT INTO t1 VALUES('three', 6);
           11  +  INSERT INTO t1 VALUES('four', 8);
           12  +  INSERT INTO t1 VALUES('five', 10);
           13  +
           14  +  CREATE INDEX i2 ON t1(a DESC);
           15  +} {}
           16  +
           17  +proc incr_index_check {idx nStep} {
           18  +  set Q {
           19  +    SELECT errmsg, current_key FROM incremental_index_check($idx, $after)
           20  +    LIMIT $nStep
           21  +  }
           22  +
           23  +  set res [list]
           24  +  while {1} {
           25  +    unset -nocomplain current_key
           26  +    set res1 [db eval $Q]
           27  +    if {[llength $res1]==0} break
           28  +    set res [concat $res $res1]
           29  +    set after [lindex $res end]
           30  +  }
           31  +
           32  +  return $res
           33  +}
           34  +
           35  +proc do_index_check_test {tn idx res} {
           36  +  uplevel [list do_execsql_test $tn.1 "
           37  +    SELECT errmsg, current_key FROM incremental_index_check('$idx');
           38  +  " $res]
           39  +
           40  +  uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]]
           41  +  uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]]
           42  +  uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]]
           43  +}
           44  +
           45  +
           46  +do_execsql_test 1.2 {
           47  +  SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1');
           48  +} {
           49  +  1 'five',5
           50  +  1 'four',4
           51  +  1 'one',1
           52  +  1 'three',3
           53  +  1 'two',2
           54  +}
           55  +
           56  +do_index_check_test 1.3 i1 {
           57  +  {} 'five',5
           58  +  {} 'four',4
           59  +  {} 'one',1
           60  +  {} 'three',3
           61  +  {} 'two',2
           62  +}
           63  +
           64  +do_index_check_test 1.4 i2 {
           65  +  {} 'two',2
           66  +  {} 'three',3
           67  +  {} 'one',1
           68  +  {} 'four',4
           69  +  {} 'five',5
           70  +}
           71  +
           72  +do_test 1.5 {
           73  +  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t1' }]
           74  +  sqlite3_imposter db main $tblroot {CREATE TABLE xt1(a,b)}
           75  +  db eval {
           76  +    UPDATE xt1 SET a='six' WHERE rowid=3;
           77  +    DELETE FROM xt1 WHERE rowid = 5;
           78  +  }
           79  +  sqlite3_imposter db main
           80  +} {}
           81  +
           82  +do_index_check_test 1.6 i1 {
           83  +  {row missing} 'five',5
           84  +  {} 'four',4
           85  +  {} 'one',1
           86  +  {row data mismatch} 'three',3
           87  +  {} 'two',2
           88  +}
           89  +
           90  +do_index_check_test 1.7 i2 {
           91  +  {} 'two',2
           92  +  {row data mismatch} 'three',3
           93  +  {} 'one',1
           94  +  {} 'four',4
           95  +  {row missing} 'five',5
           96  +}
           97  +
           98  +#--------------------------------------------------------------------------
           99  +do_execsql_test 2.0 {
          100  +
          101  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c, d);
          102  +
          103  +  INSERT INTO t2 VALUES(1, NULL, 1, 1);
          104  +  INSERT INTO t2 VALUES(2, 1, NULL, 1);
          105  +  INSERT INTO t2 VALUES(3, 1, 1, NULL);
          106  +
          107  +  INSERT INTO t2 VALUES(4, 2, 2, 1);
          108  +  INSERT INTO t2 VALUES(5, 2, 2, 2);
          109  +  INSERT INTO t2 VALUES(6, 2, 2, 3);
          110  +
          111  +  INSERT INTO t2 VALUES(7, 2, 2, 1);
          112  +  INSERT INTO t2 VALUES(8, 2, 2, 2);
          113  +  INSERT INTO t2 VALUES(9, 2, 2, 3);
          114  +
          115  +  CREATE INDEX i3 ON t2(b, c, d);
          116  +  CREATE INDEX i4 ON t2(b DESC, c DESC, d DESC);
          117  +  CREATE INDEX i5 ON t2(d, c DESC, b);
          118  +} {}
          119  +
          120  +do_index_check_test 2.1 i3 {
          121  +  {} NULL,1,1,1 
          122  +  {} 1,NULL,1,2 
          123  +  {} 1,1,NULL,3 
          124  +  {} 2,2,1,4 
          125  +  {} 2,2,1,7 
          126  +  {} 2,2,2,5
          127  +  {} 2,2,2,8 
          128  +  {} 2,2,3,6 
          129  +  {} 2,2,3,9
          130  +}
          131  +
          132  +do_index_check_test 2.2 i4 {
          133  +  {} 2,2,3,6 
          134  +  {} 2,2,3,9
          135  +  {} 2,2,2,5
          136  +  {} 2,2,2,8 
          137  +  {} 2,2,1,4 
          138  +  {} 2,2,1,7 
          139  +  {} 1,1,NULL,3 
          140  +  {} 1,NULL,1,2 
          141  +  {} NULL,1,1,1 
          142  +}
          143  +
          144  +do_index_check_test 2.3 i5 {
          145  +  {} NULL,1,1,3 
          146  +  {} 1,2,2,4 
          147  +  {} 1,2,2,7 
          148  +  {} 1,1,NULL,1 
          149  +  {} 1,NULL,1,2 
          150  +  {} 2,2,2,5 
          151  +  {} 2,2,2,8 
          152  +  {} 3,2,2,6 
          153  +  {} 3,2,2,9
          154  +}
          155  +
          156  +#--------------------------------------------------------------------------
          157  +do_execsql_test 3.0 {
          158  +
          159  +  CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID;
          160  +  CREATE INDEX t3wxy ON t3(w, x, y);
          161  +  CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC);
          162  +
          163  +  INSERT INTO t3 VALUES(NULL, NULL, NULL, 1);
          164  +  INSERT INTO t3 VALUES(NULL, NULL, NULL, 2);
          165  +  INSERT INTO t3 VALUES(NULL, NULL, NULL, 3);
          166  +
          167  +  INSERT INTO t3 VALUES('a', NULL, NULL, 4);
          168  +  INSERT INTO t3 VALUES('a', NULL, NULL, 5);
          169  +  INSERT INTO t3 VALUES('a', NULL, NULL, 6);
          170  +
          171  +  INSERT INTO t3 VALUES('a', 'b', NULL, 7);
          172  +  INSERT INTO t3 VALUES('a', 'b', NULL, 8);
          173  +  INSERT INTO t3 VALUES('a', 'b', NULL, 9);
          174  +
          175  +} {}
          176  +
          177  +do_index_check_test 3.1 t3wxy {
          178  +  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
          179  +  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
          180  +  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
          181  +}
          182  +do_index_check_test 3.2 t3wxy2 {
          183  +  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
          184  +  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
          185  +  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
          186  +}
          187  +
          188  +#--------------------------------------------------------------------------
          189  +# Test with an index that uses non-default collation sequences.
          190  +#
          191  +do_execsql_test 4.0 {
          192  +  CREATE TABLE t4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT);
          193  +  INSERT INTO t4 VALUES(1, 'aaa', 'bbb');
          194  +  INSERT INTO t4 VALUES(2, 'AAA', 'CCC');
          195  +  INSERT INTO t4 VALUES(3, 'aab', 'ddd');
          196  +  INSERT INTO t4 VALUES(4, 'AAB', 'EEE');
          197  +
          198  +  CREATE INDEX t4cc ON t4(c1 COLLATE nocase, c2 COLLATE nocase);
          199  +}
          200  +
          201  +do_index_check_test 4.1 t4cc {
          202  +  {} 'aaa','bbb',1 
          203  +  {} 'AAA','CCC',2 
          204  +  {} 'aab','ddd',3 
          205  +  {} 'AAB','EEE',4
          206  +}
          207  +
          208  +do_test 4.2 {
          209  +  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t4' }]
          210  +  sqlite3_imposter db main $tblroot \
          211  +     {CREATE TABLE xt4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT)}
          212  +
          213  +  db eval {
          214  +    UPDATE xt4 SET c1='hello' WHERE rowid=2;
          215  +    DELETE FROM xt4 WHERE rowid = 3;
          216  +  }
          217  +  sqlite3_imposter db main
          218  +} {}
          219  +
          220  +do_index_check_test 4.3 t4cc {
          221  +  {} 'aaa','bbb',1 
          222  +  {row data mismatch} 'AAA','CCC',2 
          223  +  {row missing} 'aab','ddd',3 
          224  +  {} 'AAB','EEE',4
          225  +}
          226  +
          227  +#--------------------------------------------------------------------------
          228  +# Test an index on an expression.
          229  +#
          230  +do_execsql_test 5.0 {
          231  +  CREATE TABLE t5(x INTEGER PRIMARY KEY, y TEXT, UNIQUE(y));
          232  +  INSERT INTO t5 VALUES(1, '{"x":1, "y":1}');
          233  +  INSERT INTO t5 VALUES(2, '{"x":2, "y":2}');
          234  +  INSERT INTO t5 VALUES(3, '{"x":3, "y":3}');
          235  +  INSERT INTO t5 VALUES(4, '{"w":4, "z":4}');
          236  +  INSERT INTO t5 VALUES(5, '{"x":5, "y":5}');
          237  +
          238  +  CREATE INDEX t5x ON t5( json_extract(y, '$.x') );
          239  +  CREATE INDEX t5y ON t5( json_extract(y, '$.y') DESC );
          240  +}
          241  +
          242  +do_index_check_test 5.1.1 t5x {
          243  +  {} NULL,4 {} 1,1 {} 2,2 {} 3,3 {} 5,5
          244  +}
          245  +
          246  +do_index_check_test 5.1.2 t5y {
          247  +  {} 5,5 {} 3,3 {} 2,2 {} 1,1 {} NULL,4
          248  +}
          249  +
          250  +do_index_check_test 5.1.3 sqlite_autoindex_t5_1 {
          251  +  {} {'{"w":4, "z":4}',4} 
          252  +  {} {'{"x":1, "y":1}',1} 
          253  +  {} {'{"x":2, "y":2}',2} 
          254  +  {} {'{"x":3, "y":3}',3} 
          255  +  {} {'{"x":5, "y":5}',5}
          256  +}
          257  +
          258  +do_test 5.2 {
          259  +  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t5' }]
          260  +  sqlite3_imposter db main $tblroot \
          261  +      {CREATE TABLE xt5(a INTEGER PRIMARY KEY, c1 TEXT);}
          262  +  db eval {
          263  +    UPDATE xt5 SET c1='{"x":22, "y":11}' WHERE rowid=1;
          264  +    DELETE FROM xt5 WHERE rowid = 4;
          265  +  }
          266  +  sqlite3_imposter db main
          267  +} {}
          268  +
          269  +do_index_check_test 5.3.1 t5x {
          270  +  {row missing} NULL,4 
          271  +  {row data mismatch} 1,1 
          272  +  {} 2,2 
          273  +  {} 3,3 
          274  +  {} 5,5
          275  +}
          276  +
          277  +do_index_check_test 5.3.2 sqlite_autoindex_t5_1 {
          278  +  {row missing} {'{"w":4, "z":4}',4} 
          279  +  {row data mismatch} {'{"x":1, "y":1}',1} 
          280  +  {} {'{"x":2, "y":2}',2} 
          281  +  {} {'{"x":3, "y":3}',3} 
          282  +  {} {'{"x":5, "y":5}',5}
          283  +}
          284  +
          285  +#-------------------------------------------------------------------------
          286  +#
          287  +do_execsql_test 6.0 {
          288  +  CREATE TABLE t6(x INTEGER PRIMARY KEY, y, z);
          289  +  CREATE INDEX t6x1 ON t6(y, /* one,two,three */ z);
          290  +  CREATE INDEX t6x2 ON t6(z, -- hello,world,
          291  +  y);
          292  +
          293  +  CREATE INDEX t6x3 ON t6(z -- hello,world
          294  +  , y);
          295  +
          296  +  INSERT INTO t6 VALUES(1, 2, 3);
          297  +  INSERT INTO t6 VALUES(4, 5, 6);
          298  +}
          299  +
          300  +do_index_check_test 6.1 t6x1 {
          301  +  {} 2,3,1 
          302  +  {} 5,6,4
          303  +}
          304  +do_index_check_test 6.2 t6x2 {
          305  +  {} 3,2,1 
          306  +  {} 6,5,4
          307  +}
          308  +do_index_check_test 6.2 t6x3 {
          309  +  {} 3,2,1 
          310  +  {} 6,5,4
          311  +}

Added ext/repair/test/test.tcl.

            1  +# Run this script using
            2  +#
            3  +#       sqlite3_checker --test $thisscript $testscripts
            4  +#
            5  +# The $testscripts argument is optional.  If omitted, all *.test files
            6  +# in the same directory as $thisscript are run.
            7  +#
            8  +set NTEST 0
            9  +set NERR  0
           10  +
           11  +
           12  +# Invoke the do_test procedure to run a single test
           13  +#
           14  +# The $expected parameter is the expected result.  The result is the return
           15  +# value from the last TCL command in $cmd.
           16  +#
           17  +# Normally, $expected must match exactly.  But if $expected is of the form
           18  +# "/regexp/" then regular expression matching is used.  If $expected is
           19  +# "~/regexp/" then the regular expression must NOT match.  If $expected is
           20  +# of the form "#/value-list/" then each term in value-list must be numeric
           21  +# and must approximately match the corresponding numeric term in $result.
           22  +# Values must match within 10%.  Or if the $expected term is A..B then the
           23  +# $result term must be in between A and B.
           24  +#
           25  +proc do_test {name cmd expected} {
           26  +  if {[info exists ::testprefix]} {
           27  +    set name "$::testprefix$name"
           28  +  }
           29  +
           30  +  incr ::NTEST
           31  +  puts -nonewline $name...
           32  +  flush stdout
           33  +
           34  +  if {[catch {uplevel #0 "$cmd;\n"} result]} {
           35  +    puts -nonewline $name...
           36  +    puts "\nError: $result"
           37  +    incr ::NERR
           38  +  } else {
           39  +    set ok [expr {[string compare $result $expected]==0}]
           40  +    if {!$ok} {
           41  +      puts "\n!  $name expected: \[$expected\]\n! $name got:      \[$result\]"
           42  +      incr ::NERR
           43  +    } else {
           44  +      puts " Ok"
           45  +    }
           46  +  }
           47  +  flush stdout
           48  +}
           49  +
           50  +#
           51  +#   do_execsql_test TESTNAME SQL RES
           52  +#
           53  +proc do_execsql_test {testname sql {result {}}} {
           54  +  uplevel [list do_test $testname [list db eval $sql] [list {*}$result]]
           55  +}
           56  +
           57  +if {[llength $argv]==0} {
           58  +  set dir [file dirname $argv0]
           59  +  set argv [glob -nocomplain $dir/*.test]
           60  +}
           61  +foreach testfile $argv {
           62  +  file delete -force test.db
           63  +  sqlite3 db test.db
           64  +  source $testfile
           65  +  catch {db close}
           66  +}
           67  +puts "$NERR errors out of $NTEST tests"

Changes to main.mk.

   460    460   
   461    461   # executables needed for testing
   462    462   #
   463    463   TESTPROGS = \
   464    464     testfixture$(EXE) \
   465    465     sqlite3$(EXE) \
   466    466     sqlite3_analyzer$(EXE) \
          467  +  sqlite3_checker$(EXE) \
   467    468     sqldiff$(EXE) \
   468    469     dbhash$(EXE)
   469    470   
   470    471   # Databases containing fuzzer test cases
   471    472   #
   472    473   FUZZDATA = \
   473    474     $(TOP)/test/fuzzdata1.db \
................................................................................
   782    783   
   783    784   sqlite3_analyzer.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/spaceanal.tcl $(TOP)/tool/sqlite3_analyzer.c.in $(TOP)/tool/mkccode.tcl
   784    785   	tclsh $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c
   785    786   
   786    787   sqlite3_analyzer$(EXE): sqlite3_analyzer.c
   787    788   	$(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) 
   788    789   
          790  +CHECKER_DEPS =\
          791  +  $(TOP)/tool/mkccode.tcl \
          792  +  sqlite3.c \
          793  +  $(TOP)/src/tclsqlite.c \
          794  +  $(TOP)/ext/repair/sqlite3_checker.tcl \
          795  +  $(TOP)/ext/repair/checkindex.c \
          796  +  $(TOP)/ext/repair/checkfreelist.c \
          797  +  $(TOP)/ext/misc/btreeinfo.c \
          798  +  $(TOP)/ext/repair/sqlite3_checker.c.in
          799  +
          800  +sqlite3_checker.c:	$(CHECKER_DEPS)
          801  +	tclsh $(TOP)/tool/mkccode.tcl $(TOP)/ext/repair/sqlite3_checker.c.in >$@
          802  +
          803  +sqlite3_checker$(TEXE):	sqlite3_checker.c
          804  +	$(TCCX) $(TCL_FLAGS) sqlite3_checker.c -o $@ $(LIBTCL) $(THREADLIB)
          805  +
   789    806   dbdump$(EXE):	$(TOP)/ext/misc/dbdump.c sqlite3.o
   790    807   	$(TCCX) -DDBDUMP_STANDALONE -o dbdump$(EXE) \
   791    808               $(TOP)/ext/misc/dbdump.c sqlite3.o $(THREADLIB)
   792    809   
   793    810   # Rules to build the 'testfixture' application.
   794    811   #
   795    812   TESTFIXTURE_FLAGS  = -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1

Deleted test/checkfreelist.test.

     1         -# 2017-10-11
     2         -#
     3         -# The author disclaims copyright to this source code.  In place of
     4         -# a legal notice, here is a blessing:
     5         -#
     6         -#    May you do good and not evil.
     7         -#    May you find forgiveness for yourself and forgive others.
     8         -#    May you share freely, never taking more than you give.
     9         -#
    10         -#***********************************************************************
    11         -# This file implements regression tests for SQLite library.  The
    12         -# focus of this file is testing the checkfreelist extension.
    13         -#
    14         -
    15         -set testdir [file dirname $argv0]
    16         -source $testdir/tester.tcl
    17         -set testprefix checkfreelist
    18         -
    19         -ifcapable !vtab||!compound {
    20         -  finish_test
    21         -  return
    22         -}
    23         -
    24         -if {[file exists ../checkfreelist.so]==0} {
    25         -  finish_test
    26         -  return
    27         -}
    28         -
    29         -do_execsql_test 1.0 {
    30         -  CREATE TABLE t1(a, b);
    31         -}
    32         -
    33         -db enable_load_extension 1
    34         -do_execsql_test 1.1 {
    35         -  SELECT load_extension('../checkfreelist.so');
    36         -} {{}}
    37         -
    38         -do_execsql_test 1.2 { SELECT checkfreelist('main') } {ok}
    39         -do_execsql_test 1.3 {
    40         -  WITH s(i) AS (
    41         -    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000
    42         -  )
    43         -  INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s;
    44         -  DELETE FROM t1 WHERE rowid%3;
    45         -  PRAGMA freelist_count;
    46         -} {6726}
    47         -
    48         -do_execsql_test 1.4 { SELECT checkfreelist('main') } {ok}
    49         -do_execsql_test 1.5 {
    50         -  WITH freelist_trunk(i, d, n) AS (
    51         -    SELECT 1, NULL, sqlite_readint32(data, 32) FROM sqlite_dbpage WHERE pgno=1
    52         -      UNION ALL
    53         -    SELECT n, data, sqlite_readint32(data) 
    54         -    FROM freelist_trunk, sqlite_dbpage WHERE pgno=n
    55         -  )
    56         -  SELECT i FROM freelist_trunk WHERE i!=1;
    57         -} {
    58         -  10010 9716 9344 8970 8596 8223 7848 7475 7103 6728 6355 5983 5609 5235
    59         -  4861 4488 4113 3741 3368 2993 2620 2248 1873 1500 1126 753 378 5
    60         -}
    61         -
    62         -do_execsql_test 1.6 { SELECT checkfreelist('main') } {ok}
    63         -
    64         -proc set_int {blob idx newval} {
    65         -  binary scan $blob I* ints
    66         -  lset ints $idx $newval
    67         -  binary format I* $ints
    68         -}
    69         -db func set_int set_int
    70         -
    71         -proc get_int {blob idx} {
    72         -  binary scan $blob I* ints
    73         -  lindex $ints $idx
    74         -}
    75         -db func get_int get_int
    76         -
    77         -do_execsql_test 1.7 {
    78         -  BEGIN;
    79         -    UPDATE sqlite_dbpage 
    80         -      SET data = set_int(data, 1, get_int(data, 1)-1) 
    81         -      WHERE pgno=4861;
    82         -    SELECT checkfreelist('main');
    83         -  ROLLBACK;
    84         -} {{free-list count mismatch: actual=6725 header=6726}}
    85         -
    86         -do_execsql_test 1.8 {
    87         -  BEGIN;
    88         -    UPDATE sqlite_dbpage 
    89         -      SET data = set_int(data, 5, (SELECT * FROM pragma_page_count)+1)
    90         -      WHERE pgno=4861;
    91         -    SELECT checkfreelist('main');
    92         -  ROLLBACK;
    93         -} {{leaf page 10093 is out of range (child 3 of trunk page 4861)}}
    94         -
    95         -do_execsql_test 1.9 {
    96         -  BEGIN;
    97         -    UPDATE sqlite_dbpage 
    98         -      SET data = set_int(data, 5, 0)
    99         -      WHERE pgno=4861;
   100         -    SELECT checkfreelist('main');
   101         -  ROLLBACK;
   102         -} {{leaf page 0 is out of range (child 3 of trunk page 4861)}}
   103         -
   104         -do_execsql_test 1.10 {
   105         -  BEGIN;
   106         -    UPDATE sqlite_dbpage 
   107         -      SET data = set_int(data, get_int(data, 1)+1, 0)
   108         -      WHERE pgno=5;
   109         -    SELECT checkfreelist('main');
   110         -  ROLLBACK;
   111         -} {{leaf page 0 is out of range (child 247 of trunk page 5)}}
   112         -
   113         -do_execsql_test 1.11 {
   114         -  BEGIN;
   115         -    UPDATE sqlite_dbpage 
   116         -      SET data = set_int(data, 1, 249)
   117         -      WHERE pgno=5;
   118         -    SELECT checkfreelist('main');
   119         -  ROLLBACK;
   120         -} {{leaf count out of range (249) on trunk page 5}}
   121         -
   122         -finish_test
   123         -