SQLite

# 2004 November 10
#
# 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.  The
# focus of this script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.26 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {

     index i3                           <t2>          \
     index {sqlite_autoindex_<t2>_1}    <t2>          \
     index {sqlite_autoindex_<t2>_2}    <t2>          \
  ]

# Check that ALTER TABLE works on attached databases.
#
ifcapable attach {
  do_test alter-1.8.1 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      ATTACH 'test2.db' AS aux;
    }
  } {}
  do_test alter-1.8.2 {
    execsql {
      CREATE TABLE t4(a PRIMARY KEY, b, c);
      CREATE TABLE aux.t4(a PRIMARY KEY, b, c);
      CREATE INDEX i4 ON t4(b);
      CREATE INDEX aux.i4 ON t4(b);
    }
  } {}
  do_test alter-1.8.3 {
    execsql {
      INSERT INTO t4 VALUES('main', 'main', 'main');
      INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux');
      SELECT * FROM t4 WHERE a = 'main';
    }
  } {main main main}
  do_test alter-1.8.4 {
    execsql {
      ALTER TABLE t4 RENAME TO t5;
      SELECT * FROM t4 WHERE a = 'aux';
    }
  } {aux aux aux}
  do_test alter-1.8.5 {
    execsql {
      SELECT * FROM t5;
    }
  } {main main main}
  do_test alter-1.8.6 {
    execsql {
      SELECT * FROM t5 WHERE b = 'main';
    }
  } {main main main}
  do_test alter-1.8.7 {
    execsql {
      ALTER TABLE aux.t4 RENAME TO t5;
      SELECT * FROM aux.t5 WHERE b = 'aux';
    }
  } {aux aux aux}
}

do_test alter-1.9.1 {
  execsql {
    CREATE TABLE tbl1   (a, b, c);
    INSERT INTO tbl1 VALUES(1, 2, 3);
  }
} {}

  set ::TRIGGER
} {trig3 4 5 6}

# Make sure "ON" cannot be used as a database, table or column name without
# quoting. Otherwise the sqlite_alter_trigger() function might not work.
file delete -force test3.db
file delete -force test3.db-journal
ifcapable attach {
  do_test alter-3.2.1 {
    catchsql {
      ATTACH 'test3.db' AS ON;
    }
  } {1 {near "ON": syntax error}}
  do_test alter-3.2.2 {
    catchsql {
      ATTACH 'test3.db' AS 'ON';
    }
  } {0 {}}
  do_test alter-3.2.3 {
    catchsql {
      CREATE TABLE ON.t1(a, b, c); 
    }
  } {1 {near "ON": syntax error}}
  do_test alter-3.2.4 {
    catchsql {
      CREATE TABLE 'ON'.t1(a, b, c); 
    }
  } {0 {}}
  do_test alter-3.2.4 {
    catchsql {
      CREATE TABLE 'ON'.ON(a, b, c); 
    }
  } {1 {near "ON": syntax error}}
  do_test alter-3.2.5 {
    catchsql {
      CREATE TABLE 'ON'.'ON'(a, b, c); 
    }
  } {0 {}}
}
do_test alter-3.2.6 {
  catchsql {
    CREATE TABLE t10(a, ON, c);
  }
} {1 {near "ON": syntax error}}
do_test alter-3.2.7 {
  catchsql {
    CREATE TABLE t10(a, 'ON', c);
  }
} {0 {}}
do_test alter-3.2.8 {
  catchsql {
    CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END;
  }
} {1 {near "ON": syntax error}}
ifcapable attach {
  do_test alter-3.2.9 {
    catchsql {
      CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
    }
  } {0 {}}
}
do_test alter-3.2.10 {
  execsql {
    DROP TABLE t10;
  }
} {}

do_test alter-3.3.1 {

#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing that SQLite can handle a subtle 
# file format change that may be used in the future to implement
# "ALTER TABLE ... ADD COLUMN".
#
# $Id: alter2.test,v 1.10 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# We have to have pragmas in order to do this test
ifcapable {!pragma} return

do_test alter2-6.1 {
  db close
  set_file_format 2
  sqlite3 db test.db
  set ::DB [sqlite3_connection_pointer db]
  get_file_format
} {2}
ifcapable attach {
  do_test alter2-6.2 {
    file delete -force test2.db-journal
    file delete -force test2.db
    execsql {
      ATTACH 'test2.db' AS aux;
      CREATE TABLE aux.t1(a, b);
    }
    get_file_format test2.db
  } $default_file_format
}
do_test alter2-6.3 {
  execsql {
    CREATE TABLE t1(a, b);
  }
  get_file_format 
} {2}

#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing that SQLite can handle a subtle 
# file format change that may be used in the future to implement
# "ALTER TABLE ... ADD COLUMN".
#
# $Id: alter3.test,v 1.10 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.

}
do_test alter3-4.99 {
  execsql {
    DROP TABLE t1;
  }
} {}

ifcapable attach {
  do_test alter3-5.1 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 'one');
      INSERT INTO t1 VALUES(2, 'two');
      ATTACH 'test2.db' AS aux;
      CREATE TABLE aux.t1 AS SELECT * FROM t1;
      PRAGMA aux.schema_version = 30;
      SELECT sql FROM aux.sqlite_master;
    } 
  } {{CREATE TABLE t1(a,b)}}
  do_test alter3-5.2 {
    execsql {
      ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
      SELECT sql FROM aux.sqlite_master;
    }
  } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
  do_test alter3-5.3 {
    execsql {
      SELECT * FROM aux.t1;
    }
  } {1 one {} 2 two {}}
  ifcapable schema_version {
    do_test alter3-5.4 {
      execsql {
        PRAGMA aux.schema_version;
      }
    } {31}
  }
  if {!$has_codec} {
    do_test alter3-5.5 {
      list [get_file_format test2.db] [get_file_format]
    } {2 3}
  }
  do_test alter3-5.6 {
    execsql {
      ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
      SELECT sql FROM aux.sqlite_master;
    }
  } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
  do_test alter3-5.7 {
    execsql {
      SELECT * FROM aux.t1;
    }
  } {1 one {} 1000 2 two {} 1000}
  ifcapable schema_version {
    do_test alter3-5.8 {
      execsql {
        PRAGMA aux.schema_version;
      }
    } {32}
  }
  do_test alter3-5.9 {
    execsql {
      SELECT * FROM t1;
    }
  } {1 one 2 two}
  do_test alter3-5.99 {
    execsql {
      DROP TABLE aux.t1;
      DROP TABLE t1;
    }
  } {}
}

#----------------------------------------------------------------
# Test that the table schema is correctly reloaded when a column
# is added to a table.
#
ifcapable trigger&&tempdb {
  do_test alter3-6.1 {

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.47 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !attach {
  finish_test
  return
}

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db
  file delete -force test$i.db-journal
}

set btree_trace 0

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach2.test,v 1.37 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !attach {
  finish_test
  return
}

# Ticket #354
#
# Databases test.db and test2.db contain identical schemas.  Make
# sure we can attach test2.db from test.db.
#
do_test attach2-1.1 {

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and schema changes to attached databases.
#
# $Id: attach3.test,v 1.18 2007/10/09 08:29:32 danielk1977 Exp $
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !attach {
  finish_test
  return
}

# Create tables t1 and t2 in the main database
execsql {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(c, d);
}

#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH statement and
# specifically out-of-memory conditions within that command.
#
# $Id: attachmalloc.test,v 1.7 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !memdebug||!attach {
  finish_test
  return
}

source $testdir/malloc_common.tcl

do_malloc_test attachmalloc-1 -tclprep {

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the sqlite3_set_authorizer() API
# and related functionality.
#
# $Id: auth.test,v 1.38 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.

    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t2}
} {1 {access to t2.b is prohibited}}
ifcapable attach {
  do_test auth-1.35.2 {
    execsql {ATTACH DATABASE 'test.db' AS two}
    catchsql {SELECT * FROM two.t2}
  } {1 {access to two.t2.b is prohibited}}
  execsql {DETACH DATABASE two}
}
do_test auth-1.36 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }

} {0 {}}
do_test auth-1.250 {
  execsql {SELECT * FROM t2}
} {11 2 33 7 8 9}

# ticket #340 - authorization for ATTACH and DETACH.
#
ifcapable attach {
  do_test auth-1.251 {
    db authorizer ::auth
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_ATTACH"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      }
      return SQLITE_OK
    }
    catchsql {
      ATTACH DATABASE ':memory:' AS test1
    }
  } {0 {}}
  do_test auth-1.252 {
    set ::authargs
  } {:memory: {} {} {}}
  do_test auth-1.253 {
    catchsql {DETACH DATABASE test1}
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_ATTACH"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {
      ATTACH DATABASE ':memory:' AS test1;
    }
  } {1 {not authorized}}
  do_test auth-1.254 {
    lindex [execsql {PRAGMA database_list}] 7
  } {}
  do_test auth-1.255 {
    catchsql {DETACH DATABASE test1}
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_ATTACH"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {
      ATTACH DATABASE ':memory:' AS test1;
    }
  } {0 {}}
  do_test auth-1.256 {
    lindex [execsql {PRAGMA database_list}] 7
  } {}
  do_test auth-1.257 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DETACH"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_OK
      }
      return SQLITE_OK
    }
    execsql {ATTACH DATABASE ':memory:' AS test1}
    catchsql {
      DETACH DATABASE test1;
    }
  } {0 {}}
  do_test auth-1.258 {
    lindex [execsql {PRAGMA database_list}] 7
  } {}
  do_test auth-1.259 {
    execsql {ATTACH DATABASE ':memory:' AS test1}
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DETACH"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_IGNORE


















      }
      return SQLITE_OK
    }
    catchsql {
      DETACH DATABASE test1;
    }
  } {0 {}}
  ifcapable tempdb {
    ifcapable schema_pragmas {
    do_test auth-1.260 {
      lindex [execsql {PRAGMA database_list}] 7
    } {test1}
    } ;# ifcapable schema_pragmas








    do_test auth-1.261 {
      proc auth {code arg1 arg2 arg3 arg4} {
        if {$code=="SQLITE_DETACH"} {
          set ::authargs [list $arg1 $arg2 $arg3 $arg4]




































          return SQLITE_DENY
        }
        return SQLITE_OK
      }
      catchsql {
        DETACH DATABASE test1;
      }
    } {1 {not authorized}}
    ifcapable schema_pragmas {
    do_test auth-1.262 {
      lindex [execsql {PRAGMA database_list}] 7
    } {test1}
    } ;# ifcapable schema_pragmas
    db authorizer {}
    execsql {DETACH DATABASE test1}
    db authorizer ::auth
    
    # Authorization for ALTER TABLE. These tests are omitted if the library
    # was built without ALTER TABLE support.
    ifcapable altertable {
    
      do_test auth-1.263 {
        proc auth {code arg1 arg2 arg3 arg4} {
          if {$code=="SQLITE_ALTER_TABLE"} {
            set ::authargs [list $arg1 $arg2 $arg3 $arg4]
            return SQLITE_OK
          }
          return SQLITE_OK
        }
        catchsql {
          ALTER TABLE t1 RENAME TO t1x
        }
      } {0 {}}
      do_test auth-1.264 {
        execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
      } {t1x}
      do_test auth-1.265 {
        set authargs
      } {temp t1 {} {}}
      do_test auth-1.266 {
        proc auth {code arg1 arg2 arg3 arg4} {
          if {$code=="SQLITE_ALTER_TABLE"} {
            set ::authargs [list $arg1 $arg2 $arg3 $arg4]
            return SQLITE_IGNORE
          }
          return SQLITE_OK
        }
        catchsql {
          ALTER TABLE t1x RENAME TO t1
        }
      } {0 {}}
      do_test auth-1.267 {
        execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
      } {t1x}
      do_test auth-1.268 {
        set authargs
      } {temp t1x {} {}}
      do_test auth-1.269 {
        proc auth {code arg1 arg2 arg3 arg4} {
          if {$code=="SQLITE_ALTER_TABLE"} {
            set ::authargs [list $arg1 $arg2 $arg3 $arg4]
            return SQLITE_DENY
          }
          return SQLITE_OK
        }
        catchsql {
          ALTER TABLE t1x RENAME TO t1
        }
      } {1 {not authorized}}
      do_test auth-1.270 {
        execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
      } {t1x}
  
      do_test auth-1.271 {
        set authargs
      } {temp t1x {} {}}
    } ;# ifcapable altertable
  
  } else {
    db authorizer {}
    db eval {
      DETACH DATABASE test1;
    }
  }
}

ifcapable  altertable {
db authorizer {}
catchsql {ALTER TABLE t1x RENAME TO t1}
db authorizer ::auth

# 2004 November 12
#
# 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.  The
# focus of this script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.10 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If the library is not compiled with autoincrement support then
# skip all tests in this file.

      SELECT 2, * FROM temp.sqlite_sequence;
    }
  } {}
}

# Make sure AUTOINCREMENT works on ATTACH-ed tables.
#
ifcapable tempdb&&attach {
  do_test autoinc-5.1 {
    file delete -force test2.db
    file delete -force test2.db-journal
    sqlite3 db2 test2.db
    execsql {
      CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n);
      CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT);

# The focus of this file is testing the ability of the database to
# uses its rollback journal to recover intact (no database corruption)
# from a power failure during the middle of a COMMIT.  The OS interface
# modules are overloaded using the modified I/O routines found in test6.c.  
# These routines allow us to simulate the kind of file damage that 
# occurs after a power failure.
#
# $Id: crash.test,v 1.26 2007/10/09 08:29:32 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !crashtest {
  finish_test
  return

# crash-4.1.*: Test recovery when crash occurs during sync() of the 
#              main database journal file.
# crash-4.2.*: Test recovery when crash occurs during sync() of an 
#              attached database journal file.
# crash-4.3.*: Test recovery when crash occurs during sync() of the master
#              journal file. 
#
ifcapable attach {
  do_test crash-4.0 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      ATTACH 'test2.db' AS aux;
      PRAGMA aux.default_cache_size = 10;
      CREATE TABLE aux.abc2 AS SELECT 2*a as a, 2*b as b, 2*c as c FROM abc;
    }
    expr ([file size test2.db] / 1024) > 450
  } {1}
  
  set fin 0
  for {set i 1} {$i<$repeats} {incr i} {
    set sig [signature]
    set sig2 [signature2]
    do_test crash-4.1.$i.1 {
       set c [crashsql -delay $i -file test.db-journal "
         ATTACH 'test2.db' AS aux;
         BEGIN;
         SELECT randstr($i,$i) FROM abc LIMIT $i;
         INSERT INTO abc VALUES(randstr(10,10), 0, 0);
         DELETE FROM abc WHERE random()%10!=0;
         INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
         DELETE FROM abc2 WHERE random()%10!=0;
         COMMIT;
       "]
       if { $c == {0 {}} } {
         set ::fin 1
         set c {1 {child process exited abnormally}}
       }
       set c
    } {1 {child process exited abnormally}}
    if {$::fin} break
    do_test crash-4.1.$i.2 {
      signature
    } $sig
    do_test crash-4.1.$i.3 {
      signature2
    } $sig2
  } 
  set i 0
  set fin 0
  while {[incr i]} {
    set sig [signature]
    set sig2 [signature2]
    set ::fin 0
    do_test crash-4.2.$i.1 {
       set c [crashsql -delay $i -file test2.db-journal "
         ATTACH 'test2.db' AS aux;
         BEGIN;
         SELECT randstr($i,$i) FROM abc LIMIT $i;
         INSERT INTO abc VALUES(randstr(10,10), 0, 0);
         DELETE FROM abc WHERE random()%10!=0;
         INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
         DELETE FROM abc2 WHERE random()%10!=0;
         COMMIT;
       "]
       if { $c == {0 {}} } {
         set ::fin 1
         set c {1 {child process exited abnormally}}
       }
       set c
    } {1 {child process exited abnormally}}
    if { $::fin } break
    do_test crash-4.2.$i.2 {
      signature
    } $sig
    do_test crash-4.2.$i.3 {
      signature2
    } $sig2
  } 
  for {set i 1} {$i < 5} {incr i} {
    set sig [signature]
    set sig2 [signature2]
    do_test crash-4.3.$i.1 {
       crashsql -delay 1 -file test.db-mj* "
         ATTACH 'test2.db' AS aux;
         BEGIN;
         SELECT random() FROM abc LIMIT $i;
         INSERT INTO abc VALUES(randstr(10,10), 0, 0);
         DELETE FROM abc WHERE random()%10!=0;
         INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
         DELETE FROM abc2 WHERE random()%10!=0;
         COMMIT;
       "
    } {1 {child process exited abnormally}}
    do_test crash-4.3.$i.2 {
      signature
    } $sig
    do_test crash-4.3.$i.3 {
      signature2
    } $sig2
  }
}

#--------------------------------------------------------------------------
# The following test cases - crash-5.* - exposes a bug that existed in the
# sqlite3pager_movepage() API used by auto-vacuum databases.
# database when a crash occurs during a multi-file transaction. See comments
# in test crash-5.3 for details.

#
#***********************************************************************
# This file implements regression tests for SQLite library.  The focus of
# this file is testing the SQLite routines used for converting between the
# various suported unicode encodings (UTF-8, UTF-16, UTF-16le and
# UTF-16be).
#
# $Id: enc2.test,v 1.29 2007/10/09 08:29:32 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If UTF16 support is disabled, ignore the tests in this file
#
ifcapable {!utf16} {

  run_test_script enc2-$i $enc
  db close
  incr i
}

# Test that it is an error to try to attach a database with a different
# encoding to the main database.
ifcapable attach {
  do_test enc2-4.1 {
    file delete -force test.db
    sqlite3 db test.db
    db eval "PRAGMA encoding = 'UTF-8'"
    db eval "CREATE TABLE abc(a, b, c);"
  } {}
  do_test enc2-4.2 {
    file delete -force test2.db
    sqlite3 db2 test2.db
    db2 eval "PRAGMA encoding = 'UTF-16'"
    db2 eval "CREATE TABLE abc(a, b, c);"
  } {}
  do_test enc2-4.3 {
    catchsql {
      ATTACH 'test2.db' as aux;
    }
  } {1 {attached databases must use the same text encoding as main database}}

  db2 close
  db close
}

# The following tests - enc2-5.* - test that SQLite selects the correct
# collation sequence when more than one is available.

set ::values [list one two three four five]
set ::test_collate_enc INVALID
proc test_collate {enc lhs rhs} {

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# of these tests is exclusive access mode (i.e. the thing activated by 
# "PRAGMA locking_mode = EXCLUSIVE").
#
# $Id: exclusive.test,v 1.7 2007/10/09 08:29:32 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!pager_pragmas} {
  finish_test
  return

do_test exclusive-1.6 {
  execsql {
    pragma locking_mode;
    pragma main.locking_mode;
    pragma temp.locking_mode;
  } 
} [list normal normal $temp_mode]
ifcapable attach {
  do_test exclusive-1.7 {
    execsql {
      pragma locking_mode = exclusive;
      ATTACH 'test2.db' as aux;
    }
    execsql {
      pragma main.locking_mode;
      pragma aux.locking_mode;
    }
  } {exclusive exclusive}
  do_test exclusive-1.8 {
    execsql {
      pragma main.locking_mode = normal;
    }
    execsql {
      pragma main.locking_mode;
      pragma temp.locking_mode;
      pragma aux.locking_mode;
    }
  } [list normal $temp_mode exclusive]
  do_test exclusive-1.9 {
    execsql {
      pragma locking_mode;
    }
  } {exclusive}
  do_test exclusive-1.10 {
    execsql {
      ATTACH 'test3.db' as aux2;
    }
    execsql {
      pragma main.locking_mode;
      pragma aux.locking_mode;
      pragma aux2.locking_mode;
    }
  } {normal exclusive exclusive}
  do_test exclusive-1.11 {
    execsql {
      pragma aux.locking_mode = normal;
    }
    execsql {
      pragma main.locking_mode;
      pragma aux.locking_mode;
      pragma aux2.locking_mode;
    }
  } {normal normal exclusive}
  do_test exclusive-1.12 {
    execsql {
      pragma locking_mode = normal;
    }
    execsql {
      pragma main.locking_mode;
      pragma temp.locking_mode;
      pragma aux.locking_mode;
      pragma aux2.locking_mode;
    }
  } [list normal $temp_mode normal normal]
  do_test exclusive-1.13 {
    execsql {
      ATTACH 'test4.db' as aux3;
    }
    execsql {
      pragma main.locking_mode;
      pragma temp.locking_mode;
      pragma aux.locking_mode;
      pragma aux2.locking_mode;
      pragma aux3.locking_mode;
    }
  } [list normal $temp_mode normal normal normal]
  
  do_test exclusive-1.99 {
    execsql {
      DETACH aux;
      DETACH aux2;
      DETACH aux3;
    }
  } {}
}

#----------------------------------------------------------------------
# Test cases exclusive-2.X verify that connections in exclusive 
# locking_mode do not relinquish locks.
#
do_test exclusive-2.0 {
  execsql {

#
# The focus of the tests in this file is the  following interface:
#
#      sqlite_commit_hook    (tests hook-1..hook-3 inclusive)
#      sqlite_update_hook    (tests hook-4-*)
#      sqlite_rollback_hook  (tests hook-5.*)
#
# $Id: hook.test,v 1.12 2007/10/09 08:29:32 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test hook-1.2 {
  db commit_hook
} {}

    INSERT INTO t1 VALUES(1, 'one');
    INSERT INTO t1 VALUES(2, 'two');
  }
}

# Update-hook + ATTACH
set ::update_hook {}
ifcapable attach {
  do_test hook-4.2.3 {
    file delete -force test2.db
    execsql {
      ATTACH 'test2.db' AS aux;
      CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
      INSERT INTO aux.t3 SELECT * FROM t1;
      UPDATE t3 SET b = 'two or so' WHERE a = 2;
      DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)
    }
    set ::update_hook
  } [list \
      INSERT aux t3 1 \
      INSERT aux t3 2 \
      UPDATE aux t3 2 \
      DELETE aux t3 1 \
      DELETE aux t3 2 \
  ]
}

ifcapable trigger {
  execsql {
    DROP TRIGGER t1_trigger;
  }
}

    UPDATE main t1 1 \
    UPDATE main t1 2 \
    UPDATE main t1 3 \
    DELETE main t1 2 \
    DELETE main t1 3 \
]
set ::update_hook {}
ifcapable compound&&attach {
  do_test hook-4.3.2 {
    execsql {
      SELECT * FROM t1 UNION SELECT * FROM t3;
      SELECT * FROM t1 UNION ALL SELECT * FROM t3;
      SELECT * FROM t1 INTERSECT SELECT * FROM t3;
      SELECT * FROM t1 EXCEPT SELECT * FROM t3;
      SELECT * FROM t1 ORDER BY b;

# 2007 May 1
#
# 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.
#
#***********************************************************************
#
# $Id: incrblob.test,v 1.17 2007/10/09 08:29:32 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!autovacuum || !pragma || !incrblob} {
  finish_test

} {}

#------------------------------------------------------------------------
# incrblob-5.*: 
#
#     Test that opening a blob in an attached database works.
#
ifcapable attach {
  do_test incrblob-5.1 {
    file delete -force test2.db test2.db-journal
    set ::size [expr [file size [info script]]]
    execsql {
      ATTACH 'test2.db' AS aux;
      CREATE TABLE aux.files(name, text);
      INSERT INTO aux.files VALUES('this one', zeroblob($::size));
    }
    set fd  [db incrblob aux files text 1]
    fconfigure $fd -translation binary
    set fd2 [open [info script]]
    fconfigure $fd2 -translation binary
    puts -nonewline $fd [read $fd2]
    close $fd
    close $fd2
    set ::text [db one {select text from aux.files}]
    string length $::text
  } [file size [info script]]
  do_test incrblob-5.2 {
    set fd2 [open [info script]]
    fconfigure $fd2 -translation binary
    set ::data [read $fd2]
    close $fd2
    set ::data
  } $::text
}

# free memory
unset -nocomplain ::data
unset -nocomplain ::text

#------------------------------------------------------------------------
# incrblob-6.*: 
#
#     Test that opening a blob for write-access is impossible if
#     another connection has the database RESERVED lock.
#

# 2007 May 04
#
# 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.  The
# focus of this file is testing the incremental vacuum feature.
#
# $Id: incrvacuum2.test,v 1.4 2007/10/09 08:29:32 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum || !pragma} {

    PRAGMA incremental_vacuum(1000);
  }
  file size test.db
} {3072}

# Make sure incremental vacuum works on attached databases.
#
ifcapable attach {
  do_test incrvacuum2-2.1 {
    file delete -force test2.db test2.db-journal
    execsql {
      ATTACH DATABASE 'test2.db' AS aux;
      PRAGMA aux.auto_vacuum=incremental;
      CREATE TABLE aux.t2(x);
      INSERT INTO t2 VALUES(zeroblob(30000));
      INSERT INTO t1 SELECT * FROM t2;
      DELETE FROM t2;
      DELETE FROM t1;
    }
    list [file size test.db] [file size test2.db]
  } {32768 32768}
  do_test incrvacuum2-2.2 {
    execsql {
      PRAGMA aux.incremental_vacuum(1)
    }
    list [file size test.db] [file size test2.db]
  } {32768 31744}
  do_test incrvacuum2-2.3 {
    execsql {
      PRAGMA aux.incremental_vacuum(5)
    }
    list [file size test.db] [file size test2.db]
  } {32768 26624}
  do_test incrvacuum2-2.4 {
    execsql {
      PRAGMA main.incremental_vacuum(5)
    }
    list [file size test.db] [file size test2.db]
  } {27648 26624}
  do_test incrvacuum2-2.5 {
    execsql {
      PRAGMA aux.incremental_vacuum
    }
    list [file size test.db] [file size test2.db]
  } {27648 3072}
  do_test incrvacuum2-2.6 {
    execsql {
      PRAGMA incremental_vacuum(1)
    }
    list [file size test.db] [file size test2.db]
  } {26624 3072}
}

 

finish_test

# 2007 January 24
#
# 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.  The
# focus of this file is testing the INSERT transfer optimization.
#
# $Id: insert4.test,v 1.8 2007/10/09 08:29:32 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !view||!subquery {
  finish_test
  return

#
xfer_check insert4-3.22 1 {1 9} \
    {a int, b int} \
    {x integer, b int}

# Ticket #2291.
#

do_test insert4-4.1a {
  execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))}
} {}
ifcapable vacuum {
  do_test insert4-4.1b {
    execsql {

      INSERT INTO t4 VALUES(NULL,0);
      INSERT INTO t4 VALUES(NULL,1);
      INSERT INTO t4 VALUES(NULL,1);
      VACUUM;   
    }
  } {}
}

# Check some error conditions:
#
do_test insert4-5.1 {
  # Table does not exist.
  catchsql { INSERT INTO t2 SELECT * FROM nosuchtable }
} {1 {no such table: nosuchtable}}

#
#***********************************************************************
#
# The focus of this file is testing some specific characteristics of the 
# IO traffic generated by SQLite (making sure SQLite is not writing out
# more database pages than it has to, stuff like that).
#
# $Id: io.test,v 1.12 2007/10/09 08:29:32 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Test summary:
#
# io-1.* -  Test that quick-balance does not journal pages unnecessarily.

# Test that if the database modification is part of multi-file commit,
# the journal file is always created. In this case, the journal file
# is created during execution of the COMMIT statement, so we have to
# use the same technique to check that it is created as in the above 
# block.
file delete -force test2.db test2.db-journal
ifcapable attach {
  do_test io-2.7.1 {
    execsql {
      ATTACH 'test2.db' AS aux;
      PRAGMA aux.page_size = 1024;
      CREATE TABLE aux.abc2(a, b);
      BEGIN;
      INSERT INTO abc VALUES(9, 10);
    }
    file exists test.db-journal
  } {0}
  do_test io-2.7.2 {
    execsql { INSERT INTO abc2 SELECT * FROM abc }
    file exists test2.db-journal
  } {0}
  do_test io-2.7.3 {
    execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
  } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10}
  do_test io-2.7.4 {
    set fd [open test2.db-journal w]
    puts $fd "This is not a journal file"
    close $fd
    catchsql { COMMIT }
  } {1 {unable to open database file}}
  do_test io-2.7.5 {
    file delete -force test2.db-journal
    catchsql { COMMIT }
  } {1 {cannot commit - no transaction is active}}
  do_test io-2.7.6 {
    execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
  } {1 2 3 4 5 6 7 8}
}

# Try an explicit ROLLBACK before the journal file is created.
#
do_test io-2.8.1 {
  execsql {
    BEGIN;
    DELETE FROM abc;

# This file implements regression tests for SQLite library.  The
# focus of this file is testing for correct handling of I/O errors
# such as writes failing because the disk is full.
# 
# The tests in this file use special facilities that are only
# available in the SQLite test fixture.
#
# $Id: ioerr.test,v 1.33 2007/10/09 08:29:32 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl


# If SQLITE_DEFAULT_AUTOVACUUM is set to true, then a simulated IO error
# on the 8th IO operation in the SQL script below doesn't report an error.

 SELECT * FROM abc;
}

# Test IO errors that may occur during a multi-file commit.
#
# Tests 8 and 17 are excluded when auto-vacuum is enabled for the same 
# reason as in test cases ioerr-1.XXX
ifcapable attach {
  set ex ""
  if {[string match [execsql {pragma auto_vacuum}] 1]} {
    set ex [list 4 17]
  }
  do_ioerr_test ioerr-5 -sqlprep {
    ATTACH 'test2.db' AS test2;
  } -sqlbody {
    BEGIN;
    CREATE TABLE t1(a,b,c);
    CREATE TABLE test2.t2(a,b,c);
    COMMIT;
  } -exclude $ex
}

# Test IO errors when replaying two hot journals from a 2-file 
# transaction. This test only runs on UNIX.
ifcapable crashtest&&attach {
  if {![catch {sqlite3 -has_codec} r] && !$r} {
    do_ioerr_test ioerr-6 -tclprep {
      execsql {
        ATTACH 'test2.db' as aux;
        CREATE TABLE tx(a, b);
        CREATE TABLE aux.ty(a, b);
      }

# This file attempts to check the behavior of the SQLite library in 
# an out-of-memory situation. When compiled with -DSQLITE_DEBUG=1, 
# the SQLite library accepts a special command (sqlite3_memdebug_fail N C)
# which causes the N-th malloc to fail.  This special feature is used
# to see what happens in the library if a malloc were to really fail
# due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.51 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Only run these tests if memory debugging is turned on.
#
ifcapable !memdebug {

  append sql16 "\00\00"
  set ::STMT [sqlite3_prepare16 db $sql16 -1 DUMMY]
  sqlite3_finalize $::STMT
}

# Test malloc errors when replaying two hot journals from a 2-file 
# transaction.
ifcapable crashtest&&attach {
  do_malloc_test 13 -tclprep {
    set rc [crashsql -delay 1 -file test2.db {
      ATTACH 'test2.db' as aux;
      PRAGMA cache_size = 10;
      BEGIN;
      CREATE TABLE aux.t2(a, b, c);
      CREATE TABLE t1(a, b, c);

  }
}
unset static_string

# Make sure SQLITE_NOMEM is reported out on an ATTACH failure even
# when the malloc failure occurs within the nested parse.
#
ifcapable attach {
  do_malloc_test 20 -tclprep {
    db close
    file delete -force test2.db test2.db-journal
    sqlite3 db test2.db
    sqlite3_extended_result_codes db 1
    db eval {CREATE TABLE t1(x);}
    db close
  } -tclbody {
    if {[catch {sqlite3 db test.db}]} {
      error "out of memory"
    }
    sqlite3_extended_result_codes db 1
  } -sqlbody {
    ATTACH DATABASE 'test2.db' AS t2;
    SELECT * FROM t1;
    DETACH DATABASE t2;
  } 
}

# Test malloc failure whilst installing a foreign key.
#
ifcapable foreignkey {
  do_malloc_test 21 -sqlbody {
    CREATE TABLE abc(a, b, c, FOREIGN KEY(a) REFERENCES abc(b))
  } 

#
#***********************************************************************
#
# This file contains tests to ensure that the library handles malloc() failures
# correctly. The emphasis of these tests are the _prepare(), _step() and
# _finalize() calls.
#
# $Id: malloc3.test,v 1.17 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Only run these tests if memory debugging is turned on.
#
ifcapable !memdebug {

    }
  } {a b c a b c 1 2 3 1 2 3}
}

# Test a simple multi-file transaction 
#
file delete -force test2.db
ifcapable attach {
  SQL {ATTACH 'test2.db' AS aux;}
  SQL {BEGIN}
  SQL {CREATE TABLE aux.tbl2(x, y, z)}
  SQL {INSERT INTO tbl2 VALUES(1, 2, 3)}
  SQL {INSERT INTO def VALUES(4, 5, 6)}
  TEST 30 {
    do_test $testid {
      execsql {
        SELECT * FROM tbl2, def WHERE d = x;
      }
    } {1 2 3 1 2 3}
  }
  SQL {COMMIT}
  TEST 31 {
    do_test $testid {
      execsql {
        SELECT * FROM tbl2, def WHERE d = x;
      }
    } {1 2 3 1 2 3}
  }
}

# Test what happens when a malloc() fails while there are other active
# statements. This changes the way sqlite3VdbeHalt() works.
TEST 32 {
  if {![info exists ::STMT32]} {
    set sql "SELECT name FROM sqlite_master"

# 2007 Aug 29
#
# 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.
#
#***********************************************************************
#
# $Id: mallocD.test,v 1.4 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Only run these tests if memory debugging is turned on.
#
ifcapable !memdebug {

do_malloc_test mallocD-3 -sqlprep $PREP -sqlbody {
  BEGIN;
  INSERT INTO abc VALUES(1, 2, 3);
  INSERT INTO abc VALUES(4, 5, randstr(1500,1500));
  COMMIT;
}

ifcapable attach {
  do_malloc_test mallocD-4 -sqlprep $PREP -sqlbody {
    ATTACH 'test2.db' AS aux;
    BEGIN;
    CREATE TABLE aux.def(d, e, f);
    INSERT INTO abc VALUES(4, 5, 6);
    COMMIT;
  }
}

sqlite3_simulate_device -char {}

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.55 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Test organization:
#
# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.

do_test pragma-1.18 {
  execsql {
    PRAGMA bogus = -1234;  -- Parsing of negative values
  }
} {}

# Test modifying the safety_level of an attached database.
ifcapable pager_pragmas&&attach {
  do_test pragma-2.1 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      ATTACH 'test2.db' AS aux;
    } 
  } {}

  do_test pragma-2.2 {
    execsql {
      pragma aux.synchronous;
    } 
  } {2}
  do_test pragma-2.3 {
    execsql {
      pragma aux.synchronous = OFF;
      pragma aux.synchronous;
      pragma synchronous;
    } 
  } {0 2}
  do_test pragma-2.4 {
    execsql {
      pragma aux.synchronous = ON;
      pragma synchronous;
      pragma aux.synchronous;
    } 
  } {2 1}
} ;# ifcapable pager_pragmas

# Construct a corrupted index and make sure the integrity_check
# pragma finds it.
#
# These tests won't work if the database is encrypted
#

    CREATE INDEX i2 ON t2(a);
    INSERT INTO t2 VALUES(11,2,3);
    INSERT INTO t2 VALUES(22,3,4);
    COMMIT;
    SELECT rowid, * from t2;
  }
} {1 11 2 3 2 22 3 4}
ifcapable attach {
  if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} {
    do_test pragma-3.2 {
      set rootpage [execsql {SELECT rootpage FROM sqlite_master WHERE name='i2'}]
      set db [btree_open test.db 100 0]
      btree_begin_transaction $db
      set c [btree_cursor $db $rootpage 1]
      btree_first $c
      btree_delete $c
      btree_commit $db
      btree_close $db
      execsql {PRAGMA integrity_check}
    } {{rowid 1 missing from index i2} {wrong # of entries in index i2}}
    do_test pragma-3.3 {
      execsql {PRAGMA integrity_check=1}
    } {{rowid 1 missing from index i2}}
    do_test pragma-3.4 {
      execsql {
        ATTACH DATABASE 'test.db' AS t2;
        PRAGMA integrity_check
      }
    } {{rowid 1 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {wrong # of entries in index i2}}
    do_test pragma-3.5 {
      execsql {
        PRAGMA integrity_check=3
      }
    } {{rowid 1 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2}}
    do_test pragma-3.6 {
      execsql {
        PRAGMA integrity_check=xyz
      }
    } {{rowid 1 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {wrong # of entries in index i2}}
    do_test pragma-3.7 {
      execsql {
        PRAGMA integrity_check=0
      }
    } {{rowid 1 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {wrong # of entries in index i2}}
  
    # Add additional corruption by appending unused pages to the end of
    # the database file testerr.db
    #
    do_test pragma-3.8 {
      execsql {DETACH t2}
      file delete -force testerr.db testerr.db-journal
      set out [open testerr.db w]
      fconfigure $out -translation binary
      set in [open test.db r]
      fconfigure $in -translation binary
      puts -nonewline $out [read $in]
      seek $in 0
      puts -nonewline $out [read $in]
      close $in
      close $out
      execsql {REINDEX t2}
      execsql {PRAGMA integrity_check}
    } {ok}
    do_test pragma-3.9 {
      execsql {
        ATTACH 'testerr.db' AS t2;
        PRAGMA integrity_check
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2} {wrong # of entries in index i2}}
    do_test pragma-3.10 {
      execsql {
        PRAGMA integrity_check=1
      }
    } {{*** in database t2 ***
Page 4 is never used}}
    do_test pragma-3.11 {
      execsql {
        PRAGMA integrity_check=5
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2} {wrong # of entries in index i2}}
    do_test pragma-3.12 {
      execsql {
        PRAGMA integrity_check=4
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2}}
    do_test pragma-3.13 {
      execsql {
        PRAGMA integrity_check=3
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used}}
    do_test pragma-3.14 {
      execsql {
        PRAGMA integrity_check(2)
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used}}
    do_test pragma-3.15 {
      execsql {
        ATTACH 'testerr.db' AS t3;
        PRAGMA integrity_check
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2} {wrong # of entries in index i2}}
    do_test pragma-3.16 {
      execsql {
        PRAGMA integrity_check(9)
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2}}
    do_test pragma-3.17 {
      execsql {
        PRAGMA integrity_check=7
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2} {wrong # of entries in index i2} {*** in database t3 ***
Page 4 is never used
Page 5 is never used}}
    do_test pragma-3.18 {
      execsql {
        PRAGMA integrity_check=4
      }
    } {{*** in database t2 ***
Page 4 is never used
Page 5 is never used
Page 6 is never used} {rowid 1 missing from index i2}}
  }
  do_test pragma-3.99 {
    catchsql {DETACH t3}
    catchsql {DETACH t2}
    file delete -force testerr.db testerr.db-journal
    catchsql {DROP INDEX i2}
  } {0 {}}
}

# Test modifying the cache_size of an attached database.
ifcapable pager_pragmas&&attach {
do_test pragma-4.1 {
  execsql {
    ATTACH 'test2.db' AS aux;
    pragma aux.cache_size;
    pragma aux.default_cache_size;
  } 
} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]

} {2}
catchsql {COMMIT;}
} ;# ifcapable pager_pragmas

# Test schema-query pragmas
#
ifcapable schema_pragmas {
ifcapable tempdb&&attach {
  do_test pragma-6.1 {
    set res {}
    execsql {SELECT * FROM sqlite_temp_master}
    foreach {idx name file} [execsql {pragma database_list}] {
      lappend res $idx $name
    }
    set res

do_test pragma-8.1.10 {
  sqlite3_finalize $::STMT
} SQLITE_SCHEMA

# Make sure the schema-version can be manipulated in an attached database.
file delete -force test2.db
file delete -force test2.db-journal
ifcapable attach {
  do_test pragma-8.1.11 {
    execsql {
      ATTACH 'test2.db' AS aux;
      CREATE TABLE aux.t1(a, b, c);
      PRAGMA aux.schema_version = 205;
    }
  } {}
  do_test pragma-8.1.12 {
    execsql {
      PRAGMA aux.schema_version;
    }
  } 205
}
do_test pragma-8.1.13 {
  execsql {
    PRAGMA schema_version;
  }
} 108

# And check that modifying the schema-version in an attached database
# forces the second connection to reload the schema.
ifcapable attach {
  do_test pragma-8.1.14 {
    sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
    execsql {
      ATTACH 'test2.db' AS aux;
      SELECT * FROM aux.t1;
    } db2
  } {}
  do_test pragma-8.1.15 {
    execsql {
      PRAGMA aux.schema_version = 206;
    }
  } {}
  do_test pragma-8.1.16 {
    set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
    sqlite3_step $::STMT
  } SQLITE_ERROR
  do_test pragma-8.1.17 {
    sqlite3_finalize $::STMT
  } SQLITE_SCHEMA
  do_test pragma-8.1.18 {
    db2 close
  } {}
}

# Now test that the user-version can be read and written (and that we aren't
# accidentally manipulating the schema-version instead).
do_test pragma-8.2.1 {
  execsql2 {
    PRAGMA user_version;
  }

  } {2}
  do_test pragma-8.2.4.3 {
    execsql {
      PRAGMA schema_version;
    }
  } {109}
}

ifcapable attach {
  db eval {ATTACH 'test2.db' AS aux}
  
  # Check that the user-version in the auxilary database can be manipulated (
  # and that we aren't accidentally manipulating the same in the main db).
  do_test pragma-8.2.5 {
    execsql {
      PRAGMA aux.user_version;
    }
  } {0}
  do_test pragma-8.2.6 {
    execsql {
      PRAGMA aux.user_version = 3;
    }
  } {}
  do_test pragma-8.2.7 {
    execsql {
      PRAGMA aux.user_version;
    }
  } {3}
  do_test pragma-8.2.8 {
    execsql {
      PRAGMA main.user_version;
    }
  } {2}
  
  # Now check that a ROLLBACK resets the user-version if it has been modified
  # within a transaction.
  do_test pragma-8.2.9 {
    execsql {
      BEGIN;
      PRAGMA aux.user_version = 10;
      PRAGMA user_version = 11;
    }
  } {}
  do_test pragma-8.2.10 {
    execsql {
      PRAGMA aux.user_version;
    }
  } {10}
  do_test pragma-8.2.11 {
    execsql {
      PRAGMA main.user_version;
    }
  } {11}
  do_test pragma-8.2.12 {
    execsql {
      ROLLBACK;
      PRAGMA aux.user_version;
    }
  } {3}
  do_test pragma-8.2.13 {
    execsql {
      PRAGMA main.user_version;
    }
  } {2}
}

# Try a negative value for the user-version
do_test pragma-8.2.14 {
  execsql {
    PRAGMA user_version = -450;
  }
} {}

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma2.test,v 1.4 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Test organization:
#
# pragma2-1.*: Test freelist_count pragma on the main database.

    PRAGMA main.freelist_count;
  }
} {1}

file delete -force test2.db
file delete -force test2.db-journal

ifcapable attach {
  do_test pragma2-2.1 {
    execsql {
      ATTACH 'test2.db' AS aux;
      PRAGMA aux.auto_vacuum=OFF;
      PRAGMA aux.freelist_count;
    }
  } {0}
  do_test pragma2-2.2 {
    execsql {
      CREATE TABLE aux.abc(a, b, c);
      PRAGMA aux.freelist_count;
    }
  } {0}
  do_test pragma2-2.3 {
    set ::val [string repeat 0123456789 1000]
    execsql {
      INSERT INTO aux.abc VALUES(1, 2, $::val);
      PRAGMA aux.freelist_count;
    }
  } {0}
  do_test pragma2-2.4 {
    expr {[file size test2.db] / 1024}
  } {11}
  do_test pragma2-2.5 {
    execsql {
      DELETE FROM aux.abc;
      PRAGMA aux.freelist_count;
    }
  } {9}
  
  do_test pragma2-3.1 {
    execsql {
      PRAGMA aux.freelist_count;
      PRAGMA main.freelist_count;
      PRAGMA freelist_count;
    }
  } {9 1 1}
  do_test pragma2-3.2 {
    execsql {
      PRAGMA freelist_count = 500;
      PRAGMA freelist_count;
    }
  } {1 1}
  do_test pragma2-3.3 {
    execsql {
      PRAGMA aux.freelist_count = 500;
      PRAGMA aux.freelist_count;
    }
  } {9 9}
}

finish_test

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the various conditions under which an SQLITE_SCHEMA
# error should be returned.
#
# $Id: schema.test,v 1.8 2007/10/09 08:29:33 danielk1977 Exp $

#---------------------------------------------------------------------
# When any of the following types of SQL statements or actions are 
# executed, all pre-compiled statements are invalidated. An attempt
# to execute an invalidated statement always returns SQLITE_SCHEMA.
#
# CREATE/DROP TABLE...................................schema-1.*

  sqlite3_finalize $::STMT
} {SQLITE_SCHEMA}

#---------------------------------------------------------------------
# Tests 5.1 to 5.4 check that prepared statements are invalidated when
# a database is DETACHed (but not when one is ATTACHed).
#
ifcapable attach {
  do_test schema-5.1 {
    set sql {SELECT * FROM abc;}
    set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]
    execsql {
      ATTACH 'test2.db' AS aux;
    }
    sqlite3_step $::STMT
  } {SQLITE_DONE}
  do_test schema-5.2 {
    sqlite3_reset $::STMT
  } {SQLITE_OK}
  do_test schema-5.3 {
    execsql {
      DETACH aux;
    }
    sqlite3_step $::STMT
  } {SQLITE_ERROR}
  do_test schema-5.4 {
    sqlite3_finalize $::STMT
  } {SQLITE_SCHEMA}
}

#---------------------------------------------------------------------
# Tests 6.* check that prepared statements are invalidated when
# a user-function is deleted (but not when one is added).
do_test schema-6.1 {
  set sql {SELECT * FROM abc;}
  set ::STMT [sqlite3_prepare $::DB $sql -1 TAIL]

#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the various conditions under which an SQLITE_SCHEMA
# error should be returned.  This is a copy of schema.test that
# has been altered to use sqlite3_prepare_v2 instead of sqlite3_prepare
#
# $Id: schema2.test,v 1.3 2007/10/09 08:29:33 danielk1977 Exp $

#---------------------------------------------------------------------
# When any of the following types of SQL statements or actions are 
# executed, all pre-compiled statements are invalidated. An attempt
# to execute an invalidated statement always returns SQLITE_SCHEMA.
#
# CREATE/DROP TABLE...................................schema2-1.*

  sqlite3_finalize $::STMT
} {SQLITE_OK}

#---------------------------------------------------------------------
# Tests 5.1 to 5.4 check that prepared statements are invalidated when
# a database is DETACHed (but not when one is ATTACHed).
#
ifcapable attach {
  do_test schema2-5.1 {
    set sql {SELECT * FROM abc;}
    set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]
    execsql {
      ATTACH 'test2.db' AS aux;
    }
    sqlite3_step $::STMT
  } {SQLITE_DONE}
  do_test schema2-5.2 {
    sqlite3_reset $::STMT
  } {SQLITE_OK}
  do_test schema2-5.3 {
    execsql {
      DETACH aux;
    }
    sqlite3_step $::STMT
  } {SQLITE_DONE}
  do_test schema2-5.4 {
    sqlite3_finalize $::STMT
  } {SQLITE_OK}
}

#---------------------------------------------------------------------
# Tests 6.* check that prepared statements are invalidated when
# a user-function is deleted (but not when one is added).
do_test schema2-6.1 {
  set sql {SELECT * FROM abc;}
  set ::STMT [sqlite3_prepare_v2 $::DB $sql -1 TAIL]

# 2005 December 30
#
# 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.
#
#***********************************************************************
#
# $Id: shared.test,v 1.28 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
db close

# These tests cannot be run without the ATTACH command.
#
ifcapable !shared_cache||!attach {
  finish_test
  return
}

set ::enable_shared_cache [sqlite3_enable_shared_cache 1]

foreach av [list 0 1] {

#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library. The 
# focus of this script is testing that the overflow-page related
# enhancements added after version 3.3.17 speed things up.
#
# $Id: speed3.test,v 1.5 2007/10/09 08:29:33 danielk1977 Exp $
#

#---------------------------------------------------------------------
# Test plan:
#
# If auto-vacuum is enabled for the database, the following cases
# should show performance improvement with respect to 3.3.17.

#     page and the overflow page containing the value do not have
#     to be read (test cases speed3-2.X).
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !tclvar||!attach {
  finish_test
  return
}

speed_trial_init speed1

# Set a uniform random seed

#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file contains tests to verify that the limits defined in
# sqlite source file limits.h are enforced.
#
# $Id: sqllimits1.test,v 1.19 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Test organization:
#
#     sqllimits-1.*:  SQLITE_MAX_LENGTH

  }
  catchsql "SELECT myfunc([join $vals ,])"
} {1 {too many arguments on function myfunc}}

#--------------------------------------------------------------------
# Test cases sqllimits-8.*: Test the SQLITE_MAX_ATTACHED limit.
#
ifcapable attach {
  do_test sqllimits-1.8.1 {
    set max $::SQLITE_MAX_ATTACHED
    for {set i 0} {$i < ($max)} {incr i} {
      file delete -force test${i}.db test${i}.db-journal
    }
    for {set i 0} {$i < ($max)} {incr i} {
      execsql "ATTACH 'test${i}.db' AS aux${i}"
    }
    catchsql "ATTACH 'test${i}.db' AS aux${i}"
  } "1 {too many attached databases - max $::SQLITE_MAX_ATTACHED}"
  do_test sqllimits-1.8.2 {
    set max $::SQLITE_MAX_ATTACHED
    for {set i 0} {$i < ($max)} {incr i} {
      execsql "DETACH aux${i}"
    }
  } {}
}

#--------------------------------------------------------------------
# Test cases sqllimits-9.*: Check that the SQLITE_MAX_VARIABLE_NUMBER 
# limit works.
#
do_test sqllimits-1.9.1 {
  set max $::SQLITE_MAX_VARIABLE_NUMBER

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that fsync is disabled when
# pragma synchronous=off even for multi-database commits.
#
# $Id: sync.test,v 1.6 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

#
# These tests are only applicable on unix when pager pragma are
# enabled. Also, since every test uses an ATTACHed database, they
# are only run when ATTACH is enabled.
#
if {$::tcl_platform(platform)!="unix"} {
  finish_test
  return
}
ifcapable !pager_pragmas||!attach {
  finish_test
  return
}

do_test sync-1.1 {
  set sqlite_sync_count 0
  file delete -force test2.db

# 2001 September 15
#
# 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.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.48 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {

      }
    } msg
  ]
  set result [list $rc $msg]
} {0 {}}

# Try to drop a table from within a callback:
do_test table-14.2 {
  set rc [
    catch {
      db eval {SELECT * FROM tablet8 LIMIT 1} {} {
        db eval {DROP TABLE t9;}
      }
    } msg
  ] 
  set result [list $rc $msg]
} {1 {database table is locked}}

ifcapable attach {
  # Now attach a database and ensure that a table can be created in the 
  # attached database whilst in a callback from a query on the main database.
  do_test table-14.3 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      ATTACH 'test2.db' as aux;
    }
    db eval {SELECT * FROM tablet8 LIMIT 1} {} {
      db eval {CREATE TABLE aux.t1(a, b, c)}
    }
  } {}
  
  # On the other hand, it should be impossible to drop a table when any VMs 
  # are active. This is because VerifyCookie instructions may have already
  # been executed, and btree root-pages may not move after this (which a
  # delete table might do).
  do_test table-14.4 {
    set rc [
      catch {
        db eval {SELECT * FROM tablet8 LIMIT 1} {} {
          db eval {DROP TABLE aux.t1;}
        }
      } msg
    ] 
    set result [list $rc $msg]
  } {1 {database table is locked}}
}

# Create and drop 2000 tables. This is to check that the balance_shallow()
# routine works correctly on the sqlite_master table. At one point it
# contained a bug that would prevent the right-child pointer of the
# child page from being copied to the root page.
#
do_test table-15.1 {

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for temporary tables and indices.
#
# $Id: temptable.test,v 1.19 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !tempdb {
  finish_test
  return

  sqlite3 db test.db
  catchsql {
    SELECT * FROM t8,t9;
  }
} {1 {no such table: t9}}

file delete -force test2.db test2.db-journal
ifcapable attach {
  do_test temptable-7.1 {
    catchsql {
      ATTACH 'test2.db' AS two;
      CREATE TEMP TABLE two.abc(x,y);
    }
  } {1 {temporary table name must be unqualified}}
}

# Need to do the following for tcl 8.5 on mac. On that configuration, the
# -readonly flag is taken so seriously that a subsequent [file delete -force]
# (required before the next test file can be executed) will fail.
#
catch {file attributes test.db -readonly 0}

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
#   This test attempts to deadlock SQLite in shared-cache mode.
#     
#
# $Id: thread002.test,v 1.2 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl
source $testdir/thread_common.tcl
if {[info commands sqlthread] eq ""} {
  finish_test
  return
}
ifcapable !attach { 
  finish_test
  return
}

db close
sqlite3_enable_shared_cache 1

set ::NTHREAD 10

#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #1873 has been
# fixed.  
#
#
# $Id: tkt1873.test,v 1.2 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !attach {
  finish_test
  return
}

file delete -force test2.db test2.db-journal

do_test tkt1873-1.1 {
  execsql {
    CREATE TABLE t1(x, y);
    ATTACH 'test2.db' AS aux;

# Ticket #764. At one stage TEMP triggers would fail to re-install when the
# schema was reloaded. The following tests ensure that TEMP triggers are
# correctly re-installed.
#
# Also verify that references within trigger programs are resolved at
# statement compile time, not trigger installation time. This means, for
# example, that you can drop and re-create tables referenced by triggers. 
ifcapable tempdb&&attach {
  do_test trigger1-10.0 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      ATTACH 'test2.db' AS aux;
    }
  } {}

# 2002 February 26
#
# 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.  The
# focus of this file is testing VIEW statements.
#
# $Id: view.test,v 1.34 2007/10/09 08:29:33 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Omit this entire file if the library is not configured with views enabled.
ifcapable !view {
  finish_test
  return

#
do_test view-12.1 {
  catchsql {
    CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=?
  }
} {1 {parameters are not allowed in views}}

ifcapable attach {
  do_test view-13.1 {
    file delete -force test2.db
    catchsql {
      ATTACH 'test2.db' AS two;
      CREATE TABLE two.t2(x,y);
      CREATE VIEW v13 AS SELECT y FROM two.t2;
    }
  } {1 {view v13 cannot reference objects in database two}}
}

# Ticket #1658
#
do_test view-14.1 {
  catchsql {
    CREATE TEMP VIEW t1 AS SELECT a,b FROM t1;
    SELECT * FROM temp.t1;

# 2006 June 10
#
# 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.  The
# focus of this file is creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.47 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !vtab||!schema_pragmas {
  finish_test
  return

} {31429}
do_test vtab1.7-13 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} {}

ifcapable attach {
  do_test vtab1.8-1 {
    set echo_module ""
    execsql {
      ATTACH 'test2.db' AS aux;
      CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc);
    }
    set echo_module
  } [list xCreate echo aux e2 real_abc   \
          xSync   echo(real_abc)         \
          xCommit echo(real_abc)         \
  ]
}
do_test vtab1.8-2 {
  catchsql {
    DROP TABLE aux.e2;
  }
  execsql {
    DROP TABLE treal;
    DROP TABLE techo;
    DROP TABLE echo_abc;
    DROP TABLE real_abc;
  }
} {}

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# of this test is reading and writing to the database from within a
# virtual table xSync() callback.
#
# $Id: vtab7.test,v 1.3 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !vtab {
  finish_test
  return

  execsql {
    INSERT INTO abc2 VALUES(1, 2, 3);
    SELECT name FROM sqlite_master ORDER BY name;
  }
} {abc abc2 log}

# Write to an attached database from xSync().
ifcapable attach {
  do_test vtab7-3.1 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      ATTACH 'test2.db' AS db2;
      CREATE TABLE db2.stuff(description, shape, color);
    }
    set ::callbacks(xSync,abc) {
      execsql { INSERT INTO db2.stuff VALUES('abc', 'square', 'green'); }
    }
    execsql {
      INSERT INTO abc2 VALUES(1, 2, 3);
      SELECT * from stuff;
    }
  } {abc square green}
}

# UPDATE: The next test passes, but leaks memory. So leave it out.
#
# The following tests test that writing to the database from within
# the xCommit callback causes a misuse error.
# do_test vtab7-4.1 {
#   unset -nocomplain ::callbacks(xSync,abc)