1# 2004 Jun 29 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. 12# 13# This file implements tests for the "sqlite3_trace()" API. 14# 15# $Id: trace.test,v 1.8 2009/04/07 14:14:23 danielk1977 Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20ifcapable !trace { 21 finish_test 22 return 23} 24 25set ::stmtlist {} 26do_test trace-1.1 { 27 set rc [catch {db trace 1 2 3} msg] 28 lappend rc $msg 29} {1 {wrong # args: should be "db trace ?CALLBACK?"}} 30proc trace_proc cmd { 31 lappend ::stmtlist [string trim $cmd] 32} 33do_test trace-1.2 { 34 db trace trace_proc 35 db trace 36} {trace_proc} 37do_test trace-1.3 { 38 execsql { 39 CREATE TABLE t1(a,b); 40 INSERT INTO t1 VALUES(1,2); 41 SELECT * FROM t1; 42 } 43} {1 2} 44do_test trace-1.4 { 45 set ::stmtlist 46} {{CREATE TABLE t1(a,b);} {INSERT INTO t1 VALUES(1,2);} {SELECT * FROM t1;}} 47do_test trace-1.5 { 48 db trace {} 49 db trace 50} {} 51do_test trace-1.6 { 52 db eval { 53 CREATE TABLE t1b(x TEXT PRIMARY KEY, y); 54 INSERT INTO t1b VALUES('abc','def'),('ghi','jkl'),('mno','pqr'); 55 } 56 set ::stmtlist {} 57 set xyzzy a* 58 db trace trace_proc 59 db eval { 60 SELECT y FROM t1b WHERE x GLOB $xyzzy 61 } 62} {def} 63do_test trace-1.7 { 64 set ::stmtlist 65} {{SELECT y FROM t1b WHERE x GLOB 'a*'}} 66db trace {} 67 68# If we prepare a statement and execute it multiple times, the trace 69# happens on each execution. 70# 71db close 72sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 73do_test trace-2.1 { 74 set STMT [sqlite3_prepare $DB {INSERT INTO t1 VALUES(2,3)} -1 TAIL] 75 db trace trace_proc 76 proc trace_proc sql { 77 global TRACE_OUT 78 lappend TRACE_OUT [string trim $sql] 79 } 80 set TRACE_OUT {} 81 sqlite3_step $STMT 82 set TRACE_OUT 83} {{INSERT INTO t1 VALUES(2,3)}} 84do_test trace-2.2 { 85 set TRACE_OUT {} 86 sqlite3_reset $STMT 87 set TRACE_OUT 88} {} 89do_test trace-2.3 { 90 sqlite3_step $STMT 91 set TRACE_OUT 92} {{INSERT INTO t1 VALUES(2,3)}} 93do_test trace-2.4 { 94 set TRACE_OUT {} 95 execsql {SELECT * FROM t1} 96} {1 2 2 3 2 3} 97do_test trace-2.5 { 98 set TRACE_OUT 99} {{SELECT * FROM t1}} 100catch {sqlite3_finalize $STMT} 101 102do_test trace-2.6 { 103 set TRACE_OUT {} 104 db eval VACUUM 105 set TRACE_OUT 106} {VACUUM} 107 108# Similar tests, but this time for profiling. 109# 110do_test trace-3.1 { 111 set rc [catch {db profile 1 2 3} msg] 112 lappend rc $msg 113} {1 {wrong # args: should be "db profile ?CALLBACK?"}} 114set ::stmtlist {} 115proc profile_proc {cmd tm} { 116 lappend ::stmtlist [string trim $cmd] 117} 118do_test trace-3.2 { 119 db trace {} 120 db profile profile_proc 121 db profile 122} {profile_proc} 123do_test trace-3.3 { 124 execsql { 125 CREATE TABLE t2(a,b); 126 INSERT INTO t2 VALUES(1,2); 127 SELECT * FROM t2; 128 } 129} {1 2} 130do_test trace-3.4 { 131 set ::stmtlist 132} {{CREATE TABLE t2(a,b);} {INSERT INTO t2 VALUES(1,2);} {SELECT * FROM t2;}} 133do_test trace-3.5 { 134 db profile {} 135 db profile 136} {} 137 138# If we prepare a statement and execute it multiple times, the profile 139# happens on each execution. 140# 141db close 142sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 143do_test trace-4.1 { 144 set STMT [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL] 145 db trace trace_proc 146 proc profile_proc {sql tm} { 147 global TRACE_OUT 148 lappend TRACE_OUT [string trim $sql] 149 } 150 set TRACE_OUT {} 151 sqlite3_step $STMT 152 set TRACE_OUT 153} {{INSERT INTO t2 VALUES(2,3)}} 154do_test trace-4.2 { 155 set TRACE_OUT {} 156 sqlite3_reset $STMT 157 set TRACE_OUT 158} {} 159do_test trace-4.3 { 160 sqlite3_step $STMT 161 set TRACE_OUT 162} {{INSERT INTO t2 VALUES(2,3)}} 163do_test trace-4.4 { 164 set TRACE_OUT {} 165 execsql {SELECT * FROM t1} 166} {1 2 2 3 2 3} 167do_test trace-4.5 { 168 set TRACE_OUT 169} {{SELECT * FROM t1}} 170catch {sqlite3_finalize $STMT} 171 172# 3.8.11: Profile output even if the statement is not run to completion. 173do_test trace-4.6 { 174 set TRACE_OUT {} 175 db eval {SELECT * FROM t1} {} {if {$a>=1} break} 176 set TRACE_OUT 177} {{SELECT * FROM t1}} 178 179 180# Trigger tracing. 181# 182ifcapable trigger { 183 do_test trace-5.1 { 184 db eval { 185 CREATE TRIGGER r1t1 AFTER UPDATE ON t1 BEGIN 186 UPDATE t2 SET a=new.a WHERE rowid=new.rowid; 187 END; 188 CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN 189 SELECT 'hello'; 190 END; 191 } 192 set TRACE_OUT {} 193 proc trace_proc cmd { 194 lappend ::TRACE_OUT [string trim $cmd] 195 } 196 db eval { 197 UPDATE t1 SET a=a+1; 198 } 199 set TRACE_OUT 200 } {{UPDATE t1 SET a=a+1;} {-- TRIGGER r1t1} {-- UPDATE t2 SET a=new.a WHERE rowid=new.rowid} {-- TRIGGER r1t2} {-- SELECT 'hello'} {-- TRIGGER r1t1} {-- UPDATE t2 SET a=new.a WHERE rowid=new.rowid} {-- TRIGGER r1t2} {-- SELECT 'hello'} {-- TRIGGER r1t1} {-- UPDATE t2 SET a=new.a WHERE rowid=new.rowid} {-- TRIGGER r1t2} {-- SELECT 'hello'}} 201} 202 203# With 3.6.21, we add the ability to expand host parameters in the trace 204# output. Test this feature. 205# 206do_test trace-6.1 { 207 set ::t6int [expr {3+3}] 208 set ::t6real [expr {1.5*4.0}] 209 set ::t6str {test-six y'all} 210 db eval {SELECT x'3031323334' AS x} {set ::t6blob $x} 211 unset -nocomplain t6null 212 set TRACE_OUT {} 213 execsql {SELECT $::t6int, $::t6real, $t6str, $t6blob, $t6null} 214} {6 6.0 {test-six y'all} 01234 {}} 215do_test trace-6.2 { 216 set TRACE_OUT 217} {{SELECT 6, 6.0, 'test-six y''all', x'3031323334', NULL}} 218do_test trace-6.3 { 219 set TRACE_OUT {} 220 execsql {SELECT $::t6int, ?1, $::t6int} 221} {6 6 6} 222do_test trace-6.4 { 223 set TRACE_OUT 224} {{SELECT 6, 6, 6}} 225do_test trace-6.5 { 226 execsql {CREATE TABLE t6([$::t6int],"?1"); INSERT INTO t6 VALUES(1,2)} 227 set TRACE_OUT {} 228 execsql {SELECT '$::t6int', [$::t6int], $::t6int, ?1, "?1", $::t6int FROM t6} 229} {{$::t6int} 1 6 6 2 6} 230do_test trace-6.6 { 231 set TRACE_OUT 232} {{SELECT '$::t6int', [$::t6int], 6, 6, "?1", 6 FROM t6}} 233 234# Do these same tests with a UTF16 database. 235# 236do_test trace-6.100 { 237 db close 238 sqlite3 db :memory: 239 db eval { 240 PRAGMA encoding=UTF16be; 241 CREATE TABLE t6([$::t6str],"?1"); 242 INSERT INTO t6 VALUES(1,2); 243 } 244 db trace trace_proc 245 set TRACE_OUT {} 246 execsql {SELECT '$::t6str', [$::t6str], $::t6str, ?1, "?1", $::t6str FROM t6} 247} {{$::t6str} 1 {test-six y'all} {test-six y'all} 2 {test-six y'all}} 248do_test trace-6.101 { 249 set TRACE_OUT 250} {{SELECT '$::t6str', [$::t6str], 'test-six y''all', 'test-six y''all', "?1", 'test-six y''all' FROM t6}} 251 252do_test trace-6.200 { 253 db close 254 sqlite3 db :memory: 255 db eval { 256 PRAGMA encoding=UTF16le; 257 CREATE TABLE t6([$::t6str],"?1"); 258 INSERT INTO t6 VALUES(1,2); 259 } 260 db trace trace_proc 261 set TRACE_OUT {} 262 execsql {SELECT '$::t6str', [$::t6str], $::t6str, ?1, "?1", $::t6str FROM t6} 263} {{$::t6str} 1 {test-six y'all} {test-six y'all} 2 {test-six y'all}} 264do_test trace-6.201 { 265 set TRACE_OUT 266} {{SELECT '$::t6str', [$::t6str], 'test-six y''all', 'test-six y''all', "?1", 'test-six y''all' FROM t6}} 267 268 269finish_test 270