PageRenderTime 48ms CodeModel.GetById 14ms RepoModel.GetById 0ms app.codeStats 0ms

/trunk/src/sqlite/test/shared.test

#
Unknown | 1062 lines | 1006 code | 56 blank | 0 comment | 0 complexity | 12b01d3f4e67e9fb2d18bbc08d256681 MD5 | raw file
Possible License(s): BSD-3-Clause
  1. # 2005 December 30
  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: shared.test,v 1.36 2009/03/16 13:19:36 danielk1977 Exp $
  13. set testdir [file dirname $argv0]
  14. source $testdir/tester.tcl
  15. db close
  16. # These tests cannot be run without the ATTACH command.
  17. #
  18. ifcapable !shared_cache||!attach {
  19. finish_test
  20. return
  21. }
  22. set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
  23. foreach av [list 0 1] {
  24. # Open the database connection and execute the auto-vacuum pragma
  25. file delete -force test.db
  26. sqlite3 db test.db
  27. ifcapable autovacuum {
  28. do_test shared-[expr $av+1].1.0 {
  29. execsql "pragma auto_vacuum=$::av"
  30. execsql {pragma auto_vacuum}
  31. } "$av"
  32. } else {
  33. if {$av} {
  34. db close
  35. break
  36. }
  37. }
  38. # if we're using proxy locks, we use 2 filedescriptors for a db
  39. # that is open but NOT yet locked, after a lock is taken we'll have 3,
  40. # normally sqlite uses 1 (proxy locking adds the conch and the local lock)
  41. set using_proxy 0
  42. foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
  43. set using_proxy $value
  44. }
  45. set extrafds_prelock 0
  46. set extrafds_postlock 0
  47. if {$using_proxy>0} {
  48. set extrafds_prelock 1
  49. set extrafds_postlock 2
  50. }
  51. # $av is currently 0 if this loop iteration is to test with auto-vacuum turned
  52. # off, and 1 if it is turned on. Increment it so that (1 -> no auto-vacuum)
  53. # and (2 -> auto-vacuum). The sole reason for this is so that it looks nicer
  54. # when we use this variable as part of test-case names.
  55. #
  56. incr av
  57. # Test organization:
  58. #
  59. # shared-1.*: Simple test to verify basic sanity of table level locking when
  60. # two connections share a pager cache.
  61. # shared-2.*: Test that a read transaction can co-exist with a
  62. # write-transaction, including a simple test to ensure the
  63. # external locking protocol is still working.
  64. # shared-3.*: Simple test of read-uncommitted mode.
  65. # shared-4.*: Check that the schema is locked and unlocked correctly.
  66. # shared-5.*: Test that creating/dropping schema items works when databases
  67. # are attached in different orders to different handles.
  68. # shared-6.*: Locking, UNION ALL queries and sub-queries.
  69. # shared-7.*: Autovacuum and shared-cache.
  70. # shared-8.*: Tests related to the text encoding of shared-cache databases.
  71. # shared-9.*: TEMP triggers and shared-cache databases.
  72. # shared-10.*: Tests of sqlite3_close().
  73. # shared-11.*: Test transaction locking.
  74. #
  75. do_test shared-$av.1.1 {
  76. # Open a second database on the file test.db. It should use the same pager
  77. # cache and schema as the original connection. Verify that only 1 file is
  78. # opened.
  79. sqlite3 db2 test.db
  80. set ::sqlite_open_file_count
  81. expr $sqlite_open_file_count-$extrafds_postlock
  82. } {1}
  83. do_test shared-$av.1.2 {
  84. # Add a table and a single row of data via the first connection.
  85. # Ensure that the second connection can see them.
  86. execsql {
  87. CREATE TABLE abc(a, b, c);
  88. INSERT INTO abc VALUES(1, 2, 3);
  89. } db
  90. execsql {
  91. SELECT * FROM abc;
  92. } db2
  93. } {1 2 3}
  94. do_test shared-$av.1.3 {
  95. # Have the first connection begin a transaction and obtain a read-lock
  96. # on table abc. This should not prevent the second connection from
  97. # querying abc.
  98. execsql {
  99. BEGIN;
  100. SELECT * FROM abc;
  101. }
  102. execsql {
  103. SELECT * FROM abc;
  104. } db2
  105. } {1 2 3}
  106. do_test shared-$av.1.4 {
  107. # Try to insert a row into abc via connection 2. This should fail because
  108. # of the read-lock connection 1 is holding on table abc (obtained in the
  109. # previous test case).
  110. catchsql {
  111. INSERT INTO abc VALUES(4, 5, 6);
  112. } db2
  113. } {1 {database table is locked: abc}}
  114. do_test shared-$av.1.5 {
  115. # Using connection 2 (the one without the open transaction), try to create
  116. # a new table. This should fail because of the open read transaction
  117. # held by connection 1.
  118. catchsql {
  119. CREATE TABLE def(d, e, f);
  120. } db2
  121. } {1 {database table is locked: sqlite_master}}
  122. do_test shared-$av.1.6 {
  123. # Upgrade connection 1's transaction to a write transaction. Create
  124. # a new table - def - and insert a row into it. Because the connection 1
  125. # transaction modifies the schema, it should not be possible for
  126. # connection 2 to access the database at all until the connection 1
  127. # has finished the transaction.
  128. execsql {
  129. CREATE TABLE def(d, e, f);
  130. INSERT INTO def VALUES('IV', 'V', 'VI');
  131. }
  132. } {}
  133. do_test shared-$av.1.7 {
  134. # Read from the sqlite_master table with connection 1 (inside the
  135. # transaction). Then test that we can not do this with connection 2. This
  136. # is because of the schema-modified lock established by connection 1
  137. # in the previous test case.
  138. execsql {
  139. SELECT * FROM sqlite_master;
  140. }
  141. catchsql {
  142. SELECT * FROM sqlite_master;
  143. } db2
  144. } {1 {database schema is locked: main}}
  145. do_test shared-$av.1.8 {
  146. # Commit the connection 1 transaction.
  147. execsql {
  148. COMMIT;
  149. }
  150. } {}
  151. do_test shared-$av.2.1 {
  152. # Open connection db3 to the database. Use a different path to the same
  153. # file so that db3 does *not* share the same pager cache as db and db2
  154. # (there should be two open file handles).
  155. if {$::tcl_platform(platform)=="unix"} {
  156. sqlite3 db3 ./test.db
  157. } else {
  158. sqlite3 db3 TEST.DB
  159. }
  160. set ::sqlite_open_file_count
  161. expr $sqlite_open_file_count-($extrafds_prelock+$extrafds_postlock)
  162. } {2}
  163. do_test shared-$av.2.2 {
  164. # Start read transactions on db and db2 (the shared pager cache). Ensure
  165. # db3 cannot write to the database.
  166. execsql {
  167. BEGIN;
  168. SELECT * FROM abc;
  169. }
  170. execsql {
  171. BEGIN;
  172. SELECT * FROM abc;
  173. } db2
  174. catchsql {
  175. INSERT INTO abc VALUES(1, 2, 3);
  176. } db2
  177. } {1 {database table is locked: abc}}
  178. do_test shared-$av.2.3 {
  179. # Turn db's transaction into a write-transaction. db3 should still be
  180. # able to read from table def (but will not see the new row). Connection
  181. # db2 should not be able to read def (because of the write-lock).
  182. # Todo: The failed "INSERT INTO abc ..." statement in the above test
  183. # has started a write-transaction on db2 (should this be so?). This
  184. # would prevent connection db from starting a write-transaction. So roll the
  185. # db2 transaction back and replace it with a new read transaction.
  186. execsql {
  187. ROLLBACK;
  188. BEGIN;
  189. SELECT * FROM abc;
  190. } db2
  191. execsql {
  192. INSERT INTO def VALUES('VII', 'VIII', 'IX');
  193. }
  194. concat [
  195. catchsql { SELECT * FROM def; } db3
  196. ] [
  197. catchsql { SELECT * FROM def; } db2
  198. ]
  199. } {0 {IV V VI} 1 {database table is locked: def}}
  200. do_test shared-$av.2.4 {
  201. # Commit the open transaction on db. db2 still holds a read-transaction.
  202. # This should prevent db3 from writing to the database, but not from
  203. # reading.
  204. execsql {
  205. COMMIT;
  206. }
  207. concat [
  208. catchsql { SELECT * FROM def; } db3
  209. ] [
  210. catchsql { INSERT INTO def VALUES('X', 'XI', 'XII'); } db3
  211. ]
  212. } {0 {IV V VI VII VIII IX} 1 {database is locked}}
  213. catchsql COMMIT db2
  214. do_test shared-$av.3.1.1 {
  215. # This test case starts a linear scan of table 'seq' using a
  216. # read-uncommitted connection. In the middle of the scan, rows are added
  217. # to the end of the seq table (ahead of the current cursor position).
  218. # The uncommitted rows should be included in the results of the scan.
  219. execsql "
  220. CREATE TABLE seq(i PRIMARY KEY, x);
  221. INSERT INTO seq VALUES(1, '[string repeat X 500]');
  222. INSERT INTO seq VALUES(2, '[string repeat X 500]');
  223. "
  224. execsql {SELECT * FROM sqlite_master} db2
  225. execsql {PRAGMA read_uncommitted = 1} db2
  226. set ret [list]
  227. db2 eval {SELECT i FROM seq ORDER BY i} {
  228. if {$i < 4} {
  229. set max [execsql {SELECT max(i) FROM seq}]
  230. db eval {
  231. INSERT INTO seq SELECT i + :max, x FROM seq;
  232. }
  233. }
  234. lappend ret $i
  235. }
  236. set ret
  237. } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
  238. do_test shared-$av.3.1.2 {
  239. # Another linear scan through table seq using a read-uncommitted connection.
  240. # This time, delete each row as it is read. Should not affect the results of
  241. # the scan, but the table should be empty after the scan is concluded
  242. # (test 3.1.3 verifies this).
  243. set ret [list]
  244. db2 eval {SELECT i FROM seq} {
  245. db eval {DELETE FROM seq WHERE i = :i}
  246. lappend ret $i
  247. }
  248. set ret
  249. } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
  250. do_test shared-$av.3.1.3 {
  251. execsql {
  252. SELECT * FROM seq;
  253. }
  254. } {}
  255. catch {db close}
  256. catch {db2 close}
  257. catch {db3 close}
  258. #--------------------------------------------------------------------------
  259. # Tests shared-4.* test that the schema locking rules are applied
  260. # correctly. i.e.:
  261. #
  262. # 1. All transactions require a read-lock on the schemas of databases they
  263. # access.
  264. # 2. Transactions that modify a database schema require a write-lock on that
  265. # schema.
  266. # 3. It is not possible to compile a statement while another handle has a
  267. # write-lock on the schema.
  268. #
  269. # Open two database handles db and db2. Each has a single attach database
  270. # (as well as main):
  271. #
  272. # db.main -> ./test.db
  273. # db.test2 -> ./test2.db
  274. # db2.main -> ./test2.db
  275. # db2.test -> ./test.db
  276. #
  277. file delete -force test.db
  278. file delete -force test2.db
  279. file delete -force test2.db-journal
  280. sqlite3 db test.db
  281. sqlite3 db2 test2.db
  282. do_test shared-$av.4.1.1 {
  283. set sqlite_open_file_count
  284. expr $sqlite_open_file_count-($extrafds_prelock*2)
  285. } {2}
  286. do_test shared-$av.4.1.2 {
  287. execsql {ATTACH 'test2.db' AS test2}
  288. set sqlite_open_file_count
  289. expr $sqlite_open_file_count-($extrafds_postlock*2)
  290. } {2}
  291. do_test shared-$av.4.1.3 {
  292. execsql {ATTACH 'test.db' AS test} db2
  293. set sqlite_open_file_count
  294. expr $sqlite_open_file_count-($extrafds_postlock*2)
  295. } {2}
  296. # Sanity check: Create a table in ./test.db via handle db, and test that handle
  297. # db2 can "see" the new table immediately. A handle using a seperate pager
  298. # cache would have to reload the database schema before this were possible.
  299. #
  300. do_test shared-$av.4.2.1 {
  301. execsql {
  302. CREATE TABLE abc(a, b, c);
  303. CREATE TABLE def(d, e, f);
  304. INSERT INTO abc VALUES('i', 'ii', 'iii');
  305. INSERT INTO def VALUES('I', 'II', 'III');
  306. }
  307. } {}
  308. do_test shared-$av.4.2.2 {
  309. execsql {
  310. SELECT * FROM test.abc;
  311. } db2
  312. } {i ii iii}
  313. # Open a read-transaction and read from table abc via handle 2. Check that
  314. # handle 1 can read table abc. Check that handle 1 cannot modify table abc
  315. # or the database schema. Then check that handle 1 can modify table def.
  316. #
  317. do_test shared-$av.4.3.1 {
  318. execsql {
  319. BEGIN;
  320. SELECT * FROM test.abc;
  321. } db2
  322. } {i ii iii}
  323. do_test shared-$av.4.3.2 {
  324. catchsql {
  325. INSERT INTO abc VALUES('iv', 'v', 'vi');
  326. }
  327. } {1 {database table is locked: abc}}
  328. do_test shared-$av.4.3.3 {
  329. catchsql {
  330. CREATE TABLE ghi(g, h, i);
  331. }
  332. } {1 {database table is locked: sqlite_master}}
  333. do_test shared-$av.4.3.3 {
  334. catchsql {
  335. INSERT INTO def VALUES('IV', 'V', 'VI');
  336. }
  337. } {0 {}}
  338. do_test shared-$av.4.3.4 {
  339. # Cleanup: commit the transaction opened by db2.
  340. execsql {
  341. COMMIT
  342. } db2
  343. } {}
  344. # Open a write-transaction using handle 1 and modify the database schema.
  345. # Then try to execute a compiled statement to read from the same
  346. # database via handle 2 (fails to get the lock on sqlite_master). Also
  347. # try to compile a read of the same database using handle 2 (also fails).
  348. # Finally, compile a read of the other database using handle 2. This
  349. # should also fail.
  350. #
  351. ifcapable compound {
  352. do_test shared-$av.4.4.1.2 {
  353. # Sanity check 1: Check that the schema is what we think it is when viewed
  354. # via handle 1.
  355. execsql {
  356. CREATE TABLE test2.ghi(g, h, i);
  357. SELECT 'test.db:'||name FROM sqlite_master
  358. UNION ALL
  359. SELECT 'test2.db:'||name FROM test2.sqlite_master;
  360. }
  361. } {test.db:abc test.db:def test2.db:ghi}
  362. do_test shared-$av.4.4.1.2 {
  363. # Sanity check 2: Check that the schema is what we think it is when viewed
  364. # via handle 2.
  365. execsql {
  366. SELECT 'test2.db:'||name FROM sqlite_master
  367. UNION ALL
  368. SELECT 'test.db:'||name FROM test.sqlite_master;
  369. } db2
  370. } {test2.db:ghi test.db:abc test.db:def}
  371. }
  372. do_test shared-$av.4.4.2 {
  373. set ::DB2 [sqlite3_connection_pointer db2]
  374. set sql {SELECT * FROM abc}
  375. set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
  376. execsql {
  377. BEGIN;
  378. CREATE TABLE jkl(j, k, l);
  379. }
  380. sqlite3_step $::STMT1
  381. } {SQLITE_ERROR}
  382. do_test shared-$av.4.4.3 {
  383. sqlite3_finalize $::STMT1
  384. } {SQLITE_LOCKED}
  385. do_test shared-$av.4.4.4 {
  386. set rc [catch {
  387. set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
  388. } msg]
  389. list $rc $msg
  390. } {1 {(6) database schema is locked: test}}
  391. do_test shared-$av.4.4.5 {
  392. set rc [catch {
  393. set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY]
  394. } msg]
  395. list $rc $msg
  396. } {1 {(6) database schema is locked: test}}
  397. catch {db2 close}
  398. catch {db close}
  399. #--------------------------------------------------------------------------
  400. # Tests shared-5.*
  401. #
  402. foreach db [list test.db test1.db test2.db test3.db] {
  403. file delete -force $db ${db}-journal
  404. }
  405. do_test shared-$av.5.1.1 {
  406. sqlite3 db1 test.db
  407. sqlite3 db2 test.db
  408. execsql {
  409. ATTACH 'test1.db' AS test1;
  410. ATTACH 'test2.db' AS test2;
  411. ATTACH 'test3.db' AS test3;
  412. } db1
  413. execsql {
  414. ATTACH 'test3.db' AS test3;
  415. ATTACH 'test2.db' AS test2;
  416. ATTACH 'test1.db' AS test1;
  417. } db2
  418. } {}
  419. do_test shared-$av.5.1.2 {
  420. execsql {
  421. CREATE TABLE test1.t1(a, b);
  422. CREATE INDEX test1.i1 ON t1(a, b);
  423. } db1
  424. } {}
  425. ifcapable view {
  426. do_test shared-$av.5.1.3 {
  427. execsql {
  428. CREATE VIEW test1.v1 AS SELECT * FROM t1;
  429. } db1
  430. } {}
  431. }
  432. ifcapable trigger {
  433. do_test shared-$av.5.1.4 {
  434. execsql {
  435. CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN
  436. INSERT INTO t1 VALUES(new.a, new.b);
  437. END;
  438. } db1
  439. } {}
  440. }
  441. do_test shared-$av.5.1.5 {
  442. execsql {
  443. DROP INDEX i1;
  444. } db2
  445. } {}
  446. ifcapable view {
  447. do_test shared-$av.5.1.6 {
  448. execsql {
  449. DROP VIEW v1;
  450. } db2
  451. } {}
  452. }
  453. ifcapable trigger {
  454. do_test shared-$av.5.1.7 {
  455. execsql {
  456. DROP TRIGGER trig1;
  457. } db2
  458. } {}
  459. }
  460. do_test shared-$av.5.1.8 {
  461. execsql {
  462. DROP TABLE t1;
  463. } db2
  464. } {}
  465. ifcapable compound {
  466. do_test shared-$av.5.1.9 {
  467. execsql {
  468. SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master
  469. } db1
  470. } {}
  471. }
  472. #--------------------------------------------------------------------------
  473. # Tests shared-6.* test that a query obtains all the read-locks it needs
  474. # before starting execution of the query. This means that there is no chance
  475. # some rows of data will be returned before a lock fails and SQLITE_LOCK
  476. # is returned.
  477. #
  478. do_test shared-$av.6.1.1 {
  479. execsql {
  480. CREATE TABLE t1(a, b);
  481. CREATE TABLE t2(a, b);
  482. INSERT INTO t1 VALUES(1, 2);
  483. INSERT INTO t2 VALUES(3, 4);
  484. } db1
  485. } {}
  486. ifcapable compound {
  487. do_test shared-$av.6.1.2 {
  488. execsql {
  489. SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  490. } db2
  491. } {1 2 3 4}
  492. }
  493. do_test shared-$av.6.1.3 {
  494. # Establish a write lock on table t2 via connection db2. Then make a
  495. # UNION all query using connection db1 that first accesses t1, followed
  496. # by t2. If the locks are grabbed at the start of the statement (as
  497. # they should be), no rows are returned. If (as was previously the case)
  498. # they are grabbed as the tables are accessed, the t1 rows will be
  499. # returned before the query fails.
  500. #
  501. execsql {
  502. BEGIN;
  503. INSERT INTO t2 VALUES(5, 6);
  504. } db2
  505. set ret [list]
  506. catch {
  507. db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
  508. lappend ret $a $b
  509. }
  510. }
  511. set ret
  512. } {}
  513. do_test shared-$av.6.1.4 {
  514. execsql {
  515. COMMIT;
  516. BEGIN;
  517. INSERT INTO t1 VALUES(7, 8);
  518. } db2
  519. set ret [list]
  520. catch {
  521. db1 eval {
  522. SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2;
  523. } {
  524. lappend ret $d
  525. }
  526. }
  527. set ret
  528. } {}
  529. catch {db1 close}
  530. catch {db2 close}
  531. foreach f [list test.db test2.db] {
  532. file delete -force $f ${f}-journal
  533. }
  534. #--------------------------------------------------------------------------
  535. # Tests shared-7.* test auto-vacuum does not invalidate cursors from
  536. # other shared-cache users when it reorganizes the database on
  537. # COMMIT.
  538. #
  539. do_test shared-$av.7.1 {
  540. # This test case sets up a test database in auto-vacuum mode consisting
  541. # of two tables, t1 and t2. Both have a single index. Table t1 is
  542. # populated first (so consists of pages toward the start of the db file),
  543. # t2 second (pages toward the end of the file).
  544. sqlite3 db test.db
  545. sqlite3 db2 test.db
  546. execsql {
  547. BEGIN;
  548. CREATE TABLE t1(a PRIMARY KEY, b);
  549. CREATE TABLE t2(a PRIMARY KEY, b);
  550. }
  551. set ::contents {}
  552. for {set i 0} {$i < 100} {incr i} {
  553. set a [string repeat "$i " 20]
  554. set b [string repeat "$i " 20]
  555. db eval {
  556. INSERT INTO t1 VALUES(:a, :b);
  557. }
  558. lappend ::contents [list [expr $i+1] $a $b]
  559. }
  560. execsql {
  561. INSERT INTO t2 SELECT * FROM t1;
  562. COMMIT;
  563. }
  564. } {}
  565. do_test shared-$av.7.2 {
  566. # This test case deletes the contents of table t1 (the one at the start of
  567. # the file) while many cursors are open on table t2 and its index. All of
  568. # the non-root pages will be moved from the end to the start of the file
  569. # when the DELETE is committed - this test verifies that moving the pages
  570. # does not disturb the open cursors.
  571. #
  572. proc lockrow {db tbl oids body} {
  573. set ret [list]
  574. db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" {
  575. if {$i==[lindex $oids 0]} {
  576. set noids [lrange $oids 1 end]
  577. if {[llength $noids]==0} {
  578. set subret [eval $body]
  579. } else {
  580. set subret [lockrow $db $tbl $noids $body]
  581. }
  582. }
  583. lappend ret [list $i $a $b]
  584. }
  585. return [linsert $subret 0 $ret]
  586. }
  587. proc locktblrows {db tbl body} {
  588. set oids [db eval "SELECT oid FROM $tbl"]
  589. lockrow $db $tbl $oids $body
  590. }
  591. set scans [locktblrows db t2 {
  592. execsql {
  593. DELETE FROM t1;
  594. } db2
  595. }]
  596. set error 0
  597. # Test that each SELECT query returned the expected contents of t2.
  598. foreach s $scans {
  599. if {[lsort -integer -index 0 $s]!=$::contents} {
  600. set error 1
  601. }
  602. }
  603. set error
  604. } {0}
  605. catch {db close}
  606. catch {db2 close}
  607. unset -nocomplain contents
  608. #--------------------------------------------------------------------------
  609. # The following tests try to trick the shared-cache code into assuming
  610. # the wrong encoding for a database.
  611. #
  612. file delete -force test.db test.db-journal
  613. ifcapable utf16 {
  614. do_test shared-$av.8.1.1 {
  615. sqlite3 db test.db
  616. execsql {
  617. PRAGMA encoding = 'UTF-16';
  618. SELECT * FROM sqlite_master;
  619. }
  620. } {}
  621. do_test shared-$av.8.1.2 {
  622. string range [execsql {PRAGMA encoding;}] 0 end-2
  623. } {UTF-16}
  624. do_test shared-$av.8.1.3 {
  625. sqlite3 db2 test.db
  626. execsql {
  627. PRAGMA encoding = 'UTF-8';
  628. CREATE TABLE abc(a, b, c);
  629. } db2
  630. } {}
  631. do_test shared-$av.8.1.4 {
  632. execsql {
  633. SELECT * FROM sqlite_master;
  634. }
  635. } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
  636. do_test shared-$av.8.1.5 {
  637. db2 close
  638. execsql {
  639. PRAGMA encoding;
  640. }
  641. } {UTF-8}
  642. file delete -force test2.db test2.db-journal
  643. do_test shared-$av.8.2.1 {
  644. execsql {
  645. ATTACH 'test2.db' AS aux;
  646. SELECT * FROM aux.sqlite_master;
  647. }
  648. } {}
  649. do_test shared-$av.8.2.2 {
  650. sqlite3 db2 test2.db
  651. execsql {
  652. PRAGMA encoding = 'UTF-16';
  653. CREATE TABLE def(d, e, f);
  654. } db2
  655. string range [execsql {PRAGMA encoding;} db2] 0 end-2
  656. } {UTF-16}
  657. catch {db close}
  658. catch {db2 close}
  659. file delete -force test.db test2.db
  660. do_test shared-$av.8.3.2 {
  661. sqlite3 db test.db
  662. execsql { CREATE TABLE def(d, e, f) }
  663. execsql { PRAGMA encoding }
  664. } {UTF-8}
  665. do_test shared-$av.8.3.3 {
  666. set zDb16 "[encoding convertto unicode test.db]\x00\x00"
  667. set db16 [sqlite3_open16 $zDb16 {}]
  668. set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY]
  669. sqlite3_step $stmt
  670. set sql [sqlite3_column_text $stmt 0]
  671. sqlite3_finalize $stmt
  672. set sql
  673. } {CREATE TABLE def(d, e, f)}
  674. do_test shared-$av.8.3.4 {
  675. set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY]
  676. sqlite3_step $stmt
  677. set enc [sqlite3_column_text $stmt 0]
  678. sqlite3_finalize $stmt
  679. set enc
  680. } {UTF-8}
  681. sqlite3_close $db16
  682. # Bug #2547 is causing this to fail.
  683. if 0 {
  684. do_test shared-$av.8.2.3 {
  685. catchsql {
  686. SELECT * FROM aux.sqlite_master;
  687. }
  688. } {1 {attached databases must use the same text encoding as main database}}
  689. }
  690. }
  691. catch {db close}
  692. catch {db2 close}
  693. file delete -force test.db test2.db
  694. #---------------------------------------------------------------------------
  695. # The following tests - shared-9.* - test interactions between TEMP triggers
  696. # and shared-schemas.
  697. #
  698. ifcapable trigger&&tempdb {
  699. do_test shared-$av.9.1 {
  700. sqlite3 db test.db
  701. sqlite3 db2 test.db
  702. execsql {
  703. CREATE TABLE abc(a, b, c);
  704. CREATE TABLE abc_mirror(a, b, c);
  705. CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN
  706. INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c);
  707. END;
  708. INSERT INTO abc VALUES(1, 2, 3);
  709. SELECT * FROM abc_mirror;
  710. }
  711. } {1 2 3}
  712. do_test shared-$av.9.2 {
  713. execsql {
  714. INSERT INTO abc VALUES(4, 5, 6);
  715. SELECT * FROM abc_mirror;
  716. } db2
  717. } {1 2 3}
  718. do_test shared-$av.9.3 {
  719. db close
  720. db2 close
  721. } {}
  722. } ; # End shared-9.*
  723. #---------------------------------------------------------------------------
  724. # The following tests - shared-10.* - test that the library behaves
  725. # correctly when a connection to a shared-cache is closed.
  726. #
  727. do_test shared-$av.10.1 {
  728. # Create a small sample database with two connections to it (db and db2).
  729. file delete -force test.db
  730. sqlite3 db test.db
  731. sqlite3 db2 test.db
  732. execsql {
  733. CREATE TABLE ab(a PRIMARY KEY, b);
  734. CREATE TABLE de(d PRIMARY KEY, e);
  735. INSERT INTO ab VALUES('Chiang Mai', 100000);
  736. INSERT INTO ab VALUES('Bangkok', 8000000);
  737. INSERT INTO de VALUES('Ubon', 120000);
  738. INSERT INTO de VALUES('Khon Kaen', 200000);
  739. }
  740. } {}
  741. do_test shared-$av.10.2 {
  742. # Open a read-transaction with the first connection, a write-transaction
  743. # with the second.
  744. execsql {
  745. BEGIN;
  746. SELECT * FROM ab;
  747. }
  748. execsql {
  749. BEGIN;
  750. INSERT INTO de VALUES('Pataya', 30000);
  751. } db2
  752. } {}
  753. do_test shared-$av.10.3 {
  754. # An external connection should be able to read the database, but not
  755. # prepare a write operation.
  756. if {$::tcl_platform(platform)=="unix"} {
  757. sqlite3 db3 ./test.db
  758. } else {
  759. sqlite3 db3 TEST.DB
  760. }
  761. execsql {
  762. SELECT * FROM ab;
  763. } db3
  764. catchsql {
  765. BEGIN;
  766. INSERT INTO de VALUES('Pataya', 30000);
  767. } db3
  768. } {1 {database is locked}}
  769. do_test shared-$av.10.4 {
  770. # Close the connection with the write-transaction open
  771. db2 close
  772. } {}
  773. do_test shared-$av.10.5 {
  774. # Test that the db2 transaction has been automatically rolled back.
  775. # If it has not the ('Pataya', 30000) entry will still be in the table.
  776. execsql {
  777. SELECT * FROM de;
  778. }
  779. } {Ubon 120000 {Khon Kaen} 200000}
  780. do_test shared-$av.10.5 {
  781. # Closing db2 should have dropped the shared-cache back to a read-lock.
  782. # So db3 should be able to prepare a write...
  783. catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3
  784. } {0 {}}
  785. do_test shared-$av.10.6 {
  786. # ... but not commit it.
  787. catchsql {COMMIT} db3
  788. } {1 {database is locked}}
  789. do_test shared-$av.10.7 {
  790. # Commit the (read-only) db transaction. Check via db3 to make sure the
  791. # contents of table "de" are still as they should be.
  792. execsql {
  793. COMMIT;
  794. }
  795. execsql {
  796. SELECT * FROM de;
  797. } db3
  798. } {Ubon 120000 {Khon Kaen} 200000 Pataya 30000}
  799. do_test shared-$av.10.9 {
  800. # Commit the external transaction.
  801. catchsql {COMMIT} db3
  802. } {0 {}}
  803. integrity_check shared-$av.10.10
  804. do_test shared-$av.10.11 {
  805. db close
  806. db3 close
  807. } {}
  808. do_test shared-$av.11.1 {
  809. file delete -force test.db
  810. sqlite3 db test.db
  811. sqlite3 db2 test.db
  812. execsql {
  813. CREATE TABLE abc(a, b, c);
  814. CREATE TABLE abc2(a, b, c);
  815. BEGIN;
  816. INSERT INTO abc VALUES(1, 2, 3);
  817. }
  818. } {}
  819. do_test shared-$av.11.2 {
  820. catchsql {BEGIN;} db2
  821. catchsql {SELECT * FROM abc;} db2
  822. } {1 {database table is locked: abc}}
  823. do_test shared-$av.11.3 {
  824. catchsql {BEGIN} db2
  825. } {1 {cannot start a transaction within a transaction}}
  826. do_test shared-$av.11.4 {
  827. catchsql {SELECT * FROM abc2;} db2
  828. } {0 {}}
  829. do_test shared-$av.11.5 {
  830. catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2
  831. } {1 {database table is locked}}
  832. do_test shared-$av.11.6 {
  833. catchsql {SELECT * FROM abc2}
  834. } {0 {}}
  835. do_test shared-$av.11.6 {
  836. execsql {
  837. ROLLBACK;
  838. PRAGMA read_uncommitted = 1;
  839. } db2
  840. } {}
  841. do_test shared-$av.11.7 {
  842. execsql {
  843. INSERT INTO abc2 VALUES(4, 5, 6);
  844. INSERT INTO abc2 VALUES(7, 8, 9);
  845. }
  846. } {}
  847. do_test shared-$av.11.8 {
  848. set res [list]
  849. db2 eval {
  850. SELECT abc.a as I, abc2.a as II FROM abc, abc2;
  851. } {
  852. execsql {
  853. DELETE FROM abc WHERE 1;
  854. }
  855. lappend res $I $II
  856. }
  857. set res
  858. } {1 4 {} 7}
  859. if {[llength [info command sqlite3_shared_cache_report]]==1} {
  860. do_test shared-$av.11.9 {
  861. string tolower [sqlite3_shared_cache_report]
  862. } [string tolower [list [file nativename [file normalize test.db]] 2]]
  863. }
  864. do_test shared-$av.11.11 {
  865. db close
  866. db2 close
  867. } {}
  868. # This tests that if it is impossible to free any pages, SQLite will
  869. # exceed the limit set by PRAGMA cache_size.
  870. file delete -force test.db test.db-journal
  871. sqlite3 db test.db
  872. ifcapable pager_pragmas {
  873. do_test shared-$av.12.1 {
  874. execsql {
  875. PRAGMA cache_size = 10;
  876. PRAGMA cache_size;
  877. }
  878. } {10}
  879. }
  880. do_test shared-$av.12.2 {
  881. set ::db_handles [list]
  882. for {set i 1} {$i < 15} {incr i} {
  883. lappend ::db_handles db$i
  884. sqlite3 db$i test.db
  885. execsql "CREATE TABLE db${i}(a, b, c)" db$i
  886. execsql "INSERT INTO db${i} VALUES(1, 2, 3)"
  887. }
  888. } {}
  889. proc nested_select {handles} {
  890. [lindex $handles 0] eval "SELECT * FROM [lindex $handles 0]" {
  891. lappend ::res $a $b $c
  892. if {[llength $handles]>1} {
  893. nested_select [lrange $handles 1 end]
  894. }
  895. }
  896. }
  897. do_test shared-$av.12.3 {
  898. set ::res [list]
  899. nested_select $::db_handles
  900. set ::res
  901. } [string range [string repeat "1 2 3 " [llength $::db_handles]] 0 end-1]
  902. do_test shared-$av.12.X {
  903. db close
  904. foreach h $::db_handles {
  905. $h close
  906. }
  907. } {}
  908. # Internally, locks are acquired on shared B-Tree structures in the order
  909. # that the structures appear in the virtual memory address space. This
  910. # test case attempts to cause the order of the structures in memory
  911. # to be different from the order in which they are attached to a given
  912. # database handle. This covers an extra line or two.
  913. #
  914. do_test shared-$av.13.1 {
  915. file delete -force test2.db test3.db test4.db test5.db
  916. sqlite3 db :memory:
  917. execsql {
  918. ATTACH 'test2.db' AS aux2;
  919. ATTACH 'test3.db' AS aux3;
  920. ATTACH 'test4.db' AS aux4;
  921. ATTACH 'test5.db' AS aux5;
  922. DETACH aux2;
  923. DETACH aux3;
  924. DETACH aux4;
  925. ATTACH 'test2.db' AS aux2;
  926. ATTACH 'test3.db' AS aux3;
  927. ATTACH 'test4.db' AS aux4;
  928. }
  929. } {}
  930. do_test shared-$av.13.2 {
  931. execsql {
  932. CREATE TABLE t1(a, b, c);
  933. CREATE TABLE aux2.t2(a, b, c);
  934. CREATE TABLE aux3.t3(a, b, c);
  935. CREATE TABLE aux4.t4(a, b, c);
  936. CREATE TABLE aux5.t5(a, b, c);
  937. SELECT count(*) FROM
  938. aux2.sqlite_master,
  939. aux3.sqlite_master,
  940. aux4.sqlite_master,
  941. aux5.sqlite_master
  942. }
  943. } {1}
  944. do_test shared-$av.13.3 {
  945. db close
  946. } {}
  947. # Test that nothing horrible happens if a connection to a shared B-Tree
  948. # structure is closed while some other connection has an open cursor.
  949. #
  950. do_test shared-$av.14.1 {
  951. sqlite3 db test.db
  952. sqlite3 db2 test.db
  953. execsql {SELECT name FROM sqlite_master}
  954. } {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}
  955. do_test shared-$av.14.2 {
  956. set res [list]
  957. db eval {SELECT name FROM sqlite_master} {
  958. if {$name eq "db7"} {
  959. db2 close
  960. }
  961. lappend res $name
  962. }
  963. set res
  964. } {db1 db2 db3 db4 db5 db6 db7 db8 db9 db10 db11 db12 db13 db14}
  965. do_test shared-$av.14.3 {
  966. db close
  967. } {}
  968. # Populate a database schema using connection [db]. Then drop it using
  969. # [db2]. This is to try to find any points where shared-schema elements
  970. # are allocated using the lookaside buffer of [db].
  971. #
  972. # Mutexes are enabled for this test as that activates a couple of useful
  973. # assert() statements in the C code.
  974. #
  975. do_test shared-$av-15.1 {
  976. file delete -force test.db
  977. sqlite3 db test.db -fullmutex 1
  978. sqlite3 db2 test.db -fullmutex 1
  979. execsql {
  980. CREATE TABLE t1(a, b, c);
  981. CREATE INDEX i1 ON t1(a, b);
  982. CREATE VIEW v1 AS SELECT * FROM t1;
  983. CREATE VIEW v2 AS SELECT * FROM t1, v1
  984. WHERE t1.c=v1.c GROUP BY t1.a ORDER BY v1.b;
  985. CREATE TRIGGER tr1 AFTER INSERT ON t1
  986. WHEN new.a!=1
  987. BEGIN
  988. DELETE FROM t1 WHERE a=5;
  989. INSERT INTO t1 VALUES(1, 2, 3);
  990. UPDATE t1 SET c=c+1;
  991. END;
  992. INSERT INTO t1 VALUES(5, 6, 7);
  993. INSERT INTO t1 VALUES(8, 9, 10);
  994. INSERT INTO t1 VALUES(11, 12, 13);
  995. ANALYZE;
  996. SELECT * FROM t1;
  997. }
  998. } {1 2 6 8 9 12 1 2 5 11 12 14 1 2 4}
  999. do_test shared-$av-15.2 {
  1000. execsql { DROP TABLE t1 } db2
  1001. } {}
  1002. db close
  1003. db2 close
  1004. }
  1005. sqlite3_enable_shared_cache $::enable_shared_cache
  1006. finish_test