Debian Codegraph @

test/pragma4.test

raw
1 # 2017 Jan 4
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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix pragma4
17
18 proc do_pragma_ncol_test {tn sql nCol} {
19 set ::stmt 0
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
23 }
24
25 # If there is no RHS argument, the following PRAGMA statements operate as
26 # queries, returning a single row containing a single column.
27 #
28 # Or, if there is RHS argument, they return zero rows of zero columns.
29 #
30 foreach {tn sql} {
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 18 "PRAGMA page_size = 511"
48 19 "PRAGMA page_size = 512"
49 20 "PRAGMA query_only = false"
50 21 "PRAGMA read_uncommitted = true"
51 22 "PRAGMA recursive_triggers = false"
52 23 "PRAGMA reverse_unordered_selects = false"
53 24 "PRAGMA schema_version = 211"
54 25 "PRAGMA short_column_names = 1"
55 26 "PRAGMA synchronous = full"
56 29 "PRAGMA temp_store = memory"
57 30 "PRAGMA user_version = 405"
58 31 "PRAGMA writable_schema = 1"
59 } {
60 reset_db
61
62 # Without RHS:
63 do_pragma_ncol_test 1.$tn.1 [lindex [split $sql =] 0] 1
64
65 # With RHS:
66 do_pragma_ncol_test 1.$tn.2 $sql 0
67 }
68
69 # These pragmas should never return any values.
70 #
71 foreach {tn sql} {
72 1 "PRAGMA shrink_memory"
73 2 "PRAGMA shrink_memory = 10"
74 3 "PRAGMA case_sensitive_like = 0"
75 4 "PRAGMA case_sensitive_like = 1"
76 5 "PRAGMA case_sensitive_like"
77 } {
78
79 do_pragma_ncol_test 1.$tn.1 $sql 0
80 }
81
82 # EXPLAIN on a PRAGMA integrity_check.
83 # Verify that that P4_INTARRAY argument to OP_IntegrityCk is rendered
84 # correctly.
85 #
86 catch {db close}
87 forcedelete test.db
88 sqlite3 db test.db
89 do_test pragma4-2.100 {
90 db eval {
91 PRAGMA page_size=512;
92 CREATE TABLE t1(x);
93 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10000)
94 INSERT INTO t1(x) SELECT zeroblob(300) FROM c;
95 CREATE TABLE t2(y);
96 DROP TABLE t1;
97 }
98 string map {\[ x \] x \173 {} \175 {}} \
99 [db eval {EXPLAIN PRAGMA integrity_check}]
100 } {/ IntegrityCk 1 2 8 x[0-9]+,1x /}
101
102
103 #--------------------------------------------------------------------------
104 #
105 reset_db
106 forcedelete test.db2
107 do_execsql_test 4.1.1 {
108 CREATE TABLE t1(a, b, c);
109 ATTACH 'test.db2' AS aux;
110 CREATE TABLE aux.t2(d, e, f);
111 }
112 do_execsql_test 4.1.2 { PRAGMA table_info = t1 } {
113 0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0
114 }
115 do_execsql_test 4.1.3 { PRAGMA table_info = t2 } {
116 0 d {} 0 {} 0 1 e {} 0 {} 0 2 f {} 0 {} 0
117 }
118 do_test 4.1.4 {
119 sqlite3 db3 test.db
120 sqlite3 db2 test.db2
121 execsql { DROP TABLE t1 } db3
122 execsql { DROP TABLE t2 } db2
123 } {}
124 if {[permutation]=="prepare"} {
125 do_catchsql_test 4.1.5a {
126 PRAGMA table_info(t1)
127 } {1 {database schema has changed}}
128 }
129 do_execsql_test 4.1.5 {
130 PRAGMA table_info(t1)
131 }
132 do_execsql_test 4.1.6 { PRAGMA table_info(t2) }
133
134 db2 close
135 db3 close
136 reset_db
137 forcedelete test.db2
138 do_execsql_test 4.2.1 {
139 CREATE TABLE t1(a, b, c);
140 ATTACH 'test.db2' AS aux;
141 CREATE TABLE aux.t2(d, e, f);
142 }
143 ifcapable vtab {
144 do_execsql_test 4.2.2 { SELECT * FROM pragma_table_info('t1') } {
145 0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0
146 }
147 do_execsql_test 4.2.3 { SELECT * FROM pragma_table_info('t2') } {
148 0 d {} 0 {} 0 1 e {} 0 {} 0 2 f {} 0 {} 0
149 }
150 }
151 do_test 4.2.4 {
152 sqlite3 db3 test.db
153 sqlite3 db2 test.db2
154 execsql { DROP TABLE t1 } db3
155 execsql { DROP TABLE t2 } db2
156 } {}
157 ifcapable vtab {
158 do_execsql_test 4.2.5 { SELECT * FROM pragma_table_info('t1') }
159 do_execsql_test 4.2.6 { SELECT * FROM pragma_table_info('t2') }
160 }
161
162 db2 close
163 db3 close
164 reset_db
165 forcedelete test.db2
166 do_execsql_test 4.3.1 {
167 CREATE TABLE t1(a, b, c);
168 CREATE INDEX i1 ON t1(b);
169 ATTACH 'test.db2' AS aux;
170 CREATE TABLE aux.t2(d, e, f);
171 CREATE INDEX aux.i2 ON t2(e);
172 }
173 ifcapable vtab {
174 do_execsql_test 4.3.2 { SELECT * FROM pragma_index_info('i1') } {0 1 b}
175 do_execsql_test 4.3.3 { SELECT * FROM pragma_index_info('i2') } {0 1 e}
176 }
177 do_test 4.3.4 {
178 sqlite3 db3 test.db
179 sqlite3 db2 test.db2
180 execsql { DROP INDEX i1 } db3
181 execsql { DROP INDEX i2 } db2
182 } {}
183 if {[permutation]=="prepare"} { catchsql { SELECT * FROM sqlite_master } }
184 ifcapable vtab {
185 do_execsql_test 4.3.5 { SELECT * FROM pragma_index_info('i1') }
186 do_execsql_test 4.3.6 { SELECT * FROM pragma_index_info('i2') }
187 }
188
189 execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}
190 do_execsql_test 4.4.0 {
191 CREATE INDEX main.i1 ON t1(b, c);
192 CREATE INDEX aux.i2 ON t2(e, f);
193 }
194 ifcapable vtab {
195 do_execsql_test 4.4.1 { SELECT * FROM pragma_index_list('t1') } {0 i1 0 c 0}
196 do_execsql_test 4.4.2 { SELECT * FROM pragma_index_list('t2') } {0 i2 0 c 0}
197 }
198 do_test 4.4.3 {
199 execsql { DROP INDEX i1 } db3
200 execsql { DROP INDEX i2 } db2
201 } {}
202 if {[permutation]=="prepare"} {
203 catchsql { SELECT * FROM sqlite_master, aux.sqlite_master }
204 }
205 ifcapable vtab {
206 do_execsql_test 4.4.5 { SELECT * FROM pragma_index_list('t1') } {}
207 do_execsql_test 4.4.6 { SELECT * FROM pragma_index_list('t2') } {}
208 }
209 execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}
210
211 do_execsql_test 4.5.0 {
212 CREATE UNIQUE INDEX main.i1 ON t1(a);
213 CREATE UNIQUE INDEX aux.i2 ON t2(d);
214 CREATE TABLE main.c1 (a, b, c REFERENCES t1(a));
215 CREATE TABLE aux.c2 (d, e, r REFERENCES t2(d));
216 }
217 ifcapable vtab {
218 do_execsql_test 4.5.1 { SELECT * FROM pragma_foreign_key_list('c1') } {
219 0 0 t1 c a {NO ACTION} {NO ACTION} NONE
220 }
221 do_execsql_test 4.5.2 { SELECT * FROM pragma_foreign_key_list('c2') } {
222 0 0 t2 r d {NO ACTION} {NO ACTION} NONE
223 }
224 }
225 do_test 4.5.3 {
226 execsql { DROP TABLE c1 } db3
227 execsql { DROP TABLE c2 } db2
228 } {}
229 if {[permutation]=="prepare"} {
230 catchsql { SELECT * FROM sqlite_master, aux.sqlite_master }
231 }
232 ifcapable vtab {
233 do_execsql_test 4.5.4 { SELECT * FROM pragma_foreign_key_list('c1') }
234 do_execsql_test 4.5.5 { SELECT * FROM pragma_foreign_key_list('c2') }
235 }
236 execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}
237
238 do_execsql_test 4.6.0 {
239 CREATE TABLE main.c1 (a, b, c REFERENCES t1(a));
240 CREATE TABLE aux.c2 (d, e, r REFERENCES t2(d));
241 INSERT INTO main.c1 VALUES(1, 2, 3);
242 INSERT INTO aux.c2 VALUES(4, 5, 6);
243 }
244 do_execsql_test 4.6.1 { pragma foreign_key_check('c1') } {
245 c1 1 t1 0
246 }
247 do_execsql_test 4.6.2 { pragma foreign_key_check('c2') } {
248 c2 1 t2 0
249 }
250 do_test 4.6.3 {
251 execsql { DROP TABLE c2 } db2
252 } {}
253 do_execsql_test 4.6.4 { pragma foreign_key_check('c1') } {c1 1 t1 0}
254 do_catchsql_test 4.6.5 {
255 pragma foreign_key_check('c2')
256 } {1 {no such table: c2}}
257
258 do_execsql_test 5.0 {
259 CREATE TABLE t4(a DEFAULT 'abc' /* comment */, b DEFAULT -1 -- comment
260 , c DEFAULT +4.0 /* another comment */
261 );
262 PRAGMA table_info = t4;
263 } {
264 0 a {} 0 'abc' 0 1 b {} 0 -1 0 2 c {} 0 +4.0 0
265 }
266
267 # 2024-03-24 https://sqlite.org/forum/forumpost/85b6a8b6705fb77a
268 #
269 catch {db2 close}
270 catch {db3 close}
271 ifcapable vtab {
272 reset_db
273 do_execsql_test 6.0 {
274 DROP TABLE IF EXISTS t1;
275 DROP TABLE IF EXISTS t2;
276 CREATE TABLE t1(a INT PRIMARY KEY, b INT);
277 CREATE TABLE t2(c INT PRIMARY KEY, d INT REFERENCES t1);
278 SELECT t.name, f."table", f."from", i.name, i.pk
279 FROM pragma_table_list() AS t
280 JOIN pragma_foreign_key_list(t.name, t.schema) AS f
281 JOIN pragma_table_info(f."table", t.schema) AS i
282 WHERE i.pk;
283 } {t2 t1 d a 1}
284
285 # With a corrupt VIEW in the schema, the PRAGMA table_list command
286 # will generate internal errors. Confirm that these internal errors
287 # do not appears on the log. https://sqlite.org/src/forumpost/00ee467e
288 test_sqlite3_log [list lappend ::log]
289 set ::log {}
290 do_execsql_test 6.1 {
291 CREATE VIEW v1 AS SELECT abs(a) FROM t1;
292 PRAGMA writable_schema=ON;
293 UPDATE sqlite_schema
294 SET sql=replace(sql,'abs(a)','nosuchfunc(a)')
295 WHERE name='v1';
296 PRAGMA writable_schema=RESET;
297 } {}
298 do_execsql_test 6.2 {
299 PRAGMA table_list;
300 } {main v1 view 0 0 0 main t2 table 2 0 0 main t1 table 2 0 0 main sqlite_schema table 5 0 0 temp sqlite_temp_schema table 5 0 0}
301 do_test 6.3 {
302 set ::log
303 } {}
304 test_sqlite3_log
305 }
306
307 # 2024-05-08 https://sqlite.org/forum/forumpost/cf29a33e94
308 #
309 ifcapable vtab {
310 do_execsql_test 7.0 {
311 CREATE TABLE t3 ("a" TEXT, "b" TEXT);
312 CREATE TABLE t4 ("a" TEXT, "b" TEXT, "c" TEXT);
313 }
314
315 do_execsql_test 7.1 {
316 CREATE TABLE pragma_t3 AS SELECT * FROM pragma_table_info('t3');
317 CREATE TABLE pragma_t4 AS SELECT * FROM pragma_table_info('t4');
318 }
319
320 do_execsql_test 7.2 {
321 SELECT pragma_t4.name, pragma_t3.name
322 FROM pragma_t4 RIGHT JOIN pragma_t3 ON (pragma_t4.name=pragma_t3.name);
323 } {a a b b}
324
325 do_execsql_test 7.3 {
326 SELECT t4.name, t3.name
327 FROM pragma_table_info('t4') t4
328 RIGHT JOIN pragma_table_info('t3') t3 ON (t4.name=t3.name);
329 } {a a b b}
330 }
331
332
333
334
335 finish_test