000001 # 2002 March 6
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library.
000012 #
000013 # This file implements tests for the PRAGMA command.
000014 #
000015 # $Id: pragma.test,v 1.73 2009/01/12 14:01:45 danielk1977 Exp $
000016
000017 set testdir [file dirname $argv0]
000018 source $testdir/tester.tcl
000019 set testprefix pragma
000020
000021 # Do not use a codec for tests in this file, as the database file is
000022 # manipulated directly using tcl scripts (using the [hexio_write] command).
000023 #
000024 do_not_use_codec
000025
000026 # Test organization:
000027 #
000028 # pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.
000029 # pragma-2.*: Test synchronous on attached db.
000030 # pragma-3.*: Test detection of table/index inconsistency by integrity_check.
000031 # pragma-4.*: Test cache_size and default_cache_size on attached db.
000032 # pragma-5.*: Test that pragma synchronous may not be used inside of a
000033 # transaction.
000034 # pragma-6.*: Test schema-query pragmas.
000035 # pragma-7.*: Miscellaneous tests.
000036 # pragma-8.*: Test user_version and schema_version pragmas.
000037 # pragma-9.*: Test temp_store and temp_store_directory.
000038 # pragma-10.*: Test the count_changes pragma in the presence of triggers.
000039 # pragma-11.*: Test the collation_list pragma.
000040 # pragma-14.*: Test the page_count pragma.
000041 # pragma-15.*: Test that the value set using the cache_size pragma is not
000042 # reset when the schema is reloaded.
000043 # pragma-16.*: Test proxy locking
000044 # pragma-20.*: Test data_store_directory.
000045 # pragma-22.*: Test that "PRAGMA [db].integrity_check" respects the "db"
000046 # directive - if it is present.
000047 #
000048
000049 ifcapable !pragma {
000050 finish_test
000051 return
000052 }
000053
000054 # Capture the output of a pragma in a TEMP table.
000055 #
000056 proc capture_pragma {db tabname sql} {
000057 $db eval "DROP TABLE IF EXISTS temp.$tabname"
000058 set once 1
000059 $db eval $sql x {
000060 if {$once} {
000061 set once 0
000062 set ins "INSERT INTO $tabname VALUES"
000063 set crtab "CREATE TEMP TABLE $tabname "
000064 set sep "("
000065 foreach col $x(*) {
000066 append ins ${sep}\$x($col)
000067 append crtab ${sep}\"$col\"
000068 set sep ,
000069 }
000070 append ins )
000071 append crtab )
000072 $db eval $crtab
000073 }
000074 $db eval $ins
000075 }
000076 }
000077
000078 # Delete the preexisting database to avoid the special setup
000079 # that the "all.test" script does.
000080 #
000081 db close
000082 delete_file test.db test.db-journal
000083 delete_file test3.db test3.db-journal
000084 sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
000085
000086 # EVIDENCE-OF: R-13861-56665 PRAGMA schema.cache_size; PRAGMA
000087 # schema.cache_size = pages; PRAGMA schema.cache_size = -kibibytes;
000088 # Query or change the suggested maximum number of database disk pages
000089 # that SQLite will hold in memory at once per open database file.
000090 #
000091 ifcapable pager_pragmas {
000092 set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}]
000093 set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}]
000094 do_test pragma-1.1 {
000095 execsql {
000096 PRAGMA cache_size;
000097 PRAGMA default_cache_size;
000098 PRAGMA synchronous;
000099 }
000100 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
000101 do_test pragma-1.2 {
000102 # EVIDENCE-OF: R-42059-47211 If the argument N is positive then the
000103 # suggested cache size is set to N.
000104 execsql {
000105 PRAGMA synchronous=OFF;
000106 PRAGMA cache_size=1234;
000107 PRAGMA cache_size;
000108 PRAGMA default_cache_size;
000109 PRAGMA synchronous;
000110 }
000111 } [list 1234 $DFLT_CACHE_SZ 0]
000112 do_test pragma-1.3 {
000113 db close
000114 sqlite3 db test.db
000115 execsql {
000116 PRAGMA cache_size;
000117 PRAGMA default_cache_size;
000118 PRAGMA synchronous;
000119 }
000120 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
000121 do_test pragma-1.4 {
000122 execsql {
000123 PRAGMA synchronous=OFF;
000124 PRAGMA cache_size;
000125 PRAGMA default_cache_size;
000126 PRAGMA synchronous;
000127 }
000128 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0]
000129 do_test pragma-1.5 {
000130 execsql {
000131 PRAGMA cache_size=-4321;
000132 PRAGMA cache_size;
000133 PRAGMA default_cache_size;
000134 PRAGMA synchronous;
000135 }
000136 } [list -4321 $DFLT_CACHE_SZ 0]
000137 do_test pragma-1.6 {
000138 execsql {
000139 PRAGMA synchronous=ON;
000140 PRAGMA cache_size;
000141 PRAGMA default_cache_size;
000142 PRAGMA synchronous;
000143 }
000144 } [list -4321 $DFLT_CACHE_SZ 1]
000145 do_test pragma-1.7 {
000146 db close
000147 sqlite3 db test.db
000148 execsql {
000149 PRAGMA cache_size;
000150 PRAGMA default_cache_size;
000151 PRAGMA synchronous;
000152 }
000153 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
000154 do_test pragma-1.8 {
000155 execsql {
000156 PRAGMA default_cache_size=-123;
000157 PRAGMA cache_size;
000158 PRAGMA default_cache_size;
000159 PRAGMA synchronous;
000160 }
000161 } {123 123 2}
000162 do_test pragma-1.9.1 {
000163 db close
000164 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
000165 execsql {
000166 PRAGMA cache_size;
000167 PRAGMA default_cache_size;
000168 PRAGMA synchronous;
000169 }
000170 } {123 123 2}
000171 ifcapable vacuum {
000172 do_test pragma-1.9.2 {
000173 execsql {
000174 VACUUM;
000175 PRAGMA cache_size;
000176 PRAGMA default_cache_size;
000177 PRAGMA synchronous;
000178 }
000179 } {123 123 2}
000180 }
000181 do_test pragma-1.10 {
000182 execsql {
000183 PRAGMA synchronous=NORMAL;
000184 PRAGMA cache_size;
000185 PRAGMA default_cache_size;
000186 PRAGMA synchronous;
000187 }
000188 } {123 123 1}
000189 do_test pragma-1.11.1 {
000190 execsql {
000191 PRAGMA synchronous=EXTRA;
000192 PRAGMA cache_size;
000193 PRAGMA default_cache_size;
000194 PRAGMA synchronous;
000195 }
000196 } {123 123 3}
000197 do_test pragma-1.11.2 {
000198 execsql {
000199 PRAGMA synchronous=FULL;
000200 PRAGMA cache_size;
000201 PRAGMA default_cache_size;
000202 PRAGMA synchronous;
000203 }
000204 } {123 123 2}
000205 do_test pragma-1.12 {
000206 db close
000207 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
000208 execsql {
000209 PRAGMA cache_size;
000210 PRAGMA default_cache_size;
000211 PRAGMA synchronous;
000212 }
000213 } {123 123 2}
000214
000215 # Make sure the pragma handler understands numeric values in addition
000216 # to keywords like "off" and "full".
000217 #
000218 do_test pragma-1.13 {
000219 execsql {
000220 PRAGMA synchronous=0;
000221 PRAGMA synchronous;
000222 }
000223 } {0}
000224 do_test pragma-1.14 {
000225 execsql {
000226 PRAGMA synchronous=2;
000227 PRAGMA synchronous;
000228 }
000229 } {2}
000230 do_test pragma-1.14.1 {
000231 execsql {
000232 PRAGMA synchronous=4;
000233 PRAGMA synchronous;
000234 }
000235 } {4}
000236 do_test pragma-1.14.2 {
000237 execsql {
000238 PRAGMA synchronous=3;
000239 PRAGMA synchronous;
000240 }
000241 } {3}
000242 do_test pragma-1.14.3 {
000243 execsql {
000244 PRAGMA synchronous=8;
000245 PRAGMA synchronous;
000246 }
000247 } {0}
000248 do_test pragma-1.14.4 {
000249 execsql {
000250 PRAGMA synchronous=10;
000251 PRAGMA synchronous;
000252 }
000253 } {2}
000254
000255 do_execsql_test 1.15.1 {
000256 PRAGMA default_cache_size = 0;
000257 }
000258 do_execsql_test 1.15.2 {
000259 PRAGMA default_cache_size;
000260 } $DFLT_CACHE_SZ
000261 do_execsql_test 1.15.3 {
000262 PRAGMA default_cache_size = -500;
000263 }
000264 do_execsql_test 1.15.4 {
000265 PRAGMA default_cache_size;
000266 } 500
000267 do_execsql_test 1.15.3 {
000268 PRAGMA default_cache_size = 500;
000269 }
000270 do_execsql_test 1.15.4 {
000271 PRAGMA default_cache_size;
000272 } 500
000273 db close
000274 hexio_write test.db 48 FFFFFF00
000275 sqlite3 db test.db
000276 do_execsql_test 1.15.4 {
000277 PRAGMA default_cache_size;
000278 } 256
000279 } ;# ifcapable pager_pragmas
000280
000281 # Test turning "flag" pragmas on and off.
000282 #
000283 ifcapable debug {
000284 # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
000285 #
000286 do_test pragma-1.15 {
000287 execsql {
000288 PRAGMA vdbe_listing=YES;
000289 PRAGMA vdbe_listing;
000290 }
000291 } {1}
000292 do_test pragma-1.16 {
000293 execsql {
000294 PRAGMA vdbe_listing=NO;
000295 PRAGMA vdbe_listing;
000296 }
000297 } {0}
000298 }
000299
000300 do_test pragma-1.17 {
000301 execsql {
000302 PRAGMA parser_trace=ON;
000303 PRAGMA parser_trace=OFF;
000304 }
000305 } {}
000306 do_test pragma-1.18 {
000307 execsql {
000308 PRAGMA bogus = -1234; -- Parsing of negative values
000309 }
000310 } {}
000311
000312 # Test modifying the safety_level of an attached database.
000313 ifcapable pager_pragmas&&attach {
000314 do_test pragma-2.1 {
000315 forcedelete test2.db
000316 forcedelete test2.db-journal
000317 execsql {
000318 ATTACH 'test2.db' AS aux;
000319 }
000320 } {}
000321 do_test pragma-2.2 {
000322 execsql {
000323 pragma aux.synchronous;
000324 }
000325 } {2}
000326 do_test pragma-2.3 {
000327 execsql {
000328 pragma aux.synchronous = OFF;
000329 pragma aux.synchronous;
000330 pragma synchronous;
000331 }
000332 } {0 2}
000333 do_test pragma-2.4 {
000334 execsql {
000335 pragma aux.synchronous = ON;
000336 pragma synchronous;
000337 pragma aux.synchronous;
000338 }
000339 } {2 1}
000340 } ;# ifcapable pager_pragmas
000341
000342 # Construct a corrupted index and make sure the integrity_check
000343 # pragma finds it.
000344 #
000345 # These tests won't work if the database is encrypted
000346 #
000347 do_test pragma-3.1 {
000348 db close
000349 forcedelete test.db test.db-journal
000350 sqlite3 db test.db
000351 execsql {
000352 PRAGMA auto_vacuum=OFF;
000353 BEGIN;
000354 CREATE TABLE t2(a,b,c);
000355 CREATE INDEX i2 ON t2(a);
000356 INSERT INTO t2 VALUES(11,2,3);
000357 INSERT INTO t2 VALUES(22,3,4);
000358 COMMIT;
000359 SELECT rowid, * from t2;
000360 }
000361 } {1 11 2 3 2 22 3 4}
000362 ifcapable attach {
000363 if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} {
000364 do_test pragma-3.2 {
000365 db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break
000366 set pgsz [db eval {PRAGMA page_size}]
000367 # overwrite the header on the rootpage of the index in order to
000368 # make the index appear to be empty.
000369 #
000370 set offset [expr {$pgsz*($rootpage-1)}]
000371 hexio_write test.db $offset 0a00000000040000000000
000372 db close
000373 sqlite3 db test.db
000374 execsql {PRAGMA integrity_check}
000375 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000376 do_test pragma-3.3 {
000377 execsql {PRAGMA integrity_check=1}
000378 } {{row 1 missing from index i2}}
000379 do_test pragma-3.4 {
000380 execsql {
000381 ATTACH DATABASE 'test.db' AS t2;
000382 PRAGMA integrity_check
000383 }
000384 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000385 do_test pragma-3.5 {
000386 execsql {
000387 PRAGMA integrity_check=4
000388 }
000389 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2}}
000390 do_catchsql_test pragma-3.6 {
000391 PRAGMA integrity_check=xyz
000392 } {1 {no such table: xyz}}
000393 do_catchsql_test pragma-3.6b {
000394 PRAGMA integrity_check=t2
000395 } {0 {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}}
000396 do_catchsql_test pragma-3.6c {
000397 PRAGMA integrity_check=sqlite_schema
000398 } {0 ok}
000399 do_test pragma-3.7 {
000400 execsql {
000401 PRAGMA integrity_check=0
000402 }
000403 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000404
000405 # Add additional corruption by appending unused pages to the end of
000406 # the database file testerr.db
000407 #
000408 do_test pragma-3.8 {
000409 execsql {DETACH t2}
000410 forcedelete testerr.db testerr.db-journal
000411 set out [open testerr.db w]
000412 fconfigure $out -translation binary
000413 set in [open test.db r]
000414 fconfigure $in -translation binary
000415 puts -nonewline $out [read $in]
000416 seek $in 0
000417 puts -nonewline $out [read $in]
000418 close $in
000419 close $out
000420 hexio_write testerr.db 28 00000000
000421 execsql {REINDEX t2}
000422 execsql {PRAGMA integrity_check}
000423 } {ok}
000424 do_test pragma-3.8.1 {
000425 execsql {PRAGMA quick_check}
000426 } {ok}
000427 do_test pragma-3.8.2 {
000428 execsql {PRAGMA QUICK_CHECK}
000429 } {ok}
000430 do_test pragma-3.9a {
000431 execsql {
000432 ATTACH 'testerr.db' AS t2;
000433 PRAGMA integrity_check
000434 }
000435 } {{*** in database t2 ***
000436 Page 4 is never used
000437 Page 5 is never used
000438 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000439 do_execsql_test pragma-3.9b {
000440 PRAGMA t2.integrity_check=t2;
000441 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000442 do_execsql_test pragma-3.9c {
000443 PRAGMA t2.integrity_check=sqlite_schema;
000444 } {ok}
000445 do_test pragma-3.10 {
000446 execsql {
000447 PRAGMA integrity_check=1
000448 }
000449 } {{*** in database t2 ***
000450 Page 4 is never used}}
000451 do_test pragma-3.11 {
000452 execsql {
000453 PRAGMA integrity_check=5
000454 }
000455 } {{*** in database t2 ***
000456 Page 4 is never used
000457 Page 5 is never used
000458 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2}}
000459 do_test pragma-3.12 {
000460 execsql {
000461 PRAGMA integrity_check=4
000462 }
000463 } {{*** in database t2 ***
000464 Page 4 is never used
000465 Page 5 is never used
000466 Page 6 is never used} {row 1 missing from index i2}}
000467 do_test pragma-3.13 {
000468 execsql {
000469 PRAGMA integrity_check=3
000470 }
000471 } {{*** in database t2 ***
000472 Page 4 is never used
000473 Page 5 is never used
000474 Page 6 is never used}}
000475 do_test pragma-3.14 {
000476 execsql {
000477 PRAGMA integrity_check(2)
000478 }
000479 } {{*** in database t2 ***
000480 Page 4 is never used
000481 Page 5 is never used}}
000482 do_test pragma-3.15 {
000483 execsql {
000484 ATTACH 'testerr.db' AS t3;
000485 PRAGMA integrity_check
000486 }
000487 } {{*** in database t2 ***
000488 Page 4 is never used
000489 Page 5 is never used
000490 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
000491 Page 4 is never used
000492 Page 5 is never used
000493 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}
000494 do_test pragma-3.16 {
000495 execsql {
000496 PRAGMA integrity_check(10)
000497 }
000498 } {{*** in database t2 ***
000499 Page 4 is never used
000500 Page 5 is never used
000501 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
000502 Page 4 is never used
000503 Page 5 is never used
000504 Page 6 is never used} {row 1 missing from index i2}}
000505 do_test pragma-3.17 {
000506 execsql {
000507 PRAGMA integrity_check=8
000508 }
000509 } {{*** in database t2 ***
000510 Page 4 is never used
000511 Page 5 is never used
000512 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
000513 Page 4 is never used
000514 Page 5 is never used}}
000515 do_test pragma-3.18 {
000516 execsql {
000517 PRAGMA integrity_check=4
000518 }
000519 } {{*** in database t2 ***
000520 Page 4 is never used
000521 Page 5 is never used
000522 Page 6 is never used} {row 1 missing from index i2}}
000523 }
000524 do_test pragma-3.19 {
000525 catch {db close}
000526 forcedelete test.db test.db-journal
000527 sqlite3 db test.db
000528 db eval {PRAGMA integrity_check}
000529 } {ok}
000530 }
000531
000532 # Verify that PRAGMA integrity_check catches UNIQUE and NOT NULL
000533 # constraint violations.
000534 #
000535 ifcapable altertable {
000536 sqlite3_db_config db DEFENSIVE 0
000537 do_execsql_test pragma-3.20 {
000538 CREATE TABLE t1(a,b);
000539 CREATE INDEX t1a ON t1(a);
000540 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(2,4),(NULL,5),(NULL,6);
000541 PRAGMA writable_schema=ON;
000542 UPDATE sqlite_master SET sql='CREATE UNIQUE INDEX t1a ON t1(a)'
000543 WHERE name='t1a';
000544 UPDATE sqlite_master SET sql='CREATE TABLE t1(a NOT NULL,b)'
000545 WHERE name='t1';
000546 PRAGMA writable_schema=OFF;
000547 ALTER TABLE t1 RENAME TO t1x;
000548 PRAGMA integrity_check;
000549 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a} {NULL value in t1x.a}}
000550 do_execsql_test pragma-3.21 {
000551 PRAGMA integrity_check(3);
000552 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a}}
000553 do_execsql_test pragma-3.22 {
000554 PRAGMA integrity_check(2);
000555 } {{non-unique entry in index t1a} {NULL value in t1x.a}}
000556 do_execsql_test pragma-3.23 {
000557 PRAGMA integrity_check(1);
000558 } {{non-unique entry in index t1a}}
000559 }
000560
000561 # PRAGMA integrity check (or more specifically the sqlite3BtreeCount()
000562 # interface) used to leave index cursors in an inconsistent state
000563 # which could result in an assertion fault in sqlite3BtreeKey()
000564 # called from saveCursorPosition() if content is removed from the
000565 # index while the integrity_check is still running. This test verifies
000566 # that problem has been fixed.
000567 #
000568 do_test pragma-3.30 {
000569 catch { db close }
000570 delete_file test.db
000571 sqlite3 db test.db
000572 db eval {
000573 CREATE TABLE t1(a,b,c);
000574 WITH RECURSIVE
000575 c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<100)
000576 INSERT INTO t1(a,b,c) SELECT i, printf('xyz%08x',i), 2000-i FROM c;
000577 CREATE INDEX t1a ON t1(a);
000578 CREATE INDEX t1bc ON t1(b,c);
000579 }
000580 db eval {PRAGMA integrity_check} {
000581 db eval {DELETE FROM t1}
000582 }
000583 } {}
000584
000585 # Test modifying the cache_size of an attached database.
000586 ifcapable pager_pragmas&&attach {
000587 do_test pragma-4.1 {
000588 execsql {
000589 ATTACH 'test2.db' AS aux;
000590 pragma aux.cache_size;
000591 pragma aux.default_cache_size;
000592 }
000593 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000594 do_test pragma-4.2 {
000595 execsql {
000596 pragma aux.cache_size = 50;
000597 pragma aux.cache_size;
000598 pragma aux.default_cache_size;
000599 }
000600 } [list 50 $DFLT_CACHE_SZ]
000601 do_test pragma-4.3 {
000602 execsql {
000603 pragma aux.default_cache_size = 456;
000604 pragma aux.cache_size;
000605 pragma aux.default_cache_size;
000606 }
000607 } {456 456}
000608 do_test pragma-4.4 {
000609 execsql {
000610 pragma cache_size;
000611 pragma default_cache_size;
000612 }
000613 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000614 do_test pragma-4.5 {
000615 execsql {
000616 DETACH aux;
000617 ATTACH 'test3.db' AS aux;
000618 pragma aux.cache_size;
000619 pragma aux.default_cache_size;
000620 }
000621 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000622 do_test pragma-4.6 {
000623 execsql {
000624 DETACH aux;
000625 ATTACH 'test2.db' AS aux;
000626 pragma aux.cache_size;
000627 pragma aux.default_cache_size;
000628 }
000629 } {456 456}
000630 } ;# ifcapable pager_pragmas
000631
000632 # Test that modifying the sync-level in the middle of a transaction is
000633 # disallowed.
000634 ifcapable pager_pragmas {
000635 do_test pragma-5.0 {
000636 execsql {
000637 pragma synchronous;
000638 }
000639 } {2}
000640 do_test pragma-5.1 {
000641 catchsql {
000642 BEGIN;
000643 pragma synchronous = OFF;
000644 }
000645 } {1 {Safety level may not be changed inside a transaction}}
000646 do_test pragma-5.2 {
000647 execsql {
000648 pragma synchronous;
000649 }
000650 } {2}
000651 catchsql {COMMIT;}
000652 } ;# ifcapable pager_pragmas
000653
000654 # Test schema-query pragmas
000655 #
000656 ifcapable schema_pragmas {
000657 ifcapable tempdb&&attach {
000658 do_test pragma-6.1 {
000659 set res {}
000660 execsql {SELECT * FROM sqlite_temp_master}
000661 foreach {idx name file} [execsql {pragma database_list}] {
000662 lappend res $idx $name
000663 }
000664 set res
000665 } {0 main 1 temp 2 aux}
000666 }
000667 do_test pragma-6.2 {
000668 execsql {
000669 CREATE TABLE t2(a TYPE_X, b [TYPE_Y], c "TYPE_Z");
000670 pragma table_info(t2)
000671 }
000672 } {0 a TYPE_X 0 {} 0 1 b TYPE_Y 0 {} 0 2 c TYPE_Z 0 {} 0}
000673 do_test pragma-6.2.1 {
000674 execsql {
000675 pragma table_info;
000676 }
000677 } {}
000678 db nullvalue <<NULL>>
000679 do_test pragma-6.2.2 {
000680 execsql {
000681 CREATE TABLE t5(
000682 a TEXT DEFAULT CURRENT_TIMESTAMP,
000683 b DEFAULT (5+3),
000684 c TEXT,
000685 d INTEGER DEFAULT NULL,
000686 e TEXT DEFAULT '',
000687 UNIQUE(b,c,d),
000688 PRIMARY KEY(e,b,c)
000689 );
000690 PRAGMA table_info(t5);
000691 }
000692 } {0 a TEXT 0 CURRENT_TIMESTAMP 0 1 b {} 0 5+3 2 2 c TEXT 0 <<NULL>> 3 3 d INTEGER 0 NULL 0 4 e TEXT 0 '' 1}
000693 db nullvalue {}
000694 do_test pragma-6.2.3 {
000695 execsql {
000696 CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c);
000697 pragma table_info(t2_3)
000698 }
000699 } {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0}
000700 ifcapable {foreignkey} {
000701 do_test pragma-6.3.1 {
000702 execsql {
000703 CREATE TABLE t3(a int references t2(b), b UNIQUE);
000704 pragma foreign_key_list(t3);
000705 }
000706 } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE}
000707 do_test pragma-6.3.2 {
000708 execsql {
000709 pragma foreign_key_list;
000710 }
000711 } {}
000712 do_test pragma-6.3.3 {
000713 execsql {
000714 pragma foreign_key_list(t3_bogus);
000715 }
000716 } {}
000717 do_test pragma-6.3.4 {
000718 execsql {
000719 pragma foreign_key_list(t5);
000720 }
000721 } {}
000722 do_test pragma-6.4 {
000723 capture_pragma db out {
000724 pragma index_list(t3);
000725 }
000726 db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq}
000727 } {0 sqlite_autoindex_t3_1 1}
000728 }
000729 ifcapable {!foreignkey} {
000730 execsql {CREATE TABLE t3(a,b UNIQUE)}
000731 }
000732 do_test pragma-6.5.1 {
000733 execsql {
000734 CREATE INDEX t3i1 ON t3(a,b);
000735 }
000736 capture_pragma db out {
000737 pragma index_info(t3i1);
000738 }
000739 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
000740 } {0 0 a 1 1 b}
000741
000742 # EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown
000743 # by the index_info pragma, but they are listed by the index_xinfo
000744 # pragma.
000745 #
000746 do_test pragma-6.5.1b {
000747 capture_pragma db out {PRAGMA index_xinfo(t3i1)}
000748 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
000749 } {0 0 a 1 1 b 2 -1 {}}
000750
000751
000752 # EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This
000753 # pragma returns one row for each key column in the named index.
000754 #
000755 # (The first column of output from PRAGMA index_info is...)
000756 # EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0
000757 # means left-most.)
000758 #
000759 # (The second column of output from PRAGMA index_info is...)
000760 # EVIDENCE-OF: R-65019-08383 The rank of the column within the table
000761 # being indexed.
000762 #
000763 # (The third column of output from PRAGMA index_info is...)
000764 # EVIDENCE-OF: R-09773-34266 The name of the column being indexed.
000765 #
000766 do_execsql_test pragma-6.5.1c {
000767 CREATE INDEX t3i2 ON t3(b,a);
000768 PRAGMA index_info='t3i2';
000769 DROP INDEX t3i2;
000770 } {0 1 b 1 0 a}
000771
000772 do_test pragma-6.5.2 {
000773 execsql {
000774 pragma index_info(t3i1_bogus);
000775 }
000776 } {}
000777
000778 ifcapable tempdb {
000779 # Test for ticket #3320. When a temp table of the same name exists, make
000780 # sure the schema of the main table can still be queried using
000781 # "pragma table_info":
000782 do_test pragma-6.6.1 {
000783 execsql {
000784 CREATE TABLE trial(col_main);
000785 CREATE TEMP TABLE trial(col_temp);
000786 }
000787 } {}
000788 do_test pragma-6.6.2 {
000789 execsql {
000790 PRAGMA table_info(trial);
000791 }
000792 } {0 col_temp {} 0 {} 0}
000793 do_test pragma-6.6.3 {
000794 execsql {
000795 PRAGMA temp.table_info(trial);
000796 }
000797 } {0 col_temp {} 0 {} 0}
000798 do_test pragma-6.6.4 {
000799 execsql {
000800 PRAGMA main.table_info(trial);
000801 }
000802 } {0 col_main {} 0 {} 0}
000803 }
000804
000805 do_test pragma-6.7 {
000806 execsql {
000807 CREATE TABLE test_table(
000808 one INT NOT NULL DEFAULT -1,
000809 two text,
000810 three VARCHAR(45, 65) DEFAULT 'abcde',
000811 four REAL DEFAULT X'abcdef',
000812 five DEFAULT CURRENT_TIME
000813 );
000814 }
000815 capture_pragma db out {PRAGMA table_info(test_table)}
000816 db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out
000817 ORDER BY cid}
000818 } [concat \
000819 {0 one INT 1 -1 0} \
000820 {1 two TEXT 0 {} 0} \
000821 {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \
000822 {3 four REAL 0 X'abcdef' 0} \
000823 {4 five {} 0 CURRENT_TIME 0} \
000824 ]
000825 do_test pragma-6.8 {
000826 execsql {
000827 CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c));
000828 PRAGMA table_info(t68);
000829 }
000830 } [concat \
000831 {0 a {} 0 {} 1} \
000832 {1 b {} 0 {} 2} \
000833 {2 c {} 0 {} 4} \
000834 ]
000835 } ;# ifcapable schema_pragmas
000836 # Miscellaneous tests
000837 #
000838 ifcapable schema_pragmas {
000839 # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
000840 # pragma returns one row for each index associated with the given table.
000841 #
000842 do_test pragma-7.1.1 {
000843 # Make sure a pragma knows to read the schema if it needs to
000844 db close
000845 sqlite3 db test.db
000846 capture_pragma db out "PRAGMA index_list(t3)"
000847 db eval {SELECT name, "origin" FROM out ORDER BY name DESC}
000848 } {t3i1 c sqlite_autoindex_t3_1 u}
000849 do_test pragma-7.1.2 {
000850 execsql {
000851 pragma index_list(t3_bogus);
000852 }
000853 } {}
000854 } ;# ifcapable schema_pragmas
000855 ifcapable {utf16} {
000856 if {[permutation] == ""} {
000857 do_test pragma-7.2 {
000858 db close
000859 sqlite3 db test.db
000860 catchsql {
000861 pragma encoding=bogus;
000862 }
000863 } {1 {unsupported encoding: bogus}}
000864 }
000865 }
000866 ifcapable tempdb {
000867 do_test pragma-7.3 {
000868 db close
000869 sqlite3 db test.db
000870 execsql {
000871 pragma lock_status;
000872 }
000873 } {main unlocked temp closed}
000874 } else {
000875 do_test pragma-7.3 {
000876 db close
000877 sqlite3 db test.db
000878 execsql {
000879 pragma lock_status;
000880 }
000881 } {main unlocked}
000882 }
000883
000884
000885 #----------------------------------------------------------------------
000886 # Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
000887 # user_version" statements.
000888 #
000889 # pragma-8.1: PRAGMA schema_version
000890 # pragma-8.2: PRAGMA user_version
000891 #
000892
000893 ifcapable schema_version {
000894
000895 # First check that we can set the schema version and then retrieve the
000896 # same value.
000897 do_test pragma-8.1.1 {
000898 execsql {
000899 PRAGMA schema_version = 105;
000900 }
000901 } {}
000902 do_test pragma-8.1.2 {
000903 execsql2 {
000904 PRAGMA schema_version;
000905 }
000906 } {schema_version 105}
000907 do_test pragma-8.1.3 {
000908 execsql {
000909 PRAGMA schema_version = 106;
000910 }
000911 } {}
000912 do_test pragma-8.1.4 {
000913 execsql {
000914 PRAGMA schema_version;
000915 }
000916 } 106
000917
000918 # Check that creating a table modifies the schema-version (this is really
000919 # to verify that the value being read is in fact the schema version).
000920 do_test pragma-8.1.5 {
000921 execsql {
000922 CREATE TABLE t4(a, b, c);
000923 INSERT INTO t4 VALUES(1, 2, 3);
000924 SELECT * FROM t4;
000925 }
000926 } {1 2 3}
000927 do_test pragma-8.1.6 {
000928 execsql {
000929 PRAGMA schema_version;
000930 }
000931 } 107
000932
000933 # Now open a second connection to the database. Ensure that changing the
000934 # schema-version using the first connection forces the second connection
000935 # to reload the schema. This has to be done using the C-API test functions,
000936 # because the TCL API accounts for SCHEMA_ERROR and retries the query.
000937 do_test pragma-8.1.7 {
000938 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
000939 execsql {
000940 SELECT * FROM t4;
000941 } db2
000942 } {1 2 3}
000943 do_test pragma-8.1.8 {
000944 execsql {
000945 PRAGMA schema_version = 108;
000946 }
000947 } {}
000948 do_test pragma-8.1.9 {
000949 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]
000950 sqlite3_step $::STMT
000951 } SQLITE_ERROR
000952 do_test pragma-8.1.10 {
000953 sqlite3_finalize $::STMT
000954 } SQLITE_SCHEMA
000955
000956 # Make sure the schema-version can be manipulated in an attached database.
000957 forcedelete test2.db
000958 forcedelete test2.db-journal
000959 ifcapable attach {
000960 do_test pragma-8.1.11 {
000961 execsql {
000962 ATTACH 'test2.db' AS aux;
000963 CREATE TABLE aux.t1(a, b, c);
000964 PRAGMA aux.schema_version = 205;
000965 }
000966 } {}
000967 do_test pragma-8.1.12 {
000968 execsql {
000969 PRAGMA aux.schema_version;
000970 }
000971 } 205
000972 }
000973 do_test pragma-8.1.13 {
000974 execsql {
000975 PRAGMA schema_version;
000976 }
000977 } 108
000978
000979 # And check that modifying the schema-version in an attached database
000980 # forces the second connection to reload the schema.
000981 ifcapable attach {
000982 do_test pragma-8.1.14 {
000983 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
000984 execsql {
000985 ATTACH 'test2.db' AS aux;
000986 SELECT * FROM aux.t1;
000987 } db2
000988 } {}
000989 do_test pragma-8.1.15 {
000990 execsql {
000991 PRAGMA aux.schema_version = 206;
000992 }
000993 } {}
000994 do_test pragma-8.1.16 {
000995 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
000996 sqlite3_step $::STMT
000997 } SQLITE_ERROR
000998 do_test pragma-8.1.17 {
000999 sqlite3_finalize $::STMT
001000 } SQLITE_SCHEMA
001001 do_test pragma-8.1.18 {
001002 db2 close
001003 } {}
001004 }
001005
001006 # Now test that the user-version can be read and written (and that we aren't
001007 # accidentally manipulating the schema-version instead).
001008 do_test pragma-8.2.1 {
001009 execsql2 {
001010 PRAGMA user_version;
001011 }
001012 } {user_version 0}
001013 do_test pragma-8.2.2 {
001014 execsql {
001015 PRAGMA user_version = 2;
001016 }
001017 } {}
001018 do_test pragma-8.2.3.1 {
001019 execsql2 {
001020 PRAGMA user_version;
001021 }
001022 } {user_version 2}
001023 do_test pragma-8.2.3.2 {
001024 db close
001025 sqlite3 db test.db
001026 execsql {
001027 PRAGMA user_version;
001028 }
001029 } {2}
001030 do_test pragma-8.2.4.1 {
001031 execsql {
001032 PRAGMA schema_version;
001033 }
001034 } {108}
001035 ifcapable vacuum {
001036 do_test pragma-8.2.4.2 {
001037 execsql {
001038 VACUUM;
001039 PRAGMA user_version;
001040 }
001041 } {2}
001042 do_test pragma-8.2.4.3 {
001043 execsql {
001044 PRAGMA schema_version;
001045 }
001046 } {109}
001047 }
001048
001049 ifcapable attach {
001050 db eval {ATTACH 'test2.db' AS aux}
001051
001052 # Check that the user-version in the auxilary database can be manipulated (
001053 # and that we aren't accidentally manipulating the same in the main db).
001054 do_test pragma-8.2.5 {
001055 execsql {
001056 PRAGMA aux.user_version;
001057 }
001058 } {0}
001059 do_test pragma-8.2.6 {
001060 execsql {
001061 PRAGMA aux.user_version = 3;
001062 }
001063 } {}
001064 do_test pragma-8.2.7 {
001065 execsql {
001066 PRAGMA aux.user_version;
001067 }
001068 } {3}
001069 do_test pragma-8.2.8 {
001070 execsql {
001071 PRAGMA main.user_version;
001072 }
001073 } {2}
001074
001075 # Now check that a ROLLBACK resets the user-version if it has been modified
001076 # within a transaction.
001077 do_test pragma-8.2.9 {
001078 execsql {
001079 BEGIN;
001080 PRAGMA aux.user_version = 10;
001081 PRAGMA user_version = 11;
001082 }
001083 } {}
001084 do_test pragma-8.2.10 {
001085 execsql {
001086 PRAGMA aux.user_version;
001087 }
001088 } {10}
001089 do_test pragma-8.2.11 {
001090 execsql {
001091 PRAGMA main.user_version;
001092 }
001093 } {11}
001094 do_test pragma-8.2.12 {
001095 execsql {
001096 ROLLBACK;
001097 PRAGMA aux.user_version;
001098 }
001099 } {3}
001100 do_test pragma-8.2.13 {
001101 execsql {
001102 PRAGMA main.user_version;
001103 }
001104 } {2}
001105 }
001106
001107 # Try a negative value for the user-version
001108 do_test pragma-8.2.14 {
001109 execsql {
001110 PRAGMA user_version = -450;
001111 }
001112 } {}
001113 do_test pragma-8.2.15 {
001114 execsql {
001115 PRAGMA user_version;
001116 }
001117 } {-450}
001118 } ; # ifcapable schema_version
001119
001120 # Check to see if TEMP_STORE is memory or disk. Return strings
001121 # "memory" or "disk" as appropriate.
001122 #
001123 proc check_temp_store {} {
001124 db eval {
001125 PRAGMA temp.cache_size = 1;
001126 CREATE TEMP TABLE IF NOT EXISTS a(b);
001127 DELETE FROM a;
001128 INSERT INTO a VALUES(randomblob(1000));
001129 INSERT INTO a SELECT * FROM a;
001130 INSERT INTO a SELECT * FROM a;
001131 INSERT INTO a SELECT * FROM a;
001132 INSERT INTO a SELECT * FROM a;
001133 INSERT INTO a SELECT * FROM a;
001134 INSERT INTO a SELECT * FROM a;
001135 INSERT INTO a SELECT * FROM a;
001136 INSERT INTO a SELECT * FROM a;
001137 }
001138 db eval {PRAGMA database_list} {
001139 if {$name=="temp"} {
001140 set bt [btree_from_db db 1]
001141 if {[btree_ismemdb $bt]} {
001142 return "memory"
001143 }
001144 return "disk"
001145 }
001146 }
001147 return "unknown"
001148 }
001149
001150 # Application_ID
001151 #
001152 do_test pragma-8.3.1 {
001153 execsql {
001154 PRAGMA application_id;
001155 }
001156 } {0}
001157 do_test pragma-8.3.2 {
001158 execsql {PRAGMA Application_ID(12345); PRAGMA application_id;}
001159 } {12345}
001160
001161 # Test temp_store and temp_store_directory pragmas
001162 #
001163 ifcapable pager_pragmas {
001164 do_test pragma-9.1 {
001165 db close
001166 sqlite3 db test.db
001167 execsql {
001168 PRAGMA temp_store;
001169 }
001170 } {0}
001171 if {$TEMP_STORE<=1} {
001172 do_test pragma-9.1.1 {
001173 check_temp_store
001174 } {disk}
001175 } else {
001176 do_test pragma-9.1.1 {
001177 check_temp_store
001178 } {memory}
001179 }
001180
001181 do_test pragma-9.2 {
001182 db close
001183 sqlite3 db test.db
001184 execsql {
001185 PRAGMA temp_store=file;
001186 PRAGMA temp_store;
001187 }
001188 } {1}
001189 if {$TEMP_STORE==3} {
001190 # When TEMP_STORE is 3, always use memory regardless of pragma settings.
001191 do_test pragma-9.2.1 {
001192 check_temp_store
001193 } {memory}
001194 } else {
001195 do_test pragma-9.2.1 {
001196 check_temp_store
001197 } {disk}
001198 }
001199
001200 do_test pragma-9.3 {
001201 db close
001202 sqlite3 db test.db
001203 execsql {
001204 PRAGMA temp_store=memory;
001205 PRAGMA temp_store;
001206 }
001207 } {2}
001208 if {$TEMP_STORE==0} {
001209 # When TEMP_STORE is 0, always use the disk regardless of pragma settings.
001210 do_test pragma-9.3.1 {
001211 check_temp_store
001212 } {disk}
001213 } else {
001214 do_test pragma-9.3.1 {
001215 check_temp_store
001216 } {memory}
001217 }
001218
001219 do_test pragma-9.4 {
001220 execsql {
001221 PRAGMA temp_store_directory;
001222 }
001223 } {}
001224 ifcapable wsd {
001225 do_test pragma-9.5 {
001226 set pwd [string map {' ''} [file nativename [get_pwd]]]
001227 execsql "
001228 PRAGMA temp_store_directory='$pwd';
001229 "
001230 } {}
001231 do_test pragma-9.6 {
001232 execsql {
001233 PRAGMA temp_store_directory;
001234 }
001235 } [list [file nativename [get_pwd]]]
001236 do_test pragma-9.7 {
001237 catchsql {
001238 PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
001239 }
001240 } {1 {not a writable directory}}
001241 do_test pragma-9.8 {
001242 execsql {
001243 PRAGMA temp_store_directory='';
001244 }
001245 } {}
001246 if {![info exists TEMP_STORE] || $TEMP_STORE<=1} {
001247 ifcapable tempdb {
001248 do_test pragma-9.9 {
001249 execsql {
001250 PRAGMA temp_store_directory;
001251 PRAGMA temp_store=FILE;
001252 CREATE TEMP TABLE temp_store_directory_test(a integer);
001253 INSERT INTO temp_store_directory_test values (2);
001254 SELECT * FROM temp_store_directory_test;
001255 }
001256 } {2}
001257 do_test pragma-9.10 {
001258 catchsql "
001259 PRAGMA temp_store_directory='$pwd';
001260 SELECT * FROM temp_store_directory_test;
001261 "
001262 } {1 {no such table: temp_store_directory_test}}
001263 }
001264 }
001265 }
001266 do_test pragma-9.11 {
001267 execsql {
001268 PRAGMA temp_store = 0;
001269 PRAGMA temp_store;
001270 }
001271 } {0}
001272 do_test pragma-9.12 {
001273 execsql {
001274 PRAGMA temp_store = 1;
001275 PRAGMA temp_store;
001276 }
001277 } {1}
001278 do_test pragma-9.13 {
001279 execsql {
001280 PRAGMA temp_store = 2;
001281 PRAGMA temp_store;
001282 }
001283 } {2}
001284 do_test pragma-9.14 {
001285 execsql {
001286 PRAGMA temp_store = 3;
001287 PRAGMA temp_store;
001288 }
001289 } {0}
001290 do_test pragma-9.15 {
001291 catchsql {
001292 BEGIN EXCLUSIVE;
001293 CREATE TEMP TABLE temp_table(t);
001294 INSERT INTO temp_table VALUES('valuable data');
001295 PRAGMA temp_store = 1;
001296 }
001297 } {1 {temporary storage cannot be changed from within a transaction}}
001298 do_test pragma-9.16 {
001299 execsql {
001300 SELECT * FROM temp_table;
001301 COMMIT;
001302 }
001303 } {{valuable data}}
001304
001305 do_test pragma-9.17 {
001306 execsql {
001307 INSERT INTO temp_table VALUES('valuable data II');
001308 SELECT * FROM temp_table;
001309 }
001310 } {{valuable data} {valuable data II}}
001311
001312 do_test pragma-9.18 {
001313 set rc [catch {
001314 db eval {SELECT t FROM temp_table} {
001315 execsql {pragma temp_store = 1}
001316 }
001317 } msg]
001318 list $rc $msg
001319 } {1 {temporary storage cannot be changed from within a transaction}}
001320
001321 } ;# ifcapable pager_pragmas
001322
001323 ifcapable trigger {
001324
001325 do_test pragma-10.0 {
001326 catchsql {
001327 DROP TABLE main.t1;
001328 }
001329 execsql {
001330 PRAGMA count_changes = 1;
001331
001332 CREATE TABLE t1(a PRIMARY KEY);
001333 CREATE TABLE t1_mirror(a);
001334 CREATE TABLE t1_mirror2(a);
001335 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN
001336 INSERT INTO t1_mirror VALUES(new.a);
001337 END;
001338 CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
001339 INSERT INTO t1_mirror2 VALUES(new.a);
001340 END;
001341 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN
001342 UPDATE t1_mirror SET a = new.a WHERE a = old.a;
001343 END;
001344 CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN
001345 UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;
001346 END;
001347 CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN
001348 DELETE FROM t1_mirror WHERE a = old.a;
001349 END;
001350 CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
001351 DELETE FROM t1_mirror2 WHERE a = old.a;
001352 END;
001353 }
001354 } {}
001355
001356 do_test pragma-10.1 {
001357 execsql {
001358 INSERT INTO t1 VALUES(randstr(10,10));
001359 }
001360 } {1}
001361 do_test pragma-10.2 {
001362 execsql {
001363 UPDATE t1 SET a = randstr(10,10);
001364 }
001365 } {1}
001366 do_test pragma-10.3 {
001367 execsql {
001368 DELETE FROM t1;
001369 }
001370 } {1}
001371
001372 } ;# ifcapable trigger
001373
001374 ifcapable schema_pragmas {
001375 do_test pragma-11.1 {
001376 execsql2 {
001377 pragma collation_list;
001378 }
001379 } {seq 0 name RTRIM seq 1 name NOCASE seq 2 name BINARY}
001380 do_test pragma-11.2 {
001381 db collate New_Collation blah...
001382 execsql {
001383 pragma collation_list;
001384 }
001385 } {0 New_Collation 1 RTRIM 2 NOCASE 3 BINARY}
001386 }
001387
001388 ifcapable schema_pragmas&&tempdb {
001389 do_test pragma-12.1 {
001390 sqlite3 db2 test.db
001391 execsql {
001392 PRAGMA temp.table_info('abc');
001393 } db2
001394 } {}
001395 db2 close
001396
001397 do_test pragma-12.2 {
001398 sqlite3 db2 test.db
001399 execsql {
001400 PRAGMA temp.default_cache_size = 200;
001401 PRAGMA temp.default_cache_size;
001402 } db2
001403 } {200}
001404 db2 close
001405
001406 do_test pragma-12.3 {
001407 sqlite3 db2 test.db
001408 execsql {
001409 PRAGMA temp.cache_size = 400;
001410 PRAGMA temp.cache_size;
001411 } db2
001412 } {400}
001413 db2 close
001414 }
001415
001416 ifcapable bloblit {
001417
001418 do_test pragma-13.1 {
001419 execsql {
001420 DROP TABLE IF EXISTS t4;
001421 PRAGMA vdbe_trace=on;
001422 PRAGMA vdbe_listing=on;
001423 PRAGMA sql_trace=on;
001424 CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
001425 INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');
001426 INSERT INTO t4(b) VALUES(randstr(30,30));
001427 INSERT INTO t4(b) VALUES(1.23456);
001428 INSERT INTO t4(b) VALUES(NULL);
001429 INSERT INTO t4(b) VALUES(0);
001430 INSERT INTO t4(b) SELECT b||b||b||b FROM t4;
001431 SELECT * FROM t4;
001432 }
001433 execsql {
001434 PRAGMA vdbe_trace=off;
001435 PRAGMA vdbe_listing=off;
001436 PRAGMA sql_trace=off;
001437 }
001438 } {}
001439
001440 } ;# ifcapable bloblit
001441
001442 ifcapable pager_pragmas {
001443 db close
001444 forcedelete test.db
001445 sqlite3 db test.db
001446
001447 # EVIDENCE-OF: R-15672-33611 PRAGMA schema.page_count; Return the total
001448 # number of pages in the database file.
001449 #
001450 do_test pragma-14.1 {
001451 execsql { pragma auto_vacuum = 0 }
001452 execsql { pragma page_count; pragma main.page_count }
001453 } {0 0}
001454
001455 do_test pragma-14.2 {
001456 execsql {
001457 CREATE TABLE abc(a, b, c);
001458 PRAGMA page_count;
001459 PRAGMA main.page_count;
001460 PRAGMA temp.page_count;
001461 }
001462 } {2 2 0}
001463 do_test pragma-14.2uc {
001464 execsql {pragma PAGE_COUNT}
001465 } {2}
001466
001467 do_test pragma-14.3 {
001468 execsql {
001469 BEGIN;
001470 CREATE TABLE def(a, b, c);
001471 PRAGMA page_count;
001472 }
001473 } {3}
001474 do_test pragma-14.3uc {
001475 execsql {pragma PAGE_COUNT}
001476 } {3}
001477
001478 do_test pragma-14.4 {
001479 set page_size [db one {pragma page_size}]
001480 expr [file size test.db] / $page_size
001481 } {2}
001482
001483 do_test pragma-14.5 {
001484 execsql {
001485 ROLLBACK;
001486 PRAGMA page_count;
001487 }
001488 } {2}
001489
001490 do_test pragma-14.6 {
001491 forcedelete test2.db
001492 sqlite3 db2 test2.db
001493 execsql {
001494 PRAGMA auto_vacuum = 0;
001495 CREATE TABLE t1(a, b, c);
001496 CREATE TABLE t2(a, b, c);
001497 CREATE TABLE t3(a, b, c);
001498 CREATE TABLE t4(a, b, c);
001499 } db2
001500 db2 close
001501 execsql {
001502 ATTACH 'test2.db' AS aux;
001503 PRAGMA aux.page_count;
001504 }
001505 } {5}
001506 do_test pragma-14.6uc {
001507 execsql {pragma AUX.PAGE_COUNT}
001508 } {5}
001509 }
001510
001511 # Test that the value set using the cache_size pragma is not reset when the
001512 # schema is reloaded.
001513 #
001514 ifcapable pager_pragmas {
001515 db close
001516 sqlite3 db test.db
001517 do_test pragma-15.1 {
001518 execsql {
001519 PRAGMA cache_size=59;
001520 PRAGMA cache_size;
001521 }
001522 } {59}
001523 do_test pragma-15.2 {
001524 sqlite3 db2 test.db
001525 execsql {
001526 CREATE TABLE newtable(a, b, c);
001527 } db2
001528 db2 close
001529 } {}
001530 do_test pragma-15.3 {
001531 # Evaluating this statement will cause the schema to be reloaded (because
001532 # the schema was changed by another connection in pragma-15.2). At one
001533 # point there was a bug that reset the cache_size to its default value
001534 # when this happened.
001535 execsql { SELECT * FROM sqlite_master }
001536 execsql { PRAGMA cache_size }
001537 } {59}
001538 }
001539
001540 # Reset the sqlite3_temp_directory variable for the next run of tests:
001541 sqlite3 dbX :memory:
001542 dbX eval {PRAGMA temp_store_directory = ""}
001543 dbX close
001544
001545 ifcapable lock_proxy_pragmas&&prefer_proxy_locking {
001546 set sqlite_hostid_num 1
001547
001548 set using_proxy 0
001549 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
001550 set using_proxy $value
001551 }
001552
001553 # Test the lock_proxy_file pragmas.
001554 #
001555 db close
001556 set env(SQLITE_FORCE_PROXY_LOCKING) "0"
001557
001558 sqlite3 db test.db
001559 do_test pragma-16.1 {
001560 execsql {
001561 PRAGMA lock_proxy_file="mylittleproxy";
001562 select * from sqlite_master;
001563 }
001564 execsql {
001565 PRAGMA lock_proxy_file;
001566 }
001567 } {mylittleproxy}
001568
001569 do_test pragma-16.2 {
001570 sqlite3 db2 test.db
001571 execsql {
001572 PRAGMA lock_proxy_file="mylittleproxy";
001573 } db2
001574 } {}
001575
001576 db2 close
001577 do_test pragma-16.2.1 {
001578 sqlite3 db2 test.db
001579 execsql {
001580 PRAGMA lock_proxy_file=":auto:";
001581 select * from sqlite_master;
001582 } db2
001583 execsql {
001584 PRAGMA lock_proxy_file;
001585 } db2
001586 } {mylittleproxy}
001587
001588 db2 close
001589 do_test pragma-16.3 {
001590 sqlite3 db2 test.db
001591 execsql {
001592 PRAGMA lock_proxy_file="myotherproxy";
001593 } db2
001594 catchsql {
001595 select * from sqlite_master;
001596 } db2
001597 } {1 {database is locked}}
001598
001599 do_test pragma-16.4 {
001600 db2 close
001601 db close
001602 sqlite3 db2 test.db
001603 execsql {
001604 PRAGMA lock_proxy_file="myoriginalproxy";
001605 PRAGMA lock_proxy_file="myotherproxy";
001606 PRAGMA lock_proxy_file;
001607 } db2
001608 } {myotherproxy}
001609
001610 db2 close
001611 set env(SQLITE_FORCE_PROXY_LOCKING) "1"
001612 do_test pragma-16.5 {
001613 sqlite3 db2 test.db
001614 execsql {
001615 PRAGMA lock_proxy_file=":auto:";
001616 PRAGMA lock_proxy_file;
001617 } db2
001618 } {myotherproxy}
001619
001620 do_test pragma-16.6 {
001621 db2 close
001622 sqlite3 db2 test2.db
001623 set lockpath [execsql {
001624 PRAGMA lock_proxy_file=":auto:";
001625 PRAGMA lock_proxy_file;
001626 } db2]
001627 string match "*test2.db:auto:" $lockpath
001628 } {1}
001629
001630 set sqlite_hostid_num 2
001631 do_test pragma-16.7 {
001632 list [catch {
001633 sqlite3 db test2.db
001634 execsql {
001635 PRAGMA lock_proxy_file=":auto:";
001636 select * from sqlite_master;
001637 }
001638 } msg] $msg
001639 } {1 {database is locked}}
001640 db close
001641
001642 do_test pragma-16.8 {
001643 list [catch {
001644 sqlite3 db test2.db
001645 execsql { select * from sqlite_master }
001646 } msg] $msg
001647 } {1 {database is locked}}
001648
001649 db2 close
001650 do_test pragma-16.8.1 {
001651 execsql {
001652 PRAGMA lock_proxy_file="yetanotherproxy";
001653 PRAGMA lock_proxy_file;
001654 }
001655 } {yetanotherproxy}
001656 do_test pragma-16.8.2 {
001657 execsql {
001658 create table mine(x);
001659 }
001660 } {}
001661
001662 db close
001663 do_test pragma-16.9 {
001664 sqlite3 db proxytest.db
001665 set lockpath2 [execsql {
001666 PRAGMA lock_proxy_file=":auto:";
001667 PRAGMA lock_proxy_file;
001668 } db]
001669 string match "*proxytest.db:auto:" $lockpath2
001670 } {1}
001671
001672 set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy
001673 set sqlite_hostid_num 0
001674 }
001675
001676 # Parsing of auto_vacuum settings.
001677 #
001678 foreach {autovac_setting val} {
001679 0 0
001680 1 1
001681 2 2
001682 3 0
001683 -1 0
001684 none 0
001685 NONE 0
001686 NoNe 0
001687 full 1
001688 FULL 1
001689 incremental 2
001690 INCREMENTAL 2
001691 -1234 0
001692 1234 0
001693 } {
001694 do_test pragma-17.1.$autovac_setting {
001695 catch {db close}
001696 sqlite3 db :memory:
001697 execsql "
001698 PRAGMA auto_vacuum=$::autovac_setting;
001699 PRAGMA auto_vacuum;
001700 "
001701 } $val
001702 }
001703
001704 # Parsing of temp_store settings.
001705 #
001706 foreach {temp_setting val} {
001707 0 0
001708 1 1
001709 2 2
001710 3 0
001711 -1 0
001712 file 1
001713 FILE 1
001714 fIlE 1
001715 memory 2
001716 MEMORY 2
001717 MeMoRy 2
001718 } {
001719 do_test pragma-18.1.$temp_setting {
001720 catch {db close}
001721 sqlite3 db :memory:
001722 execsql "
001723 PRAGMA temp_store=$::temp_setting;
001724 PRAGMA temp_store=$::temp_setting;
001725 PRAGMA temp_store;
001726 "
001727 } $val
001728 }
001729
001730 # The SQLITE_FCNTL_PRAGMA logic, with error handling.
001731 #
001732 db close
001733 testvfs tvfs
001734 sqlite3 db test.db -vfs tvfs
001735 do_test pragma-19.1 {
001736 catchsql {PRAGMA error}
001737 } {1 {SQL logic error}}
001738 do_test pragma-19.2 {
001739 catchsql {PRAGMA error='This is the error message'}
001740 } {1 {This is the error message}}
001741 do_test pragma-19.3 {
001742 catchsql {PRAGMA error='7 This is the error message'}
001743 } {1 {This is the error message}}
001744 do_test pragma-19.4 {
001745 catchsql {PRAGMA error=7}
001746 } {1 {out of memory}}
001747 do_test pragma-19.5 {
001748 file tail [lindex [execsql {PRAGMA filename}] 0]
001749 } {test.db}
001750
001751 if {$tcl_platform(platform)=="windows"} {
001752 # Test data_store_directory pragma
001753 #
001754 db close
001755 sqlite3 db test.db
001756 file mkdir data_dir
001757 do_test pragma-20.1 {
001758 catchsql {PRAGMA data_store_directory}
001759 } {0 {}}
001760 do_test pragma-20.2 {
001761 set pwd [string map {' ''} [file nativename [get_pwd]]]
001762 catchsql "PRAGMA data_store_directory='$pwd';"
001763 } {0 {}}
001764 do_test pragma-20.3 {
001765 catchsql {PRAGMA data_store_directory}
001766 } [list 0 [list [file nativename [get_pwd]]]]
001767 do_test pragma-20.4 {
001768 set pwd [string map {' ''} [file nativename \
001769 [file join [get_pwd] data_dir]]]
001770 catchsql "PRAGMA data_store_directory='$pwd';"
001771 } {0 {}}
001772 do_test pragma-20.5 {
001773 sqlite3 db2 test2.db
001774 catchsql "PRAGMA database_list;" db2
001775 } [list 0 [list 0 main [file nativename \
001776 [file join [get_pwd] data_dir test2.db]]]]
001777 catch {db2 close}
001778 do_test pragma-20.6 {
001779 sqlite3 db2 [file join [get_pwd] test2.db]
001780 catchsql "PRAGMA database_list;" db2
001781 } [list 0 [list 0 main [file nativename \
001782 [file join [get_pwd] test2.db]]]]
001783 catch {db2 close}
001784 do_test pragma-20.7 {
001785 catchsql "PRAGMA data_store_directory='';"
001786 } {0 {}}
001787 do_test pragma-20.8 {
001788 catchsql {PRAGMA data_store_directory}
001789 } {0 {}}
001790
001791 forcedelete data_dir
001792 } ;# endif windows
001793
001794 database_may_be_corrupt
001795 if {![nonzero_reserved_bytes]} {
001796
001797 do_test 21.1 {
001798 # Create a corrupt database in testerr.db. And a non-corrupt at test.db.
001799 #
001800 db close
001801 forcedelete test.db
001802 sqlite3 db test.db
001803 execsql {
001804 PRAGMA page_size = 1024;
001805 PRAGMA auto_vacuum = 0;
001806 CREATE TABLE t1(a PRIMARY KEY, b);
001807 INSERT INTO t1 VALUES(1, 1);
001808 }
001809 for {set i 0} {$i < 10} {incr i} {
001810 execsql { INSERT INTO t1 SELECT a + (1 << $i), b + (1 << $i) FROM t1 }
001811 }
001812 db close
001813 forcecopy test.db testerr.db
001814 hexio_write testerr.db 15000 [string repeat 55 100]
001815 } {100}
001816
001817 set mainerr {*** in database main ***
001818 Multiple uses for byte 672 of page 15}
001819 set auxerr {*** in database aux ***
001820 Multiple uses for byte 672 of page 15}
001821
001822 set mainerr {/{\*\*\* in database main \*\*\*
001823 Multiple uses for byte 672 of page 15}.*/}
001824 set auxerr {/{\*\*\* in database aux \*\*\*
001825 Multiple uses for byte 672 of page 15}.*/}
001826
001827 do_test 22.2 {
001828 catch { db close }
001829 sqlite3 db testerr.db
001830 execsql { PRAGMA integrity_check }
001831 } $mainerr
001832
001833 do_test 22.3.1 {
001834 catch { db close }
001835 sqlite3 db test.db
001836 execsql {
001837 ATTACH 'testerr.db' AS 'aux';
001838 PRAGMA integrity_check;
001839 }
001840 } $auxerr
001841 do_test 22.3.2 {
001842 execsql { PRAGMA main.integrity_check; }
001843 } {ok}
001844 do_test 22.3.3 {
001845 execsql { PRAGMA aux.integrity_check; }
001846 } $auxerr
001847
001848 do_test 22.4.1 {
001849 catch { db close }
001850 sqlite3 db testerr.db
001851 execsql {
001852 ATTACH 'test.db' AS 'aux';
001853 PRAGMA integrity_check;
001854 }
001855 } $mainerr
001856 do_test 22.4.2 {
001857 execsql { PRAGMA main.integrity_check; }
001858 } $mainerr
001859 do_test 22.4.3 {
001860 execsql { PRAGMA aux.integrity_check; }
001861 } {ok}
001862 }
001863
001864 db close
001865 forcedelete test.db test.db-wal test.db-journal
001866 sqlite3 db test.db
001867 sqlite3 db2 test.db
001868 do_test 23.1 {
001869 db eval {
001870 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
001871 CREATE INDEX i1 ON t1(b,c);
001872 CREATE INDEX i2 ON t1(c,d);
001873 CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC);
001874 CREATE INDEX i3 ON t1(d,b+c,c);
001875 CREATE TABLE t2(x INTEGER REFERENCES t1);
001876 }
001877 db2 eval {SELECT name FROM sqlite_master}
001878 } {t1 i1 i2 i2x i3 t2}
001879 do_test 23.2a {
001880 db eval {
001881 DROP INDEX i2;
001882 CREATE INDEX i2 ON t1(c,d,b);
001883 }
001884 capture_pragma db2 out {PRAGMA index_info(i2)}
001885 db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno}
001886 } {2 c | 3 d | 1 b |}
001887
001888 # EVIDENCE-OF: R-56143-29319 PRAGMA schema.index_xinfo(index-name); This
001889 # pragma returns information about every column in an index.
001890 #
001891 # EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma
001892 # returns information about every column in the index, not just the key
001893 # columns.
001894 #
001895 do_test 23.2b {
001896 capture_pragma db2 out {PRAGMA index_xinfo(i2)}
001897 db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
001898 } {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |}
001899
001900 # (The first column of output from PRAGMA index_xinfo is...)
001901 # EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0
001902 # means left-most. Key columns come before auxiliary columns.)
001903 #
001904 # (The second column of output from PRAGMA index_xinfo is...)
001905 # EVIDENCE-OF: R-06603-49335 The rank of the column within the table
001906 # being indexed, or -1 if the index-column is the rowid of the table
001907 # being indexed and -2 if the index is on an expression.
001908 #
001909 # (The third column of output from PRAGMA index_xinfo is...)
001910 # EVIDENCE-OF: R-40641-22898 The name of the column being indexed, or
001911 # NULL if the index-column is the rowid of the table being indexed or an
001912 # expression.
001913 #
001914 # (The fourth column of output from PRAGMA index_xinfo is...)
001915 # EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse
001916 # (DESC) order by the index and 0 otherwise.
001917 #
001918 # (The fifth column of output from PRAGMA index_xinfo is...)
001919 # EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to
001920 # compare values in the index-column.
001921 #
001922 # (The sixth column of output from PRAGMA index_xinfo is...)
001923 # EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0
001924 # if the index-column is an auxiliary column.
001925 #
001926 do_test 23.2c {
001927 db2 eval {PRAGMA index_xinfo(i2)}
001928 } {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0}
001929 do_test 23.2d {
001930 db2 eval {PRAGMA index_xinfo(i2x)}
001931 } {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0}
001932 do_test 23.2e {
001933 db2 eval {PRAGMA index_xinfo(i3)}
001934 } {0 3 d 0 BINARY 1 1 -2 {} 0 BINARY 1 2 2 c 0 BINARY 1 3 -1 {} 0 BINARY 0}
001935
001936 # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
001937 # pragma returns one row for each index associated with the given table.
001938 #
001939 # (The first column of output from PRAGMA index_list is...)
001940 # EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index
001941 # for internal tracking purposes.
001942 #
001943 # (The second column of output from PRAGMA index_list is...)
001944 # EVIDENCE-OF: R-35496-03635 The name of the index.
001945 #
001946 # (The third column of output from PRAGMA index_list is...)
001947 # EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not.
001948 #
001949 # (The fourth column of output from PRAGMA index_list is...)
001950 # EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE
001951 # INDEX statement, "u" if the index was created by a UNIQUE constraint,
001952 # or "pk" if the index was created by a PRIMARY KEY constraint.
001953 #
001954 do_test 23.3 {
001955 db eval {
001956 DROP INDEX IF EXISTS i3;
001957 CREATE INDEX i3 ON t1(d,b,c);
001958 }
001959 capture_pragma db2 out {PRAGMA index_list(t1)}
001960 db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq}
001961 } {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |}
001962 ifcapable altertable {
001963 do_test 23.4 {
001964 db eval {
001965 ALTER TABLE t1 ADD COLUMN e;
001966 }
001967 db2 eval {
001968 PRAGMA table_info(t1);
001969 }
001970 } {/4 e {} 0 {} 0/}
001971 }
001972 do_test 23.5 {
001973 db eval {
001974 DROP TABLE t2;
001975 CREATE TABLE t2(x, y INTEGER REFERENCES t1);
001976 }
001977 db2 eval {
001978 PRAGMA foreign_key_list(t2);
001979 }
001980 } {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE}
001981 db2 close
001982
001983 ifcapable !has_codec {
001984 reset_db
001985 do_execsql_test 24.0 {
001986 PRAGMA page_size = 1024;
001987 CREATE TABLE t1(a, b, c);
001988 CREATE INDEX i1 ON t1(b);
001989 INSERT INTO t1 VALUES('a', 'b', 'c');
001990 PRAGMA integrity_check;
001991 } {ok}
001992
001993 set r [db one {SELECT rootpage FROM sqlite_master WHERE name = 't1'}]
001994 db close
001995 hexio_write test.db [expr $r*1024 - 16] 000000000000000701040f0f1f616263
001996
001997 sqlite3 db test.db
001998 do_catchsql_test 24.1 {
001999 SELECT * FROM t1;
002000 } {1 {database disk image is malformed}}
002001 do_catchsql_test 24.2 {
002002 PRAGMA integrity_check;
002003 } {0 {{database disk image is malformed}}}
002004 }
002005 database_never_corrupt
002006 finish_test