| blob | 8fd5fe754620713b95634277515f6965d48f17e6 |
1 # 2006 September 4
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 # This file implements regression tests for SQLite library.
12 #
13 # $Id: misc7.test,v 1.29 2009/07/16 18:21:18 drh Exp $
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
18 if {[clang_sanitize_address]==0} {
19 do_test misc7-1-misuse {
20 c_misuse_test
21 } {}
22 }
24 do_test misc7-2 {
25 c_realloc_test
26 } {}
28 do_test misc7-3 {
29 c_collation_test
30 } {}
32 # Try to open a directory:
33 #
34 do_test misc7-4 {
35 delete_file mydir
36 file mkdir mydir
37 set rc [catch {
38 sqlite3 db2 ./mydir
39 } msg]
40 list $rc $msg
41 } {1 {unable to open database file}}
43 # Try to open a file with a directory where its journal file should be.
44 #
45 do_test misc7-5 {
46 delete_file mydir
47 file mkdir mydir-journal
48 sqlite3 db2 ./mydir
49 catchsql {
50 CREATE TABLE abc(a, b, c);
51 } db2
52 } {1 {unable to open database file}}
53 db2 close
55 #--------------------------------------------------------------------
56 # The following tests, misc7-6.* test the libraries behaviour when
57 # it cannot open a file. To force this condition, we use up all the
58 # file-descriptors before running sqlite. This probably only works
59 # on unix.
60 #
62 proc use_up_files {} {
63 set ret [list]
64 catch {
65 while 1 { lappend ret [open test.db] }
66 }
67 return $ret
68 }
70 proc do_fileopen_test {prefix sql} {
71 set fd_list [use_up_files]
72 set ::go 1
73 set ::n 1
74 set ::sql $sql
75 while {$::go} {
76 catch {db close}
77 do_test ${prefix}.${::n} {
78 set rc [catch {
79 sqlite db test.db
80 db eval $::sql
81 } msg]
82 if {$rc == 0} {set ::go 0}
84 expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)}
85 } 1
87 close [lindex $fd_list 0]
88 set fd_list [lrange $fd_list 1 end]
89 incr ::n
90 }
91 foreach fd $fd_list {
92 close $fd
93 }
94 db close
95 }
97 execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }
98 db close
100 if {$tcl_platform(platform)!="windows"} {
101 do_fileopen_test misc7-6.1 {
102 BEGIN;
103 INSERT INTO abc VALUES(1, 2, 3);
104 INSERT INTO abc VALUES(2, 3, 4);
105 INSERT INTO abc SELECT a+2, b, c FROM abc;
106 COMMIT;
107 }
109 do_fileopen_test misc7-6.2 {
110 PRAGMA temp.cache_size = 1000;
111 }
112 }
114 #
115 # End of tests for out-of-file-descriptors condition.
116 #--------------------------------------------------------------------
118 sqlite3 db test.db
119 execsql {
120 DELETE FROM abc;
121 INSERT INTO abc VALUES(1, 2, 3);
122 INSERT INTO abc VALUES(2, 3, 4);
123 INSERT INTO abc SELECT a+2, b, c FROM abc;
124 }
127 #--------------------------------------------------------------------
128 # Test that the sqlite3_busy_timeout call seems to delay approximately
129 # the right amount of time.
130 #
131 do_test misc7-7.0 {
132 sqlite3 db2 test.db
133 sqlite3_busy_timeout [sqlite3_connection_pointer db] 2000
134 execsql {
135 BEGIN EXCLUSIVE;
136 } db2
138 # Now db2 has an exclusive lock on the database file, and db has
139 # a busy-timeout of 2000 milliseconds. So check that trying to
140 # access the database using connection db delays for at least 1500 ms.
141 #
142 set tm [time {
143 set result [catchsql {
144 SELECT * FROM sqlite_master;
145 } db]
146 }]
147 set delay [lindex $tm 0] ;# In microseconds
148 lappend result [expr {$delay>1500000 && $delay<4000000}]
149 } {1 {database is locked} 1}
150 db2 close
152 #--------------------------------------------------------------------
153 # Test that nothing goes horribly wrong when attaching a database
154 # after the omit_readlock pragma has been exercised.
155 #
156 # Note: The PRAGMA omit_readlock was an early hack to disable the
157 # fcntl() calls for read-only databases so that read-only databases could
158 # be read on broken NFS systems. That pragma has now been removed.
159 # (Use the unix-none VFS as a replacement, if needed.) But these tests
160 # do not really depend on omit_readlock, so we left them in place.
161 #
162 do_test misc7-7.1 {
163 forcedelete test2.db
164 forcedelete test2.db-journal
165 execsql {
166 PRAGMA omit_readlock = 1;
167 ATTACH 'test2.db' AS aux;
168 CREATE TABLE aux.hello(world);
169 SELECT name FROM aux.sqlite_master;
170 }
171 } {hello}
172 do_test misc7-7.2 {
173 execsql {
174 DETACH aux;
175 }
176 } {}
177 do_test misc7-7.3 {
178 db close
179 sqlite3 db test.db -readonly 1
180 execsql {
181 PRAGMA omit_readlock = 1;
182 ATTACH 'test2.db' AS aux;
183 SELECT name FROM aux.sqlite_master;
184 SELECT name FROM aux.sqlite_master;
185 }
186 } {hello hello}
187 do_test misc7-7.3 {
188 db close
189 sqlite3 db test.db
190 set ::DB [sqlite3_connection_pointer db]
191 list
192 } {}
194 # Test the UTF-16 version of the "out of memory" message (used when
195 # malloc fails during sqlite3_open() ).
196 #
197 ifcapable utf16 {
198 do_test misc7-8 {
199 encoding convertfrom unicode [sqlite3_errmsg16 0x00000000]
200 } {out of memory}
201 }
203 do_test misc7-9 {
204 execsql {
205 SELECT *
206 FROM (SELECT name+1 AS one FROM sqlite_master LIMIT 1 OFFSET 1)
207 WHERE one LIKE 'hello%';
208 }
209 } {}
211 #--------------------------------------------------------------------
212 # Improve coverage for vtab code.
213 #
214 ifcapable vtab {
215 # Run some debug code to improve reported coverage
216 #
218 # set sqlite_where_trace 1
219 do_test misc7-10 {
220 register_echo_module [sqlite3_connection_pointer db]
221 execsql {
222 CREATE VIRTUAL TABLE t1 USING echo(abc);
223 SELECT a FROM t1 WHERE a = 1 ORDER BY b;
224 }
225 } {1}
226 set sqlite_where_trace 0
228 # Specify an ORDER BY clause that cannot be indexed.
229 do_test misc7-11 {
230 execsql {
231 SELECT t1.a, t2.a FROM t1, t1 AS t2 ORDER BY 2 LIMIT 1;
232 }
233 } {1 1}
235 # The whole point of this is to test an error code other than
236 # SQLITE_NOMEM from the vtab xBestIndex callback.
237 #
238 do_ioerr_test misc7-12 -tclprep {
239 sqlite3 db2 test.db
240 register_echo_module [sqlite3_connection_pointer db2]
241 db2 eval {
242 CREATE TABLE abc(a PRIMARY KEY, b, c);
243 INSERT INTO abc VALUES(1, 2, 3);
244 CREATE VIRTUAL TABLE t1 USING echo(abc);
245 }
246 db2 close
247 } -tclbody {
248 register_echo_module [sqlite3_connection_pointer db]
249 execsql {SELECT * FROM t1 WHERE a = 1;}
250 }
252 # The case where the virtual table module returns a very large number
253 # as the cost of a scan (greater than SQLITE_BIG_DOUBLE in the code).
254 #
255 do_test misc7-13 {
256 sqlite3 db test.db
257 register_echo_module [sqlite3_connection_pointer db]
258 set ::echo_module_cost 2.0e+99
259 execsql {SELECT * FROM t1 WHERE a = 1;}
260 } {1 2 3}
261 unset ::echo_module_cost
262 }
264 db close
265 forcedelete test.db
266 forcedelete test.db-journal
267 sqlite3 db test.db
269 ifcapable explain {
270 do_execsql_test misc7-14.1 {
271 CREATE TABLE abc(a PRIMARY KEY, b, c);
272 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1;
273 } {
274 0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?)}
275 }
276 do_execsql_test misc7-14.2 {
277 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1;
278 } {0 0 0
279 {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?)}
280 }
281 do_execsql_test misc7-14.3 {
282 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a;
283 } {0 0 0
284 {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1}
285 }
286 }
288 db close
289 forcedelete test.db
290 forcedelete test.db-journal
291 sqlite3 db test.db
293 #--------------------------------------------------------------------
294 # This is all to force the pager_remove_from_stmt_list() function
295 # (inside pager.c) to remove a pager from the middle of the
296 # statement-list.
297 #
298 do_test misc7-15.1 {
299 execsql {
300 PRAGMA cache_size = 10;
301 BEGIN;
302 CREATE TABLE abc(a PRIMARY KEY, b, c);
303 INSERT INTO abc
304 VALUES(randstr(100,100), randstr(100,100), randstr(100,100));
305 INSERT INTO abc SELECT
306 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
307 INSERT INTO abc SELECT
308 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
309 INSERT INTO abc SELECT
310 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
311 INSERT INTO abc SELECT
312 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
313 INSERT INTO abc SELECT
314 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
315 INSERT INTO abc SELECT
316 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
317 INSERT INTO abc SELECT
318 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
319 INSERT INTO abc SELECT
320 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
321 COMMIT;
322 }
323 expr {[file size test.db]>10240}
324 } {1}
325 do_test misc7-15.2 {
326 execsql {
327 DELETE FROM abc WHERE rowid > 12;
328 INSERT INTO abc SELECT
329 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
330 }
331 } {}
333 db close
334 forcedelete test.db
335 forcedelete test.db-journal
336 sqlite3 db test.db
338 do_ioerr_test misc7-16 -sqlprep {
339 PRAGMA cache_size = 10;
340 PRAGMA default_cache_size = 10;
341 CREATE TABLE t3(a, b, UNIQUE(a, b));
342 INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
343 INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
344 INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
345 INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
346 INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
347 INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
348 UPDATE t3
349 SET b = 'hello world'
350 WHERE rowid >= (SELECT max(rowid)-1 FROM t3);
351 } -tclbody {
352 set rc [catch {db eval {
353 BEGIN;
354 PRAGMA cache_size = 10;
355 INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
356 UPDATE t3 SET a = b;
357 COMMIT;
358 }} msg]
360 if {!$rc || ($rc && [string first "UNIQUE" $msg]==0)} {
361 set msg
362 } else {
363 error $msg
364 }
365 }
367 sqlite3 db test.db
369 do_test misc7-16.X {
370 execsql {
371 SELECT count(*) FROM t3;
372 }
373 } {32}
375 #----------------------------------------------------------------------
376 # Test the situation where a hot-journal is discovered but write-access
377 # to it is denied. This should return SQLITE_BUSY.
378 #
379 # These tests do not work on windows due to restrictions in the
380 # windows file system.
381 #
382 if {$tcl_platform(platform)!="windows"} {
384 # Some network filesystems (ex: AFP) do not support setting read-only
385 # permissions. Only run these tests if full unix permission setting
386 # capabilities are supported.
387 #
388 file attributes test.db -permissions rw-r--r--
389 if {[file attributes test.db -permissions]==0644} {
391 do_test misc7-17.1 {
392 execsql {
393 BEGIN;
394 DELETE FROM t3 WHERE (oid%3)==0;
395 }
396 forcecopy test.db bak.db
397 forcecopy test.db-journal bak.db-journal
398 execsql {
399 COMMIT;
400 }
402 db close
403 forcecopy bak.db test.db
404 forcecopy bak.db-journal test.db-journal
405 sqlite3 db test.db
407 catch {file attributes test.db-journal -permissions r--------}
408 catch {file attributes test.db-journal -readonly 1}
409 catchsql {
410 SELECT count(*) FROM t3;
411 }
412 } {1 {unable to open database file}}
413 do_test misc7-17.2 {
414 # Note that the -readonly flag must be cleared before the -permissions
415 # are set. Otherwise, when using tcl 8.5 on mac, the fact that the
416 # -readonly flag is set causes the attempt to set the permissions
417 # to fail.
418 catch {file attributes test.db-journal -readonly 0}
419 catch {file attributes test.db-journal -permissions rw-------}
420 catchsql {
421 SELECT count(*) FROM t3;
422 }
423 } {0 32}
425 # sqlite3_test_control_pending_page [expr ($::sqlite_pending_byte / 1024) + 1]
426 set ::pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]
427 sqlite3_test_control_pending_byte $::sqlite_pending_byte
428 do_test misc7-17.3 {
429 db eval {
430 pragma writable_schema = true;
431 UPDATE sqlite_master
432 SET rootpage = $pending_byte_page
433 WHERE type = 'table' AND name = 't3';
434 }
435 execsql {
436 SELECT rootpage FROM sqlite_master WHERE type = 'table' AND name = 't3';
437 }
438 } $::pending_byte_page
440 do_test misc7-17.4 {
441 db close
442 sqlite3 db test.db
443 catchsql {
444 SELECT count(*) FROM t3;
445 }
446 } {1 {database disk image is malformed}}
447 }
448 }
450 # Ticket #2470
451 #
452 do_test misc7-18.1 {
453 execsql {
454 CREATE TABLE table_1 (col_10);
455 CREATE TABLE table_2 (
456 col_1, col_2, col_3, col_4, col_5,
457 col_6, col_7, col_8, col_9, col_10
458 );
459 SELECT a.col_10
460 FROM
461 (SELECT table_1.col_10 AS col_10 FROM table_1) a,
462 (SELECT table_1.col_10, table_2.col_9 AS qcol_9
463 FROM table_1, table_2
464 GROUP BY table_1.col_10, qcol_9);
465 }
466 } {}
468 # Testing boundary conditions on sqlite3_status()
469 #
470 do_test misc7-19.1 {
471 sqlite3_status -1 0
472 } {21 0 0}
473 do_test misc7-19.2 {
474 sqlite3_status 1000 0
475 } {21 0 0}
478 # sqlite3_global_recover() is a no-op. But we might as well test it
479 # if only to get the test coverage.
480 #
481 do_test misc7-20.1 {
482 sqlite3_global_recover
483 } {SQLITE_OK}
485 # Try to open a really long file name.
486 #
487 do_test misc7-21.1 {
488 set zFile [file join [get_pwd] "[string repeat abcde 104].db"]
489 set rc [catch {sqlite3 db2 $zFile} msg]
490 list $rc $msg
491 } {1 {unable to open database file}}
493 # Try to do hot-journal rollback with a read-only connection. The
494 # error code should be SQLITE_READONLY_ROLLBACK.
495 #
496 do_test misc7-22.1 {
497 db close
498 forcedelete test.db copy.db-journal
499 sqlite3 db test.db
500 execsql {
501 CREATE TABLE t1(a, b);
502 INSERT INTO t1 VALUES(1, 2);
503 INSERT INTO t1 VALUES(3, 4);
504 }
505 db close
506 sqlite3 db test.db -readonly 1
507 catchsql {
508 INSERT INTO t1 VALUES(5, 6);
509 }
510 } {1 {attempt to write a readonly database}}
511 do_test misc7-22.2 { execsql { SELECT * FROM t1 } } {1 2 3 4}
512 do_test misc7-22.3 {
513 set fd [open test.db-journal w]
514 puts $fd [string repeat abc 1000]
515 close $fd
516 catchsql { SELECT * FROM t1 }
517 } {1 {attempt to write a readonly database}}
518 do_test misc7-22.4 {
519 sqlite3_extended_errcode db
520 } SQLITE_READONLY_ROLLBACK
522 db close
523 forcedelete test.db
525 finish_test