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# Trigger tracing. 173# 174ifcapable trigger { 175 do_test trace-5.1 { 176 db eval { 177 CREATE TRIGGER r1t1 AFTER UPDATE ON t1 BEGIN 178 UPDATE t2 SET a=new.a WHERE rowid=new.rowid; 179 END; 180 CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN 181 SELECT 'hello'; 182 END; 183 } 184 set TRACE_OUT {} 185 proc trace_proc cmd { 186 lappend ::TRACE_OUT [string trim $cmd] 187 } 188 db eval { 189 UPDATE t1 SET a=a+1; 190 } 191 set TRACE_OUT 192 } {{UPDATE t1 SET a=a+1;} {-- TRIGGER r1t1} {-- TRIGGER r1t2} {-- TRIGGER r1t1} {-- TRIGGER r1t2} {-- TRIGGER r1t1} {-- TRIGGER r1t2}} 193} 194 195# With 3.6.21, we add the ability to expand host parameters in the trace 196# output. Test this feature. 197# 198do_test trace-6.1 { 199 set ::t6int [expr {3+3}] 200 set ::t6real [expr {1.5*4.0}] 201 set ::t6str {test-six y'all} 202 db eval {SELECT x'3031323334' AS x} {set ::t6blob $x} 203 unset -nocomplain t6null 204 set TRACE_OUT {} 205 execsql {SELECT $::t6int, $::t6real, $t6str, $t6blob, $t6null} 206} {6 6.0 {test-six y'all} 01234 {}} 207do_test trace-6.2 { 208 set TRACE_OUT 209} {{SELECT 6, 6.0, 'test-six y''all', x'3031323334', NULL}} 210do_test trace-6.3 { 211 set TRACE_OUT {} 212 execsql {SELECT $::t6int, ?1, $::t6int} 213} {6 6 6} 214do_test trace-6.4 { 215 set TRACE_OUT 216} {{SELECT 6, 6, 6}} 217do_test trace-6.5 { 218 execsql {CREATE TABLE t6([$::t6int],"?1"); INSERT INTO t6 VALUES(1,2)} 219 set TRACE_OUT {} 220 execsql {SELECT '$::t6int', [$::t6int], $::t6int, ?1, "?1", $::t6int FROM t6} 221} {{$::t6int} 1 6 6 2 6} 222do_test trace-6.6 { 223 set TRACE_OUT 224} {{SELECT '$::t6int', [$::t6int], 6, 6, "?1", 6 FROM t6}} 225 226# Do these same tests with a UTF16 database. 227# 228do_test trace-6.100 { 229 db close 230 sqlite3 db :memory: 231 db eval { 232 PRAGMA encoding=UTF16be; 233 CREATE TABLE t6([$::t6str],"?1"); 234 INSERT INTO t6 VALUES(1,2); 235 } 236 db trace trace_proc 237 set TRACE_OUT {} 238 execsql {SELECT '$::t6str', [$::t6str], $::t6str, ?1, "?1", $::t6str FROM t6} 239} {{$::t6str} 1 {test-six y'all} {test-six y'all} 2 {test-six y'all}} 240do_test trace-6.101 { 241 set TRACE_OUT 242} {{SELECT '$::t6str', [$::t6str], 'test-six y''all', 'test-six y''all', "?1", 'test-six y''all' FROM t6}} 243 244do_test trace-6.200 { 245 db close 246 sqlite3 db :memory: 247 db eval { 248 PRAGMA encoding=UTF16le; 249 CREATE TABLE t6([$::t6str],"?1"); 250 INSERT INTO t6 VALUES(1,2); 251 } 252 db trace trace_proc 253 set TRACE_OUT {} 254 execsql {SELECT '$::t6str', [$::t6str], $::t6str, ?1, "?1", $::t6str FROM t6} 255} {{$::t6str} 1 {test-six y'all} {test-six y'all} 2 {test-six y'all}} 256do_test trace-6.201 { 257 set TRACE_OUT 258} {{SELECT '$::t6str', [$::t6str], 'test-six y''all', 'test-six y''all', "?1", 'test-six y''all' FROM t6}} 259 260 261finish_test 262