1# 2016 July 14 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 focus of 12# this test file is the "sqlite3_trace_v2()" and "sqlite3_expanded_sql()" 13# APIs. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18ifcapable !trace { finish_test ; return } 19set ::testprefix trace3 20 21proc trace_v2_error { args } { 22 lappend ::stmtlist(error) [string trim $args] 23 error "trace error"; # this will be ignored. 24} 25proc trace_v2_record { args } { 26 lappend ::stmtlist(record) [string trim $args] 27} 28proc trace_v2_nop { args } {}; # do nothing. 29 30do_test trace3-1.0 { 31 execsql { 32 CREATE TABLE t1(a,b); 33 INSERT INTO t1 VALUES(1,NULL); 34 INSERT INTO t1 VALUES(2,-1); 35 INSERT INTO t1 VALUES(3,0); 36 INSERT INTO t1 VALUES(4,1); 37 INSERT INTO t1 VALUES(5,-2147483648); 38 INSERT INTO t1 VALUES(6,2147483647); 39 INSERT INTO t1 VALUES(7,-9223372036854775808); 40 INSERT INTO t1 VALUES(8,9223372036854775807); 41 INSERT INTO t1 VALUES(9,-1.0); 42 INSERT INTO t1 VALUES(10,0.0); 43 INSERT INTO t1 VALUES(11,1.0); 44 INSERT INTO t1 VALUES(12,''); 45 INSERT INTO t1 VALUES(13,'1'); 46 INSERT INTO t1 VALUES(14,'one'); 47 INSERT INTO t1 VALUES(15,x'abcd0123'); 48 INSERT INTO t1 VALUES(16,x'4567cdef'); 49 } 50} {} 51 52do_test trace3-1.1 { 53 set rc [catch {db trace_v2 1 2 3} msg] 54 lappend rc $msg 55} {1 {wrong # args: should be "db trace_v2 ?CALLBACK? ?MASK?"}} 56do_test trace3-1.2 { 57 set rc [catch {db trace_v2 1 bad} msg] 58 lappend rc $msg 59} {1 {bad trace type "bad": must be statement, profile, row, or close}} 60 61do_test trace3-2.1 { 62 db trace_v2 trace_v2_nop 63 db trace_v2 64} {trace_v2_nop} 65 66do_test trace3-3.1 { 67 unset -nocomplain ::stmtlist 68 db trace_v2 trace_v2_nop 69 execsql { 70 SELECT a, b FROM t1 ORDER BY a; 71 } 72 array get ::stmtlist 73} {} 74do_test trace3-3.2 { 75 set ::stmtlist(error) {} 76 db trace_v2 trace_v2_error 77 execsql { 78 SELECT a, b FROM t1 ORDER BY a; 79 } 80 set ::stmtlist(error) 81} {/^\{-?\d+ \{SELECT a, b FROM t1 ORDER BY a;\}\}$/} 82do_test trace3-3.3 { 83 set ::stmtlist(record) {} 84 db trace_v2 trace_v2_record 85 execsql { 86 SELECT a, b FROM t1 ORDER BY a; 87 } 88 set ::stmtlist(record) 89} {/^\{-?\d+ \{SELECT a, b FROM t1 ORDER BY a;\}\}$/} 90do_test trace3-3.4 { 91 set ::stmtlist(record) {} 92 db trace_v2 trace_v2_record statement 93 execsql { 94 SELECT a, b FROM t1 ORDER BY a; 95 } 96 set ::stmtlist(record) 97} {/^\{-?\d+ \{SELECT a, b FROM t1 ORDER BY a;\}\}$/} 98do_test trace3-3.5 { 99 set ::stmtlist(record) {} 100 db trace_v2 trace_v2_record 1 101 execsql { 102 SELECT a, b FROM t1 ORDER BY a; 103 } 104 set ::stmtlist(record) 105} {/^\{-?\d+ \{SELECT a, b FROM t1 ORDER BY a;\}\}$/} 106 107do_test trace3-4.1 { 108 set ::stmtlist(record) {} 109 db trace_v2 trace_v2_record profile 110 execsql { 111 SELECT a, b FROM t1 ORDER BY a; 112 } 113 set ::stmtlist(record) 114} {/^\{-?\d+ -?\d+\}$/} 115do_test trace3-4.2 { 116 set ::stmtlist(record) {} 117 db trace_v2 trace_v2_record 2 118 execsql { 119 SELECT a, b FROM t1 ORDER BY a; 120 } 121 set ::stmtlist(record) 122} {/^\{-?\d+ -?\d+\}$/} 123 124do_test trace3-4.3 { 125 set ::stmtlist(record) {} 126 db trace_v2 trace_v2_record profile 127 execsql { 128 SELECT a, b FROM t1 ORDER BY a; 129 } 130 set stmt [lindex [lindex $::stmtlist(record) 0] 0] 131 set ns [lindex [lindex $::stmtlist(record) 0] 1] 132 list $stmt [expr {$ns >= 0 && $ns <= 9999999}]; # less than 0.010 seconds 133} {/^-?\d+ 1$/} 134do_test trace3-4.4 { 135 set ::stmtlist(record) {} 136 db trace_v2 trace_v2_record 2 137 execsql { 138 SELECT a, b FROM t1 ORDER BY a; 139 } 140 set stmt [lindex [lindex $::stmtlist(record) 0] 0] 141 set ns [lindex [lindex $::stmtlist(record) 0] 1] 142 list $stmt [expr {$ns >= 0 && $ns <= 9999999}]; # less than 0.010 seconds 143} {/^-?\d+ 1$/} 144 145do_test trace3-5.1 { 146 set ::stmtlist(record) {} 147 db trace_v2 trace_v2_record row 148 execsql { 149 SELECT a, b FROM t1 ORDER BY a; 150 } 151 set ::stmtlist(record) 152} "/^[string trim [string repeat {-?\d+ } 16]]\$/" 153do_test trace3-5.2 { 154 set ::stmtlist(record) {} 155 db trace_v2 trace_v2_record 4 156 execsql { 157 SELECT a, b FROM t1 ORDER BY a; 158 } 159 set ::stmtlist(record) 160} "/^[string trim [string repeat {-?\d+ } 16]]\$/" 161 162do_test trace3-6.1 { 163 set ::stmtlist(record) {} 164 db trace_v2 trace_v2_record {profile row} 165 execsql { 166 SELECT a, b FROM t1 ORDER BY a; 167 } 168 set ::stmtlist(record) 169} "/^[string trim [string repeat {-?\d+ } 16]] \\\{-?\\d+ -?\\d+\\\}\$/" 170do_test trace3-6.2 { 171 set ::stmtlist(record) {} 172 db trace_v2 trace_v2_record {statement profile row} 173 execsql { 174 SELECT a, b FROM t1 ORDER BY a; 175 } 176 set ::stmtlist(record) 177} "/^\\\{-?\\d+ \\\{SELECT a, b FROM t1 ORDER BY a;\\\}\\\} [string trim \ 178[string repeat {-?\d+ } 16]] \\\{-?\\d+ -?\\d+\\\}\$/" 179 180do_test trace3-7.1 { 181 set DB [sqlite3_connection_pointer db] 182 183 set STMT [sqlite3_prepare_v2 $DB \ 184 "SELECT a, b FROM t1 WHERE b = ? ORDER BY a;" -1 TAIL] 185} {/^[0-9A-Fa-f]+$/} 186 187do_test trace3-8.1 { 188 list [sqlite3_bind_null $STMT 1] [sqlite3_expanded_sql $STMT] 189} {{} {SELECT a, b FROM t1 WHERE b = NULL ORDER BY a;}} 190do_test trace3-8.2 { 191 list [sqlite3_bind_int $STMT 1 123] [sqlite3_expanded_sql $STMT] 192} {{} {SELECT a, b FROM t1 WHERE b = 123 ORDER BY a;}} 193do_test trace3-8.3 { 194 list [sqlite3_bind_int64 $STMT 1 123] [sqlite3_expanded_sql $STMT] 195} {{} {SELECT a, b FROM t1 WHERE b = 123 ORDER BY a;}} 196do_test trace3-8.4 { 197 list [sqlite3_bind_text $STMT 1 "some string" 11] \ 198 [sqlite3_expanded_sql $STMT] 199} {{} {SELECT a, b FROM t1 WHERE b = 'some string' ORDER BY a;}} 200do_test trace3-8.5 { 201 list [sqlite3_bind_text $STMT 1 "some 'bad' string" 17] \ 202 [sqlite3_expanded_sql $STMT] 203} {{} {SELECT a, b FROM t1 WHERE b = 'some ''bad'' string' ORDER BY a;}} 204do_test trace3-8.6 { 205 list [sqlite3_bind_double $STMT 1 123] [sqlite3_expanded_sql $STMT] 206} {{} {SELECT a, b FROM t1 WHERE b = 123.0 ORDER BY a;}} 207do_test trace3-8.7 { 208 list [sqlite3_bind_text16 $STMT 1 \ 209 [encoding convertto unicode hi\000yall\000] 16] \ 210 [sqlite3_expanded_sql $STMT] 211} {{} {SELECT a, b FROM t1 WHERE b = 'hi' ORDER BY a;}} 212do_test trace3-8.8 { 213 list [sqlite3_bind_blob $STMT 1 "\x12\x34\x56" 3] \ 214 [sqlite3_expanded_sql $STMT] 215} {{} {SELECT a, b FROM t1 WHERE b = x'123456' ORDER BY a;}} 216do_test trace3-8.9 { 217 list [sqlite3_bind_blob $STMT 1 "\xAB\xCD\xEF" 3] \ 218 [sqlite3_expanded_sql $STMT] 219} {{} {SELECT a, b FROM t1 WHERE b = x'abcdef' ORDER BY a;}} 220 221do_test trace3-9.1 { 222 sqlite3_finalize $STMT 223} {SQLITE_OK} 224 225do_test trace3-10.1 { 226 db trace_v2 "" 227 db trace_v2 228} {} 229do_test trace3-10.2 { 230 unset -nocomplain ::stmtlist 231 db trace_v2 "" {statement profile row} 232 execsql { 233 SELECT a, b FROM t1 ORDER BY a; 234 } 235 array get ::stmtlist 236} {} 237 238do_test trace3-11.1 { 239 set ::stmtlist(record) {} 240 db trace_v2 trace_v2_record close 241 db close 242 set ::stmtlist(record) 243} {/^-?\d+$/} 244 245reset_db 246 247do_test trace3-11.2 { 248 set ::stmtlist(record) {} 249 db trace_v2 trace_v2_record 8 250 db close 251 set ::stmtlist(record) 252} {/^-?\d+$/} 253 254#------------------------------------------------------------------------- 255reset_db 256do_test 12.1.0 { 257 set ::STMT [sqlite3_prepare_v2 $DB \ 258 "SELECT ?1 || ?1 || ?1 || ?2 || ?3 || ?4 || ? || ?1 || ?" -1 TAIL 259 ] 260 sqlite3_bind_parameter_count $::STMT 261} {6} 262 263do_test 12.1.1 { 264 sqlite3_bind_text $STMT 1 "A" 1 265 sqlite3_bind_text $STMT 2 "B" 1 266 sqlite3_bind_text $STMT 3 "C" 1 267 sqlite3_bind_text $STMT 4 "D" 1 268 sqlite3_bind_text $STMT 5 "E" 1 269 sqlite3_bind_text $STMT 6 "F" 1 270 sqlite3_expanded_sql $STMT 271} {SELECT 'A' || 'A' || 'A' || 'B' || 'C' || 'D' || 'E' || 'A' || 'F'} 272 273do_test 12.1.2 { 274 sqlite3_step $STMT 275 sqlite3_column_text $STMT 0 276} {AAABCDEAF} 277 278do_test 12.1.3 { 279 sqlite3_finalize $STMT 280} {SQLITE_OK} 281 282do_test 12.2.0 { 283 execsql { 284 CREATE TABLE nameFtsFuzzySearchTable( 285 word, distance, langid, score, top, scope 286 ); 287 } 288 set ::STMT [sqlite3_prepare_v2 $DB { 289 SELECT 290 substr(word,1,length(?1)-1) AS term, 291 distance, 292 langid, 293 score 294 FROM 295 nameFtsFuzzySearchTable 296 WHERE 297 word MATCH (?1) AND abs(?1) = abs(term) 298 AND top = ?2 AND distance > ?3 AND scope = ?4 AND langid = ? 299 GROUP BY term, langid 300 HAVING (1.0 - ((distance / 100.0) / CAST( length(?1) - 1 AS REAL ))) >= ? 301 } -1 TAIL] 302 sqlite3_bind_parameter_count $::STMT 303} {6} 304 305do_test 12.1.1 { 306 sqlite3_bind_text $STMT 1 "A" 1 307 sqlite3_bind_text $STMT 2 "B" 1 308 sqlite3_bind_text $STMT 3 "C" 1 309 sqlite3_bind_text $STMT 4 "D" 1 310 sqlite3_bind_text $STMT 5 "E" 1 311 sqlite3_bind_text $STMT 6 "F" 1 312 sqlite3_expanded_sql $STMT 313} { 314 SELECT 315 substr(word,1,length('A')-1) AS term, 316 distance, 317 langid, 318 score 319 FROM 320 nameFtsFuzzySearchTable 321 WHERE 322 word MATCH ('A') AND abs('A') = abs(term) 323 AND top = 'B' AND distance > 'C' AND scope = 'D' AND langid = 'E' 324 GROUP BY term, langid 325 HAVING (1.0 - ((distance / 100.0) / CAST( length('A') - 1 AS REAL ))) >= 'F' 326 } 327 328do_test 12.1.2 { 329 sqlite3_finalize $STMT 330} {SQLITE_OK} 331 332 333finish_test 334