Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Reorganize the code in sqlite3ota.c in preparation for adding support for update and delete operations. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | ota-update |
| Files: | files | file ages | folders |
| SHA1: |
98387f05697526c7740e91d8a846a31f |
| User & Date: | dan 2014-09-05 19:31:15 |
Context
|
2014-09-05
| ||
| 19:52 | Switch back to using a single database connection in sqlite3ota.c. (check-in: 3c2f4a07 user: dan tags: ota-update) | |
| 19:31 | Reorganize the code in sqlite3ota.c in preparation for adding support for update and delete operations. (check-in: 98387f05 user: dan tags: ota-update) | |
|
2014-09-04
| ||
| 19:05 | Avoid ever running a checkpoint in ota mode. (check-in: 9ae44447 user: dan tags: ota-update) | |
Changes
Changes to ext/ota/ota1.test.
59 59 set rc [ota step] 60 60 ota close 61 61 if {$rc != "SQLITE_OK"} break 62 62 } 63 63 set rc 64 64 } 65 65 66 -foreach {tn2 cmd} {1 step_ota 2 run_ota} { 66 +foreach {tn2 cmd} {1 run_ota 2 step_ota} { 67 67 foreach {tn schema} { 68 68 1 { 69 69 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 70 70 } 71 71 2 { 72 72 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 73 73 CREATE INDEX i1 ON t1(b);
Changes to ext/ota/sqlite3ota.c.
26 26 ** single row: 27 27 ** 28 28 ** "tbl" -> Table currently being written (target database names). 29 29 ** 30 30 ** "idx" -> Index currently being written (target database names). 31 31 ** Or, if the main table is being written, a NULL value. 32 32 ** 33 -** "row" -> Last rowid processed from ota database table (i.e. data_%). 33 +** "row" -> Number of rows for this object already processed 34 34 ** 35 35 ** "progress" -> total number of key/value b-tree operations performed 36 36 ** so far as part of this ota update. 37 37 */ 38 38 #define OTA_CREATE_STATE "CREATE TABLE IF NOT EXISTS ota_state" \ 39 39 "(tbl, idx, row, progress)" 40 40 41 -typedef struct OtaTblIter OtaTblIter; 42 -typedef struct OtaIdxIter OtaIdxIter; 43 41 typedef struct OtaState OtaState; 42 +typedef struct OtaObjIter OtaObjIter; 43 +typedef unsigned char u8; 44 44 45 45 /* 46 -** Iterator used to iterate through all data tables in the OTA. As follows: 46 +** A structure to store values read from the ota_state table in memory. 47 +*/ 48 +struct OtaState { 49 + char *zTbl; 50 + char *zIdx; 51 + int nRow; 52 +}; 53 + 54 +/* 55 +** An iterator of this type is used to iterate through all objects in 56 +** the target database that require updating. For each such table, the 57 +** iterator visits, in order: 47 58 ** 48 -** OtaTblIter iter; 49 -** for(rc=tblIterFirst(db, &iter); 50 -** rc==SQLITE_OK && iter.zTarget; 51 -** rc=tblIterNext(&iter) 52 -** ){ 53 -** } 59 +** * the table itself, 60 +** * each index of the table (zero or more points to visit), and 61 +** * a special "cleanup table" point. 54 62 */ 55 -struct OtaTblIter { 63 +struct OtaObjIter { 56 64 sqlite3_stmt *pTblIter; /* Iterate through tables */ 57 - int iEntry; /* Index of current entry (from 1) */ 65 + sqlite3_stmt *pIdxIter; /* Index iterator */ 66 + int nTblCol; /* Size of azTblCol[] array */ 67 + char **azTblCol; /* Array of quoted column names */ 68 + u8 *abTblPk; /* Array of flags - true for PK columns */ 58 69 59 - /* Output varibles. zTarget==0 implies EOF. */ 60 - const char *zTarget; /* Name of target table */ 61 - const char *zSource; /* Name of source table */ 70 + /* Output variables. zTbl==0 implies EOF. */ 71 + int bCleanup; /* True in "cleanup" state */ 72 + const char *zTbl; /* Name of target db table */ 73 + const char *zIdx; /* Name of target db index (or null) */ 74 + int iVisit; /* Number of points visited, incl. current */ 62 75 63 - /* Useful things populated by a call to tblIterPrepareAll() */ 64 - int nCol; /* Number of columns in this table */ 65 - char **azCol; /* Array of quoted column names */ 66 - sqlite3_stmt *pSelect; /* PK b-tree SELECT statement */ 67 - sqlite3_stmt *pInsert; /* PK b-tree INSERT statement */ 76 + /* Statements created by otaObjIterPrepareAll() */ 77 + int nCol; /* Number of columns in current object */ 78 + sqlite3_stmt *pSelect; /* Source data */ 79 + sqlite3_stmt *pInsert; /* Statement for INSERT operations */ 68 80 }; 69 81 70 82 /* 71 -** API is: 72 -** 73 -** idxIterFirst() 74 -** idxIterNext() 75 -** idxIterFinalize() 76 -** idxIterPrepareAll() 83 +** OTA handle. 77 84 */ 78 -struct OtaIdxIter { 79 - sqlite3_stmt *pIdxIter; /* Iterate through indexes */ 80 - int iEntry; /* Index of current entry (from 1) */ 81 - 82 - /* Output varibles. zTarget==0 implies EOF. */ 83 - const char *zIndex; /* Name of index */ 84 - 85 - int nCol; /* Number of columns in index */ 86 - int *aiCol; /* Array of column indexes */ 87 - sqlite3_stmt *pWriter; /* Index writer */ 88 - sqlite3_stmt *pSelect; /* Select to read values in index order */ 89 -}; 90 - 91 -struct OtaState { 92 - char *zTbl; 93 - char *zIdx; 94 - sqlite3_int64 iRow; 95 -}; 96 - 97 - 98 85 struct sqlite3ota { 99 86 sqlite3 *dbDest; /* Target db */ 100 87 sqlite3 *dbOta; /* Ota db */ 101 88 char *zTarget; /* Path to target db */ 102 89 103 90 int rc; /* Value returned by last ota_step() call */ 104 91 char *zErrmsg; /* Error message if rc!=SQLITE_OK */ 105 92 106 - OtaTblIter tbliter; /* Used to iterate through tables */ 107 - OtaIdxIter idxiter; /* Used to iterate through indexes */ 93 + int nStep; /* Rows processed for current object */ 94 + OtaObjIter objiter; 108 95 }; 109 96 97 +/* 98 +** Prepare the SQL statement in buffer zSql against database handle db. 99 +** If successful, set *ppStmt to point to the new statement and return 100 +** SQLITE_OK. 101 +** 102 +** Otherwise, if an error does occur, set *ppStmt to NULL and return 103 +** an SQLite error code. Additionally, set output variable *pzErrmsg to 104 +** point to a buffer containing an error message. It is the responsibility 105 +** of the caller to (eventually) free this buffer using sqlite3_free(). 106 +*/ 110 107 static int prepareAndCollectError( 111 108 sqlite3 *db, 112 - const char *zSql, 113 109 sqlite3_stmt **ppStmt, 114 - char **pzErrmsg 110 + char **pzErrmsg, 111 + const char *zSql 115 112 ){ 116 113 int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0); 117 114 if( rc!=SQLITE_OK ){ 118 115 *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); 119 116 *ppStmt = 0; 120 117 } 121 118 return rc; 122 119 } 120 + 121 +/* 122 +** Reset the SQL statement passed as the first argument. Return a copy 123 +** of the value returned by sqlite3_reset(). 124 +** 125 +** If an error has occurred, then set *pzErrmsg to point to a buffer 126 +** containing an error message. It is the responsibility of the caller 127 +** to eventually free this buffer using sqlite3_free(). 128 +*/ 129 +static int resetAndCollectError(sqlite3_stmt *pStmt, char **pzErrmsg){ 130 + int rc = sqlite3_reset(pStmt); 131 + if( rc!=SQLITE_OK ){ 132 + *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(sqlite3_db_handle(pStmt))); 133 + } 134 + return rc; 135 +} 123 136 124 137 /* 125 138 ** Unless it is NULL, argument zSql points to a buffer allocated using 126 139 ** sqlite3_malloc containing an SQL statement. This function prepares the SQL 127 140 ** statement against database db and frees the buffer. If statement 128 141 ** compilation is successful, *ppStmt is set to point to the new statement 129 142 ** handle and SQLITE_OK is returned. ................................................................................ 134 147 ** buffer using sqlite3_free(). 135 148 ** 136 149 ** If argument zSql is NULL, this function assumes that an OOM has occurred. 137 150 ** In this case SQLITE_NOMEM is returned and *ppStmt set to NULL. 138 151 */ 139 152 static int prepareFreeAndCollectError( 140 153 sqlite3 *db, 141 - char *zSql, 142 154 sqlite3_stmt **ppStmt, 143 - char **pzErrmsg 155 + char **pzErrmsg, 156 + char *zSql 144 157 ){ 145 158 int rc; 146 159 assert( *pzErrmsg==0 ); 147 160 if( zSql==0 ){ 148 161 rc = SQLITE_NOMEM; 149 162 *ppStmt = 0; 150 163 }else{ 151 - rc = prepareAndCollectError(db, zSql, ppStmt, pzErrmsg); 164 + rc = prepareAndCollectError(db, ppStmt, pzErrmsg, zSql); 152 165 sqlite3_free(zSql); 153 166 } 154 167 return rc; 155 168 } 156 169 157 -static char *quoteSqlName(const char *zName){ 170 +/* 171 +** Free the OtaObjIter.azTblCol[] and OtaObjIter.abTblPk[] arrays allocated 172 +** by an earlier call to otaObjIterGetCols(). 173 +*/ 174 +static void otaObjIterFreeCols(OtaObjIter *pIter){ 175 + int i; 176 + for(i=0; i<pIter->nTblCol; i++){ 177 + sqlite3_free(pIter->azTblCol[i]); 178 + } 179 + sqlite3_free(pIter->azTblCol); 180 + sqlite3_free(pIter->abTblPk); 181 + pIter->azTblCol = 0; 182 + pIter->abTblPk = 0; 183 + pIter->nTblCol = 0; 184 +} 185 + 186 +/* 187 +** Clean up any resources allocated as part of the iterator object passed 188 +** as the only argument. 189 +*/ 190 +static void otaObjIterFinalize(OtaObjIter *pIter){ 191 + sqlite3_finalize(pIter->pTblIter); 192 + sqlite3_finalize(pIter->pIdxIter); 193 + sqlite3_finalize(pIter->pSelect); 194 + sqlite3_finalize(pIter->pInsert); 195 + otaObjIterFreeCols(pIter); 196 + memset(pIter, 0, sizeof(OtaObjIter)); 197 +} 198 + 199 +/* 200 +** Advance the iterator to the next position. 201 +** 202 +** If no error occurs, SQLITE_OK is returned and the iterator is left 203 +** pointing to the next entry. Otherwise, an error code and message is 204 +** left in the OTA handle passed as the first argument. A copy of the 205 +** error code is returned. 206 +*/ 207 +static int otaObjIterNext(sqlite3ota *p, OtaObjIter *pIter){ 208 + int rc = p->rc; 209 + if( rc==SQLITE_OK ){ 210 + 211 + /* Free any SQLite statements used while processing the previous object */ 212 + sqlite3_finalize(pIter->pSelect); 213 + sqlite3_finalize(pIter->pInsert); 214 + pIter->pSelect = 0; 215 + pIter->pInsert = 0; 216 + pIter->nCol = 0; 217 + 218 + if( pIter->bCleanup ){ 219 + otaObjIterFreeCols(pIter); 220 + pIter->bCleanup = 0; 221 + rc = sqlite3_step(pIter->pTblIter); 222 + if( rc!=SQLITE_ROW ){ 223 + rc = sqlite3_reset(pIter->pTblIter); 224 + pIter->zTbl = 0; 225 + }else{ 226 + pIter->zTbl = (const char*)sqlite3_column_text(pIter->pTblIter, 0); 227 + rc = SQLITE_OK; 228 + } 229 + }else{ 230 + if( pIter->zIdx==0 ){ 231 + sqlite3_bind_text(pIter->pIdxIter, 1, pIter->zTbl, -1, SQLITE_STATIC); 232 + } 233 + rc = sqlite3_step(pIter->pIdxIter); 234 + if( rc!=SQLITE_ROW ){ 235 + rc = sqlite3_reset(pIter->pIdxIter); 236 + pIter->bCleanup = 1; 237 + pIter->zIdx = 0; 238 + }else{ 239 + pIter->zIdx = (const char*)sqlite3_column_text(pIter->pIdxIter, 0); 240 + rc = SQLITE_OK; 241 + } 242 + } 243 + } 244 + 245 + if( rc!=SQLITE_OK ){ 246 + otaObjIterFinalize(pIter); 247 + p->rc = rc; 248 + } 249 + pIter->iVisit++; 250 + return rc; 251 +} 252 + 253 +/* 254 +** Initialize the iterator structure passed as the second argument. 255 +** 256 +** If no error occurs, SQLITE_OK is returned and the iterator is left 257 +** pointing to the first entry. Otherwise, an error code and message is 258 +** left in the OTA handle passed as the first argument. A copy of the 259 +** error code is returned. 260 +*/ 261 +static int otaObjIterFirst(sqlite3ota *p, OtaObjIter *pIter){ 262 + int rc; 263 + memset(pIter, 0, sizeof(OtaObjIter)); 264 + 265 + rc = prepareAndCollectError(p->dbOta, &pIter->pTblIter, &p->zErrmsg, 266 + "SELECT substr(name, 6) FROM sqlite_master " 267 + "WHERE type='table' AND name LIKE 'data_%'" 268 + ); 269 + 270 + if( rc==SQLITE_OK ){ 271 + rc = prepareAndCollectError(p->dbDest, &pIter->pIdxIter, &p->zErrmsg, 272 + "SELECT name FROM sqlite_master " 273 + "WHERE type='index' AND tbl_name = ?" 274 + ); 275 + } 276 + 277 + pIter->bCleanup = 1; 278 + p->rc = rc; 279 + return otaObjIterNext(p, pIter); 280 +} 281 + 282 +/* 283 +** Allocate a buffer and populate it with the double-quoted version of the 284 +** string in the argument buffer, suitable for use as an SQL identifier. 285 +** For example: 286 +** 287 +** [quick "brown" fox] -> ["quick ""brown"" fox"] 288 +** 289 +** Assuming the allocation is successful, a pointer to the new buffer is 290 +** returned. It is the responsibility of the caller to free it using 291 +** sqlite3_free() at some point in the future. Or, if the allocation fails, 292 +** a NULL pointer is returned. 293 +*/ 294 +static char *otaQuoteName(const char *zName){ 158 295 int nName = strlen(zName); 159 296 char *zRet = sqlite3_malloc(nName * 2 + 2 + 1); 160 297 if( zRet ){ 161 298 int i; 162 299 char *p = zRet; 163 300 *p++ = '"'; 164 301 for(i=0; i<nName; i++){ ................................................................................ 167 304 } 168 305 *p++ = '"'; 169 306 *p++ = '\0'; 170 307 } 171 308 return zRet; 172 309 } 173 310 174 -static int tblIterPrepareAll(sqlite3ota *p){ 175 - OtaTblIter *pIter = &p->tbliter; 176 - int rc = SQLITE_OK; 177 - char *zCol = 0; 178 - char *zBindings = 0; 179 - char *zSql; 180 - sqlite3_stmt *pPragma = 0; 181 - int i; 182 - int bSeenPk = 0; /* Set to true once PK column seen */ 183 - 184 - /* Allocate and populate the azCol[] array */ 185 - zSql = sqlite3_mprintf("PRAGMA main.table_info(%Q)", pIter->zTarget); 186 - rc = prepareFreeAndCollectError(p->dbDest, zSql, &pPragma, &p->zErrmsg); 187 - pIter->nCol = 0; 188 - if( rc==SQLITE_OK ){ 189 - while( SQLITE_ROW==sqlite3_step(pPragma) ){ 190 - const char *zName = (const char*)sqlite3_column_text(pPragma, 1); 191 - if( (pIter->nCol % 4)==0 ){ 192 - int nByte = sizeof(char*) * (pIter->nCol+4); 193 - char **azNew = (char**)sqlite3_realloc(pIter->azCol, nByte); 194 - if( azNew==0 ){ 195 - rc = SQLITE_NOMEM; 196 - break; 197 - } 198 - pIter->azCol = azNew; 311 +/* 312 +** If they are not already populated, populate the pIter->azTblCol[], 313 +** pIter->abTblPk[] and pIter->nTblCol variables according to the table 314 +** that the iterator currently points to. 315 +** 316 +** Return SQLITE_OK if successful, or an SQLite error code otherwise. If 317 +** an error does occur, an error code and error message are also left in 318 +** the OTA handle. 319 +*/ 320 +static int otaObjIterGetCols(sqlite3ota *p, OtaObjIter *pIter){ 321 + if( pIter->azTblCol==0 ){ 322 + sqlite3_stmt *pStmt; 323 + char *zSql; 324 + int nCol = 0; 325 + int bSeenPk = 0; 326 + int rc2; /* sqlite3_finalize() return value */ 327 + 328 + zSql = sqlite3_mprintf("PRAGMA table_info(%Q)", pIter->zTbl); 329 + p->rc = prepareFreeAndCollectError(p->dbDest, &pStmt, &p->zErrmsg, zSql); 330 + while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ 331 + if( (nCol % 8)==0 ){ 332 + int nByte = sizeof(char*) * (nCol+8); 333 + char **azNew = (char**)sqlite3_realloc(pIter->azTblCol, nByte); 334 + u8 *abNew = (u8*)sqlite3_realloc(pIter->azTblCol, nCol+8); 335 + 336 + if( azNew ) pIter->azTblCol = azNew; 337 + if( abNew ) pIter->abTblPk = abNew; 338 + if( azNew==0 || abNew==0 ) p->rc = SQLITE_NOMEM; 339 + } 340 + 341 + if( p->rc==SQLITE_OK ){ 342 + const char *zName = (const char*)sqlite3_column_text(pStmt, 1); 343 + pIter->abTblPk[nCol] = sqlite3_column_int(pStmt, 5); 344 + if( pIter->abTblPk[nCol] ) bSeenPk = 1; 345 + pIter->azTblCol[nCol] = otaQuoteName(zName); 346 + if( pIter->azTblCol[nCol]==0 ) p->rc = SQLITE_NOMEM; 347 + nCol++; 199 348 } 200 - pIter->azCol[pIter->nCol] = quoteSqlName(zName); 201 - if( pIter->azCol[pIter->nCol]==0 ){ 202 - rc = SQLITE_NOMEM; 349 + } 350 + pIter->nTblCol = nCol; 351 + rc2 = sqlite3_finalize(pStmt); 352 + if( p->rc==SQLITE_OK ) p->rc = rc2; 353 + 354 + if( p->rc==SQLITE_OK && bSeenPk==0 ){ 355 + p->zErrmsg = sqlite3_mprintf("table %s has no PRIMARY KEY", pIter->zTbl); 356 + p->rc = SQLITE_ERROR; 357 + } 358 + } 359 + 360 + return p->rc; 361 +} 362 + 363 +static char *otaObjIterGetCollist( 364 + sqlite3ota *p, 365 + OtaObjIter *pIter, 366 + int nCol, 367 + int *aiCol 368 +){ 369 + char *zList = 0; 370 + if( p->rc==SQLITE_OK ){ 371 + const char *zSep = ""; 372 + int i; 373 + for(i=0; i<nCol; i++){ 374 + int iCol = aiCol ? aiCol[i] : i; 375 + zList = sqlite3_mprintf("%z%s%s", zList, zSep, pIter->azTblCol[iCol]); 376 + zSep = ", "; 377 + if( zList==0 ){ 378 + p->rc = SQLITE_NOMEM; 203 379 break; 204 380 } 205 - pIter->nCol++; 206 - if( sqlite3_column_int(pPragma, 5) ) bSeenPk = 1; 207 - } 208 - if( rc==SQLITE_OK ){ 209 - rc = sqlite3_finalize(pPragma); 210 - }else{ 211 - sqlite3_finalize(pPragma); 212 381 } 213 382 } 383 + return zList; 384 +} 214 385 215 - /* If the table has no PRIMARY KEY, throw an exception. */ 216 - if( bSeenPk==0 ){ 217 - p->zErrmsg = sqlite3_mprintf("table %s has no PRIMARY KEY", pIter->zTarget); 218 - rc = SQLITE_ERROR; 219 - } 220 - 221 - /* Populate the zCol variable */ 222 - for(i=0; rc==SQLITE_OK && i<pIter->nCol; i++){ 223 - zCol = sqlite3_mprintf("%z%s%s", zCol, (i==0?"":", "), pIter->azCol[i]); 224 - if( zCol==0 ){ 225 - rc = SQLITE_NOMEM; 226 - } 227 - } 228 - 229 - /* Allocate and populate zBindings */ 230 - if( rc==SQLITE_OK ){ 231 - zBindings = (char*)sqlite3_malloc(pIter->nCol * 2); 232 - if( zBindings==0 ){ 233 - rc = SQLITE_NOMEM; 386 +static char *otaObjIterGetBindlist(sqlite3ota *p, int nBind){ 387 + char *zRet = 0; 388 + if( p->rc==SQLITE_OK ){ 389 + int nByte = nBind*2 + 1; 390 + zRet = sqlite3_malloc(nByte); 391 + if( zRet==0 ){ 392 + p->rc = SQLITE_NOMEM; 234 393 }else{ 235 394 int i; 236 - for(i=0; i<pIter->nCol; i++){ 237 - zBindings[i*2] = '?'; 238 - zBindings[i*2+1] = ','; 395 + for(i=0; i<nBind; i++){ 396 + zRet[i*2] = '?'; 397 + zRet[i*2+1] = (i+1==nBind) ? '\0' : ','; 398 + } 399 + } 400 + } 401 + return zRet; 402 +} 403 + 404 +/* 405 +** Ensure that the SQLite statement handles required to update the 406 +** target database object currently indicated by the iterator passed 407 +** as the second argument are available. 408 +*/ 409 +static int otaObjIterPrepareAll( 410 + sqlite3ota *p, 411 + OtaObjIter *pIter, 412 + int nOffset /* Add "LIMIT -1 OFFSET $nOffset" to SELECT */ 413 +){ 414 + assert( pIter->bCleanup==0 ); 415 + if( pIter->pSelect==0 && otaObjIterGetCols(p, pIter)==SQLITE_OK ){ 416 + char *zCollist = 0; /* List of indexed columns */ 417 + char **pz = &p->zErrmsg; 418 + const char *zIdx = pIter->zIdx; 419 + char *zLimit = 0; 420 + 421 + if( nOffset ){ 422 + zLimit = sqlite3_mprintf(" LIMIT -1 OFFSET %d", nOffset); 423 + if( !zLimit ) p->rc = SQLITE_NOMEM; 424 + } 425 + 426 + if( zIdx ){ 427 + int *aiCol; /* Column map */ 428 + 429 + /* Create the index writer */ 430 + if( p->rc==SQLITE_OK ){ 431 + p->rc = sqlite3_index_writer( 432 + p->dbDest, 0, zIdx, &pIter->pInsert, &aiCol, &pIter->nCol 433 + ); 434 + } 435 + 436 + /* Create the SELECT statement to read keys in sorted order */ 437 + zCollist = otaObjIterGetCollist(p, pIter, pIter->nCol, aiCol); 438 + if( p->rc==SQLITE_OK ){ 439 + p->rc = prepareFreeAndCollectError(p->dbOta, &pIter->pSelect, pz, 440 + sqlite3_mprintf( 441 + "SELECT %s FROM 'data_%q' ORDER BY %s%s", 442 + zCollist, pIter->zTbl, zCollist, zLimit 443 + ) 444 + ); 445 + } 446 + }else{ 447 + char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol); 448 + zCollist = otaObjIterGetCollist(p, pIter, pIter->nTblCol, 0); 449 + pIter->nCol = pIter->nTblCol; 450 + 451 + /* Create the SELECT statement to read keys from data_xxx */ 452 + if( p->rc==SQLITE_OK ){ 453 + p->rc = prepareFreeAndCollectError(p->dbOta, &pIter->pSelect, pz, 454 + sqlite3_mprintf( 455 + "SELECT %s FROM 'data_%q'%s", 456 + zCollist, pIter->zTbl, zLimit) 457 + ); 458 + } 459 + 460 + /* Create the INSERT statement to write to the target PK b-tree */ 461 + if( p->rc==SQLITE_OK ){ 462 + p->rc = prepareFreeAndCollectError(p->dbDest, &pIter->pInsert, pz, 463 + sqlite3_mprintf( 464 + "INSERT INTO %Q(%s) VALUES(%s)", pIter->zTbl, zCollist, zBindings 465 + ) 466 + ); 239 467 } 240 - zBindings[pIter->nCol*2-1] = '\0'; 241 - } 242 - } 243 - 244 - /* Create OtaTblIter.pSelect */ 245 - if( rc==SQLITE_OK ){ 246 - zSql = sqlite3_mprintf("SELECT rowid, %s FROM %Q", zCol, pIter->zSource); 247 - rc = prepareFreeAndCollectError(p->dbOta,zSql,&pIter->pSelect, &p->zErrmsg); 248 - } 249 - 250 - /* Create OtaTblIter.pInsert */ 251 - if( rc==SQLITE_OK ){ 252 - zSql = sqlite3_mprintf("INSERT INTO %Q(%s) VALUES(%s)", 253 - pIter->zTarget, zCol, zBindings 254 - ); 255 - rc = prepareFreeAndCollectError(p->dbDest,zSql,&pIter->pInsert,&p->zErrmsg); 256 - } 257 - 258 - sqlite3_free(zCol); 259 - sqlite3_free(zBindings); 260 - return rc; 261 -} 262 - 263 -static void tblIterFreeAll(OtaTblIter *pIter){ 468 + sqlite3_free(zBindings); 469 + } 470 + sqlite3_free(zCollist); 471 + sqlite3_free(zLimit); 472 + } 473 + 474 + return p->rc; 475 +} 476 + 477 +/* 478 +** This function does the work for an sqlite3ota_step() call. 479 +** 480 +** The object-iterator (p->objiter) currently points to a valid object, 481 +** and the input cursor (p->objiter.pSelect) currently points to a valid 482 +** input row. Perform whatever processing is required and return. 483 +** 484 +** If no error occurs, SQLITE_OK is returned. Otherwise, an error code 485 +** and message is left in the OTA handle and a copy of the error code 486 +** returned. 487 +*/ 488 +static int otaStep(sqlite3ota *p){ 489 + OtaObjIter *pIter = &p->objiter; 264 490 int i; 265 491 266 - sqlite3_finalize(pIter->pSelect); 267 - sqlite3_finalize(pIter->pInsert); 268 - for(i=0; i<pIter->nCol; i++) sqlite3_free(pIter->azCol[i]); 269 - sqlite3_free(pIter->azCol); 270 - pIter->azCol = 0; 271 - pIter->pSelect = 0; 272 - pIter->pInsert = 0; 273 - pIter->nCol = 0; 274 -} 275 - 276 -static int tblIterNext(OtaTblIter *pIter){ 277 - int rc; 278 - 279 - tblIterFreeAll(pIter); 280 - assert( pIter->pTblIter ); 281 - rc = sqlite3_step(pIter->pTblIter); 282 - if( rc==SQLITE_ROW ){ 283 - pIter->zSource = (const char*)sqlite3_column_text(pIter->pTblIter, 0); 284 - pIter->zTarget = &pIter->zSource[5]; assert( 5==strlen("data_") ); 285 - pIter->iEntry++; 286 - }else{ 287 - pIter->zSource = 0; 288 - pIter->zTarget = 0; 289 - } 290 - 291 - if( rc==SQLITE_ROW || rc==SQLITE_DONE ) rc = SQLITE_OK; 292 - return rc; 293 -} 294 - 295 -static int tblIterFirst(sqlite3 *db, OtaTblIter *pIter){ 296 - int rc; /* return code */ 297 - memset(pIter, 0, sizeof(OtaTblIter)); 298 - rc = sqlite3_prepare_v2(db, 299 - "SELECT name FROM sqlite_master " 300 - "WHERE type='table' AND name LIKE 'data_%'", -1, &pIter->pTblIter, 0 301 - ); 302 - if( rc==SQLITE_OK ){ 303 - rc = tblIterNext(pIter); 304 - } 305 - return rc; 306 -} 307 - 308 - 309 -static void tblIterFinalize(OtaTblIter *pIter){ 310 - tblIterFreeAll(pIter); 311 - sqlite3_finalize(pIter->pTblIter); 312 - memset(pIter, 0, sizeof(OtaTblIter)); 313 -} 314 - 315 -static void idxIterFreeAll(OtaIdxIter *pIter){ 316 - sqlite3_finalize(pIter->pWriter); 317 - sqlite3_finalize(pIter->pSelect); 318 - pIter->pWriter = 0; 319 - pIter->pSelect = 0; 320 - pIter->aiCol = 0; 321 - pIter->nCol = 0; 322 -} 323 - 324 -static int idxIterPrepareAll(sqlite3ota *p){ 325 - int rc; 326 - int i; /* Iterator variable */ 327 - char *zSql = 0; 328 - char *zCols = 0; /* Columns list */ 329 - OtaIdxIter *pIter = &p->idxiter; 330 - 331 - /* Prepare the writer statement to write (insert) entries into the index. */ 332 - rc = sqlite3_index_writer( 333 - p->dbDest, 0, pIter->zIndex, &pIter->pWriter, &pIter->aiCol, &pIter->nCol 334 - ); 335 - 336 - /* Prepare a SELECT statement to read values from the source table in 337 - ** the same order as they are stored in the current index. The statement 338 - ** is: 339 - ** 340 - ** SELECT rowid, <cols> FROM data_<tbl> ORDER BY <cols> 341 - */ 342 - for(i=0; rc==SQLITE_OK && i<pIter->nCol; i++){ 343 - const char *zQuoted = p->tbliter.azCol[ pIter->aiCol[i] ]; 344 - zCols = sqlite3_mprintf("%z%s%s", zCols, zCols?", ":"", zQuoted); 345 - if( !zCols ){ 346 - rc = SQLITE_NOMEM; 347 - } 348 - } 349 - if( rc==SQLITE_OK ){ 350 - const char *zFmt = "SELECT rowid, %s FROM %Q ORDER BY %s"; 351 - zSql = sqlite3_mprintf(zFmt, zCols, p->tbliter.zSource, zCols); 352 - if( zSql ){ 353 - sqlite3_stmt **pp = &p->idxiter.pSelect; 354 - rc = prepareFreeAndCollectError(p->dbOta, zSql, pp, &p->zErrmsg); 355 - }else{ 356 - rc = SQLITE_NOMEM; 357 - } 358 - } 359 - 360 - sqlite3_free(zCols); 361 - return rc; 362 -} 363 - 364 -static int idxIterNext(OtaIdxIter *pIter){ 365 - int rc; 366 - 367 - idxIterFreeAll(pIter); 368 - assert( pIter->pIdxIter ); 369 - rc = sqlite3_step(pIter->pIdxIter); 370 - if( rc==SQLITE_ROW ){ 371 - pIter->zIndex = (const char*)sqlite3_column_text(pIter->pIdxIter, 0); 372 - pIter->iEntry++; 373 - }else{ 374 - pIter->zIndex = 0; 375 - rc = sqlite3_finalize(pIter->pIdxIter); 376 - pIter->pIdxIter = 0; 377 - } 378 - 379 - if( rc==SQLITE_ROW ) rc = SQLITE_OK; 380 - return rc; 381 -} 382 - 383 -static int idxIterFirst(sqlite3 *db, const char *zTable, OtaIdxIter *pIter){ 384 - int rc; /* return code */ 385 - memset(pIter, 0, sizeof(OtaIdxIter)); 386 - rc = sqlite3_prepare_v2(db, 387 - "SELECT name FROM sqlite_master " 388 - "WHERE type='index' AND tbl_name = ?", -1, &pIter->pIdxIter, 0 389 - ); 390 - if( rc==SQLITE_OK ){ 391 - rc = sqlite3_bind_text(pIter->pIdxIter, 1, zTable, -1, SQLITE_TRANSIENT); 392 - } 393 - if( rc==SQLITE_OK ){ 394 - rc = idxIterNext(pIter); 395 - } 396 - return rc; 397 -} 398 - 399 -static void idxIterFinalize(OtaIdxIter *pIter){ 400 - idxIterFreeAll(pIter); 401 - sqlite3_finalize(pIter->pIdxIter); 402 - memset(pIter, 0, sizeof(OtaIdxIter)); 492 + for(i=0; i<pIter->nCol; i++){ 493 + sqlite3_value *pVal = sqlite3_column_value(pIter->pSelect, i); 494 + sqlite3_bind_value(pIter->pInsert, i+1, pVal); 495 + } 496 + 497 + sqlite3_step(pIter->pInsert); 498 + p->rc = resetAndCollectError(pIter->pInsert, &p->zErrmsg); 499 + return p->rc; 403 500 } 404 501 405 502 /* 406 -** Call sqlite3_reset() on the SQL statement passed as the second argument. 407 -** If it returns anything other than SQLITE_OK, store the error code and 408 -** error message in the OTA handle. 503 +** Step the OTA object. 409 504 */ 410 -static void otaResetStatement(sqlite3ota *p, sqlite3_stmt *pStmt){ 411 - assert( p->rc==SQLITE_OK ); 412 - assert( p->zErrmsg==0 ); 413 - p->rc = sqlite3_reset(pStmt); 414 - if( p->rc!=SQLITE_OK ){ 415 - sqlite3 *db = sqlite3_db_handle(pStmt); 416 - p->zErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); 417 - } 418 -} 419 - 420 -/* 421 -** Check that all SQL statements required to process the current 422 -** table and index have been prepared. If not, prepare them. If 423 -** an error occurs, store the error code and message in the OTA 424 -** handle before returning. 425 -*/ 426 -static int otaPrepareAll(sqlite3ota *p){ 427 - assert( p->rc==SQLITE_OK ); 428 - assert( p->zErrmsg==0 ); 429 - assert( p->tbliter.zTarget ); 430 - 431 - if( p->tbliter.pSelect==0 ){ 432 - p->rc = tblIterPrepareAll(p); 433 - } 434 - if( p->rc==SQLITE_OK && p->idxiter.zIndex && 0==p->idxiter.pSelect ){ 435 - p->rc = idxIterPrepareAll(p); 436 - } 437 - return p->rc; 438 -} 439 - 440 505 int sqlite3ota_step(sqlite3ota *p){ 441 506 if( p ){ 442 - while( p && p->rc==SQLITE_OK && p->tbliter.zTarget ){ 443 - sqlite3_stmt *pSelect; 444 - int i; 507 + OtaObjIter *pIter = &p->objiter; 508 + while( p && p->rc==SQLITE_OK && pIter->zTbl ){ 445 509 446 - otaPrepareAll(p); 447 - pSelect = (p->idxiter.zIndex ? p->idxiter.pSelect : p->tbliter.pSelect); 448 - 449 - /* Advance to the next input row. */ 450 - if( p->rc==SQLITE_OK ){ 451 - int rc = sqlite3_step(pSelect); 452 - if( rc!=SQLITE_ROW ){ 453 - otaResetStatement(p, pSelect); 454 - 455 - /* Go to the next index. */ 456 - if( p->rc==SQLITE_OK ){ 457 - if( p->idxiter.zIndex ){ 458 - p->rc = idxIterNext(&p->idxiter); 459 - }else{ 460 - p->rc = idxIterFirst(p->dbDest, p->tbliter.zTarget, &p->idxiter); 461 - } 510 + if( pIter->bCleanup ){ 511 + /* this is where cleanup of the ota_xxx table will happen... */ 512 + }else{ 513 + otaObjIterPrepareAll(p, pIter, 0); 514 + 515 + /* Advance to the next row to process. */ 516 + if( p->rc==SQLITE_OK ){ 517 + int rc = sqlite3_step(pIter->pSelect); 518 + if( rc==SQLITE_ROW ){ 519 + p->nStep++; 520 + return otaStep(p); 462 521 } 463 - 464 - /* If there is no next index, go to the next table. */ 465 - if( p->rc==SQLITE_OK && p->idxiter.zIndex==0 ){ 466 - p->rc = tblIterNext(&p->tbliter); 467 - } 468 - continue; 522 + p->rc = sqlite3_reset(pIter->pSelect); 523 + p->nStep = 0; 469 524 } 470 525 } 471 526 472 - /* Update the target database PK table according to the row that 473 - ** tbliter.pSelect currently points to. 474 - ** 475 - ** todo: For now, we assume all rows are INSERT commands - this will 476 - ** change. */ 477 - if( p->rc==SQLITE_OK ){ 478 - sqlite3_stmt *pInsert; 479 - int nCol; 480 - if( p->idxiter.zIndex ){ 481 - pInsert = p->idxiter.pWriter; 482 - nCol = p->idxiter.nCol; 483 - }else{ 484 - pInsert = p->tbliter.pInsert; 485 - nCol = p->tbliter.nCol; 486 - } 487 - 488 - for(i=0; i<nCol; i++){ 489 - sqlite3_value *pVal = sqlite3_column_value(pSelect, i+1); 490 - sqlite3_bind_value(pInsert, i+1, pVal); 491 - } 492 - 493 - sqlite3_step(pInsert); 494 - otaResetStatement(p, pInsert); 495 - } 496 - 497 - break; 527 + otaObjIterNext(p, pIter); 498 528 } 499 529 500 - if( p->rc==SQLITE_OK && p->tbliter.zTarget==0 ) p->rc = SQLITE_DONE; 530 + if( p->rc==SQLITE_OK && pIter->zTbl==0 ){ 531 + p->rc = SQLITE_DONE; 532 + } 501 533 } 502 - 503 - return (p ? p->rc : SQLITE_NOMEM); 534 + return p->rc; 504 535 } 505 536 506 537 static void otaOpenDatabase(sqlite3ota *p, sqlite3 **pDb, const char *zFile){ 507 538 if( p->rc==SQLITE_OK ){ 508 539 p->rc = sqlite3_open(zFile, pDb); 509 540 if( p->rc ){ 510 541 const char *zErr = sqlite3_errmsg(*pDb); 511 542 p->zErrmsg = sqlite3_mprintf("sqlite3_open(): %s", zErr); 512 543 } 513 544 } 514 545 } 515 546 516 547 static void otaSaveTransactionState(sqlite3ota *p){ 517 - sqlite3_stmt *pSelect; 518 548 char *zInsert; 519 549 520 - pSelect = (p->idxiter.zIndex ? p->idxiter.pSelect : p->tbliter.pSelect); 521 550 zInsert = sqlite3_mprintf( 522 551 "INSERT OR REPLACE INTO ota_state(rowid, tbl, idx, row, progress)" 523 - "VALUES(1, %Q, %Q, %lld, NULL)", 524 - p->tbliter.zTarget, p->idxiter.zIndex, sqlite3_column_int64(pSelect, 0) 552 + "VALUES(1, %Q, %Q, %d, NULL)", 553 + p->objiter.zTbl, p->objiter.zIdx, p->nStep 525 554 ); 555 + 526 556 if( zInsert==0 ){ 527 557 p->rc = SQLITE_NOMEM; 528 558 }else{ 529 559 p->rc = sqlite3_exec(p->dbOta, zInsert, 0, 0, &p->zErrmsg); 530 560 if( p->rc==SQLITE_OK ){ 531 561 p->rc = sqlite3_exec(p->dbOta, "COMMIT", 0, 0, &p->zErrmsg); 532 562 } ................................................................................ 547 577 static OtaState *otaLoadState(sqlite3ota *p){ 548 578 const char *zSelect = "SELECT tbl, idx, row, progress FROM ota_state"; 549 579 OtaState *pRet = 0; 550 580 sqlite3_stmt *pStmt; 551 581 int rc; 552 582 553 583 assert( p->rc==SQLITE_OK ); 554 - rc = prepareAndCollectError(p->dbOta, zSelect, &pStmt, &p->zErrmsg); 584 + rc = prepareAndCollectError(p->dbOta, &pStmt, &p->zErrmsg, zSelect); 555 585 if( rc==SQLITE_OK ){ 556 586 if( sqlite3_step(pStmt)==SQLITE_ROW ){ 557 587 const char *zIdx = (const char*)sqlite3_column_text(pStmt, 1); 558 588 const char *zTbl = (const char*)sqlite3_column_text(pStmt, 0); 559 589 int nIdx = zIdx ? (strlen(zIdx) + 1) : 0; 560 590 int nTbl = strlen(zTbl) + 1; 561 591 int nByte = sizeof(OtaState) + nTbl + nIdx; ................................................................................ 566 596 memcpy(pRet->zTbl, sqlite3_column_text(pStmt, 0), nTbl); 567 597 if( zIdx ){ 568 598 pRet->zIdx = &pRet->zTbl[nTbl]; 569 599 memcpy(pRet->zIdx, zIdx, nIdx); 570 600 }else{ 571 601 pRet->zIdx = 0; 572 602 } 573 - pRet->iRow = sqlite3_column_int64(pStmt, 2); 603 + pRet->nRow = sqlite3_column_int(pStmt, 2); 574 604 } 575 605 }else{ 576 606 pRet = (OtaState*)sqlite3_malloc(sizeof(OtaState)); 577 607 if( pRet ){ 578 608 memset(pRet, 0, sizeof(*pRet)); 579 609 } 580 610 } ................................................................................ 585 615 pRet = 0; 586 616 } 587 617 } 588 618 589 619 p->rc = rc; 590 620 return pRet; 591 621 } 622 + 623 +static int otaStrCompare(const char *z1, const char *z2){ 624 + if( z1==0 && z2==0 ) return 0; 625 + if( z1==0 || z2==0 ) return 1; 626 + return (sqlite3_stricmp(z1, z2)!=0); 627 +} 592 628 593 629 static void otaLoadTransactionState(sqlite3ota *p, OtaState *pState){ 594 630 assert( p->rc==SQLITE_OK ); 595 631 if( pState->zTbl ){ 632 + OtaObjIter *pIter = &p->objiter; 596 633 int rc; 597 - while( rc==SQLITE_OK 598 - && p->tbliter.zTarget 599 - && sqlite3_stricmp(p->tbliter.zTarget, pState->zTbl) 600 - ){ 601 - rc = tblIterNext(&p->tbliter); 634 + 635 + while( rc==SQLITE_OK && pIter->zTbl && (pIter->bCleanup 636 + || otaStrCompare(pIter->zTbl, pState->zTbl) 637 + || otaStrCompare(pIter->zIdx, pState->zIdx) 638 + )){ 639 + rc = otaObjIterNext(p, &p->objiter); 602 640 } 603 - if( rc==SQLITE_OK && !p->tbliter.zTarget ){ 641 + 642 + if( rc==SQLITE_OK && !p->objiter.zTbl ){ 604 643 rc = SQLITE_ERROR; 605 644 p->zErrmsg = sqlite3_mprintf("ota_state mismatch error"); 606 645 } 607 646 608 - if( rc==SQLITE_OK && pState->zIdx ){ 609 - rc = idxIterFirst(p->dbDest, p->tbliter.zTarget, &p->idxiter); 610 - while( rc==SQLITE_OK 611 - && p->idxiter.zIndex 612 - && sqlite3_stricmp(p->idxiter.zIndex, pState->zIdx) 613 - ){ 614 - rc = idxIterNext(&p->idxiter); 615 - } 616 - if( rc==SQLITE_OK && !p->idxiter.zIndex ){ 617 - rc = SQLITE_ERROR; 618 - p->zErrmsg = sqlite3_mprintf("ota_state mismatch error"); 619 - } 647 + if( rc==SQLITE_OK ){ 648 + p->nStep = pState->nRow; 649 + rc = otaObjIterPrepareAll(p, &p->objiter, p->nStep); 620 650 } 621 651 622 - if( rc==SQLITE_OK ){ 623 - rc = otaPrepareAll(p); 624 - } 625 - 626 - if( rc==SQLITE_OK ){ 627 - sqlite3_stmt *pSelect; 628 - pSelect = (p->idxiter.zIndex ? p->idxiter.pSelect : p->tbliter.pSelect); 629 - while( sqlite3_column_int64(pSelect, 0)!=pState->iRow ){ 630 - rc = sqlite3_step(pSelect); 631 - if( rc!=SQLITE_ROW ) break; 632 - } 633 - if( rc==SQLITE_ROW ){ 634 - rc = SQLITE_OK; 635 - }else{ 636 - rc = SQLITE_ERROR; 637 - p->zErrmsg = sqlite3_mprintf("ota_state mismatch error"); 638 - } 639 - } 640 652 p->rc = rc; 641 653 } 642 654 } 643 655 644 656 /* 645 657 ** Move the "*-oal" file corresponding to the target database to the 646 658 ** "*-wal" location. If an error occurs, leave an error code and error ................................................................................ 699 711 if( p->rc==SQLITE_OK ){ 700 712 pState = otaLoadState(p); 701 713 if( pState && pState->zTbl==0 ){ 702 714 otaDeleteOalFile(p); 703 715 } 704 716 } 705 717 706 - 707 718 if( p->rc==SQLITE_OK ){ 708 719 const char *zScript = 709 720 "PRAGMA journal_mode=off;" 710 721 "PRAGMA pager_ota_mode=1;" 711 722 "PRAGMA ota_mode=1;" 712 723 "BEGIN IMMEDIATE;" 713 724 ; ................................................................................ 715 726 } 716 727 717 728 if( p->rc==SQLITE_OK ){ 718 729 const char *zScript = "BEGIN IMMEDIATE"; 719 730 p->rc = sqlite3_exec(p->dbOta, zScript, 0, 0, &p->zErrmsg); 720 731 } 721 732 722 - /* Point the table iterator at the first table */ 733 + /* Point the object iterator at the first object */ 723 734 if( p->rc==SQLITE_OK ){ 724 - p->rc = tblIterFirst(p->dbOta, &p->tbliter); 735 + p->rc = otaObjIterFirst(p, &p->objiter); 725 736 } 726 737 727 738 if( p->rc==SQLITE_OK ){ 728 739 otaLoadTransactionState(p, pState); 729 740 } 730 741 731 742 sqlite3_free(pState); 732 743 } 733 744 734 745 return p; 735 746 } 736 747 737 -static void otaCloseHandle(sqlite3 *db){ 738 - int rc = sqlite3_close(db); 739 - assert( rc==SQLITE_OK ); 740 -} 741 - 748 +/* 749 +** Close the OTA handle. 750 +*/ 742 751 int sqlite3ota_close(sqlite3ota *p, char **pzErrmsg){ 743 752 int rc; 744 753 if( p ){ 745 754 746 755 /* If the update has not been fully applied, save the state in 747 756 ** the ota db. If successful, this call also commits the open 748 757 ** transaction on the ota db. */ ................................................................................ 749 758 assert( p->rc!=SQLITE_ROW ); 750 759 if( p->rc==SQLITE_OK ){ 751 760 assert( p->zErrmsg==0 ); 752 761 otaSaveTransactionState(p); 753 762 } 754 763 755 764 /* Close all open statement handles. */ 756 - tblIterFinalize(&p->tbliter); 757 - idxIterFinalize(&p->idxiter); 765 + otaObjIterFinalize(&p->objiter); 758 766 759 767 /* Commit the transaction to the *-oal file. */ 760 768 if( p->rc==SQLITE_OK || p->rc==SQLITE_DONE ){ 761 769 rc = sqlite3_exec(p->dbDest, "COMMIT", 0, 0, &p->zErrmsg); 762 770 if( rc!=SQLITE_OK ) p->rc = rc; 763 771 } 764 - otaCloseHandle(p->dbDest); 765 - otaCloseHandle(p->dbOta); 766 772 773 + assert( sqlite3_next_stmt(p->dbDest, 0)==0 ); 774 + assert( sqlite3_next_stmt(p->dbOta, 0)==0 ); 775 + 776 + /* Close the open database handles */ 777 + sqlite3_close(p->dbDest); 778 + sqlite3_close(p->dbOta); 779 + 780 + /* If the OTA has been completely applied and no error occurred, move 781 + ** the *-oal file to *-wal. */ 767 782 if( p->rc==SQLITE_DONE ){ 768 783 otaMoveOalFile(p); 769 784 } 770 785 771 786 rc = p->rc; 772 787 *pzErrmsg = p->zErrmsg; 773 788 sqlite3_free(p);