SQLite4
Check-in [1710627ec6]
Not logged in

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

Overview
Comment:Add the COVERING clause to CREATE INDEX statements. Does not work yet, this commit just adds parser support.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1710627ec6a12883104e08de7a93e88bf15cab13
User & Date: dan 2013-07-22 18:38:05
Context
2013-07-22
19:49
Fix compiler warnings. check-in: 610033d7f5 user: drh tags: trunk
18:38
Add the COVERING clause to CREATE INDEX statements. Does not work yet, this commit just adds parser support. check-in: 1710627ec6 user: dan tags: trunk
13:24
Disable automatic indices at all times. They do not currently work. Needs to be fixed. check-in: 446fbd37d1 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
....
1337
1338
1339
1340
1341
1342
1343

1344
1345
1346
1347
1348
1349
1350
....
1353
1354
1355
1356
1357
1358
1359

1360
1361
1362
1363
1364
1365
1366
....
1367
1368
1369
1370
1371
1372
1373

1374
1375
1376
1377
1378
1379

1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
....
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
....
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
....
2571
2572
2573
2574
2575
2576
2577























2578
2579
2580
2581
2582
2583
2584
....
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600

2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
....
2613
2614
2615
2616
2617
2618
2619













2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641

2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
....
2704
2705
2706
2707
2708
2709
2710

2711

2712
2713
2714
2715
2716
2717
2718
....
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742

2743
2744
2745
2746
2747
2748
2749
2750
2751
....
2761
2762
2763
2764
2765
2766
2767











2768
2769
2770
2771
2772
2773
2774
....
2890
2891
2892
2893
2894
2895
2896

2897
2898
2899
2900
2901
2902
2903
      if( iCol<pTab->nCol ){
        pTab->aCol[iCol].isPrimKey = i+1;
        pTab->aCol[iCol].notNull = 1;
      }
    }
    if( pList->nExpr>1 ) iCol = -1;
  }
  pPk = sqlite4CreateIndex(
     pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0, 1
  );

  if( iCol>=0 && iCol<pTab->nCol
   && (zType = pTab->aCol[iCol].zType)!=0
   && sqlite4_stricmp(zType, "INTEGER")==0
   && sortOrder==SQLITE4_SO_ASC
   && pPk
  ){
................................................................................
}

static Index *newIndex(
  Parse *pParse,                  /* Parse context for current statement */
  Table *pTab,                    /* Table index is created on */
  const char *zName,              /* Name of index object to create */
  int nCol,                       /* Number of columns in index */

  int onError,                    /* One of OE_Abort, OE_Replace etc. */
  int nExtra,                     /* Bytes of extra space to allocate */
  char **pzExtra                  /* OUT: Pointer to extra space */
){
  sqlite4 *db = pParse->db;       /* Database handle */
  Index *pIndex;                  /* Return value */
  char *zExtra = 0;               /* nExtra bytes of extra space allocated */
................................................................................
  /* Allocate the index structure. */
  nName = sqlite4Strlen30(zName);
  pIndex = sqlite4DbMallocZero(db, 
      ROUND8(sizeof(Index)) +              /* Index structure  */
      ROUND8(sizeof(tRowcnt)*(nCol+1)) +   /* Index.aiRowEst   */
      sizeof(char *)*nCol +                /* Index.azColl     */
      sizeof(int)*nCol +                   /* Index.aiColumn   */

      sizeof(u8)*nCol +                    /* Index.aSortOrder */
      nName + 1 +                          /* Index.zName      */
      nExtra                               /* Collation sequence names */
  );
  assert( pIndex || db->mallocFailed );

  if( pIndex ){
................................................................................
    zExtra = (char*)pIndex;
    pIndex->aiRowEst = (tRowcnt*)&zExtra[ROUND8(sizeof(Index))];
    pIndex->azColl = (char**)
      ((char*)pIndex->aiRowEst + ROUND8(sizeof(tRowcnt)*nCol+1));
    assert( EIGHT_BYTE_ALIGNMENT(pIndex->aiRowEst) );
    assert( EIGHT_BYTE_ALIGNMENT(pIndex->azColl) );
    pIndex->aiColumn = (int *)(&pIndex->azColl[nCol]);

    pIndex->aSortOrder = (u8 *)(&pIndex->aiColumn[nCol]);
    pIndex->zName = (char *)(&pIndex->aSortOrder[nCol]);
    zExtra = (char *)(&pIndex->zName[nName+1]);
    memcpy(pIndex->zName, zName, nName+1);
    pIndex->pTable = pTab;
    pIndex->nColumn = nCol;

    pIndex->onError = (u8)onError;
    pIndex->pSchema = pTab->pSchema;

  }

  *pzExtra = zExtra;
  return pIndex;
}

static int addIndexToHash(sqlite4 *db, Index *pIdx){
................................................................................
){
  sqlite4 *db = pParse->db;
  Index *pIndex;                  /* New index */
  char *zExtra;

  assert( !pTab->pIndex || pTab->pIndex->eIndexType!=SQLITE4_INDEX_PRIMARYKEY );
  assert( sqlite4Strlen30("binary")==6 );
  pIndex = newIndex(pParse, pTab, pTab->zName, 1, OE_Abort, 1+6, &zExtra);
  if( pIndex && addIndexToHash(db, pIndex) ){
    sqlite4DbFree(db, pIndex);
    pIndex = 0;
  }
  if( pIndex ){
    pIndex->aiColumn[0] = -1;
    pIndex->azColl[0] = zExtra;
................................................................................
    char *zIdx = 0;
    int iDb = 0;

    pTab = createIndexFindTable(pParse, p, &pIdxName, &zIdx, &iDb);
    if( pTab && 0==createIndexAuth(pParse, pTab, zIdx) ){
      int nExtra = sqlite4Fts5IndexSz();
      char *zExtra = 0;
      pIdx = newIndex(pParse, pTab, zIdx, 0, 0, nExtra, &zExtra);
      if( pIdx ){
        pIdx->pFts = (Fts5Index *)zExtra;
        sqlite4Fts5IndexInit(pParse, pIdx, pList);
        pIdx->eIndexType = SQLITE4_INDEX_FTS5;
      }
    }

................................................................................
    if( pIdx ) freeIndex(db, pIdx);
    sqlite4DbFree(db, zIdx);
  }

  sqlite4ExprListDelete(db, pList);
  sqlite4SrcListDelete(db, p->pTblName);
}
























/*
** Create a new index for an SQL table.  pName1.pName2 is the name of the index 
** and pTblList is the name of the table that is to be indexed.  Both will 
** be NULL for a primary key or an index that is created to satisfy a
** UNIQUE constraint.  If pTable and pIndex are NULL, use pParse->pNewTable
** as the table to be indexed.  pParse->pNewTable is a table that is
................................................................................
**
** If the index is created successfully, return a pointer to the new Index
** structure. This is used by sqlite4AddPrimaryKey() to mark the index
** as the tables primary key (Index.autoIndex==2).
*/
Index *sqlite4CreateIndex(
  Parse *pParse,     /* All information about this parse */
  Token *pName1,     /* First part of index name. May be NULL */
  Token *pName2,     /* Second part of index name. May be NULL */
  SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */
  ExprList *pList,   /* A list of columns to be indexed */

  int onError,       /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  Token *pStart,     /* The CREATE token that begins this statement */
  Token *pEnd,       /* The ")" that closes the CREATE INDEX statement */
  int sortOrder,     /* Sort order of primary key when pList==NULL */
  int ifNotExist,    /* Omit error if index already exists */
  int bPrimaryKey    /* True to create the tables primary key */
){
  Index *pRet = 0;     /* Pointer to return */
  Table *pTab = 0;     /* Table to be indexed */
  Index *pIndex = 0;   /* The index to be created */
  char *zName = 0;     /* Name of the index */
  int i, j;
................................................................................
  Token nullId;        /* Fake token for an empty ID list */
  sqlite4 *db = pParse->db;
  int iDb;             /* Index of the database that is being written */
  Token *pName = 0;    /* Unqualified name of the index to create */
  ExprListItem *pListItem; /* For looping over pList */
  int nExtra = 0;
  char *zExtra;














  assert( pStart==0 || pEnd!=0 ); /* pEnd must be non-NULL if pStart is */
  assert( pParse->nErr==0 );      /* Never called with prior errors */
  if( db->mallocFailed || IN_DECLARE_VTAB ){
    goto exit_create_index;
  }
  if( SQLITE4_OK!=sqlite4ReadSchema(pParse) ){
    goto exit_create_index;
  }

  /*
  ** Find the table that is to be indexed.  Return early if not found.
  */
  if( pTblName!=0 ){
    CreateIndex sCreate;
    sCreate.bUnique = onError;
    sCreate.bIfnotexist = ifNotExist;
    sCreate.tCreate = *pStart;
    sCreate.tName1 = *pName1;
    sCreate.tName2 = *pName2;
    sCreate.pTblName = pTblName;


    pTab = createIndexFindTable(pParse, &sCreate, &pName, &zName, &iDb);
    if( !pTab ) goto exit_create_index;

  }else{

    assert( pName==0 );
    assert( pStart==0 );
    pTab = pParse->pNewTable;
    if( !pTab ) goto exit_create_index;
    iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
  }

  assert( pTab!=0 );
  assert( pParse->nErr==0 );
  assert( IsVirtual(pTab)==0 && IsView(pTab)==0 );

  /* If pName==0 it means that we are dealing with a primary key or 
  ** UNIQUE constraint.  We have to invent our own name.  */
  if( pName==0 ){
    if( !bPrimaryKey ){
      int n;
      Index *pLoop;
      for(pLoop=pTab->pIndex, n=1; pLoop; pLoop=pLoop->pNext, n++){}
      zName = sqlite4MPrintf(db, "sqlite_%s_unique%d", pTab->zName, n);
    }else{
      zName = sqlite4MPrintf(db, "%s", pTab->zName);
................................................................................
      if( ALWAYS(pColl) ){
        nExtra += (1 + sqlite4Strlen30(pColl->zName));
      }
    }
  }

  /* Allocate the new Index structure. */

  pIndex = newIndex(pParse, pTab, zName, pList->nExpr, onError, nExtra,&zExtra);

  if( !pIndex ) goto exit_create_index;

  assert( pIndex->eIndexType==SQLITE4_INDEX_USER );
  if( pName==0 ){
    if( bPrimaryKey ){
      pIndex->eIndexType = SQLITE4_INDEX_PRIMARYKEY;
    }else{
................................................................................
  ** if any column is not found.
  **
  ** TODO:  Add a test to make sure that the same column is not named
  ** more than once within the same index.  Only the first instance of
  ** the column will ever be used by the optimizer.
  */
  for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){
    const char *zColName = pListItem->zName;
    Column *pTabCol;
    char *zColl;                   /* Collation sequence name */

    for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){
      if( sqlite4_stricmp(zColName, pTabCol->zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqlite4ErrorMsg(pParse, "table %s has no column named %s",
        pTab->zName, zColName);
      pParse->checkSchema = 1;

      goto exit_create_index;
    }
    pIndex->aiColumn[i] = j;
    if( pListItem->pExpr && pListItem->pExpr->pColl ){
      int nColl;
      zColl = pListItem->pExpr->pColl->zName;
      nColl = sqlite4Strlen30(zColl) + 1;
      assert( nExtra>=nColl );
      memcpy(zExtra, zColl, nColl);
................................................................................
    if( !db->init.busy && !sqlite4LocateCollSeq(pParse, zColl) ){
      goto exit_create_index;
    }
    pIndex->azColl[i] = zColl;
    pIndex->aSortOrder[i] = (u8)pListItem->sortOrder;
  }
  sqlite4DefaultRowEst(pIndex);












  if( pTab==pParse->pNewTable ){
    /* This routine has been called to create an automatic index as a
    ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
    ** a PRIMARY KEY or UNIQUE clause following the column definitions.
    ** i.e. one of:
    **
................................................................................
exit_create_index:
  if( pIndex ){
    sqlite4DbFree(db, pIndex->zColAff);
    sqlite4DbFree(db, pIndex);
  }
  sqlite4ExprListDelete(db, pList);
  sqlite4SrcListDelete(db, pTblName);

  sqlite4DbFree(db, zName);
  return pRet;
}

/*
** Fill the Index.aiRowEst[] array with default information - information
** to be used when we have not run the ANALYZE command.







|
<
<







 







>







 







>







 







>
|





>


<







 







|







 







|







 







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







 







|
<
<

>

<


<







 







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










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

<

<











|

|







 







>
|
>







 







<
<


<
<
<
<
<
<
<
>
|
|







 







>
>
>
>
>
>
>
>
>
>
>







 







>







1073
1074
1075
1076
1077
1078
1079
1080


1081
1082
1083
1084
1085
1086
1087
....
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
....
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
....
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383

1384
1385
1386
1387
1388
1389
1390
....
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
....
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
....
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
....
2614
2615
2616
2617
2618
2619
2620
2621


2622
2623
2624

2625
2626

2627
2628
2629
2630
2631
2632
2633
....
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663

2664










2665
2666
2667

2668

2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
....
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
....
2749
2750
2751
2752
2753
2754
2755


2756
2757







2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
....
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
....
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
      if( iCol<pTab->nCol ){
        pTab->aCol[iCol].isPrimKey = i+1;
        pTab->aCol[iCol].notNull = 1;
      }
    }
    if( pList->nExpr>1 ) iCol = -1;
  }
  pPk = sqlite4CreateIndex(pParse, 0, pList, 0, onError, 0, sortOrder, 1);



  if( iCol>=0 && iCol<pTab->nCol
   && (zType = pTab->aCol[iCol].zType)!=0
   && sqlite4_stricmp(zType, "INTEGER")==0
   && sortOrder==SQLITE4_SO_ASC
   && pPk
  ){
................................................................................
}

static Index *newIndex(
  Parse *pParse,                  /* Parse context for current statement */
  Table *pTab,                    /* Table index is created on */
  const char *zName,              /* Name of index object to create */
  int nCol,                       /* Number of columns in index */
  int nCover,                     /* Number of covered columns in index */
  int onError,                    /* One of OE_Abort, OE_Replace etc. */
  int nExtra,                     /* Bytes of extra space to allocate */
  char **pzExtra                  /* OUT: Pointer to extra space */
){
  sqlite4 *db = pParse->db;       /* Database handle */
  Index *pIndex;                  /* Return value */
  char *zExtra = 0;               /* nExtra bytes of extra space allocated */
................................................................................
  /* Allocate the index structure. */
  nName = sqlite4Strlen30(zName);
  pIndex = sqlite4DbMallocZero(db, 
      ROUND8(sizeof(Index)) +              /* Index structure  */
      ROUND8(sizeof(tRowcnt)*(nCol+1)) +   /* Index.aiRowEst   */
      sizeof(char *)*nCol +                /* Index.azColl     */
      sizeof(int)*nCol +                   /* Index.aiColumn   */
      sizeof(int)*nCover +                 /* Index.aiCover    */
      sizeof(u8)*nCol +                    /* Index.aSortOrder */
      nName + 1 +                          /* Index.zName      */
      nExtra                               /* Collation sequence names */
  );
  assert( pIndex || db->mallocFailed );

  if( pIndex ){
................................................................................
    zExtra = (char*)pIndex;
    pIndex->aiRowEst = (tRowcnt*)&zExtra[ROUND8(sizeof(Index))];
    pIndex->azColl = (char**)
      ((char*)pIndex->aiRowEst + ROUND8(sizeof(tRowcnt)*nCol+1));
    assert( EIGHT_BYTE_ALIGNMENT(pIndex->aiRowEst) );
    assert( EIGHT_BYTE_ALIGNMENT(pIndex->azColl) );
    pIndex->aiColumn = (int *)(&pIndex->azColl[nCol]);
    pIndex->aiCover = (int *)(&pIndex->aiColumn[nCol]);
    pIndex->aSortOrder = (u8 *)(&pIndex->aiCover[nCover]);
    pIndex->zName = (char *)(&pIndex->aSortOrder[nCol]);
    zExtra = (char *)(&pIndex->zName[nName+1]);
    memcpy(pIndex->zName, zName, nName+1);
    pIndex->pTable = pTab;
    pIndex->nColumn = nCol;
    pIndex->nCover = nCover;
    pIndex->onError = (u8)onError;
    pIndex->pSchema = pTab->pSchema;

  }

  *pzExtra = zExtra;
  return pIndex;
}

static int addIndexToHash(sqlite4 *db, Index *pIdx){
................................................................................
){
  sqlite4 *db = pParse->db;
  Index *pIndex;                  /* New index */
  char *zExtra;

  assert( !pTab->pIndex || pTab->pIndex->eIndexType!=SQLITE4_INDEX_PRIMARYKEY );
  assert( sqlite4Strlen30("binary")==6 );
  pIndex = newIndex(pParse, pTab, pTab->zName, 1, 0, OE_Abort, 1+6, &zExtra);
  if( pIndex && addIndexToHash(db, pIndex) ){
    sqlite4DbFree(db, pIndex);
    pIndex = 0;
  }
  if( pIndex ){
    pIndex->aiColumn[0] = -1;
    pIndex->azColl[0] = zExtra;
................................................................................
    char *zIdx = 0;
    int iDb = 0;

    pTab = createIndexFindTable(pParse, p, &pIdxName, &zIdx, &iDb);
    if( pTab && 0==createIndexAuth(pParse, pTab, zIdx) ){
      int nExtra = sqlite4Fts5IndexSz();
      char *zExtra = 0;
      pIdx = newIndex(pParse, pTab, zIdx, 0, 0, 0, nExtra, &zExtra);
      if( pIdx ){
        pIdx->pFts = (Fts5Index *)zExtra;
        sqlite4Fts5IndexInit(pParse, pIdx, pList);
        pIdx->eIndexType = SQLITE4_INDEX_FTS5;
      }
    }

................................................................................
    if( pIdx ) freeIndex(db, pIdx);
    sqlite4DbFree(db, zIdx);
  }

  sqlite4ExprListDelete(db, pList);
  sqlite4SrcListDelete(db, p->pTblName);
}

/*
** Parameter zName points to a nul-terminated buffer containing a column
** name. If table pTab has a column of the specified name, return its
** index (the first table column being 0, the next 1 etc.). Or, if there
** is no such column, leave an error in pParse and return -1.
*/
static int findTableColumn(Parse *pParse, Table *pTab, const char *zName){
  int j;
  Column *pTabCol;

  for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){
    if( sqlite4_stricmp(zName, pTabCol->zName)==0 ) break;
  }
  if( j>=pTab->nCol ){
    sqlite4ErrorMsg(pParse, "table %s has no column named %s",
        pTab->zName, zName);
    pParse->checkSchema = 1;
    return -1;
  }

  return j;
}

/*
** Create a new index for an SQL table.  pName1.pName2 is the name of the index 
** and pTblList is the name of the table that is to be indexed.  Both will 
** be NULL for a primary key or an index that is created to satisfy a
** UNIQUE constraint.  If pTable and pIndex are NULL, use pParse->pNewTable
** as the table to be indexed.  pParse->pNewTable is a table that is
................................................................................
**
** If the index is created successfully, return a pointer to the new Index
** structure. This is used by sqlite4AddPrimaryKey() to mark the index
** as the tables primary key (Index.autoIndex==2).
*/
Index *sqlite4CreateIndex(
  Parse *pParse,     /* All information about this parse */
  CreateIndex *pCI,  /* Name of index to create etc. */


  ExprList *pList,   /* A list of columns to be indexed */
  IdList *pCovering, /* Covering list (or NULL) */
  int onError,       /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */

  Token *pEnd,       /* The ")" that closes the CREATE INDEX statement */
  int sortOrder,     /* Sort order of primary key when pList==NULL */

  int bPrimaryKey    /* True to create the tables primary key */
){
  Index *pRet = 0;     /* Pointer to return */
  Table *pTab = 0;     /* Table to be indexed */
  Index *pIndex = 0;   /* The index to be created */
  char *zName = 0;     /* Name of the index */
  int i, j;
................................................................................
  Token nullId;        /* Fake token for an empty ID list */
  sqlite4 *db = pParse->db;
  int iDb;             /* Index of the database that is being written */
  Token *pName = 0;    /* Unqualified name of the index to create */
  ExprListItem *pListItem; /* For looping over pList */
  int nExtra = 0;
  char *zExtra;

  int nCover = 0;
  Token *pStart = 0;
  SrcList *pTblName = 0;

  if( pCI ){
    pStart = &pCI->tCreate;
    pTblName = pCI->pTblName;
  }
  if( pCovering ){
    nCover = pCovering->nId;
    if( nCover==0 ) nCover = pList->nExpr;
  }

  assert( pStart==0 || pEnd!=0 ); /* pEnd must be non-NULL if pStart is */
  assert( pParse->nErr==0 );      /* Never called with prior errors */
  if( db->mallocFailed || IN_DECLARE_VTAB ){
    goto exit_create_index;
  }
  if( SQLITE4_OK!=sqlite4ReadSchema(pParse) ){
    goto exit_create_index;
  }


  /* Find the table that is to be indexed.  Return early if not found. */










  if( pCI ){
    pTab = createIndexFindTable(pParse, pCI, &pName, &zName, &iDb);
    if( !pTab ) goto exit_create_index;

  }else{

    assert( pName==0 );
    assert( pStart==0 );
    pTab = pParse->pNewTable;
    if( !pTab ) goto exit_create_index;
    iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
  }

  assert( pTab!=0 );
  assert( pParse->nErr==0 );
  assert( IsVirtual(pTab)==0 && IsView(pTab)==0 );

  /* If pCI==0 it means that we are dealing with a PRIMARY KEY or 
  ** UNIQUE constraint.  We have to invent our own name.  */
  if( pCI==0 ){
    if( !bPrimaryKey ){
      int n;
      Index *pLoop;
      for(pLoop=pTab->pIndex, n=1; pLoop; pLoop=pLoop->pNext, n++){}
      zName = sqlite4MPrintf(db, "sqlite_%s_unique%d", pTab->zName, n);
    }else{
      zName = sqlite4MPrintf(db, "%s", pTab->zName);
................................................................................
      if( ALWAYS(pColl) ){
        nExtra += (1 + sqlite4Strlen30(pColl->zName));
      }
    }
  }

  /* Allocate the new Index structure. */
  pIndex = newIndex(
      pParse, pTab, zName, pList->nExpr, nCover, onError, nExtra, &zExtra
  );
  if( !pIndex ) goto exit_create_index;

  assert( pIndex->eIndexType==SQLITE4_INDEX_USER );
  if( pName==0 ){
    if( bPrimaryKey ){
      pIndex->eIndexType = SQLITE4_INDEX_PRIMARYKEY;
    }else{
................................................................................
  ** if any column is not found.
  **
  ** TODO:  Add a test to make sure that the same column is not named
  ** more than once within the same index.  Only the first instance of
  ** the column will ever be used by the optimizer.
  */
  for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){


    char *zColl;                   /* Collation sequence name */








    j = findTableColumn(pParse, pTab, pListItem->zName);
    if( j<0 ) goto exit_create_index;

    pIndex->aiColumn[i] = j;
    if( pListItem->pExpr && pListItem->pExpr->pColl ){
      int nColl;
      zColl = pListItem->pExpr->pColl->zName;
      nColl = sqlite4Strlen30(zColl) + 1;
      assert( nExtra>=nColl );
      memcpy(zExtra, zColl, nColl);
................................................................................
    if( !db->init.busy && !sqlite4LocateCollSeq(pParse, zColl) ){
      goto exit_create_index;
    }
    pIndex->azColl[i] = zColl;
    pIndex->aSortOrder[i] = (u8)pListItem->sortOrder;
  }
  sqlite4DefaultRowEst(pIndex);

  /* Scan the names of any covered columns. */
  for(i=0; i<nCover; i++){
    if( pCovering->nId ){
      j = findTableColumn(pParse, pTab, pCovering->a[i].zName);
      if( j<0 ) goto exit_create_index;
      pIndex->aiCover[i] = j;
    }else{
      pIndex->aiCover[i] = i;
    }
  }

  if( pTab==pParse->pNewTable ){
    /* This routine has been called to create an automatic index as a
    ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
    ** a PRIMARY KEY or UNIQUE clause following the column definitions.
    ** i.e. one of:
    **
................................................................................
exit_create_index:
  if( pIndex ){
    sqlite4DbFree(db, pIndex->zColAff);
    sqlite4DbFree(db, pIndex);
  }
  sqlite4ExprListDelete(db, pList);
  sqlite4SrcListDelete(db, pTblName);
  sqlite4IdListDelete(db, pCovering);
  sqlite4DbFree(db, zName);
  return pRet;
}

/*
** Fill the Index.aiRowEst[] array with default information - information
** to be used when we have not run the ANALYZE command.

Changes to src/parse.y.

98
99
100
101
102
103
104





105
106
107
108
109
110
111
...
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
...
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
....
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
....
1203
1204
1205
1206
1207
1208
1209












1210
1211
1212
1213
1214
1215
1216
** One or more VALUES claues
*/
struct ValueList {
  ExprList *pList;
  Select *pSelect;
};






} // end %include

// Input is a single SQL command
input ::= cmdlist.
cmdlist ::= cmdlist ecmd.
cmdlist ::= ecmd.
ecmd ::= SEMI.
................................................................................
// In addition to the type name, we also care about the primary key and
// UNIQUE constraints.
//
ccons ::= NULL onconf.
ccons ::= NOT NULL onconf(R).  {sqlite4AddNotNull(pParse, R);}
ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I).
                               {sqlite4AddPrimaryKey(pParse,0,R,I,Z);}
ccons ::= UNIQUE onconf(R).    {sqlite4CreateIndex(pParse,0,0,0,0,R,0,0,0,0,0);}
ccons ::= CHECK LP expr(X) RP. {sqlite4AddCheckConstraint(pParse,X.pExpr);}
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
                               {sqlite4CreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D).  {sqlite4DeferForeignKey(pParse,D);}
ccons ::= COLLATE ids(C).      {sqlite4AddCollateType(pParse, &C);}

// The optional AUTOINCREMENT keyword
................................................................................
conslist ::= conslist COMMA tcons.
conslist ::= conslist tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT nm.
tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
                             {sqlite4AddPrimaryKey(pParse,X,R,I,0);}
tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
                             {sqlite4CreateIndex(pParse,0,0,0,X,R,0,0,0,0,0);}
tcons ::= CHECK LP expr(E) RP onconf.
                             {sqlite4AddCheckConstraint(pParse,E.pExpr);}
tcons ::= FOREIGN KEY LP idxlist(FA) RP
          REFERENCES nm(T) idxlist_opt(TA) refargs(R) defer_subclause_opt(D). {
    sqlite4CreateForeignKey(pParse, FA, &T, TA, R);
    sqlite4DeferForeignKey(pParse, D);
}
................................................................................
  C.bIfnotexist = NE;
  C.tCreate = S;
  C.tName1 = X;
  C.tName2 = D;
  C.pTblName = sqlite4SrcListAppend(pParse->db, 0, &Y, 0);
}

cmd ::= createindex(C) LP idxlist(Z) RP(E). {
  sqlite4CreateIndex(pParse, &C.tName1, &C.tName2, C.pTblName, Z, 
                C.bUnique, &C.tCreate, &E, SQLITE4_SO_ASC, C.bIfnotexist, 0);
}

%type uniqueflag {int}
uniqueflag(A) ::= UNIQUE.  {A = OE_Abort;}
uniqueflag(A) ::= .        {A = OE_None;}

%type idxlist {ExprList*}
................................................................................
uidxlist(A) ::= ids(X) EQ ids(Y). {
  A = sqlite4ExprListAppend(pParse, 0, sqlite4PExpr(pParse, @Y, 0, 0, &Y));
  sqlite4ExprListSetName(pParse, A, &X, 1);
}
uidxlist(A) ::= ids(Y). {
  A = sqlite4ExprListAppend(pParse, 0, sqlite4PExpr(pParse, @Y, 0, 0, &Y));
}














///////////////////////////// The DROP INDEX command /////////////////////////
//
cmd ::= DROP INDEX ifexists(E) fullname(X).   {sqlite4DropIndex(pParse, X, E);}

///////////////////////////// The PRAGMA command /////////////////////////////







>
>
>
>
>







 







|







 







|







 







|
|
|







 







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







98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
...
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
...
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
....
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
....
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
** One or more VALUES claues
*/
struct ValueList {
  ExprList *pList;
  Select *pSelect;
};

/*
** A COVERING clause.
*/
struct CoveringOpt { IdList *pList; Token sEnd; };

} // end %include

// Input is a single SQL command
input ::= cmdlist.
cmdlist ::= cmdlist ecmd.
cmdlist ::= ecmd.
ecmd ::= SEMI.
................................................................................
// In addition to the type name, we also care about the primary key and
// UNIQUE constraints.
//
ccons ::= NULL onconf.
ccons ::= NOT NULL onconf(R).  {sqlite4AddNotNull(pParse, R);}
ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I).
                               {sqlite4AddPrimaryKey(pParse,0,R,I,Z);}
ccons ::= UNIQUE onconf(R).    {sqlite4CreateIndex(pParse,0,0,0,R,0,0,0);}
ccons ::= CHECK LP expr(X) RP. {sqlite4AddCheckConstraint(pParse,X.pExpr);}
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
                               {sqlite4CreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D).  {sqlite4DeferForeignKey(pParse,D);}
ccons ::= COLLATE ids(C).      {sqlite4AddCollateType(pParse, &C);}

// The optional AUTOINCREMENT keyword
................................................................................
conslist ::= conslist COMMA tcons.
conslist ::= conslist tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT nm.
tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
                             {sqlite4AddPrimaryKey(pParse,X,R,I,0);}
tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
                             {sqlite4CreateIndex(pParse,0,X,0,R,0,0,0);}
tcons ::= CHECK LP expr(E) RP onconf.
                             {sqlite4AddCheckConstraint(pParse,E.pExpr);}
tcons ::= FOREIGN KEY LP idxlist(FA) RP
          REFERENCES nm(T) idxlist_opt(TA) refargs(R) defer_subclause_opt(D). {
    sqlite4CreateForeignKey(pParse, FA, &T, TA, R);
    sqlite4DeferForeignKey(pParse, D);
}
................................................................................
  C.bIfnotexist = NE;
  C.tCreate = S;
  C.tName1 = X;
  C.tName2 = D;
  C.pTblName = sqlite4SrcListAppend(pParse->db, 0, &Y, 0);
}

cmd ::= createindex(C) LP idxlist(Z) RP(E) covering_opt(F). {
  Token *pEnd = (F.pList ? &F.sEnd : &E);
  sqlite4CreateIndex(pParse, &C, Z, F.pList, C.bUnique, pEnd, SQLITE4_SO_ASC,0);
}

%type uniqueflag {int}
uniqueflag(A) ::= UNIQUE.  {A = OE_Abort;}
uniqueflag(A) ::= .        {A = OE_None;}

%type idxlist {ExprList*}
................................................................................
uidxlist(A) ::= ids(X) EQ ids(Y). {
  A = sqlite4ExprListAppend(pParse, 0, sqlite4PExpr(pParse, @Y, 0, 0, &Y));
  sqlite4ExprListSetName(pParse, A, &X, 1);
}
uidxlist(A) ::= ids(Y). {
  A = sqlite4ExprListAppend(pParse, 0, sqlite4PExpr(pParse, @Y, 0, 0, &Y));
}

%type covering_opt { struct CoveringOpt }
%destructor covering_opt {sqlite4IdListDelete(pParse->db, $$.pList);}
covering_opt(A) ::= . { A.pList = 0; }
covering_opt(A) ::= COVERING ALL(X). { 
  A.pList = sqlite4DbMallocZero(pParse->db, sizeof(IdList)); 
  A.sEnd = X;
}
covering_opt(A) ::= COVERING LP inscollist(X) RP(Y). { 
  A.pList = X; 
  A.sEnd = Y;
}


///////////////////////////// The DROP INDEX command /////////////////////////
//
cmd ::= DROP INDEX ifexists(E) fullname(X).   {sqlite4DropIndex(pParse, X, E);}

///////////////////////////// The PRAGMA command /////////////////////////////

Changes to src/sqliteInt.h.

1384
1385
1386
1387
1388
1389
1390


1391
1392
1393
1394
1395
1396
1397
....
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
** algorithm to employ whenever an attempt is made to insert a non-unique
** element.
*/
struct Index {
  char *zName;     /* Name of this index */
  int nColumn;     /* Number of columns in the table used by this index */
  int *aiColumn;   /* Which columns are used by this index.  1st is 0 */


  tRowcnt *aiRowEst; /* Result of ANALYZE: Est. rows selected by each column */
  Table *pTable;   /* The SQL table being indexed */
  int tnum;        /* Page containing root of this index in database file */
  u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  u8 eIndexType;   /* SQLITE4_INDEX_USER, UNIQUE or PRIMARYKEY */
  u8 fIndex;       /* One or more of the IDX_* flags below */
  char *zColAff;   /* String defining the affinity of each column */
................................................................................
                                      Token*, Select*, Expr*, IdList*);
void sqlite4SrcListIndexedBy(Parse *, SrcList *, Token *);
int sqlite4IndexedByLookup(Parse *, SrcListItem *);
void sqlite4SrcListShiftJoinType(SrcList*);
void sqlite4SrcListAssignCursors(Parse*, SrcList*);
void sqlite4IdListDelete(sqlite4*, IdList*);
void sqlite4SrcListDelete(sqlite4*, SrcList*);
Index *sqlite4CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*, int, int, int);
void sqlite4DropIndex(Parse*, SrcList*, int);
int sqlite4Select(Parse*, Select*, SelectDest*);
Select *sqlite4SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,int,Expr*,Expr*);
void sqlite4SelectDelete(sqlite4*, Select*);
Table *sqlite4SrcListLookup(Parse*, SrcList*);
int sqlite4IsReadOnly(Parse*, Table*, int);







>
>







 







|
<







1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
....
2757
2758
2759
2760
2761
2762
2763
2764

2765
2766
2767
2768
2769
2770
2771
** algorithm to employ whenever an attempt is made to insert a non-unique
** element.
*/
struct Index {
  char *zName;     /* Name of this index */
  int nColumn;     /* Number of columns in the table used by this index */
  int *aiColumn;   /* Which columns are used by this index.  1st is 0 */
  int nCover;      /* Number of covering columns in this index */
  int *aiCover;    /* Which columns are covered by this index */
  tRowcnt *aiRowEst; /* Result of ANALYZE: Est. rows selected by each column */
  Table *pTable;   /* The SQL table being indexed */
  int tnum;        /* Page containing root of this index in database file */
  u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  u8 eIndexType;   /* SQLITE4_INDEX_USER, UNIQUE or PRIMARYKEY */
  u8 fIndex;       /* One or more of the IDX_* flags below */
  char *zColAff;   /* String defining the affinity of each column */
................................................................................
                                      Token*, Select*, Expr*, IdList*);
void sqlite4SrcListIndexedBy(Parse *, SrcList *, Token *);
int sqlite4IndexedByLookup(Parse *, SrcListItem *);
void sqlite4SrcListShiftJoinType(SrcList*);
void sqlite4SrcListAssignCursors(Parse*, SrcList*);
void sqlite4IdListDelete(sqlite4*, IdList*);
void sqlite4SrcListDelete(sqlite4*, SrcList*);
Index *sqlite4CreateIndex(Parse*,CreateIndex*,ExprList*,IdList*,int,Token*,int,int);

void sqlite4DropIndex(Parse*, SrcList*, int);
int sqlite4Select(Parse*, Select*, SelectDest*);
Select *sqlite4SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,int,Expr*,Expr*);
void sqlite4SelectDelete(sqlite4*, Select*);
Table *sqlite4SrcListLookup(Parse*, SrcList*);
int sqlite4IsReadOnly(Parse*, Table*, int);

Added test/covidx.test.











































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# 2013 July 23
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the COVERING clause of the CREATE 
# INDEX statement.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix covidx

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2("123abc", "odd column name");
  CREATE TABLE "frivolous table name"(x, y);
}

foreach {tn sql res} {
  1 { t1(a) COVERING(a, b, c) }                   {0 {}}
  2 { t1(a) COVERING ALL }                        {0 {}}
  3 { t2("123abc") COVERING ("odd column name") } {0 {}}
  4 { "frivolous table name"(x) COVERING (y) }    {0 {}}

  5 { t1(c) COVERING (d) } {1 {table t1 has no column named d}}
  6 { t1(c) COVERING () }  {1 {near ")": syntax error}}
} {
  do_catchsql_test 1.$tn "CREATE INDEX i1 ON $sql" $res
  execsql { 
    DROP INDEX IF EXISTS i1;
  }
}

do_execsql_test 2.1 {
  CREATE TABLE x1(x);
  CREATE INDEX xi1 ON x1(x) COVERING (x);
  CREATE INDEX xi2 ON x1(x) COVERING ALL   ;

  SELECT sql FROM sqlite_master where type = 'index';
} {
  {CREATE INDEX xi1 ON x1(x) COVERING (x)}
  {CREATE INDEX xi2 ON x1(x) COVERING ALL}
}

finish_test

Changes to test/permutations.test.

156
157
158
159
160
161
162

163
164
165
166
167
168
169
  capi2.test
  cast.test
  check.test
  coalesce.test 
  collate1.test collate2.test collate3.test collate4.test collate5.test
  collate6.test collate7.test collate8.test collate9.test collateA.test
  collateerr.test

  conflict.test 
  count.test
  createtab.test
  cse.test
  ctime.test
  date.test
  default.test







>







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
  capi2.test
  cast.test
  check.test
  coalesce.test 
  collate1.test collate2.test collate3.test collate4.test collate5.test
  collate6.test collate7.test collate8.test collate9.test collateA.test
  collateerr.test
  covidx.test
  conflict.test 
  count.test
  createtab.test
  cse.test
  ctime.test
  date.test
  default.test

Changes to tool/mkkeywordhash.c.

158
159
160
161
162
163
164

165
166
167
168
169
170
171
  { "CAST",             "TK_CAST",         CAST                   },
  { "CHECK",            "TK_CHECK",        ALWAYS                 },
  { "COLLATE",          "TK_COLLATE",      ALWAYS                 },
  { "COLUMN",           "TK_COLUMNKW",     ALTER                  },
  { "COMMIT",           "TK_COMMIT",       ALWAYS                 },
  { "CONFLICT",         "TK_CONFLICT",     CONFLICT               },
  { "CONSTRAINT",       "TK_CONSTRAINT",   ALWAYS                 },

  { "CREATE",           "TK_CREATE",       ALWAYS                 },
  { "CROSS",            "TK_JOIN_KW",      ALWAYS                 },
  { "CURRENT_DATE",     "TK_CTIME_KW",     ALWAYS                 },
  { "CURRENT_TIME",     "TK_CTIME_KW",     ALWAYS                 },
  { "CURRENT_TIMESTAMP","TK_CTIME_KW",     ALWAYS                 },
  { "DATABASE",         "TK_DATABASE",     ATTACH                 },
  { "DEFAULT",          "TK_DEFAULT",      ALWAYS                 },







>







158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
  { "CAST",             "TK_CAST",         CAST                   },
  { "CHECK",            "TK_CHECK",        ALWAYS                 },
  { "COLLATE",          "TK_COLLATE",      ALWAYS                 },
  { "COLUMN",           "TK_COLUMNKW",     ALTER                  },
  { "COMMIT",           "TK_COMMIT",       ALWAYS                 },
  { "CONFLICT",         "TK_CONFLICT",     CONFLICT               },
  { "CONSTRAINT",       "TK_CONSTRAINT",   ALWAYS                 },
  { "COVERING",         "TK_COVERING",     ALWAYS                 },
  { "CREATE",           "TK_CREATE",       ALWAYS                 },
  { "CROSS",            "TK_JOIN_KW",      ALWAYS                 },
  { "CURRENT_DATE",     "TK_CTIME_KW",     ALWAYS                 },
  { "CURRENT_TIME",     "TK_CTIME_KW",     ALWAYS                 },
  { "CURRENT_TIMESTAMP","TK_CTIME_KW",     ALWAYS                 },
  { "DATABASE",         "TK_DATABASE",     ATTACH                 },
  { "DEFAULT",          "TK_DEFAULT",      ALWAYS                 },