mirror of
https://gitlab.com/cznic/sqlite.git
synced 2025-05-22 23:28:02 +00:00
examples
internal
lib
speedtest1
testdata
tcl
8_3_names.test
affinity2.test
affinity3.test
aggerror.test
aggnested.test
alias.test
all.test
alter.test
alter2.test
alter3.test
alter4.test
alterauth.test
alterauth2.test
altercol.test
alterlegacy.test
altermalloc.test
altermalloc2.test
altertab.test
altertab2.test
altertab3.test
amatch1.test
analyze.test
analyze3.test
analyze4.test
analyze5.test
analyze6.test
analyze7.test
analyze8.test
analyze9.test
analyzeC.test
analyzeD.test
analyzeE.test
analyzeF.test
analyzeG.test
analyzer1.test
async.test
async2.test
async3.test
async4.test
async5.test
atof1.test
atomic.test
atomic2.test
attach.test
attach2.test
attach3.test
attach4.test
attachmalloc.test
auth.test
auth2.test
auth3.test
autoanalyze1.test
autoinc.test
autoindex1.test
autoindex2.test
autoindex3.test
autoindex4.test
autoindex5.test
autovacuum.test
autovacuum_ioerr2.test
avtrans.test
backcompat.test
backup.test
backup2.test
backup4.test
backup5.test
backup_ioerr.test
backup_malloc.test
badutf.test
badutf2.test
bc_common.tcl
bestindex1.test
bestindex2.test
bestindex3.test
bestindex4.test
bestindex5.test
bestindex6.test
bestindex7.test
between.test
bigfile.test
bigfile2.test
bigmmap.test
bigrow.test
bigsort.test
bind.test
bindxfer.test
bitvec.test
blob.test
boundary1.tcl
boundary1.test
boundary2.tcl
boundary2.test
boundary3.tcl
boundary3.test
boundary4.tcl
boundary4.test
btree01.test
btree02.test
btreefault.test
busy.test
busy2.test
cache.test
cacheflush.test
cachespill.test
capi2.test
capi3.test
capi3b.test
capi3c.test
capi3d.test
capi3e.test
cast.test
cffault.test
check.test
checkfault.test
chunksize.test
close.test
closure01.test
coalesce.test
collate1.test
collate2.test
collate3.test
collate4.test
collate5.test
collate6.test
collate7.test
collate8.test
collate9.test
collateA.test
collateB.test
colmeta.test
colname.test
conflict.test
conflict2.test
conflict3.test
contrib01.test
corrupt.test
corrupt2.test
corrupt3.test
corrupt4.test
corrupt5.test
corrupt6.test
corrupt7.test
corrupt8.test
corrupt9.test
corruptA.test
corruptB.test
corruptC.test
corruptD.test
corruptE.test
corruptF.test
corruptG.test
corruptH.test
corruptI.test
corruptJ.test
corruptK.test
corruptL.test
corruptM.test
cost.test
count.test
countofview.test
coveridxscan.test
crash.test
crash2.test
crash3.test
crash4.test
crash5.test
crash6.test
crash7.test
crash8.test
crashM.test
createtab.test
cse.test
csv01.test
ctime.test
cursorhint.test
cursorhint2.test
dataversion1.test
date.test
date2.test
dbdata.test
dbfuzz001.test
dbpage.test
dbstatus.test
dbstatus2.test
decimal.test
default.test
delete.test
delete2.test
delete3.test
delete4.test
delete_db.test
descidx1.test
descidx2.test
descidx3.test
diskfull.test
distinct.test
distinct2.test
distinctagg.test
e_blobbytes.test
e_blobclose.test
e_blobopen.test
e_blobwrite.test
e_changes.test
e_createtable.test
e_delete.test
e_droptrigger.test
e_dropview.test
e_expr.test
e_fkey.test
e_fts3.test
e_insert.test
e_reindex.test
e_resolve.test
e_select.test
e_select2.test
e_totalchanges.test
e_update.test
e_uri.test
e_vacuum.test
e_wal.test
e_walauto.test
e_walckpt.test
e_walhook.test
emptytable.test
enc.test
enc2.test
enc3.test
enc4.test
eqp.test
errmsg.test
eval.test
exclusive.test
exclusive2.test
exec.test
exists.test
expr.test
expr2.test
extension01.test
extraquick.test
fallocate.test
filectrl.test
filefmt.test
filter1.test
filter2.tcl
filter2.test
filterfault.test
fkey1.test
fkey2.test
fkey3.test
fkey4.test
fkey5.test
fkey6.test
fkey7.test
fkey8.test
fkey_malloc.test
fordelete.test
format4.test
fts-9fd058691.test
fts1a.test
fts1b.test
fts1c.test
fts1d.test
fts1e.test
fts1f.test
fts1i.test
fts1j.test
fts1k.test
fts1l.test
fts1m.test
fts1n.test
fts1o.test
fts1porter.test
fts2.test
fts2a.test
fts2b.test
fts2c.test
fts2d.test
fts2e.test
fts2f.test
fts2g.test
fts2h.test
fts2i.test
fts2j.test
fts2k.test
fts2l.test
fts2m.test
fts2n.test
fts2o.test
fts2p.test
fts2q.test
fts2r.test
fts2token.test
fts3.test
fts3_common.tcl
fts3aa.test
fts3ab.test
fts3ac.test
fts3ad.test
fts3ae.test
fts3af.test
fts3ag.test
fts3ah.test
fts3ai.test
fts3aj.test
fts3ak.test
fts3al.test
fts3am.test
fts3an.test
fts3ao.test
fts3atoken.test
fts3auto.test
fts3aux1.test
fts3aux2.test
fts3b.test
fts3c.test
fts3comp1.test
fts3conf.test
fts3corrupt.test
fts3corrupt2.test
fts3corrupt3.test
fts3corrupt4.test
fts3corrupt5.test
fts3corrupt6.test
fts3cov.test
fts3d.test
fts3defer.test
fts3defer2.test
fts3defer3.test
fts3drop.test
fts3e.test
fts3expr.test
fts3expr2.test
fts3expr3.test
fts3expr4.test
fts3expr5.test
fts3fault.test
fts3fault2.test
fts3first.test
fts3fuzz001.test
fts3join.test
fts3malloc.test
fts3matchinfo.test
fts3matchinfo2.test
fts3misc.test
fts3near.test
fts3offsets.test
fts3prefix.test
fts3prefix2.test
fts3query.test
fts3rank.test
fts3rnd.test
fts3shared.test
fts3snippet.test
fts3snippet2.test
fts3sort.test
fts3tok1.test
fts3tok_err.test
fts3varint.test
fts4aa.test
fts4check.test
fts4content.test
fts4docid.test
fts4growth.test
fts4growth2.test
fts4incr.test
fts4langid.test
fts4lastrowid.test
fts4merge.test
fts4merge2.test
fts4merge3.test
fts4merge4.test
fts4merge5.test
fts4min.test
fts4noti.test
fts4onepass.test
fts4opt.test
fts4record.test
fts4rename.test
fts4umlaut.test
fts4unicode.test
fts4upfrom.test
full.test
func.test
func2.test
func3.test
func4.test
func5.test
func6.test
fuzz-oss1.test
fuzz.test
fuzz2.test
fuzz3.test
fuzz4.test
fuzz_common.tcl
fuzz_malloc.test
fuzzer1.test
fuzzer2.test
fuzzerfault.test
gcfault.test
gencol1.test
genesis.tcl
having.test
hexlit.test
hidden.test
hook.test
hook2.test
icu.test
ieee754.test
imposter1.test
in.test
in2.test
in3.test
in4.test
in5.test
in6.test
incrblob.test
incrblob2.test
incrblob3.test
incrblob4.test
incrblob_err.test
incrblobfault.test
incrcorrupt.test
incrvacuum.test
incrvacuum2.test
incrvacuum3.test
incrvacuum_ioerr.test
index.test
index2.test
index3.test
index4.test
index5.test
index6.test
index7.test
index8.test
index9.test
indexedby.test
indexexpr1.test
indexexpr2.test
indexfault.test
init.test
insert.test
insert2.test
insert3.test
insert4.test
insert5.test
insertfault.test
instr.test
instrfault.test
intarray.test
interrupt.test
interrupt2.test
intpkey.test
intreal.test
io.test
ioerr.test
ioerr2.test
ioerr3.test
ioerr4.test
ioerr5.test
ioerr6.test
istrue.test
join.test
join2.test
join3.test
join4.test
join5.test
join6.test
journal1.test
journal2.test
journal3.test
jrnlmode.test
jrnlmode2.test
jrnlmode3.test
json101.test
json102.test
json103.test
json104.test
json105.test
keyword1.test
lastinsert.test
laststmtchanges.test
like.test
like2.test
like3.test
limit.test
limit2.test
loadext.test
loadext2.test
lock.test
lock2.test
lock3.test
lock4.test
lock5.test
lock6.test
lock7.test
lock_common.tcl
lookaside.test
main.test
make-where7.tcl
malloc.test
malloc3.test
malloc4.test
malloc5.test
malloc6.test
malloc7.test
malloc8.test
malloc9.test
mallocA.test
mallocAll.test
mallocB.test
mallocC.test
mallocD.test
mallocE.test
mallocF.test
mallocG.test
mallocH.test
mallocI.test
mallocJ.test
mallocK.test
mallocL.test
mallocM.test
malloc_common.tcl
malloctraceviewer.tcl
manydb.test
mem5.test
memdb.test
memdb1.test
memleak.test
memsubsys1.test
memsubsys2.test
minmax.test
minmax2.test
minmax3.test
minmax4.test
misc1.test
misc2.test
misc3.test
misc4.test
misc5.test
misc6.test
misc7.test
misc8.test
misuse.test
mjournal.test
mmap1.test
mmap2.test
mmap3.test
mmap4.test
mmapfault.test
mmapwarm.test
multiplex.test
multiplex2.test
multiplex3.test
multiplex4.test
mutex1.test
mutex2.test
nan.test
nockpt.test
nolock.test
normalize.test
notify1.test
notify2.test
notify3.test
notnull.test
null.test
nulls1.test
numcast.test
numindex1.test
offset1.test
openv2.test
orderby1.test
orderby2.test
orderby3.test
orderby4.test
orderby5.test
orderby6.test
orderby7.test
orderby8.test
orderby9.test
orderbyA.test
oserror.test
ovfl.test
pager1.test
pager2.test
pager3.test
pager4.test
pagerfault.test
pagerfault2.test
pagerfault3.test
pageropt.test
pagesize.test
parser1.test
pcache.test
pcache2.test
percentile.test
permutations.test
pg_common.tcl
pragma.test
pragma2.test
pragma3.test
pragma4.test
pragma5.test
pragmafault.test
prefixes.test
printf.test
printf2.test
progress.test
ptrchng.test
pushdown.test
queryonly.test
quick.test
quota-glob.test
quota.test
quota2.test
quote.test
randexpr1.tcl
randexpr1.test
rbu.test
rdonly.test
recover.test
regexp1.test
regexp2.test
reindex.test
releasetest.tcl
releasetest_data.tcl
resetdb.test
resolver01.test
rollback.test
rollback2.test
rollbackfault.test
round1.test
rowallock.test
rowhash.test
rowid.test
rowvalue.test
rowvalue2.test
rowvalue3.test
rowvalue4.test
rowvalue5.test
rowvalue6.test
rowvalue7.test
rowvalue8.test
rowvalue9.test
rowvaluefault.test
rowvaluevtab.test
rtree.test
savepoint.test
savepoint2.test
savepoint4.test
savepoint5.test
savepoint6.test
savepoint7.test
savepointfault.test
scanstatus.test
schema.test
schema2.test
schema3.test
schema4.test
schema5.test
schema6.test
schemafault.test
securedel.test
securedel2.test
select1.test
select2.test
select3.test
select4.test
select5.test
select6.test
select7.test
select8.test
select9.test
selectA.test
selectB.test
selectC.test
selectD.test
selectE.test
selectF.test
selectG.test
server1.test
session.test
shared.test
shared2.test
shared3.test
shared4.test
shared6.test
shared7.test
shared8.test
shared9.test
sharedA.test
sharedB.test
shared_err.test
sharedlock.test
shell1.test
shell2.test
shell3.test
shell4.test
shell5.test
shell6.test
shell7.test
shell8.test
shmlock.test
shortread1.test
show_speedtest1_rtree.tcl
shrink.test
sidedelete.test
skipscan1.test
skipscan2.test
skipscan3.test
skipscan5.test
skipscan6.test
snapshot.test
snapshot2.test
snapshot3.test
snapshot4.test
snapshot_fault.test
snapshot_up.test
soak.test
softheap1.test
sort.test
sort2.test
sort3.test
sort4.test
sort5.test
sorterref.test
sortfault.test
speed1.test
speed1p.test
speed2.test
speed3.test
speed4.test
speed4p.test
spellfix.test
spellfix2.test
spellfix3.test
spellfix4.test
sqldiff1.test
sqllimits1.test
sqllog.test
stat.test
statfault.test
stmt.test
stmtvtab1.test
subjournal.test
subquery.test
subquery2.test
subselect.test
substr.test
subtype1.test
superlock.test
swarmvtab.test
swarmvtab2.test
swarmvtab3.test
swarmvtabfault.test
symlink.test
symlink2.test
sync.test
sync2.test
syscall.test
sysfault.test
tabfunc01.test
table.test
tableapi.test
tableopts.test
tclsqlite.test
tempdb.test
tempdb2.test
tempfault.test
temptable.test
temptable2.test
temptable3.test
temptrigger.test
tester.tcl
thread001.test
thread002.test
thread003.test
thread004.test
thread005.test
thread1.test
thread2.test
thread_common.tcl
tkt-02a8e81d44.test
tkt-18458b1a.test
tkt-26ff0c2d1e.test
tkt-2a5629202f.test
tkt-2d1a5c67d.test
tkt-2ea2425d34.test
tkt-31338dca7e.test
tkt-313723c356.test
tkt-385a5b56b9.test
tkt-38cb5df375.test
tkt-3998683a16.test
tkt-3a77c9714e.test
tkt-3fe897352e.test
tkt-4a03edc4c8.test
tkt-4c86b126f2.test
tkt-4dd95f6943.test
tkt-4ef7e3cfca.test
tkt-54844eea3f.test
tkt-5d863f876e.test
tkt-5e10420e8d.test
tkt-5ee23731f.test
tkt-6bfb98dfc0.test
tkt-752e1646fc.test
tkt-78e04e52ea.test
tkt-7a31705a7e6.test
tkt-7bbfb7d442.test
tkt-80ba201079.test
tkt-80e031a00f.test
tkt-8454a207b9.test
tkt-868145d012.test
tkt-8c63ff0ec.test
tkt-91e2e8ba6f.test
tkt-94c04eaadb.test
tkt-9a8b09f8e6.test
tkt-9d68c883.test
tkt-9f2eb3abac.test
tkt-a7b7803e.test
tkt-a7debbe0.test
tkt-a8a0d2996a.test
tkt-b1d3a2e531.test
tkt-b351d95f9.test
tkt-b72787b1.test
tkt-b75a9ca6b0.test
tkt-ba7cbfaedc.test
tkt-bd484a090c.test
tkt-bdc6bbbb38.test
tkt-c48d99d690.test
tkt-c694113d5.test
tkt-cbd054fa6b.test
tkt-d11f09d36e.test
tkt-d635236375.test
tkt-d82e3f3721.test
tkt-f3e5abed55.test
tkt-f67b41381a.test
tkt-f777251dc7a.test
tkt-f7b4edec.test
tkt-f973c7ac31.test
tkt-fa7bf5ec.test
tkt-fc62af4523.test
tkt-fc7bd6358f.test
tkt1435.test
tkt1443.test
tkt1444.test
tkt1449.test
tkt1473.test
tkt1501.test
tkt1512.test
tkt1514.test
tkt1536.test
tkt1537.test
tkt1567.test
tkt1644.test
tkt1667.test
tkt1873.test
tkt2141.test
tkt2192.test
tkt2213.test
tkt2251.test
tkt2285.test
tkt2332.test
tkt2339.test
tkt2391.test
tkt2409.test
tkt2450.test
tkt2565.test
tkt2640.test
tkt2643.test
tkt2686.test
tkt2767.test
tkt2817.test
tkt2820.test
tkt2822.test
tkt2832.test
tkt2854.test
tkt2920.test
tkt2927.test
tkt2942.test
tkt3080.test
tkt3093.test
tkt3121.test
tkt3201.test
tkt3292.test
tkt3298.test
tkt3334.test
tkt3346.test
tkt3357.test
tkt3419.test
tkt3424.test
tkt3442.test
tkt3457.test
tkt3461.test
tkt3493.test
tkt3508.test
tkt3522.test
tkt3527.test
tkt3541.test
tkt3554.test
tkt3581.test
tkt35xx.test
tkt3630.test
tkt3718.test
tkt3731.test
tkt3757.test
tkt3761.test
tkt3762.test
tkt3773.test
tkt3791.test
tkt3793.test
tkt3810.test
tkt3824.test
tkt3832.test
tkt3838.test
tkt3841.test
tkt3871.test
tkt3879.test
tkt3911.test
tkt3918.test
tkt3922.test
tkt3929.test
tkt3935.test
tkt3992.test
tkt3997.test
tkt4018.test
tokenize.test
tpch01.test
trace.test
trace2.test
trace3.test
trans.test
trans2.test
trans3.test
transitive1.test
trigger1.test
trigger2.test
trigger3.test
trigger4.test
trigger5.test
trigger6.test
trigger7.test
trigger8.test
trigger9.test
triggerA.test
triggerB.test
triggerC.test
triggerD.test
triggerE.test
triggerF.test
triggerG.test
triggerupfrom.test
trustschema1.test
types.test
types2.test
types3.test
unionvtab.test
unionvtabfault.test
unique.test
unique2.test
unixexcl.test
unordered.test
update.test
update2.test
upfrom1.tcl
upfrom1.test
upfrom2.test
upfrom3.test
upfromfault.test
upsert1.test
upsert2.test
upsert3.test
upsert4.test
upsertfault.test
uri.test
uri2.test
userauth01.test
utf16align.test
vacuum-into.test
vacuum.test
vacuum2.test
vacuum3.test
vacuum4.test
vacuum5.test
vacuummem.test
varint.test
veryquick.test
view.test
vtab1.test
vtab2.test
vtab3.test
vtab4.test
vtab5.test
vtab6.test
vtab7.test
vtab8.test
vtab9.test
vtabA.test
vtabB.test
vtabC.test
vtabD.test
vtabE.test
vtabF.test
vtabH.test
vtabI.test
vtabJ.test
vtab_alter.test
vtab_err.test
vtab_shared.test
vtabdrop.test
wal.test
wal2.test
wal3.test
wal4.test
wal5.test
wal6.test
wal64k.test
wal7.test
wal8.test
wal9.test
wal_common.tcl
walbak.test
walbig.test
walblock.test
walcksum.test
walcrash.test
walcrash2.test
walcrash3.test
walcrash4.test
walfault.test
walfault2.test
walhook.test
walmode.test
walnoshm.test
waloverwrite.test
walpersist.test
walprotocol.test
walprotocol2.test
walro.test
walro2.test
walrofault.test
walsetlk.test
walshared.test
walslow.test
walthread.test
walvfs.test
wapp.tcl
wapptest.tcl
where.test
where2.test
where3.test
where4.test
where5.test
where6.test
where7.test
where8.test
where9.test
whereA.test
whereB.test
whereC.test
whereD.test
whereE.test
whereF.test
whereG.test
whereH.test
whereI.test
whereJ.test
whereK.test
whereL.test
wherefault.test
wherelfault.test
wherelimit.test
wherelimit2.test
win32heap.test
win32lock.test
win32longpath.test
win32nolock.test
window1.test
window2.tcl
window2.test
window3.tcl
window3.test
window4.tcl
window4.test
window5.test
window6.test
window7.tcl
window7.test
window8.tcl
window8.test
window9.test
windowA.test
windowB.test
windowerr.tcl
windowerr.test
windowfault.test
with1.test
with2.test
with3.test
with4.test
withM.test
without_rowid1.test
without_rowid2.test
without_rowid3.test
without_rowid4.test
without_rowid5.test
without_rowid6.test
without_rowid7.test
writecrash.test
zeroblob.test
zerodamage.test
zipfile.test
zipfile2.test
zipfilefault.test
.gitignore
testlog-darwin-amd64
testlog-linux-amd64
testlog-linux-arm
testlog-linux-arm64
AUTHORS
CONTRIBUTORS
LICENSE
Makefile
README.md
SQLITE-LICENSE
all_test.go
cgo_test.go
doc.go
generator.go
go.mod
go.sum
mutex.go
null_test.go
sqlite.go
sqlite_go18.go
sqlite_go18_test.go
tcl_test.go
unconvert.sh
1520 lines
42 KiB
Plaintext
1520 lines
42 KiB
Plaintext
![]() |
# 2010 April 13
|
||
|
#
|
||
|
# 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.
|
||
|
#
|
||
|
#***********************************************************************
|
||
|
# This file implements regression tests for SQLite library. The
|
||
|
# focus of this file is testing the operation of the library in
|
||
|
# "PRAGMA journal_mode=WAL" mode.
|
||
|
#
|
||
|
|
||
|
set testdir [file dirname $argv0]
|
||
|
source $testdir/tester.tcl
|
||
|
source $testdir/lock_common.tcl
|
||
|
source $testdir/malloc_common.tcl
|
||
|
source $testdir/wal_common.tcl
|
||
|
|
||
|
set testprefix wal
|
||
|
|
||
|
ifcapable !wal {finish_test ; return }
|
||
|
test_set_config_pagecache 0 0
|
||
|
|
||
|
proc reopen_db {} {
|
||
|
catch { db close }
|
||
|
forcedelete test.db test.db-wal test.db-wal-summary
|
||
|
sqlite3_wal db test.db
|
||
|
}
|
||
|
|
||
|
set ::blobcnt 0
|
||
|
proc blob {nByte} {
|
||
|
incr ::blobcnt
|
||
|
return [string range [string repeat "${::blobcnt}x" $nByte] 1 $nByte]
|
||
|
}
|
||
|
|
||
|
proc sqlite3_wal {args} {
|
||
|
eval sqlite3 $args
|
||
|
[lindex $args 0] eval { PRAGMA auto_vacuum = 0 }
|
||
|
[lindex $args 0] eval { PRAGMA page_size = 1024 }
|
||
|
[lindex $args 0] eval { PRAGMA journal_mode = wal }
|
||
|
[lindex $args 0] eval { PRAGMA synchronous = normal }
|
||
|
[lindex $args 0] function blob blob
|
||
|
db timeout 1000
|
||
|
}
|
||
|
|
||
|
proc log_deleted {logfile} {
|
||
|
return [expr [file exists $logfile]==0]
|
||
|
}
|
||
|
|
||
|
#
|
||
|
# These are 'warm-body' tests used while developing the WAL code. They
|
||
|
# serve to prove that a few really simple cases work:
|
||
|
#
|
||
|
# wal-1.*: Read and write the database.
|
||
|
# wal-2.*: Test MVCC with one reader, one writer.
|
||
|
# wal-3.*: Test transaction rollback.
|
||
|
# wal-4.*: Test savepoint/statement rollback.
|
||
|
# wal-5.*: Test the temp database.
|
||
|
# wal-6.*: Test creating databases with different page sizes.
|
||
|
#
|
||
|
#
|
||
|
#
|
||
|
do_test wal-0.1 {
|
||
|
execsql { PRAGMA auto_vacuum = 0 }
|
||
|
execsql { PRAGMA synchronous = normal }
|
||
|
execsql { PRAGMA journal_mode = wal }
|
||
|
} {wal}
|
||
|
do_test wal-0.2 {
|
||
|
file size test.db
|
||
|
} {1024}
|
||
|
|
||
|
do_test wal-1.0 {
|
||
|
execsql {
|
||
|
BEGIN;
|
||
|
CREATE TABLE t1(a, b);
|
||
|
}
|
||
|
list [file exists test.db-journal] \
|
||
|
[file exists test.db-wal] \
|
||
|
[file size test.db]
|
||
|
} {0 1 1024}
|
||
|
do_test wal-1.1 {
|
||
|
execsql COMMIT
|
||
|
list [file exists test.db-journal] [file exists test.db-wal]
|
||
|
} {0 1}
|
||
|
do_test wal-1.2 {
|
||
|
# There are now two pages in the log.
|
||
|
file size test.db-wal
|
||
|
} [wal_file_size 2 1024]
|
||
|
|
||
|
do_test wal-1.3 {
|
||
|
execsql { SELECT * FROM sqlite_master }
|
||
|
} {table t1 t1 2 {CREATE TABLE t1(a, b)}}
|
||
|
|
||
|
do_test wal-1.4 {
|
||
|
execsql { INSERT INTO t1 VALUES(1, 2) }
|
||
|
execsql { INSERT INTO t1 VALUES(3, 4) }
|
||
|
execsql { INSERT INTO t1 VALUES(5, 6) }
|
||
|
execsql { INSERT INTO t1 VALUES(7, 8) }
|
||
|
execsql { INSERT INTO t1 VALUES(9, 10) }
|
||
|
} {}
|
||
|
|
||
|
do_test wal-1.5 {
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6 7 8 9 10}
|
||
|
|
||
|
do_test wal-2.1 {
|
||
|
sqlite3_wal db2 ./test.db
|
||
|
execsql { BEGIN; SELECT * FROM t1 } db2
|
||
|
} {1 2 3 4 5 6 7 8 9 10}
|
||
|
|
||
|
do_test wal-2.2 {
|
||
|
execsql { INSERT INTO t1 VALUES(11, 12) }
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12}
|
||
|
|
||
|
do_test wal-2.3 {
|
||
|
execsql { SELECT * FROM t1 } db2
|
||
|
} {1 2 3 4 5 6 7 8 9 10}
|
||
|
|
||
|
do_test wal-2.4 {
|
||
|
execsql { INSERT INTO t1 VALUES(13, 14) }
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
|
||
|
|
||
|
do_test wal-2.5 {
|
||
|
execsql { SELECT * FROM t1 } db2
|
||
|
} {1 2 3 4 5 6 7 8 9 10}
|
||
|
|
||
|
do_test wal-2.6 {
|
||
|
execsql { COMMIT; SELECT * FROM t1 } db2
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
|
||
|
|
||
|
do_test wal-3.1 {
|
||
|
execsql { BEGIN; DELETE FROM t1 }
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {}
|
||
|
do_test wal-3.2 {
|
||
|
execsql { SELECT * FROM t1 } db2
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
|
||
|
do_test wal-3.3 {
|
||
|
execsql { ROLLBACK }
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
|
||
|
db2 close
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# The following tests, wal-4.*, test that savepoints work with WAL
|
||
|
# databases.
|
||
|
#
|
||
|
do_test wal-4.1 {
|
||
|
execsql {
|
||
|
DELETE FROM t1;
|
||
|
BEGIN;
|
||
|
INSERT INTO t1 VALUES('a', 'b');
|
||
|
SAVEPOINT sp;
|
||
|
INSERT INTO t1 VALUES('c', 'd');
|
||
|
SELECT * FROM t1;
|
||
|
}
|
||
|
} {a b c d}
|
||
|
do_test wal-4.2 {
|
||
|
execsql {
|
||
|
ROLLBACK TO sp;
|
||
|
SELECT * FROM t1;
|
||
|
}
|
||
|
} {a b}
|
||
|
do_test wal-4.3 {
|
||
|
execsql {
|
||
|
COMMIT;
|
||
|
SELECT * FROM t1;
|
||
|
}
|
||
|
} {a b}
|
||
|
|
||
|
do_test wal-4.4.1 {
|
||
|
db close
|
||
|
sqlite3 db test.db
|
||
|
db func blob blob
|
||
|
list [execsql { SELECT * FROM t1 }] [file size test.db-wal]
|
||
|
} {{a b} 0}
|
||
|
do_test wal-4.4.2 {
|
||
|
execsql { PRAGMA cache_size = 10 }
|
||
|
execsql {
|
||
|
CREATE TABLE t2(a, b);
|
||
|
INSERT INTO t2 VALUES(blob(400), blob(400));
|
||
|
SAVEPOINT tr;
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 2 */
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 4 */
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 8 */
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 16 */
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 32 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 2 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 4 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 8 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 16 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 32 */
|
||
|
SELECT count(*) FROM t2;
|
||
|
}
|
||
|
} {32}
|
||
|
do_test wal-4.4.3 {
|
||
|
execsql { ROLLBACK TO tr }
|
||
|
} {}
|
||
|
do_test wal-4.4.4 {
|
||
|
set logsize [file size test.db-wal]
|
||
|
execsql {
|
||
|
INSERT INTO t1 VALUES('x', 'y');
|
||
|
RELEASE tr;
|
||
|
}
|
||
|
expr { $logsize == [file size test.db-wal] }
|
||
|
} {1}
|
||
|
do_test wal-4.4.5 {
|
||
|
execsql { SELECT count(*) FROM t2 }
|
||
|
} {1}
|
||
|
do_test wal-4.4.6 {
|
||
|
forcecopy test.db test2.db
|
||
|
forcecopy test.db-wal test2.db-wal
|
||
|
sqlite3 db2 test2.db
|
||
|
execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 } db2
|
||
|
} {1 2}
|
||
|
do_test wal-4.4.7 {
|
||
|
execsql { PRAGMA integrity_check } db2
|
||
|
} {ok}
|
||
|
db2 close
|
||
|
|
||
|
do_test wal-4.5.1 {
|
||
|
reopen_db
|
||
|
db func blob blob
|
||
|
execsql {
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
CREATE TABLE t1(a, b);
|
||
|
INSERT INTO t1 VALUES('a', 'b');
|
||
|
}
|
||
|
sqlite3 db test.db
|
||
|
db func blob blob
|
||
|
list [execsql { SELECT * FROM t1 }] [file size test.db-wal]
|
||
|
} {{a b} 0}
|
||
|
do_test wal-4.5.2 {
|
||
|
execsql { PRAGMA cache_size = 10 }
|
||
|
execsql {
|
||
|
CREATE TABLE t2(a, b);
|
||
|
BEGIN;
|
||
|
INSERT INTO t2 VALUES(blob(400), blob(400));
|
||
|
SAVEPOINT tr;
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 2 */
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 4 */
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 8 */
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 16 */
|
||
|
INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 32 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 2 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 4 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 8 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 16 */
|
||
|
INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 32 */
|
||
|
SELECT count(*) FROM t2;
|
||
|
}
|
||
|
} {32}
|
||
|
do_test wal-4.5.3 {
|
||
|
execsql { ROLLBACK TO tr }
|
||
|
} {}
|
||
|
do_test wal-4.5.4 {
|
||
|
set logsize [file size test.db-wal]
|
||
|
execsql {
|
||
|
INSERT INTO t1 VALUES('x', 'y');
|
||
|
RELEASE tr;
|
||
|
COMMIT;
|
||
|
}
|
||
|
expr { $logsize == [file size test.db-wal] }
|
||
|
} {1}
|
||
|
do_test wal-4.5.5 {
|
||
|
execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 }
|
||
|
} {1 2}
|
||
|
do_test wal-4.5.6 {
|
||
|
forcecopy test.db test2.db
|
||
|
forcecopy test.db-wal test2.db-wal
|
||
|
sqlite3 db2 test2.db
|
||
|
execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 } db2
|
||
|
} {1 2}
|
||
|
do_test wal-4.5.7 {
|
||
|
execsql { PRAGMA integrity_check } db2
|
||
|
} {ok}
|
||
|
db2 close
|
||
|
|
||
|
do_test wal-4.6.1 {
|
||
|
execsql {
|
||
|
DELETE FROM t2;
|
||
|
PRAGMA wal_checkpoint;
|
||
|
BEGIN;
|
||
|
INSERT INTO t2 VALUES('w', 'x');
|
||
|
SAVEPOINT save;
|
||
|
INSERT INTO t2 VALUES('y', 'z');
|
||
|
ROLLBACK TO save;
|
||
|
COMMIT;
|
||
|
}
|
||
|
execsql { SELECT * FROM t2 }
|
||
|
} {w x}
|
||
|
|
||
|
|
||
|
reopen_db
|
||
|
do_test wal-5.1 {
|
||
|
execsql {
|
||
|
CREATE TEMP TABLE t2(a, b);
|
||
|
INSERT INTO t2 VALUES(1, 2);
|
||
|
}
|
||
|
} {}
|
||
|
do_test wal-5.2 {
|
||
|
execsql {
|
||
|
BEGIN;
|
||
|
INSERT INTO t2 VALUES(3, 4);
|
||
|
SELECT * FROM t2;
|
||
|
}
|
||
|
} {1 2 3 4}
|
||
|
do_test wal-5.3 {
|
||
|
execsql {
|
||
|
ROLLBACK;
|
||
|
SELECT * FROM t2;
|
||
|
}
|
||
|
} {1 2}
|
||
|
do_test wal-5.4 {
|
||
|
execsql {
|
||
|
CREATE TEMP TABLE t3(x UNIQUE);
|
||
|
BEGIN;
|
||
|
INSERT INTO t2 VALUES(3, 4);
|
||
|
INSERT INTO t3 VALUES('abc');
|
||
|
}
|
||
|
catchsql { INSERT INTO t3 VALUES('abc') }
|
||
|
} {1 {UNIQUE constraint failed: t3.x}}
|
||
|
do_test wal-5.5 {
|
||
|
execsql {
|
||
|
COMMIT;
|
||
|
SELECT * FROM t2;
|
||
|
}
|
||
|
} {1 2 3 4}
|
||
|
db close
|
||
|
|
||
|
foreach sector {512 4096} {
|
||
|
sqlite3_simulate_device -sectorsize $sector
|
||
|
foreach pgsz {512 1024 2048 4096} {
|
||
|
forcedelete test.db test.db-wal
|
||
|
do_test wal-6.$sector.$pgsz.1 {
|
||
|
sqlite3 db test.db -vfs devsym
|
||
|
execsql "
|
||
|
PRAGMA page_size = $pgsz;
|
||
|
PRAGMA auto_vacuum = 0;
|
||
|
PRAGMA journal_mode = wal;
|
||
|
"
|
||
|
execsql "
|
||
|
CREATE TABLE t1(a, b);
|
||
|
INSERT INTO t1 VALUES(1, 2);
|
||
|
"
|
||
|
db close
|
||
|
file size test.db
|
||
|
} [expr $pgsz*2]
|
||
|
|
||
|
do_test wal-6.$sector.$pgsz.2 {
|
||
|
log_deleted test.db-wal
|
||
|
} {1}
|
||
|
}
|
||
|
}
|
||
|
|
||
|
do_test wal-7.1 {
|
||
|
forcedelete test.db test.db-wal
|
||
|
sqlite3_wal db test.db
|
||
|
execsql {
|
||
|
PRAGMA page_size = 1024;
|
||
|
CREATE TABLE t1(a, b);
|
||
|
INSERT INTO t1 VALUES(1, 2);
|
||
|
}
|
||
|
list [file size test.db] [file size test.db-wal]
|
||
|
} [list 1024 [wal_file_size 3 1024]]
|
||
|
do_test wal-7.2 {
|
||
|
execsql { PRAGMA wal_checkpoint }
|
||
|
list [file size test.db] [file size test.db-wal]
|
||
|
} [list 2048 [wal_file_size 3 1024]]
|
||
|
|
||
|
# Execute some transactions in auto-vacuum mode to test database file
|
||
|
# truncation.
|
||
|
#
|
||
|
do_test wal-8.1 {
|
||
|
reopen_db
|
||
|
catch { db close }
|
||
|
forcedelete test.db test.db-wal
|
||
|
|
||
|
sqlite3 db test.db
|
||
|
db function blob blob
|
||
|
execsql {
|
||
|
PRAGMA auto_vacuum = 1;
|
||
|
PRAGMA journal_mode = wal;
|
||
|
PRAGMA auto_vacuum;
|
||
|
}
|
||
|
} {wal 1}
|
||
|
do_test wal-8.2 {
|
||
|
execsql {
|
||
|
PRAGMA page_size = 1024;
|
||
|
CREATE TABLE t1(x);
|
||
|
INSERT INTO t1 VALUES(blob(900));
|
||
|
INSERT INTO t1 VALUES(blob(900));
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 4 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 8 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 16 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 32 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 64 */
|
||
|
PRAGMA wal_checkpoint;
|
||
|
}
|
||
|
file size test.db
|
||
|
} [expr 68*1024]
|
||
|
do_test wal-8.3 {
|
||
|
execsql {
|
||
|
DELETE FROM t1 WHERE rowid<54;
|
||
|
PRAGMA wal_checkpoint;
|
||
|
}
|
||
|
file size test.db
|
||
|
} [expr 14*1024]
|
||
|
|
||
|
# Run some "warm-body" tests to ensure that log-summary files with more
|
||
|
# than 256 entries (log summaries that contain index blocks) work Ok.
|
||
|
#
|
||
|
do_test wal-9.1 {
|
||
|
reopen_db
|
||
|
execsql {
|
||
|
PRAGMA cache_size=2000;
|
||
|
CREATE TABLE t1(x PRIMARY KEY);
|
||
|
INSERT INTO t1 VALUES(blob(900));
|
||
|
INSERT INTO t1 VALUES(blob(900));
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 4 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 8 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 16 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 32 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 64 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 128 */
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; /* 256 */
|
||
|
}
|
||
|
file size test.db
|
||
|
} 1024
|
||
|
do_test wal-9.2 {
|
||
|
sqlite3_wal db2 test.db
|
||
|
execsql {PRAGMA integrity_check } db2
|
||
|
} {ok}
|
||
|
|
||
|
do_test wal-9.3 {
|
||
|
forcedelete test2.db test2.db-wal
|
||
|
copy_file test.db test2.db
|
||
|
copy_file test.db-wal test2.db-wal
|
||
|
sqlite3_wal db3 test2.db
|
||
|
execsql {PRAGMA integrity_check } db3
|
||
|
} {ok}
|
||
|
db3 close
|
||
|
|
||
|
do_test wal-9.4 {
|
||
|
execsql { PRAGMA wal_checkpoint }
|
||
|
db2 close
|
||
|
sqlite3_wal db2 test.db
|
||
|
execsql {PRAGMA integrity_check } db2
|
||
|
} {ok}
|
||
|
|
||
|
foreach handle {db db2 db3} { catch { $handle close } }
|
||
|
unset handle
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# The following block of tests - wal-10.* - test that the WAL locking
|
||
|
# scheme works in simple cases. This block of tests is run twice. Once
|
||
|
# using multiple connections in the address space of the current process,
|
||
|
# and once with all connections except one running in external processes.
|
||
|
#
|
||
|
do_multiclient_test tn {
|
||
|
|
||
|
# Initialize the database schema and contents.
|
||
|
#
|
||
|
do_test wal-10.$tn.1 {
|
||
|
execsql {
|
||
|
PRAGMA auto_vacuum = 0;
|
||
|
PRAGMA journal_mode = wal;
|
||
|
CREATE TABLE t1(a, b);
|
||
|
INSERT INTO t1 VALUES(1, 2);
|
||
|
SELECT * FROM t1;
|
||
|
}
|
||
|
} {wal 1 2}
|
||
|
|
||
|
# Open a transaction and write to the database using [db]. Check that [db2]
|
||
|
# is still able to read the snapshot before the transaction was opened.
|
||
|
#
|
||
|
do_test wal-10.$tn.2 {
|
||
|
execsql { BEGIN; INSERT INTO t1 VALUES(3, 4); }
|
||
|
sql2 {SELECT * FROM t1}
|
||
|
} {1 2}
|
||
|
|
||
|
# Have [db] commit the transaction. Check that [db2] is now seeing the
|
||
|
# new, updated snapshot.
|
||
|
#
|
||
|
do_test wal-10.$tn.3 {
|
||
|
execsql { COMMIT }
|
||
|
sql2 {SELECT * FROM t1}
|
||
|
} {1 2 3 4}
|
||
|
|
||
|
# Have [db2] open a read transaction. Then write to the db via [db]. Check
|
||
|
# that [db2] is still seeing the original snapshot. Then read with [db3].
|
||
|
# [db3] should see the newly committed data.
|
||
|
#
|
||
|
do_test wal-10.$tn.4 {
|
||
|
sql2 { BEGIN ; SELECT * FROM t1}
|
||
|
} {1 2 3 4}
|
||
|
do_test wal-10.$tn.5 {
|
||
|
execsql { INSERT INTO t1 VALUES(5, 6); }
|
||
|
sql2 {SELECT * FROM t1}
|
||
|
} {1 2 3 4}
|
||
|
do_test wal-10.$tn.6 {
|
||
|
sql3 {SELECT * FROM t1}
|
||
|
} {1 2 3 4 5 6}
|
||
|
do_test wal-10.$tn.7 {
|
||
|
sql2 COMMIT
|
||
|
} {}
|
||
|
|
||
|
# Have [db2] open a write transaction. Then attempt to write to the
|
||
|
# database via [db]. This should fail (writer lock cannot be obtained).
|
||
|
#
|
||
|
# Then open a read-transaction with [db]. Commit the [db2] transaction
|
||
|
# to disk. Verify that [db] still cannot write to the database (because
|
||
|
# it is reading an old snapshot).
|
||
|
#
|
||
|
# Close the current [db] transaction. Open a new one. [db] can now write
|
||
|
# to the database (as it is not locked and [db] is reading the latest
|
||
|
# snapshot).
|
||
|
#
|
||
|
do_test wal-10.$tn.7 {
|
||
|
sql2 { BEGIN; INSERT INTO t1 VALUES(7, 8) ; }
|
||
|
catchsql { INSERT INTO t1 VALUES(9, 10) }
|
||
|
} {1 {database is locked}}
|
||
|
do_test wal-10.$tn.8 {
|
||
|
execsql { BEGIN ; SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6}
|
||
|
do_test wal-10.$tn.9 {
|
||
|
sql2 COMMIT
|
||
|
catchsql { INSERT INTO t1 VALUES(9, 10) }
|
||
|
} {1 {database is locked}}
|
||
|
do_test wal-10.$tn.10 {
|
||
|
execsql { COMMIT }
|
||
|
execsql { BEGIN }
|
||
|
execsql { INSERT INTO t1 VALUES(9, 10) }
|
||
|
execsql { COMMIT }
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6 7 8 9 10}
|
||
|
|
||
|
# 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 7 7}} ;# 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 8 7}}
|
||
|
|
||
|
# 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 8 8}}
|
||
|
do_test wal-10.$tn.17 {
|
||
|
execsql { PRAGMA wal_checkpoint }
|
||
|
} {0 8 8}
|
||
|
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 {
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
|
||
|
do_test wal-10.$tn.21 {
|
||
|
sql3 COMMIT
|
||
|
sql2 COMMIT
|
||
|
} {}
|
||
|
do_test wal-10.$tn.22 {
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {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 9 9}
|
||
|
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 9 9}
|
||
|
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 {
|
||
|
code3 {
|
||
|
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 3 0}}
|
||
|
do_test wal-10.$tn.30 {
|
||
|
code3 { sqlite3_finalize $::STMT }
|
||
|
execsql { PRAGMA wal_checkpoint }
|
||
|
} {0 3 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
|
||
|
execsql { BEGIN ; SELECT * FROM t1 }
|
||
|
sql2 { INSERT INTO t1 VALUES(21, 22) }
|
||
|
catchsql { INSERT INTO t1 VALUES(23, 24) }
|
||
|
} {1 {database is locked}}
|
||
|
do_test wal-10.$tn.32 {
|
||
|
# This statement would fail when the bug was present.
|
||
|
sql2 { INSERT INTO t1 VALUES(23, 24) }
|
||
|
} {}
|
||
|
do_test wal-10.$tn.33 {
|
||
|
execsql { SELECT * FROM t1 ; COMMIT }
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
|
||
|
do_test wal-10.$tn.34 {
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24}
|
||
|
|
||
|
# Test that if a checkpointer cannot obtain the required locks, it
|
||
|
# releases all locks before returning a busy error.
|
||
|
#
|
||
|
do_test wal-10.$tn.35 {
|
||
|
execsql {
|
||
|
DELETE FROM t1;
|
||
|
INSERT INTO t1 VALUES('a', 'b');
|
||
|
INSERT INTO t1 VALUES('c', 'd');
|
||
|
}
|
||
|
sql2 {
|
||
|
BEGIN;
|
||
|
SELECT * FROM t1;
|
||
|
}
|
||
|
} {a b c d}
|
||
|
do_test wal-10.$tn.36 {
|
||
|
catchsql { PRAGMA wal_checkpoint }
|
||
|
} {0 {0 8 8}}
|
||
|
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 9 9}
|
||
|
}
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# 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).
|
||
|
#
|
||
|
do_test wal-11.1 {
|
||
|
reopen_db
|
||
|
execsql {
|
||
|
PRAGMA cache_size = 10;
|
||
|
PRAGMA page_size = 1024;
|
||
|
CREATE TABLE t1(x PRIMARY KEY);
|
||
|
}
|
||
|
list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044]
|
||
|
} {1 3}
|
||
|
do_test wal-11.2 {
|
||
|
execsql { PRAGMA wal_checkpoint }
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 3 [wal_file_size 3 1024]]
|
||
|
do_test wal-11.3 {
|
||
|
execsql { INSERT INTO t1 VALUES( blob(900) ) }
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 3 [wal_file_size 4 1024]]
|
||
|
|
||
|
do_test wal-11.4 {
|
||
|
execsql {
|
||
|
BEGIN;
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; -- 2
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; -- 4
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; -- 8
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; -- 16
|
||
|
}
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 3 [wal_file_size 32 1024]]
|
||
|
do_test wal-11.5 {
|
||
|
execsql {
|
||
|
SELECT count(*) FROM t1;
|
||
|
PRAGMA integrity_check;
|
||
|
}
|
||
|
} {16 ok}
|
||
|
do_test wal-11.6 {
|
||
|
execsql COMMIT
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 3 [wal_file_size 40 1024]]
|
||
|
do_test wal-11.7 {
|
||
|
execsql {
|
||
|
SELECT count(*) FROM t1;
|
||
|
PRAGMA integrity_check;
|
||
|
}
|
||
|
} {16 ok}
|
||
|
do_test wal-11.8 {
|
||
|
execsql { PRAGMA wal_checkpoint }
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 37 [wal_file_size 40 1024]]
|
||
|
do_test wal-11.9 {
|
||
|
db close
|
||
|
list [expr [file size test.db]/1024] [log_deleted test.db-wal]
|
||
|
} {37 1}
|
||
|
sqlite3_wal db test.db
|
||
|
|
||
|
# After adding the capability of WAL to overwrite prior uncommitted
|
||
|
# frame in the WAL-file with revised content, the size of the WAL file
|
||
|
# following cache-spill is smaller.
|
||
|
#
|
||
|
#set nWal 39
|
||
|
#if {[permutation]!="mmap"} {set nWal 37}
|
||
|
#ifcapable !mmap {set nWal 37}
|
||
|
set nWal 34
|
||
|
|
||
|
do_test wal-11.10 {
|
||
|
execsql {
|
||
|
PRAGMA cache_size = 10;
|
||
|
BEGIN;
|
||
|
INSERT INTO t1 SELECT blob(900) FROM t1; -- 32
|
||
|
SELECT count(*) FROM t1;
|
||
|
}
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 37 [wal_file_size $nWal 1024]]
|
||
|
do_test wal-11.11 {
|
||
|
execsql {
|
||
|
SELECT count(*) FROM t1;
|
||
|
ROLLBACK;
|
||
|
SELECT count(*) FROM t1;
|
||
|
}
|
||
|
} {32 16}
|
||
|
do_test wal-11.12 {
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 37 [wal_file_size $nWal 1024]]
|
||
|
do_test wal-11.13 {
|
||
|
execsql {
|
||
|
INSERT INTO t1 VALUES( blob(900) );
|
||
|
SELECT count(*) FROM t1;
|
||
|
PRAGMA integrity_check;
|
||
|
}
|
||
|
} {17 ok}
|
||
|
do_test wal-11.14 {
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 37 [wal_file_size $nWal 1024]]
|
||
|
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# This block of tests, wal-12.*, tests the fix for a problem that
|
||
|
# could occur if a log that is a prefix of an older log is written
|
||
|
# into a reused log file.
|
||
|
#
|
||
|
reopen_db
|
||
|
do_test wal-12.1 {
|
||
|
execsql {
|
||
|
PRAGMA page_size = 1024;
|
||
|
CREATE TABLE t1(x, y);
|
||
|
CREATE TABLE t2(x, y);
|
||
|
INSERT INTO t1 VALUES('A', 1);
|
||
|
}
|
||
|
list [expr [file size test.db]/1024] [file size test.db-wal]
|
||
|
} [list 1 [wal_file_size 5 1024]]
|
||
|
do_test wal-12.2 {
|
||
|
db close
|
||
|
sqlite3 db test.db
|
||
|
execsql {
|
||
|
PRAGMA synchronous = normal;
|
||
|
UPDATE t1 SET y = 0 WHERE x = 'A';
|
||
|
}
|
||
|
list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044]
|
||
|
} {3 1}
|
||
|
do_test wal-12.3 {
|
||
|
execsql { INSERT INTO t2 VALUES('B', 1) }
|
||
|
list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044]
|
||
|
} {3 2}
|
||
|
do_test wal-12.4 {
|
||
|
forcecopy test.db test2.db
|
||
|
forcecopy test.db-wal test2.db-wal
|
||
|
sqlite3_wal db2 test2.db
|
||
|
execsql { SELECT * FROM t2 } db2
|
||
|
} {B 1}
|
||
|
db2 close
|
||
|
do_test wal-12.5 {
|
||
|
execsql {
|
||
|
PRAGMA wal_checkpoint;
|
||
|
UPDATE t2 SET y = 2 WHERE x = 'B';
|
||
|
PRAGMA wal_checkpoint;
|
||
|
UPDATE t1 SET y = 1 WHERE x = 'A';
|
||
|
PRAGMA wal_checkpoint;
|
||
|
UPDATE t1 SET y = 0 WHERE x = 'A';
|
||
|
}
|
||
|
execsql { SELECT * FROM t2 }
|
||
|
} {B 2}
|
||
|
do_test wal-12.6 {
|
||
|
forcecopy test.db test2.db
|
||
|
forcecopy test.db-wal test2.db-wal
|
||
|
sqlite3_wal db2 test2.db
|
||
|
execsql { SELECT * FROM t2 } db2
|
||
|
} {B 2}
|
||
|
db2 close
|
||
|
db close
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# Check a fun corruption case has been fixed.
|
||
|
#
|
||
|
# The problem was that after performing a checkpoint using a connection
|
||
|
# that had an out-of-date pager-cache, the next time the connection was
|
||
|
# used it did not realize the cache was out-of-date and proceeded to
|
||
|
# operate with an inconsistent cache. Leading to corruption.
|
||
|
#
|
||
|
catch { db close }
|
||
|
catch { db2 close }
|
||
|
catch { db3 close }
|
||
|
forcedelete test.db test.db-wal
|
||
|
sqlite3 db test.db
|
||
|
sqlite3 db2 test.db
|
||
|
do_test wal-14 {
|
||
|
execsql {
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
CREATE TABLE t1(a PRIMARY KEY, b);
|
||
|
INSERT INTO t1 VALUES(randomblob(10), randomblob(100));
|
||
|
INSERT INTO t1 SELECT randomblob(10), randomblob(100) FROM t1;
|
||
|
INSERT INTO t1 SELECT randomblob(10), randomblob(100) FROM t1;
|
||
|
INSERT INTO t1 SELECT randomblob(10), randomblob(100) FROM t1;
|
||
|
}
|
||
|
|
||
|
db2 eval {
|
||
|
INSERT INTO t1 SELECT randomblob(10), randomblob(100);
|
||
|
INSERT INTO t1 SELECT randomblob(10), randomblob(100);
|
||
|
INSERT INTO t1 SELECT randomblob(10), randomblob(100);
|
||
|
INSERT INTO t1 SELECT randomblob(10), randomblob(100);
|
||
|
}
|
||
|
|
||
|
# After executing the "PRAGMA wal_checkpoint", connection [db] was being
|
||
|
# left with an inconsistent cache. Running the CREATE INDEX statement
|
||
|
# in this state led to database corruption.
|
||
|
catchsql {
|
||
|
PRAGMA wal_checkpoint;
|
||
|
CREATE INDEX i1 on t1(b);
|
||
|
}
|
||
|
|
||
|
db2 eval { PRAGMA integrity_check }
|
||
|
} {ok}
|
||
|
|
||
|
catch { db close }
|
||
|
catch { db2 close }
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# The following block of tests - wal-15.* - focus on testing the
|
||
|
# implementation of the sqlite3_wal_checkpoint() interface.
|
||
|
#
|
||
|
forcedelete test.db test.db-wal
|
||
|
sqlite3 db test.db
|
||
|
do_test wal-15.1 {
|
||
|
execsql {
|
||
|
PRAGMA auto_vacuum = 0;
|
||
|
PRAGMA page_size = 1024;
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
}
|
||
|
execsql {
|
||
|
CREATE TABLE t1(a, b);
|
||
|
INSERT INTO t1 VALUES(1, 2);
|
||
|
}
|
||
|
} {}
|
||
|
|
||
|
# Test that an error is returned if the database name is not recognized
|
||
|
#
|
||
|
do_test wal-15.2.1 {
|
||
|
sqlite3_wal_checkpoint db aux
|
||
|
} {SQLITE_ERROR}
|
||
|
do_test wal-15.2.2 {
|
||
|
sqlite3_errcode db
|
||
|
} {SQLITE_ERROR}
|
||
|
do_test wal-15.2.3 {
|
||
|
sqlite3_errmsg db
|
||
|
} {unknown database: aux}
|
||
|
|
||
|
# Test that an error is returned if an attempt is made to checkpoint
|
||
|
# if a transaction is open on the database.
|
||
|
#
|
||
|
do_test wal-15.3.1 {
|
||
|
execsql {
|
||
|
BEGIN;
|
||
|
INSERT INTO t1 VALUES(3, 4);
|
||
|
}
|
||
|
sqlite3_wal_checkpoint db main
|
||
|
} {SQLITE_LOCKED}
|
||
|
do_test wal-15.3.2 {
|
||
|
sqlite3_errcode db
|
||
|
} {SQLITE_LOCKED}
|
||
|
do_test wal-15.3.3 {
|
||
|
sqlite3_errmsg db
|
||
|
} {database table is locked}
|
||
|
|
||
|
# Earlier versions returned an error is returned if the db cannot be
|
||
|
# checkpointed because of locks held by another connection. Check that
|
||
|
# this is no longer the case.
|
||
|
#
|
||
|
sqlite3 db2 test.db
|
||
|
do_test wal-15.4.1 {
|
||
|
execsql {
|
||
|
BEGIN;
|
||
|
SELECT * FROM t1;
|
||
|
} db2
|
||
|
} {1 2}
|
||
|
do_test wal-15.4.2 {
|
||
|
execsql { COMMIT }
|
||
|
sqlite3_wal_checkpoint db
|
||
|
} {SQLITE_OK}
|
||
|
do_test wal-15.4.3 {
|
||
|
sqlite3_errmsg db
|
||
|
} {not an error}
|
||
|
|
||
|
# After [db2] drops its lock, [db] may checkpoint the db.
|
||
|
#
|
||
|
do_test wal-15.4.4 {
|
||
|
execsql { COMMIT } db2
|
||
|
sqlite3_wal_checkpoint db
|
||
|
} {SQLITE_OK}
|
||
|
do_test wal-15.4.5 {
|
||
|
sqlite3_errmsg db
|
||
|
} {not an error}
|
||
|
do_test wal-15.4.6 {
|
||
|
file size test.db
|
||
|
} [expr 1024*2]
|
||
|
|
||
|
catch { db2 close }
|
||
|
catch { db close }
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# The following block of tests - wal-16.* - test that if a NULL pointer or
|
||
|
# an empty string is passed as the second argument of the wal_checkpoint()
|
||
|
# API, an attempt is made to checkpoint all attached databases.
|
||
|
#
|
||
|
foreach {tn ckpt_cmd ckpt_res ckpt_main ckpt_aux} {
|
||
|
1 {sqlite3_wal_checkpoint db} SQLITE_OK 1 1
|
||
|
2 {sqlite3_wal_checkpoint db ""} SQLITE_OK 1 1
|
||
|
3 {db eval "PRAGMA wal_checkpoint"} {0 10 10} 1 1
|
||
|
|
||
|
4 {sqlite3_wal_checkpoint db main} SQLITE_OK 1 0
|
||
|
5 {sqlite3_wal_checkpoint db aux} SQLITE_OK 0 1
|
||
|
6 {sqlite3_wal_checkpoint db temp} SQLITE_OK 0 0
|
||
|
7 {db eval "PRAGMA main.wal_checkpoint"} {0 10 10} 1 0
|
||
|
8 {db eval "PRAGMA aux.wal_checkpoint"} {0 13 13} 0 1
|
||
|
9 {db eval "PRAGMA temp.wal_checkpoint"} {0 -1 -1} 0 0
|
||
|
} {
|
||
|
do_test wal-16.$tn.1 {
|
||
|
forcedelete test2.db test2.db-wal test2.db-journal
|
||
|
forcedelete test.db test.db-wal test.db-journal
|
||
|
|
||
|
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 main.synchronous = NORMAL;
|
||
|
PRAGMA aux.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));
|
||
|
INSERT INTO t2 VALUES(2, randomblob(1000));
|
||
|
INSERT INTO t1 SELECT * FROM t2;
|
||
|
}
|
||
|
|
||
|
list [file size test.db] [file size test.db-wal]
|
||
|
} [list [expr 1*1024] [wal_file_size 10 1024]]
|
||
|
do_test wal-16.$tn.3 {
|
||
|
list [file size test2.db] [file size test2.db-wal]
|
||
|
} [list [expr 1*1024] [wal_file_size 13 1024]]
|
||
|
|
||
|
do_test wal-16.$tn.4 [list eval $ckpt_cmd] $ckpt_res
|
||
|
|
||
|
do_test wal-16.$tn.5 {
|
||
|
list [file size test.db] [file size test.db-wal]
|
||
|
} [list [expr ($ckpt_main ? 7 : 1)*1024] [wal_file_size 10 1024]]
|
||
|
|
||
|
do_test wal-16.$tn.6 {
|
||
|
list [file size test2.db] [file size test2.db-wal]
|
||
|
} [list [expr ($ckpt_aux ? 7 : 1)*1024] [wal_file_size 13 1024]]
|
||
|
|
||
|
catch { db close }
|
||
|
}
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# The following tests - wal-17.* - attempt to verify that the correct
|
||
|
# number of "padding" frames are appended to the log file when a transaction
|
||
|
# is committed in synchronous=FULL mode.
|
||
|
#
|
||
|
# Do this by creating a database that uses 512 byte pages. Then writing
|
||
|
# a transaction that modifies 171 pages. In synchronous=NORMAL mode, this
|
||
|
# produces a log file of:
|
||
|
#
|
||
|
# 32 + (24+512)*171 = 90312 bytes.
|
||
|
#
|
||
|
# Slightly larger than 11*8192 = 90112 bytes.
|
||
|
#
|
||
|
# Run the test using various different sector-sizes. In each case, the
|
||
|
# WAL code should write the 90300 bytes of log file containing the
|
||
|
# transaction, then append as may frames as are required to extend the
|
||
|
# log file so that no part of the next transaction will be written into
|
||
|
# a disk-sector used by transaction just committed.
|
||
|
#
|
||
|
set old_pending_byte [sqlite3_test_control_pending_byte 0x10000000]
|
||
|
catch { db close }
|
||
|
foreach {tn sectorsize logsize} "
|
||
|
1 128 [wal_file_size 172 512]
|
||
|
2 256 [wal_file_size 172 512]
|
||
|
3 512 [wal_file_size 172 512]
|
||
|
4 1024 [wal_file_size 172 512]
|
||
|
5 2048 [wal_file_size 172 512]
|
||
|
6 4096 [wal_file_size 176 512]
|
||
|
7 8192 [wal_file_size 184 512]
|
||
|
" {
|
||
|
forcedelete test.db test.db-wal test.db-journal
|
||
|
sqlite3_simulate_device -sectorsize $sectorsize
|
||
|
sqlite3 db test.db -vfs devsym
|
||
|
|
||
|
do_test wal-17.$tn.1 {
|
||
|
execsql {
|
||
|
PRAGMA auto_vacuum = 0;
|
||
|
PRAGMA page_size = 512;
|
||
|
PRAGMA cache_size = -2000;
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
PRAGMA synchronous = FULL;
|
||
|
}
|
||
|
execsql {
|
||
|
BEGIN;
|
||
|
CREATE TABLE t(x);
|
||
|
}
|
||
|
for {set i 0} {$i<166} {incr i} {
|
||
|
execsql { INSERT INTO t VALUES(randomblob(400)) }
|
||
|
}
|
||
|
execsql COMMIT
|
||
|
|
||
|
file size test.db-wal
|
||
|
} $logsize
|
||
|
|
||
|
do_test wal-17.$tn.2 {
|
||
|
file size test.db
|
||
|
} 512
|
||
|
|
||
|
do_test wal-17.$tn.3 {
|
||
|
db close
|
||
|
file size test.db
|
||
|
} [expr 512*171]
|
||
|
}
|
||
|
sqlite3_test_control_pending_byte $old_pending_byte
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# This test - wal-18.* - verifies a couple of specific conditions that
|
||
|
# may be encountered while recovering a log file are handled correctly:
|
||
|
#
|
||
|
# wal-18.1.* When the first 32-bits of a frame checksum is correct but
|
||
|
# the second 32-bits are false, and
|
||
|
#
|
||
|
# wal-18.2.* When the page-size field that occurs at the start of a log
|
||
|
# file is a power of 2 greater than 16384 or smaller than 512.
|
||
|
#
|
||
|
forcedelete test.db test.db-wal test.db-journal
|
||
|
do_test wal-18.0 {
|
||
|
sqlite3 db test.db
|
||
|
execsql {
|
||
|
PRAGMA page_size = 1024;
|
||
|
PRAGMA auto_vacuum = 0;
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
PRAGMA synchronous = OFF;
|
||
|
|
||
|
CREATE TABLE t1(a, b, UNIQUE(a, b));
|
||
|
INSERT INTO t1 VALUES(0, 0);
|
||
|
PRAGMA wal_checkpoint;
|
||
|
|
||
|
INSERT INTO t1 VALUES(1, 2); -- frames 1 and 2
|
||
|
INSERT INTO t1 VALUES(3, 4); -- frames 3 and 4
|
||
|
INSERT INTO t1 VALUES(5, 6); -- frames 5 and 6
|
||
|
}
|
||
|
|
||
|
forcecopy test.db testX.db
|
||
|
forcecopy test.db-wal testX.db-wal
|
||
|
db close
|
||
|
list [file size testX.db] [file size testX.db-wal]
|
||
|
} [list [expr 3*1024] [wal_file_size 6 1024]]
|
||
|
|
||
|
unset -nocomplain nFrame result
|
||
|
foreach {nFrame result} {
|
||
|
0 {0 0}
|
||
|
1 {0 0}
|
||
|
2 {0 0 1 2}
|
||
|
3 {0 0 1 2}
|
||
|
4 {0 0 1 2 3 4}
|
||
|
5 {0 0 1 2 3 4}
|
||
|
6 {0 0 1 2 3 4 5 6}
|
||
|
} {
|
||
|
do_test wal-18.1.$nFrame {
|
||
|
forcecopy testX.db test.db
|
||
|
forcecopy testX.db-wal test.db-wal
|
||
|
|
||
|
hexio_write test.db-wal [expr 24 + $nFrame*(24+1024) + 20] 00000000
|
||
|
|
||
|
sqlite3 db test.db
|
||
|
execsql {
|
||
|
SELECT * FROM t1;
|
||
|
PRAGMA integrity_check;
|
||
|
}
|
||
|
} [concat $result ok]
|
||
|
db close
|
||
|
}
|
||
|
|
||
|
proc randomblob {pgsz} {
|
||
|
sqlite3 rbdb :memory:
|
||
|
set blob [rbdb one {SELECT randomblob($pgsz)}]
|
||
|
rbdb close
|
||
|
set blob
|
||
|
}
|
||
|
|
||
|
proc logcksum {ckv1 ckv2 blob} {
|
||
|
upvar $ckv1 c1
|
||
|
upvar $ckv2 c2
|
||
|
|
||
|
# Since the magic number at the start of the -wal file header is
|
||
|
# 931071618 that indicates that the content should always be read as
|
||
|
# little-endian.
|
||
|
#
|
||
|
set scanpattern i*
|
||
|
|
||
|
binary scan $blob $scanpattern values
|
||
|
foreach {v1 v2} $values {
|
||
|
set c1 [expr {($c1 + $v1 + $c2)&0xFFFFFFFF}]
|
||
|
set c2 [expr {($c2 + $v2 + $c1)&0xFFFFFFFF}]
|
||
|
}
|
||
|
}
|
||
|
|
||
|
forcecopy test.db testX.db
|
||
|
foreach {tn pgsz works} {
|
||
|
1 128 0
|
||
|
2 256 0
|
||
|
3 512 1
|
||
|
4 1024 1
|
||
|
5 2048 1
|
||
|
6 4096 1
|
||
|
7 8192 1
|
||
|
8 16384 1
|
||
|
9 32768 1
|
||
|
10 65536 1
|
||
|
11 131072 0
|
||
|
11 1016 0
|
||
|
} {
|
||
|
|
||
|
if {$::SQLITE_MAX_PAGE_SIZE < $pgsz} {
|
||
|
set works 0
|
||
|
}
|
||
|
|
||
|
for {set pg 1} {$pg <= 3} {incr pg} {
|
||
|
forcecopy testX.db test.db
|
||
|
forcedelete test.db-wal
|
||
|
|
||
|
# Check that the database now exists and consists of three pages. And
|
||
|
# that there is no associated wal file.
|
||
|
#
|
||
|
do_test wal-18.2.$tn.$pg.1 { file exists test.db-wal } 0
|
||
|
do_test wal-18.2.$tn.$pg.2 { file exists test.db } 1
|
||
|
do_test wal-18.2.$tn.$pg.3 { file size test.db } [expr 1024*3]
|
||
|
|
||
|
do_test wal-18.2.$tn.$pg.4 {
|
||
|
|
||
|
# Create a wal file that contains a single frame (database page
|
||
|
# number $pg) with the commit flag set. The frame checksum is
|
||
|
# correct, but the contents of the database page are corrupt.
|
||
|
#
|
||
|
# The page-size in the log file header is set to $pgsz. If the
|
||
|
# WAL code considers $pgsz to be a valid SQLite database file page-size,
|
||
|
# the database will be corrupt (because the garbage frame contents
|
||
|
# will be treated as valid content). If $pgsz is invalid (too small
|
||
|
# or too large), the db will not be corrupt as the log file will
|
||
|
# be ignored.
|
||
|
#
|
||
|
set walhdr [binary format IIIIII 931071618 3007000 $pgsz 1234 22 23]
|
||
|
set framebody [randomblob $pgsz]
|
||
|
set framehdr [binary format IIII $pg 5 22 23]
|
||
|
set c1 0
|
||
|
set c2 0
|
||
|
logcksum c1 c2 $walhdr
|
||
|
|
||
|
append walhdr [binary format II $c1 $c2]
|
||
|
logcksum c1 c2 [string range $framehdr 0 7]
|
||
|
logcksum c1 c2 $framebody
|
||
|
set framehdr [binary format IIIIII $pg 5 22 23 $c1 $c2]
|
||
|
|
||
|
set fd [open test.db-wal w]
|
||
|
fconfigure $fd -encoding binary -translation binary
|
||
|
puts -nonewline $fd $walhdr
|
||
|
puts -nonewline $fd $framehdr
|
||
|
puts -nonewline $fd $framebody
|
||
|
close $fd
|
||
|
|
||
|
file size test.db-wal
|
||
|
} [wal_file_size 1 $pgsz]
|
||
|
|
||
|
do_test wal-18.2.$tn.$pg.5 {
|
||
|
sqlite3 db test.db
|
||
|
set rc [catch { db one {PRAGMA integrity_check} } msg]
|
||
|
expr { $rc!=0 || $msg!="ok" }
|
||
|
} $works
|
||
|
|
||
|
db close
|
||
|
}
|
||
|
}
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# The following test - wal-19.* - fixes a bug that was present during
|
||
|
# development.
|
||
|
#
|
||
|
# When a database connection in WAL mode is closed, it attempts an
|
||
|
# EXCLUSIVE lock on the database file. If the lock is obtained, the
|
||
|
# connection knows that it is the last connection to disconnect from
|
||
|
# the database, so it runs a checkpoint operation. The bug was that
|
||
|
# the connection was not updating its private copy of the wal-index
|
||
|
# header before doing so, meaning that it could checkpoint an old
|
||
|
# snapshot.
|
||
|
#
|
||
|
do_test wal-19.1 {
|
||
|
forcedelete test.db test.db-wal test.db-journal
|
||
|
sqlite3 db test.db
|
||
|
sqlite3 db2 test.db
|
||
|
execsql {
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
CREATE TABLE t1(a, b);
|
||
|
INSERT INTO t1 VALUES(1, 2);
|
||
|
INSERT INTO t1 VALUES(3, 4);
|
||
|
}
|
||
|
execsql { SELECT * FROM t1 } db2
|
||
|
} {1 2 3 4}
|
||
|
do_test wal-19.2 {
|
||
|
execsql {
|
||
|
INSERT INTO t1 VALUES(5, 6);
|
||
|
SELECT * FROM t1;
|
||
|
}
|
||
|
} {1 2 3 4 5 6}
|
||
|
do_test wal-19.3 {
|
||
|
db close
|
||
|
db2 close
|
||
|
file exists test.db-wal
|
||
|
} {0}
|
||
|
do_test wal-19.4 {
|
||
|
# When the bug was present, the following was returning {1 2 3 4} only,
|
||
|
# as [db2] had an out-of-date copy of the wal-index header when it was
|
||
|
# closed.
|
||
|
#
|
||
|
sqlite3 db test.db
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6}
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# This test - wal-20.* - uses two connections. One in this process and
|
||
|
# the other in an external process. The procedure is:
|
||
|
#
|
||
|
# 1. Using connection 1, create the database schema.
|
||
|
#
|
||
|
# 2. Using connection 2 (in an external process), add so much
|
||
|
# data to the database without checkpointing that a wal-index
|
||
|
# larger than 64KB is required.
|
||
|
#
|
||
|
# 3. Using connection 1, checkpoint the database. Make sure all
|
||
|
# the data is present and the database is not corrupt.
|
||
|
#
|
||
|
# At one point, SQLite was failing to grow the mapping of the wal-index
|
||
|
# file in step 3 and the checkpoint was corrupting the database file.
|
||
|
#
|
||
|
if {[permutation]!="unix-excl"} {
|
||
|
do_test wal-20.1 {
|
||
|
catch {db close}
|
||
|
forcedelete test.db test.db-wal test.db-journal
|
||
|
sqlite3 db test.db
|
||
|
execsql {
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
CREATE TABLE t1(x);
|
||
|
INSERT INTO t1 VALUES(randomblob(900));
|
||
|
SELECT count(*) FROM t1;
|
||
|
}
|
||
|
} {wal 1}
|
||
|
do_test wal-20.2 {
|
||
|
set ::buddy [launch_testfixture]
|
||
|
testfixture $::buddy {
|
||
|
sqlite3 db test.db
|
||
|
db transaction { db eval {
|
||
|
PRAGMA wal_autocheckpoint = 0;
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 2 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 4 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 8 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 16 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 32 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 64 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 128 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 256 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 512 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 1024 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 2048 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 4096 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 8192 */
|
||
|
INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 16384 */
|
||
|
} }
|
||
|
}
|
||
|
} {0}
|
||
|
do_test wal-20.3 {
|
||
|
close $::buddy
|
||
|
execsql { PRAGMA wal_checkpoint }
|
||
|
execsql { SELECT count(*) FROM t1 }
|
||
|
} {16384}
|
||
|
do_test wal-20.4 {
|
||
|
db close
|
||
|
sqlite3 db test.db
|
||
|
execsql { SELECT count(*) FROM t1 }
|
||
|
} {16384}
|
||
|
integrity_check wal-20.5
|
||
|
}
|
||
|
|
||
|
catch { db2 close }
|
||
|
catch { db close }
|
||
|
|
||
|
do_test wal-21.1 {
|
||
|
faultsim_delete_and_reopen
|
||
|
execsql {
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
CREATE TABLE t1(a, b);
|
||
|
INSERT INTO t1 VALUES(1, 2);
|
||
|
INSERT INTO t1 VALUES(3, 4);
|
||
|
INSERT INTO t1 VALUES(5, 6);
|
||
|
INSERT INTO t1 VALUES(7, 8);
|
||
|
INSERT INTO t1 VALUES(9, 10);
|
||
|
INSERT INTO t1 VALUES(11, 12);
|
||
|
}
|
||
|
} {wal}
|
||
|
do_test wal-21.2 {
|
||
|
execsql {
|
||
|
PRAGMA cache_size = 10;
|
||
|
PRAGMA wal_checkpoint;
|
||
|
BEGIN;
|
||
|
SAVEPOINT s;
|
||
|
INSERT INTO t1 SELECT randomblob(900), randomblob(900) FROM t1;
|
||
|
ROLLBACK TO s;
|
||
|
COMMIT;
|
||
|
}
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4 5 6 7 8 9 10 11 12}
|
||
|
do_test wal-21.3 {
|
||
|
execsql { PRAGMA integrity_check }
|
||
|
} {ok}
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# Test reading and writing of databases with different page-sizes.
|
||
|
#
|
||
|
incr ::do_not_use_codec
|
||
|
foreach pgsz {512 1024 2048 4096 8192 16384 32768 65536} {
|
||
|
do_multiclient_test tn [string map [list %PGSZ% $pgsz] {
|
||
|
do_test wal-22.%PGSZ%.$tn.1 {
|
||
|
sql1 {
|
||
|
PRAGMA main.page_size = %PGSZ%;
|
||
|
PRAGMA auto_vacuum = 0;
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
CREATE TABLE t1(x UNIQUE);
|
||
|
INSERT INTO t1 SELECT randomblob(800);
|
||
|
INSERT INTO t1 SELECT randomblob(800);
|
||
|
INSERT INTO t1 SELECT randomblob(800);
|
||
|
}
|
||
|
} {wal}
|
||
|
do_test wal-22.%PGSZ%.$tn.2 { sql2 { PRAGMA integrity_check } } {ok}
|
||
|
do_test wal-22.%PGSZ%.$tn.3 {
|
||
|
sql1 {PRAGMA wal_checkpoint}
|
||
|
expr {[file size test.db] % %PGSZ%}
|
||
|
} {0}
|
||
|
}]
|
||
|
}
|
||
|
incr ::do_not_use_codec -1
|
||
|
|
||
|
#-------------------------------------------------------------------------
|
||
|
# Test that when 1 or more pages are recovered from a WAL file,
|
||
|
# sqlite3_log() is invoked to report this to the user.
|
||
|
#
|
||
|
ifcapable curdir {
|
||
|
set walfile [file nativename [file join [get_pwd] test.db-wal]]
|
||
|
} else {
|
||
|
set walfile test.db-wal
|
||
|
}
|
||
|
catch {db close}
|
||
|
forcedelete test.db
|
||
|
do_test wal-23.1 {
|
||
|
faultsim_delete_and_reopen
|
||
|
execsql {
|
||
|
CREATE TABLE t1(a, b);
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
INSERT INTO t1 VALUES(1, 2);
|
||
|
INSERT INTO t1 VALUES(3, 4);
|
||
|
}
|
||
|
faultsim_save_and_close
|
||
|
|
||
|
sqlite3_shutdown
|
||
|
test_sqlite3_log [list lappend ::log]
|
||
|
set ::log [list]
|
||
|
sqlite3 db test.db
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4}
|
||
|
do_test wal-23.2 { set ::log } {}
|
||
|
|
||
|
do_test wal-23.3 {
|
||
|
db close
|
||
|
set ::log [list]
|
||
|
faultsim_restore_and_reopen
|
||
|
execsql { SELECT * FROM t1 }
|
||
|
} {1 2 3 4}
|
||
|
do_test wal-23.4 {
|
||
|
set ::log
|
||
|
} [list SQLITE_NOTICE_RECOVER_WAL \
|
||
|
"recovered 2 frames from WAL file $walfile"]
|
||
|
|
||
|
|
||
|
ifcapable autovacuum {
|
||
|
# This block tests that if the size of a database is reduced by a
|
||
|
# transaction (because of an incremental or auto-vacuum), that no
|
||
|
# data is written to the WAL file for the truncated pages as part
|
||
|
# of the commit. e.g. if a transaction reduces the size of a database
|
||
|
# to N pages, data for page N+1 should not be written to the WAL file
|
||
|
# when committing the transaction. At one point such data was being
|
||
|
# written.
|
||
|
#
|
||
|
catch {db close}
|
||
|
forcedelete test.db
|
||
|
sqlite3 db test.db
|
||
|
do_execsql_test 24.1 {
|
||
|
PRAGMA auto_vacuum = 2;
|
||
|
PRAGMA journal_mode = WAL;
|
||
|
PRAGMA page_size = 1024;
|
||
|
CREATE TABLE t1(x);
|
||
|
INSERT INTO t1 VALUES(randomblob(5000));
|
||
|
INSERT INTO t1 SELECT * FROM t1;
|
||
|
INSERT INTO t1 SELECT * FROM t1;
|
||
|
INSERT INTO t1 SELECT * FROM t1;
|
||
|
INSERT INTO t1 SELECT * FROM t1;
|
||
|
} {wal}
|
||
|
do_test 24.2 {
|
||
|
execsql {
|
||
|
DELETE FROM t1;
|
||
|
PRAGMA wal_checkpoint;
|
||
|
}
|
||
|
db close
|
||
|
sqlite3 db test.db
|
||
|
file exists test.db-wal
|
||
|
} 0
|
||
|
do_test 24.3 {
|
||
|
file size test.db
|
||
|
} [expr 84 * 1024]
|
||
|
do_test 24.4 {
|
||
|
execsql {
|
||
|
PRAGMA cache_size = 200;
|
||
|
PRAGMA incremental_vacuum;
|
||
|
PRAGMA wal_checkpoint;
|
||
|
}
|
||
|
file size test.db
|
||
|
} [expr 3 * 1024]
|
||
|
|
||
|
# WAL file now contains a single frame - the new root page for table t1.
|
||
|
# It would be two frames (the new root page and a padding frame) if the
|
||
|
# ZERO_DAMAGE flag were not set.
|
||
|
do_test 24.5 {
|
||
|
file size test.db-wal
|
||
|
} [wal_file_size 1 1024]
|
||
|
}
|
||
|
|
||
|
db close
|
||
|
sqlite3_shutdown
|
||
|
test_sqlite3_log
|
||
|
sqlite3_initialize
|
||
|
|
||
|
# Make sure PRAGMA journal_mode=WAL works with ATTACHED databases in
|
||
|
# all journal modes.
|
||
|
#
|
||
|
foreach mode {OFF MEMORY PERSIST DELETE TRUNCATE WAL} {
|
||
|
delete_file test.db test2.db
|
||
|
sqlite3 db test.db
|
||
|
do_test wal-25.$mode {
|
||
|
db eval "PRAGMA journal_mode=$mode"
|
||
|
db eval {ATTACH 'test2.db' AS t2; PRAGMA journal_mode=WAL;}
|
||
|
} {wal}
|
||
|
db close
|
||
|
}
|
||
|
|
||
|
test_restore_config_pagecache
|
||
|
finish_test
|