SQLite

# 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.31 2006/02/10 13:33:31 danielk1977 Exp $
#

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

# Return the text values from the current row pointed at by STMT as a list.
proc get_row_values {STMT} {

} {}
do_test capi2-11.2 {
  check_origins {SELECT col2, col1 FROM tab1}
} [list {main tab1 col2} {main tab1 col1}]
do_test capi2-11.3 {
  check_origins {SELECT col2 AS hello, col1 AS world FROM tab1}
} [list {main tab1 col2} {main tab1 col1}]

ifcapable subquery {
  do_test capi2-11.4 {
    check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM tab1)}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-11.5 {
    check_origins {SELECT (SELECT col2 FROM tab1), (SELECT col1 FROM tab1)}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-11.6 {
    check_origins {SELECT (SELECT col2), (SELECT col1) FROM tab1}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-11.7 {
    check_origins {SELECT * FROM tab1}
  } [list {main tab1 col1} {main tab1 col2}]
  do_test capi2-11.8 {
    check_origins {SELECT * FROM (SELECT * FROM tab1)}
  } [list {main tab1 col1} {main tab1 col2}]
}

ifcapable view&&subquery {
  do_test capi2-12.1 {
    execsql {
      CREATE VIEW view1 AS SELECT * FROM  tab1;
    }
  } {}
  do_test capi2-12.2 {
    check_origins {SELECT col2, col1 FROM view1}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-12.3 {
    check_origins {SELECT col2 AS hello, col1 AS world FROM view1}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-12.4 {
    check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view1)}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-12.5 {
    check_origins {SELECT (SELECT col2 FROM view1), (SELECT col1 FROM view1)}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-12.6 {
    check_origins {SELECT (SELECT col2), (SELECT col1) FROM view1}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-12.7 {
    check_origins {SELECT * FROM view1}
  } [list {main tab1 col1} {main tab1 col2}]
  do_test capi2-12.8 {
    check_origins {select * from (select * from view1)}
  } [list {main tab1 col1} {main tab1 col2}]
  do_test capi2-12.9 {
    check_origins {select * from (select * from (select * from view1))}
  } [list {main tab1 col1} {main tab1 col2}]
  do_test capi2-12.10 {
    db close
    sqlite3 db test.db
    set ::DB [sqlite3_connection_pointer db]
    check_origins {select * from (select * from (select * from view1))}
  } [list {main tab1 col1} {main tab1 col2}]
  
  # This view will thwart the flattening optimization.
  do_test capi2-13.1 {
    execsql {
      CREATE VIEW view2 AS SELECT * FROM tab1 limit 10 offset 10;
    }
  } {}
  breakpoint
  do_test capi2-13.2 {
    check_origins {SELECT col2, col1 FROM view2}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-13.3 {
    check_origins {SELECT col2 AS hello, col1 AS world FROM view2}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-13.4 {
    check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view2)}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-13.5 {
    check_origins {SELECT (SELECT col2 FROM view2), (SELECT col1 FROM view2)}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-13.6 {
    check_origins {SELECT (SELECT col2), (SELECT col1) FROM view2}
  } [list {main tab1 col2} {main tab1 col1}]
  do_test capi2-13.7 {
    check_origins {SELECT * FROM view2}
  } [list {main tab1 col1} {main tab1 col2}]
  do_test capi2-13.8 {
    check_origins {select * from (select * from view2)}
  } [list {main tab1 col1} {main tab1 col2}]
  do_test capi2-13.9 {
    check_origins {select * from (select * from (select * from view2))}
  } [list {main tab1 col1} {main tab1 col2}]
  do_test capi2-13.10 {
    db close
    sqlite3 db test.db
    set ::DB [sqlite3_connection_pointer db]
    check_origins {select * from (select * from (select * from view2))}
  } [list {main tab1 col1} {main tab1 col2}]
  do_test capi2-13.11 {
    check_origins {select * from (select * from tab1 limit 10 offset 10)}
  } [list {main tab1 col1} {main tab1 col2}]
}


} ;# ifcapable columnmetadata

db2 close
finish_test

#    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 the sqlite3_table_column_metadata() API.
#
# $Id: colmeta.test,v 1.3 2006/02/10 13:33:31 danielk1977 Exp $

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

ifcapable !columnmetadata {
  finish_test
  return
}

# Set up a schema in the main and temp test databases.
do_test colmeta-0 {
  execsql {
    CREATE TABLE abc(a, b, c);
    CREATE TABLE abc2(a PRIMARY KEY COLLATE NOCASE, b VARCHAR(32), c);
    CREATE TABLE abc3(a NOT NULL, b INTEGER PRIMARY KEY, c);
  }
  ifcapable autoinc {
    execsql {
      CREATE TABLE abc4(a, b INTEGER PRIMARY KEY AUTOINCREMENT, c);
    }
  }
  ifcapable view {
    execsql {
      CREATE VIEW v1 AS SELECT * FROM abc2;
    }
  }
} {}


# Return values are of the form:
#
#   {<decl-type> <collation> <not null> <primary key> <auto increment>}
#
set tests {
  1  {main abc a}                {0 {{} BINARY 0 0 0}}
  2  {{} abc a}                  {0 {{} BINARY 0 0 0}}
  3  {{} abc2 b}                 {0 {VARCHAR(32) BINARY 0 0 0}}
  4  {main abc2 b}               {0 {VARCHAR(32) BINARY 0 0 0}}
  5  {{} abc2 a}                 {0 {{} NOCASE 0 1 0}}
  6  {{} abc3 a}                 {0 {{} BINARY 1 0 0}}
  7  {{} abc3 b}                 {0 {INTEGER BINARY 0 1 0}}
  13 {main abc rowid}            {0 {INTEGER BINARY 0 1 0}}
  14 {main abc3 rowid}           {0 {INTEGER BINARY 0 1 0}}
  16 {main abc d}                {1 {no such table column: abc.d}}
}
ifcapable view {
  set tests [concat $tests {
    8  {{} abc4 b}                 {0 {INTEGER BINARY 0 1 1}}
    15 {main abc4 rowid}           {0 {INTEGER BINARY 0 1 1}}
  }]
}
ifcapable view {
  set tests [concat $tests {
    9  {{} v1 a}                   {1 {no such table column: v1.a}}
    10 {main v1 b}                 {1 {no such table column: v1.b}}
    11 {main v1 badname}           {1 {no such table column: v1.badname}}
    12 {main v1 rowid}             {1 {no such table column: v1.rowid}}




  }]
}

foreach {tn params results} $tests {
  set ::DB [sqlite3_connection_pointer db]

  set tstbody [concat sqlite3_table_column_metadata $::DB $params] 
  do_test colmeta-$tn.1 {

# 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.26 2006/02/10 13:33:31 danielk1977 Exp $

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


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

# a constraint).
do_ioerr_test ioerr-10 -tclprep {
  execsql {
    BEGIN;
    CREATE TABLE t1(a PRIMARY KEY, b);
  }
  for {set i 0} {$i < 500} {incr i} {
    execsql {INSERT INTO t1 VALUES(:i, 'hello world');}
  }
  execsql {
    COMMIT;
  }
} -tclbody {

  catch {execsql {

# are not expired correctly as a result of changes to TEMP
# views and triggers.
#

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

ifcapable !tempdb||!view {
  finish_test
  return
}  

# Create two tables T1 and T2 and make V1 point to T1.
do_test tkt1644-1.1 {
  execsql {