Annotation of embedaddon/sqlite3/test/savepoint.test, revision 1.1
1.1 ! misho 1: # 2008 December 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: #
! 12: # $Id: savepoint.test,v 1.13 2009/07/18 08:30:45 danielk1977 Exp $
! 13:
! 14: set testdir [file dirname $argv0]
! 15: source $testdir/tester.tcl
! 16: source $testdir/lock_common.tcl
! 17: source $testdir/malloc_common.tcl
! 18:
! 19: #----------------------------------------------------------------------
! 20: # The following tests - savepoint-1.* - test that the SAVEPOINT, RELEASE
! 21: # and ROLLBACK TO comands are correctly parsed, and that the auto-commit
! 22: # flag is correctly set and unset as a result.
! 23: #
! 24: do_test savepoint-1.1 {
! 25: wal_set_journal_mode
! 26: execsql {
! 27: SAVEPOINT sp1;
! 28: RELEASE sp1;
! 29: }
! 30: } {}
! 31: do_test savepoint-1.2 {
! 32: execsql {
! 33: SAVEPOINT sp1;
! 34: ROLLBACK TO sp1;
! 35: }
! 36: } {}
! 37: do_test savepoint-1.3 {
! 38: execsql { SAVEPOINT sp1 }
! 39: db close
! 40: } {}
! 41: sqlite3 db test.db
! 42: do_test savepoint-1.4.1 {
! 43: execsql {
! 44: SAVEPOINT sp1;
! 45: SAVEPOINT sp2;
! 46: RELEASE sp1;
! 47: }
! 48: sqlite3_get_autocommit db
! 49: } {1}
! 50: do_test savepoint-1.4.2 {
! 51: execsql {
! 52: SAVEPOINT sp1;
! 53: SAVEPOINT sp2;
! 54: RELEASE sp2;
! 55: }
! 56: sqlite3_get_autocommit db
! 57: } {0}
! 58: do_test savepoint-1.4.3 {
! 59: execsql { RELEASE sp1 }
! 60: sqlite3_get_autocommit db
! 61: } {1}
! 62: do_test savepoint-1.4.4 {
! 63: execsql {
! 64: SAVEPOINT sp1;
! 65: SAVEPOINT sp2;
! 66: ROLLBACK TO sp1;
! 67: }
! 68: sqlite3_get_autocommit db
! 69: } {0}
! 70: do_test savepoint-1.4.5 {
! 71: execsql { RELEASE SAVEPOINT sp1 }
! 72: sqlite3_get_autocommit db
! 73: } {1}
! 74: do_test savepoint-1.4.6 {
! 75: execsql {
! 76: SAVEPOINT sp1;
! 77: SAVEPOINT sp2;
! 78: SAVEPOINT sp3;
! 79: ROLLBACK TO SAVEPOINT sp3;
! 80: ROLLBACK TRANSACTION TO sp2;
! 81: ROLLBACK TRANSACTION TO SAVEPOINT sp1;
! 82: }
! 83: sqlite3_get_autocommit db
! 84: } {0}
! 85: do_test savepoint-1.4.7 {
! 86: execsql { RELEASE SAVEPOINT SP1 }
! 87: sqlite3_get_autocommit db
! 88: } {1}
! 89: do_test savepoint-1.5 {
! 90: execsql {
! 91: SAVEPOINT sp1;
! 92: ROLLBACK TO sp1;
! 93: }
! 94: } {}
! 95: do_test savepoint-1.6 {
! 96: execsql COMMIT
! 97: } {}
! 98: wal_check_journal_mode savepoint-1.7
! 99:
! 100: #------------------------------------------------------------------------
! 101: # These tests - savepoint-2.* - test rollbacks and releases of savepoints
! 102: # with a very simple data set.
! 103: #
! 104:
! 105: do_test savepoint-2.1 {
! 106: execsql {
! 107: CREATE TABLE t1(a, b, c);
! 108: BEGIN;
! 109: INSERT INTO t1 VALUES(1, 2, 3);
! 110: SAVEPOINT one;
! 111: UPDATE t1 SET a = 2, b = 3, c = 4;
! 112: }
! 113: execsql { SELECT * FROM t1 }
! 114: } {2 3 4}
! 115: do_test savepoint-2.2 {
! 116: execsql {
! 117: ROLLBACK TO one;
! 118: }
! 119: execsql { SELECT * FROM t1 }
! 120: } {1 2 3}
! 121: do_test savepoint-2.3 {
! 122: execsql {
! 123: INSERT INTO t1 VALUES(4, 5, 6);
! 124: }
! 125: execsql { SELECT * FROM t1 }
! 126: } {1 2 3 4 5 6}
! 127: do_test savepoint-2.4 {
! 128: execsql {
! 129: ROLLBACK TO one;
! 130: }
! 131: execsql { SELECT * FROM t1 }
! 132: } {1 2 3}
! 133:
! 134:
! 135: do_test savepoint-2.5 {
! 136: execsql {
! 137: INSERT INTO t1 VALUES(7, 8, 9);
! 138: SAVEPOINT two;
! 139: INSERT INTO t1 VALUES(10, 11, 12);
! 140: }
! 141: execsql { SELECT * FROM t1 }
! 142: } {1 2 3 7 8 9 10 11 12}
! 143: do_test savepoint-2.6 {
! 144: execsql {
! 145: ROLLBACK TO two;
! 146: }
! 147: execsql { SELECT * FROM t1 }
! 148: } {1 2 3 7 8 9}
! 149: do_test savepoint-2.7 {
! 150: execsql {
! 151: INSERT INTO t1 VALUES(10, 11, 12);
! 152: }
! 153: execsql { SELECT * FROM t1 }
! 154: } {1 2 3 7 8 9 10 11 12}
! 155: do_test savepoint-2.8 {
! 156: execsql {
! 157: ROLLBACK TO one;
! 158: }
! 159: execsql { SELECT * FROM t1 }
! 160: } {1 2 3}
! 161: do_test savepoint-2.9 {
! 162: execsql {
! 163: INSERT INTO t1 VALUES('a', 'b', 'c');
! 164: SAVEPOINT two;
! 165: INSERT INTO t1 VALUES('d', 'e', 'f');
! 166: }
! 167: execsql { SELECT * FROM t1 }
! 168: } {1 2 3 a b c d e f}
! 169: do_test savepoint-2.10 {
! 170: execsql {
! 171: RELEASE two;
! 172: }
! 173: execsql { SELECT * FROM t1 }
! 174: } {1 2 3 a b c d e f}
! 175: do_test savepoint-2.11 {
! 176: execsql {
! 177: ROLLBACK;
! 178: }
! 179: execsql { SELECT * FROM t1 }
! 180: } {}
! 181: wal_check_journal_mode savepoint-2.12
! 182:
! 183: #------------------------------------------------------------------------
! 184: # This block of tests - savepoint-3.* - test that when a transaction
! 185: # savepoint is rolled back, locks are not released from database files.
! 186: # And that when a transaction savepoint is released, they are released.
! 187: #
! 188: # These tests do not work in WAL mode. WAL mode does not take RESERVED
! 189: # locks on the database file.
! 190: #
! 191: if {[wal_is_wal_mode]==0} {
! 192: do_test savepoint-3.1 {
! 193: execsql { SAVEPOINT "transaction" }
! 194: execsql { PRAGMA lock_status }
! 195: } {main unlocked temp closed}
! 196:
! 197: do_test savepoint-3.2 {
! 198: execsql { INSERT INTO t1 VALUES(1, 2, 3) }
! 199: execsql { PRAGMA lock_status }
! 200: } {main reserved temp closed}
! 201:
! 202: do_test savepoint-3.3 {
! 203: execsql { ROLLBACK TO "transaction" }
! 204: execsql { PRAGMA lock_status }
! 205: } {main reserved temp closed}
! 206:
! 207: do_test savepoint-3.4 {
! 208: execsql { INSERT INTO t1 VALUES(1, 2, 3) }
! 209: execsql { PRAGMA lock_status }
! 210: } {main reserved temp closed}
! 211:
! 212: do_test savepoint-3.5 {
! 213: execsql { RELEASE "transaction" }
! 214: execsql { PRAGMA lock_status }
! 215: } {main unlocked temp closed}
! 216: }
! 217:
! 218: #------------------------------------------------------------------------
! 219: # Test that savepoints that include schema modifications are handled
! 220: # correctly. Test cases savepoint-4.*.
! 221: #
! 222: do_test savepoint-4.1 {
! 223: execsql {
! 224: CREATE TABLE t2(d, e, f);
! 225: SELECT sql FROM sqlite_master;
! 226: }
! 227: } {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}}
! 228: do_test savepoint-4.2 {
! 229: execsql {
! 230: BEGIN;
! 231: CREATE TABLE t3(g,h);
! 232: INSERT INTO t3 VALUES('I', 'II');
! 233: SAVEPOINT one;
! 234: DROP TABLE t3;
! 235: }
! 236: } {}
! 237: do_test savepoint-4.3 {
! 238: execsql {
! 239: CREATE TABLE t3(g, h, i);
! 240: INSERT INTO t3 VALUES('III', 'IV', 'V');
! 241: }
! 242: execsql {SELECT * FROM t3}
! 243: } {III IV V}
! 244: do_test savepoint-4.4 {
! 245: execsql { ROLLBACK TO one; }
! 246: execsql {SELECT * FROM t3}
! 247: } {I II}
! 248: do_test savepoint-4.5 {
! 249: execsql {
! 250: ROLLBACK;
! 251: SELECT sql FROM sqlite_master;
! 252: }
! 253: } {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}}
! 254:
! 255: do_test savepoint-4.6 {
! 256: execsql {
! 257: BEGIN;
! 258: INSERT INTO t1 VALUES('o', 't', 't');
! 259: SAVEPOINT sp1;
! 260: CREATE TABLE t3(a, b, c);
! 261: INSERT INTO t3 VALUES('z', 'y', 'x');
! 262: }
! 263: execsql {SELECT * FROM t3}
! 264: } {z y x}
! 265: do_test savepoint-4.7 {
! 266: execsql {
! 267: ROLLBACK TO sp1;
! 268: CREATE TABLE t3(a);
! 269: INSERT INTO t3 VALUES('value');
! 270: }
! 271: execsql {SELECT * FROM t3}
! 272: } {value}
! 273: do_test savepoint-4.8 {
! 274: execsql COMMIT
! 275: } {}
! 276: wal_check_journal_mode savepoint-4.9
! 277:
! 278: #------------------------------------------------------------------------
! 279: # Test some logic errors to do with the savepoint feature.
! 280: #
! 281:
! 282: ifcapable incrblob {
! 283: do_test savepoint-5.1.1 {
! 284: execsql {
! 285: CREATE TABLE blobs(x);
! 286: INSERT INTO blobs VALUES('a twentyeight character blob');
! 287: }
! 288: set fd [db incrblob blobs x 1]
! 289: puts -nonewline $fd "hello"
! 290: catchsql {SAVEPOINT abc}
! 291: } {1 {cannot open savepoint - SQL statements in progress}}
! 292: do_test savepoint-5.1.2 {
! 293: close $fd
! 294: catchsql {SAVEPOINT abc}
! 295: } {0 {}}
! 296:
! 297: do_test savepoint-5.2 {
! 298: execsql {RELEASE abc}
! 299: catchsql {RELEASE abc}
! 300: } {1 {no such savepoint: abc}}
! 301:
! 302: do_test savepoint-5.3.1 {
! 303: execsql {SAVEPOINT abc}
! 304: catchsql {ROLLBACK TO def}
! 305: } {1 {no such savepoint: def}}
! 306: do_test savepoint-5.3.2 {
! 307: execsql {SAVEPOINT def}
! 308: set fd [db incrblob -readonly blobs x 1]
! 309: catchsql {ROLLBACK TO def}
! 310: } {1 {cannot rollback savepoint - SQL statements in progress}}
! 311: do_test savepoint-5.3.3 {
! 312: catchsql {RELEASE def}
! 313: } {0 {}}
! 314: do_test savepoint-5.3.4 {
! 315: close $fd
! 316: execsql {savepoint def}
! 317: set fd [db incrblob blobs x 1]
! 318: catchsql {release def}
! 319: } {1 {cannot release savepoint - SQL statements in progress}}
! 320: do_test savepoint-5.3.5 {
! 321: close $fd
! 322: execsql {release abc}
! 323: } {}
! 324:
! 325: # Rollback mode:
! 326: #
! 327: # Open a savepoint transaction and insert a row into the database. Then,
! 328: # using a second database handle, open a read-only transaction on the
! 329: # database file. Check that the savepoint transaction cannot be committed
! 330: # until after the read-only transaction has been closed.
! 331: #
! 332: # WAL mode:
! 333: #
! 334: # As above, except that the savepoint transaction can be successfully
! 335: # committed before the read-only transaction has been closed.
! 336: #
! 337: do_test savepoint-5.4.1 {
! 338: execsql {
! 339: SAVEPOINT main;
! 340: INSERT INTO blobs VALUES('another blob');
! 341: }
! 342: } {}
! 343: do_test savepoint-5.4.2 {
! 344: sqlite3 db2 test.db
! 345: execsql { BEGIN ; SELECT count(*) FROM blobs } db2
! 346: } {1}
! 347: if {[wal_is_wal_mode]} {
! 348: do_test savepoint-5.4.3 { catchsql "RELEASE main" } {0 {}}
! 349: do_test savepoint-5.4.4 { db2 close } {}
! 350: } else {
! 351: do_test savepoint-5.4.3 {
! 352: catchsql { RELEASE main }
! 353: } {1 {database is locked}}
! 354: do_test savepoint-5.4.4 {
! 355: db2 close
! 356: catchsql { RELEASE main }
! 357: } {0 {}}
! 358: }
! 359: do_test savepoint-5.4.5 {
! 360: execsql { SELECT x FROM blobs WHERE rowid = 2 }
! 361: } {{another blob}}
! 362: do_test savepoint-5.4.6 {
! 363: execsql { SELECT count(*) FROM blobs }
! 364: } {2}
! 365: }
! 366: wal_check_journal_mode savepoint-5.5
! 367:
! 368: #-------------------------------------------------------------------------
! 369: # The following tests, savepoint-6.*, test an incr-vacuum inside of a
! 370: # couple of nested savepoints.
! 371: #
! 372: ifcapable {autovacuum && pragma} {
! 373: db close
! 374: forcedelete test.db
! 375: sqlite3 db test.db
! 376:
! 377: do_test savepoint-6.1 {
! 378: execsql { PRAGMA auto_vacuum = incremental }
! 379: wal_set_journal_mode
! 380: execsql {
! 381: CREATE TABLE t1(a, b, c);
! 382: CREATE INDEX i1 ON t1(a, b);
! 383: BEGIN;
! 384: INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400));
! 385: }
! 386: set r "randstr(10,400)"
! 387: for {set ii 0} {$ii < 10} {incr ii} {
! 388: execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1"
! 389: }
! 390: execsql { COMMIT }
! 391: } {}
! 392:
! 393: integrity_check savepoint-6.2
! 394:
! 395: do_test savepoint-6.3 {
! 396: execsql {
! 397: PRAGMA cache_size = 10;
! 398: BEGIN;
! 399: UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0;
! 400: SAVEPOINT one;
! 401: DELETE FROM t1 WHERE rowid%2;
! 402: PRAGMA incr_vacuum;
! 403: SAVEPOINT two;
! 404: INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1;
! 405: DELETE FROM t1 WHERE rowid%2;
! 406: PRAGMA incr_vacuum;
! 407: ROLLBACK TO one;
! 408: COMMIT;
! 409: }
! 410: } {}
! 411:
! 412: integrity_check savepoint-6.4
! 413:
! 414: wal_check_journal_mode savepoint-6.5
! 415: }
! 416:
! 417: #-------------------------------------------------------------------------
! 418: # The following tests, savepoint-7.*, attempt to break the logic
! 419: # surrounding savepoints by growing and shrinking the database file.
! 420: #
! 421: db close
! 422: forcedelete test.db
! 423: sqlite3 db test.db
! 424:
! 425: do_test savepoint-7.1 {
! 426: execsql { PRAGMA auto_vacuum = incremental }
! 427: wal_set_journal_mode
! 428: execsql {
! 429: PRAGMA cache_size = 10;
! 430: BEGIN;
! 431: CREATE TABLE t1(a PRIMARY KEY, b);
! 432: INSERT INTO t1(a) VALUES('alligator');
! 433: INSERT INTO t1(a) VALUES('angelfish');
! 434: INSERT INTO t1(a) VALUES('ant');
! 435: INSERT INTO t1(a) VALUES('antelope');
! 436: INSERT INTO t1(a) VALUES('ape');
! 437: INSERT INTO t1(a) VALUES('baboon');
! 438: INSERT INTO t1(a) VALUES('badger');
! 439: INSERT INTO t1(a) VALUES('bear');
! 440: INSERT INTO t1(a) VALUES('beetle');
! 441: INSERT INTO t1(a) VALUES('bird');
! 442: INSERT INTO t1(a) VALUES('bison');
! 443: UPDATE t1 SET b = randstr(1000,1000);
! 444: UPDATE t1 SET b = b||randstr(1000,1000);
! 445: UPDATE t1 SET b = b||randstr(1000,1000);
! 446: UPDATE t1 SET b = b||randstr(10,1000);
! 447: COMMIT;
! 448: }
! 449: expr ([execsql { PRAGMA page_count }] > 20)
! 450: } {1}
! 451: do_test savepoint-7.2.1 {
! 452: execsql {
! 453: BEGIN;
! 454: SAVEPOINT one;
! 455: CREATE TABLE t2(a, b);
! 456: INSERT INTO t2 SELECT a, b FROM t1;
! 457: ROLLBACK TO one;
! 458: }
! 459: execsql {
! 460: PRAGMA integrity_check;
! 461: }
! 462: } {ok}
! 463: do_test savepoint-7.2.2 {
! 464: execsql {
! 465: COMMIT;
! 466: PRAGMA integrity_check;
! 467: }
! 468: } {ok}
! 469:
! 470: do_test savepoint-7.3.1 {
! 471: execsql {
! 472: CREATE TABLE t2(a, b);
! 473: INSERT INTO t2 SELECT a, b FROM t1;
! 474: }
! 475: } {}
! 476: do_test savepoint-7.3.2 {
! 477: execsql {
! 478: BEGIN;
! 479: SAVEPOINT one;
! 480: DELETE FROM t2;
! 481: PRAGMA incremental_vacuum;
! 482: SAVEPOINT two;
! 483: INSERT INTO t2 SELECT a, b FROM t1;
! 484: ROLLBACK TO two;
! 485: COMMIT;
! 486: }
! 487: execsql { PRAGMA integrity_check }
! 488: } {ok}
! 489: wal_check_journal_mode savepoint-7.3.3
! 490:
! 491: do_test savepoint-7.4.1 {
! 492: db close
! 493: forcedelete test.db
! 494: sqlite3 db test.db
! 495: execsql { PRAGMA auto_vacuum = incremental }
! 496: wal_set_journal_mode
! 497: execsql {
! 498: CREATE TABLE t1(a, b, PRIMARY KEY(a, b));
! 499: INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000));
! 500: BEGIN;
! 501: DELETE FROM t1;
! 502: SAVEPOINT one;
! 503: PRAGMA incremental_vacuum;
! 504: ROLLBACK TO one;
! 505: COMMIT;
! 506: }
! 507:
! 508: execsql { PRAGMA integrity_check }
! 509: } {ok}
! 510:
! 511: do_test savepoint-7.5.1 {
! 512: execsql {
! 513: PRAGMA incremental_vacuum;
! 514: CREATE TABLE t5(x, y);
! 515: INSERT INTO t5 VALUES(1, randstr(1000,1000));
! 516: INSERT INTO t5 VALUES(2, randstr(1000,1000));
! 517: INSERT INTO t5 VALUES(3, randstr(1000,1000));
! 518:
! 519: BEGIN;
! 520: INSERT INTO t5 VALUES(4, randstr(1000,1000));
! 521: INSERT INTO t5 VALUES(5, randstr(1000,1000));
! 522: DELETE FROM t5 WHERE x=1 OR x=2;
! 523: SAVEPOINT one;
! 524: PRAGMA incremental_vacuum;
! 525: SAVEPOINT two;
! 526: INSERT INTO t5 VALUES(1, randstr(1000,1000));
! 527: INSERT INTO t5 VALUES(2, randstr(1000,1000));
! 528: ROLLBACK TO two;
! 529: ROLLBACK TO one;
! 530: COMMIT;
! 531: PRAGMA integrity_check;
! 532: }
! 533: } {ok}
! 534: do_test savepoint-7.5.2 {
! 535: execsql {
! 536: DROP TABLE t5;
! 537: }
! 538: } {}
! 539: wal_check_journal_mode savepoint-7.5.3
! 540:
! 541: # Test oddly named and quoted savepoints.
! 542: #
! 543: do_test savepoint-8-1 {
! 544: execsql { SAVEPOINT "save1" }
! 545: execsql { RELEASE save1 }
! 546: } {}
! 547: do_test savepoint-8-2 {
! 548: execsql { SAVEPOINT "Including whitespace " }
! 549: execsql { RELEASE "including Whitespace " }
! 550: } {}
! 551:
! 552: # Test that the authorization callback works.
! 553: #
! 554: ifcapable auth {
! 555: proc auth {args} {
! 556: eval lappend ::authdata $args
! 557: return SQLITE_OK
! 558: }
! 559: db auth auth
! 560:
! 561: do_test savepoint-9.1 {
! 562: set ::authdata [list]
! 563: execsql { SAVEPOINT sp1 }
! 564: set ::authdata
! 565: } {SQLITE_SAVEPOINT BEGIN sp1 {} {}}
! 566: do_test savepoint-9.2 {
! 567: set ::authdata [list]
! 568: execsql { ROLLBACK TO sp1 }
! 569: set ::authdata
! 570: } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}}
! 571: do_test savepoint-9.3 {
! 572: set ::authdata [list]
! 573: execsql { RELEASE sp1 }
! 574: set ::authdata
! 575: } {SQLITE_SAVEPOINT RELEASE sp1 {} {}}
! 576:
! 577: proc auth {args} {
! 578: eval lappend ::authdata $args
! 579: return SQLITE_DENY
! 580: }
! 581: db auth auth
! 582:
! 583: do_test savepoint-9.4 {
! 584: set ::authdata [list]
! 585: set res [catchsql { SAVEPOINT sp1 }]
! 586: concat $::authdata $res
! 587: } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}}
! 588: do_test savepoint-9.5 {
! 589: set ::authdata [list]
! 590: set res [catchsql { ROLLBACK TO sp1 }]
! 591: concat $::authdata $res
! 592: } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}}
! 593: do_test savepoint-9.6 {
! 594: set ::authdata [list]
! 595: set res [catchsql { RELEASE sp1 }]
! 596: concat $::authdata $res
! 597: } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}}
! 598:
! 599: catch { db eval ROLLBACK }
! 600: db auth ""
! 601: }
! 602:
! 603: #-------------------------------------------------------------------------
! 604: # The following tests - savepoint-10.* - test the interaction of
! 605: # savepoints and ATTACH statements.
! 606: #
! 607:
! 608: # First make sure it is not possible to attach or detach a database while
! 609: # a savepoint is open (it is not possible if any transaction is open).
! 610: #
! 611: do_test savepoint-10.1.1 {
! 612: catchsql {
! 613: SAVEPOINT one;
! 614: ATTACH 'test2.db' AS aux;
! 615: }
! 616: } {1 {cannot ATTACH database within transaction}}
! 617: do_test savepoint-10.1.2 {
! 618: execsql {
! 619: RELEASE one;
! 620: ATTACH 'test2.db' AS aux;
! 621: }
! 622: catchsql {
! 623: SAVEPOINT one;
! 624: DETACH aux;
! 625: }
! 626: } {1 {cannot DETACH database within transaction}}
! 627: do_test savepoint-10.1.3 {
! 628: execsql {
! 629: RELEASE one;
! 630: DETACH aux;
! 631: }
! 632: } {}
! 633:
! 634: # The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3
! 635: # And the following set of tests is only really interested in the status
! 636: # of the aux1 and aux2 locks. So record the current lock status of
! 637: # TEMP for use in the answers.
! 638: set templockstate [lindex [db eval {PRAGMA lock_status}] 3]
! 639:
! 640:
! 641: if {[wal_is_wal_mode]==0} {
! 642: do_test savepoint-10.2.1 {
! 643: forcedelete test3.db
! 644: forcedelete test2.db
! 645: execsql {
! 646: ATTACH 'test2.db' AS aux1;
! 647: ATTACH 'test3.db' AS aux2;
! 648: DROP TABLE t1;
! 649: CREATE TABLE main.t1(x, y);
! 650: CREATE TABLE aux1.t2(x, y);
! 651: CREATE TABLE aux2.t3(x, y);
! 652: SELECT name FROM sqlite_master
! 653: UNION ALL
! 654: SELECT name FROM aux1.sqlite_master
! 655: UNION ALL
! 656: SELECT name FROM aux2.sqlite_master;
! 657: }
! 658: } {t1 t2 t3}
! 659: do_test savepoint-10.2.2 {
! 660: execsql { PRAGMA lock_status }
! 661: } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
! 662:
! 663: do_test savepoint-10.2.3 {
! 664: execsql {
! 665: SAVEPOINT one;
! 666: INSERT INTO t1 VALUES(1, 2);
! 667: PRAGMA lock_status;
! 668: }
! 669: } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked]
! 670: do_test savepoint-10.2.4 {
! 671: execsql {
! 672: INSERT INTO t3 VALUES(3, 4);
! 673: PRAGMA lock_status;
! 674: }
! 675: } [list main reserved temp $templockstate aux1 unlocked aux2 reserved]
! 676: do_test savepoint-10.2.5 {
! 677: execsql {
! 678: SAVEPOINT two;
! 679: INSERT INTO t2 VALUES(5, 6);
! 680: PRAGMA lock_status;
! 681: }
! 682: } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
! 683: do_test savepoint-10.2.6 {
! 684: execsql { SELECT * FROM t2 }
! 685: } {5 6}
! 686: do_test savepoint-10.2.7 {
! 687: execsql { ROLLBACK TO two }
! 688: execsql { SELECT * FROM t2 }
! 689: } {}
! 690: do_test savepoint-10.2.8 {
! 691: execsql { PRAGMA lock_status }
! 692: } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
! 693: do_test savepoint-10.2.9 {
! 694: execsql { SELECT 'a', * FROM t1 UNION ALL SELECT 'b', * FROM t3 }
! 695: } {a 1 2 b 3 4}
! 696: do_test savepoint-10.2.9 {
! 697: execsql {
! 698: INSERT INTO t2 VALUES(5, 6);
! 699: RELEASE one;
! 700: }
! 701: execsql {
! 702: SELECT * FROM t1;
! 703: SELECT * FROM t2;
! 704: SELECT * FROM t3;
! 705: }
! 706: } {1 2 5 6 3 4}
! 707: do_test savepoint-10.2.9 {
! 708: execsql { PRAGMA lock_status }
! 709: } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
! 710:
! 711: do_test savepoint-10.2.10 {
! 712: execsql {
! 713: SAVEPOINT one;
! 714: INSERT INTO t1 VALUES('a', 'b');
! 715: SAVEPOINT two;
! 716: INSERT INTO t2 VALUES('c', 'd');
! 717: SAVEPOINT three;
! 718: INSERT INTO t3 VALUES('e', 'f');
! 719: }
! 720: execsql {
! 721: SELECT * FROM t1;
! 722: SELECT * FROM t2;
! 723: SELECT * FROM t3;
! 724: }
! 725: } {1 2 a b 5 6 c d 3 4 e f}
! 726: do_test savepoint-10.2.11 {
! 727: execsql { ROLLBACK TO two }
! 728: execsql {
! 729: SELECT * FROM t1;
! 730: SELECT * FROM t2;
! 731: SELECT * FROM t3;
! 732: }
! 733: } {1 2 a b 5 6 3 4}
! 734: do_test savepoint-10.2.12 {
! 735: execsql {
! 736: INSERT INTO t3 VALUES('g', 'h');
! 737: ROLLBACK TO two;
! 738: }
! 739: execsql {
! 740: SELECT * FROM t1;
! 741: SELECT * FROM t2;
! 742: SELECT * FROM t3;
! 743: }
! 744: } {1 2 a b 5 6 3 4}
! 745: do_test savepoint-10.2.13 {
! 746: execsql { ROLLBACK }
! 747: execsql {
! 748: SELECT * FROM t1;
! 749: SELECT * FROM t2;
! 750: SELECT * FROM t3;
! 751: }
! 752: } {1 2 5 6 3 4}
! 753: do_test savepoint-10.2.14 {
! 754: execsql { PRAGMA lock_status }
! 755: } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
! 756: }
! 757:
! 758: #-------------------------------------------------------------------------
! 759: # The following tests - savepoint-11.* - test the interaction of
! 760: # savepoints and creating or dropping tables and indexes in
! 761: # auto-vacuum mode.
! 762: #
! 763: do_test savepoint-11.1 {
! 764: db close
! 765: forcedelete test.db
! 766: sqlite3 db test.db
! 767: execsql { PRAGMA auto_vacuum = full; }
! 768: wal_set_journal_mode
! 769: execsql {
! 770: CREATE TABLE t1(a, b, UNIQUE(a, b));
! 771: INSERT INTO t1 VALUES(1, randstr(1000,1000));
! 772: INSERT INTO t1 VALUES(2, randstr(1000,1000));
! 773: }
! 774: } {}
! 775: do_test savepoint-11.2 {
! 776: execsql {
! 777: SAVEPOINT one;
! 778: CREATE TABLE t2(a, b, UNIQUE(a, b));
! 779: SAVEPOINT two;
! 780: CREATE TABLE t3(a, b, UNIQUE(a, b));
! 781: }
! 782: } {}
! 783: integrity_check savepoint-11.3
! 784: do_test savepoint-11.4 {
! 785: execsql { ROLLBACK TO two }
! 786: } {}
! 787: integrity_check savepoint-11.5
! 788: do_test savepoint-11.6 {
! 789: execsql {
! 790: CREATE TABLE t3(a, b, UNIQUE(a, b));
! 791: ROLLBACK TO one;
! 792: }
! 793: } {}
! 794: integrity_check savepoint-11.7
! 795: do_test savepoint-11.8 {
! 796: execsql { ROLLBACK }
! 797: execsql { PRAGMA wal_checkpoint }
! 798: file size test.db
! 799: } {8192}
! 800:
! 801: do_test savepoint-11.9 {
! 802: execsql {
! 803: DROP TABLE IF EXISTS t1;
! 804: DROP TABLE IF EXISTS t2;
! 805: DROP TABLE IF EXISTS t3;
! 806: }
! 807: } {}
! 808: do_test savepoint-11.10 {
! 809: execsql {
! 810: BEGIN;
! 811: CREATE TABLE t1(a, b);
! 812: CREATE TABLE t2(x, y);
! 813: INSERT INTO t2 VALUES(1, 2);
! 814: SAVEPOINT one;
! 815: INSERT INTO t2 VALUES(3, 4);
! 816: SAVEPOINT two;
! 817: DROP TABLE t1;
! 818: ROLLBACK TO two;
! 819: }
! 820: execsql {SELECT * FROM t2}
! 821: } {1 2 3 4}
! 822: do_test savepoint-11.11 {
! 823: execsql COMMIT
! 824: } {}
! 825: do_test savepoint-11.12 {
! 826: execsql {SELECT * FROM t2}
! 827: } {1 2 3 4}
! 828: wal_check_journal_mode savepoint-11.13
! 829:
! 830: #-------------------------------------------------------------------------
! 831: # The following tests - savepoint-12.* - test the interaction of
! 832: # savepoints and "ON CONFLICT ROLLBACK" clauses.
! 833: #
! 834: do_test savepoint-12.1 {
! 835: execsql {
! 836: CREATE TABLE t4(a PRIMARY KEY, b);
! 837: INSERT INTO t4 VALUES(1, 'one');
! 838: }
! 839: } {}
! 840: do_test savepoint-12.2 {
! 841: # The final statement of the following SQL hits a constraint when the
! 842: # conflict handling mode is "OR ROLLBACK" and there are a couple of
! 843: # open savepoints. At one point this would fail to clear the internal
! 844: # record of the open savepoints, resulting in an assert() failure
! 845: # later on.
! 846: #
! 847: catchsql {
! 848: BEGIN;
! 849: INSERT INTO t4 VALUES(2, 'two');
! 850: SAVEPOINT sp1;
! 851: INSERT INTO t4 VALUES(3, 'three');
! 852: SAVEPOINT sp2;
! 853: INSERT OR ROLLBACK INTO t4 VALUES(1, 'one');
! 854: }
! 855: } {1 {column a is not unique}}
! 856: do_test savepoint-12.3 {
! 857: sqlite3_get_autocommit db
! 858: } {1}
! 859: do_test savepoint-12.4 {
! 860: execsql { SAVEPOINT one }
! 861: } {}
! 862: wal_check_journal_mode savepoint-12.5
! 863:
! 864: #-------------------------------------------------------------------------
! 865: # The following tests - savepoint-13.* - test the interaction of
! 866: # savepoints and "journal_mode = off".
! 867: #
! 868: if {[wal_is_wal_mode]==0} {
! 869: do_test savepoint-13.1 {
! 870: db close
! 871: catch {forcedelete test.db}
! 872: sqlite3 db test.db
! 873: execsql {
! 874: BEGIN;
! 875: CREATE TABLE t1(a PRIMARY KEY, b);
! 876: INSERT INTO t1 VALUES(1, 2);
! 877: COMMIT;
! 878: PRAGMA journal_mode = off;
! 879: }
! 880: } {off}
! 881: do_test savepoint-13.2 {
! 882: execsql {
! 883: BEGIN;
! 884: INSERT INTO t1 VALUES(3, 4);
! 885: INSERT INTO t1 SELECT a+4,b+4 FROM t1;
! 886: COMMIT;
! 887: }
! 888: } {}
! 889: do_test savepoint-13.3 {
! 890: execsql {
! 891: BEGIN;
! 892: INSERT INTO t1 VALUES(9, 10);
! 893: SAVEPOINT s1;
! 894: INSERT INTO t1 VALUES(11, 12);
! 895: COMMIT;
! 896: }
! 897: } {}
! 898: do_test savepoint-13.4 {
! 899: execsql {
! 900: BEGIN;
! 901: INSERT INTO t1 VALUES(13, 14);
! 902: SAVEPOINT s1;
! 903: INSERT INTO t1 VALUES(15, 16);
! 904: ROLLBACK TO s1;
! 905: ROLLBACK;
! 906: SELECT * FROM t1;
! 907: }
! 908: } {1 2 3 4 5 6 7 8 9 10 11 12}
! 909: }
! 910:
! 911: db close
! 912: delete_file test.db
! 913: do_multiclient_test tn {
! 914: do_test savepoint-14.$tn.1 {
! 915: sql1 {
! 916: CREATE TABLE foo(x);
! 917: INSERT INTO foo VALUES(1);
! 918: INSERT INTO foo VALUES(2);
! 919: }
! 920: sql2 {
! 921: BEGIN;
! 922: SELECT * FROM foo;
! 923: }
! 924: } {1 2}
! 925: do_test savepoint-14.$tn.2 {
! 926: sql1 {
! 927: SAVEPOINT one;
! 928: INSERT INTO foo VALUES(1);
! 929: }
! 930: csql1 { RELEASE one }
! 931: } {1 {database is locked}}
! 932: do_test savepoint-14.$tn.3 {
! 933: sql1 { ROLLBACK TO one }
! 934: sql2 { COMMIT }
! 935: sql1 { RELEASE one }
! 936: } {}
! 937:
! 938: do_test savepoint-14.$tn.4 {
! 939: sql2 {
! 940: BEGIN;
! 941: SELECT * FROM foo;
! 942: }
! 943: } {1 2}
! 944: do_test savepoint-14.$tn.5 {
! 945: sql1 {
! 946: SAVEPOINT one;
! 947: INSERT INTO foo VALUES(1);
! 948: }
! 949: csql1 { RELEASE one }
! 950: } {1 {database is locked}}
! 951: do_test savepoint-14.$tn.6 {
! 952: sql2 { COMMIT }
! 953: sql1 {
! 954: ROLLBACK TO one;
! 955: INSERT INTO foo VALUES(3);
! 956: INSERT INTO foo VALUES(4);
! 957: INSERT INTO foo VALUES(5);
! 958: RELEASE one;
! 959: }
! 960: } {}
! 961: do_test savepoint-14.$tn.7 {
! 962: sql2 { CREATE INDEX fooidx ON foo(x); }
! 963: sql3 { PRAGMA integrity_check }
! 964: } {ok}
! 965: }
! 966:
! 967: do_multiclient_test tn {
! 968: do_test savepoint-15.$tn.1 {
! 969: sql1 {
! 970: CREATE TABLE foo(x);
! 971: INSERT INTO foo VALUES(1);
! 972: INSERT INTO foo VALUES(2);
! 973: }
! 974: sql2 { BEGIN; SELECT * FROM foo; }
! 975: } {1 2}
! 976: do_test savepoint-15.$tn.2 {
! 977: sql1 {
! 978: PRAGMA locking_mode = EXCLUSIVE;
! 979: BEGIN;
! 980: INSERT INTO foo VALUES(3);
! 981: }
! 982: csql1 { COMMIT }
! 983: } {1 {database is locked}}
! 984: do_test savepoint-15.$tn.3 {
! 985: sql1 { ROLLBACK }
! 986: sql2 { COMMIT }
! 987: sql1 {
! 988: INSERT INTO foo VALUES(3);
! 989: PRAGMA locking_mode = NORMAL;
! 990: INSERT INTO foo VALUES(4);
! 991: }
! 992: sql2 { CREATE INDEX fooidx ON foo(x); }
! 993: sql3 { PRAGMA integrity_check }
! 994: } {ok}
! 995: }
! 996:
! 997: do_multiclient_test tn {
! 998: do_test savepoint-16.$tn.1 {
! 999: sql1 {
! 1000: CREATE TABLE foo(x);
! 1001: INSERT INTO foo VALUES(1);
! 1002: INSERT INTO foo VALUES(2);
! 1003: }
! 1004: } {}
! 1005: do_test savepoint-16.$tn.2 {
! 1006:
! 1007: db eval {SELECT * FROM foo} {
! 1008: sql1 { INSERT INTO foo VALUES(3) }
! 1009: sql2 { SELECT * FROM foo }
! 1010: sql1 { INSERT INTO foo VALUES(4) }
! 1011: break
! 1012: }
! 1013:
! 1014: sql2 { CREATE INDEX fooidx ON foo(x); }
! 1015: sql3 { PRAGMA integrity_check }
! 1016: } {ok}
! 1017: do_test savepoint-16.$tn.3 {
! 1018: sql1 { SELECT * FROM foo }
! 1019: } {1 2 3 4}
! 1020: }
! 1021:
! 1022: #-------------------------------------------------------------------------
! 1023: # This next block of tests verifies that a problem reported on the mailing
! 1024: # list has been resolved. At one point the second "CREATE TABLE t6" would
! 1025: # fail as table t6 still existed in the internal cache of the db schema
! 1026: # (even though it had been removed from the database by the ROLLBACK
! 1027: # command).
! 1028: #
! 1029: sqlite3 db test.db
! 1030: do_execsql_test savepoint-17.1 {
! 1031: BEGIN;
! 1032: CREATE TABLE t6(a, b);
! 1033: INSERT INTO t6 VALUES(1, 2);
! 1034: SAVEPOINT one;
! 1035: INSERT INTO t6 VALUES(3, 4);
! 1036: ROLLBACK TO one;
! 1037: SELECT * FROM t6;
! 1038: ROLLBACK;
! 1039: } {1 2}
! 1040:
! 1041: do_execsql_test savepoint-17.2 {
! 1042: CREATE TABLE t6(a, b);
! 1043: } {}
! 1044:
! 1045: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to savepoint.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