/ Check-in [bdf2ec77]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Initial check-in of the "scrub.exe" utility program prototype. Not yet fully functional. In particular, no scrubbing is done.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | scrub-backup
Files: files | file ages | folders
SHA1: bdf2ec77d1542d4e9b68218f558710a3efc15823
User & Date: drh 2016-05-05 17:15:23
Context
2016-05-05
23:09
Finished implementation compiles, but untested. check-in: aeb88bdf user: drh tags: scrub-backup
17:15
Initial check-in of the "scrub.exe" utility program prototype. Not yet fully functional. In particular, no scrubbing is done. check-in: bdf2ec77 user: drh tags: scrub-backup
11:53
Renumber internal constants in the printf() implemention for a small performance improvement. check-in: 69d11447 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   584    584   sqlite3$(TEXE):	$(TOP)/src/shell.c sqlite3.c
   585    585   	$(LTLINK) $(READLINE_FLAGS) $(SHELL_OPT) -o $@ \
   586    586   		$(TOP)/src/shell.c sqlite3.c \
   587    587   		$(LIBREADLINE) $(TLIBS) -rpath "$(libdir)"
   588    588   
   589    589   sqldiff$(TEXE):	$(TOP)/tool/sqldiff.c sqlite3.c sqlite3.h
   590    590   	$(LTLINK) -o $@ $(TOP)/tool/sqldiff.c sqlite3.c $(TLIBS)
          591  +
          592  +scrub$(TEXE):	$(TOP)/ext/misc/scrub.c sqlite3.o
          593  +	$(LTLINK) -o $@ -I. -DSCRUB_STANDALONE \
          594  +		$(TOP)/ext/misc/scrub.c sqlite3.o $(TLIBS)
   591    595   
   592    596   srcck1$(BEXE):	$(TOP)/tool/srcck1.c
   593    597   	$(BCC) -o srcck1$(BEXE) $(TOP)/tool/srcck1.c
   594    598   
   595    599   sourcetest:	srcck1$(BEXE) sqlite3.c
   596    600   	./srcck1 sqlite3.c
   597    601   

Changes to Makefile.msc.

  1451   1451   	$(LTLINK) $(SHELL_COMPILE_OPTS) $(READLINE_FLAGS) $(TOP)\src\shell.c $(SHELL_CORE_SRC) \
  1452   1452   		/link $(SQLITE3EXEPDB) $(LDFLAGS) $(LTLINKOPTS) $(SHELL_LINK_OPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LIBREADLINE) $(LTLIBS) $(TLIBS)
  1453   1453   
  1454   1454   # <<mark>>
  1455   1455   sqldiff.exe:	$(TOP)\tool\sqldiff.c $(SQLITE3C) $(SQLITE3H)
  1456   1456   	$(LTLINK) $(NO_WARN) $(TOP)\tool\sqldiff.c $(SQLITE3C) /link $(LDFLAGS) $(LTLINKOPTS)
  1457   1457   
         1458  +scrub.exe:	$(TOP)\ext\misc\scrub.c $(SQLITE3C) $(SQLITE3H)
         1459  +	$(LTLINK) $(NO_WARN) $(TOP)\ext\misc\scrub.c $(SQLITE3C) /link $(LDFLAGS) $(LTLINKOPTS)
         1460  +
  1458   1461   srcck1.exe:	$(TOP)\tool\srcck1.c
  1459   1462   	$(BCC) $(NO_WARN) -Fe$@ $(TOP)\tool\srcck1.c
  1460   1463   
  1461   1464   sourcetest:	srcck1.exe sqlite3.c
  1462   1465   	srcck1.exe sqlite3.c
  1463   1466   
  1464   1467   fuzzershell.exe:	$(TOP)\tool\fuzzershell.c $(SQLITE3C) $(SQLITE3H)

Added ext/misc/scrub.c.

            1  +/*
            2  +** 2016-05-05
            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  +** This file implements a utility function (and a utility program) that
           14  +** makes a copy of an SQLite database while simultaneously zeroing out all
           15  +** deleted content.
           16  +**
           17  +** Normally (when PRAGMA secure_delete=OFF, which is the default) when SQLite
           18  +** deletes content, it does not overwrite the deleted content but rather marks
           19  +** the region of the file that held that content as being reusable.  This can
           20  +** cause deleted content to recoverable from the database file.  This stale
           21  +** content is removed by the VACUUM command, but VACUUM can be expensive for
           22  +** large databases.  When in PRAGMA secure_delete=ON mode, the deleted content
           23  +** is zeroed, but secure_delete=ON has overhead as well.
           24  +**
           25  +** This utility attempts to make a copy of a complete SQLite database where
           26  +** all of the deleted content is zeroed out in the copy, and it attempts to
           27  +** do so while being faster than running VACUUM.
           28  +**
           29  +** Usage:
           30  +**
           31  +**   int sqlite3_scrub_backup(
           32  +**       const char *zSourceFile,   // Source database filename
           33  +**       const char *zDestFile,     // Destination database filename
           34  +**       char **pzErrMsg            // Write error message here
           35  +**   );
           36  +**
           37  +** Simply call the API above specifying the filename of the source database
           38  +** and the name of the backup copy.  The source database must already exist
           39  +** and can be in active use. (A read lock is held during the backup.)  The
           40  +** destination file should not previously exist.  If the pzErrMsg parameter
           41  +** is non-NULL and if an error occurs, then an error message might be written
           42  +** into memory obtained from sqlite3_malloc() and *pzErrMsg made to point to
           43  +** that error message.  But if the error is an OOM, the error might not be
           44  +** reported.  The routine always returns non-zero if there is an error.
           45  +**
           46  +** If compiled with -DSCRUB_STANDALONE then a main() procedure is added and
           47  +** this file becomes a standalone program that can be run as follows:
           48  +**
           49  +**      ./sqlite3scrub SOURCE DEST
           50  +*/
           51  +#include "sqlite3.h"
           52  +#include <assert.h>
           53  +#include <stdio.h>
           54  +#include <stdlib.h>
           55  +#include <stdarg.h>
           56  +#include <string.h>
           57  +
           58  +typedef struct ScrubState ScrubState;
           59  +typedef unsigned char u8;
           60  +
           61  +/* State information for a scrub-and-backup operation */
           62  +struct ScrubState {
           63  +  const char *zSrcFile;    /* Name of the source file */
           64  +  const char *zDestFile;   /* Name of the destination file */
           65  +  int rcErr;               /* Error code */
           66  +  char *zErr;              /* Error message text */
           67  +  sqlite3 *dbSrc;          /* Source database connection */
           68  +  sqlite3_file *pSrc;      /* Source file handle */
           69  +  sqlite3 *dbDest;         /* Destination database connection */
           70  +  sqlite3_file *pDest;     /* Destination file handle */
           71  +  unsigned int szPage;     /* Page size */
           72  +  unsigned int nPage;      /* Number of pages */
           73  +  u8 *page1;               /* Content of page 1 */
           74  +};
           75  +
           76  +/* Store an error message */
           77  +static void scrubBackupErr(ScrubState *p, const char *zFormat, ...){
           78  +  va_list ap;
           79  +  sqlite3_free(p->zErr);
           80  +  va_start(ap, zFormat);
           81  +  p->zErr = sqlite3_vmprintf(zFormat, ap);
           82  +  va_end(ap);
           83  +  if( p->rcErr==0 ) p->rcErr = SQLITE_ERROR;
           84  +}
           85  +
           86  +/* Allocate memory to hold a single page of content */
           87  +static u8 *scrubBackupAllocPage(ScrubState *p){
           88  +  u8 *pPage;
           89  +  if( p->rcErr ) return 0;
           90  +  pPage = sqlite3_malloc( p->szPage );
           91  +  if( pPage==0 ) p->rcErr = SQLITE_NOMEM;
           92  +  return pPage;
           93  +}
           94  +
           95  +/* Read a page from the source database into memory.  Use the memory
           96  +** provided by pBuf if not NULL or allocate a new page if pBuf==NULL.
           97  +*/
           98  +static u8 *scrubBackupRead(ScrubState *p, int pgno, u8 *pBuf){
           99  +  int rc;
          100  +  sqlite3_int64 iOff;
          101  +  u8 *pOut = pBuf;
          102  +  if( p->rcErr ) return 0;
          103  +  if( pOut==0 ){
          104  +    pOut = scrubBackupAllocPage(p);
          105  +    if( pOut==0 ) return 0;
          106  +  }
          107  +  iOff = (pgno-1)*(sqlite3_int64)p->szPage;
          108  +  rc = p->pSrc->pMethods->xRead(p->pSrc, pOut, p->szPage, iOff);
          109  +  if( rc!=SQLITE_OK ){
          110  +    if( pBuf==0 ) sqlite3_free(pOut);
          111  +    pOut = 0;
          112  +    scrubBackupErr(p, "read failed for page %d", pgno);
          113  +    p->rcErr = SQLITE_IOERR;
          114  +  }
          115  +  return pOut;  
          116  +}
          117  +
          118  +/* Write a page to the destination database */
          119  +static void scrubBackupWrite(ScrubState *p, int pgno, u8 *pData){
          120  +  int rc;
          121  +  sqlite3_int64 iOff;
          122  +  if( p->rcErr ) return;
          123  +  iOff = (pgno-1)*(sqlite3_int64)p->szPage;
          124  +  rc = p->pDest->pMethods->xWrite(p->pDest, pData, p->szPage, iOff);
          125  +  if( rc!=SQLITE_OK ){
          126  +    scrubBackupErr(p, "write failed for page %d", pgno);
          127  +    p->rcErr = SQLITE_IOERR;
          128  +  }
          129  +}
          130  +
          131  +/* Prepare a statement against the "db" database. */
          132  +static sqlite3_stmt *scrubBackupPrepare(
          133  +  ScrubState *p,      /* Backup context */
          134  +  sqlite3 *db,        /* Database to prepare against */
          135  +  const char *zSql    /* SQL statement */
          136  +){
          137  +  sqlite3_stmt *pStmt;
          138  +  if( p->rcErr ) return 0;
          139  +  p->rcErr = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
          140  +  if( p->rcErr ){
          141  +    scrubBackupErr(p, "SQL error \"%s\" on \"%s\"",
          142  +                   sqlite3_errmsg(db), zSql);
          143  +    sqlite3_finalize(pStmt);
          144  +    return 0;
          145  +  }
          146  +  return pStmt;
          147  +}
          148  +
          149  +
          150  +/* Open the source database file */
          151  +static void scrubBackupOpenSrc(ScrubState *p){
          152  +  sqlite3_stmt *pStmt;
          153  +  int rc;
          154  +  /* Open the source database file */
          155  +  p->rcErr = sqlite3_open_v2(p->zSrcFile, &p->dbSrc,
          156  +                 SQLITE_OPEN_READONLY |
          157  +                 SQLITE_OPEN_URI | SQLITE_OPEN_PRIVATECACHE, 0);
          158  +  if( p->rcErr ){
          159  +    scrubBackupErr(p, "cannot open source database: %s",
          160  +                      sqlite3_errmsg(p->dbSrc));
          161  +    return;
          162  +  }
          163  +  p->rcErr = sqlite3_exec(p->dbSrc, "BEGIN", 0, 0, 0);
          164  +  if( p->rcErr ){
          165  +    scrubBackupErr(p,
          166  +       "cannot start a read transaction on the source database: %s",
          167  +       sqlite3_errmsg(p->dbSrc));
          168  +    return;
          169  +  }
          170  +  pStmt = scrubBackupPrepare(p, p->dbSrc, "PRAGMA page_size");
          171  +  if( pStmt==0 ) return;
          172  +  rc = sqlite3_step(pStmt);
          173  +  if( rc==SQLITE_ROW ){
          174  +    p->szPage = sqlite3_column_int(pStmt, 0);
          175  +  }else{
          176  +    scrubBackupErr(p, "unable to determine the page size");
          177  +  }
          178  +  sqlite3_finalize(pStmt);
          179  +  if( p->rcErr ) return;
          180  +  pStmt = scrubBackupPrepare(p, p->dbSrc, "PRAGMA page_count");
          181  +  if( pStmt==0 ) return;
          182  +  rc = sqlite3_step(pStmt);
          183  +  if( rc==SQLITE_ROW ){
          184  +    p->nPage = sqlite3_column_int(pStmt, 0);
          185  +  }else{
          186  +    scrubBackupErr(p, "unable to determine the size of the source database");
          187  +  }
          188  +  sqlite3_finalize(pStmt);
          189  +  sqlite3_file_control(p->dbSrc, "main", SQLITE_FCNTL_FILE_POINTER, &p->pSrc);
          190  +  if( p->pSrc==0 || p->pSrc->pMethods==0 ){
          191  +    scrubBackupErr(p, "cannot get the source file handle");
          192  +    p->rcErr = SQLITE_ERROR;
          193  +  }
          194  +}
          195  +
          196  +/* Create and open the destination file */
          197  +static void scrubBackupOpenDest(ScrubState *p){
          198  +  sqlite3_stmt *pStmt;
          199  +  int rc;
          200  +  char *zSql;
          201  +  if( p->rcErr ) return;
          202  +  p->rcErr = sqlite3_open_v2(p->zDestFile, &p->dbDest,
          203  +                 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
          204  +                 SQLITE_OPEN_URI | SQLITE_OPEN_PRIVATECACHE, 0);
          205  +  if( p->rcErr ){
          206  +    scrubBackupErr(p, "cannot open destination database: %s",
          207  +                      sqlite3_errmsg(p->dbDest));
          208  +    return;
          209  +  }
          210  +  zSql = sqlite3_mprintf("PRAGMA page_size(%u);", p->szPage);
          211  +  if( zSql==0 ){
          212  +    p->rcErr = SQLITE_NOMEM;
          213  +    return;
          214  +  }
          215  +  p->rcErr = sqlite3_exec(p->dbDest, zSql, 0, 0, 0);
          216  +  sqlite3_free(zSql);
          217  +  if( p->rcErr ){
          218  +    scrubBackupErr(p,
          219  +       "cannot set the page size on the destination database: %s",
          220  +       sqlite3_errmsg(p->dbDest));
          221  +    return;
          222  +  }
          223  +  sqlite3_exec(p->dbDest, "PRAGMA journal_mode=OFF;", 0, 0, 0);
          224  +  p->rcErr = sqlite3_exec(p->dbDest, "BEGIN EXCLUSIVE;", 0, 0, 0);
          225  +  if( p->rcErr ){
          226  +    scrubBackupErr(p,
          227  +       "cannot start a write transaction on the destination database: %s",
          228  +       sqlite3_errmsg(p->dbDest));
          229  +    return;
          230  +  }
          231  +  pStmt = scrubBackupPrepare(p, p->dbDest, "PRAGMA page_count;");
          232  +  if( pStmt==0 ) return;
          233  +  rc = sqlite3_step(pStmt);
          234  +  if( rc!=SQLITE_ROW ){
          235  +    scrubBackupErr(p, "cannot measure the size of the destination");
          236  +  }else if( sqlite3_column_int(pStmt, 0)>1 ){
          237  +    scrubBackupErr(p, "destination database is not empty - holds %d pages",
          238  +                   sqlite3_column_int(pStmt, 0));
          239  +  }
          240  +  sqlite3_finalize(pStmt);
          241  +  sqlite3_file_control(p->dbDest, "main", SQLITE_FCNTL_FILE_POINTER, &p->pDest);
          242  +  if( p->pDest==0 || p->pDest->pMethods==0 ){
          243  +    scrubBackupErr(p, "cannot get the destination file handle");
          244  +    p->rcErr = SQLITE_ERROR;
          245  +  }
          246  +}
          247  +
          248  +int sqlite3_scrub_backup(
          249  +  const char *zSrcFile,    /* Source file */
          250  +  const char *zDestFile,   /* Destination file */
          251  +  char **pzErr             /* Write error here if non-NULL */
          252  +){
          253  +  ScrubState s;
          254  +  unsigned int i;
          255  +  u8 *pBuf = 0;
          256  +  u8 *pData;
          257  +
          258  +  memset(&s, 0, sizeof(s));
          259  +  s.zSrcFile = zSrcFile;
          260  +  s.zDestFile = zDestFile;
          261  +
          262  +  scrubBackupOpenSrc(&s);
          263  +  scrubBackupOpenDest(&s);
          264  +  pBuf = scrubBackupAllocPage(&s);
          265  +
          266  +  for(i=1; s.rcErr==0 && i<=s.nPage; i++){
          267  +    pData = scrubBackupRead(&s, i, pBuf);
          268  +    scrubBackupWrite(&s, i, pData);
          269  +  }
          270  +
          271  +  /* Close the destination database without closing the transaction. If we
          272  +  ** commit, page zero will be overwritten. */
          273  +  sqlite3_close(s.dbDest);
          274  +
          275  +  sqlite3_close(s.dbSrc);
          276  +  sqlite3_free(s.page1);
          277  +  if( pzErr ){
          278  +    *pzErr = s.zErr;
          279  +  }else{
          280  +    sqlite3_free(s.zErr);
          281  +  }
          282  +  return s.rcErr;
          283  +}   
          284  +
          285  +#ifdef SCRUB_STANDALONE
          286  +/* The main() routine when this utility is run as a stand-alone program */
          287  +int main(int argc, char **argv){
          288  +  char *zErr = 0;
          289  +  int rc;
          290  +  if( argc!=3 ){
          291  +    fprintf(stderr,"Usage: %s SOURCE DESTINATION\n", argv[0]);
          292  +    exit(1);
          293  +  }
          294  +  rc = sqlite3_scrub_backup(argv[1], argv[2], &zErr);
          295  +  if( rc==SQLITE_NOMEM ){
          296  +    fprintf(stderr, "%s: out of memory\n", argv[0]);
          297  +    exit(1);
          298  +  }
          299  +  if( zErr ){
          300  +    fprintf(stderr, "%s: %s\n", argv[0], zErr);
          301  +    sqlite3_free(zErr);
          302  +    exit(1);
          303  +  }
          304  +  return 0;
          305  +}
          306  +#endif

Changes to main.mk.

   484    484   	$(TCCX) $(READLINE_FLAGS) -o sqlite3$(EXE) $(SHELL_OPT) \
   485    485   		$(TOP)/src/shell.c libsqlite3.a $(LIBREADLINE) $(TLIBS) $(THREADLIB)
   486    486   
   487    487   sqldiff$(EXE):	$(TOP)/tool/sqldiff.c sqlite3.c sqlite3.h
   488    488   	$(TCCX) -o sqldiff$(EXE) -DSQLITE_THREADSAFE=0 \
   489    489   		$(TOP)/tool/sqldiff.c sqlite3.c $(TLIBS) $(THREADLIB)
   490    490   
          491  +scrub$(EXE):	$(TOP)/ext/misc/scrub.c sqlite3.o
          492  +	$(TCC) -I. -DSCRUB_STANDALONE -o scrub$(EXE) $(TOP)/ext/misc/scrub.c sqlite3.o $(THREADLIB)
          493  +
   491    494   srcck1$(EXE):	$(TOP)/tool/srcck1.c
   492    495   	$(BCC) -o srcck1$(EXE) $(TOP)/tool/srcck1.c
   493    496   
   494    497   sourcetest:	srcck1$(EXE) sqlite3.c
   495    498   	./srcck1 sqlite3.c
   496    499   
   497    500   fuzzershell$(EXE):	$(TOP)/tool/fuzzershell.c sqlite3.c sqlite3.h