AryaWu/sqlite
0
1# 2007 May 12#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#12# $Id: incrblob.test,v 1.24 2009/06/19 22:23:42 drh Exp $13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18ifcapable {!autovacuum || !pragma || !incrblob} {19 finish_test20 return21}22 23do_test incrblob-1.1 {24 execsql {25 CREATE TABLE blobs(k PRIMARY KEY, v BLOB);26 INSERT INTO blobs VALUES('one', X'0102030405060708090A');27 INSERT INTO blobs VALUES('two', X'0A090807060504030201');28 }29} {}30 31do_test incrblob-1.2.1 {32 set ::blob [db incrblob blobs v 1]33 string match incrblob_* $::blob34} {1}35unset -nocomplain data36do_test incrblob-1.2.2 {37 binary scan [read $::blob] c* data38 set data39} {1 2 3 4 5 6 7 8 9 10}40do_test incrblob-1.2.3 {41 seek $::blob 042 puts -nonewline $::blob "1234567890"43 flush $::blob44} {}45do_test incrblob-1.2.4 {46 seek $::blob 047 binary scan [read $::blob] c* data48 set data49} {49 50 51 52 53 54 55 56 57 48}50do_test incrblob-1.2.5 {51 close $::blob52} {}53do_test incrblob-1.2.6 {54 execsql {55 SELECT v FROM blobs WHERE rowid = 1;56 }57} {1234567890}58 59#--------------------------------------------------------------------60# Test cases incrblob-1.3.X check that it is possible to read and write61# regions of a blob that lie on overflow pages.62#63do_test incrblob-1.3.1 {64 set ::str "[string repeat . 10000]"65 execsql {66 INSERT INTO blobs(rowid, k, v) VALUES(3, 'three', $::str);67 }68} {}69 70do_test incrblob-1.3.2 {71 set ::blob [db incrblob blobs v 3]72 seek $::blob 850073 read $::blob 1074} {..........}75do_test incrblob-1.3.3 {76 seek $::blob 850077 puts -nonewline $::blob 123456789078} {}79do_test incrblob-1.3.4 {80 seek $::blob 849681 read $::blob 1082} {....123456}83do_test incrblob-1.3.10 {84 close $::blob85} {}86 87#------------------------------------------------------------------------88# incrblob-2.*: 89#90# Test that the following operations use ptrmap pages to reduce91# unnecessary reads:92#93# * Reading near the end of a blob,94# * Writing near the end of a blob, and95# * SELECT a column value that is located on an overflow page.96#97proc nRead {db} {98 set bt [btree_from_db $db]99 db_enter $db100 array set stats [btree_pager_stats $bt]101 db_leave $db102 return $stats(read)103}104proc nWrite {db} {105 set bt [btree_from_db $db]106 db_enter $db107 array set stats [btree_pager_stats $bt]108 db_leave $db109 return $stats(write)110}111 112sqlite3_soft_heap_limit 0113 114foreach AutoVacuumMode [list 0 1] {115 116 if {$AutoVacuumMode>0} {117 ifcapable !autovacuum {118 break119 }120 }121 122 db close123 forcedelete test.db test.db-journal124 125 sqlite3 db test.db126 execsql "PRAGMA mmap_size = 0"127 execsql "PRAGMA auto_vacuum = $AutoVacuumMode"128 129 # Extra value added to size answers130 set ib2_extra 0131 if {$AutoVacuumMode} {incr ib2_extra}132 if {[nonzero_reserved_bytes]} {incr ib2_extra}133 134 do_test incrblob-2.$AutoVacuumMode.1 {135 set ::str [string repeat abcdefghij 2900]136 execsql {137 BEGIN;138 CREATE TABLE blobs(k PRIMARY KEY, v BLOB, i INTEGER);139 DELETE FROM blobs;140 INSERT INTO blobs VALUES('one', $::str || randstr(500,500), 45);141 COMMIT;142 }143 expr [file size test.db]/1024144 } [expr 31 + $ib2_extra]145 146 ifcapable autovacuum {147 do_test incrblob-2.$AutoVacuumMode.2 {148 execsql {149 PRAGMA auto_vacuum;150 }151 } $AutoVacuumMode152 }153 154 do_test incrblob-2.$AutoVacuumMode.3 {155 # Open and close the db to make sure the page cache is empty.156 db close157 sqlite3 db test.db158 execsql "PRAGMA mmap_size = 0"159 160 # Read the last 20 bytes of the blob via a blob handle.161 set ::blob [db incrblob blobs v 1]162 seek $::blob -20 end163 set ::fragment [read $::blob]164 close $::blob165 166 # If the database is not in auto-vacuum mode, the whole of167 # the overflow-chain must be scanned. In auto-vacuum mode,168 # sqlite uses the ptrmap pages to avoid reading the other pages.169 #170 nRead db171 } [expr $AutoVacuumMode ? 4 : 30+$ib2_extra]172 173 do_test incrblob-2.$AutoVacuumMode.4 {174 string range [db one {SELECT v FROM blobs}] end-19 end175 } $::fragment176 177 do_test incrblob-2.$AutoVacuumMode.5 {178 # Open and close the db to make sure the page cache is empty.179 db close180 sqlite3 db test.db181 execsql "PRAGMA mmap_size = 0"182 183 # Write the second-to-last 20 bytes of the blob via a blob handle.184 #185 set ::blob [db incrblob blobs v 1]186 seek $::blob -40 end187 puts -nonewline $::blob "1234567890abcdefghij"188 flush $::blob189 190 # If the database is not in auto-vacuum mode, the whole of191 # the overflow-chain must be scanned. In auto-vacuum mode,192 # sqlite uses the ptrmap pages to avoid reading the other pages.193 #194 nRead db195 } [expr $AutoVacuumMode ? 4 : 30 + $ib2_extra]196 197 # Pages 1 (the write-counter) and 32 (the blob data) were written.198 do_test incrblob-2.$AutoVacuumMode.6 {199 close $::blob200 nWrite db201 } 2202 203 do_test incrblob-2.$AutoVacuumMode.7 {204 string range [db one {SELECT v FROM blobs}] end-39 end-20205 } "1234567890abcdefghij"206 207 do_test incrblob-2.$AutoVacuumMode.8 {208 # Open and close the db to make sure the page cache is empty.209 db close210 sqlite3 db test.db211 execsql { PRAGMA mmap_size = 0 }212 213 execsql { SELECT i FROM blobs } 214 } {45}215 216 do_test incrblob-2.$AutoVacuumMode.9 {217 nRead db218 } [expr $AutoVacuumMode ? 4 : 30 + $ib2_extra]219}220sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)221 222#------------------------------------------------------------------------223# incrblob-3.*: 224#225# Test the outcome of trying to write to a read-only blob handle.226#227do_test incrblob-3.1 {228 set ::blob [db incrblob -readonly blobs v 1]229 seek $::blob -40 end230 read $::blob 20231} "1234567890abcdefghij"232do_test incrblob-3.2 {233 seek $::blob 0234 set rc [catch {235 puts -nonewline $::blob "helloworld"236 } msg]237 close $::blob238 list $rc $msg239} "1 {channel \"$::blob\" wasn't opened for writing}"240 241do_test incrblob-3.3 {242 set ::blob [db incrblob -readonly blobs v 1]243 seek $::blob -40 end244 read $::blob 20245} "1234567890abcdefghij"246do_test incrblob-3.4 {247 set rc [catch {248 sqlite3_blob_write $::blob 20 "qwertyuioplkjhgfds" 249 } msg]250 list $rc $msg251} {1 SQLITE_READONLY}252catch {close $::blob}253 254#------------------------------------------------------------------------255# incrblob-4.*: 256#257# Try a couple of error conditions:258#259# 4.1 - Attempt to open a row that does not exist.260# 4.2 - Attempt to open a column that does not exist.261# 4.3 - Attempt to open a table that does not exist.262# 4.4 - Attempt to open a database that does not exist.263#264# 4.5 - Attempt to open an integer265# 4.6 - Attempt to open a real value266# 4.7 - Attempt to open an SQL null267#268# 4.8 - Attempt to open an indexed column for writing269# 4.9 - Attempt to open an indexed column for reading (this works)270#271# 4.11 - Attempt to open a column of a view.272# 4.12 - Attempt to open a column of a virtual table.273#274do_test incrblob-4.1 {275 set rc [catch {276 set ::blob [db incrblob blobs v 2]277 } msg ] 278 list $rc $msg279} {1 {no such rowid: 2}}280do_test incrblob-4.2 {281 set rc [catch {282 set ::blob [db incrblob blobs blue 1]283 } msg ] 284 list $rc $msg285} {1 {no such column: "blue"}}286do_test incrblob-4.3 {287 set rc [catch {288 set ::blob [db incrblob nosuchtable blue 1]289 } msg ]290 list $rc $msg291} {1 {no such table: main.nosuchtable}}292do_test incrblob-4.4 {293 set rc [catch {294 set ::blob [db incrblob nosuchdb blobs v 1]295 } msg ] 296 list $rc $msg297} {1 {no such table: nosuchdb.blobs}}298 299do_test incrblob-4.5 {300 set rc [catch {301 set ::blob [db incrblob blobs i 1]302 } msg ] 303 list $rc $msg304} {1 {cannot open value of type integer}}305do_test incrblob-4.6 {306 execsql {307 INSERT INTO blobs(k, v, i) VALUES(123, 567.765, NULL);308 }309 set rc [catch {310 set ::blob [db incrblob blobs v 2]311 } msg ] 312 list $rc $msg313} {1 {cannot open value of type real}}314do_test incrblob-4.7 {315 set rc [catch {316 set ::blob [db incrblob blobs i 2]317 } msg ] 318 list $rc $msg319} {1 {cannot open value of type null}}320 321do_test incrblob-4.8.1 {322 execsql {323 INSERT INTO blobs(k, v, i) VALUES(X'010203040506070809', 'hello', 'world');324 }325 set rc [catch {326 set ::blob [db incrblob blobs k 3]327 } msg ] 328 list $rc $msg329} {1 {cannot open indexed column for writing}}330do_test incrblob-4.8.2 {331 execsql {332 CREATE TABLE t3(a INTEGER PRIMARY KEY, b);333 INSERT INTO t3 VALUES(1, 2);334 }335 set rc [catch {336 set ::blob [db incrblob -readonly t3 a 1]337 } msg ] 338 list $rc $msg339} {1 {cannot open value of type null}}340do_test incrblob-4.8.3 {341 set rc [catch {342 set ::blob [db incrblob -readonly t3 rowid 1]343 } msg ] 344 list $rc $msg345} {1 {no such column: "rowid"}}346 347do_test incrblob-4.9.1 {348 set rc [catch {349 set ::blob [db incrblob -readonly blobs k 3]350 } msg]351} {0}352do_test incrblob-4.9.2 {353 binary scan [read $::blob] c* c354 close $::blob355 set c356} {1 2 3 4 5 6 7 8 9}357 358do_test incrblob-4.10 {359 set ::blob [db incrblob -readonly blobs k 3]360 set rc [catch { sqlite3_blob_read $::blob 10 100 } msg]361 list $rc $msg362} {1 SQLITE_ERROR}363do_test incrblob-4.10.2 {364 close $::blob365} {}366 367ifcapable view {368 do_test incrblob-4.11 {369 execsql { CREATE VIEW blobs_view AS SELECT k, v, i FROM blobs }370 set rc [catch { db incrblob blobs_view v 3 } msg]371 list $rc $msg372 } {1 {cannot open view: blobs_view}}373}374ifcapable vtab {375 register_echo_module [sqlite3_connection_pointer db]376 do_test incrblob-4.12 {377 execsql { CREATE VIRTUAL TABLE blobs_echo USING echo(blobs) }378 set rc [catch { db incrblob blobs_echo v 3 } msg]379 list $rc $msg380 } {1 {cannot open virtual table: blobs_echo}}381}382 383 384#------------------------------------------------------------------------385# incrblob-5.*: 386#387# Test that opening a blob in an attached database works.388#389ifcapable attach {390 do_test incrblob-5.1 {391 forcedelete test2.db test2.db-journal392 set ::size [expr [file size $::cmdlinearg(INFO_SCRIPT)]]393 execsql {394 ATTACH 'test2.db' AS aux;395 CREATE TABLE aux.files(name, text);396 INSERT INTO aux.files VALUES('this one', zeroblob($::size));397 }398 set fd [db incrblob aux files text 1]399 fconfigure $fd -translation binary400 set fd2 [open $::cmdlinearg(INFO_SCRIPT)]401 fconfigure $fd2 -translation binary402 puts -nonewline $fd [read $fd2]403 close $fd404 close $fd2405 set ::text [db one {select text from aux.files}]406 string length $::text407 } [file size $::cmdlinearg(INFO_SCRIPT)]408 do_test incrblob-5.2 {409 set fd2 [open $::cmdlinearg(INFO_SCRIPT)]410 fconfigure $fd2 -translation binary411 set ::data [read $fd2]412 close $fd2413 set ::data414 } $::text415}416 417# free memory418unset -nocomplain ::data419unset -nocomplain ::text420 421#------------------------------------------------------------------------422# incrblob-6.*: 423#424# Test that opening a blob for write-access is impossible if425# another connection has the database RESERVED lock.426#427# Then test that blob writes that take place inside of a428# transaction are not visible to external connections until429# after the transaction is commited and the blob channel 430# closed.431#432# This test does not work with the "memsubsys1" configuration.433# Permutation memsubsys1 configures a very small static allocation 434# for use as page-cache memory. This causes SQLite to upgrade435# to an exclusive lock when writing earlier than usual, which436# makes some of these tests fail.437#438sqlite3_soft_heap_limit 0439if {[permutation] != "memsubsys1"} {440 do_test incrblob-6.1 {441 sqlite3 db2 test.db442 execsql {443 BEGIN;444 INSERT INTO blobs(k, v, i) VALUES('a', 'different', 'connection');445 } db2446 } {}447 do_test incrblob-6.2 {448 execsql {449 SELECT rowid FROM blobs ORDER BY rowid450 }451 } {1 2 3}452 do_test incrblob-6.3 {453 set rc [catch {454 db incrblob blobs v 1455 } msg]456 list $rc $msg457 } {1 {database is locked}}458 do_test incrblob-6.4 {459 set rc [catch {460 db incrblob blobs v 3461 } msg]462 list $rc $msg463 } {1 {database is locked}}464 do_test incrblob-6.5 {465 set ::blob [db incrblob -readonly blobs v 3]466 read $::blob467 } {hello}468 do_test incrblob-6.6 {469 close $::blob470 } {}471 472 do_test incrblob-6.7 {473 set ::blob [db2 incrblob blobs i 4]474 gets $::blob475 } {connection}476 do_test incrblob-6.8 {477 tell $::blob478 } {10}479 do_test incrblob-6.9 {480 seek $::blob 0481 puts -nonewline $::blob "invocation"482 flush $::blob483 } {}484 485 # At this point commit should be illegal (because 486 # there is an open blob channel).487 #488 do_test incrblob-6.11 {489 catchsql {490 COMMIT;491 } db2492 } {1 {cannot commit transaction - SQL statements in progress}}493 494 do_test incrblob-6.12 {495 execsql {496 SELECT * FROM blobs WHERE rowid = 4;497 }498 } {}499 do_test incrblob-6.13 {500 close $::blob501 } {}502 do_test incrblob-6.14 {503 catchsql {504 COMMIT;505 } db2506 } {0 {}}507 do_test incrblob-6.15 {508 execsql {509 SELECT * FROM blobs WHERE rowid = 4;510 }511 } {a different invocation}512 db2 close513}514sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)515 516#-----------------------------------------------------------------------517# The following tests verify the behavior of the incremental IO518# APIs in the following cases:519#520# 7.1 A row that containing an open blob is modified.521#522# 7.2 A CREATE TABLE requires that an overflow page that is part523# of an open blob is moved.524#525# 7.3 An INCREMENTAL VACUUM moves an overflow page that is part526# of an open blob.527#528# In the first case above, correct behavior is for all subsequent529# read/write operations on the blob-handle to return SQLITE_ABORT.530# More accurately, blob-handles are invalidated whenever the table531# they belong to is written to.532#533# The second two cases have no external effect. They are testing534# that the internal cache of overflow page numbers is correctly535# invalidated.536#537do_test incrblob-7.1.0 {538 execsql {539 BEGIN;540 DROP TABLE blobs;541 CREATE TABLE t1 (a, b, c, d BLOB);542 INSERT INTO t1(a, b, c, d) VALUES(1, 2, 3, 4);543 COMMIT;544 }545} {}546 547foreach {tn arg} {1 "" 2 -readonly} {548 549 execsql {550 UPDATE t1 SET d = zeroblob(10000);551 }552 553 do_test incrblob-7.1.$tn.1 {554 set ::b [eval db incrblob $arg t1 d 1]555 binary scan [sqlite3_blob_read $::b 5000 5] c* c556 set c557 } {0 0 0 0 0}558 do_test incrblob-7.1.$tn.2 {559 execsql {560 UPDATE t1 SET d = 15;561 }562 } {}563 do_test incrblob-7.1.$tn.3 {564 set rc [catch { sqlite3_blob_read $::b 5000 5 } msg]565 list $rc $msg566 } {1 SQLITE_ABORT}567 do_test incrblob-7.1.$tn.4 {568 execsql {569 SELECT d FROM t1;570 }571 } {15}572 do_test incrblob-7.1.$tn.5 {573 set rc [catch { close $::b } msg]574 list $rc $msg575 } {0 {}}576 do_test incrblob-7.1.$tn.6 {577 execsql {578 SELECT d FROM t1;579 }580 } {15}581 582}583 584set fd [open $::cmdlinearg(INFO_SCRIPT)]585fconfigure $fd -translation binary586set ::data [read $fd 14000]587close $fd588 589db close590forcedelete test.db test.db-journal591sqlite3 db test.db592 593do_test incrblob-7.2.1 {594 execsql {595 PRAGMA auto_vacuum = "incremental";596 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); -- root@page3597 INSERT INTO t1 VALUES(123, $::data);598 }599 set ::b [db incrblob -readonly t1 b 123]600 fconfigure $::b -translation binary601 read $::b602} $::data603do_test incrblob-7.2.2 {604 execsql {605 CREATE TABLE t2(a INTEGER PRIMARY KEY, b); -- root@page4606 }607 seek $::b 0608 read $::b609} $::data610do_test incrblob-7.2.3 {611 close $::b612 execsql {613 SELECT rootpage FROM sqlite_master;614 }615} {3 4}616 617set ::otherdata "[string range $::data 0 1000][string range $::data 1001 end]"618do_test incrblob-7.3.1 {619 execsql {620 INSERT INTO t2 VALUES(456, $::otherdata);621 }622 set ::b [db incrblob -readonly t2 b 456]623 fconfigure $::b -translation binary624 read $::b625} $::otherdata626do_test incrblob-7.3.2 {627 expr [file size test.db]/1024628} 30629do_test incrblob-7.3.3 {630 execsql {631 DELETE FROM t1 WHERE a = 123;632 PRAGMA INCREMENTAL_VACUUM(0);633 }634 seek $::b 0635 read $::b636} $::otherdata637 638# Attempt to write on a read-only blob. Make sure the error code639# gets set. Ticket #2464.640#641do_test incrblob-7.4 {642 set rc [catch {sqlite3_blob_write $::b 10 HELLO} msg]643 lappend rc $msg644} {1 SQLITE_READONLY}645do_test incrblob-7.5 {646 sqlite3_errcode db647} {SQLITE_READONLY}648do_test incrblob-7.6 {649 sqlite3_errmsg db650} {attempt to write a readonly database}651 652# Test that if either the "offset" or "amount" arguments to653# sqlite3_blob_write() are less than zero, SQLITE_ERROR is returned.654# 655do_test incrblob-8.1 {656 execsql { INSERT INTO t1 VALUES(314159, 'sqlite') }657 set ::b [db incrblob t1 b 314159]658 fconfigure $::b -translation binary659 set rc [catch {sqlite3_blob_write $::b 10 HELLO -1} msg]660 lappend rc $msg661} {1 SQLITE_ERROR}662do_test incrblob-8.2 {663 sqlite3_errcode db664} {SQLITE_ERROR}665do_test incrblob-8.3 {666 set rc [catch {sqlite3_blob_write $::b -1 HELLO 5} msg]667 lappend rc $msg668} {1 SQLITE_ERROR}669do_test incrblob-8.4 {670 sqlite3_errcode db671} {SQLITE_ERROR}672do_test incrblob-8.5 {673 execsql {SELECT b FROM t1 WHERE a = 314159}674} {sqlite}675do_test incrblob-8.6 {676 set rc [catch {sqlite3_blob_write $::b 0 etilqs 6} msg]677 lappend rc $msg678} {0 {}}679do_test incrblob-8.7 {680 execsql {SELECT b FROM t1 WHERE a = 314159}681} {etilqs}682 683# The following test case exposes an instance in the blob code where684# an error message was set using a call similar to sqlite3_mprintf(zErr),685# where zErr is an arbitrary string. This is no good if the string contains686# characters that can be mistaken for printf() formatting directives.687#688do_test incrblob-9.1 {689 list [catch { db incrblob t1 "A tricky column name %s%s" 1 } msg] $msg690} {1 {no such column: "A tricky column name %s%s"}}691 692 693finish_test694