gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/pragma4.test (about) 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 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 2 2 1 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 268 finish_test