gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/incrblob.test (about) 1 # 2007 May 1 2 # 3 # The author disclaims copyright to this source code. In place of 4 # 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 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 ifcapable {!autovacuum || !pragma || !incrblob} { 19 finish_test 20 return 21 } 22 23 do_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 31 do_test incrblob-1.2.1 { 32 set ::blob [db incrblob blobs v 1] 33 string match incrblob_* $::blob 34 } {1} 35 unset -nocomplain data 36 do_test incrblob-1.2.2 { 37 binary scan [read $::blob] c* data 38 set data 39 } {1 2 3 4 5 6 7 8 9 10} 40 do_test incrblob-1.2.3 { 41 seek $::blob 0 42 puts -nonewline $::blob "1234567890" 43 flush $::blob 44 } {} 45 do_test incrblob-1.2.4 { 46 seek $::blob 0 47 binary scan [read $::blob] c* data 48 set data 49 } {49 50 51 52 53 54 55 56 57 48} 50 do_test incrblob-1.2.5 { 51 close $::blob 52 } {} 53 do_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 write 61 # regions of a blob that lie on overflow pages. 62 # 63 do_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 70 do_test incrblob-1.3.2 { 71 set ::blob [db incrblob blobs v 3] 72 seek $::blob 8500 73 read $::blob 10 74 } {..........} 75 do_test incrblob-1.3.3 { 76 seek $::blob 8500 77 puts -nonewline $::blob 1234567890 78 } {} 79 do_test incrblob-1.3.4 { 80 seek $::blob 8496 81 read $::blob 10 82 } {....123456} 83 do_test incrblob-1.3.10 { 84 close $::blob 85 } {} 86 87 #------------------------------------------------------------------------ 88 # incrblob-2.*: 89 # 90 # Test that the following operations use ptrmap pages to reduce 91 # unnecessary reads: 92 # 93 # * Reading near the end of a blob, 94 # * Writing near the end of a blob, and 95 # * SELECT a column value that is located on an overflow page. 96 # 97 proc nRead {db} { 98 set bt [btree_from_db $db] 99 db_enter $db 100 array set stats [btree_pager_stats $bt] 101 db_leave $db 102 return $stats(read) 103 } 104 proc nWrite {db} { 105 set bt [btree_from_db $db] 106 db_enter $db 107 array set stats [btree_pager_stats $bt] 108 db_leave $db 109 return $stats(write) 110 } 111 112 sqlite3_soft_heap_limit 0 113 114 foreach AutoVacuumMode [list 0 1] { 115 116 if {$AutoVacuumMode>0} { 117 ifcapable !autovacuum { 118 break 119 } 120 } 121 122 db close 123 forcedelete test.db test.db-journal 124 125 sqlite3 db test.db 126 execsql "PRAGMA mmap_size = 0" 127 execsql "PRAGMA auto_vacuum = $AutoVacuumMode" 128 129 # Extra value added to size answers 130 set ib2_extra 0 131 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]/1024 144 } [expr 31 + $ib2_extra] 145 146 ifcapable autovacuum { 147 do_test incrblob-2.$AutoVacuumMode.2 { 148 execsql { 149 PRAGMA auto_vacuum; 150 } 151 } $AutoVacuumMode 152 } 153 154 do_test incrblob-2.$AutoVacuumMode.3 { 155 # Open and close the db to make sure the page cache is empty. 156 db close 157 sqlite3 db test.db 158 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 end 163 set ::fragment [read $::blob] 164 close $::blob 165 166 # If the database is not in auto-vacuum mode, the whole of 167 # 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 db 171 } [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 end 175 } $::fragment 176 177 do_test incrblob-2.$AutoVacuumMode.5 { 178 # Open and close the db to make sure the page cache is empty. 179 db close 180 sqlite3 db test.db 181 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 end 187 puts -nonewline $::blob "1234567890abcdefghij" 188 flush $::blob 189 190 # If the database is not in auto-vacuum mode, the whole of 191 # 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 db 195 } [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 $::blob 200 nWrite db 201 } 2 202 203 do_test incrblob-2.$AutoVacuumMode.7 { 204 string range [db one {SELECT v FROM blobs}] end-39 end-20 205 } "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 close 210 sqlite3 db test.db 211 execsql { PRAGMA mmap_size = 0 } 212 213 execsql { SELECT i FROM blobs } 214 } {45} 215 216 do_test incrblob-2.$AutoVacuumMode.9 { 217 nRead db 218 } [expr $AutoVacuumMode ? 4 : 30 + $ib2_extra] 219 } 220 sqlite3_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 # 227 do_test incrblob-3.1 { 228 set ::blob [db incrblob -readonly blobs v 1] 229 seek $::blob -40 end 230 read $::blob 20 231 } "1234567890abcdefghij" 232 do_test incrblob-3.2 { 233 seek $::blob 0 234 set rc [catch { 235 puts -nonewline $::blob "helloworld" 236 } msg] 237 close $::blob 238 list $rc $msg 239 } "1 {channel \"$::blob\" wasn't opened for writing}" 240 241 do_test incrblob-3.3 { 242 set ::blob [db incrblob -readonly blobs v 1] 243 seek $::blob -40 end 244 read $::blob 20 245 } "1234567890abcdefghij" 246 do_test incrblob-3.4 { 247 set rc [catch { 248 sqlite3_blob_write $::blob 20 "qwertyuioplkjhgfds" 249 } msg] 250 list $rc $msg 251 } {1 SQLITE_READONLY} 252 catch {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 integer 265 # 4.6 - Attempt to open a real value 266 # 4.7 - Attempt to open an SQL null 267 # 268 # 4.8 - Attempt to open an indexed column for writing 269 # 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 # 274 do_test incrblob-4.1 { 275 set rc [catch { 276 set ::blob [db incrblob blobs v 2] 277 } msg ] 278 list $rc $msg 279 } {1 {no such rowid: 2}} 280 do_test incrblob-4.2 { 281 set rc [catch { 282 set ::blob [db incrblob blobs blue 1] 283 } msg ] 284 list $rc $msg 285 } {1 {no such column: "blue"}} 286 do_test incrblob-4.3 { 287 set rc [catch { 288 set ::blob [db incrblob nosuchtable blue 1] 289 } msg ] 290 list $rc $msg 291 } {1 {no such table: main.nosuchtable}} 292 do_test incrblob-4.4 { 293 set rc [catch { 294 set ::blob [db incrblob nosuchdb blobs v 1] 295 } msg ] 296 list $rc $msg 297 } {1 {no such table: nosuchdb.blobs}} 298 299 do_test incrblob-4.5 { 300 set rc [catch { 301 set ::blob [db incrblob blobs i 1] 302 } msg ] 303 list $rc $msg 304 } {1 {cannot open value of type integer}} 305 do_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 $msg 313 } {1 {cannot open value of type real}} 314 do_test incrblob-4.7 { 315 set rc [catch { 316 set ::blob [db incrblob blobs i 2] 317 } msg ] 318 list $rc $msg 319 } {1 {cannot open value of type null}} 320 321 do_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 $msg 329 } {1 {cannot open indexed column for writing}} 330 do_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 $msg 339 } {1 {cannot open value of type null}} 340 do_test incrblob-4.8.3 { 341 set rc [catch { 342 set ::blob [db incrblob -readonly t3 rowid 1] 343 } msg ] 344 list $rc $msg 345 } {1 {no such column: "rowid"}} 346 347 do_test incrblob-4.9.1 { 348 set rc [catch { 349 set ::blob [db incrblob -readonly blobs k 3] 350 } msg] 351 } {0} 352 do_test incrblob-4.9.2 { 353 binary scan [read $::blob] c* c 354 close $::blob 355 set c 356 } {1 2 3 4 5 6 7 8 9} 357 358 do_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 $msg 362 } {1 SQLITE_ERROR} 363 do_test incrblob-4.10.2 { 364 close $::blob 365 } {} 366 367 ifcapable 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 $msg 372 } {1 {cannot open view: blobs_view}} 373 } 374 ifcapable 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 $msg 380 } {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 # 389 ifcapable attach { 390 do_test incrblob-5.1 { 391 forcedelete test2.db test2.db-journal 392 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 binary 400 set fd2 [open $::cmdlinearg(INFO_SCRIPT)] 401 fconfigure $fd2 -translation binary 402 puts -nonewline $fd [read $fd2] 403 close $fd 404 close $fd2 405 set ::text [db one {select text from aux.files}] 406 string length $::text 407 } [file size $::cmdlinearg(INFO_SCRIPT)] 408 do_test incrblob-5.2 { 409 set fd2 [open $::cmdlinearg(INFO_SCRIPT)] 410 fconfigure $fd2 -translation binary 411 set ::data [read $fd2] 412 close $fd2 413 set ::data 414 } $::text 415 } 416 417 # free memory 418 unset -nocomplain ::data 419 unset -nocomplain ::text 420 421 #------------------------------------------------------------------------ 422 # incrblob-6.*: 423 # 424 # Test that opening a blob for write-access is impossible if 425 # another connection has the database RESERVED lock. 426 # 427 # Then test that blob writes that take place inside of a 428 # transaction are not visible to external connections until 429 # 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 upgrade 435 # to an exclusive lock when writing earlier than usual, which 436 # makes some of these tests fail. 437 # 438 sqlite3_soft_heap_limit 0 439 if {[permutation] != "memsubsys1"} { 440 do_test incrblob-6.1 { 441 sqlite3 db2 test.db 442 execsql { 443 BEGIN; 444 INSERT INTO blobs(k, v, i) VALUES('a', 'different', 'connection'); 445 } db2 446 } {} 447 do_test incrblob-6.2 { 448 execsql { 449 SELECT rowid FROM blobs ORDER BY rowid 450 } 451 } {1 2 3} 452 do_test incrblob-6.3 { 453 set rc [catch { 454 db incrblob blobs v 1 455 } msg] 456 list $rc $msg 457 } {1 {database is locked}} 458 do_test incrblob-6.4 { 459 set rc [catch { 460 db incrblob blobs v 3 461 } msg] 462 list $rc $msg 463 } {1 {database is locked}} 464 do_test incrblob-6.5 { 465 set ::blob [db incrblob -readonly blobs v 3] 466 read $::blob 467 } {hello} 468 do_test incrblob-6.6 { 469 close $::blob 470 } {} 471 472 do_test incrblob-6.7 { 473 set ::blob [db2 incrblob blobs i 4] 474 gets $::blob 475 } {connection} 476 do_test incrblob-6.8 { 477 tell $::blob 478 } {10} 479 do_test incrblob-6.9 { 480 seek $::blob 0 481 puts -nonewline $::blob "invocation" 482 flush $::blob 483 } {} 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 } db2 492 } {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 $::blob 501 } {} 502 do_test incrblob-6.14 { 503 catchsql { 504 COMMIT; 505 } db2 506 } {0 {}} 507 do_test incrblob-6.15 { 508 execsql { 509 SELECT * FROM blobs WHERE rowid = 4; 510 } 511 } {a different invocation} 512 db2 close 513 } 514 sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit) 515 516 #----------------------------------------------------------------------- 517 # The following tests verify the behavior of the incremental IO 518 # 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 part 523 # of an open blob is moved. 524 # 525 # 7.3 An INCREMENTAL VACUUM moves an overflow page that is part 526 # of an open blob. 527 # 528 # In the first case above, correct behavior is for all subsequent 529 # read/write operations on the blob-handle to return SQLITE_ABORT. 530 # More accurately, blob-handles are invalidated whenever the table 531 # they belong to is written to. 532 # 533 # The second two cases have no external effect. They are testing 534 # that the internal cache of overflow page numbers is correctly 535 # invalidated. 536 # 537 do_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 547 foreach {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* c 556 set c 557 } {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 $msg 566 } {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 $msg 575 } {0 {}} 576 do_test incrblob-7.1.$tn.6 { 577 execsql { 578 SELECT d FROM t1; 579 } 580 } {15} 581 582 } 583 584 set fd [open $::cmdlinearg(INFO_SCRIPT)] 585 fconfigure $fd -translation binary 586 set ::data [read $fd 14000] 587 close $fd 588 589 db close 590 forcedelete test.db test.db-journal 591 sqlite3 db test.db 592 593 do_test incrblob-7.2.1 { 594 execsql { 595 PRAGMA auto_vacuum = "incremental"; 596 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); -- root@page3 597 INSERT INTO t1 VALUES(123, $::data); 598 } 599 set ::b [db incrblob -readonly t1 b 123] 600 fconfigure $::b -translation binary 601 read $::b 602 } $::data 603 do_test incrblob-7.2.2 { 604 execsql { 605 CREATE TABLE t2(a INTEGER PRIMARY KEY, b); -- root@page4 606 } 607 seek $::b 0 608 read $::b 609 } $::data 610 do_test incrblob-7.2.3 { 611 close $::b 612 execsql { 613 SELECT rootpage FROM sqlite_master; 614 } 615 } {3 4} 616 617 set ::otherdata "[string range $::data 0 1000][string range $::data 1001 end]" 618 do_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 binary 624 read $::b 625 } $::otherdata 626 do_test incrblob-7.3.2 { 627 expr [file size test.db]/1024 628 } 30 629 do_test incrblob-7.3.3 { 630 execsql { 631 DELETE FROM t1 WHERE a = 123; 632 PRAGMA INCREMENTAL_VACUUM(0); 633 } 634 seek $::b 0 635 read $::b 636 } $::otherdata 637 638 # Attempt to write on a read-only blob. Make sure the error code 639 # gets set. Ticket #2464. 640 # 641 do_test incrblob-7.4 { 642 set rc [catch {sqlite3_blob_write $::b 10 HELLO} msg] 643 lappend rc $msg 644 } {1 SQLITE_READONLY} 645 do_test incrblob-7.5 { 646 sqlite3_errcode db 647 } {SQLITE_READONLY} 648 do_test incrblob-7.6 { 649 sqlite3_errmsg db 650 } {attempt to write a readonly database} 651 652 # Test that if either the "offset" or "amount" arguments to 653 # sqlite3_blob_write() are less than zero, SQLITE_ERROR is returned. 654 # 655 do_test incrblob-8.1 { 656 execsql { INSERT INTO t1 VALUES(314159, 'sqlite') } 657 set ::b [db incrblob t1 b 314159] 658 fconfigure $::b -translation binary 659 set rc [catch {sqlite3_blob_write $::b 10 HELLO -1} msg] 660 lappend rc $msg 661 } {1 SQLITE_ERROR} 662 do_test incrblob-8.2 { 663 sqlite3_errcode db 664 } {SQLITE_ERROR} 665 do_test incrblob-8.3 { 666 set rc [catch {sqlite3_blob_write $::b -1 HELLO 5} msg] 667 lappend rc $msg 668 } {1 SQLITE_ERROR} 669 do_test incrblob-8.4 { 670 sqlite3_errcode db 671 } {SQLITE_ERROR} 672 do_test incrblob-8.5 { 673 execsql {SELECT b FROM t1 WHERE a = 314159} 674 } {sqlite} 675 do_test incrblob-8.6 { 676 set rc [catch {sqlite3_blob_write $::b 0 etilqs 6} msg] 677 lappend rc $msg 678 } {0 {}} 679 do_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 where 684 # 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 contains 686 # characters that can be mistaken for printf() formatting directives. 687 # 688 do_test incrblob-9.1 { 689 list [catch { db incrblob t1 "A tricky column name %s%s" 1 } msg] $msg 690 } {1 {no such column: "A tricky column name %s%s"}} 691 692 693 finish_test