Debian Codegraph @

test/shared.test

raw
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
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.
164 if {$::tcl_platform(platform) eq "unix"} {
165 sqlite3 db3 "file:test.db?cache=private" -uri 1
166 } else {
167 sqlite3 db3 TEST.DB
168 }
169 set ::sqlite_open_file_count
170 expr $sqlite_open_file_count-($extrafds_prelock+$extrafds_postlock)
171 } {2}
172 do_test shared-$av.2.2 {
173 # Start read transactions on db and db2 (the shared pager cache). Ensure
174 # db3 cannot write to the database.
175 execsql {
176 BEGIN;
177 SELECT * FROM abc;
178 }
179 execsql {
180 BEGIN;
181 SELECT * FROM abc;
182 } db2
183 catchsql {
184 INSERT INTO abc VALUES(1, 2, 3);
185 } db2
186 } {1 {database table is locked: abc}}
187 do_test shared-$av.2.3 {
188 # Turn db's transaction into a write-transaction. db3 should still be
189 # able to read from table def (but will not see the new row). Connection
190 # db2 should not be able to read def (because of the write-lock).
191
192 # Todo: The failed "INSERT INTO abc ..." statement in the above test
193 # has started a write-transaction on db2 (should this be so?). This
194 # would prevent connection db from starting a write-transaction. So roll the
195 # db2 transaction back and replace it with a new read transaction.
196 execsql {
197 ROLLBACK;
198 BEGIN;
199 SELECT * FROM abc;
200 } db2
201
202 execsql {
203 INSERT INTO def VALUES('VII', 'VIII', 'IX');
204 }
205 concat [
206 catchsql { SELECT * FROM def; } db3
207 ] [
208 catchsql { SELECT * FROM def; } db2
209 ]
210 } {0 {IV V VI} 1 {database table is locked: def}}
211 do_test shared-$av.2.4 {
212 # Commit the open transaction on db. db2 still holds a read-transaction.
213 # This should prevent db3 from writing to the database, but not from
214 # reading.
215 execsql {
216 COMMIT;
217 }
218 concat [
219 catchsql { SELECT * FROM def; } db3
220 ] [
221 catchsql { INSERT INTO def VALUES('X', 'XI', 'XII'); } db3
222 ]
223 } {0 {IV V VI VII VIII IX} 1 {database is locked}}
224
225 catchsql COMMIT db2
226
227 do_test shared-$av.3.1.1 {
228 # This test case starts a linear scan of table 'seq' using a
229 # read-uncommitted connection. In the middle of the scan, rows are added
230 # to the end of the seq table (ahead of the current cursor position).
231 # The uncommitted rows should be included in the results of the scan.
232 execsql "
233 CREATE TABLE seq(i PRIMARY KEY, x);
234 INSERT INTO seq VALUES(1, '[string repeat X 500]');
235 INSERT INTO seq VALUES(2, '[string repeat X 500]');
236 "
237 execsql {SELECT * FROM sqlite_master} db2
238 execsql {PRAGMA read_uncommitted = 1} db2
239
240 set ret [list]
241 db2 eval {SELECT i FROM seq ORDER BY i} {
242 if {$i < 4} {
243 set max [execsql {SELECT max(i) FROM seq}]
244 db eval {
245 INSERT INTO seq SELECT i + :max, x FROM seq;
246 }
247 }
248 lappend ret $i
249 }
250 set ret
251 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
252 do_test shared-$av.3.1.2 {
253 # Another linear scan through table seq using a read-uncommitted connection.
254 # This time, delete each row as it is read. Should not affect the results of
255 # the scan, but the table should be empty after the scan is concluded
256 # (test 3.1.3 verifies this).
257 set ret [list]
258 db2 eval {SELECT i FROM seq} {
259 db eval {DELETE FROM seq WHERE i = :i}
260 lappend ret $i
261 }
262 set ret
263 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
264 do_test shared-$av.3.1.3 {
265 execsql {
266 SELECT * FROM seq;
267 }
268 } {}
269
270 catch {db close}
271 catch {db2 close}
272 catch {db3 close}
273
274 #--------------------------------------------------------------------------
275 # Tests shared-4.* test that the schema locking rules are applied
276 # correctly. i.e.:
277 #
278 # 1. All transactions require a read-lock on the schemas of databases they
279 # access.
280 # 2. Transactions that modify a database schema require a write-lock on that
281 # schema.
282 # 3. It is not possible to compile a statement while another handle has a
283 # write-lock on the schema.
284 #
285
286 # Open two database handles db and db2. Each has a single attach database
287 # (as well as main):
288 #
289 # db.main -> ./test.db
290 # db.test2 -> ./test2.db
291 # db2.main -> ./test2.db
292 # db2.test -> ./test.db
293 #
294 forcedelete test.db
295 forcedelete test2.db
296 forcedelete test2.db-journal
297 sqlite3 db test.db
298 sqlite3 db2 test2.db
299 do_test shared-$av.4.1.1 {
300 set sqlite_open_file_count
301 expr $sqlite_open_file_count-($extrafds_prelock*2)
302 } {2}
303 do_test shared-$av.4.1.2 {
304 execsql {ATTACH 'test2.db' AS test2}
305 set sqlite_open_file_count
306 expr $sqlite_open_file_count-($extrafds_postlock*2)
307 } {2}
308 do_test shared-$av.4.1.3 {
309 execsql {ATTACH 'test.db' AS test} db2
310 set sqlite_open_file_count
311 expr $sqlite_open_file_count-($extrafds_postlock*2)
312 } {2}
313
314 # Sanity check: Create a table in ./test.db via handle db, and test that handle
315 # db2 can "see" the new table immediately. A handle using a separate pager
316 # cache would have to reload the database schema before this were possible.
317 #
318 do_test shared-$av.4.2.1 {
319 execsql {
320 CREATE TABLE abc(a, b, c);
321 CREATE TABLE def(d, e, f);
322 INSERT INTO abc VALUES('i', 'ii', 'iii');
323 INSERT INTO def VALUES('I', 'II', 'III');
324 }
325 } {}
326 do_test shared-$av.4.2.2 {
327 execsql {
328 SELECT * FROM test.abc;
329 } db2
330 } {i ii iii}
331
332 # Open a read-transaction and read from table abc via handle 2. Check that
333 # handle 1 can read table abc. Check that handle 1 cannot modify table abc
334 # or the database schema. Then check that handle 1 can modify table def.
335 #
336 do_test shared-$av.4.3.1 {
337 execsql {
338 BEGIN;
339 SELECT * FROM test.abc;
340 } db2
341 } {i ii iii}
342 do_test shared-$av.4.3.2 {
343 catchsql {
344 INSERT INTO abc VALUES('iv', 'v', 'vi');
345 }
346 } {1 {database table is locked: abc}}
347 do_test shared-$av.4.3.3 {
348 catchsql {
349 CREATE TABLE ghi(g, h, i);
350 }
351 } {1 {database table is locked: sqlite_master}}
352 do_test shared-$av.4.3.3 {
353 catchsql {
354 INSERT INTO def VALUES('IV', 'V', 'VI');
355 }
356 } {0 {}}
357 do_test shared-$av.4.3.4 {
358 # Cleanup: commit the transaction opened by db2.
359 execsql {
360 COMMIT
361 } db2
362 } {}
363
364 # Open a write-transaction using handle 1 and modify the database schema.
365 # Then try to execute a compiled statement to read from the same
366 # database via handle 2 (fails to get the lock on sqlite_master). Also
367 # try to compile a read of the same database using handle 2 (also fails).
368 # Finally, compile a read of the other database using handle 2. This
369 # should also fail.
370 #
371 ifcapable compound {
372 do_test shared-$av.4.4.1.2 {
373 # Sanity check 1: Check that the schema is what we think it is when viewed
374 # via handle 1.
375 execsql {
376 CREATE TABLE test2.ghi(g, h, i);
377 SELECT 'test.db:'||name FROM sqlite_master
378 UNION ALL
379 SELECT 'test2.db:'||name FROM test2.sqlite_master;
380 }
381 } {test.db:abc test.db:def test2.db:ghi}
382 do_test shared-$av.4.4.1.2 {
383 # Sanity check 2: Check that the schema is what we think it is when viewed
384 # via handle 2.
385 execsql {
386 SELECT 'test2.db:'||name FROM sqlite_master
387 UNION ALL
388 SELECT 'test.db:'||name FROM test.sqlite_master;
389 } db2
390 } {test2.db:ghi test.db:abc test.db:def}
391 }
392
393 do_test shared-$av.4.4.2 {
394 set ::DB2 [sqlite3_connection_pointer db2]
395 set sql {SELECT * FROM abc}
396 set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
397 execsql {
398 BEGIN;
399 CREATE TABLE jkl(j, k, l);
400 }
401 sqlite3_step $::STMT1
402 } {SQLITE_ERROR}
403 do_test shared-$av.4.4.3 {
404 sqlite3_finalize $::STMT1
405 } {SQLITE_LOCKED}
406 do_test shared-$av.4.4.4 {
407 set rc [catch {
408 set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
409 } msg]
410 list $rc $msg
411 } {1 {(6) database schema is locked: test}}
412 do_test shared-$av.4.4.5 {
413 set rc [catch {
414 set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY]
415 } msg]
416 list $rc $msg
417 } {1 {(6) database schema is locked: test}}
418
419
420 catch {db2 close}
421 catch {db close}
422
423 #--------------------------------------------------------------------------
424 # Tests shared-5.*
425 #
426 foreach db [list test.db test1.db test2.db test3.db] {
427 forcedelete $db ${db}-journal
428 }
429 do_test shared-$av.5.1.1 {
430 sqlite3 db1 test.db
431 sqlite3 db2 test.db
432 execsql {
433 ATTACH 'test1.db' AS test1;
434 ATTACH 'test2.db' AS test2;
435 ATTACH 'test3.db' AS test3;
436 } db1
437 execsql {
438 ATTACH 'test3.db' AS test3;
439 ATTACH 'test2.db' AS test2;
440 ATTACH 'test1.db' AS test1;
441 } db2
442 } {}
443 do_test shared-$av.5.1.2 {
444 execsql {
445 CREATE TABLE test1.t1(a, b);
446 CREATE INDEX test1.i1 ON t1(a, b);
447 } db1
448 } {}
449 ifcapable view {
450 do_test shared-$av.5.1.3 {
451 execsql {
452 CREATE VIEW test1.v1 AS SELECT * FROM t1;
453 } db1
454 } {}
455 }
456 ifcapable trigger {
457 do_test shared-$av.5.1.4 {
458 execsql {
459 CREATE TRIGGER test1.trig1 AFTER INSERT ON t1 BEGIN
460 INSERT INTO t1 VALUES(new.a, new.b);
461 END;
462 } db1
463 } {}
464 }
465 do_test shared-$av.5.1.5 {
466 execsql {
467 DROP INDEX i1;
468 } db2
469 } {}
470 ifcapable view {
471 do_test shared-$av.5.1.6 {
472 execsql {
473 DROP VIEW v1;
474 } db2
475 } {}
476 }
477 ifcapable trigger {
478 do_test shared-$av.5.1.7 {
479 execsql {
480 DROP TRIGGER trig1;
481 } db2
482 } {}
483 }
484 do_test shared-$av.5.1.8 {
485 execsql {
486 DROP TABLE t1;
487 } db2
488 } {}
489 ifcapable compound {
490 do_test shared-$av.5.1.9 {
491 execsql {
492 SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master
493 } db1
494 } {}
495 }
496
497 #--------------------------------------------------------------------------
498 # Tests shared-6.* test that a query obtains all the read-locks it needs
499 # before starting execution of the query. This means that there is no chance
500 # some rows of data will be returned before a lock fails and SQLITE_LOCK
501 # is returned.
502 #
503 do_test shared-$av.6.1.1 {
504 execsql {
505 CREATE TABLE t1(a, b);
506 CREATE TABLE t2(a, b);
507 INSERT INTO t1 VALUES(1, 2);
508 INSERT INTO t2 VALUES(3, 4);
509 } db1
510 } {}
511 ifcapable compound {
512 do_test shared-$av.6.1.2 {
513 execsql {
514 SELECT * FROM t1 UNION ALL SELECT * FROM t2;
515 } db2
516 } {1 2 3 4}
517 }
518 do_test shared-$av.6.1.3 {
519 # Establish a write lock on table t2 via connection db2. Then make a
520 # UNION all query using connection db1 that first accesses t1, followed
521 # by t2. If the locks are grabbed at the start of the statement (as
522 # they should be), no rows are returned. If (as was previously the case)
523 # they are grabbed as the tables are accessed, the t1 rows will be
524 # returned before the query fails.
525 #
526 execsql {
527 BEGIN;
528 INSERT INTO t2 VALUES(5, 6);
529 } db2
530 set ret [list]
531 catch {
532 db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
533 lappend ret $a $b
534 }
535 }
536 set ret
537 } {}
538 do_test shared-$av.6.1.4 {
539 execsql {
540 COMMIT;
541 BEGIN;
542 INSERT INTO t1 VALUES(7, 8);
543 } db2
544 set ret [list]
545 catch {
546 db1 eval {
547 SELECT (CASE WHEN a>4 THEN (SELECT a FROM t1) ELSE 0 END) AS d FROM t2;
548 } {
549 lappend ret $d
550 }
551 }
552 set ret
553 } {}
554
555 catch {db1 close}
556 catch {db2 close}
557 foreach f [list test.db test2.db] {
558 forcedelete $f ${f}-journal
559 }
560
561 #--------------------------------------------------------------------------
562 # Tests shared-7.* test auto-vacuum does not invalidate cursors from
563 # other shared-cache users when it reorganizes the database on
564 # COMMIT.
565 #
566 do_test shared-$av.7.1 {
567 # This test case sets up a test database in auto-vacuum mode consisting
568 # of two tables, t1 and t2. Both have a single index. Table t1 is
569 # populated first (so consists of pages toward the start of the db file),
570 # t2 second (pages toward the end of the file).
571 sqlite3 db test.db
572 sqlite3 db2 test.db
573 execsql {
574 BEGIN;
575 CREATE TABLE t1(a PRIMARY KEY, b);
576 CREATE TABLE t2(a PRIMARY KEY, b);
577 }
578 set ::contents {}
579 for {set i 0} {$i < 100} {incr i} {
580 set a [string repeat "$i " 20]
581 set b [string repeat "$i " 20]
582 db eval {
583 INSERT INTO t1 VALUES(:a, :b);
584 }
585 lappend ::contents [list [expr $i+1] $a $b]
586 }
587 execsql {
588 INSERT INTO t2 SELECT * FROM t1;
589 COMMIT;
590 }
591 } {}
592 do_test shared-$av.7.2 {
593 # This test case deletes the contents of table t1 (the one at the start of
594 # the file) while many cursors are open on table t2 and its index. All of
595 # the non-root pages will be moved from the end to the start of the file
596 # when the DELETE is committed - this test verifies that moving the pages
597 # does not disturb the open cursors.
598 #
599
600 proc lockrow {db tbl oids body} {
601 set ret [list]
602 db eval "SELECT oid AS i, a, b FROM $tbl ORDER BY a" {
603 if {$i==[lindex $oids 0]} {
604 set noids [lrange $oids 1 end]
605 if {[llength $noids]==0} {
606 set subret [eval $body]
607 } else {
608 set subret [lockrow $db $tbl $noids $body]
609 }
610 }
611 lappend ret [list $i $a $b]
612 }
613 return [linsert $subret 0 $ret]
614 }
615 proc locktblrows {db tbl body} {
616 set oids [db eval "SELECT oid FROM $tbl"]
617 lockrow $db $tbl $oids $body
618 }
619
620 set scans [locktblrows db t2 {
621 execsql {
622 DELETE FROM t1;
623 } db2
624 }]
625 set error 0
626
627 # Test that each SELECT query returned the expected contents of t2.
628 foreach s $scans {
629 if {[lsort -integer -index 0 $s]!=$::contents} {
630 set error 1
631 }
632 }
633 set error
634 } {0}
635
636 catch {db close}
637 catch {db2 close}
638 unset -nocomplain contents
639
640 #--------------------------------------------------------------------------
641 # The following tests try to trick the shared-cache code into assuming
642 # the wrong encoding for a database.
643 #
644 forcedelete test.db test.db-journal
645 ifcapable utf16 {
646 do_test shared-$av.8.1.1 {
647 sqlite3 db test.db
648 execsql {
649 PRAGMA encoding = 'UTF-16';
650 SELECT * FROM sqlite_master;
651 }
652 } {}
653 do_test shared-$av.8.1.2 {
654 string range [execsql {PRAGMA encoding;}] 0 end-2
655 } {UTF-16}
656
657 do_test shared-$av.8.1.3 {
658 sqlite3 db2 test.db
659 execsql {
660 PRAGMA encoding = 'UTF-8';
661 CREATE TABLE abc(a, b, c);
662 } db2
663 } {}
664 do_test shared-$av.8.1.4 {
665 execsql {
666 SELECT * FROM sqlite_master;
667 }
668 } "table abc abc [expr $AUTOVACUUM?3:2] {CREATE TABLE abc(a, b, c)}"
669 do_test shared-$av.8.1.5 {
670 db2 close
671 execsql {
672 PRAGMA encoding;
673 }
674 } {UTF-8}
675
676 forcedelete test2.db test2.db-journal
677 do_test shared-$av.8.2.1 {
678 execsql {
679 ATTACH 'test2.db' AS aux;
680 SELECT * FROM aux.sqlite_master;
681 }
682 } {}
683 do_test shared-$av.8.2.2 {
684 sqlite3 db2 test2.db
685 execsql {
686 PRAGMA encoding = 'UTF-16';
687 CREATE TABLE def(d, e, f);
688 } db2
689 string range [execsql {PRAGMA encoding;} db2] 0 end-2
690 } {UTF-16}
691
692 catch {db close}
693 catch {db2 close}
694 forcedelete test.db test2.db
695
696 do_test shared-$av.8.3.2 {
697 sqlite3 db test.db
698 execsql { CREATE TABLE def(d, e, f) }
699 execsql { PRAGMA encoding }
700 } {UTF-8}
701 do_test shared-$av.8.3.3 {
702 set zDb16 "[encoding convertto unicode test.db]\x00\x00"
703 set db16 [sqlite3_open16 $zDb16 {}]
704
705 set stmt [sqlite3_prepare $db16 "SELECT sql FROM sqlite_master" -1 DUMMY]
706 sqlite3_step $stmt
707 set sql [sqlite3_column_text $stmt 0]
708 sqlite3_finalize $stmt
709 set sql
710 } {CREATE TABLE def(d, e, f)}
711 do_test shared-$av.8.3.4 {
712 set stmt [sqlite3_prepare $db16 "PRAGMA encoding" -1 DUMMY]
713 sqlite3_step $stmt
714 set enc [sqlite3_column_text $stmt 0]
715 sqlite3_finalize $stmt
716 set enc
717 } {UTF-8}
718
719 sqlite3_close $db16
720
721 # Bug #2547 is causing this to fail.
722 if 0 {
723 do_test shared-$av.8.2.3 {
724 catchsql {
725 SELECT * FROM aux.sqlite_master;
726 }
727 } {1 {attached databases must use the same text encoding as main database}}
728 }
729 }
730
731 catch {db close}
732 catch {db2 close}
733 forcedelete test.db test2.db
734
735 #---------------------------------------------------------------------------
736 # The following tests - shared-9.* - test interactions between TEMP triggers
737 # and shared-schemas.
738 #
739 ifcapable trigger&&tempdb {
740
741 do_test shared-$av.9.1 {
742 sqlite3 db test.db
743 sqlite3 db2 test.db
744 execsql {
745 CREATE TABLE abc(a, b, c);
746 CREATE TABLE abc_mirror(a, b, c);
747 CREATE TEMP TRIGGER BEFORE INSERT ON abc BEGIN
748 INSERT INTO abc_mirror(a, b, c) VALUES(new.a, new.b, new.c);
749 END;
750 INSERT INTO abc VALUES(1, 2, 3);
751 SELECT * FROM abc_mirror;
752 }
753 } {1 2 3}
754 do_test shared-$av.9.2 {
755 execsql {
756 INSERT INTO abc VALUES(4, 5, 6);
757 SELECT * FROM abc_mirror;
758 } db2
759 } {1 2 3}
760 do_test shared-$av.9.3 {
761 db close
762 db2 close
763 } {}
764
765 } ; # End shared-9.*
766
767 #---------------------------------------------------------------------------
768 # The following tests - shared-10.* - test that the library behaves
769 # correctly when a connection to a shared-cache is closed.
770 #
771 do_test shared-$av.10.1 {
772 # Create a small sample database with two connections to it (db and db2).
773 forcedelete test.db
774 sqlite3 db test.db
775 sqlite3 db2 test.db
776 execsql {
777 CREATE TABLE ab(a PRIMARY KEY, b);
778 CREATE TABLE de(d PRIMARY KEY, e);
779 INSERT INTO ab VALUES('Chiang Mai', 100000);
780 INSERT INTO ab VALUES('Bangkok', 8000000);
781 INSERT INTO de VALUES('Ubon', 120000);
782 INSERT INTO de VALUES('Khon Kaen', 200000);
783 }
784 } {}
785 do_test shared-$av.10.2 {
786 # Open a read-transaction with the first connection, a write-transaction
787 # with the second.
788 execsql {
789 BEGIN;
790 SELECT * FROM ab;
791 }
792 execsql {
793 BEGIN;
794 INSERT INTO de VALUES('Pataya', 30000);
795 } db2
796 } {}
797 do_test shared-$av.10.3 {
798 # An external connection should be able to read the database, but not
799 # prepare a write operation.
800 if {$::tcl_platform(platform) eq "unix"} {
801 sqlite3 db3 "file:test.db?cache=private" -uri 1
802 } else {
803 sqlite3 db3 TEST.DB
804 }
805 execsql {
806 SELECT * FROM ab;
807 } db3
808 catchsql {
809 BEGIN;
810 INSERT INTO de VALUES('Pataya', 30000);
811 } db3
812 } {1 {database is locked}}
813 do_test shared-$av.10.4 {
814 # Close the connection with the write-transaction open
815 db2 close
816 } {}
817 do_test shared-$av.10.5 {
818 # Test that the db2 transaction has been automatically rolled back.
819 # If it has not the ('Pataya', 30000) entry will still be in the table.
820 execsql {
821 SELECT * FROM de;
822 }
823 } {Ubon 120000 {Khon Kaen} 200000}
824 do_test shared-$av.10.5 {
825 # Closing db2 should have dropped the shared-cache back to a read-lock.
826 # So db3 should be able to prepare a write...
827 catchsql {INSERT INTO de VALUES('Pataya', 30000);} db3
828 } {0 {}}
829 do_test shared-$av.10.6 {
830 # ... but not commit it.
831 catchsql {COMMIT} db3
832 } {1 {database is locked}}
833 do_test shared-$av.10.7 {
834 # Commit the (read-only) db transaction. Check via db3 to make sure the
835 # contents of table "de" are still as they should be.
836 execsql {
837 COMMIT;
838 }
839 execsql {
840 SELECT * FROM de;
841 } db3
842 } {Ubon 120000 {Khon Kaen} 200000 Pataya 30000}
843 do_test shared-$av.10.9 {
844 # Commit the external transaction.
845 catchsql {COMMIT} db3
846 } {0 {}}
847 integrity_check shared-$av.10.10
848 do_test shared-$av.10.11 {
849 db close
850 db3 close
851 } {}
852
853 do_test shared-$av.11.1 {
854 forcedelete test.db
855 sqlite3 db test.db
856 sqlite3 db2 test.db
857 execsql {
858 CREATE TABLE abc(a, b, c);
859 CREATE TABLE abc2(a, b, c);
860 BEGIN;
861 INSERT INTO abc VALUES(1, 2, 3);
862 }
863 } {}
864 do_test shared-$av.11.2 {
865 catchsql {BEGIN;} db2
866 catchsql {SELECT * FROM abc;} db2
867 } {1 {database table is locked: abc}}
868 do_test shared-$av.11.3 {
869 catchsql {BEGIN} db2
870 } {1 {cannot start a transaction within a transaction}}
871 do_test shared-$av.11.4 {
872 catchsql {SELECT * FROM abc2;} db2
873 } {0 {}}
874 do_test shared-$av.11.5 {
875 catchsql {INSERT INTO abc2 VALUES(1, 2, 3);} db2
876 } {1 {database table is locked}}
877 do_test shared-$av.11.6 {
878 catchsql {SELECT * FROM abc2}
879 } {0 {}}
880 do_test shared-$av.11.6 {
881 execsql {
882 ROLLBACK;
883 PRAGMA read_uncommitted = 1;
884 } db2
885 } {}
886 do_test shared-$av.11.7 {
887 execsql {
888 INSERT INTO abc2 VALUES(4, 5, 6);
889 INSERT INTO abc2 VALUES(7, 8, 9);
890 }
891 } {}
892 do_test shared-$av.11.8 {
893 set res [list]
894 db2 eval {
895 SELECT abc.a as I, abc2.a as II FROM abc, abc2;
896 } {
897 execsql {
898 DELETE FROM abc WHERE 1;
899 }
900 lappend res $I $II
901 }
902 set res
903 } {1 4 {} 7}
904 if {[llength [info command sqlite3_shared_cache_report]]==1} {
905 ifcapable curdir {
906 do_test shared-$av.11.9 {
907 string tolower [sqlite3_shared_cache_report]
908 } [string tolower [list [file nativename [file normalize test.db]] 2]]
909 }
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 # Shared cache on a :memory: database. This only works for URI filenames.
1060 #
1061 do_test shared-$av-16.1 {
1062 sqlite3 db1 file::memory: -uri 1
1063 sqlite3 db2 file::memory: -uri 1
1064 db1 eval {
1065 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3);
1066 }
1067 db2 eval {
1068 SELECT x FROM t1 ORDER BY x;
1069 }
1070 } {1 2 3}
1071 do_test shared-$av-16.2 {
1072 db2 eval {
1073 INSERT INTO t1 VALUES(99);
1074 DELETE FROM t1 WHERE x=2;
1075 }
1076 db1 eval {
1077 SELECT x FROM t1 ORDER BY x;
1078 }
1079 } {1 3 99}
1080
1081 # Verify that there is no cache sharing ordinary (non-URI) filenames are
1082 # used.
1083 #
1084 do_test shared-$av-16.3 {
1085 db1 close
1086 db2 close
1087 sqlite3 db1 :memory:
1088 sqlite3 db2 :memory:
1089 db1 eval {
1090 CREATE TABLE t1(x); INSERT INTO t1 VALUES(4),(5),(6);
1091 }
1092 catchsql {
1093 SELECT * FROM t1;
1094 } db2
1095 } {1 {no such table: t1}}
1096
1097 # Shared cache on named memory databases.
1098 #
1099 do_test shared-$av-16.4 {
1100 db1 close
1101 db2 close
1102 forcedelete test.db test.db-wal test.db-journal
1103 sqlite3 db1 file:test.db?mode=memory -uri 1
1104 sqlite3 db2 file:test.db?mode=memory -uri 1
1105 db1 eval {
1106 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3);
1107 }
1108 db2 eval {
1109 SELECT x FROM t1 ORDER BY x;
1110 }
1111 } {1 2 3}
1112 do_test shared-$av-16.5 {
1113 db2 eval {
1114 INSERT INTO t1 VALUES(99);
1115 DELETE FROM t1 WHERE x=2;
1116 }
1117 db1 eval {
1118 SELECT x FROM t1 ORDER BY x;
1119 }
1120 } {1 3 99}
1121 do_test shared-$av-16.6 {
1122 file exists test.db
1123 } {0} ;# Verify that the database is in-memory
1124
1125 # Shared cache on named memory databases with different names.
1126 #
1127 do_test shared-$av-16.7 {
1128 db1 close
1129 db2 close
1130 forcedelete test1.db test2.db
1131 sqlite3 db1 file:test1.db?mode=memory -uri 1
1132 sqlite3 db2 file:test2.db?mode=memory -uri 1
1133 db1 eval {
1134 CREATE TABLE t1(x); INSERT INTO t1 VALUES(1),(2),(3);
1135 }
1136 catchsql {
1137 SELECT x FROM t1 ORDER BY x;
1138 } db2
1139 } {1 {no such table: t1}}
1140 do_test shared-$av-16.8 {
1141 file exists test1.db
1142 } {0} ;# Verify that the database is in-memory
1143
1144 # Shared cache on named memory databases attached to readonly connections.
1145 #
1146 if {![sqlite3 -has-codec]} {
1147 do_test shared-$av-16.8.1 {
1148 db1 close
1149 db2 close
1150
1151 sqlite3 db test1.db
1152 db eval {
1153 CREATE TABLE yy(a, b);
1154 INSERT INTO yy VALUES(77, 88);
1155 }
1156 db close
1157
1158 sqlite3 db1 test1.db -uri 1 -readonly 1
1159 sqlite3 db2 test2.db -uri 1
1160
1161 db1 eval {
1162 ATTACH 'file:mem?mode=memory&cache=shared' AS shared;
1163 CREATE TABLE shared.xx(a, b);
1164 INSERT INTO xx VALUES(55, 66);
1165 }
1166 db2 eval {
1167 ATTACH 'file:mem?mode=memory&cache=shared' AS shared;
1168 SELECT * FROM xx;
1169 }
1170 } {55 66}
1171
1172 do_test shared-$av-16.8.2 { db1 eval { SELECT * FROM yy } } {77 88}
1173 do_test shared-$av-16.8.3 {
1174 list [catch {db1 eval { INSERT INTO yy VALUES(1, 2) }} msg] $msg
1175 } {1 {attempt to write a readonly database}}
1176
1177 db1 close
1178 db2 close
1179 }
1180
1181 } ;# end of autovacuum on/off loop
1182
1183 sqlite3_enable_shared_cache $::enable_shared_cache
1184 finish_test