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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 ifcapable !trace { 21 finish_test 22 return 23 } 24 25 set ::stmtlist {} 26 do_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?"}} 30 proc trace_proc cmd { 31 lappend ::stmtlist [string trim $cmd] 32 } 33 do_test trace-1.2 { 34 db trace trace_proc 35 db trace 36 } {trace_proc} 37 do_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} 44 do_test trace-1.4 { 45 set ::stmtlist 46 } {{CREATE TABLE t1(a,b);} {INSERT INTO t1 VALUES(1,2);} {SELECT * FROM t1;}} 47 do_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 # 55 db close 56 sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 57 do_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)}} 68 do_test trace-2.2 { 69 set TRACE_OUT {} 70 sqlite3_reset $STMT 71 set TRACE_OUT 72 } {} 73 do_test trace-2.3 { 74 sqlite3_step $STMT 75 set TRACE_OUT 76 } {{INSERT INTO t1 VALUES(2,3)}} 77 do_test trace-2.4 { 78 set TRACE_OUT {} 79 execsql {SELECT * FROM t1} 80 } {1 2 2 3 2 3} 81 do_test trace-2.5 { 82 set TRACE_OUT 83 } {{SELECT * FROM t1}} 84 catch {sqlite3_finalize $STMT} 85 86 do_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 # 94 do_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?"}} 98 set ::stmtlist {} 99 proc profile_proc {cmd tm} { 100 lappend ::stmtlist [string trim $cmd] 101 } 102 do_test trace-3.2 { 103 db trace {} 104 db profile profile_proc 105 db profile 106 } {profile_proc} 107 do_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} 114 do_test trace-3.4 { 115 set ::stmtlist 116 } {{CREATE TABLE t2(a,b);} {INSERT INTO t2 VALUES(1,2);} {SELECT * FROM t2;}} 117 do_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 # 125 db close 126 sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 127 do_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)}} 138 do_test trace-4.2 { 139 set TRACE_OUT {} 140 sqlite3_reset $STMT 141 set TRACE_OUT 142 } {} 143 do_test trace-4.3 { 144 sqlite3_step $STMT 145 set TRACE_OUT 146 } {{INSERT INTO t2 VALUES(2,3)}} 147 do_test trace-4.4 { 148 set TRACE_OUT {} 149 execsql {SELECT * FROM t1} 150 } {1 2 2 3 2 3} 151 do_test trace-4.5 { 152 set TRACE_OUT 153 } {{SELECT * FROM t1}} 154 catch {sqlite3_finalize $STMT} 155 156 # Trigger tracing. 157 # 158 ifcapable 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 # 182 do_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 {}} 191 do_test trace-6.2 { 192 set TRACE_OUT 193 } {{SELECT 6, 6.0, 'test-six y''all', x'3031323334', NULL}} 194 do_test trace-6.3 { 195 set TRACE_OUT {} 196 execsql {SELECT $::t6int, ?1, $::t6int} 197 } {6 6 6} 198 do_test trace-6.4 { 199 set TRACE_OUT 200 } {{SELECT 6, 6, 6}} 201 do_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} 206 do_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 # 212 do_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}} 224 do_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 228 do_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}} 240 do_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 245 finish_test 246
