/ Check-in [dfdebd12]
Login

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

Overview
Comment:Create the new ext/repair folder and move checkfreelist.c there. Remove checkfreelist.c from the command-line shell (undoing check-in [48418f2e]).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dbpage
Files: files | file ages | folders
SHA3-256: dfdebd12bfc80b91d234ab328cb6106d5d37ccb79b58e36e556c1a8af640a4ab
User & Date: drh 2017-10-12 19:50:28
Context
2017-10-12
20:37
Add the sqlite_dbpage virtual table (enabled using SQLITE_ENABLE_DBPAGE_VTAB). Make that virtual table and dbstat available to the command-line shell. check-in: eaeeb09d user: drh tags: trunk
19:50
Create the new ext/repair folder and move checkfreelist.c there. Remove checkfreelist.c from the command-line shell (undoing check-in [48418f2e]). Closed-Leaf check-in: dfdebd12 user: drh tags: dbpage
01:24
Merge fixes from the 3.21 branch. check-in: 18d4654f user: drh tags: dbpage
2017-10-11
18:26
Add the checkfreelist extension to the command-line shell. check-in: 48418f2e user: drh tags: dbpage
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/repair/README.md.

            1  +This folder contains extensions and utility programs intended to analyze
            2  +live database files, detect problems, and possibly fix them.
            3  +
            4  +As SQLite is being used on larger and larger databases, database sizes
            5  +are growing into the terabyte range.  At that size, hardware malfunctions
            6  +and/or cosmic rays will occasionally corrupt a database file.  Detecting 
            7  +problems and fixing errors a terabyte-sized databases can take hours or days,
            8  +and it is undesirable to take applications that depend on the databases 
            9  +off-line for such a long time.
           10  +The utilities in the folder are intended to provide mechanisms for
           11  +detecting and fixing problems in large databases while those databases
           12  +are in active use.
           13  +
           14  +The utilities and extensions in this folder are experimental and under
           15  +active development at the time of this writing (2017-10-12).  If and when
           16  +they stabilize, this README will be updated to reflect that fact.

Name change from ext/misc/checkfreelist.c to ext/repair/checkfreelist.c.


Changes to src/shell.c.

  2152   2152   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2153   2153     rc = sqlite3CompletionVtabInit(db);
  2154   2154   #endif
  2155   2155     return rc;
  2156   2156   }
  2157   2157   
  2158   2158   /************************* End ../ext/misc/completion.c ********************/
  2159         -/************************* Begin ../ext/misc/checkfreelist.c ******************/
  2160         -/*
  2161         -** 2017 October 11
  2162         -**
  2163         -** The author disclaims copyright to this source code.  In place of
  2164         -** a legal notice, here is a blessing:
  2165         -**
  2166         -**    May you do good and not evil.
  2167         -**    May you find forgiveness for yourself and forgive others.
  2168         -**    May you share freely, never taking more than you give.
  2169         -**
  2170         -*************************************************************************
  2171         -**
  2172         -** This module exports a single C function:
  2173         -**
  2174         -**   int sqlite3_check_freelist(sqlite3 *db, const char *zDb);
  2175         -**
  2176         -** This function checks the free-list in database zDb (one of "main", 
  2177         -** "temp", etc.) and reports any errors by invoking the sqlite3_log()
  2178         -** function. It returns SQLITE_OK if successful, or an SQLite error
  2179         -** code otherwise. It is not an error if the free-list is corrupted but
  2180         -** no IO or OOM errors occur.
  2181         -**
  2182         -** If this file is compiled and loaded as an SQLite loadable extension,
  2183         -** it adds an SQL function "checkfreelist" to the database handle, to
  2184         -** be invoked as follows:
  2185         -**
  2186         -**   SELECT checkfreelist(<database-name>);
  2187         -**
  2188         -** This function performs the same checks as sqlite3_check_freelist(),
  2189         -** except that it returns all error messages as a single text value,
  2190         -** separated by newline characters. If the freelist is not corrupted
  2191         -** in any way, an empty string is returned.
  2192         -**
  2193         -** To compile this module for use as an SQLite loadable extension:
  2194         -**
  2195         -**   gcc -Os -fPIC -shared checkfreelist.c -o checkfreelist.so
  2196         -*/
  2197         -
  2198         -SQLITE_EXTENSION_INIT1
  2199         -
  2200         -#ifndef SQLITE_AMALGAMATION
  2201         -# include <string.h>
  2202         -# include <stdio.h>
  2203         -# include <stdlib.h>
  2204         -# include <assert.h>
  2205         -# define ALWAYS(X)  1
  2206         -# define NEVER(X)   0
  2207         -  typedef unsigned char u8;
  2208         -  typedef unsigned short u16;
  2209         -  typedef unsigned int u32;
  2210         -#define get4byte(x) (        \
  2211         -    ((u32)((x)[0])<<24) +    \
  2212         -    ((u32)((x)[1])<<16) +    \
  2213         -    ((u32)((x)[2])<<8) +     \
  2214         -    ((u32)((x)[3]))          \
  2215         -)
  2216         -#endif
  2217         -
  2218         -/*
  2219         -** Execute a single PRAGMA statement and return the integer value returned
  2220         -** via output parameter (*pnOut).
  2221         -**
  2222         -** The SQL statement passed as the third argument should be a printf-style
  2223         -** format string containing a single "%s" which will be replace by the
  2224         -** value passed as the second argument. e.g.
  2225         -**
  2226         -**   sqlGetInteger(db, "main", "PRAGMA %s.page_count", pnOut)
  2227         -**
  2228         -** executes "PRAGMA main.page_count" and stores the results in (*pnOut).
  2229         -*/
  2230         -static int sqlGetInteger(
  2231         -  sqlite3 *db,                    /* Database handle */
  2232         -  const char *zDb,                /* Database name ("main", "temp" etc.) */
  2233         -  const char *zFmt,               /* SQL statement format */
  2234         -  u32 *pnOut                      /* OUT: Integer value */
  2235         -){
  2236         -  int rc, rc2;
  2237         -  char *zSql;
  2238         -  sqlite3_stmt *pStmt = 0;
  2239         -  int bOk = 0;
  2240         -
  2241         -  zSql = sqlite3_mprintf(zFmt, zDb);
  2242         -  if( zSql==0 ){
  2243         -    rc = SQLITE_NOMEM;
  2244         -  }else{
  2245         -    rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  2246         -    sqlite3_free(zSql);
  2247         -  }
  2248         -
  2249         -  if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
  2250         -    *pnOut = (u32)sqlite3_column_int(pStmt, 0);
  2251         -    bOk = 1;
  2252         -  }
  2253         -
  2254         -  rc2 = sqlite3_finalize(pStmt);
  2255         -  if( rc==SQLITE_OK ) rc = rc2;
  2256         -  if( rc==SQLITE_OK && bOk==0 ) rc = SQLITE_ERROR;
  2257         -  return rc;
  2258         -}
  2259         -
  2260         -/*
  2261         -** Argument zFmt must be a printf-style format string and must be 
  2262         -** followed by its required arguments. If argument pzOut is NULL, 
  2263         -** then the results of printf()ing the format string are passed to
  2264         -** sqlite3_log(). Otherwise, they are appended to the string
  2265         -** at (*pzOut).
  2266         -*/
  2267         -static int checkFreelistError(char **pzOut, const char *zFmt, ...){
  2268         -  int rc = SQLITE_OK;
  2269         -  char *zErr = 0;
  2270         -  va_list ap;
  2271         -
  2272         -  va_start(ap, zFmt);
  2273         -  zErr = sqlite3_vmprintf(zFmt, ap);
  2274         -  if( zErr==0 ){
  2275         -    rc = SQLITE_NOMEM;
  2276         -  }else{
  2277         -    if( pzOut ){
  2278         -      *pzOut = sqlite3_mprintf("%s%z%s", *pzOut?"\n":"", *pzOut, zErr);
  2279         -      if( *pzOut==0 ) rc = SQLITE_NOMEM;
  2280         -    }else{
  2281         -      sqlite3_log(SQLITE_ERROR, "checkfreelist: %s", zErr);
  2282         -    }
  2283         -    sqlite3_free(zErr);
  2284         -  }
  2285         -  va_end(ap);
  2286         -  return rc;
  2287         -}
  2288         -
  2289         -static int checkFreelist(
  2290         -  sqlite3 *db, 
  2291         -  const char *zDb,
  2292         -  char **pzOut
  2293         -){
  2294         -  /* This query returns one row for each page on the free list. Each row has
  2295         -  ** two columns - the page number and page content.  */
  2296         -  const char *zTrunk = 
  2297         -    "WITH freelist_trunk(i, d, n) AS ("
  2298         -      "SELECT 1, NULL, sqlite_readint32(data, 32) "
  2299         -      "FROM sqlite_dbpage(:1) WHERE pgno=1 "
  2300         -        "UNION ALL "
  2301         -      "SELECT n, data, sqlite_readint32(data) "
  2302         -      "FROM freelist_trunk, sqlite_dbpage(:1) WHERE pgno=n "
  2303         -    ")"
  2304         -    "SELECT i, d FROM freelist_trunk WHERE i!=1;";
  2305         -
  2306         -  int rc, rc2;                    /* Return code */
  2307         -  sqlite3_stmt *pTrunk = 0;       /* Compilation of zTrunk */
  2308         -  u32 nPage = 0;                  /* Number of pages in db */
  2309         -  u32 nExpected = 0;              /* Expected number of free pages */
  2310         -  u32 nFree = 0;                  /* Number of pages on free list */
  2311         -
  2312         -  if( zDb==0 ) zDb = "main";
  2313         -
  2314         -  if( (rc = sqlGetInteger(db, zDb, "PRAGMA %s.page_count", &nPage))
  2315         -   || (rc = sqlGetInteger(db, zDb, "PRAGMA %s.freelist_count", &nExpected))
  2316         -  ){
  2317         -    return rc;
  2318         -  }
  2319         -
  2320         -  rc = sqlite3_prepare_v2(db, zTrunk, -1, &pTrunk, 0);
  2321         -  if( rc!=SQLITE_OK ) return rc;
  2322         -  sqlite3_bind_text(pTrunk, 1, zDb, -1, SQLITE_STATIC);
  2323         -  while( rc==SQLITE_OK && sqlite3_step(pTrunk)==SQLITE_ROW ){
  2324         -    u32 i;
  2325         -    u32 iTrunk = (u32)sqlite3_column_int(pTrunk, 0);
  2326         -    const u8 *aData = (const u8*)sqlite3_column_blob(pTrunk, 1);
  2327         -    int nData = sqlite3_column_bytes(pTrunk, 1);
  2328         -    u32 iNext = get4byte(&aData[0]);
  2329         -    u32 nLeaf = get4byte(&aData[4]);
  2330         -
  2331         -    if( nLeaf>((nData/4)-2-6) ){
  2332         -      rc = checkFreelistError(pzOut, 
  2333         -          "leaf count out of range (%d) on trunk page %d", 
  2334         -          (int)nLeaf, (int)iTrunk
  2335         -      );
  2336         -      nLeaf = (nData/4) - 2 - 6;
  2337         -    }
  2338         -
  2339         -    nFree += 1+nLeaf;
  2340         -    if( iNext>nPage ){
  2341         -      rc = checkFreelistError(pzOut, 
  2342         -          "trunk page %d is out of range", (int)iNext
  2343         -      );
  2344         -    }
  2345         -
  2346         -    for(i=0; rc==SQLITE_OK && i<nLeaf; i++){
  2347         -      u32 iLeaf = get4byte(&aData[8 + 4*i]);
  2348         -      if( iLeaf==0 || iLeaf>nPage ){
  2349         -        rc = checkFreelistError(pzOut,
  2350         -            "leaf page %d is out of range (child %d of trunk page %d)", 
  2351         -            (int)iLeaf, (int)i, (int)iTrunk
  2352         -        );
  2353         -      }
  2354         -    }
  2355         -  }
  2356         -
  2357         -  if( rc==SQLITE_OK && nFree!=nExpected ){
  2358         -    rc = checkFreelistError(pzOut,
  2359         -        "free-list count mismatch: actual=%d header=%d", 
  2360         -        (int)nFree, (int)nExpected
  2361         -    );
  2362         -  }
  2363         -
  2364         -  rc2 = sqlite3_finalize(pTrunk);
  2365         -  if( rc==SQLITE_OK ) rc = rc2;
  2366         -  return rc;
  2367         -}
  2368         -
  2369         -int sqlite3_check_freelist(sqlite3 *db, const char *zDb){
  2370         -  return checkFreelist(db, zDb, 0);
  2371         -}
  2372         -
  2373         -static void checkfreelist_function(
  2374         -  sqlite3_context *pCtx,
  2375         -  int nArg,
  2376         -  sqlite3_value **apArg
  2377         -){
  2378         -  const char *zDb;
  2379         -  int rc;
  2380         -  char *zOut = 0;
  2381         -  sqlite3 *db = sqlite3_context_db_handle(pCtx);
  2382         -
  2383         -  assert( nArg==1 );
  2384         -  zDb = (const char*)sqlite3_value_text(apArg[0]);
  2385         -  rc = checkFreelist(db, zDb, &zOut);
  2386         -  if( rc==SQLITE_OK ){
  2387         -    sqlite3_result_text(pCtx, zOut?zOut:"ok", -1, SQLITE_TRANSIENT);
  2388         -  }else{
  2389         -    sqlite3_result_error_code(pCtx, rc);
  2390         -  }
  2391         -
  2392         -  sqlite3_free(zOut);
  2393         -}
  2394         -
  2395         -/*
  2396         -** An SQL function invoked as follows:
  2397         -**
  2398         -**   sqlite_readint32(BLOB)           -- Decode 32-bit integer from start of blob
  2399         -*/
  2400         -static void readint_function(
  2401         -  sqlite3_context *pCtx,
  2402         -  int nArg,
  2403         -  sqlite3_value **apArg
  2404         -){
  2405         -  const u8 *zBlob;
  2406         -  int nBlob;
  2407         -  int iOff = 0;
  2408         -  u32 iRet = 0;
  2409         -
  2410         -  if( nArg!=1 && nArg!=2 ){
  2411         -    sqlite3_result_error(
  2412         -        pCtx, "wrong number of arguments to function sqlite_readint32()", -1
  2413         -    );
  2414         -    return;
  2415         -  }
  2416         -  if( nArg==2 ){
  2417         -    iOff = sqlite3_value_int(apArg[1]);
  2418         -  }
  2419         -
  2420         -  zBlob = sqlite3_value_blob(apArg[0]);
  2421         -  nBlob = sqlite3_value_bytes(apArg[0]);
  2422         -
  2423         -  if( nBlob>=(iOff+4) ){
  2424         -    iRet = get4byte(&zBlob[iOff]);
  2425         -  }
  2426         -
  2427         -  sqlite3_result_int64(pCtx, (sqlite3_int64)iRet);
  2428         -}
  2429         -
  2430         -/*
  2431         -** Register the SQL functions.
  2432         -*/
  2433         -static int cflRegister(sqlite3 *db){
  2434         -  int rc = sqlite3_create_function(
  2435         -      db, "sqlite_readint32", -1, SQLITE_UTF8, 0, readint_function, 0, 0
  2436         -  );
  2437         -  if( rc!=SQLITE_OK ) return rc;
  2438         -  rc = sqlite3_create_function(
  2439         -      db, "checkfreelist", 1, SQLITE_UTF8, 0, checkfreelist_function, 0, 0
  2440         -  );
  2441         -  return rc;
  2442         -}
  2443         -
  2444         -/*
  2445         -** Extension load function.
  2446         -*/
  2447         -#ifdef _WIN32
  2448         -
  2449         -#endif
  2450         -int sqlite3_checkfreelist_init(
  2451         -  sqlite3 *db, 
  2452         -  char **pzErrMsg, 
  2453         -  const sqlite3_api_routines *pApi
  2454         -){
  2455         -  SQLITE_EXTENSION_INIT2(pApi);
  2456         -  return cflRegister(db);
  2457         -}
  2458         -
  2459         -/************************* End ../ext/misc/checkfreelist.c ********************/
  2460   2159   
  2461   2160   #if defined(SQLITE_ENABLE_SESSION)
  2462   2161   /*
  2463   2162   ** State information for a single open session
  2464   2163   */
  2465   2164   typedef struct OpenSession OpenSession;
  2466   2165   struct OpenSession {
................................................................................
  4543   4242       }
  4544   4243   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  4545   4244       sqlite3_enable_load_extension(p->db, 1);
  4546   4245   #endif
  4547   4246       sqlite3_fileio_init(p->db, 0, 0);
  4548   4247       sqlite3_shathree_init(p->db, 0, 0);
  4549   4248       sqlite3_completion_init(p->db, 0, 0);
  4550         -    sqlite3_checkfreelist_init(p->db, 0, 0);
  4551   4249       sqlite3_create_function(p->db, "shell_add_schema", 2, SQLITE_UTF8, 0,
  4552   4250                               shellAddSchemaName, 0, 0);
  4553   4251     }
  4554   4252   }
  4555   4253   
  4556   4254   #if HAVE_READLINE || HAVE_EDITLINE
  4557   4255   /*

Changes to src/shell.c.in.

   792    792   */
   793    793   #define SQLITE_EXTENSION_INIT1
   794    794   #define SQLITE_EXTENSION_INIT2(X) (void)(X)
   795    795   
   796    796   INCLUDE ../ext/misc/shathree.c
   797    797   INCLUDE ../ext/misc/fileio.c
   798    798   INCLUDE ../ext/misc/completion.c
   799         -INCLUDE ../ext/misc/checkfreelist.c
   800    799   
   801    800   #if defined(SQLITE_ENABLE_SESSION)
   802    801   /*
   803    802   ** State information for a single open session
   804    803   */
   805    804   typedef struct OpenSession OpenSession;
   806    805   struct OpenSession {
................................................................................
  2883   2882       }
  2884   2883   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  2885   2884       sqlite3_enable_load_extension(p->db, 1);
  2886   2885   #endif
  2887   2886       sqlite3_fileio_init(p->db, 0, 0);
  2888   2887       sqlite3_shathree_init(p->db, 0, 0);
  2889   2888       sqlite3_completion_init(p->db, 0, 0);
  2890         -    sqlite3_checkfreelist_init(p->db, 0, 0);
  2891   2889       sqlite3_create_function(p->db, "shell_add_schema", 2, SQLITE_UTF8, 0,
  2892   2890                               shellAddSchemaName, 0, 0);
  2893   2891     }
  2894   2892   }
  2895   2893   
  2896   2894   #if HAVE_READLINE || HAVE_EDITLINE
  2897   2895   /*