SQLite4
Check-in [9678daa99f]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add file selectF.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9678daa99f05495cddec8272f13ec93c4c586a9f
User & Date: dan 2013-06-12 19:20:59
Context
2013-06-13
15:24
Remove the 'encoding' argument from sqlite4_create_function(). check-in: f88d080127 user: dan tags: trunk
2013-06-12
19:20
Add file selectF.test. check-in: 9678daa99f user: dan tags: trunk
2013-06-11
17:27
Add a note to www/porting.wiki describing the changes to utf-16 support. check-in: 5cd50e225c user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
....
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
** 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() or sqlite4_create_function16() 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 Auto-Commit Mode
** 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].







|
|
|
|
|
|







 







|







2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
....
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
** 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].

Added test/selectF.test.



















































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
# 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




Changes to www/porting.wiki.

37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
..
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

<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()
................................................................................
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>








|







 







|
|









|






37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
..
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

<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()
................................................................................
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>