PageRenderTime 34ms CodeModel.GetById 25ms app.highlight 5ms RepoModel.GetById 0ms app.codeStats 0ms

/trunk/src/sqlite/test/shared.test

#
Unknown | 1062 lines | 1006 code | 56 blank | 0 comment | 0 complexity | 12b01d3f4e67e9fb2d18bbc08d256681 MD5 | raw file
Possible License(s): BSD-3-Clause
   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
  14set testdir [file dirname $argv0]
  15source $testdir/tester.tcl
  16db close
  17
  18# These tests cannot be run without the ATTACH command.
  19#
  20ifcapable !shared_cache||!attach {
  21  finish_test
  22  return
  23}
  24
  25set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
  26
  27foreach av [list 0 1] {
  28
  29# Open the database connection and execute the auto-vacuum pragma
  30file delete -force test.db
  31sqlite3 db test.db
  32
  33ifcapable 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)
  48set using_proxy 0
  49foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
  50  set using_proxy $value
  51}
  52set extrafds_prelock 0
  53set extrafds_postlock 0
  54if {$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#
  64incr 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
  85do_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}
  93do_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}
 104do_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}
 116do_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}}
 124do_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}}
 132do_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} {}
 143do_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}}
 155do_test shared-$av.1.8 {
 156  # Commit the connection 1 transaction.
 157  execsql {
 158    COMMIT;
 159  }
 160} {}
 161
 162do_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}
 174do_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}}
 189do_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}}
 213do_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
 227catchsql COMMIT db2
 228
 229do_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}
 254do_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}
 266do_test shared-$av.3.1.3 {
 267  execsql {
 268    SELECT * FROM seq;
 269  }
 270} {}
 271
 272catch {db close}
 273catch {db2 close}
 274catch {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#
 296file delete -force test.db
 297file delete -force test2.db
 298file delete -force test2.db-journal
 299sqlite3 db  test.db
 300sqlite3 db2 test2.db
 301do_test shared-$av.4.1.1 {
 302  set sqlite_open_file_count
 303  expr $sqlite_open_file_count-($extrafds_prelock*2)
 304} {2}
 305do_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}
 310do_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#
 320do_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} {}
 328do_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#
 338do_test shared-$av.4.3.1 {
 339  execsql {
 340    BEGIN;
 341    SELECT * FROM test.abc;
 342  } db2
 343} {i ii iii}
 344do_test shared-$av.4.3.2 {
 345  catchsql {
 346    INSERT INTO abc VALUES('iv', 'v', 'vi');
 347  }
 348} {1 {database table is locked: abc}}
 349do_test shared-$av.4.3.3 {
 350  catchsql {
 351    CREATE TABLE ghi(g, h, i);
 352  }
 353} {1 {database table is locked: sqlite_master}}
 354do_test shared-$av.4.3.3 {
 355  catchsql {
 356    INSERT INTO def VALUES('IV', 'V', 'VI');
 357  }
 358} {0 {}}
 359do_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#
 373ifcapable 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
 395do_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}
 405do_test shared-$av.4.4.3 {
 406  sqlite3_finalize $::STMT1
 407} {SQLITE_LOCKED}
 408do_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}}
 414do_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
 422catch {db2 close}
 423catch {db close}
 424
 425#--------------------------------------------------------------------------
 426# Tests shared-5.* 
 427#
 428foreach db [list test.db test1.db test2.db test3.db] {
 429  file delete -force $db ${db}-journal
 430}
 431do_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} {}
 445do_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} {}
 451ifcapable view {
 452  do_test shared-$av.5.1.3 {
 453    execsql {
 454      CREATE VIEW test1.v1 AS SELECT * FROM t1;
 455    } db1
 456  } {}
 457}
 458ifcapable 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}
 467do_test shared-$av.5.1.5 {
 468  execsql {
 469    DROP INDEX i1;
 470  } db2
 471} {}
 472ifcapable view {
 473  do_test shared-$av.5.1.6 {
 474    execsql {
 475      DROP VIEW v1;
 476    } db2
 477  } {}
 478}
 479ifcapable trigger {
 480  do_test shared-$av.5.1.7 {
 481    execsql {
 482      DROP TRIGGER trig1;
 483    } db2
 484  } {}
 485}
 486do_test shared-$av.5.1.8 {
 487  execsql {
 488    DROP TABLE t1;
 489  } db2
 490} {}
 491ifcapable 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#
 505do_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} {}
 513ifcapable 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}
 520do_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} {}
 540do_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
 557catch {db1 close}
 558catch {db2 close}
 559foreach f [list test.db test2.db] {
 560  file delete -force $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#
 568do_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} {}
 594do_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
 638catch {db close}
 639catch {db2 close}
 640unset -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#
 646file delete -force test.db test.db-journal
 647ifcapable 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  file delete -force 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  file delete -force 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.
 724if 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
 733catch {db close}
 734catch {db2 close}
 735file delete -force test.db test2.db
 736
 737#---------------------------------------------------------------------------
 738# The following tests - shared-9.* - test interactions between TEMP triggers
 739# and shared-schemas.
 740#
 741ifcapable trigger&&tempdb {
 742
 743do_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}
 756do_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}
 762do_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#
 773do_test shared-$av.10.1 {
 774  # Create a small sample database with two connections to it (db and db2).
 775  file delete -force 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} {}
 787do_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} {}
 799do_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}}
 815do_test shared-$av.10.4 {
 816  # Close the connection with the write-transaction open
 817  db2 close
 818} {}
 819do_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}
 826do_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 {}}
 831do_test shared-$av.10.6 {
 832  # ... but not commit it.
 833  catchsql {COMMIT} db3
 834} {1 {database is locked}}
 835do_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}
 845do_test shared-$av.10.9 {
 846  # Commit the external transaction.
 847  catchsql {COMMIT} db3
 848} {0 {}}
 849integrity_check shared-$av.10.10
 850do_test shared-$av.10.11 {
 851  db close
 852  db3 close
 853} {}
 854
 855do_test shared-$av.11.1 {
 856  file delete -force 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} {}
 866do_test shared-$av.11.2 {
 867  catchsql {BEGIN;} db2
 868  catchsql {SELECT * FROM abc;} db2
 869} {1 {database table is locked: abc}}
 870do_test shared-$av.11.3 {
 871  catchsql {BEGIN} db2
 872} {1 {cannot start a transaction within a transaction}}
 873do_test shared-$av.11.4 {
 874  catchsql {SELECT * FROM abc2;} db2
 875} {0 {}}
 876do_test shared-$av.11.5 {
 877  catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2
 878} {1 {database table is locked}}
 879do_test shared-$av.11.6 {
 880  catchsql {SELECT * FROM abc2}
 881} {0 {}}
 882do_test shared-$av.11.6 {
 883  execsql {
 884    ROLLBACK;
 885    PRAGMA read_uncommitted = 1;
 886  } db2
 887} {}
 888do_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} {}
 894do_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}
 906if {[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
 912do_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.
 919file delete -force test.db test.db-journal
 920sqlite3 db test.db 
 921ifcapable pager_pragmas {
 922  do_test shared-$av.12.1 {
 923    execsql {
 924      PRAGMA cache_size = 10;
 925      PRAGMA cache_size;
 926    }
 927  } {10}
 928}
 929do_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} {}
 938proc 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}
 946do_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
 952do_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#
 965do_test shared-$av.13.1 {
 966  file delete -force 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} {}
 981do_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}
 995do_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#
1002do_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}
1007do_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}
1017do_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#
1028do_test shared-$av-15.1 {
1029  file delete -force 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}
1053do_test shared-$av-15.2 {
1054  execsql { DROP TABLE t1 } db2
1055} {}
1056db close
1057db2 close
1058
1059}
1060
1061sqlite3_enable_shared_cache $::enable_shared_cache
1062finish_test