/ Check-in [a764915b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Modify the trace callback mechanism so that SQL commands executed from within virtual table or user function callbacks are passed to the trace callback without parameter expansion and enclosed in SQL comments.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a764915b87564fa91ee68e9b1f41394ce0f1fc7e
User & Date: dan 2011-01-22 13:32:30
Context
2011-01-24
19:14
Fix a harmless compiler warning (a shadowed local variable) in analyze.c. check-in: a1ad7fb3 user: drh tags: trunk
16:00
Ensure that if a deferred FK constraint is violated by a statement that creates its own implicit transaction, the statement is not an "active-write" after sqlite3_step() returns. Closed-Leaf check-in: 8063197e user: dan tags: deferred-fk-quirk
2011-01-22
13:32
Modify the trace callback mechanism so that SQL commands executed from within virtual table or user function callbacks are passed to the trace callback without parameter expansion and enclosed in SQL comments. check-in: a764915b user: dan tags: trunk
2011-01-21
18:25
Change sqlite3StrAccumAppend() to use realloc instead of malloc. check-in: 380f61df user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

install-sh became executable.


Changes to src/sqliteInt.h.

   812    812       u8 orphanTrigger;           /* Last statement is orphaned TEMP trigger */
   813    813     } init;
   814    814     int nExtension;               /* Number of loaded extensions */
   815    815     void **aExtension;            /* Array of shared library handles */
   816    816     struct Vdbe *pVdbe;           /* List of active virtual machines */
   817    817     int activeVdbeCnt;            /* Number of VDBEs currently executing */
   818    818     int writeVdbeCnt;             /* Number of active VDBEs that are writing */
          819  +  int vdbeExecCnt;              /* Number of nested calls to VdbeExec() */
   819    820     void (*xTrace)(void*,const char*);        /* Trace function */
   820    821     void *pTraceArg;                          /* Argument to the trace function */
   821    822     void (*xProfile)(void*,const char*,u64);  /* Profiling function */
   822    823     void *pProfileArg;                        /* Argument to profile function */
   823    824     void *pCommitArg;                 /* Argument to xCommitCallback() */   
   824    825     int (*xCommitCallback)(void*);    /* Invoked at every commit. */
   825    826     void *pRollbackArg;               /* Argument to xRollbackCallback() */   

Changes to src/vdbeapi.c.

   406    406     }
   407    407   #ifndef SQLITE_OMIT_EXPLAIN
   408    408     if( p->explain ){
   409    409       rc = sqlite3VdbeList(p);
   410    410     }else
   411    411   #endif /* SQLITE_OMIT_EXPLAIN */
   412    412     {
          413  +    db->vdbeExecCnt++;
   413    414       rc = sqlite3VdbeExec(p);
          415  +    db->vdbeExecCnt--;
   414    416     }
   415    417   
   416    418   #ifndef SQLITE_OMIT_TRACE
   417    419     /* Invoke the profile callback if there is one
   418    420     */
   419    421     if( rc!=SQLITE_ROW && db->xProfile && !db->init.busy && p->zSql ){
   420    422       sqlite3_int64 iNow;

Changes to src/vdbetrace.c.

    40     40       nTotal += n;
    41     41       zSql += n;
    42     42     }
    43     43     return nTotal;
    44     44   }
    45     45   
    46     46   /*
    47         -** Return a pointer to a string in memory obtained form sqlite3DbMalloc() which
    48         -** holds a copy of zRawSql but with host parameters expanded to their
    49         -** current bindings.
           47  +** This function returns a pointer to a nul-terminated string in memory
           48  +** obtained from sqlite3DbMalloc(). If sqlite3.vdbeExecCnt is 1, then the
           49  +** string contains a copy of zRawSql but with host parameters expanded to 
           50  +** their current bindings. Or, if sqlite3.vdbeExecCnt is greater than 1, 
           51  +** then the returned string holds a copy of zRawSql with "-- " prepended
           52  +** to each line of text.
    50     53   **
    51     54   ** The calling function is responsible for making sure the memory returned
    52     55   ** is eventually freed.
    53     56   **
    54     57   ** ALGORITHM:  Scan the input string looking for host parameters in any of
    55     58   ** these forms:  ?, ?N, $A, @A, :A.  Take care to avoid text within
    56     59   ** string literals, quoted identifier names, and comments.  For text forms,
................................................................................
    73     76     StrAccum out;            /* Accumulate the output here */
    74     77     char zBase[100];         /* Initial working space */
    75     78   
    76     79     db = p->db;
    77     80     sqlite3StrAccumInit(&out, zBase, sizeof(zBase), 
    78     81                         db->aLimit[SQLITE_LIMIT_LENGTH]);
    79     82     out.db = db;
    80         -  while( zRawSql[0] ){
    81         -    n = findNextHostParameter(zRawSql, &nToken);
    82         -    assert( n>0 );
    83         -    sqlite3StrAccumAppend(&out, zRawSql, n);
    84         -    zRawSql += n;
    85         -    assert( zRawSql[0] || nToken==0 );
    86         -    if( nToken==0 ) break;
    87         -    if( zRawSql[0]=='?' ){
    88         -      if( nToken>1 ){
    89         -        assert( sqlite3Isdigit(zRawSql[1]) );
    90         -        sqlite3GetInt32(&zRawSql[1], &idx);
           83  +  if( db->vdbeExecCnt>1 ){
           84  +    while( *zRawSql ){
           85  +      const char *zStart = zRawSql;
           86  +      while( *(zRawSql++)!='\n' && *zRawSql );
           87  +      sqlite3StrAccumAppend(&out, "-- ", 3);
           88  +      sqlite3StrAccumAppend(&out, zStart, zRawSql-zStart);
           89  +    }
           90  +  }else{
           91  +    while( zRawSql[0] ){
           92  +      n = findNextHostParameter(zRawSql, &nToken);
           93  +      assert( n>0 );
           94  +      sqlite3StrAccumAppend(&out, zRawSql, n);
           95  +      zRawSql += n;
           96  +      assert( zRawSql[0] || nToken==0 );
           97  +      if( nToken==0 ) break;
           98  +      if( zRawSql[0]=='?' ){
           99  +        if( nToken>1 ){
          100  +          assert( sqlite3Isdigit(zRawSql[1]) );
          101  +          sqlite3GetInt32(&zRawSql[1], &idx);
          102  +        }else{
          103  +          idx = nextIndex;
          104  +        }
    91    105         }else{
    92         -        idx = nextIndex;
          106  +        assert( zRawSql[0]==':' || zRawSql[0]=='$' || zRawSql[0]=='@' );
          107  +        testcase( zRawSql[0]==':' );
          108  +        testcase( zRawSql[0]=='$' );
          109  +        testcase( zRawSql[0]=='@' );
          110  +        idx = sqlite3VdbeParameterIndex(p, zRawSql, nToken);
          111  +        assert( idx>0 );
    93    112         }
    94         -    }else{
    95         -      assert( zRawSql[0]==':' || zRawSql[0]=='$' || zRawSql[0]=='@' );
    96         -      testcase( zRawSql[0]==':' );
    97         -      testcase( zRawSql[0]=='$' );
    98         -      testcase( zRawSql[0]=='@' );
    99         -      idx = sqlite3VdbeParameterIndex(p, zRawSql, nToken);
   100         -      assert( idx>0 );
   101         -    }
   102         -    zRawSql += nToken;
   103         -    nextIndex = idx + 1;
   104         -    assert( idx>0 && idx<=p->nVar );
   105         -    pVar = &p->aVar[idx-1];
   106         -    if( pVar->flags & MEM_Null ){
   107         -      sqlite3StrAccumAppend(&out, "NULL", 4);
   108         -    }else if( pVar->flags & MEM_Int ){
   109         -      sqlite3XPrintf(&out, "%lld", pVar->u.i);
   110         -    }else if( pVar->flags & MEM_Real ){
   111         -      sqlite3XPrintf(&out, "%!.15g", pVar->r);
   112         -    }else if( pVar->flags & MEM_Str ){
          113  +      zRawSql += nToken;
          114  +      nextIndex = idx + 1;
          115  +      assert( idx>0 && idx<=p->nVar );
          116  +      pVar = &p->aVar[idx-1];
          117  +      if( pVar->flags & MEM_Null ){
          118  +        sqlite3StrAccumAppend(&out, "NULL", 4);
          119  +      }else if( pVar->flags & MEM_Int ){
          120  +        sqlite3XPrintf(&out, "%lld", pVar->u.i);
          121  +      }else if( pVar->flags & MEM_Real ){
          122  +        sqlite3XPrintf(&out, "%!.15g", pVar->r);
          123  +      }else if( pVar->flags & MEM_Str ){
   113    124   #ifndef SQLITE_OMIT_UTF16
   114         -      u8 enc = ENC(db);
   115         -      if( enc!=SQLITE_UTF8 ){
   116         -        Mem utf8;
   117         -        memset(&utf8, 0, sizeof(utf8));
   118         -        utf8.db = db;
   119         -        sqlite3VdbeMemSetStr(&utf8, pVar->z, pVar->n, enc, SQLITE_STATIC);
   120         -        sqlite3VdbeChangeEncoding(&utf8, SQLITE_UTF8);
   121         -        sqlite3XPrintf(&out, "'%.*q'", utf8.n, utf8.z);
   122         -        sqlite3VdbeMemRelease(&utf8);
   123         -      }else
          125  +        u8 enc = ENC(db);
          126  +        if( enc!=SQLITE_UTF8 ){
          127  +          Mem utf8;
          128  +          memset(&utf8, 0, sizeof(utf8));
          129  +          utf8.db = db;
          130  +          sqlite3VdbeMemSetStr(&utf8, pVar->z, pVar->n, enc, SQLITE_STATIC);
          131  +          sqlite3VdbeChangeEncoding(&utf8, SQLITE_UTF8);
          132  +          sqlite3XPrintf(&out, "'%.*q'", utf8.n, utf8.z);
          133  +          sqlite3VdbeMemRelease(&utf8);
          134  +        }else
   124    135   #endif
   125         -      {
   126         -        sqlite3XPrintf(&out, "'%.*q'", pVar->n, pVar->z);
          136  +        {
          137  +          sqlite3XPrintf(&out, "'%.*q'", pVar->n, pVar->z);
          138  +        }
          139  +      }else if( pVar->flags & MEM_Zero ){
          140  +        sqlite3XPrintf(&out, "zeroblob(%d)", pVar->u.nZero);
          141  +      }else{
          142  +        assert( pVar->flags & MEM_Blob );
          143  +        sqlite3StrAccumAppend(&out, "x'", 2);
          144  +        for(i=0; i<pVar->n; i++){
          145  +          sqlite3XPrintf(&out, "%02x", pVar->z[i]&0xff);
          146  +        }
          147  +        sqlite3StrAccumAppend(&out, "'", 1);
   127    148         }
   128         -    }else if( pVar->flags & MEM_Zero ){
   129         -      sqlite3XPrintf(&out, "zeroblob(%d)", pVar->u.nZero);
   130         -    }else{
   131         -      assert( pVar->flags & MEM_Blob );
   132         -      sqlite3StrAccumAppend(&out, "x'", 2);
   133         -      for(i=0; i<pVar->n; i++){
   134         -        sqlite3XPrintf(&out, "%02x", pVar->z[i]&0xff);
   135         -      }
   136         -      sqlite3StrAccumAppend(&out, "'", 1);
   137    149       }
   138    150     }
   139    151     return sqlite3StrAccumFinish(&out);
   140    152   }
   141    153   
   142    154   #endif /* #ifndef SQLITE_OMIT_TRACE */

Added test/trace2.test.

            1  +# 2011 Jan 21
            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. Specifically,
           14  +# it tests the special handling of nested SQL statements (those executed
           15  +# by virtual table or user function callbacks). These statements are treated
           16  +# differently in two respects:
           17  +#
           18  +#   1. Each line of the statement is prefixed with "-- " to turn it into
           19  +#      an SQL comment.
           20  +#
           21  +#   2. Parameter expansion is not performed.
           22  +#
           23  +
           24  +set testdir [file dirname $argv0]
           25  +source $testdir/tester.tcl
           26  +ifcapable !trace { finish_test ; return }
           27  +set ::testprefix trace2
           28  +
           29  +proc sql {zSql} { db one $zSql }
           30  +proc trace {zSql} { lappend ::trace $zSql }
           31  +
           32  +db func sql sql
           33  +db trace trace
           34  +
           35  +proc do_trace_test {tn sql expected} {
           36  +  # Test that the list of string passed to the trace callback when $sql
           37  +  # is executed is equivalent to the list of strings in $expected.
           38  +  #
           39  +  set ::trace [list]
           40  +  execsql $sql
           41  +  uplevel do_test $tn [list {set ::trace}] [list [list {*}$expected]]
           42  +}
           43  +
           44  +proc do_trace_select_test {tn sql expected} {
           45  +
           46  +  uplevel [list do_trace_test ${tn}.a $sql $expected]
           47  +
           48  +  # Now execute each SQL statement passed to the trace callback in the
           49  +  # block above. Check that this causes the same set of strings to be
           50  +  # passed to the trace callback again. i.e. that executing the output
           51  +  # of the trace callback is equivalent to the SQL script in $sql.
           52  +  #
           53  +  set sqllist $::trace
           54  +  set ::trace [list]
           55  +  foreach item $sqllist { execsql $item }
           56  +  uplevel do_test $tn.b [list {set ::trace}] [list $sqllist]
           57  +}
           58  +
           59  +do_trace_select_test 1.1  {
           60  +  SELECT 1, 2, 3;
           61  +} {
           62  +  "SELECT 1, 2, 3;"
           63  +}
           64  +
           65  +do_trace_select_test 1.2  {
           66  +  SELECT sql('SELECT 1, 2, 3');
           67  +} {
           68  +  "SELECT sql('SELECT 1, 2, 3');"
           69  +  "-- SELECT 1, 2, 3"
           70  +}
           71  +
           72  +do_trace_select_test 1.3  {
           73  +  SELECT sql('SELECT 1, 
           74  +    2, 
           75  +    3'
           76  +  );
           77  +} {
           78  +  "SELECT sql('SELECT 1, 
           79  +    2, 
           80  +    3'
           81  +  );"
           82  +  "-- SELECT 1, 
           83  +--     2, 
           84  +--     3"
           85  +}
           86  +
           87  +do_trace_select_test 1.4  {
           88  +  SELECT sql('SELECT 1, 
           89  +
           90  +
           91  +    3'
           92  +  );
           93  +} {
           94  +  "SELECT sql('SELECT 1, 
           95  +
           96  +
           97  +    3'
           98  +  );"
           99  +  "-- SELECT 1, 
          100  +-- 
          101  +-- 
          102  +--     3"
          103  +}
          104  +
          105  +do_trace_select_test 1.5  {
          106  +  SELECT $var, sql('SELECT 1, 
          107  +    $var, 
          108  +    3'
          109  +  );
          110  +} {
          111  +  "SELECT NULL, sql('SELECT 1, 
          112  +    $var, 
          113  +    3'
          114  +  );"
          115  +  "-- SELECT 1, 
          116  +--     $var, 
          117  +--     3"
          118  +}
          119  +
          120  +ifcapable fts3 {
          121  +  do_execsql_test 2.1 {
          122  +    CREATE VIRTUAL TABLE x1 USING fts4;
          123  +    INSERT INTO x1 VALUES('Cloudy, with a high near 16');
          124  +    INSERT INTO x1 VALUES('Wind chill values as low as -13');
          125  +  }
          126  +
          127  +  do_trace_test 2.2 {
          128  +    INSERT INTO x1 VALUES('North northwest wind between 8 and 14 mph');
          129  +  } {
          130  +    "INSERT INTO x1 VALUES('North northwest wind between 8 and 14 mph');" 
          131  +    "-- INSERT INTO 'main'.'x1_content' VALUES(?,?)" 
          132  +    "-- REPLACE INTO 'main'.'x1_docsize' VALUES(?,?)" 
          133  +    "-- SELECT value FROM 'main'.'x1_stat' WHERE id=0" 
          134  +    "-- REPLACE INTO 'main'.'x1_stat' VALUES(0,?)" 
          135  +    "-- SELECT (SELECT max(idx) FROM 'main'.'x1_segdir' WHERE level = ?) + 1" 
          136  +    "-- SELECT coalesce((SELECT max(blockid) FROM 'main'.'x1_segments') + 1, 1)"
          137  +    "-- INSERT INTO 'main'.'x1_segdir' VALUES(?,?,?,?,?,?)"
          138  +  }
          139  +
          140  +  do_trace_test 2.3 {
          141  +    INSERT INTO x1(x1) VALUES('optimize');
          142  +  } {
          143  +    "INSERT INTO x1(x1) VALUES('optimize');"
          144  +    "-- SELECT count(*), max(level) FROM 'main'.'x1_segdir'"
          145  +    "-- SELECT idx, start_block, leaves_end_block, end_block, root FROM 'main'.'x1_segdir' ORDER BY level DESC, idx ASC"
          146  +    "-- SELECT coalesce((SELECT max(blockid) FROM 'main'.'x1_segments') + 1, 1)"
          147  +    "-- DELETE FROM 'main'.'x1_segdir'"
          148  +    "-- INSERT INTO 'main'.'x1_segdir' VALUES(?,?,?,?,?,?)"
          149  +  }
          150  +}
          151  +
          152  +finish_test