SQLite

Check-in [f9769d701c]
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
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: f9769d701c65770f4b8488f541c59e508393e6c2
User & Date: drh 2013-11-01 20:30:36.718
Context
2013-11-01
22:02
Size KeyInfo objects so that IdxInserts always compare the correct number of fields. (check-in: 302a81390f 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: f9769d701c user: drh tags: omit-rowid)
18:14
Additional UPDATE test cases for WITHOUT ROWID. (check-in: 65384ae0f0 user: drh tags: omit-rowid)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
︙︙
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667

      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 );








|
<








1652
1653
1654
1655
1656
1657
1658
1659

1660
1661
1662
1663
1664
1665
1666

      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 );
︙︙
2995
2996
2997
2998
2999
3000
3001
3002




3003
3004
3005

3006


3007
3008
3009
3010
3011
3012
3013

    }
    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);









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








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

    }
    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
︙︙