AryaWu/sqlite
0
1# 2006 September 42#3# The author disclaims copyright to this source code. In place of4# 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.tcl17set testprefix misc718 19if {[clang_sanitize_address]==0} {20 do_test misc7-1-misuse {21 c_misuse_test22 } {}23}24 25do_test misc7-2 {26 c_realloc_test27} {}28 29do_test misc7-3 {30 c_collation_test31} {}32 33# Try to open a directory:34#35do_test misc7-4 {36 delete_file mydir37 file mkdir mydir38 set rc [catch {39 sqlite3 db2 ./mydir40 } msg]41 list $rc $msg42} {1 {unable to open database file}}43 44# Try to open a file with a directory where its journal file should be.45#46if {[atomic_batch_write test.db]==0} {47 do_test misc7-5 {48 delete_file mydir49 file mkdir mydir-journal50 sqlite3 db2 ./mydir51 catchsql {52 CREATE TABLE abc(a, b, c);53 } db254 } {1 {unable to open database file}}55 db2 close56}57 58#--------------------------------------------------------------------59# The following tests, misc7-6.* test the libraries behaviour when60# it cannot open a file. To force this condition, we use up all the61# file-descriptors before running sqlite. This probably only works62# on unix.63#64 65proc use_up_files {} {66 set ret [list]67 catch {68 while 1 { lappend ret [open test.db] }69 }70 return $ret71}72 73proc do_fileopen_test {prefix sql} {74 set fd_list [use_up_files]75 set ::go 176 set ::n 177 set ::sql $sql78 while {$::go} {79 catch {db close}80 do_test ${prefix}.${::n} {81 set rc [catch {82 sqlite db test.db83 db eval $::sql84 } msg]85 if {$rc == 0} {set ::go 0}86 87 expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)}88 } 189 90 close [lindex $fd_list 0]91 set fd_list [lrange $fd_list 1 end]92 incr ::n93 }94 foreach fd $fd_list {95 close $fd96 }97 db close98}99 100execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }101db close102 103if {$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 121sqlite3 db test.db122execsql {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 approximately132# the right amount of time.133#134do_test misc7-7.0 {135 sqlite3 db2 test.db136 sqlite3_busy_timeout [sqlite3_connection_pointer db] 2000137 execsql {138 BEGIN EXCLUSIVE;139 } db2140 141 # Now db2 has an exclusive lock on the database file, and db has142 # a busy-timeout of 2000 milliseconds. So check that trying to143 # 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 microseconds151 lappend result [expr {$delay>1500000 && $delay<4000000}]152} {1 {database is locked} 1}153db2 close154 155#--------------------------------------------------------------------156# Test that nothing goes horribly wrong when attaching a database157# after the omit_readlock pragma has been exercised.158#159# Note: The PRAGMA omit_readlock was an early hack to disable the160# fcntl() calls for read-only databases so that read-only databases could161# 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 tests163# do not really depend on omit_readlock, so we left them in place.164#165do_test misc7-7.1 {166 forcedelete test2.db167 forcedelete test2.db-journal168 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}175do_test misc7-7.2 {176 execsql {177 DETACH aux;178 }179} {}180do_test misc7-7.3 {181 db close182 sqlite3 db test.db -readonly 1183 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}190do_test misc7-7.3 {191 db close192 sqlite3 db test.db193 set ::DB [sqlite3_connection_pointer db]194 list195} {}196 197# Test the UTF-16 version of the "out of memory" message (used when198# malloc fails during sqlite3_open() ).199#200ifcapable utf16 {201 do_test misc7-8 {202 encoding convertfrom unicode [sqlite3_errmsg16 0x00000000]203 } {out of memory}204}205 206do_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#217ifcapable vtab {218 # Run some debug code to improve reported coverage219 #220 221 # set sqlite_where_trace 1222 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 0230 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 than242 # SQLITE_NOMEM from the vtab xBestIndex callback.243 #244 do_ioerr_test misc7-12 -tclprep {245 sqlite3 db2 test.db246 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 close253 } -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 number259 # as the cost of a scan (greater than SQLITE_BIG_DOUBLE in the code).260 #261 do_test misc7-13 {262 sqlite3 db test.db263 register_echo_module [sqlite3_connection_pointer db]264 set ::echo_module_cost 2.0e+99265 execsql {SELECT * FROM t1 WHERE a = 1;}266 } {1 2 3}267 unset ::echo_module_cost268}269 270db close271forcedelete test.db272forcedelete test.db-journal273sqlite3 db test.db274 275ifcapable 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 PLAN283 `--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 PLAN289 `--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 PLAN295 `--SCAN t2 USING INDEX sqlite_autoindex_abc_1296}297}298 299db close300forcedelete test.db301forcedelete test.db-journal302sqlite3 db test.db303 304#--------------------------------------------------------------------305# This is all to force the pager_remove_from_stmt_list() function306# (inside pager.c) to remove a pager from the middle of the307# statement-list.308#309do_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}336do_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 344db close345forcedelete test.db346forcedelete test.db-journal347sqlite3 db test.db348 349do_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 msg373 } else {374 error $msg375 }376}377 378sqlite3 db test.db379 380do_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-access388# to it is denied. This should return SQLITE_BUSY.389#390# These tests do not work on windows due to restrictions in the391# windows file system.392#393if {$tcl_platform(platform) ne "windows"} {394 395 # Some network filesystems (ex: AFP) do not support setting read-only396 # permissions. Only run these tests if full unix permission setting397 # 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.db408 forcecopy test.db-journal bak.db-journal409 execsql {410 COMMIT;411 }412 413 db close414 forcecopy bak.db test.db415 forcecopy bak.db-journal test.db-journal416 sqlite3 db test.db417 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 -permissions426 # 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 permissions428 # 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 0441 db eval {442 pragma writable_schema = true;443 UPDATE sqlite_master 444 SET rootpage = $pending_byte_page445 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_page451 452 do_test misc7-17.4 {453 db close454 sqlite3 db test.db455 catchsql {456 SELECT count(*) FROM t3;457 } 458 } {1 {malformed database schema (t3) - invalid rootpage}}459 }460}461 462# Ticket #2470463#464reset_db465do_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_10471 );472 SELECT a.col_10473 FROM474 (SELECT table_1.col_10 AS col_10 FROM table_1) a,475 (SELECT table_1.col_10, table_2.col_9 AS qcol_9476 FROM table_1, table_2477 GROUP BY table_1.col_10, qcol_9);478 }479} {}480 481# Testing boundary conditions on sqlite3_status()482#483do_test misc7-19.1 {484 sqlite3_status -1 0485} {21 0 0}486do_test misc7-19.2 {487 sqlite3_status 1000 0488} {21 0 0}489 490 491# sqlite3_global_recover() is a no-op. But we might as well test it492# if only to get the test coverage.493#494do_test misc7-20.1 {495 sqlite3_global_recover496} {SQLITE_OK}497 498# Try to open a really long file name.499#500do_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 $msg504} {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#509do_test misc7-22.1 {510 db close511 forcedelete test.db copy.db-journal512 sqlite3 db test.db513 execsql {514 CREATE TABLE t1(a, b);515 INSERT INTO t1 VALUES(1, 2);516 INSERT INTO t1 VALUES(3, 4);517 }518 db close519 sqlite3 db test.db -readonly 1520 catchsql {521 INSERT INTO t1 VALUES(5, 6);522 }523} {1 {attempt to write a readonly database}}524do_test misc7-22.2 { execsql { SELECT * FROM t1 } } {1 2 3 4}525do_test misc7-22.3 { 526 set fd [open test.db-journal w]527 puts $fd [string repeat abc 1000]528 close $fd529 catchsql { SELECT * FROM t1 }530} {1 {attempt to write a readonly database}}531do_test misc7-22.4 { 532 sqlite3_extended_errcode db533} SQLITE_READONLY_ROLLBACK534catch { db close }535forcedelete test.db536 537if {$::tcl_platform(platform) eq "unix"538 && [atomic_batch_write test.db]==0539} {540 reset_db541 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 close548 forcedelete tst549 file mkdir tst550 forcecopy test.db tst/test.db551 file attributes tst -permissions r-xr-xr-x552 } {}553 554 sqlite3 db tst/test.db555 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 db565 } {SQLITE_READONLY_DIRECTORY}566 567 do_test 23.5 {568 db close569 forcedelete tst570 } {}571}572 573finish_test574