gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/misc7.test (about) 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)!="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)!="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)=="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