Index: Makefile.in ================================================================== --- Makefile.in +++ Makefile.in @@ -164,11 +164,12 @@ # Object files for the SQLite library (non-amalgamation). # LIBOBJS0 = alter.lo analyze.lo attach.lo auth.lo \ backup.lo bitvec.lo btmutex.lo btree.lo build.lo \ - callback.lo complete.lo ctime.lo date.lo dbstat.lo delete.lo \ + callback.lo complete.lo ctime.lo \ + date.lo dbpage.lo dbstat.lo delete.lo \ expr.lo fault.lo fkey.lo \ fts3.lo fts3_aux.lo fts3_expr.lo fts3_hash.lo fts3_icu.lo \ fts3_porter.lo fts3_snippet.lo fts3_tokenizer.lo fts3_tokenizer1.lo \ fts3_tokenize_vtab.lo \ fts3_unicode.lo fts3_unicode2.lo fts3_write.lo \ @@ -213,10 +214,11 @@ $(TOP)/src/build.c \ $(TOP)/src/callback.c \ $(TOP)/src/complete.c \ $(TOP)/src/ctime.c \ $(TOP)/src/date.c \ + $(TOP)/src/dbpage.c \ $(TOP)/src/dbstat.c \ $(TOP)/src/delete.c \ $(TOP)/src/expr.c \ $(TOP)/src/fault.c \ $(TOP)/src/fkey.c \ @@ -447,10 +449,11 @@ $(TOP)/src/bitvec.c \ $(TOP)/src/btree.c \ $(TOP)/src/build.c \ $(TOP)/src/ctime.c \ $(TOP)/src/date.c \ + $(TOP)/src/dbpage.c \ $(TOP)/src/dbstat.c \ $(TOP)/src/expr.c \ $(TOP)/src/func.c \ $(TOP)/src/insert.c \ $(TOP)/src/wal.c \ @@ -567,10 +570,12 @@ SHELL_OPT = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 # SHELL_OPT += -DSQLITE_ENABLE_FTS5 SHELL_OPT += -DSQLITE_ENABLE_EXPLAIN_COMMENTS SHELL_OPT += -DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION SHELL_OPT += -DSQLITE_ENABLE_STMTVTAB +SHELL_OPT += -DSQLITE_ENABLE_DBPAGE_VTAB +SHELL_OPT += -DSQLITE_ENABLE_DBSTAT_VTAB FUZZERSHELL_OPT = -DSQLITE_ENABLE_JSON1 FUZZCHECK_OPT = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_MEMSYS5 -DSQLITE_OSS_FUZZ FUZZCHECK_OPT += -DSQLITE_MAX_MEMORY=50000000 FUZZCHECK_SRC = $(TOP)/test/fuzzcheck.c $(TOP)/test/ossfuzz.c DBFUZZ_OPT = @@ -750,10 +755,13 @@ $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/ctime.c date.lo: $(TOP)/src/date.c $(HDR) $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/date.c +dbpage.lo: $(TOP)/src/dbpage.c $(HDR) + $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/dbpage.c + dbstat.lo: $(TOP)/src/dbstat.c $(HDR) $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/dbstat.c delete.lo: $(TOP)/src/delete.c $(HDR) $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/delete.c Index: Makefile.msc ================================================================== --- Makefile.msc +++ Makefile.msc @@ -1089,11 +1089,12 @@ # <> # Object files for the SQLite library (non-amalgamation). # LIBOBJS0 = vdbe.lo parse.lo alter.lo analyze.lo attach.lo auth.lo \ backup.lo bitvec.lo btmutex.lo btree.lo build.lo \ - callback.lo complete.lo ctime.lo date.lo dbstat.lo delete.lo \ + callback.lo complete.lo ctime.lo \ + date.lo dbpage.lo dbstat.lo delete.lo \ expr.lo fault.lo fkey.lo \ fts3.lo fts3_aux.lo fts3_expr.lo fts3_hash.lo fts3_icu.lo \ fts3_porter.lo fts3_snippet.lo fts3_tokenizer.lo fts3_tokenizer1.lo \ fts3_tokenize_vtab.lo fts3_unicode.lo fts3_unicode2.lo fts3_write.lo \ fts5.lo \ @@ -1152,10 +1153,11 @@ $(TOP)\src\build.c \ $(TOP)\src\callback.c \ $(TOP)\src\complete.c \ $(TOP)\src\ctime.c \ $(TOP)\src\date.c \ + $(TOP)\src\dbpage.c \ $(TOP)\src\dbstat.c \ $(TOP)\src\delete.c \ $(TOP)\src\expr.c \ $(TOP)\src\fault.c \ $(TOP)\src\fkey.c \ @@ -1506,17 +1508,18 @@ # Additional compiler options for the shell. These are only effective # when the shell is not being dynamically linked. # !IF $(DYNAMIC_SHELL)==0 && $(FOR_WIN10)==0 -SHELL_COMPILE_OPTS = $(SHELL_COMPILE_OPTS) -DSQLITE_SHELL_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_STMTVTAB +SHELL_COMPILE_OPTS = $(SHELL_COMPILE_OPTS) -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_STMTVTAB +SHELL_COMPILE_OPTS = $(SHELL_COMPILE_OPTS) -DSQLITE_ENABLE_DBPAGE_VTAB -DSQLITE_ENABLE_DBSTAT_VTAB !ENDIF # <> # Extra compiler options for various test tools. # -MPTESTER_COMPILE_OPTS = -DSQLITE_SHELL_JSON1 -DSQLITE_ENABLE_FTS5 +MPTESTER_COMPILE_OPTS = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS5 FUZZERSHELL_COMPILE_OPTS = -DSQLITE_ENABLE_JSON1 FUZZCHECK_COMPILE_OPTS = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_MEMSYS5 -DSQLITE_OSS_FUZZ -DSQLITE_MAX_MEMORY=50000000 FUZZCHECK_SRC = $(TOP)\test\fuzzcheck.c $(TOP)\test\ossfuzz.c OSSSHELL_SRC = $(TOP)\test\ossshell.c $(TOP)\test\ossfuzz.c DBFUZZ_COMPILE_OPTS = -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION @@ -1745,11 +1748,14 @@ $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\ctime.c date.lo: $(TOP)\src\date.c $(HDR) $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\date.c -dbstat.lo: $(TOP)\src\date.c $(HDR) +dbpage.lo: $(TOP)\src\dbpage.c $(HDR) + $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\dbpage.c + +dbstat.lo: $(TOP)\src\dbstat.c $(HDR) $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\dbstat.c delete.lo: $(TOP)\src\delete.c $(HDR) $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\delete.c Index: autoconf/Makefile.msc ================================================================== --- autoconf/Makefile.msc +++ autoconf/Makefile.msc @@ -925,11 +925,11 @@ # Additional compiler options for the shell. These are only effective # when the shell is not being dynamically linked. # !IF $(DYNAMIC_SHELL)==0 && $(FOR_WIN10)==0 -SHELL_COMPILE_OPTS = $(SHELL_COMPILE_OPTS) -DSQLITE_SHELL_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_STMTVTAB +SHELL_COMPILE_OPTS = $(SHELL_COMPILE_OPTS) -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_STMTVTAB !ENDIF # This is the default Makefile target. The objects listed here # are what get build when you type just "make" with no arguments. ADDED ext/repair/README.md Index: ext/repair/README.md ================================================================== --- /dev/null +++ ext/repair/README.md @@ -0,0 +1,16 @@ +This folder contains extensions and utility programs intended to analyze +live database files, detect problems, and possibly fix them. + +As SQLite is being used on larger and larger databases, database sizes +are growing into the terabyte range. At that size, hardware malfunctions +and/or cosmic rays will occasionally corrupt a database file. Detecting +problems and fixing errors a terabyte-sized databases can take hours or days, +and it is undesirable to take applications that depend on the databases +off-line for such a long time. +The utilities in the folder are intended to provide mechanisms for +detecting and fixing problems in large databases while those databases +are in active use. + +The utilities and extensions in this folder are experimental and under +active development at the time of this writing (2017-10-12). If and when +they stabilize, this README will be updated to reflect that fact. ADDED ext/repair/checkfreelist.c Index: ext/repair/checkfreelist.c ================================================================== --- /dev/null +++ ext/repair/checkfreelist.c @@ -0,0 +1,299 @@ +/* +** 2017 October 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 module exports a single C function: +** +** int sqlite3_check_freelist(sqlite3 *db, const char *zDb); +** +** This function checks the free-list in database zDb (one of "main", +** "temp", etc.) and reports any errors by invoking the sqlite3_log() +** function. It returns SQLITE_OK if successful, or an SQLite error +** code otherwise. It is not an error if the free-list is corrupted but +** no IO or OOM errors occur. +** +** If this file is compiled and loaded as an SQLite loadable extension, +** it adds an SQL function "checkfreelist" to the database handle, to +** be invoked as follows: +** +** SELECT checkfreelist(); +** +** This function performs the same checks as sqlite3_check_freelist(), +** except that it returns all error messages as a single text value, +** separated by newline characters. If the freelist is not corrupted +** in any way, an empty string is returned. +** +** To compile this module for use as an SQLite loadable extension: +** +** gcc -Os -fPIC -shared checkfreelist.c -o checkfreelist.so +*/ + +#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 + +/* +** Execute a single PRAGMA statement and return the integer value returned +** via output parameter (*pnOut). +** +** The SQL statement passed as the third argument should be a printf-style +** format string containing a single "%s" which will be replace by the +** value passed as the second argument. e.g. +** +** sqlGetInteger(db, "main", "PRAGMA %s.page_count", pnOut) +** +** executes "PRAGMA main.page_count" and stores the results in (*pnOut). +*/ +static int sqlGetInteger( + sqlite3 *db, /* Database handle */ + const char *zDb, /* Database name ("main", "temp" etc.) */ + const char *zFmt, /* SQL statement format */ + u32 *pnOut /* OUT: Integer value */ +){ + int rc, rc2; + char *zSql; + sqlite3_stmt *pStmt = 0; + int bOk = 0; + + zSql = sqlite3_mprintf(zFmt, zDb); + if( zSql==0 ){ + rc = SQLITE_NOMEM; + }else{ + rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0); + sqlite3_free(zSql); + } + + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ + *pnOut = (u32)sqlite3_column_int(pStmt, 0); + bOk = 1; + } + + rc2 = sqlite3_finalize(pStmt); + if( rc==SQLITE_OK ) rc = rc2; + if( rc==SQLITE_OK && bOk==0 ) rc = SQLITE_ERROR; + return rc; +} + +/* +** Argument zFmt must be a printf-style format string and must be +** followed by its required arguments. If argument pzOut is NULL, +** then the results of printf()ing the format string are passed to +** sqlite3_log(). Otherwise, they are appended to the string +** at (*pzOut). +*/ +static int checkFreelistError(char **pzOut, const char *zFmt, ...){ + int rc = SQLITE_OK; + char *zErr = 0; + va_list ap; + + va_start(ap, zFmt); + zErr = sqlite3_vmprintf(zFmt, ap); + if( zErr==0 ){ + rc = SQLITE_NOMEM; + }else{ + if( pzOut ){ + *pzOut = sqlite3_mprintf("%s%z%s", *pzOut?"\n":"", *pzOut, zErr); + if( *pzOut==0 ) rc = SQLITE_NOMEM; + }else{ + sqlite3_log(SQLITE_ERROR, "checkfreelist: %s", zErr); + } + sqlite3_free(zErr); + } + va_end(ap); + return rc; +} + +static int checkFreelist( + sqlite3 *db, + const char *zDb, + char **pzOut +){ + /* This query returns one row for each page on the free list. Each row has + ** two columns - the page number and page content. */ + const char *zTrunk = + "WITH freelist_trunk(i, d, n) AS (" + "SELECT 1, NULL, sqlite_readint32(data, 32) " + "FROM sqlite_dbpage(:1) WHERE pgno=1 " + "UNION ALL " + "SELECT n, data, sqlite_readint32(data) " + "FROM freelist_trunk, sqlite_dbpage(:1) WHERE pgno=n " + ")" + "SELECT i, d FROM freelist_trunk WHERE i!=1;"; + + int rc, rc2; /* Return code */ + sqlite3_stmt *pTrunk = 0; /* Compilation of zTrunk */ + u32 nPage = 0; /* Number of pages in db */ + u32 nExpected = 0; /* Expected number of free pages */ + u32 nFree = 0; /* Number of pages on free list */ + + if( zDb==0 ) zDb = "main"; + + if( (rc = sqlGetInteger(db, zDb, "PRAGMA %s.page_count", &nPage)) + || (rc = sqlGetInteger(db, zDb, "PRAGMA %s.freelist_count", &nExpected)) + ){ + return rc; + } + + rc = sqlite3_prepare_v2(db, zTrunk, -1, &pTrunk, 0); + if( rc!=SQLITE_OK ) return rc; + sqlite3_bind_text(pTrunk, 1, zDb, -1, SQLITE_STATIC); + while( rc==SQLITE_OK && sqlite3_step(pTrunk)==SQLITE_ROW ){ + u32 i; + u32 iTrunk = (u32)sqlite3_column_int(pTrunk, 0); + const u8 *aData = (const u8*)sqlite3_column_blob(pTrunk, 1); + int nData = sqlite3_column_bytes(pTrunk, 1); + u32 iNext = get4byte(&aData[0]); + u32 nLeaf = get4byte(&aData[4]); + + if( nLeaf>((nData/4)-2-6) ){ + rc = checkFreelistError(pzOut, + "leaf count out of range (%d) on trunk page %d", + (int)nLeaf, (int)iTrunk + ); + nLeaf = (nData/4) - 2 - 6; + } + + nFree += 1+nLeaf; + if( iNext>nPage ){ + rc = checkFreelistError(pzOut, + "trunk page %d is out of range", (int)iNext + ); + } + + for(i=0; rc==SQLITE_OK && inPage ){ + rc = checkFreelistError(pzOut, + "leaf page %d is out of range (child %d of trunk page %d)", + (int)iLeaf, (int)i, (int)iTrunk + ); + } + } + } + + if( rc==SQLITE_OK && nFree!=nExpected ){ + rc = checkFreelistError(pzOut, + "free-list count mismatch: actual=%d header=%d", + (int)nFree, (int)nExpected + ); + } + + rc2 = sqlite3_finalize(pTrunk); + if( rc==SQLITE_OK ) rc = rc2; + return rc; +} + +int sqlite3_check_freelist(sqlite3 *db, const char *zDb){ + return checkFreelist(db, zDb, 0); +} + +static void checkfreelist_function( + sqlite3_context *pCtx, + int nArg, + sqlite3_value **apArg +){ + const char *zDb; + int rc; + char *zOut = 0; + sqlite3 *db = sqlite3_context_db_handle(pCtx); + + assert( nArg==1 ); + zDb = (const char*)sqlite3_value_text(apArg[0]); + rc = checkFreelist(db, zDb, &zOut); + if( rc==SQLITE_OK ){ + sqlite3_result_text(pCtx, zOut?zOut:"ok", -1, SQLITE_TRANSIENT); + }else{ + sqlite3_result_error_code(pCtx, rc); + } + + sqlite3_free(zOut); +} + +/* +** An SQL function invoked as follows: +** +** sqlite_readint32(BLOB) -- Decode 32-bit integer from start of blob +*/ +static void readint_function( + sqlite3_context *pCtx, + int nArg, + sqlite3_value **apArg +){ + const u8 *zBlob; + int nBlob; + int iOff = 0; + u32 iRet = 0; + + if( nArg!=1 && nArg!=2 ){ + sqlite3_result_error( + pCtx, "wrong number of arguments to function sqlite_readint32()", -1 + ); + return; + } + if( nArg==2 ){ + iOff = sqlite3_value_int(apArg[1]); + } + + zBlob = sqlite3_value_blob(apArg[0]); + nBlob = sqlite3_value_bytes(apArg[0]); + + if( nBlob>=(iOff+4) ){ + iRet = get4byte(&zBlob[iOff]); + } + + sqlite3_result_int64(pCtx, (sqlite3_int64)iRet); +} + +/* +** Register the SQL functions. +*/ +static int cflRegister(sqlite3 *db){ + int rc = sqlite3_create_function( + db, "sqlite_readint32", -1, SQLITE_UTF8, 0, readint_function, 0, 0 + ); + if( rc!=SQLITE_OK ) return rc; + rc = sqlite3_create_function( + db, "checkfreelist", 1, SQLITE_UTF8, 0, checkfreelist_function, 0, 0 + ); + return rc; +} + +/* +** Extension load function. +*/ +#ifdef _WIN32 +__declspec(dllexport) +#endif +int sqlite3_checkfreelist_init( + sqlite3 *db, + char **pzErrMsg, + const sqlite3_api_routines *pApi +){ + SQLITE_EXTENSION_INIT2(pApi); + return cflRegister(db); +} Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -53,11 +53,12 @@ # Object files for the SQLite library. # LIBOBJ+= vdbe.o parse.o \ alter.o analyze.o attach.o auth.o \ backup.o bitvec.o btmutex.o btree.o build.o \ - callback.o complete.o ctime.o date.o dbstat.o delete.o expr.o \ + callback.o complete.o ctime.o \ + date.o dbpage.o dbstat.o delete.o expr.o \ fault.o fkey.o \ fts3.o fts3_aux.o fts3_expr.o fts3_hash.o fts3_icu.o fts3_porter.o \ fts3_snippet.o fts3_tokenizer.o fts3_tokenizer1.o \ fts3_tokenize_vtab.o \ fts3_unicode.o fts3_unicode2.o \ @@ -94,10 +95,11 @@ $(TOP)/src/build.c \ $(TOP)/src/callback.c \ $(TOP)/src/complete.c \ $(TOP)/src/ctime.c \ $(TOP)/src/date.c \ + $(TOP)/src/dbpage.c \ $(TOP)/src/dbstat.c \ $(TOP)/src/delete.c \ $(TOP)/src/expr.c \ $(TOP)/src/fault.c \ $(TOP)/src/fkey.c \ @@ -357,10 +359,11 @@ $(TOP)/src/attach.c \ $(TOP)/src/backup.c \ $(TOP)/src/btree.c \ $(TOP)/src/build.c \ $(TOP)/src/date.c \ + $(TOP)/src/dbpage.c \ $(TOP)/src/dbstat.c \ $(TOP)/src/expr.c \ $(TOP)/src/func.c \ $(TOP)/src/insert.c \ $(TOP)/src/wal.c \ @@ -478,10 +481,12 @@ # SHELL_OPT += -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 SHELL_OPT += -DSQLITE_ENABLE_EXPLAIN_COMMENTS SHELL_OPT += -DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION SHELL_OPT += -DSQLITE_ENABLE_STMTVTAB +SHELL_OPT += -DSQLITE_ENABLE_DBPAGE_VTAB +SHELL_OPT += -DSQLITE_ENABLE_DBSTAT_VTAB FUZZERSHELL_OPT = -DSQLITE_ENABLE_JSON1 FUZZCHECK_OPT = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_MEMSYS5 FUZZCHECK_OPT += -DSQLITE_MAX_MEMORY=50000000 DBFUZZ_OPT = KV_OPT = -DSQLITE_THREADSAFE=0 -DSQLITE_DIRECT_OVERFLOW_READ Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -2038,11 +2038,11 @@ static Pgno btreePagecount(BtShared *pBt){ return pBt->nPage; } u32 sqlite3BtreeLastPage(Btree *p){ assert( sqlite3BtreeHoldsMutex(p) ); - assert( ((p->pBt->nPage)&0x8000000)==0 ); + assert( ((p->pBt->nPage)&0x80000000)==0 ); return btreePagecount(p->pBt); } /* ** Get a page from the pager and initialize it. ADDED src/dbpage.c Index: src/dbpage.c ================================================================== --- /dev/null +++ src/dbpage.c @@ -0,0 +1,329 @@ +/* +** 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 contains an implementation of the "sqlite_dbpage" virtual table. +** +** The sqlite_dbpage virtual table is used to read or write whole raw +** pages of the database file. The pager interface is used so that +** uncommitted changes and changes recorded in the WAL file are correctly +** retrieved. +** +** Usage example: +** +** SELECT data FROM sqlite_dbpage('aux1') WHERE pgno=123; +** +** This is an eponymous virtual table so it does not need to be created before +** use. The optional argument to the sqlite_dbpage() table name is the +** schema for the database file that is to be read. The default schema is +** "main". +** +** The data field of sqlite_dbpage table can be updated. The new +** value must be a BLOB which is the correct page size, otherwise the +** update fails. Rows may not be deleted or inserted. +*/ + +#include "sqliteInt.h" /* Requires access to internal data structures */ +#if (defined(SQLITE_ENABLE_DBPAGE_VTAB) || defined(SQLITE_TEST)) \ + && !defined(SQLITE_OMIT_VIRTUALTABLE) + +typedef struct DbpageTable DbpageTable; +typedef struct DbpageCursor DbpageCursor; + +struct DbpageCursor { + sqlite3_vtab_cursor base; /* Base class. Must be first */ + int pgno; /* Current page number */ + int mxPgno; /* Last page to visit on this scan */ +}; + +struct DbpageTable { + sqlite3_vtab base; /* Base class. Must be first */ + sqlite3 *db; /* The database */ + Pager *pPager; /* Pager being read/written */ + int iDb; /* Index of database to analyze */ + int szPage; /* Size of each page in bytes */ + int nPage; /* Number of pages in the file */ +}; + +/* +** Connect to or create a dbpagevfs virtual table. +*/ +static int dbpageConnect( + sqlite3 *db, + void *pAux, + int argc, const char *const*argv, + sqlite3_vtab **ppVtab, + char **pzErr +){ + DbpageTable *pTab = 0; + int rc = SQLITE_OK; + int iDb; + + if( argc>=4 ){ + Token nm; + sqlite3TokenInit(&nm, (char*)argv[3]); + iDb = sqlite3FindDb(db, &nm); + if( iDb<0 ){ + *pzErr = sqlite3_mprintf("no such schema: %s", argv[3]); + return SQLITE_ERROR; + } + }else{ + iDb = 0; + } + rc = sqlite3_declare_vtab(db, + "CREATE TABLE x(pgno INTEGER PRIMARY KEY, data BLOB, schema HIDDEN)"); + if( rc==SQLITE_OK ){ + pTab = (DbpageTable *)sqlite3_malloc64(sizeof(DbpageTable)); + if( pTab==0 ) rc = SQLITE_NOMEM_BKPT; + } + + assert( rc==SQLITE_OK || pTab==0 ); + if( rc==SQLITE_OK ){ + Btree *pBt = db->aDb[iDb].pBt; + memset(pTab, 0, sizeof(DbpageTable)); + pTab->db = db; + pTab->iDb = iDb; + pTab->pPager = pBt ? sqlite3BtreePager(pBt) : 0; + } + + *ppVtab = (sqlite3_vtab*)pTab; + return rc; +} + +/* +** Disconnect from or destroy a dbpagevfs virtual table. +*/ +static int dbpageDisconnect(sqlite3_vtab *pVtab){ + sqlite3_free(pVtab); + return SQLITE_OK; +} + +/* +** idxNum: +** +** 0 full table scan +** 1 pgno=?1 +*/ +static int dbpageBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ + int i; + pIdxInfo->estimatedCost = 1.0e6; /* Initial cost estimate */ + for(i=0; inConstraint; i++){ + struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i]; + if( p->usable && p->iColumn<=0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ ){ + pIdxInfo->estimatedRows = 1; + pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE; + pIdxInfo->estimatedCost = 1.0; + pIdxInfo->idxNum = 1; + pIdxInfo->aConstraintUsage[i].argvIndex = 1; + pIdxInfo->aConstraintUsage[i].omit = 1; + break; + } + } + if( pIdxInfo->nOrderBy>=1 + && pIdxInfo->aOrderBy[0].iColumn<=0 + && pIdxInfo->aOrderBy[0].desc==0 + ){ + pIdxInfo->orderByConsumed = 1; + } + return SQLITE_OK; +} + +/* +** Open a new dbpagevfs cursor. +*/ +static int dbpageOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ + DbpageCursor *pCsr; + + pCsr = (DbpageCursor *)sqlite3_malloc64(sizeof(DbpageCursor)); + if( pCsr==0 ){ + return SQLITE_NOMEM_BKPT; + }else{ + memset(pCsr, 0, sizeof(DbpageCursor)); + pCsr->base.pVtab = pVTab; + pCsr->pgno = -1; + } + + *ppCursor = (sqlite3_vtab_cursor *)pCsr; + return SQLITE_OK; +} + +/* +** Close a dbpagevfs cursor. +*/ +static int dbpageClose(sqlite3_vtab_cursor *pCursor){ + DbpageCursor *pCsr = (DbpageCursor *)pCursor; + sqlite3_free(pCsr); + return SQLITE_OK; +} + +/* +** Move a dbpagevfs cursor to the next entry in the file. +*/ +static int dbpageNext(sqlite3_vtab_cursor *pCursor){ + int rc = SQLITE_OK; + DbpageCursor *pCsr = (DbpageCursor *)pCursor; + pCsr->pgno++; + return rc; +} + +static int dbpageEof(sqlite3_vtab_cursor *pCursor){ + DbpageCursor *pCsr = (DbpageCursor *)pCursor; + return pCsr->pgno > pCsr->mxPgno; +} + +static int dbpageFilter( + sqlite3_vtab_cursor *pCursor, + int idxNum, const char *idxStr, + int argc, sqlite3_value **argv +){ + DbpageCursor *pCsr = (DbpageCursor *)pCursor; + DbpageTable *pTab = (DbpageTable *)pCursor->pVtab; + int rc = SQLITE_OK; + Btree *pBt = pTab->db->aDb[pTab->iDb].pBt; + + pTab->szPage = sqlite3BtreeGetPageSize(pBt); + pTab->nPage = sqlite3BtreeLastPage(pBt); + if( idxNum==1 ){ + pCsr->pgno = sqlite3_value_int(argv[0]); + if( pCsr->pgno<1 || pCsr->pgno>pTab->nPage ){ + pCsr->pgno = 1; + pCsr->mxPgno = 0; + }else{ + pCsr->mxPgno = pCsr->pgno; + } + }else{ + pCsr->pgno = 1; + pCsr->mxPgno = pTab->nPage; + } + return rc; +} + +static int dbpageColumn( + sqlite3_vtab_cursor *pCursor, + sqlite3_context *ctx, + int i +){ + DbpageCursor *pCsr = (DbpageCursor *)pCursor; + DbpageTable *pTab = (DbpageTable *)pCursor->pVtab; + int rc = SQLITE_OK; + switch( i ){ + case 0: { /* pgno */ + sqlite3_result_int(ctx, pCsr->pgno); + break; + } + case 1: { /* data */ + DbPage *pDbPage = 0; + rc = sqlite3PagerGet(pTab->pPager, pCsr->pgno, (DbPage**)&pDbPage, 0); + if( rc==SQLITE_OK ){ + sqlite3_result_blob(ctx, sqlite3PagerGetData(pDbPage), pTab->szPage, + SQLITE_TRANSIENT); + } + sqlite3PagerUnref(pDbPage); + break; + } + default: { /* schema */ + sqlite3 *db = sqlite3_context_db_handle(ctx); + sqlite3_result_text(ctx, db->aDb[pTab->iDb].zDbSName, -1, SQLITE_STATIC); + break; + } + } + return SQLITE_OK; +} + +static int dbpageRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ + DbpageCursor *pCsr = (DbpageCursor *)pCursor; + *pRowid = pCsr->pgno; + return SQLITE_OK; +} + +static int dbpageUpdate( + sqlite3_vtab *pVtab, + int argc, + sqlite3_value **argv, + sqlite_int64 *pRowid +){ + DbpageTable *pTab = (DbpageTable *)pVtab; + int pgno; + DbPage *pDbPage = 0; + int rc = SQLITE_OK; + char *zErr = 0; + + if( argc==1 ){ + zErr = "cannot delete"; + goto update_fail; + } + pgno = sqlite3_value_int(argv[0]); + if( pgno<1 || pgno>pTab->nPage ){ + zErr = "bad page number"; + goto update_fail; + } + if( sqlite3_value_int(argv[1])!=pgno ){ + zErr = "cannot insert"; + goto update_fail; + } + if( sqlite3_value_type(argv[3])!=SQLITE_BLOB + || sqlite3_value_bytes(argv[3])!=pTab->szPage + ){ + zErr = "bad page value"; + goto update_fail; + } + rc = sqlite3PagerGet(pTab->pPager, pgno, (DbPage**)&pDbPage, 0); + if( rc==SQLITE_OK ){ + rc = sqlite3PagerWrite(pDbPage); + if( rc==SQLITE_OK ){ + memcpy(sqlite3PagerGetData(pDbPage), + sqlite3_value_blob(argv[3]), + pTab->szPage); + } + } + sqlite3PagerUnref(pDbPage); + return rc; + +update_fail: + sqlite3_free(pVtab->zErrMsg); + pVtab->zErrMsg = sqlite3_mprintf("%s", zErr); + return SQLITE_ERROR; +} + +/* +** Invoke this routine to register the "dbpage" virtual table module +*/ +int sqlite3DbpageRegister(sqlite3 *db){ + static sqlite3_module dbpage_module = { + 0, /* iVersion */ + dbpageConnect, /* xCreate */ + dbpageConnect, /* xConnect */ + dbpageBestIndex, /* xBestIndex */ + dbpageDisconnect, /* xDisconnect */ + dbpageDisconnect, /* xDestroy */ + dbpageOpen, /* xOpen - open a cursor */ + dbpageClose, /* xClose - close a cursor */ + dbpageFilter, /* xFilter - configure scan constraints */ + dbpageNext, /* xNext - advance a cursor */ + dbpageEof, /* xEof - check for end of scan */ + dbpageColumn, /* xColumn - read data */ + dbpageRowid, /* xRowid - read data */ + dbpageUpdate, /* xUpdate */ + 0, /* xBegin */ + 0, /* xSync */ + 0, /* xCommit */ + 0, /* xRollback */ + 0, /* xFindMethod */ + 0, /* xRename */ + 0, /* xSavepoint */ + 0, /* xRelease */ + 0, /* xRollbackTo */ + }; + return sqlite3_create_module(db, "sqlite_dbpage", &dbpage_module, 0); +} +#elif defined(SQLITE_ENABLE_DBPAGE_VTAB) +int sqlite3DbpageRegister(sqlite3 *db){ return SQLITE_OK; } +#endif /* SQLITE_ENABLE_DBSTAT_VTAB */ Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -3051,10 +3051,16 @@ #ifdef SQLITE_ENABLE_RTREE if( !db->mallocFailed && rc==SQLITE_OK){ rc = sqlite3RtreeInit(db); } #endif + +#ifdef SQLITE_ENABLE_DBPAGE_VTAB + if( !db->mallocFailed && rc==SQLITE_OK){ + rc = sqlite3DbpageRegister(db); + } +#endif #ifdef SQLITE_ENABLE_DBSTAT_VTAB if( !db->mallocFailed && rc==SQLITE_OK){ rc = sqlite3DbstatRegister(db); } Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -4968,24 +4968,28 @@ { "number of views:", "SELECT count(*) FROM %s WHERE type='view'" }, { "schema size:", "SELECT total(length(sql)) FROM %s" }, }; - sqlite3_file *pFile = 0; int i; char *zSchemaTab; char *zDb = nArg>=2 ? azArg[1] : "main"; + sqlite3_stmt *pStmt = 0; unsigned char aHdr[100]; open_db(p, 0); if( p->db==0 ) return 1; - sqlite3_file_control(p->db, zDb, SQLITE_FCNTL_FILE_POINTER, &pFile); - if( pFile==0 || pFile->pMethods==0 || pFile->pMethods->xRead==0 ){ - return 1; - } - i = pFile->pMethods->xRead(pFile, aHdr, 100, 0); - if( i!=SQLITE_OK ){ + sqlite3_prepare_v2(p->db,"SELECT data FROM sqlite_dbpage(?1) WHERE pgno=1", + -1, &pStmt, 0); + sqlite3_bind_text(pStmt, 1, zDb, -1, SQLITE_STATIC); + if( sqlite3_step(pStmt)==SQLITE_ROW + && sqlite3_column_bytes(pStmt,0)>100 + ){ + memcpy(aHdr, sqlite3_column_blob(pStmt,0), 100); + sqlite3_finalize(pStmt); + }else{ raw_printf(stderr, "unable to read database header\n"); + sqlite3_finalize(pStmt); return 1; } i = get2byteInt(aHdr+16); if( i==1 ) i = 65536; utf8_printf(p->out, "%-20s %d\n", "database page size:", i); Index: src/shell.c.in ================================================================== --- src/shell.c.in +++ src/shell.c.in @@ -3608,24 +3608,28 @@ { "number of views:", "SELECT count(*) FROM %s WHERE type='view'" }, { "schema size:", "SELECT total(length(sql)) FROM %s" }, }; - sqlite3_file *pFile = 0; int i; char *zSchemaTab; char *zDb = nArg>=2 ? azArg[1] : "main"; + sqlite3_stmt *pStmt = 0; unsigned char aHdr[100]; open_db(p, 0); if( p->db==0 ) return 1; - sqlite3_file_control(p->db, zDb, SQLITE_FCNTL_FILE_POINTER, &pFile); - if( pFile==0 || pFile->pMethods==0 || pFile->pMethods->xRead==0 ){ - return 1; - } - i = pFile->pMethods->xRead(pFile, aHdr, 100, 0); - if( i!=SQLITE_OK ){ + sqlite3_prepare_v2(p->db,"SELECT data FROM sqlite_dbpage(?1) WHERE pgno=1", + -1, &pStmt, 0); + sqlite3_bind_text(pStmt, 1, zDb, -1, SQLITE_STATIC); + if( sqlite3_step(pStmt)==SQLITE_ROW + && sqlite3_column_bytes(pStmt,0)>100 + ){ + memcpy(aHdr, sqlite3_column_blob(pStmt,0), 100); + sqlite3_finalize(pStmt); + }else{ raw_printf(stderr, "unable to read database header\n"); + sqlite3_finalize(pStmt); return 1; } i = get2byteInt(aHdr+16); if( i==1 ) i = 65536; utf8_printf(p->out, "%-20s %d\n", "database page size:", i); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -4398,10 +4398,13 @@ #if SQLITE_MAX_WORKER_THREADS>0 int sqlite3ThreadCreate(SQLiteThread**,void*(*)(void*),void*); int sqlite3ThreadJoin(SQLiteThread*, void**); #endif +#if defined(SQLITE_ENABLE_DBPAGE_VTAB) || defined(SQLITE_TEST) +int sqlite3DbpageRegister(sqlite3*); +#endif #if defined(SQLITE_ENABLE_DBSTAT_VTAB) || defined(SQLITE_TEST) int sqlite3DbstatRegister(sqlite3*); #endif int sqlite3ExprVectorSize(Expr *pExpr); ADDED test/checkfreelist.test Index: test/checkfreelist.test ================================================================== --- /dev/null +++ test/checkfreelist.test @@ -0,0 +1,123 @@ +# 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 + ADDED test/dbpage.test Index: test/dbpage.test ================================================================== --- /dev/null +++ test/dbpage.test @@ -0,0 +1,69 @@ +# 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 sqlite_dbpage virtual table. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix dbpage + +ifcapable !vtab||!compound { + finish_test + return +} + +do_execsql_test 100 { + PRAGMA page_size=4096; + PRAGMA journal_mode=WAL; + CREATE TABLE t1(a,b); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) + INSERT INTO t1(a,b) SELECT x, printf('%d-x%.*c',x,x,'x') FROM c; + PRAGMA integrity_check; +} {wal ok} +do_execsql_test 110 { + SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno; +} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0D00000016'} +do_execsql_test 120 { + SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=2; +} {2 X'0500000001'} +do_execsql_test 130 { + SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=4; +} {4 X'0D00000016'} +do_execsql_test 140 { + SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=5; +} {} +do_execsql_test 150 { + SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=0; +} {} + +do_execsql_test 200 { + CREATE TEMP TABLE saved_content(x); + INSERT INTO saved_content(x) SELECT data FROM sqlite_dbpage WHERE pgno=4; + UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=4; +} {} +do_catchsql_test 210 { + PRAGMA integrity_check; +} {1 {database disk image is malformed}} +do_execsql_test 220 { + SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno; +} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0000000000'} +do_execsql_test 230 { + UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) WHERE pgno=4; +} {} +do_catchsql_test 230 { + PRAGMA integrity_check; +} {0 ok} + + + + +finish_test Index: tool/mksqlite3c.tcl ================================================================== --- tool/mksqlite3c.tcl +++ tool/mksqlite3c.tcl @@ -392,10 +392,11 @@ rtree.c icu.c fts3_icu.c sqlite3rbu.c dbstat.c + dbpage.c sqlite3session.c json1.c fts5.c stmt.c } {