Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix some problems surrounding WITHOUT ROWID tables with DESC primary key indexes . |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | ota-update |
Files: | files | file ages | folders |
SHA1: |
a21fefb79a161e6bb057ae4f6ba554f6 |
User & Date: | dan 2015-02-03 18:43:42.428 |
Context
2015-02-04
| ||
11:08 | Fix a memory leak that could follow an OOM error in ota. (check-in: 0d5415f264 user: dan tags: ota-update) | |
2015-02-03
| ||
18:43 | Fix some problems surrounding WITHOUT ROWID tables with DESC primary key indexes . (check-in: a21fefb79a user: dan tags: ota-update) | |
15:56 | Remove "PRAGMA ota_mode". (check-in: 1c111447a0 user: dan tags: ota-update) | |
Changes
Changes to ext/ota/ota1.test.
︙ | ︙ | |||
144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 | CREATE INDEX i3 ON t1(a, b, c, a, b, c); } 9 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)); CREATE INDEX i1 ON t1(b); } } { reset_db execsql $schema do_test 1.$tn2.$tn.1 { create_ota1 ota.db $cmd test.db ota.db } {SQLITE_DONE} | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > | | > > > > | | 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 | CREATE INDEX i3 ON t1(a, b, c, a, b, c); } 9 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)); CREATE INDEX i1 ON t1(b); } 10 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); CREATE INDEX i1 ON t1(b DESC); } 11 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); CREATE INDEX i1 ON t1(b DESC, a ASC, c DESC); } 12 { CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; } 13 { CREATE TABLE t1(a INT, b, c, PRIMARY KEY(a DESC)) WITHOUT ROWID; } 14 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a DESC, c)) WITHOUT ROWID; CREATE INDEX i1 ON t1(b); } 15 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c DESC)) WITHOUT ROWID; CREATE INDEX i1 ON t1(b); } 16 { CREATE TABLE t1(a, b, c, PRIMARY KEY(c DESC, a)) WITHOUT ROWID; CREATE INDEX i1 ON t1(b DESC, c, a); } } { reset_db execsql $schema do_test 1.$tn2.$tn.1 { create_ota1 ota.db $cmd test.db ota.db } {SQLITE_DONE} do_execsql_test 1.$tn2.$tn.2 { SELECT * FROM t1 ORDER BY a ASC } { 1 2 3 2 two three 3 {} 8.2 } do_execsql_test 1.$tn2.$tn.3 { SELECT * FROM t1 ORDER BY b ASC } { 3 {} 8.2 1 2 3 2 two three } do_execsql_test 1.$tn2.$tn.4 { SELECT * FROM t1 ORDER BY c ASC } { 1 2 3 3 {} 8.2 2 two three } do_execsql_test 1.$tn2.$tn.5 { PRAGMA integrity_check } ok } } #------------------------------------------------------------------------- # Check that an OTA cannot be applied to a table that has no PK. # # UPDATE: At one point OTA required that all tables featured either |
︙ | ︙ | |||
270 271 272 273 274 275 276 277 278 279 280 281 282 283 | CREATE INDEX i1 ON t1(b); CREATE INDEX i2 ON t1(c, b); CREATE INDEX i3 ON t1(c, b, c); } 5 { CREATE TABLE t1(a INT PRIMARY KEY, b, c); CREATE INDEX i1 ON t1(b); CREATE INDEX i2 ON t1(c, b); CREATE INDEX i3 ON t1(c, b, c); } } { reset_db execsql $schema execsql { | > > > > > > > > > > > > > | 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 | CREATE INDEX i1 ON t1(b); CREATE INDEX i2 ON t1(c, b); CREATE INDEX i3 ON t1(c, b, c); } 5 { CREATE TABLE t1(a INT PRIMARY KEY, b, c); CREATE INDEX i1 ON t1(b); CREATE INDEX i2 ON t1(c, b); CREATE INDEX i3 ON t1(c, b, c); } 6 { CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c); CREATE INDEX i1 ON t1(b DESC); CREATE INDEX i2 ON t1(c, b); CREATE INDEX i3 ON t1(c DESC, b, c); } 7 { CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; CREATE INDEX i1 ON t1(b); CREATE INDEX i2 ON t1(c, b); CREATE INDEX i3 ON t1(c, b, c); } } { reset_db execsql $schema execsql { |
︙ | ︙ |
Changes to ext/ota/sqlite3ota.c.
︙ | ︙ | |||
641 642 643 644 645 646 647 | assert( p->zErrmsg==0 ); rc = prepareFreeAndCollectError(p->db, &pXInfo, &p->zErrmsg, sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", pIter->zIdx) ); } while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){ | < > > | 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 | assert( p->zErrmsg==0 ); rc = prepareFreeAndCollectError(p->db, &pXInfo, &p->zErrmsg, sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", pIter->zIdx) ); } while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){ int iCid = sqlite3_column_int(pXInfo, 1); int bDesc = sqlite3_column_int(pXInfo, 3); const char *zCollate = (const char*)sqlite3_column_text(pXInfo, 4); const char *zCol; const char *zType; if( iCid<0 ){ /* An integer primary key. If the table has an explicit IPK, use ** its name. Otherwise, use "ota_rowid". */ if( pIter->eType==OTA_PK_IPK ){ |
︙ | ︙ | |||
665 666 667 668 669 670 671 | }else{ zCol = pIter->azTblCol[iCid]; zType = pIter->azTblType[iCid]; } zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom, zCol, zCollate); if( pIter->bUnique==0 || sqlite3_column_int(pXInfo, 5) ){ | > | | 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 | }else{ zCol = pIter->azTblCol[iCid]; zType = pIter->azTblType[iCid]; } zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom, zCol, zCollate); if( pIter->bUnique==0 || sqlite3_column_int(pXInfo, 5) ){ const char *zOrder = (bDesc ? " DESC" : ""); zImpPK = sqlite3_mprintf("%z%sc%d%s", zImpPK, zCom, nBind, zOrder); } zImpCols = sqlite3_mprintf( "%z%sc%d %s COLLATE %Q", zImpCols, zCom, nBind, zType, zCollate ); zWhere = sqlite3_mprintf("%z%sc%d IS ?", zWhere, zAnd, nBind); if( zRet==0 || zImpPK==0 || zImpCols==0 || zWhere==0 ) rc = SQLITE_NOMEM; zCom = ", "; |
︙ | ︙ | |||
834 835 836 837 838 839 840 841 842 843 844 845 846 847 | zRet[i*2] = '?'; zRet[i*2+1] = (i+1==nBind) ? '\0' : ','; } } } return zRet; } /* ** If an error has already occurred when this function is called, it ** immediately returns zero (without doing any work). Or, if an error ** occurs during the execution of this function, it sets the error code ** in the sqlite3ota object indicated by the first argument and returns ** zero. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 | zRet[i*2] = '?'; zRet[i*2+1] = (i+1==nBind) ? '\0' : ','; } } } return zRet; } /* ** The iterator currently points to a table (not index) of type ** OTA_PK_WITHOUT_ROWID. This function creates the PRIMARY KEY ** declaration for the corresponding imposter table. For example, ** if the iterator points to a table created as: ** ** CREATE TABLE t1(a, b, c, PRIMARY KEY(b, a DESC)) WITHOUT ROWID ** ** this function returns: ** ** PRIMARY KEY("b", "a" DESC) */ static char *otaWithoutRowidPK(sqlite3ota *p, OtaObjIter *pIter){ char *z = 0; assert( pIter->zIdx==0 ); if( p->rc==SQLITE_OK ){ const char *zSep = "PRIMARY KEY("; sqlite3_stmt *pXInfo = 0; /* PRAGMA index_xinfo = (pIter->zTbl) */ int rc; /* sqlite3_finalize() return code */ p->rc = prepareFreeAndCollectError(p->db, &pXInfo, &p->zErrmsg, sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", pIter->zTbl) ); while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){ if( sqlite3_column_int(pXInfo, 5) ){ /* int iCid = sqlite3_column_int(pXInfo, 0); */ const char *zCol = (const char*)sqlite3_column_text(pXInfo, 2); const char *zDesc = sqlite3_column_int(pXInfo, 3) ? " DESC" : ""; z = otaMPrintfAndCollectError(p, "%z%s\"%w\"%s", z, zSep, zCol, zDesc); zSep = ", "; } } z = otaMPrintfAndCollectError(p, "%z)", z); rc = sqlite3_finalize(pXInfo); if( p->rc==SQLITE_OK ) p->rc = rc; } return z; } /* ** If an error has already occurred when this function is called, it ** immediately returns zero (without doing any work). Or, if an error ** occurs during the execution of this function, it sets the error code ** in the sqlite3ota object indicated by the first argument and returns ** zero. |
︙ | ︙ | |||
872 873 874 875 876 877 878 879 880 881 882 883 884 | int tnum = pIter->tnum; const char *zComma = ""; char *zSql = 0; int iCol; sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->db, "main", 0, 1); for(iCol=0; p->rc==SQLITE_OK && iCol<pIter->nTblCol; iCol++){ int iDataCol = pIter->aiTblOrder[iCol]; const char *zCol = pIter->azTblCol[iDataCol]; const char *zColl = 0; p->rc = sqlite3_table_column_metadata( p->db, "main", pIter->zTbl, zCol, 0, &zColl, 0, 0, 0 ); | > > > > > > > > | | | | < < | | < < < | < < | 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 | int tnum = pIter->tnum; const char *zComma = ""; char *zSql = 0; int iCol; sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->db, "main", 0, 1); for(iCol=0; p->rc==SQLITE_OK && iCol<pIter->nTblCol; iCol++){ const char *zPk = ""; int iDataCol = pIter->aiTblOrder[iCol]; const char *zCol = pIter->azTblCol[iDataCol]; const char *zColl = 0; p->rc = sqlite3_table_column_metadata( p->db, "main", pIter->zTbl, zCol, 0, &zColl, 0, 0, 0 ); if( pIter->eType==OTA_PK_IPK && pIter->abTblPk[iCol] ){ /* If the target table column is an "INTEGER PRIMARY KEY", add ** "PRIMARY KEY" to the imposter table column declaration. */ zPk = "PRIMARY KEY "; } zSql = otaMPrintfAndCollectError(p, "%z%s\"%w\" %s %sCOLLATE %s", zSql, zComma, zCol, pIter->azTblType[iDataCol], zPk, zColl ); zComma = ", "; } if( pIter->eType==OTA_PK_WITHOUT_ROWID ){ char *zPk = otaWithoutRowidPK(p, pIter); if( zPk ){ zSql = otaMPrintfAndCollectError(p, "%z, %z", zSql, zPk); } } zSql = otaMPrintfAndCollectError(p, "CREATE TABLE ota_imposter(%z)%s", zSql, (pIter->eType==OTA_PK_WITHOUT_ROWID ? " WITHOUT ROWID" : "") ); if( p->rc==SQLITE_OK ){ sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->db, "main", 1, tnum); |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 | } break; case PragTyp_INDEX_INFO: if( zRight ){ Index *pIdx; Table *pTab; pIdx = sqlite3FindIndex(db, zRight, zDb); if( pIdx ){ int i; int mx = pPragma->iArg ? pIdx->nColumn : pIdx->nKeyCol; pTab = pIdx->pTable; sqlite3VdbeSetNumCols(v, 6); pParse->nMem = 6; sqlite3CodeVerifySchema(pParse, iDb); | > > > > | 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 | } break; case PragTyp_INDEX_INFO: if( zRight ){ Index *pIdx; Table *pTab; pIdx = sqlite3FindIndex(db, zRight, zDb); if( pIdx==0 ){ pTab = sqlite3FindTable(db, zRight, zDb); if( pTab && !HasRowid(pTab) ) pIdx = sqlite3PrimaryKeyIndex(pTab); } if( pIdx ){ int i; int mx = pPragma->iArg ? pIdx->nColumn : pIdx->nKeyCol; pTab = pIdx->pTable; sqlite3VdbeSetNumCols(v, 6); pParse->nMem = 6; sqlite3CodeVerifySchema(pParse, iDb); |
︙ | ︙ |