Annotation of embedaddon/sqlite3/test/shared.test, revision 1.1
1.1 ! misho 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. Use a different path to the same
! 164: # file so that db3 does *not* share the same pager cache as db and db2
! 165: # (there should be two open file handles).
! 166: if {$::tcl_platform(platform)=="unix"} {
! 167: sqlite3 db3 ./test.db
! 168: } else {
! 169: sqlite3 db3 TEST.DB
! 170: }
! 171: set ::sqlite_open_file_count
! 172: expr $sqlite_open_file_count-($extrafds_prelock+$extrafds_postlock)
! 173: } {2}
! 174: do_test shared-$av.2.2 {
! 175: # Start read transactions on db and db2 (the shared pager cache). Ensure
! 176: # db3 cannot write to the database.
! 177: execsql {
! 178: BEGIN;
! 179: SELECT * FROM abc;
! 180: }
! 181: execsql {
! 182: BEGIN;
! 183: SELECT * FROM abc;
! 184: } db2
! 185: catchsql {
! 186: INSERT INTO abc VALUES(1, 2, 3);
! 187: } db2
! 188: } {1 {database table is locked: abc}}
! 189: do_test shared-$av.2.3 {
! 190: # Turn db's transaction into a write-transaction. db3 should still be
! 191: # able to read from table def (but will not see the new row). Connection
! 192: # db2 should not be able to read def (because of the write-lock).
! 193:
! 194: # Todo: The failed "INSERT INTO abc ..." statement in the above test
! 195: # has started a write-transaction on db2 (should this be so?). This
! 196: # would prevent connection db from starting a write-transaction. So roll the
! 197: # db2 transaction back and replace it with a new read transaction.
! 198: execsql {
! 199: ROLLBACK;
! 200: BEGIN;
! 201: SELECT * FROM abc;
! 202: } db2
! 203:
! 204: execsql {
! 205: INSERT INTO def VALUES('VII', 'VIII', 'IX');
! 206: }
! 207: concat [
! 208: catchsql { SELECT * FROM def; } db3
! 209: ] [
! 210: catchsql { SELECT * FROM def; } db2
! 211: ]
! 212: } {0 {IV V VI} 1 {database table is locked: def}}
! 213: do_test shared-$av.2.4 {
! 214: # Commit the open transaction on db. db2 still holds a read-transaction.
! 215: # This should prevent db3 from writing to the database, but not from
! 216: # reading.
! 217: execsql {
! 218: COMMIT;
! 219: }
! 220: concat [
! 221: catchsql { SELECT * FROM def; } db3
! 222: ] [
! 223: catchsql { INSERT INTO def VALUES('X', 'XI', 'XII'); } db3
! 224: ]
! 225: } {0 {IV V VI VII VIII IX} 1 {database is locked}}
! 226:
! 227: catchsql COMMIT db2
! 228:
! 229: do_test shared-$av.3.1.1 {
! 230: # This test case starts a linear scan of table 'seq' using a
! 231: # read-uncommitted connection. In the middle of the scan, rows are added
! 232: # to the end of the seq table (ahead of the current cursor position).
! 233: # The uncommitted rows should be included in the results of the scan.
! 234: execsql "
! 235: CREATE TABLE seq(i PRIMARY KEY, x);
! 236: INSERT INTO seq VALUES(1, '[string repeat X 500]');
! 237: INSERT INTO seq VALUES(2, '[string repeat X 500]');
! 238: "
! 239: execsql {SELECT * FROM sqlite_master} db2
! 240: execsql {PRAGMA read_uncommitted = 1} db2
! 241:
! 242: set ret [list]
! 243: db2 eval {SELECT i FROM seq ORDER BY i} {
! 244: if {$i < 4} {
! 245: set max [execsql {SELECT max(i) FROM seq}]
! 246: db eval {
! 247: INSERT INTO seq SELECT i + :max, x FROM seq;
! 248: }
! 249: }
! 250: lappend ret $i
! 251: }
! 252: set ret
! 253: } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
! 254: do_test shared-$av.3.1.2 {
! 255: # Another linear scan through table seq using a read-uncommitted connection.
! 256: # This time, delete each row as it is read. Should not affect the results of
! 257: # the scan, but the table should be empty after the scan is concluded
! 258: # (test 3.1.3 verifies this).
! 259: set ret [list]
! 260: db2 eval {SELECT i FROM seq} {
! 261: db eval {DELETE FROM seq WHERE i = :i}
! 262: lappend ret $i
! 263: }
! 264: set ret
! 265: } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
! 266: do_test shared-$av.3.1.3 {
! 267: execsql {
! 268: SELECT * FROM seq;
! 269: }
! 270: } {}
! 271:
! 272: catch {db close}
! 273: catch {db2 close}
! 274: catch {db3 close}
! 275:
! 276: #--------------------------------------------------------------------------
! 277: # Tests shared-4.* test that the schema locking rules are applied
! 278: # correctly. i.e.:
! 279: #
! 280: # 1. All transactions require a read-lock on the schemas of databases they
! 281: # access.
! 282: # 2. Transactions that modify a database schema require a write-lock on that
! 283: # schema.
! 284: # 3. It is not possible to compile a statement while another handle has a
! 285: # write-lock on the schema.
! 286: #
! 287:
! 288: # Open two database handles db and db2. Each has a single attach database
! 289: # (as well as main):
! 290: #
! 291: # db.main -> ./test.db
! 292: # db.test2 -> ./test2.db
! 293: # db2.main -> ./test2.db
! 294: # db2.test -> ./test.db
! 295: #
! 296: forcedelete test.db
! 297: forcedelete test2.db
! 298: forcedelete test2.db-journal
! 299: sqlite3 db test.db
! 300: sqlite3 db2 test2.db
! 301: do_test shared-$av.4.1.1 {
! 302: set sqlite_open_file_count
! 303: expr $sqlite_open_file_count-($extrafds_prelock*2)
! 304: } {2}
! 305: do_test shared-$av.4.1.2 {
! 306: execsql {ATTACH 'test2.db' AS test2}
! 307: set sqlite_open_file_count
! 308: expr $sqlite_open_file_count-($extrafds_postlock*2)
! 309: } {2}
! 310: do_test shared-$av.4.1.3 {
! 311: execsql {ATTACH 'test.db' AS test} db2
! 312: set sqlite_open_file_count
! 313: expr $sqlite_open_file_count-($extrafds_postlock*2)
! 314: } {2}
! 315:
! 316: # Sanity check: Create a table in ./test.db via handle db, and test that handle
! 317: # db2 can "see" the new table immediately. A handle using a seperate pager
! 318: # cache would have to reload the database schema before this were possible.
! 319: #
! 320: do_test shared-$av.4.2.1 {
! 321: execsql {
! 322: CREATE TABLE abc(a, b, c);
! 323: CREATE TABLE def(d, e, f);
! 324: INSERT INTO abc VALUES('i', 'ii', 'iii');
! 325: INSERT INTO def VALUES('I', 'II', 'III');
! 326: }
! 327: } {}
! 328: do_test shared-$av.4.2.2 {
! 329: execsql {
! 330: SELECT * FROM test.abc;
! 331: } db2
! 332: } {i ii iii}
! 333:
! 334: # Open a read-transaction and read from table abc via handle 2. Check that
! 335: # handle 1 can read table abc. Check that handle 1 cannot modify table abc
! 336: # or the database schema. Then check that handle 1 can modify table def.
! 337: #
! 338: do_test shared-$av.4.3.1 {
! 339: execsql {
! 340: BEGIN;
! 341: SELECT * FROM test.abc;
! 342: } db2
! 343: } {i ii iii}
! 344: do_test shared-$av.4.3.2 {
! 345: catchsql {
! 346: INSERT INTO abc VALUES('iv', 'v', 'vi');
! 347: }
! 348: } {1 {database table is locked: abc}}
! 349: do_test shared-$av.4.3.3 {
! 350: catchsql {
! 351: CREATE TABLE ghi(g, h, i);
! 352: }
! 353: } {1 {database table is locked: sqlite_master}}
! 354: do_test shared-$av.4.3.3 {
! 355: catchsql {
! 356: INSERT INTO def VALUES('IV', 'V', 'VI');
! 357: }
! 358: } {0 {}}
! 359: do_test shared-$av.4.3.4 {
! 360: # Cleanup: commit the transaction opened by db2.
! 361: execsql {
! 362: COMMIT
! 363: } db2
! 364: } {}
! 365:
! 366: # Open a write-transaction using handle 1 and modify the database schema.
! 367: # Then try to execute a compiled statement to read from the same
! 368: # database via handle 2 (fails to get the lock on sqlite_master). Also
! 369: # try to compile a read of the same database using handle 2 (also fails).
! 370: # Finally, compile a read of the other database using handle 2. This
! 371: # should also fail.
! 372: #
! 373: ifcapable compound {
! 374: do_test shared-$av.4.4.1.2 {
! 375: # Sanity check 1: Check that the schema is what we think it is when viewed
! 376: # via handle 1.
! 377: execsql {
! 378: CREATE TABLE test2.ghi(g, h, i);
! 379: SELECT 'test.db:'||name FROM sqlite_master
! 380: UNION ALL
! 381: SELECT 'test2.db:'||name FROM test2.sqlite_master;
! 382: }
! 383: } {test.db:abc test.db:def test2.db:ghi}
! 384: do_test shared-$av.4.4.1.2 {
! 385: # Sanity check 2: Check that the schema is what we think it is when viewed
! 386: # via handle 2.
! 387: execsql {
! 388: SELECT 'test2.db:'||name FROM sqlite_master
! 389: UNION ALL
! 390: SELECT 'test.db:'||name FROM test.sqlite_master;
! 391: } db2
! 392: } {test2.db:ghi test.db:abc test.db:def}
! 393: }
! 394:
! 395: do_test shared-$av.4.4.2 {
! 396: set ::DB2 [sqlite3_connection_pointer db2]
! 397: set sql {SELECT * FROM abc}
! 398: set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
! 399: execsql {
! 400: BEGIN;
! 401: CREATE TABLE jkl(j, k, l);
! 402: }
! 403: sqlite3_step $::STMT1
! 404: } {SQLITE_ERROR}
! 405: do_test shared-$av.4.4.3 {
! 406: sqlite3_finalize $::STMT1
! 407: } {SQLITE_LOCKED}
! 408: do_test shared-$av.4.4.4 {
! 409: set rc [catch {
! 410: set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
! 411: } msg]
! 412: list $rc $msg
! 413: } {1 {(6) database schema is locked: test}}
! 414: do_test shared-$av.4.4.5 {
! 415: set rc [catch {
! 416: set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY]
! 417: } msg]
! 418: list $rc $msg
! 419: } {1 {(6) database schema is locked: test}}
! 420:
! 421:
! 422: catch {db2 close}
! 423: catch {db close}
! 424:
! 425: #--------------------------------------------------------------------------
! 426: # Tests shared-5.*
! 427: #
! 428: foreach db [list test.db test1.db test2.db test3.db] {
! 429: forcedelete $db ${db}-journal
! 430: }
! 431: do_test shared-$av.5.1.1 {
! 432: sqlite3 db1 test.db
! 433: sqlite3 db2 test.db
! 434: execsql {
! 435: ATTACH 'test1.db' AS test1;
! 436: ATTACH 'test2.db' AS test2;
! 437: ATTACH 'test3.db' AS test3;
! 438: } db1
! 439: execsql {
! 440: ATTACH 'test3.db' AS test3;
! 441: ATTACH 'test2.db' AS test2;
! 442: ATTACH 'test1.db' AS test1;
! 443: } db2
! 444: } {}
! 445: do_test shared-$av.5.1.2 {
! 446: execsql {
! 447: CREATE TABLE test1.t1(a, b);
! 448: CREATE INDEX test1.i1 ON t1(a, b);
! 449: } db1
! 450: } {}
! 451: ifcapable view {
! 452: do_test shared-$av.5.1.3 {
! 453: execsql {
! 454: CREATE VIEW test1.v1 AS SELECT * FROM t1;
! 455: } db1
! 456: } {}
! 457: }
! 458: ifcapable trigger {
! 459: do_test shared-$av.5.1.4 {
! 460: execsql {
! 461: CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN
! 462: INSERT INTO t1 VALUES(new.a, new.b);
! 463: END;
! 464: } db1
! 465: } {}
! 466: }
! 467: do_test shared-$av.5.1.5 {
! 468: execsql {
! 469: DROP INDEX i1;
! 470: } db2
! 471: } {}
! 472: ifcapable view {
! 473: do_test shared-$av.5.1.6 {
! 474: execsql {
! 475: DROP VIEW v1;
! 476: } db2
! 477: } {}
! 478: }
! 479: ifcapable trigger {
! 480: do_test shared-$av.5.1.7 {
! 481: execsql {
! 482: DROP TRIGGER trig1;
! 483: } db2
! 484: } {}
! 485: }
! 486: do_test shared-$av.5.1.8 {
! 487: execsql {
! 488: DROP TABLE t1;
! 489: } db2
! 490: } {}
! 491: ifcapable compound {
! 492: do_test shared-$av.5.1.9 {
! 493: execsql {
! 494: SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master
! 495: } db1
! 496: } {}
! 497: }
! 498:
! 499: #--------------------------------------------------------------------------
! 500: # Tests shared-6.* test that a query obtains all the read-locks it needs
! 501: # before starting execution of the query. This means that there is no chance
! 502: # some rows of data will be returned before a lock fails and SQLITE_LOCK
! 503: # is returned.
! 504: #
! 505: do_test shared-$av.6.1.1 {
! 506: execsql {
! 507: CREATE TABLE t1(a, b);
! 508: CREATE TABLE t2(a, b);
! 509: INSERT INTO t1 VALUES(1, 2);
! 510: INSERT INTO t2 VALUES(3, 4);
! 511: } db1
! 512: } {}
! 513: ifcapable compound {
! 514: do_test shared-$av.6.1.2 {
! 515: execsql {
! 516: SELECT * FROM t1 UNION ALL SELECT * FROM t2;
! 517: } db2
! 518: } {1 2 3 4}
! 519: }
! 520: do_test shared-$av.6.1.3 {
! 521: # Establish a write lock on table t2 via connection db2. Then make a
! 522: # UNION all query using connection db1 that first accesses t1, followed
! 523: # by t2. If the locks are grabbed at the start of the statement (as
! 524: # they should be), no rows are returned. If (as was previously the case)
! 525: # they are grabbed as the tables are accessed, the t1 rows will be
! 526: # returned before the query fails.
! 527: #
! 528: execsql {
! 529: BEGIN;
! 530: INSERT INTO t2 VALUES(5, 6);
! 531: } db2
! 532: set ret [list]
! 533: catch {
! 534: db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
! 535: lappend ret $a $b
! 536: }
! 537: }
! 538: set ret
! 539: } {}
! 540: do_test shared-$av.6.1.4 {
! 541: execsql {
! 542: COMMIT;
! 543: BEGIN;
! 544: INSERT INTO t1 VALUES(7, 8);
! 545: } db2
! 546: set ret [list]
! 547: catch {
! 548: db1 eval {
! 549: SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2;
! 550: } {
! 551: lappend ret $d
! 552: }
! 553: }
! 554: set ret
! 555: } {}
! 556:
! 557: catch {db1 close}
! 558: catch {db2 close}
! 559: foreach f [list test.db test2.db] {
! 560: forcedelete $f ${f}-journal
! 561: }
! 562:
! 563: #--------------------------------------------------------------------------
! 564: # Tests shared-7.* test auto-vacuum does not invalidate cursors from
! 565: # other shared-cache users when it reorganizes the database on
! 566: # COMMIT.
! 567: #
! 568: do_test shared-$av.7.1 {
! 569: # This test case sets up a test database in auto-vacuum mode consisting
! 570: # of two tables, t1 and t2. Both have a single index. Table t1 is
! 571: # populated first (so consists of pages toward the start of the db file),
! 572: # t2 second (pages toward the end of the file).
! 573: sqlite3 db test.db
! 574: sqlite3 db2 test.db
! 575: execsql {
! 576: BEGIN;
! 577: CREATE TABLE t1(a PRIMARY KEY, b);
! 578: CREATE TABLE t2(a PRIMARY KEY, b);
! 579: }
! 580: set ::contents {}
! 581: for {set i 0} {$i < 100} {incr i} {
! 582: set a [string repeat "$i " 20]
! 583: set b [string repeat "$i " 20]
! 584: db eval {
! 585: INSERT INTO t1 VALUES(:a, :b);
! 586: }
! 587: lappend ::contents [list [expr $i+1] $a $b]
! 588: }
! 589: execsql {
! 590: INSERT INTO t2 SELECT * FROM t1;
! 591: COMMIT;
! 592: }
! 593: } {}
! 594: do_test shared-$av.7.2 {
! 595: # This test case deletes the contents of table t1 (the one at the start of
! 596: # the file) while many cursors are open on table t2 and its index. All of
! 597: # the non-root pages will be moved from the end to the start of the file
! 598: # when the DELETE is committed - this test verifies that moving the pages
! 599: # does not disturb the open cursors.
! 600: #
! 601:
! 602: proc lockrow {db tbl oids body} {
! 603: set ret [list]
! 604: db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" {
! 605: if {$i==[lindex $oids 0]} {
! 606: set noids [lrange $oids 1 end]
! 607: if {[llength $noids]==0} {
! 608: set subret [eval $body]
! 609: } else {
! 610: set subret [lockrow $db $tbl $noids $body]
! 611: }
! 612: }
! 613: lappend ret [list $i $a $b]
! 614: }
! 615: return [linsert $subret 0 $ret]
! 616: }
! 617: proc locktblrows {db tbl body} {
! 618: set oids [db eval "SELECT oid FROM $tbl"]
! 619: lockrow $db $tbl $oids $body
! 620: }
! 621:
! 622: set scans [locktblrows db t2 {
! 623: execsql {
! 624: DELETE FROM t1;
! 625: } db2
! 626: }]
! 627: set error 0
! 628:
! 629: # Test that each SELECT query returned the expected contents of t2.
! 630: foreach s $scans {
! 631: if {[lsort -integer -index 0 $s]!=$::contents} {
! 632: set error 1
! 633: }
! 634: }
! 635: set error
! 636: } {0}
! 637:
! 638: catch {db close}
! 639: catch {db2 close}
! 640: unset -nocomplain contents
! 641:
! 642: #--------------------------------------------------------------------------
! 643: # The following tests try to trick the shared-cache code into assuming
! 644: # the wrong encoding for a database.
! 645: #
! 646: forcedelete test.db test.db-journal
! 647: ifcapable utf16 {
! 648: do_test shared-$av.8.1.1 {
! 649: sqlite3 db test.db
! 650: execsql {
! 651: PRAGMA encoding = 'UTF-16';
! 652: SELECT * FROM sqlite_master;
! 653: }
! 654: } {}
! 655: do_test shared-$av.8.1.2 {
! 656: string range [execsql {PRAGMA encoding;}] 0 end-2
! 657: } {UTF-16}
! 658:
! 659: do_test shared-$av.8.1.3 {
! 660: sqlite3 db2 test.db
! 661: execsql {
! 662: PRAGMA encoding = 'UTF-8';
! 663: CREATE TABLE abc(a, b, c);
! 664: } db2
! 665: } {}
! 666: do_test shared-$av.8.1.4 {
! 667: execsql {
! 668: SELECT * FROM sqlite_master;
! 669: }
! 670: } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
! 671: do_test shared-$av.8.1.5 {
! 672: db2 close
! 673: execsql {
! 674: PRAGMA encoding;
! 675: }
! 676: } {UTF-8}
! 677:
! 678: forcedelete test2.db test2.db-journal
! 679: do_test shared-$av.8.2.1 {
! 680: execsql {
! 681: ATTACH 'test2.db' AS aux;
! 682: SELECT * FROM aux.sqlite_master;
! 683: }
! 684: } {}
! 685: do_test shared-$av.8.2.2 {
! 686: sqlite3 db2 test2.db
! 687: execsql {
! 688: PRAGMA encoding = 'UTF-16';
! 689: CREATE TABLE def(d, e, f);
! 690: } db2
! 691: string range [execsql {PRAGMA encoding;} db2] 0 end-2
! 692: } {UTF-16}
! 693:
! 694: catch {db close}
! 695: catch {db2 close}
! 696: forcedelete test.db test2.db
! 697:
! 698: do_test shared-$av.8.3.2 {
! 699: sqlite3 db test.db
! 700: execsql { CREATE TABLE def(d, e, f) }
! 701: execsql { PRAGMA encoding }
! 702: } {UTF-8}
! 703: do_test shared-$av.8.3.3 {
! 704: set zDb16 "[encoding convertto unicode test.db]\x00\x00"
! 705: set db16 [sqlite3_open16 $zDb16 {}]
! 706:
! 707: set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY]
! 708: sqlite3_step $stmt
! 709: set sql [sqlite3_column_text $stmt 0]
! 710: sqlite3_finalize $stmt
! 711: set sql
! 712: } {CREATE TABLE def(d, e, f)}
! 713: do_test shared-$av.8.3.4 {
! 714: set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY]
! 715: sqlite3_step $stmt
! 716: set enc [sqlite3_column_text $stmt 0]
! 717: sqlite3_finalize $stmt
! 718: set enc
! 719: } {UTF-8}
! 720:
! 721: sqlite3_close $db16
! 722:
! 723: # Bug #2547 is causing this to fail.
! 724: if 0 {
! 725: do_test shared-$av.8.2.3 {
! 726: catchsql {
! 727: SELECT * FROM aux.sqlite_master;
! 728: }
! 729: } {1 {attached databases must use the same text encoding as main database}}
! 730: }
! 731: }
! 732:
! 733: catch {db close}
! 734: catch {db2 close}
! 735: forcedelete test.db test2.db
! 736:
! 737: #---------------------------------------------------------------------------
! 738: # The following tests - shared-9.* - test interactions between TEMP triggers
! 739: # and shared-schemas.
! 740: #
! 741: ifcapable trigger&&tempdb {
! 742:
! 743: do_test shared-$av.9.1 {
! 744: sqlite3 db test.db
! 745: sqlite3 db2 test.db
! 746: execsql {
! 747: CREATE TABLE abc(a, b, c);
! 748: CREATE TABLE abc_mirror(a, b, c);
! 749: CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN
! 750: INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c);
! 751: END;
! 752: INSERT INTO abc VALUES(1, 2, 3);
! 753: SELECT * FROM abc_mirror;
! 754: }
! 755: } {1 2 3}
! 756: do_test shared-$av.9.2 {
! 757: execsql {
! 758: INSERT INTO abc VALUES(4, 5, 6);
! 759: SELECT * FROM abc_mirror;
! 760: } db2
! 761: } {1 2 3}
! 762: do_test shared-$av.9.3 {
! 763: db close
! 764: db2 close
! 765: } {}
! 766:
! 767: } ; # End shared-9.*
! 768:
! 769: #---------------------------------------------------------------------------
! 770: # The following tests - shared-10.* - test that the library behaves
! 771: # correctly when a connection to a shared-cache is closed.
! 772: #
! 773: do_test shared-$av.10.1 {
! 774: # Create a small sample database with two connections to it (db and db2).
! 775: forcedelete test.db
! 776: sqlite3 db test.db
! 777: sqlite3 db2 test.db
! 778: execsql {
! 779: CREATE TABLE ab(a PRIMARY KEY, b);
! 780: CREATE TABLE de(d PRIMARY KEY, e);
! 781: INSERT INTO ab VALUES('Chiang Mai', 100000);
! 782: INSERT INTO ab VALUES('Bangkok', 8000000);
! 783: INSERT INTO de VALUES('Ubon', 120000);
! 784: INSERT INTO de VALUES('Khon Kaen', 200000);
! 785: }
! 786: } {}
! 787: do_test shared-$av.10.2 {
! 788: # Open a read-transaction with the first connection, a write-transaction
! 789: # with the second.
! 790: execsql {
! 791: BEGIN;
! 792: SELECT * FROM ab;
! 793: }
! 794: execsql {
! 795: BEGIN;
! 796: INSERT INTO de VALUES('Pataya', 30000);
! 797: } db2
! 798: } {}
! 799: do_test shared-$av.10.3 {
! 800: # An external connection should be able to read the database, but not
! 801: # prepare a write operation.
! 802: if {$::tcl_platform(platform)=="unix"} {
! 803: sqlite3 db3 ./test.db
! 804: } else {
! 805: sqlite3 db3 TEST.DB
! 806: }
! 807: execsql {
! 808: SELECT * FROM ab;
! 809: } db3
! 810: catchsql {
! 811: BEGIN;
! 812: INSERT INTO de VALUES('Pataya', 30000);
! 813: } db3
! 814: } {1 {database is locked}}
! 815: do_test shared-$av.10.4 {
! 816: # Close the connection with the write-transaction open
! 817: db2 close
! 818: } {}
! 819: do_test shared-$av.10.5 {
! 820: # Test that the db2 transaction has been automatically rolled back.
! 821: # If it has not the ('Pataya', 30000) entry will still be in the table.
! 822: execsql {
! 823: SELECT * FROM de;
! 824: }
! 825: } {Ubon 120000 {Khon Kaen} 200000}
! 826: do_test shared-$av.10.5 {
! 827: # Closing db2 should have dropped the shared-cache back to a read-lock.
! 828: # So db3 should be able to prepare a write...
! 829: catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3
! 830: } {0 {}}
! 831: do_test shared-$av.10.6 {
! 832: # ... but not commit it.
! 833: catchsql {COMMIT} db3
! 834: } {1 {database is locked}}
! 835: do_test shared-$av.10.7 {
! 836: # Commit the (read-only) db transaction. Check via db3 to make sure the
! 837: # contents of table "de" are still as they should be.
! 838: execsql {
! 839: COMMIT;
! 840: }
! 841: execsql {
! 842: SELECT * FROM de;
! 843: } db3
! 844: } {Ubon 120000 {Khon Kaen} 200000 Pataya 30000}
! 845: do_test shared-$av.10.9 {
! 846: # Commit the external transaction.
! 847: catchsql {COMMIT} db3
! 848: } {0 {}}
! 849: integrity_check shared-$av.10.10
! 850: do_test shared-$av.10.11 {
! 851: db close
! 852: db3 close
! 853: } {}
! 854:
! 855: do_test shared-$av.11.1 {
! 856: forcedelete test.db
! 857: sqlite3 db test.db
! 858: sqlite3 db2 test.db
! 859: execsql {
! 860: CREATE TABLE abc(a, b, c);
! 861: CREATE TABLE abc2(a, b, c);
! 862: BEGIN;
! 863: INSERT INTO abc VALUES(1, 2, 3);
! 864: }
! 865: } {}
! 866: do_test shared-$av.11.2 {
! 867: catchsql {BEGIN;} db2
! 868: catchsql {SELECT * FROM abc;} db2
! 869: } {1 {database table is locked: abc}}
! 870: do_test shared-$av.11.3 {
! 871: catchsql {BEGIN} db2
! 872: } {1 {cannot start a transaction within a transaction}}
! 873: do_test shared-$av.11.4 {
! 874: catchsql {SELECT * FROM abc2;} db2
! 875: } {0 {}}
! 876: do_test shared-$av.11.5 {
! 877: catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2
! 878: } {1 {database table is locked}}
! 879: do_test shared-$av.11.6 {
! 880: catchsql {SELECT * FROM abc2}
! 881: } {0 {}}
! 882: do_test shared-$av.11.6 {
! 883: execsql {
! 884: ROLLBACK;
! 885: PRAGMA read_uncommitted = 1;
! 886: } db2
! 887: } {}
! 888: do_test shared-$av.11.7 {
! 889: execsql {
! 890: INSERT INTO abc2 VALUES(4, 5, 6);
! 891: INSERT INTO abc2 VALUES(7, 8, 9);
! 892: }
! 893: } {}
! 894: do_test shared-$av.11.8 {
! 895: set res [list]
! 896: db2 eval {
! 897: SELECT abc.a as I, abc2.a as II FROM abc, abc2;
! 898: } {
! 899: execsql {
! 900: DELETE FROM abc WHERE 1;
! 901: }
! 902: lappend res $I $II
! 903: }
! 904: set res
! 905: } {1 4 {} 7}
! 906: if {[llength [info command sqlite3_shared_cache_report]]==1} {
! 907: do_test shared-$av.11.9 {
! 908: string tolower [sqlite3_shared_cache_report]
! 909: } [string tolower [list [file nativename [file normalize test.db]] 2]]
! 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: }
! 1060:
! 1061: sqlite3_enable_shared_cache $::enable_shared_cache
! 1062: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to shared.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test