/ Check-in [35e6ac18]
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:When copying a rollback mode database over a WAL database using the backup API, leave the destination database in WAL mode (instead of switching it to rollback mode). Fix for [af95b8c609].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 35e6ac18e3d030095da57fff87a288939e9be93f
User & Date: dan 2011-08-23 11:47:06
Context
2011-08-23
12:50
Merge the PAGECACHE_BLOCKALLOC changes into trunk. check-in: 768c1846 user: drh tags: trunk
11:47
When copying a rollback mode database over a WAL database using the backup API, leave the destination database in WAL mode (instead of switching it to rollback mode). Fix for [af95b8c609]. check-in: 35e6ac18 user: dan tags: trunk
00:01
Remove a branch that is always true. check-in: a6568192 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/backup.c.

414
415
416
417
418
419
420









421
422
423
424
425
426
427
     && (rc = sqlite3BtreeUpdateMeta(p->pDest,1,p->iDestSchema+1))==SQLITE_OK
    ){
      int nDestTruncate;
  
      if( p->pDestDb ){
        sqlite3ResetInternalSchema(p->pDestDb, -1);
      }










      /* Set nDestTruncate to the final number of pages in the destination
      ** database. The complication here is that the destination page
      ** size may be different to the source page size. 
      **
      ** If the source page size is smaller than the destination page size, 
      ** round up. In this case the call to sqlite3OsTruncate() below will







>
>
>
>
>
>
>
>
>







414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
     && (rc = sqlite3BtreeUpdateMeta(p->pDest,1,p->iDestSchema+1))==SQLITE_OK
    ){
      int nDestTruncate;
  
      if( p->pDestDb ){
        sqlite3ResetInternalSchema(p->pDestDb, -1);
      }

      if( destMode==PAGER_JOURNALMODE_WAL ){
        /* This call cannot fail. The success of the BtreeUpdateMeta() 
        ** method above indicates that a write transaction has been opened 
        ** and page 1 is already dirty. Therefore this always succeeds.
        */
        TESTONLY(int rc2 =) sqlite3BtreeSetVersion(p->pDest, 2);
        assert( rc2==SQLITE_OK );
      }

      /* Set nDestTruncate to the final number of pages in the destination
      ** database. The complication here is that the destination page
      ** size may be different to the source page size. 
      **
      ** If the source page size is smaller than the destination page size, 
      ** round up. In this case the call to sqlite3OsTruncate() below will

Changes to src/btree.c.

8145
8146
8147
8148
8149
8150
8151
8152
8153
8154
8155
8156
8157
8158
8159
** "write version" (single byte at byte offset 19) fields in the database
** header to iVersion.
*/
int sqlite3BtreeSetVersion(Btree *pBtree, int iVersion){
  BtShared *pBt = pBtree->pBt;
  int rc;                         /* Return code */
 
  assert( pBtree->inTrans==TRANS_NONE );
  assert( iVersion==1 || iVersion==2 );

  /* If setting the version fields to 1, do not automatically open the
  ** WAL connection, even if the version fields are currently set to 2.
  */
  pBt->doNotUseWAL = (u8)(iVersion==1);








<







8145
8146
8147
8148
8149
8150
8151

8152
8153
8154
8155
8156
8157
8158
** "write version" (single byte at byte offset 19) fields in the database
** header to iVersion.
*/
int sqlite3BtreeSetVersion(Btree *pBtree, int iVersion){
  BtShared *pBt = pBtree->pBt;
  int rc;                         /* Return code */
 

  assert( iVersion==1 || iVersion==2 );

  /* If setting the version fields to 1, do not automatically open the
  ** WAL connection, even if the version fields are currently set to 2.
  */
  pBt->doNotUseWAL = (u8)(iVersion==1);

Changes to test/walbak.test.

75
76
77
78
79
80
81



82
83
84
85
86
87
88
...
266
267
268
269
270
271
272
273





















274
275
276
277


278




















































279
do_test walbak-1.5 {
  list [file size test.db] [file size test.db-wal]
} [list 1024 [wal_file_size 6 1024]]
do_test walbak-1.6 {
  execsql { PRAGMA wal_checkpoint }
  list [file size test.db] [file size test.db-wal]
} [list [expr 3*1024] [wal_file_size 6 1024]]



do_test walbak-1.7 {
  execsql { 
    CREATE TABLE t2(a, b);
    INSERT INTO t2 SELECT * FROM t1;
    DROP TABLE t1;
  }
  list [file size test.db] [file size test.db-wal]
................................................................................

  do_test walbak-3.$tn.4 {
    sqlite3_backup B db main db2 main
    B step 10000
    B finish
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8}






















  db  close
  db2 close
}
























































finish_test







>
>
>







 








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




>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
do_test walbak-1.5 {
  list [file size test.db] [file size test.db-wal]
} [list 1024 [wal_file_size 6 1024]]
do_test walbak-1.6 {
  execsql { PRAGMA wal_checkpoint }
  list [file size test.db] [file size test.db-wal]
} [list [expr 3*1024] [wal_file_size 6 1024]]
do_test walbak-1.6.1 {
  hexio_read test.db 18 2
} {0202}
do_test walbak-1.7 {
  execsql { 
    CREATE TABLE t2(a, b);
    INSERT INTO t2 SELECT * FROM t1;
    DROP TABLE t1;
  }
  list [file size test.db] [file size test.db-wal]
................................................................................

  do_test walbak-3.$tn.4 {
    sqlite3_backup B db main db2 main
    B step 10000
    B finish
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8}

  # Check that [db] is still in WAL mode.
  do_test walbak-3.$tn.5 {
    execsql { PRAGMA journal_mode }
  } {wal}
  do_test walbak-3.$tn.6 {
    execsql { PRAGMA wal_checkpoint }
    hexio_read test.db 18 2
  } {0202}

  # If it was not an in-memory database, check that [db2] is still in
  # rollback mode.
  if {[file exists test.db2]} {
    do_test walbak-3.$tn.7 {
      execsql { PRAGMA journal_mode } db2
    } {wal}
    do_test walbak-3.$tn.8 {
      execsql { PRAGMA wal_checkpoint }
      hexio_read test.db 18 2
    } {0202}
  }

  db  close
  db2 close
}

#-------------------------------------------------------------------------
# Test that the following holds when a backup operation is run:
#
#   Source  |  Destination inital  |  Destination final
#   ---------------------------------------------------
#   Rollback   Rollback               Rollback
#   Rollback   WAL                    WAL
#   WAL        Rollback               WAL
#   WAL        WAL                    WAL
#
foreach {tn src dest dest_final} {
  1   delete    delete    delete
  2   delete    wal       wal
  3   wal       delete    wal
  4   wal       wal       wal
} {
  catch { db close } 
  catch { db2 close } 
  forcedelete test.db test.db2

  do_test walbak-4.$tn.1 {
    sqlite3 db test.db
    db eval "PRAGMA journal_mode = $src"
    db eval {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES('I', 'II');
      INSERT INTO t1 VALUES('III', 'IV');
    }

    sqlite3 db2 test.db2
    db2 eval "PRAGMA journal_mode = $dest"
    db2 eval {
      CREATE TABLE t2(x, y);
      INSERT INTO t2 VALUES('1', '2');
      INSERT INTO t2 VALUES('3', '4');
    }
  } {}

  do_test walbak-4.$tn.2 { execsql { PRAGMA journal_mode } db  } $src
  do_test walbak-4.$tn.3 { execsql { PRAGMA journal_mode } db2 } $dest

  do_test walbak-4.$tn.4 { db backup test.db2 } {}
  do_test walbak-4.$tn.5 {
    execsql { SELECT * FROM t1 } db2
  } {I II III IV}
  do_test walbak-4.$tn.5 { execsql { PRAGMA journal_mode } db2 } $dest_final


  db2 close
  do_test walbak-4.$tn.6 { file exists test.db2-wal } 0
  sqlite3 db2 test.db2
  do_test walbak-4.$tn.7 { execsql { PRAGMA journal_mode } db2 } $dest_final
}


finish_test