SQLite4  Check-in [9678daa99f]

** behavior is undefined.
**
** ^The fourth parameter, eTextRep, specifies the
** [SQLITE4_UTF8 | text encoding] this SQL function prefers for
** its parameters.  Every SQL function implementation must be able to work
** with UTF-8, UTF-16le, or UTF-16be.  But some implementations may be
** more efficient with one encoding than another.  ^An application may
** invoke sqlite4_create_function() multiple times with the same function 
** but with different values of eTextRep. ^When multiple implementations 
** of the same function are available, SQLite will pick the one that 
** involves the least amount of data conversion. If there is only a single 
** implementation which does not care what text encoding is used, then the 
** fourth argument should be [SQLITE4_ANY].
**
** ^(The fifth parameter is an arbitrary pointer.  The implementation of the
** function can gain access to this pointer using [sqlite4_context_appdata()].)^
**
** ^The sixth, seventh and eighth parameters, xFunc, xStep and xFinal, are
** pointers to C-language functions that implement the SQL function or
** aggregate. ^A scalar SQL function requires an implementation of the xFunc

** of the default VFS is not implemented correctly, or not implemented at
** all, then the behavior of sqlite4_sleep() may deviate from the description
** in the previous paragraphs.
*/
int sqlite4_sleep(int);

/*
** CAPIREF: Test For An Open Transaction
** KEYWORDS: {autocommit mode}
**
** ^The sqlite4_db_transaction_status() interface returns non-zero if
** the database connection passed as the only argument is currently within
** an explicitly started transaction. An explicit transaction is opened
** using a [BEGIN] command, and usually concluded using a [COMMIT] or
** [ROLLBACK].

# 2013 June 12
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# 
# Test cases for SELECT statements.
#

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

# Evaluate the SQL query passed as the only argument using database
# connection [db]. Return any results as a list of lists - one list
# element for each row. i.e.
#
#   "SELECT 1, 2 UNION SELECT 3, 4" -> "{1 2} {3 4}"
#
proc eval_to_list {bOrdered sql} {
  set sqlresult [list]
  db eval $sql a {
    set row [list]
    foreach c $a(*) { lappend row $a($c) }
    lappend sqlresult $row
  }
  if {!$bOrdered} {set sqlresult [lsort $sqlresult]}
  set sqlresult
}

# Execute SELECT query $zSql using [eval_to_list] and compare the results
# to $lRes. If argument $bOrdered is false, this indicates that the order
# of results is non-deterministic. In this case the results are passed
# to [lsort] before they are compared to $lRes.
#
proc do_select_test {testname bOrdered zSql lRes} {
  uplevel [list do_test $testname [list eval_to_list $bOrdered $zSql] $lRes]
}

# The SELECT tests in this file all use the following command. See the
# example below for details.
#
proc do_select_test_set {tn data indexes tests} {
  db_delete_and_reopen
  execsql $data
  db_save_and_close

  if {$tn=="*"} {
    foreach {tn2 bOrder sql res} $tests {
      puts "$tn2 $bOrder \"$sql\" {[eval_to_list $bOrder $sql]}"
    }
  } else {
    foreach {tn1 sql} $indexes {
      db_restore_and_reopen
      foreach {tn2 bOrder sql res} $tests {
        do_select_test $tn.$tn1.$tn2 $bOrder $sql $res
      }
    }
  }
}

#--------------------------------------------------------------------
# Warm-body tests. This block serves as an example of how to use
# the [do_select_test_set] command.
#
do_select_test_set 1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES(2, 4,  6);
  INSERT INTO t1 VALUES(1, 2,  3);
  INSERT INTO t1 VALUES(4, 8,  12);
  INSERT INTO t1 VALUES(3, 6,  9);
  INSERT INTO t1 VALUES(6, 12, 18);
  INSERT INTO t1 VALUES(5, 10, 15);
} {
  1 { }
  2 { CREATE INDEX ON t1(a, b, c) }
  3 { CREATE INDEX ON t1(c, b, a) }
  4 { 
    CREATE INDEX ON t1(a);
    CREATE INDEX ON t1(b);
    CREATE INDEX ON t1(c);
  }
} {
1 0 "SELECT * FROM t1 WHERE a = 2" {{2 4 6}}
2 0 "SELECT * FROM t1 WHERE a < 4" {{1 2 3} {2 4 6} {3 6 9}}
3 0 "SELECT * FROM t1 WHERE a = b/2 AND c%2" {{1 2 3} {3 6 9} {5 10 15}}
4 0 "SELECT (a/2) FROM t1 GROUP BY (a/2)" {0 1 2 3}
5 0 "SELECT a+b FROM t1 GROUP BY (a+b)" {12 15 18 3 6 9}
6 1 "SELECT a+b FROM t1 GROUP BY (a+b) ORDER BY 1" {3 6 9 12 15 18}
7 0 "SELECT a*b FROM t1 WHERE (c%2)" {18 2 50}
8 0 "SELECT count(*) FROM t1, t1 AS t2 WHERE t1.a=t2.a" {6}
9 0 "SELECT * FROM t1 WHERE a=1 AND b=2" {{1 2 3}}
10 0 "SELECT * FROM t1 WHERE a>3 AND b<12" {{4 8 12} {5 10 15}}
}

finish_test

<h3> UTF-16 Functions </h3>

<p>
Many SQLite3 APIs come in two flavours - UTF-8 and UTF-16. For example,
sqlite3_complete() and sqlite3_complete16(). In most cases, the only 
difference between the two versions is that one interprets or returns
text encoded using UTF-8, and the other using native byte-order UTF-16.
For SQLite4, all UTF-16 APIs have been removed except the following:

<ul>
  <li> sqlite4_column_text16()
  <li> sqlite4_value_text16()
  <li> sqlite4_bind_text16()
  <li> sqlite4_result_text16()
  <li> sqlite4_result_error16()
</ul>

<p>
In place of the removed APIs, SQLite4 offers an API - sqlite4_translate() -
to translate from UTF-16 to UTF-8 and vice-versa. For example, to obtain
the current error message formated using UTF-16 (available in SQLite3
by calling sqlite3_errmsg16()), the following:

<pre>
  u16 *pError;                    /* Pointer to translated error message */
  sqlite4_buffer buf;             /* Buffer to manage memory used for pError */

  /* Initialize a buffer object. Then populate it with the UTF-16 translation
  ** of the UTF-8 error message returned by sqlite4_errmsg().  */
  sqlite4_buffer_init(&buf, 0);
  pError = sqlite4_translate(
      &buf, sqlite4_errmsg(db), -1, SQLITE4_TRANSLATE_UTF8_UTF16
  );

  if( pError==0 ){
    /* An out-of-memory error has occurred */
  }else{
    /* pError now points to a buffer containing the current error message
    ** encoded using native byte-order UTF-16. Do something with it! */
  }

  /* Free the contents of the buffer (and hence pError) */
  sqlite4_buffer_clear(&buf);
</pre>