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