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