/ Check-in [74ffea76]
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:Updates to ensure the values of PRAGMA settings like "page_size", "auto_vacuum", "user_version" and "application_id" are not lost when a database is RBU vacuumed.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rbu-vacuum
Files: files | file ages | folders
SHA1: 74ffea76b8c9ddd7699ce6ca77dce005bd7922ac
User & Date: dan 2016-04-19 19:27:09
Context
2016-04-20
17:47
Add fault injection tests for rbu vacuum. Fix some problems revealed by the same. check-in: f042fdd1 user: dan tags: rbu-vacuum
2016-04-19
19:27
Updates to ensure the values of PRAGMA settings like "page_size", "auto_vacuum", "user_version" and "application_id" are not lost when a database is RBU vacuumed. check-in: 74ffea76 user: dan tags: rbu-vacuum
17:11
When an RBU vacuum is started on a db identified using a URI filename, pass the same URI parameters when creating the new version of the db. This ensures that RBU vacuum works with password protected databases. check-in: ca021ba8 user: dan tags: rbu-vacuum
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/rbu/rbuvacuum.test.

170
171
172
173
174
175
176



































































177
178
179
180
181
182
183
...
225
226
227
228
229
230
231
232
233
234
235
236
237
    DELETE FROM t4 WHERE a='g';
  }
  do_rbu_vacuum_test 1.6.5 $step
  do_execsql_test 1.6.6 {
    SELECT * FROM t4 ORDER BY b
  } {a b c d e f j k l}




































































}

#-------------------------------------------------------------------------
# Test some error cases:
#
#   2.1.* the db being vacuumed being in wal mode already.
#   2.2.* database modified mid vacuum.
................................................................................
  do_test 2.2.$i.1 {
    sqlite3rbu_vacuum rbu test.db state.db 
    rbu step
  } {SQLITE_BUSY}
  do_test 2.2.$i.2 {
    list [catch { rbu close } msg] $msg
  } {1 {SQLITE_BUSY - database modified during rbu vacuum}}

}

catch { db close }
finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







<





170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
...
292
293
294
295
296
297
298

299
300
301
302
303
    DELETE FROM t4 WHERE a='g';
  }
  do_rbu_vacuum_test 1.6.5 $step
  do_execsql_test 1.6.6 {
    SELECT * FROM t4 ORDER BY b
  } {a b c d e f j k l}

  reset_db
  do_execsql_test 1.7.0 {
    CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    INSERT INTO t1 VALUES(NULL, 'one');
    INSERT INTO t1 VALUES(NULL, 'two');
    DELETE FROM t1 WHERE a=2;
    INSERT INTO t1 VALUES(NULL, 'three');
    INSERT INTO t1 VALUES(NULL, 'four');
    DELETE FROM t1 WHERE a=4;
    INSERT INTO t1 VALUES(NULL, 'five');
    INSERT INTO t1 VALUES(NULL, 'six');
    DELETE FROM t1 WHERE a=6;
    SELECT * FROM t1;
  } {1 one 3 three 5 five}
  do_rbu_vacuum_test 1.7.1 $step
  do_execsql_test 1.7.2 {
    INSERT INTO t1 VALUES(NULL, 'seven');
    SELECT * FROM t1;
  } {1 one 3 three 5 five 7 seven}

  reset_db
  do_execsql_test 1.8.0 {
    CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    CREATE INDEX i1 ON t1(b);
    INSERT INTO t1 VALUES(NULL, 'one');
    INSERT INTO t1 VALUES(NULL, 'two');
    INSERT INTO t1 VALUES(NULL, 'three');
    INSERT INTO t1 VALUES(NULL, 'four');
    INSERT INTO t1 VALUES(NULL, 'five');
    INSERT INTO t1 VALUES(NULL, 'six');
    ANALYZE;
    SELECT * FROM sqlite_stat1;
  } {t1 i1 {6 1}}
  do_rbu_vacuum_test 1.8.1 $step
  do_execsql_test 1.7.2 {
    SELECT * FROM sqlite_stat1;
  } {t1 i1 {6 1}}

  reset_db
  do_execsql_test 1.9.0 {
    PRAGMA page_size = 8192;
    PRAGMA auto_vacuum = 2;
    PRAGMA user_version = 412;
    PRAGMA application_id = 413;

    CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    CREATE INDEX i1 ON t1(b);
    INSERT INTO t1 VALUES(NULL, 'one');
    INSERT INTO t1 VALUES(NULL, 'two');
    INSERT INTO t1 VALUES(NULL, 'three');
    INSERT INTO t1 VALUES(NULL, 'four');
    INSERT INTO t1 VALUES(NULL, 'five');
    INSERT INTO t1 VALUES(NULL, 'six');

    PRAGMA main.page_size;
    PRAGMA main.auto_vacuum;
    PRAGMA main.user_version;
    PRAGMA main.application_id;
  } {8192 2 412 413}

  do_rbu_vacuum_test 1.9.1 $step
  do_execsql_test 1.9.2 {
    PRAGMA main.page_size;
    PRAGMA main.auto_vacuum;
    PRAGMA main.user_version;
    PRAGMA main.application_id;
  } {8192 2 412 413}
}

#-------------------------------------------------------------------------
# Test some error cases:
#
#   2.1.* the db being vacuumed being in wal mode already.
#   2.2.* database modified mid vacuum.
................................................................................
  do_test 2.2.$i.1 {
    sqlite3rbu_vacuum rbu test.db state.db 
    rbu step
  } {SQLITE_BUSY}
  do_test 2.2.$i.2 {
    list [catch { rbu close } msg] $msg
  } {1 {SQLITE_BUSY - database modified during rbu vacuum}}

}

catch { db close }
finish_test

Changes to ext/rbu/sqlite3rbu.c.

3328
3329
3330
3331
3332
3333
3334
































3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346


3347
3348

3349
3350


3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
....
3465
3466
3467
3468
3469
3470
3471





3472
3473
3474
3475
3476
3477
3478
....
3487
3488
3489
3490
3491
3492
3493


3494
3495
3496
3497
3498
3499
3500
....
3850
3851
3852
3853
3854
3855
3856

3857
3858
3859
3860
3861

3862
3863
3864
3865
3866
3867
3868
3869
          p->nPhaseOneStep = sqlite3_column_int64(pStmt, 0);
        }
        p->rc = sqlite3_finalize(pStmt);
      }
    }
  }
}

































/*
** The RBU handle passed as the only argument has just been opened and 
** the state database is empty. If this RBU handle was opened for an
** RBU vacuum operation, create the schema in the target db.
*/
static void rbuCreateTargetSchema(sqlite3rbu *p){
  sqlite3_stmt *pSql = 0;
  sqlite3_stmt *pInsert = 0;

  assert( rbuIsVacuum(p) );



  p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, 
    "SELECT sql FROM sqlite_master WHERE sql!='' AND rootpage!=0"

    " ORDER BY type DESC"
  );


  while( p->rc==SQLITE_OK && sqlite3_step(pSql)==SQLITE_ROW ){
    const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
    p->rc = sqlite3_exec(p->dbMain, zSql, 0, 0, &p->zErrmsg);
  }
  rbuFinalize(p, pSql);
  if( p->rc!=SQLITE_OK ) return;

  p->rc = sqlite3_exec(p->dbMain, "PRAGMA writable_schema=1", 0,0, &p->zErrmsg);

  if( p->rc==SQLITE_OK ){
    p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, 
        "SELECT * FROM sqlite_master WHERE rootpage=0 OR rootpage IS NULL" 
    );
  }

  if( p->rc==SQLITE_OK ){
................................................................................
        );
      }
    }

    if( p->rc==SQLITE_OK ){
      if( p->eStage==RBU_STAGE_OAL ){
        sqlite3 *db = p->dbMain;






        /* Open transactions both databases. The *-oal file is opened or
        ** created at this point. */
        p->rc = sqlite3_exec(db, "BEGIN IMMEDIATE", 0, 0, &p->zErrmsg);
        if( p->rc==SQLITE_OK ){
          p->rc = sqlite3_exec(p->dbRbu, "BEGIN", 0, 0, &p->zErrmsg);
        }
................................................................................
          }
        }

        /* If this is an RBU vacuum operation and the state table was empty
        ** when this handle was opened, create the target database schema. */
        if( pState->eStage==0 && rbuIsVacuum(p) ){
          rbuCreateTargetSchema(p);


        }

        /* Point the object iterator at the first object */
        if( p->rc==SQLITE_OK ){
          p->rc = rbuObjIterFirst(p, &p->objiter);
        }

................................................................................
      /* If this is being called to read the first page of the target 
      ** database as part of an rbu vacuum operation, synthesize the 
      ** contents of the first page if it does not yet exist. Otherwise,
      ** SQLite will not check for a *-wal file.  */
      if( pRbu && rbuIsVacuum(pRbu) 
          && rc==SQLITE_IOERR_SHORT_READ && iOfst==0
          && (p->openFlags & SQLITE_OPEN_MAIN_DB)

      ){
        sqlite3_file *pFd = (sqlite3_file*)pRbu->pRbuFd;
        rc = pFd->pMethods->xRead(pFd, zBuf, iAmt, iOfst);
        if( rc==SQLITE_OK ){
          u8 *aBuf = (u8*)zBuf;

          rbuPutU32(&aBuf[52], 0);          /* largest root page number */
          rbuPutU32(&aBuf[36], 0);          /* number of free pages */
          rbuPutU32(&aBuf[32], 0);          /* first page on free list trunk */
          rbuPutU32(&aBuf[28], 1);          /* size of db file in pages */
          rbuPutU32(&aBuf[24], pRbu->pRbuFd->iCookie+1);  /* Change counter */

          if( iAmt>100 ){
            assert( iAmt>=101 );







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>











<
>
>
|
|
>
|
|
>
>







<
<







 







>
>
>
>
>







 







>
>







 







>





>
|







3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377

3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393


3394
3395
3396
3397
3398
3399
3400
....
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
....
3526
3527
3528
3529
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
....
3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
3909
3910
3911
3912
          p->nPhaseOneStep = sqlite3_column_int64(pStmt, 0);
        }
        p->rc = sqlite3_finalize(pStmt);
      }
    }
  }
}

/*
** The second argument passed to this function is the name of a PRAGMA 
** setting - "page_size", "auto_vacuum", "user_version" or "application_id".
** This function executes the following on sqlite3rbu.dbRbu:
**
**   "PRAGMA main.$zPragma"
**
** where $zPragma is the string passed as the second argument, then
** on sqlite3rbu.dbMain:
**
**   "PRAGMA main.$zPragma = $val"
**
** where $val is the value returned by the first PRAGMA invocation.
**
** In short, it copies the value  of the specified PRAGMA setting from
** dbRbu to dbMain.
*/
static void rbuCopyPragma(sqlite3rbu *p, const char *zPragma){
  if( p->rc==SQLITE_OK ){
    sqlite3_stmt *pPragma = 0;
    p->rc = prepareFreeAndCollectError(p->dbRbu, &pPragma, &p->zErrmsg, 
        sqlite3_mprintf("PRAGMA main.%s", zPragma)
    );
    if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPragma) ){
      p->rc = rbuMPrintfExec(p, p->dbMain, "PRAGMA main.%s = %d",
          zPragma, sqlite3_column_int(pPragma, 0)
      );
    }
    rbuFinalize(p, pPragma);
  }
}

/*
** The RBU handle passed as the only argument has just been opened and 
** the state database is empty. If this RBU handle was opened for an
** RBU vacuum operation, create the schema in the target db.
*/
static void rbuCreateTargetSchema(sqlite3rbu *p){
  sqlite3_stmt *pSql = 0;
  sqlite3_stmt *pInsert = 0;

  assert( rbuIsVacuum(p) );

  p->rc = sqlite3_exec(p->dbMain, "PRAGMA writable_schema=1", 0,0, &p->zErrmsg);
  if( p->rc==SQLITE_OK ){
    p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, 
      "SELECT sql FROM sqlite_master WHERE sql!='' AND rootpage!=0"
      " AND name!='sqlite_sequence' "
      " ORDER BY type DESC"
    );
  }

  while( p->rc==SQLITE_OK && sqlite3_step(pSql)==SQLITE_ROW ){
    const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
    p->rc = sqlite3_exec(p->dbMain, zSql, 0, 0, &p->zErrmsg);
  }
  rbuFinalize(p, pSql);
  if( p->rc!=SQLITE_OK ) return;



  if( p->rc==SQLITE_OK ){
    p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, 
        "SELECT * FROM sqlite_master WHERE rootpage=0 OR rootpage IS NULL" 
    );
  }

  if( p->rc==SQLITE_OK ){
................................................................................
        );
      }
    }

    if( p->rc==SQLITE_OK ){
      if( p->eStage==RBU_STAGE_OAL ){
        sqlite3 *db = p->dbMain;

        if( pState->eStage==0 && rbuIsVacuum(p) ){
          rbuCopyPragma(p, "page_size");
          rbuCopyPragma(p, "auto_vacuum");
        }

        /* Open transactions both databases. The *-oal file is opened or
        ** created at this point. */
        p->rc = sqlite3_exec(db, "BEGIN IMMEDIATE", 0, 0, &p->zErrmsg);
        if( p->rc==SQLITE_OK ){
          p->rc = sqlite3_exec(p->dbRbu, "BEGIN", 0, 0, &p->zErrmsg);
        }
................................................................................
          }
        }

        /* If this is an RBU vacuum operation and the state table was empty
        ** when this handle was opened, create the target database schema. */
        if( pState->eStage==0 && rbuIsVacuum(p) ){
          rbuCreateTargetSchema(p);
          rbuCopyPragma(p, "user_version");
          rbuCopyPragma(p, "application_id");
        }

        /* Point the object iterator at the first object */
        if( p->rc==SQLITE_OK ){
          p->rc = rbuObjIterFirst(p, &p->objiter);
        }

................................................................................
      /* If this is being called to read the first page of the target 
      ** database as part of an rbu vacuum operation, synthesize the 
      ** contents of the first page if it does not yet exist. Otherwise,
      ** SQLite will not check for a *-wal file.  */
      if( pRbu && rbuIsVacuum(pRbu) 
          && rc==SQLITE_IOERR_SHORT_READ && iOfst==0
          && (p->openFlags & SQLITE_OPEN_MAIN_DB)
          && pRbu->pRbuFd->base.pMethods
      ){
        sqlite3_file *pFd = (sqlite3_file*)pRbu->pRbuFd;
        rc = pFd->pMethods->xRead(pFd, zBuf, iAmt, iOfst);
        if( rc==SQLITE_OK ){
          u8 *aBuf = (u8*)zBuf;
          u32 iRoot = rbuGetU32(&aBuf[52]) ? 1 : 0;
          rbuPutU32(&aBuf[52], iRoot);      /* largest root page number */
          rbuPutU32(&aBuf[36], 0);          /* number of free pages */
          rbuPutU32(&aBuf[32], 0);          /* first page on free list trunk */
          rbuPutU32(&aBuf[28], 1);          /* size of db file in pages */
          rbuPutU32(&aBuf[24], pRbu->pRbuFd->iCookie+1);  /* Change counter */

          if( iAmt>100 ){
            assert( iAmt>=101 );