AryaWu/sqlite
0
1# 2002 March 62#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.12#13# This file implements tests for the PRAGMA command.14#15# $Id: pragma.test,v 1.73 2009/01/12 14:01:45 danielk1977 Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix pragma20 21# Do not use a codec for tests in this file, as the database file is22# manipulated directly using tcl scripts (using the [hexio_write] command).23#24do_not_use_codec25 26# Test organization:27#28# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.29# pragma-2.*: Test synchronous on attached db.30# pragma-3.*: Test detection of table/index inconsistency by integrity_check.31# pragma-4.*: Test cache_size and default_cache_size on attached db.32# pragma-5.*: Test that pragma synchronous may not be used inside of a33# transaction.34# pragma-6.*: Test schema-query pragmas.35# pragma-7.*: Miscellaneous tests.36# pragma-8.*: Test user_version and schema_version pragmas.37# pragma-9.*: Test temp_store and temp_store_directory.38# pragma-10.*: Test the count_changes pragma in the presence of triggers.39# pragma-11.*: Test the collation_list pragma.40# pragma-14.*: Test the page_count pragma.41# pragma-15.*: Test that the value set using the cache_size pragma is not42# reset when the schema is reloaded.43# pragma-16.*: Test proxy locking44# pragma-20.*: Test data_store_directory.45# pragma-22.*: Test that "PRAGMA [db].integrity_check" respects the "db"46# directive - if it is present.47#48 49ifcapable !pragma {50 finish_test51 return52}53 54# Capture the output of a pragma in a TEMP table.55#56proc capture_pragma {db tabname sql} {57 $db eval "DROP TABLE IF EXISTS temp.$tabname"58 set once 159 $db eval $sql x {60 if {$once} {61 set once 062 set ins "INSERT INTO $tabname VALUES"63 set crtab "CREATE TEMP TABLE $tabname "64 set sep "("65 foreach col $x(*) {66 append ins ${sep}\$x($col)67 append crtab ${sep}\"$col\"68 set sep ,69 }70 append ins )71 append crtab )72 $db eval $crtab73 }74 $db eval $ins75 }76}77 78# Delete the preexisting database to avoid the special setup79# that the "all.test" script does.80#81db close82delete_file test.db test.db-journal83delete_file test3.db test3.db-journal84sqlite3 db test.db; set DB [sqlite3_connection_pointer db]85 86# EVIDENCE-OF: R-13861-56665 PRAGMA schema.cache_size; PRAGMA87# schema.cache_size = pages; PRAGMA schema.cache_size = -kibibytes;88# Query or change the suggested maximum number of database disk pages89# that SQLite will hold in memory at once per open database file.90#91ifcapable pager_pragmas {92set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}]93set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}]94do_test pragma-1.1 {95 execsql {96 PRAGMA cache_size;97 PRAGMA default_cache_size;98 PRAGMA synchronous;99 }100} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]101do_test pragma-1.2 {102 # EVIDENCE-OF: R-42059-47211 If the argument N is positive then the103 # suggested cache size is set to N.104 execsql {105 PRAGMA synchronous=OFF;106 PRAGMA cache_size=1234;107 PRAGMA cache_size;108 PRAGMA default_cache_size;109 PRAGMA synchronous;110 }111} [list 1234 $DFLT_CACHE_SZ 0]112do_test pragma-1.3 {113 db close114 sqlite3 db test.db115 execsql {116 PRAGMA cache_size;117 PRAGMA default_cache_size;118 PRAGMA synchronous;119 }120} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]121do_test pragma-1.4 {122 execsql {123 PRAGMA synchronous=OFF;124 PRAGMA cache_size;125 PRAGMA default_cache_size;126 PRAGMA synchronous;127 }128} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0]129do_test pragma-1.5 {130 execsql {131 PRAGMA cache_size=-4321;132 PRAGMA cache_size;133 PRAGMA default_cache_size;134 PRAGMA synchronous;135 }136} [list -4321 $DFLT_CACHE_SZ 0]137do_test pragma-1.6 {138 execsql {139 PRAGMA synchronous=ON;140 PRAGMA cache_size;141 PRAGMA default_cache_size;142 PRAGMA synchronous;143 }144} [list -4321 $DFLT_CACHE_SZ 1]145do_test pragma-1.7 {146 db close147 sqlite3 db test.db148 execsql {149 PRAGMA cache_size;150 PRAGMA default_cache_size;151 PRAGMA synchronous;152 }153} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]154do_test pragma-1.8 {155 execsql {156 PRAGMA default_cache_size=-123;157 PRAGMA cache_size;158 PRAGMA default_cache_size;159 PRAGMA synchronous;160 }161} {123 123 2}162do_test pragma-1.9.1 {163 db close164 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]165 execsql {166 PRAGMA cache_size;167 PRAGMA default_cache_size;168 PRAGMA synchronous;169 }170} {123 123 2}171ifcapable vacuum {172 do_test pragma-1.9.2 {173 execsql {174 VACUUM;175 PRAGMA cache_size;176 PRAGMA default_cache_size;177 PRAGMA synchronous;178 }179 } {123 123 2}180}181do_test pragma-1.10 {182 execsql {183 PRAGMA synchronous=NORMAL;184 PRAGMA cache_size;185 PRAGMA default_cache_size;186 PRAGMA synchronous;187 }188} {123 123 1}189do_test pragma-1.11.1 {190 execsql {191 PRAGMA synchronous=EXTRA;192 PRAGMA cache_size;193 PRAGMA default_cache_size;194 PRAGMA synchronous;195 }196} {123 123 3}197do_test pragma-1.11.2 {198 execsql {199 PRAGMA synchronous=FULL;200 PRAGMA cache_size;201 PRAGMA default_cache_size;202 PRAGMA synchronous;203 }204} {123 123 2}205do_test pragma-1.12 {206 db close207 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]208 execsql {209 PRAGMA cache_size;210 PRAGMA default_cache_size;211 PRAGMA synchronous;212 }213} {123 123 2}214 215# Make sure the pragma handler understands numeric values in addition216# to keywords like "off" and "full".217#218do_test pragma-1.13 {219 execsql {220 PRAGMA synchronous=0;221 PRAGMA synchronous;222 }223} {0}224do_test pragma-1.14 {225 execsql {226 PRAGMA synchronous=2;227 PRAGMA synchronous;228 }229} {2}230do_test pragma-1.14.1 {231 execsql {232 PRAGMA synchronous=4;233 PRAGMA synchronous;234 }235} {4}236do_test pragma-1.14.2 {237 execsql {238 PRAGMA synchronous=3;239 PRAGMA synchronous;240 }241} {3}242do_test pragma-1.14.3 {243 execsql {244 PRAGMA synchronous=8;245 PRAGMA synchronous;246 }247} {0}248do_test pragma-1.14.4 {249 execsql {250 PRAGMA synchronous=10;251 PRAGMA synchronous;252 }253} {2}254 255do_execsql_test 1.15.1 {256 PRAGMA default_cache_size = 0;257}258do_execsql_test 1.15.2 {259 PRAGMA default_cache_size;260} $DFLT_CACHE_SZ261do_execsql_test 1.15.3 {262 PRAGMA default_cache_size = -500;263}264do_execsql_test 1.15.4 {265 PRAGMA default_cache_size;266} 500267do_execsql_test 1.15.3 {268 PRAGMA default_cache_size = 500;269}270do_execsql_test 1.15.4 {271 PRAGMA default_cache_size;272} 500273db close274hexio_write test.db 48 FFFFFF00275sqlite3 db test.db276do_execsql_test 1.15.4 {277 PRAGMA default_cache_size;278} 256279} ;# ifcapable pager_pragmas280 281# Test turning "flag" pragmas on and off.282#283ifcapable debug {284 # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG285 #286 do_test pragma-1.15 {287 execsql {288 PRAGMA vdbe_listing=YES;289 PRAGMA vdbe_listing;290 }291 } {1}292 do_test pragma-1.16 {293 execsql {294 PRAGMA vdbe_listing=NO;295 PRAGMA vdbe_listing;296 }297 } {0}298}299 300do_test pragma-1.17 {301 execsql {302 PRAGMA parser_trace=ON;303 PRAGMA parser_trace=OFF;304 }305} {}306do_test pragma-1.18 {307 execsql {308 PRAGMA bogus = -1234; -- Parsing of negative values309 }310} {}311 312# Test modifying the safety_level of an attached database.313ifcapable pager_pragmas&&attach {314 do_test pragma-2.1 {315 forcedelete test2.db316 forcedelete test2.db-journal317 execsql {318 ATTACH 'test2.db' AS aux;319 } 320 } {}321 do_test pragma-2.2 {322 execsql {323 pragma aux.synchronous;324 } 325 } {2}326 do_test pragma-2.3 {327 execsql {328 pragma aux.synchronous = OFF;329 pragma aux.synchronous;330 pragma synchronous;331 } 332 } {0 2}333 do_test pragma-2.4 {334 execsql {335 pragma aux.synchronous = ON;336 pragma synchronous;337 pragma aux.synchronous;338 } 339 } {2 1}340} ;# ifcapable pager_pragmas341 342# Construct a corrupted index and make sure the integrity_check343# pragma finds it.344#345# These tests won't work if the database is encrypted346#347do_test pragma-3.1 {348 db close349 forcedelete test.db test.db-journal350 sqlite3 db test.db351 execsql {352 PRAGMA auto_vacuum=OFF;353 BEGIN;354 CREATE TABLE t2(a,b,c);355 CREATE INDEX i2 ON t2(a);356 INSERT INTO t2 VALUES(11,2,3);357 INSERT INTO t2 VALUES(22,3,4);358 COMMIT;359 SELECT rowid, * from t2;360 }361} {1 11 2 3 2 22 3 4}362ifcapable attach {363 if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} {364 do_test pragma-3.2 {365 db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break366 set pgsz [db eval {PRAGMA page_size}]367 # overwrite the header on the rootpage of the index in order to368 # make the index appear to be empty.369 #370 set offset [expr {$pgsz*($rootpage-1)}]371 hexio_write test.db $offset 0a00000000040000000000372 db close373 sqlite3 db test.db374 execsql {PRAGMA integrity_check}375 } {{wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2}}376 do_test pragma-3.3 {377 execsql {PRAGMA integrity_check=1}378 } {{wrong # of entries in index i2}}379 do_test pragma-3.4 {380 execsql {381 ATTACH DATABASE 'test.db' AS t2;382 PRAGMA integrity_check383 }384 } {{wrong # of entries in index i2} {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}}385 do_test pragma-3.5 {386 execsql {387 PRAGMA integrity_check=4388 }389 } {{wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}390 do_catchsql_test pragma-3.5.2 {391 PRAGMA integrity_check='4'392 } {1 {no such table: 4}}393 do_catchsql_test pragma-3.6 {394 PRAGMA integrity_check=xyz395 } {1 {no such table: xyz}}396 do_catchsql_test pragma-3.6b {397 PRAGMA integrity_check=t2398 } {0 {{wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2}}}399 do_catchsql_test pragma-3.6c {400 PRAGMA integrity_check=sqlite_schema401 } {0 ok}402 do_test pragma-3.7 {403 execsql {404 PRAGMA integrity_check=0405 }406 } {{wrong # of entries in index i2} {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}}407 408 # Add additional corruption by appending unused pages to the end of409 # the database file testerr.db410 #411 do_test pragma-3.8 {412 execsql {DETACH t2}413 forcedelete testerr.db testerr.db-journal414 set out [open testerr.db w]415 fconfigure $out -translation binary416 set in [open test.db r]417 fconfigure $in -translation binary418 puts -nonewline $out [read $in]419 seek $in 0420 puts -nonewline $out [read $in]421 close $in422 close $out423 hexio_write testerr.db 28 00000000424 execsql {REINDEX t2}425 execsql {PRAGMA integrity_check}426 } {ok}427 do_test pragma-3.8.1 {428 execsql {PRAGMA quick_check}429 } {ok}430 do_test pragma-3.8.2 {431 execsql {PRAGMA QUICK_CHECK}432 } {ok}433 do_test pragma-3.9a {434 execsql {435 ATTACH 'testerr.db' AS t2;436 PRAGMA integrity_check437 }438 } {{*** in database t2 ***439Page 4: never used440Page 5: never used441Page 6: never used} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2}}442 do_execsql_test pragma-3.9b {443 PRAGMA t2.integrity_check=t2;444 } {{wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2}}445 do_execsql_test pragma-3.9c {446 PRAGMA t2.integrity_check=sqlite_schema;447 } {ok}448 do_test pragma-3.10 {449 execsql {450 PRAGMA integrity_check=1451 }452 } {{*** in database t2 ***453Page 4: never used}}454 do_test pragma-3.11 {455 execsql {456 PRAGMA integrity_check=5457 }458 } {{*** in database t2 ***459Page 4: never used460Page 5: never used461Page 6: never used} {wrong # of entries in index i2} {row 1 missing from index i2}}462 do_test pragma-3.12 {463 execsql {464 PRAGMA integrity_check=4465 }466 } {{*** in database t2 ***467Page 4: never used468Page 5: never used469Page 6: never used} {wrong # of entries in index i2}}470 do_test pragma-3.13 {471 execsql {472 PRAGMA integrity_check=3473 }474 } {{*** in database t2 ***475Page 4: never used476Page 5: never used477Page 6: never used}}478 do_test pragma-3.14 {479 execsql {480 PRAGMA integrity_check(2)481 }482 } {{*** in database t2 ***483Page 4: never used484Page 5: never used}}485 do_test pragma-3.15 {486 execsql {487 ATTACH 'testerr.db' AS t3;488 PRAGMA integrity_check489 }490 } {{*** in database t2 ***491Page 4: never used492Page 5: never used493Page 6: never used} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {*** in database t3 ***494Page 4: never used495Page 5: never used496Page 6: never used} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2}}497 do_test pragma-3.16 {498 execsql {499 PRAGMA integrity_check(10)500 }501 } {{*** in database t2 ***502Page 4: never used503Page 5: never used504Page 6: never used} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {*** in database t3 ***505Page 4: never used506Page 5: never used507Page 6: never used} {wrong # of entries in index i2}}508 do_test pragma-3.17 {509 execsql {510 PRAGMA integrity_check=8511 }512 } {{*** in database t2 ***513Page 4: never used514Page 5: never used515Page 6: never used} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {*** in database t3 ***516Page 4: never used517Page 5: never used}}518 do_test pragma-3.18 {519 execsql {520 PRAGMA integrity_check=4521 }522 } {{*** in database t2 ***523Page 4: never used524Page 5: never used525Page 6: never used} {wrong # of entries in index i2}}526 }527 do_test pragma-3.19 {528 catch {db close}529 forcedelete test.db test.db-journal530 sqlite3 db test.db531 db eval {PRAGMA integrity_check}532 } {ok}533}534 535# Verify that PRAGMA integrity_check catches UNIQUE and NOT NULL536# constraint violations.537#538ifcapable altertable {539 sqlite3_db_config db DEFENSIVE 0540 do_execsql_test pragma-3.20 {541 CREATE TABLE t1(a,b);542 CREATE INDEX t1a ON t1(a);543 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(2,4),(NULL,5),(NULL,6);544 PRAGMA writable_schema=ON;545 UPDATE sqlite_master SET sql='CREATE UNIQUE INDEX t1a ON t1(a)'546 WHERE name='t1a';547 UPDATE sqlite_master SET sql='CREATE TABLE t1(a NOT NULL,b)'548 WHERE name='t1';549 PRAGMA writable_schema=OFF;550 ALTER TABLE t1 RENAME TO t1x;551 PRAGMA integrity_check;552 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a} {NULL value in t1x.a}}553 do_execsql_test pragma-3.21 {554 PRAGMA integrity_check(3);555 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a}}556 do_execsql_test pragma-3.22 {557 PRAGMA integrity_check(2);558 } {{non-unique entry in index t1a} {NULL value in t1x.a}}559 do_execsql_test pragma-3.23 {560 PRAGMA integrity_check(1);561 } {{non-unique entry in index t1a}}562 563 # forum post https://sqlite.org/forum/forumpost/ee4f6fa5ab564 do_execsql_test pragma-3.24 {565 DROP TABLE IF EXISTS t1;566 CREATE TABLE t1(a);567 INSERT INTO t1 VALUES (1);568 ALTER TABLE t1 ADD COLUMN b NOT NULL DEFAULT 0.25;569 SELECT * FROM t1;570 PRAGMA integrity_check(t1);571 } {1 0.25 ok}572 do_execsql_test pragma-3.25 {573 ALTER TABLE t1 ADD COLUMN c CHECK (1);574 SELECT * FROM t1;575 PRAGMA integrity_check(t1);576 } {1 0.25 {} ok}577}578 579# PRAGMA integrity check (or more specifically the sqlite3BtreeCount()580# interface) used to leave index cursors in an inconsistent state581# which could result in an assertion fault in sqlite3BtreeKey()582# called from saveCursorPosition() if content is removed from the583# index while the integrity_check is still running. This test verifies584# that problem has been fixed.585#586do_test pragma-3.30 {587 catch { db close }588 delete_file test.db589 sqlite3 db test.db590 db eval {591 CREATE TABLE t1(a,b,c);592 WITH RECURSIVE593 c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<100)594 INSERT INTO t1(a,b,c) SELECT i, printf('xyz%08x',i), 2000-i FROM c;595 CREATE INDEX t1a ON t1(a);596 CREATE INDEX t1bc ON t1(b,c);597 }598 db eval {PRAGMA integrity_check} {599 db eval {DELETE FROM t1}600 }601} {}602 603# The values stored in indexes must be byte-for-byte identical to the604# values stored in tables.605#606reset_db607do_execsql_test pragma-3.40 {608 CREATE TABLE t1(609 a INTEGER PRIMARY KEY,610 b TEXT COLLATE nocase,611 c INT COLLATE nocase,612 d TEXT613 );614 INSERT INTO t1(a,b,c,d) VALUES615 (1, 'one','one','one'),616 (2, 'two','two','two'),617 (3, 'three','three','three'),618 (4, 'four','four','four'),619 (5, 'five','five','five');620 CREATE INDEX t1bcd ON t1(b,c,d);621 CREATE TABLE t2(622 a INTEGER PRIMARY KEY,623 b TEXT COLLATE nocase,624 c INT COLLATE nocase,625 d TEXT626 );627 INSERT INTO t2(a,b,c,d) VALUES628 (1, 'one','one','one'),629 (2, 'two','two','TWO'),630 (3, 'three','THREE','three'),631 (4, 'FOUR','four','four'),632 (5, 'FIVE','FIVE','five');633 CREATE INDEX t2bcd ON t2(b,c,d);634 CREATE TEMP TABLE saved_schema AS SELECT name, rootpage FROM sqlite_schema;635 PRAGMA writable_schema=ON;636 UPDATE sqlite_schema637 SET rootpage=(SELECT rootpage FROM saved_schema WHERE name='t2bcd')638 WHERE name='t1bcd';639 UPDATE sqlite_schema640 SET rootpage=(SELECT rootpage FROM saved_schema WHERE name='t1bcd')641 WHERE name='t2bcd';642 PRAGMA Writable_schema=RESET;643}644ifcapable vtab {645 do_execsql_test pragma-3.41 {646 SELECT integrity_check AS x FROM pragma_integrity_check ORDER BY 1;647 } {648 {row 2 missing from index t1bcd}649 {row 2 missing from index t2bcd}650 {row 3 values differ from index t1bcd}651 {row 3 values differ from index t2bcd}652 {row 4 values differ from index t1bcd}653 {row 4 values differ from index t2bcd}654 {row 5 values differ from index t1bcd}655 {row 5 values differ from index t2bcd}656 }657}658db eval {DROP TABLE t2}659 660# Test modifying the cache_size of an attached database.661ifcapable pager_pragmas&&attach {662do_test pragma-4.1 {663 execsql {664 ATTACH 'test2.db' AS aux;665 pragma aux.cache_size;666 pragma aux.default_cache_size;667 } 668} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]669do_test pragma-4.2 {670 execsql {671 pragma aux.cache_size = 50;672 pragma aux.cache_size;673 pragma aux.default_cache_size;674 } 675} [list 50 $DFLT_CACHE_SZ]676do_test pragma-4.3 {677 execsql {678 pragma aux.default_cache_size = 456;679 pragma aux.cache_size;680 pragma aux.default_cache_size;681 } 682} {456 456}683do_test pragma-4.4 {684 execsql {685 pragma cache_size;686 pragma default_cache_size;687 } 688} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]689do_test pragma-4.5 {690 execsql {691 DETACH aux;692 ATTACH 'test3.db' AS aux;693 pragma aux.cache_size;694 pragma aux.default_cache_size;695 } 696} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]697do_test pragma-4.6 {698 execsql {699 DETACH aux;700 ATTACH 'test2.db' AS aux;701 pragma aux.cache_size;702 pragma aux.default_cache_size;703 } 704} {456 456}705} ;# ifcapable pager_pragmas706 707# Test that modifying the sync-level in the middle of a transaction is708# disallowed.709ifcapable pager_pragmas {710do_test pragma-5.0 {711 execsql {712 pragma synchronous;713 } 714} {2}715do_test pragma-5.1 {716 catchsql {717 BEGIN;718 pragma synchronous = OFF;719 } 720} {1 {Safety level may not be changed inside a transaction}}721do_test pragma-5.2 {722 execsql {723 pragma synchronous;724 } 725} {2}726catchsql {COMMIT;}727} ;# ifcapable pager_pragmas728 729# Test schema-query pragmas730#731ifcapable schema_pragmas {732ifcapable tempdb&&attach {733 do_test pragma-6.1 {734 set res {}735 execsql {SELECT * FROM sqlite_temp_master}736 foreach {idx name file} [execsql {pragma database_list}] {737 lappend res $idx $name738 }739 set res740 } {0 main 1 temp 2 aux}741}742do_test pragma-6.2 {743 execsql {744 CREATE TABLE t2(a TYPE_X, b [TYPE_Y], c "TYPE_Z");745 pragma table_info(t2)746 }747} {0 a TYPE_X 0 {} 0 1 b TYPE_Y 0 {} 0 2 c TYPE_Z 0 {} 0}748do_test pragma-6.2.1 {749 execsql {750 pragma table_info;751 }752} {}753db nullvalue <<NULL>>754do_test pragma-6.2.2 {755 execsql {756 CREATE TABLE t5(757 a TEXT DEFAULT CURRENT_TIMESTAMP, 758 b DEFAULT (5+3),759 c TEXT,760 d INTEGER DEFAULT NULL,761 e TEXT DEFAULT '',762 UNIQUE(b,c,d),763 PRIMARY KEY(e,b,c)764 );765 PRAGMA table_info(t5);766 }767} {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}768db nullvalue {}769do_test pragma-6.2.3 {770 execsql {771 CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c);772 pragma table_info(t2_3)773 }774} {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0}775ifcapable {foreignkey} {776 do_test pragma-6.3.1 {777 execsql {778 CREATE TABLE t3(a int references t2(b), b UNIQUE);779 pragma foreign_key_list(t3);780 }781 } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE}782 do_test pragma-6.3.2 {783 execsql {784 pragma foreign_key_list;785 }786 } {}787 do_test pragma-6.3.3 {788 execsql {789 pragma foreign_key_list(t3_bogus);790 }791 } {}792 do_test pragma-6.3.4 {793 execsql {794 pragma foreign_key_list(t5);795 }796 } {}797 do_test pragma-6.4 {798 capture_pragma db out {799 pragma index_list(t3);800 }801 db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq}802 } {0 sqlite_autoindex_t3_1 1}803}804ifcapable {!foreignkey} {805 execsql {CREATE TABLE t3(a,b UNIQUE)}806}807do_test pragma-6.5.1 {808 execsql {809 CREATE INDEX t3i1 ON t3(a,b);810 }811 capture_pragma db out {812 pragma index_info(t3i1);813 }814 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}815} {0 0 a 1 1 b}816 817# EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown818# by the index_info pragma, but they are listed by the index_xinfo819# pragma.820#821do_test pragma-6.5.1b {822 capture_pragma db out {PRAGMA index_xinfo(t3i1)}823 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}824} {0 0 a 1 1 b 2 -1 {}}825 826 827# EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This828# pragma returns one row for each key column in the named index.829#830# (The first column of output from PRAGMA index_info is...)831# EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0832# means left-most.)833#834# (The second column of output from PRAGMA index_info is...)835# EVIDENCE-OF: R-65019-08383 The rank of the column within the table836# being indexed.837#838# (The third column of output from PRAGMA index_info is...)839# EVIDENCE-OF: R-09773-34266 The name of the column being indexed.840#841do_execsql_test pragma-6.5.1c {842 CREATE INDEX t3i2 ON t3(b,a);843 PRAGMA index_info='t3i2';844 DROP INDEX t3i2;845} {0 1 b 1 0 a}846 847do_test pragma-6.5.2 {848 execsql {849 pragma index_info(t3i1_bogus);850 }851} {}852 853ifcapable tempdb {854 # Test for ticket #3320. When a temp table of the same name exists, make855 # sure the schema of the main table can still be queried using 856 # "pragma table_info":857 do_test pragma-6.6.1 {858 execsql {859 CREATE TABLE trial(col_main);860 CREATE TEMP TABLE trial(col_temp);861 }862 } {}863 do_test pragma-6.6.2 {864 execsql {865 PRAGMA table_info(trial);866 }867 } {0 col_temp {} 0 {} 0}868 do_test pragma-6.6.3 {869 execsql {870 PRAGMA temp.table_info(trial);871 }872 } {0 col_temp {} 0 {} 0}873 do_test pragma-6.6.4 {874 execsql {875 PRAGMA main.table_info(trial);876 }877 } {0 col_main {} 0 {} 0}878}879 880do_test pragma-6.7 {881 execsql {882 CREATE TABLE test_table(883 one INT NOT NULL DEFAULT -1, 884 two text,885 three VARCHAR(45, 65) DEFAULT 'abcde',886 four REAL DEFAULT X'abcdef',887 five DEFAULT CURRENT_TIME888 );889 }890 capture_pragma db out {PRAGMA table_info(test_table)}891 db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out892 ORDER BY cid}893} [concat \894 {0 one INT 1 -1 0} \895 {1 two TEXT 0 {} 0} \896 {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \897 {3 four REAL 0 X'abcdef' 0} \898 {4 five {} 0 CURRENT_TIME 0} \899]900do_test pragma-6.8 {901 execsql {902 CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c));903 PRAGMA table_info(t68);904 }905} [concat \906 {0 a {} 0 {} 1} \907 {1 b {} 0 {} 2} \908 {2 c {} 0 {} 4} \909]910} ;# ifcapable schema_pragmas911# Miscellaneous tests912#913ifcapable schema_pragmas {914# EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This915# pragma returns one row for each index associated with the given table.916#917do_test pragma-7.1.1 {918 # Make sure a pragma knows to read the schema if it needs to919 db close920 sqlite3 db test.db921 capture_pragma db out "PRAGMA index_list(t3)"922 db eval {SELECT name, "origin" FROM out ORDER BY name DESC}923} {t3i1 c sqlite_autoindex_t3_1 u}924do_test pragma-7.1.2 {925 execsql {926 pragma index_list(t3_bogus);927 }928} {}929} ;# ifcapable schema_pragmas930ifcapable {utf16} {931 if {[permutation] == ""} {932 do_test pragma-7.2 {933 db close934 sqlite3 db test.db935 catchsql {936 pragma encoding=bogus;937 }938 } {1 {unsupported encoding: bogus}}939 }940}941ifcapable tempdb {942 do_test pragma-7.3 {943 db close944 sqlite3 db test.db945 execsql {946 pragma lock_status;947 }948 } {main unlocked temp closed}949} else {950 do_test pragma-7.3 {951 db close952 sqlite3 db test.db953 execsql {954 pragma lock_status;955 }956 } {main unlocked}957}958 959 960#----------------------------------------------------------------------961# Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA962# user_version" statements.963#964# pragma-8.1: PRAGMA schema_version965# pragma-8.2: PRAGMA user_version966#967 968ifcapable schema_version {969 970# First check that we can set the schema version and then retrieve the971# same value.972do_test pragma-8.1.1 {973 execsql {974 PRAGMA schema_version = 105;975 }976} {}977do_test pragma-8.1.2 {978 execsql2 {979 PRAGMA schema_version;980 }981} {schema_version 105}982sqlite3_db_config db DEFENSIVE 1983do_execsql_test pragma-8.1.3 {984 PRAGMA schema_version = 106;985 PRAGMA schema_version;986} 105987sqlite3_db_config db DEFENSIVE 0988do_execsql_test pragma-8.1.4 {989 PRAGMA schema_version = 106;990 PRAGMA schema_version;991} 106992 993# Check that creating a table modifies the schema-version (this is really994# to verify that the value being read is in fact the schema version).995do_test pragma-8.1.5 {996 execsql {997 CREATE TABLE t4(a, b, c);998 INSERT INTO t4 VALUES(1, 2, 3);999 SELECT * FROM t4;1000 }1001} {1 2 3}1002do_test pragma-8.1.6 {1003 execsql {1004 PRAGMA schema_version;1005 }1006} 1071007 1008# Now open a second connection to the database. Ensure that changing the1009# schema-version using the first connection forces the second connection1010# to reload the schema. This has to be done using the C-API test functions,1011# because the TCL API accounts for SCHEMA_ERROR and retries the query.1012do_test pragma-8.1.7 {1013 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]1014 execsql {1015 SELECT * FROM t4;1016 } db21017} {1 2 3}1018do_test pragma-8.1.8 {1019 execsql {1020 PRAGMA schema_version = 108;1021 }1022} {}1023do_test pragma-8.1.9 {1024 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]1025 sqlite3_step $::STMT1026} SQLITE_ERROR1027do_test pragma-8.1.10 {1028 sqlite3_finalize $::STMT1029} SQLITE_SCHEMA1030 1031# Make sure the schema-version can be manipulated in an attached database.1032forcedelete test2.db1033forcedelete test2.db-journal1034ifcapable attach {1035 do_test pragma-8.1.11 {1036 execsql {1037 ATTACH 'test2.db' AS aux;1038 CREATE TABLE aux.t1(a, b, c);1039 PRAGMA aux.schema_version = 205;1040 }1041 } {}1042 do_test pragma-8.1.12 {1043 execsql {1044 PRAGMA aux.schema_version;1045 }1046 } 2051047}1048do_test pragma-8.1.13 {1049 execsql {1050 PRAGMA schema_version;1051 }1052} 1081053 1054# And check that modifying the schema-version in an attached database1055# forces the second connection to reload the schema.1056ifcapable attach {1057 do_test pragma-8.1.14 {1058 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]1059 execsql {1060 ATTACH 'test2.db' AS aux;1061 SELECT * FROM aux.t1;1062 } db21063 } {}1064 do_test pragma-8.1.15 {1065 execsql {1066 PRAGMA aux.schema_version = 206;1067 }1068 } {}1069 do_test pragma-8.1.16 {1070 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]1071 sqlite3_step $::STMT1072 } SQLITE_ERROR1073 do_test pragma-8.1.17 {1074 sqlite3_finalize $::STMT1075 } SQLITE_SCHEMA1076 do_test pragma-8.1.18 {1077 db2 close1078 } {}1079}1080 1081# Now test that the user-version can be read and written (and that we aren't1082# accidentally manipulating the schema-version instead).1083do_test pragma-8.2.1 {1084 execsql2 {1085 PRAGMA user_version;1086 }1087} {user_version 0}1088do_test pragma-8.2.2 {1089 execsql {1090 PRAGMA user_version = 2;1091 }1092} {}1093do_test pragma-8.2.3.1 {1094 execsql2 {1095 PRAGMA user_version;1096 }1097} {user_version 2}1098do_test pragma-8.2.3.2 {1099 db close1100 sqlite3 db test.db1101 execsql {1102 PRAGMA user_version;1103 }1104} {2}1105do_test pragma-8.2.4.1 {1106 execsql {1107 PRAGMA schema_version;1108 }1109} {108}1110ifcapable vacuum {1111 do_test pragma-8.2.4.2 {1112 execsql {1113 VACUUM;1114 PRAGMA user_version;1115 }1116 } {2}1117 do_test pragma-8.2.4.3 {1118 execsql {1119 PRAGMA schema_version;1120 }1121 } {109}1122}1123 1124ifcapable attach {1125 db eval {ATTACH 'test2.db' AS aux}1126 1127 # Check that the user-version in the auxilary database can be manipulated (1128 # and that we aren't accidentally manipulating the same in the main db).1129 do_test pragma-8.2.5 {1130 execsql {1131 PRAGMA aux.user_version;1132 }1133 } {0}1134 do_test pragma-8.2.6 {1135 execsql {1136 PRAGMA aux.user_version = 3;1137 }1138 } {}1139 do_test pragma-8.2.7 {1140 execsql {1141 PRAGMA aux.user_version;1142 }1143 } {3}1144 do_test pragma-8.2.8 {1145 execsql {1146 PRAGMA main.user_version;1147 }1148 } {2}1149 1150 # Now check that a ROLLBACK resets the user-version if it has been modified1151 # within a transaction.1152 do_test pragma-8.2.9 {1153 execsql {1154 BEGIN;1155 PRAGMA aux.user_version = 10;1156 PRAGMA user_version = 11;1157 }1158 } {}1159 do_test pragma-8.2.10 {1160 execsql {1161 PRAGMA aux.user_version;1162 }1163 } {10}1164 do_test pragma-8.2.11 {1165 execsql {1166 PRAGMA main.user_version;1167 }1168 } {11}1169 do_test pragma-8.2.12 {1170 execsql {1171 ROLLBACK;1172 PRAGMA aux.user_version;1173 }1174 } {3}1175 do_test pragma-8.2.13 {1176 execsql {1177 PRAGMA main.user_version;1178 }1179 } {2}1180}1181 1182# Try a negative value for the user-version1183do_test pragma-8.2.14 {1184 execsql {1185 PRAGMA user_version = -450;1186 }1187} {}1188do_test pragma-8.2.15 {1189 execsql {1190 PRAGMA user_version;1191 }1192} {-450}1193} ; # ifcapable schema_version1194 1195# Check to see if TEMP_STORE is memory or disk. Return strings1196# "memory" or "disk" as appropriate.1197#1198proc check_temp_store {} {1199 db eval {1200 PRAGMA temp.cache_size = 1;