sl@0: # 2006 September 4
sl@0: #
sl@0: # Portions Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiaries. All rights reserved.
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.
sl@0: #
sl@0: # $Id: misc7.test,v 1.24 2008/08/22 13:57:39 pweilbacher Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: do_test misc7-1-misuse {
sl@0:   c_misuse_test
sl@0: } {}
sl@0: 
sl@0: do_test misc7-2 {
sl@0:   c_realloc_test
sl@0: } {}
sl@0: 
sl@0: do_test misc7-3 {
sl@0:   c_collation_test
sl@0: } {}
sl@0: 
sl@0: # Try to open a directory:
sl@0: # Symbian OS: '/' in the file name replaced with '\\'
sl@0: do_test misc7-4 {
sl@0:   file delete mydir
sl@0:   file mkdir mydir
sl@0:   set rc [catch {
sl@0:     sqlite3 db2 .\\mydir
sl@0:   } msg]
sl@0:   list $rc $msg
sl@0: } {1 {unable to open database file}}
sl@0: 
sl@0: # Try to open a file with a directory where its journal file should be.
sl@0: # Symbian OS: '/' in the file name replaced with '\\'
sl@0: do_test misc7-5 {
sl@0:   file delete mydir
sl@0:   file mkdir mydir-journal
sl@0:   sqlite3 db2 .\\mydir
sl@0:   catchsql {
sl@0:     CREATE TABLE abc(a, b, c);
sl@0:   } db2
sl@0: } {1 {unable to open database file}}
sl@0: db2 close
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # The following tests, misc7-6.* test the libraries behaviour when
sl@0: # it cannot open a file. To force this condition, we use up all the
sl@0: # file-descriptors before running sqlite. This probably only works
sl@0: # on unix.
sl@0: #
sl@0: 
sl@0: proc use_up_files {} {
sl@0:   set ret [list]
sl@0:   catch {
sl@0:     while 1 { lappend ret [open test.db] }
sl@0:   }
sl@0:   return $ret
sl@0: }
sl@0: 
sl@0: proc do_fileopen_test {prefix sql} {
sl@0:   set fd_list [use_up_files]
sl@0:   set ::go 1
sl@0:   set ::n 1
sl@0:   set ::sql $sql
sl@0:   while {$::go} {
sl@0:     catch {db close}
sl@0:     do_test ${prefix}.${::n} {
sl@0:       set rc [catch {
sl@0:         sqlite db test.db
sl@0:         db eval $::sql
sl@0:       } msg]
sl@0:       if {$rc == 0} {set ::go 0}
sl@0:   
sl@0:       expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)}
sl@0:     } 1
sl@0:   
sl@0:     close [lindex $fd_list 0]
sl@0:     set fd_list [lrange $fd_list 1 end]
sl@0:     incr ::n
sl@0:   }
sl@0:   foreach fd $fd_list {
sl@0:     close $fd
sl@0:   }
sl@0:   db close
sl@0: }
sl@0: 
sl@0: execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }
sl@0: db close
sl@0: 
sl@0: if {$tcl_platform(platform)!="windows" && $tcl_platform(platform)!="symbian"} {
sl@0:   do_fileopen_test misc7-6.1 {
sl@0:     BEGIN;
sl@0:     INSERT INTO abc VALUES(1, 2, 3);
sl@0:     INSERT INTO abc VALUES(2, 3, 4);
sl@0:     INSERT INTO abc SELECT a+2, b, c FROM abc;
sl@0:     COMMIT;
sl@0:   }
sl@0:   
sl@0:   do_fileopen_test misc7-6.2 {
sl@0:     PRAGMA temp.cache_size = 1000;
sl@0:   }
sl@0: }
sl@0: 
sl@0: #
sl@0: # End of tests for out-of-file-descriptors condition.
sl@0: #--------------------------------------------------------------------
sl@0: 
sl@0: sqlite3 db test.db
sl@0: execsql {
sl@0:   DELETE FROM abc;
sl@0:   INSERT INTO abc VALUES(1, 2, 3);
sl@0:   INSERT INTO abc VALUES(2, 3, 4);
sl@0:   INSERT INTO abc SELECT a+2, b, c FROM abc;
sl@0: }
sl@0:   
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # Test that the sqlite3_busy_timeout call seems to delay approximately
sl@0: # the right amount of time.
sl@0: #
sl@0: do_test misc7-7.0 {
sl@0:   sqlite3 db2 test.db
sl@0:   sqlite3_busy_timeout [sqlite3_connection_pointer db] 2000
sl@0:   execsql {
sl@0:     BEGIN EXCLUSIVE;
sl@0:   } db2
sl@0: 
sl@0:   # Now db2 has an exclusive lock on the database file, and db has
sl@0:   # a busy-timeout of 2000 milliseconds. So check that trying to
sl@0:   # access the database using connection db delays for at least 1500 ms.
sl@0:   #
sl@0:   set tm [time {
sl@0:     set result [catchsql {
sl@0:         SELECT * FROM sqlite_master;
sl@0:       } db]
sl@0:   }]
sl@0:   set delay [lindex $tm 0]  ;# In microseconds
sl@0:   lappend result [expr {$delay>1500000 && $delay<4000000}]
sl@0: } {1 {database is locked} 1}
sl@0: db2 close
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # Test that nothing goes horribly wrong when attaching a database
sl@0: # after the omit_readlock pragma has been exercised.
sl@0: #
sl@0: do_test misc7-7.1 {
sl@0:   file delete -force test2.db
sl@0:   file delete -force test2.db-journal
sl@0:   execsql {
sl@0:     PRAGMA omit_readlock = 1;
sl@0:     ATTACH 'test2.db' AS aux;
sl@0:     CREATE TABLE aux.hello(world);
sl@0:     SELECT name FROM aux.sqlite_master;
sl@0:   }
sl@0: } {hello}
sl@0: do_test misc7-7.2 {
sl@0:   execsql {
sl@0:     DETACH aux;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # Test the UTF-16 version of the "out of memory" message (used when
sl@0: # malloc fails during sqlite3_open() ).
sl@0: #
sl@0: ifcapable utf16 {
sl@0:   do_test misc7-8 {
sl@0:     encoding convertfrom unicode [sqlite3_errmsg16 0x00000000]
sl@0:   } {out of memory}
sl@0: }
sl@0: 
sl@0: do_test misc7-9 {
sl@0:   execsql {
sl@0:     SELECT * 
sl@0:     FROM (SELECT name+1 AS one FROM sqlite_master LIMIT 1 OFFSET 1) 
sl@0:     WHERE one LIKE 'hello%';
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # Improve coverage for vtab code.
sl@0: #
sl@0: ifcapable vtab {
sl@0:   # Run some debug code to improve reported coverage
sl@0:   #
sl@0: 
sl@0:   # set sqlite_where_trace 1
sl@0:   do_test misc7-10 {
sl@0:     register_echo_module [sqlite3_connection_pointer db]
sl@0:     execsql {
sl@0:       CREATE VIRTUAL TABLE t1 USING echo(abc);
sl@0:       SELECT a FROM t1 WHERE a = 1 ORDER BY b;
sl@0:     }
sl@0:   } {1}
sl@0:   set sqlite_where_trace 0
sl@0: 
sl@0:   # Specify an ORDER BY clause that cannot be indexed.
sl@0:   do_test misc7-11 {
sl@0:     execsql {
sl@0:       SELECT t1.a, t2.a FROM t1, t1 AS t2 ORDER BY 2 LIMIT 1;
sl@0:     }
sl@0:   } {1 1}
sl@0: 
sl@0:   # The whole point of this is to test an error code other than
sl@0:   # SQLITE_NOMEM from the vtab xBestIndex callback.
sl@0:   #
sl@0:   do_ioerr_test misc7-12 -tclprep {
sl@0:     sqlite3 db2 test.db
sl@0:     register_echo_module [sqlite3_connection_pointer db2]
sl@0:     db2 eval {
sl@0:       CREATE TABLE abc(a PRIMARY KEY, b, c);
sl@0:       INSERT INTO abc VALUES(1, 2, 3);
sl@0:       CREATE VIRTUAL TABLE t1 USING echo(abc);
sl@0:     }
sl@0:     db2 close
sl@0:   } -tclbody {
sl@0:     register_echo_module [sqlite3_connection_pointer db]
sl@0:     execsql {SELECT * FROM t1 WHERE a = 1;}
sl@0:   } 
sl@0: 
sl@0:   # The case where the virtual table module returns a very large number
sl@0:   # as the cost of a scan (greater than SQLITE_BIG_DOUBLE in the code).
sl@0:   #
sl@0:   do_test misc7-13 {
sl@0:     sqlite3 db test.db
sl@0:     register_echo_module [sqlite3_connection_pointer db]
sl@0:     set ::echo_module_cost 2.0e+99
sl@0:     execsql {SELECT * FROM t1 WHERE a = 1;}
sl@0:   } {1 2 3}
sl@0:   unset ::echo_module_cost
sl@0: }
sl@0: 
sl@0: db close
sl@0: file delete -force test.db
sl@0: file delete -force test.db-journal
sl@0: sqlite3 db test.db
sl@0: 
sl@0: ifcapable explain {
sl@0:   do_test misc7-14.1 {
sl@0:     execsql {
sl@0:       CREATE TABLE abc(a PRIMARY KEY, b, c);
sl@0:     }
sl@0:     execsql {
sl@0:       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1;
sl@0:     }
sl@0:   } {0 0 {TABLE abc AS t2 USING PRIMARY KEY}}
sl@0:   do_test misc7-14.2 {
sl@0:     execsql {
sl@0:       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1;
sl@0:     }
sl@0:   } {0 0 {TABLE abc AS t2 WITH INDEX sqlite_autoindex_abc_1}}
sl@0:   do_test misc7-14.3 {
sl@0:     execsql {
sl@0:       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a;
sl@0:     }
sl@0:   } {0 0 {TABLE abc AS t2 WITH INDEX sqlite_autoindex_abc_1 ORDER BY}}
sl@0: }
sl@0: 
sl@0: db close
sl@0: file delete -force test.db
sl@0: file delete -force test.db-journal
sl@0: sqlite3 db test.db
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # This is all to force the pager_remove_from_stmt_list() function
sl@0: # (inside pager.c) to remove a pager from the middle of the
sl@0: # statement-list.
sl@0: #
sl@0: do_test misc7-15.1 {
sl@0:   execsql {
sl@0:     PRAGMA cache_size = 10;
sl@0:     BEGIN;
sl@0:     CREATE TABLE abc(a PRIMARY KEY, b, c);
sl@0:     INSERT INTO abc 
sl@0:     VALUES(randstr(100,100), randstr(100,100), randstr(100,100));
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:     COMMIT;
sl@0:   }
sl@0:   expr {[file size test.db]>10240}
sl@0: } {1}
sl@0: do_test misc7-15.2 {
sl@0:   execsql {
sl@0:     DELETE FROM abc WHERE rowid > 12;
sl@0:     INSERT INTO abc SELECT 
sl@0:             randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: db close
sl@0: file delete -force test.db
sl@0: file delete -force test.db-journal
sl@0: sqlite3 db test.db
sl@0: 
sl@0: do_ioerr_test misc7-16 -sqlprep {
sl@0:    PRAGMA cache_size = 10;
sl@0:    PRAGMA default_cache_size = 10;
sl@0:    CREATE TABLE t3(a, b, UNIQUE(a, b));
sl@0:    INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
sl@0:    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
sl@0:    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
sl@0:    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
sl@0:    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
sl@0:    INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
sl@0:    UPDATE t3 
sl@0:    SET b = 'hello world'
sl@0:    WHERE rowid >= (SELECT max(rowid)-1 FROM t3);
sl@0: } -tclbody {
sl@0:   set rc [catch {db eval {
sl@0:     BEGIN;
sl@0:       PRAGMA cache_size = 10;
sl@0:       INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
sl@0:       UPDATE t3 SET a = b;
sl@0:     COMMIT;
sl@0:   }} msg]
sl@0: 
sl@0:   if {!$rc || ($rc && [string first "columns" $msg]==0)} {
sl@0:     set msg
sl@0:   } else {
sl@0:     error $msg
sl@0:   }
sl@0: }
sl@0: 
sl@0: sqlite3 db test.db
sl@0: 
sl@0: do_test misc7-16.X {
sl@0:   execsql {
sl@0:     SELECT count(*) FROM t3;
sl@0:   }
sl@0: } {32}
sl@0: 
sl@0: set sqlite_pager_n_sort_bucket 4
sl@0: do_test misc7-17 {
sl@0:   execsql {
sl@0:     PRAGMA integrity_check;
sl@0:     VACUUM;
sl@0:     PRAGMA integrity_check;
sl@0:   }
sl@0: } {ok ok}
sl@0: set sqlite_pager_n_sort_bucket 0
sl@0: 
sl@0: #----------------------------------------------------------------------
sl@0: # Test the situation where a hot-journal is discovered but write-access
sl@0: # to it is denied. This should return SQLITE_BUSY.
sl@0: #
sl@0: # These tests do not work on windows due to restrictions in the
sl@0: # windows file system.
sl@0: #
sl@0: # TODO: Not clear why this test is crashing tclsqlite3.exe
sl@0: #
sl@0: if {$tcl_platform(platform)!="windows" && $tcl_platform(platform)!="symbian" && $tcl_platform(platform)!="os2"} {
sl@0: 
sl@0:   # Some network filesystems (ex: AFP) do not support setting read-only
sl@0:   # permissions.  Only run these tests if full unix permission setting
sl@0:   # capabilities are supported.
sl@0:   #
sl@0:   file attributes test.db -permissions rw-r--r--
sl@0:   if {[file attributes test.db -permissions]==0644} {
sl@0: 
sl@0:     do_test misc7-17.1 {
sl@0:       execsql {
sl@0:         BEGIN;
sl@0:         DELETE FROM t3 WHERE (oid%3)==0;
sl@0:       }
sl@0:       copy_file test.db bak.db
sl@0:       copy_file test.db-journal bak.db-journal
sl@0:       execsql {
sl@0:         COMMIT;
sl@0:       }
sl@0:     
sl@0:       db close
sl@0:       copy_file bak.db test.db
sl@0:       copy_file bak.db-journal test.db-journal
sl@0:       sqlite3 db test.db
sl@0:     
sl@0:       catch {file attributes test.db-journal -permissions r--------}
sl@0:       catch {file attributes test.db-journal -readonly 1}
sl@0:       catchsql {
sl@0:         SELECT count(*) FROM t3;
sl@0:       }
sl@0:     } {1 {database is locked}}
sl@0:     do_test misc7-17.2 {
sl@0:       # Note that the -readonly flag must be cleared before the -permissions
sl@0:       # are set. Otherwise, when using tcl 8.5 on mac, the fact that the 
sl@0:       # -readonly flag is set causes the attempt to set the permissions
sl@0:       # to fail.
sl@0:       catch {file attributes test.db-journal -readonly 0}
sl@0:       catch {file attributes test.db-journal -permissions rw-------}
sl@0:       catchsql {
sl@0:         SELECT count(*) FROM t3;
sl@0:       }
sl@0:     } {0 32}
sl@0:     
sl@0:     set ::pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]
sl@0:     do_test misc7-17.3 {
sl@0:       db eval {
sl@0:         pragma writable_schema = true;
sl@0:         UPDATE sqlite_master 
sl@0:           SET rootpage = $pending_byte_page
sl@0:           WHERE type = 'table' AND name = 't3';
sl@0:       }
sl@0:       execsql {
sl@0:         SELECT rootpage FROM sqlite_master WHERE type = 'table' AND name = 't3';
sl@0:       }
sl@0:     } $::pending_byte_page
sl@0:     
sl@0:     do_test misc7-17.4 {
sl@0:       db close
sl@0:       sqlite3 db test.db
sl@0:       catchsql {
sl@0:         SELECT count(*) FROM t3;
sl@0:       } 
sl@0:     } {1 {database disk image is malformed}}
sl@0:   }
sl@0: }
sl@0: 
sl@0: # Ticket #2470
sl@0: #
sl@0: do_test misc7-18.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE table_1 (col_10);
sl@0:     CREATE TABLE table_2 (
sl@0:       col_1, col_2, col_3, col_4, col_5,
sl@0:       col_6, col_7, col_8, col_9, col_10
sl@0:     );
sl@0:     SELECT a.col_10
sl@0:     FROM
sl@0:       (SELECT table_1.col_10 AS col_10 FROM table_1) a,
sl@0:       (SELECT table_1.col_10, table_2.col_9 AS qcol_9
sl@0:          FROM table_1, table_2
sl@0:         GROUP BY table_1.col_10, qcol_9);
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # Testing boundary conditions on sqlite3_status()
sl@0: #
sl@0: do_test misc7-19.1 {
sl@0:   sqlite3_status -1 0
sl@0: } {21 0 0}
sl@0: do_test misc7-19.2 {
sl@0:   sqlite3_status 1000 0
sl@0: } {21 0 0}
sl@0: 
sl@0: 
sl@0: # sqlite3_global_recover() is a no-op.  But we might as well test it
sl@0: # if only to get the test coverage.
sl@0: #
sl@0: do_test misc7-20.1 {
sl@0:   sqlite3_global_recover
sl@0: } {SQLITE_OK}
sl@0: 
sl@0: 
sl@0: db close
sl@0: file delete -force test.db
sl@0: 
sl@0: finish_test