gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/shared.test (about) 1 # 2005 December 30 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: shared.test,v 1.36 2009/03/16 13:19:36 danielk1977 Exp $ 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 db close 17 18 # These tests cannot be run without the ATTACH command. 19 # 20 ifcapable !shared_cache||!attach { 21 finish_test 22 return 23 } 24 25 set ::enable_shared_cache [sqlite3_enable_shared_cache 1] 26 27 foreach av [list 0 1] { 28 29 # Open the database connection and execute the auto-vacuum pragma 30 forcedelete test.db 31 sqlite3 db test.db 32 33 ifcapable autovacuum { 34 do_test shared-[expr $av+1].1.0 { 35 execsql "pragma auto_vacuum=$::av" 36 execsql {pragma auto_vacuum} 37 } "$av" 38 } else { 39 if {$av} { 40 db close 41 break 42 } 43 } 44 45 # if we're using proxy locks, we use 2 filedescriptors for a db 46 # that is open but NOT yet locked, after a lock is taken we'll have 3, 47 # normally sqlite uses 1 (proxy locking adds the conch and the local lock) 48 set using_proxy 0 49 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] { 50 set using_proxy $value 51 } 52 set extrafds_prelock 0 53 set extrafds_postlock 0 54 if {$using_proxy>0} { 55 set extrafds_prelock 1 56 set extrafds_postlock 2 57 } 58 59 # $av is currently 0 if this loop iteration is to test with auto-vacuum turned 60 # off, and 1 if it is turned on. Increment it so that (1 -> no auto-vacuum) 61 # and (2 -> auto-vacuum). The sole reason for this is so that it looks nicer 62 # when we use this variable as part of test-case names. 63 # 64 incr av 65 66 # Test organization: 67 # 68 # shared-1.*: Simple test to verify basic sanity of table level locking when 69 # two connections share a pager cache. 70 # shared-2.*: Test that a read transaction can co-exist with a 71 # write-transaction, including a simple test to ensure the 72 # external locking protocol is still working. 73 # shared-3.*: Simple test of read-uncommitted mode. 74 # shared-4.*: Check that the schema is locked and unlocked correctly. 75 # shared-5.*: Test that creating/dropping schema items works when databases 76 # are attached in different orders to different handles. 77 # shared-6.*: Locking, UNION ALL queries and sub-queries. 78 # shared-7.*: Autovacuum and shared-cache. 79 # shared-8.*: Tests related to the text encoding of shared-cache databases. 80 # shared-9.*: TEMP triggers and shared-cache databases. 81 # shared-10.*: Tests of sqlite3_close(). 82 # shared-11.*: Test transaction locking. 83 # 84 85 do_test shared-$av.1.1 { 86 # Open a second database on the file test.db. It should use the same pager 87 # cache and schema as the original connection. Verify that only 1 file is 88 # opened. 89 sqlite3 db2 test.db 90 set ::sqlite_open_file_count 91 expr $sqlite_open_file_count-$extrafds_postlock 92 } {1} 93 do_test shared-$av.1.2 { 94 # Add a table and a single row of data via the first connection. 95 # Ensure that the second connection can see them. 96 execsql { 97 CREATE TABLE abc(a, b, c); 98 INSERT INTO abc VALUES(1, 2, 3); 99 } db 100 execsql { 101 SELECT * FROM abc; 102 } db2 103 } {1 2 3} 104 do_test shared-$av.1.3 { 105 # Have the first connection begin a transaction and obtain a read-lock 106 # on table abc. This should not prevent the second connection from 107 # querying abc. 108 execsql { 109 BEGIN; 110 SELECT * FROM abc; 111 } 112 execsql { 113 SELECT * FROM abc; 114 } db2 115 } {1 2 3} 116 do_test shared-$av.1.4 { 117 # Try to insert a row into abc via connection 2. This should fail because 118 # of the read-lock connection 1 is holding on table abc (obtained in the 119 # previous test case). 120 catchsql { 121 INSERT INTO abc VALUES(4, 5, 6); 122 } db2 123 } {1 {database table is locked: abc}} 124 do_test shared-$av.1.5 { 125 # Using connection 2 (the one without the open transaction), try to create 126 # a new table. This should fail because of the open read transaction 127 # held by connection 1. 128 catchsql { 129 CREATE TABLE def(d, e, f); 130 } db2 131 } {1 {database table is locked: sqlite_master}} 132 do_test shared-$av.1.6 { 133 # Upgrade connection 1's transaction to a write transaction. Create 134 # a new table - def - and insert a row into it. Because the connection 1 135 # transaction modifies the schema, it should not be possible for 136 # connection 2 to access the database at all until the connection 1 137 # has finished the transaction. 138 execsql { 139 CREATE TABLE def(d, e, f); 140 INSERT INTO def VALUES('IV', 'V', 'VI'); 141 } 142 } {} 143 do_test shared-$av.1.7 { 144 # Read from the sqlite_master table with connection 1 (inside the 145 # transaction). Then test that we can not do this with connection 2. This 146 # is because of the schema-modified lock established by connection 1 147 # in the previous test case. 148 execsql { 149 SELECT * FROM sqlite_master; 150 } 151 catchsql { 152 SELECT * FROM sqlite_master; 153 } db2 154 } {1 {database schema is locked: main}} 155 do_test shared-$av.1.8 { 156 # Commit the connection 1 transaction. 157 execsql { 158 COMMIT; 159 } 160 } {} 161 162 do_test shared-$av.2.1 { 163 # Open connection db3 to the database. 164 if {$::tcl_platform(platform)=="unix"} { 165 sqlite3 db3 "file:test.db?cache=private" -uri 1 166 } else { 167 sqlite3 db3 TEST.DB 168 } 169 set ::sqlite_open_file_count 170 expr $sqlite_open_file_count-($extrafds_prelock+$extrafds_postlock) 171 } {2} 172 do_test shared-$av.2.2 { 173 # Start read transactions on db and db2 (the shared pager cache). Ensure 174 # db3 cannot write to the database. 175 execsql { 176 BEGIN; 177 SELECT * FROM abc; 178 } 179 execsql { 180 BEGIN; 181 SELECT * FROM abc; 182 } db2 183 catchsql { 184 INSERT INTO abc VALUES(1, 2, 3); 185 } db2 186 } {1 {database table is locked: abc}} 187 do_test shared-$av.2.3 { 188 # Turn db's transaction into a write-transaction. db3 should still be 189 # able to read from table def (but will not see the new row). Connection 190 # db2 should not be able to read def (because of the write-lock). 191 192 # Todo: The failed "INSERT INTO abc ..." statement in the above test 193 # has started a write-transaction on db2 (should this be so?). This 194 # would prevent connection db from starting a write-transaction. So roll the 195 # db2 transaction back and replace it with a new read transaction. 196 execsql { 197 ROLLBACK; 198 BEGIN; 199 SELECT * FROM abc; 200 } db2 201 202 execsql { 203 INSERT INTO def VALUES('VII', 'VIII', 'IX'); 204 } 205 concat [ 206 catchsql { SELECT * FROM def; } db3 207 ] [ 208 catchsql { SELECT * FROM def; } db2 209 ] 210 } {0 {IV V VI} 1 {database table is locked: def}} 211 do_test shared-$av.2.4 { 212 # Commit the open transaction on db. db2 still holds a read-transaction. 213 # This should prevent db3 from writing to the database, but not from 214 # reading. 215 execsql { 216 COMMIT; 217 } 218 concat [ 219 catchsql { SELECT * FROM def; } db3 220 ] [ 221 catchsql { INSERT INTO def VALUES('X', 'XI', 'XII'); } db3 222 ] 223 } {0 {IV V VI VII VIII IX} 1 {database is locked}} 224 225 catchsql COMMIT db2 226 227 do_test shared-$av.3.1.1 { 228 # This test case starts a linear scan of table 'seq' using a 229 # read-uncommitted connection. In the middle of the scan, rows are added 230 # to the end of the seq table (ahead of the current cursor position). 231 # The uncommitted rows should be included in the results of the scan. 232 execsql " 233 CREATE TABLE seq(i PRIMARY KEY, x); 234 INSERT INTO seq VALUES(1, '[string repeat X 500]'); 235 INSERT INTO seq VALUES(2, '[string repeat X 500]'); 236 " 237 execsql {SELECT * FROM sqlite_master} db2 238 execsql {PRAGMA read_uncommitted = 1} db2 239 240 set ret [list] 241 db2 eval {SELECT i FROM seq ORDER BY i} { 242 if {$i < 4} { 243 set max [execsql {SELECT max(i) FROM seq}] 244 db eval { 245 INSERT INTO seq SELECT i + :max, x FROM seq; 246 } 247 } 248 lappend ret $i 249 } 250 set ret 251 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} 252 do_test shared-$av.3.1.2 { 253 # Another linear scan through table seq using a read-uncommitted connection. 254 # This time, delete each row as it is read. Should not affect the results of 255 # the scan, but the table should be empty after the scan is concluded 256 # (test 3.1.3 verifies this). 257 set ret [list] 258 db2 eval {SELECT i FROM seq} { 259 db eval {DELETE FROM seq WHERE i = :i} 260 lappend ret $i 261 } 262 set ret 263 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} 264 do_test shared-$av.3.1.3 { 265 execsql { 266 SELECT * FROM seq; 267 } 268 } {} 269 270 catch {db close} 271 catch {db2 close} 272 catch {db3 close} 273 274 #-------------------------------------------------------------------------- 275 # Tests shared-4.* test that the schema locking rules are applied 276 # correctly. i.e.: 277 # 278 # 1. All transactions require a read-lock on the schemas of databases they 279 # access. 280 # 2. Transactions that modify a database schema require a write-lock on that 281 # schema. 282 # 3. It is not possible to compile a statement while another handle has a 283 # write-lock on the schema. 284 # 285 286 # Open two database handles db and db2. Each has a single attach database 287 # (as well as main): 288 # 289 # db.main -> ./test.db 290 # db.test2 -> ./test2.db 291 # db2.main -> ./test2.db 292 # db2.test -> ./test.db 293 # 294 forcedelete test.db 295 forcedelete test2.db 296 forcedelete test2.db-journal 297 sqlite3 db test.db 298 sqlite3 db2 test2.db 299 do_test shared-$av.4.1.1 { 300 set sqlite_open_file_count 301 expr $sqlite_open_file_count-($extrafds_prelock*2) 302 } {2} 303 do_test shared-$av.4.1.2 { 304 execsql {ATTACH 'test2.db' AS test2} 305 set sqlite_open_file_count 306 expr $sqlite_open_file_count-($extrafds_postlock*2) 307 } {2} 308 do_test shared-$av.4.1.3 { 309 execsql {ATTACH 'test.db' AS test} db2 310 set sqlite_open_file_count 311 expr $sqlite_open_file_count-($extrafds_postlock*2) 312 } {2} 313 314 # Sanity check: Create a table in ./test.db via handle db, and test that handle 315 # db2 can "see" the new table immediately. A handle using a seperate pager 316 # cache would have to reload the database schema before this were possible. 317 # 318 do_test shared-$av.4.2.1 { 319 execsql { 320 CREATE TABLE abc(a, b, c); 321 CREATE TABLE def(d, e, f); 322 INSERT INTO abc VALUES('i', 'ii', 'iii'); 323 INSERT INTO def VALUES('I', 'II', 'III'); 324 } 325 } {} 326 do_test shared-$av.4.2.2 { 327 execsql { 328 SELECT * FROM test.abc; 329 } db2 330 } {i ii iii} 331 332 # Open a read-transaction and read from table abc via handle 2. Check that 333 # handle 1 can read table abc. Check that handle 1 cannot modify table abc 334 # or the database schema. Then check that handle 1 can modify table def. 335 # 336 do_test shared-$av.4.3.1 { 337 execsql { 338 BEGIN; 339 SELECT * FROM test.abc; 340 } db2 341 } {i ii iii} 342 do_test shared-$av.4.3.2 { 343 catchsql { 344 INSERT INTO abc VALUES('iv', 'v', 'vi'); 345 } 346 } {1 {database table is locked: abc}} 347 do_test shared-$av.4.3.3 { 348 catchsql { 349 CREATE TABLE ghi(g, h, i); 350 } 351 } {1 {database table is locked: sqlite_master}} 352 do_test shared-$av.4.3.3 { 353 catchsql { 354 INSERT INTO def VALUES('IV', 'V', 'VI'); 355 } 356 } {0 {}} 357 do_test shared-$av.4.3.4 { 358 # Cleanup: commit the transaction opened by db2. 359 execsql { 360 COMMIT 361 } db2 362 } {} 363 364 # Open a write-transaction using handle 1 and modify the database schema. 365 # Then try to execute a compiled statement to read from the same 366 # database via handle 2 (fails to get the lock on sqlite_master). Also 367 # try to compile a read of the same database using handle 2 (also fails). 368 # Finally, compile a read of the other database using handle 2. This 369 # should also fail. 370 # 371 ifcapable compound { 372 do_test shared-$av.4.4.1.2 { 373 # Sanity check 1: Check that the schema is what we think it is when viewed 374 # via handle 1. 375 execsql { 376 CREATE TABLE test2.ghi(g, h, i); 377 SELECT 'test.db:'||name FROM sqlite_master 378 UNION ALL 379 SELECT 'test2.db:'||name FROM test2.sqlite_master; 380 } 381 } {test.db:abc test.db:def test2.db:ghi} 382 do_test shared-$av.4.4.1.2 { 383 # Sanity check 2: Check that the schema is what we think it is when viewed 384 # via handle 2. 385 execsql { 386 SELECT 'test2.db:'||name FROM sqlite_master 387 UNION ALL 388 SELECT 'test.db:'||name FROM test.sqlite_master; 389 } db2 390 } {test2.db:ghi test.db:abc test.db:def} 391 } 392 393 do_test shared-$av.4.4.2 { 394 set ::DB2 [sqlite3_connection_pointer db2] 395 set sql {SELECT * FROM abc} 396 set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY] 397 execsql { 398 BEGIN; 399 CREATE TABLE jkl(j, k, l); 400 } 401 sqlite3_step $::STMT1 402 } {SQLITE_ERROR} 403 do_test shared-$av.4.4.3 { 404 sqlite3_finalize $::STMT1 405 } {SQLITE_LOCKED} 406 do_test shared-$av.4.4.4 { 407 set rc [catch { 408 set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY] 409 } msg] 410 list $rc $msg 411 } {1 {(6) database schema is locked: test}} 412 do_test shared-$av.4.4.5 { 413 set rc [catch { 414 set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY] 415 } msg] 416 list $rc $msg 417 } {1 {(6) database schema is locked: test}} 418 419 420 catch {db2 close} 421 catch {db close} 422 423 #-------------------------------------------------------------------------- 424 # Tests shared-5.* 425 # 426 foreach db [list test.db test1.db test2.db test3.db] { 427 forcedelete $db ${db}-journal 428 } 429 do_test shared-$av.5.1.1 { 430 sqlite3 db1 test.db 431 sqlite3 db2 test.db 432 execsql { 433 ATTACH 'test1.db' AS test1; 434 ATTACH 'test2.db' AS test2; 435 ATTACH 'test3.db' AS test3; 436 } db1 437 execsql { 438 ATTACH 'test3.db' AS test3; 439 ATTACH 'test2.db' AS test2; 440 ATTACH 'test1.db' AS test1; 441 } db2 442 } {} 443 do_test shared-$av.5.1.2 { 444 execsql { 445 CREATE TABLE test1.t1(a, b); 446 CREATE INDEX test1.i1 ON t1(a, b); 447 } db1 448 } {} 449 ifcapable view { 450 do_test shared-$av.5.1.3 { 451 execsql { 452 CREATE VIEW test1.v1 AS SELECT * FROM t1; 453 } db1 454 } {} 455 } 456 ifcapable trigger { 457 do_test shared-$av.5.1.4 { 458 execsql { 459 CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN 460 INSERT INTO t1 VALUES(new.a, new.b); 461 END; 462 } db1 463 } {} 464 } 465 do_test shared-$av.5.1.5 { 466 execsql { 467 DROP INDEX i1; 468 } db2 469 } {} 470 ifcapable view { 471 do_test shared-$av.5.1.6 { 472 execsql { 473 DROP VIEW v1; 474 } db2 475 } {} 476 } 477 ifcapable trigger { 478 do_test shared-$av.5.1.7 { 479 execsql { 480 DROP TRIGGER trig1; 481 } db2 482 } {} 483 } 484 do_test shared-$av.5.1.8 { 485 execsql { 486 DROP TABLE t1; 487 } db2 488 } {} 489 ifcapable compound { 490 do_test shared-$av.5.1.9 { 491 execsql { 492 SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master 493 } db1 494 } {} 495 } 496 497 #-------------------------------------------------------------------------- 498 # Tests shared-6.* test that a query obtains all the read-locks it needs 499 # before starting execution of the query. This means that there is no chance 500 # some rows of data will be returned before a lock fails and SQLITE_LOCK 501 # is returned. 502 # 503 do_test shared-$av.6.1.1 { 504 execsql { 505 CREATE TABLE t1(a, b); 506 CREATE TABLE t2(a, b); 507 INSERT INTO t1 VALUES(1, 2); 508 INSERT INTO t2 VALUES(3, 4); 509 } db1 510 } {} 511 ifcapable compound { 512 do_test shared-$av.6.1.2 { 513 execsql { 514 SELECT * FROM t1 UNION ALL SELECT * FROM t2; 515 } db2 516 } {1 2 3 4} 517 } 518 do_test shared-$av.6.1.3 { 519 # Establish a write lock on table t2 via connection db2. Then make a 520 # UNION all query using connection db1 that first accesses t1, followed 521 # by t2. If the locks are grabbed at the start of the statement (as 522 # they should be), no rows are returned. If (as was previously the case) 523 # they are grabbed as the tables are accessed, the t1 rows will be 524 # returned before the query fails. 525 # 526 execsql { 527 BEGIN; 528 INSERT INTO t2 VALUES(5, 6); 529 } db2 530 set ret [list] 531 catch { 532 db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} { 533 lappend ret $a $b 534 } 535 } 536 set ret 537 } {} 538 do_test shared-$av.6.1.4 { 539 execsql { 540 COMMIT; 541 BEGIN; 542 INSERT INTO t1 VALUES(7, 8); 543 } db2 544 set ret [list] 545 catch { 546 db1 eval { 547 SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2; 548 } { 549 lappend ret $d 550 } 551 } 552 set ret 553 } {} 554 555 catch {db1 close} 556 catch {db2 close} 557 foreach f [list test.db test2.db] { 558 forcedelete $f ${f}-journal 559 } 560 561 #-------------------------------------------------------------------------- 562 # Tests shared-7.* test auto-vacuum does not invalidate cursors from 563 # other shared-cache users when it reorganizes the database on 564 # COMMIT. 565 # 566 do_test shared-$av.7.1 { 567 # This test case sets up a test database in auto-vacuum mode consisting 568 # of two tables, t1 and t2. Both have a single index. Table t1 is 569 # populated first (so consists of pages toward the start of the db file), 570 # t2 second (pages toward the end of the file). 571 sqlite3 db test.db 572 sqlite3 db2 test.db 573 execsql { 574 BEGIN; 575 CREATE TABLE t1(a PRIMARY KEY, b); 576 CREATE TABLE t2(a PRIMARY KEY, b); 577 } 578 set ::contents {} 579 for {set i 0} {$i < 100} {incr i} { 580 set a [string repeat "$i " 20] 581 set b [string repeat "$i " 20] 582 db eval { 583 INSERT INTO t1 VALUES(:a, :b); 584 } 585 lappend ::contents [list [expr $i+1] $a $b] 586 } 587 execsql { 588 INSERT INTO t2 SELECT * FROM t1; 589 COMMIT; 590 } 591 } {} 592 do_test shared-$av.7.2 { 593 # This test case deletes the contents of table t1 (the one at the start of 594 # the file) while many cursors are open on table t2 and its index. All of 595 # the non-root pages will be moved from the end to the start of the file 596 # when the DELETE is committed - this test verifies that moving the pages 597 # does not disturb the open cursors. 598 # 599 600 proc lockrow {db tbl oids body} { 601 set ret [list] 602 db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" { 603 if {$i==[lindex $oids 0]} { 604 set noids [lrange $oids 1 end] 605 if {[llength $noids]==0} { 606 set subret [eval $body] 607 } else { 608 set subret [lockrow $db $tbl $noids $body] 609 } 610 } 611 lappend ret [list $i $a $b] 612 } 613 return [linsert $subret 0 $ret] 614 } 615 proc locktblrows {db tbl body} { 616 set oids [db eval "SELECT oid FROM $tbl"] 617 lockrow $db $tbl $oids $body 618 } 619 620 set scans [locktblrows db t2 { 621 execsql { 622 DELETE FROM t1; 623 } db2 624 }] 625 set error 0 626 627 # Test that each SELECT query returned the expected contents of t2. 628 foreach s $scans { 629 if {[lsort -integer -index 0 $s]!=$::contents} { 630 set error 1 631 } 632 } 633 set error 634 } {0} 635 636 catch {db close} 637 catch {db2 close} 638 unset -nocomplain contents 639 640 #-------------------------------------------------------------------------- 641 # The following tests try to trick the shared-cache code into assuming 642 # the wrong encoding for a database. 643 # 644 forcedelete test.db test.db-journal 645 ifcapable utf16 { 646 do_test shared-$av.8.1.1 { 647 sqlite3 db test.db 648 execsql { 649 PRAGMA encoding = 'UTF-16'; 650 SELECT * FROM sqlite_master; 651 } 652 } {} 653 do_test shared-$av.8.1.2 { 654 string range [execsql {PRAGMA encoding;}] 0 end-2 655 } {UTF-16} 656 657 do_test shared-$av.8.1.3 { 658 sqlite3 db2 test.db 659 execsql { 660 PRAGMA encoding = 'UTF-8'; 661 CREATE TABLE abc(a, b, c); 662 } db2 663 } {} 664 do_test shared-$av.8.1.4 { 665 execsql { 666 SELECT * FROM sqlite_master; 667 } 668 } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}" 669 do_test shared-$av.8.1.5 { 670 db2 close 671 execsql { 672 PRAGMA encoding; 673 } 674 } {UTF-8} 675 676 forcedelete test2.db test2.db-journal 677 do_test shared-$av.8.2.1 { 678 execsql { 679 ATTACH 'test2.db' AS aux; 680 SELECT * FROM aux.sqlite_master; 681 } 682 } {} 683 do_test shared-$av.8.2.2 { 684 sqlite3 db2 test2.db 685 execsql { 686 PRAGMA encoding = 'UTF-16'; 687 CREATE TABLE def(d, e, f); 688 } db2 689 string range [execsql {PRAGMA encoding;} db2] 0 end-2 690 } {UTF-16} 691 692 catch {db close} 693 catch {db2 close} 694 forcedelete test.db test2.db 695 696 do_test shared-$av.8.3.2 { 697 sqlite3 db test.db 698 execsql { CREATE TABLE def(d, e, f) } 699 execsql { PRAGMA encoding } 700 } {UTF-8} 701 do_test shared-$av.8.3.3 { 702 set zDb16 "[encoding convertto unicode test.db]\x00\x00" 703 set db16 [sqlite3_open16 $zDb16 {}] 704 705 set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY] 706 sqlite3_step $stmt 707 set sql [sqlite3_column_text $stmt 0] 708 sqlite3_finalize $stmt 709 set sql 710 } {CREATE TABLE def(d, e, f)} 711 do_test shared-$av.8.3.4 { 712 set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY] 713 sqlite3_step $stmt 714 set enc [sqlite3_column_text $stmt 0] 715 sqlite3_finalize $stmt 716 set enc 717 } {UTF-8} 718 719 sqlite3_close $db16 720 721 # Bug #2547 is causing this to fail. 722 if 0 { 723 do_test shared-$av.8.2.3 { 724 catchsql { 725 SELECT * FROM aux.sqlite_master; 726 } 727 } {1 {attached databases must use the same text encoding as main database}} 728 } 729 } 730 731 catch {db close} 732 catch {db2 close} 733 forcedelete test.db test2.db 734 735 #--------------------------------------------------------------------------- 736 # The following tests - shared-9.* - test interactions between TEMP triggers 737 # and shared-schemas. 738 # 739 ifcapable trigger&&tempdb { 740 741 do_test shared-$av.9.1 { 742 sqlite3 db test.db 743 sqlite3 db2 test.db 744 execsql { 745 CREATE TABLE abc(a, b, c); 746 CREATE TABLE abc_mirror(a, b, c); 747 CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN 748 INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c); 749 END; 750 INSERT INTO abc VALUES(1, 2, 3); 751 SELECT * FROM abc_mirror; 752 } 753 } {1 2 3} 754 do_test shared-$av.9.2 { 755 execsql { 756 INSERT INTO abc VALUES(4, 5, 6); 757 SELECT * FROM abc_mirror; 758 } db2 759 } {1 2 3} 760 do_test shared-$av.9.3 { 761 db close 762 db2 close 763 } {} 764 765 } ; # End shared-9.* 766 767 #--------------------------------------------------------------------------- 768 # The following tests - shared-10.* - test that the library behaves 769 # correctly when a connection to a shared-cache is closed. 770 # 771 do_test shared-$av.10.1 { 772 # Create a small sample database with two connections to it (db and db2). 773 forcedelete test.db 774 sqlite3 db test.db 775 sqlite3 db2 test.db 776 execsql { 777 CREATE TABLE ab(a PRIMARY KEY, b); 778 CREATE TABLE de(d PRIMARY KEY, e); 779 INSERT INTO ab VALUES('Chiang Mai', 100000); 780 INSERT INTO ab VALUES('Bangkok', 8000000); 781 INSERT INTO de VALUES('Ubon', 120000); 782 INSERT INTO de VALUES('Khon Kaen', 200000); 783 } 784 } {} 785 do_test shared-$av.10.2 { 786 # Open a read-transaction with the first connection, a write-transaction 787 # with the second. 788 execsql { 789 BEGIN; 790 SELECT * FROM ab; 791 } 792 execsql { 793 BEGIN; 794 INSERT INTO de VALUES('Pataya', 30000); 795 } db2 796 } {} 797 do_test shared-$av.10.3 { 798 # An external connection should be able to read the database, but not 799 # prepare a write operation. 800 if {$::tcl_platform(platform)=="unix"} { 801 sqlite3 db3 "file:test.db?cache=private" -uri 1 802 } else { 803 sqlite3 db3 TEST.DB 804 } 805 execsql { 806 SELECT * FROM ab; 807 } db3 808 catchsql { 809 BEGIN; 810 INSERT INTO de VALUES('Pataya', 30000); 811 } db3 812 } {1 {database is locked}} 813 do_test shared-$av.10.4 { 814 # Close the connection with the write-transaction open 815 db2 close 816 } {} 817 do_test shared-$av.10.5 { 818 # Test that the db2 transaction has been automatically rolled back. 819 # If it has not the ('Pataya', 30000) entry will still be in the table. 820 execsql { 821 SELECT * FROM de; 822 } 823 } {Ubon 120000 {Khon Kaen} 200000} 824 do_test shared-$av.10.5 { 825 # Closing db2 should have dropped the shared-cache back to a read-lock. 826 # So db3 should be able to prepare a write... 827 catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3 828 } {0 {}} 829 do_test shared-$av.10.6 { 830 # ... but not commit it. 831 catchsql {COMMIT} db3 832 } {1 {database is locked}} 833 do_test shared-$av.10.7 { 834 # Commit the (read-only) db transaction. Check via db3 to make sure the 835 # contents of table "de" are still as they should be. 836 execsql { 837 COMMIT; 838 } 839 execsql { 840 SELECT * FROM de; 841 } db3 842 } {Ubon 120000 {Khon Kaen} 200000 Pataya 30000} 843 do_test shared-$av.10.9 { 844 # Commit the external transaction. 845 catchsql {COMMIT} db3 846 } {0 {}} 847 integrity_check shared-$av.10.10 848 do_test shared-$av.10.11 { 849 db close 850 db3 close 851 } {} 852 853 do_test shared-$av.11.1 { 854 forcedelete test.db 855 sqlite3 db test.db 856 sqlite3 db2 test.db 857 execsql { 858 CREATE TABLE abc(a, b, c); 859 CREATE TABLE abc2(a, b, c); 860 BEGIN; 861 INSERT INTO abc VALUES(1, 2, 3); 862 } 863 } {} 864 do_test shared-$av.11.2 { 865 catchsql {BEGIN;} db2 866 catchsql {SELECT * FROM abc;} db2 867 } {1 {database table is locked: abc}} 868 do_test shared-$av.11.3 { 869 catchsql {BEGIN} db2 870 } {1 {cannot start a transaction within a transaction}} 871 do_test shared-$av.11.4 { 872 catchsql {SELECT * FROM abc2;} db2 873 } {0 {}} 874 do_test shared-$av.11.5 { 875 catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2 876 } {1 {database table is locked}} 877 do_test shared-$av.11.6 { 878 catchsql {SELECT * FROM abc2} 879 } {0 {}} 880 do_test shared-$av.11.6 { 881 execsql { 882 ROLLBACK; 883 PRAGMA read_uncommitted = 1; 884 } db2 885 } {} 886 do_test shared-$av.11.7 { 887 execsql { 888 INSERT INTO abc2 VALUES(4, 5, 6); 889 INSERT INTO abc2 VALUES(7, 8, 9); 890 } 891 } {} 892 do_test shared-$av.11.8 { 893 set res [list] 894 db2 eval { 895 SELECT abc.a as I, abc2.a as II FROM abc, abc2; 896 } { 897 execsql { 898 DELETE FROM abc WHERE 1; 899 } 900 lappend res $I $II 901 } 902 set res 903 } {1 4 {} 7} 904 if {[llength [info command sqlite3_shared_cache_report]]==1} { 905 ifcapable curdir { 906 do_test shared-$av.11.9 { 907 string tolower [sqlite3_shared_cache_report] 908 } [string tolower [list [file nativename [file normalize test.db]] 2]] 909 } 910 } 911 912 do_test shared-$av.11.11 { 913 db close 914 db2 close 915 } {} 916 917 # This tests that if it is impossible to free any pages, SQLite will 918 # exceed the limit set by PRAGMA cache_size. 919 forcedelete test.db test.db-journal 920 sqlite3 db test.db 921 ifcapable pager_pragmas { 922 do_test shared-$av.12.1 { 923 execsql { 924 PRAGMA cache_size = 10; 925 PRAGMA cache_size; 926 } 927 } {10} 928 } 929 do_test shared-$av.12.2 { 930 set ::db_handles [list] 931 for {set i 1} {$i < 15} {incr i} { 932 lappend ::db_handles db$i 933 sqlite3 db$i test.db 934 execsql "CREATE TABLE db${i}(a, b, c)" db$i 935 execsql "INSERT INTO db${i} VALUES(1, 2, 3)" 936 } 937 } {} 938 proc nested_select {handles} { 939 [lindex $handles 0] eval "SELECT * FROM [lindex $handles 0]" { 940 lappend ::res $a $b $c 941 if {[llength $handles]>1} { 942 nested_select [lrange $handles 1 end] 943 } 944 } 945 } 946 do_test shared-$av.12.3 { 947 set ::res [list] 948 nested_select $::db_handles 949 set ::res 950 } [string range [string repeat "1 2 3 " [llength $::db_handles]] 0 end-1] 951 952 do_test shared-$av.12.X { 953 db close 954 foreach h $::db_handles { 955 $h close 956 } 957 } {} 958 959 # Internally, locks are acquired on shared B-Tree structures in the order 960 # that the structures appear in the virtual memory address space. This 961 # test case attempts to cause the order of the structures in memory 962 # to be different from the order in which they are attached to a given 963 # database handle. This covers an extra line or two. 964 # 965 do_test shared-$av.13.1 { 966 forcedelete test2.db test3.db test4.db test5.db 967 sqlite3 db :memory: 968 execsql { 969 ATTACH 'test2.db' AS aux2; 970 ATTACH 'test3.db' AS aux3; 971 ATTACH 'test4.db' AS aux4; 972 ATTACH 'test5.db' AS aux5; 973 DETACH aux2; 974 DETACH aux3; 975 DETACH aux4; 976 ATTACH 'test2.db' AS aux2; 977 ATTACH 'test3.db' AS aux3; 978 ATTACH 'test4.db' AS aux4; 979 } 980 } {} 981 do_test shared-$av.13.2 { 982 execsql { 983 CREATE TABLE t1(a, b, c); 984 CREATE TABLE aux2.t2(a, b, c); 985 CREATE TABLE aux3.t3(a, b, c); 986 CREATE TABLE aux4.t4(a, b, c); 987 CREATE TABLE aux5.t5(a, b, c); 988 SELECT count(*) FROM 989 aux2.sqlite_master, 990 aux3.sqlite_master, 991 aux4.sqlite_master, 992 aux5.sqlite_master 993 } 994 } {1} 995 do_test shared-$av.13.3 { 996 db close 997 } {} 998 999 # Test that nothing horrible happens if a connection to a shared B-Tree 1000 # structure is closed while some other connection has an open cursor. 1001 # 1002 do_test shared-$av.14.1 { 1003 sqlite3 db test.db 1004 sqlite3 db2 test.db 1005 execsql {SELECT name FROM sqlite_master} 1006 } {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14} 1007 do_test shared-$av.14.2 { 1008 set res [list] 1009 db eval {SELECT name FROM sqlite_master} { 1010 if {$name eq "db7"} { 1011 db2 close 1012 } 1013 lappend res $name 1014 } 1015 set res 1016 } {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14} 1017 do_test shared-$av.14.3 { 1018 db close 1019 } {} 1020 1021 # Populate a database schema using connection [db]. Then drop it using 1022 # [db2]. This is to try to find any points where shared-schema elements 1023 # are allocated using the lookaside buffer of [db]. 1024 # 1025 # Mutexes are enabled for this test as that activates a couple of useful 1026 # assert() statements in the C code. 1027 # 1028 do_test shared-$av-15.1 { 1029 forcedelete test.db 1030 sqlite3 db test.db -fullmutex 1 1031 sqlite3 db2 test.db -fullmutex 1 1032 execsql { 1033 CREATE TABLE t1(a, b, c); 1034 CREATE INDEX i1 ON t1(a, b); 1035 CREATE VIEW v1 AS SELECT * FROM t1; 1036 CREATE VIEW v2 AS SELECT * FROM t1, v1 1037 WHERE t1.c=v1.c GROUP BY t1.a ORDER BY v1.b; 1038 CREATE TRIGGER tr1 AFTER INSERT ON t1 1039 WHEN new.a!=1 1040 BEGIN 1041 DELETE FROM t1 WHERE a=5; 1042 INSERT INTO t1 VALUES(1, 2, 3); 1043 UPDATE t1 SET c=c+1; 1044 END; 1045 1046 INSERT INTO t1 VALUES(5, 6, 7); 1047 INSERT INTO t1 VALUES(8, 9, 10); 1048 INSERT INTO t1 VALUES(11, 12, 13); 1049 ANALYZE; 1050 SELECT * FROM t1; 1051 } 1052 } {1 2 6 8 9 12 1 2 5 11 12 14 1 2 4} 1053 do_test shared-$av-15.2 { 1054 execsql { DROP TABLE t1 } db2 1055 } {} 1056 db close 1057 db2 close 1058 1059 # Shared cache on a :memory: database. This only works for URI filenames. 1060 # 1061 do_test shared-$av-16.1 { 1062 sqlite3 db1 file::memory: -uri 1 1063 sqlite3 db2 file::memory: -uri 1 1064 db1 eval { 1065 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3); 1066 } 1067 db2 eval { 1068 SELECT x FROM t1 ORDER BY x; 1069 } 1070 } {1 2 3} 1071 do_test shared-$av-16.2 { 1072 db2 eval { 1073 INSERT INTO t1 VALUES(99); 1074 DELETE FROM t1 WHERE x=2; 1075 } 1076 db1 eval { 1077 SELECT x FROM t1 ORDER BY x; 1078 } 1079 } {1 3 99} 1080 1081 # Verify that there is no cache sharing ordinary (non-URI) filenames are 1082 # used. 1083 # 1084 do_test shared-$av-16.3 { 1085 db1 close 1086 db2 close 1087 sqlite3 db1 :memory: 1088 sqlite3 db2 :memory: 1089 db1 eval { 1090 CREATE TABLE t1(x); INSERT INTO t1 VALUES(4),(5),(6); 1091 } 1092 catchsql { 1093 SELECT * FROM t1; 1094 } db2 1095 } {1 {no such table: t1}} 1096 1097 # Shared cache on named memory databases. 1098 # 1099 do_test shared-$av-16.4 { 1100 db1 close 1101 db2 close 1102 forcedelete test.db test.db-wal test.db-journal 1103 sqlite3 db1 file:test.db?mode=memory -uri 1 1104 sqlite3 db2 file:test.db?mode=memory -uri 1 1105 db1 eval { 1106 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3); 1107 } 1108 db2 eval { 1109 SELECT x FROM t1 ORDER BY x; 1110 } 1111 } {1 2 3} 1112 do_test shared-$av-16.5 { 1113 db2 eval { 1114 INSERT INTO t1 VALUES(99); 1115 DELETE FROM t1 WHERE x=2; 1116 } 1117 db1 eval { 1118 SELECT x FROM t1 ORDER BY x; 1119 } 1120 } {1 3 99} 1121 do_test shared-$av-16.6 { 1122 file exists test.db 1123 } {0} ;# Verify that the database is in-memory 1124 1125 # Shared cache on named memory databases with different names. 1126 # 1127 do_test shared-$av-16.7 { 1128 db1 close 1129 db2 close 1130 forcedelete test1.db test2.db 1131 sqlite3 db1 file:test1.db?mode=memory -uri 1 1132 sqlite3 db2 file:test2.db?mode=memory -uri 1 1133 db1 eval { 1134 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3); 1135 } 1136 catchsql { 1137 SELECT x FROM t1 ORDER BY x; 1138 } db2 1139 } {1 {no such table: t1}} 1140 do_test shared-$av-16.8 { 1141 file exists test1.db 1142 } {0} ;# Verify that the database is in-memory 1143 1144 # Shared cache on named memory databases attached to readonly connections. 1145 # 1146 if {![sqlite3 -has-codec]} { 1147 do_test shared-$av-16.8.1 { 1148 db1 close 1149 db2 close 1150 1151 sqlite3 db test1.db 1152 db eval { 1153 CREATE TABLE yy(a, b); 1154 INSERT INTO yy VALUES(77, 88); 1155 } 1156 db close 1157 1158 sqlite3 db1 test1.db -uri 1 -readonly 1 1159 sqlite3 db2 test2.db -uri 1 1160 1161 db1 eval { 1162 ATTACH 'file:mem?mode=memory&cache=shared' AS shared; 1163 CREATE TABLE shared.xx(a, b); 1164 INSERT INTO xx VALUES(55, 66); 1165 } 1166 db2 eval { 1167 ATTACH 'file:mem?mode=memory&cache=shared' AS shared; 1168 SELECT * FROM xx; 1169 } 1170 } {55 66} 1171 1172 do_test shared-$av-16.8.2 { db1 eval { SELECT * FROM yy } } {77 88} 1173 do_test shared-$av-16.8.3 { 1174 list [catch {db1 eval { INSERT INTO yy VALUES(1, 2) }} msg] $msg 1175 } {1 {attempt to write a readonly database}} 1176 1177 db1 close 1178 db2 close 1179 } 1180 1181 } ;# end of autovacuum on/off loop 1182 1183 sqlite3_enable_shared_cache $::enable_shared_cache 1184 finish_test