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.21 2009/07/17 14:37:25 shane Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20ifcapable !gettable { 21 finish_test 22 return 23} 24 25source $testdir/malloc_common.tcl 26 27do_test tableapi-1.0 { 28 set ::dbx [sqlite3_open test.db] 29 catch {sqlite_exec_printf $::dbx {DROP TABLE xyz} {}} 30 sqlite3_exec_printf $::dbx {CREATE TABLE %s(a int, b text)} xyz 31} {0 {}} 32do_test tableapi-1.1 { 33 sqlite3_exec_printf $::dbx { 34 INSERT INTO xyz VALUES(1,'%q') 35 } {Hi Y'all} 36} {0 {}} 37do_test tableapi-1.2 { 38 sqlite3_exec_printf $::dbx {SELECT * FROM xyz} {} 39} {0 {a b 1 {Hi Y'all}}} 40 41do_test tableapi-2.1 { 42 sqlite3_get_table_printf $::dbx { 43 BEGIN TRANSACTION; 44 SELECT * FROM xyz WHERE b='%q' 45 } {Hi Y'all} 46} {0 1 2 a b 1 {Hi Y'all}} 47do_test tableapi-2.2 { 48 sqlite3_get_table_printf $::dbx { 49 SELECT * FROM xyz 50 } {} 51} {0 1 2 a b 1 {Hi Y'all}} 52do_test tableapi-2.3 { 53 for {set i 2} {$i<=50} {incr i} { 54 sqlite3_get_table_printf $::dbx \ 55 "INSERT INTO xyz VALUES($i,'(%s)')" $i 56 } 57 sqlite3_get_table_printf $::dbx { 58 SELECT * FROM xyz ORDER BY a 59 } {} 60} {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)} 61do_test tableapi-2.3.1 { 62 sqlite3_get_table_printf $::dbx { 63 SELECT * FROM xyz WHERE a>49 ORDER BY a 64 } {} 65} {0 1 2 a b 50 (50)} 66do_test tableapi-2.3.2 { 67 sqlite3_get_table_printf $::dbx { 68 SELECT * FROM xyz WHERE a>47 ORDER BY a 69 } {} 70} {0 3 2 a b 48 (48) 49 (49) 50 (50)} 71do_test tableapi-2.3.3 { 72 sqlite3_get_table_printf $::dbx { 73 SELECT * FROM xyz WHERE a>47 ORDER BY a; invalid 74 } {} 75} {1 {near "invalid": syntax error}} 76do_test tableapi-2.3.4 { 77 sqlite3_get_table_printf $::dbx { 78 SELECT * FROM xyz WHERE a>47 ORDER BY a 79 } {} 8 80} {0 a b 48 (48) 49 (49) 50 (50)} 81do_test tableapi-2.4 { 82 set manyquote '''''''' 83 append manyquote $manyquote 84 append manyquote $manyquote 85 append manyquote $manyquote 86 append manyquote $manyquote 87 append manyquote $manyquote 88 append manyquote $manyquote 89 set ::big_str "$manyquote Hello $manyquote" 90 sqlite3_get_table_printf $::dbx { 91 INSERT INTO xyz VALUES(51,'%q') 92 } $::big_str 93} {0 0 0} 94do_test tableapi-2.5 { 95 sqlite3_get_table_printf $::dbx { 96 SELECT * FROM xyz WHERE a>49 ORDER BY a; 97 } {} 98} "0 2 2 a b 50 (50) 51 \173$::big_str\175" 99do_test tableapi-2.6 { 100 sqlite3_get_table_printf $::dbx { 101 INSERT INTO xyz VALUES(52,NULL) 102 } {} 103 ifcapable subquery { 104 sqlite3_get_table_printf $::dbx { 105 SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC 106 } {} 107 } else { 108 sqlite3_get_table_printf $::dbx { 109 SELECT * FROM xyz WHERE a=42 OR a=50 OR a=52 ORDER BY a DESC 110 } {} 111 } 112} {0 3 2 a b 52 NULL 50 (50) 42 (42)} 113do_test tableapi-2.7 { 114 sqlite3_get_table_printf $::dbx { 115 SELECT * FROM xyz WHERE a>1000 116 } {} 117} {0 0 0} 118 119# Repeat all tests with the empty_result_callbacks pragma turned on 120# 121do_test tableapi-3.1 { 122 sqlite3_get_table_printf $::dbx { 123 ROLLBACK; 124 PRAGMA empty_result_callbacks = ON; 125 SELECT * FROM xyz WHERE b='%q' 126 } {Hi Y'all} 127} {0 1 2 a b 1 {Hi Y'all}} 128do_test tableapi-3.2 { 129 sqlite3_get_table_printf $::dbx { 130 SELECT * FROM xyz 131 } {} 132} {0 1 2 a b 1 {Hi Y'all}} 133do_test tableapi-3.3 { 134 for {set i 2} {$i<=50} {incr i} { 135 sqlite3_get_table_printf $::dbx \ 136 "INSERT INTO xyz VALUES($i,'(%s)')" $i 137 } 138 sqlite3_get_table_printf $::dbx { 139 SELECT * FROM xyz ORDER BY a 140 } {} 141} {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)} 142do_test tableapi-3.3.1 { 143 sqlite3_get_table_printf $::dbx { 144 SELECT * FROM xyz WHERE a>49 ORDER BY a 145 } {} 146} {0 1 2 a b 50 (50)} 147do_test tableapi-3.3.2 { 148 sqlite3_get_table_printf $::dbx { 149 SELECT * FROM xyz WHERE a>47 ORDER BY a 150 } {} 151} {0 3 2 a b 48 (48) 49 (49) 50 (50)} 152do_test tableapi-3.4 { 153 sqlite3_get_table_printf $::dbx { 154 INSERT INTO xyz VALUES(51,'%q') 155 } $::big_str 156} {0 0 0} 157do_test tableapi-3.5 { 158 sqlite3_get_table_printf $::dbx { 159 SELECT * FROM xyz WHERE a>49 ORDER BY a; 160 } {} 161} "0 2 2 a b 50 (50) 51 \173$::big_str\175" 162do_test tableapi-3.6 { 163 sqlite3_get_table_printf $::dbx { 164 INSERT INTO xyz VALUES(52,NULL) 165 } {} 166 ifcapable subquery { 167 sqlite3_get_table_printf $::dbx { 168 SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC 169 } {} 170 } else { 171 sqlite3_get_table_printf $::dbx { 172 SELECT * FROM xyz WHERE a=42 OR a=50 OR a=52 ORDER BY a DESC 173 } {} 174 } 175} {0 3 2 a b 52 NULL 50 (50) 42 (42)} 176do_test tableapi-3.7 { 177 sqlite3_get_table_printf $::dbx { 178 SELECT * FROM xyz WHERE a>1000 179 } {} 180} {0 0 2 a b} 181 182do_test tableapi-4.1 { 183 set rc [catch { 184 sqlite3_get_table_printf $::dbx { 185 SELECT * FROM xyz; SELECT * FROM sqlite_master 186 } {} 187 } msg] 188 concat $rc $msg 189} {0 1 {sqlite3_get_table() called with two or more incompatible queries}} 190 191# A report on the mailing list says that the sqlite_get_table() api fails 192# on queries involving more than 40 columns. The following code attempts 193# to test that complaint 194# 195do_test tableapi-5.1 { 196 set sql "CREATE TABLE t2(" 197 set sep "" 198 for {set i 1} {$i<=100} {incr i} { 199 append sql ${sep}x$i 200 set sep , 201 } 202 append sql ) 203 sqlite3_get_table_printf $::dbx $sql {} 204 set sql "INSERT INTO t2 VALUES(" 205 set sep "" 206 for {set i 1} {$i<=100} {incr i} { 207 append sql ${sep}$i 208 set sep , 209 } 210 append sql ) 211 sqlite3_get_table_printf $::dbx $sql {} 212 sqlite3_get_table_printf $::dbx {SELECT * FROM t2} {} 213} {0 1 100 x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 x16 x17 x18 x19 x20 x21 x22 x23 x24 x25 x26 x27 x28 x29 x30 x31 x32 x33 x34 x35 x36 x37 x38 x39 x40 x41 x42 x43 x44 x45 x46 x47 x48 x49 x50 x51 x52 x53 x54 x55 x56 x57 x58 x59 x60 x61 x62 x63 x64 x65 x66 x67 x68 x69 x70 x71 x72 x73 x74 x75 x76 x77 x78 x79 x80 x81 x82 x83 x84 x85 x86 x87 x88 x89 x90 x91 x92 x93 x94 x95 x96 x97 x98 x99 x100 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} 214do_test tableapi-5.2 { 215 set sql "INSERT INTO t2 VALUES(" 216 set sep "" 217 for {set i 1} {$i<=100} {incr i} { 218 append sql ${sep}[expr {$i+1000}] 219 set sep , 220 } 221 append sql ) 222 sqlite3_get_table_printf $::dbx $sql {} 223 sqlite3_get_table_printf $::dbx {SELECT * FROM t2} {} 224} {0 2 100 x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 x16 x17 x18 x19 x20 x21 x22 x23 x24 x25 x26 x27 x28 x29 x30 x31 x32 x33 x34 x35 x36 x37 x38 x39 x40 x41 x42 x43 x44 x45 x46 x47 x48 x49 x50 x51 x52 x53 x54 x55 x56 x57 x58 x59 x60 x61 x62 x63 x64 x65 x66 x67 x68 x69 x70 x71 x72 x73 x74 x75 x76 x77 x78 x79 x80 x81 x82 x83 x84 x85 x86 x87 x88 x89 x90 x91 x92 x93 x94 x95 x96 x97 x98 x99 x100 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 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100} 225 226ifcapable schema_pragmas { 227 do_test tableapi-6.1 { 228 sqlite3_get_table_printf $::dbx {PRAGMA user_version} {} 229 } {0 1 1 user_version 0} 230} 231 232# do_malloc_test closes and deletes the usual db connections and files on 233# each iteration. $::dbx is a seperate connection, and on Windows, will 234# cause the file deletion of test.db to fail, so we move the close of $::dbx 235# up to here before the do_malloc_test. 236do_test tableapi-99.0 { 237 sqlite3_close $::dbx 238} {SQLITE_OK} 239 240do_malloc_test tableapi-7 -sqlprep { 241 DROP TABLE IF EXISTS t1; 242 CREATE TABLE t1(a,b); 243 INSERT INTO t1 VALUES(1,2); 244 INSERT INTO t1 VALUES(3,4); 245 INSERT INTO t1 SELECT a+4, b+4 FROM t1; 246 INSERT INTO t1 SELECT a+8, b+8 FROM t1; 247} -tclbody { 248 set r [sqlite3_get_table_printf db {SELECT rowid, a, b FROM t1} {}] 249 if {[llength $r]<26} {error "out of memory"} 250} 251 252finish_test 253