xref: /sqlite-3.40.0/test/tableapi.test (revision c023e03e) 
1# 2001 September 15
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#***********************************************************************
11# This file implements regression tests for SQLite library.  The
12# focus of this file is testing the sqlite_exec_printf() and
13# sqlite_get_table_printf() APIs.
14#
15# $Id: tableapi.test,v 1.5 2002/03/23 00:52:01 drh Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20do_test tableapi-1.0 {
21  set ::dbx [sqlite_open testdb]
22  catch {sqlite_exec_printf $::dbx {DROP TABLE xyz} {}}
23  sqlite_exec_printf $::dbx {CREATE TABLE %s(a int, b text)} xyz
24} {0 {}}
25do_test tableapi-1.1 {
26  sqlite_exec_printf $::dbx {
27    INSERT INTO xyz VALUES(1,'%q')
28  } {Hi Y'all}
29} {0 {}}
30do_test tableapi-1.2 {
31  sqlite_exec_printf $::dbx {SELECT * FROM xyz} {}
32} {0 {a b 1 {Hi Y'all}}}
33
34do_test tableapi-2.1 {
35  sqlite_get_table_printf $::dbx {
36    BEGIN TRANSACTION;
37    SELECT * FROM xyz WHERE b='%q'
38  } {Hi Y'all}
39} {0 1 2 a b 1 {Hi Y'all}}
40do_test tableapi-2.2 {
41  sqlite_get_table_printf $::dbx {
42    SELECT * FROM xyz
43  } {}
44} {0 1 2 a b 1 {Hi Y'all}}
45do_test tableapi-2.3 {
46  for {set i 2} {$i<=50} {incr i} {
47    sqlite_get_table_printf $::dbx \
48       "INSERT INTO xyz VALUES($i,'(%s)')" $i
49  }
50  sqlite_get_table_printf $::dbx {
51    SELECT * FROM xyz ORDER BY a
52  } {}
53} {0 50 2 a b 1 {Hi Y'all} 2 (2) 3 (3) 4 (4) 5 (5) 6 (6) 7 (7) 8 (8) 9 (9) 10 (10) 11 (11) 12 (12) 13 (13) 14 (14) 15 (15) 16 (16) 17 (17) 18 (18) 19 (19) 20 (20) 21 (21) 22 (22) 23 (23) 24 (24) 25 (25) 26 (26) 27 (27) 28 (28) 29 (29) 30 (30) 31 (31) 32 (32) 33 (33) 34 (34) 35 (35) 36 (36) 37 (37) 38 (38) 39 (39) 40 (40) 41 (41) 42 (42) 43 (43) 44 (44) 45 (45) 46 (46) 47 (47) 48 (48) 49 (49) 50 (50)}
54do_test tableapi-2.3.1 {
55  sqlite_get_table_printf $::dbx {
56    SELECT * FROM xyz  WHERE a>49 ORDER BY a
57  } {}
58} {0 1 2 a b 50 (50)}
59do_test tableapi-2.3.2 {
60  sqlite_get_table_printf $::dbx {
61    SELECT * FROM xyz WHERE a>47 ORDER BY a
62  } {}
63} {0 3 2 a b 48 (48) 49 (49) 50 (50)}
64do_test tableapi-2.4 {
65  set ::big_str [sqlite_mprintf_str {%500'* Hello %500'*} 0 0 {}]
66  sqlite_get_table_printf $::dbx {
67    INSERT INTO xyz VALUES(51,'%q')
68  } $::big_str
69} {0 0 0}
70do_test tableapi-2.5 {
71  sqlite_get_table_printf $::dbx {
72    SELECT * FROM xyz WHERE a>49 ORDER BY a;
73  } {}
74} "0 2 2 a b 50 (50) 51 \173$::big_str\175"
75do_test tableapi-2.6 {
76  sqlite_get_table_printf $::dbx {
77    INSERT INTO xyz VALUES(52,NULL)
78  } {}
79  sqlite_get_table_printf $::dbx {
80    SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC
81  } {}
82} {0 3 2 a b 52 NULL 50 (50) 42 (42)}
83do_test tableapi-2.7 {
84  sqlite_get_table_printf $::dbx {
85    SELECT * FROM xyz WHERE a>1000
86  } {}
87} {0 0 0}
88
89# Repeat all tests with the empty_result_callbacks pragma turned on
90#
91do_test tableapi-3.1 {
92  sqlite_get_table_printf $::dbx {
93    ROLLBACK;
94    PRAGMA empty_result_callbacks = ON;
95    SELECT * FROM xyz WHERE b='%q'
96  } {Hi Y'all}
97} {0 1 2 a b 1 {Hi Y'all}}
98do_test tableapi-3.2 {
99  sqlite_get_table_printf $::dbx {
100    SELECT * FROM xyz
101  } {}
102} {0 1 2 a b 1 {Hi Y'all}}
103do_test tableapi-3.3 {
104  for {set i 2} {$i<=50} {incr i} {
105    sqlite_get_table_printf $::dbx \
106       "INSERT INTO xyz VALUES($i,'(%s)')" $i
107  }
108  sqlite_get_table_printf $::dbx {
109    SELECT * FROM xyz ORDER BY a
110  } {}
111} {0 50 2 a b 1 {Hi Y'all} 2 (2) 3 (3) 4 (4) 5 (5) 6 (6) 7 (7) 8 (8) 9 (9) 10 (10) 11 (11) 12 (12) 13 (13) 14 (14) 15 (15) 16 (16) 17 (17) 18 (18) 19 (19) 20 (20) 21 (21) 22 (22) 23 (23) 24 (24) 25 (25) 26 (26) 27 (27) 28 (28) 29 (29) 30 (30) 31 (31) 32 (32) 33 (33) 34 (34) 35 (35) 36 (36) 37 (37) 38 (38) 39 (39) 40 (40) 41 (41) 42 (42) 43 (43) 44 (44) 45 (45) 46 (46) 47 (47) 48 (48) 49 (49) 50 (50)}
112do_test tableapi-3.3.1 {
113  sqlite_get_table_printf $::dbx {
114    SELECT * FROM xyz  WHERE a>49 ORDER BY a
115  } {}
116} {0 1 2 a b 50 (50)}
117do_test tableapi-3.3.2 {
118  sqlite_get_table_printf $::dbx {
119    SELECT * FROM xyz WHERE a>47 ORDER BY a
120  } {}
121} {0 3 2 a b 48 (48) 49 (49) 50 (50)}
122do_test tableapi-3.4 {
123  set ::big_str [sqlite_mprintf_str {%500'* Hello %500'*} 0 0 {}]
124  sqlite_get_table_printf $::dbx {
125    INSERT INTO xyz VALUES(51,'%q')
126  } $::big_str
127} {0 0 0}
128do_test tableapi-3.5 {
129  sqlite_get_table_printf $::dbx {
130    SELECT * FROM xyz WHERE a>49 ORDER BY a;
131  } {}
132} "0 2 2 a b 50 (50) 51 \173$::big_str\175"
133do_test tableapi-3.6 {
134  sqlite_get_table_printf $::dbx {
135    INSERT INTO xyz VALUES(52,NULL)
136  } {}
137  sqlite_get_table_printf $::dbx {
138    SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC
139  } {}
140} {0 3 2 a b 52 NULL 50 (50) 42 (42)}
141do_test tableapi-3.7 {
142  sqlite_get_table_printf $::dbx {
143    SELECT * FROM xyz WHERE a>1000
144  } {}
145} {0 0 2 a b}
146
147do_test tableapi-4.1 {
148  set rc [catch {
149    sqlite_get_table_printf $::dbx {
150      SELECT * FROM xyz;  SELECT * FROM sqlite_master
151    } {}
152  } msg]
153  concat $rc $msg
154} {0 1 {sqlite_get_table() called with two or more incompatible queries}}
155
156do_test tableapi-99.0 {
157  sqlite_close $::dbx
158} {}
159
160finish_test
161