AryaWu/sqlite
0
1# 2017 Jan 42#3# The author disclaims copyright to this source code. In place of4# 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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix pragma417 18proc do_pragma_ncol_test {tn sql nCol} {19 set ::stmt 020 set ::stmt [sqlite3_prepare_v2 db $sql -1 dummy]21 uplevel [list do_test $tn { sqlite3_column_count $::stmt } $nCol]22 sqlite3_finalize $::stmt23}24 25# If there is no RHS argument, the following PRAGMA statements operate as26# 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#30foreach {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_db61 62 # Without RHS:63 do_pragma_ncol_test 1.$tn.1 [lindex [split $sql =] 0] 164 65 # With RHS:66 do_pragma_ncol_test 1.$tn.2 $sql 067}68 69# These pragmas should never return any values.70#71foreach {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 080}81 82# EXPLAIN on a PRAGMA integrity_check.83# Verify that that P4_INTARRAY argument to OP_IntegrityCk is rendered84# correctly.85#86catch {db close}87forcedelete test.db88sqlite3 db test.db89do_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#105reset_db106forcedelete test.db2107do_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}112do_execsql_test 4.1.2 { PRAGMA table_info = t1 } {113 0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0114}115do_execsql_test 4.1.3 { PRAGMA table_info = t2 } {116 0 d {} 0 {} 0 1 e {} 0 {} 0 2 f {} 0 {} 0117}118do_test 4.1.4 { 119 sqlite3 db3 test.db120 sqlite3 db2 test.db2121 execsql { DROP TABLE t1 } db3122 execsql { DROP TABLE t2 } db2123} {} 124if {[permutation]=="prepare"} {125 do_catchsql_test 4.1.5a { 126 PRAGMA table_info(t1) 127 } {1 {database schema has changed}}128}129do_execsql_test 4.1.5 { 130 PRAGMA table_info(t1) 131}132do_execsql_test 4.1.6 { PRAGMA table_info(t2) }133 134db2 close135db3 close136reset_db137forcedelete test.db2138do_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}143ifcapable 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 {} 0146 }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 {} 0149 }150}151do_test 4.2.4 { 152 sqlite3 db3 test.db153 sqlite3 db2 test.db2154 execsql { DROP TABLE t1 } db3155 execsql { DROP TABLE t2 } db2156} {}157ifcapable 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 162db2 close163db3 close164reset_db165forcedelete test.db2166do_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}173ifcapable 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}177do_test 4.3.4 { 178 sqlite3 db3 test.db179 sqlite3 db2 test.db2180 execsql { DROP INDEX i1 } db3181 execsql { DROP INDEX i2 } db2182} {}183if {[permutation]=="prepare"} { catchsql { SELECT * FROM sqlite_master } }184ifcapable 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 189execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}190do_execsql_test 4.4.0 {191 CREATE INDEX main.i1 ON t1(b, c);192 CREATE INDEX aux.i2 ON t2(e, f);193}194ifcapable 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}198do_test 4.4.3 { 199 execsql { DROP INDEX i1 } db3200 execsql { DROP INDEX i2 } db2201} {}202if {[permutation]=="prepare"} { 203 catchsql { SELECT * FROM sqlite_master, aux.sqlite_master }204}205ifcapable 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}209execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}210 211do_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}217ifcapable 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} NONE220 }221 do_execsql_test 4.5.2 { SELECT * FROM pragma_foreign_key_list('c2') } {222 0 0 t2 r d {NO ACTION} {NO ACTION} NONE223 }224}225do_test 4.5.3 { 226 execsql { DROP TABLE c1 } db3227 execsql { DROP TABLE c2 } db2228} {}229if {[permutation]=="prepare"} { 230 catchsql { SELECT * FROM sqlite_master, aux.sqlite_master }231}232ifcapable 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}236execsql {SELECT * FROM main.sqlite_master, aux.sqlite_master}237 238do_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}244do_execsql_test 4.6.1 { pragma foreign_key_check('c1') } {245 c1 1 t1 0246}247do_execsql_test 4.6.2 { pragma foreign_key_check('c2') } {248 c2 1 t2 0249}250do_test 4.6.3 { 251 execsql { DROP TABLE c2 } db2252} {}253do_execsql_test 4.6.4 { pragma foreign_key_check('c1') } {c1 1 t1 0}254do_catchsql_test 4.6.5 { 255 pragma foreign_key_check('c2') 256} {1 {no such table: c2}}257 258do_execsql_test 5.0 {259 CREATE TABLE t4(a DEFAULT 'abc' /* comment */, b DEFAULT -1 -- comment260 , 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 0265}266 267# 2024-03-24 https://sqlite.org/forum/forumpost/85b6a8b6705fb77a268#269catch {db2 close}270catch {db3 close}271ifcapable vtab {272 reset_db273 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.pk279 FROM pragma_table_list() AS t280 JOIN pragma_foreign_key_list(t.name, t.schema) AS f281 JOIN pragma_table_info(f."table", t.schema) AS i282 WHERE i.pk;283 } {t2 t1 d a 1}284 285 # With a corrupt VIEW in the schema, the PRAGMA table_list command286 # will generate internal errors. Confirm that these internal errors287 # do not appears on the log. https://sqlite.org/src/forumpost/00ee467e288 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_schema294 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 ::log303 } {}304 test_sqlite3_log305}306 307# 2024-05-08 https://sqlite.org/forum/forumpost/cf29a33e94308#309ifcapable 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 335finish_test336