/ Check-in [7da617e9]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Enhance the sqlite3_normalize_sql() interface so that it works even if the prepared statement was not initially compiled using SQLITE_PREPARE_NORMALIZED. Enhance the ".trace" command in the CLI so that it is able to access the full scope of functionality provided by sqlite3_trace_v2() and in particular so that it is able to show normalized SQL output using the newly enhanced sqlite3_normalize_sql() interface.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 7da617e97eb905cb009c47403786682b911e32a630f266e1c53ea72836fc88b5
User & Date: drh 2018-12-05 13:39:06
Context
2018-12-05
13:49
Ensure that ALTER TABLE modifies table and column names embedded in WITH clauses that are part of views and triggers. check-in: f44bc7a8 user: dan tags: trunk
13:44
Merge enhancements from trunk, especially the enhanced sqlite3_normalized_sql() interface. check-in: 47b73f6b user: drh tags: begin-concurrent
13:39
Enhance the sqlite3_normalize_sql() interface so that it works even if the prepared statement was not initially compiled using SQLITE_PREPARE_NORMALIZED. Enhance the ".trace" command in the CLI so that it is able to access the full scope of functionality provided by sqlite3_trace_v2() and in particular so that it is able to show normalized SQL output using the newly enhanced sqlite3_normalize_sql() interface. check-in: 7da617e9 user: drh tags: trunk
2018-12-04
16:51
Add the "index_usage" utility program. check-in: df954552 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/hash.c.

68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
static unsigned int strHashN(const char *z, int n){
  unsigned int h = 0;
  int i;
  for(i=0; i<n; i++){
    /* Knuth multiplicative hashing.  (Sorting & Searching, p. 510).
    ** 0x9e3779b1 is 2654435761 which is the closest prime number to
    ** (2**32)*golden_ratio, where golden_ratio = (sqrt(5) - 1)/2. */
    h += sqlite3UpperToLower[z[i]];
    h *= 0x9e3779b1;
  }
  return h;
}
#endif /* SQLITE_ENABLE_NORMALIZE */









|







68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
static unsigned int strHashN(const char *z, int n){
  unsigned int h = 0;
  int i;
  for(i=0; i<n; i++){
    /* Knuth multiplicative hashing.  (Sorting & Searching, p. 510).
    ** 0x9e3779b1 is 2654435761 which is the closest prime number to
    ** (2**32)*golden_ratio, where golden_ratio = (sqrt(5) - 1)/2. */
    h += sqlite3UpperToLower[(unsigned char)z[i]];
    h *= 0x9e3779b1;
  }
  return h;
}
#endif /* SQLITE_ENABLE_NORMALIZE */


Changes to src/hash.h.

64
65
66
67
68
69
70



71
72
73
74
75
76
77

/*
** Access routines.  To delete, insert a NULL pointer.
*/
void sqlite3HashInit(Hash*);
void *sqlite3HashInsert(Hash*, const char *pKey, void *pData);
void *sqlite3HashFind(const Hash*, const char *pKey);



void sqlite3HashClear(Hash*);

/*
** Macros for looping over all elements of a hash table.  The idiom is
** like this:
**
**   Hash h;







>
>
>







64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

/*
** Access routines.  To delete, insert a NULL pointer.
*/
void sqlite3HashInit(Hash*);
void *sqlite3HashInsert(Hash*, const char *pKey, void *pData);
void *sqlite3HashFind(const Hash*, const char *pKey);
#ifdef SQLITE_ENABLE_NORMALIZE
void *sqlite3HashFindN(const Hash *pH, const char *pKey, int nKey);
#endif
void sqlite3HashClear(Hash*);

/*
** Macros for looping over all elements of a hash table.  The idiom is
** like this:
**
**   Hash h;

Changes to src/prepare.c.

787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
...
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
...
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
...
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
...
988
989
990
991
992
993
994
995




996

997
998
999
1000
1001
1002
1003
** normalized version of the specified SQL string.  This should take into
** account any potential expansion that could occur (e.g. via IN clauses
** being expanded, etc).  This size returned is the total number of bytes
** including the NUL terminator.
*/
static int estimateNormalizedSize(
  const char *zSql, /* The original SQL string */
  int nSql,         /* Length of original SQL string */
  u8 prepFlags      /* The flags passed to sqlite3_prepare_v3() */
){
  int nOut = nSql + 4;
  const char *z = zSql;
  while( nOut<nSql*5 ){
    while( z[0]!=0 && z[0]!='I' && z[0]!='i' ){ z++; }
    if( z[0]==0 ) break;
    z++;
................................................................................
      zOut[j++] = sqlite3Tolower(zSql[iIn+k]);
    }
  }
  *piOut = j;
}

/*
** Perform normalization of the SQL contained in the prepared statement and
** store the result in the zNormSql field.  The schema for the associated
** databases are consulted while performing the normalization in order to
** determine if a token appears to be an identifier.  All identifiers are
** left intact in the normalized SQL and all literals are replaced with a
** single '?'.
*/
void sqlite3Normalize(
  Vdbe *pVdbe,      /* VM being reprepared */
  const char *zSql, /* The original SQL string */
  int nSql,         /* Size of the input string in bytes */
  u8 prepFlags      /* The flags passed to sqlite3_prepare_v3() */
){
  sqlite3 *db;           /* Database handle. */
  char *z;               /* The output string */
  int nZ;                /* Size of the output string in bytes */
  int i;                 /* Next character to read from zSql[] */
  int j;                 /* Next character to fill in on z[] */
  int tokenType = 0;     /* Type of the next token */
................................................................................
  int prevTokenType = 0; /* Type of the previous token, except spaces */
  int n;                 /* Size of the next token */
  int nParen = 0;        /* Nesting level of parenthesis */
  Hash inHash;           /* Table of parenthesis levels to output index. */

  db = sqlite3VdbeDb(pVdbe);
  assert( db!=0 );
  assert( pVdbe->zNormSql==0 );
  if( zSql==0 ) return;
  nZ = estimateNormalizedSize(zSql, nSql, prepFlags);
  z = sqlite3DbMallocRawNN(db, nZ);
  if( z==0 ) return;
  sqlite3HashInit(&inHash);
  for(i=j=0; i<nSql && zSql[i]; i+=n){
    int flags = 0;
    if( tokenType!=TK_SPACE ) prevTokenType = tokenType;
    n = sqlite3GetTokenNormalized((unsigned char*)zSql+i, &tokenType, &flags);
    switch( tokenType ){
      case TK_SPACE: {
        break;
      }
      case TK_ILLEGAL: {
        sqlite3DbFree(db, z);
        sqlite3HashClear(&inHash);
        return;
      }
      case TK_STRING:
      case TK_INTEGER:
      case TK_FLOAT:
      case TK_VARIABLE:
      case TK_BLOB: {
        z[j++] = '?';
................................................................................
          int i2 = i, n2 = n, rc = SQLITE_OK;
          if( nParen>0 ){
            assert( nParen<nSql );
            sqlite3HashInsert(&inHash, zSql+nParen, 0);
          }
          if( flags&SQLITE_TOKEN_QUOTED ){ i2++; n2-=2; }
          if( shouldTreatAsIdentifier(db, zSql+i2, n2, &rc)==0 ){
            if( rc!=SQLITE_OK ){
              sqlite3DbFree(db, z);
              sqlite3HashClear(&inHash);
              return;
            }
            if( sqlite3_keyword_check(zSql+i2, n2)==0 ){
              z[j++] = '?';
              break;
            }
          }
        }
        copyNormalizedToken(zSql, i, n, flags, z, &j);
................................................................................
    }
  }
  assert( j<nZ && "one" );
  while( j>0 && z[j-1]==' ' ){ j--; }
  if( j>0 && z[j-1]!=';' ){ z[j++] = ';'; }
  z[j] = 0;
  assert( j<nZ && "two" );
  pVdbe->zNormSql = z;




  sqlite3HashClear(&inHash);

}
#endif /* SQLITE_ENABLE_NORMALIZE */

/*
** Rerun the compilation of a statement after a schema change.
**
** If the statement is successfully recompiled, return SQLITE_OK. Otherwise,







|
<







 







|
|
|
<
<
<

|


|
<







 







<
|
|

|










|
<
<







 







|
<
<
<
<







 







|
>
>
>
>

>







787
788
789
790
791
792
793
794

795
796
797
798
799
800
801
...
842
843
844
845
846
847
848
849
850
851



852
853
854
855
856

857
858
859
860
861
862
863
...
864
865
866
867
868
869
870

871
872
873
874
875
876
877
878
879
880
881
882
883
884
885


886
887
888
889
890
891
892
...
959
960
961
962
963
964
965
966




967
968
969
970
971
972
973
...
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
** normalized version of the specified SQL string.  This should take into
** account any potential expansion that could occur (e.g. via IN clauses
** being expanded, etc).  This size returned is the total number of bytes
** including the NUL terminator.
*/
static int estimateNormalizedSize(
  const char *zSql, /* The original SQL string */
  int nSql          /* Length of original SQL string */

){
  int nOut = nSql + 4;
  const char *z = zSql;
  while( nOut<nSql*5 ){
    while( z[0]!=0 && z[0]!='I' && z[0]!='i' ){ z++; }
    if( z[0]==0 ) break;
    z++;
................................................................................
      zOut[j++] = sqlite3Tolower(zSql[iIn+k]);
    }
  }
  *piOut = j;
}

/*
** Compute a normalization of the SQL given by zSql[0..nSql-1].  Return
** the normalization in space obtained from sqlite3DbMalloc().  Or return
** NULL if anything goes wrong or if zSql is NULL.



*/
char *sqlite3Normalize(
  Vdbe *pVdbe,      /* VM being reprepared */
  const char *zSql, /* The original SQL string */
  int nSql          /* Size of the input string in bytes */

){
  sqlite3 *db;           /* Database handle. */
  char *z;               /* The output string */
  int nZ;                /* Size of the output string in bytes */
  int i;                 /* Next character to read from zSql[] */
  int j;                 /* Next character to fill in on z[] */
  int tokenType = 0;     /* Type of the next token */
................................................................................
  int prevTokenType = 0; /* Type of the previous token, except spaces */
  int n;                 /* Size of the next token */
  int nParen = 0;        /* Nesting level of parenthesis */
  Hash inHash;           /* Table of parenthesis levels to output index. */

  db = sqlite3VdbeDb(pVdbe);
  assert( db!=0 );

  if( zSql==0 ) return 0;
  nZ = estimateNormalizedSize(zSql, nSql);
  z = sqlite3DbMallocRawNN(db, nZ);
  if( z==0 ) goto normalizeError;
  sqlite3HashInit(&inHash);
  for(i=j=0; i<nSql && zSql[i]; i+=n){
    int flags = 0;
    if( tokenType!=TK_SPACE ) prevTokenType = tokenType;
    n = sqlite3GetTokenNormalized((unsigned char*)zSql+i, &tokenType, &flags);
    switch( tokenType ){
      case TK_SPACE: {
        break;
      }
      case TK_ILLEGAL: {
        goto normalizeError;


      }
      case TK_STRING:
      case TK_INTEGER:
      case TK_FLOAT:
      case TK_VARIABLE:
      case TK_BLOB: {
        z[j++] = '?';
................................................................................
          int i2 = i, n2 = n, rc = SQLITE_OK;
          if( nParen>0 ){
            assert( nParen<nSql );
            sqlite3HashInsert(&inHash, zSql+nParen, 0);
          }
          if( flags&SQLITE_TOKEN_QUOTED ){ i2++; n2-=2; }
          if( shouldTreatAsIdentifier(db, zSql+i2, n2, &rc)==0 ){
            if( rc!=SQLITE_OK ) goto normalizeError;




            if( sqlite3_keyword_check(zSql+i2, n2)==0 ){
              z[j++] = '?';
              break;
            }
          }
        }
        copyNormalizedToken(zSql, i, n, flags, z, &j);
................................................................................
    }
  }
  assert( j<nZ && "one" );
  while( j>0 && z[j-1]==' ' ){ j--; }
  if( j>0 && z[j-1]!=';' ){ z[j++] = ';'; }
  z[j] = 0;
  assert( j<nZ && "two" );
  sqlite3HashClear(&inHash);
  return z;

normalizeError:
  sqlite3DbFree(db, z);
  sqlite3HashClear(&inHash);
  return 0;
}
#endif /* SQLITE_ENABLE_NORMALIZE */

/*
** Rerun the compilation of a statement after a schema change.
**
** If the statement is successfully recompiled, return SQLITE_OK. Otherwise,

Changes to src/shell.c.in.

1003
1004
1005
1006
1007
1008
1009

1010
1011
1012
1013
1014
1015
1016
....
1062
1063
1064
1065
1066
1067
1068






1069
1070
1071
1072
1073
1074
1075
....
3488
3489
3490
3491
3492
3493
3494

3495














3496
3497
3498
3499
3500
3501
3502
....
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004
4005
4006
4007
4008
4009
4010
4011
4012
4013
4014
4015






4016




















4017
4018
4019












4020
4021
4022
4023
4024
4025
4026
....
7834
7835
7836
7837
7838
7839
7840

7841


7842
7843



























7844
7845
7846
7847

7848
7849
7850


7851
7852
7853

7854
7855
7856
7857

7858
7859
7860
7861
7862
7863
7864
  u8 autoEQP;            /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */
  u8 autoEQPtest;        /* autoEQP is in test mode */
  u8 statsOn;            /* True to display memory stats before each finalize */
  u8 scanstatsOn;        /* True to display scan stats before each finalize */
  u8 openMode;           /* SHELL_OPEN_NORMAL, _APPENDVFS, or _ZIPFILE */
  u8 doXdgOpen;          /* Invoke start/open/xdg-open in output_reset() */
  u8 nEqpLevel;          /* Depth of the EQP output graph */

  unsigned mEqpLines;    /* Mask of veritical lines in the EQP output graph */
  int outCount;          /* Revert to stdout when reaching zero */
  int cnt;               /* Number of records displayed so far */
  FILE *out;             /* Write results here */
  FILE *traceOut;        /* Output for sqlite3_trace() */
  int nErr;              /* Number of errors seen */
  int mode;              /* An output mode setting */
................................................................................
#define SHELL_OPEN_UNSPEC      0      /* No open-mode specified */
#define SHELL_OPEN_NORMAL      1      /* Normal database file */
#define SHELL_OPEN_APPENDVFS   2      /* Use appendvfs */
#define SHELL_OPEN_ZIPFILE     3      /* Use the zipfile virtual table */
#define SHELL_OPEN_READONLY    4      /* Open a normal database read-only */
#define SHELL_OPEN_DESERIALIZE 5      /* Open using sqlite3_deserialize() */







/*
** These are the allowed shellFlgs values
*/
#define SHFLG_Pagecache      0x00000001 /* The --pagecache option is used */
#define SHFLG_Lookaside      0x00000002 /* Lookaside memory is used */
#define SHFLG_Backslash      0x00000004 /* The --backslash option is used */
#define SHFLG_PreserveRowid  0x00000008 /* .dump preserves rowid values */
................................................................................
#ifndef SQLITE_NOHAVE_SYSTEM
  ".system CMD ARGS...      Run CMD ARGS... in a system shell",
#endif
  ".tables ?TABLE?          List names of tables matching LIKE pattern TABLE",
  ".testcase NAME           Begin redirecting output to 'testcase-out.txt'",
  ".timeout MS              Try opening locked tables for MS milliseconds",
  ".timer on|off            Turn SQL timer on or off",

  ".trace FILE|off          Output each SQL statement as it is run",














  ".vfsinfo ?AUX?           Information about the top-level VFS",
  ".vfslist                 List all available VFSes",
  ".vfsname ?AUX?           Print the name of the VFS stack",
  ".width NUM1 NUM2 ...     Set column widths for \"column\" mode",
  "     Negative values right-justify",
};

................................................................................
    if( f==0 ){
      utf8_printf(stderr, "Error: cannot open \"%s\"\n", zFile);
    }
  }
  return f;
}

#if !defined(SQLITE_OMIT_TRACE) && !defined(SQLITE_OMIT_FLOATING_POINT)
/*
** A routine for handling output from sqlite3_trace().
*/
static int sql_trace_callback(
  unsigned mType,
  void *pArg,
  void *pP,
  void *pX
){
  FILE *f = (FILE*)pArg;
  UNUSED_PARAMETER(mType);
  UNUSED_PARAMETER(pP);
  if( f ){






    const char *z = (const char*)pX;




















    int i = strlen30(z);
    while( i>0 && z[i-1]==';' ){ i--; }
    utf8_printf(f, "%.*s;\n", i, z);












  }
  return 0;
}
#endif

/*
** A no-op routine that runs with the ".breakpoint" doc-command.  This is
................................................................................
      }
    }else{
      raw_printf(stderr, "Usage: .timer on|off\n");
      rc = 1;
    }
  }else


  if( c=='t' && strncmp(azArg[0], "trace", n)==0 ){


    open_db(p, 0);
    if( nArg!=2 ){



























      raw_printf(stderr, "Usage: .trace FILE|off\n");
      rc = 1;
      goto meta_command_exit;
    }

    output_file_close(p->traceOut);
    p->traceOut = output_file_open(azArg[1], 0);
#if !defined(SQLITE_OMIT_TRACE) && !defined(SQLITE_OMIT_FLOATING_POINT)


    if( p->traceOut==0 ){
      sqlite3_trace_v2(p->db, 0, 0, 0);
    }else{

      sqlite3_trace_v2(p->db, SQLITE_TRACE_STMT, sql_trace_callback,p->traceOut);
    }
#endif
  }else


#if SQLITE_USER_AUTHENTICATION
  if( c=='u' && strncmp(azArg[0], "user", n)==0 ){
    if( nArg<2 ){
      raw_printf(stderr, "Usage: .user SUBCOMMAND ...\n");
      rc = 1;
      goto meta_command_exit;







>







 







>
>
>
>
>
>







 







>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|




|
|
|
|

|
|
|
|
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
>
>
>
>
>
>
>
>
>
>
>
>







 







>

>
>

<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
|
>
|
|
<
>
>



>
|

<

>







1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
....
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
....
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
....
4017
4018
4019
4020
4021
4022
4023
4024
4025
4026
4027
4028
4029
4030
4031
4032
4033
4034
4035
4036
4037
4038
4039
4040
4041
4042
4043
4044
4045
4046
4047
4048
4049
4050
4051
4052
4053
4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065


4066
4067
4068
4069
4070
4071
4072
4073
4074
4075
4076
4077
4078
4079
4080
4081
4082
4083
4084
....
7892
7893
7894
7895
7896
7897
7898
7899
7900
7901
7902
7903

7904
7905
7906
7907
7908
7909
7910
7911
7912
7913
7914
7915
7916
7917
7918
7919
7920
7921
7922
7923
7924
7925
7926
7927
7928
7929
7930
7931
7932
7933
7934
7935
7936
7937

7938
7939
7940
7941
7942
7943
7944
7945

7946
7947
7948
7949
7950
7951
7952
7953
7954
  u8 autoEQP;            /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */
  u8 autoEQPtest;        /* autoEQP is in test mode */
  u8 statsOn;            /* True to display memory stats before each finalize */
  u8 scanstatsOn;        /* True to display scan stats before each finalize */
  u8 openMode;           /* SHELL_OPEN_NORMAL, _APPENDVFS, or _ZIPFILE */
  u8 doXdgOpen;          /* Invoke start/open/xdg-open in output_reset() */
  u8 nEqpLevel;          /* Depth of the EQP output graph */
  u8 eTraceType;         /* SHELL_TRACE_* value for type of trace */
  unsigned mEqpLines;    /* Mask of veritical lines in the EQP output graph */
  int outCount;          /* Revert to stdout when reaching zero */
  int cnt;               /* Number of records displayed so far */
  FILE *out;             /* Write results here */
  FILE *traceOut;        /* Output for sqlite3_trace() */
  int nErr;              /* Number of errors seen */
  int mode;              /* An output mode setting */
................................................................................
#define SHELL_OPEN_UNSPEC      0      /* No open-mode specified */
#define SHELL_OPEN_NORMAL      1      /* Normal database file */
#define SHELL_OPEN_APPENDVFS   2      /* Use appendvfs */
#define SHELL_OPEN_ZIPFILE     3      /* Use the zipfile virtual table */
#define SHELL_OPEN_READONLY    4      /* Open a normal database read-only */
#define SHELL_OPEN_DESERIALIZE 5      /* Open using sqlite3_deserialize() */

/* Allowed values for ShellState.eTraceType
*/
#define SHELL_TRACE_PLAIN      0      /* Show input SQL text */
#define SHELL_TRACE_EXPANDED   1      /* Show expanded SQL text */
#define SHELL_TRACE_NORMALIZED 2      /* Show normalized SQL text */

/*
** These are the allowed shellFlgs values
*/
#define SHFLG_Pagecache      0x00000001 /* The --pagecache option is used */
#define SHFLG_Lookaside      0x00000002 /* Lookaside memory is used */
#define SHFLG_Backslash      0x00000004 /* The --backslash option is used */
#define SHFLG_PreserveRowid  0x00000008 /* .dump preserves rowid values */
................................................................................
#ifndef SQLITE_NOHAVE_SYSTEM
  ".system CMD ARGS...      Run CMD ARGS... in a system shell",
#endif
  ".tables ?TABLE?          List names of tables matching LIKE pattern TABLE",
  ".testcase NAME           Begin redirecting output to 'testcase-out.txt'",
  ".timeout MS              Try opening locked tables for MS milliseconds",
  ".timer on|off            Turn SQL timer on or off",
#ifndef SQLITE_OMIT_TRACE
  ".trace ?OPTIONS?         Output each SQL statement as it is run",
  "    FILE                    Send output to FILE",
  "    stdout                  Send output to stdout",
  "    stderr                  Send output to stderr",
  "    off                     Disable tracing",
  "    --expanded              Expand query parameters",
#ifdef SQLITE_ENABLE_NORMALIZE
  "    --normalized            Normal the SQL statements",
#endif
  "    --plain                 Show SQL as it is input",
  "    --stmt                  Trace statement execution (SQLITE_TRACE_STMT)",
  "    --profile               Profile statements (SQLITE_TRACE_PROFILE)",
  "    --row                   Trace each row (SQLITE_TRACE_ROW)",
  "    --close                 Trace connection close (SQLITE_TRACE_CLOSE)",
#endif /* SQLITE_OMIT_TRACE */
  ".vfsinfo ?AUX?           Information about the top-level VFS",
  ".vfslist                 List all available VFSes",
  ".vfsname ?AUX?           Print the name of the VFS stack",
  ".width NUM1 NUM2 ...     Set column widths for \"column\" mode",
  "     Negative values right-justify",
};

................................................................................
    if( f==0 ){
      utf8_printf(stderr, "Error: cannot open \"%s\"\n", zFile);
    }
  }
  return f;
}

#ifndef SQLITE_OMIT_TRACE
/*
** A routine for handling output from sqlite3_trace().
*/
static int sql_trace_callback(
  unsigned mType,         /* The trace type */
  void *pArg,             /* The ShellState pointer */
  void *pP,               /* Usually a pointer to sqlite_stmt */
  void *pX                /* Auxiliary output */
){
  ShellState *p = (ShellState*)pArg;
  sqlite3_stmt *pStmt;
  const char *zSql;
  int nSql;
  if( p->traceOut==0 ) return 0;
  if( mType==SQLITE_TRACE_CLOSE ){
    utf8_printf(p->traceOut, "-- closing database connection\n");
    return 0;
  }
  if( mType!=SQLITE_TRACE_ROW && ((const char*)pX)[0]=='-' ){
    zSql = (const char*)pX;
  }else{
    pStmt = (sqlite3_stmt*)pP;
    switch( p->eTraceType ){
      case SHELL_TRACE_EXPANDED: {
        zSql = sqlite3_expanded_sql(pStmt);
        break;
      }
#ifdef SQLITE_ENABLE_NORMALIZE
      case SHELL_TRACE_NORMALIZED: {
        zSql = sqlite3_normalized_sql(pStmt);
        break;
      }
#endif
      default: {
        zSql = sqlite3_sql(pStmt);
        break;
      }
    }
  }
  if( zSql==0 ) return 0;
  nSql = strlen30(zSql);


  while( nSql>0 && zSql[nSql-1]==';' ){ nSql--; }
  switch( mType ){
    case SQLITE_TRACE_ROW:
    case SQLITE_TRACE_STMT: {
      utf8_printf(p->traceOut, "%.*s;\n", nSql, zSql);
      break;
    }
    case SQLITE_TRACE_PROFILE: {
      sqlite3_int64 nNanosec = *(sqlite3_int64*)pX;
      utf8_printf(p->traceOut, "%.*s; -- %lld ns\n", nSql, zSql, nNanosec);
      break;
    }
  }
  return 0;
}
#endif

/*
** A no-op routine that runs with the ".breakpoint" doc-command.  This is
................................................................................
      }
    }else{
      raw_printf(stderr, "Usage: .timer on|off\n");
      rc = 1;
    }
  }else

#ifndef SQLITE_OMIT_TRACE
  if( c=='t' && strncmp(azArg[0], "trace", n)==0 ){
    int mType = 0;
    int jj;
    open_db(p, 0);

    for(jj=1; jj<nArg; jj++){
      const char *z = azArg[jj];
      if( z[0]=='-' ){
        if( optionMatch(z, "expanded") ){
          p->eTraceType = SHELL_TRACE_EXPANDED;
        }
#ifdef SQLITE_ENABLE_NORMALIZE
        else if( optionMatch(z, "normalized") ){
          p->eTraceType = SHELL_TRACE_NORMALIZED;
        }
#endif
        else if( optionMatch(z, "plain") ){
          p->eTraceType = SHELL_TRACE_PLAIN;
        }
        else if( optionMatch(z, "profile") ){
          mType |= SQLITE_TRACE_PROFILE;
        }
        else if( optionMatch(z, "row") ){
          mType |= SQLITE_TRACE_ROW;
        }
        else if( optionMatch(z, "stmt") ){
          mType |= SQLITE_TRACE_STMT;
        }
        else if( optionMatch(z, "close") ){
          mType |= SQLITE_TRACE_CLOSE;
        }
        else {
          raw_printf(stderr, "Unknown option \"%s\" on \".trace\"\n", z);
          rc = 1;
          goto meta_command_exit;
        }
      }else{
        output_file_close(p->traceOut);
        p->traceOut = output_file_open(azArg[1], 0);

      }
    }
    if( p->traceOut==0 ){
      sqlite3_trace_v2(p->db, 0, 0, 0);
    }else{
      if( mType==0 ) mType = SQLITE_TRACE_STMT;
      sqlite3_trace_v2(p->db, mType, sql_trace_callback, p);
    }

  }else
#endif /* !defined(SQLITE_OMIT_TRACE) */

#if SQLITE_USER_AUTHENTICATION
  if( c=='u' && strncmp(azArg[0], "user", n)==0 ){
    if( nArg<2 ){
      raw_printf(stderr, "Usage: .user SUBCOMMAND ...\n");
      rc = 1;
      goto meta_command_exit;

Changes to src/sqliteInt.h.

4420
4421
4422
4423
4424
4425
4426
4427
4428
4429
4430
4431
4432
4433
4434
int sqlite3VtabBegin(sqlite3 *, VTable *);
FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*);
sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*);
int sqlite3VdbeParameterIndex(Vdbe*, const char*, int);
int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
void sqlite3ParserReset(Parse*);
#ifdef SQLITE_ENABLE_NORMALIZE
void sqlite3Normalize(Vdbe*, const char*, int, u8);
#endif
int sqlite3Reprepare(Vdbe*);
void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*);
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
int sqlite3TempInMemory(const sqlite3*);
const char *sqlite3JournalModename(int);
#ifndef SQLITE_OMIT_WAL







|







4420
4421
4422
4423
4424
4425
4426
4427
4428
4429
4430
4431
4432
4433
4434
int sqlite3VtabBegin(sqlite3 *, VTable *);
FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*);
sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*);
int sqlite3VdbeParameterIndex(Vdbe*, const char*, int);
int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
void sqlite3ParserReset(Parse*);
#ifdef SQLITE_ENABLE_NORMALIZE
char *sqlite3Normalize(Vdbe*, const char*, int);
#endif
int sqlite3Reprepare(Vdbe*);
void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*);
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
int sqlite3TempInMemory(const sqlite3*);
const char *sqlite3JournalModename(int);
#ifndef SQLITE_OMIT_WAL

Changes to src/vdbeapi.c.

1708
1709
1710
1711
1712
1713
1714




1715
1716
1717
1718
1719
1720
1721
1722

#ifdef SQLITE_ENABLE_NORMALIZE
/*
** Return the normalized SQL associated with a prepared statement.
*/
const char *sqlite3_normalized_sql(sqlite3_stmt *pStmt){
  Vdbe *p = (Vdbe *)pStmt;




  return p ? p->zNormSql : 0;
}
#endif /* SQLITE_ENABLE_NORMALIZE */

#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
/*
** Allocate and populate an UnpackedRecord structure based on the serialized
** record in nKey/pKey. Return a pointer to the new UnpackedRecord structure







>
>
>
>
|







1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726

#ifdef SQLITE_ENABLE_NORMALIZE
/*
** Return the normalized SQL associated with a prepared statement.
*/
const char *sqlite3_normalized_sql(sqlite3_stmt *pStmt){
  Vdbe *p = (Vdbe *)pStmt;
  if( p==0 ) return 0;
  if( p->zNormSql==0 && p->zSql!=0 ){
    p->zNormSql = sqlite3Normalize(p, p->zSql, sqlite3Strlen30(p->zSql));
  }
  return p->zNormSql;
}
#endif /* SQLITE_ENABLE_NORMALIZE */

#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
/*
** Allocate and populate an UnpackedRecord structure based on the serialized
** record in nKey/pKey. Return a pointer to the new UnpackedRecord structure

Changes to src/vdbeaux.c.

63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
    p->expmask = 0;
  }
  assert( p->zSql==0 );
  p->zSql = sqlite3DbStrNDup(p->db, z, n);
#ifdef SQLITE_ENABLE_NORMALIZE
  assert( p->zNormSql==0 );
  if( p->zSql && (prepFlags & SQLITE_PREPARE_NORMALIZE)!=0 ){
    sqlite3Normalize(p, p->zSql, n, prepFlags);
    assert( p->zNormSql!=0 || p->db->mallocFailed );
  }
#endif
}

/*
** Swap all content between two VDBE structures.







|







63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
    p->expmask = 0;
  }
  assert( p->zSql==0 );
  p->zSql = sqlite3DbStrNDup(p->db, z, n);
#ifdef SQLITE_ENABLE_NORMALIZE
  assert( p->zNormSql==0 );
  if( p->zSql && (prepFlags & SQLITE_PREPARE_NORMALIZE)!=0 ){
    p->zNormSql = sqlite3Normalize(p, p->zSql, n);
    assert( p->zNormSql!=0 || p->db->mallocFailed );
  }
#endif
}

/*
** Swap all content between two VDBE structures.

Changes to test/shell4.test.

103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
}]
  list [regexp {Memory Used} $res] \
       [regexp {Heap Usage} $res] \
       [regexp {Autoindex Inserts} $res]
} {1 1 1}

do_test shell4-2.1 {
  catchcmd ":memory:" "CREATE TABLE t1(x);\n.trace"
} {1 {Usage: .trace FILE|off}}
do_test shell4-2.2 {
  catchcmd ":memory:" "CREATE TABLE t1(x);\n.trace off\n.trace off\n"
} {0 {}}
do_test shell4-2.3 {
  catchcmd ":memory:" ".trace stdout\n.trace\n.trace off\n.dump\n"
} {/^1 {PRAGMA.*Usage:.*}$/}
ifcapable trace {
do_test shell4-2.4 {
  catchcmd ":memory:" ".trace stdout\nCREATE TABLE t1(x);SELECT * FROM t1;"
} {0 {CREATE TABLE t1(x);
SELECT * FROM t1;}}
do_test shell4-2.5 {
  catchcmd ":memory:" "CREATE TABLE t1(x);\n.trace stdout\nSELECT * FROM t1;"







|
|




|
|







103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
}]
  list [regexp {Memory Used} $res] \
       [regexp {Heap Usage} $res] \
       [regexp {Autoindex Inserts} $res]
} {1 1 1}

do_test shell4-2.1 {
  catchcmd ":memory:" "CREATE TABLE t1(x);\n.trace --unknown"
} {1 {Unknown option "--unknown" on ".trace"}}
do_test shell4-2.2 {
  catchcmd ":memory:" "CREATE TABLE t1(x);\n.trace off\n.trace off\n"
} {0 {}}
do_test shell4-2.3 {
  catchcmd ":memory:" ".trace stdout\n.dump\n.trace off\n"
} {/^0 {PRAGMA.*}$/}
ifcapable trace {
do_test shell4-2.4 {
  catchcmd ":memory:" ".trace stdout\nCREATE TABLE t1(x);SELECT * FROM t1;"
} {0 {CREATE TABLE t1(x);
SELECT * FROM t1;}}
do_test shell4-2.5 {
  catchcmd ":memory:" "CREATE TABLE t1(x);\n.trace stdout\nSELECT * FROM t1;"