Annotation of embedaddon/sqlite3/test/avtrans.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to avtrans.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test