SQLite

Check-in [93bdf70e85]
Login

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

Overview
Comment:Add support for ORDER BY clauses to schemalint.tcl.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1: 93bdf70e859915ff3696ba0fc68f91ceb2e1a971
User & Date: dan 2015-11-20 20:55:27.428
Context
2015-11-23
17:10
Fix ORDER BY handling in the schemalint.tcl script. Add internal self-tests to the same script. (check-in: b8f277c9b4 user: dan tags: schemalint)
2015-11-20
20:55
Add support for ORDER BY clauses to schemalint.tcl. (check-in: 93bdf70e85 user: dan tags: schemalint)
2015-11-11
18:08
Add a hack to debug out a description of the WHERE clause of a SELECT (or other) statement. Use this in script tool/schemalint.tcl to automatically recommend indexes that might speed up specific queries. (check-in: c6fa01c28e user: dan tags: schemalint)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
3953
3954
3955
3956
3957
3958
3959

3960
3961
3962
3963
3964
3965
3966
3967
3968
3969
3970
3971
3972
3973
3974
3975
3976
  }

  return zBuf;
}

static char *whereTraceWC(
  Parse *pParse, 

  struct SrcList_item *pItem,
  char *zIn,
  WhereClause *pWC
){
  sqlite3 *db = pParse->db;
  Table *pTab = pItem->pTab;
  char *zBuf = zIn;
  int iCol;
  int ii;
  int bFirst = 1;

  /* List of WO_SINGLE constraints */
  for(iCol=0; iCol<pTab->nCol; iCol++){
    int opMask = WO_SINGLE; 
    WhereScan scan;
    WhereTerm *pTerm;
    for(pTerm=whereScanInit(&scan, pWC, pItem->iCursor, iCol, opMask, 0);







>









|







3953
3954
3955
3956
3957
3958
3959
3960
3961
3962
3963
3964
3965
3966
3967
3968
3969
3970
3971
3972
3973
3974
3975
3976
3977
  }

  return zBuf;
}

static char *whereTraceWC(
  Parse *pParse, 
  int bInitialSpace,
  struct SrcList_item *pItem,
  char *zIn,
  WhereClause *pWC
){
  sqlite3 *db = pParse->db;
  Table *pTab = pItem->pTab;
  char *zBuf = zIn;
  int iCol;
  int ii;
  int bFirst = !bInitialSpace;

  /* List of WO_SINGLE constraints */
  for(iCol=0; iCol<pTab->nCol; iCol++){
    int opMask = WO_SINGLE; 
    WhereScan scan;
    WhereTerm *pTerm;
    for(pTerm=whereScanInit(&scan, pWC, pItem->iCursor, iCol, opMask, 0);
3986
3987
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004
4005
4006
4007

4008
4009
4010
4011
4012
4013
4014

  /* Add composite - (WO_OR|WO_AND) - constraints */
  for(ii=0; ii<pWC->nTerm; ii++){
    WhereTerm *pTerm = &pWC->a[ii];
    if( pTerm->eOperator & (WO_OR|WO_AND) ){
      const char *zFmt = ((pTerm->eOperator&WO_OR) ? "%z%s{or " : "%z%s{");
      zBuf = whereAppendPrintf(db, zFmt, zBuf, bFirst ? "" : " ");
      zBuf = whereTraceWC(pParse, pItem, zBuf, &pTerm->u.pOrInfo->wc);
      zBuf = whereAppendPrintf(db, "%z}", zBuf);
      bFirst = 0;
    }
  }

  return zBuf;
}

static void whereTraceBuilder(
  Parse *pParse,
  WhereLoopBuilder *p
){
  sqlite3 *db = pParse->db;
  if( db->xTrace ){

    WhereInfo *pWInfo = p->pWInfo;
    int nTablist = pWInfo->pTabList->nSrc;
    int ii;

    /* Loop through each element of the FROM clause. Ignore any sub-selects
    ** or views. Invoke the xTrace() callback once for each real table. */
    for(ii=0; ii<nTablist; ii++){







|














>







3987
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004
4005
4006
4007
4008
4009
4010
4011
4012
4013
4014
4015
4016

  /* Add composite - (WO_OR|WO_AND) - constraints */
  for(ii=0; ii<pWC->nTerm; ii++){
    WhereTerm *pTerm = &pWC->a[ii];
    if( pTerm->eOperator & (WO_OR|WO_AND) ){
      const char *zFmt = ((pTerm->eOperator&WO_OR) ? "%z%s{or " : "%z%s{");
      zBuf = whereAppendPrintf(db, zFmt, zBuf, bFirst ? "" : " ");
      zBuf = whereTraceWC(pParse, 0, pItem, zBuf, &pTerm->u.pOrInfo->wc);
      zBuf = whereAppendPrintf(db, "%z}", zBuf);
      bFirst = 0;
    }
  }

  return zBuf;
}

static void whereTraceBuilder(
  Parse *pParse,
  WhereLoopBuilder *p
){
  sqlite3 *db = pParse->db;
  if( db->xTrace ){
    ExprList *pOrderBy = p->pOrderBy;
    WhereInfo *pWInfo = p->pWInfo;
    int nTablist = pWInfo->pTabList->nSrc;
    int ii;

    /* Loop through each element of the FROM clause. Ignore any sub-selects
    ** or views. Invoke the xTrace() callback once for each real table. */
    for(ii=0; ii<nTablist; ii++){
4031
4032
4033
4034
4035
4036
4037
4038
4039
4040
4041























4042
4043
4044
4045
4046
4047
4048
        for(iCol=0; iCol<nCol; iCol++){
          if( iCol==(sizeof(Bitmask)*8-1) ) break;
          if( pItem->colUsed & ((u64)1 << iCol) ){
            zBuf = whereAppendPrintf(db, "%z %s", zBuf, pTab->aCol[iCol].zName);
          }
        }
      }
      zBuf = whereAppendPrintf(db, "%z} ", zBuf);

      /* Append the contents of WHERE clause */
      zBuf = whereTraceWC(pParse, pItem, zBuf, p->pWC);
























      /* Pass the buffer to the xTrace() callback, then free it */
      db->xTrace(db->pTraceArg, zBuf);
      sqlite3DbFree(db, zBuf);
    }
  }
}







|


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







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
        for(iCol=0; iCol<nCol; iCol++){
          if( iCol==(sizeof(Bitmask)*8-1) ) break;
          if( pItem->colUsed & ((u64)1 << iCol) ){
            zBuf = whereAppendPrintf(db, "%z %s", zBuf, pTab->aCol[iCol].zName);
          }
        }
      }
      zBuf = whereAppendPrintf(db, "%z}",zBuf);

      /* Append the contents of WHERE clause */
      zBuf = whereTraceWC(pParse, 1, pItem, zBuf, p->pWC);

      /* Append the ORDER BY clause, if any */
      if( pOrderBy ){
        int i;
        int bFirst = 1;
        for(i=0; i<pOrderBy->nExpr; i++){
          Expr *pExpr = pOrderBy->a[i].pExpr; 
          CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);

          pExpr = sqlite3ExprSkipCollate(pExpr);
          if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
            if( pExpr->iColumn>=0 ){
              const char *zName = pTab->aCol[pExpr->iColumn].zName;
              zBuf = whereAppendPrintf(db, "%z%s%s %s %s", zBuf,
                  bFirst ? " {orderby " : " ", zName, pColl->zName,
                  (pOrderBy->a[i].sortOrder ? "DESC" : "ASC")
              );
              bFirst = 0;
            }
          }
        }
        if( bFirst==0 ) zBuf = whereAppendPrintf(db, "%z}", zBuf);
      }

      /* Pass the buffer to the xTrace() callback, then free it */
      db->xTrace(db->pTraceArg, zBuf);
      sqlite3DbFree(db, zBuf);
    }
  }
}
Changes to test/schemalint.test.
72
73
74
75
76
77
78



















79
80
81
}

do_trace_test 2.3 {
  SELECT * FROM t1 WHERE (a=? AND b=?) OR c=?
} {
  {t1 {cols a b c} {or {{eq c BINARY 0}} {{eq a BINARY 0} {eq b BINARY 0}}}}
}




















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
}

do_trace_test 2.3 {
  SELECT * FROM t1 WHERE (a=? AND b=?) OR c=?
} {
  {t1 {cols a b c} {or {{eq c BINARY 0}} {{eq a BINARY 0} {eq b BINARY 0}}}}
}

#-----------------------------------------------------------------------
# Cases involving ORDER BY.
#
do_trace_test 3.1 {
  SELECT * FROM t1 ORDER BY a;
} {{t1 {cols a b c} {orderby a BINARY ASC}}}

do_trace_test 3.2 {
  SELECT * FROM t1 WHERE a=? ORDER BY b;
} {{t1 {cols a b c} {eq a BINARY 0} {orderby b BINARY ASC}}}

do_trace_test 3.3 {
  SELECT min(a) FROM t1;
} {{t1 {cols a} {orderby a BINARY ASC}}}

do_trace_test 3.4 {
  SELECT max(a) FROM t1;
} {{t1 {cols a} {orderby a BINARY DESC}}}

finish_test

Changes to tool/schemalint.tcl.
111
112
113
114
115
116
117



118
119
120
121
122
123
124
    }
    set ll $new
  }

  return $ll
}




proc eqset_to_index {tname eqset {range {}}} {
  global G
  set lCols [list]
  set idxname $tname
  foreach e [concat [lsort $eqset] [list $range]] {
    if {[llength $e]==0} continue
    foreach {c collate} $e {}







>
>
>







111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
    }
    set ll $new
  }

  return $ll
}

#--------------------------------------------------------------------------
# Formulate a CREATE INDEX statement that creates an index on table $tname.
#
proc eqset_to_index {tname eqset {range {}}} {
  global G
  set lCols [list]
  set idxname $tname
  foreach e [concat [lsort $eqset] [list $range]] {
    if {[llength $e]==0} continue
    foreach {c collate} $e {}
162
163
164
165
166
167
168




169
170
171
172
173
174
175
}

proc find_trial_indexes {} {
  global G
  foreach t $G(trace) {
    set tname [lindex $t 0]
    catch { array unset mask }





    foreach lCons [expand_or_cons [lrange $t 2 end]] {
      set constraints [list]

      foreach a $lCons {
        set type [lindex $a 0]
        if {$type=="eq" || $type=="range"} {







>
>
>
>







165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
}

proc find_trial_indexes {} {
  global G
  foreach t $G(trace) {
    set tname [lindex $t 0]
    catch { array unset mask }

    if {[lindex $t end 0]=="orderby"} {
      set orderby [lrange [lindex $t end] 1 end]
    }

    foreach lCons [expand_or_cons [lrange $t 2 end]] {
      set constraints [list]

      foreach a $lCons {
        set type [lindex $a 0]
        if {$type=="eq" || $type=="range"} {