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