Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | 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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | schemalint |
Files: | files | file ages | folders |
SHA1: |
c6fa01c28ef7ceea2963a92dfffe62ee |
User & Date: | dan 2015-11-11 18:08:58.267 |
Context
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) | |
15:28 | Improvements to the SQLITE_CONFIG_PAGECACHE documentation. Enhance the command-line shell to be able to take advantage of the full range of SQLITE_CONFIG_PAGECACHE capabilities, such as setting pMem==NULL and N<0. (check-in: 2518d5c971 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | |||
3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 | 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 3943 3944 3945 3946 3947 3948 3949 3950 3951 3952 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 3978 3979 3980 3981 3982 3983 3984 3985 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 4015 4016 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 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | pLoop->cId = '0'; #endif return 1; } return 0; } #ifdef SQLITE_SCHEMA_LINT static char *whereAppendPrintf(sqlite3 *db, const char *zFmt, ...){ va_list ap; char *zRes = 0; va_start(ap, zFmt); zRes = sqlite3_vmprintf(zFmt, ap); if( zRes==0 ){ db->mallocFailed = 1; }else if( db->mallocFailed ){ sqlite3_free(zRes); zRes = 0; } va_end(ap); return zRes; } /* ** Append a representation of term pTerm to the string in zIn and return ** the result. Or, if an OOM occurs, free zIn and return a NULL pointer. */ static char *whereAppendSingleTerm( Parse *pParse, Table *pTab, int bOr, char *zIn, WhereTerm *pTerm ){ char *zBuf; sqlite3 *db = pParse->db; Expr *pX = pTerm->pExpr; CollSeq *pColl; const char *zOp = 0; if( pTerm->eOperator & (WO_IS|WO_EQ|WO_IN) ){ zOp = "eq"; }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GE|WO_GT) ){ zOp = "range"; } pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight); if( zOp ){ const char *zFmt = bOr ? "%z{{%s %s %s %lld}}" : "%z{%s %s %s %lld}"; zBuf = whereAppendPrintf(db, zFmt, zIn, zOp, pTab->aCol[pTerm->u.leftColumn].zName, (pColl ? pColl->zName : "BINARY"), pTerm->prereqRight ); }else{ zBuf = zIn; } 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); pTerm; pTerm=whereScanNext(&scan) ){ assert( iCol==pTerm->u.leftColumn ); if( bFirst==0 ) zBuf = whereAppendPrintf(db, "%z ", zBuf); zBuf = whereAppendSingleTerm(pParse, pTab, pWC->op==TK_OR, zBuf, pTerm); bFirst = 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++){ char *zBuf = 0; int iCol; int nCol; Table *pTab; struct SrcList_item *pItem = &pWInfo->pTabList->a[ii]; if( pItem->pSelect ) continue; pTab = pItem->pTab; nCol = pTab->nCol; /* Append the table name to the buffer. */ zBuf = whereAppendPrintf(db, "%s", pTab->zName); /* Append the list of columns required to create a covering index */ zBuf = whereAppendPrintf(db, "%z {cols", zBuf); if( 0==(pItem->colUsed & ((u64)1 << (sizeof(Bitmask)*8-1))) ){ 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); } } } #else # define whereTraceBuilder(x,y) #endif /* ** Generate the beginning of the loop used for WHERE clause processing. ** The return value is a pointer to an opaque structure that contains ** information needed to terminate the loop. Later, the calling routine ** should invoke sqlite3WhereEnd() with the return value of this function ** in order to complete the WHERE clause processing. ** |
︙ | |||
4156 4157 4158 4159 4160 4161 4162 4163 4164 4165 4166 4167 4168 4169 | 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 | + + + | if( sqlite3WhereTrace & 0x100 ){ /* Display all terms of the WHERE clause */ int i; for(i=0; i<sWLB.pWC->nTerm; i++){ whereTermPrint(&sWLB.pWC->a[i], i); } } #endif /* Schema-lint xTrace callback */ whereTraceBuilder(pParse, &sWLB); if( nTabList!=1 || whereShortCut(&sWLB)==0 ){ rc = whereLoopAddAll(&sWLB); if( rc ) goto whereBeginError; #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace ){ /* Display all of the WhereLoop objects */ |
︙ |
Added test/schemalint.test.
|
Added tool/schemalint.tcl.