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