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: |
93bdf70e859915ff3696ba0fc68f91ce |
| 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
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"} {
|