wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 | file delete -force 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 | file delete -force test.db |
||
297 | file delete -force test2.db |
||
298 | file delete -force 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 | file delete -force $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 | file delete -force $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 | file delete -force 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 | file delete -force 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 | file delete -force 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 | file delete -force 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 | file delete -force 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 | file delete -force 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 | file delete -force 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 | file delete -force 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 | file delete -force 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 |