/ Check-in [2c028ddc]
Login

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

Overview
Comment:Construct secondary indices on WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: 2c028ddc85cb45746cad6ab0cefd99134fbd50d7
User & Date: drh 2013-10-23 22:23:03
Context
2013-10-23
23:37
Change the sqlite3OpenTable() utility to open the PRIMARY KEY index when reading a WITHOUT ROWID table. check-in: 247f3899 user: drh tags: omit-rowid
22:23
Construct secondary indices on WITHOUT ROWID tables. check-in: 2c028ddc user: drh tags: omit-rowid
17:39
Report an error when trying to resolve column name "rowid" in a WITHOUT ROWID table. check-in: 36bcc9cb user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

   743    743             && (pParse->db->flags & SQLITE_WriteSchema)==0
   744    744             && 0==sqlite3StrNICmp(zName, "sqlite_", 7) ){
   745    745       sqlite3ErrorMsg(pParse, "object name reserved for internal use: %s", zName);
   746    746       return SQLITE_ERROR;
   747    747     }
   748    748     return SQLITE_OK;
   749    749   }
          750  +
          751  +/*
          752  +** Return the PRIMARY KEY index of a table
          753  +*/
          754  +Index *sqlite3PrimaryKeyIndex(Table *pTab){
          755  +  Index *p;
          756  +  for(p=pTab->pIndex; p && p->autoIndex!=2; p=p->pNext){}
          757  +  return p;
          758  +}
          759  +
          760  +/*
          761  +** Return the column of index pIdx that corresponds to table
          762  +** column iCol.  Return -1 if not found.
          763  +*/
          764  +i16 sqlite3ColumnOfIndex(Index *pIdx, i16 iCol){
          765  +  int i;
          766  +  for(i=0; i<pIdx->nColumn; i++){
          767  +    if( iCol==pIdx->aiColumn[i] ) return i;
          768  +  }
          769  +  return -1;
          770  +}
   750    771   
   751    772   /*
   752    773   ** Begin constructing a new table representation in memory.  This is
   753    774   ** the first of several action routines that get called in response
   754    775   ** to a CREATE TABLE statement.  In particular, this routine is called
   755    776   ** after seeing tokens "CREATE" and "TABLE" and the table name. The isTemp
   756    777   ** flag is true if the table should be stored in the auxiliary database
................................................................................
  1606   1627       pList->a[0].zName = sqlite3DbStrDup(pParse->db,
  1607   1628                                           pTab->aCol[pTab->iPKey].zName);
  1608   1629       pList->a[0].sortOrder = pParse->iPkSortOrder;
  1609   1630       assert( pParse->pNewTable==pTab );
  1610   1631       pPk = sqlite3CreateIndex(pParse, 0, 0, 0, pList, pTab->keyConf, 0, 0, 0, 0);
  1611   1632       if( pPk==0 ) return;
  1612   1633       pTab->iPKey = -1;
         1634  +  }else{
         1635  +    pPk = sqlite3PrimaryKeyIndex(pTab);
  1613   1636     }
  1614         -  for(pPk=pTab->pIndex; pPk && pPk->autoIndex<2; pPk=pPk->pNext){}
  1615   1637     assert( pPk!=0 );
  1616   1638     nPk = pPk->nKeyCol;
  1617   1639   
  1618   1640     /* Make sure every column of the PRIMARY KEY is NOT NULL */
  1619   1641     for(i=0; i<nPk; i++){
  1620   1642       pTab->aCol[pPk->aiColumn[i]].notNull = 1;
  1621   1643     }
................................................................................
  2596   2618   
  2597   2619     v = sqlite3GetVdbe(pParse);
  2598   2620     if( v==0 ) return;
  2599   2621     if( memRootPage>=0 ){
  2600   2622       tnum = memRootPage;
  2601   2623     }else{
  2602   2624       tnum = pIndex->tnum;
  2603         -    sqlite3VdbeAddOp2(v, OP_Clear, tnum, iDb);
  2604   2625     }
  2605   2626     pKey = sqlite3IndexKeyinfo(pParse, pIndex);
  2606         -  sqlite3VdbeAddOp4(v, OP_OpenWrite, iIdx, tnum, iDb, 
  2607         -                    (char *)pKey, P4_KEYINFO_HANDOFF);
  2608         -  sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR|((memRootPage>=0)?OPFLAG_P2ISREG:0));
  2609   2627   
  2610   2628     /* Open the sorter cursor if we are to use one. */
  2611   2629     iSorter = pParse->nTab++;
  2612   2630     sqlite3VdbeAddOp4(v, OP_SorterOpen, iSorter, 0, 0, (char*)pKey, P4_KEYINFO);
  2613   2631   
  2614   2632     /* Open the table. Loop through all rows of the table, inserting index
  2615   2633     ** records into the sorter. */
  2616         -  sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
         2634  +  if( HasRowid(pTab) ){
         2635  +    sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
         2636  +  }else{
         2637  +    Index *pPk = sqlite3PrimaryKeyIndex(pTab);
         2638  +    assert( pPk!=0 );
         2639  +    assert( pPk->tnum=pTab->tnum );
         2640  +    sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pPk->tnum, iDb,
         2641  +                     (char*)sqlite3IndexKeyinfo(pParse, pPk),
         2642  +                     P4_KEYINFO_HANDOFF);
         2643  +  }
  2617   2644     addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iTab, 0);
  2618   2645     regRecord = sqlite3GetTempReg(pParse);
  2619   2646   
  2620   2647     sqlite3GenerateIndexKey(pParse, pIndex, iTab, regRecord, 1, &iPartIdxLabel);
  2621   2648     sqlite3VdbeAddOp2(v, OP_SorterInsert, iSorter, regRecord);
  2622   2649     sqlite3VdbeResolveLabel(v, iPartIdxLabel);
  2623   2650     sqlite3VdbeAddOp2(v, OP_Next, iTab, addr1+1);
  2624   2651     sqlite3VdbeJumpHere(v, addr1);
         2652  +  if( memRootPage<0 ) sqlite3VdbeAddOp2(v, OP_Clear, tnum, iDb);
         2653  +  sqlite3VdbeAddOp4(v, OP_OpenWrite, iIdx, tnum, iDb, 
         2654  +                    (char *)pKey, P4_KEYINFO_HANDOFF);
         2655  +  sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR|((memRootPage>=0)?OPFLAG_P2ISREG:0));
         2656  +
  2625   2657     addr1 = sqlite3VdbeAddOp2(v, OP_SorterSort, iSorter, 0);
  2626   2658     if( pIndex->onError!=OE_None ){
  2627   2659       int j2 = sqlite3VdbeCurrentAddr(v) + 3;
  2628   2660       sqlite3VdbeAddOp2(v, OP_Goto, 0, j2);
  2629   2661       addr2 = sqlite3VdbeCurrentAddr(v);
  2630   2662       sqlite3VdbeAddOp3(v, OP_SorterCompare, iSorter, j2, regRecord);
  2631   2663       sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_UNIQUE,
................................................................................
  2720   2752     sqlite3 *db = pParse->db;
  2721   2753     Db *pDb;             /* The specific table containing the indexed database */
  2722   2754     int iDb;             /* Index of the database that is being written */
  2723   2755     Token *pName = 0;    /* Unqualified name of the index to create */
  2724   2756     struct ExprList_item *pListItem; /* For looping over pList */
  2725   2757     const Column *pTabCol;           /* A column in the table */
  2726   2758     int nExtra = 0;                  /* Space allocated for zExtra[] */
         2759  +  int nExtraCol;                   /* Number of extra columns needed */
  2727   2760     char *zExtra;                    /* Extra space after the Index object */
         2761  +  Index *pPk = 0;      /* PRIMARY KEY index for WITHOUT ROWID tables */
  2728   2762   
  2729   2763     assert( pParse->nErr==0 );      /* Never called with prior errors */
  2730   2764     if( db->mallocFailed || IN_DECLARE_VTAB ){
  2731   2765       goto exit_create_index;
  2732   2766     }
  2733   2767     if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
  2734   2768       goto exit_create_index;
................................................................................
  2772   2806       if( pTab==0 ) goto exit_create_index;
  2773   2807       if( iDb==1 && db->aDb[iDb].pSchema!=pTab->pSchema ){
  2774   2808         sqlite3ErrorMsg(pParse, 
  2775   2809              "cannot create a TEMP index on non-TEMP table \"%s\"",
  2776   2810              pTab->zName);
  2777   2811         goto exit_create_index;
  2778   2812       }
         2813  +    if( !HasRowid(pTab) ) pPk = sqlite3PrimaryKeyIndex(pTab);
  2779   2814     }else{
  2780   2815       assert( pName==0 );
  2781   2816       assert( pStart==0 );
  2782   2817       pTab = pParse->pNewTable;
  2783   2818       if( !pTab ) goto exit_create_index;
  2784   2819       iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  2785   2820     }
................................................................................
  2889   2924       }
  2890   2925     }
  2891   2926   
  2892   2927     /* 
  2893   2928     ** Allocate the index structure. 
  2894   2929     */
  2895   2930     nName = sqlite3Strlen30(zName);
  2896         -  pIndex = sqlite3AllocateIndexObject(db, pList->nExpr + 1,
         2931  +  nExtraCol = pPk ? pPk->nKeyCol : 1;
         2932  +  pIndex = sqlite3AllocateIndexObject(db, pList->nExpr + nExtraCol,
  2897   2933                                         nName + nExtra + 1, &zExtra);
  2898   2934     if( db->mallocFailed ){
  2899   2935       goto exit_create_index;
  2900   2936     }
  2901   2937     assert( EIGHT_BYTE_ALIGNMENT(pIndex->aiRowEst) );
  2902   2938     assert( EIGHT_BYTE_ALIGNMENT(pIndex->azColl) );
  2903   2939     pIndex->zName = zExtra;
................................................................................
  2967   3003         goto exit_create_index;
  2968   3004       }
  2969   3005       pIndex->azColl[i] = zColl;
  2970   3006       requestedSortOrder = pListItem->sortOrder & sortOrderMask;
  2971   3007       pIndex->aSortOrder[i] = (u8)requestedSortOrder;
  2972   3008       if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
  2973   3009     }
  2974         -  pIndex->aiColumn[i] = -1;
  2975         -  pIndex->azColl[i] = "BINARY";
         3010  +  if( pPk ){
         3011  +    for(j=0; j<pPk->nKeyCol; j++, i++){
         3012  +      pIndex->aiColumn[i] = pPk->aiColumn[j];
         3013  +      pIndex->azColl[i] = pPk->azColl[j];
         3014  +      pIndex->aSortOrder[i] = pPk->aSortOrder[j];
         3015  +    }
         3016  +  }else{
         3017  +    pIndex->aiColumn[i] = -1;
         3018  +    pIndex->azColl[i] = "BINARY";
         3019  +  }
  2976   3020     sqlite3DefaultRowEst(pIndex);
  2977   3021     if( pParse->pNewTable==0 ) estimateIndexWidth(pIndex);
  2978   3022   
  2979   3023     if( pTab==pParse->pNewTable ){
  2980   3024       /* This routine has been called to create an automatic index as a
  2981   3025       ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
  2982   3026       ** a PRIMARY KEY or UNIQUE clause following the column definitions.

Changes to src/delete.c.

   630    630     int *piPartIdxLabel  /* OUT: Jump to this label to skip partial index */
   631    631   ){
   632    632     Vdbe *v = pParse->pVdbe;
   633    633     int j;
   634    634     Table *pTab = pIdx->pTable;
   635    635     int regBase;
   636    636     int nCol;
          637  +  Index *pPk;
   637    638   
   638    639     if( piPartIdxLabel ){
   639    640       if( pIdx->pPartIdxWhere ){
   640    641         *piPartIdxLabel = sqlite3VdbeMakeLabel(v);
   641    642         pParse->iPartIdxTab = iCur;
   642    643         sqlite3ExprIfFalse(pParse, pIdx->pPartIdxWhere, *piPartIdxLabel, 
   643    644                            SQLITE_JUMPIFNULL);
   644    645       }else{
   645    646         *piPartIdxLabel = 0;
   646    647       }
   647    648     }
   648         -  nCol = pIdx->nKeyCol;
   649         -  regBase = sqlite3GetTempRange(pParse, nCol+1);
   650         -  sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regBase+nCol);
          649  +  nCol = pIdx->nColumn;
          650  +  regBase = sqlite3GetTempRange(pParse, nCol);
          651  +  pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
   651    652     for(j=0; j<nCol; j++){
   652    653       i16 idx = pIdx->aiColumn[j];
   653         -    if( idx==pTab->iPKey ){
   654         -      sqlite3VdbeAddOp2(v, OP_SCopy, regBase+nCol, regBase+j);
          654  +    if( pPk ) idx = sqlite3ColumnOfIndex(pPk, idx);
          655  +    if( idx<0 || idx==pTab->iPKey ){
          656  +      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regBase+j);
   655    657       }else{
   656    658         sqlite3VdbeAddOp3(v, OP_Column, iCur, idx, regBase+j);
   657    659         sqlite3ColumnDefault(v, pTab, idx, -1);
   658    660       }
   659    661     }
   660    662     if( doMakeRec ){
   661    663       const char *zAff;
................................................................................
   662    664       if( pTab->pSelect
   663    665        || OptimizationDisabled(pParse->db, SQLITE_IdxRealAsInt)
   664    666       ){
   665    667         zAff = 0;
   666    668       }else{
   667    669         zAff = sqlite3IndexAffinityStr(v, pIdx);
   668    670       }
   669         -    sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol+1, regOut);
          671  +    sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regOut);
   670    672       sqlite3VdbeChangeP4(v, -1, zAff, P4_TRANSIENT);
   671    673     }
   672         -  sqlite3ReleaseTempRange(pParse, regBase, nCol+1);
          674  +  sqlite3ReleaseTempRange(pParse, regBase, nCol);
   673    675     return regBase;
   674    676   }

Changes to src/pragma.c.

  1387   1387     case PragTyp_TABLE_INFO: if( zRight ){
  1388   1388       Table *pTab;
  1389   1389       pTab = sqlite3FindTable(db, zRight, zDb);
  1390   1390       if( pTab ){
  1391   1391         int i, k;
  1392   1392         int nHidden = 0;
  1393   1393         Column *pCol;
  1394         -      Index *pPk;
  1395         -      for(pPk=pTab->pIndex; pPk && pPk->autoIndex!=2; pPk=pPk->pNext){}
         1394  +      Index *pPk = sqlite3PrimaryKeyIndex(pTab);
  1396   1395         sqlite3VdbeSetNumCols(v, 6);
  1397   1396         pParse->nMem = 6;
  1398   1397         sqlite3CodeVerifySchema(pParse, iDb);
  1399   1398         sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "cid", SQLITE_STATIC);
  1400   1399         sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC);
  1401   1400         sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "type", SQLITE_STATIC);
  1402   1401         sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "notnull", SQLITE_STATIC);

Changes to src/sqliteInt.h.

  2770   2770   void sqlite3ResetAllSchemasOfConnection(sqlite3*);
  2771   2771   void sqlite3ResetOneSchema(sqlite3*,int);
  2772   2772   void sqlite3CollapseDatabaseArray(sqlite3*);
  2773   2773   void sqlite3BeginParse(Parse*,int);
  2774   2774   void sqlite3CommitInternalChanges(sqlite3*);
  2775   2775   Table *sqlite3ResultSetOfSelect(Parse*,Select*);
  2776   2776   void sqlite3OpenMasterTable(Parse *, int);
         2777  +Index *sqlite3PrimaryKeyIndex(Table*);
         2778  +i16 sqlite3ColumnOfIndex(Index*, i16);
  2777   2779   void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int);
  2778   2780   void sqlite3AddColumn(Parse*,Token*);
  2779   2781   void sqlite3AddNotNull(Parse*, int);
  2780   2782   void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int);
  2781   2783   void sqlite3AddCheckConstraint(Parse*, Expr*);
  2782   2784   void sqlite3AddColumnType(Parse*,Token*);
  2783   2785   void sqlite3AddDefaultValue(Parse*,ExprSpan*);

Changes to src/where.c.

  2121   2121       for(i=BMS-1; i<pTable->nCol; i++){
  2122   2122         pIdx->aiColumn[n] = i;
  2123   2123         pIdx->azColl[n] = "BINARY";
  2124   2124         n++;
  2125   2125       }
  2126   2126     }
  2127   2127     assert( n==nKeyCol );
         2128  +  pIdx->aiColumn[n] = -1;
         2129  +  pIdx->azColl[n] = "BINARY";
  2128   2130   
  2129   2131     /* Create the automatic index */
  2130   2132     pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx);
  2131   2133     assert( pLevel->iIdxCur>=0 );
  2132   2134     pLevel->iIdxCur = pParse->nTab++;
  2133   2135     sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nKeyCol+1, 0,
  2134   2136                       (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
................................................................................
  6109   6111       */
  6110   6112       if( pLoop->wsFlags & (WHERE_INDEXED|WHERE_IDX_ONLY) ){
  6111   6113         pIdx = pLoop->u.btree.pIndex;
  6112   6114       }else if( pLoop->wsFlags & WHERE_MULTI_OR ){
  6113   6115         pIdx = pLevel->u.pCovidx;
  6114   6116       }
  6115   6117       if( pIdx && !db->mallocFailed ){
  6116         -      int k, j, last;
         6118  +      int k, last;
  6117   6119         VdbeOp *pOp;
  6118   6120   
  6119   6121         last = sqlite3VdbeCurrentAddr(v);
  6120   6122         k = pLevel->addrBody;
  6121   6123         pOp = sqlite3VdbeGetOp(v, k);
  6122   6124         for(; k<last; k++, pOp++){
  6123   6125           if( pOp->p1!=pLevel->iTabCur ) continue;
  6124   6126           if( pOp->opcode==OP_Column ){
  6125         -          for(j=0; j<pIdx->nColumn; j++){
  6126         -            if( pOp->p2==pIdx->aiColumn[j] ){
  6127         -              pOp->p2 = j;
  6128         -              pOp->p1 = pLevel->iIdxCur;
  6129         -              break;
  6130         -            }
         6127  +          i16 x = sqlite3ColumnOfIndex(pIdx, pOp->p2);
         6128  +          if( x>=0 ){
         6129  +            pOp->p2 = x;
         6130  +            pOp->p1 = pLevel->iIdxCur;
  6131   6131             }
  6132         -          assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 || j<pIdx->nColumn );
         6132  +          assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 || x>=0 );
  6133   6133           }else if( pOp->opcode==OP_Rowid ){
  6134   6134             pOp->p1 = pLevel->iIdxCur;
  6135   6135             pOp->opcode = OP_IdxRowid;
  6136   6136           }
  6137   6137         }
  6138   6138       }
  6139   6139     }