shared.test

最新的sqlite3.6.2源代码

  # by t2. If the locks are grabbed at the start of the statement (as 
  # they should be), no rows are returned. If (as was previously the case)
  # they are grabbed as the tables are accessed, the t1 rows will be 
  # returned before the query fails.
  #
  execsql {
    BEGIN;
    INSERT INTO t2 VALUES(5, 6);
  } db2
  set ret [list]
  catch {
    db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
      lappend ret $a $b
    }
  }
  set ret
} {}
do_test shared-$av.6.1.4 {
  execsql {
    COMMIT;
    BEGIN;
    INSERT INTO t1 VALUES(7, 8);
  } db2
  set ret [list]
  catch {
    db1 eval {
      SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2;
    } {
      lappend ret $d
    }
  }
  set ret
} {}

catch {db1 close}
catch {db2 close}
foreach f [list test.db test2.db] {
  file delete -force $f ${f}-journal
}

#--------------------------------------------------------------------------
# Tests shared-7.* test auto-vacuum does not invalidate cursors from
# other shared-cache users when it reorganizes the database on 
# COMMIT.
#
do_test shared-$av.7.1 {
  # This test case sets up a test database in auto-vacuum mode consisting 
  # of two tables, t1 and t2. Both have a single index. Table t1 is 
  # populated first (so consists of pages toward the start of the db file), 
  # t2 second (pages toward the end of the file). 
  sqlite3 db test.db
  sqlite3 db2 test.db
  execsql {
    BEGIN;
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE t2(a PRIMARY KEY, b);
  }
  set ::contents {}
  for {set i 0} {$i < 100} {incr i} {
    set a [string repeat "$i " 20]
    set b [string repeat "$i " 20]
    db eval {
      INSERT INTO t1 VALUES(:a, :b);
    }
    lappend ::contents [list [expr $i+1] $a $b]
  }
  execsql {
    INSERT INTO t2 SELECT * FROM t1;
    COMMIT;
  }
} {}
do_test shared-$av.7.2 {
  # This test case deletes the contents of table t1 (the one at the start of
  # the file) while many cursors are open on table t2 and its index. All of
  # the non-root pages will be moved from the end to the start of the file
  # when the DELETE is committed - this test verifies that moving the pages
  # does not disturb the open cursors.
  #

  proc lockrow {db tbl oids body} {
    set ret [list]
    db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" {
      if {$i==[lindex $oids 0]} {
        set noids [lrange $oids 1 end]
        if {[llength $noids]==0} {
          set subret [eval $body]
        } else {
          set subret [lockrow $db $tbl $noids $body]
        }
      }
      lappend ret [list $i $a $b]
    }
    return [linsert $subret 0 $ret]
  }
  proc locktblrows {db tbl body} {
    set oids [db eval "SELECT oid FROM $tbl"]
    lockrow $db $tbl $oids $body
  }

  set scans [locktblrows db t2 {
    execsql {
      DELETE FROM t1;
    } db2
  }]
  set error 0

  # Test that each SELECT query returned the expected contents of t2.
  foreach s $scans {
    if {[lsort -integer -index 0 $s]!=$::contents} {
      set error 1
    }
  }
  set error
} {0}

catch {db close}
catch {db2 close}
unset -nocomplain contents

#--------------------------------------------------------------------------
# The following tests try to trick the shared-cache code into assuming
# the wrong encoding for a database.
#
file delete -force test.db test.db-journal
ifcapable utf16 {
  do_test shared-$av.8.1.1 {
    sqlite3 db test.db
    execsql {
      PRAGMA encoding = 'UTF-16';
      SELECT * FROM sqlite_master;
    }
  } {}
  do_test shared-$av.8.1.2 {
    string range [execsql {PRAGMA encoding;}] 0 end-2
  } {UTF-16}

  do_test shared-$av.8.1.3 {
    sqlite3 db2 test.db
    execsql {
      PRAGMA encoding = 'UTF-8';
      CREATE TABLE abc(a, b, c);
    } db2
  } {}
  do_test shared-$av.8.1.4 {
    execsql {
      SELECT * FROM sqlite_master;
    }
  } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
  do_test shared-$av.8.1.5 {
    db2 close
    execsql {
      PRAGMA encoding;
    }
  } {UTF-8}

  file delete -force test2.db test2.db-journal
  do_test shared-$av.8.2.1 {
    execsql {
      ATTACH 'test2.db' AS aux;
      SELECT * FROM aux.sqlite_master;
    }
  } {}
  do_test shared-$av.8.2.2 {
    sqlite3 db2 test2.db
    execsql {
      PRAGMA encoding = 'UTF-16';
      CREATE TABLE def(d, e, f);
    } db2
    string range [execsql {PRAGMA encoding;} db2] 0 end-2
  } {UTF-16}

  catch {db close}
  catch {db2 close}
  file delete -force test.db test2.db

  do_test shared-$av.8.3.2 {
    sqlite3 db test.db
    execsql { CREATE TABLE def(d, e, f) }
    execsql { PRAGMA encoding }
  } {UTF-8}
  do_test shared-$av.8.3.3 {
    set zDb16 "[encoding convertto unicode test.db]\x00\x00"
    set db16 [sqlite3_open16 $zDb16 {}]

    set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY]
    sqlite3_step $stmt
    set sql [sqlite3_column_text $stmt 0]
    sqlite3_finalize $stmt
    set sql
  } {CREATE TABLE def(d, e, f)}
  do_test shared-$av.8.3.4 {
    set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY]
    sqlite3_step $stmt
    set enc [sqlite3_column_text $stmt 0]
    sqlite3_finalize $stmt
    set enc
  } {UTF-8}

  sqlite3_close $db16

# Bug #2547 is causing this to fail.
if 0 {
  do_test shared-$av.8.2.3 {
    catchsql {
      SELECT * FROM aux.sqlite_master;
    }
  } {1 {attached databases must use the same text encoding as main database}}
}
}

catch {db close}
catch {db2 close}
file delete -force test.db test2.db

#---------------------------------------------------------------------------
# The following tests - shared-9.* - test interactions between TEMP triggers
# and shared-schemas.
#
ifcapable trigger&&tempdb {

do_test shared-$av.9.1 {
  sqlite3 db test.db
  sqlite3 db2 test.db
  execsql {
    CREATE TABLE abc(a, b, c);
    CREATE TABLE abc_mirror(a, b, c);
    CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN 
      INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c);
    END;
    INSERT INTO abc VALUES(1, 2, 3);
    SELECT * FROM abc_mirror;
  }
} {1 2 3}
do_test shared-$av.9.2 {
  execsql {
    INSERT INTO abc VALUES(4, 5, 6);
    SELECT * FROM abc_mirror;
  } db2
} {1 2 3}
do_test shared-$av.9.3 {
  db close
  db2 close
} {}

} ; # End shared-9.*

#---------------------------------------------------------------------------
# The following tests - shared-10.* - test that the library behaves 
# correctly when a connection to a shared-cache is closed. 
#
do_test shared-$av.10.1 {
  # Create a small sample database with two connections to it (db and db2).
  file delete -force test.db
  sqlite3 db  test.db
  sqlite3 db2 test.db
  execsql {
    CREATE TABLE ab(a PRIMARY KEY, b);
    CREATE TABLE de(d PRIMARY KEY, e);
    INSERT INTO ab VALUES('Chiang Mai', 100000);
    INSERT INTO ab VALUES('Bangkok', 8000000);
    INSERT INTO de VALUES('Ubon', 120000);
    INSERT INTO de VALUES('Khon Kaen', 200000);
  }
} {}
do_test shared-$av.10.2 {
  # Open a read-transaction with the first connection, a write-transaction
  # with the second.
  execsql {
    BEGIN;
    SELECT * FROM ab;
  }
  execsql {
    BEGIN;
    INSERT INTO de VALUES('Pataya', 30000);
  } db2
} {}
do_test shared-$av.10.3 {
  # An external connection should be able to read the database, but not
  # prepare a write operation.
  if {$::tcl_platform(platform)=="unix"} {
    sqlite3 db3 ./test.db
  } else {
    sqlite3 db3 TEST.DB
  }
  execsql {
    SELECT * FROM ab;
  } db3
  catchsql {
    BEGIN;
    INSERT INTO de VALUES('Pataya', 30000);
  } db3
} {1 {database is locked}}
do_test shared-$av.10.4 {
  # Close the connection with the write-transaction open
  db2 close
} {}
do_test shared-$av.10.5 {
  # Test that the db2 transaction has been automatically rolled back.
  # If it has not the ('Pataya', 30000) entry will still be in the table.
  execsql {
    SELECT * FROM de;
  }
} {Ubon 120000 {Khon Kaen} 200000}
do_test shared-$av.10.5 {
  # Closing db2 should have dropped the shared-cache back to a read-lock.
  # So db3 should be able to prepare a write...
  catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3
} {0 {}}
do_test shared-$av.10.6 {
  # ... but not commit it.
  catchsql {COMMIT} db3
} {1 {database is locked}}
do_test shared-$av.10.7 {
  # Commit the (read-only) db transaction. Check via db3 to make sure the 
  # contents of table "de" are still as they should be.
  execsql {
    COMMIT;
  }
  execsql {
    SELECT * FROM de;
  } db3
} {Ubon 120000 {Khon Kaen} 200000 Pataya 30000}
do_test shared-$av.10.9 {
  # Commit the external transaction.
  catchsql {COMMIT} db3
} {0 {}}
integrity_check shared-$av.10.10
do_test shared-$av.10.11 {
  db close
  db3 close
} {}

do_test shared-$av.11.1 {
  file delete -force test.db
  sqlite3 db  test.db
  sqlite3 db2 test.db
  execsql {
    CREATE TABLE abc(a, b, c);
    CREATE TABLE abc2(a, b, c);
    BEGIN;
    INSERT INTO abc VALUES(1, 2, 3);
  }
} {}
do_test shared-$av.11.2 {
  catchsql {BEGIN;} db2
  catchsql {SELECT * FROM abc;} db2
} {1 {database table is locked: abc}}
do_test shared-$av.11.3 {
  catchsql {BEGIN} db2
} {1 {cannot start a transaction within a transaction}}
do_test shared-$av.11.4 {
  catchsql {SELECT * FROM abc2;} db2
} {0 {}}
do_test shared-$av.11.5 {
  catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2
} {1 {database is locked}}
do_test shared-$av.11.6 {
  catchsql {SELECT * FROM abc2}
} {0 {}}
do_test shared-$av.11.6 {
  execsql {
    ROLLBACK;
    PRAGMA read_uncommitted = 1;
  } db2
} {}
do_test shared-$av.11.7 {
  execsql {
    INSERT INTO abc2 VALUES(4, 5, 6);
    INSERT INTO abc2 VALUES(7, 8, 9);
  }
} {}
do_test shared-$av.11.8 {
  set res [list]
  db2 eval {
    SELECT abc.a as I, abc2.a as II FROM abc, abc2;
  } {
    execsql {
      DELETE FROM abc WHERE 1;
    }
    lappend res $I $II
  }
  set res
} {1 4 {} 7}
if {[llength [info command sqlite3_shared_cache_report]]==1} {
  do_test shared-$av.11.9 {
    string tolower [sqlite3_shared_cache_report]
  } [string tolower [list [file nativename [file normalize test.db]] 2]]
}

do_test shared-$av.11.11 {
  db close
  db2 close
} {}

# This tests that if it is impossible to free any pages, SQLite will
# exceed the limit set by PRAGMA cache_size.
file delete -force test.db test.db-journal
sqlite3 db test.db 
ifcapable pager_pragmas {
  do_test shared-$av.12.1 {
    execsql {
      PRAGMA cache_size = 10;
      PRAGMA cache_size;
    }
  } {10}
}
do_test shared-$av.12.2 {
  set ::db_handles [list]
  for {set i 1} {$i < 15} {incr i} {
    lappend ::db_handles db$i
    sqlite3 db$i test.db 
    execsql "CREATE TABLE db${i}(a, b, c)" db$i 
    execsql "INSERT INTO db${i} VALUES(1, 2, 3)"
  }
} {}
proc nested_select {handles} {
  [lindex $handles 0] eval "SELECT * FROM [lindex $handles 0]" {
    lappend ::res $a $b $c
    if {[llength $handles]>1} {
      nested_select [lrange $handles 1 end]
    }
  }
}
do_test shared-$av.12.3 {
  set ::res [list]
  nested_select $::db_handles
  set ::res
} [string range [string repeat "1 2 3 " [llength $::db_handles]] 0 end-1]

do_test shared-$av.12.X {
  db close
  foreach h $::db_handles { 
    $h close
  }
} {}

# Internally, locks are acquired on shared B-Tree structures in the order
# that the structures appear in the virtual memory address space. This
# test case attempts to cause the order of the structures in memory 
# to be different from the order in which they are attached to a given
# database handle. This covers an extra line or two.
#
do_test shared-$av.13.1 {
  file delete -force test2.db test3.db test4.db test5.db
  sqlite3 db :memory:
  execsql {
    ATTACH 'test2.db' AS aux2;
    ATTACH 'test3.db' AS aux3;
    ATTACH 'test4.db' AS aux4;
    ATTACH 'test5.db' AS aux5;
    DETACH aux2;
    DETACH aux3;
    DETACH aux4;
    ATTACH 'test2.db' AS aux2;
    ATTACH 'test3.db' AS aux3;
    ATTACH 'test4.db' AS aux4;
  }
} {}
do_test shared-$av.13.2 {
  execsql {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE aux2.t2(a, b, c);
    CREATE TABLE aux3.t3(a, b, c);
    CREATE TABLE aux4.t4(a, b, c);
    CREATE TABLE aux5.t5(a, b, c);
    SELECT count(*) FROM 
      aux2.sqlite_master, 
      aux3.sqlite_master, 
      aux4.sqlite_master, 
      aux5.sqlite_master
  }
} {1}
do_test shared-$av.13.3 {
  db close
} {}

# Test that nothing horrible happens if a connection to a shared B-Tree 
# structure is closed while some other connection has an open cursor.
#
do_test shared-$av.14.1 {
  sqlite3 db test.db
  sqlite3 db2 test.db
  execsql {SELECT name FROM sqlite_master}
} {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}
do_test shared-$av.14.2 {
  set res [list]
  db eval {SELECT name FROM sqlite_master} {
    if {$name eq "db7"} {
      db2 close
    }
    lappend res $name
  }
  set res
} {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}
do_test shared-$av.14.3 {
  db close
} {}

}

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test