Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Take collating sequence into account when removing redundant columns from indexes on WITHOUT ROWID tables. This is the first proof-of-concept fix for ticket [3182d3879020ef3]. More testing needed. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | tkt-3182d38790 |
| Files: | files | file ages | folders |
| SHA3-256: |
b34fa5bff40d3d364bd8c80e7de55c60 |
| User & Date: | drh 2019-04-28 19:27:02 |
Context
|
2019-04-29
| ||
| 13:30 | Improved header comment and precondition checking for the new isDupColumn() function. (Closed-Leaf check-in: 740d5ff6 user: drh tags: tkt-3182d38790) | |
|
2019-04-28
| ||
| 19:27 | Take collating sequence into account when removing redundant columns from indexes on WITHOUT ROWID tables. This is the first proof-of-concept fix for ticket [3182d3879020ef3]. More testing needed. (check-in: b34fa5bf user: drh tags: tkt-3182d38790) | |
|
2019-04-27
| ||
| 20:30 | Add the ".recover" command to the shell tool. For recovering as much data as possible from corrupt databases. (check-in: 50fe4845 user: dan tags: trunk) | |
Changes
Changes to src/build.c.
1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 .... 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 .... 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 .... 3388 3389 3390 3391 3392 3393 3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 |
i16 x = pIdx->aiColumn[i];
assert( x<pIdx->pTable->nCol );
wIndex += x<0 ? 1 : aCol[pIdx->aiColumn[i]].szEst;
}
pIdx->szIdxRow = sqlite3LogEst(wIndex*4);
}
/* Return true if value x is found any of the first nCol entries of aiCol[]
*/
static int hasColumn(const i16 *aiCol, int nCol, int x){
while( nCol-- > 0 ) if( x==*(aiCol++) ) return 1;
return 0;
}
/* Recompute the colNotIdxed field of the Index.
**
** colNotIdxed is a bitmask that has a 0 bit representing each indexed
** columns that are within the first 63 columns of the table. The
................................................................................
/*
** Remove all redundant columns from the PRIMARY KEY. For example, change
** "PRIMARY KEY(a,b,a,b,c,b,c,d)" into just "PRIMARY KEY(a,b,c,d)". Later
** code assumes the PRIMARY KEY contains no repeated columns.
*/
for(i=j=1; i<pPk->nKeyCol; i++){
if( hasColumn(pPk->aiColumn, j, pPk->aiColumn[i]) ){
pPk->nColumn--;
}else{
pPk->aiColumn[j++] = pPk->aiColumn[i];
}
}
pPk->nKeyCol = j;
}
assert( pPk!=0 );
pPk->isCovering = 1;
................................................................................
/* Update the in-memory representation of all UNIQUE indices by converting
** the final rowid column into one or more columns of the PRIMARY KEY.
*/
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
int n;
if( IsPrimaryKeyIndex(pIdx) ) continue;
for(i=n=0; i<nPk; i++){
if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ) n++;
}
if( n==0 ){
/* This index is a superset of the primary key */
pIdx->nColumn = pIdx->nKeyCol;
continue;
}
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>=pIdx->nKeyCol+n );
assert( pIdx->nColumn>=j );
................................................................................
** tables (when pPk!=0) this will be the declared PRIMARY KEY. For
** normal tables (when pPk==0) this will be the rowid.
*/
if( pPk ){
for(j=0; j<pPk->nKeyCol; j++){
int x = pPk->aiColumn[j];
assert( x>=0 );
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 );
|
| > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > | > > > | | > |
1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 .... 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 .... 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 .... 3429 3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 3445 3446 |
i16 x = pIdx->aiColumn[i];
assert( x<pIdx->pTable->nCol );
wIndex += x<0 ? 1 : aCol[pIdx->aiColumn[i]].szEst;
}
pIdx->szIdxRow = sqlite3LogEst(wIndex*4);
}
/* Return true if column number x is any of the first nCol entries of aiCol[].
** This is used to determine if the column number x appears in any of the
** first nCol entries of an index.
*/
static int hasColumn(const i16 *aiCol, int nCol, int x){
while( nCol-- > 0 ){
assert( aiCol[0]>=0 );
if( x==*(aiCol++) ){
return 1;
}
}
return 0;
}
/*
** Return true if any of the first nKey entries of index pIdx1 exactly
** match the iCol-th entry of pIdx2.
**
** The first nKey entries of pIdx1 are guaranteed to be ordinary columns,
** not a rowid or expression.
**
** This routine differs from hasColumn() in that both the column and the
** collating sequence must match for this routine, but for hasColumn() only
** the column name must match.
*/
static int isDupColumn(Index *pIdx1, int nKey, Index *pIdx2, int iCol){
int i, j;
assert( nKey<=pIdx1->nColumn );
assert( iCol<MAX(pIdx2->nColumn,pIdx2->nKeyCol) );
j = pIdx2->aiColumn[iCol];
testcase( j==XN_EXPR );
assert( j!=XN_ROWID );
for(i=0; i<nKey; i++){
assert( pIdx1->aiColumn[i]>=0 || j>=0 );
if( pIdx1->aiColumn[i]==j
&& sqlite3StrICmp(pIdx1->azColl[i],pIdx2->azColl[iCol])==0
){
return 1;
}
}
return 0;
}
/* Recompute the colNotIdxed field of the Index.
**
** colNotIdxed is a bitmask that has a 0 bit representing each indexed
** columns that are within the first 63 columns of the table. The
................................................................................
/*
** Remove all redundant columns from the PRIMARY KEY. For example, change
** "PRIMARY KEY(a,b,a,b,c,b,c,d)" into just "PRIMARY KEY(a,b,c,d)". Later
** code assumes the PRIMARY KEY contains no repeated columns.
*/
for(i=j=1; i<pPk->nKeyCol; i++){
if( isDupColumn(pPk, j, pPk, i) ){
pPk->nColumn--;
}else{
testcase( hasColumn(pPk->aiColumn, j, pPk->aiColumn[i]) );
pPk->aiColumn[j++] = pPk->aiColumn[i];
}
}
pPk->nKeyCol = j;
}
assert( pPk!=0 );
pPk->isCovering = 1;
................................................................................
/* Update the in-memory representation of all UNIQUE indices by converting
** the final rowid column into one or more columns of the PRIMARY KEY.
*/
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
int n;
if( IsPrimaryKeyIndex(pIdx) ) continue;
for(i=n=0; i<nPk; i++){
if( !isDupColumn(pIdx, pIdx->nKeyCol, pPk, i) ){
testcase( hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) );
n++;
}
}
if( n==0 ){
/* This index is a superset of the primary key */
pIdx->nColumn = pIdx->nKeyCol;
continue;
}
if( resizeIndexObject(db, pIdx, pIdx->nKeyCol+n) ) return;
for(i=0, j=pIdx->nKeyCol; i<nPk; i++){
if( !isDupColumn(pIdx, pIdx->nKeyCol, pPk, i) ){
testcase( hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) );
pIdx->aiColumn[j] = pPk->aiColumn[i];
pIdx->azColl[j] = pPk->azColl[i];
j++;
}
}
assert( pIdx->nColumn>=pIdx->nKeyCol+n );
assert( pIdx->nColumn>=j );
................................................................................
** tables (when pPk!=0) this will be the declared PRIMARY KEY. For
** normal tables (when pPk==0) this will be the rowid.
*/
if( pPk ){
for(j=0; j<pPk->nKeyCol; j++){
int x = pPk->aiColumn[j];
assert( x>=0 );
if( isDupColumn(pIndex, pIndex->nKeyCol, pPk, j) ){
pIndex->nColumn--;
}else{
testcase( hasColumn(pIndex->aiColumn,pIndex->nKeyCol,x) );
pIndex->aiColumn[i] = x;
pIndex->azColl[i] = pPk->azColl[j];
pIndex->aSortOrder[i] = pPk->aSortOrder[j];
i++;
}
}
assert( i==pIndex->nColumn );
|