Debian Codegraph @

test/misc7.test

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