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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18if {[clang_sanitize_address]==0} { 19 do_test misc7-1-misuse { 20 c_misuse_test 21 } {} 22} 23 24do_test misc7-2 { 25 c_realloc_test 26} {} 27 28do_test misc7-3 { 29 c_collation_test 30} {} 31 32# Try to open a directory: 33# 34do_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}} 42 43# Try to open a file with a directory where its journal file should be. 44# 45do_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}} 53db2 close 54 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# 61 62proc use_up_files {} { 63 set ret [list] 64 catch { 65 while 1 { lappend ret [open test.db] } 66 } 67 return $ret 68} 69 70proc 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} 83 84 expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)} 85 } 1 86 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} 96 97execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); } 98db close 99 100if {$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 } 108 109 do_fileopen_test misc7-6.2 { 110 PRAGMA temp.cache_size = 1000; 111 } 112} 113 114# 115# End of tests for out-of-file-descriptors condition. 116#-------------------------------------------------------------------- 117 118sqlite3 db test.db 119execsql { 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} 125 126 127#-------------------------------------------------------------------- 128# Test that the sqlite3_busy_timeout call seems to delay approximately 129# the right amount of time. 130# 131do_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 137 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} 150db2 close 151 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# 162do_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} 172do_test misc7-7.2 { 173 execsql { 174 DETACH aux; 175 } 176} {} 177do_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} 187do_test misc7-7.3 { 188 db close 189 sqlite3 db test.db 190 set ::DB [sqlite3_connection_pointer db] 191 list 192} {} 193 194# Test the UTF-16 version of the "out of memory" message (used when 195# malloc fails during sqlite3_open() ). 196# 197ifcapable utf16 { 198 do_test misc7-8 { 199 encoding convertfrom unicode [sqlite3_errmsg16 0x00000000] 200 } {out of memory} 201} 202 203do_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} {} 210 211#-------------------------------------------------------------------- 212# Improve coverage for vtab code. 213# 214ifcapable vtab { 215 # Run some debug code to improve reported coverage 216 # 217 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 227 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} 234 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 } 251 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} 263 264db close 265forcedelete test.db 266forcedelete test.db-journal 267sqlite3 db test.db 268 269ifcapable 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} 287 288db close 289forcedelete test.db 290forcedelete test.db-journal 291sqlite3 db test.db 292 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# 298do_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} 325do_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} {} 332 333db close 334forcedelete test.db 335forcedelete test.db-journal 336sqlite3 db test.db 337 338do_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] 359 360 if {!$rc || ($rc && [string first "UNIQUE" $msg]==0)} { 361 set msg 362 } else { 363 error $msg 364 } 365} 366 367sqlite3 db test.db 368 369do_test misc7-16.X { 370 execsql { 371 SELECT count(*) FROM t3; 372 } 373} {32} 374 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# 382if {$tcl_platform(platform)!="windows"} { 383 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} { 390 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 } 401 402 db close 403 forcecopy bak.db test.db 404 forcecopy bak.db-journal test.db-journal 405 sqlite3 db test.db 406 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} 424 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 439 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} 449 450# Ticket #2470 451# 452do_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} {} 467 468# Testing boundary conditions on sqlite3_status() 469# 470do_test misc7-19.1 { 471 sqlite3_status -1 0 472} {21 0 0} 473do_test misc7-19.2 { 474 sqlite3_status 1000 0 475} {21 0 0} 476 477 478# sqlite3_global_recover() is a no-op. But we might as well test it 479# if only to get the test coverage. 480# 481do_test misc7-20.1 { 482 sqlite3_global_recover 483} {SQLITE_OK} 484 485# Try to open a really long file name. 486# 487do_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}} 492 493# Try to do hot-journal rollback with a read-only connection. The 494# error code should be SQLITE_READONLY_ROLLBACK. 495# 496do_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}} 511do_test misc7-22.2 { execsql { SELECT * FROM t1 } } {1 2 3 4} 512do_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}} 518do_test misc7-22.4 { 519 sqlite3_extended_errcode db 520} SQLITE_READONLY_ROLLBACK 521 522db close 523forcedelete test.db 524 525finish_test 526