1# 2004 Jan 24 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. 15# 16# $Id: schema.test,v 1.2 2005/01/24 13:03:32 danielk1977 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 attempt 21# 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# 32# Note: Test cases schema-6.* are missing right now. 33# 34 35set testdir [file dirname $argv0] 36source $testdir/tester.tcl 37 38do_test schema-1.1 { 39 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 40 execsql { 41 CREATE TABLE abc(a, b, c); 42 } 43 sqlite3_step $::STMT 44} {SQLITE_ERROR} 45do_test schema-1.2 { 46 sqlite3_finalize $::STMT 47} {SQLITE_SCHEMA} 48do_test schema-1.3 { 49 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 50 execsql { 51 DROP TABLE abc; 52 } 53 sqlite3_step $::STMT 54} {SQLITE_ERROR} 55do_test schema-1.4 { 56 sqlite3_finalize $::STMT 57} {SQLITE_SCHEMA} 58 59ifcapable view { 60 do_test schema-2.1 { 61 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 62 execsql { 63 CREATE VIEW v1 AS SELECT * FROM sqlite_master; 64 } 65 sqlite3_step $::STMT 66 } {SQLITE_ERROR} 67 do_test schema-2.2 { 68 sqlite3_finalize $::STMT 69 } {SQLITE_SCHEMA} 70 do_test schema-2.3 { 71 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 72 execsql { 73 DROP VIEW v1; 74 } 75 sqlite3_step $::STMT 76 } {SQLITE_ERROR} 77 do_test schema-2.4 { 78 sqlite3_finalize $::STMT 79 } {SQLITE_SCHEMA} 80} 81 82ifcapable trigger { 83 do_test schema-3.1 { 84 execsql { 85 CREATE TABLE abc(a, b, c); 86 } 87 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 88 execsql { 89 CREATE TRIGGER abc_trig AFTER INSERT ON abc BEGIN 90 SELECT 1, 2, 3; 91 END; 92 } 93 sqlite3_step $::STMT 94 } {SQLITE_ERROR} 95 do_test schema-3.2 { 96 sqlite3_finalize $::STMT 97 } {SQLITE_SCHEMA} 98 do_test schema-3.3 { 99 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 100 execsql { 101 DROP TRIGGER abc_trig; 102 } 103 sqlite3_step $::STMT 104 } {SQLITE_ERROR} 105 do_test schema-3.4 { 106 sqlite3_finalize $::STMT 107 } {SQLITE_SCHEMA} 108} 109 110do_test schema-4.1 { 111 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 112 execsql { 113 CREATE INDEX abc_index ON abc(a); 114 } 115 sqlite3_step $::STMT 116} {SQLITE_ERROR} 117do_test schema-4.2 { 118 sqlite3_finalize $::STMT 119} {SQLITE_SCHEMA} 120do_test schema-4.3 { 121 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 122 execsql { 123 DROP INDEX abc_index; 124 } 125 sqlite3_step $::STMT 126} {SQLITE_ERROR} 127do_test schema-4.4 { 128 sqlite3_finalize $::STMT 129} {SQLITE_SCHEMA} 130 131#--------------------------------------------------------------------- 132# Tests 5.1 to 5.4 check that prepared statements are invalidated when 133# a database is DETACHed (but not when one is ATTACHed). 134# 135do_test schema-5.1 { 136 set sql {SELECT * FROM abc;} 137 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 138 execsql { 139 ATTACH 'test2.db' AS aux; 140 } 141 sqlite3_step $::STMT 142} {SQLITE_DONE} 143do_test schema-5.2 { 144 sqlite3_reset $::STMT 145} {SQLITE_OK} 146do_test schema-5.3 { 147 execsql { 148 DETACH aux; 149 } 150 sqlite3_step $::STMT 151} {SQLITE_ERROR} 152do_test schema-5.4 { 153 sqlite3_finalize $::STMT 154} {SQLITE_SCHEMA} 155 156#--------------------------------------------------------------------- 157# Tests 7.* check that prepared statements are invalidated when 158# a collation sequence is deleted (but not when one is added). 159# 160do_test schema-7.1 { 161 set sql {SELECT * FROM abc;} 162 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 163 add_test_collate $::DB 1 1 1 164 sqlite3_step $::STMT 165} {SQLITE_DONE} 166do_test schema-7.2 { 167 sqlite3_reset $::STMT 168} {SQLITE_OK} 169do_test schema-7.3 { 170 add_test_collate $::DB 0 0 0 171 sqlite3_step $::STMT 172} {SQLITE_ERROR} 173do_test schema-7.4 { 174 sqlite3_finalize $::STMT 175} {SQLITE_SCHEMA} 176 177#--------------------------------------------------------------------- 178# Tests 8.1 and 8.2 check that prepared statements are invalidated when 179# the authorization function is set. 180# 181ifcapable auth { 182 do_test schema-8.1 { 183 set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL] 184 db auth {} 185 sqlite3_step $::STMT 186 } {SQLITE_ERROR} 187 do_test schema-8.3 { 188 sqlite3_finalize $::STMT 189 } {SQLITE_SCHEMA} 190} 191 192#--------------------------------------------------------------------- 193# schema-9.1: Test that if a table is dropped by one database connection, 194# other database connections are aware of the schema change. 195# schema-9.2: Test that if a view is dropped by one database connection, 196# other database connections are aware of the schema change. 197# 198do_test schema-9.1 { 199 sqlite3 db2 test.db 200 execsql { 201 DROP TABLE abc; 202 } db2 203 db2 close 204 catchsql { 205 SELECT * FROM abc; 206 } 207} {1 {no such table: abc}} 208execsql { 209 CREATE TABLE abc(a, b, c); 210} 211ifcapable view { 212 do_test schema-9.2 { 213 execsql { 214 CREATE VIEW abcview AS SELECT * FROM abc; 215 } 216 sqlite3 db2 test.db 217 execsql { 218 DROP VIEW abcview; 219 } db2 220 db2 close 221 catchsql { 222 SELECT * FROM abcview; 223 } 224 } {1 {no such table: abcview}} 225} 226 227#--------------------------------------------------------------------- 228# Test that if a CREATE TABLE statement fails because there are other 229# btree cursors open on the same database file it does not corrupt 230# the sqlite_master table. 231# 232do_test schema-10.1 { 233 execsql { 234 INSERT INTO abc VALUES(1, 2, 3); 235 } 236 set sql {SELECT * FROM abc} 237 set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL] 238 sqlite3_step $::STMT 239} {SQLITE_ROW} 240do_test schema-10.2 { 241 catchsql { 242 CREATE TABLE t2(a, b, c); 243 } 244} {1 {database table is locked}} 245do_test schema-10.3 { 246 sqlite3_finalize $::STMT 247} {SQLITE_OK} 248do_test schema-10.4 { 249 sqlite3 db2 test.db 250 execsql { 251 SELECT * FROM abc 252 } db2 253} {1 2 3} 254do_test schema-10.5 { 255 db2 close 256} {} 257 258finish_test 259 260