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