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