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: |
93bdf70e859915ff3696ba0fc68f91ce |
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
Changes to src/where.c.
︙ | ︙ | |||
3953 3954 3955 3956 3957 3958 3959 3960 3961 3962 3963 3964 3965 3966 3967 3968 | } 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; | > | | 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 | /* 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 ? "" : " "); | | > | 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 | 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); } } } | | | > > > > > > > > > > > > > > > > > > > > > > > | 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"} { |
︙ | ︙ |