Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Prevent ota updates from violating NOT NULL constraints. Add a comment to the "limitations" section of sqlite3ota.h saying that CHECK constraints are not enforced. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | ota-update |
| Files: | files | file ages | folders |
| SHA1: |
74e073dd604142212f3d3e1931065d12 |
| User & Date: | dan 2015-02-05 17:36:30 |
Context
|
2015-02-05
| ||
| 17:46 | Change a comment in sqlite3ota.h to make it clear that it is not possible to insert a NULL value into an INTEGER PRIMARY KEY column using ota. check-in: a5e86bea user: dan tags: ota-update | |
| 17:36 | Prevent ota updates from violating NOT NULL constraints. Add a comment to the "limitations" section of sqlite3ota.h saying that CHECK constraints are not enforced. check-in: 74e073dd user: dan tags: ota-update | |
| 01:49 | Figure out the primary-key type of a table using queries of sqlite_master and the table_info and index_list pragmas, obviating the need for SQLITE_TESTCTRL_TBLTYPE. check-in: 50ecdfc4 user: drh tags: ota-update | |
Changes
Changes to ext/ota/ota10.test.
113 113 CREATE TABLE data_xt(a, xt, ota_rowid, ota_control); 114 114 INSERT INTO data_xt VALUES('a', 'b', 1, 0); 115 115 } 116 116 } msg] $msg 117 117 } {1 {SQLITE_ERROR - SQL logic error or missing database}} 118 118 } 119 119 120 +#-------------------------------------------------------------------- 121 +# Test that it is not possible to violate a NOT NULL constraint by 122 +# applying an OTA update. 123 +# 124 +do_execsql_test 4.1 { 125 + CREATE TABLE t2(a INTEGER NOT NULL, b TEXT NOT NULL, c PRIMARY KEY); 126 + CREATE TABLE t3(a INTEGER NOT NULL, b TEXT NOT NULL, c INTEGER PRIMARY KEY); 127 + CREATE TABLE t4(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID; 128 + 129 + INSERT INTO t2 VALUES(10, 10, 10); 130 + INSERT INTO t3 VALUES(10, 10, 10); 131 + INSERT INTO t4 VALUES(10, 10); 132 + 133 +} 134 + 135 +foreach {tn error ota} { 136 + 2 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.a} { 137 + INSERT INTO data_t2 VALUES(NULL, 'abc', 1, 0); 138 + } 139 + 3 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.b} { 140 + INSERT INTO data_t2 VALUES(2, NULL, 1, 0); 141 + } 142 + 4 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.c} { 143 + INSERT INTO data_t2 VALUES(1, 'abc', NULL, 0); 144 + } 145 + 5 {SQLITE_MISMATCH - datatype mismatch} { 146 + INSERT INTO data_t3 VALUES(1, 'abc', NULL, 0); 147 + } 148 + 6 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t4.b} { 149 + INSERT INTO data_t4 VALUES('a', NULL, 0); 150 + } 151 + 7 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t4.a} { 152 + INSERT INTO data_t4 VALUES(NULL, 'a', 0); 153 + } 154 + 8 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.a} { 155 + INSERT INTO data_t2 VALUES(NULL, 0, 10, 'x..'); 156 + } 157 + 9 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t3.b} { 158 + INSERT INTO data_t3 VALUES(10, NULL, 10, '.x.'); 159 + } 160 +} { 161 + set ota " 162 + CREATE TABLE data_t2(a, b, c, ota_control); 163 + CREATE TABLE data_t3(a, b, c, ota_control); 164 + CREATE TABLE data_t4(a, b, ota_control); 165 + $ota 166 + " 167 + do_test 4.$tn { 168 + list [catch { apply_ota $ota } msg] $msg 169 + } [list 1 $error] 170 +} 171 + 120 172 121 173 122 174 finish_test 175 +
Changes to ext/ota/sqlite3ota.c.
108 108 sqlite3_stmt *pTblIter; /* Iterate through tables */ 109 109 sqlite3_stmt *pIdxIter; /* Index iterator */ 110 110 int nTblCol; /* Size of azTblCol[] array */ 111 111 char **azTblCol; /* Array of unquoted target column names */ 112 112 char **azTblType; /* Array of target column types */ 113 113 int *aiSrcOrder; /* src table col -> target table col */ 114 114 unsigned char *abTblPk; /* Array of flags, set on target PK columns */ 115 + unsigned char *abNotNull; /* Array of flags, set on NOT NULL columns */ 115 116 int eType; /* Table type - an OTA_PK_XXX value */ 116 117 117 118 /* Output variables. zTbl==0 implies EOF. */ 118 119 int bCleanup; /* True in "cleanup" state */ 119 120 const char *zTbl; /* Name of target db table */ 120 121 const char *zIdx; /* Name of target db index (or null) */ 121 122 int iTnum; /* Root page of current object */ ................................................................................ 251 252 sqlite3_free(pIter->azTblType[i]); 252 253 } 253 254 sqlite3_free(pIter->azTblCol); 254 255 pIter->azTblCol = 0; 255 256 pIter->azTblType = 0; 256 257 pIter->aiSrcOrder = 0; 257 258 pIter->abTblPk = 0; 259 + pIter->abNotNull = 0; 258 260 pIter->nTblCol = 0; 259 261 sqlite3_free(pIter->zMask); 260 262 pIter->zMask = 0; 261 263 pIter->eType = 0; /* Invalid value */ 262 264 } 263 265 264 266 /* ................................................................................ 413 415 414 416 /* 415 417 ** Allocate and zero the pIter->azTblCol[] and abTblPk[] arrays so that 416 418 ** there is room for at least nCol elements. If an OOM occurs, store an 417 419 ** error code in the OTA handle passed as the first argument. 418 420 */ 419 421 static void otaAllocateIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){ 420 - int nByte = (sizeof(char*) * 2 + sizeof(int) + sizeof(unsigned char)) * nCol; 422 + int nByte = (2*sizeof(char*) + sizeof(int) + 2*sizeof(unsigned char)) * nCol; 421 423 char **azNew; 422 424 423 425 assert( p->rc==SQLITE_OK ); 424 426 azNew = (char**)sqlite3_malloc(nByte); 425 427 if( azNew ){ 426 428 memset(azNew, 0, nByte); 427 429 pIter->azTblCol = azNew; 428 430 pIter->azTblType = &azNew[nCol]; 429 431 pIter->aiSrcOrder = (int*)&pIter->azTblType[nCol]; 430 432 pIter->abTblPk = (unsigned char*)&pIter->aiSrcOrder[nCol]; 433 + pIter->abNotNull = (unsigned char*)&pIter->abTblPk[nCol]; 431 434 }else{ 432 435 p->rc = SQLITE_NOMEM; 433 436 } 434 437 } 435 438 436 439 static char *otaStrndup(const char *zStr, int nStr, int *pRc){ 437 440 char *zRet = 0; ................................................................................ 651 654 if( i==pIter->nTblCol ){ 652 655 p->rc = SQLITE_ERROR; 653 656 p->zErrmsg = sqlite3_mprintf("column missing from data_%q: %s", 654 657 pIter->zTbl, zName 655 658 ); 656 659 }else{ 657 660 int iPk = sqlite3_column_int(pStmt, 5); 661 + int bNotNull = sqlite3_column_int(pStmt, 3); 658 662 const char *zType = (const char*)sqlite3_column_text(pStmt, 2); 659 663 660 664 if( i!=iOrder ){ 661 665 SWAP(int, pIter->aiSrcOrder[i], pIter->aiSrcOrder[iOrder]); 662 666 SWAP(char*, pIter->azTblCol[i], pIter->azTblCol[iOrder]); 663 667 } 664 668 665 669 pIter->azTblType[iOrder] = otaStrndup(zType, -1, &p->rc); 666 670 pIter->abTblPk[iOrder] = (iPk!=0); 671 + pIter->abNotNull[iOrder] = (unsigned char)bNotNull || (iPk!=0); 667 672 iOrder++; 668 673 } 669 674 } 670 675 671 676 rc2 = sqlite3_finalize(pStmt); 672 677 if( p->rc==SQLITE_OK ) p->rc = rc2; 673 678 } ................................................................................ 1137 1142 ); 1138 1143 1139 1144 if( pIter->eType==OTA_PK_IPK && pIter->abTblPk[iCol] ){ 1140 1145 /* If the target table column is an "INTEGER PRIMARY KEY", add 1141 1146 ** "PRIMARY KEY" to the imposter table column declaration. */ 1142 1147 zPk = "PRIMARY KEY "; 1143 1148 } 1144 - zSql = otaMPrintf(p, "%z%s\"%w\" %s %sCOLLATE %s", 1145 - zSql, zComma, zCol, pIter->azTblType[iCol], zPk, zColl 1149 + zSql = otaMPrintf(p, "%z%s\"%w\" %s %sCOLLATE %s%s", 1150 + zSql, zComma, zCol, pIter->azTblType[iCol], zPk, zColl, 1151 + (pIter->abNotNull[iCol] ? " NOT NULL" : "") 1146 1152 ); 1147 1153 zComma = ", "; 1148 1154 } 1149 1155 1150 1156 if( pIter->eType==OTA_PK_WITHOUT_ROWID ){ 1151 1157 char *zPk = otaWithoutRowidPK(p, pIter); 1152 1158 if( zPk ){
Changes to ext/ota/sqlite3ota.h.
66 66 ** declaration, affected rows must be identified by rowid. 67 67 ** 68 68 ** * UPDATE statements may not modify PRIMARY KEY columns. 69 69 ** 70 70 ** * No triggers will be fired. 71 71 ** 72 72 ** * No foreign key violations are detected or reported. 73 +** 74 +** * CHECK constraints are not enforced. 73 75 ** 74 76 ** * No constraint handling mode except for "OR ROLLBACK" is supported. 75 77 ** 76 78 ** 77 79 ** PREPARATION 78 80 ** 79 81 ** An "OTA update" is stored as a separate SQLite database. A database