/ Check-in [cf0f7eeb]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Schemalint changes: Avoid creating candidate indexes if a compatible index exists. Do not quote identifiers that do not require it.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1:cf0f7eeb4f6490b1e3f05b45e83b87cd64640846
User & Date: dan 2016-02-17 20:06:12
Context
2016-02-18
19:10
Have the schemalint output distinguish between existing and recommended indexes. check-in: 4ab3df25 user: dan tags: schemalint
2016-02-17
20:06
Schemalint changes: Avoid creating candidate indexes if a compatible index exists. Do not quote identifiers that do not require it. check-in: cf0f7eeb user: dan tags: schemalint
2016-02-16
18:37
Fix further issues in schemalint. check-in: 73a7f010 user: dan tags: schemalint
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell_indexes.c.

29
30
31
32
33
34
35

36
37
38
39
40
41
42
...
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446



447
448
449



450
451
452
453

454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
...
496
497
498
499
500
501
502














503
504
505
506
507
508
509
510
511
512


513




514

515


516

517
518
519













































































520
521
522
523
524
525
526
527

528
529
530
531
532

533
534
535
536
537
538
539
...
541
542
543
544
545
546
547
548




549
550
551
552
553
554
555
556
557
...
661
662
663
664
665
666
667






668
669
670
671
672
673
674
...
792
793
794
795
796
797
798
799

800
801
802
803
804

805
806
807
808
809
**   creating candidate indexes.
*/
struct IdxConstraint {
  char *zColl;                    /* Collation sequence */
  int bRange;                     /* True for range, false for eq */
  int iCol;                       /* Constrained table column */
  i64 depmask;                    /* Dependency mask */

  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
  IdxConstraint *pLink;           /* See above */
};

/*
** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause:
**
................................................................................
  sqlite3 *dbm,                   /* In-memory database to create tables in */
  IdxScan *pScan,                 /* List of scans */
  char **pzErrmsg                 /* OUT: Error message */
){
  int rc = SQLITE_OK;
  IdxScan *pIter;
  for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
    int nPk = 0;
    char *zCols = 0;
    char *zPk = 0;
    char *zCreate = 0;
    int iCol;

    rc = idxGetTableInfo(db, pIter, pzErrmsg);

    for(iCol=0; rc==SQLITE_OK && iCol<pIter->pTable->nCol; iCol++){
      IdxColumn *pCol = &pIter->pTable->aCol[iCol];
      if( pCol->iPk>nPk ) nPk = pCol->iPk;
      zCols = sqlite3_mprintf("%z%s%Q", zCols, (zCols?", ":""), pCol->zName);
      if( zCols==0 ) rc = SQLITE_NOMEM;
    }

    for(iCol=1; rc==SQLITE_OK && iCol<=nPk; iCol++){
      int j;
      for(j=0; j<pIter->pTable->nCol; j++){
        IdxColumn *pCol = &pIter->pTable->aCol[j];
        if( pCol->iPk==iCol ){
          zPk = sqlite3_mprintf("%z%s%Q", zPk, (zPk?", ":""), pCol->zName);
          if( zPk==0 ) rc = SQLITE_NOMEM;
          break;
        }
      }
    }

    if( rc==SQLITE_OK ){
      if( zPk ){
        zCreate = sqlite3_mprintf("CREATE TABLE %Q(%s, PRIMARY KEY(%s))",
            pIter->zTable, zCols, zPk



        );
      }else{
        zCreate = sqlite3_mprintf("CREATE TABLE %Q(%s)", pIter->zTable, zCols);



      }
      if( zCreate==0 ) rc = SQLITE_NOMEM;
    }


    if( rc==SQLITE_OK ){
#if 0
      printf("/* %s */\n", zCreate);
#endif
      rc = sqlite3_exec(dbm, zCreate, 0, 0, pzErrmsg);
    }
    sqlite3_free(zCols);
    sqlite3_free(zPk);
    sqlite3_free(zCreate);
  }
  return rc;
}

/*
** This function is a no-op if *pRc is set to anything other than 
** SQLITE_OK when it is called.
................................................................................
    }
    sqlite3_free(zAppend);
    sqlite3_free(zIn);
  }
  va_end(ap);
  return zRet;
}















static char *idxAppendColDefn(
  int *pRc, 
  char *zIn, 
  IdxTable *pTab, 
  IdxConstraint *pCons
){
  char *zRet = zIn;
  IdxColumn *p = &pTab->aCol[pCons->iCol];
  if( zRet ) zRet = idxAppendText(pRc, zRet, ", ");


  zRet = idxAppendText(pRc, zRet, "%Q", p->zName);




  if( sqlite3_stricmp(p->zColl, pCons->zColl) ){

    zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);


  }

  return zRet;
}














































































static int idxCreateFromCons(
  sqlite3 *dbm,
  IdxScan *pScan,
  IdxConstraint *pEq, 
  IdxConstraint *pTail
){
  int rc = SQLITE_OK;
  if( pEq || pTail ){

    IdxTable *pTab = pScan->pTable;
    char *zCols = 0;
    char *zIdx = 0;
    IdxConstraint *pCons;
    int h = 0;


    for(pCons=pEq; pCons; pCons=pCons->pLink){
      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
    }
    for(pCons=pTail; pCons; pCons=pCons->pLink){
      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
    }
................................................................................
    /* Hash the list of columns to come up with a name for the index */
    if( rc==SQLITE_OK ){
      int i;
      for(i=0; zCols[i]; i++){
        h += ((h<<3) + zCols[i]);
      }

      zIdx = sqlite3_mprintf("CREATE INDEX IF NOT EXISTS "




          "'%q_idx_%08x' ON %Q(%s)", pScan->zTable, h, pScan->zTable, zCols
      );
      if( !zIdx ){
        rc = SQLITE_NOMEM;
      }else{
        rc = sqlite3_exec(dbm, zIdx, 0, 0, 0);
#if 0
        printf("/* %s */\n", zIdx);
#endif
................................................................................

  rc2 = sqlite3_finalize(pDepmask);
  if( rc==SQLITE_OK ) rc = rc2;
  return rc;
}

static void idxScanFree(IdxScan *pScan){






}

int idxFindIndexes(
  sqlite3 *dbm,                        /* Database handle */
  const char *zSql,                    /* SQL to find indexes for */
  void (*xOut)(void*, const char*),    /* Output callback */
  void *pOutCtx,                       /* Context for xOut() */
................................................................................
  }

  /* Create candidate indexes within the in-memory database file */
  if( rc==SQLITE_OK ){
    rc = idxCreateCandidates(dbm, ctx.pScan, pzErrmsg);
  }

  /* Create candidate indexes within the in-memory database file */

  if( rc==SQLITE_OK ){
    rc = idxFindIndexes(dbm, zSql, xOut, pOutCtx, pzErrmsg);
  }

  idxScanFree(ctx.pScan);

  sqlite3_close(dbm);
  return rc;
}









>







 







<
<
<
<
<
<

<
<
<
<
<
<
<
<
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
>
>
>
|
<
<
>
>
>

<
<
<
>
|
<
<
<
<

<
<
<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>










>
>
|
>
>
>
>

>
|
>
>
|
>



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







<
>





>







 







|
>
>
>
>
|
<







 







>
>
>
>
>
>







 







|
>





>





29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
...
410
411
412
413
414
415
416






417








418
419














420
421
422
423


424
425
426
427



428
429




430



431
432
433
434
435
436
437
...
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595

596
597
598
599
600
601
602
603
604
605
606
607
608
609
...
611
612
613
614
615
616
617
618
619
620
621
622
623

624
625
626
627
628
629
630
...
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
...
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
**   creating candidate indexes.
*/
struct IdxConstraint {
  char *zColl;                    /* Collation sequence */
  int bRange;                     /* True for range, false for eq */
  int iCol;                       /* Constrained table column */
  i64 depmask;                    /* Dependency mask */
  int bFlag;                      /* Used by idxFindCompatible() */
  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
  IdxConstraint *pLink;           /* See above */
};

/*
** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause:
**
................................................................................
  sqlite3 *dbm,                   /* In-memory database to create tables in */
  IdxScan *pScan,                 /* List of scans */
  char **pzErrmsg                 /* OUT: Error message */
){
  int rc = SQLITE_OK;
  IdxScan *pIter;
  for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){






    rc = idxGetTableInfo(db, pIter, pzErrmsg);








    if( rc==SQLITE_OK ){
      int rc2;














      sqlite3_stmt *pSql = 0;
      rc = idxPrintfPrepareStmt(db, &pSql, pzErrmsg, 
          "SELECT sql FROM sqlite_master WHERE tbl_name = %Q", pIter->zTable
      );


      while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
        const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
        rc = sqlite3_exec(dbm, zSql, 0, 0, pzErrmsg);
      }



      rc2 = sqlite3_finalize(pSql);
      if( rc==SQLITE_OK ) rc = rc2;




    }



  }
  return rc;
}

/*
** This function is a no-op if *pRc is set to anything other than 
** SQLITE_OK when it is called.
................................................................................
    }
    sqlite3_free(zAppend);
    sqlite3_free(zIn);
  }
  va_end(ap);
  return zRet;
}

static int idxIdentifierRequiresQuotes(const char *zId){
  int i;
  for(i=0; zId[i]; i++){
    if( !(zId[i]=='_')
     && !(zId[i]>='0' && zId[i]<='9')
     && !(zId[i]>='a' && zId[i]<='z')
     && !(zId[i]>='A' && zId[i]<='Z')
    ){
      return 1;
    }
  }
  return 0;
}

static char *idxAppendColDefn(
  int *pRc, 
  char *zIn, 
  IdxTable *pTab, 
  IdxConstraint *pCons
){
  char *zRet = zIn;
  IdxColumn *p = &pTab->aCol[pCons->iCol];
  if( zRet ) zRet = idxAppendText(pRc, zRet, ", ");

  if( idxIdentifierRequiresQuotes(p->zName) ){
    zRet = idxAppendText(pRc, zRet, "%Q", p->zName);
  }else{
    zRet = idxAppendText(pRc, zRet, "%s", p->zName);
  }

  if( sqlite3_stricmp(p->zColl, pCons->zColl) ){
    if( idxIdentifierRequiresQuotes(pCons->zColl) ){
      zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);
    }else{
      zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl);
    }
  }
  return zRet;
}

/*
** Search database dbm for an index compatible with the one idxCreateFromCons()
** would create from arguments pScan, pEq and pTail. If no error occurs and 
** such an index is found, return non-zero. Or, if no such index is found,
** return zero.
**
** If an error occurs, set *pRc to an SQLite error code and return zero.
*/
static int idxFindCompatible(
  int *pRc,                       /* OUT: Error code */
  sqlite3* dbm,                   /* Database to search */
  IdxScan *pScan,                 /* Scan for table to search for index on */
  IdxConstraint *pEq,             /* List of == constraints */
  IdxConstraint *pTail            /* List of range constraints */
){
  const char *zTbl = pScan->zTable;
  sqlite3_stmt *pIdxList = 0;
  IdxConstraint *pIter;
  int nEq = 0;                    /* Number of elements in pEq */
  int rc, rc2;

  /* Count the elements in list pEq */
  for(pIter=pEq; pIter; pIter=pIter->pNext) nEq++;

  rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl);
  while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){
    int bMatch = 1;
    IdxConstraint *pT = pTail;
    sqlite3_stmt *pInfo = 0;
    const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1);

    /* Zero the IdxConstraint.bFlag values in the pEq list */
    for(pIter=pEq; pIter; pIter=pIter->pNext) pIter->bFlag = 0;

    rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx);
    while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){
      int iIdx = sqlite3_column_int(pInfo, 0);
      int iCol = sqlite3_column_int(pInfo, 1);
      const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);

      if( iIdx<nEq ){
        for(pIter=pEq; pIter; pIter=pIter->pNext){
          if( pIter->bFlag ) continue;
          if( pIter->iCol!=iCol ) continue;
          if( sqlite3_stricmp(pIter->zColl, zColl) ) continue;
          pIter->bFlag = 1;
          break;
        }
        if( pIter==0 ){
          bMatch = 0;
          break;
        }
      }else{
        if( pT ){
          if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){
            bMatch = 0;
            break;
          }
          pT = pT->pLink;
        }
      }
    }
    rc2 = sqlite3_finalize(pInfo);
    if( rc==SQLITE_OK ) rc = rc2;

    if( rc==SQLITE_OK && bMatch ){
      sqlite3_finalize(pIdxList);
      return 1;
    }
  }
  rc2 = sqlite3_finalize(pIdxList);
  if( rc==SQLITE_OK ) rc = rc2;

  *pRc = rc;
  return 0;
}

static int idxCreateFromCons(
  sqlite3 *dbm,
  IdxScan *pScan,
  IdxConstraint *pEq, 
  IdxConstraint *pTail
){
  int rc = SQLITE_OK;

  if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){
    IdxTable *pTab = pScan->pTable;
    char *zCols = 0;
    char *zIdx = 0;
    IdxConstraint *pCons;
    int h = 0;
    const char *zFmt;

    for(pCons=pEq; pCons; pCons=pCons->pLink){
      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
    }
    for(pCons=pTail; pCons; pCons=pCons->pLink){
      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
    }
................................................................................
    /* Hash the list of columns to come up with a name for the index */
    if( rc==SQLITE_OK ){
      int i;
      for(i=0; zCols[i]; i++){
        h += ((h<<3) + zCols[i]);
      }

      if( idxIdentifierRequiresQuotes(pScan->zTable) ){
        zFmt = "CREATE INDEX '%q_idx_%08x' ON %Q(%s)";
      }else{
        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, 0);
#if 0
        printf("/* %s */\n", zIdx);
#endif
................................................................................

  rc2 = sqlite3_finalize(pDepmask);
  if( rc==SQLITE_OK ) rc = rc2;
  return rc;
}

static void idxScanFree(IdxScan *pScan){
  IdxScan *pIter;
  IdxScan *pNext;
  for(pIter=pScan; pIter; pIter=pNext){
    pNext = pIter->pNextScan;

  }
}

int idxFindIndexes(
  sqlite3 *dbm,                        /* Database handle */
  const char *zSql,                    /* SQL to find indexes for */
  void (*xOut)(void*, const char*),    /* Output callback */
  void *pOutCtx,                       /* Context for xOut() */
................................................................................
  }

  /* Create candidate indexes within the in-memory database file */
  if( rc==SQLITE_OK ){
    rc = idxCreateCandidates(dbm, ctx.pScan, pzErrmsg);
  }

  /* Figure out which of the candidate indexes are preferred by the query
  ** planner and report the results to the user.  */
  if( rc==SQLITE_OK ){
    rc = idxFindIndexes(dbm, zSql, xOut, pOutCtx, pzErrmsg);
  }

  idxScanFree(ctx.pScan);
  sqlite3_finalize(ctx.pInsertMask);
  sqlite3_close(dbm);
  return rc;
}