/ Check-in [f9769d70]
Login

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.

1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
....
2995
2996
2997
2998
2999
3000
3001
3002




3003
3004
3005

3006


3007
3008
3009
3010
3011
3012
3013
      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);








|
<







 







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







1652
1653
1654
1655
1656
1657
1658
1659

1660
1661
1662
1663
1664
1665
1666
....
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
      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.

1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
          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);
      }







<







1520
1521
1522
1523
1524
1525
1526

1527
1528
1529
1530
1531
1532
1533
          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.

567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
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







|







567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
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