# 2006 September 4

#

#

# The author disclaims copyright to this source code.  In place of

# a legal notice, here is a blessing:

#

#    May you do good and not evil.

#    May you find forgiveness for yourself and forgive others.

#    May you share freely, never taking more than you give.

#

#***********************************************************************

# This file implements regression tests for SQLite library.

#

# $Id: misc7.test,v 1.24 2008/08/22 13:57:39 pweilbacher Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

do_test misc7-1-misuse {

  c_misuse_test

} {}

do_test misc7-2 {

  c_realloc_test

} {}

do_test misc7-3 {

  c_collation_test

} {}

# Try to open a directory:

# Symbian OS: '/' in the file name replaced with '\\'

do_test misc7-4 {

  file delete mydir

  file mkdir mydir

  set rc [catch {

    sqlite3 db2 .\\mydir

  } msg]

  list $rc $msg

} {1 {unable to open database file}}

# Try to open a file with a directory where its journal file should be.

# Symbian OS: '/' in the file name replaced with '\\'

do_test misc7-5 {

  file delete mydir

  file mkdir mydir-journal

  sqlite3 db2 .\\mydir

  catchsql {

    CREATE TABLE abc(a, b, c);

  } db2

} {1 {unable to open database file}}

db2 close

#--------------------------------------------------------------------

# The following tests, misc7-6.* test the libraries behaviour when

# it cannot open a file. To force this condition, we use up all the

# file-descriptors before running sqlite. This probably only works

# on unix.

#

proc use_up_files {} {

  set ret [list]

  catch {

    while 1 { lappend ret [open test.db] }

  }

  return $ret

}

proc do_fileopen_test {prefix sql} {

  set fd_list [use_up_files]

  set ::go 1

  set ::n 1

  set ::sql $sql

  while {$::go} {

    catch {db close}

    do_test ${prefix}.${::n} {

      set rc [catch {

        sqlite db test.db

        db eval $::sql

      } msg]

      if {$rc == 0} {set ::go 0}

      expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)}

    } 1

    close [lindex $fd_list 0]

    set fd_list [lrange $fd_list 1 end]

    incr ::n

  }

  foreach fd $fd_list {

    close $fd

  }

  db close

}

execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }

db close

  do_fileopen_test misc7-6.1 {

    BEGIN;

    INSERT INTO abc VALUES(1, 2, 3);

    INSERT INTO abc VALUES(2, 3, 4);

    INSERT INTO abc SELECT a+2, b, c FROM abc;

    COMMIT;

  }

  do_fileopen_test misc7-6.2 {

    PRAGMA temp.cache_size = 1000;

  }

}

#

# End of tests for out-of-file-descriptors condition.

#--------------------------------------------------------------------

sqlite3 db test.db

execsql {

  DELETE FROM abc;

  INSERT INTO abc VALUES(1, 2, 3);

  INSERT INTO abc VALUES(2, 3, 4);

  INSERT INTO abc SELECT a+2, b, c FROM abc;

}

#--------------------------------------------------------------------

# Test that the sqlite3_busy_timeout call seems to delay approximately

# the right amount of time.

#

do_test misc7-7.0 {

  sqlite3 db2 test.db

  sqlite3_busy_timeout [sqlite3_connection_pointer db] 2000

  execsql {

    BEGIN EXCLUSIVE;

  } db2

  # Now db2 has an exclusive lock on the database file, and db has

  # a busy-timeout of 2000 milliseconds. So check that trying to

  # access the database using connection db delays for at least 1500 ms.

  #

  set tm [time {

    set result [catchsql {

        SELECT * FROM sqlite_master;

      } db]

  }]

  set delay [lindex $tm 0]  ;# In microseconds

  lappend result [expr {$delay>1500000 && $delay<4000000}]

} {1 {database is locked} 1}

db2 close

#--------------------------------------------------------------------

# Test that nothing goes horribly wrong when attaching a database

# after the omit_readlock pragma has been exercised.

#

do_test misc7-7.1 {

  file delete -force test2.db

  file delete -force test2.db-journal

  execsql {

    PRAGMA omit_readlock = 1;

    ATTACH 'test2.db' AS aux;

    CREATE TABLE aux.hello(world);

    SELECT name FROM aux.sqlite_master;

  }

} {hello}

do_test misc7-7.2 {

  execsql {

    DETACH aux;

  }

} {}

# Test the UTF-16 version of the "out of memory" message (used when

# malloc fails during sqlite3_open() ).

#

ifcapable utf16 {

  do_test misc7-8 {

    encoding convertfrom unicode [sqlite3_errmsg16 0x00000000]

  } {out of memory}

}

do_test misc7-9 {

  execsql {

    SELECT * 

    FROM (SELECT name+1 AS one FROM sqlite_master LIMIT 1 OFFSET 1) 

    WHERE one LIKE 'hello%';

  }

} {}

#--------------------------------------------------------------------

# Improve coverage for vtab code.

#

ifcapable vtab {

  # Run some debug code to improve reported coverage

  #

  # set sqlite_where_trace 1

  do_test misc7-10 {

    register_echo_module [sqlite3_connection_pointer db]

    execsql {

      CREATE VIRTUAL TABLE t1 USING echo(abc);

      SELECT a FROM t1 WHERE a = 1 ORDER BY b;

    }

  } {1}

  set sqlite_where_trace 0

  # Specify an ORDER BY clause that cannot be indexed.

  do_test misc7-11 {

    execsql {

      SELECT t1.a, t2.a FROM t1, t1 AS t2 ORDER BY 2 LIMIT 1;

    }

  } {1 1}

  # The whole point of this is to test an error code other than

  # SQLITE_NOMEM from the vtab xBestIndex callback.

  #

  do_ioerr_test misc7-12 -tclprep {

    sqlite3 db2 test.db

    register_echo_module [sqlite3_connection_pointer db2]

    db2 eval {

      CREATE TABLE abc(a PRIMARY KEY, b, c);

      INSERT INTO abc VALUES(1, 2, 3);

      CREATE VIRTUAL TABLE t1 USING echo(abc);

    }

    db2 close

  } -tclbody {

    register_echo_module [sqlite3_connection_pointer db]

    execsql {SELECT * FROM t1 WHERE a = 1;}

  } 

  # The case where the virtual table module returns a very large number

  # as the cost of a scan (greater than SQLITE_BIG_DOUBLE in the code).

  #

  do_test misc7-13 {

    sqlite3 db test.db

    register_echo_module [sqlite3_connection_pointer db]

    set ::echo_module_cost 2.0e+99

    execsql {SELECT * FROM t1 WHERE a = 1;}

  } {1 2 3}

  unset ::echo_module_cost

}

db close

file delete -force test.db

file delete -force test.db-journal

sqlite3 db test.db

ifcapable explain {

  do_test misc7-14.1 {

    execsql {

      CREATE TABLE abc(a PRIMARY KEY, b, c);

    }

    execsql {

      EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1;

    }

  } {0 0 {TABLE abc AS t2 USING PRIMARY KEY}}

  do_test misc7-14.2 {

    execsql {

      EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1;

    }

  } {0 0 {TABLE abc AS t2 WITH INDEX sqlite_autoindex_abc_1}}

  do_test misc7-14.3 {

    execsql {

      EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a;

    }

  } {0 0 {TABLE abc AS t2 WITH INDEX sqlite_autoindex_abc_1 ORDER BY}}

}

db close

file delete -force test.db

file delete -force test.db-journal

sqlite3 db test.db

#--------------------------------------------------------------------

# This is all to force the pager_remove_from_stmt_list() function

# (inside pager.c) to remove a pager from the middle of the

# statement-list.

#

do_test misc7-15.1 {

  execsql {

    PRAGMA cache_size = 10;

    BEGIN;

    CREATE TABLE abc(a PRIMARY KEY, b, c);

    INSERT INTO abc 

    VALUES(randstr(100,100), randstr(100,100), randstr(100,100));

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

    COMMIT;

  }

  expr {[file size test.db]>10240}

} {1}

do_test misc7-15.2 {

  execsql {

    DELETE FROM abc WHERE rowid > 12;

    INSERT INTO abc SELECT 

            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;

  }

} {}

db close

file delete -force test.db

file delete -force test.db-journal

sqlite3 db test.db

do_ioerr_test misc7-16 -sqlprep {

   PRAGMA cache_size = 10;

   PRAGMA default_cache_size = 10;

   CREATE TABLE t3(a, b, UNIQUE(a, b));

   INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );

   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;

   UPDATE t3 

   SET b = 'hello world'

   WHERE rowid >= (SELECT max(rowid)-1 FROM t3);

} -tclbody {

  set rc [catch {db eval {

    BEGIN;

      PRAGMA cache_size = 10;

      INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );

      UPDATE t3 SET a = b;

    COMMIT;

  }} msg]

  if {!$rc || ($rc && [string first "columns" $msg]==0)} {

    set msg

  } else {

    error $msg

  }

}

sqlite3 db test.db

do_test misc7-16.X {

  execsql {

    SELECT count(*) FROM t3;

  }

} {32}

set sqlite_pager_n_sort_bucket 4

do_test misc7-17 {

  execsql {

    PRAGMA integrity_check;

    VACUUM;

    PRAGMA integrity_check;

  }

} {ok ok}

set sqlite_pager_n_sort_bucket 0

#----------------------------------------------------------------------

# Test the situation where a hot-journal is discovered but write-access

# to it is denied. This should return SQLITE_BUSY.

#

# These tests do not work on windows due to restrictions in the

# windows file system.

#

# TODO: Not clear why this test is crashing tclsqlite3.exe

#

if {$tcl_platform(platform)!="windows" && $tcl_platform(platform)!="symbian" && $tcl_platform(platform)!="os2"} {

  # Some network filesystems (ex: AFP) do not support setting read-only

  # permissions.  Only run these tests if full unix permission setting

  # capabilities are supported.

  #

  file attributes test.db -permissions rw-r--r--

  if {[file attributes test.db -permissions]==0644} {

    do_test misc7-17.1 {

      execsql {

        BEGIN;

        DELETE FROM t3 WHERE (oid%3)==0;

      }

      copy_file test.db bak.db

      copy_file test.db-journal bak.db-journal

      execsql {

        COMMIT;

      }

      db close

      copy_file bak.db test.db

      copy_file bak.db-journal test.db-journal

      sqlite3 db test.db

      catch {file attributes test.db-journal -permissions r--------}

      catch {file attributes test.db-journal -readonly 1}

      catchsql {

        SELECT count(*) FROM t3;

      }

    } {1 {database is locked}}

    do_test misc7-17.2 {

      # Note that the -readonly flag must be cleared before the -permissions

      # are set. Otherwise, when using tcl 8.5 on mac, the fact that the 

      # -readonly flag is set causes the attempt to set the permissions

      # to fail.

      catch {file attributes test.db-journal -readonly 0}

      catch {file attributes test.db-journal -permissions rw-------}

      catchsql {

        SELECT count(*) FROM t3;

      }

    } {0 32}

    set ::pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]

    do_test misc7-17.3 {

      db eval {

        pragma writable_schema = true;

        UPDATE sqlite_master 

          SET rootpage = $pending_byte_page

          WHERE type = 'table' AND name = 't3';

      }

      execsql {

        SELECT rootpage FROM sqlite_master WHERE type = 'table' AND name = 't3';

      }

    } $::pending_byte_page

    do_test misc7-17.4 {

      db close

      sqlite3 db test.db

      catchsql {

        SELECT count(*) FROM t3;

      } 

    } {1 {database disk image is malformed}}

  }

}

# Ticket #2470

#

do_test misc7-18.1 {

  execsql {

    CREATE TABLE table_1 (col_10);

    CREATE TABLE table_2 (

      col_1, col_2, col_3, col_4, col_5,

      col_6, col_7, col_8, col_9, col_10

    );

    SELECT a.col_10

    FROM

      (SELECT table_1.col_10 AS col_10 FROM table_1) a,

      (SELECT table_1.col_10, table_2.col_9 AS qcol_9

         FROM table_1, table_2

        GROUP BY table_1.col_10, qcol_9);

  }

} {}

# Testing boundary conditions on sqlite3_status()

#

do_test misc7-19.1 {

  sqlite3_status -1 0

} {21 0 0}

do_test misc7-19.2 {

  sqlite3_status 1000 0

} {21 0 0}

# sqlite3_global_recover() is a no-op.  But we might as well test it

# if only to get the test coverage.

#

do_test misc7-20.1 {

  sqlite3_global_recover

} {SQLITE_OK}

db close

file delete -force test.db

finish_test