SQLite

#
#    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: async.test,v 1.20 2009/04/25 08:39:15 danielk1977 Exp $

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

if {[info commands sqlite3async_initialize] eq ""} {
  # The async logic is not built into this system
  finish_test

rename do_test {}
rename async_really_do_test do_test
rename finish_test {}
rename async_really_finish_test finish_test

if {[info exists ASYNC_SAVE_ISQUICK]} { set ISQUICK $ASYNC_SAVE_ISQUICK }
finish_test

# 2009 April 25
#
# 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: async4.test,v 1.3 2009/06/01 16:42:18 shane Exp $

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

# These tests only work for Tcl version 8.5 and later on Windows (for now)
#
if {$tcl_platform(platform)=="windows"} {

do_test async4.2.8 {
  db close
  sqlite3async_start
  sqlite3async_wait
} {}

finish_test

# 2009 January 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the sqlite3_backup_XXX API.
#
# $Id: backup.test,v 1.10 2009/05/13 07:52:08 danielk1977 Exp $

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

#---------------------------------------------------------------------
# Test organization:
#

  } db2
  sqlite3_backup B db aux1 db2 aux2
} {B}
do_test backup-4.2.2 {
  catchsql { DETACH aux2 } db2
} {1 {database aux2 is locked}}
do_test backup-4.2.3 {
breakpoint
  B step 50
} {SQLITE_DONE}
do_test backup-4.2.4 {
  B finish
} {SQLITE_OK}

do_test backup-4.3.1 {

  } {ok}

  db2 close
  db3 close
}

finish_test

# 2005 November 2
#
# 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 CHECK constraints
#
# $Id: check.test,v 1.12 2009/03/24 15:08:10 drh Exp $

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

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test

do_test check-1.17 {
  execsql {
    SELECT * FROM t1;
  }
} {4 11.0}

do_test check-2.1 {
breakpoint
  execsql {
    CREATE TABLE t2(
      x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ),
      y REAL CHECK( typeof(coalesce(y,0.1))=='real' ),
      z TEXT CHECK( typeof(coalesce(z,''))=='text' )
    );
  }

# when there exists a page that is both an a descendent or ancestor of
# itself.
#
# Also test that an SQLITE_CORRUPT error is returned if a B-Tree page
# contains a (corrupt) reference to a page greater than the configured
# maximum page number.
#
# $Id: corruptB.test,v 1.2 2008/09/10 17:53:36 danielk1977 Exp $

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


do_test corruptB-1.1 {
  execsql {

} {2}
do_test corruptB-3.1.2 {
  sqlite3 db test.db
  catchsql { SELECT * FROM t2 }
} {1 {database disk image is malformed}}

finish_test

# 2009 June 3
#
# 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: corruptD.test,v 1.1 2009/06/03 17:26:20 danielk1977 Exp $

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

#--------------------------------------------------------------------------
# OVERVIEW
#

# The following tests, corruptD-1.5.*, focus on the offsets contained
# in the cell offset array.
# 
#   defragmentPage
#

finish_test

# 2009 February 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 "SELECT count(*)" statements.
#
# $Id: count.test,v 1.5 2009/05/01 21:13:37 drh Exp $

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

# Test plan:
#
#  count-0.*: Make sure count(*) works on an empty database.  (Ticket #3774)

  do_test count-2.14 {
    execsql { CREATE VIRTUAL TABLE techo USING echo(t1); }
    uses_op_count {SELECT count(*) FROM techo}
  } {0}
}

do_test count-3.1 {
breakpoint
  execsql {
    CREATE TABLE t3(a, b);
    SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==0;
  }
} {0}
do_test count-3.2 {
  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 the DELETE FROM statement.
#
# $Id: delete.test,v 1.25 2009/04/07 09:16:57 danielk1977 Exp $

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

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

    CREATE INDEX i5 ON t5(a);
    CREATE INDEX i6 ON t6(c);
  }
} {}
do_test delete-9.2 {
  set res [list]
  db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
    if {$r==2} { breakpoint ; db eval { DELETE FROM t5 } }
    lappend res $r $c $d
  }
  set res
} {1 a b 1 c d 2 a b {} c d}
do_test delete-9.3 {
  execsql {
    INSERT INTO t5 VALUES(1, 2);
    INSERT INTO t5 VALUES(3, 4);
    INSERT INTO t5 VALUES(5, 6);
  }
  set res [list]
  db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
    if {$r==2} { breakpoint ; db eval { DELETE FROM t5 WHERE rowid = 2 } }
    lappend res $r $c $d
  }
  set res
} {1 a b 1 c d 2 a b {} c d 3 a b 3 c d}
do_test delete-9.4 {
  execsql {
    DELETE FROM t5;
    INSERT INTO t5 VALUES(1, 2);
    INSERT INTO t5 VALUES(3, 4);
    INSERT INTO t5 VALUES(5, 6);
  }
  set res [list]
  db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
    if {$r==2} { breakpoint ; db eval { DELETE FROM t5 WHERE rowid = 1 } }
    lappend res $r $c $d
  }
  set res
} {1 a b 1 c d 2 a b 2 c d 3 a b 3 c d}
do_test delete-9.5 {
  execsql {
    DELETE FROM t5;
    INSERT INTO t5 VALUES(1, 2);
    INSERT INTO t5 VALUES(3, 4);
    INSERT INTO t5 VALUES(5, 6);
  }
  set res [list]
  db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
    if {$r==2} { breakpoint ; db eval { DELETE FROM t5 WHERE rowid = 3 } }
    lappend res $r $c $d
  }
  set res
} {1 a b 1 c d 2 a b 2 c d}


finish_test

# 2009 January 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.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the FTS3 module syntax parser.
#
# $Id: fts3expr2.test,v 1.1 2009/01/01 07:08:55 danielk1977 Exp $
#

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

# If SQLITE_ENABLE_FTS3 is defined, omit this file.
ifcapable !fts3 {

  do_exprparse_test fts3expr2-$iTest.2 $e2 $t
  do_exprparse_test fts3expr2-$iTest.3 $e3 $t
  do_exprparse_test fts3expr2-$iTest.4 $e4 $t
}

set sqlite_fts3_enable_parentheses 0
finish_test

# 2008 September 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: in4.test,v 1.3 2009/01/24 09:56:15 danielk1977 Exp $

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

do_test in4-1.1 {
  execsql {
    CREATE TABLE t1(a, b);

  execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}
} {1 1 1}
do_test in4-3.12 {
  execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()}
} {}

finish_test

# 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: ioerr2.test,v 1.11 2009/02/12 09:11:56 danielk1977 Exp $

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

ifcapable !integrityck {
  finish_test
  return

  UPDATE ab SET b = randstr(200,200);
  UPDATE de SET e = randstr(200,200) WHERE d = (SELECT max(d) FROM de);
  DELETE FROM ab;
  COMMIT;
}

finish_test

# 2008 April 17
#
# 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 these tests is the journal mode pragma.
#
# $Id: jrnlmode.test,v 1.15 2009/04/20 17:43:03 drh Exp $

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

ifcapable {!pager_pragmas} {
  finish_test
  return

      PRAGMA auto_vacuum = 1;
      CREATE TABLE abc(a, b, c);
    }
    execsql { PRAGMA page_count }
  } {3}

  do_test jrnlmode-4.2 {
breakpoint
    execsql { PRAGMA journal_mode = off }
  } {off}

  do_test jrnlmode-4.3 {
    execsql { INSERT INTO abc VALUES(1, 2, randomblob(2000)) }
  } {}

# 2009 March 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.
#
#***********************************************************************
#
# $Id: jrnlmode2.test,v 1.5 2009/03/26 17:13:06 danielk1977 Exp $

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

ifcapable {!pager_pragmas} {
  finish_test
  return

do_test jrnlmode2-2.6 {
  sqlite3 db2 test.db -readonly 1
  catchsql { SELECT * FROM t1 } db2
} {0 {1 2 3 4 5 6}}

catch { db2 close }
finish_test

# 2009 March 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 sqlite3_unlock_notify() API.
#
# $Id: notify1.test,v 1.3 2009/04/07 22:06:57 drh Exp $

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

ifcapable !unlock_notify||!shared_cache {
  finish_test
  return

#-------------------------------------------------------------------------
# These tests, notify1-5.*, test that a malloc() failure that occurs while
# allocating an array to use as an argument to an unlock-notify callback
# is handled correctly.
# 
source $testdir/malloc_common.tcl
breakpoint
do_malloc_test notify1-5 -tclprep {
  set ::lUnlock [list]
  execsql {
    CREATE TABLE t1(a, b);
    BEGIN;
    INSERT INTO t1 VALUES('a', 'b');
  }

# 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.34 2009/01/07 15:18:21 danielk1977 Exp $


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

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

} {Page-One}
do_test pager-3.5 {
  for {set i 2} {$i<=20} {incr i} {
    set gx [page_get $::p1 $i]
    page_write $gx "Page-$i"
    page_unref $gx
  }
breakpoint
  pager_commit $::p1
  page_unref $::g(1)
} {}
for {set i 2} {$i<=20} {incr i} {
  do_test pager-3.6.[expr {$i-1}] [subst {
    set gx \[page_get $::p1 $i\]
    set v \[page_read \$gx\]

# 2008 December 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.
#
#***********************************************************************
#
# $Id: savepoint2.test,v 1.4 2008/12/22 10:58:46 danielk1977 Exp $

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

# Tests in this file are quite similar to those run by trans.test and
# avtrans.test.
#

  integrity_check savepoint2-$ii.6.1
}

unset -nocomplain ::sig
unset -nocomplain SQL

finish_test

# 2008 December 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.
#
#***********************************************************************
#
# $Id: savepoint3.test,v 1.4 2009/01/09 17:11:05 danielk1977 Exp $

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

source $testdir/malloc_common.tcl

do_malloc_test savepoint3-1 -sqlprep {

      PRAGMA incremental_vacuum;
} -sqlbody {
  ROLLBACK TO abc;
}


finish_test

# 2008 December 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.
#
#***********************************************************************
#
# $Id: savepoint4.test,v 1.5 2009/04/23 18:42:05 shane Exp $

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

ifcapable !crashtest {
  finish_test
  return

      DELETE FROM t1 WHERE random()%10==0;
      INSERT INTO t1 SELECT randstr(10,10)||x FROM t1 WHERE random()%9==0;
    }
  } {}
}

finish_test

# 2009 January 3
#
# 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: savepoint6.test,v 1.3 2009/01/07 17:06:53 danielk1977 Exp $

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

proc sql {zSql} {
  uplevel db eval [list $zSql]
  #puts stderr "$zSql ;"

  }
}

unset -nocomplain ::lSavepoint
unset -nocomplain ::aEntry

finish_test

# 2005 January 19
#
# 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: shared2.test,v 1.7 2009/03/18 18:43:36 danielk1977 Exp $

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

ifcapable !shared_cache {
  finish_test

} {}

db close
db2 close

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test

# 2009 April 01
#
# 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: shared6.test,v 1.3 2009/04/02 18:28:08 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !shared_cache { finish_test ; return }

do_test shared6-1.1.1 {
  execsql {

  
  db1 close
  db2 close
} {}

sqlite3_enable_shared_cache $::enable_shared_cache
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 CREATE TABLE statement.
#
# $Id: table.test,v 1.52 2009/05/11 20:53:29 drh 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 {

do_test table-2.1d {
  catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)}
} {0 {}}
do_test table-2.1e {
  catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)}
} {0 {}}
do_test table-2.1f {
breakpoint
  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)}

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# The focus of this file is in making sure that rolling back
# a statement journal works correctly.
#
# $Id: tempdb.test,v 1.3 2009/05/01 05:23:18 danielk1977 Exp $

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

# Use a temporary database.
#
db close

      INSERT INTO t2 SELECT * FROM t1;
  }
  catchsql { INSERT INTO t1 SELECT * FROM t2 }
  set sqlite_open_file_count
} [expr 1 + (0==$jrnl_in_memory)]

finish_test

# 2009 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.
#
#***********************************************************************
#
# $Id: thread004.test,v 1.2 2009/03/26 14:48:07 danielk1977 Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl
if {[run_thread_tests]==0} { finish_test ; return }
ifcapable !shared_cache {
  finish_test

    if {![info exists finished($t)]} { vwait finished($t) }
  }

  set finished(2)
} {0}
sqlite3_enable_shared_cache $::enable_shared_cache
finish_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 fix for ticket #3357.
#
# $Id: tkt3357.test,v 1.1 2008/09/06 14:19:11 danielk1977 Exp $

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

do_test tkt3357-1.1 {
  execsql {
    create table a(id integer primary key, b_id integer, myvalue varchar);

      FROM a, b WHERE a.b_id = b.id
    ) cc
    LEFT OUTER JOIN b dd ON cc.b_id = dd.id
  }
} {1 1 Test 2 1 Test2 3 1 Test3}

finish_test

# 2008 October 06
#
# 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: tkt3424.test,v 1.1 2008/10/08 17:58:50 danielk1977 Exp $

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

do_test tkt3424-1.1 {
  execsql {
    CREATE TABLE names(id INTEGER, data TEXT, code TEXT);

    SELECT * FROM 
    names LEFT OUTER JOIN orig
    ON names.data = orig.data AND names.code = orig.code;
  }
} {1 E1 AAA AAA E1 2 {} BBB {} {}}

finish_test

#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #3442 has been
# fixed.  
#
#
# $Id: tkt3442.test,v 1.1 2008/10/22 16:04:43 shane Exp $


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


# Create a schema with some indexes.

do_test tkt3442-1.5 {
  catchsql {
    SELECT node FROM listhash WHERE id=[5000] LIMIT 1;
  }
} {1 {no such column: 5000}}

finish_test

# 2008 October 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.
#
# $Id: tkt3457.test,v 1.1 2008/10/29 07:01:57 danielk1977 Exp $

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

if {$tcl_platform(platform) != "unix"} {
  finish_test
  return

do_test tkt3457-1.5 {
  file copy -force bak.db-journal test.db-journal
  file attributes test.db-journal -permissions rw-rw-rw-
  catchsql { SELECT * FROM t1 }
} {0 {1 2 3 4 5 6}}

finish_test

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #3461 has been
# fixed.  
#
# $Id: tkt3461.test,v 1.3 2009/04/23 13:22:44 drh Exp $

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

####################################
####################################
# REMOVE THESE TWO LINES:

do_test tkt3461-3.1 {
  execsql {
    CREATE TABLE t2(c, d);
    INSERT INTO t2 VALUES(3, 4);
  }
  # execsql { PRAGMA vdbe_trace = 1; PRAGMA vdbe_listing=1 }
breakpoint
  execsql { 
    SELECT a, b+1 AS b_plus_one, c, d 
    FROM t1 LEFT JOIN t2 
    ON (a=c AND d=b_plus_one)
  }
} {1 3 {} {}}

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specifically,
# it tests that affinities and collation sequences are correctly applied
# in aggregate queries.
#
# $Id: tkt3493.test,v 1.1 2008/11/12 08:07:12 danielk1977 Exp $

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

do_test tkt3493-1.1 {
  execsql {
    BEGIN;

  execsql { SELECT b>a FROM t2 GROUP BY a, b}
} {0}
do_test tkt3493-3.3.4 {
  execsql { SELECT b>a COLLATE NOCASE FROM t2 GROUP BY a, b}
} {1}

finish_test

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket #3554 has been
# fixed.  
#
# $Id: tkt3554.test,v 1.1 2008/12/26 07:56:39 danielk1977 Exp $

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

ifcapable !trigger {
  finish_test
  return

  execsql {
    INSERT INTO test VALUES('a', 10000, 12000);
  }
  execsql { SELECT * FROM test }
} {a 9000 12000}

finish_test

#***********************************************************************
# This file implements regression tests for SQLite library.
#
# When a transaction rolls back, make sure that dirty pages in the
# page cache which are not in the rollback journal are reinitialized
# in the btree layer.
#
# $Id: tkt35xx.test,v 1.3 2008/11/21 09:09:02 danielk1977 Exp $

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

do_test tkt35xx-1.1 {
  execsql {
    PRAGMA auto_vacuum = 0;

} {}
do_test tkt35xx-1.2.6 {
  execsql { SELECT count(*) FROM t3 }
} {5}
integrity_check tkt35xx-1.2.7

finish_test

#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the execution of SQL statements from
# within callbacks generated by VMs that themselves open statement 
# transactions.
#
# $Id: tkt3718.test,v 1.1 2009/03/18 10:33:02 danielk1977 Exp $

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

do_test tkt3718-1.1 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);

  } {1}

  integrity_check tkt3718-4.$tn.integrity
}


finish_test

  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 1 \
  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 2 \
  xFilter {SELECT rowid, * FROM 't1' WHERE b = ?} 9
]


finish_test

# 2009 May 25
#
# 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.
#
#***********************************************************************
#
# Tests to verify ticket #3879 is fixed.
#
# $Id: tkt3879.test,v 1.1 2009/05/25 11:46:11 danielk1977 Exp $

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

do_test tkt3879.1.1 {
  execsql {
    CREATE TABLE t1 (a PRIMARY KEY, b);

    FROM t3, t2 AS j0, t2 AS j1, t1
    WHERE j0.m=t3.m AND t1.a=j0.a AND j1.n=j0.m
    ORDER BY t1.b;
  }
} {222 123 222 123}

finish_test

# 2009 April 14
#
# 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: vtabD.test,v 1.2 2009/04/23 18:42:05 shane Exp $

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

ifcapable !vtab||!schema_pragmas { finish_test ; return }

# Register the echo module

if {[working_64bit_int]} {
do_test vtabD-1.8 {
  execsql { SELECT * FROM tv1 WHERE a = 90001 OR b = 810000 }
} {90001 8100180001 900 810000}
}

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# is testing of where.c. More specifically, the focus is the optimization
# of WHERE clauses that feature the OR operator.
#
# $Id: where8.test,v 1.6 2009/04/21 09:02:47 danielk1977 Exp $

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

# Test organization:
#
#   where8-1.*: Tests to demonstrate simple cases work with a single table

}

catch {unset results}
catch {unset A}
catch {unset B}

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# is testing of where.c. More specifically, the focus is the optimization
# of WHERE clauses that feature the OR operator.
#
# $Id: where8m.test,v 1.2 2009/04/21 09:02:47 danielk1977 Exp $

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

source $testdir/malloc_common.tcl

do_malloc_test where8m-1 -sqlprep {

  }
  db eval COMMIT
} -sqlbody {
  SELECT count(*) FROM t1 WHERE a BETWEEN 5 AND 995 OR b BETWEEN 5 AND 900000;
}

finish_test

# 2008 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#
# $Id: where9.test,v 1.8 2009/04/21 09:02:47 danielk1977 Exp $

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

ifcapable !or_opt {
  finish_test
  return

     WHERE b IS NULL
        OR c IS NULL
        OR +d IS NULL
    ORDER BY a
  }
} {90 91 92 96 97 99 scan 98 sort 0}
do_test where9-1.2.5 {
breakpoint
  count_steps {
    SELECT a FROM t4
     WHERE b IS NULL
        OR c IS NULL
        OR d IS NULL
    ORDER BY a
  }