SQLite

/*
** 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.
**
*************************************************************************
** A TCL Interface to SQLite
**
** $Id: tclsqlite.c,v 1.87 2004/06/18 17:10:17 drh Exp $
*/
#ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */

#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

** (Hence there is no namespace.  There is no point in using a namespace
** if the extension only supplies one new name!)  The "sqlite" command is
** used to open a new SQLite database.  See the DbMain() routine above
** for additional information.
*/
int Sqlite3_Init(Tcl_Interp *interp){
  Tcl_InitStubs(interp, "8.0", 0);
  Tcl_CreateObjCommand(interp, "sqlite", (Tcl_ObjCmdProc*)DbMain, 0, 0);
  Tcl_PkgProvide(interp, "sqlite", "2.0");
  return TCL_OK;
}
int Tclsqlite3_Init(Tcl_Interp *interp){
  Tcl_InitStubs(interp, "8.0", 0);
  Tcl_CreateObjCommand(interp, "sqlite", (Tcl_ObjCmdProc*)DbMain, 0, 0);
  Tcl_PkgProvide(interp, "sqlite", "2.0");
  return TCL_OK;
}
int Sqlite3_SafeInit(Tcl_Interp *interp){
  return TCL_OK;
}
int Tclsqlite3_SafeInit(Tcl_Interp *interp){
  return TCL_OK;

#    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.23 2004/06/09 00:48:15 drh Exp $
#

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

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
do_test attach-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1;
  }
} {1 2 3 4}
do_test attach-1.2 {
  sqlite db2 test2.db
  execsql {
    CREATE TABLE t2(x,y);
    INSERT INTO t2 VALUES(1,'x');
    INSERT INTO t2 VALUES(2,'y');
    SELECT * FROM t2;
  } db2
} {1 x 2 y}

do_test attach-2.15 {
  execsql {
    SELECT type, name, tbl_name FROM db2.sqlite_master;
  }
} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
do_test attach-2.16 {
  db close
  sqlite db test.db
  execsql {
    ATTACH 'test2.db' AS db2;
    SELECT type, name, tbl_name FROM db2.sqlite_master;
  }
} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}

do_test attach-3.1 {
  db close
  db2 close
  sqlite db test.db
  sqlite db2 test2.db
  execsql {
    SELECT * FROM t1
  }
} {1 2 3 4}
do_test attach-3.2 {
  catchsql {
    SELECT * FROM t2

#set btree_trace 1

# Ticket #323
do_test attach-4.1 {
  execsql {DETACH db2}
  db2 close
  sqlite db2 test2.db
  execsql {
    CREATE TABLE t3(x,y);
    CREATE UNIQUE INDEX t3i1 ON t3(x);
    INSERT INTO t3 VALUES(1,2);
    SELECT * FROM t3;
  } db2;
} {1 2}

  }
} {910 1112 1516}

# Tests for the sqliteFix...() routines in attach.c
#
do_test attach-5.1 {
  db close
  sqlite db test.db
  db2 close
  file delete -force test2.db
  sqlite db2 test2.db
  catchsql {
    ATTACH DATABASE 'test.db' AS orig;
    CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN;
      SELECT 'no-op';
    END;
  } db2
} {1 {trigger r1 cannot reference objects in database orig}}

do_test attach-6.1 {
  catchsql {
    ATTACH DATABASE 'no-such-file' AS nosuch;
  }
} {0 {}}
if {$tcl_platform(platform)=="unix"} {
  do_test attach-6.2 {
    sqlite dbx cannot-read
    dbx eval {CREATE TABLE t1(a,b,c)}
    dbx close
    file attributes cannot-read -permission 0000
    catchsql {
      ATTACH DATABASE 'cannot-read' AS noread;
    }
  } {1 {unable to open database: cannot-read}}

#    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.18 2004/06/14 09:41:18 danielk1977 Exp $
#

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


# 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 {
  db eval {
    CREATE TABLE t1(a,b);
    CREATE INDEX x1 ON t1(a);
  }
  file delete -force test2.db
  file delete -force test2.db-journal
  sqlite db2 test2.db
  db2 eval {
    CREATE TABLE t1(a,b);
    CREATE INDEX x1 ON t1(a);
  }
  catchsql {
    ATTACH 'test2.db' AS t2;
  }

  }
} {1 {cannot commit - no transaction is active}}

# Ticket #574:  Make sure it works using the non-callback API
#
do_test attach2-3.1 {
  db close
  set DB [sqlite db test.db]
  set rc [catch {sqlite3_prepare $DB "ATTACH 'test2.db' AS t2" -1 TAIL} VM]
  if {$rc} {lappend rc $VM}
  sqlite3_finalize $VM
  set rc
} {0}
do_test attach2-3.2 {
  set rc [catch {sqlite3_prepare $DB "DETACH t2" -1 TAIL} VM]

  }] $expected_result
}
set sqlite_os_trace 0

# Tests attach2-4.* test that read-locks work correctly with attached
# databases.
do_test attach2-4.1 {
  sqlite db test.db
  sqlite db2 test.db
  execsql {ATTACH 'test2.db' as file2}
  execsql {ATTACH 'test2.db' as file2} db2
} {}

lock_status 4.1.1 db {main unlocked temp unlocked file2 unlocked}
lock_status 4.1.2 db2 {main unlocked temp unlocked file2 unlocked}

# These tests - attach2-5.* - check that the master journal file is deleted
# correctly when a multi-file transaction is committed or rolled back.
#
# Update: It's not actually created if a rollback occurs, so that test
# doesn't really prove too much.
foreach f [glob test.db*] {file delete -force $f}
do_test attach2-5.1 {
  sqlite db test.db
  execsql {
    ATTACH 'test.db2' AS aux;
  }
} {}
do_test attach2-5.2 {
  execsql {
    BEGIN;

#    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.6 2004/06/07 07:52:19 danielk1977 Exp $
#


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

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

# Create tables t1 and t2 in database file test2.db
file delete -force test2.db
sqlite db2 test2.db
execsql {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(c, d);
} db2
db2 close

# Create a table in the auxilary database.

#    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: auth.test,v 1.14 2004/06/14 11:54:18 danielk1977 Exp $
#

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

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

do_test auth-1.1.1 {
  db close
  set ::DB [sqlite db test.db]
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  db authorizer ::auth

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the ability of SQLite to handle database
# files larger than 4GB.
#
# $Id: bigfile.test,v 1.4 2004/06/13 23:07:04 drh Exp $
#

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

# These tests only work for Tcl version 8.4 and later.  Prior to 8.4,
# Tcl was unable to handle large files.

if {[catch {fake_big_file 4096 test.db}]} {
  puts "**** Unable to create a file larger than 4096 MB. *****"
  finish_test
  return
}

do_test bigfile-1.2 {
  sqlite db test.db
  execsql {
    SELECT md5sum(x) FROM t1;
  }
} $::MAGIC_SUM


# The previous test may fail on some systems because they are unable

  execsql {
    CREATE TABLE t2 AS SELECT * FROM t1;
    SELECT md5sum(x) FROM t2;
  }
} $::MAGIC_SUM
do_test bigfile-1.4 {
  db close
  sqlite db test.db
  execsql {
    SELECT md5sum(x) FROM t1;
  }
} $::MAGIC_SUM
do_test bigfile-1.5 {
  execsql {
    SELECT md5sum(x) FROM t2;
  }
} $::MAGIC_SUM

db close
if {[catch {fake_big_file 8192 test.db}]} {
  puts "**** Unable to create a file larger than 8192 MB. *****"
  finish_test
  return
}

do_test bigfile-1.6 {
  sqlite db test.db
  execsql {
    SELECT md5sum(x) FROM t1;
  }
} $::MAGIC_SUM
do_test bigfile-1.7 {
  execsql {
    CREATE TABLE t3 AS SELECT * FROM t1;
    SELECT md5sum(x) FROM t3;
  }
} $::MAGIC_SUM
do_test bigfile-1.8 {
  db close
  sqlite db test.db
  execsql {
    SELECT md5sum(x) FROM t1;
  }
} $::MAGIC_SUM
do_test bigfile-1.9 {
  execsql {
    SELECT md5sum(x) FROM t2;

if {[catch {fake_big_file 16384 test.db}]} {
  puts "**** Unable to create a file larger than 16384 MB. *****"
  finish_test
  return
}

do_test bigfile-1.11 {
  sqlite db test.db
  execsql {
    SELECT md5sum(x) FROM t1;
  }
} $::MAGIC_SUM
do_test bigfile-1.12 {
  execsql {
    CREATE TABLE t4 AS SELECT * FROM t1;
    SELECT md5sum(x) FROM t4;
  }
} $::MAGIC_SUM
do_test bigfile-1.13 {
  db close
  sqlite db test.db
  execsql {
    SELECT md5sum(x) FROM t1;
  }
} $::MAGIC_SUM
do_test bigfile-1.14 {
  execsql {
    SELECT md5sum(x) FROM t2;

# 2003 September 6
#
# 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 testing the sqlite_bind API.
#
# $Id: bind.test,v 1.11 2004/05/27 09:28:44 danielk1977 Exp $
#

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

proc sqlite_step {stmt N VALS COLS} {
  upvar VALS vals

  }

  return $rc
}

do_test bind-1.1 {
  db close
  set DB [sqlite db test.db]
  execsql {CREATE TABLE t1(a,b,c)}
  set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(?,?,?)} -1 TAIL]
  set TAIL
} {}
do_test bind-1.2 {
  sqlite_step $VM N VALUES COLNAMES
} {SQLITE_DONE}

# 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.
#
# $Id: blob.test,v 1.1 2004/05/27 13:55:27 danielk1977 Exp $

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

proc bin_to_hex {blob} {
  set bytes {}
  binary scan $blob \c* bytes

  set blobs2 [list]
  foreach b $blobs {lappend blobs2 [bin_to_hex $b]}
  set blobs2
} {}

# Try to bind a blob value to a prepared statement.
do_test blob-3.0 {
  set DB [sqlite db2 test.db]
  set STMT [sqlite3_prepare $DB "DELETE FROM t1 WHERE a = ?" -1 DUMMY]
  sqlite3_bind_blob $STMT 1 "\x12\x34\x56" 3
  sqlite3_step $STMT
} {SQLITE_DONE}
do_test blob-3.1 {
  sqlite3_finalize $STMT
  db2 close

# 2003 January 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.13 2004/06/15 02:44:20 danielk1977 Exp $
#

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

# proc sqlite_step {stmt N VALS COLS} {
#   upvar $VALS vals

  return $VALUES
}

# Check basic functionality
#
do_test capi2-1.1 {
  db close
  set DB [sqlite db test.db]
  execsql {CREATE TABLE t1(a,b,c)}
  set VM [sqlite3_prepare $DB {SELECT name, rowid FROM sqlite_master} -1 TAIL]
  set TAIL
} {}
do_test capi2-1.2 {
  sqlite3_step $VM
} {SQLITE_ROW}

    INSERT INTO t3 VALUES(2);
    INSERT INTO t3 SELECT x+2 FROM t3;
    INSERT INTO t3 SELECT x+4 FROM t3;
    INSERT INTO t3 SELECT x+8 FROM t3;
    COMMIT;
  }
  set VM1 [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL]
  sqlite db2 test.db
  execsql {BEGIN} db2
} {}
# Update for v3: BEGIN doesn't write-lock the database. It is quite
# difficult to get v3 to write-lock the database, which causes a few
# problems for test scripts.
#
# do_test capi2-6.2 {

# 2003 January 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the callback-free C/C++ API.
#
# $Id: capi3.test,v 1.10 2004/05/28 13:13:04 danielk1977 Exp $
#

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

# Return the UTF-16 representation of the supplied UTF-8 string $str.
# If $nt is true, append two 0x00 bytes as a nul terminator.

# capi3-2.*: Test sqlite3_prepare16
# capi3-3.*: Test sqlite3_open
# capi3-4.*: Test sqlite3_open16
# capi3-5.*: Test the various sqlite3_result_* APIs
#

db close
set DB [sqlite db test.db]

do_test capi3-1.1 {
  set STMT [sqlite3_prepare $DB {SELECT name FROM sqlite_master} -1 TAIL]
  sqlite3_finalize $STMT
  set TAIL
} {}
do_test capi3-1.2 {

# 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 script is page cache subsystem.
#
# $Id: collate3.test,v 1.2 2004/06/11 10:51:41 danielk1977 Exp $

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

#
# Tests are organised as follows:
#

#
do_test collate3-2.0 {
  db collate string_compare {string compare}
  execsql {
    CREATE TABLE collate3t1(c1 COLLATE string_compare, c2);
  }
  db close
  sqlite db test.db
  expr 0
} 0
do_test collate3-2.1 {
  catchsql {
    SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
  }
} {1 {no such collation sequence: string_compare}} 

do_test collate3-3.0 {
  db collate string_compare {string compare}
  execsql {
    CREATE INDEX collate3t1_i1 ON collate3t1(c1);
    INSERT INTO collate3t1 VALUES('xxx', 'yyy');
  }
  db close
  sqlite db test.db
  expr 0
} 0
db eval {select * from collate3t1}
breakpoint
do_test collate3-3.1 {
  catchsql {
    INSERT INTO collate3t1 VALUES('xxx', 0);

    CREATE TABLE collate3t1(a, b);
    INSERT INTO collate3t1 VALUES('hello', NULL);
    CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined);
  }
} {}
do_test collate3-4.7 {
  db close
  sqlite db test.db
  catchsql {
    SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
  }
} {1 {no such collation sequence: user_defined}}
do_test collate3-4.8 {
  db collate user_defined "string compare"
  catchsql {
    SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
  }
} {0 {hello {}}}
do_test collate3-4.8 {
  db close
  lindex [catch {
    sqlite db test.db
  }] 0
} {0}
do_test collate3-4.8 {
  execsql {
    DROP TABLE collate3t1;
  }
} {}

    CREATE VIEW collate3v1 AS SELECT * FROM collate3t1 
        ORDER BY 1 COLLATE user_defined;
    SELECT * FROM collate3v1;
  }
} {2 {} 12 {} 101 {}}
do_test collate3-4.10 {
  db close
  sqlite db test.db
  catchsql {
    SELECT * FROM collate3v1;
  }
} {1 {no such collation sequence: user_defined}}
do_test collate3-4.11 {
  db collate user_defined numeric_compare
  catchsql {

} {0 10}
do_test collate3-5.7 {
  execsql {
    DROP TABLE collate3t1;
    CREATE TABLE collate3t1(a COLLATE unk);
  }
  db close
  sqlite db test.db
  catchsql {
    SELECT a FROM collate3t1 ORDER BY 1;
  }
} {1 {no such collation sequence: unk}}
do_test collate3-5.8 {
  set ::cfact_cnt 0
  proc cfact {nm} {

# 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 DELETE FROM statement.
#
# $Id: delete.test,v 1.15 2004/06/10 05:59:25 danielk1977 Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {

    INSERT INTO t3 VALUES(123);
    SELECT * FROM t3;
  }
} {123}
db close
catch {file attributes test.db -permissions 0444}
catch {file attributes test.db -readonly 1}
sqlite db test.db
do_test delete-8.1 {
  catchsql {
    DELETE FROM t3;
  }
} {1 {attempt to write a readonly database}}
do_test delete-8.2 {
  execsql {SELECT * FROM t3} 

#
#***********************************************************************
# 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: enc.test,v 1.2 2004/06/18 04:24:56 danielk1977 Exp $

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

proc do_bincmp_test {testname got expect} {
  binary scan $expect \c* expectvals
  binary scan $got \c* gotvals

# sqlite_utf16to8 (steps 3, 4)
# sqlite_utf16to16le (step 5)
# sqlite_utf16to16be (step 5)
#
proc test_conversion {testname str} {
 
  # Step 1.
  set utf16le_sqlite [test_translate $str UTF8 UTF16LE]
  set utf16le_tcl [encoding convertto unicode $str]
  append utf16le_tcl "\x00\x00"
  if { $::tcl_platform(byteOrder)!="littleEndian" } {
    set utf16le_tcl [swap_byte_order $utf16le_tcl]
  }
  do_bincmp_test $testname.1 $utf16le_sqlite $utf16le_tcl
  set utf16le $utf16le_tcl

  # Step 2.
  set utf16be_sqlite [test_translate $str UTF8 UTF16BE]
  set utf16be_tcl [encoding convertto unicode $str]
  append utf16be_tcl "\x00\x00"
  if { $::tcl_platform(byteOrder)=="littleEndian" } {
    set utf16be_tcl [swap_byte_order $utf16be_tcl]
  }
  do_bincmp_test $testname.2 $utf16be_sqlite $utf16be_tcl
  set utf16be $utf16be_tcl
 
  # Step 3.
  if { $::tcl_platform(byteOrder)=="littleEndian" } {
    set utf16 $utf16le
  } else {
    set utf16 $utf16be
  }
  set utf8_sqlite [test_translate $utf16 UTF16 UTF8]
  do_bincmp_test $testname.3 $utf8_sqlite [binarize $str]

  # Step 4 (little endian).
  append utf16le_bom "\xFF\xFE" $utf16le
  set utf8_sqlite [test_translate $utf16le_bom UTF16 UTF8]
  do_bincmp_test $testname.4.le $utf8_sqlite [binarize $str]

  # Step 4 (big endian).
  append utf16be_bom "\xFE\xFF" $utf16be
  set utf8_sqlite [test_translate $utf16be_bom UTF16 UTF8]
  do_bincmp_test $testname.4.be $utf8_sqlite [binarize $str]

  # Step 5 (little endian to little endian).
  set utf16_sqlite [test_translate $utf16le_bom UTF16LE UTF16LE]
  do_bincmp_test $testname.5.le.le $utf16_sqlite $utf16le

  # Step 5 (big endian to big endian).
  set utf16_sqlite [test_translate $utf16be_bom UTF16 UTF16BE]
  do_bincmp_test $testname.5.be.be $utf16_sqlite $utf16be

  # Step 5 (big endian to little endian).
  set utf16_sqlite [test_translate $utf16be_bom UTF16 UTF16LE]
  do_bincmp_test $testname.5.be.le $utf16_sqlite $utf16le

  # Step 5 (little endian to big endian).
  set utf16_sqlite [test_translate $utf16le_bom UTF16 UTF16BE]
  do_bincmp_test $testname.5.le.be $utf16_sqlite $utf16be
}

translate_selftest

test_conversion enc-1 "hello world"
test_conversion enc-2 "sqlite"
test_conversion enc-3 ""

#
#***********************************************************************
# 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.11 2004/06/18 06:02:35 danielk1977 Exp $

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

# The rough organisation of tests in this file is:
#
# enc2.1.*: Simple tests with a UTF-8 db.

# database, and that it is possible to retreive values in
# various text encodings.
#
proc run_test_script {t enc} {

# Open the database and pull out a (the) row.
do_test $t.1 {
  set DB [sqlite db test.db]
  execsql {SELECT * FROM t1}
} {one I 1}

# Insert some data
do_test $t.2 {
  execsql {INSERT INTO t1 VALUES('two', 'II', 2);}
  execsql {SELECT * FROM t1}

# The three unicode encodings understood by SQLite.
set encodings [list UTF-8 UTF-16le UTF-16be]

set sqlite_os_trace 0
set i 1
foreach enc $encodings {
  file delete -force test.db
  sqlite db test.db
  db eval "PRAGMA encoding = \"$enc\""
  execsql $dbcontents
  db close
  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.
do_test enc2-4.1 {
  file delete -force test.db
  sqlite 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
  sqlite 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;
  }

  set l [lsearch -exact $::values $lhs]
  set r [lsearch -exact $::values $rhs]
  set res [expr $l - $r]
  return $res
}

file delete -force test.db
set DB [sqlite db test.db]
do_test enc2-5.0 {
  execsql {
    CREATE TABLE t5(a);
    INSERT INTO t5 VALUES('one');
    INSERT INTO t5 VALUES('two');
    INSERT INTO t5 VALUES('five');
    INSERT INTO t5 VALUES('three');

  add_test_collate $DB 0 0 1
  set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
  lappend res $::test_collate_enc
} {one two three four five UTF-16BE}

db close
file delete -force test.db
set DB [sqlite db test.db]
execsql {pragma encoding = 'UTF-16LE'}
do_test enc2-5.4 {
  execsql {
    CREATE TABLE t5(a);
    INSERT INTO t5 VALUES('one');
    INSERT INTO t5 VALUES('two');
    INSERT INTO t5 VALUES('five');

  add_test_collate $DB 1 0 0
  set res [execsql {SELECT * FROM t5 ORDER BY 1 COLLATE test_collate}]
  lappend res $::test_collate_enc
} {one two three four five UTF-8}

db close
file delete -force test.db
set DB [sqlite db test.db]
execsql {pragma encoding = 'UTF-16BE'}
do_test enc2-5.8 {
  execsql {
    CREATE TABLE t5(a);
    INSERT INTO t5 VALUES('one');
    INSERT INTO t5 VALUES('two');
    INSERT INTO t5 VALUES('five');

# 1: Open an empty database file assuming UTF-16 encoding.
# 2: Open the same database with a different handle assuming UTF-8. Create
#    a table using this handle.
# 3: Read the sqlite_master table from the first handle. 
# 4: Ensure the first handle recognises the database encoding is UTF-8.
#
do_test enc2-6.1 {
  sqlite db test.db
  execsql {
    PRAGMA encoding = 'UTF-16';
    SELECT * FROM sqlite_master;
  }
} {}
do_test enc2-6.2 {
  set enc [execsql {
    PRAGMA encoding;
  }]
  string range $enc 0 end-2 ;# Chop off the "le" or "be"
} {UTF-16}
do_test enc2-6.3 {
  sqlite db2 test.db
  execsql {
    PRAGMA encoding = 'UTF-8';
    CREATE TABLE abc(a, b, c);
  } db2
} {}
do_test enc2-6.4 {
  execsql {

# 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 expressions.
#
# $Id: expr.test,v 1.34 2004/06/17 05:36:45 danielk1977 Exp $

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

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
execsql {INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')}
proc test_expr {name settings expr result} {
  do_test $name [format {
    execsql {BEGIN; UPDATE test1 SET %s; SELECT %s FROM test1; ROLLBACK;}
  } $settings $expr] $result
}

test_expr expr-1.1 {i1=10, i2=20} {i1+i2} 30
test_expr expr-1.2 {i1=10, i2=20} {i1-i2} -10
test_expr expr-1.3 {i1=10, i2=20} {i1*i2} 200
# update for sqlite v3: Change 0.5 to 0 in expr1.4 due to manifest types.
test_expr expr-1.4 {i1=10, i2=20} {i1/i2} 0
test_expr expr-1.5 {i1=10, i2=20} {i2/i1} 2
test_expr expr-1.6 {i1=10, i2=20} {i2<i1} 0
test_expr expr-1.7 {i1=10, i2=20} {i2<=i1} 0
test_expr expr-1.8 {i1=10, i2=20} {i2>i1} 1
test_expr expr-1.9 {i1=10, i2=20} {i2>=i1} 1
test_expr expr-1.10 {i1=10, i2=20} {i2!=i1} 1

# 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 built-in functions.
#
# $Id: func.test,v 1.22 2004/06/17 05:36:45 danielk1977 Exp $

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

# Create a table to work with.
#
do_test func-0.0 {

# Use the "sqlite_register_test_function" TCL command which is part of
# the text fixture in order to verify correct operation of some of
# the user-defined SQL function APIs that are not used by the built-in
# functions.
#
db close
set ::DB [sqlite db test.db]
sqlite_register_test_function $::DB testfunc
do_test func-10.1 {
  catchsql {
    SELECT testfunc(NULL,NULL);
  }
} {1 {first argument should be one of: int int64 string double null value}}
do_test func-10.2 {

# Test the built-in sqlite_version(*) SQL function.
#
do_test func-11.1 {
  execsql {
    SELECT sqlite_version(*);
  }
} [sqlite -version]

# Test that destructors passed to sqlite by calls to sqlite3_result_text()
# etc. are called. These tests use two special user-defined functions
# (implemented in func.c) only available in test builds. 
#
# Function test_destructor() takes one argument and returns a copy of the
# text form of that argument. A destructor is associated with the return
# value. Function test_destructor_count() returns the number of outstanding
# destructor calls for values returned by test_destructor().

    SELECT test_auxdata('hello'||'world', a) FROM t4;
  }
} {{0 0} {1 0}}

# Test that auxilary data is preserved between calls for SQL variables.
do_test func-13.7 {
  db close
  set DB [sqlite db test.db]
  set sql "SELECT test_auxdata( ? , a ) FROM t4;"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_bind_text $STMT 1 hello -1
  set res [list]
  while { "SQLITE_ROW"==[sqlite3_step $STMT] } {
    lappend res [sqlite3_column_text $STMT 0]
  }

# 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 INDEX statement.
#
# $Id: index.test,v 1.29 2004/06/17 06:13:35 danielk1977 Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
  execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
  execsql {CREATE INDEX index1 ON test1(f1)}
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} {index1 test1}
do_test index-1.1b {
  execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 
           WHERE name='index1'}
} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
do_test index-1.1c {
  db close
  sqlite db test.db
  execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 
           WHERE name='index1'}
} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
do_test index-1.1d {
  db close
  sqlite db test.db
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} {index1 test1}

# Verify that the index dies with the table
#
do_test index-1.2 {
  execsql {DROP TABLE test1}

# Numeric strings should compare as if they were numbers.  So even if the
# strings are not character-by-character the same, if they represent the
# same number they should compare equal to one another.  Verify that this
# is true in indices.
#
# Updated for sqlite v3: SQLite will now store these values as numbers
# (because the affinity of column a is NUMERIC) so the quirky
# representations are not retained. i.e. '+1.0' becomes '1'.
do_test index-12.1 {
  execsql {
    CREATE TABLE t4(a NUM,b);
    INSERT INTO t4 VALUES('0.0',1);
    INSERT INTO t4 VALUES('0.00',2);

# 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 INSERT statement.
#
# $Id: insert.test,v 1.17 2004/05/13 05:16:17 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {

    DELETE FROM test2;
    CREATE INDEX index9 ON test2(f1,f2);
    CREATE INDEX indext ON test2(f4,f5);
    SELECT * from test2;
  }
} {}

# Update for sqlite v3:
# Change the 111 to '111' in the following two test cases, because
# the default value is being inserted as a string. TODO: It shouldn't be.
do_test insert-3.2 {
  execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')}
  execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.3 {

# 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.4 2004/06/15 11:40:10 danielk1977 Exp $

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

set ::go 1
for {set n 1} {$go} {incr n} {
  do_test ioerr-1.$n.1 {
    set ::sqlite_io_error_pending 0
    db close
    catch {file delete -force test.db}
    catch {file delete -force test.db-journal}
    sqlite db test.db
    execsql {SELECT * FROM sqlite_master}
  } {}
  do_test ioerr-1.$n.2 [subst {
    set ::sqlite_io_error_pending $n
  }] $n
  do_test ioerr-1.$n.3 {
    set r [catch {db eval {

set ::go 1
for {set n 1} {$go} {incr n} {
  do_test ioerr-2.$n.1 {
    set ::sqlite_io_error_pending 0
    db close
    catch {file delete -force test.db}
    catch {file delete -force test.db-journal}
    sqlite db test.db
    execsql {
      BEGIN;
      CREATE TABLE t1(a, b, c);
      INSERT INTO t1 VALUES(1, randstr(5,50), randstr(5,50));
      INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1;
      INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;
      INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1;

    # puts "error_pending=$::sqlite_io_error_pending"
    # if {$r} {puts $msg}
set sqlite_os_trace 0
    set ::go [expr {$::sqlite_io_error_pending<=0}]
    expr {$::sqlite_io_error_pending>0 || $r!=0}
    set ::sqlite_io_error_pending 0
    db close
    sqlite db test.db
    cksum
  } $cksum
}
set ::sqlite_io_error_pending 0

finish_test

# 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 script is database locks.
#
# $Id: lock.test,v 1.25 2004/06/14 08:26:37 danielk1977 Exp $


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

# Create an alternative connection to the database
#
do_test lock-1.0 {
  sqlite db2 ./test.db
  set dummy {}
} {}
do_test lock-1.1 {
  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
} {}
do_test lock-1.2 {
  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2

# has the database locked.
#
do_test lock-4.1 {
  db2 close
  catch {db eval ROLLBACK}
  db eval BEGIN
  db eval {UPDATE t1 SET a=0 WHERE 0}
  sqlite db2 ./test.db
  catchsql {UPDATE t1 SET a=0} db2
} {1 {database is locked}}
do_test lock-4.2 {
  set ::callback_value {}
  set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
  lappend rc $msg $::callback_value
} {1 {database is locked} {}}

# 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 exercising the code in main.c.
#
# $Id: main.test,v 1.14 2003/05/04 17:58:27 drh Exp $

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

# Tests of the sqlite_complete() function.
#
do_test main-1.1 {

#
do_test main-2.0 {
  catch {db close}
  file delete -force test.db
  set fd [open test.db w]
  puts $fd hi!
  close $fd
  set v [catch {sqlite db test.db} msg]
  if {$v} {lappend v $msg} {lappend v {}}
} {0 {}}

# Here are some tests for tokenize.c.  
#
do_test main-3.1 {
  catch {db close}
  foreach f [glob -nocomplain testdb/*] {file delete -force $f}
  file delete -force testdb
  sqlite db testdb
  set v [catch {execsql {SELECT * from T1 where x!!5}} msg]
  lappend v $msg
} {1 {unrecognized token: "!!"}}
do_test main-3.2 {
  catch {db close}
  foreach f [glob -nocomplain testdb/*] {file delete -force $f}
  file delete -force testdb
  sqlite db testdb
  set v [catch {execsql {SELECT * from T1 where @x}} msg]
  lappend v $msg
} {1 {unrecognized token: "@"}}

do_test main-3.3 {
  catch {db close}
  foreach f [glob -nocomplain testdb/*] {file delete -force $f}
  file delete -force testdb
  sqlite db testdb
  execsql {
    create table T1(X REAL);
    insert into T1 values(0.5);
    insert into T1 values(0.5e2);
    insert into T1 values(0.5e-002);
    insert into T1 values(5e-002);
    insert into T1 values(-5.0e-2);

#***********************************************************************
# This file attempts to check the library in an out-of-memory situation.
# When compiled with -DMEMORY_DEBUG=1, the SQLite library accepts a special
# command (sqlite_malloc_fail N) 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.7 2004/05/27 17:22:56 drh Exp $

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

# Only run these tests if memory debugging is turned on.
#
if {[info command sqlite_malloc_stat]==""} {
   puts "Skipping malloc tests: not compiled with -DMEMORY_DEBUG..."
   finish_test
   return
}

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-1.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
          CREATE TABLE t1(
             a int, b float, c double, d text, e varchar(20),
             primary key(a,b,c)

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-2.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         INSERT INTO t1 VALUES(1,1,'99 abcdefghijklmnopqrstuvwxyz');

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-3.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-4.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-5.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a,b);
         CREATE TABLE t2(x,y);

# 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 script is in-memory database backend.
#
# $Id: memdb.test,v 1.8 2004/06/15 11:40:10 danielk1977 Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
# Verify that after the rollback, the MD5 checksum is unchanged.
#
# These tests were browed from trans.tcl.
#
do_test memdb-1.1 {
  db close
  sqlite db :memory:
  # sqlite db test.db
  execsql {
    BEGIN;
    CREATE TABLE t3(x TEXT);
    INSERT INTO t3 VALUES(randstr(10,400));
    INSERT INTO t3 VALUES(randstr(10,400));
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;

# 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 runs all tests.
#
# $Id: memleak.test,v 1.3 2004/02/12 18:46:39 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {
  catch {db close}
  memleak_check

  quick.test
  malloc.test
  misuse.test
  memleak.test
  btree2.test
  trans.test
}
if {[sqlite -has-codec]} {
  lappend EXCLUDE \
    attach.test \
    attach2.test \
    auth.test \
    format3.test \
    version.test
}

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.27 2004/06/10 05:59:25 danielk1977 Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {

# another process has the database locked.
#
# Update for v3: The BEGIN doesn't lock the database so the schema is read
# and the SELECT returns successfully.
do_test misc1-11.1 {
  execsql {BEGIN}
  execsql {UPDATE t1 SET a=0 WHERE 0}
  sqlite db2 test.db
  set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
  lappend rc $msg
# v2 result: {1 {database is locked}}
} {0 3}
do_test misc1-11.2 {
  execsql {COMMIT}
  set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc2.test,v 1.11 2003/12/17 23:57:36 drh Exp $

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

# Test for ticket #360
#
do_test misc2-1.1 {

# Make sure we can open a database with an empty filename.  What this
# does is store the database in a temporary file that is deleted when
# the database is closed.  Ticket #432.
#
do_test misc2-6.1 {
  db close
  sqlite db {}
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
    SELECT * FROM t1;
  }
} {1 2}

# Make sure we get an error message (not a segfault) on an attempt to
# update a table from within the callback of a select on that same
# table.
#
do_test misc2-7.1 {
  db close
  file delete -force test.db
  sqlite db test.db
  execsql {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(1);
  }
  set rc [catch {
    db eval {SELECT rowid FROM t1} {} {
      db eval "DELETE FROM t1 WHERE rowid=$rowid"

    }
  } msg]
  lappend rc $msg
} {1 {database table is locked}}
do_test misc2-7.3 {
  db close
  file delete -force test.db
  sqlite db :memory:
  execsql {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(1);
  }
  set rc [catch {
    db eval {SELECT rowid FROM t1} {} {
      db eval "DELETE FROM t1 WHERE rowid=$rowid"

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the SQLITE_MISUSE detection logic.
# This test file leaks memory and file descriptors.
#
# $Id: misuse.test,v 1.4 2004/01/07 19:24:48 drh Exp $

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

# Make sure the test logic works
#
do_test misuse-1.1 {
  db close
  catch {file delete -force test2.db}
  set ::DB [sqlite db test2.db]
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
  }
  sqlite_exec_printf $::DB {SELECT * FROM t1} {}
} {0 {a b 1 2}}
do_test misuse-1.2 {

  }
} {0 {1 2}}

# Attempt to register a new SQL function while an sqlite_exec() is active.
#
do_test misuse-2.1 {
  db close
  set ::DB [sqlite db test2.db]
  execsql {
    SELECT * FROM t1
  }
} {1 2}
do_test misuse-2.2 {
  sqlite_exec_printf $::DB {SELECT * FROM t1} {}
} {0 {a b 1 2}}

  }
} {1 {library routine called out of sequence}}

# Attempt to register a new SQL aggregate while an sqlite_exec() is active.
#
do_test misuse-3.1 {
  db close
  set ::DB [sqlite db test2.db]
  execsql {
    SELECT * FROM t1
  }
} {1 2}
do_test misuse-3.2 {
  sqlite_exec_printf $::DB {SELECT * FROM t1} {}
} {0 {a b 1 2}}

  }
} {1 {library routine called out of sequence}}

# Attempt to close the database from an sqlite_exec callback.
#
do_test misuse-4.1 {
  db close
  set ::DB [sqlite db test2.db]
  execsql {
    SELECT * FROM t1
  }
} {1 2}
do_test misuse-4.2 {
  sqlite_exec_printf $::DB {SELECT * FROM t1} {}
} {0 {a b 1 2}}

  }
} {1 {library routine called out of sequence}}

# Attempt to use a database after it has been closed.
#
do_test misuse-5.1 {
  db close
  set ::DB [sqlite db test2.db]
  execsql {
    SELECT * FROM t1
  }
} {1 2}
do_test misuse-5.2 {
  sqlite_exec_printf $::DB {SELECT * FROM t1} {}
} {0 {a b 1 2}}
do_test misuse-5.3 {
  db close
  sqlite_exec_printf $::DB {SELECT * FROM t1} {}
} {21 {library routine called out of sequence}}

finish_test

# 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 script is page cache subsystem.
#
# $Id: pager.test,v 1.16 2004/06/14 06:13:06 danielk1977 Exp $


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

if {[info commands pager_open]!=""} {
db close

} ;# end if( not mem: and has pager_open command );

if 0 {
# Ticket #615: an assertion fault inside the pager.  It is a benign
# fault, but we might as well test for it.
#
do_test pager-5.1 {
  sqlite db test.db
  execsql {
    BEGIN;
    CREATE TABLE t1(x);
    PRAGMA synchronous=off;
    COMMIT;
  }
} {}
}

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.12 2004/05/26 06:58:45 danielk1977 Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
db close
file delete test.db
set DB [sqlite db test.db]

do_test pragma-1.1 {
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {2000 2000 1 1}
do_test pragma-1.2 {
  execsql {
    PRAGMA cache_size=1234;
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {1234 2000 1 1}
do_test pragma-1.3 {
  db close
  sqlite db test.db
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {2000 2000 1 1}

    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {4321 2000 1 1}
do_test pragma-1.7 {
  db close
  sqlite db test.db
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {2000 2000 1 1}

    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}
do_test pragma-1.10 {
  db close
  set ::DB [sqlite db test.db]
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}

    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 2 0}
do_test pragma-1.13 {
  db close
  set ::DB [sqlite db test.db]
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}
do_test pragma-1.14 {
  execsql {
    PRAGMA default_synchronous=FULL;
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 2 2}
do_test pragma-1.15 {
  db close
  set ::DB [sqlite db test.db]
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 2 2}

# Construct a corrupted index and make sure the integrity_check
# pragma finds it.
#
if {![sqlite -has-codec]} {
do_test pragma-3.1 {
  execsql {
    BEGIN;
    CREATE TABLE t2(a,b,c);
    CREATE INDEX i2 ON t2(a);
    INSERT INTO t2 VALUES(11,2,3);
    INSERT INTO t2 VALUES(22,3,4);

do_test pragma-4.6 {
  execsql {
    PRAGMA temp_store;
  }
} {2}
do_test pragma-4.7 {
  db close
  sqlite db test.db
  execsql {
    PRAGMA temp_store;
  }
} {0}
do_test pragma-4.8 {
  execsql {
    PRAGMA default_temp_store;

do_test pragma-4.10 {
  execsql {
    PRAGMA temp_store;
  }
} {0}
do_test pragma-4.11 {
  db close
  sqlite db test.db
  execsql {
    PRAGMA temp_store;
  }
} {1}
do_test pragma-4.12 {
  execsql {
    PRAGMA default_temp_store;

do_test pragma-4.14 {
  execsql {
    PRAGMA temp_store;
  }
} {1}
do_test pragma-4.15 {
  db close
  sqlite db test.db
  execsql {
    PRAGMA temp_store;
  }
} {2}
do_test pragma-4.16 {
  execsql {
    PRAGMA default_temp_store;

do_test pragma-4.18 {
  execsql {
    PRAGMA default_temp_store
  }
} {2}
do_test pragma-4.19 {
  db close
  sqlite db test.db
  execsql {
    PRAGMA temp_store
  }
} {2}

# Changing the TEMP_STORE deletes any existing temporary tables
#

# 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 runs all tests.
#
# $Id: quick.test,v 1.23 2004/06/15 11:40:10 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {}
set ISQUICK 1

set EXCLUDE {
  all.test
  quick.test
  btree2.test
  malloc.test
  memleak.test
  misuse.test
  format3.test
}

if {[sqlite -has-codec]} {
  lappend EXCLUDE \
    attach.test \
    attach2.test \
    auth.test \
    format3.test \
    version.test
}

#    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 magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.14 2004/05/13 11:34:17 danielk1977 Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {

do_test rowid-3.4 {
  set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
  foreach {a b c d e f} $r1 {}
  set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
  foreach {u v w x y z} $r2 {}
  expr {$u==$e && $w==$c && $y==$a}
} {1}
# sqlite v3 - do_probtest doesn't exist anymore?
if 0 {
do_probtest rowid-3.5 {
  set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
  foreach {a b c d e f} $r1 {}
  expr {$a!=$b && $c!=$d && $e!=$f}
} {1}
}

# 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.26 2004/06/12 09:25:30 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 {

} {test1 test1 table}

# Close and reopen the database.  Verify that everything is
# still the same.
#
do_test table-1.4 {
  db close
  sqlite db test.db
  execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
} {test1 test1 table}

# Drop the database and make sure it disappears.
#
do_test table-1.5 {
  execsql {DROP TABLE test1}
  execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
} {}

# Close and reopen the database.  Verify that the table is
# still gone.
#
do_test table-1.6 {
  db close
  sqlite db test.db
  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
} {}

# Repeat the above steps, but this time quote the table name.
#
do_test table-1.10 {
  execsql {CREATE TABLE "create" (f1 int)}

} {1 {table test2 already exists}}
do_test table-2.1b {
  set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
  lappend v $msg
} {1 {object name reserved for internal use: sqlite_master}}
do_test table-2.1c {
  db close
  sqlite db test.db
  set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
  lappend v $msg
} {1 {object name reserved for internal use: sqlite_master}}
do_test table-2.1d {
  execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
} {}

# Verify that we cannot make a table with the same name as an index
#
do_test table-2.2a {
  execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)}
  set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
  lappend v $msg
} {1 {there is already an index named test3}}
do_test table-2.2b {
  db close
  sqlite db test.db
  set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
  lappend v $msg
} {1 {there is already an index named test3}}
do_test table-2.2c {
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} {test2 test3}
do_test table-2.2d {

} {1 {table biG already exists}}
do_test table-3.4 {
  set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
  lappend v $msg
} {1 {table bIg already exists}}
do_test table-3.5 {
  db close
  sqlite db test.db
  set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
  lappend v $msg
} {1 {table Big already exists}}
do_test table-3.6 {
  execsql {DROP TABLE big}
  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
} {}

    append sql "last_field text)"
    execsql $sql
  }
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} $r
do_test table-4.1b {
  db close
  sqlite db test.db
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} $r

# Drop the even numbered tables
#
set r {}
for {set i 1} {$i<=100} {incr i 2} {

  execsql2 {
    CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3'xyz];
    SELECT * FROM t5;
  }
} {y'all 1}
do_test table-8.5 {
  db close
  sqlite db test.db
  execsql2 {
    SELECT * FROM [t4'abc];
  }
} {cnt 1 max(b+c) 5}
do_test table-8.6 {
  execsql2 {
    SELECT * FROM t2;

# This file implements regression tests for TCL interface to the
# SQLite library. 
#
# Actually, all tests are based on the TCL interface, so the main
# interface is pretty well tested.  This file contains some addition
# tests for fringe issues that the main test suite does not cover.
#
# $Id: tclsqlite.test,v 1.23 2004/06/10 10:51:53 danielk1977 Exp $

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

# Check the error messages generated by tclsqlite
#
if {[sqlite -has-codec]} {
  set r "sqlite_orig HANDLE FILENAME ?-key CODEC-KEY?"
} else {
  set r "sqlite HANDLE FILENAME ?MODE?"
}
do_test tcl-1.1 {
  set v [catch {sqlite bogus} msg]
  lappend v $msg
} [list 1 "wrong # args: should be \"$r\""]
do_test tcl-1.2 {
  set v [catch {db bogus} msg]
  lappend v $msg
} {1 {bad option "bogus": must be authorizer, busy, changes, close, commit_hook, complete, errorcode, eval, function, last_insert_rowid, last_statement_changes, onecolumn, progress, rekey, timeout, trace, collate, or collation_needed}}
do_test tcl-1.3 {

      expr x*
    }
  } msg]
  regsub {:.*$} $msg {} msg
  lappend v $msg
} {1 {syntax error in expression "x*"}}

if {[sqlite -tcl-uses-utf]} {
  do_test tcl-2.1 {
    execsql "CREATE TABLE t\u0123x(a int, b\u1235 float)"
    execsql "PRAGMA table_info(t\u0123x)"
  } "0 a int 0 {} 0 1 b\u1235 float 0 {} 0"
  do_test tcl-2.2 {
    execsql "INSERT INTO t\u0123x VALUES(1,2.3)"
    db eval "SELECT * FROM t\u0123x" result break

#    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.13 2004/06/14 08:26:37 danielk1977 Exp $

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

# Create an alternative connection to the database
#
do_test temptable-1.0 {
  sqlite db2 ./test.db
  set dummy {}
} {}

# Create a permanent table.
#
do_test temptable-1.1 {
  execsql {CREATE TABLE t1(a,b,c);}

  catchsql {
    DROP TABLE t2;     -- should drop TEMP
    SELECT * FROM t2;  -- data should be from MAIN
  } db2
} {0 {9 8 7}}
do_test temptable-4.6 {
  db2 close
  sqlite db2 ./test.db
  catchsql {
    SELECT * FROM t2;
  } db2
} {0 {9 8 7}}
do_test temptable-4.7 {
  catchsql {
    DROP TABLE t2;
    SELECT * FROM t2;
  }
} {1 {no such table: t2}}
do_test temptable-4.8 {
  db2 close
  sqlite db2 ./test.db
  execsql {
    CREATE TEMP TABLE t2(x unique,y);
    INSERT INTO t2 VALUES(1,2);
    SELECT * FROM t2;
  } db2
} {1 2}
do_test temptable-4.9 {

do_test temptable-4.14 {
  execsql {
    SELECT * FROM t2;
  }
} {3 4}
do_test temptable-4.15 {
  db2 close
  sqlite db2 ./test.db
  execsql {
    SELECT * FROM t2;
  } db2
} {3 4}

# Now create a temporary table in db2 and a permanent index in db.  The
# temporary table in db2 should mask the name of the permanent index,

    SELECT * FROM t8;
  }
} {xyzzy}
do_test temptable-6.2 {
  db close
  catch {file attributes test.db -permissions 0444}
  catch {file attributes test.db -readonly 1}
  sqlite db test.db
  if {[file writable test.db]} {
    error "Unable to make the database file test.db readonly - rerun this test as an unprivileged user"
  }
  execsql {
    SELECT * FROM t8;
  }
} {xyzzy}

do_test temptable-6.7 {
  catchsql {
    SELECT * FROM t8,t9;
  }
} {0 {xyzzy 1 2}}
do_test temptable-6.8 {
  db close
  sqlite db test.db
  catchsql {
    SELECT * FROM t8,t9;
  }
} {1 {no such table: t9}}

finish_test

# 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 some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.36 2004/06/15 02:44:20 danielk1977 Exp $

# Make sure tclsqlite was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
    puts stderr "$argv0 was linked against an older version"
    puts stderr "of TCL that does not support Unicode, but uses a header"
    puts stderr "file (\"tcl.h\") from a new TCL version that does support"
    puts stderr "Unicode.  This combination causes internal errors."
    puts stderr "Recompile using a TCL library and header file that match"

    puts stderr "and try again.\n**************************"
    exit 1
  }
}

# Use the pager codec if it is available
#
if {[sqlite -has-codec] && [info command sqlite_orig]==""} {
  rename sqlite sqlite_orig
  proc sqlite {args} {
    if {[llength $args]==2 && [string index [lindex $args 0] 0]!="-"} {
      lappend args -key {xyzzy}
    }
    uplevel 1 sqlite_orig $args
  }
}


# Create a test database
#
catch {db close}
file delete -force test.db
file delete -force test.db-journal
sqlite db ./test.db
if {[info exists ::SETUP_SQL]} {
  db eval $::SETUP_SQL
}

# Abort early if this script has been run before.
#
if {[info exists nTest]} return

# 2003 December 18
#
# 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 multithreading behavior
#
# $Id: thread1.test,v 1.6 2004/06/14 08:26:37 danielk1977 Exp $


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

# Skip this whole file if the thread testing code is not enabled
#
if {[llength [info command thread_step]]==0 || [sqlite -has-codec]} {
  finish_test
  return
}

# Create some data to work with
#
do_test thread1-1.1 {

# 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 script is database locks.
#
# $Id: trans.test,v 1.22 2004/06/10 01:08:06 drh Exp $


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


# Create several tables to work with.

    INSERT INTO two VALUES(1,'I');
    INSERT INTO two VALUES(5,'V');
    INSERT INTO two VALUES(10,'X');
    SELECT b FROM two ORDER BY a;
  }
} {I V X}
do_test trans-1.9 {
  sqlite altdb test.db
  execsql {SELECT b FROM one ORDER BY a} altdb
} {one two three}
do_test trans-1.10 {
  execsql {SELECT b FROM two ORDER BY a} altdb
} {I V X}
integrity_check trans-1.11

# Arrange for another process to begin modifying the database but abort
# and die in the middle of the modification.  Then have this process read
# the database.  This process should detect the journal file and roll it
# back.  Verify that this happens correctly.
#
set fd [open test.tcl w]
puts $fd {
  sqlite db test.db
  db eval {
    PRAGMA default_cache_size=20;
    BEGIN;
    CREATE TABLE t3 AS SELECT * FROM t2;
    DELETE FROM t2;
  }
  sqlite_abort

# Verify that after the rollback, the MD5 checksum is unchanged.
#
do_test trans-9.1 {
  execsql {
    PRAGMA default_cache_size=10;
  }
  db close
  sqlite db test.db
  execsql {
    BEGIN;
    CREATE TABLE t3(x TEXT);
    INSERT INTO t3 VALUES(randstr(10,400));
    INSERT INTO t3 VALUES(randstr(10,400));
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;

  catchsql {
    INSERT INTO t1 VALUES(1,2);
    SELECT * FROM t2;
  }
} {1 {no such table: main.t2}}
do_test trigger-3.3 {
  db close
  set rc [catch {sqlite db test.db} err]
  if {$rc} {lappend rc $err}
  set rc
} {0}
do_test trigger-3.4 {
  catchsql {
    INSERT INTO t1 VALUES(1,2);
    SELECT * FROM t2;

  execsql {
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  }
} {1 2 3 4 3 4}
do_test trigger-3.9 {
  db close
  sqlite db test.db
  execsql {
    INSERT INTO t1 VALUES(5,6);
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  }
} {1 2 3 4 5 6 3 4}

do_test trigger-4.1 {
  execsql {
    CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
      INSERT INTO t2 VALUES(NEW.a,NEW.b);
    END;
    INSERT INTO t1 VALUES(7,8);
    SELECT * FROM t2;
  }
} {3 4 7 8}
do_test trigger-4.2 {
  sqlite db2 test.db
  execsql {
    INSERT INTO t1 VALUES(9,10);
  } db2;
  db2 close
  execsql {
    SELECT * FROM t2;
  }
} {3 4 7 8}
do_test trigger-4.3 {
  execsql {
    DROP TABLE t1;
    SELECT * FROM t2;
  };
} {3 4 7 8}
do_test trigger-4.4 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM t2;
  };
} {3 4 7 8}

integrity_check trigger-5.1

  catchsql {DELETE FROM t2}
} {1 {deletes are not allows}}
do_test trigger-6.4 {
  execsql {SELECT * FROM t2}
} {3 4 7 8}
do_test trigger-6.5 {
  db close
  sqlite db test.db
  execsql {SELECT type, name FROM sqlite_master}
} {view v1 table t2 trigger t2}
do_test trigger-6.6 {
  execsql {
    DROP TRIGGER t2;
    SELECT type, name FROM sqlite_master;
  }
} {view v1 table t2}
do_test trigger-6.7 {
  execsql {SELECT * FROM t2}
} {3 4 7 8}
do_test trigger-6.8 {
  db close
  sqlite db test.db
  execsql {SELECT * FROM t2}
} {3 4 7 8}

integrity_check trigger-7.1

# Check to make sure the name of a trigger can be quoted so that keywords
# can be used as trigger names.  Ticket #468

do_test trigger4-1.2 {
  execsql {
    select * from test2;
  }
} {1 3}
do_test trigger4-1.3 {
  db close
  sqlite db test.db
  execsql {
    insert into test values(4,5,6);
    select * from test1;
  }
} {1 2 4 5}
do_test trigger4-1.4 {
  execsql {

do_test trigger4-2.2 {
  execsql {
    select * from test2;
  }
} {1 3 4 6}
do_test trigger4-2.3 {
  db close
  sqlite db test.db
  execsql {
    update test set b=66 where id=4;
    select * from test1;
  }
} {1 22 4 5}
do_test trigger4-2.4 {
  execsql {
    select * from test2;
  }
} {1 3 4 66}

do_test trigger4-3.1 {
  catchsql {
    drop table test2;
    insert into test values(7,8,9);
  }
} {1 {no such table: main.test2}}
do_test trigger4-3.2 {
  db close
  sqlite db test.db
  catchsql {
    insert into test values(7,8,9);
  }
} {1 {no such table: main.test2}}
do_test trigger4-3.3 {
  catchsql {
    update test set a=222 where id=1;

do_test trigger4-3.6 {
  execsql {
    select * from test2;
  }
} {7 9}
do_test trigger4-3.7 {
  db close
  sqlite db test.db
  execsql {
    update test set b=99 where id=7;
    select * from test2;
  }
} {7 99}

integrity_check trigger4-4.1

finish_test

# 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 VACUUM statement.
#
# $Id: vacuum.test,v 1.20 2004/05/29 10:43:07 danielk1977 Exp $

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

proc cksum {{db db}} {
  set sql "SELECT name, type, sql FROM sqlite_master ORDER BY name, type"
  set txt [$db eval $sql]\n

    BEGIN;
    VACUUM;
    COMMIT;
  }
} {1 {cannot VACUUM from within a transaction}}
catch {db eval COMMIT}
do_test vacuum-2.2 {
  sqlite db2 test.db
  execsql {
    BEGIN;
    CREATE TABLE t4 AS SELECT * FROM t1;
    CREATE TABLE t5 AS SELECT * FROM t1;
    COMMIT;
    DROP TABLE t4;
    DROP TABLE t5;

# Ticket #427.  Make sure VACUUM works when the EMPTY_RESULT_CALLBACKS
# pragma is turned on.
#
do_test vacuum-3.1 {
  db close
  db2 close
  file delete test.db
  sqlite db test.db
  execsql {
    PRAGMA empty_result_callbacks=on;
    VACUUM;
  }
} {}

# Ticket #464.  Make sure VACUUM works with the sqlite3_prepare() API.
#
do_test vacuum-4.1 {
  db close
  set DB [sqlite db test.db]
  set VM [sqlite3_prepare $DB {VACUUM} -1 TAIL]
  sqlite3_step $VM
} {SQLITE_DONE}
do_test vacuum-4.2 {
  sqlite3_finalize $VM
} SQLITE_OK

# Ticket #515.  VACUUM after deleting and recreating the table that
# a view refers to.
#
do_test vacuum-5.1 {
  db close
  file delete -force test.db
  sqlite db test.db
  catchsql {
    CREATE TABLE Test (TestID int primary key);
    INSERT INTO Test VALUES (NULL);
    CREATE VIEW viewTest AS SELECT * FROM Test;

    BEGIN;
    CREATE TEMP TABLE tempTest (TestID int primary key, Test2 int NULL);

# 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.16 2003/05/31 16:21:13 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);

  execsql {
    CREATE VIEW v1 AS SELECT a,b FROM t1;
    SELECT * FROM v1 ORDER BY a;
  }
} {1 2 4 5 7 8}
do_test view-1.3.1 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM v1 ORDER BY a;
  }
} {1 2 4 5 7 8}
do_test view-1.4 {
  catchsql {
    DROP VIEW v1;

    INSERT INTO t1 VALUES(4,5,6,7);
    INSERT INTO t1 VALUES(7,8,9,10);
    SELECT * FROM v1 ORDER BY a;
  }
} {2 3 5 6 8 9}
do_test view-1.8 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM v1 ORDER BY a;
  }
} {2 3 5 6 8 9}

do_test view-2.1 {
  execsql {

      SELECT test1.id, a, b
      FROM test1 JOIN test2 ON test2.id=test1.id;
    SELECT * FROM test;
  }
} {1 2 3}
do_test view-7.2 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM test;
  }
} {1 2 3}
do_test view-7.3 {
  execsql {
    DROP VIEW test;
    CREATE VIEW test AS
      SELECT test1.id, a, b
      FROM test1 JOIN test2 USING(id);
    SELECT * FROM test;
  }
} {1 2 3}
do_test view-7.4 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM test;
  }
} {1 2 3}
do_test view-7.5 {
  execsql {
    DROP VIEW test;
    CREATE VIEW test AS
      SELECT test1.id, a, b
      FROM test1 NATURAL JOIN test2;
    SELECT * FROM test;
  }
} {1 2 3}
do_test view-7.6 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM test;
  }
} {1 2 3}

do_test view-8.1 {
  execsql {
    CREATE VIEW v6 AS SELECT pqr, xyz FROM v1;
    SELECT * FROM v6 ORDER BY xyz;
  }
} {7 2 13 5 19 8 27 12}
do_test view-8.2 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM v6 ORDER BY xyz;
  }
} {7 2 13 5 19 8 27 12}
do_test view-8.3 {
  execsql {
    CREATE VIEW v7 AS SELECT pqr+xyz AS a FROM v6;