1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/misc7.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,479 @@
1.4 +# 2006 September 4
1.5 +#
1.6 +# Portions Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiaries. All rights reserved.
1.7 +#
1.8 +# The author disclaims copyright to this source code. In place of
1.9 +# a legal notice, here is a blessing:
1.10 +#
1.11 +# May you do good and not evil.
1.12 +# May you find forgiveness for yourself and forgive others.
1.13 +# May you share freely, never taking more than you give.
1.14 +#
1.15 +#***********************************************************************
1.16 +# This file implements regression tests for SQLite library.
1.17 +#
1.18 +# $Id: misc7.test,v 1.24 2008/08/22 13:57:39 pweilbacher Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +do_test misc7-1-misuse {
1.24 + c_misuse_test
1.25 +} {}
1.26 +
1.27 +do_test misc7-2 {
1.28 + c_realloc_test
1.29 +} {}
1.30 +
1.31 +do_test misc7-3 {
1.32 + c_collation_test
1.33 +} {}
1.34 +
1.35 +# Try to open a directory:
1.36 +# Symbian OS: '/' in the file name replaced with '\\'
1.37 +do_test misc7-4 {
1.38 + file delete mydir
1.39 + file mkdir mydir
1.40 + set rc [catch {
1.41 + sqlite3 db2 .\\mydir
1.42 + } msg]
1.43 + list $rc $msg
1.44 +} {1 {unable to open database file}}
1.45 +
1.46 +# Try to open a file with a directory where its journal file should be.
1.47 +# Symbian OS: '/' in the file name replaced with '\\'
1.48 +do_test misc7-5 {
1.49 + file delete mydir
1.50 + file mkdir mydir-journal
1.51 + sqlite3 db2 .\\mydir
1.52 + catchsql {
1.53 + CREATE TABLE abc(a, b, c);
1.54 + } db2
1.55 +} {1 {unable to open database file}}
1.56 +db2 close
1.57 +
1.58 +#--------------------------------------------------------------------
1.59 +# The following tests, misc7-6.* test the libraries behaviour when
1.60 +# it cannot open a file. To force this condition, we use up all the
1.61 +# file-descriptors before running sqlite. This probably only works
1.62 +# on unix.
1.63 +#
1.64 +
1.65 +proc use_up_files {} {
1.66 + set ret [list]
1.67 + catch {
1.68 + while 1 { lappend ret [open test.db] }
1.69 + }
1.70 + return $ret
1.71 +}
1.72 +
1.73 +proc do_fileopen_test {prefix sql} {
1.74 + set fd_list [use_up_files]
1.75 + set ::go 1
1.76 + set ::n 1
1.77 + set ::sql $sql
1.78 + while {$::go} {
1.79 + catch {db close}
1.80 + do_test ${prefix}.${::n} {
1.81 + set rc [catch {
1.82 + sqlite db test.db
1.83 + db eval $::sql
1.84 + } msg]
1.85 + if {$rc == 0} {set ::go 0}
1.86 +
1.87 + expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)}
1.88 + } 1
1.89 +
1.90 + close [lindex $fd_list 0]
1.91 + set fd_list [lrange $fd_list 1 end]
1.92 + incr ::n
1.93 + }
1.94 + foreach fd $fd_list {
1.95 + close $fd
1.96 + }
1.97 + db close
1.98 +}
1.99 +
1.100 +execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }
1.101 +db close
1.102 +
1.103 +if {$tcl_platform(platform)!="windows" && $tcl_platform(platform)!="symbian"} {
1.104 + do_fileopen_test misc7-6.1 {
1.105 + BEGIN;
1.106 + INSERT INTO abc VALUES(1, 2, 3);
1.107 + INSERT INTO abc VALUES(2, 3, 4);
1.108 + INSERT INTO abc SELECT a+2, b, c FROM abc;
1.109 + COMMIT;
1.110 + }
1.111 +
1.112 + do_fileopen_test misc7-6.2 {
1.113 + PRAGMA temp.cache_size = 1000;
1.114 + }
1.115 +}
1.116 +
1.117 +#
1.118 +# End of tests for out-of-file-descriptors condition.
1.119 +#--------------------------------------------------------------------
1.120 +
1.121 +sqlite3 db test.db
1.122 +execsql {
1.123 + DELETE FROM abc;
1.124 + INSERT INTO abc VALUES(1, 2, 3);
1.125 + INSERT INTO abc VALUES(2, 3, 4);
1.126 + INSERT INTO abc SELECT a+2, b, c FROM abc;
1.127 +}
1.128 +
1.129 +
1.130 +#--------------------------------------------------------------------
1.131 +# Test that the sqlite3_busy_timeout call seems to delay approximately
1.132 +# the right amount of time.
1.133 +#
1.134 +do_test misc7-7.0 {
1.135 + sqlite3 db2 test.db
1.136 + sqlite3_busy_timeout [sqlite3_connection_pointer db] 2000
1.137 + execsql {
1.138 + BEGIN EXCLUSIVE;
1.139 + } db2
1.140 +
1.141 + # Now db2 has an exclusive lock on the database file, and db has
1.142 + # a busy-timeout of 2000 milliseconds. So check that trying to
1.143 + # access the database using connection db delays for at least 1500 ms.
1.144 + #
1.145 + set tm [time {
1.146 + set result [catchsql {
1.147 + SELECT * FROM sqlite_master;
1.148 + } db]
1.149 + }]
1.150 + set delay [lindex $tm 0] ;# In microseconds
1.151 + lappend result [expr {$delay>1500000 && $delay<4000000}]
1.152 +} {1 {database is locked} 1}
1.153 +db2 close
1.154 +
1.155 +#--------------------------------------------------------------------
1.156 +# Test that nothing goes horribly wrong when attaching a database
1.157 +# after the omit_readlock pragma has been exercised.
1.158 +#
1.159 +do_test misc7-7.1 {
1.160 + file delete -force test2.db
1.161 + file delete -force test2.db-journal
1.162 + execsql {
1.163 + PRAGMA omit_readlock = 1;
1.164 + ATTACH 'test2.db' AS aux;
1.165 + CREATE TABLE aux.hello(world);
1.166 + SELECT name FROM aux.sqlite_master;
1.167 + }
1.168 +} {hello}
1.169 +do_test misc7-7.2 {
1.170 + execsql {
1.171 + DETACH aux;
1.172 + }
1.173 +} {}
1.174 +
1.175 +# Test the UTF-16 version of the "out of memory" message (used when
1.176 +# malloc fails during sqlite3_open() ).
1.177 +#
1.178 +ifcapable utf16 {
1.179 + do_test misc7-8 {
1.180 + encoding convertfrom unicode [sqlite3_errmsg16 0x00000000]
1.181 + } {out of memory}
1.182 +}
1.183 +
1.184 +do_test misc7-9 {
1.185 + execsql {
1.186 + SELECT *
1.187 + FROM (SELECT name+1 AS one FROM sqlite_master LIMIT 1 OFFSET 1)
1.188 + WHERE one LIKE 'hello%';
1.189 + }
1.190 +} {}
1.191 +
1.192 +#--------------------------------------------------------------------
1.193 +# Improve coverage for vtab code.
1.194 +#
1.195 +ifcapable vtab {
1.196 + # Run some debug code to improve reported coverage
1.197 + #
1.198 +
1.199 + # set sqlite_where_trace 1
1.200 + do_test misc7-10 {
1.201 + register_echo_module [sqlite3_connection_pointer db]
1.202 + execsql {
1.203 + CREATE VIRTUAL TABLE t1 USING echo(abc);
1.204 + SELECT a FROM t1 WHERE a = 1 ORDER BY b;
1.205 + }
1.206 + } {1}
1.207 + set sqlite_where_trace 0
1.208 +
1.209 + # Specify an ORDER BY clause that cannot be indexed.
1.210 + do_test misc7-11 {
1.211 + execsql {
1.212 + SELECT t1.a, t2.a FROM t1, t1 AS t2 ORDER BY 2 LIMIT 1;
1.213 + }
1.214 + } {1 1}
1.215 +
1.216 + # The whole point of this is to test an error code other than
1.217 + # SQLITE_NOMEM from the vtab xBestIndex callback.
1.218 + #
1.219 + do_ioerr_test misc7-12 -tclprep {
1.220 + sqlite3 db2 test.db
1.221 + register_echo_module [sqlite3_connection_pointer db2]
1.222 + db2 eval {
1.223 + CREATE TABLE abc(a PRIMARY KEY, b, c);
1.224 + INSERT INTO abc VALUES(1, 2, 3);
1.225 + CREATE VIRTUAL TABLE t1 USING echo(abc);
1.226 + }
1.227 + db2 close
1.228 + } -tclbody {
1.229 + register_echo_module [sqlite3_connection_pointer db]
1.230 + execsql {SELECT * FROM t1 WHERE a = 1;}
1.231 + }
1.232 +
1.233 + # The case where the virtual table module returns a very large number
1.234 + # as the cost of a scan (greater than SQLITE_BIG_DOUBLE in the code).
1.235 + #
1.236 + do_test misc7-13 {
1.237 + sqlite3 db test.db
1.238 + register_echo_module [sqlite3_connection_pointer db]
1.239 + set ::echo_module_cost 2.0e+99
1.240 + execsql {SELECT * FROM t1 WHERE a = 1;}
1.241 + } {1 2 3}
1.242 + unset ::echo_module_cost
1.243 +}
1.244 +
1.245 +db close
1.246 +file delete -force test.db
1.247 +file delete -force test.db-journal
1.248 +sqlite3 db test.db
1.249 +
1.250 +ifcapable explain {
1.251 + do_test misc7-14.1 {
1.252 + execsql {
1.253 + CREATE TABLE abc(a PRIMARY KEY, b, c);
1.254 + }
1.255 + execsql {
1.256 + EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1;
1.257 + }
1.258 + } {0 0 {TABLE abc AS t2 USING PRIMARY KEY}}
1.259 + do_test misc7-14.2 {
1.260 + execsql {
1.261 + EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1;
1.262 + }
1.263 + } {0 0 {TABLE abc AS t2 WITH INDEX sqlite_autoindex_abc_1}}
1.264 + do_test misc7-14.3 {
1.265 + execsql {
1.266 + EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a;
1.267 + }
1.268 + } {0 0 {TABLE abc AS t2 WITH INDEX sqlite_autoindex_abc_1 ORDER BY}}
1.269 +}
1.270 +
1.271 +db close
1.272 +file delete -force test.db
1.273 +file delete -force test.db-journal
1.274 +sqlite3 db test.db
1.275 +
1.276 +#--------------------------------------------------------------------
1.277 +# This is all to force the pager_remove_from_stmt_list() function
1.278 +# (inside pager.c) to remove a pager from the middle of the
1.279 +# statement-list.
1.280 +#
1.281 +do_test misc7-15.1 {
1.282 + execsql {
1.283 + PRAGMA cache_size = 10;
1.284 + BEGIN;
1.285 + CREATE TABLE abc(a PRIMARY KEY, b, c);
1.286 + INSERT INTO abc
1.287 + VALUES(randstr(100,100), randstr(100,100), randstr(100,100));
1.288 + INSERT INTO abc SELECT
1.289 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.290 + INSERT INTO abc SELECT
1.291 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.292 + INSERT INTO abc SELECT
1.293 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.294 + INSERT INTO abc SELECT
1.295 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.296 + INSERT INTO abc SELECT
1.297 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.298 + INSERT INTO abc SELECT
1.299 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.300 + INSERT INTO abc SELECT
1.301 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.302 + INSERT INTO abc SELECT
1.303 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.304 + COMMIT;
1.305 + }
1.306 + expr {[file size test.db]>10240}
1.307 +} {1}
1.308 +do_test misc7-15.2 {
1.309 + execsql {
1.310 + DELETE FROM abc WHERE rowid > 12;
1.311 + INSERT INTO abc SELECT
1.312 + randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
1.313 + }
1.314 +} {}
1.315 +
1.316 +db close
1.317 +file delete -force test.db
1.318 +file delete -force test.db-journal
1.319 +sqlite3 db test.db
1.320 +
1.321 +do_ioerr_test misc7-16 -sqlprep {
1.322 + PRAGMA cache_size = 10;
1.323 + PRAGMA default_cache_size = 10;
1.324 + CREATE TABLE t3(a, b, UNIQUE(a, b));
1.325 + INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
1.326 + INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
1.327 + INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
1.328 + INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
1.329 + INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
1.330 + INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
1.331 + UPDATE t3
1.332 + SET b = 'hello world'
1.333 + WHERE rowid >= (SELECT max(rowid)-1 FROM t3);
1.334 +} -tclbody {
1.335 + set rc [catch {db eval {
1.336 + BEGIN;
1.337 + PRAGMA cache_size = 10;
1.338 + INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
1.339 + UPDATE t3 SET a = b;
1.340 + COMMIT;
1.341 + }} msg]
1.342 +
1.343 + if {!$rc || ($rc && [string first "columns" $msg]==0)} {
1.344 + set msg
1.345 + } else {
1.346 + error $msg
1.347 + }
1.348 +}
1.349 +
1.350 +sqlite3 db test.db
1.351 +
1.352 +do_test misc7-16.X {
1.353 + execsql {
1.354 + SELECT count(*) FROM t3;
1.355 + }
1.356 +} {32}
1.357 +
1.358 +set sqlite_pager_n_sort_bucket 4
1.359 +do_test misc7-17 {
1.360 + execsql {
1.361 + PRAGMA integrity_check;
1.362 + VACUUM;
1.363 + PRAGMA integrity_check;
1.364 + }
1.365 +} {ok ok}
1.366 +set sqlite_pager_n_sort_bucket 0
1.367 +
1.368 +#----------------------------------------------------------------------
1.369 +# Test the situation where a hot-journal is discovered but write-access
1.370 +# to it is denied. This should return SQLITE_BUSY.
1.371 +#
1.372 +# These tests do not work on windows due to restrictions in the
1.373 +# windows file system.
1.374 +#
1.375 +# TODO: Not clear why this test is crashing tclsqlite3.exe
1.376 +#
1.377 +if {$tcl_platform(platform)!="windows" && $tcl_platform(platform)!="symbian" && $tcl_platform(platform)!="os2"} {
1.378 +
1.379 + # Some network filesystems (ex: AFP) do not support setting read-only
1.380 + # permissions. Only run these tests if full unix permission setting
1.381 + # capabilities are supported.
1.382 + #
1.383 + file attributes test.db -permissions rw-r--r--
1.384 + if {[file attributes test.db -permissions]==0644} {
1.385 +
1.386 + do_test misc7-17.1 {
1.387 + execsql {
1.388 + BEGIN;
1.389 + DELETE FROM t3 WHERE (oid%3)==0;
1.390 + }
1.391 + copy_file test.db bak.db
1.392 + copy_file test.db-journal bak.db-journal
1.393 + execsql {
1.394 + COMMIT;
1.395 + }
1.396 +
1.397 + db close
1.398 + copy_file bak.db test.db
1.399 + copy_file bak.db-journal test.db-journal
1.400 + sqlite3 db test.db
1.401 +
1.402 + catch {file attributes test.db-journal -permissions r--------}
1.403 + catch {file attributes test.db-journal -readonly 1}
1.404 + catchsql {
1.405 + SELECT count(*) FROM t3;
1.406 + }
1.407 + } {1 {database is locked}}
1.408 + do_test misc7-17.2 {
1.409 + # Note that the -readonly flag must be cleared before the -permissions
1.410 + # are set. Otherwise, when using tcl 8.5 on mac, the fact that the
1.411 + # -readonly flag is set causes the attempt to set the permissions
1.412 + # to fail.
1.413 + catch {file attributes test.db-journal -readonly 0}
1.414 + catch {file attributes test.db-journal -permissions rw-------}
1.415 + catchsql {
1.416 + SELECT count(*) FROM t3;
1.417 + }
1.418 + } {0 32}
1.419 +
1.420 + set ::pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]
1.421 + do_test misc7-17.3 {
1.422 + db eval {
1.423 + pragma writable_schema = true;
1.424 + UPDATE sqlite_master
1.425 + SET rootpage = $pending_byte_page
1.426 + WHERE type = 'table' AND name = 't3';
1.427 + }
1.428 + execsql {
1.429 + SELECT rootpage FROM sqlite_master WHERE type = 'table' AND name = 't3';
1.430 + }
1.431 + } $::pending_byte_page
1.432 +
1.433 + do_test misc7-17.4 {
1.434 + db close
1.435 + sqlite3 db test.db
1.436 + catchsql {
1.437 + SELECT count(*) FROM t3;
1.438 + }
1.439 + } {1 {database disk image is malformed}}
1.440 + }
1.441 +}
1.442 +
1.443 +# Ticket #2470
1.444 +#
1.445 +do_test misc7-18.1 {
1.446 + execsql {
1.447 + CREATE TABLE table_1 (col_10);
1.448 + CREATE TABLE table_2 (
1.449 + col_1, col_2, col_3, col_4, col_5,
1.450 + col_6, col_7, col_8, col_9, col_10
1.451 + );
1.452 + SELECT a.col_10
1.453 + FROM
1.454 + (SELECT table_1.col_10 AS col_10 FROM table_1) a,
1.455 + (SELECT table_1.col_10, table_2.col_9 AS qcol_9
1.456 + FROM table_1, table_2
1.457 + GROUP BY table_1.col_10, qcol_9);
1.458 + }
1.459 +} {}
1.460 +
1.461 +# Testing boundary conditions on sqlite3_status()
1.462 +#
1.463 +do_test misc7-19.1 {
1.464 + sqlite3_status -1 0
1.465 +} {21 0 0}
1.466 +do_test misc7-19.2 {
1.467 + sqlite3_status 1000 0
1.468 +} {21 0 0}
1.469 +
1.470 +
1.471 +# sqlite3_global_recover() is a no-op. But we might as well test it
1.472 +# if only to get the test coverage.
1.473 +#
1.474 +do_test misc7-20.1 {
1.475 + sqlite3_global_recover
1.476 +} {SQLITE_OK}
1.477 +
1.478 +
1.479 +db close
1.480 +file delete -force test.db
1.481 +
1.482 +finish_test