1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/pragma.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1235 @@
1.4 +# 2002 March 6
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file implements tests for the PRAGMA command.
1.17 +#
1.18 +# $Id: pragma.test,v 1.66 2008/09/02 00:52:52 drh Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Test organization:
1.24 +#
1.25 +# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.
1.26 +# pragma-2.*: Test synchronous on attached db.
1.27 +# pragma-3.*: Test detection of table/index inconsistency by integrity_check.
1.28 +# pragma-4.*: Test cache_size and default_cache_size on attached db.
1.29 +# pragma-5.*: Test that pragma synchronous may not be used inside of a
1.30 +# transaction.
1.31 +# pragma-6.*: Test schema-query pragmas.
1.32 +# pragma-7.*: Miscellaneous tests.
1.33 +# pragma-8.*: Test user_version and schema_version pragmas.
1.34 +# pragma-9.*: Test temp_store and temp_store_directory.
1.35 +# pragma-10.*: Test the count_changes pragma in the presence of triggers.
1.36 +# pragma-11.*: Test the collation_list pragma.
1.37 +# pragma-14.*: Test the page_count pragma.
1.38 +# pragma-15.*: Test that the value set using the cache_size pragma is not
1.39 +# reset when the schema is reloaded.
1.40 +#
1.41 +
1.42 +ifcapable !pragma {
1.43 + finish_test
1.44 + return
1.45 +}
1.46 +
1.47 +# Delete the preexisting database to avoid the special setup
1.48 +# that the "all.test" script does.
1.49 +#
1.50 +db close
1.51 +file delete test.db test.db-journal
1.52 +file delete test3.db test3.db-journal
1.53 +sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
1.54 +
1.55 +
1.56 +ifcapable pager_pragmas {
1.57 +set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}]
1.58 +set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}]
1.59 +do_test pragma-1.1 {
1.60 + execsql {
1.61 + PRAGMA cache_size;
1.62 + PRAGMA default_cache_size;
1.63 + PRAGMA synchronous;
1.64 + }
1.65 +} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
1.66 +do_test pragma-1.2 {
1.67 + execsql {
1.68 + PRAGMA synchronous=OFF;
1.69 + PRAGMA cache_size=1234;
1.70 + PRAGMA cache_size;
1.71 + PRAGMA default_cache_size;
1.72 + PRAGMA synchronous;
1.73 + }
1.74 +} [list 1234 $DFLT_CACHE_SZ 0]
1.75 +do_test pragma-1.3 {
1.76 + db close
1.77 + sqlite3 db test.db
1.78 + execsql {
1.79 + PRAGMA cache_size;
1.80 + PRAGMA default_cache_size;
1.81 + PRAGMA synchronous;
1.82 + }
1.83 +} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
1.84 +do_test pragma-1.4 {
1.85 + execsql {
1.86 + PRAGMA synchronous=OFF;
1.87 + PRAGMA cache_size;
1.88 + PRAGMA default_cache_size;
1.89 + PRAGMA synchronous;
1.90 + }
1.91 +} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0]
1.92 +do_test pragma-1.5 {
1.93 + execsql {
1.94 + PRAGMA cache_size=4321;
1.95 + PRAGMA cache_size;
1.96 + PRAGMA default_cache_size;
1.97 + PRAGMA synchronous;
1.98 + }
1.99 +} [list 4321 $DFLT_CACHE_SZ 0]
1.100 +do_test pragma-1.6 {
1.101 + execsql {
1.102 + PRAGMA synchronous=ON;
1.103 + PRAGMA cache_size;
1.104 + PRAGMA default_cache_size;
1.105 + PRAGMA synchronous;
1.106 + }
1.107 +} [list 4321 $DFLT_CACHE_SZ 1]
1.108 +do_test pragma-1.7 {
1.109 + db close
1.110 + sqlite3 db test.db
1.111 + execsql {
1.112 + PRAGMA cache_size;
1.113 + PRAGMA default_cache_size;
1.114 + PRAGMA synchronous;
1.115 + }
1.116 +} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2]
1.117 +do_test pragma-1.8 {
1.118 + execsql {
1.119 + PRAGMA default_cache_size=123;
1.120 + PRAGMA cache_size;
1.121 + PRAGMA default_cache_size;
1.122 + PRAGMA synchronous;
1.123 + }
1.124 +} {123 123 2}
1.125 +do_test pragma-1.9.1 {
1.126 + db close
1.127 + sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
1.128 + execsql {
1.129 + PRAGMA cache_size;
1.130 + PRAGMA default_cache_size;
1.131 + PRAGMA synchronous;
1.132 + }
1.133 +} {123 123 2}
1.134 +ifcapable vacuum {
1.135 + do_test pragma-1.9.2 {
1.136 + execsql {
1.137 + VACUUM;
1.138 + PRAGMA cache_size;
1.139 + PRAGMA default_cache_size;
1.140 + PRAGMA synchronous;
1.141 + }
1.142 + } {123 123 2}
1.143 +}
1.144 +do_test pragma-1.10 {
1.145 + execsql {
1.146 + PRAGMA synchronous=NORMAL;
1.147 + PRAGMA cache_size;
1.148 + PRAGMA default_cache_size;
1.149 + PRAGMA synchronous;
1.150 + }
1.151 +} {123 123 1}
1.152 +do_test pragma-1.11 {
1.153 + execsql {
1.154 + PRAGMA synchronous=FULL;
1.155 + PRAGMA cache_size;
1.156 + PRAGMA default_cache_size;
1.157 + PRAGMA synchronous;
1.158 + }
1.159 +} {123 123 2}
1.160 +do_test pragma-1.12 {
1.161 + db close
1.162 + sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db]
1.163 + execsql {
1.164 + PRAGMA cache_size;
1.165 + PRAGMA default_cache_size;
1.166 + PRAGMA synchronous;
1.167 + }
1.168 +} {123 123 2}
1.169 +
1.170 +# Make sure the pragma handler understands numeric values in addition
1.171 +# to keywords like "off" and "full".
1.172 +#
1.173 +do_test pragma-1.13 {
1.174 + execsql {
1.175 + PRAGMA synchronous=0;
1.176 + PRAGMA synchronous;
1.177 + }
1.178 +} {0}
1.179 +do_test pragma-1.14 {
1.180 + execsql {
1.181 + PRAGMA synchronous=2;
1.182 + PRAGMA synchronous;
1.183 + }
1.184 +} {2}
1.185 +} ;# ifcapable pager_pragmas
1.186 +
1.187 +# Test turning "flag" pragmas on and off.
1.188 +#
1.189 +ifcapable debug {
1.190 + # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
1.191 + #
1.192 + do_test pragma-1.15 {
1.193 + execsql {
1.194 + PRAGMA vdbe_listing=YES;
1.195 + PRAGMA vdbe_listing;
1.196 + }
1.197 + } {1}
1.198 + do_test pragma-1.16 {
1.199 + execsql {
1.200 + PRAGMA vdbe_listing=NO;
1.201 + PRAGMA vdbe_listing;
1.202 + }
1.203 + } {0}
1.204 +}
1.205 +
1.206 +do_test pragma-1.17 {
1.207 + execsql {
1.208 + PRAGMA parser_trace=ON;
1.209 + PRAGMA parser_trace=OFF;
1.210 + }
1.211 +} {}
1.212 +do_test pragma-1.18 {
1.213 + execsql {
1.214 + PRAGMA bogus = -1234; -- Parsing of negative values
1.215 + }
1.216 +} {}
1.217 +
1.218 +# Test modifying the safety_level of an attached database.
1.219 +ifcapable pager_pragmas&&attach {
1.220 + do_test pragma-2.1 {
1.221 + file delete -force test2.db
1.222 + file delete -force test2.db-journal
1.223 + execsql {
1.224 + ATTACH 'test2.db' AS aux;
1.225 + }
1.226 + } {}
1.227 + do_test pragma-2.2 {
1.228 + execsql {
1.229 + pragma aux.synchronous;
1.230 + }
1.231 + } {2}
1.232 + do_test pragma-2.3 {
1.233 + execsql {
1.234 + pragma aux.synchronous = OFF;
1.235 + pragma aux.synchronous;
1.236 + pragma synchronous;
1.237 + }
1.238 + } {0 2}
1.239 + do_test pragma-2.4 {
1.240 + execsql {
1.241 + pragma aux.synchronous = ON;
1.242 + pragma synchronous;
1.243 + pragma aux.synchronous;
1.244 + }
1.245 + } {2 1}
1.246 +} ;# ifcapable pager_pragmas
1.247 +
1.248 +# Construct a corrupted index and make sure the integrity_check
1.249 +# pragma finds it.
1.250 +#
1.251 +# These tests won't work if the database is encrypted
1.252 +#
1.253 +do_test pragma-3.1 {
1.254 + db close
1.255 + file delete -force test.db test.db-journal
1.256 + sqlite3 db test.db
1.257 + execsql {
1.258 + PRAGMA auto_vacuum=OFF;
1.259 + BEGIN;
1.260 + CREATE TABLE t2(a,b,c);
1.261 + CREATE INDEX i2 ON t2(a);
1.262 + INSERT INTO t2 VALUES(11,2,3);
1.263 + INSERT INTO t2 VALUES(22,3,4);
1.264 + COMMIT;
1.265 + SELECT rowid, * from t2;
1.266 + }
1.267 +} {1 11 2 3 2 22 3 4}
1.268 +ifcapable attach {
1.269 + if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} {
1.270 + do_test pragma-3.2 {
1.271 + db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break
1.272 + set pgsz [db eval {PRAGMA page_size}]
1.273 + # overwrite the header on the rootpage of the index in order to
1.274 + # make the index appear to be empty.
1.275 + #
1.276 + set offset [expr {$pgsz*($rootpage-1)}]
1.277 + hexio_write test.db $offset 0a00000000040000000000
1.278 + db close
1.279 + sqlite3 db test.db
1.280 + execsql {PRAGMA integrity_check}
1.281 + } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
1.282 + do_test pragma-3.3 {
1.283 + execsql {PRAGMA integrity_check=1}
1.284 + } {{rowid 1 missing from index i2}}
1.285 + do_test pragma-3.4 {
1.286 + execsql {
1.287 + ATTACH DATABASE 'test.db' AS t2;
1.288 + PRAGMA integrity_check
1.289 + }
1.290 + } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
1.291 + do_test pragma-3.5 {
1.292 + execsql {
1.293 + PRAGMA integrity_check=4
1.294 + }
1.295 + } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2}}
1.296 + do_test pragma-3.6 {
1.297 + execsql {
1.298 + PRAGMA integrity_check=xyz
1.299 + }
1.300 + } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
1.301 + do_test pragma-3.7 {
1.302 + execsql {
1.303 + PRAGMA integrity_check=0
1.304 + }
1.305 + } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
1.306 +
1.307 + # Add additional corruption by appending unused pages to the end of
1.308 + # the database file testerr.db
1.309 + #
1.310 + do_test pragma-3.8 {
1.311 + execsql {DETACH t2}
1.312 + file delete -force testerr.db testerr.db-journal
1.313 + set out [open testerr.db w]
1.314 + fconfigure $out -translation binary
1.315 + set in [open test.db r]
1.316 + fconfigure $in -translation binary
1.317 + puts -nonewline $out [read $in]
1.318 + seek $in 0
1.319 + puts -nonewline $out [read $in]
1.320 + close $in
1.321 + close $out
1.322 + execsql {REINDEX t2}
1.323 + execsql {PRAGMA integrity_check}
1.324 + } {ok}
1.325 + do_test pragma-3.8.1 {
1.326 + execsql {PRAGMA quick_check}
1.327 + } {ok}
1.328 + do_test pragma-3.9 {
1.329 + execsql {
1.330 + ATTACH 'testerr.db' AS t2;
1.331 + PRAGMA integrity_check
1.332 + }
1.333 + } {{*** in database t2 ***
1.334 +Page 4 is never used
1.335 +Page 5 is never used
1.336 +Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
1.337 + do_test pragma-3.10 {
1.338 + execsql {
1.339 + PRAGMA integrity_check=1
1.340 + }
1.341 + } {{*** in database t2 ***
1.342 +Page 4 is never used}}
1.343 + do_test pragma-3.11 {
1.344 + execsql {
1.345 + PRAGMA integrity_check=5
1.346 + }
1.347 + } {{*** in database t2 ***
1.348 +Page 4 is never used
1.349 +Page 5 is never used
1.350 +Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2}}
1.351 + do_test pragma-3.12 {
1.352 + execsql {
1.353 + PRAGMA integrity_check=4
1.354 + }
1.355 + } {{*** in database t2 ***
1.356 +Page 4 is never used
1.357 +Page 5 is never used
1.358 +Page 6 is never used} {rowid 1 missing from index i2}}
1.359 + do_test pragma-3.13 {
1.360 + execsql {
1.361 + PRAGMA integrity_check=3
1.362 + }
1.363 + } {{*** in database t2 ***
1.364 +Page 4 is never used
1.365 +Page 5 is never used
1.366 +Page 6 is never used}}
1.367 + do_test pragma-3.14 {
1.368 + execsql {
1.369 + PRAGMA integrity_check(2)
1.370 + }
1.371 + } {{*** in database t2 ***
1.372 +Page 4 is never used
1.373 +Page 5 is never used}}
1.374 + do_test pragma-3.15 {
1.375 + execsql {
1.376 + ATTACH 'testerr.db' AS t3;
1.377 + PRAGMA integrity_check
1.378 + }
1.379 + } {{*** in database t2 ***
1.380 +Page 4 is never used
1.381 +Page 5 is never used
1.382 +Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
1.383 +Page 4 is never used
1.384 +Page 5 is never used
1.385 +Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
1.386 + do_test pragma-3.16 {
1.387 + execsql {
1.388 + PRAGMA integrity_check(10)
1.389 + }
1.390 + } {{*** in database t2 ***
1.391 +Page 4 is never used
1.392 +Page 5 is never used
1.393 +Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
1.394 +Page 4 is never used
1.395 +Page 5 is never used
1.396 +Page 6 is never used} {rowid 1 missing from index i2}}
1.397 + do_test pragma-3.17 {
1.398 + execsql {
1.399 + PRAGMA integrity_check=8
1.400 + }
1.401 + } {{*** in database t2 ***
1.402 +Page 4 is never used
1.403 +Page 5 is never used
1.404 +Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
1.405 +Page 4 is never used
1.406 +Page 5 is never used}}
1.407 + do_test pragma-3.18 {
1.408 + execsql {
1.409 + PRAGMA integrity_check=4
1.410 + }
1.411 + } {{*** in database t2 ***
1.412 +Page 4 is never used
1.413 +Page 5 is never used
1.414 +Page 6 is never used} {rowid 1 missing from index i2}}
1.415 + }
1.416 + do_test pragma-3.99 {
1.417 + catchsql {DETACH t3}
1.418 + catchsql {DETACH t2}
1.419 + file delete -force testerr.db testerr.db-journal
1.420 + catchsql {DROP INDEX i2}
1.421 + } {0 {}}
1.422 +}
1.423 +
1.424 +# Test modifying the cache_size of an attached database.
1.425 +ifcapable pager_pragmas&&attach {
1.426 +do_test pragma-4.1 {
1.427 + execsql {
1.428 + ATTACH 'test2.db' AS aux;
1.429 + pragma aux.cache_size;
1.430 + pragma aux.default_cache_size;
1.431 + }
1.432 +} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
1.433 +do_test pragma-4.2 {
1.434 + execsql {
1.435 + pragma aux.cache_size = 50;
1.436 + pragma aux.cache_size;
1.437 + pragma aux.default_cache_size;
1.438 + }
1.439 +} [list 50 $DFLT_CACHE_SZ]
1.440 +do_test pragma-4.3 {
1.441 + execsql {
1.442 + pragma aux.default_cache_size = 456;
1.443 + pragma aux.cache_size;
1.444 + pragma aux.default_cache_size;
1.445 + }
1.446 +} {456 456}
1.447 +do_test pragma-4.4 {
1.448 + execsql {
1.449 + pragma cache_size;
1.450 + pragma default_cache_size;
1.451 + }
1.452 +} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
1.453 +do_test pragma-4.5 {
1.454 + execsql {
1.455 + DETACH aux;
1.456 + ATTACH 'test3.db' AS aux;
1.457 + pragma aux.cache_size;
1.458 + pragma aux.default_cache_size;
1.459 + }
1.460 +} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
1.461 +do_test pragma-4.6 {
1.462 + execsql {
1.463 + DETACH aux;
1.464 + ATTACH 'test2.db' AS aux;
1.465 + pragma aux.cache_size;
1.466 + pragma aux.default_cache_size;
1.467 + }
1.468 +} {456 456}
1.469 +} ;# ifcapable pager_pragmas
1.470 +
1.471 +# Test that modifying the sync-level in the middle of a transaction is
1.472 +# disallowed.
1.473 +ifcapable pager_pragmas {
1.474 +do_test pragma-5.0 {
1.475 + execsql {
1.476 + pragma synchronous;
1.477 + }
1.478 +} {2}
1.479 +do_test pragma-5.1 {
1.480 + catchsql {
1.481 + BEGIN;
1.482 + pragma synchronous = OFF;
1.483 + }
1.484 +} {1 {Safety level may not be changed inside a transaction}}
1.485 +do_test pragma-5.2 {
1.486 + execsql {
1.487 + pragma synchronous;
1.488 + }
1.489 +} {2}
1.490 +catchsql {COMMIT;}
1.491 +} ;# ifcapable pager_pragmas
1.492 +
1.493 +# Test schema-query pragmas
1.494 +#
1.495 +ifcapable schema_pragmas {
1.496 +ifcapable tempdb&&attach {
1.497 + do_test pragma-6.1 {
1.498 + set res {}
1.499 + execsql {SELECT * FROM sqlite_temp_master}
1.500 + foreach {idx name file} [execsql {pragma database_list}] {
1.501 + lappend res $idx $name
1.502 + }
1.503 + set res
1.504 + } {0 main 1 temp 2 aux}
1.505 +}
1.506 +do_test pragma-6.2 {
1.507 + execsql {
1.508 + pragma table_info(t2)
1.509 + }
1.510 +} {0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0}
1.511 +db nullvalue <<NULL>>
1.512 +do_test pragma-6.2.2 {
1.513 + execsql {
1.514 + CREATE TABLE t5(
1.515 + a TEXT DEFAULT CURRENT_TIMESTAMP,
1.516 + b DEFAULT (5+3),
1.517 + c TEXT,
1.518 + d INTEGER DEFAULT NULL,
1.519 + e TEXT DEFAULT ''
1.520 + );
1.521 + PRAGMA table_info(t5);
1.522 + }
1.523 +} {0 a TEXT 0 CURRENT_TIMESTAMP 0 1 b {} 0 5+3 0 2 c TEXT 0 <<NULL>> 0 3 d INTEGER 0 NULL 0 4 e TEXT 0 '' 0}
1.524 +db nullvalue {}
1.525 +ifcapable {foreignkey} {
1.526 + do_test pragma-6.3 {
1.527 + execsql {
1.528 + CREATE TABLE t3(a int references t2(b), b UNIQUE);
1.529 + pragma foreign_key_list(t3);
1.530 + }
1.531 + } {0 0 t2 a b}
1.532 + do_test pragma-6.4 {
1.533 + execsql {
1.534 + pragma index_list(t3);
1.535 + }
1.536 + } {0 sqlite_autoindex_t3_1 1}
1.537 +}
1.538 +ifcapable {!foreignkey} {
1.539 + execsql {CREATE TABLE t3(a,b UNIQUE)}
1.540 +}
1.541 +do_test pragma-6.5 {
1.542 + execsql {
1.543 + CREATE INDEX t3i1 ON t3(a,b);
1.544 + pragma index_info(t3i1);
1.545 + }
1.546 +} {0 0 a 1 1 b}
1.547 +
1.548 +ifcapable tempdb {
1.549 + # Test for ticket #3320. When a temp table of the same name exists, make
1.550 + # sure the schema of the main table can still be queried using
1.551 + # "pragma table_info":
1.552 + do_test pragma-6.6.1 {
1.553 + execsql {
1.554 + CREATE TABLE trial(col_main);
1.555 + CREATE TEMP TABLE trial(col_temp);
1.556 + }
1.557 + } {}
1.558 + do_test pragma-6.6.2 {
1.559 + execsql {
1.560 + PRAGMA table_info(trial);
1.561 + }
1.562 + } {0 col_temp {} 0 {} 0}
1.563 + do_test pragma-6.6.3 {
1.564 + execsql {
1.565 + PRAGMA temp.table_info(trial);
1.566 + }
1.567 + } {0 col_temp {} 0 {} 0}
1.568 + do_test pragma-6.6.4 {
1.569 + execsql {
1.570 + PRAGMA main.table_info(trial);
1.571 + }
1.572 + } {0 col_main {} 0 {} 0}
1.573 +}
1.574 +} ;# ifcapable schema_pragmas
1.575 +# Miscellaneous tests
1.576 +#
1.577 +ifcapable schema_pragmas {
1.578 +do_test pragma-7.1 {
1.579 + # Make sure a pragma knows to read the schema if it needs to
1.580 + db close
1.581 + sqlite3 db test.db
1.582 + execsql {
1.583 + pragma index_list(t3);
1.584 + }
1.585 +} {0 t3i1 0 1 sqlite_autoindex_t3_1 1}
1.586 +} ;# ifcapable schema_pragmas
1.587 +ifcapable {utf16} {
1.588 + do_test pragma-7.2 {
1.589 + db close
1.590 + sqlite3 db test.db
1.591 + catchsql {
1.592 + pragma encoding=bogus;
1.593 + }
1.594 + } {1 {unsupported encoding: bogus}}
1.595 +}
1.596 +ifcapable tempdb {
1.597 + do_test pragma-7.3 {
1.598 + db close
1.599 + sqlite3 db test.db
1.600 + execsql {
1.601 + pragma lock_status;
1.602 + }
1.603 + } {main unlocked temp closed}
1.604 +} else {
1.605 + do_test pragma-7.3 {
1.606 + db close
1.607 + sqlite3 db test.db
1.608 + execsql {
1.609 + pragma lock_status;
1.610 + }
1.611 + } {main unlocked}
1.612 +}
1.613 +
1.614 +
1.615 +#----------------------------------------------------------------------
1.616 +# Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
1.617 +# user_version" statements.
1.618 +#
1.619 +# pragma-8.1: PRAGMA schema_version
1.620 +# pragma-8.2: PRAGMA user_version
1.621 +#
1.622 +
1.623 +ifcapable schema_version {
1.624 +
1.625 +# First check that we can set the schema version and then retrieve the
1.626 +# same value.
1.627 +do_test pragma-8.1.1 {
1.628 + execsql {
1.629 + PRAGMA schema_version = 105;
1.630 + }
1.631 +} {}
1.632 +do_test pragma-8.1.2 {
1.633 + execsql2 {
1.634 + PRAGMA schema_version;
1.635 + }
1.636 +} {schema_version 105}
1.637 +do_test pragma-8.1.3 {
1.638 + execsql {
1.639 + PRAGMA schema_version = 106;
1.640 + }
1.641 +} {}
1.642 +do_test pragma-8.1.4 {
1.643 + execsql {
1.644 + PRAGMA schema_version;
1.645 + }
1.646 +} 106
1.647 +
1.648 +# Check that creating a table modifies the schema-version (this is really
1.649 +# to verify that the value being read is in fact the schema version).
1.650 +do_test pragma-8.1.5 {
1.651 + execsql {
1.652 + CREATE TABLE t4(a, b, c);
1.653 + INSERT INTO t4 VALUES(1, 2, 3);
1.654 + SELECT * FROM t4;
1.655 + }
1.656 +} {1 2 3}
1.657 +do_test pragma-8.1.6 {
1.658 + execsql {
1.659 + PRAGMA schema_version;
1.660 + }
1.661 +} 107
1.662 +
1.663 +# Now open a second connection to the database. Ensure that changing the
1.664 +# schema-version using the first connection forces the second connection
1.665 +# to reload the schema. This has to be done using the C-API test functions,
1.666 +# because the TCL API accounts for SCHEMA_ERROR and retries the query.
1.667 +do_test pragma-8.1.7 {
1.668 + sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
1.669 + execsql {
1.670 + SELECT * FROM t4;
1.671 + } db2
1.672 +} {1 2 3}
1.673 +do_test pragma-8.1.8 {
1.674 + execsql {
1.675 + PRAGMA schema_version = 108;
1.676 + }
1.677 +} {}
1.678 +do_test pragma-8.1.9 {
1.679 + set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]
1.680 + sqlite3_step $::STMT
1.681 +} SQLITE_ERROR
1.682 +do_test pragma-8.1.10 {
1.683 + sqlite3_finalize $::STMT
1.684 +} SQLITE_SCHEMA
1.685 +
1.686 +# Make sure the schema-version can be manipulated in an attached database.
1.687 +file delete -force test2.db
1.688 +file delete -force test2.db-journal
1.689 +ifcapable attach {
1.690 + do_test pragma-8.1.11 {
1.691 + execsql {
1.692 + ATTACH 'test2.db' AS aux;
1.693 + CREATE TABLE aux.t1(a, b, c);
1.694 + PRAGMA aux.schema_version = 205;
1.695 + }
1.696 + } {}
1.697 + do_test pragma-8.1.12 {
1.698 + execsql {
1.699 + PRAGMA aux.schema_version;
1.700 + }
1.701 + } 205
1.702 +}
1.703 +do_test pragma-8.1.13 {
1.704 + execsql {
1.705 + PRAGMA schema_version;
1.706 + }
1.707 +} 108
1.708 +
1.709 +# And check that modifying the schema-version in an attached database
1.710 +# forces the second connection to reload the schema.
1.711 +ifcapable attach {
1.712 + do_test pragma-8.1.14 {
1.713 + sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
1.714 + execsql {
1.715 + ATTACH 'test2.db' AS aux;
1.716 + SELECT * FROM aux.t1;
1.717 + } db2
1.718 + } {}
1.719 + do_test pragma-8.1.15 {
1.720 + execsql {
1.721 + PRAGMA aux.schema_version = 206;
1.722 + }
1.723 + } {}
1.724 + do_test pragma-8.1.16 {
1.725 + set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
1.726 + sqlite3_step $::STMT
1.727 + } SQLITE_ERROR
1.728 + do_test pragma-8.1.17 {
1.729 + sqlite3_finalize $::STMT
1.730 + } SQLITE_SCHEMA
1.731 + do_test pragma-8.1.18 {
1.732 + db2 close
1.733 + } {}
1.734 +}
1.735 +
1.736 +# Now test that the user-version can be read and written (and that we aren't
1.737 +# accidentally manipulating the schema-version instead).
1.738 +do_test pragma-8.2.1 {
1.739 + execsql2 {
1.740 + PRAGMA user_version;
1.741 + }
1.742 +} {user_version 0}
1.743 +do_test pragma-8.2.2 {
1.744 + execsql {
1.745 + PRAGMA user_version = 2;
1.746 + }
1.747 +} {}
1.748 +do_test pragma-8.2.3.1 {
1.749 + execsql2 {
1.750 + PRAGMA user_version;
1.751 + }
1.752 +} {user_version 2}
1.753 +do_test pragma-8.2.3.2 {
1.754 + db close
1.755 + sqlite3 db test.db
1.756 + execsql {
1.757 + PRAGMA user_version;
1.758 + }
1.759 +} {2}
1.760 +do_test pragma-8.2.4.1 {
1.761 + execsql {
1.762 + PRAGMA schema_version;
1.763 + }
1.764 +} {108}
1.765 +ifcapable vacuum {
1.766 + do_test pragma-8.2.4.2 {
1.767 + execsql {
1.768 + VACUUM;
1.769 + PRAGMA user_version;
1.770 + }
1.771 + } {2}
1.772 + do_test pragma-8.2.4.3 {
1.773 + execsql {
1.774 + PRAGMA schema_version;
1.775 + }
1.776 + } {109}
1.777 +}
1.778 +
1.779 +ifcapable attach {
1.780 + db eval {ATTACH 'test2.db' AS aux}
1.781 +
1.782 + # Check that the user-version in the auxilary database can be manipulated (
1.783 + # and that we aren't accidentally manipulating the same in the main db).
1.784 + do_test pragma-8.2.5 {
1.785 + execsql {
1.786 + PRAGMA aux.user_version;
1.787 + }
1.788 + } {0}
1.789 + do_test pragma-8.2.6 {
1.790 + execsql {
1.791 + PRAGMA aux.user_version = 3;
1.792 + }
1.793 + } {}
1.794 + do_test pragma-8.2.7 {
1.795 + execsql {
1.796 + PRAGMA aux.user_version;
1.797 + }
1.798 + } {3}
1.799 + do_test pragma-8.2.8 {
1.800 + execsql {
1.801 + PRAGMA main.user_version;
1.802 + }
1.803 + } {2}
1.804 +
1.805 + # Now check that a ROLLBACK resets the user-version if it has been modified
1.806 + # within a transaction.
1.807 + do_test pragma-8.2.9 {
1.808 + execsql {
1.809 + BEGIN;
1.810 + PRAGMA aux.user_version = 10;
1.811 + PRAGMA user_version = 11;
1.812 + }
1.813 + } {}
1.814 + do_test pragma-8.2.10 {
1.815 + execsql {
1.816 + PRAGMA aux.user_version;
1.817 + }
1.818 + } {10}
1.819 + do_test pragma-8.2.11 {
1.820 + execsql {
1.821 + PRAGMA main.user_version;
1.822 + }
1.823 + } {11}
1.824 + do_test pragma-8.2.12 {
1.825 + execsql {
1.826 + ROLLBACK;
1.827 + PRAGMA aux.user_version;
1.828 + }
1.829 + } {3}
1.830 + do_test pragma-8.2.13 {
1.831 + execsql {
1.832 + PRAGMA main.user_version;
1.833 + }
1.834 + } {2}
1.835 +}
1.836 +
1.837 +# Try a negative value for the user-version
1.838 +do_test pragma-8.2.14 {
1.839 + execsql {
1.840 + PRAGMA user_version = -450;
1.841 + }
1.842 +} {}
1.843 +do_test pragma-8.2.15 {
1.844 + execsql {
1.845 + PRAGMA user_version;
1.846 + }
1.847 +} {-450}
1.848 +} ; # ifcapable schema_version
1.849 +
1.850 +# Check to see if TEMP_STORE is memory or disk. Return strings
1.851 +# "memory" or "disk" as appropriate.
1.852 +#
1.853 +proc check_temp_store {} {
1.854 + db eval {CREATE TEMP TABLE IF NOT EXISTS a(b)}
1.855 + db eval {PRAGMA database_list} {
1.856 + if {$name=="temp"} {
1.857 + set bt [btree_from_db db 1]
1.858 + if {[btree_ismemdb $bt]} {
1.859 + return "memory"
1.860 + }
1.861 + return "disk"
1.862 + }
1.863 + }
1.864 + return "unknown"
1.865 +}
1.866 +
1.867 +
1.868 +# Test temp_store and temp_store_directory pragmas
1.869 +#
1.870 +ifcapable pager_pragmas {
1.871 +do_test pragma-9.1 {
1.872 + db close
1.873 + sqlite3 db test.db
1.874 + execsql {
1.875 + PRAGMA temp_store;
1.876 + }
1.877 +} {0}
1.878 +if {$TEMP_STORE<=1} {
1.879 + do_test pragma-9.1.1 {
1.880 + check_temp_store
1.881 + } {disk}
1.882 +} else {
1.883 + do_test pragma-9.1.1 {
1.884 + check_temp_store
1.885 + } {memory}
1.886 +}
1.887 +
1.888 +do_test pragma-9.2 {
1.889 + db close
1.890 + sqlite3 db test.db
1.891 + execsql {
1.892 + PRAGMA temp_store=file;
1.893 + PRAGMA temp_store;
1.894 + }
1.895 +} {1}
1.896 +if {$TEMP_STORE==3} {
1.897 + # When TEMP_STORE is 3, always use memory regardless of pragma settings.
1.898 + do_test pragma-9.2.1 {
1.899 + check_temp_store
1.900 + } {memory}
1.901 +} else {
1.902 + do_test pragma-9.2.1 {
1.903 + check_temp_store
1.904 + } {disk}
1.905 +}
1.906 +
1.907 +do_test pragma-9.3 {
1.908 + db close
1.909 + sqlite3 db test.db
1.910 + execsql {
1.911 + PRAGMA temp_store=memory;
1.912 + PRAGMA temp_store;
1.913 + }
1.914 +} {2}
1.915 +if {$TEMP_STORE==0} {
1.916 + # When TEMP_STORE is 0, always use the disk regardless of pragma settings.
1.917 + do_test pragma-9.3.1 {
1.918 + check_temp_store
1.919 + } {disk}
1.920 +} else {
1.921 + do_test pragma-9.3.1 {
1.922 + check_temp_store
1.923 + } {memory}
1.924 +}
1.925 +
1.926 +do_test pragma-9.4 {
1.927 + execsql {
1.928 + PRAGMA temp_store_directory;
1.929 + }
1.930 +} {}
1.931 +ifcapable wsd {
1.932 + do_test pragma-9.5 {
1.933 + set pwd [string map {' ''} [file nativename [pwd]]]
1.934 + execsql "
1.935 + PRAGMA temp_store_directory='$pwd';
1.936 + "
1.937 + } {}
1.938 + do_test pragma-9.6 {
1.939 + execsql {
1.940 + PRAGMA temp_store_directory;
1.941 + }
1.942 + } [list [file nativename [pwd]]]
1.943 + do_test pragma-9.7 {
1.944 + catchsql {
1.945 + PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
1.946 + }
1.947 + } {1 {not a writable directory}}
1.948 + do_test pragma-9.8 {
1.949 + execsql {
1.950 + PRAGMA temp_store_directory='';
1.951 + }
1.952 + } {}
1.953 + if {![info exists TEMP_STORE] || $TEMP_STORE<=1} {
1.954 + ifcapable tempdb {
1.955 + do_test pragma-9.9 {
1.956 + execsql {
1.957 + PRAGMA temp_store_directory;
1.958 + PRAGMA temp_store=FILE;
1.959 + CREATE TEMP TABLE temp_store_directory_test(a integer);
1.960 + INSERT INTO temp_store_directory_test values (2);
1.961 + SELECT * FROM temp_store_directory_test;
1.962 + }
1.963 + } {2}
1.964 + do_test pragma-9.10 {
1.965 + catchsql "
1.966 + PRAGMA temp_store_directory='$pwd';
1.967 + SELECT * FROM temp_store_directory_test;
1.968 + "
1.969 + } {1 {no such table: temp_store_directory_test}}
1.970 + }
1.971 + }
1.972 +}
1.973 +do_test pragma-9.11 {
1.974 + execsql {
1.975 + PRAGMA temp_store = 0;
1.976 + PRAGMA temp_store;
1.977 + }
1.978 +} {0}
1.979 +do_test pragma-9.12 {
1.980 + execsql {
1.981 + PRAGMA temp_store = 1;
1.982 + PRAGMA temp_store;
1.983 + }
1.984 +} {1}
1.985 +do_test pragma-9.13 {
1.986 + execsql {
1.987 + PRAGMA temp_store = 2;
1.988 + PRAGMA temp_store;
1.989 + }
1.990 +} {2}
1.991 +do_test pragma-9.14 {
1.992 + execsql {
1.993 + PRAGMA temp_store = 3;
1.994 + PRAGMA temp_store;
1.995 + }
1.996 +} {0}
1.997 +do_test pragma-9.15 {
1.998 + catchsql {
1.999 + BEGIN EXCLUSIVE;
1.1000 + CREATE TEMP TABLE temp_table(t);
1.1001 + INSERT INTO temp_table VALUES('valuable data');
1.1002 + PRAGMA temp_store = 1;
1.1003 + }
1.1004 +} {1 {temporary storage cannot be changed from within a transaction}}
1.1005 +do_test pragma-9.16 {
1.1006 + execsql {
1.1007 + SELECT * FROM temp_table;
1.1008 + COMMIT;
1.1009 + }
1.1010 +} {{valuable data}}
1.1011 +
1.1012 +do_test pragma-9.17 {
1.1013 + execsql {
1.1014 + INSERT INTO temp_table VALUES('valuable data II');
1.1015 + SELECT * FROM temp_table;
1.1016 + }
1.1017 +} {{valuable data} {valuable data II}}
1.1018 +
1.1019 +do_test pragma-9.18 {
1.1020 + set rc [catch {
1.1021 + db eval {SELECT t FROM temp_table} {
1.1022 + execsql {pragma temp_store = 1}
1.1023 + }
1.1024 + } msg]
1.1025 + list $rc $msg
1.1026 +} {1 {temporary storage cannot be changed from within a transaction}}
1.1027 +
1.1028 +} ;# ifcapable pager_pragmas
1.1029 +
1.1030 +ifcapable trigger {
1.1031 +
1.1032 +do_test pragma-10.0 {
1.1033 + catchsql {
1.1034 + DROP TABLE main.t1;
1.1035 + }
1.1036 + execsql {
1.1037 + PRAGMA count_changes = 1;
1.1038 +
1.1039 + CREATE TABLE t1(a PRIMARY KEY);
1.1040 + CREATE TABLE t1_mirror(a);
1.1041 + CREATE TABLE t1_mirror2(a);
1.1042 + CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN
1.1043 + INSERT INTO t1_mirror VALUES(new.a);
1.1044 + END;
1.1045 + CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
1.1046 + INSERT INTO t1_mirror2 VALUES(new.a);
1.1047 + END;
1.1048 + CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN
1.1049 + UPDATE t1_mirror SET a = new.a WHERE a = old.a;
1.1050 + END;
1.1051 + CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN
1.1052 + UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;
1.1053 + END;
1.1054 + CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN
1.1055 + DELETE FROM t1_mirror WHERE a = old.a;
1.1056 + END;
1.1057 + CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
1.1058 + DELETE FROM t1_mirror2 WHERE a = old.a;
1.1059 + END;
1.1060 + }
1.1061 +} {}
1.1062 +
1.1063 +do_test pragma-10.1 {
1.1064 + execsql {
1.1065 + INSERT INTO t1 VALUES(randstr(10,10));
1.1066 + }
1.1067 +} {1}
1.1068 +do_test pragma-10.2 {
1.1069 + execsql {
1.1070 + UPDATE t1 SET a = randstr(10,10);
1.1071 + }
1.1072 +} {1}
1.1073 +do_test pragma-10.3 {
1.1074 + execsql {
1.1075 + DELETE FROM t1;
1.1076 + }
1.1077 +} {1}
1.1078 +
1.1079 +} ;# ifcapable trigger
1.1080 +
1.1081 +ifcapable schema_pragmas {
1.1082 + do_test pragma-11.1 {
1.1083 + execsql2 {
1.1084 + pragma collation_list;
1.1085 + }
1.1086 + } {seq 0 name NOCASE seq 1 name RTRIM seq 2 name BINARY}
1.1087 + do_test pragma-11.2 {
1.1088 + db collate New_Collation blah...
1.1089 + execsql {
1.1090 + pragma collation_list;
1.1091 + }
1.1092 + } {0 New_Collation 1 NOCASE 2 RTRIM 3 BINARY}
1.1093 +}
1.1094 +
1.1095 +ifcapable schema_pragmas&&tempdb {
1.1096 + do_test pragma-12.1 {
1.1097 + sqlite3 db2 test.db
1.1098 + execsql {
1.1099 + PRAGMA temp.table_info('abc');
1.1100 + } db2
1.1101 + } {}
1.1102 + db2 close
1.1103 +
1.1104 + do_test pragma-12.2 {
1.1105 + sqlite3 db2 test.db
1.1106 + execsql {
1.1107 + PRAGMA temp.default_cache_size = 200;
1.1108 + PRAGMA temp.default_cache_size;
1.1109 + } db2
1.1110 + } {200}
1.1111 + db2 close
1.1112 +
1.1113 + do_test pragma-12.3 {
1.1114 + sqlite3 db2 test.db
1.1115 + execsql {
1.1116 + PRAGMA temp.cache_size = 400;
1.1117 + PRAGMA temp.cache_size;
1.1118 + } db2
1.1119 + } {400}
1.1120 + db2 close
1.1121 +}
1.1122 +
1.1123 +ifcapable bloblit {
1.1124 +
1.1125 +do_test pragma-13.1 {
1.1126 + execsql {
1.1127 + DROP TABLE IF EXISTS t4;
1.1128 + PRAGMA vdbe_trace=on;
1.1129 + PRAGMA vdbe_listing=on;
1.1130 + PRAGMA sql_trace=on;
1.1131 + CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
1.1132 + INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');
1.1133 + INSERT INTO t4(b) VALUES(randstr(30,30));
1.1134 + INSERT INTO t4(b) VALUES(1.23456);
1.1135 + INSERT INTO t4(b) VALUES(NULL);
1.1136 + INSERT INTO t4(b) VALUES(0);
1.1137 + INSERT INTO t4(b) SELECT b||b||b||b FROM t4;
1.1138 + SELECT * FROM t4;
1.1139 + }
1.1140 + execsql {
1.1141 + PRAGMA vdbe_trace=off;
1.1142 + PRAGMA vdbe_listing=off;
1.1143 + PRAGMA sql_trace=off;
1.1144 + }
1.1145 +} {}
1.1146 +
1.1147 +} ;# ifcapable bloblit
1.1148 +
1.1149 +ifcapable pager_pragmas {
1.1150 + db close
1.1151 + file delete -force test.db
1.1152 + sqlite3 db test.db
1.1153 +
1.1154 + do_test pragma-14.1 {
1.1155 + execsql { pragma auto_vacuum = 0 }
1.1156 + execsql { pragma page_count }
1.1157 + } {0}
1.1158 +
1.1159 + do_test pragma-14.2 {
1.1160 + execsql {
1.1161 + CREATE TABLE abc(a, b, c);
1.1162 + PRAGMA page_count;
1.1163 + }
1.1164 + } {2}
1.1165 +
1.1166 + do_test pragma-14.3 {
1.1167 + execsql {
1.1168 + BEGIN;
1.1169 + CREATE TABLE def(a, b, c);
1.1170 + PRAGMA page_count;
1.1171 + }
1.1172 + } {3}
1.1173 +
1.1174 + do_test pragma-14.4 {
1.1175 + set page_size [db one {pragma page_size}]
1.1176 + expr [file size test.db] / $page_size
1.1177 + } {2}
1.1178 +
1.1179 + do_test pragma-14.5 {
1.1180 + execsql {
1.1181 + ROLLBACK;
1.1182 + PRAGMA page_count;
1.1183 + }
1.1184 + } {2}
1.1185 +
1.1186 + do_test pragma-14.6 {
1.1187 + file delete -force test2.db
1.1188 + sqlite3 db2 test2.db
1.1189 + execsql {
1.1190 + PRAGMA auto_vacuum = 0;
1.1191 + CREATE TABLE t1(a, b, c);
1.1192 + CREATE TABLE t2(a, b, c);
1.1193 + CREATE TABLE t3(a, b, c);
1.1194 + CREATE TABLE t4(a, b, c);
1.1195 + } db2
1.1196 + db2 close
1.1197 + execsql {
1.1198 + ATTACH 'test2.db' AS aux;
1.1199 + PRAGMA aux.page_count;
1.1200 + }
1.1201 + } {5}
1.1202 +}
1.1203 +
1.1204 +# Test that the value set using the cache_size pragma is not reset when the
1.1205 +# schema is reloaded.
1.1206 +#
1.1207 +ifcapable pager_pragmas {
1.1208 + db close
1.1209 + sqlite3 db test.db
1.1210 + do_test pragma-15.1 {
1.1211 + execsql {
1.1212 + PRAGMA cache_size=59;
1.1213 + PRAGMA cache_size;
1.1214 + }
1.1215 + } {59}
1.1216 + do_test pragma-15.2 {
1.1217 + sqlite3 db2 test.db
1.1218 + execsql {
1.1219 + CREATE TABLE newtable(a, b, c);
1.1220 + } db2
1.1221 + db2 close
1.1222 + } {}
1.1223 + do_test pragma-15.3 {
1.1224 + # Evaluating this statement will cause the schema to be reloaded (because
1.1225 + # the schema was changed by another connection in pragma-15.2). At one
1.1226 + # point there was a bug that reset the cache_size to its default value
1.1227 + # when this happened.
1.1228 + execsql { SELECT * FROM sqlite_master }
1.1229 + execsql { PRAGMA cache_size }
1.1230 + } {59}
1.1231 +}
1.1232 +
1.1233 +# Reset the sqlite3_temp_directory variable for the next run of tests:
1.1234 +sqlite3 dbX :memory:
1.1235 +dbX eval {PRAGMA temp_store_directory = ""}
1.1236 +dbX close
1.1237 +
1.1238 +finish_test