3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
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
19 if {[clang_sanitize_address]==0} {
20 do_test misc7-1-misuse {
33 # Try to open a directory:
42 } {1 {unable to open database file}}
44 # Try to open a file with a directory where its journal file should be.
48 file mkdir mydir-journal
51 CREATE TABLE abc(a, b, c);
53 } {1 {unable to open database file}}
56 #--------------------------------------------------------------------
57 # The following tests, misc7-6.* test the libraries behaviour when
58 # it cannot open a file. To force this condition, we use up all the
59 # file-descriptors before running sqlite. This probably only works
63 proc use_up_files {} {
66 while 1 { lappend ret [open test.db] }
71 proc do_fileopen_test {prefix sql} {
72 set fd_list [use_up_files]
78 do_test ${prefix}.${::n} {
83 if {$rc == 0} {set ::go 0}
85 expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)}
88 close [lindex $fd_list 0]
89 set fd_list [lrange $fd_list 1 end]
98 execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }
101 if {$tcl_platform(platform)!="windows"} {
102 do_fileopen_test misc7-6.1 {
104 INSERT INTO abc VALUES(1, 2, 3);
105 INSERT INTO abc VALUES(2, 3, 4);
106 INSERT INTO abc SELECT a+2, b, c FROM abc;
110 do_fileopen_test misc7-6.2 {
111 PRAGMA temp.cache_size = 1000;
116 # End of tests for out-of-file-descriptors condition.
117 #--------------------------------------------------------------------
122 INSERT INTO abc VALUES(1, 2, 3);
123 INSERT INTO abc VALUES(2, 3, 4);
124 INSERT INTO abc SELECT a+2, b, c FROM abc;
128 #--------------------------------------------------------------------
129 # Test that the sqlite3_busy_timeout call seems to delay approximately
130 # the right amount of time.
134 sqlite3_busy_timeout [sqlite3_connection_pointer db] 2000
139 # Now db2 has an exclusive lock on the database file, and db has
140 # a busy-timeout of 2000 milliseconds. So check that trying to
141 # access the database using connection db delays for at least 1500 ms.
144 set result [catchsql {
145 SELECT * FROM sqlite_master;
148 set delay [lindex $tm 0] ;# In microseconds
149 lappend result [expr {$delay>1500000 && $delay<4000000}]
150 } {1 {database is locked} 1}
153 #--------------------------------------------------------------------
154 # Test that nothing goes horribly wrong when attaching a database
155 # after the omit_readlock pragma has been exercised.
157 # Note: The PRAGMA omit_readlock was an early hack to disable the
158 # fcntl() calls for read-only databases so that read-only databases could
159 # be read on broken NFS systems. That pragma has now been removed.
160 # (Use the unix-none VFS as a replacement, if needed.) But these tests
161 # do not really depend on omit_readlock, so we left them in place.
165 forcedelete test2.db-journal
167 PRAGMA omit_readlock = 1;
168 ATTACH 'test2.db' AS aux;
169 CREATE TABLE aux.hello(world);
170 SELECT name FROM aux.sqlite_master;
180 sqlite3 db test.db -readonly 1
182 PRAGMA omit_readlock = 1;
183 ATTACH 'test2.db' AS aux;
184 SELECT name FROM aux.sqlite_master;
185 SELECT name FROM aux.sqlite_master;
191 set ::DB [sqlite3_connection_pointer db]
195 # Test the UTF-16 version of the "out of memory" message (used when
196 # malloc fails during sqlite3_open() ).
200 encoding convertfrom unicode [sqlite3_errmsg16 0x00000000]
207 FROM (SELECT name+1 AS one FROM sqlite_master LIMIT 1 OFFSET 1)
208 WHERE one LIKE 'hello%';
212 #--------------------------------------------------------------------
213 # Improve coverage for vtab code.
216 # Run some debug code to improve reported coverage
219 # set sqlite_where_trace 1
221 register_echo_module [sqlite3_connection_pointer db]
223 CREATE VIRTUAL TABLE t1 USING echo(abc);
224 SELECT a FROM t1 WHERE a = 1 ORDER BY b;
227 set sqlite_where_trace 0
229 # Specify an ORDER BY clause that cannot be indexed.
232 SELECT t1.a, t2.a FROM t1, t1 AS t2 ORDER BY 2 LIMIT 1;
236 # The whole point of this is to test an error code other than
237 # SQLITE_NOMEM from the vtab xBestIndex callback.
239 do_ioerr_test misc7-12 -tclprep {
241 register_echo_module [sqlite3_connection_pointer db2]
243 CREATE TABLE abc(a PRIMARY KEY, b, c);
244 INSERT INTO abc VALUES(1, 2, 3);
245 CREATE VIRTUAL TABLE t1 USING echo(abc);
249 register_echo_module [sqlite3_connection_pointer db]
250 execsql {SELECT * FROM t1 WHERE a = 1;}
253 # The case where the virtual table module returns a very large number
254 # as the cost of a scan (greater than SQLITE_BIG_DOUBLE in the code).
258 register_echo_module [sqlite3_connection_pointer db]
259 set ::echo_module_cost 2.0e+99
260 execsql {SELECT * FROM t1 WHERE a = 1;}
262 unset ::echo_module_cost
267 forcedelete test.db-journal
271 do_execsql_test misc7-14.1 {
272 CREATE TABLE abc(a PRIMARY KEY, b, c);
273 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1;
275 0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?)}
277 do_execsql_test misc7-14.2 {
278 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1;
280 {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?)}
282 do_execsql_test misc7-14.3 {
283 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a;
285 {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1}
291 forcedelete test.db-journal
294 #--------------------------------------------------------------------
295 # This is all to force the pager_remove_from_stmt_list() function
296 # (inside pager.c) to remove a pager from the middle of the
301 PRAGMA cache_size = 10;
303 CREATE TABLE abc(a PRIMARY KEY, b, c);
305 VALUES(randstr(100,100), randstr(100,100), randstr(100,100));
306 INSERT INTO abc SELECT
307 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
308 INSERT INTO abc SELECT
309 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
310 INSERT INTO abc SELECT
311 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
312 INSERT INTO abc SELECT
313 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
314 INSERT INTO abc SELECT
315 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
316 INSERT INTO abc SELECT
317 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
318 INSERT INTO abc SELECT
319 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
320 INSERT INTO abc SELECT
321 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
324 expr {[file size test.db]>10240}
328 DELETE FROM abc WHERE rowid > 12;
329 INSERT INTO abc SELECT
330 randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
336 forcedelete test.db-journal
339 do_ioerr_test misc7-16 -sqlprep {
340 PRAGMA cache_size = 10;
341 PRAGMA default_cache_size = 10;
342 CREATE TABLE t3(a, b, UNIQUE(a, b));
343 INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
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 INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
350 SET b = 'hello world'
351 WHERE rowid >= (SELECT max(rowid)-1 FROM t3);
353 set rc [catch {db eval {
355 PRAGMA cache_size = 10;
356 INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
361 if {!$rc || ($rc && [string first "UNIQUE" $msg]==0)} {
372 SELECT count(*) FROM t3;
376 #----------------------------------------------------------------------
377 # Test the situation where a hot-journal is discovered but write-access
378 # to it is denied. This should return SQLITE_BUSY.
380 # These tests do not work on windows due to restrictions in the
381 # windows file system.
383 if {$tcl_platform(platform)!="windows"} {
385 # Some network filesystems (ex: AFP) do not support setting read-only
386 # permissions. Only run these tests if full unix permission setting
387 # capabilities are supported.
389 file attributes test.db -permissions rw-r--r--
390 if {[file attributes test.db -permissions]==0644} {
395 DELETE FROM t3 WHERE (oid%3)==0;
397 forcecopy test.db bak.db
398 forcecopy test.db-journal bak.db-journal
404 forcecopy bak.db test.db
405 forcecopy bak.db-journal test.db-journal
408 catch {file attributes test.db-journal -permissions r--------}
409 catch {file attributes test.db-journal -readonly 1}
411 SELECT count(*) FROM t3;
413 } {1 {unable to open database file}}
415 # Note that the -readonly flag must be cleared before the -permissions
416 # are set. Otherwise, when using tcl 8.5 on mac, the fact that the
417 # -readonly flag is set causes the attempt to set the permissions
419 catch {file attributes test.db-journal -readonly 0}
420 catch {file attributes test.db-journal -permissions rw-------}
422 SELECT count(*) FROM t3;
426 # sqlite3_test_control_pending_page [expr ($::sqlite_pending_byte / 1024) + 1]
427 set ::pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]
428 sqlite3_test_control_pending_byte $::sqlite_pending_byte
431 pragma writable_schema = true;
433 SET rootpage = $pending_byte_page
434 WHERE type = 'table' AND name = 't3';
437 SELECT rootpage FROM sqlite_master WHERE type = 'table' AND name = 't3';
439 } $::pending_byte_page
445 SELECT count(*) FROM t3;
447 } {1 {database disk image is malformed}}
455 CREATE TABLE table_1 (col_10);
456 CREATE TABLE table_2 (
457 col_1, col_2, col_3, col_4, col_5,
458 col_6, col_7, col_8, col_9, col_10
462 (SELECT table_1.col_10 AS col_10 FROM table_1) a,
463 (SELECT table_1.col_10, table_2.col_9 AS qcol_9
464 FROM table_1, table_2
465 GROUP BY table_1.col_10, qcol_9);
469 # Testing boundary conditions on sqlite3_status()
475 sqlite3_status 1000 0
479 # sqlite3_global_recover() is a no-op. But we might as well test it
480 # if only to get the test coverage.
483 sqlite3_global_recover
486 # Try to open a really long file name.
489 set zFile [file join [get_pwd] "[string repeat abcde 104].db"]
490 set rc [catch {sqlite3 db2 $zFile} msg]
492 } {1 {unable to open database file}}
494 # Try to do hot-journal rollback with a read-only connection. The
495 # error code should be SQLITE_READONLY_ROLLBACK.
499 forcedelete test.db copy.db-journal
502 CREATE TABLE t1(a, b);
503 INSERT INTO t1 VALUES(1, 2);
504 INSERT INTO t1 VALUES(3, 4);
507 sqlite3 db test.db -readonly 1
509 INSERT INTO t1 VALUES(5, 6);
511 } {1 {attempt to write a readonly database}}
512 do_test misc7-22.2 { execsql { SELECT * FROM t1 } } {1 2 3 4}
514 set fd [open test.db-journal w]
515 puts $fd [string repeat abc 1000]
517 catchsql { SELECT * FROM t1 }
518 } {1 {attempt to write a readonly database}}
520 sqlite3_extended_errcode db
521 } SQLITE_READONLY_ROLLBACK
525 if {$::tcl_platform(platform)=="unix"} {
527 do_execsql_test 23.0 {
528 CREATE TABLE t1(x, y);
529 INSERT INTO t1 VALUES(1, 2);
536 forcecopy test.db tst/test.db
537 file attributes tst -permissions r-xr-xr-x
540 sqlite3 db tst/test.db
541 do_execsql_test 23.2 {
545 do_catchsql_test 23.3 {
546 INSERT INTO t1 VALUES(3, 4);
547 } {1 {attempt to write a readonly database}}
550 sqlite3_extended_errcode db
551 } {SQLITE_READONLY_DIRECTORY}