SQLite

Check-in [c6e7595092]
Login

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

Overview
Comment:Add tests to pager1.test and pagerfault.test.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c6e75950928954fbb1590f952e3cdbcb0753b745
User & Date: dan 2010-07-01 19:01:56.000
Context
2010-07-01
19:45
If you delete a database file but not its *-wal file, then open the database (thus creating a new empty database file) the *-wal file is automatically deleted. (check-in: 95c69dfbdd user: drh tags: trunk)
19:01
Add tests to pager1.test and pagerfault.test. (check-in: c6e7595092 user: dan tags: trunk)
15:09
Add pager test cases. Change a condition in pager.c to NEVER(). (check-in: a8f6341d3b user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/pager1.test.
1218
1219
1220
1221
1222
1223
1224









1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241

1242
1243
1244

1245



1246
1247
1248
1249















































































1250
1251
1252
1253
1254
1255
1256
} {SQLITE_DONE SQLITE_OK}
do_test pager1-9.2.3 {
 db one {SELECT md5sum(a, b) FROM ab}
} [db2 one {SELECT md5sum(a, b) FROM ab}]
do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128}
db close
db2 close










#-------------------------------------------------------------------------
# Test that regardless of the value returned by xSectorSize(), the
# minimum effective sector-size is 512 and the maximum 65536 bytes.
#
testvfs tv -default 1
foreach sectorsize {
    32   64   128   256   512   1024   2048 
    4096 8192 16384 32768 65536 131072 262144
} {
  tv sectorsize $sectorsize
  set eff $sectorsize
  if {$sectorsize < 512}   { set eff 512 }
  if {$sectorsize > 65536} { set eff 65536 }

  do_test pager1-10.$sectorsize {
    faultsim_delete_and_reopen

    execsql {
      PRAGMA journal_mode = PERSIST;
      PRAGMA page_size = 1024;

      CREATE TABLE t1(a, b);



    }
    file size test.db-journal
  } [expr $sectorsize > 65536 ? 65536 : $sectorsize]
}















































































db close
tv delete

testvfs tv -default 1
faultsim_delete_and_reopen
db func a_string a_string
do_execsql_test pager1-11.1 {







>
>
>
>
>
>
>
>
>















|

>



>
|
>
>
>



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







1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
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
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
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
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
} {SQLITE_DONE SQLITE_OK}
do_test pager1-9.2.3 {
 db one {SELECT md5sum(a, b) FROM ab}
} [db2 one {SELECT md5sum(a, b) FROM ab}]
do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128}
db close
db2 close

proc recursive_select {id table {script {}}} {
  set cnt 0
  db eval "SELECT rowid, * FROM $table WHERE rowid = ($id-1)" {
    recursive_select $rowid $table $script
    incr cnt
  }
  if {$cnt==0} { eval $script }
}

#-------------------------------------------------------------------------
# Test that regardless of the value returned by xSectorSize(), the
# minimum effective sector-size is 512 and the maximum 65536 bytes.
#
testvfs tv -default 1
foreach sectorsize {
    32   64   128   256   512   1024   2048 
    4096 8192 16384 32768 65536 131072 262144
} {
  tv sectorsize $sectorsize
  set eff $sectorsize
  if {$sectorsize < 512}   { set eff 512 }
  if {$sectorsize > 65536} { set eff 65536 }

  do_test pager1-10.$sectorsize.1 {
    faultsim_delete_and_reopen
    db func a_string a_string
    execsql {
      PRAGMA journal_mode = PERSIST;
      PRAGMA page_size = 1024;
      BEGIN;
        CREATE TABLE t1(a, b);
        CREATE TABLE t2(a, b);
        CREATE TABLE t3(a, b);
      COMMIT;
    }
    file size test.db-journal
  } [expr $sectorsize > 65536 ? 65536 : $sectorsize]

  do_test pager1-10.$sectorsize.2 {
    execsql { 
      INSERT INTO t3 VALUES(a_string(300), a_string(300));
      INSERT INTO t3 SELECT * FROM t3;        /*  2 */
      INSERT INTO t3 SELECT * FROM t3;        /*  4 */
      INSERT INTO t3 SELECT * FROM t3;        /*  8 */
      INSERT INTO t3 SELECT * FROM t3;        /* 16 */
      INSERT INTO t3 SELECT * FROM t3;        /* 32 */
    }
  } {}

  do_test pager1-10.$sectorsize.3 {
    db close
    sqlite3 db test.db
    execsql { 
      PRAGMA cache_size = 10;
      BEGIN;
    }
    recursive_select 32 t3 {db eval "INSERT INTO t2 VALUES(1, 2)"}
    execsql {
      COMMIT;
      SELECT * FROM t2;
    }
  } {1 2}

  do_test pager1-10.$sectorsize.4 {
    execsql {
      CREATE TABLE t6(a, b);
      CREATE TABLE t7(a, b);
      CREATE TABLE t5(a, b);
      DROP TABLE t6;
      DROP TABLE t7;
    }
    if {$eff==65536} breakpoint
    execsql {
      BEGIN;
        CREATE TABLE t6(a, b);
    }
    recursive_select 32 t3 {db eval "INSERT INTO t5 VALUES(1, 2)"}
    execsql {
      COMMIT;
      SELECT * FROM t5;
    }
  } {1 2}
  
}
db close

tv sectorsize 4096
do_test pager1.10.x.1 {
  faultsim_delete_and_reopen
  execsql {
    PRAGMA page_size = 1024;
    CREATE TABLE t1(x);
  }
  for {set i 0} {$i<30} {incr i} {
    execsql { INSERT INTO t1 VALUES(zeroblob(900)) }
  }
  file size test.db
} {32768}
do_test pager1.10.x.2 {
  execsql {
    CREATE TABLE t2(x);
    DROP TABLE t2;
  }
  file size test.db
} {33792}
do_test pager1.10.x.3 {
  execsql {
    BEGIN;
    CREATE TABLE t2(x);
  }
  recursive_select 30 t1
  execsql {
    CREATE TABLE t3(x);
    COMMIT;
  }
} {}

db close
tv delete

testvfs tv -default 1
faultsim_delete_and_reopen
db func a_string a_string
do_execsql_test pager1-11.1 {
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
  testvfs tv -noshm 1
  sqlite3 db2 test.db -vfs tv
  catchsql { SELECT * FROM ko } db2
} {1 {unable to open database file}}
db2 close
tv delete
do_test pager1-21.2 {
breakpoint
  testvfs tv -iversion 1
  sqlite3 db2 test.db -vfs tv
  catchsql { SELECT * FROM ko } db2
} {1 {unable to open database file}}
db2 close
tv delete








<







1889
1890
1891
1892
1893
1894
1895

1896
1897
1898
1899
1900
1901
1902
  testvfs tv -noshm 1
  sqlite3 db2 test.db -vfs tv
  catchsql { SELECT * FROM ko } db2
} {1 {unable to open database file}}
db2 close
tv delete
do_test pager1-21.2 {

  testvfs tv -iversion 1
  sqlite3 db2 test.db -vfs tv
  catchsql { SELECT * FROM ko } db2
} {1 {unable to open database file}}
db2 close
tv delete

1852
1853
1854
1855
1856
1857
1858




1859
1860
1861
1862
1863
1864
1865
#   pager1-23.3.*: Same test as above, but while a reserved lock is held
#                  on the database file.
#
#   pager1-23.4.*: And, for fun, while holding an exclusive lock.
#
#   pager1-23.5.*: Try to set various different journal modes with an
#                  in-memory database (only MEMORY and OFF should work).




#
do_test pager1-23.1.1 {
  faultsim_delete_and_reopen
  execsql {
    PRAGMA journal_mode = PERSIST;
    CREATE TABLE t1(a, b);
  }







>
>
>
>







1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
#   pager1-23.3.*: Same test as above, but while a reserved lock is held
#                  on the database file.
#
#   pager1-23.4.*: And, for fun, while holding an exclusive lock.
#
#   pager1-23.5.*: Try to set various different journal modes with an
#                  in-memory database (only MEMORY and OFF should work).
#
#   pager1-23.6.*: Try to set locking_mode=normal on an in-memory database
#                  (doesn't work - in-memory databases always use
#                  locking_mode=exclusive).
#
do_test pager1-23.1.1 {
  faultsim_delete_and_reopen
  execsql {
    PRAGMA journal_mode = PERSIST;
    CREATE TABLE t1(a, b);
  }
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952

1953









































1954







1955










































    execsql "PRAGMA journal_mode = $mode"
  } [if $possible {list $mode} {list off}]
  do_test pager1-23.5.$tn.2 {
    execsql "PRAGMA journal_mode = memory"
    execsql "PRAGMA journal_mode = $mode"
  } [if $possible {list $mode} {list memory}]
}
do_test pager1-23.8.1 {
  execsql {PRAGMA locking_mode = normal}
} {exclusive}
do_test pager1-23.8.2 {
  execsql {PRAGMA locking_mode = exclusive}
} {exclusive}
do_test pager1-23.8.3 {
  execsql {PRAGMA locking_mode}
} {exclusive}
do_test pager1-23.8.4 {
  execsql {PRAGMA main.locking_mode}
} {exclusive}












































finish_test

























































|


|


|


|



>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
    execsql "PRAGMA journal_mode = $mode"
  } [if $possible {list $mode} {list off}]
  do_test pager1-23.5.$tn.2 {
    execsql "PRAGMA journal_mode = memory"
    execsql "PRAGMA journal_mode = $mode"
  } [if $possible {list $mode} {list memory}]
}
do_test pager1-23.6.1 {
  execsql {PRAGMA locking_mode = normal}
} {exclusive}
do_test pager1-23.6.2 {
  execsql {PRAGMA locking_mode = exclusive}
} {exclusive}
do_test pager1-23.6.3 {
  execsql {PRAGMA locking_mode}
} {exclusive}
do_test pager1-23.6.4 {
  execsql {PRAGMA main.locking_mode}
} {exclusive}

#-------------------------------------------------------------------------
#
do_test pager1-24.1.1 {
  faultsim_delete_and_reopen
  db func a_string a_string
  execsql {
    PRAGMA cache_size = 10;
    PRAGMA auto_vacuum = FULL;
    CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z));
    CREATE TABLE x2(x, y, z, PRIMARY KEY(y, z));
    INSERT INTO x2 VALUES(a_string(400), a_string(500), a_string(600));
    INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
    INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
    INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
    INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
    INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
    INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
    INSERT INTO x1 SELECT * FROM x2;
  }
} {}
proc recursive_select {id {script {}}} {
  set cnt 0
  db eval {SELECT rowid, x,y,z FROM x2 WHERE rowid = ($id-1)} {
    recursive_select $rowid $script
    incr cnt
  }
  if {$cnt==0} { eval $script }
}
do_test pager1-24.1.2 {
  execsql {
    BEGIN;
      DELETE FROM x1 WHERE rowid<32;
  }
  recursive_select 64
} {}
do_test pager1-24.1.3 {
  execsql { 
      UPDATE x1 SET z = a_string(300) WHERE rowid>40;
    COMMIT;
    PRAGMA integrity_check;
    SELECT count(*) FROM x1;
  }
} {ok 33}

do_test pager1-24.1.4 {
  execsql {
    DELETE FROM x1;
    INSERT INTO x1 SELECT * FROM x2;
    BEGIN;
      DELETE FROM x1 WHERE rowid<32;
      UPDATE x1 SET z = a_string(299) WHERE rowid>40;
  }
  recursive_select 64 {db eval COMMIT}
  execsql {
    PRAGMA integrity_check;
    SELECT count(*) FROM x1;
  }
} {ok 33}

do_test pager1-24.1.5 {
  execsql {
    DELETE FROM x1;
    INSERT INTO x1 SELECT * FROM x2;
  }
  recursive_select 64 { db eval {CREATE TABLE x3(x, y, z)} }
  execsql { SELECT * FROM x3 }
} {}

#-------------------------------------------------------------------------
#
do_test pager1-25-1 {
  faultsim_delete_and_reopen
  execsql {
    BEGIN;
      SAVEPOINT abc;
        CREATE TABLE t1(a, b);
      ROLLBACK TO abc;
    COMMIT;
  }
  db close
} {}
breakpoint
do_test pager1-25-2 {
  faultsim_delete_and_reopen
  execsql {
    SAVEPOINT abc;
      CREATE TABLE t1(a, b);
    ROLLBACK TO abc;
    COMMIT;
  }
  db close
} {}

finish_test
Changes to test/pagerfault.test.
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
  faultsim_restore_and_reopen
} -body {
  execsql { CREATE TABLE xx(a, b) }
} -test {
  faultsim_test_result {0 {}}
}


#---------------------------------------------------------------------------
# Test fault injection into a small backup operation.
#
do_test pagerfault-14-pre1 {
  faultsim_delete_and_reopen
  db func a_string a_string;
  execsql {







<







610
611
612
613
614
615
616

617
618
619
620
621
622
623
  faultsim_restore_and_reopen
} -body {
  execsql { CREATE TABLE xx(a, b) }
} -test {
  faultsim_test_result {0 {}}
}


#---------------------------------------------------------------------------
# Test fault injection into a small backup operation.
#
do_test pagerfault-14-pre1 {
  faultsim_delete_and_reopen
  db func a_string a_string;
  execsql {
641
642
643
644
645
646
647


















648
649
650
651
652
653
654
} -test {
  faultsim_test_result {0 {}} {1 {}} {1 {SQL logic error or missing database}}
}
do_faultsim_test pagerfault-14b -prep {
  faultsim_restore_and_reopen
  sqlite3 db2 ""
  db2 eval { PRAGMA page_size = 4096; CREATE TABLE xx(a) }


















} -body {
  sqlite3_backup B db2 main db main
  B step 200
  set rc [B finish]
  if {[string match SQLITE_IOERR_* $rc]} {set rc SQLITE_IOERR}
  if {$rc != "SQLITE_OK"} { error [sqlite3_test_errstr $rc] }
  set {} {}







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







640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
} -test {
  faultsim_test_result {0 {}} {1 {}} {1 {SQL logic error or missing database}}
}
do_faultsim_test pagerfault-14b -prep {
  faultsim_restore_and_reopen
  sqlite3 db2 ""
  db2 eval { PRAGMA page_size = 4096; CREATE TABLE xx(a) }
} -body {
  sqlite3_backup B db2 main db main
  B step 200
  set rc [B finish]
  if {[string match SQLITE_IOERR_* $rc]} {set rc SQLITE_IOERR}
  if {$rc != "SQLITE_OK"} { error [sqlite3_test_errstr $rc] }
  set {} {}
} -test {
  faultsim_test_result {0 {}}
}
do_faultsim_test pagerfault-14c -prep {
  faultsim_restore_and_reopen
  sqlite3 db2 test.db2
  db2 eval { 
    PRAGMA synchronous = off; 
    PRAGMA page_size = 4096; 
    CREATE TABLE xx(a);
  }
} -body {
  sqlite3_backup B db2 main db main
  B step 200
  set rc [B finish]
  if {[string match SQLITE_IOERR_* $rc]} {set rc SQLITE_IOERR}
  if {$rc != "SQLITE_OK"} { error [sqlite3_test_errstr $rc] }
  set {} {}
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
} -body {
  execsql { PRAGMA journal_mode = delete }
} -test {
  faultsim_test_result {0 delete}
  faultsim_integrity_check
}

}

do_faultsim_test pagerfault-19a -prep {
  sqlite3 db :memory:
  db func a_string a_string
  execsql {
    PRAGMA auto_vacuum = FULL;
    BEGIN;
      CREATE TABLE t1(a, b);







<
<







832
833
834
835
836
837
838


839
840
841
842
843
844
845
} -body {
  execsql { PRAGMA journal_mode = delete }
} -test {
  faultsim_test_result {0 delete}
  faultsim_integrity_check
}



do_faultsim_test pagerfault-19a -prep {
  sqlite3 db :memory:
  db func a_string a_string
  execsql {
    PRAGMA auto_vacuum = FULL;
    BEGIN;
      CREATE TABLE t1(a, b);
871
872
873
874
875
876
877



878
879
880
881


882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900

901
902
903
904









905




906






907
908
909

910
    SELECT * FROM t4;
    SELECT * FROM t6;
  }
} -test {
  faultsim_test_result {0 {3 5 7}}
}




do_test pagerfault-20-pre1 {
  faultsim_delete_and_reopen
  db func a_string a_string
  execsql {


    CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z));
    INSERT INTO x1 VALUES(a_string(400), a_string(500), a_string(600));
    INSERT INTO x1 SELECT a_string(600), a_string(400), a_string(500) FROM x1;
    INSERT INTO x1 SELECT a_string(500), a_string(600), a_string(400) FROM x1;
    INSERT INTO x1 SELECT a_string(400), a_string(500), a_string(600) FROM x1;
    INSERT INTO x1 SELECT a_string(600), a_string(400), a_string(500) FROM x1;
    INSERT INTO x1 SELECT a_string(500), a_string(600), a_string(400) FROM x1;
    INSERT INTO x1 SELECT a_string(400), a_string(500), a_string(600) FROM x1;
  }
  faultsim_save_and_close
} {}
do_faultsim_test pagerfault-20 -prep {
  faultsim_restore_and_reopen
  db func a_string a_string
} -body {
  execsql { 
    BEGIN;
    UPDATE x1 SET z = a_string(300);
    DELETE FROM x1 WHERE rowid<32;

    COMMIT;
  }
} -test {
  faultsim_test_result {0 {}}









  faultsim_integrity_check




  set nRow [db one {SELECT count(*) FROM x1}]






  if {$nRow!=33 && $nRow!=64} {error "Wrong number of rows $nRow"}
}


finish_test







>
>
>


<

>
>
|
<
<
<
<
<
<
<





<



|
|
>




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


>

886
887
888
889
890
891
892
893
894
895
896
897

898
899
900
901







902
903
904
905
906

907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
    SELECT * FROM t4;
    SELECT * FROM t6;
  }
} -test {
  faultsim_test_result {0 {3 5 7}}
}

#-------------------------------------------------------------------------
# This tests fault-injection in a special case in the auto-vacuum code.
#
do_test pagerfault-20-pre1 {
  faultsim_delete_and_reopen

  execsql {
    PRAGMA cache_size = 10;
    PRAGMA auto_vacuum = FULL;
    CREATE TABLE t0(a, b);







  }
  faultsim_save_and_close
} {}
do_faultsim_test pagerfault-20 -prep {
  faultsim_restore_and_reopen

} -body {
  execsql { 
    BEGIN;
      CREATE TABLE t1(a, b);
      CREATE TABLE t2(a, b);
      DROP TABLE t1;
    COMMIT;
  }
} -test {
  faultsim_test_result {0 {}}
}

do_test pagerfault-21-pre1 {
  faultsim_delete_and_reopen
  execsql {
    PRAGMA cache_size = 10;
    CREATE TABLE t0(a PRIMARY KEY, b);
    INSERT INTO t0 VALUES(1, 2);
  }
  faultsim_save_and_close
} {}
do_faultsim_test pagerfault-21 -prep {
  faultsim_restore_and_reopen
} -body {
  db eval { SELECT * FROM t0 LIMIT 1 } {
    db eval { INSERT INTO t0 SELECT a+1, b FROM t0 }
    db eval { INSERT INTO t0 SELECT a+2, b FROM t0 }
  }
} -test {
  faultsim_test_result {0 {}}
}

}


finish_test