/ Check-in [74e073dd]
Login

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: 74e073dd604142212f3d3e1931065d124daabd80
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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