# 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} {  set VALUES [list]  for {set i 0} {$i < [sqlite3_data_count $STMT]} {incr i} {    lappend VALUES [sqlite3_column_text $STMT $i]  }  return $VALUES}# Return the column names followed by declaration types for the result set# of the SQL statement STMT.## i.e. for:# CREATE TABLE abc(a text, b integer); # SELECT * FROM abc;## The result is {a b text integer}proc get_column_names {STMT} {  set VALUES [list]  for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {    lappend VALUES [sqlite3_column_name $STMT $i]  }  for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {    lappend VALUES [sqlite3_column_decltype $STMT $i]  }  return $VALUES}# Check basic functionality#do_test capi2-1.1 {  set DB [sqlite3_connection_pointer 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}do_test capi2-1.3 {  sqlite3_data_count $VM} {2}do_test capi2-1.4 {  get_row_values $VM} {t1 1}do_test capi2-1.5 {  get_column_names $VM} {name rowid text INTEGER}do_test capi2-1.6 {  sqlite3_step $VM } {SQLITE_DONE}do_test capi2-1.7 {  list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM]} {2 {} {name rowid text INTEGER}}do_test capi2-1.8 {  sqlite3_step $VM} {SQLITE_MISUSE}# Update: In v2, once SQLITE_MISUSE is returned the statement handle cannot# be interrogated for more information. However in v3, since the column# count, names and types are determined at compile time, these are still# accessible after an SQLITE_MISUSE error.do_test capi2-1.9 {  list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM]} {2 {} {name rowid text INTEGER}}do_test capi2-1.10 {  sqlite3_data_count $VM} {0}do_test capi2-1.11 {  sqlite3_finalize $VM} {SQLITE_OK}# Check to make sure that the "tail" of a multi-statement SQL script# is returned by sqlite3_prepare.#do_test capi2-2.1 {  set SQL {    SELECT name, rowid FROM sqlite_master;    SELECT name, rowid FROM sqlite_master WHERE 0;    -- A comment at the end  }  set VM [sqlite3_prepare $DB $SQL -1 SQL]  set SQL} {    SELECT name, rowid FROM sqlite_master WHERE 0;    -- A comment at the end  }do_test capi2-2.2 {  set r [sqlite3_step $VM]  lappend r [sqlite3_column_count $VM] \            [get_row_values $VM] \            [get_column_names $VM]} {SQLITE_ROW 2 {t1 1} {name rowid text INTEGER}}do_test capi2-2.3 {  set r [sqlite3_step $VM]  lappend r [sqlite3_column_count $VM] \            [get_row_values $VM] \            [get_column_names $VM]} {SQLITE_DONE 2 {} {name rowid text INTEGER}}do_test capi2-2.4 {  sqlite3_finalize $VM} {SQLITE_OK}do_test capi2-2.5 {  set VM [sqlite3_prepare $DB $SQL -1 SQL]  set SQL} {    -- A comment at the end  }do_test capi2-2.6 {  set r [sqlite3_step $VM]  lappend r [sqlite3_column_count $VM] \            [get_row_values $VM] \            [get_column_names $VM]} {SQLITE_DONE 2 {} {name rowid text INTEGER}}do_test capi2-2.7 {  sqlite3_finalize $VM} {SQLITE_OK}do_test capi2-2.8 {  set VM [sqlite3_prepare $DB $SQL -1 SQL]  list $SQL $VM} {{} {}}# Check the error handling.#do_test capi2-3.1 {  set rc [catch {      sqlite3_prepare $DB {select bogus from sqlite_master} -1 TAIL  } msg]  lappend rc $msg $TAIL} {1 {(1) no such column: bogus} {}}do_test capi2-3.2 {  set rc [catch {      sqlite3_prepare $DB {select bogus from } -1 TAIL  } msg]  lappend rc $msg $TAIL} {1 {(1) near " ": syntax error} {}}do_test capi2-3.3 {  set rc [catch {      sqlite3_prepare $DB {;;;;select bogus from sqlite_master} -1 TAIL  } msg]  lappend rc $msg $TAIL} {1 {(1) no such column: bogus} {}}do_test capi2-3.4 {  set rc [catch {      sqlite3_prepare $DB {select bogus from sqlite_master;x;} -1 TAIL  } msg]  lappend rc $msg $TAIL} {1 {(1) no such column: bogus} {x;}}do_test capi2-3.5 {  set rc [catch {      sqlite3_prepare $DB {select bogus from sqlite_master;;;x;} -1 TAIL  } msg]  lappend rc $msg $TAIL} {1 {(1) no such column: bogus} {;;x;}}do_test capi2-3.6 {  set rc [catch {      sqlite3_prepare $DB {select 5/0} -1 TAIL  } VM]  lappend rc $TAIL} {0 {}}do_test capi2-3.7 {  list [sqlite3_step $VM] \       [sqlite3_column_count $VM] \       [get_row_values $VM] \       [get_column_names $VM]} {SQLITE_ROW 1 {{}} {5/0 {}}}do_test capi2-3.8 {  sqlite3_finalize $VM} {SQLITE_OK}do_test capi2-3.9 {  execsql {CREATE UNIQUE INDEX i1 ON t1(a)}  set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,2,3)} -1 TAIL]  set TAIL} {}do_test capi2-3.9b {db changes} {0}do_test capi2-3.10 {  list [sqlite3_step $VM] \       [sqlite3_column_count $VM] \       [get_row_values $VM] \       [get_column_names $VM]} {SQLITE_DONE 0 {} {}}# Update for v3 - the change has not actually happened until the query is# finalized. Is this going to cause trouble for anyone? Lee Nelson maybe?# (Later:) The change now happens just before SQLITE_DONE is returned.do_test capi2-3.10b {db changes} {1}do_test capi2-3.11 {  sqlite3_finalize $VM} {SQLITE_OK}do_test capi2-3.11b {db changes} {1}do_test capi2-3.12 {  sqlite3_finalize $VM} {SQLITE_MISUSE}do_test capi2-3.13 {  set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,3,4)} -1 TAIL]  list [sqlite3_step $VM] \       [sqlite3_column_count $VM] \       [get_row_values $VM] \       [get_column_names $VM]} {SQLITE_ERROR 0 {} {}}# Update for v3: Preparing a statement does not affect the change counter.# (Test result changes from 0 to 1).  (Later:) change counter updates occur# when sqlite3_step returns, not at finalize time.do_test capi2-3.13b {db changes} {0}do_test capi2-3.14 {  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]} {SQLITE_CONSTRAINT {column a is not unique}}do_test capi2-3.15 {  set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL]  set TAIL} {}do_test capi2-3.16 {  list [sqlite3_step $VM] \       [sqlite3_column_count $VM] \       [get_row_values $VM] \       [get_column_names $VM]} {SQLITE_DONE 0 {} {}}do_test capi2-3.17 {  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]} {SQLITE_OK {not an error}}do_test capi2-3.18 {  set VM [sqlite3_prepare $DB {INSERT INTO t2 VALUES(NULL,2)} -1 TAIL]  list [sqlite3_step $VM] \       [sqlite3_column_count $VM] \       [get_row_values $VM] \       [get_column_names $VM]} {SQLITE_ERROR 0 {} {}}do_test capi2-3.19 {  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]} {SQLITE_CONSTRAINT {t2.a may not be NULL}}do_test capi2-3.20 {  execsql {    CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) );    INSERT INTO a1 VALUES(1, 1);  }} {}do_test capi2-3.21 {  set VM [sqlite3_prepare $DB {INSERT INTO a1 VALUES(1, 1)} -1 TAIL]  sqlite3_step $VM} {SQLITE_ERROR}do_test capi2-3.22 {  sqlite3_errcode $DB} {SQLITE_ERROR}do_test capi2-3.23 {  sqlite3_finalize $VM} {SQLITE_CONSTRAINT}do_test capi2-3.24 {  sqlite3_errcode $DB} {SQLITE_CONSTRAINT}# Two or more virtual machines exists at the same time.#do_test capi2-4.1 {  set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL]  set TAIL} {}do_test capi2-4.2 {  set VM2 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL]  set TAIL} {}do_test capi2-4.3 {  set VM3 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(3,4)} -1 TAIL]  set TAIL} {}do_test capi2-4.4 {  list [sqlite3_step $VM2] \       [sqlite3_column_count $VM2] \       [get_row_values $VM2] \       [get_column_names $VM2]} {SQLITE_DONE 0 {} {}}do_test capi2-4.5 {  execsql {SELECT * FROM t2 ORDER BY a}} {2 3}do_test capi2-4.6 {  sqlite3_finalize $VM2} {SQLITE_OK}do_test capi2-4.7 {  list [sqlite3_step $VM3] \       [sqlite3_column_count $VM3] \       [get_row_values $VM3] \       [get_column_names $VM3]} {SQLITE_DONE 0 {} {}}do_test capi2-4.8 {  execsql {SELECT * FROM t2 ORDER BY a}} {2 3 3 4}do_test capi2-4.9 {  sqlite3_finalize $VM3} {SQLITE_OK}do_test capi2-4.10 {  list [sqlite3_step $VM1] \       [sqlite3_column_count $VM1] \       [get_row_values $VM1] \       [get_column_names $VM1]} {SQLITE_DONE 0 {} {}}do_test capi2-4.11 {  execsql {SELECT * FROM t2 ORDER BY a}} {1 2 2 3 3 4}do_test capi2-4.12 {  sqlite3_finalize $VM1} {SQLITE_OK}# Interleaved SELECTs#do_test capi2-5.1 {  set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]  set VM2 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]  set VM3 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]  list [sqlite3_step $VM1] \       [sqlite3_column_count $VM1] \       [get_row_values $VM1] \       [get_column_names $VM1]} {SQLITE_ROW 2 {2 3} {a b {} {}}}do_test capi2-5.2 {  list [sqlite3_step $VM2] \       [sqlite3_column_count $VM2] \       [get_row_values $VM2] \       [get_column_names $VM2]} {SQLITE_ROW 2 {2 3} {a b {} {}}}do_test capi2-5.3 {  list [sqlite3_step $VM1] \       [sqlite3_column_count $VM1] \       [get_row_values $VM1] \       [get_column_names $VM1]} {SQLITE_ROW 2 {3 4} {a b {} {}}}do_test capi2-5.4 {  list [sqlite3_step $VM3] \       [sqlite3_column_count $VM3] \       [get_row_values $VM3] \       [get_column_names $VM3]} {SQLITE_ROW 2 {2 3} {a b {} {}}}do_test capi2-5.5 {  list [sqlite3_step $VM3] \       [sqlite3_column_count $VM3] \       [get_row_values $VM3] \       [get_column_names $VM3]} {SQLITE_ROW 2 {3 4} {a b {} {}}}do_test capi2-5.6 {  list [sqlite3_step $VM3] \       [sqlite3_column_count $VM3] \       [get_row_values $VM3] \       [get_column_names $VM3]} {SQLITE_ROW 2 {1 2} {a b {} {}}}do_test capi2-5.7 {  list [sqlite3_step $VM3] \       [sqlite3_column_count $VM3] \       [get_row_values $VM3] \       [get_column_names $VM3]} {SQLITE_DONE 2 {} {a b {} {}}}do_test capi2-5.8 {  sqlite3_finalize $VM3} {SQLITE_OK}do_test capi2-5.9 {  list [sqlite3_step $VM1] \       [sqlite3_column_count $VM1] \       [get_row_values $VM1] \       [get_column_names $VM1]} {SQLITE_ROW 2 {1 2} {a b {} {}}}do_test capi2-5.10 {  sqlite3_finalize $VM1} {SQLITE_OK}do_test capi2-5.11 {  list [sqlite3_step $VM2] \       [sqlite3_column_count $VM2] \       [get_row_values $VM2] \       [get_column_names $VM2]} {SQLITE_ROW 2 {3 4} {a b {} {}}}do_test capi2-5.12 {  list [sqlite3_step $VM2] \       [sqlite3_column_count $VM2] \       [get_row_values $VM2] \       [get_column_names $VM2]} {SQLITE_ROW 2 {1 2} {a b {} {}}}do_test capi2-5.11 {