3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix pragma4
18 proc do_pragma_ncol_test {tn sql nCol} {
20 set ::stmt [sqlite3_prepare_v2 db $sql -1 dummy]
21 uplevel [list do_test $tn { sqlite3_column_count $::stmt } $nCol]
22 sqlite3_finalize $::stmt
25 # If there is no RHS argument, the following PRAGMA statements operate as
26 # queries, returning a single row containing a single column.
28 # Or, if there is RHS argument, they return zero rows of zero columns.
31 1 "PRAGMA application_id = 10"
32 2 "PRAGMA automatic_index = 1"
33 3 "PRAGMA auto_vacuum = 1"
34 4 "PRAGMA cache_size = -100"
35 5 "PRAGMA cache_spill = 1"
36 6 "PRAGMA cell_size_check = 1"
37 7 "PRAGMA checkpoint_fullfsync = 1"
38 8 "PRAGMA count_changes = 1"
39 9 "PRAGMA default_cache_size = 100"
40 10 "PRAGMA defer_foreign_keys = 1"
41 11 "PRAGMA empty_result_callbacks = 1"
42 12 "PRAGMA encoding = 'utf-8'"
43 13 "PRAGMA foreign_keys = 1"
44 14 "PRAGMA full_column_names = 1"
45 15 "PRAGMA fullfsync = 1"
46 16 "PRAGMA ignore_check_constraints = 1"
47 17 "PRAGMA legacy_file_format = 1"
48 18 "PRAGMA page_size = 511"
49 19 "PRAGMA page_size = 512"
50 20 "PRAGMA query_only = false"
51 21 "PRAGMA read_uncommitted = true"
52 22 "PRAGMA recursive_triggers = false"
53 23 "PRAGMA reverse_unordered_selects = false"
54 24 "PRAGMA schema_version = 211"
55 25 "PRAGMA short_column_names = 1"
56 26 "PRAGMA synchronous = full"
57 29 "PRAGMA temp_store = memory"
58 30 "PRAGMA user_version = 405"
59 31 "PRAGMA writable_schema = 1"
64 do_pragma_ncol_test 1.$tn.1 [lindex [split $sql =] 0] 1
67 do_pragma_ncol_test 1.$tn.2 $sql 0
70 # These pragmas should never return any values.
73 1 "PRAGMA shrink_memory"
74 2 "PRAGMA shrink_memory = 10"
75 3 "PRAGMA case_sensitive_like = 0"
76 4 "PRAGMA case_sensitive_like = 1"
77 5 "PRAGMA case_sensitive_like"
80 do_pragma_ncol_test 1.$tn.1 $sql 0
83 # EXPLAIN on a PRAGMA integrity_check.
84 # Verify that that P4_INTARRAY argument to OP_IntegrityCk is rendered
90 do_test pragma4-2.100 {
94 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10000)
95 INSERT INTO t1(x) SELECT zeroblob(300) FROM c;
99 string map {\[ x \] x \173 {} \175 {}} \
100 [db eval {EXPLAIN PRAGMA integrity_check}]
101 } {/ IntegrityCk 2 2 1 x[0-9]+,1x /}
104 #--------------------------------------------------------------------------
108 do_execsql_test 4.1.1 {
109 CREATE TABLE t1(a, b, c);
110 ATTACH 'test.db2' AS aux;
111 CREATE TABLE aux.t2(d, e, f);
113 do_execsql_test 4.1.2 { PRAGMA table_info = t1 } {
114 0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0
116 do_execsql_test 4.1.3 { PRAGMA table_info = t2 } {
117 0 d {} 0 {} 0 1 e {} 0 {} 0 2 f {} 0 {} 0
122 execsql { DROP TABLE t1 } db3
123 execsql { DROP TABLE t2 } db2
125 do_execsql_test 4.1.5 { PRAGMA table_info(t1) }
126 do_execsql_test 4.1.6 { PRAGMA table_info(t2) }
132 do_execsql_test 4.2.1 {
133 CREATE TABLE t1(a, b, c);
134 ATTACH 'test.db2' AS aux;
135 CREATE TABLE aux.t2(d, e, f);
138 do_execsql_test 4.2.2 { SELECT * FROM pragma_table_info('t1') } {
139 0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0
141 do_execsql_test 4.2.3 { SELECT * FROM pragma_table_info('t2') } {
142 0 d {} 0 {} 0 1 e {} 0 {} 0 2 f {} 0 {} 0
148 execsql { DROP TABLE t1 } db3
149 execsql { DROP TABLE t2 } db2
152 do_execsql_test 4.2.5 { SELECT * FROM pragma_table_info('t1') }
153 do_execsql_test 4.2.6 { SELECT * FROM pragma_table_info('t2') }
160 do_execsql_test 4.3.1 {
161 CREATE TABLE t1(a, b, c);
162 CREATE INDEX i1 ON t1(b);
163 ATTACH 'test.db2' AS aux;
164 CREATE TABLE aux.t2(d, e, f);
165 CREATE INDEX aux.i2 ON t2(e);
168 do_execsql_test 4.3.2 { SELECT * FROM pragma_index_info('i1') } {0 1 b}
169 do_execsql_test 4.3.3 { SELECT * FROM pragma_index_info('i2') } {0 1 e}
174 execsql { DROP INDEX i1 } db3
175 execsql { DROP INDEX i2 } db2
177 if {[permutation]=="prepare"} { catchsql { SELECT * FROM sqlite_master } }
179 do_execsql_test 4.3.5 { SELECT * FROM pragma_index_info('i1') }
180 do_execsql_test 4.3.6 { SELECT * FROM pragma_index_info('i2') }
183 execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}
184 do_execsql_test 4.4.0 {
185 CREATE INDEX main.i1 ON t1(b, c);
186 CREATE INDEX aux.i2 ON t2(e, f);
189 do_execsql_test 4.4.1 { SELECT * FROM pragma_index_list('t1') } {0 i1 0 c 0}
190 do_execsql_test 4.4.2 { SELECT * FROM pragma_index_list('t2') } {0 i2 0 c 0}
193 execsql { DROP INDEX i1 } db3
194 execsql { DROP INDEX i2 } db2
196 if {[permutation]=="prepare"} {
197 catchsql { SELECT * FROM sqlite_master, aux.sqlite_master }
200 do_execsql_test 4.4.5 { SELECT * FROM pragma_index_list('t1') } {}
201 do_execsql_test 4.4.6 { SELECT * FROM pragma_index_list('t2') } {}
203 execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}
205 do_execsql_test 4.5.0 {
206 CREATE UNIQUE INDEX main.i1 ON t1(a);
207 CREATE UNIQUE INDEX aux.i2 ON t2(d);
208 CREATE TABLE main.c1 (a, b, c REFERENCES t1(a));
209 CREATE TABLE aux.c2 (d, e, r REFERENCES t2(d));
212 do_execsql_test 4.5.1 { SELECT * FROM pragma_foreign_key_list('c1') } {
213 0 0 t1 c a {NO ACTION} {NO ACTION} NONE
215 do_execsql_test 4.5.2 { SELECT * FROM pragma_foreign_key_list('c2') } {
216 0 0 t2 r d {NO ACTION} {NO ACTION} NONE
220 execsql { DROP TABLE c1 } db3
221 execsql { DROP TABLE c2 } db2
223 if {[permutation]=="prepare"} {
224 catchsql { SELECT * FROM sqlite_master, aux.sqlite_master }
227 do_execsql_test 4.5.4 { SELECT * FROM pragma_foreign_key_list('c1') }
228 do_execsql_test 4.5.5 { SELECT * FROM pragma_foreign_key_list('c2') }
230 execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}
232 do_execsql_test 4.6.0 {
233 CREATE TABLE main.c1 (a, b, c REFERENCES t1(a));
234 CREATE TABLE aux.c2 (d, e, r REFERENCES t2(d));
235 INSERT INTO main.c1 VALUES(1, 2, 3);
236 INSERT INTO aux.c2 VALUES(4, 5, 6);
238 do_execsql_test 4.6.1 { pragma foreign_key_check('c1') } {
241 do_execsql_test 4.6.2 { pragma foreign_key_check('c2') } {
245 execsql { DROP TABLE c2 } db2
247 do_execsql_test 4.6.4 { pragma foreign_key_check('c1') } {c1 1 t1 0}
248 do_catchsql_test 4.6.5 {
249 pragma foreign_key_check('c2')
250 } {1 {no such table: c2}}
252 do_execsql_test 5.0 {
253 CREATE TABLE t4(a DEFAULT 'abc' /* comment */, b DEFAULT -1 -- comment
254 , c DEFAULT +4.0 /* another comment */
256 PRAGMA table_info = t4;
258 0 a {} 0 'abc' 0 1 b {} 0 -1 0 2 c {} 0 +4.0 0