/ Check-in [5cd07000]
Login

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: 5cd070000da1d9e399090677b4db75dc5639c33211385d6eb84f14a4d0b617cd
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
Hide Diffs Unified Diffs Show Whitespace Changes Patch

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