/ Check-in [8f8b373e]
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:Fix test cases so that they work with SQLITE_DEFAULT_WAL_SAFETYLEVEL defined.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | apple-osx
Files: files | file ages | folders
SHA1: 8f8b373eed7052e6e93c1805fc1effcf1db09366
User & Date: dan 2011-06-25 16:35:41
Context
2011-06-25
21:43
Changes to make pragma synchronous sticky when SQLITE_DEFAULT_WAL_SAFETYLEVEL is used check-in: c6158b25 user: adam tags: apple-osx
16:35
Fix test cases so that they work with SQLITE_DEFAULT_WAL_SAFETYLEVEL defined. check-in: 8f8b373e user: dan tags: apple-osx
2011-06-24
21:47
Fix the build. check-in: 97729542 user: drh tags: apple-osx
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

2355
2356
2357
2358
2359
2360
2361

2362





2363
2364
2365
2366
2367
2368
2369
2370
2371
      rc = sqlite3PagerOpenWal(pBt->pPager, &isOpen);
      if( rc!=SQLITE_OK ){
        goto page1_init_failed;
      }else if( isOpen==0 ){
#ifdef SQLITE_DEFAULT_WAL_SAFETYLEVEL
        /* Default to specified safety_level for WAL mode */
        if( pBt->db!=0 && pBt->db->aDb!=0 ){

          sqlite3 *db = pBt->db;





          if( db->aDb[0].safety_level != SQLITE_DEFAULT_WAL_SAFETYLEVEL) {
            db->aDb[0].safety_level = SQLITE_DEFAULT_WAL_SAFETYLEVEL;
            sqlite3PagerSetSafetyLevel(pBt->pPager, SQLITE_DEFAULT_WAL_SAFETYLEVEL, 
                                       (db->flags&SQLITE_FullFSync)!=0,
                                       (db->flags&SQLITE_CkptFullFSync)!=0);
          }
        }
#endif
        releasePage(pPage1);







>

>
>
>
>
>
|
|







2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
      rc = sqlite3PagerOpenWal(pBt->pPager, &isOpen);
      if( rc!=SQLITE_OK ){
        goto page1_init_failed;
      }else if( isOpen==0 ){
#ifdef SQLITE_DEFAULT_WAL_SAFETYLEVEL
        /* Default to specified safety_level for WAL mode */
        if( pBt->db!=0 && pBt->db->aDb!=0 ){
          int iDb;
          sqlite3 *db = pBt->db;
          Db *aDb = db->aDb;
          for(iDb=0; iDb<db->nDb; iDb++){
            if( aDb[iDb].pBt && aDb[iDb].pBt->pBt==pBt ) break;
          }
          assert( iDb<db->nDb );
          if( aDb[iDb].safety_level != SQLITE_DEFAULT_WAL_SAFETYLEVEL) {
            aDb[iDb].safety_level = SQLITE_DEFAULT_WAL_SAFETYLEVEL;
            sqlite3PagerSetSafetyLevel(pBt->pPager, SQLITE_DEFAULT_WAL_SAFETYLEVEL, 
                                       (db->flags&SQLITE_FullFSync)!=0,
                                       (db->flags&SQLITE_CkptFullFSync)!=0);
          }
        }
#endif
        releasePage(pPage1);

Changes to test/incrvacuum2.test.

11
12
13
14
15
16
17

18
19
20
21
22
23
24
...
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
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the incremental vacuum feature.
#
# $Id: incrvacuum2.test,v 1.6 2009/07/25 13:42:50 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl


# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum || !pragma} {
  finish_test
  return
}
................................................................................
    DELETE FROM t1;
  }

  do_test 4.2 {
    execsql { 
      PRAGMA journal_mode = WAL;
      PRAGMA incremental_vacuum(1);
      PRAGMA wal_checkpoint;
    }



    file size test.db-wal
  } {1640}


  do_test 4.3 {
    db close
    sqlite3 db test.db
    set maxsz 0
    while {[file size test.db] > [expr 512*3]} {
      execsql { PRAGMA journal_mode = WAL }
      execsql { PRAGMA wal_checkpoint }
      execsql { PRAGMA incremental_vacuum(1) }
      set newsz [file size test.db-wal]
      if {$newsz>$maxsz} {set maxsz $newsz}
    }
    set maxsz 
  } {2176}
}

finish_test







>







 







<

>
>
>

<
>













|



11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
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
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the incremental vacuum feature.
#
# $Id: incrvacuum2.test,v 1.6 2009/07/25 13:42:50 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/wal_common.tcl

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum || !pragma} {
  finish_test
  return
}
................................................................................
    DELETE FROM t1;
  }

  do_test 4.2 {
    execsql { 
      PRAGMA journal_mode = WAL;
      PRAGMA incremental_vacuum(1);

    }
  } {wal}
  do_test 4.2.1 {
    execsql { PRAGMA wal_checkpoint }
    file size test.db-wal

  } [wal_file_size [wal_frames db 2 1] 512]

  do_test 4.3 {
    db close
    sqlite3 db test.db
    set maxsz 0
    while {[file size test.db] > [expr 512*3]} {
      execsql { PRAGMA journal_mode = WAL }
      execsql { PRAGMA wal_checkpoint }
      execsql { PRAGMA incremental_vacuum(1) }
      set newsz [file size test.db-wal]
      if {$newsz>$maxsz} {set maxsz $newsz}
    }
    set maxsz 
  } [wal_file_size [wal_frames db 3 1] 512]
}

finish_test

Changes to test/pager1.test.

2004
2005
2006
2007
2008
2009
2010
2011
2012









2013
2014
2015
2016

2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
  execsql { PRAGMA wal_checkpoint }
} {0 -1 -1}
do_test pager1-22.2.1 {
  testvfs tv -default 1
  tv filter xSync
  tv script xSyncCb
  proc xSyncCb {args} {incr ::synccount}
  set ::synccount 0
  sqlite3 db test.db









  execsql {
    PRAGMA journal_mode = WAL;
    PRAGMA synchronous = off;
    INSERT INTO ko DEFAULT VALUES;

  }
  execsql { PRAGMA wal_checkpoint }
  set synccount
} {0}
db close
tv delete

#-------------------------------------------------------------------------
# Tests for changing journal mode.
#
#   pager1-23.1.*: Test that when changing from PERSIST to DELETE mode,
#                  the journal file is deleted.
#







<

>
>
>
>
>
>
>
>
>

<


>

<


<
<







2004
2005
2006
2007
2008
2009
2010

2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021

2022
2023
2024
2025

2026
2027


2028
2029
2030
2031
2032
2033
2034
  execsql { PRAGMA wal_checkpoint }
} {0 -1 -1}
do_test pager1-22.2.1 {
  testvfs tv -default 1
  tv filter xSync
  tv script xSyncCb
  proc xSyncCb {args} {incr ::synccount}

  sqlite3 db test.db

  # Switch the db to WAL mode. And then execute a SELECT to make sure
  # that the WAL file is open. Note that this may change the synchronous
  # setting if DEFAULT_WAL_SAFETYLEVEL is defined.
  execsql { PRAGMA journal_mode = WAL ; SELECT * FROM ko }

  # Set synchronous=OFF. Insert some data and run a checkpoint. Since
  # sync=off, this should not cause any calls to the xSync() method.
  set ::synccount 0
  execsql {

    PRAGMA synchronous = off;
    INSERT INTO ko DEFAULT VALUES;
    PRAGMA wal_checkpoint;
  }

  set synccount
} {0}



#-------------------------------------------------------------------------
# Tests for changing journal mode.
#
#   pager1-23.1.*: Test that when changing from PERSIST to DELETE mode,
#                  the journal file is deleted.
#

Changes to test/superlock.test.

9
10
11
12
13
14
15

16
17
18
19
20
21
22
..
73
74
75
76
77
78
79
80


81
82
83
84
85
86
87
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl


set testprefix superlock

# Test organization:
#
#   1.*: Test superlock on a rollback database. Test that once the db is
#        superlocked, it is not possible for a second client to read from
................................................................................

do_test 3.2 { sqlite3demo_superlock unlock test.db } {unlock}
do_catchsql_test 3.3 { SELECT * FROM t1 }           {1 {database is locked}}
do_catchsql_test 3.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}}
do_catchsql_test 3.5 { PRAGMA wal_checkpoint }      {0 {1 -1 -1}}
do_test 3.6 { unlock } {}

do_execsql_test 4.1 { PRAGMA wal_checkpoint } {0 2 2}



do_test 4.2 { sqlite3demo_superlock unlock test.db } {unlock}
do_catchsql_test 4.3 { SELECT * FROM t1 }           {1 {database is locked}}
do_catchsql_test 4.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}}
do_catchsql_test 4.5 { PRAGMA wal_checkpoint }      {0 {1 -1 -1}}
do_test 4.6 { unlock } {}








>







 







|
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/wal_common.tcl

set testprefix superlock

# Test organization:
#
#   1.*: Test superlock on a rollback database. Test that once the db is
#        superlocked, it is not possible for a second client to read from
................................................................................

do_test 3.2 { sqlite3demo_superlock unlock test.db } {unlock}
do_catchsql_test 3.3 { SELECT * FROM t1 }           {1 {database is locked}}
do_catchsql_test 3.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}}
do_catchsql_test 3.5 { PRAGMA wal_checkpoint }      {0 {1 -1 -1}}
do_test 3.6 { unlock } {}

do_execsql_test 4.1 { PRAGMA wal_checkpoint } [
  list 0 [wal_frames db 1 1] [wal_frames db 1 1]
]

do_test 4.2 { sqlite3demo_superlock unlock test.db } {unlock}
do_catchsql_test 4.3 { SELECT * FROM t1 }           {1 {database is locked}}
do_catchsql_test 4.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}}
do_catchsql_test 4.5 { PRAGMA wal_checkpoint }      {0 {1 -1 -1}}
do_test 4.6 { unlock } {}

Changes to test/wal.test.

544
545
546
547
548
549
550

551
552

553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
...
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
...
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
...
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
....
1053
1054
1055
1056
1057
1058
1059

1060

1061
1062
1063
1064
1065
1066
1067
1068
1069
....
1125
1126
1127
1128
1129
1130
1131

1132
1133
1134
1135
1136
1137
1138
....
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
  # Open a read transaction with [db2]. Check that this prevents [db] from
  # checkpointing the database. But not from writing to it.
  #
  do_test wal-10.$tn.11 {
    sql2 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10}
  do_test wal-10.$tn.12 {

    catchsql { PRAGMA wal_checkpoint } 
  } {0 {0 13 13}}   ;# Reader no longer block checkpoints

  do_test wal-10.$tn.13 {
    execsql { INSERT INTO t1 VALUES(11, 12) }
    sql2 {SELECT * FROM t1}
  } {1 2 3 4 5 6 7 8 9 10}

  # Writers do not block checkpoints any more either.
  #
  do_test wal-10.$tn.14 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {0 15 13}}

  # The following series of test cases used to verify another blocking
  # case in WAL - a case which no longer blocks.
  #
  do_test wal-10.$tn.15 {
    sql2 { COMMIT; BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.16 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {0 15 15}}
  do_test wal-10.$tn.17 {
    execsql { PRAGMA wal_checkpoint } 
  } {0 15 15}
  do_test wal-10.$tn.18 {
    sql3 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.19 {
    catchsql { INSERT INTO t1 VALUES(13, 14) }
  } {0 {}}
  do_test wal-10.$tn.20 {
................................................................................
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}

  # Another series of tests that used to demonstrate blocking behavior
  # but which now work.
  #
  do_test wal-10.$tn.23 {
    execsql { PRAGMA wal_checkpoint }
  } {0 17 17}
  do_test wal-10.$tn.24 {
    sql2 { BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
  do_test wal-10.$tn.25 {
    execsql { PRAGMA wal_checkpoint }
  } {0 17 17}
  do_test wal-10.$tn.26 {
    catchsql { INSERT INTO t1 VALUES(15, 16) }
  } {0 {}}
  do_test wal-10.$tn.27 {
    sql3 { INSERT INTO t1 VALUES(17, 18) }
  } {}
  do_test wal-10.$tn.28 {
................................................................................
      set ::STMT [sqlite3_prepare db3 "SELECT * FROM t1" -1 TAIL]
      sqlite3_step $::STMT
    }
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18}
  do_test wal-10.$tn.29 {
    execsql { INSERT INTO t1 VALUES(19, 20) }
    catchsql { PRAGMA wal_checkpoint }
  } {0 {0 6 0}}
  do_test wal-10.$tn.30 {
    code3 { sqlite3_finalize $::STMT }
    execsql { PRAGMA wal_checkpoint }
  } {0 6 0}

  # At one point, if a reader failed to upgrade to a writer because it
  # was reading an old snapshot, the write-locks were not being released.
  # Test that this bug has been fixed.
  #
  do_test wal-10.$tn.31 {
    sql2 COMMIT
................................................................................
    }
    sql2 {
      BEGIN;
        SELECT * FROM t1;
    }
  } {a b c d}
  do_test wal-10.$tn.36 {
    catchsql { PRAGMA wal_checkpoint }
  } {0 {0 16 16}}
  do_test wal-10.$tn.36 {
    sql3 { INSERT INTO t1 VALUES('e', 'f') }
    sql2 { SELECT * FROM t1 }
  } {a b c d}
  do_test wal-10.$tn.37 {
    sql2 COMMIT
    execsql { PRAGMA wal_checkpoint }
  } {0 18 18}
}

#-------------------------------------------------------------------------
# This block of tests, wal-11.*, test that nothing goes terribly wrong
# if frames must be written to the log file before a transaction is
# committed (in order to free up memory).
#
................................................................................
    sqlite3 db test.db
    execsql {
      ATTACH 'test2.db' AS aux;
      PRAGMA main.auto_vacuum = 0;
      PRAGMA aux.auto_vacuum = 0;
      PRAGMA main.journal_mode = WAL;
      PRAGMA aux.journal_mode = WAL;

      PRAGMA synchronous = NORMAL;

    }
  } {wal wal}

  do_test wal-16.$tn.2 {
    execsql {
      CREATE TABLE main.t1(a, b, PRIMARY KEY(a, b));
      CREATE TABLE aux.t2(a, b, PRIMARY KEY(a, b));

      INSERT INTO t2 VALUES(1, randomblob(1000));
................................................................................
  sqlite3 db test.db -vfs devsym

  do_test wal-17.$tn.1 {
    execsql {
      PRAGMA auto_vacuum = 0;
      PRAGMA page_size = 512;
      PRAGMA journal_mode = WAL;

      PRAGMA synchronous = FULL;
    }
    execsql {
      BEGIN;
      CREATE TABLE t(x);
    }
    for {set i 0} {$i<166} {incr i} {
................................................................................
      PRAGMA incremental_vacuum;
      PRAGMA wal_checkpoint;
    }
    file size test.db
  } [expr 3 * 1024]
  do_test 24.5 {
    file size test.db-wal
  } 2128
}

db close
sqlite3_shutdown
test_sqlite3_log
sqlite3_initialize

finish_test







>
|
<
>








|
|








|
|


|







 







|





|







 







|
|



|







 







|
|







|







 







>

>

|







 







>







 







|








544
545
546
547
548
549
550
551
552

553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
...
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
...
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
...
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
....
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
....
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
....
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
  # Open a read transaction with [db2]. Check that this prevents [db] from
  # checkpointing the database. But not from writing to it.
  #
  do_test wal-10.$tn.11 {
    sql2 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10}
  do_test wal-10.$tn.12 {
    # Reader no longer blocks checkpoint:
    execsql { PRAGMA wal_checkpoint } 

  } [list 0 [wal_frames db 8 5] [wal_frames db 8 5]] 
  do_test wal-10.$tn.13 {
    execsql { INSERT INTO t1 VALUES(11, 12) }
    sql2 {SELECT * FROM t1}
  } {1 2 3 4 5 6 7 8 9 10}

  # Writers do not block checkpoints any more either.
  #
  do_test wal-10.$tn.14 {
    execsql { PRAGMA wal_checkpoint } 
  } [list 0 [wal_frames db 9 6] [wal_frames db 8 5]]

  # The following series of test cases used to verify another blocking
  # case in WAL - a case which no longer blocks.
  #
  do_test wal-10.$tn.15 {
    sql2 { COMMIT; BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.16 {
    execsql { PRAGMA wal_checkpoint } 
  } [list 0 [wal_frames db 9 6] [wal_frames db 9 6]]
  do_test wal-10.$tn.17 {
    execsql { PRAGMA wal_checkpoint } 
  } [list 0 [wal_frames db 9 6] [wal_frames db 9 6]]
  do_test wal-10.$tn.18 {
    sql3 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.19 {
    catchsql { INSERT INTO t1 VALUES(13, 14) }
  } {0 {}}
  do_test wal-10.$tn.20 {
................................................................................
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}

  # Another series of tests that used to demonstrate blocking behavior
  # but which now work.
  #
  do_test wal-10.$tn.23 {
    execsql { PRAGMA wal_checkpoint }
  } [list 0 [wal_frames db 10 7] [wal_frames db 10 7]]
  do_test wal-10.$tn.24 {
    sql2 { BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
  do_test wal-10.$tn.25 {
    execsql { PRAGMA wal_checkpoint }
  } [list 0 [wal_frames db 10 7] [wal_frames db 10 7]]
  do_test wal-10.$tn.26 {
    catchsql { INSERT INTO t1 VALUES(15, 16) }
  } {0 {}}
  do_test wal-10.$tn.27 {
    sql3 { INSERT INTO t1 VALUES(17, 18) }
  } {}
  do_test wal-10.$tn.28 {
................................................................................
      set ::STMT [sqlite3_prepare db3 "SELECT * FROM t1" -1 TAIL]
      sqlite3_step $::STMT
    }
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18}
  do_test wal-10.$tn.29 {
    execsql { INSERT INTO t1 VALUES(19, 20) }
    execsql { PRAGMA wal_checkpoint }
  } [list 0 [wal_frames db 4 2] 0]
  do_test wal-10.$tn.30 {
    code3 { sqlite3_finalize $::STMT }
    execsql { PRAGMA wal_checkpoint }
  } [list 0 [wal_frames db 4 2] 0]

  # At one point, if a reader failed to upgrade to a writer because it
  # was reading an old snapshot, the write-locks were not being released.
  # Test that this bug has been fixed.
  #
  do_test wal-10.$tn.31 {
    sql2 COMMIT
................................................................................
    }
    sql2 {
      BEGIN;
        SELECT * FROM t1;
    }
  } {a b c d}
  do_test wal-10.$tn.36 {
    execsql { PRAGMA wal_checkpoint }
  } [list 0 [wal_frames db 11 5] [wal_frames db 11 5]]
  do_test wal-10.$tn.36 {
    sql3 { INSERT INTO t1 VALUES('e', 'f') }
    sql2 { SELECT * FROM t1 }
  } {a b c d}
  do_test wal-10.$tn.37 {
    sql2 COMMIT
    execsql { PRAGMA wal_checkpoint }
  } [list 0 [wal_frames db 13 5] [wal_frames db 13 5]]
}

#-------------------------------------------------------------------------
# This block of tests, wal-11.*, test that nothing goes terribly wrong
# if frames must be written to the log file before a transaction is
# committed (in order to free up memory).
#
................................................................................
    sqlite3 db test.db
    execsql {
      ATTACH 'test2.db' AS aux;
      PRAGMA main.auto_vacuum = 0;
      PRAGMA aux.auto_vacuum = 0;
      PRAGMA main.journal_mode = WAL;
      PRAGMA aux.journal_mode = WAL;
      SELECT count(*) FROM main.sqlite_master, aux.sqlite_master;
      PRAGMA synchronous = NORMAL;
      PRAGMA aux.synchronous = FULL;
    }
  } {wal wal 0}

  do_test wal-16.$tn.2 {
    execsql {
      CREATE TABLE main.t1(a, b, PRIMARY KEY(a, b));
      CREATE TABLE aux.t2(a, b, PRIMARY KEY(a, b));

      INSERT INTO t2 VALUES(1, randomblob(1000));
................................................................................
  sqlite3 db test.db -vfs devsym

  do_test wal-17.$tn.1 {
    execsql {
      PRAGMA auto_vacuum = 0;
      PRAGMA page_size = 512;
      PRAGMA journal_mode = WAL;
      SELECT * FROM sqlite_master;
      PRAGMA synchronous = FULL;
    }
    execsql {
      BEGIN;
      CREATE TABLE t(x);
    }
    for {set i 0} {$i<166} {incr i} {
................................................................................
      PRAGMA incremental_vacuum;
      PRAGMA wal_checkpoint;
    }
    file size test.db
  } [expr 3 * 1024]
  do_test 24.5 {
    file size test.db-wal
  } [wal_file_size [wal_frames db 1 1] 1024]
}

db close
sqlite3_shutdown
test_sqlite3_log
sqlite3_initialize

finish_test

Changes to test/wal2.test.

352
353
354
355
356
357
358
359
360
361
362
363
364
365




366
367
368

369
370
371
372
373
374
375
...
718
719
720
721
722
723
724
725
726
727
728
729
730
731




732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
....
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195













1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210


1211
1212
1213
1214
1215
1216
1217
....
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
....
1270
1271
1272
1273
1274
1275
1276

1277





















1278
1279
1280
1281
1282
1283
1284

}

#-------------------------------------------------------------------------
# Test that a database connection using a VFS that does not support the
# xShmXXX interfaces cannot open a WAL database.
#
do_test wal2-4.1 {
  sqlite3 db test.db
  execsql {
    PRAGMA auto_vacuum = 0;
    PRAGMA journal_mode = WAL;
    CREATE TABLE data(x);
    INSERT INTO data VALUES('need xShmOpen to see this');




    PRAGMA wal_checkpoint;
  }
} {wal 0 5 5}

do_test wal2-4.2 {
  db close
  testvfs tvfs -noshm 1
  sqlite3 db test.db -vfs tvfs
  catchsql { SELECT * FROM data }
} {1 {unable to open database file}}
do_test wal2-4.3 {
................................................................................
  do_test wal2-6.4.$tn.1 { execsql $S } $res
  do_test wal2-6.4.$tn.2 { set ::locks  } $L
}

db close
tvfs delete

do_test wal2-6.5.1 {
  sqlite3 db test.db
  execsql {
    PRAGMA auto_vacuum = 0;
    PRAGMA journal_mode = wal;
    PRAGMA locking_mode = exclusive;
    CREATE TABLE t2(a, b);




    PRAGMA wal_checkpoint;
    INSERT INTO t2 VALUES('I', 'II');
    PRAGMA journal_mode;
  }
} {wal exclusive 0 3 3 wal}
do_test wal2-6.5.2 {
  execsql {
    PRAGMA locking_mode = normal;
    INSERT INTO t2 VALUES('III', 'IV');
    PRAGMA locking_mode = exclusive;
    SELECT * FROM t2;
  }
} {normal exclusive I II III IV}
do_test wal2-6.5.3 {
  execsql { PRAGMA wal_checkpoint }
} {0 4 4}
db close

proc lock_control {method filename handle spec} {
  foreach {start n op type} $spec break
  if {$op == "lock"} { return SQLITE_IOERR }
  return SQLITE_OK
}
................................................................................
    catch { db close }
  }
}

#-------------------------------------------------------------------------
# Test that "PRAGMA checkpoint_fullsync" appears to be working.
#
foreach {tn sql reslist} {
  1 { }                                 {8 0 3 0 5 0}
  2 { PRAGMA checkpoint_fullfsync = 1 } {8 4 3 2 5 2}
  3 { PRAGMA checkpoint_fullfsync = 0 } {8 0 3 0 5 0}
} {
  if { $::sqlite_options(default_ckptfullfsync) && $tn == 1} {
    # checkpoint_fullfsync on by default
    set reslist {8 4 3 2 5 2}
  }

  faultsim_delete_and_reopen

  execsql {PRAGMA auto_vacuum = 0}
  execsql $sql
  do_execsql_test wal2-14.$tn.1 { PRAGMA journal_mode = WAL } {wal}














  set sqlite_sync_count 0
  set sqlite_fullsync_count 0

  do_execsql_test wal2-14.$tn.2 {
    PRAGMA wal_autocheckpoint = 10;
    CREATE TABLE t1(a, b);                -- 2 wal syncs
    INSERT INTO t1 VALUES(1, 2);          -- 1 wal sync
    PRAGMA wal_checkpoint;                -- 1 wal sync, 1 db sync
    BEGIN;
      INSERT INTO t1 VALUES(3, 4);
      INSERT INTO t1 VALUES(5, 6);
    COMMIT;                               -- 1 wal sync
    PRAGMA wal_checkpoint;                -- 1 wal sync, 1 db sync
  } {10 0 5 5 0 2 2}



  do_test wal2-14.$tn.3 {
    cond_incr_sync_count 1
    list $sqlite_sync_count $sqlite_fullsync_count
  } [lrange $reslist 0 1]

  set sqlite_sync_count 0
................................................................................

catch { db close }

# PRAGMA checkpoint_fullsync
# PRAGMA fullfsync
# PRAGMA synchronous
#
foreach {tn settings commit_sync ckpt_sync} {
  1  {0 0 off}     {0 0}  {0 0}
  2  {0 0 normal}  {0 0}  {2 0}
  3  {0 0 full}    {1 0}  {2 0}

  4  {0 1 off}     {0 0}  {0 0}
  5  {0 1 normal}  {0 0}  {0 2}
  6  {0 1 full}    {0 1}  {0 2}

  7  {1 0 off}     {0 0}  {0 0}
  8  {1 0 normal}  {0 0}  {0 2}
  9  {1 0 full}    {1 0}  {0 2}

  10 {1 1 off}     {0 0}  {0 0}
  11 {1 1 normal}  {0 0}  {0 2}
  12 {1 1 full}    {0 1}  {0 2}
} {
  forcedelete test.db

  testvfs tvfs -default 1
  tvfs filter xSync
  tvfs script xSyncCb
  proc xSyncCb {method file fileid flags} {
................................................................................
  sqlite3 db test.db
  do_execsql_test 15.$tn.1 "
    CREATE TABLE t1(x);
    PRAGMA journal_mode = WAL;
    PRAGMA checkpoint_fullfsync = [lindex $settings 0];
    PRAGMA fullfsync = [lindex $settings 1];
    PRAGMA synchronous = [lindex $settings 2];

  " {wal}






















  do_test 15.$tn.2 {
    set sync(normal) 0
    set sync(full) 0
    execsql { INSERT INTO t1 VALUES('abc') }
    list $::sync(normal) $::sync(full)
  } $commit_sync







|






>
>
>
>


<
>







 







|






>
>
>
>




|










|







 







|
|
|
|

<
<
<
<
<




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





<








|
>
>







 







|
|
|
|

|
|
|

|
|
|

|
|
|







 







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







352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371

372
373
374
375
376
377
378
379
...
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
....
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193





1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216

1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
....
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
....
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323

}

#-------------------------------------------------------------------------
# Test that a database connection using a VFS that does not support the
# xShmXXX interfaces cannot open a WAL database.
#
do_test wal2-4.1.1 {
  sqlite3 db test.db
  execsql {
    PRAGMA auto_vacuum = 0;
    PRAGMA journal_mode = WAL;
    CREATE TABLE data(x);
    INSERT INTO data VALUES('need xShmOpen to see this');
  }
} {wal}
do_test wal2-4.1.2 {
  execsql {
    PRAGMA wal_checkpoint;
  }

} [list 0 [wal_frames db 3 2] [wal_frames db 3 2]]
do_test wal2-4.2 {
  db close
  testvfs tvfs -noshm 1
  sqlite3 db test.db -vfs tvfs
  catchsql { SELECT * FROM data }
} {1 {unable to open database file}}
do_test wal2-4.3 {
................................................................................
  do_test wal2-6.4.$tn.1 { execsql $S } $res
  do_test wal2-6.4.$tn.2 { set ::locks  } $L
}

db close
tvfs delete

do_test wal2-6.5.1.1 {
  sqlite3 db test.db
  execsql {
    PRAGMA auto_vacuum = 0;
    PRAGMA journal_mode = wal;
    PRAGMA locking_mode = exclusive;
    CREATE TABLE t2(a, b);
  }
} {wal exclusive}
do_test wal2-6.5.1.2 {
  execsql {
    PRAGMA wal_checkpoint;
    INSERT INTO t2 VALUES('I', 'II');
    PRAGMA journal_mode;
  }
} [list 0 [wal_frames db 2 1] [wal_frames db 2 1] wal]
do_test wal2-6.5.2 {
  execsql {
    PRAGMA locking_mode = normal;
    INSERT INTO t2 VALUES('III', 'IV');
    PRAGMA locking_mode = exclusive;
    SELECT * FROM t2;
  }
} {normal exclusive I II III IV}
do_test wal2-6.5.3 {
  execsql { PRAGMA wal_checkpoint }
} [list 0 [wal_frames db 2 2] [wal_frames db 2 2]]
db close

proc lock_control {method filename handle spec} {
  foreach {start n op type} $spec break
  if {$op == "lock"} { return SQLITE_IOERR }
  return SQLITE_OK
}
................................................................................
    catch { db close }
  }
}

#-------------------------------------------------------------------------
# Test that "PRAGMA checkpoint_fullsync" appears to be working.
#
foreach {tn sql} {
  1 { } 
  2 { PRAGMA checkpoint_fullfsync = 1 }
  3 { PRAGMA checkpoint_fullfsync = 0 }
} {





  faultsim_delete_and_reopen

  execsql {PRAGMA auto_vacuum = 0}
  execsql $sql
  do_execsql_test wal2-14.$tn.1 { 
    PRAGMA journal_mode = WAL;
    PRAGMA wal_autocheckpoint = 10;
  } {wal 10}

  unset -nocomplain res
  set res(0,1) {5 0 2 0 2 0}      ;# checkpoint_fullfsync=0 sync=NORMAL
  set res(0,2) {8 0 3 0 5 0}      ;# checkpoint_fullfsync=0 sync=FULL
  set res(1,1) {5 4 2 2 2 2}      ;# checkpoint_fullfsync=1 sync=NORMAL
  set res(1,2) {8 4 3 2 5 2}      ;# checkpoint_fullfsync=1 sync=FULL

  set key1 [db one {PRAGMA checkpoint_fullfsync}]
  set key2 [db one {PRAGMA main.synchronous}]
  set reslist $res($key1,$key2)

  set sqlite_sync_count 0
  set sqlite_fullsync_count 0

  do_execsql_test wal2-14.$tn.2 {

    CREATE TABLE t1(a, b);                -- 2 wal syncs
    INSERT INTO t1 VALUES(1, 2);          -- 1 wal sync
    PRAGMA wal_checkpoint;                -- 1 wal sync, 1 db sync
    BEGIN;
      INSERT INTO t1 VALUES(3, 4);
      INSERT INTO t1 VALUES(5, 6);
    COMMIT;                               -- 1 wal sync
    PRAGMA wal_checkpoint;                -- 1 wal sync, 1 db sync
  } [list 0 [wal_frames db 3 2] [wal_frames db 3 2] \
          0 [wal_frames db 1 1] [wal_frames db 1 1] \
  ]

  do_test wal2-14.$tn.3 {
    cond_incr_sync_count 1
    list $sqlite_sync_count $sqlite_fullsync_count
  } [lrange $reslist 0 1]

  set sqlite_sync_count 0
................................................................................

catch { db close }

# PRAGMA checkpoint_fullsync
# PRAGMA fullfsync
# PRAGMA synchronous
#
foreach {tn settings} {
  1  {0 0 off}    
  2  {0 0 normal}
  3  {0 0 full} 

  4  {0 1 off} 
  5  {0 1 normal} 
  6  {0 1 full}  

  7  {1 0 off}  
  8  {1 0 normal}
  9  {1 0 full} 

  10 {1 1 off} 
  11 {1 1 normal}
  12 {1 1 full} 
} {
  forcedelete test.db

  testvfs tvfs -default 1
  tvfs filter xSync
  tvfs script xSyncCb
  proc xSyncCb {method file fileid flags} {
................................................................................
  sqlite3 db test.db
  do_execsql_test 15.$tn.1 "
    CREATE TABLE t1(x);
    PRAGMA journal_mode = WAL;
    PRAGMA checkpoint_fullfsync = [lindex $settings 0];
    PRAGMA fullfsync = [lindex $settings 1];
    PRAGMA synchronous = [lindex $settings 2];
    SELECT count(*) FROM sqlite_master;
  " {wal 1}

  unset -nocomplain res
  set res(0,0,0) {{0 0}  {0 0}}
  set res(0,0,1) {{0 0}  {2 0}}
  set res(0,0,2) {{1 0}  {2 0}}
  set res(0,1,0) {{0 0}  {0 0}}
  set res(0,1,1) {{0 0}  {0 2}}
  set res(0,1,2) {{0 1}  {0 2}}
  set res(1,0,0) {{0 0}  {0 0}}
  set res(1,0,1) {{0 0}  {0 2}}
  set res(1,0,2) {{1 0}  {0 2}}
  set res(1,1,0) {{0 0}  {0 0}}
  set res(1,1,1) {{0 0}  {0 2}}
  set res(1,1,2) {{0 1}  {0 2}}

  set key1 [db one {PRAGMA checkpoint_fullfsync}]
  set key2 [db one {PRAGMA fullfsync}]
  set key3 [db one {PRAGMA synchronous}]

  set commit_sync [lindex $res($key1,$key2,$key3) 0]
  set ckpt_sync   [lindex $res($key1,$key2,$key3) 1]

  do_test 15.$tn.2 {
    set sync(normal) 0
    set sync(full) 0
    execsql { INSERT INTO t1 VALUES('abc') }
    list $::sync(normal) $::sync(full)
  } $commit_sync

Changes to test/wal3.test.

225
226
227
228
229
230
231
232
233


234
235
236
237
238
239
240
...
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
...
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
...
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644




645
646
647
648
649
650
651
    file delete -force test.db test.db-wal test.db-journal
  
    testvfs T
    T filter {} 
    T script sync_counter
    sqlite3 db test.db -vfs T
  
    execsql "PRAGMA synchronous = $syncmode"
    execsql { PRAGMA journal_mode = WAL }



    set ::syncs [list]
    T filter xSync
    execsql {
      CREATE TABLE x(y);
      INSERT INTO x VALUES('z');
      PRAGMA wal_checkpoint;
................................................................................
do_test wal3-6.1.2 {
  sqlite3 db2 test.db
  sqlite3 db3 test.db
  execsql { BEGIN ; SELECT * FROM t1 } db3
} {o t t f}
do_test wal3-6.1.3 {
  execsql { PRAGMA wal_checkpoint } db2
} {0 7 7}

# At this point the log file has been fully checkpointed. However, 
# connection [db3] holds a lock that prevents the log from being wrapped.
# Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But
# as it is obtaining the lock, [db2] appends to the log file.
#
T filter xShmLock
................................................................................
      BEGIN;
      SELECT * FROM t1;
    }]
  }
}
do_test wal3-6.2.2 {
  execsql { PRAGMA wal_checkpoint }
} {0 7 7}
do_test wal3-6.2.3 {
  set ::R
} {h h l b}
do_test wal3-6.2.4 {
  set sz1 [file size test.db-wal]
  execsql { INSERT INTO t1 VALUES('b', 'c'); }
  set sz2 [file size test.db-wal]
................................................................................

db close
db2 close
T delete

#-------------------------------------------------------------------------
# 
do_test wal3-8.1 {
  file delete -force test.db test.db-journal test.db wal .test.db-conch
  sqlite3 db test.db
  sqlite3 db2 test.db
  execsql {
    PRAGMA auto_vacuum = off;
    PRAGMA journal_mode = WAL;
    CREATE TABLE b(c);
    INSERT INTO b VALUES('Tehran');
    INSERT INTO b VALUES('Qom');
    INSERT INTO b VALUES('Markazi');
    PRAGMA wal_checkpoint;
  }
} {wal 0 9 9}




do_test wal3-8.2 {
  execsql { SELECT * FROM b }
} {Tehran Qom Markazi}
do_test wal3-8.3 {
  db eval { SELECT * FROM b } {
    db eval { INSERT INTO b VALUES('Qazvin') }
    set r [db2 eval { SELECT * FROM b }]







<

>
>







 







|







 







|







 







|










<

|
>
>
>
>







225
226
227
228
229
230
231

232
233
234
235
236
237
238
239
240
241
...
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
...
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
...
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642

643
644
645
646
647
648
649
650
651
652
653
654
655
    file delete -force test.db test.db-wal test.db-journal
  
    testvfs T
    T filter {} 
    T script sync_counter
    sqlite3 db test.db -vfs T
  

    execsql { PRAGMA journal_mode = WAL }
    execsql { SELECT * FROM sqlite_master }
    execsql "PRAGMA synchronous = $syncmode"

    set ::syncs [list]
    T filter xSync
    execsql {
      CREATE TABLE x(y);
      INSERT INTO x VALUES('z');
      PRAGMA wal_checkpoint;
................................................................................
do_test wal3-6.1.2 {
  sqlite3 db2 test.db
  sqlite3 db3 test.db
  execsql { BEGIN ; SELECT * FROM t1 } db3
} {o t t f}
do_test wal3-6.1.3 {
  execsql { PRAGMA wal_checkpoint } db2
} [list 0 [wal_frames db 4 3] [wal_frames db 4 3]]

# At this point the log file has been fully checkpointed. However, 
# connection [db3] holds a lock that prevents the log from being wrapped.
# Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But
# as it is obtaining the lock, [db2] appends to the log file.
#
T filter xShmLock
................................................................................
      BEGIN;
      SELECT * FROM t1;
    }]
  }
}
do_test wal3-6.2.2 {
  execsql { PRAGMA wal_checkpoint }
} [list 0 [wal_frames db 4 3] [wal_frames db 4 3]]
do_test wal3-6.2.3 {
  set ::R
} {h h l b}
do_test wal3-6.2.4 {
  set sz1 [file size test.db-wal]
  execsql { INSERT INTO t1 VALUES('b', 'c'); }
  set sz2 [file size test.db-wal]
................................................................................

db close
db2 close
T delete

#-------------------------------------------------------------------------
# 
do_test wal3-8.1.1 {
  file delete -force test.db test.db-journal test.db wal .test.db-conch
  sqlite3 db test.db
  sqlite3 db2 test.db
  execsql {
    PRAGMA auto_vacuum = off;
    PRAGMA journal_mode = WAL;
    CREATE TABLE b(c);
    INSERT INTO b VALUES('Tehran');
    INSERT INTO b VALUES('Qom');
    INSERT INTO b VALUES('Markazi');

  }
} {wal}

do_test wal3.8.1.2 {
  execsql { PRAGMA wal_checkpoint; }
} [list 0 [wal_frames db 5 4] [wal_frames db 5 4]]
do_test wal3-8.2 {
  execsql { SELECT * FROM b }
} {Tehran Qom Markazi}
do_test wal3-8.3 {
  db eval { SELECT * FROM b } {
    db eval { INSERT INTO b VALUES('Qazvin') }
    set r [db2 eval { SELECT * FROM b }]

Changes to test/wal5.test.

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
251
...
297
298
299
300
301
302
303







304
305
306
307
308
309
310
      sql1 {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
      }
    } {}
    do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5}


    do_test 2.1.$tn.3 { code1 { do_wal_checkpoint db } } {0 5 5}


    do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5}


  }

  do_multiclient_test tn {
    setup_and_attach_aux
    do_test 2.2.$tn.1 {
      execsql {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
        INSERT INTO t2 VALUES(3, 4);
      }
    } {}
    do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7}


    do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
    do_test 2.2.$tn.4 { code1 { do_wal_checkpoint db -mode restart } } {1 5 5}


    do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7}


  }

  do_multiclient_test tn {
    setup_and_attach_aux
    do_test 2.3.$tn.1 {
      execsql {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
      }
    } {}
    do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5}


    do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
    do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {}
    do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {}
    do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7}


    do_test 2.3.$tn.7 { code1 { do_wal_checkpoint db -mode full } } {1 7 5}


    if {$tcl_platform(platform) == "windows"} {
        # on unix, the size_hint is a no-op if no chunk size is set.
        # the windows implementation does not have a similar check,
        # and because of this, the db file size has an extra page.
        do_test 2.3.$tn.8 { file_page_counts } {2 7 2 7}
    } {
        do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7}


    }
  }

  # Check that checkpoints block on the correct locks. And respond correctly
  # if they cannot obtain those locks. There are three locks that a checkpoint
  # may block on (in the following order):
  #
................................................................................
          CREATE TABLE t1(a, b);
          INSERT INTO t1 VALUES(1, 2);
        }
        sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) }
        sql3 { BEGIN; SELECT * FROM t1 }
      } {1 2}








      do_test 2.4.$tn1.$tn.2 {
        code1 { db busy busyhandler }
        code1 { do_wal_checkpoint db -mode [string tolower $checkpoint] }
      } $ckpt_expected
      do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected
    }
  }







|
>
>
|
>
>
|
>
>













|
>
>

|
>
>
|
>
>












|
>
>



|
>
>
|
>
>






|
>
>







 







>
>
>
>
>
>
>







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
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
      sql1 {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
      }
    } {}
    do_test 2.2.$tn.2 { file_page_counts } [
      list 1 [wal_frames db 3 2] 1 [wal_frames db 3 2]
    ]
    do_test 2.1.$tn.3 { code1 { do_wal_checkpoint db } } [
      list 0 [wal_frames db 3 2] [wal_frames db 3 2]
    ]
    do_test 2.1.$tn.4 { file_page_counts } [
      list 2 [wal_frames db 3 2] 2 [wal_frames db 3 2]
    ]
  }

  do_multiclient_test tn {
    setup_and_attach_aux
    do_test 2.2.$tn.1 {
      execsql {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
        INSERT INTO t2 VALUES(3, 4);
      }
    } {}
    do_test 2.2.$tn.2 { file_page_counts } [
      list 1 [wal_frames db 3 2] 1 [wal_frames db 4 3]
    ]
    do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
    do_test 2.2.$tn.4 { code1 { do_wal_checkpoint db -mode restart } } [
      list 1 [wal_frames db 3 2] [wal_frames db 3 2]
    ]
    do_test 2.2.$tn.5 { file_page_counts } [
      list 2 [wal_frames db 3 2] 2 [wal_frames db 4 3]
    ]
  }

  do_multiclient_test tn {
    setup_and_attach_aux
    do_test 2.3.$tn.1 {
      execsql {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
      }
    } {}
    do_test 2.3.$tn.2 { file_page_counts } [
      list 1 [wal_frames db 3 2] 1 [wal_frames db 3 2]
    ]
    do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
    do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {}
    do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {}
    do_test 2.3.$tn.6 { file_page_counts } [
      list 1 [wal_frames db 4 3] 1 [wal_frames db 4 3]
    ]
    do_test 2.3.$tn.7 { code1 { do_wal_checkpoint db -mode full } } [
      list 1 [wal_frames db 4 3] [wal_frames db 3 2]
    ]
    if {$tcl_platform(platform) == "windows"} {
        # on unix, the size_hint is a no-op if no chunk size is set.
        # the windows implementation does not have a similar check,
        # and because of this, the db file size has an extra page.
        do_test 2.3.$tn.8 { file_page_counts } {2 7 2 7}
    } {
        do_test 2.3.$tn.8 { file_page_counts } [
          list 1 [wal_frames db 4 3] 2 [wal_frames db 4 3]
        ]
    }
  }

  # Check that checkpoints block on the correct locks. And respond correctly
  # if they cannot obtain those locks. There are three locks that a checkpoint
  # may block on (in the following order):
  #
................................................................................
          CREATE TABLE t1(a, b);
          INSERT INTO t1 VALUES(1, 2);
        }
        sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) }
        sql3 { BEGIN; SELECT * FROM t1 }
      } {1 2}

      # The value in ckpt_expected assumes that synchronous=FULL. If
      # synchronous=NORMAL, decrease the WAL size by 2 frames.
      if {$tn==1 && [db one {PRAGMA main.synchronous}] == 1} {
        lset ckpt_expected 1 [expr [lindex $ckpt_expected 1] - 2]
        lset ckpt_expected 2 [expr [lindex $ckpt_expected 2] - 2]
      }
  
      do_test 2.4.$tn1.$tn.2 {
        code1 { db busy busyhandler }
        code1 { do_wal_checkpoint db -mode [string tolower $checkpoint] }
      } $ckpt_expected
      do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected
    }
  }

Changes to test/wal_common.tcl.

28
29
30
31
32
33
34














35
36
37
38
39
40
41
  upvar $ckv1 c1
  upvar $ckv2 c2
  foreach {v1 v2} $intlist {
    set c1 [expr {($c1 + $v1 + $c2)&0xFFFFFFFF}]
    set c2 [expr {($c2 + $v2 + $c1)&0xFFFFFFFF}]
  }
}
















# This proc calculates checksums in the same way as those used by SQLite 
# in WAL files. If the $endian argument is "big", then checksums are
# calculated by interpreting data as an array of big-endian integers. If
# it is "little", data is interpreted as an array of little-endian integers.
#







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







28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
  upvar $ckv1 c1
  upvar $ckv2 c2
  foreach {v1 v2} $intlist {
    set c1 [expr {($c1 + $v1 + $c2)&0xFFFFFFFF}]
    set c2 [expr {($c2 + $v2 + $c1)&0xFFFFFFFF}]
  }
}

# If the synchronous mode for the main database of db handle $db
# is either OFF or NORMAL, return $nRight. Otherwise, if it is
# FULL, return $nWrite+$nTrans.
#
proc wal_frames {db nWrite nTrans} {
  set nRet $nWrite
  switch -- [$db one {PRAGMA main.synchronous}] {
    0 { }
    1 { }
    default { incr nRet $nTrans }
  }
  set nRet
}


# This proc calculates checksums in the same way as those used by SQLite 
# in WAL files. If the $endian argument is "big", then checksums are
# calculated by interpreting data as an array of big-endian integers. If
# it is "little", data is interpreted as an array of little-endian integers.
#

Changes to test/walmode.test.

55
56
57
58
59
60
61







62

63
64
65
66
67
68
69
  execsql { PRAGMA page_size = 1024 }
  execsql { PRAGMA journal_mode = wal }
} {wal}
do_test walmode-1.2 {
  file size test.db
} {1024}








set expected_sync_count 3

if {$::tcl_platform(platform)!="windows"} {
  ifcapable dirsync {
    incr expected_sync_count
  }
}
do_test walmode-1.3 {
  set sqlite_sync_count







>
>
>
>
>
>
>
|
>







55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
  execsql { PRAGMA page_size = 1024 }
  execsql { PRAGMA journal_mode = wal }
} {wal}
do_test walmode-1.2 {
  file size test.db
} {1024}

# Determine how many sync() calls to expect from the "journal_mode=WAL" 
# command above. Note that if DEFAULT_WAL_SAFETYLEVEL is defined, the
# safety-level may have been modified while compiling the "journal_mode=WAL"
# statement.
switch -- [db eval {PRAGMA main.synchronous}] {
  0        { set expected_sync_count 0 }
  1        { set expected_sync_count 2 }
  default  { set expected_sync_count 3 }
}
if {$::tcl_platform(platform)!="windows"} {
  ifcapable dirsync {
    incr expected_sync_count
  }
}
do_test walmode-1.3 {
  set sqlite_sync_count