1# 2005 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.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 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# Rollback of a DDL statement.........................schema-12.*
32#
33# Test cases schema-9.* and schema-10.* test some specific bugs
34# that came up during development.
35#
36# Test cases schema-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 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 $::STMT
50} {SQLITE_ERROR}
51do_test schema-1.2 {
52  sqlite3_finalize $::STMT
53} {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 $::STMT
60} {SQLITE_ERROR}
61do_test schema-1.4 {
62  sqlite3_finalize $::STMT
63} {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 $::STMT
72  } {SQLITE_ERROR}
73  do_test schema-2.2 {
74    sqlite3_finalize $::STMT
75  } {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 $::STMT
82  } {SQLITE_ERROR}
83  do_test schema-2.4 {
84    sqlite3_finalize $::STMT
85  } {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 BEGIN
96        SELECT 1, 2, 3;
97      END;
98    }
99    sqlite3_step $::STMT
100  } {SQLITE_ERROR}
101  do_test schema-3.2 {
102    sqlite3_finalize $::STMT
103  } {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 $::STMT
110  } {SQLITE_ERROR}
111  do_test schema-3.4 {
112    sqlite3_finalize $::STMT
113  } {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 $::STMT
125} {SQLITE_ERROR}
126do_test schema-4.2 {
127  sqlite3_finalize $::STMT
128} {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 $::STMT
135} {SQLITE_ERROR}
136do_test schema-4.4 {
137  sqlite3_finalize $::STMT
138} {SQLITE_SCHEMA}
139
140#---------------------------------------------------------------------
141# Tests 5.1 to 5.4 check that prepared statements are invalidated when
142# 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 $::STMT
152  } {SQLITE_DONE}
153  do_test schema-5.2 {
154    sqlite3_reset $::STMT
155  } {SQLITE_OK}
156  do_test schema-5.3 {
157    execsql {
158      DETACH aux;
159    }
160    sqlite3_step $::STMT
161  } {SQLITE_ERROR}
162  do_test schema-5.4 {
163    sqlite3_finalize $::STMT
164  } {SQLITE_SCHEMA}
165}
166
167#---------------------------------------------------------------------
168# Tests 6.* check that prepared statements are invalidated when
169# 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 $::STMT
175} {SQLITE_DONE}
176do_test schema-6.2 {
177  sqlite3_reset $::STMT
178} {SQLITE_OK}
179do_test schema-6.3 {
180  sqlite_delete_function $::DB hello_function
181  sqlite3_step $::STMT
182} {SQLITE_ERROR}
183do_test schema-6.4 {
184  sqlite3_finalize $::STMT
185} {SQLITE_SCHEMA}
186
187#---------------------------------------------------------------------
188# Tests 7.* check that prepared statements are invalidated when
189# 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 1
196    sqlite3_step $::STMT
197  } {SQLITE_DONE}
198  do_test schema-7.2 {
199    sqlite3_reset $::STMT
200  } {SQLITE_OK}
201  do_test schema-7.3 {
202    add_test_collate $::DB 0 0 0
203    sqlite3_step $::STMT
204  } {SQLITE_ERROR}
205  do_test schema-7.4 {
206    sqlite3_finalize $::STMT
207  } {SQLITE_SCHEMA}
208}
209
210#---------------------------------------------------------------------
211# Tests 8.1 and 8.2 check that prepared statements are invalidated when
212# the authorization function is set.
213#
214ifcapable auth {
215  do_test schema-8.1 {
216    set ::STMT [sqlite3_prepare $::DB {SELECT * FROM sqlite_master} -1 TAIL]
217    db auth {}
218    sqlite3_step $::STMT
219  } {SQLITE_ERROR}
220  do_test schema-8.3 {
221    sqlite3_finalize $::STMT
222  } {SQLITE_SCHEMA}
223}
224
225#---------------------------------------------------------------------
226# schema-9.1: Test that if a table is dropped by one database connection,
227#             other database connections are aware of the schema change.
228# schema-9.2: Test that if a view is dropped by one database connection,
229#             other database connections are aware of the schema change.
230#
231do_test schema-9.1 {
232  sqlite3 db2 test.db
233  execsql {
234    DROP TABLE abc;
235  } db2
236  db2 close
237  catchsql {
238    SELECT * FROM abc;
239  }
240} {1 {no such table: abc}}
241execsql {
242  CREATE TABLE abc(a, b, c);
243}
244ifcapable view {
245  do_test schema-9.2 {
246    execsql {
247      CREATE VIEW abcview AS SELECT * FROM abc;
248    }
249    sqlite3 db2 test.db
250    execsql {
251      DROP VIEW abcview;
252    } db2
253    db2 close
254    catchsql {
255      SELECT * FROM abcview;
256    }
257  } {1 {no such table: abcview}}
258}
259
260#---------------------------------------------------------------------
261# Test that if a CREATE TABLE statement fails because there are other
262# btree cursors open on the same database file it does not corrupt
263# the sqlite_master table.
264#
265# 2007-05-02: These tests have been overcome by events.  Open btree
266# cursors no longer block CREATE TABLE.  But there is no reason not
267# to keep the tests in the test suite.
268#
269do_test schema-10.1 {
270  execsql {
271    INSERT INTO abc VALUES(1, 2, 3);
272  }
273  set sql {SELECT * FROM abc}
274  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
275  sqlite3_step $::STMT
276} {SQLITE_ROW}
277do_test schema-10.2 {
278  catchsql {
279    CREATE TABLE t2(a, b, c);
280  }
281} {0 {}}
282do_test schema-10.3 {
283  sqlite3_finalize $::STMT
284} {SQLITE_OK}
285do_test schema-10.4 {
286  sqlite3 db2 test.db
287  execsql {
288    SELECT * FROM abc
289  } db2
290} {1 2 3}
291do_test schema-10.5 {
292  db2 close
293} {}
294
295#---------------------------------------------------------------------
296# Attempting to delete or replace a user-function or collation sequence
297# while there are active statements returns an SQLITE_BUSY error.
298#
299# schema-11.1 - 11.4: User function.
300# schema-11.5 - 11.8: Collation sequence.
301#
302do_test schema-11.1 {
303  db function tstfunc {}
304  set sql {SELECT * FROM abc}
305  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
306  sqlite3_step $::STMT
307} {SQLITE_ROW}
308do_test schema-11.2 {
309  sqlite_delete_function $::DB tstfunc
310} {SQLITE_BUSY}
311do_test schema-11.3 {
312  set rc [catch {
313    db function tstfunc {}
314  } msg]
315  list $rc $msg
316} {1 {unable to delete/modify user-function due to active statements}}
317do_test schema-11.4 {
318  sqlite3_finalize $::STMT
319} {SQLITE_OK}
320do_test schema-11.5 {
321  db collate tstcollate {}
322  set sql {SELECT * FROM abc}
323  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
324  sqlite3_step $::STMT
325} {SQLITE_ROW}
326do_test schema-11.6 {
327  sqlite_delete_collation $::DB tstcollate
328} {SQLITE_BUSY}
329do_test schema-11.7 {
330  set rc [catch {
331    db collate tstcollate {}
332  } msg]
333  list $rc $msg
334} {1 {unable to delete/modify collation sequence due to active statements}}
335do_test schema-11.8 {
336  sqlite3_finalize $::STMT
337} {SQLITE_OK}
338
339# The following demonstrates why statements need to be expired whenever
340# there is a rollback (explicit or otherwise).
341#
342do_test schema-12.1 {
343  # Begin a transaction and create a table. This increments
344  # the schema cookie. Then compile an SQL statement, using
345  # the current (incremented) value of the cookie.
346  execsql {
347    BEGIN;
348    CREATE TABLE t3(a, b, c);
349  }
350  set ::STMT [sqlite3_prepare $::DB "CREATE TABLE t4(a,b,c)" -1 TAIL]
351
352  # Rollback the transaction, resetting the schema cookie to the value
353  # it had at the start of this test case. Then create a table,
354  # incrementing the schema cookie.
355  execsql {
356    ROLLBACK;
357    CREATE TABLE t4(a, b, c);
358  }
359
360  # The schema cookie now has the same value as it did when SQL statement
361  # $::STMT was prepared. So unless it has been expired, it would be
362  # possible to run the "CREATE TABLE t4" statement and create a
363  # duplicate table.
364  list [sqlite3_step $::STMT] [sqlite3_finalize $::STMT]
365} {SQLITE_ERROR SQLITE_SCHEMA}
366
367ifcapable {auth} {
368
369do_test schema-13.1 {
370  set S [sqlite3_prepare_v2 db "SELECT * FROM sqlite_master" -1 dummy]
371  db function hello hello
372  db function hello {}
373  db auth auth
374  proc auth {args} {
375    if {[lindex $args 0] == "SQLITE_READ"} {return SQLITE_DENY}
376    return SQLITE_OK
377  }
378  sqlite3_step $S
379} {SQLITE_AUTH}
380
381do_test schema-13.2 {
382  sqlite3_step $S
383} {SQLITE_AUTH}
384
385do_test schema-13.3 {
386  sqlite3_finalize $S
387} {SQLITE_AUTH}
388
389}
390
391finish_test
392