PageRenderTime 42ms CodeModel.GetById 17ms RepoModel.GetById 1ms app.codeStats 0ms

/test/trans.test

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