/ Check-in [74e073dd]
Login
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 Unified Diffs Ignore Whitespace Patch

Changes to ext/ota/ota10.test.

113
114
115
116
117
118
119



120




121













































122

        CREATE TABLE data_xt(a, xt, ota_rowid, ota_control);
        INSERT INTO data_xt VALUES('a', 'b', 1, 0);
      }
    } msg] $msg
  } {1 {SQLITE_ERROR - SQL logic error or missing database}}
}























































finish_test








>
>
>
|
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
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
        CREATE TABLE data_xt(a, xt, ota_rowid, ota_control);
        INSERT INTO data_xt VALUES('a', 'b', 1, 0);
      }
    } msg] $msg
  } {1 {SQLITE_ERROR - SQL logic error or missing database}}
}

#--------------------------------------------------------------------
# Test that it is not possible to violate a NOT NULL constraint by
# applying an OTA update.
#
do_execsql_test 4.1 {
  CREATE TABLE t2(a INTEGER NOT NULL, b TEXT NOT NULL, c PRIMARY KEY);
  CREATE TABLE t3(a INTEGER NOT NULL, b TEXT NOT NULL, c INTEGER PRIMARY KEY);
  CREATE TABLE t4(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID;

  INSERT INTO t2 VALUES(10, 10, 10);
  INSERT INTO t3 VALUES(10, 10, 10);
  INSERT INTO t4 VALUES(10, 10);

}

foreach {tn error ota} {
  2 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.a} {
    INSERT INTO data_t2 VALUES(NULL, 'abc', 1, 0);
  }
  3 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.b} {
    INSERT INTO data_t2 VALUES(2, NULL, 1, 0);
  }
  4 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.c} {
    INSERT INTO data_t2 VALUES(1, 'abc', NULL, 0);
  }
  5 {SQLITE_MISMATCH - datatype mismatch} {
    INSERT INTO data_t3 VALUES(1, 'abc', NULL, 0);
  }
  6 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t4.b} {
    INSERT INTO data_t4 VALUES('a', NULL, 0);
  }
  7 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t4.a} {
    INSERT INTO data_t4 VALUES(NULL, 'a', 0);
  }
  8  {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.a} {
    INSERT INTO data_t2 VALUES(NULL, 0, 10, 'x..');
  }
  9  {SQLITE_CONSTRAINT - NOT NULL constraint failed: t3.b} {
    INSERT INTO data_t3 VALUES(10, NULL, 10, '.x.');
  }
} {
  set ota "
    CREATE TABLE data_t2(a, b, c, ota_control);
    CREATE TABLE data_t3(a, b, c, ota_control);
    CREATE TABLE data_t4(a, b, ota_control);
    $ota
  "
  do_test 4.$tn {
    list [catch { apply_ota $ota } msg] $msg
  } [list 1 $error]
}



finish_test

Changes to ext/ota/sqlite3ota.c.

108
109
110
111
112
113
114

115
116
117
118
119
120
121
...
251
252
253
254
255
256
257

258
259
260
261
262
263
264
...
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430

431
432
433
434
435
436
437
...
651
652
653
654
655
656
657

658
659
660
661
662
663
664
665
666

667
668
669
670
671
672
673
....
1137
1138
1139
1140
1141
1142
1143
1144
1145

1146
1147
1148
1149
1150
1151
1152
  sqlite3_stmt *pTblIter;         /* Iterate through tables */
  sqlite3_stmt *pIdxIter;         /* Index iterator */
  int nTblCol;                    /* Size of azTblCol[] array */
  char **azTblCol;                /* Array of unquoted target column names */
  char **azTblType;               /* Array of target column types */
  int *aiSrcOrder;                /* src table col -> target table col */
  unsigned char *abTblPk;         /* Array of flags, set on target PK columns */

  int eType;                      /* Table type - an OTA_PK_XXX value */

  /* Output variables. zTbl==0 implies EOF. */
  int bCleanup;                   /* True in "cleanup" state */
  const char *zTbl;               /* Name of target db table */
  const char *zIdx;               /* Name of target db index (or null) */
  int iTnum;                      /* Root page of current object */
................................................................................
    sqlite3_free(pIter->azTblType[i]);
  }
  sqlite3_free(pIter->azTblCol);
  pIter->azTblCol = 0;
  pIter->azTblType = 0;
  pIter->aiSrcOrder = 0;
  pIter->abTblPk = 0;

  pIter->nTblCol = 0;
  sqlite3_free(pIter->zMask);
  pIter->zMask = 0;
  pIter->eType = 0;               /* Invalid value */
}

/*
................................................................................

/*
** Allocate and zero the pIter->azTblCol[] and abTblPk[] arrays so that
** there is room for at least nCol elements. If an OOM occurs, store an
** error code in the OTA handle passed as the first argument.
*/
static void otaAllocateIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){
  int nByte = (sizeof(char*) * 2 + sizeof(int) + sizeof(unsigned char)) * nCol;
  char **azNew;

  assert( p->rc==SQLITE_OK );
  azNew = (char**)sqlite3_malloc(nByte);
  if( azNew ){
    memset(azNew, 0, nByte);
    pIter->azTblCol = azNew;
    pIter->azTblType = &azNew[nCol];
    pIter->aiSrcOrder = (int*)&pIter->azTblType[nCol];
    pIter->abTblPk = (unsigned char*)&pIter->aiSrcOrder[nCol];

  }else{
    p->rc = SQLITE_NOMEM;
  }
}

static char *otaStrndup(const char *zStr, int nStr, int *pRc){
  char *zRet = 0;
................................................................................
      if( i==pIter->nTblCol ){
        p->rc = SQLITE_ERROR;
        p->zErrmsg = sqlite3_mprintf("column missing from data_%q: %s",
            pIter->zTbl, zName
        );
      }else{
        int iPk = sqlite3_column_int(pStmt, 5);

        const char *zType = (const char*)sqlite3_column_text(pStmt, 2);

        if( i!=iOrder ){
          SWAP(int, pIter->aiSrcOrder[i], pIter->aiSrcOrder[iOrder]);
          SWAP(char*, pIter->azTblCol[i], pIter->azTblCol[iOrder]);
        }

        pIter->azTblType[iOrder] = otaStrndup(zType, -1, &p->rc);
        pIter->abTblPk[iOrder] = (iPk!=0);

        iOrder++;
      }
    }

    rc2 = sqlite3_finalize(pStmt);
    if( p->rc==SQLITE_OK ) p->rc = rc2;
  }
................................................................................
      );

      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 = otaMPrintf(p, "%z%s\"%w\" %s %sCOLLATE %s", 
          zSql, zComma, zCol, pIter->azTblType[iCol], zPk, zColl

      );
      zComma = ", ";
    }

    if( pIter->eType==OTA_PK_WITHOUT_ROWID ){
      char *zPk = otaWithoutRowidPK(p, pIter);
      if( zPk ){







>







 







>







 







|










>







 







>









>







 







|
|
>







108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
...
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
...
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
...
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
....
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
  sqlite3_stmt *pTblIter;         /* Iterate through tables */
  sqlite3_stmt *pIdxIter;         /* Index iterator */
  int nTblCol;                    /* Size of azTblCol[] array */
  char **azTblCol;                /* Array of unquoted target column names */
  char **azTblType;               /* Array of target column types */
  int *aiSrcOrder;                /* src table col -> target table col */
  unsigned char *abTblPk;         /* Array of flags, set on target PK columns */
  unsigned char *abNotNull;       /* Array of flags, set on NOT NULL columns */
  int eType;                      /* Table type - an OTA_PK_XXX value */

  /* Output variables. zTbl==0 implies EOF. */
  int bCleanup;                   /* True in "cleanup" state */
  const char *zTbl;               /* Name of target db table */
  const char *zIdx;               /* Name of target db index (or null) */
  int iTnum;                      /* Root page of current object */
................................................................................
    sqlite3_free(pIter->azTblType[i]);
  }
  sqlite3_free(pIter->azTblCol);
  pIter->azTblCol = 0;
  pIter->azTblType = 0;
  pIter->aiSrcOrder = 0;
  pIter->abTblPk = 0;
  pIter->abNotNull = 0;
  pIter->nTblCol = 0;
  sqlite3_free(pIter->zMask);
  pIter->zMask = 0;
  pIter->eType = 0;               /* Invalid value */
}

/*
................................................................................

/*
** Allocate and zero the pIter->azTblCol[] and abTblPk[] arrays so that
** there is room for at least nCol elements. If an OOM occurs, store an
** error code in the OTA handle passed as the first argument.
*/
static void otaAllocateIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){
  int nByte = (2*sizeof(char*) + sizeof(int) + 2*sizeof(unsigned char)) * nCol;
  char **azNew;

  assert( p->rc==SQLITE_OK );
  azNew = (char**)sqlite3_malloc(nByte);
  if( azNew ){
    memset(azNew, 0, nByte);
    pIter->azTblCol = azNew;
    pIter->azTblType = &azNew[nCol];
    pIter->aiSrcOrder = (int*)&pIter->azTblType[nCol];
    pIter->abTblPk = (unsigned char*)&pIter->aiSrcOrder[nCol];
    pIter->abNotNull = (unsigned char*)&pIter->abTblPk[nCol];
  }else{
    p->rc = SQLITE_NOMEM;
  }
}

static char *otaStrndup(const char *zStr, int nStr, int *pRc){
  char *zRet = 0;
................................................................................
      if( i==pIter->nTblCol ){
        p->rc = SQLITE_ERROR;
        p->zErrmsg = sqlite3_mprintf("column missing from data_%q: %s",
            pIter->zTbl, zName
        );
      }else{
        int iPk = sqlite3_column_int(pStmt, 5);
        int bNotNull = sqlite3_column_int(pStmt, 3);
        const char *zType = (const char*)sqlite3_column_text(pStmt, 2);

        if( i!=iOrder ){
          SWAP(int, pIter->aiSrcOrder[i], pIter->aiSrcOrder[iOrder]);
          SWAP(char*, pIter->azTblCol[i], pIter->azTblCol[iOrder]);
        }

        pIter->azTblType[iOrder] = otaStrndup(zType, -1, &p->rc);
        pIter->abTblPk[iOrder] = (iPk!=0);
        pIter->abNotNull[iOrder] = (unsigned char)bNotNull || (iPk!=0);
        iOrder++;
      }
    }

    rc2 = sqlite3_finalize(pStmt);
    if( p->rc==SQLITE_OK ) p->rc = rc2;
  }
................................................................................
      );

      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 = otaMPrintf(p, "%z%s\"%w\" %s %sCOLLATE %s%s", 
          zSql, zComma, zCol, pIter->azTblType[iCol], zPk, zColl,
          (pIter->abNotNull[iCol] ? " NOT NULL" : "")
      );
      zComma = ", ";
    }

    if( pIter->eType==OTA_PK_WITHOUT_ROWID ){
      char *zPk = otaWithoutRowidPK(p, pIter);
      if( zPk ){

Changes to ext/ota/sqlite3ota.h.

66
67
68
69
70
71
72


73
74
75
76
77
78
79
**     declaration, affected rows must be identified by rowid.
**
**   * UPDATE statements may not modify PRIMARY KEY columns.
**
**   * No triggers will be fired.
**
**   * No foreign key violations are detected or reported.


**
**   * No constraint handling mode except for "OR ROLLBACK" is supported.
**
**
** PREPARATION
**
** An "OTA update" is stored as a separate SQLite database. A database







>
>







66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
**     declaration, affected rows must be identified by rowid.
**
**   * UPDATE statements may not modify PRIMARY KEY columns.
**
**   * No triggers will be fired.
**
**   * No foreign key violations are detected or reported.
**
**   * CHECK constraints are not enforced.
**
**   * No constraint handling mode except for "OR ROLLBACK" is supported.
**
**
** PREPARATION
**
** An "OTA update" is stored as a separate SQLite database. A database