| OLD | NEW |
|---|
| (Empty) |
| 1 # 2001 September 15 | |
| 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 # This file implements regression tests for SQLite library. This | |
| 12 # file is a copy of "trans.test" modified to run under autovacuum mode. | |
| 13 # the point is to stress the autovacuum logic and try to get it to fail. | |
| 14 # | |
| 15 # $Id: avtrans.test,v 1.6 2007/09/12 17:01:45 danielk1977 Exp $ | |
| 16 | |
| 17 | |
| 18 set testdir [file dirname $argv0] | |
| 19 source $testdir/tester.tcl | |
| 20 | |
| 21 | |
| 22 # Create several tables to work with. | |
| 23 # | |
| 24 do_test avtrans-1.0 { | |
| 25 execsql { PRAGMA auto_vacuum=ON } | |
| 26 wal_set_journal_mode | |
| 27 execsql { | |
| 28 CREATE TABLE one(a int PRIMARY KEY, b text); | |
| 29 INSERT INTO one VALUES(1,'one'); | |
| 30 INSERT INTO one VALUES(2,'two'); | |
| 31 INSERT INTO one VALUES(3,'three'); | |
| 32 SELECT b FROM one ORDER BY a; | |
| 33 } | |
| 34 } {one two three} | |
| 35 do_test avtrans-1.1 { | |
| 36 execsql { | |
| 37 CREATE TABLE two(a int PRIMARY KEY, b text); | |
| 38 INSERT INTO two VALUES(1,'I'); | |
| 39 INSERT INTO two VALUES(5,'V'); | |
| 40 INSERT INTO two VALUES(10,'X'); | |
| 41 SELECT b FROM two ORDER BY a; | |
| 42 } | |
| 43 } {I V X} | |
| 44 do_test avtrans-1.9 { | |
| 45 sqlite3 altdb test.db | |
| 46 execsql {SELECT b FROM one ORDER BY a} altdb | |
| 47 } {one two three} | |
| 48 do_test avtrans-1.10 { | |
| 49 execsql {SELECT b FROM two ORDER BY a} altdb | |
| 50 } {I V X} | |
| 51 integrity_check avtrans-1.11 | |
| 52 wal_check_journal_mode avtrans-1.12 | |
| 53 | |
| 54 # Basic transactions | |
| 55 # | |
| 56 do_test avtrans-2.1 { | |
| 57 set v [catch {execsql {BEGIN}} msg] | |
| 58 lappend v $msg | |
| 59 } {0 {}} | |
| 60 do_test avtrans-2.2 { | |
| 61 set v [catch {execsql {END}} msg] | |
| 62 lappend v $msg | |
| 63 } {0 {}} | |
| 64 do_test avtrans-2.3 { | |
| 65 set v [catch {execsql {BEGIN TRANSACTION}} msg] | |
| 66 lappend v $msg | |
| 67 } {0 {}} | |
| 68 do_test avtrans-2.4 { | |
| 69 set v [catch {execsql {COMMIT TRANSACTION}} msg] | |
| 70 lappend v $msg | |
| 71 } {0 {}} | |
| 72 do_test avtrans-2.5 { | |
| 73 set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg] | |
| 74 lappend v $msg | |
| 75 } {0 {}} | |
| 76 do_test avtrans-2.6 { | |
| 77 set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg] | |
| 78 lappend v $msg | |
| 79 } {0 {}} | |
| 80 do_test avtrans-2.10 { | |
| 81 execsql { | |
| 82 BEGIN; | |
| 83 SELECT a FROM one ORDER BY a; | |
| 84 SELECT a FROM two ORDER BY a; | |
| 85 END; | |
| 86 } | |
| 87 } {1 2 3 1 5 10} | |
| 88 integrity_check avtrans-2.11 | |
| 89 wal_check_journal_mode avtrans-2.12 | |
| 90 | |
| 91 # Check the locking behavior | |
| 92 # | |
| 93 sqlite3_soft_heap_limit 0 | |
| 94 do_test avtrans-3.1 { | |
| 95 execsql { | |
| 96 BEGIN; | |
| 97 UPDATE one SET a = 0 WHERE 0; | |
| 98 SELECT a FROM one ORDER BY a; | |
| 99 } | |
| 100 } {1 2 3} | |
| 101 do_test avtrans-3.2 { | |
| 102 catchsql { | |
| 103 SELECT a FROM two ORDER BY a; | |
| 104 } altdb | |
| 105 } {0 {1 5 10}} | |
| 106 do_test avtrans-3.3 { | |
| 107 catchsql { | |
| 108 SELECT a FROM one ORDER BY a; | |
| 109 } altdb | |
| 110 } {0 {1 2 3}} | |
| 111 do_test avtrans-3.4 { | |
| 112 catchsql { | |
| 113 INSERT INTO one VALUES(4,'four'); | |
| 114 } | |
| 115 } {0 {}} | |
| 116 do_test avtrans-3.5 { | |
| 117 catchsql { | |
| 118 SELECT a FROM two ORDER BY a; | |
| 119 } altdb | |
| 120 } {0 {1 5 10}} | |
| 121 do_test avtrans-3.6 { | |
| 122 catchsql { | |
| 123 SELECT a FROM one ORDER BY a; | |
| 124 } altdb | |
| 125 } {0 {1 2 3}} | |
| 126 do_test avtrans-3.7 { | |
| 127 catchsql { | |
| 128 INSERT INTO two VALUES(4,'IV'); | |
| 129 } | |
| 130 } {0 {}} | |
| 131 do_test avtrans-3.8 { | |
| 132 catchsql { | |
| 133 SELECT a FROM two ORDER BY a; | |
| 134 } altdb | |
| 135 } {0 {1 5 10}} | |
| 136 do_test avtrans-3.9 { | |
| 137 catchsql { | |
| 138 SELECT a FROM one ORDER BY a; | |
| 139 } altdb | |
| 140 } {0 {1 2 3}} | |
| 141 do_test avtrans-3.10 { | |
| 142 execsql {END TRANSACTION} | |
| 143 } {} | |
| 144 do_test avtrans-3.11 { | |
| 145 set v [catch {execsql { | |
| 146 SELECT a FROM two ORDER BY a; | |
| 147 } altdb} msg] | |
| 148 lappend v $msg | |
| 149 } {0 {1 4 5 10}} | |
| 150 do_test avtrans-3.12 { | |
| 151 set v [catch {execsql { | |
| 152 SELECT a FROM one ORDER BY a; | |
| 153 } altdb} msg] | |
| 154 lappend v $msg | |
| 155 } {0 {1 2 3 4}} | |
| 156 do_test avtrans-3.13 { | |
| 157 set v [catch {execsql { | |
| 158 SELECT a FROM two ORDER BY a; | |
| 159 } db} msg] | |
| 160 lappend v $msg | |
| 161 } {0 {1 4 5 10}} | |
| 162 do_test avtrans-3.14 { | |
| 163 set v [catch {execsql { | |
| 164 SELECT a FROM one ORDER BY a; | |
| 165 } db} msg] | |
| 166 lappend v $msg | |
| 167 } {0 {1 2 3 4}} | |
| 168 sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit) | |
| 169 integrity_check avtrans-3.15 | |
| 170 | |
| 171 do_test avtrans-4.1 { | |
| 172 set v [catch {execsql { | |
| 173 COMMIT; | |
| 174 } db} msg] | |
| 175 lappend v $msg | |
| 176 } {1 {cannot commit - no transaction is active}} | |
| 177 do_test avtrans-4.2 { | |
| 178 set v [catch {execsql { | |
| 179 ROLLBACK; | |
| 180 } db} msg] | |
| 181 lappend v $msg | |
| 182 } {1 {cannot rollback - no transaction is active}} | |
| 183 do_test avtrans-4.3 { | |
| 184 catchsql { | |
| 185 BEGIN TRANSACTION; | |
| 186 UPDATE two SET a = 0 WHERE 0; | |
| 187 SELECT a FROM two ORDER BY a; | |
| 188 } db | |
| 189 } {0 {1 4 5 10}} | |
| 190 do_test avtrans-4.4 { | |
| 191 catchsql { | |
| 192 SELECT a FROM two ORDER BY a; | |
| 193 } altdb | |
| 194 } {0 {1 4 5 10}} | |
| 195 do_test avtrans-4.5 { | |
| 196 catchsql { | |
| 197 SELECT a FROM one ORDER BY a; | |
| 198 } altdb | |
| 199 } {0 {1 2 3 4}} | |
| 200 do_test avtrans-4.6 { | |
| 201 catchsql { | |
| 202 BEGIN TRANSACTION; | |
| 203 SELECT a FROM one ORDER BY a; | |
| 204 } db | |
| 205 } {1 {cannot start a transaction within a transaction}} | |
| 206 do_test avtrans-4.7 { | |
| 207 catchsql { | |
| 208 SELECT a FROM two ORDER BY a; | |
| 209 } altdb | |
| 210 } {0 {1 4 5 10}} | |
| 211 do_test avtrans-4.8 { | |
| 212 catchsql { | |
| 213 SELECT a FROM one ORDER BY a; | |
| 214 } altdb | |
| 215 } {0 {1 2 3 4}} | |
| 216 do_test avtrans-4.9 { | |
| 217 set v [catch {execsql { | |
| 218 END TRANSACTION; | |
| 219 SELECT a FROM two ORDER BY a; | |
| 220 } db} msg] | |
| 221 lappend v $msg | |
| 222 } {0 {1 4 5 10}} | |
| 223 do_test avtrans-4.10 { | |
| 224 set v [catch {execsql { | |
| 225 SELECT a FROM two ORDER BY a; | |
| 226 } altdb} msg] | |
| 227 lappend v $msg | |
| 228 } {0 {1 4 5 10}} | |
| 229 do_test avtrans-4.11 { | |
| 230 set v [catch {execsql { | |
| 231 SELECT a FROM one ORDER BY a; | |
| 232 } altdb} msg] | |
| 233 lappend v $msg | |
| 234 } {0 {1 2 3 4}} | |
| 235 integrity_check avtrans-4.12 | |
| 236 do_test avtrans-4.98 { | |
| 237 altdb close | |
| 238 execsql { | |
| 239 DROP TABLE one; | |
| 240 DROP TABLE two; | |
| 241 } | |
| 242 } {} | |
| 243 integrity_check avtrans-4.99 | |
| 244 | |
| 245 # Check out the commit/rollback behavior of the database | |
| 246 # | |
| 247 do_test avtrans-5.1 { | |
| 248 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} | |
| 249 } {} | |
| 250 do_test avtrans-5.2 { | |
| 251 execsql {BEGIN TRANSACTION} | |
| 252 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} | |
| 253 } {} | |
| 254 do_test avtrans-5.3 { | |
| 255 execsql {CREATE TABLE one(a text, b int)} | |
| 256 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} | |
| 257 } {one} | |
| 258 do_test avtrans-5.4 { | |
| 259 execsql {SELECT a,b FROM one ORDER BY b} | |
| 260 } {} | |
| 261 do_test avtrans-5.5 { | |
| 262 execsql {INSERT INTO one(a,b) VALUES('hello', 1)} | |
| 263 execsql {SELECT a,b FROM one ORDER BY b} | |
| 264 } {hello 1} | |
| 265 do_test avtrans-5.6 { | |
| 266 execsql {ROLLBACK} | |
| 267 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} | |
| 268 } {} | |
| 269 do_test avtrans-5.7 { | |
| 270 set v [catch { | |
| 271 execsql {SELECT a,b FROM one ORDER BY b} | |
| 272 } msg] | |
| 273 lappend v $msg | |
| 274 } {1 {no such table: one}} | |
| 275 | |
| 276 # Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs | |
| 277 # DROP TABLEs and DROP INDEXs | |
| 278 # | |
| 279 do_test avtrans-5.8 { | |
| 280 execsql { | |
| 281 SELECT name fROM sqlite_master | |
| 282 WHERE type='table' OR type='index' | |
| 283 ORDER BY name | |
| 284 } | |
| 285 } {} | |
| 286 do_test avtrans-5.9 { | |
| 287 execsql { | |
| 288 BEGIN TRANSACTION; | |
| 289 CREATE TABLE t1(a int, b int, c int); | |
| 290 SELECT name fROM sqlite_master | |
| 291 WHERE type='table' OR type='index' | |
| 292 ORDER BY name; | |
| 293 } | |
| 294 } {t1} | |
| 295 do_test avtrans-5.10 { | |
| 296 execsql { | |
| 297 CREATE INDEX i1 ON t1(a); | |
| 298 SELECT name fROM sqlite_master | |
| 299 WHERE type='table' OR type='index' | |
| 300 ORDER BY name; | |
| 301 } | |
| 302 } {i1 t1} | |
| 303 do_test avtrans-5.11 { | |
| 304 execsql { | |
| 305 COMMIT; | |
| 306 SELECT name fROM sqlite_master | |
| 307 WHERE type='table' OR type='index' | |
| 308 ORDER BY name; | |
| 309 } | |
| 310 } {i1 t1} | |
| 311 do_test avtrans-5.12 { | |
| 312 execsql { | |
| 313 BEGIN TRANSACTION; | |
| 314 CREATE TABLE t2(a int, b int, c int); | |
| 315 CREATE INDEX i2a ON t2(a); | |
| 316 CREATE INDEX i2b ON t2(b); | |
| 317 DROP TABLE t1; | |
| 318 SELECT name fROM sqlite_master | |
| 319 WHERE type='table' OR type='index' | |
| 320 ORDER BY name; | |
| 321 } | |
| 322 } {i2a i2b t2} | |
| 323 do_test avtrans-5.13 { | |
| 324 execsql { | |
| 325 ROLLBACK; | |
| 326 SELECT name fROM sqlite_master | |
| 327 WHERE type='table' OR type='index' | |
| 328 ORDER BY name; | |
| 329 } | |
| 330 } {i1 t1} | |
| 331 do_test avtrans-5.14 { | |
| 332 execsql { | |
| 333 BEGIN TRANSACTION; | |
| 334 DROP INDEX i1; | |
| 335 SELECT name fROM sqlite_master | |
| 336 WHERE type='table' OR type='index' | |
| 337 ORDER BY name; | |
| 338 } | |
| 339 } {t1} | |
| 340 do_test avtrans-5.15 { | |
| 341 execsql { | |
| 342 ROLLBACK; | |
| 343 SELECT name fROM sqlite_master | |
| 344 WHERE type='table' OR type='index' | |
| 345 ORDER BY name; | |
| 346 } | |
| 347 } {i1 t1} | |
| 348 do_test avtrans-5.16 { | |
| 349 execsql { | |
| 350 BEGIN TRANSACTION; | |
| 351 DROP INDEX i1; | |
| 352 CREATE TABLE t2(x int, y int, z int); | |
| 353 CREATE INDEX i2x ON t2(x); | |
| 354 CREATE INDEX i2y ON t2(y); | |
| 355 INSERT INTO t2 VALUES(1,2,3); | |
| 356 SELECT name fROM sqlite_master | |
| 357 WHERE type='table' OR type='index' | |
| 358 ORDER BY name; | |
| 359 } | |
| 360 } {i2x i2y t1 t2} | |
| 361 do_test avtrans-5.17 { | |
| 362 execsql { | |
| 363 COMMIT; | |
| 364 SELECT name fROM sqlite_master | |
| 365 WHERE type='table' OR type='index' | |
| 366 ORDER BY name; | |
| 367 } | |
| 368 } {i2x i2y t1 t2} | |
| 369 do_test avtrans-5.18 { | |
| 370 execsql { | |
| 371 SELECT * FROM t2; | |
| 372 } | |
| 373 } {1 2 3} | |
| 374 do_test avtrans-5.19 { | |
| 375 execsql { | |
| 376 SELECT x FROM t2 WHERE y=2; | |
| 377 } | |
| 378 } {1} | |
| 379 do_test avtrans-5.20 { | |
| 380 execsql { | |
| 381 BEGIN TRANSACTION; | |
| 382 DROP TABLE t1; | |
| 383 DROP TABLE t2; | |
| 384 SELECT name fROM sqlite_master | |
| 385 WHERE type='table' OR type='index' | |
| 386 ORDER BY name; | |
| 387 } | |
| 388 } {} | |
| 389 do_test avtrans-5.21 { | |
| 390 set r [catch {execsql { | |
| 391 SELECT * FROM t2 | |
| 392 }} msg] | |
| 393 lappend r $msg | |
| 394 } {1 {no such table: t2}} | |
| 395 do_test avtrans-5.22 { | |
| 396 execsql { | |
| 397 ROLLBACK; | |
| 398 SELECT name fROM sqlite_master | |
| 399 WHERE type='table' OR type='index' | |
| 400 ORDER BY name; | |
| 401 } | |
| 402 } {i2x i2y t1 t2} | |
| 403 do_test avtrans-5.23 { | |
| 404 execsql { | |
| 405 SELECT * FROM t2; | |
| 406 } | |
| 407 } {1 2 3} | |
| 408 integrity_check avtrans-5.23 | |
| 409 | |
| 410 | |
| 411 # Try to DROP and CREATE tables and indices with the same name | |
| 412 # within a transaction. Make sure ROLLBACK works. | |
| 413 # | |
| 414 do_test avtrans-6.1 { | |
| 415 execsql2 { | |
| 416 INSERT INTO t1 VALUES(1,2,3); | |
| 417 BEGIN TRANSACTION; | |
| 418 DROP TABLE t1; | |
| 419 CREATE TABLE t1(p,q,r); | |
| 420 ROLLBACK; | |
| 421 SELECT * FROM t1; | |
| 422 } | |
| 423 } {a 1 b 2 c 3} | |
| 424 do_test avtrans-6.2 { | |
| 425 execsql2 { | |
| 426 INSERT INTO t1 VALUES(1,2,3); | |
| 427 BEGIN TRANSACTION; | |
| 428 DROP TABLE t1; | |
| 429 CREATE TABLE t1(p,q,r); | |
| 430 COMMIT; | |
| 431 SELECT * FROM t1; | |
| 432 } | |
| 433 } {} | |
| 434 do_test avtrans-6.3 { | |
| 435 execsql2 { | |
| 436 INSERT INTO t1 VALUES(1,2,3); | |
| 437 SELECT * FROM t1; | |
| 438 } | |
| 439 } {p 1 q 2 r 3} | |
| 440 do_test avtrans-6.4 { | |
| 441 execsql2 { | |
| 442 BEGIN TRANSACTION; | |
| 443 DROP TABLE t1; | |
| 444 CREATE TABLE t1(a,b,c); | |
| 445 INSERT INTO t1 VALUES(4,5,6); | |
| 446 SELECT * FROM t1; | |
| 447 DROP TABLE t1; | |
| 448 } | |
| 449 } {a 4 b 5 c 6} | |
| 450 do_test avtrans-6.5 { | |
| 451 execsql2 { | |
| 452 ROLLBACK; | |
| 453 SELECT * FROM t1; | |
| 454 } | |
| 455 } {p 1 q 2 r 3} | |
| 456 do_test avtrans-6.6 { | |
| 457 execsql2 { | |
| 458 BEGIN TRANSACTION; | |
| 459 DROP TABLE t1; | |
| 460 CREATE TABLE t1(a,b,c); | |
| 461 INSERT INTO t1 VALUES(4,5,6); | |
| 462 SELECT * FROM t1; | |
| 463 DROP TABLE t1; | |
| 464 } | |
| 465 } {a 4 b 5 c 6} | |
| 466 do_test avtrans-6.7 { | |
| 467 catchsql { | |
| 468 COMMIT; | |
| 469 SELECT * FROM t1; | |
| 470 } | |
| 471 } {1 {no such table: t1}} | |
| 472 | |
| 473 # Repeat on a table with an automatically generated index. | |
| 474 # | |
| 475 do_test avtrans-6.10 { | |
| 476 execsql2 { | |
| 477 CREATE TABLE t1(a unique,b,c); | |
| 478 INSERT INTO t1 VALUES(1,2,3); | |
| 479 BEGIN TRANSACTION; | |
| 480 DROP TABLE t1; | |
| 481 CREATE TABLE t1(p unique,q,r); | |
| 482 ROLLBACK; | |
| 483 SELECT * FROM t1; | |
| 484 } | |
| 485 } {a 1 b 2 c 3} | |
| 486 do_test avtrans-6.11 { | |
| 487 execsql2 { | |
| 488 BEGIN TRANSACTION; | |
| 489 DROP TABLE t1; | |
| 490 CREATE TABLE t1(p unique,q,r); | |
| 491 COMMIT; | |
| 492 SELECT * FROM t1; | |
| 493 } | |
| 494 } {} | |
| 495 do_test avtrans-6.12 { | |
| 496 execsql2 { | |
| 497 INSERT INTO t1 VALUES(1,2,3); | |
| 498 SELECT * FROM t1; | |
| 499 } | |
| 500 } {p 1 q 2 r 3} | |
| 501 do_test avtrans-6.13 { | |
| 502 execsql2 { | |
| 503 BEGIN TRANSACTION; | |
| 504 DROP TABLE t1; | |
| 505 CREATE TABLE t1(a unique,b,c); | |
| 506 INSERT INTO t1 VALUES(4,5,6); | |
| 507 SELECT * FROM t1; | |
| 508 DROP TABLE t1; | |
| 509 } | |
| 510 } {a 4 b 5 c 6} | |
| 511 do_test avtrans-6.14 { | |
| 512 execsql2 { | |
| 513 ROLLBACK; | |
| 514 SELECT * FROM t1; | |
| 515 } | |
| 516 } {p 1 q 2 r 3} | |
| 517 do_test avtrans-6.15 { | |
| 518 execsql2 { | |
| 519 BEGIN TRANSACTION; | |
| 520 DROP TABLE t1; | |
| 521 CREATE TABLE t1(a unique,b,c); | |
| 522 INSERT INTO t1 VALUES(4,5,6); | |
| 523 SELECT * FROM t1; | |
| 524 DROP TABLE t1; | |
| 525 } | |
| 526 } {a 4 b 5 c 6} | |
| 527 do_test avtrans-6.16 { | |
| 528 catchsql { | |
| 529 COMMIT; | |
| 530 SELECT * FROM t1; | |
| 531 } | |
| 532 } {1 {no such table: t1}} | |
| 533 | |
| 534 do_test avtrans-6.20 { | |
| 535 execsql { | |
| 536 CREATE TABLE t1(a integer primary key,b,c); | |
| 537 INSERT INTO t1 VALUES(1,-2,-3); | |
| 538 INSERT INTO t1 VALUES(4,-5,-6); | |
| 539 SELECT * FROM t1; | |
| 540 } | |
| 541 } {1 -2 -3 4 -5 -6} | |
| 542 do_test avtrans-6.21 { | |
| 543 execsql { | |
| 544 CREATE INDEX i1 ON t1(b); | |
| 545 SELECT * FROM t1 WHERE b<1; | |
| 546 } | |
| 547 } {4 -5 -6 1 -2 -3} | |
| 548 do_test avtrans-6.22 { | |
| 549 execsql { | |
| 550 BEGIN TRANSACTION; | |
| 551 DROP INDEX i1; | |
| 552 SELECT * FROM t1 WHERE b<1; | |
| 553 ROLLBACK; | |
| 554 } | |
| 555 } {1 -2 -3 4 -5 -6} | |
| 556 do_test avtrans-6.23 { | |
| 557 execsql { | |
| 558 SELECT * FROM t1 WHERE b<1; | |
| 559 } | |
| 560 } {4 -5 -6 1 -2 -3} | |
| 561 do_test avtrans-6.24 { | |
| 562 execsql { | |
| 563 BEGIN TRANSACTION; | |
| 564 DROP TABLE t1; | |
| 565 ROLLBACK; | |
| 566 SELECT * FROM t1 WHERE b<1; | |
| 567 } | |
| 568 } {4 -5 -6 1 -2 -3} | |
| 569 | |
| 570 do_test avtrans-6.25 { | |
| 571 execsql { | |
| 572 BEGIN TRANSACTION; | |
| 573 DROP INDEX i1; | |
| 574 CREATE INDEX i1 ON t1(c); | |
| 575 SELECT * FROM t1 WHERE b<1; | |
| 576 } | |
| 577 } {1 -2 -3 4 -5 -6} | |
| 578 do_test avtrans-6.26 { | |
| 579 execsql { | |
| 580 SELECT * FROM t1 WHERE c<1; | |
| 581 } | |
| 582 } {4 -5 -6 1 -2 -3} | |
| 583 do_test avtrans-6.27 { | |
| 584 execsql { | |
| 585 ROLLBACK; | |
| 586 SELECT * FROM t1 WHERE b<1; | |
| 587 } | |
| 588 } {4 -5 -6 1 -2 -3} | |
| 589 do_test avtrans-6.28 { | |
| 590 execsql { | |
| 591 SELECT * FROM t1 WHERE c<1; | |
| 592 } | |
| 593 } {1 -2 -3 4 -5 -6} | |
| 594 | |
| 595 # The following repeats steps 6.20 through 6.28, but puts a "unique" | |
| 596 # constraint the first field of the table in order to generate an | |
| 597 # automatic index. | |
| 598 # | |
| 599 do_test avtrans-6.30 { | |
| 600 execsql { | |
| 601 BEGIN TRANSACTION; | |
| 602 DROP TABLE t1; | |
| 603 CREATE TABLE t1(a int unique,b,c); | |
| 604 COMMIT; | |
| 605 INSERT INTO t1 VALUES(1,-2,-3); | |
| 606 INSERT INTO t1 VALUES(4,-5,-6); | |
| 607 SELECT * FROM t1 ORDER BY a; | |
| 608 } | |
| 609 } {1 -2 -3 4 -5 -6} | |
| 610 do_test avtrans-6.31 { | |
| 611 execsql { | |
| 612 CREATE INDEX i1 ON t1(b); | |
| 613 SELECT * FROM t1 WHERE b<1; | |
| 614 } | |
| 615 } {4 -5 -6 1 -2 -3} | |
| 616 do_test avtrans-6.32 { | |
| 617 execsql { | |
| 618 BEGIN TRANSACTION; | |
| 619 DROP INDEX i1; | |
| 620 SELECT * FROM t1 WHERE b<1; | |
| 621 ROLLBACK; | |
| 622 } | |
| 623 } {1 -2 -3 4 -5 -6} | |
| 624 do_test avtrans-6.33 { | |
| 625 execsql { | |
| 626 SELECT * FROM t1 WHERE b<1; | |
| 627 } | |
| 628 } {4 -5 -6 1 -2 -3} | |
| 629 do_test avtrans-6.34 { | |
| 630 execsql { | |
| 631 BEGIN TRANSACTION; | |
| 632 DROP TABLE t1; | |
| 633 ROLLBACK; | |
| 634 SELECT * FROM t1 WHERE b<1; | |
| 635 } | |
| 636 } {4 -5 -6 1 -2 -3} | |
| 637 | |
| 638 do_test avtrans-6.35 { | |
| 639 execsql { | |
| 640 BEGIN TRANSACTION; | |
| 641 DROP INDEX i1; | |
| 642 CREATE INDEX i1 ON t1(c); | |
| 643 SELECT * FROM t1 WHERE b<1; | |
| 644 } | |
| 645 } {1 -2 -3 4 -5 -6} | |
| 646 do_test avtrans-6.36 { | |
| 647 execsql { | |
| 648 SELECT * FROM t1 WHERE c<1; | |
| 649 } | |
| 650 } {4 -5 -6 1 -2 -3} | |
| 651 do_test avtrans-6.37 { | |
| 652 execsql { | |
| 653 DROP INDEX i1; | |
| 654 SELECT * FROM t1 WHERE c<1; | |
| 655 } | |
| 656 } {1 -2 -3 4 -5 -6} | |
| 657 do_test avtrans-6.38 { | |
| 658 execsql { | |
| 659 ROLLBACK; | |
| 660 SELECT * FROM t1 WHERE b<1; | |
| 661 } | |
| 662 } {4 -5 -6 1 -2 -3} | |
| 663 do_test avtrans-6.39 { | |
| 664 execsql { | |
| 665 SELECT * FROM t1 WHERE c<1; | |
| 666 } | |
| 667 } {1 -2 -3 4 -5 -6} | |
| 668 integrity_check avtrans-6.40 | |
| 669 | |
| 670 ifcapable !floatingpoint { | |
| 671 finish_test | |
| 672 return | |
| 673 } | |
| 674 | |
| 675 # Test to make sure rollback restores the database back to its original | |
| 676 # state. | |
| 677 # | |
| 678 do_test avtrans-7.1 { | |
| 679 execsql {BEGIN} | |
| 680 for {set i 0} {$i<1000} {incr i} { | |
| 681 set r1 [expr {rand()}] | |
| 682 set r2 [expr {rand()}] | |
| 683 set r3 [expr {rand()}] | |
| 684 execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)" | |
| 685 } | |
| 686 execsql {COMMIT} | |
| 687 set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}] | |
| 688 set ::checksum2 [ | |
| 689 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} | |
| 690 ] | |
| 691 execsql {SELECT count(*) FROM t2} | |
| 692 } {1001} | |
| 693 do_test avtrans-7.2 { | |
| 694 execsql {SELECT md5sum(x,y,z) FROM t2} | |
| 695 } $checksum | |
| 696 do_test avtrans-7.2.1 { | |
| 697 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} | |
| 698 } $checksum2 | |
| 699 do_test avtrans-7.3 { | |
| 700 execsql { | |
| 701 BEGIN; | |
| 702 DELETE FROM t2; | |
| 703 ROLLBACK; | |
| 704 SELECT md5sum(x,y,z) FROM t2; | |
| 705 } | |
| 706 } $checksum | |
| 707 do_test avtrans-7.4 { | |
| 708 execsql { | |
| 709 BEGIN; | |
| 710 INSERT INTO t2 SELECT * FROM t2; | |
| 711 ROLLBACK; | |
| 712 SELECT md5sum(x,y,z) FROM t2; | |
| 713 } | |
| 714 } $checksum | |
| 715 do_test avtrans-7.5 { | |
| 716 execsql { | |
| 717 BEGIN; | |
| 718 DELETE FROM t2; | |
| 719 ROLLBACK; | |
| 720 SELECT md5sum(x,y,z) FROM t2; | |
| 721 } | |
| 722 } $checksum | |
| 723 do_test avtrans-7.6 { | |
| 724 execsql { | |
| 725 BEGIN; | |
| 726 INSERT INTO t2 SELECT * FROM t2; | |
| 727 ROLLBACK; | |
| 728 SELECT md5sum(x,y,z) FROM t2; | |
| 729 } | |
| 730 } $checksum | |
| 731 do_test avtrans-7.7 { | |
| 732 execsql { | |
| 733 BEGIN; | |
| 734 CREATE TABLE t3 AS SELECT * FROM t2; | |
| 735 INSERT INTO t2 SELECT * FROM t3; | |
| 736 ROLLBACK; | |
| 737 SELECT md5sum(x,y,z) FROM t2; | |
| 738 } | |
| 739 } $checksum | |
| 740 do_test avtrans-7.8 { | |
| 741 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} | |
| 742 } $checksum2 | |
| 743 ifcapable tempdb { | |
| 744 do_test avtrans-7.9 { | |
| 745 execsql { | |
| 746 BEGIN; | |
| 747 CREATE TEMP TABLE t3 AS SELECT * FROM t2; | |
| 748 INSERT INTO t2 SELECT * FROM t3; | |
| 749 ROLLBACK; | |
| 750 SELECT md5sum(x,y,z) FROM t2; | |
| 751 } | |
| 752 } $checksum | |
| 753 } | |
| 754 do_test avtrans-7.10 { | |
| 755 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} | |
| 756 } $checksum2 | |
| 757 ifcapable tempdb { | |
| 758 do_test avtrans-7.11 { | |
| 759 execsql { | |
| 760 BEGIN; | |
| 761 CREATE TEMP TABLE t3 AS SELECT * FROM t2; | |
| 762 INSERT INTO t2 SELECT * FROM t3; | |
| 763 DROP INDEX i2x; | |
| 764 DROP INDEX i2y; | |
| 765 CREATE INDEX i3a ON t3(x); | |
| 766 ROLLBACK; | |
| 767 SELECT md5sum(x,y,z) FROM t2; | |
| 768 } | |
| 769 } $checksum | |
| 770 } | |
| 771 do_test avtrans-7.12 { | |
| 772 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} | |
| 773 } $checksum2 | |
| 774 ifcapable tempdb { | |
| 775 do_test avtrans-7.13 { | |
| 776 execsql { | |
| 777 BEGIN; | |
| 778 DROP TABLE t2; | |
| 779 ROLLBACK; | |
| 780 SELECT md5sum(x,y,z) FROM t2; | |
| 781 } | |
| 782 } $checksum | |
| 783 } | |
| 784 do_test avtrans-7.14 { | |
| 785 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} | |
| 786 } $checksum2 | |
| 787 integrity_check avtrans-7.15 | |
| 788 | |
| 789 # Arrange for another process to begin modifying the database but abort | |
| 790 # and die in the middle of the modification. Then have this process read | |
| 791 # the database. This process should detect the journal file and roll it | |
| 792 # back. Verify that this happens correctly. | |
| 793 # | |
| 794 set fd [open test.tcl w] | |
| 795 puts $fd { | |
| 796 sqlite3 db test.db | |
| 797 db eval { | |
| 798 PRAGMA default_cache_size=20; | |
| 799 BEGIN; | |
| 800 CREATE TABLE t3 AS SELECT * FROM t2; | |
| 801 DELETE FROM t2; | |
| 802 } | |
| 803 sqlite_abort | |
| 804 } | |
| 805 close $fd | |
| 806 do_test avtrans-8.1 { | |
| 807 catch {exec [info nameofexec] test.tcl} | |
| 808 execsql {SELECT md5sum(x,y,z) FROM t2} | |
| 809 } $checksum | |
| 810 do_test avtrans-8.2 { | |
| 811 execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master} | |
| 812 } $checksum2 | |
| 813 integrity_check avtrans-8.3 | |
| 814 | |
| 815 # In the following sequence of tests, compute the MD5 sum of the content | |
| 816 # of a table, make lots of modifications to that table, then do a rollback. | |
| 817 # Verify that after the rollback, the MD5 checksum is unchanged. | |
| 818 # | |
| 819 do_test avtrans-9.1 { | |
| 820 execsql { | |
| 821 PRAGMA default_cache_size=10; | |
| 822 } | |
| 823 db close | |
| 824 sqlite3 db test.db | |
| 825 execsql { | |
| 826 BEGIN; | |
| 827 CREATE TABLE t3(x TEXT); | |
| 828 INSERT INTO t3 VALUES(randstr(10,400)); | |
| 829 INSERT INTO t3 VALUES(randstr(10,400)); | |
| 830 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 831 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 832 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 833 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 834 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 835 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 836 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 837 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 838 INSERT INTO t3 SELECT randstr(10,400) FROM t3; | |
| 839 COMMIT; | |
| 840 SELECT count(*) FROM t3; | |
| 841 } | |
| 842 } {1024} | |
| 843 | |
| 844 # The following procedure computes a "signature" for table "t3". If | |
| 845 # T3 changes in any way, the signature should change. | |
| 846 # | |
| 847 # This is used to test ROLLBACK. We gather a signature for t3, then | |
| 848 # make lots of changes to t3, then rollback and take another signature. | |
| 849 # The two signatures should be the same. | |
| 850 # | |
| 851 proc signature {} { | |
| 852 return [db eval {SELECT count(*), md5sum(x) FROM t3}] | |
| 853 } | |
| 854 | |
| 855 # Repeat the following group of tests 20 times for quick testing and | |
| 856 # 40 times for full testing. Each iteration of the test makes table | |
| 857 # t3 a little larger, and thus takes a little longer, so doing 40 tests | |
| 858 # is more than 2.0 times slower than doing 20 tests. Considerably more. | |
| 859 # | |
| 860 if {[info exists G(isquick)]} { | |
| 861 set limit 20 | |
| 862 } else { | |
| 863 set limit 40 | |
| 864 } | |
| 865 | |
| 866 # Do rollbacks. Make sure the signature does not change. | |
| 867 # | |
| 868 for {set i 2} {$i<=$limit} {incr i} { | |
| 869 set ::sig [signature] | |
| 870 set cnt [lindex $::sig 0] | |
| 871 if {$i%2==0} { | |
| 872 execsql {PRAGMA fullfsync=ON} | |
| 873 } else { | |
| 874 execsql {PRAGMA fullfsync=OFF} | |
| 875 } | |
| 876 set sqlite_sync_count 0 | |
| 877 set sqlite_fullsync_count 0 | |
| 878 do_test avtrans-9.$i.1-$cnt { | |
| 879 execsql { | |
| 880 BEGIN; | |
| 881 DELETE FROM t3 WHERE random()%10!=0; | |
| 882 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; | |
| 883 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; | |
| 884 ROLLBACK; | |
| 885 } | |
| 886 signature | |
| 887 } $sig | |
| 888 do_test avtrans-9.$i.2-$cnt { | |
| 889 execsql { | |
| 890 BEGIN; | |
| 891 DELETE FROM t3 WHERE random()%10!=0; | |
| 892 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; | |
| 893 DELETE FROM t3 WHERE random()%10!=0; | |
| 894 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; | |
| 895 ROLLBACK; | |
| 896 } | |
| 897 signature | |
| 898 } $sig | |
| 899 if {$i<$limit} { | |
| 900 do_test avtrans-9.$i.3-$cnt { | |
| 901 execsql { | |
| 902 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; | |
| 903 } | |
| 904 } {} | |
| 905 if {$tcl_platform(platform)=="unix"} { | |
| 906 do_test avtrans-9.$i.4-$cnt { | |
| 907 expr {$sqlite_sync_count>0} | |
| 908 } 1 | |
| 909 ifcapable pager_pragmas { | |
| 910 do_test avtrans-9.$i.5-$cnt { | |
| 911 expr {$sqlite_fullsync_count>0} | |
| 912 } [expr {$i%2==0}] | |
| 913 } else { | |
| 914 do_test avtrans-9.$i.5-$cnt { | |
| 915 expr {$sqlite_fullsync_count==0} | |
| 916 } {1} | |
| 917 } | |
| 918 } | |
| 919 wal_check_journal_mode avtrans-9.$i-6.$cnt | |
| 920 } | |
| 921 set ::pager_old_format 0 | |
| 922 } | |
| 923 integrity_check avtrans-10.1 | |
| 924 wal_check_journal_mode avtrans-10.2 | |
| 925 | |
| 926 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
