Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add some support for OR terms to sqlite3_whereinfo_hook(). |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | schemalint |
| Files: | files | file ages | folders |
| SHA3-256: |
5cd070000da1d9e399090677b4db75dc |
| User & Date: | dan 2017-04-04 17:50:31 |
Context
|
2017-04-06
| ||
| 18:44 | Changes to allow indexes to be recommended for queries on SQL views. check-in: 0884ff1d user: dan tags: schemalint | |
|
2017-04-04
| ||
| 17:50 | Add some support for OR terms to sqlite3_whereinfo_hook(). check-in: 5cd07000 user: dan tags: schemalint | |
| 04:23 | Add the sqlite3_whereinfo_hook() API - an experimental API replacing the DBCONFIG_WHEREINFO hack on this branch. check-in: a54aef35 user: dan tags: schemalint | |
Changes
Changes to src/shell_indexes.c.
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
...
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
|
#if 0
const char *zOp =
eOp==SQLITE_WHEREINFO_TABLE ? "TABLE" :
eOp==SQLITE_WHEREINFO_EQUALS ? "EQUALS" :
eOp==SQLITE_WHEREINFO_RANGE ? "RANGE" :
eOp==SQLITE_WHEREINFO_ORDERBY ? "ORDERBY" :
eOp==SQLITE_WHEREINFO_NEXTOR ? "NEXTOR" :
eOp==SQLITE_WHEREINFO_ENDOR ? "ENDOR" :
eOp==SQLITE_WHEREINFO_BEGINOR ? "BEGINOR" :
"!error!";
printf("op=%s zVal=%s iVal=%d mask=%llx\n", zOp, zVal, iVal, mask);
#endif
if( p->rc==SQLITE_OK ){
assert( eOp==SQLITE_WHEREINFO_TABLE || p->pScan!=0 );
switch( eOp ){
................................................................................
zFmt = "CREATE INDEX %s_idx_%08x ON %s(%s)";
}
zIdx = sqlite3_mprintf(zFmt, pScan->zTable, h, pScan->zTable, zCols);
if( !zIdx ){
rc = SQLITE_NOMEM;
}else{
rc = sqlite3_exec(dbm, zIdx, 0, 0, pCtx->pzErrmsg);
/* printf("%s\n", zIdx); */
}
}
if( rc==SQLITE_OK && pCtx->iIdxRowid==0 ){
int rc2;
sqlite3_stmt *pLast = 0;
rc = idxPrepareStmt(dbm, &pLast, pCtx->pzErrmsg,
"SELECT max(rowid) FROM sqlite_master"
|
<
<
<
>
|
>
|
171
172
173
174
175
176
177
178
179
180
181
182
183
184
...
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
|
#if 0
const char *zOp =
eOp==SQLITE_WHEREINFO_TABLE ? "TABLE" :
eOp==SQLITE_WHEREINFO_EQUALS ? "EQUALS" :
eOp==SQLITE_WHEREINFO_RANGE ? "RANGE" :
eOp==SQLITE_WHEREINFO_ORDERBY ? "ORDERBY" :
"!error!";
printf("op=%s zVal=%s iVal=%d mask=%llx\n", zOp, zVal, iVal, mask);
#endif
if( p->rc==SQLITE_OK ){
assert( eOp==SQLITE_WHEREINFO_TABLE || p->pScan!=0 );
switch( eOp ){
................................................................................
zFmt = "CREATE INDEX %s_idx_%08x ON %s(%s)";
}
zIdx = sqlite3_mprintf(zFmt, pScan->zTable, h, pScan->zTable, zCols);
if( !zIdx ){
rc = SQLITE_NOMEM;
}else{
rc = sqlite3_exec(dbm, zIdx, 0, 0, pCtx->pzErrmsg);
#if 0
printf("CANDIDATE: %s\n", zIdx);
#endif
}
}
if( rc==SQLITE_OK && pCtx->iIdxRowid==0 ){
int rc2;
sqlite3_stmt *pLast = 0;
rc = idxPrepareStmt(dbm, &pLast, pCtx->pzErrmsg,
"SELECT max(rowid) FROM sqlite_master"
|
Changes to src/where.c.
4274 4275 4276 4277 4278 4279 4280 4281 4282 4283 4284 4285 4286 4287 4288 4289 4290 4291 4292 4293 4294 4295 .... 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 4322 4323 4324 4325 4326 4327 4328 4329 4330 4331 4332 4333 4334 4335 4336 4337 4338 4339 4340 .... 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 |
#endif
return 1;
}
return 0;
}
#ifdef SQLITE_ENABLE_WHEREINFO_HOOK
static void whereTraceWC(
Parse *pParse,
struct SrcList_item *pItem,
WhereClause *pWC
){
sqlite3 *db = pParse->db;
Table *pTab = pItem->pTab;
void (*x)(void*, int, const char*, int, i64) = db->xWhereInfo;
void *pCtx = db->pWhereInfoCtx;
int ii;
/* Issue callbacks for WO_SINGLE constraints */
for(ii=0; ii<pTab->nCol; ii++){
int opMask = WO_SINGLE;
WhereScan scan;
................................................................................
}else{
eOp = SQLITE_WHEREINFO_RANGE;
}
x(pCtx, eOp, (pC ? pC->zName : "BINARY"), ii, pTerm->prereqRight);
}
}
}
/*
** If there is a where-info hook attached to the database handle, issue all
** required callbacks for the current sqlite3WhereBegin() call.
*/
static void whereTraceBuilder(
Parse *pParse,
WhereLoopBuilder *p
){
sqlite3 *db = pParse->db;
if( db->xWhereInfo && db->init.busy==0 ){
void (*x)(void*, int, const char*, int, i64) = db->xWhereInfo;
void *pCtx = db->pWhereInfoCtx;
int ii;
int nTab = p->pWInfo->pTabList->nSrc;
/* Loop through each element of the FROM clause. Ignore any sub-selects
** or views. Invoke the xWhereInfo() callback multiple times for each
** real table. */
for(ii=0; ii<p->pWInfo->pTabList->nSrc; ii++){
struct SrcList_item *pItem = &p->pWInfo->pTabList->a[ii];
if( pItem->pSelect==0 ){
Table *pTab = pItem->pTab;
int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
/* Table name callback */
x(pCtx, SQLITE_WHEREINFO_TABLE, pTab->zName, iDb, pItem->colUsed);
................................................................................
}
}
}
}
/* WHERE callbacks */
whereTraceWC(pParse, pItem, p->pWC);
}
}
}
}
#else
# define whereTraceBuilder(x,y)
#endif
|
> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | | > > > |
4274 4275 4276 4277 4278 4279 4280 4281 4282 4283 4284 4285 4286 4287 4288 4289 4290 4291 4292 4293 4294 4295 4296 .... 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 4322 4323 4324 4325 4326 4327 4328 4329 4330 4331 4332 4333 4334 4335 4336 4337 4338 4339 4340 4341 4342 4343 4344 4345 4346 4347 4348 4349 4350 4351 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 4376 4377 4378 4379 4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 4390 4391 4392 4393 4394 4395 4396 4397 4398 4399 4400 4401 4402 4403 4404 4405 4406 4407 4408 4409 4410 4411 4412 4413 4414 4415 4416 4417 4418 4419 4420 4421 4422 4423 4424 4425 4426 4427 4428 4429 4430 4431 4432 4433 4434 4435 4436 4437 4438 4439 4440 4441 4442 4443 4444 .... 4457 4458 4459 4460 4461 4462 4463 4464 4465 4466 4467 4468 4469 4470 4471 4472 4473 |
#endif
return 1;
}
return 0;
}
#ifdef SQLITE_ENABLE_WHEREINFO_HOOK
static void whereTraceWC(
Parse *pParse,
struct SrcList_item *pItem,
WhereClause *pWC
){
sqlite3 *db = pParse->db;
Table *pTab = pItem->pTab;
void (*x)(void*, int, const char*, int, u64) = db->xWhereInfo;
void *pCtx = db->pWhereInfoCtx;
int ii;
/* Issue callbacks for WO_SINGLE constraints */
for(ii=0; ii<pTab->nCol; ii++){
int opMask = WO_SINGLE;
WhereScan scan;
................................................................................
}else{
eOp = SQLITE_WHEREINFO_RANGE;
}
x(pCtx, eOp, (pC ? pC->zName : "BINARY"), ii, pTerm->prereqRight);
}
}
}
/*
** If there are any OR terms in WHERE clause pWC, make the associated
** where-info hook callbacks.
*/
static void whereTraceOR(
Parse *pParse,
struct SrcList_item *pItem,
WhereClause *pWC
){
sqlite3 *db = pParse->db;
WhereClause tempWC;
struct TermAndIdx {
WhereTerm *pTerm;
int iIdx;
} aOr[4];
int nOr = 0;
Table *pTab = pItem->pTab;
int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
int ii;
memset(aOr, 0, sizeof(aOr));
/* Iterate through OR nodes */
for(ii=0; ii<pWC->nTerm; ii++){
WhereTerm *pTerm = &pWC->a[ii];
if( pTerm->eOperator & WO_OR ){
/* Check that each branch of this OR term contains at least
** one reference to the table currently being processed. If that
** is not the case, this term can be ignored. */
WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc;
WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm];
WhereTerm *pOrTerm;
WhereClause *pTermWC;
WhereScan scan;
for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){
int iCol;
if( (pOrTerm->eOperator & WO_AND)!=0 ){
pTermWC = &pOrTerm->u.pAndInfo->wc;
}else{
tempWC.pWInfo = pWC->pWInfo;
tempWC.pOuter = pWC;
tempWC.op = TK_AND;
tempWC.nTerm = 1;
tempWC.a = pOrTerm;
pTermWC = &tempWC;
}
for(iCol=0; iCol<pTab->nCol; iCol++){
int iCsr = pItem->iCursor;
if( !whereScanInit(&scan, pTermWC, iCsr, iCol, WO_SINGLE, 0) ){
break;
}
}
if( iCol==pTab->nCol ) break;
}
if( pOrTerm==pOrWCEnd ){
aOr[nOr].pTerm = pTerm;
aOr[nOr].iIdx = pOrWC->nTerm;
nOr++;
if( nOr==ArraySize(aOr) ) break;
}
}
}
while( 1 ){
for(ii=0; ii<nOr; ii++){
if( aOr[ii].iIdx==0 ){
aOr[ii].iIdx = aOr[ii].pTerm->u.pOrInfo->wc.nTerm;
}else{
aOr[ii].iIdx--;
break;
}
}
if( ii==nOr ) break;
/* Table name callback */
db->xWhereInfo(db->pWhereInfoCtx,
SQLITE_WHEREINFO_TABLE, pTab->zName, iDb, pItem->colUsed
);
/* whereTraceWC(pParse, pItem, pWC); */
for(ii=0; ii<nOr; ii++){
WhereClause * const pOrWC = &aOr[ii].pTerm->u.pOrInfo->wc;
if( aOr[ii].iIdx<pOrWC->nTerm ){
WhereClause *pTermWC;
WhereTerm *pOrTerm = &pOrWC->a[aOr[ii].iIdx];
if( (pOrTerm->eOperator & WO_AND)!=0 ){
pTermWC = &pOrTerm->u.pAndInfo->wc;
}else{
tempWC.pWInfo = pWC->pWInfo;
tempWC.pOuter = pWC;
tempWC.op = TK_AND;
tempWC.nTerm = 1;
tempWC.a = pOrTerm;
pTermWC = &tempWC;
}
whereTraceWC(pParse, pItem, pTermWC);
}
}
}
}
/*
** If there is a where-info hook attached to the database handle, issue all
** required callbacks for the current sqlite3WhereBegin() call.
*/
static void whereTraceBuilder(
Parse *pParse,
WhereLoopBuilder *p
){
sqlite3 *db = pParse->db;
if( db->xWhereInfo && db->init.busy==0 ){
void (*x)(void*, int, const char*, int, u64) = db->xWhereInfo;
void *pCtx = db->pWhereInfoCtx;
int ii;
SrcList *pTabList = p->pWInfo->pTabList;
/* Loop through each element of the FROM clause. Ignore any sub-selects
** or views. Invoke the xWhereInfo() callback multiple times for each
** real table. */
for(ii=0; ii<pTabList->nSrc; ii++){
struct SrcList_item *pItem = &pTabList->a[ii];
if( pItem->pSelect==0 ){
Table *pTab = pItem->pTab;
int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
/* Table name callback */
x(pCtx, SQLITE_WHEREINFO_TABLE, pTab->zName, iDb, pItem->colUsed);
................................................................................
}
}
}
}
/* WHERE callbacks */
whereTraceWC(pParse, pItem, p->pWC);
/* OR-clause processing */
whereTraceOR(pParse, pItem, p->pWC);
}
}
}
}
#else
# define whereTraceBuilder(x,y)
#endif
|
Changes to test/shell6.test.
183 184 185 186 187 188 189 190 191 |
SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
CREATE INDEX t5_idx_000123a7 ON t5(a, b)
CREATE INDEX t6_idx_00000063 ON t6(c)
0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?)
0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}
finish_test
|
> > > > > > > > > > > > > > |
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 |
SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
CREATE INDEX t5_idx_000123a7 ON t5(a, b)
CREATE INDEX t6_idx_00000063 ON t6(c)
0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?)
0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}
# OR terms.
#
do_setup_rec_test 11.1 {
CREATE TABLE t7(a, b);
} {
SELECT * FROM t7 WHERE a=? OR b=?
} {
CREATE INDEX t7_idx_00000061 ON t7(a)
CREATE INDEX t7_idx_00000062 ON t7(b)
0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?)
0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}
finish_test
|