1# 2006 November 08 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# This file implements regression tests for SQLite library. 12# 13# This file tests the various conditions under which an SQLITE_SCHEMA 14# error should be returned. This is a copy of schema.test that 15# has been altered to use sqlite3_prepare_v2 instead of sqlite3_prepare 16# 17# $Id: schema2.test,v 1.4 2009/02/04 17:40:58 drh Exp $ 18 19#--------------------------------------------------------------------- 20# When any of the following types of SQL statements or actions are 21# executed, all pre-compiled statements are invalidated. An attempt 22# to execute an invalidated statement always returns SQLITE_SCHEMA. 23# 24# CREATE/DROP TABLE...................................schema2-1.* 25# CREATE/DROP VIEW....................................schema2-2.* 26# CREATE/DROP TRIGGER.................................schema2-3.* 27# CREATE/DROP INDEX...................................schema2-4.* 28# DETACH..............................................schema2-5.* 29# Deleting a user-function............................schema2-6.* 30# Deleting a collation sequence.......................schema2-7.* 31# Setting or changing the authorization function......schema2-8.* 32# 33# Test cases schema2-9.* and schema2-10.* test some specific bugs 34# that came up during development. 35# 36# Test cases schema2-11.* test that it is impossible to delete or 37# change a collation sequence or user-function while SQL statements 38# are executing. Adding new collations or functions is allowed. 39# 40 41set testdir [file dirname $argv0] 42source $testdir/tester.tcl 43 44do_test schema2-1.1 { 45 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 46 execsql { 47 CREATE TABLE abc(a, b, c); 48 } 49 sqlite3_step $::STMT 50} {SQLITE_ROW} 51do_test schema2-1.2 { 52 sqlite3_finalize $::STMT 53} {SQLITE_OK} 54do_test schema2-1.3 { 55 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 56 execsql { 57 DROP TABLE abc; 58 } 59 sqlite3_step $::STMT 60} {SQLITE_DONE} 61do_test schema2-1.4 { 62 sqlite3_finalize $::STMT 63} {SQLITE_OK} 64 65 66ifcapable view { 67 do_test schema2-2.1 { 68 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 69 execsql { 70 CREATE VIEW v1 AS SELECT * FROM sqlite_master; 71 } 72 sqlite3_step $::STMT 73 } {SQLITE_ROW} 74 do_test schema2-2.2 { 75 sqlite3_finalize $::STMT 76 } {SQLITE_OK} 77 do_test schema2-2.3 { 78 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 79 execsql { 80 DROP VIEW v1; 81 } 82 sqlite3_step $::STMT 83 } {SQLITE_DONE} 84 do_test schema2-2.4 { 85 sqlite3_finalize $::STMT 86 } {SQLITE_OK} 87} 88 89ifcapable trigger { 90 do_test schema2-3.1 { 91 execsql { 92 CREATE TABLE abc(a, b, c); 93 } 94 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 95 execsql { 96 CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN 97 SELECT 1, 2, 3; 98 END; 99 } 100 sqlite3_step $::STMT 101 } {SQLITE_ROW} 102 do_test schema2-3.2 { 103 sqlite3_finalize $::STMT 104 } {SQLITE_OK} 105 do_test schema2-3.3 { 106 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 107 execsql { 108 DROP TRIGGER abc_trig; 109 } 110 sqlite3_step $::STMT 111 } {SQLITE_ROW} 112 do_test schema2-3.4 { 113 sqlite3_finalize $::STMT 114 } {SQLITE_OK} 115} 116 117do_test schema2-4.1 { 118 catchsql { 119 CREATE TABLE abc(a, b, c); 120 } 121 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 122 execsql { 123 CREATE INDEX abc_index ON abc(a); 124 } 125 sqlite3_step $::STMT 126} {SQLITE_ROW} 127do_test schema2-4.2 { 128 sqlite3_finalize $::STMT 129} {SQLITE_OK} 130do_test schema2-4.3 { 131 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 132 execsql { 133 DROP INDEX abc_index; 134 } 135 sqlite3_step $::STMT 136} {SQLITE_ROW} 137do_test schema2-4.4 { 138 sqlite3_finalize $::STMT 139} {SQLITE_OK} 140 141#--------------------------------------------------------------------- 142# Tests 5.1 to 5.4 check that prepared statements are invalidated when 143# a database is DETACHed (but not when one is ATTACHed). 144# 145ifcapable attach { 146 do_test schema2-5.1 { 147 set sql {SELECT * FROM abc;} 148 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 149 execsql { 150 ATTACH 'test2.db' AS aux; 151 } 152 sqlite3_step $::STMT 153 } {SQLITE_DONE} 154 do_test schema2-5.2 { 155 sqlite3_reset $::STMT 156 } {SQLITE_OK} 157 do_test schema2-5.3 { 158 execsql { 159 DETACH aux; 160 } 161 sqlite3_step $::STMT 162 } {SQLITE_DONE} 163 do_test schema2-5.4 { 164 sqlite3_finalize $::STMT 165 } {SQLITE_OK} 166} 167 168#--------------------------------------------------------------------- 169# Tests 6.* check that prepared statements are invalidated when 170# a user-function is deleted (but not when one is added). 171do_test schema2-6.1 { 172 set sql {SELECT * FROM abc;} 173 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 174 db function hello_function {} 175 sqlite3_step $::STMT 176} {SQLITE_DONE} 177do_test schema2-6.2 { 178 sqlite3_reset $::STMT 179} {SQLITE_OK} 180do_test schema2-6.3 { 181 sqlite_delete_function $::DB hello_function 182 sqlite3_step $::STMT 183} {SQLITE_DONE} 184do_test schema2-6.4 { 185 sqlite3_finalize $::STMT 186} {SQLITE_OK} 187 188#--------------------------------------------------------------------- 189# Tests 7.* check that prepared statements are invalidated when 190# a collation sequence is deleted (but not when one is added). 191# 192ifcapable utf16 { 193 do_test schema2-7.1 { 194 set sql {SELECT * FROM abc;} 195 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 196 add_test_collate $::DB 1 1 1 197 sqlite3_step $::STMT 198 } {SQLITE_DONE} 199 do_test schema2-7.2 { 200 sqlite3_reset $::STMT 201 } {SQLITE_OK} 202 do_test schema2-7.3 { 203 add_test_collate $::DB 0 0 0 204 sqlite3_step $::STMT 205 } {SQLITE_DONE} 206 do_test schema2-7.4 { 207 sqlite3_finalize $::STMT 208 } {SQLITE_OK} 209} 210 211#--------------------------------------------------------------------- 212# Tests 8.1 and 8.2 check that prepared statements are invalidated when 213# the authorization function is set. 214# 215ifcapable auth { 216 do_test schema2-8.1 { 217 set ::STMT [sqlite3_prepare_v2 $::DB {SELECT * FROM sqlite_master} -1 TAIL] 218 db auth {} 219 sqlite3_step $::STMT 220 } {SQLITE_ROW} 221 do_test schema2-8.3 { 222 sqlite3_finalize $::STMT 223 } {SQLITE_OK} 224} 225 226#--------------------------------------------------------------------- 227# schema2-9.1: Test that if a table is dropped by one database connection, 228# other database connections are aware of the schema change. 229# schema2-9.2: Test that if a view is dropped by one database connection, 230# other database connections are aware of the schema change. 231# 232do_test schema2-9.1 { 233 sqlite3 db2 test.db 234 execsql { 235 DROP TABLE abc; 236 } db2 237 db2 close 238 catchsql { 239 SELECT * FROM abc; 240 } 241} {1 {no such table: abc}} 242execsql { 243 CREATE TABLE abc(a, b, c); 244} 245ifcapable view { 246 do_test schema2-9.2 { 247 execsql { 248 CREATE VIEW abcview AS SELECT * FROM abc; 249 } 250 sqlite3 db2 test.db 251 execsql { 252 DROP VIEW abcview; 253 } db2 254 db2 close 255 catchsql { 256 SELECT * FROM abcview; 257 } 258 } {1 {no such table: abcview}} 259} 260 261#--------------------------------------------------------------------- 262# Test that if a CREATE TABLE statement fails because there are other 263# btree cursors open on the same database file it does not corrupt 264# the sqlite_master table. 265# 266# 2007-05-02: These tests have been overcome by events. Open btree 267# cursors no longer block CREATE TABLE. But there is no reason not 268# to keep the tests in the test suite. 269# 270do_test schema2-10.1 { 271 execsql { 272 INSERT INTO abc VALUES(1, 2, 3); 273 } 274 set sql {SELECT * FROM abc} 275 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 276 sqlite3_step $::STMT 277} {SQLITE_ROW} 278do_test schema2-10.2 { 279 catchsql { 280 CREATE TABLE t2(a, b, c); 281 } 282} {0 {}} 283do_test schema2-10.3 { 284 sqlite3_finalize $::STMT 285} {SQLITE_OK} 286do_test schema2-10.4 { 287 sqlite3 db2 test.db 288 execsql { 289 SELECT * FROM abc 290 } db2 291} {1 2 3} 292do_test schema2-10.5 { 293 db2 close 294} {} 295 296#--------------------------------------------------------------------- 297# Attempting to delete or replace a user-function or collation sequence 298# while there are active statements returns an SQLITE_BUSY error. 299# 300# schema2-11.1 - 11.4: User function. 301# schema2-11.5 - 11.8: Collation sequence. 302# 303do_test schema2-11.1 { 304 db function tstfunc {} 305 set sql {SELECT * FROM abc} 306 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 307 sqlite3_step $::STMT 308} {SQLITE_ROW} 309do_test schema2-11.2 { 310 sqlite_delete_function $::DB tstfunc 311} {SQLITE_BUSY} 312do_test schema2-11.3 { 313 set rc [catch { 314 db function tstfunc {} 315 } msg] 316 list $rc $msg 317} {1 {unable to delete/modify user-function due to active statements}} 318do_test schema2-11.4 { 319 sqlite3_finalize $::STMT 320} {SQLITE_OK} 321do_test schema2-11.5 { 322 db collate tstcollate {} 323 set sql {SELECT * FROM abc} 324 set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL] 325 sqlite3_step $::STMT 326} {SQLITE_ROW} 327do_test schema2-11.6 { 328 sqlite_delete_collation $::DB tstcollate 329} {SQLITE_BUSY} 330do_test schema2-11.7 { 331 set rc [catch { 332 db collate tstcollate {} 333 } msg] 334 list $rc $msg 335} {1 {unable to delete/modify collation sequence due to active statements}} 336do_test schema2-11.8 { 337 sqlite3_finalize $::STMT 338} {SQLITE_OK} 339 340finish_test 341