AryaWu/sqlite
0
1# 2016 March 32#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#12# TESTRUNNER: slow13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix temptable218 19do_execsql_test 1.1 {20 CREATE TEMP TABLE t1(a, b);21 CREATE INDEX i1 ON t1(a, b);22}23 24do_execsql_test 1.2 {25 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100000 )26 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM X;27} {}28 29do_execsql_test 1.3 {30 PRAGMA temp.integrity_check;31} {ok}32 33#-------------------------------------------------------------------------34#35reset_db36do_execsql_test 2.1 {37 CREATE TEMP TABLE t2(a, b);38 INSERT INTO t2 VALUES(1, 2);39} {}40 41do_execsql_test 2.2 {42 BEGIN;43 INSERT INTO t2 VALUES(3, 4);44 SELECT * FROM t2;45} {1 2 3 4}46 47do_execsql_test 2.3 {48 ROLLBACK;49 SELECT * FROM t2;50} {1 2}51 52#-------------------------------------------------------------------------53#54reset_db55do_execsql_test 3.1.1 {56 PRAGMA main.cache_size = 10;57 PRAGMA temp.cache_size = 10;58 59 CREATE TEMP TABLE t1(a, b);60 CREATE INDEX i1 ON t1(a, b);61 62 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 )63 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;64 65 SELECT count(*) FROM t1;66} {1000}67do_execsql_test 3.1.2 {68 BEGIN;69 UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==0;70 ROLLBACK;71}72do_execsql_test 3.1.3 {73 SELECT count(*) FROM t1;74} {1000}75do_execsql_test 3.1.4 { PRAGMA temp.integrity_check } {ok}76 77do_execsql_test 3.2.1 {78 BEGIN;79 UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==0;80 SAVEPOINT abc;81 UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==1;82 ROLLBACK TO abc;83 UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==2;84 COMMIT;85}86do_execsql_test 3.2.2 { PRAGMA temp.integrity_check } {ok}87 88#-------------------------------------------------------------------------89#90reset_db91do_execsql_test 4.1.1 {92 PRAGMA main.cache_size = 10;93 PRAGMA temp.cache_size = 10;94 95 CREATE TEMP TABLE t1(a, b);96 CREATE INDEX i1 ON t1(a, b);97 98 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<10 )99 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;100 101 SELECT count(*) FROM t1;102 PRAGMA temp.page_count;103} {10 9}104 105do_execsql_test 4.1.2 {106 BEGIN;107 UPDATE t1 SET b=randomblob(100);108 ROLLBACK;109}110 111do_execsql_test 4.1.3 {112 CREATE TEMP TABLE t2(a, b);113 CREATE INDEX i2 ON t2(a, b);114 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )115 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;116 117 SELECT count(*) FROM t2;118 SELECT count(*) FROM t1;119} {500 10}120 121do_test 4.1.4 {122 set n [db one { PRAGMA temp.page_count }]123 expr ($n >280 && $n < 300) 124} 1125 126do_execsql_test 4.1.4 { PRAGMA temp.integrity_check } {ok}127 128#-------------------------------------------------------------------------129#130reset_db131do_execsql_test 5.1.1 {132 PRAGMA main.cache_size = 10;133 PRAGMA temp.cache_size = 10;134 135 CREATE TEMP TABLE t2(a, b);136 CREATE INDEX i2 ON t2(a, b);137 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )138 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;139 140 CREATE TEMP TABLE t1(a, b);141 CREATE INDEX i1 ON t1(a, b);142 INSERT INTO t1 VALUES(1, 2);143}144 145# Test that the temp database is now much bigger than the configured146# cache size (10 pages).147do_test 5.1.2 {148 set n [db one { PRAGMA temp.page_count }]149 expr ($n > 270 && $n < 290)150} {1}151 152do_execsql_test 5.1.3 {153 BEGIN;154 UPDATE t1 SET a=2;155 UPDATE t2 SET a=randomblob(100);156 SELECT count(*) FROM t1;157 ROLLBACK;158} {1}159 160do_execsql_test 5.1.4 {161 UPDATE t2 SET a=randomblob(100);162 163 SELECT * FROM t1;164} {1 2}165 166do_execsql_test 5.1.5 { PRAGMA temp.integrity_check } {ok}167 168#-------------------------------------------------------------------------169# Test this:170#171# 1. Page is DIRTY at the start of a transaction.172# 2. Page is written out as part of the transaction.173# 3. Page is then read back in.174# 4. Transaction is rolled back. Is the page now clean or dirty?175#176# This actually does work. Step 4 marks the page as clean. But it also177# writes to the database file itself. So marking it clean is correct - 178# the page does match the contents of the db file.179#180reset_db181 182do_execsql_test 6.1 {183 PRAGMA main.cache_size = 10;184 PRAGMA temp.cache_size = 10;185 186 CREATE TEMP TABLE t1(x);187 INSERT INTO t1 VALUES('one');188 189 CREATE TEMP TABLE t2(a, b);190 CREATE INDEX i2 ON t2(a, b);191 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )192 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;193}194 195do_execsql_test 6.2 {196 UPDATE t1 SET x='two'; -- step 1197 BEGIN;198 UPDATE t2 SET a=randomblob(100); -- step 2199 SELECT * FROM t1; -- step 3200 ROLLBACK; -- step 4201 202 SELECT count(*) FROM t2;203 SELECT * FROM t1;204} {two 500 two}205 206#-------------------------------------------------------------------------207#208reset_db209sqlite3 db ""210do_execsql_test 7.1 {211 PRAGMA auto_vacuum=INCREMENTAL;212 CREATE TABLE t1(x);213 INSERT INTO t1 VALUES(zeroblob(900));214 INSERT INTO t1 VALUES(zeroblob(900));215 INSERT INTO t1 SELECT x FROM t1;216 INSERT INTO t1 SELECT x FROM t1;217 INSERT INTO t1 SELECT x FROM t1;218 INSERT INTO t1 SELECT x FROM t1;219 BEGIN;220 DELETE FROM t1 WHERE rowid%2;221 PRAGMA incremental_vacuum(4);222 ROLLBACK;223 PRAGMA integrity_check;224} {ok}225 226#-------------------------------------------------------------------------227# Try changing the page size using a backup operation when pages are228# stored in main-memory only.229#230reset_db231do_execsql_test 8.1 {232 PRAGMA auto_vacuum = OFF;233 CREATE TABLE t2(a, b);234 CREATE INDEX i2 ON t2(a, b);235 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<20 )236 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x ORDER BY 1, 2;237 PRAGMA page_count;238} {13}239 240do_test 8.2 {241 sqlite3 tmp ""242 execsql {243 PRAGMA auto_vacuum = OFF;244 PRAGMA page_size = 8192;245 CREATE TABLE t1(a, b);246 CREATE INDEX i1 ON t1(a, b);247 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100 )248 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x ORDER BY 1, 2;249 PRAGMA page_count;250 } tmp251} {10}252 253do_test 8.3 {254 sqlite3_backup B tmp main db main255 B step 5256 B finish257} {SQLITE_READONLY}258 259do_test 8.4 {260 execsql {261 SELECT count(*) FROM t1;262 PRAGMA integrity_check;263 PRAGMA page_size;264 } tmp265} {100 ok 8192}266 267do_test 8.5 { 268 tmp eval { UPDATE t1 SET a=randomblob(100) }269} {}270 271do_test 8.6 {272 sqlite3_backup B tmp main db main273 B step 1000274 B finish275} {SQLITE_READONLY}276 277tmp close278 279#-------------------------------------------------------------------------280# Try inserts and deletes with a large db in auto-vacuum mode. Check281#282foreach {tn mode} {283 1 delete284 2 wal285} {286 reset_db287 sqlite3 db ""288 do_execsql_test 9.$tn.1.1 {289 PRAGMA cache_size = 15;290 PRAGMA auto_vacuum = 1;291 }292 execsql "PRAGMA journal_mode = $mode"293 294 do_execsql_test 9.$tn.1.2 {295 CREATE TABLE tx(a, b);296 CREATE INDEX i1 ON tx(a);297 CREATE INDEX i2 ON tx(b);298 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 )299 INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;300 }301 302 for {set i 2} {$i<20} {incr i} {303 do_execsql_test 9.$tn.$i.1 { DELETE FROM tx WHERE (random()%3)==0 }304 305 do_execsql_test 9.$tn.$i.2 { PRAGMA integrity_check } ok306 307 do_execsql_test 9.$tn.$i.3 { 308 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<400 )309 INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;310 }311 312 do_execsql_test 9.$tn.$i.4 { PRAGMA integrity_check } ok313 314 do_execsql_test 9.$tn.$i.5 { 315 BEGIN;316 DELETE FROM tx WHERE (random()%3)==0;317 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )318 INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;319 COMMIT;320 }321 322 do_execsql_test 9.$tn.$i.6 { PRAGMA integrity_check } ok323 }324}325 326#-------------------------------------------------------------------------327# When using mmap mode with a temp file, SQLite must search the cache 328# before using a mapped page even when there is no write transaction329# open. For a temp file, the on-disk version may not be up to date.330#331sqlite3 db ""332do_execsql_test 10.0 {333 PRAGMA cache_size = 50;334 PRAGMA page_size = 1024;335 CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;336 CREATE INDEX i1 ON t1(a);337 CREATE TABLE t2(x, y);338 INSERT INTO t2 VALUES(1, 2);339}340 341do_execsql_test 10.1 {342 BEGIN;343 WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )344 INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;345 COMMIT;346 INSERT INTO t2 VALUES(3, 4);347}348 349ifcapable mmap {350 if {[permutation]!="journaltest" && $::TEMP_STORE<2} {351 # The journaltest permutation does not support mmap, so this part of352 # the test is omitted.353 do_execsql_test 10.2 { PRAGMA mmap_size = 512000 } 512000354 }355}356 357do_execsql_test 10.3 { SELECT * FROM t2 } {1 2 3 4}358do_execsql_test 10.4 { PRAGMA integrity_check } ok359 360finish_test361