SQLite: Check-in [f9769d70]

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

Overview

Comment:	Fix sqlite3_analyzer so that it works with WITHOUT ROWID tables. Fix index generation for secondary indices that include fields from the PRIMARY KEY.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| omit-rowid
Files:	files \| file ages \| folders
SHA1:	f9769d701c65770f4b8488f541c59e508393e6c2
User & Date:	drh 2013-11-01 20:30:36

Context

2013-11-01
22:02		Size KeyInfo objects so that IdxInserts always compare the correct number of fields. (check-in: 302a8139 user: drh tags: omit-rowid)
20:30		Fix sqlite3_analyzer so that it works with WITHOUT ROWID tables. Fix index generation for secondary indices that include fields from the PRIMARY KEY. (check-in: f9769d70 user: drh tags: omit-rowid)
18:14		Additional UPDATE test cases for WITHOUT ROWID. (check-in: 65384ae0 user: drh tags: omit-rowid)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

      continue;
    }
    for(i=n=0; i<nPk; i++){
      if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ) n++;
    }
    if( resizeIndexObject(db, pIdx, pIdx->nKeyCol+n) ) return;
    for(i=0, j=pIdx->nKeyCol; i<nPk; i++){
      if( !hasColumn(pIdx->aiColumn, j, pPk->aiColumn[i]) ){
        assert( j<pPk->nColumn );
        pIdx->aiColumn[j] = pPk->aiColumn[i];
        pIdx->azColl[j] = pPk->azColl[i];
        j++;
      }
    }
    assert( pIdx->nColumn==j );
    assert( pIdx->nKeyCol+n==j );
................................................................................
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;
    if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
  }
  if( pPk ){
    for(j=0; j<pPk->nKeyCol; j++, i++){




      pIndex->aiColumn[i] = pPk->aiColumn[j];
      pIndex->azColl[i] = pPk->azColl[j];
      pIndex->aSortOrder[i] = pPk->aSortOrder[j];

    }


  }else{
    pIndex->aiColumn[i] = -1;
    pIndex->azColl[i] = "BINARY";
  }
  sqlite3DefaultRowEst(pIndex);
  if( pParse->pNewTable==0 ) estimateIndexWidth(pIndex);








|
<







 







|
>
>
>
>
|
|
|
>
|
>
>

      continue;
    }
    for(i=n=0; i<nPk; i++){
      if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ) n++;
    }
    if( resizeIndexObject(db, pIdx, pIdx->nKeyCol+n) ) return;
    for(i=0, j=pIdx->nKeyCol; i<nPk; i++){
      if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ){

        pIdx->aiColumn[j] = pPk->aiColumn[i];
        pIdx->azColl[j] = pPk->azColl[i];
        j++;
      }
    }
    assert( pIdx->nColumn==j );
    assert( pIdx->nKeyCol+n==j );
................................................................................
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;
    if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0;
  }
  if( pPk ){
    for(j=0; j<pPk->nKeyCol; j++){
      int x = pPk->aiColumn[j];
      if( hasColumn(pIndex->aiColumn, pIndex->nKeyCol, x) ){
        pIndex->nColumn--; 
      }else{
        pIndex->aiColumn[i] = x;
        pIndex->azColl[i] = pPk->azColl[j];
        pIndex->aSortOrder[i] = pPk->aSortOrder[j];
        i++;
      }
    }
    assert( i==pIndex->nColumn );
  }else{
    pIndex->aiColumn[i] = -1;
    pIndex->azColl[i] = "BINARY";
  }
  sqlite3DefaultRowEst(pIndex);
  if( pParse->pNewTable==0 ) estimateIndexWidth(pIndex);

Changes to src/insert.c.

          sqlite3VdbeAddOp3(v, OP_Eq, regOldData+1+x, addrUniqueOk, regIdx+x);
        }
      }else{
        /* For a UNIQUE index on a WITHOUT ROWID table, conflict only if the
        ** PRIMARY KEY value of the match is different from the old PRIMARY KEY
        ** value from before the update. */
        int addrConflict = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
        assert( pIdx->nKeyCol + pPk->nKeyCol == pIdx->nColumn );
        for(i=0; i<pPk->nKeyCol-1; i++){
          sqlite3VdbeAddOp3(v, OP_Ne,
                           regOldData+pPk->aiColumn[i]+1, addrConflict, regR+i);
        }
        sqlite3VdbeAddOp3(v, OP_Eq,
                          regOldData+pPk->aiColumn[i]+1, addrUniqueOk, regR+i);
      }

          sqlite3VdbeAddOp3(v, OP_Eq, regOldData+1+x, addrUniqueOk, regIdx+x);
        }
      }else{
        /* For a UNIQUE index on a WITHOUT ROWID table, conflict only if the
        ** PRIMARY KEY value of the match is different from the old PRIMARY KEY
        ** value from before the update. */
        int addrConflict = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;

        for(i=0; i<pPk->nKeyCol-1; i++){
          sqlite3VdbeAddOp3(v, OP_Ne,
                           regOldData+pPk->aiColumn[i]+1, addrConflict, regR+i);
        }
        sqlite3VdbeAddOp3(v, OP_Eq,
                          regOldData+pPk->aiColumn[i]+1, addrUniqueOk, regR+i);
      }

Changes to tool/spaceanal.tcl.

if {$nindex>0} {
  subreport {All tables and indices} 1 0
}
subreport {All tables} {NOT is_index} 0
if {$nindex>0} {
  subreport {All indices} {is_index} 0
}
foreach tbl [mem eval {SELECT name FROM space_used WHERE NOT is_index
                       ORDER BY name}] {
  set qn [quote $tbl]
  set name [string toupper $tbl]
  set n [mem eval {SELECT count(*) FROM space_used WHERE tblname=$tbl}]
  if {$n>1} {
    set idxlist [mem eval "SELECT name FROM space_used
                            WHERE tblname='$qn' AND is_index

if {$nindex>0} {
  subreport {All tables and indices} 1 0
}
subreport {All tables} {NOT is_index} 0
if {$nindex>0} {
  subreport {All indices} {is_index} 0
}
foreach tbl [mem eval {SELECT DISTINCT tblname name FROM space_used
                       ORDER BY name}] {
  set qn [quote $tbl]
  set name [string toupper $tbl]
  set n [mem eval {SELECT count(*) FROM space_used WHERE tblname=$tbl}]
  if {$n>1} {
    set idxlist [mem eval "SELECT name FROM space_used
                            WHERE tblname='$qn' AND is_index