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