AryaWu/sqlite
0
1# 2005 Jan 242#3# The author disclaims copyright to this source code. In place of4# 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# This file implements regression tests for SQLite library.12#13# This file tests the various conditions under which an SQLITE_SCHEMA14# error should be returned.15#16# $Id: schema.test,v 1.9 2009/02/04 17:40:58 drh Exp $17 18#---------------------------------------------------------------------19# When any of the following types of SQL statements or actions are 20# executed, all pre-compiled statements are invalidated. An attempt21# to execute an invalidated statement always returns SQLITE_SCHEMA.22#23# CREATE/DROP TABLE...................................schema-1.*24# CREATE/DROP VIEW....................................schema-2.*25# CREATE/DROP TRIGGER.................................schema-3.*26# CREATE/DROP INDEX...................................schema-4.*27# DETACH..............................................schema-5.*28# Deleting a user-function............................schema-6.*29# Deleting a collation sequence.......................schema-7.*30# Setting or changing the authorization function......schema-8.*31# Rollback of a DDL statement.........................schema-12.*32#33# Test cases schema-9.* and schema-10.* test some specific bugs34# that came up during development.35#36# Test cases schema-11.* test that it is impossible to delete or37# change a collation sequence or user-function while SQL statements38# are executing. Adding new collations or functions is allowed.39#40 41set testdir [file dirname $argv0]42source $testdir/tester.tcl43 44do_test schema-1.1 {45 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]46 execsql {47 CREATE TABLE abc(a, b, c);48 }49 sqlite3_step $::STMT50} {SQLITE_ERROR}51do_test schema-1.2 {52 sqlite3_finalize $::STMT53} {SQLITE_SCHEMA}54do_test schema-1.3 {55 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]56 execsql {57 DROP TABLE abc;58 }59 sqlite3_step $::STMT60} {SQLITE_ERROR}61do_test schema-1.4 {62 sqlite3_finalize $::STMT63} {SQLITE_SCHEMA}64 65ifcapable view {66 do_test schema-2.1 {67 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]68 execsql {69 CREATE VIEW v1 AS SELECT * FROM sqlite_master;70 }71 sqlite3_step $::STMT72 } {SQLITE_ERROR}73 do_test schema-2.2 {74 sqlite3_finalize $::STMT75 } {SQLITE_SCHEMA}76 do_test schema-2.3 {77 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]78 execsql {79 DROP VIEW v1;80 }81 sqlite3_step $::STMT82 } {SQLITE_ERROR}83 do_test schema-2.4 {84 sqlite3_finalize $::STMT85 } {SQLITE_SCHEMA}86}87 88ifcapable trigger {89 do_test schema-3.1 {90 execsql {91 CREATE TABLE abc(a, b, c);92 }93 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]94 execsql {95 CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN96 SELECT 1, 2, 3;97 END;98 }99 sqlite3_step $::STMT100 } {SQLITE_ERROR}101 do_test schema-3.2 {102 sqlite3_finalize $::STMT103 } {SQLITE_SCHEMA}104 do_test schema-3.3 {105 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]106 execsql {107 DROP TRIGGER abc_trig;108 }109 sqlite3_step $::STMT110 } {SQLITE_ERROR}111 do_test schema-3.4 {112 sqlite3_finalize $::STMT113 } {SQLITE_SCHEMA}114}115 116do_test schema-4.1 {117 catchsql {118 CREATE TABLE abc(a, b, c);119 }120 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]121 execsql {122 CREATE INDEX abc_index ON abc(a);123 }124 sqlite3_step $::STMT125} {SQLITE_ERROR}126do_test schema-4.2 {127 sqlite3_finalize $::STMT128} {SQLITE_SCHEMA}129do_test schema-4.3 {130 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]131 execsql {132 DROP INDEX abc_index;133 }134 sqlite3_step $::STMT135} {SQLITE_ERROR}136do_test schema-4.4 {137 sqlite3_finalize $::STMT138} {SQLITE_SCHEMA}139 140#---------------------------------------------------------------------141# Tests 5.1 to 5.4 check that prepared statements are invalidated when142# a database is DETACHed (but not when one is ATTACHed).143#144ifcapable attach {145 do_test schema-5.1 {146 set sql {SELECT * FROM abc;}147 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]148 execsql {149 ATTACH 'test2.db' AS aux;150 }151 sqlite3_step $::STMT152 } {SQLITE_DONE}153 do_test schema-5.2 {154 sqlite3_reset $::STMT155 } {SQLITE_OK}156 do_test schema-5.3 {157 execsql {158 DETACH aux;159 }160 sqlite3_step $::STMT161 } {SQLITE_ERROR}162 do_test schema-5.4 {163 sqlite3_finalize $::STMT164 } {SQLITE_SCHEMA}165}166 167#---------------------------------------------------------------------168# Tests 6.* check that prepared statements are invalidated when169# a user-function is deleted (but not when one is added).170do_test schema-6.1 {171 set sql {SELECT * FROM abc;}172 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]173 db function hello_function {}174 sqlite3_step $::STMT175} {SQLITE_DONE}176do_test schema-6.2 {177 sqlite3_reset $::STMT178} {SQLITE_OK}179do_test schema-6.3 {180 sqlite_delete_function $::DB hello_function181 sqlite3_step $::STMT182} {SQLITE_ERROR}183do_test schema-6.4 {184 sqlite3_finalize $::STMT185} {SQLITE_SCHEMA}186 187#---------------------------------------------------------------------188# Tests 7.* check that prepared statements are invalidated when189# a collation sequence is deleted (but not when one is added).190#191ifcapable utf16 {192 do_test schema-7.1 {193 set sql {SELECT * FROM abc;}194 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]195 add_test_collate $::DB 1 1 1196 sqlite3_step $::STMT197 } {SQLITE_DONE}198 do_test schema-7.2 {199 sqlite3_reset $::STMT200 } {SQLITE_OK}201 do_test schema-7.3 {202 add_test_collate $::DB 0 0 0 203 sqlite3_step $::STMT204 } {SQLITE_ERROR}205 do_test schema-7.4 {206 sqlite3_finalize $::STMT207 } {SQLITE_SCHEMA}208}209 210#---------------------------------------------------------------------211# Tests 8.1 and 8.2 check that prepared statements are invalidated when212# the authorization function is set to a non-null function. Tests 8.11213# and 8.12 verify that no invalidations occur when the authorizer is214# cleared.215#216ifcapable auth {217 proc noop_auth {args} {return SQLITE_OK}218 do_test schema-8.1 {219 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]220 db auth noop_auth221 sqlite3_step $::STMT222 } {SQLITE_ERROR}223 do_test schema-8.2 {224 sqlite3_finalize $::STMT225 } {SQLITE_SCHEMA}226 do_test schema-8.11 {227 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]228 db auth {}229 sqlite3_step $::STMT230 } {SQLITE_ROW}231 do_test schema-8.12 {232 sqlite3_finalize $::STMT233 } {SQLITE_OK}234 235}236 237#---------------------------------------------------------------------238# schema-9.1: Test that if a table is dropped by one database connection, 239# other database connections are aware of the schema change.240# schema-9.2: Test that if a view is dropped by one database connection,241# other database connections are aware of the schema change.242#243do_test schema-9.1 {244 sqlite3 db2 test.db245 execsql {246 DROP TABLE abc;247 } db2248 db2 close249 catchsql {250 SELECT * FROM abc;251 }252} {1 {no such table: abc}}253execsql {254 CREATE TABLE abc(a, b, c);255}256ifcapable view {257 do_test schema-9.2 {258 execsql {259 CREATE VIEW abcview AS SELECT * FROM abc;260 }261 sqlite3 db2 test.db262 execsql {263 DROP VIEW abcview;264 } db2265 db2 close266 catchsql {267 SELECT * FROM abcview;268 }269 } {1 {no such table: abcview}}270}271 272#---------------------------------------------------------------------273# Test that if a CREATE TABLE statement fails because there are other274# btree cursors open on the same database file it does not corrupt275# the sqlite_master table.276#277# 2007-05-02: These tests have been overcome by events. Open btree278# cursors no longer block CREATE TABLE. But there is no reason not279# to keep the tests in the test suite.280#281do_test schema-10.1 {282 execsql {283 INSERT INTO abc VALUES(1, 2, 3);284 }285 set sql {SELECT * FROM abc}286 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]287 sqlite3_step $::STMT288} {SQLITE_ROW}289do_test schema-10.2 {290 catchsql {291 CREATE TABLE t2(a, b, c);292 }293} {0 {}}294do_test schema-10.3 {295 sqlite3_finalize $::STMT296} {SQLITE_OK}297do_test schema-10.4 {298 sqlite3 db2 test.db299 execsql {300 SELECT * FROM abc301 } db2302} {1 2 3}303do_test schema-10.5 {304 db2 close305} {}306 307#---------------------------------------------------------------------308# Attempting to delete or replace a user-function or collation sequence 309# while there are active statements returns an SQLITE_BUSY error.310#311# schema-11.1 - 11.4: User function.312# schema-11.5 - 11.8: Collation sequence.313#314do_test schema-11.1 {315 db function tstfunc {}316 set sql {SELECT * FROM abc}317 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]318 sqlite3_step $::STMT319} {SQLITE_ROW}320do_test schema-11.2 {321 sqlite_delete_function $::DB tstfunc322} {SQLITE_BUSY}323do_test schema-11.3 {324 set rc [catch {325 db function tstfunc {}326 } msg]327 list $rc $msg328} {1 {unable to delete/modify user-function due to active statements}}329do_test schema-11.4 {330 sqlite3_finalize $::STMT331} {SQLITE_OK}332do_test schema-11.5 {333 db collate tstcollate {}334 set sql {SELECT * FROM abc}335 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]336 sqlite3_step $::STMT337} {SQLITE_ROW}338do_test schema-11.6 {339 sqlite_delete_collation $::DB tstcollate340} {SQLITE_BUSY}341do_test schema-11.7 {342 set rc [catch {343 db collate tstcollate {}344 } msg]345 list $rc $msg346} {1 {unable to delete/modify collation sequence due to active statements}}347do_test schema-11.8 {348 sqlite3_finalize $::STMT349} {SQLITE_OK}350 351# The following demonstrates why statements need to be expired whenever352# there is a rollback (explicit or otherwise).353#354do_test schema-12.1 {355 # Begin a transaction and create a table. This increments 356 # the schema cookie. Then compile an SQL statement, using357 # the current (incremented) value of the cookie.358 execsql {359 BEGIN;360 CREATE TABLE t3(a, b, c);361 }362 set ::STMT [sqlite3_prepare $::DB "CREATE TABLE t4(a,b,c)" -1 TAIL]363 364 # Rollback the transaction, resetting the schema cookie to the value365 # it had at the start of this test case. Then create a table, 366 # incrementing the schema cookie.367 execsql {368 ROLLBACK;369 CREATE TABLE t4(a, b, c);370 }371 372 # The schema cookie now has the same value as it did when SQL statement373 # $::STMT was prepared. So unless it has been expired, it would be374 # possible to run the "CREATE TABLE t4" statement and create a375 # duplicate table.376 list [sqlite3_step $::STMT] [sqlite3_finalize $::STMT]377} {SQLITE_ERROR SQLITE_SCHEMA}378 379ifcapable {auth} {380 381do_test schema-13.1 {382 set S [sqlite3_prepare_v2 db "SELECT * FROM sqlite_master" -1 dummy]383 db function hello hello384 db function hello {}385 db auth auth386 proc auth {args} {387 if {[lindex $args 0] == "SQLITE_READ"} {return SQLITE_DENY}388 return SQLITE_OK389 }390 sqlite3_step $S391} {SQLITE_AUTH}392 393do_test schema-13.2 {394 sqlite3_step $S395} {SQLITE_AUTH}396 397do_test schema-13.3 {398 sqlite3_finalize $S399} {SQLITE_AUTH}400 401}402 403finish_test404