/ Check-in [93bdf70e]
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 | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1: 93bdf70e859915ff3696ba0fc68f91ceb2e1a971
User & Date: dan 2015-11-20 20:55:27
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: b8f277c9 user: dan tags: schemalint
2015-11-20
20:55
Add support for ORDER BY clauses to schemalint.tcl. check-in: 93bdf70e 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: c6fa01c2 user: dan tags: schemalint
Changes
Hide Diffs Unified Diffs 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
....
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
....
4031
4032
4033
4034
4035
4036
4037
4038
4039
4040
4041























4042
4043
4044
4045
4046
4047
4048
  }

  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);
................................................................................

  /* 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++){
................................................................................
        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);
    }
  }
}







>









|







 







|







 







>







 







|


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







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
....
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
....
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
  }

  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);
................................................................................

  /* 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++){
................................................................................
        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
...
162
163
164
165
166
167
168




169
170
171
172
173
174
175
    }
    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 {}
................................................................................
}

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"} {







>
>
>







 







>
>
>
>







111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
...
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
    }
    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 {}
................................................................................
}

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"} {