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: never used
000437 Page 5: never used
000438 Page 6: 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: never used}}
000451 do_test pragma-3.11 {
000452 execsql {
000453 PRAGMA integrity_check=5
000454 }
000455 } {{*** in database t2 ***
000456 Page 4: never used
000457 Page 5: never used
000458 Page 6: 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: never used
000465 Page 5: never used
000466 Page 6: 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: never used
000473 Page 5: never used
000474 Page 6: never used}}
000475 do_test pragma-3.14 {
000476 execsql {
000477 PRAGMA integrity_check(2)
000478 }
000479 } {{*** in database t2 ***
000480 Page 4: never used
000481 Page 5: 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: never used
000489 Page 5: never used
000490 Page 6: 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: never used
000492 Page 5: never used
000493 Page 6: 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: never used
000500 Page 5: never used
000501 Page 6: 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: never used
000503 Page 5: never used
000504 Page 6: 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: never used
000511 Page 5: never used
000512 Page 6: 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: never used
000514 Page 5: never used}}
000515 do_test pragma-3.18 {
000516 execsql {
000517 PRAGMA integrity_check=4
000518 }
000519 } {{*** in database t2 ***
000520 Page 4: never used
000521 Page 5: never used
000522 Page 6: 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 # The values stored in indexes must be byte-for-byte identical to the
000586 # values stored in tables.
000587 #
000588 reset_db
000589 do_execsql_test pragma-3.40 {
000590 CREATE TABLE t1(
000591 a INTEGER PRIMARY KEY,
000592 b TEXT COLLATE nocase,
000593 c INT COLLATE nocase,
000594 d TEXT
000595 );
000596 INSERT INTO t1(a,b,c,d) VALUES
000597 (1, 'one','one','one'),
000598 (2, 'two','two','two'),
000599 (3, 'three','three','three'),
000600 (4, 'four','four','four'),
000601 (5, 'five','five','five');
000602 CREATE INDEX t1bcd ON t1(b,c,d);
000603 CREATE TABLE t2(
000604 a INTEGER PRIMARY KEY,
000605 b TEXT COLLATE nocase,
000606 c INT COLLATE nocase,
000607 d TEXT
000608 );
000609 INSERT INTO t2(a,b,c,d) VALUES
000610 (1, 'one','one','one'),
000611 (2, 'two','two','TWO'),
000612 (3, 'three','THREE','three'),
000613 (4, 'FOUR','four','four'),
000614 (5, 'FIVE','FIVE','five');
000615 CREATE INDEX t2bcd ON t2(b,c,d);
000616 CREATE TEMP TABLE saved_schema AS SELECT name, rootpage FROM sqlite_schema;
000617 PRAGMA writable_schema=ON;
000618 UPDATE sqlite_schema
000619 SET rootpage=(SELECT rootpage FROM saved_schema WHERE name='t2bcd')
000620 WHERE name='t1bcd';
000621 UPDATE sqlite_schema
000622 SET rootpage=(SELECT rootpage FROM saved_schema WHERE name='t1bcd')
000623 WHERE name='t2bcd';
000624 PRAGMA Writable_schema=RESET;
000625 }
000626 ifcapable vtab {
000627 do_execsql_test pragma-3.41 {
000628 SELECT integrity_check AS x FROM pragma_integrity_check ORDER BY 1;
000629 } {
000630 {row 2 missing from index t1bcd}
000631 {row 2 missing from index t2bcd}
000632 {row 3 values differ from index t1bcd}
000633 {row 3 values differ from index t2bcd}
000634 {row 4 values differ from index t1bcd}
000635 {row 4 values differ from index t2bcd}
000636 {row 5 values differ from index t1bcd}
000637 {row 5 values differ from index t2bcd}
000638 }
000639 }
000640 db eval {DROP TABLE t2}
000641
000642 # Test modifying the cache_size of an attached database.
000643 ifcapable pager_pragmas&&attach {
000644 do_test pragma-4.1 {
000645 execsql {
000646 ATTACH 'test2.db' AS aux;
000647 pragma aux.cache_size;
000648 pragma aux.default_cache_size;
000649 }
000650 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000651 do_test pragma-4.2 {
000652 execsql {
000653 pragma aux.cache_size = 50;
000654 pragma aux.cache_size;
000655 pragma aux.default_cache_size;
000656 }
000657 } [list 50 $DFLT_CACHE_SZ]
000658 do_test pragma-4.3 {
000659 execsql {
000660 pragma aux.default_cache_size = 456;
000661 pragma aux.cache_size;
000662 pragma aux.default_cache_size;
000663 }
000664 } {456 456}
000665 do_test pragma-4.4 {
000666 execsql {
000667 pragma cache_size;
000668 pragma default_cache_size;
000669 }
000670 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000671 do_test pragma-4.5 {
000672 execsql {
000673 DETACH aux;
000674 ATTACH 'test3.db' AS aux;
000675 pragma aux.cache_size;
000676 pragma aux.default_cache_size;
000677 }
000678 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
000679 do_test pragma-4.6 {
000680 execsql {
000681 DETACH aux;
000682 ATTACH 'test2.db' AS aux;
000683 pragma aux.cache_size;
000684 pragma aux.default_cache_size;
000685 }
000686 } {456 456}
000687 } ;# ifcapable pager_pragmas
000688
000689 # Test that modifying the sync-level in the middle of a transaction is
000690 # disallowed.
000691 ifcapable pager_pragmas {
000692 do_test pragma-5.0 {
000693 execsql {
000694 pragma synchronous;
000695 }
000696 } {2}
000697 do_test pragma-5.1 {
000698 catchsql {
000699 BEGIN;
000700 pragma synchronous = OFF;
000701 }
000702 } {1 {Safety level may not be changed inside a transaction}}
000703 do_test pragma-5.2 {
000704 execsql {
000705 pragma synchronous;
000706 }
000707 } {2}
000708 catchsql {COMMIT;}
000709 } ;# ifcapable pager_pragmas
000710
000711 # Test schema-query pragmas
000712 #
000713 ifcapable schema_pragmas {
000714 ifcapable tempdb&&attach {
000715 do_test pragma-6.1 {
000716 set res {}
000717 execsql {SELECT * FROM sqlite_temp_master}
000718 foreach {idx name file} [execsql {pragma database_list}] {
000719 lappend res $idx $name
000720 }
000721 set res
000722 } {0 main 1 temp 2 aux}
000723 }
000724 do_test pragma-6.2 {
000725 execsql {
000726 CREATE TABLE t2(a TYPE_X, b [TYPE_Y], c "TYPE_Z");
000727 pragma table_info(t2)
000728 }
000729 } {0 a TYPE_X 0 {} 0 1 b TYPE_Y 0 {} 0 2 c TYPE_Z 0 {} 0}
000730 do_test pragma-6.2.1 {
000731 execsql {
000732 pragma table_info;
000733 }
000734 } {}
000735 db nullvalue <<NULL>>
000736 do_test pragma-6.2.2 {
000737 execsql {
000738 CREATE TABLE t5(
000739 a TEXT DEFAULT CURRENT_TIMESTAMP,
000740 b DEFAULT (5+3),
000741 c TEXT,
000742 d INTEGER DEFAULT NULL,
000743 e TEXT DEFAULT '',
000744 UNIQUE(b,c,d),
000745 PRIMARY KEY(e,b,c)
000746 );
000747 PRAGMA table_info(t5);
000748 }
000749 } {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}
000750 db nullvalue {}
000751 do_test pragma-6.2.3 {
000752 execsql {
000753 CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c);
000754 pragma table_info(t2_3)
000755 }
000756 } {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0}
000757 ifcapable {foreignkey} {
000758 do_test pragma-6.3.1 {
000759 execsql {
000760 CREATE TABLE t3(a int references t2(b), b UNIQUE);
000761 pragma foreign_key_list(t3);
000762 }
000763 } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE}
000764 do_test pragma-6.3.2 {
000765 execsql {
000766 pragma foreign_key_list;
000767 }
000768 } {}
000769 do_test pragma-6.3.3 {
000770 execsql {
000771 pragma foreign_key_list(t3_bogus);
000772 }
000773 } {}
000774 do_test pragma-6.3.4 {
000775 execsql {
000776 pragma foreign_key_list(t5);
000777 }
000778 } {}
000779 do_test pragma-6.4 {
000780 capture_pragma db out {
000781 pragma index_list(t3);
000782 }
000783 db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq}
000784 } {0 sqlite_autoindex_t3_1 1}
000785 }
000786 ifcapable {!foreignkey} {
000787 execsql {CREATE TABLE t3(a,b UNIQUE)}
000788 }
000789 do_test pragma-6.5.1 {
000790 execsql {
000791 CREATE INDEX t3i1 ON t3(a,b);
000792 }
000793 capture_pragma db out {
000794 pragma index_info(t3i1);
000795 }
000796 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
000797 } {0 0 a 1 1 b}
000798
000799 # EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown
000800 # by the index_info pragma, but they are listed by the index_xinfo
000801 # pragma.
000802 #
000803 do_test pragma-6.5.1b {
000804 capture_pragma db out {PRAGMA index_xinfo(t3i1)}
000805 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
000806 } {0 0 a 1 1 b 2 -1 {}}
000807
000808
000809 # EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This
000810 # pragma returns one row for each key column in the named index.
000811 #
000812 # (The first column of output from PRAGMA index_info is...)
000813 # EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0
000814 # means left-most.)
000815 #
000816 # (The second column of output from PRAGMA index_info is...)
000817 # EVIDENCE-OF: R-65019-08383 The rank of the column within the table
000818 # being indexed.
000819 #
000820 # (The third column of output from PRAGMA index_info is...)
000821 # EVIDENCE-OF: R-09773-34266 The name of the column being indexed.
000822 #
000823 do_execsql_test pragma-6.5.1c {
000824 CREATE INDEX t3i2 ON t3(b,a);
000825 PRAGMA index_info='t3i2';
000826 DROP INDEX t3i2;
000827 } {0 1 b 1 0 a}
000828
000829 do_test pragma-6.5.2 {
000830 execsql {
000831 pragma index_info(t3i1_bogus);
000832 }
000833 } {}
000834
000835 ifcapable tempdb {
000836 # Test for ticket #3320. When a temp table of the same name exists, make
000837 # sure the schema of the main table can still be queried using
000838 # "pragma table_info":
000839 do_test pragma-6.6.1 {
000840 execsql {
000841 CREATE TABLE trial(col_main);
000842 CREATE TEMP TABLE trial(col_temp);
000843 }
000844 } {}
000845 do_test pragma-6.6.2 {
000846 execsql {
000847 PRAGMA table_info(trial);
000848 }
000849 } {0 col_temp {} 0 {} 0}
000850 do_test pragma-6.6.3 {
000851 execsql {
000852 PRAGMA temp.table_info(trial);
000853 }
000854 } {0 col_temp {} 0 {} 0}
000855 do_test pragma-6.6.4 {
000856 execsql {
000857 PRAGMA main.table_info(trial);
000858 }
000859 } {0 col_main {} 0 {} 0}
000860 }
000861
000862 do_test pragma-6.7 {
000863 execsql {
000864 CREATE TABLE test_table(
000865 one INT NOT NULL DEFAULT -1,
000866 two text,
000867 three VARCHAR(45, 65) DEFAULT 'abcde',
000868 four REAL DEFAULT X'abcdef',
000869 five DEFAULT CURRENT_TIME
000870 );
000871 }
000872 capture_pragma db out {PRAGMA table_info(test_table)}
000873 db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out
000874 ORDER BY cid}
000875 } [concat \
000876 {0 one INT 1 -1 0} \
000877 {1 two TEXT 0 {} 0} \
000878 {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \
000879 {3 four REAL 0 X'abcdef' 0} \
000880 {4 five {} 0 CURRENT_TIME 0} \
000881 ]
000882 do_test pragma-6.8 {
000883 execsql {
000884 CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c));
000885 PRAGMA table_info(t68);
000886 }
000887 } [concat \
000888 {0 a {} 0 {} 1} \
000889 {1 b {} 0 {} 2} \
000890 {2 c {} 0 {} 4} \
000891 ]
000892 } ;# ifcapable schema_pragmas
000893 # Miscellaneous tests
000894 #
000895 ifcapable schema_pragmas {
000896 # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
000897 # pragma returns one row for each index associated with the given table.
000898 #
000899 do_test pragma-7.1.1 {
000900 # Make sure a pragma knows to read the schema if it needs to
000901 db close
000902 sqlite3 db test.db
000903 capture_pragma db out "PRAGMA index_list(t3)"
000904 db eval {SELECT name, "origin" FROM out ORDER BY name DESC}
000905 } {t3i1 c sqlite_autoindex_t3_1 u}
000906 do_test pragma-7.1.2 {
000907 execsql {
000908 pragma index_list(t3_bogus);
000909 }
000910 } {}
000911 } ;# ifcapable schema_pragmas
000912 ifcapable {utf16} {
000913 if {[permutation] == ""} {
000914 do_test pragma-7.2 {
000915 db close
000916 sqlite3 db test.db
000917 catchsql {
000918 pragma encoding=bogus;
000919 }
000920 } {1 {unsupported encoding: bogus}}
000921 }
000922 }
000923 ifcapable tempdb {
000924 do_test pragma-7.3 {
000925 db close
000926 sqlite3 db test.db
000927 execsql {
000928 pragma lock_status;
000929 }
000930 } {main unlocked temp closed}
000931 } else {
000932 do_test pragma-7.3 {
000933 db close
000934 sqlite3 db test.db
000935 execsql {
000936 pragma lock_status;
000937 }
000938 } {main unlocked}
000939 }
000940
000941
000942 #----------------------------------------------------------------------
000943 # Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
000944 # user_version" statements.
000945 #
000946 # pragma-8.1: PRAGMA schema_version
000947 # pragma-8.2: PRAGMA user_version
000948 #
000949
000950 ifcapable schema_version {
000951
000952 # First check that we can set the schema version and then retrieve the
000953 # same value.
000954 do_test pragma-8.1.1 {
000955 execsql {
000956 PRAGMA schema_version = 105;
000957 }
000958 } {}
000959 do_test pragma-8.1.2 {
000960 execsql2 {
000961 PRAGMA schema_version;
000962 }
000963 } {schema_version 105}
000964 sqlite3_db_config db DEFENSIVE 1
000965 do_execsql_test pragma-8.1.3 {
000966 PRAGMA schema_version = 106;
000967 PRAGMA schema_version;
000968 } 105
000969 sqlite3_db_config db DEFENSIVE 0
000970 do_execsql_test pragma-8.1.4 {
000971 PRAGMA schema_version = 106;
000972 PRAGMA schema_version;
000973 } 106
000974
000975 # Check that creating a table modifies the schema-version (this is really
000976 # to verify that the value being read is in fact the schema version).
000977 do_test pragma-8.1.5 {
000978 execsql {
000979 CREATE TABLE t4(a, b, c);
000980 INSERT INTO t4 VALUES(1, 2, 3);
000981 SELECT * FROM t4;
000982 }
000983 } {1 2 3}
000984 do_test pragma-8.1.6 {
000985 execsql {
000986 PRAGMA schema_version;
000987 }
000988 } 107
000989
000990 # Now open a second connection to the database. Ensure that changing the
000991 # schema-version using the first connection forces the second connection
000992 # to reload the schema. This has to be done using the C-API test functions,
000993 # because the TCL API accounts for SCHEMA_ERROR and retries the query.
000994 do_test pragma-8.1.7 {
000995 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
000996 execsql {
000997 SELECT * FROM t4;
000998 } db2
000999 } {1 2 3}
001000 do_test pragma-8.1.8 {
001001 execsql {
001002 PRAGMA schema_version = 108;
001003 }
001004 } {}
001005 do_test pragma-8.1.9 {
001006 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]
001007 sqlite3_step $::STMT
001008 } SQLITE_ERROR
001009 do_test pragma-8.1.10 {
001010 sqlite3_finalize $::STMT
001011 } SQLITE_SCHEMA
001012
001013 # Make sure the schema-version can be manipulated in an attached database.
001014 forcedelete test2.db
001015 forcedelete test2.db-journal
001016 ifcapable attach {
001017 do_test pragma-8.1.11 {
001018 execsql {
001019 ATTACH 'test2.db' AS aux;
001020 CREATE TABLE aux.t1(a, b, c);
001021 PRAGMA aux.schema_version = 205;
001022 }
001023 } {}
001024 do_test pragma-8.1.12 {
001025 execsql {
001026 PRAGMA aux.schema_version;
001027 }
001028 } 205
001029 }
001030 do_test pragma-8.1.13 {
001031 execsql {
001032 PRAGMA schema_version;
001033 }
001034 } 108
001035
001036 # And check that modifying the schema-version in an attached database
001037 # forces the second connection to reload the schema.
001038 ifcapable attach {
001039 do_test pragma-8.1.14 {
001040 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
001041 execsql {
001042 ATTACH 'test2.db' AS aux;
001043 SELECT * FROM aux.t1;
001044 } db2
001045 } {}
001046 do_test pragma-8.1.15 {
001047 execsql {
001048 PRAGMA aux.schema_version = 206;
001049 }
001050 } {}
001051 do_test pragma-8.1.16 {
001052 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
001053 sqlite3_step $::STMT
001054 } SQLITE_ERROR
001055 do_test pragma-8.1.17 {
001056 sqlite3_finalize $::STMT
001057 } SQLITE_SCHEMA
001058 do_test pragma-8.1.18 {
001059 db2 close
001060 } {}
001061 }
001062
001063 # Now test that the user-version can be read and written (and that we aren't
001064 # accidentally manipulating the schema-version instead).
001065 do_test pragma-8.2.1 {
001066 execsql2 {
001067 PRAGMA user_version;
001068 }
001069 } {user_version 0}
001070 do_test pragma-8.2.2 {
001071 execsql {
001072 PRAGMA user_version = 2;
001073 }
001074 } {}
001075 do_test pragma-8.2.3.1 {
001076 execsql2 {
001077 PRAGMA user_version;
001078 }
001079 } {user_version 2}
001080 do_test pragma-8.2.3.2 {
001081 db close
001082 sqlite3 db test.db
001083 execsql {
001084 PRAGMA user_version;
001085 }
001086 } {2}
001087 do_test pragma-8.2.4.1 {
001088 execsql {
001089 PRAGMA schema_version;
001090 }
001091 } {108}
001092 ifcapable vacuum {
001093 do_test pragma-8.2.4.2 {
001094 execsql {
001095 VACUUM;
001096 PRAGMA user_version;
001097 }
001098 } {2}
001099 do_test pragma-8.2.4.3 {
001100 execsql {
001101 PRAGMA schema_version;
001102 }
001103 } {109}
001104 }
001105
001106 ifcapable attach {
001107 db eval {ATTACH 'test2.db' AS aux}
001108
001109 # Check that the user-version in the auxilary database can be manipulated (
001110 # and that we aren't accidentally manipulating the same in the main db).
001111 do_test pragma-8.2.5 {
001112 execsql {
001113 PRAGMA aux.user_version;
001114 }
001115 } {0}
001116 do_test pragma-8.2.6 {
001117 execsql {
001118 PRAGMA aux.user_version = 3;
001119 }
001120 } {}
001121 do_test pragma-8.2.7 {
001122 execsql {
001123 PRAGMA aux.user_version;
001124 }
001125 } {3}
001126 do_test pragma-8.2.8 {
001127 execsql {
001128 PRAGMA main.user_version;
001129 }
001130 } {2}
001131
001132 # Now check that a ROLLBACK resets the user-version if it has been modified
001133 # within a transaction.
001134 do_test pragma-8.2.9 {
001135 execsql {
001136 BEGIN;
001137 PRAGMA aux.user_version = 10;
001138 PRAGMA user_version = 11;
001139 }
001140 } {}
001141 do_test pragma-8.2.10 {
001142 execsql {
001143 PRAGMA aux.user_version;
001144 }
001145 } {10}
001146 do_test pragma-8.2.11 {
001147 execsql {
001148 PRAGMA main.user_version;
001149 }
001150 } {11}
001151 do_test pragma-8.2.12 {
001152 execsql {
001153 ROLLBACK;
001154 PRAGMA aux.user_version;
001155 }
001156 } {3}
001157 do_test pragma-8.2.13 {
001158 execsql {
001159 PRAGMA main.user_version;
001160 }
001161 } {2}
001162 }
001163
001164 # Try a negative value for the user-version
001165 do_test pragma-8.2.14 {
001166 execsql {
001167 PRAGMA user_version = -450;
001168 }
001169 } {}
001170 do_test pragma-8.2.15 {
001171 execsql {
001172 PRAGMA user_version;
001173 }
001174 } {-450}
001175 } ; # ifcapable schema_version
001176
001177 # Check to see if TEMP_STORE is memory or disk. Return strings
001178 # "memory" or "disk" as appropriate.
001179 #
001180 proc check_temp_store {} {
001181 db eval {
001182 PRAGMA temp.cache_size = 1;
001183 CREATE TEMP TABLE IF NOT EXISTS a(b);
001184 DELETE FROM a;
001185 INSERT INTO a VALUES(randomblob(1000));
001186 INSERT INTO a SELECT * FROM a;
001187 INSERT INTO a SELECT * FROM a;
001188 INSERT INTO a SELECT * FROM a;
001189 INSERT INTO a SELECT * FROM a;
001190 INSERT INTO a SELECT * FROM a;
001191 INSERT INTO a SELECT * FROM a;
001192 INSERT INTO a SELECT * FROM a;
001193 INSERT INTO a SELECT * FROM a;
001194 }
001195 db eval {PRAGMA database_list} {
001196 if {$name=="temp"} {
001197 set bt [btree_from_db db 1]
001198 if {[btree_ismemdb $bt]} {
001199 return "memory"
001200 }
001201 return "disk"
001202 }
001203 }
001204 return "unknown"
001205 }
001206
001207 # Application_ID
001208 #
001209 do_test pragma-8.3.1 {
001210 execsql {
001211 PRAGMA application_id;
001212 }
001213 } {0}
001214 do_test pragma-8.3.2 {
001215 execsql {PRAGMA Application_ID(12345); PRAGMA application_id;}
001216 } {12345}
001217
001218 # Test temp_store and temp_store_directory pragmas
001219 #
001220 ifcapable pager_pragmas {
001221 do_test pragma-9.1 {
001222 db close
001223 sqlite3 db test.db
001224 execsql {
001225 PRAGMA temp_store;
001226 }
001227 } {0}
001228 if {$TEMP_STORE<=1} {
001229 do_test pragma-9.1.1 {
001230 check_temp_store
001231 } {disk}
001232 } else {
001233 do_test pragma-9.1.1 {
001234 check_temp_store
001235 } {memory}
001236 }
001237
001238 do_test pragma-9.2 {
001239 db close
001240 sqlite3 db test.db
001241 execsql {
001242 PRAGMA temp_store=file;
001243 PRAGMA temp_store;
001244 }
001245 } {1}
001246 if {$TEMP_STORE==3} {
001247 # When TEMP_STORE is 3, always use memory regardless of pragma settings.
001248 do_test pragma-9.2.1 {
001249 check_temp_store
001250 } {memory}
001251 } else {
001252 do_test pragma-9.2.1 {
001253 check_temp_store
001254 } {disk}
001255 }
001256
001257 do_test pragma-9.3 {
001258 db close
001259 sqlite3 db test.db
001260 execsql {
001261 PRAGMA temp_store=memory;
001262 PRAGMA temp_store;
001263 }
001264 } {2}
001265 if {$TEMP_STORE==0} {
001266 # When TEMP_STORE is 0, always use the disk regardless of pragma settings.
001267 do_test pragma-9.3.1 {
001268 check_temp_store
001269 } {disk}
001270 } else {
001271 do_test pragma-9.3.1 {
001272 check_temp_store
001273 } {memory}
001274 }
001275
001276 do_test pragma-9.4 {
001277 execsql {
001278 PRAGMA temp_store_directory;
001279 }
001280 } {}
001281 ifcapable wsd {
001282 do_test pragma-9.5 {
001283 set pwd [string map {' ''} [file nativename [get_pwd]]]
001284 execsql "
001285 PRAGMA temp_store_directory='$pwd';
001286 "
001287 } {}
001288 do_test pragma-9.6 {
001289 execsql {
001290 PRAGMA temp_store_directory;
001291 }
001292 } [list [file nativename [get_pwd]]]
001293 do_test pragma-9.7 {
001294 catchsql {
001295 PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
001296 }
001297 } {1 {not a writable directory}}
001298 do_test pragma-9.8 {
001299 execsql {
001300 PRAGMA temp_store_directory='';
001301 }
001302 } {}
001303 if {![info exists TEMP_STORE] || $TEMP_STORE<=1} {
001304 ifcapable tempdb {
001305 do_test pragma-9.9 {
001306 execsql {
001307 PRAGMA temp_store_directory;
001308 PRAGMA temp_store=FILE;
001309 CREATE TEMP TABLE temp_store_directory_test(a integer);
001310 INSERT INTO temp_store_directory_test values (2);
001311 SELECT * FROM temp_store_directory_test;
001312 }
001313 } {2}
001314 do_test pragma-9.10 {
001315 catchsql "
001316 PRAGMA temp_store_directory='$pwd';
001317 SELECT * FROM temp_store_directory_test;
001318 "
001319 } {1 {no such table: temp_store_directory_test}}
001320 }
001321 }
001322 }
001323 do_test pragma-9.11 {
001324 execsql {
001325 PRAGMA temp_store = 0;
001326 PRAGMA temp_store;
001327 }
001328 } {0}
001329 do_test pragma-9.12 {
001330 execsql {
001331 PRAGMA temp_store = 1;
001332 PRAGMA temp_store;
001333 }
001334 } {1}
001335 do_test pragma-9.13 {
001336 execsql {
001337 PRAGMA temp_store = 2;
001338 PRAGMA temp_store;
001339 }
001340 } {2}
001341 do_test pragma-9.14 {
001342 execsql {
001343 PRAGMA temp_store = 3;
001344 PRAGMA temp_store;
001345 }
001346 } {0}
001347 do_test pragma-9.15 {
001348 catchsql {
001349 BEGIN EXCLUSIVE;
001350 CREATE TEMP TABLE temp_table(t);
001351 INSERT INTO temp_table VALUES('valuable data');
001352 PRAGMA temp_store = 1;
001353 }
001354 } {1 {temporary storage cannot be changed from within a transaction}}
001355 do_test pragma-9.16 {
001356 execsql {
001357 SELECT * FROM temp_table;
001358 COMMIT;
001359 }
001360 } {{valuable data}}
001361
001362 do_test pragma-9.17 {
001363 execsql {
001364 INSERT INTO temp_table VALUES('valuable data II');
001365 SELECT * FROM temp_table;
001366 }
001367 } {{valuable data} {valuable data II}}
001368
001369 do_test pragma-9.18 {
001370 set rc [catch {
001371 db eval {SELECT t FROM temp_table} {
001372 execsql {pragma temp_store = 1}
001373 }
001374 } msg]
001375 list $rc $msg
001376 } {1 {temporary storage cannot be changed from within a transaction}}
001377
001378 } ;# ifcapable pager_pragmas
001379
001380 ifcapable trigger {
001381
001382 do_test pragma-10.0 {
001383 catchsql {
001384 DROP TABLE main.t1;
001385 }
001386 execsql {
001387 PRAGMA count_changes = 1;
001388
001389 CREATE TABLE t1(a PRIMARY KEY);
001390 CREATE TABLE t1_mirror(a);
001391 CREATE TABLE t1_mirror2(a);
001392 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN
001393 INSERT INTO t1_mirror VALUES(new.a);
001394 END;
001395 CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
001396 INSERT INTO t1_mirror2 VALUES(new.a);
001397 END;
001398 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN
001399 UPDATE t1_mirror SET a = new.a WHERE a = old.a;
001400 END;
001401 CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN
001402 UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;
001403 END;
001404 CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN
001405 DELETE FROM t1_mirror WHERE a = old.a;
001406 END;
001407 CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
001408 DELETE FROM t1_mirror2 WHERE a = old.a;
001409 END;
001410 }
001411 } {}
001412
001413 do_test pragma-10.1 {
001414 execsql {
001415 INSERT INTO t1 VALUES(randstr(10,10));
001416 }
001417 } {1}
001418 do_test pragma-10.2 {
001419 execsql {
001420 UPDATE t1 SET a = randstr(10,10);
001421 }
001422 } {1}
001423 do_test pragma-10.3 {
001424 execsql {
001425 DELETE FROM t1;
001426 }
001427 } {1}
001428
001429 } ;# ifcapable trigger
001430
001431 ifcapable schema_pragmas {
001432 do_test pragma-11.1 {
001433 execsql2 {
001434 pragma collation_list;
001435 }
001436 } {seq 0 name RTRIM seq 1 name NOCASE seq 2 name BINARY}
001437 do_test pragma-11.2 {
001438 db collate New_Collation blah...
001439 execsql {
001440 pragma collation_list;
001441 }
001442 } {0 New_Collation 1 RTRIM 2 NOCASE 3 BINARY}
001443 }
001444
001445 ifcapable schema_pragmas&&tempdb {
001446 do_test pragma-12.1 {
001447 sqlite3 db2 test.db
001448 execsql {
001449 PRAGMA temp.table_info('abc');
001450 } db2
001451 } {}
001452 db2 close
001453
001454 do_test pragma-12.2 {
001455 sqlite3 db2 test.db
001456 execsql {
001457 PRAGMA temp.default_cache_size = 200;
001458 PRAGMA temp.default_cache_size;
001459 } db2
001460 } {200}
001461 db2 close
001462
001463 do_test pragma-12.3 {
001464 sqlite3 db2 test.db
001465 execsql {
001466 PRAGMA temp.cache_size = 400;
001467 PRAGMA temp.cache_size;
001468 } db2
001469 } {400}
001470 db2 close
001471 }
001472
001473 ifcapable bloblit {
001474
001475 do_test pragma-13.1 {
001476 execsql {
001477 DROP TABLE IF EXISTS t4;
001478 PRAGMA vdbe_trace=on;
001479 PRAGMA vdbe_listing=on;
001480 PRAGMA sql_trace=on;
001481 CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
001482 INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');
001483 INSERT INTO t4(b) VALUES(randstr(30,30));
001484 INSERT INTO t4(b) VALUES(1.23456);
001485 INSERT INTO t4(b) VALUES(NULL);
001486 INSERT INTO t4(b) VALUES(0);
001487 INSERT INTO t4(b) SELECT b||b||b||b FROM t4;
001488 SELECT * FROM t4;
001489 }
001490 execsql {
001491 PRAGMA vdbe_trace=off;
001492 PRAGMA vdbe_listing=off;
001493 PRAGMA sql_trace=off;
001494 }
001495 } {}
001496
001497 } ;# ifcapable bloblit
001498
001499 ifcapable pager_pragmas {
001500 db close
001501 forcedelete test.db
001502 sqlite3 db test.db
001503
001504 # EVIDENCE-OF: R-15672-33611 PRAGMA schema.page_count; Return the total
001505 # number of pages in the database file.
001506 #
001507 do_test pragma-14.1 {
001508 execsql { pragma auto_vacuum = 0 }
001509 execsql { pragma page_count; pragma main.page_count }
001510 } {0 0}
001511
001512 do_test pragma-14.2 {
001513 execsql {
001514 CREATE TABLE abc(a, b, c);
001515 PRAGMA page_count;
001516 PRAGMA main.page_count;
001517 PRAGMA temp.page_count;
001518 }
001519 } {2 2 0}
001520 do_test pragma-14.2uc {
001521 execsql {pragma PAGE_COUNT}
001522 } {2}
001523
001524 do_test pragma-14.3 {
001525 execsql {
001526 BEGIN;
001527 CREATE TABLE def(a, b, c);
001528 PRAGMA page_count;
001529 }
001530 } {3}
001531 do_test pragma-14.3uc {
001532 execsql {pragma PAGE_COUNT}
001533 } {3}
001534
001535 do_test pragma-14.4 {
001536 set page_size [db one {pragma page_size}]
001537 expr [file size test.db] / $page_size
001538 } {2}
001539
001540 do_test pragma-14.5 {
001541 execsql {
001542 ROLLBACK;
001543 PRAGMA page_count;
001544 }
001545 } {2}
001546
001547 do_test pragma-14.6 {
001548 forcedelete test2.db
001549 sqlite3 db2 test2.db
001550 execsql {
001551 PRAGMA auto_vacuum = 0;
001552 CREATE TABLE t1(a, b, c);
001553 CREATE TABLE t2(a, b, c);
001554 CREATE TABLE t3(a, b, c);
001555 CREATE TABLE t4(a, b, c);
001556 } db2
001557 db2 close
001558 execsql {
001559 ATTACH 'test2.db' AS aux;
001560 PRAGMA aux.page_count;
001561 }
001562 } {5}
001563 do_test pragma-14.6uc {
001564 execsql {pragma AUX.PAGE_COUNT}
001565 } {5}
001566 }
001567
001568 # Test that the value set using the cache_size pragma is not reset when the
001569 # schema is reloaded.
001570 #
001571 ifcapable pager_pragmas {
001572 db close
001573 sqlite3 db test.db
001574 do_test pragma-15.1 {
001575 execsql {
001576 PRAGMA cache_size=59;
001577 PRAGMA cache_size;
001578 }
001579 } {59}
001580 do_test pragma-15.2 {
001581 sqlite3 db2 test.db
001582 execsql {
001583 CREATE TABLE newtable(a, b, c);
001584 } db2
001585 db2 close
001586 } {}
001587 do_test pragma-15.3 {
001588 # Evaluating this statement will cause the schema to be reloaded (because
001589 # the schema was changed by another connection in pragma-15.2). At one
001590 # point there was a bug that reset the cache_size to its default value
001591 # when this happened.
001592 execsql { SELECT * FROM sqlite_master }
001593 execsql { PRAGMA cache_size }
001594 } {59}
001595 }
001596
001597 # Reset the sqlite3_temp_directory variable for the next run of tests:
001598 sqlite3 dbX :memory:
001599 dbX eval {PRAGMA temp_store_directory = ""}
001600 dbX close
001601
001602 ifcapable lock_proxy_pragmas&&prefer_proxy_locking {
001603 set sqlite_hostid_num 1
001604
001605 set using_proxy 0
001606 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
001607 set using_proxy $value
001608 }
001609
001610 # Test the lock_proxy_file pragmas.
001611 #
001612 db close
001613 set env(SQLITE_FORCE_PROXY_LOCKING) "0"
001614
001615 sqlite3 db test.db
001616 do_test pragma-16.1 {
001617 execsql {
001618 PRAGMA lock_proxy_file="mylittleproxy";
001619 select * from sqlite_master;
001620 }
001621 execsql {
001622 PRAGMA lock_proxy_file;
001623 }
001624 } {mylittleproxy}
001625
001626 do_test pragma-16.2 {
001627 sqlite3 db2 test.db
001628 execsql {
001629 PRAGMA lock_proxy_file="mylittleproxy";
001630 } db2
001631 } {}
001632
001633 db2 close
001634 do_test pragma-16.2.1 {
001635 sqlite3 db2 test.db
001636 execsql {
001637 PRAGMA lock_proxy_file=":auto:";
001638 select * from sqlite_master;
001639 } db2
001640 execsql {
001641 PRAGMA lock_proxy_file;
001642 } db2
001643 } {mylittleproxy}
001644
001645 db2 close
001646 do_test pragma-16.3 {
001647 sqlite3 db2 test.db
001648 execsql {
001649 PRAGMA lock_proxy_file="myotherproxy";
001650 } db2
001651 catchsql {
001652 select * from sqlite_master;
001653 } db2
001654 } {1 {database is locked}}
001655
001656 do_test pragma-16.4 {
001657 db2 close
001658 db close
001659 sqlite3 db2 test.db
001660 execsql {
001661 PRAGMA lock_proxy_file="myoriginalproxy";
001662 PRAGMA lock_proxy_file="myotherproxy";
001663 PRAGMA lock_proxy_file;
001664 } db2
001665 } {myotherproxy}
001666
001667 db2 close
001668 set env(SQLITE_FORCE_PROXY_LOCKING) "1"
001669 do_test pragma-16.5 {
001670 sqlite3 db2 test.db
001671 execsql {
001672 PRAGMA lock_proxy_file=":auto:";
001673 PRAGMA lock_proxy_file;
001674 } db2
001675 } {myotherproxy}
001676
001677 do_test pragma-16.6 {
001678 db2 close
001679 sqlite3 db2 test2.db
001680 set lockpath [execsql {
001681 PRAGMA lock_proxy_file=":auto:";
001682 PRAGMA lock_proxy_file;
001683 } db2]
001684 string match "*test2.db:auto:" $lockpath
001685 } {1}
001686
001687 set sqlite_hostid_num 2
001688 do_test pragma-16.7 {
001689 list [catch {
001690 sqlite3 db test2.db
001691 execsql {
001692 PRAGMA lock_proxy_file=":auto:";
001693 select * from sqlite_master;
001694 }
001695 } msg] $msg
001696 } {1 {database is locked}}
001697 db close
001698
001699 do_test pragma-16.8 {
001700 list [catch {
001701 sqlite3 db test2.db
001702 execsql { select * from sqlite_master }
001703 } msg] $msg
001704 } {1 {database is locked}}
001705
001706 db2 close
001707 do_test pragma-16.8.1 {
001708 execsql {
001709 PRAGMA lock_proxy_file="yetanotherproxy";
001710 PRAGMA lock_proxy_file;
001711 }
001712 } {yetanotherproxy}
001713 do_test pragma-16.8.2 {
001714 execsql {
001715 create table mine(x);
001716 }
001717 } {}
001718
001719 db close
001720 do_test pragma-16.9 {
001721 sqlite3 db proxytest.db
001722 set lockpath2 [execsql {
001723 PRAGMA lock_proxy_file=":auto:";
001724 PRAGMA lock_proxy_file;
001725 } db]
001726 string match "*proxytest.db:auto:" $lockpath2
001727 } {1}
001728
001729 set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy
001730 set sqlite_hostid_num 0
001731 }
001732
001733 # Parsing of auto_vacuum settings.
001734 #
001735 foreach {autovac_setting val} {
001736 0 0
001737 1 1
001738 2 2
001739 3 0
001740 -1 0
001741 none 0
001742 NONE 0
001743 NoNe 0
001744 full 1
001745 FULL 1
001746 incremental 2
001747 INCREMENTAL 2
001748 -1234 0
001749 1234 0
001750 } {
001751 do_test pragma-17.1.$autovac_setting {
001752 catch {db close}
001753 sqlite3 db :memory:
001754 execsql "
001755 PRAGMA auto_vacuum=$::autovac_setting;
001756 PRAGMA auto_vacuum;
001757 "
001758 } $val
001759 }
001760
001761 # Parsing of temp_store settings.
001762 #
001763 foreach {temp_setting val} {
001764 0 0
001765 1 1
001766 2 2
001767 3 0
001768 -1 0
001769 file 1
001770 FILE 1
001771 fIlE 1
001772 memory 2
001773 MEMORY 2
001774 MeMoRy 2
001775 } {
001776 do_test pragma-18.1.$temp_setting {
001777 catch {db close}
001778 sqlite3 db :memory:
001779 execsql "
001780 PRAGMA temp_store=$::temp_setting;
001781 PRAGMA temp_store=$::temp_setting;
001782 PRAGMA temp_store;
001783 "
001784 } $val
001785 }
001786
001787 # The SQLITE_FCNTL_PRAGMA logic, with error handling.
001788 #
001789 db close
001790 testvfs tvfs
001791 sqlite3 db test.db -vfs tvfs
001792 do_test pragma-19.1 {
001793 catchsql {PRAGMA error}
001794 } {1 {SQL logic error}}
001795 do_test pragma-19.2 {
001796 catchsql {PRAGMA error='This is the error message'}
001797 } {1 {This is the error message}}
001798 do_test pragma-19.3 {
001799 catchsql {PRAGMA error='7 This is the error message'}
001800 } {1 {This is the error message}}
001801 do_test pragma-19.4 {
001802 catchsql {PRAGMA error=7}
001803 } {1 {out of memory}}
001804 do_test pragma-19.5 {
001805 file tail [lindex [execsql {PRAGMA filename}] 0]
001806 } {test.db}
001807
001808 if {$tcl_platform(platform)=="windows"} {
001809 # Test data_store_directory pragma
001810 #
001811 db close
001812 sqlite3 db test.db
001813 file mkdir data_dir
001814 do_test pragma-20.1 {
001815 catchsql {PRAGMA data_store_directory}
001816 } {0 {}}
001817 do_test pragma-20.2 {
001818 set pwd [string map {' ''} [file nativename [get_pwd]]]
001819 catchsql "PRAGMA data_store_directory='$pwd';"
001820 } {0 {}}
001821 do_test pragma-20.3 {
001822 catchsql {PRAGMA data_store_directory}
001823 } [list 0 [list [file nativename [get_pwd]]]]
001824 do_test pragma-20.4 {
001825 set pwd [string map {' ''} [file nativename \
001826 [file join [get_pwd] data_dir]]]
001827 catchsql "PRAGMA data_store_directory='$pwd';"
001828 } {0 {}}
001829 do_test pragma-20.5 {
001830 sqlite3 db2 test2.db
001831 catchsql "PRAGMA database_list;" db2
001832 } [list 0 [list 0 main [file nativename \
001833 [file join [get_pwd] data_dir test2.db]]]]
001834 catch {db2 close}
001835 do_test pragma-20.6 {
001836 sqlite3 db2 [file join [get_pwd] test2.db]
001837 catchsql "PRAGMA database_list;" db2
001838 } [list 0 [list 0 main [file nativename \
001839 [file join [get_pwd] test2.db]]]]
001840 catch {db2 close}
001841 do_test pragma-20.7 {
001842 catchsql "PRAGMA data_store_directory='';"
001843 } {0 {}}
001844 do_test pragma-20.8 {
001845 catchsql {PRAGMA data_store_directory}
001846 } {0 {}}
001847
001848 forcedelete data_dir
001849 } ;# endif windows
001850
001851 database_may_be_corrupt
001852 if {![nonzero_reserved_bytes]} {
001853
001854 do_test 21.1 {
001855 # Create a corrupt database in testerr.db. And a non-corrupt at test.db.
001856 #
001857 db close
001858 forcedelete test.db
001859 sqlite3 db test.db
001860 execsql {
001861 PRAGMA page_size = 1024;
001862 PRAGMA auto_vacuum = 0;
001863 CREATE TABLE t1(a PRIMARY KEY, b);
001864 INSERT INTO t1 VALUES(1, 1);
001865 }
001866 for {set i 0} {$i < 10} {incr i} {
001867 execsql { INSERT INTO t1 SELECT a + (1 << $i), b + (1 << $i) FROM t1 }
001868 }
001869 db close
001870 forcecopy test.db testerr.db
001871 hexio_write testerr.db 15000 [string repeat 55 100]
001872 } {100}
001873
001874 set mainerr {*** in database main ***
001875 Multiple uses for byte 672 of page 15}
001876 set auxerr {*** in database aux ***
001877 Multiple uses for byte 672 of page 15}
001878
001879 set mainerr {/{\*\*\* in database main \*\*\*
001880 Multiple uses for byte 672 of page 15}.*/}
001881 set auxerr {/{\*\*\* in database aux \*\*\*
001882 Multiple uses for byte 672 of page 15}.*/}
001883
001884 do_test 22.2 {
001885 catch { db close }
001886 sqlite3 db testerr.db
001887 execsql { PRAGMA integrity_check }
001888 } $mainerr
001889
001890 do_test 22.3.1 {
001891 catch { db close }
001892 sqlite3 db test.db
001893 execsql {
001894 ATTACH 'testerr.db' AS 'aux';
001895 PRAGMA integrity_check;
001896 }
001897 } $auxerr
001898 do_test 22.3.2 {
001899 execsql { PRAGMA main.integrity_check; }
001900 } {ok}
001901 do_test 22.3.3 {
001902 execsql { PRAGMA aux.integrity_check; }
001903 } $auxerr
001904
001905 do_test 22.4.1 {
001906 catch { db close }
001907 sqlite3 db testerr.db
001908 execsql {
001909 ATTACH 'test.db' AS 'aux';
001910 PRAGMA integrity_check;
001911 }
001912 } $mainerr
001913 do_test 22.4.2 {
001914 execsql { PRAGMA main.integrity_check; }
001915 } $mainerr
001916 do_test 22.4.3 {
001917 execsql { PRAGMA aux.integrity_check; }
001918 } {ok}
001919 }
001920
001921 db close
001922 forcedelete test.db test.db-wal test.db-journal
001923 sqlite3 db test.db
001924 sqlite3 db2 test.db
001925 do_test 23.1 {
001926 db eval {
001927 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
001928 CREATE INDEX i1 ON t1(b,c);
001929 CREATE INDEX i2 ON t1(c,d);
001930 CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC);
001931 CREATE INDEX i3 ON t1(d,b+c,c);
001932 CREATE TABLE t2(x INTEGER REFERENCES t1);
001933 }
001934 db2 eval {SELECT name FROM sqlite_master}
001935 } {t1 i1 i2 i2x i3 t2}
001936 do_test 23.2a {
001937 db eval {
001938 DROP INDEX i2;
001939 CREATE INDEX i2 ON t1(c,d,b);
001940 }
001941 capture_pragma db2 out {PRAGMA index_info(i2)}
001942 db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno}
001943 } {2 c | 3 d | 1 b |}
001944
001945 # EVIDENCE-OF: R-56143-29319 PRAGMA schema.index_xinfo(index-name); This
001946 # pragma returns information about every column in an index.
001947 #
001948 # EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma
001949 # returns information about every column in the index, not just the key
001950 # columns.
001951 #
001952 do_test 23.2b {
001953 capture_pragma db2 out {PRAGMA index_xinfo(i2)}
001954 db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
001955 } {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |}
001956
001957 # (The first column of output from PRAGMA index_xinfo is...)
001958 # EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0
001959 # means left-most. Key columns come before auxiliary columns.)
001960 #
001961 # (The second column of output from PRAGMA index_xinfo is...)
001962 # EVIDENCE-OF: R-06603-49335 The rank of the column within the table
001963 # being indexed, or -1 if the index-column is the rowid of the table
001964 # being indexed and -2 if the index is on an expression.
001965 #
001966 # (The third column of output from PRAGMA index_xinfo is...)
001967 # EVIDENCE-OF: R-40641-22898 The name of the column being indexed, or
001968 # NULL if the index-column is the rowid of the table being indexed or an
001969 # expression.
001970 #
001971 # (The fourth column of output from PRAGMA index_xinfo is...)
001972 # EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse
001973 # (DESC) order by the index and 0 otherwise.
001974 #
001975 # (The fifth column of output from PRAGMA index_xinfo is...)
001976 # EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to
001977 # compare values in the index-column.
001978 #
001979 # (The sixth column of output from PRAGMA index_xinfo is...)
001980 # EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0
001981 # if the index-column is an auxiliary column.
001982 #
001983 do_test 23.2c {
001984 db2 eval {PRAGMA index_xinfo(i2)}
001985 } {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0}
001986 do_test 23.2d {
001987 db2 eval {PRAGMA index_xinfo(i2x)}
001988 } {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0}
001989 do_test 23.2e {
001990 db2 eval {PRAGMA index_xinfo(i3)}
001991 } {0 3 d 0 BINARY 1 1 -2 {} 0 BINARY 1 2 2 c 0 BINARY 1 3 -1 {} 0 BINARY 0}
001992
001993 # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This
001994 # pragma returns one row for each index associated with the given table.
001995 #
001996 # (The first column of output from PRAGMA index_list is...)
001997 # EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index
001998 # for internal tracking purposes.
001999 #
002000 # (The second column of output from PRAGMA index_list is...)
002001 # EVIDENCE-OF: R-35496-03635 The name of the index.
002002 #
002003 # (The third column of output from PRAGMA index_list is...)
002004 # EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not.
002005 #
002006 # (The fourth column of output from PRAGMA index_list is...)
002007 # EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE
002008 # INDEX statement, "u" if the index was created by a UNIQUE constraint,
002009 # or "pk" if the index was created by a PRIMARY KEY constraint.
002010 #
002011 do_test 23.3 {
002012 db eval {
002013 DROP INDEX IF EXISTS i3;
002014 CREATE INDEX i3 ON t1(d,b,c);
002015 }
002016 capture_pragma db2 out {PRAGMA index_list(t1)}
002017 db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq}
002018 } {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |}
002019 ifcapable altertable {
002020 do_test 23.4 {
002021 db eval {
002022 ALTER TABLE t1 ADD COLUMN e;
002023 }
002024 db2 eval {
002025 PRAGMA table_info(t1);
002026 }
002027 } {/4 e {} 0 {} 0/}
002028 }
002029 do_test 23.5 {
002030 db eval {
002031 DROP TABLE t2;
002032 CREATE TABLE t2(x, y INTEGER REFERENCES t1);
002033 }
002034 db2 eval {
002035 PRAGMA foreign_key_list(t2);
002036 }
002037 } {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE}
002038 db2 close
002039
002040 ifcapable !has_codec {
002041 reset_db
002042 do_execsql_test 24.0 {
002043 PRAGMA page_size = 1024;
002044 CREATE TABLE t1(a, b, c);
002045 CREATE INDEX i1 ON t1(b);
002046 INSERT INTO t1 VALUES('a', 'b', 'c');
002047 PRAGMA integrity_check;
002048 } {ok}
002049
002050 set r [db one {SELECT rootpage FROM sqlite_master WHERE name = 't1'}]
002051 db close
002052 hexio_write test.db [expr $r*1024 - 16] 000000000000000701040f0f1f616263
002053
002054 sqlite3 db test.db
002055 do_catchsql_test 24.1 {
002056 SELECT * FROM t1;
002057 } {1 {database disk image is malformed}}
002058 do_catchsql_test 24.2 {
002059 PRAGMA integrity_check;
002060 } {0 {{database disk image is malformed}}}
002061 }
002062 database_never_corrupt
002063
002064 # 2023-03-27. Register allocation issue in integrity_check discovered
002065 # by new assert() statements added in [6f8b97f31a4c8552].
002066 # dbsqlfuzz dc9ab26037cf5ef797d28cd1ae0855ade584216d
002067 # tag-20230327-1
002068 #
002069 reset_db
002070 do_execsql_test 25.0 {
002071 CREATE TABLE t1(a INT, b AS (a*2) NOT NULL);
002072 CREATE TEMP TABLE t2(a PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID;
002073 CREATE UNIQUE INDEX t2x ON t2(c,b);
002074 PRAGMA integrity_check;
002075 } ok
002076 finish_test