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} {} 51 52# If we prepare a statement and execute it multiple times, the trace 53# happens on each execution. 54# 55db close 56sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 57do_test trace-2.1 { 58 set STMT [sqlite3_prepare $DB {INSERT INTO t1 VALUES(2,3)} -1 TAIL] 59 db trace trace_proc 60 proc trace_proc sql { 61 global TRACE_OUT 62 lappend TRACE_OUT [string trim $sql] 63 } 64 set TRACE_OUT {} 65 sqlite3_step $STMT 66 set TRACE_OUT 67} {{INSERT INTO t1 VALUES(2,3)}} 68do_test trace-2.2 { 69 set TRACE_OUT {} 70 sqlite3_reset $STMT 71 set TRACE_OUT 72} {} 73do_test trace-2.3 { 74 sqlite3_step $STMT 75 set TRACE_OUT 76} {{INSERT INTO t1 VALUES(2,3)}} 77do_test trace-2.4 { 78 set TRACE_OUT {} 79 execsql {SELECT * FROM t1} 80} {1 2 2 3 2 3} 81do_test trace-2.5 { 82 set TRACE_OUT 83} {{SELECT * FROM t1}} 84catch {sqlite3_finalize $STMT} 85 86do_test trace-2.6 { 87 set TRACE_OUT {} 88 db eval VACUUM 89 set TRACE_OUT 90} {VACUUM} 91 92# Similar tests, but this time for profiling. 93# 94do_test trace-3.1 { 95 set rc [catch {db profile 1 2 3} msg] 96 lappend rc $msg 97} {1 {wrong # args: should be "db profile ?CALLBACK?"}} 98set ::stmtlist {} 99proc profile_proc {cmd tm} { 100 lappend ::stmtlist [string trim $cmd] 101} 102do_test trace-3.2 { 103 db trace {} 104 db profile profile_proc 105 db profile 106} {profile_proc} 107do_test trace-3.3 { 108 execsql { 109 CREATE TABLE t2(a,b); 110 INSERT INTO t2 VALUES(1,2); 111 SELECT * FROM t2; 112 } 113} {1 2} 114do_test trace-3.4 { 115 set ::stmtlist 116} {{CREATE TABLE t2(a,b);} {INSERT INTO t2 VALUES(1,2);} {SELECT * FROM t2;}} 117do_test trace-3.5 { 118 db profile {} 119 db profile 120} {} 121 122# If we prepare a statement and execute it multiple times, the profile 123# happens on each execution. 124# 125db close 126sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 127do_test trace-4.1 { 128 set STMT [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL] 129 db trace trace_proc 130 proc profile_proc {sql tm} { 131 global TRACE_OUT 132 lappend TRACE_OUT [string trim $sql] 133 } 134 set TRACE_OUT {} 135 sqlite3_step $STMT 136 set TRACE_OUT 137} {{INSERT INTO t2 VALUES(2,3)}} 138do_test trace-4.2 { 139 set TRACE_OUT {} 140 sqlite3_reset $STMT 141 set TRACE_OUT 142} {} 143do_test trace-4.3 { 144 sqlite3_step $STMT 145 set TRACE_OUT 146} {{INSERT INTO t2 VALUES(2,3)}} 147do_test trace-4.4 { 148 set TRACE_OUT {} 149 execsql {SELECT * FROM t1} 150} {1 2 2 3 2 3} 151do_test trace-4.5 { 152 set TRACE_OUT 153} {{SELECT * FROM t1}} 154catch {sqlite3_finalize $STMT} 155 156# Trigger tracing. 157# 158ifcapable trigger { 159 do_test trace-5.1 { 160 db eval { 161 CREATE TRIGGER r1t1 AFTER UPDATE ON t1 BEGIN 162 UPDATE t2 SET a=new.a WHERE rowid=new.rowid; 163 END; 164 CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN 165 SELECT 'hello'; 166 END; 167 } 168 set TRACE_OUT {} 169 proc trace_proc cmd { 170 lappend ::TRACE_OUT [string trim $cmd] 171 } 172 db eval { 173 UPDATE t1 SET a=a+1; 174 } 175 set TRACE_OUT 176 } {{UPDATE t1 SET a=a+1;} {-- TRIGGER r1t1} {-- TRIGGER r1t2} {-- TRIGGER r1t1} {-- TRIGGER r1t2} {-- TRIGGER r1t1} {-- TRIGGER r1t2}} 177} 178 179# With 3.6.21, we add the ability to expand host parameters in the trace 180# output. Test this feature. 181# 182do_test trace-6.1 { 183 set ::t6int [expr {3+3}] 184 set ::t6real [expr {1.5*4.0}] 185 set ::t6str {test-six y'all} 186 db eval {SELECT x'3031323334' AS x} {set ::t6blob $x} 187 unset -nocomplain t6null 188 set TRACE_OUT {} 189 execsql {SELECT $::t6int, $::t6real, $t6str, $t6blob, $t6null} 190} {6 6.0 {test-six y'all} 01234 {}} 191do_test trace-6.2 { 192 set TRACE_OUT 193} {{SELECT 6, 6.0, 'test-six y''all', x'3031323334', NULL}} 194do_test trace-6.3 { 195 set TRACE_OUT {} 196 execsql {SELECT $::t6int, ?1, $::t6int} 197} {6 6 6} 198do_test trace-6.4 { 199 set TRACE_OUT 200} {{SELECT 6, 6, 6}} 201do_test trace-6.5 { 202 execsql {CREATE TABLE t6([$::t6int],"?1"); INSERT INTO t6 VALUES(1,2)} 203 set TRACE_OUT {} 204 execsql {SELECT '$::t6int', [$::t6int], $::t6int, ?1, "?1", $::t6int FROM t6} 205} {{$::t6int} 1 6 6 2 6} 206do_test trace-6.6 { 207 set TRACE_OUT 208} {{SELECT '$::t6int', [$::t6int], 6, 6, "?1", 6 FROM t6}} 209 210# Do these same tests with a UTF16 database. 211# 212do_test trace-6.100 { 213 db close 214 sqlite3 db :memory: 215 db eval { 216 PRAGMA encoding=UTF16be; 217 CREATE TABLE t6([$::t6str],"?1"); 218 INSERT INTO t6 VALUES(1,2); 219 } 220 db trace trace_proc 221 set TRACE_OUT {} 222 execsql {SELECT '$::t6str', [$::t6str], $::t6str, ?1, "?1", $::t6str FROM t6} 223} {{$::t6str} 1 {test-six y'all} {test-six y'all} 2 {test-six y'all}} 224do_test trace-6.101 { 225 set TRACE_OUT 226} {{SELECT '$::t6str', [$::t6str], 'test-six y''all', 'test-six y''all', "?1", 'test-six y''all' FROM t6}} 227 228do_test trace-6.200 { 229 db close 230 sqlite3 db :memory: 231 db eval { 232 PRAGMA encoding=UTF16le; 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.201 { 241 set TRACE_OUT 242} {{SELECT '$::t6str', [$::t6str], 'test-six y''all', 'test-six y''all', "?1", 'test-six y''all' FROM t6}} 243 244 245finish_test 246