AryaWu/sqlite
0
1# 2021-03-062#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# TESTRUNNER: shell12# 13# This file implements tests for the appendvfs extension.14#15# Tests performed:16# avfs-1.0. Test that an appendvfs DB can be added to an empty (ZLF) file.17# avfs-1.1. Test that the DB can be read with correct content upon reopen.18# avfs-1.2. Test that an appendvfs DB can be added to a simple text file.19# avfs-1.3. Test that the DB can be read with correct content upon reopen.20# avfs-1.4. Test that appended DB is aligned to default page boundary.21# avfs-2.1. Test that the simple text file retains its initial text.22# avfs-3.1. Test that the appendvfs can grow and shrink, remaining intact.23# avfs-3.2. Test that appendvfs is intact after grow/shrink/close/reopen.24# avfs-3.3. Test that appendvfs can grow by many pages and be written.25# avfs-3.4. Test that grown appendvfs can be reopened and appear intact.26# avfs-3.5. Test that much grown appendvfs can shrink and reopen intact.27# avfs-4.1. Test shell's ability to append to a non-appendvfs file.28# avfs-4.2. Test shell's ability to append to empty or nonexistent file.29# avfs-4.3. Test shell's ability to reopen and alter an appendvfs file.30# avfs-5.1. Test appendvfs refusal to open too-tiny DB appended onto ZLF.31# avfs-5.2. Test appendvfs refusal to open too-tiny DB appended on other.32# ...33# (more to come)34 35set testdir [file dirname $argv0]36source $testdir/tester.tcl37set ::testprefix avfs38 39# Do not attempt this test if SQLITE_OMIT_VIRTUALTABLE is defined. 40#41ifcapable !vtab {42 finish_test43 return44}45 46set CLI [test_find_cli]47db close48# forcedelete test.db49 50load_static_extension db appendvfs51 52set ::fa avfs.adb53set ::fza avfs.sdb54forcedelete $::fa $::fza55set ::result {}56 57proc shellDoesAr {} {58 set shdo "sh_app1.sql"59 forcedelete $shdo60 set fd [open $shdo w]61 puts $fd ".help\n.q"62 close $fd63 set res [catchcmd "-batch -cmd \".read $shdo\""]64 return [regexp {^.archive} [lindex $res 1]]65}66 67set ::vf "&vfs=apndvfs"68 69# Return file offset of appendvfs portion of a file, or {} if none such.70proc fosAvfs {fname} {71 if {[file size $fname] < 25} {72 return {}73 }74 if {[catch {set fd [open $fname rb]}]} {75 return {}76 }77 seek $fd -25 end78 set am [read $fd 17]79 set ao [read $fd 8]80 close $fd81 if {$am ne "Start-Of-SQLite3-"} {82 return {}83 }84 binary scan $ao "W" rvo85 return $rvo86}87 88do_test 1.0 {89 set results {}90 set out [open $::fza wb]91 close $out92 sqlite3 adb "file:$::fza?mode=rwc$::vf" -uri 193 adb eval {94 PRAGMA page_size=1024;95 PRAGMA cache_size=10;96 CREATE TABLE t1(a TEXT);97 INSERT INTO t1 VALUES ('dog'),('cat');98 SELECT group_concat(a) as pets FROM (SELECT a FROM t1 ORDER BY a);99 } { lappend results $pets }100 adb close101 lappend results [fosAvfs $fza]102 set ::result [join $results " | "]103} {cat,dog | 0}104 105do_test 1.1 {106 set results {}107 sqlite3 adb "file:$::fza?mode=rw$::vf" -uri 1108 adb eval {109 SELECT group_concat(a) as pets FROM (SELECT a FROM t1 ORDER BY a DESC);110 } { lappend results $pets }111 adb close112 set ::result [join $results " | "]113} {dog,cat}114 115do_test 1.2 {116 set results {}117 set out [open $::fa wb]118 set ::tlo { "Just some text," "and more text," "ending at 3 lines." }119 puts $out [join $::tlo "\n"]120 close $out121 set adbSz [file size $::fa]122 sqlite3 adb "file:$::fa?mode=rwc$::vf" -uri 1123 adb eval {124 PRAGMA auto_vacuum = 0;125 PRAGMA page_size=512;126 PRAGMA cache_size=0;127 CREATE TABLE t1(a TEXT);128 INSERT INTO t1 VALUES ('dog'),('cat'),('pig');129 SELECT group_concat(a) as pets FROM (SELECT a FROM t1 ORDER BY a);130 } { lappend results $pets }131 adb close132 set adaSz [file size $::fa]133 lappend results "Bytes before/after $adbSz/$adaSz"134 set ::result [join $results " | "]135} {cat,dog,pig | Bytes before/after 50/5145}136 137do_test 1.3 {138 set results {}139 sqlite3 adb "file:$::fa?mode=rw$::vf" -uri 1140 adb eval {141 SELECT group_concat(a) as pets FROM (SELECT a FROM t1 ORDER BY a DESC);142 } { lappend results $pets }143 adb close144 set ::result [join $results " | "]145} {pig,dog,cat}146 147do_test 1.4 {148 set ::result [fosAvfs $fa]149} {4096}150 151do_test 2.1 {152 set in [open $::fa r]153 set tli {}154 for {set i [llength $::tlo]} {$i > 0} {incr i -1} {155 lappend tli [gets $in]156 }157 close $in158 if { [join $tli ":"] ne [join $::tlo ":"] } {159 set ::result "Appendee changed."160 } else {161 set ::result "Appendee intact."162 }163} {Appendee intact.}164 165# Set of repeatable random integers for a couple tests.166set ::nrint 50000167proc rint {v} {168 return [::tcl::mathfunc::int [expr $v * 100000]]169}170array set ::randints [list 0 [rint [::tcl::mathfunc::srand 0]]]171for {set i 1} {$i < $::nrint} {incr i} {172 set ::randints($i) [rint [::tcl::mathfunc::rand]]173}174 175do_test 3.1 {176 set results {}177 sqlite3 adb "file:$::fa?mode=rw$::vf" -uri 1178 adb eval {179 DROP TABLE t1;180 PRAGMA cache_size=10;181 CREATE TABLE ri (i INTEGER);182 BEGIN;183 }184 for {set i 0} {$i < $::nrint} {incr i} {185 set r $::randints($i)186 set s $::randints([incr i])187 set t $::randints([incr i])188 set u $::randints([incr i])189 set v $::randints([incr i])190 adb eval {191 INSERT INTO ri VALUES ($r),($s),($t),($u),($v)192 }193 }194 adb eval {195 COMMIT;196 SELECT integrity_check as ic FROM pragma_integrity_check();197 } { lappend results $ic }198 set adbSz [file size $::fa]199 set qr {}200 adb eval {201 SELECT count(*) as ic FROM ri;202 DELETE FROM ri WHERE (i % 50) <> 25;203 SELECT integrity_check as ic FROM pragma_integrity_check();204 VACUUM;205 SELECT integrity_check as ic FROM pragma_integrity_check();206 SELECT count(*) as ic FROM ri;207 } { lappend qr $ic }208 adb close209 set adaSz [file size $::fa]210 set adba [expr ($adbSz + 0.1)/$adaSz]211 # lappend results $adba212 set results [concat $results [lrange $qr 0 2]]213 lappend results [expr {$adba > 10.0}]214 set ::result [join $results " | "]215} "ok | $::nrint | ok | ok | 1"216 217do_test 3.2 {218 set results {}219 sqlite3 adb "file:$::fa?mode=rw$::vf" -uri 1220 adb eval {221 SELECT integrity_check as ic FROM pragma_integrity_check();222 } { lappend results $ic }223 adb close224 set ::result [join $results " | "]225} {ok}226 227# avfs-3.3. Test that appendvfs can grow by many pages and be written.228do_test 3.3 {229 set results {}230 sqlite3 adb "file:$::fa?mode=rw$::vf" -uri 1231 set npages 300232 adb eval { BEGIN }233 while {$npages > 0} {234 adb eval { INSERT INTO ri VALUES (randomblob(1500)) }235 incr npages -1236 }237 adb eval { COMMIT }238 adb eval {239 SELECT integrity_check as ic FROM pragma_integrity_check();240 } { lappend results $ic }241 adb close242 set adaSzr [expr [file size $::fa] / 300.0 / 1500 ]243 set okSzr [expr $adaSzr > 1.0 && $adaSzr < 1.3 ]244 lappend results $okSzr245 set ::result [join $results " | "]246} {ok | 1}247 248# avfs-3.4. Test that grown appendvfs can be reopened and appear intact.249do_test 3.4 {250 set results {}251 sqlite3 adb "file:$::fa?mode=rw$::vf" -uri 1252 adb eval {253 SELECT integrity_check as ic FROM pragma_integrity_check();254 } { lappend results $ic }255 adb close256 set ::result $ic257} {ok}258 259# avfs-3.5. Test that much grown appendvfs can shrink and reopen intact.260do_test 3.5 {261 set results {}262 set adbsz [file size $::fa]263 sqlite3 adb "file:$::fa?mode=rw$::vf" -uri 1264 adb eval {265 DELETE FROM ri WHERE rowid % 8 <> 0;266 SELECT integrity_check as ic FROM pragma_integrity_check();267 VACUUM;268 SELECT integrity_check as ic FROM pragma_integrity_check();269 } { lappend results $ic }270 adb close271 set adasz [file size $::fa]272 lappend results [expr {$adbsz/$adasz > 5}]273 sqlite3 adb "file:$::fa?mode=rw$::vf" -uri 1274 adb eval {275 SELECT integrity_check as ic FROM pragma_integrity_check();276 } { lappend results $ic }277 adb close278 set ::result [join $results " | "]279} {ok | ok | 1 | ok}280 281set ::cliDoesAr [shellDoesAr]282 283do_test 4.1 {284 set shdo "sh_app1.sql"285 set shod "sh_app1.adb"286 forcedelete $shdo $shod287 set ofd [open $shdo w]288 if {$::cliDoesAr} {289 puts $ofd ".ar -c"290 } else {291 puts $ofd "pragma page_size=512;"292 puts $ofd "create table sqlar (a);"293 }294 puts $ofd ".tables"295 puts $ofd ".q"296 close $ofd297 set ofd [open $shod wb]298 puts $ofd "Some text."299 close $ofd300 set res [catchcmd "-append -batch -init $shdo $shod" ""]301 lappend res [fosAvfs $shod]302 forcedelete $shdo $shod303 set ::result [join $res " | "]304} {0 | sqlar | 4096}305 306do_test 4.2 {307 set shdo "sh_app1.sql"308 set shod "sh_app1.adb"309 forcedelete $shdo $shod310 set ofd [open $shdo w]311 if {$::cliDoesAr} {312 puts $ofd ".ar -c"313 } else {314 puts $ofd "pragma page_size=512;"315 puts $ofd "create table sqlar (a);"316 }317 puts $ofd ".tables"318 puts $ofd ".q"319 close $ofd320 set ofd [open $shod wb]321 close $ofd322 set res [catchcmd "-append -batch -init $shdo $shod" ""]323 lappend res [fosAvfs $shod]324 forcedelete $shdo ; # Leave $shod for next test.325 set ::result [join $res " | "]326} {0 | sqlar | 0}327 328do_test 4.3 {329 set shdo "sh_app1.sql"330 set shod "sh_app1.adb" ; # Same as test 4.2, reusing ADB.331 forcedelete $shdo332 set ofd [open $shdo w]333 if {$::cliDoesAr} {334 puts $ofd ".ar -u $shdo"335 puts $ofd ".mode list"336 puts $ofd "select count(*) from sqlar where name = '$shdo';"337 } else {338 puts $ofd "insert into sqlar values (1);"339 puts $ofd ".mode list"340 puts $ofd "select count(*) from sqlar;"341 }342 puts $ofd ".q"343 close $ofd344 set res [catchcmd "-append -batch -init $shdo $shod" ""]345 sqlite3 adb "file:$shod?mode=rw$::vf" -uri 1346 adb eval {347 SELECT count(*) as n FROM sqlar348 } { lappend res $n }349 adb close350 forcedelete $shdo $shod;351 set ::result [join $res " | "]352} {0 | 1 | 1}353 354do_test 5.1 {355 set fake "faketiny.sdb"356 forcedelete $fake357 set ofd [open $fake wb]358 puts -nonewline $ofd "SQLite format 3"359 puts -nonewline $ofd [binary format "c" 0]360 puts -nonewline $ofd "Start-Of-SQLite3-"361 puts -nonewline $ofd [binary format "W" 0]362 close $ofd363 if {[catch {sqlite3 adb "file:$fake?mode=rw$::vf" -uri 1}]} {364 set res "Open failed."365 } else {366 adb close367 set res "Opened when should not."368 }369 forcedelete $fake370 set ::result $res371} {Open failed.}372 373do_test 5.2 {374 set fake "faketiny.sdb"375 forcedelete $fake376 set ofd [open $fake wb]377 set fakeAppendee "Dog ate my homework.\n"378 puts -nonewline $ofd $fakeAppendee379 puts -nonewline $ofd "SQLite format 3"380 puts -nonewline $ofd [binary format "c" 0]381 puts -nonewline $ofd "Start-Of-SQLite3-"382 puts -nonewline $ofd [binary format "W" [string length $fakeAppendee]]383 close $ofd384 if {[catch {sqlite3 adb "file:$fake?mode=rw$::vf" -uri 1}]} {385 set res "Open failed."386 } else {387 adb close388 set res "Opened when should not."389 }390 forcedelete $fake391 set ::result $res392} {Open failed.}393 394forcedelete $::fa $::fza395 396unset -nocomplain ::fa ::fza ::tlo ::result ::randints ::nrint ::cliDoesAr397 398finish_test399