wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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  
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 file delete 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 file delete 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 file delete -force test2.db
156 file delete -force 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 file delete -force test.db
258 file delete -force 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  
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 file delete -force test.db
282 file delete -force 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 file delete -force test.db
327 file delete -force 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 copy_file test.db bak.db
389 copy_file test.db-journal bak.db-journal
390 execsql {
391 COMMIT;
392 }
393  
394 db close
395 copy_file bak.db test.db
396 copy_file 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 file delete -force test.db
488  
489 finish_test