/ Check-in [4104b175]
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:Add further pager tests.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 4104b175a8c3560a7680f3d2b54416821bb2e19d
User & Date: dan 2010-06-21 07:45:47
Context
2010-06-21
12:34
Change things so that journal2.test works with ENABLE_ATOMIC_WRITE. Closed-Leaf check-in: a64d96db user: dan tags: experimental
07:45
Add further pager tests. check-in: 4104b175 user: dan tags: experimental
06:00
Merge latest trunk change. check-in: f6d26e07 user: dan tags: experimental
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/pager1.test.

10
11
12
13
14
15
16

17
18
19
20
21
22
23
..
24
25
26
27
28
29
30

31
32
33
34
35
36
37
38
39
40
41
42
43
44
...
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
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
...
616
617
618
619
620
621
622










































































623
624
#***********************************************************************
#

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


#
# pager1-1.*: Test inter-process locking (clients in multiple processes).
#
# pager1-2.*: Test intra-process locking (multiple clients in this process).
#
# pager1-3.*: Savepoint related tests.
................................................................................
#
# pager1-4.*: Hot-journal related tests.
#
# pager1-5.*: Cases related to multi-file commits.
#
# pager1-6.*: Cases related to "PRAGMA max_page_count"
#


proc do_execsql_test {testname sql result} {
  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
}
proc do_catchsql_test {testname sql result} {
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}

set a_string_counter 1
proc a_string {n} {
  global a_string_counter
  incr a_string_counter
  string range [string repeat "${a_string_counter}." $n] 1 $n
}
................................................................................
do_catchsql_test pager1-3.3 {
    INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1
} {1 {constraint failed}}
do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}
do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}
do_execsql_test pager1-3.6 { COMMIT } {}

set otn 0
testvfs tv -default 1
foreach code [list {
  set s 512
} {
  set s 1024
  set sql { PRAGMA journal_mode = memory }
} {
  set s 1024
  set sql { 
    PRAGMA journal_mode = memory;
    PRAGMA locking_mode = exclusive;
  }
} {
  set s 2048
  tv devchar safe_append
} {
  set s 4096
} {
  set s 4096
  set sql { PRAGMA journal_mode = WAL }
} {
  set s 8192
  set sql { PRAGMA synchronous = off }
}] {

  incr otn
  set sql ""
  tv devchar {}
  eval $code
  tv sectorsize $s
  
  do_test pager1-3.7.$otn.0 {
    faultsim_delete_and_reopen
    execsql $sql
    execsql {
      PRAGMA cache_size = 10;
      CREATE TABLE t1(i INTEGER PRIMARY KEY, j blob);
    }
  } {}

  set tn 0
  set lowpoint 0
  foreach x {
    100 x 0 100
  x
    70 22 96 59 96 50 22 56 21 16 37 64 43 40  0 38 22 38 55  0  6   
    43 62 32 93 54 18 13 29 45 66 29 25 61 31 53 82 75 25 96 86 10 69   
     2 29  6 60 80 95 42 82 85 50 68 96 90 39 78 69 87 97 48 74 65 43   
  x
    86 34 26 50 41 85 58 44 89 22  6 51 45 46 58 32 97  6  1 12 32  2   
    69 39 48 71 33 31  5 58 90 43 24 54 12  9 18 57  4 38 91 42 27 45   
    50 38 56 29 10  0 26 37 83  1 78 15 47 30 75 62 46 29 68  5 30  4   
    27 96 33 95 79 75 56 10 29 70 32 75 52 88  5 36 50 57 46 63 88 65   
  x
    44 95 64 20 24 35 69 61 61  2 35 92 42 46 23 98 78  1 38 72 79 35   
    94 37 13 59  5 93 27 58 80 75 58  7 67 13 10 76 84  4  8 70 81 45   
     8 41 98  5 60 26 92 29 91 90  2 62 40  4  5 22 80 15 83 76 52 88   
    29  5 68 73 72  7 54 17 89 32 81 94 51 28 53 71  8 42 54 59 70 79   
  x
  } {
    incr tn
    set now [db one {SELECT count(i) FROM t1}]
    if {$x == "x"} {
      execsql { COMMIT ; BEGIN }
      set lowpoint $now
      do_test pager1.3.7.$otn.$tn { 
        sqlite3 db2 test.db
        execsql {
          SELECT COALESCE(max(i), 0) FROM t1;
          PRAGMA integrity_check;
        } 
      } [list $lowpoint ok]
      db2 close
    } else {
      if {$now > $x } {
        if { $x>=$lowpoint } {
          execsql "ROLLBACK TO sp_$x"
        } else {
          execsql "DELETE FROM t1 WHERE i>$x"
          set lowpoint $x
        }
      } elseif {$now < $x} {
        for {set k $now} {$k < $x} {incr k} {
          execsql "SAVEPOINT sp_$k"
          execsql { INSERT INTO t1(j) VALUES(randomblob(1500)) }
        }
      }
      do_execsql_test pager1.3.7.$otn.$tn { 
        SELECT COALESCE(max(i), 0) FROM t1;
        PRAGMA integrity_check;
      } [list $x ok]
    }
  }
}
db close
tv delete

#-------------------------------------------------------------------------
# Hot journal rollback related test cases.
#
# pager1.4.1.*: Test that the pager module deletes very small invalid
#               journal files.
#
# pager1.4.2.*: Test that if the master journal pointer at the end of a
................................................................................
} {10}
do_test pager1-6.2 {
  catchsql {
    CREATE TABLE t11(a, b);
  }
} {1 {database or disk is full}}











































































finish_test








>







 







>
|
<
<
<
<
<
<







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







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


10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
25
26
27
28
29
30
31
32
33






34
35
36
37
38
39
40
...
208
209
210
211
212
213
214


































































































215
216
217
218
219
220
221
...
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
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
584
585
586
587
588
589
590
591
592
593
594
595
596
#***********************************************************************
#

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

#
# pager1-1.*: Test inter-process locking (clients in multiple processes).
#
# pager1-2.*: Test intra-process locking (multiple clients in this process).
#
# pager1-3.*: Savepoint related tests.
................................................................................
#
# pager1-4.*: Hot-journal related tests.
#
# pager1-5.*: Cases related to multi-file commits.
#
# pager1-6.*: Cases related to "PRAGMA max_page_count"
#
# pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE"
#







set a_string_counter 1
proc a_string {n} {
  global a_string_counter
  incr a_string_counter
  string range [string repeat "${a_string_counter}." $n] 1 $n
}
................................................................................
do_catchsql_test pager1-3.3 {
    INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1
} {1 {constraint failed}}
do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}
do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}
do_execsql_test pager1-3.6 { COMMIT } {}



































































































#-------------------------------------------------------------------------
# Hot journal rollback related test cases.
#
# pager1.4.1.*: Test that the pager module deletes very small invalid
#               journal files.
#
# pager1.4.2.*: Test that if the master journal pointer at the end of a
................................................................................
} {10}
do_test pager1-6.2 {
  catchsql {
    CREATE TABLE t11(a, b);
  }
} {1 {database or disk is full}}


#-------------------------------------------------------------------------
# The following tests work with "PRAGMA journal_mode=TRUNCATE" and
# "PRAGMA locking_mode=EXCLUSIVE".
#
# Each test is specified with 5 variables. As follows:
#
#   $tn:  Test Number. Used as part of the [do_test] test names.
#   $sql: SQL to execute.
#   $res: Expected result of executing $sql.
#   $js:  The expected size of the journal file, in bytes, after executing
#         the SQL script. Or -1 if the journal is not expected to exist.
#   $ws:  The expected size of the WAL file, in bytes, after executing
#         the SQL script. Or -1 if the WAL is not expected to exist.
#
faultsim_delete_and_reopen
foreach {tn sql res js ws} [subst {

  1  {
    CREATE TABLE t1(a, b);
    PRAGMA auto_vacuum=OFF;
    PRAGMA synchronous=NORMAL;
    PRAGMA page_size=1024;
    PRAGMA locking_mode=EXCLUSIVE;
    PRAGMA journal_mode=TRUNCATE;
    INSERT INTO t1 VALUES(1, 2);
  } {exclusive truncate} 0 -1

  2  {
    BEGIN IMMEDIATE;
      SELECT * FROM t1;
    COMMIT;
  } {1 2} 0 -1

  3  {
    BEGIN;
      SELECT * FROM t1;
    COMMIT;
  } {1 2} 0 -1

  4  { PRAGMA journal_mode = WAL }    wal    -1 -1
  5  { INSERT INTO t1 VALUES(3, 4) }  {}     -1 [wal_file_size 1 1024]
  6  { PRAGMA locking_mode = NORMAL } normal -1 [wal_file_size 1 1024]
  7  { INSERT INTO t1 VALUES(5, 6); } {}     -1 [wal_file_size 2 1024]

  8  { PRAGMA journal_mode = TRUNCATE } truncate          0 -1
  9  { INSERT INTO t1 VALUES(7, 8) }    {}                0 -1
  10 { SELECT * FROM t1 }               {1 2 3 4 5 6 7 8} 0 -1

}] {
  do_execsql_test pager1-7.1.$tn.1 $sql $res
  catch { set J -1 ; set J [file size test.db-journal] }
  catch { set W -1 ; set W [file size test.db-wal] }
  do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws]
}

do_test pager1-8.1 {
  faultsim_delete_and_reopen
  db close
  sqlite3 db :memory:
  execsql {
    CREATE TABLE x1(x);
    INSERT INTO x1 VALUES('Charles');
    INSERT INTO x1 VALUES('James');
    INSERT INTO x1 VALUES('Mary');
    SELECT * FROM x1;
  }
} {Charles James Mary}
do_test pager1-8.2 {
  db close
  sqlite3 db :memory:
  catchsql { SELECT * FROM x1 }
} {1 {no such table: x1}}

finish_test

Added test/pager2.test.











































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
# 2010 June 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

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

set otn 0
testvfs tv -default 1
foreach code [list {
  set s 512
} {
  set s 1024
  set sql { PRAGMA journal_mode = memory }
} {
  set s 1024
  set sql { 
    PRAGMA journal_mode = memory;
    PRAGMA locking_mode = exclusive;
  }
} {
  set s 2048
  tv devchar safe_append
} {
  set s 4096
} {
  set s 4096
  set sql { PRAGMA journal_mode = WAL }
} {
  set s 8192
  set sql { PRAGMA synchronous = off }
}] {

  incr otn
  set sql ""
  tv devchar {}
  eval $code
  tv sectorsize $s
  
  do_test pager2-1.$otn.0 {
    faultsim_delete_and_reopen
    execsql $sql
    execsql {
      PRAGMA cache_size = 10;
      CREATE TABLE t1(i INTEGER PRIMARY KEY, j blob);
    }
  } {}

  set tn 0
  set lowpoint 0
  foreach x {
    100 x 0 100
  x
    70 22 96 59 96 50 22 56 21 16 37 64 43 40  0 38 22 38 55  0  6   
    43 62 32 93 54 18 13 29 45 66 29 25 61 31 53 82 75 25 96 86 10 69   
     2 29  6 60 80 95 42 82 85 50 68 96 90 39 78 69 87 97 48 74 65 43   
  x
    86 34 26 50 41 85 58 44 89 22  6 51 45 46 58 32 97  6  1 12 32  2   
    69 39 48 71 33 31  5 58 90 43 24 54 12  9 18 57  4 38 91 42 27 45   
    50 38 56 29 10  0 26 37 83  1 78 15 47 30 75 62 46 29 68  5 30  4   
    27 96 33 95 79 75 56 10 29 70 32 75 52 88  5 36 50 57 46 63 88 65   
  x
    44 95 64 20 24 35 69 61 61  2 35 92 42 46 23 98 78  1 38 72 79 35   
    94 37 13 59  5 93 27 58 80 75 58  7 67 13 10 76 84  4  8 70 81 45   
     8 41 98  5 60 26 92 29 91 90  2 62 40  4  5 22 80 15 83 76 52 88   
    29  5 68 73 72  7 54 17 89 32 81 94 51 28 53 71  8 42 54 59 70 79   
  x
  } {
    incr tn
    set now [db one {SELECT count(i) FROM t1}]
    if {$x == "x"} {
      execsql { COMMIT ; BEGIN }
      set lowpoint $now
      do_test pager2.1.$otn.$tn { 
        sqlite3 db2 test.db
        execsql {
          SELECT COALESCE(max(i), 0) FROM t1;
          PRAGMA integrity_check;
        } 
      } [list $lowpoint ok]
      db2 close
    } else {
      if {$now > $x } {
        if { $x>=$lowpoint } {
          execsql "ROLLBACK TO sp_$x"
        } else {
          execsql "DELETE FROM t1 WHERE i>$x"
          set lowpoint $x
        }
      } elseif {$now < $x} {
        for {set k $now} {$k < $x} {incr k} {
          execsql "SAVEPOINT sp_$k"
          execsql { INSERT INTO t1(j) VALUES(randomblob(1500)) }
        }
      }
      do_execsql_test pager2.1.$otn.$tn { 
        SELECT COALESCE(max(i), 0) FROM t1;
        PRAGMA integrity_check;
      } [list $x ok]
    }
  }
}
db close
tv delete


finish_test

Changes to test/pagerfault.test.

236
237
238
239
240
241
242
























243
244
245
246
247
248
249
      INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
      CREATE TABLE aux.t2 AS SELECT * FROM t1;
    COMMIT;
  }
} -test {
  faultsim_test_result {0 {}}
}

























# The following was an attempt to get a bitvec malloc to fail. Didn't work.
#
# do_test pagerfault-6-pre1 {
#   faultsim_delete_and_reopen
#   execsql {
#     CREATE TABLE t1(x, y, UNIQUE(x, y));







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







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
272
273
      INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
      CREATE TABLE aux.t2 AS SELECT * FROM t1;
    COMMIT;
  }
} -test {
  faultsim_test_result {0 {}}
}

#-------------------------------------------------------------------------
# Test fault-injection as part of a commit when using 
# journal_mode=TRUNCATE.
#
do_test pagerfault-6-pre1 {
  faultsim_delete_and_reopen
  db func a_string a_string
  execsql {
    CREATE TABLE t1(a UNIQUE, b UNIQUE);
    INSERT INTO t1 VALUES(a_string(200), a_string(300));
  }
  faultsim_save_and_close
} {}
do_faultsim_test pagerfault-6.1 -prep {
  faultsim_restore_and_reopen
  db func a_string a_string
  execsql { PRAGMA journal_mode = TRUNCATE }
} -body {
  execsql { INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1 }
} -test {
  faultsim_test_result {0 {}}
  faultsim_integrity_check
}

# The following was an attempt to get a bitvec malloc to fail. Didn't work.
#
# do_test pagerfault-6-pre1 {
#   faultsim_delete_and_reopen
#   execsql {
#     CREATE TABLE t1(x, y, UNIQUE(x, y));

Changes to test/permutations.test.

165
166
167
168
169
170
171

172
173
174
175
176
177
178
  walfault.test
} 

test_suite "coverage-pager" -description {
  Coverage tests for file pager.c.
} -files {
  pager1.test

  pagerfault.test
  journal2.test
} 


lappend ::testsuitelist xxx
#-------------------------------------------------------------------------







>







165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
  walfault.test
} 

test_suite "coverage-pager" -description {
  Coverage tests for file pager.c.
} -files {
  pager1.test
  pager2.test
  pagerfault.test
  journal2.test
} 


lappend ::testsuitelist xxx
#-------------------------------------------------------------------------

Changes to test/tester.tcl.

45
46
47
48
49
50
51


52
53
54
55
56
57
58
...
312
313
314
315
316
317
318








319
320
321
322
323
324
325
#
# Commands to run test cases:
#
#      do_ioerr_test          TESTNAME ARGS...
#      crashsql               ARGS...
#      integrity_check        TESTNAME ?DB?
#      do_test                TESTNAME SCRIPT EXPECTED


#
# Commands providing a lower level interface to the global test counters:
#
#      set_test_counter       COUNTER ?VALUE?
#      omit_test              TESTNAME REASON
#      fail_test              TESTNAME
#      incr_ntest
................................................................................
    puts "\nExpected: \[$expected\]\n     Got: \[$result\]"
    fail_test $name
  } else {
    puts " Ok"
  }
  flush stdout
}









# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {
  puts -nonewline [format {%-21.21s } $name...]
  flush stdout







>
>







 







>
>
>
>
>
>
>
>







45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
...
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
#
# Commands to run test cases:
#
#      do_ioerr_test          TESTNAME ARGS...
#      crashsql               ARGS...
#      integrity_check        TESTNAME ?DB?
#      do_test                TESTNAME SCRIPT EXPECTED
#      do_execsql_test        TESTNAME SQL EXPECTED
#      do_catchsql_test       TESTNAME SQL EXPECTED
#
# Commands providing a lower level interface to the global test counters:
#
#      set_test_counter       COUNTER ?VALUE?
#      omit_test              TESTNAME REASON
#      fail_test              TESTNAME
#      incr_ntest
................................................................................
    puts "\nExpected: \[$expected\]\n     Got: \[$result\]"
    fail_test $name
  } else {
    puts " Ok"
  }
  flush stdout
}
    
proc do_execsql_test {testname sql result} {
  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
}
proc do_catchsql_test {testname sql result} {
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}


# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {
  puts -nonewline [format {%-21.21s } $name...]
  flush stdout