From ca5ceb422978102f4ba67e67df9c700df03194cb Mon Sep 17 00:00:00 2001 From: dan Date: Fri, 21 Nov 2014 10:46:23 +0000 Subject: [PATCH] Add support for updating virtual tables via ota. FossilOrigin-Name: 4dfcfe543945aa60a7ac397a3bdb0ac9e20ef7b6 --- ext/ota/ota9.test | 66 ++++++++++++++ ext/ota/sqlite3ota.c | 201 +++++++++++++++++++++++++++++-------------- ext/ota/sqlite3ota.h | 34 +++++++- manifest | 15 ++-- manifest.uuid | 2 +- 5 files changed, 241 insertions(+), 77 deletions(-) create mode 100644 ext/ota/ota9.test diff --git a/ext/ota/ota9.test b/ext/ota/ota9.test new file mode 100644 index 0000000000..18791b479e --- /dev/null +++ b/ext/ota/ota9.test @@ -0,0 +1,66 @@ +# 2014 November 21 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# Test OTA with virtual tables +# + +if {![info exists testdir]} { + set testdir [file join [file dirname [info script]] .. .. test] +} +source $testdir/tester.tcl +set ::testprefix ota9 + +ifcapable !fts3 { + finish_test + return +} + +do_execsql_test 1.1 { + CREATE VIRTUAL TABLE f1 USING fts4(a, b, c); + INSERT INTO f1(rowid, a, b, c) VALUES(11, 'a', 'b', 'c'); + INSERT INTO f1(rowid, a, b, c) VALUES(12, 'd', 'e', 'f'); + INSERT INTO f1(rowid, a, b, c) VALUES(13, 'g', 'h', 'i'); +} + +do_test 1.1 { + forcedelete ota.db + sqlite3 db2 ota.db + db2 eval { + CREATE TABLE data_f1(ota_rowid, a, b, c, ota_control); + INSERT INTO data_f1 VALUES(14, 'x', 'y', 'z', 0); -- INSERT + INSERT INTO data_f1 VALUES(11, NULL, NULL, NULL, 1); -- DELETE + INSERT INTO data_f1 VALUES(13, NULL, NULL, 'X', '..x'); -- UPDATE + } + db2 close +} {} + +do_test 1.2.1 { + while 1 { + sqlite3ota ota test.db ota.db + set rc [ota step] + if {$rc != "SQLITE_OK"} break + ota close + } + ota close +} {SQLITE_DONE} + +do_execsql_test 1.2.2 { SELECT rowid, * FROM f1 } { + 12 d e f + 13 g h X + 14 x y z +} +do_execsql_test 1.2.3 { INSERT INTO f1(f1) VALUES('integrity-check') } +integrity_check 1.2.4 + + + +finish_test + diff --git a/ext/ota/sqlite3ota.c b/ext/ota/sqlite3ota.c index 40091d965b..8613c179f6 100644 --- a/ext/ota/sqlite3ota.c +++ b/ext/ota/sqlite3ota.c @@ -103,6 +103,7 @@ struct OtaObjIter { char **azTblCol; /* Array of quoted column names */ unsigned char *abTblPk; /* Array of flags - true for PK columns */ unsigned char bRowid; /* True for implicit IPK tables */ + unsigned char bVtab; /* True for a virtual table */ /* Output variables. zTbl==0 implies EOF. */ int bCleanup; /* True in "cleanup" state */ @@ -227,6 +228,7 @@ static void otaObjIterFreeCols(OtaObjIter *pIter){ sqlite3_free(pIter->zMask); pIter->zMask = 0; pIter->bRowid = 0; + pIter->bVtab = 0; } /* @@ -391,6 +393,51 @@ static int otaMPrintfExec(sqlite3ota *p, const char *zFmt, ...){ return p->rc; } +/* +** Increase the size of 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 otaExtendIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){ + assert( p->rc==SQLITE_OK ); + if( (nCol % 8)==0 ){ + unsigned char *abNew; + int nByte = sizeof(char*) * (nCol+8); + char **azNew = (char**)sqlite3_realloc(pIter->azTblCol, nByte); + abNew = (unsigned char*)sqlite3_realloc(pIter->abTblPk, nCol+8); + + if( azNew ) pIter->azTblCol = azNew; + if( abNew ) pIter->abTblPk = abNew; + if( azNew==0 || abNew==0 ) p->rc = SQLITE_NOMEM; + } +} + +/* +** Return true if zTab is the name of a virtual table within the target +** database. +*/ +static int otaIsVtab(sqlite3ota *p, const char *zTab){ + int res = 0; + sqlite3_stmt *pSelect = 0; + + if( p->rc==SQLITE_OK ){ + p->rc = prepareAndCollectError(p->db, &pSelect, &p->zErrmsg, + "SELECT count(*) FROM sqlite_master WHERE name = ? AND type='table' " + "AND sql LIKE 'CREATE VIRTUAL TABLE%'" + ); + } + + if( p->rc==SQLITE_OK ){ + sqlite3_bind_text(pSelect, 1, zTab, -1, SQLITE_STATIC); + if( sqlite3_step(pSelect)==SQLITE_ROW ){ + res = sqlite3_column_int(pSelect, 0); + } + p->rc = sqlite3_finalize(pSelect); + } + + return res; +} + /* ** If they are not already populated, populate the pIter->azTblCol[], ** pIter->abTblPk[], pIter->nTblCol and pIter->bRowid variables according to @@ -408,21 +455,12 @@ static int otaObjIterGetCols(sqlite3ota *p, OtaObjIter *pIter){ int bSeenPk = 0; int rc2; /* sqlite3_finalize() return value */ - assert( pIter->bRowid==0 ); + assert( pIter->bRowid==0 && pIter->bVtab==0 ); + zSql = sqlite3_mprintf("PRAGMA main.table_info(%Q)", pIter->zTbl); p->rc = prepareFreeAndCollectError(p->db, &pStmt, &p->zErrmsg, zSql); while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ - if( (nCol % 8)==0 ){ - unsigned char *abNew; - int nByte = sizeof(char*) * (nCol+8); - char **azNew = (char**)sqlite3_realloc(pIter->azTblCol, nByte); - abNew = (unsigned char*)sqlite3_realloc(pIter->abTblPk, nCol+8); - - if( azNew ) pIter->azTblCol = azNew; - if( abNew ) pIter->abTblPk = abNew; - if( azNew==0 || abNew==0 ) p->rc = SQLITE_NOMEM; - } - + otaExtendIterArrays(p, pIter, nCol); if( p->rc==SQLITE_OK ){ const char *zName = (const char*)sqlite3_column_text(pStmt, 1); int iPk = sqlite3_column_int(pStmt, 5); @@ -439,8 +477,13 @@ static int otaObjIterGetCols(sqlite3ota *p, OtaObjIter *pIter){ if( p->rc==SQLITE_OK ) p->rc = rc2; if( p->rc==SQLITE_OK && bSeenPk==0 ){ - p->zErrmsg = sqlite3_mprintf("table %s has no PRIMARY KEY", pIter->zTbl); - p->rc = SQLITE_ERROR; + const char *zTab = pIter->zTbl; + if( otaIsVtab(p, zTab) ){ + pIter->bVtab = 1; + }else{ + p->zErrmsg = sqlite3_mprintf("table %s has no PRIMARY KEY", zTab); + p->rc = SQLITE_ERROR; + } } } @@ -557,16 +600,18 @@ static char *otaObjIterGetWhere( ){ char *zList = 0; if( p->rc==SQLITE_OK ){ - const char *zSep = ""; - int i; - for(i=0; inTblCol; i++){ - if( pIter->abTblPk[i] ){ - const char *zCol = pIter->azTblCol[i]; - zList = sqlite3_mprintf("%z%s%s=?%d", zList, zSep, zCol, i+1); - zSep = " AND "; - if( zList==0 ){ - p->rc = SQLITE_NOMEM; - break; + if( pIter->bVtab ){ + zList = otaMPrintfAndCollectError(p, "rowid = ?%d", pIter->nTblCol+1); + }else{ + const char *zSep = ""; + int i; + for(i=0; inTblCol; i++){ + if( pIter->abTblPk[i] ){ + const char *zCol = pIter->azTblCol[i]; + zList = otaMPrintfAndCollectError( + p, "%z%s%s=?%d", zList, zSep, zCol, i+1 + ); + zSep = " AND "; } } } @@ -664,6 +709,8 @@ static int otaObjIterPrepareAll( int *aiCol; /* Column map */ const char **azColl; /* Collation sequences */ + assert( pIter->bVtab==0 ); + /* Create the index writers */ if( p->rc==SQLITE_OK ){ p->rc = sqlite3_index_writer( @@ -701,8 +748,8 @@ static int otaObjIterPrepareAll( p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz, zSql); } }else{ - const char *zOtaRowid = (pIter->bRowid ? ", ota_rowid" : ""); - char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol); + const char *zTbl = pIter->zTbl; + char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol+pIter->bVtab); char *zWhere = otaObjIterGetWhere(p, pIter); char *zOldlist = otaObjIterGetOldlist(p, pIter, "old"); char *zNewlist = otaObjIterGetOldlist(p, pIter, "new"); @@ -713,8 +760,9 @@ static int otaObjIterPrepareAll( if( p->rc==SQLITE_OK ){ p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz, sqlite3_mprintf( - "SELECT %s, ota_control FROM ota.'data_%q'%s", - zCollist, pIter->zTbl, zLimit) + "SELECT %s, ota_control%s FROM ota.'data_%q'%s", + zCollist, (pIter->bVtab ? ", ota_rowid" : ""), zTbl, zLimit + ) ); } @@ -722,8 +770,8 @@ static int otaObjIterPrepareAll( if( p->rc==SQLITE_OK ){ p->rc = prepareFreeAndCollectError(p->db, &pIter->pInsert, pz, sqlite3_mprintf( - "INSERT INTO main.%Q(%s) VALUES(%s)", - pIter->zTbl, zCollist, zBindings + "INSERT INTO main.%Q(%s%s) VALUES(%s)", + zTbl, zCollist, (pIter->bVtab ? ", rowid" : ""), zBindings ) ); } @@ -732,48 +780,52 @@ static int otaObjIterPrepareAll( if( p->rc==SQLITE_OK ){ p->rc = prepareFreeAndCollectError(p->db, &pIter->pDelete, pz, sqlite3_mprintf( - "DELETE FROM main.%Q WHERE %s", pIter->zTbl, zWhere + "DELETE FROM main.%Q WHERE %s", zTbl, zWhere ) ); } - /* Create the ota_tmp_xxx table and the triggers to populate it. */ - otaMPrintfExec(p, - "CREATE TABLE IF NOT EXISTS ota.'ota_tmp_%q' AS " - "SELECT *%s FROM ota.'data_%q' WHERE 0;" - - "CREATE TEMP TRIGGER ota_delete_%q BEFORE DELETE ON main.%Q " - "BEGIN " - " INSERT INTO 'ota_tmp_%q'(ota_control, %s%s) VALUES(2, %s);" - "END;" - - "CREATE TEMP TRIGGER ota_update1_%q BEFORE UPDATE ON main.%Q " - "BEGIN " - " INSERT INTO 'ota_tmp_%q'(ota_control, %s%s) VALUES(2, %s);" - "END;" - - "CREATE TEMP TRIGGER ota_update2_%q AFTER UPDATE ON main.%Q " - "BEGIN " - " INSERT INTO 'ota_tmp_%q'(ota_control, %s%s) VALUES(3, %s);" - "END;" - , pIter->zTbl, (pIter->bRowid ? ", 0 AS ota_rowid" : ""), - pIter->zTbl, - pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOtaRowid, zOldlist, - pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOtaRowid, zOldlist, - pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOtaRowid, zNewlist - ); - if( pIter->bRowid ){ + if( pIter->bVtab==0 ){ + const char *zOtaRowid = (pIter->bRowid ? ", ota_rowid" : ""); + + /* Create the ota_tmp_xxx table and the triggers to populate it. */ otaMPrintfExec(p, - "CREATE TEMP TRIGGER ota_insert_%q AFTER INSERT ON main.%Q " + "PRAGMA ota_mode = 1;" + "CREATE TABLE IF NOT EXISTS ota.'ota_tmp_%q' AS " + "SELECT *%s FROM ota.'data_%q' WHERE 0;" + + "CREATE TEMP TRIGGER ota_delete_%q BEFORE DELETE ON main.%Q " "BEGIN " - " INSERT INTO 'ota_tmp_%q'(ota_control, %s, ota_rowid)" - " VALUES(0, %s);" + " INSERT INTO 'ota_tmp_%q'(ota_control, %s%s) VALUES(2, %s);" "END;" - , pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zNewlist - ); - } + "CREATE TEMP TRIGGER ota_update1_%q BEFORE UPDATE ON main.%Q " + "BEGIN " + " INSERT INTO 'ota_tmp_%q'(ota_control, %s%s) VALUES(2, %s);" + "END;" + "CREATE TEMP TRIGGER ota_update2_%q AFTER UPDATE ON main.%Q " + "BEGIN " + " INSERT INTO 'ota_tmp_%q'(ota_control, %s%s) VALUES(3, %s);" + "END;" + , zTbl, (pIter->bRowid ? ", 0 AS ota_rowid" : ""), zTbl, + zTbl, zTbl, zTbl, zCollist, zOtaRowid, zOldlist, + zTbl, zTbl, zTbl, zCollist, zOtaRowid, zOldlist, + zTbl, zTbl, zTbl, zCollist, zOtaRowid, zNewlist + ); + if( pIter->bRowid ){ + otaMPrintfExec(p, + "CREATE TEMP TRIGGER ota_insert_%q AFTER INSERT ON main.%Q " + "BEGIN " + " INSERT INTO 'ota_tmp_%q'(ota_control, %s, ota_rowid)" + " VALUES(0, %s);" + "END;" + , zTbl, zTbl, zTbl, zCollist, zNewlist + ); + } + }else if( p->rc==SQLITE_OK ){ + p->rc = sqlite3_exec(p->db, "PRAGMA ota_mode = 0", 0, 0, &p->zErrmsg); + } /* Allocate space required for the zMask field. */ if( p->rc==SQLITE_OK ){ @@ -1001,6 +1053,7 @@ static int otaStep(sqlite3ota *p){ || eType==OTA_IDX_DELETE || eType==OTA_IDX_INSERT ){ + sqlite3_value *pVal; sqlite3_stmt *pWriter; assert( eType!=OTA_UPDATE ); @@ -1013,23 +1066,37 @@ static int otaStep(sqlite3ota *p){ } for(i=0; inCol; i++){ - sqlite3_value *pVal; if( eType==SQLITE_DELETE && pIter->zIdx==0 && pIter->abTblPk[i]==0 ){ continue; } pVal = sqlite3_column_value(pIter->pSelect, i); sqlite3_bind_value(pWriter, i+1, pVal); } + if( pIter->bVtab ){ + /* For a virtual table, the SELECT statement is: + ** + ** SELECT , ota_control, ota_rowid FROM .... + ** + ** Hence column_value(pIter->nCol+1). + */ + pVal = sqlite3_column_value(pIter->pSelect, pIter->nCol+1); + sqlite3_bind_value(pWriter, pIter->nCol+1, pVal); + } sqlite3_step(pWriter); p->rc = resetAndCollectError(pWriter, &p->zErrmsg); }else if( eType==OTA_UPDATE ){ + sqlite3_value *pVal; sqlite3_stmt *pUpdate = 0; otaGetUpdateStmt(p, pIter, zMask, &pUpdate); if( pUpdate ){ for(i=0; inCol; i++){ - sqlite3_value *pVal = sqlite3_column_value(pIter->pSelect, i); + pVal = sqlite3_column_value(pIter->pSelect, i); sqlite3_bind_value(pUpdate, i+1, pVal); } + if( pIter->bVtab ){ + pVal = sqlite3_column_value(pIter->pSelect, pIter->nCol+1); + sqlite3_bind_value(pUpdate, pIter->nCol+1, pVal); + } sqlite3_step(pUpdate); p->rc = resetAndCollectError(pUpdate, &p->zErrmsg); } @@ -1078,7 +1145,9 @@ int sqlite3ota_step(sqlite3ota *p){ /* Clean up the ota_tmp_xxx table for the previous table. It ** cannot be dropped as there are currently active SQL statements. ** But the contents can be deleted. */ - otaMPrintfExec(p, "DELETE FROM ota.'ota_tmp_%q'", pIter->zTbl); + if( pIter->bVtab==0 ){ + otaMPrintfExec(p, "DELETE FROM ota.'ota_tmp_%q'", pIter->zTbl); + } }else{ otaObjIterPrepareAll(p, pIter, 0); diff --git a/ext/ota/sqlite3ota.h b/ext/ota/sqlite3ota.h index 022f3e3f3d..4d7fa2a2ba 100644 --- a/ext/ota/sqlite3ota.h +++ b/ext/ota/sqlite3ota.h @@ -94,6 +94,18 @@ ** ** The order of the columns in the data_% table does not matter. ** +** If the target database table is a virtual table, the data_% table should +** also contain a column named "ota_rowid". This column is mapped to the +** virtual tables implicit primary key column - "rowid". Virtual tables +** for which the "rowid" column does not function like a primary key value +** can not be updated using OTA. For example, if the target db contains: +** +** CREATE VIRTUAL TABLE ft1 USING fts3(a, b); +** +** then the OTA database should contain: +** +** CREATE TABLE data_ft1(a, b, ota_rowid, ota_control); +** ** For each row to INSERT into the target database as part of the OTA ** update, the corresponding data_% table should contain a single record ** with the "ota_control" column set to contain integer value 0. The @@ -119,7 +131,7 @@ ** stored in the corresponding columns of the data_% table row, as should ** the new values of all columns being update. The text value in the ** "ota_control" column must contain the same number of characters as -** there are column in the target database table, and must consist entirely +** there are columns in the target database table, and must consist entirely ** of "x" and "." characters. For each column that is being updated, ** the corresponding character is set to "x". For those that remain as ** they are, the corresponding character of the ota_control value should @@ -148,6 +160,18 @@ ** ** UPDATE t1 SET c = ota_delta(c, 'usa') WHERE a = 4; ** +** If the target database table is a virtual table, the ota_control value +** should not include a character corresponding to the ota_rowid value. +** For example, this: +** +** INSERT INTO data_ft1(a, b, ota_rowid, ota_control) +** VALUES(NULL, 'usa', 12, '..d'); +** +** causes a result similar to: +** +** UPDATE ft1 SET b = 'usa' WHERE rowid = 12; +** +** ** USAGE ** ** The API declared below allows an application to apply an OTA update @@ -156,12 +180,16 @@ ** ** 1) Opens an OTA handle using the sqlite3ota_open() function. ** -** 2) Calls the sqlite3ota_step() function one or more times on +** 2) Registers any required virtual table modules with the database +** handle returned by sqlite3ota_db(). Also, if required, register +** the ota_delta() implementation. +** +** 3) Calls the sqlite3ota_step() function one or more times on ** the new handle. Each call to sqlite3ota_step() performs a single ** b-tree operation, so thousands of calls may be required to apply ** a complete update. ** -** 3) Calls sqlite3ota_close() to close the OTA update handle. If +** 4) Calls sqlite3ota_close() to close the OTA update handle. If ** sqlite3ota_step() has been called enough times to completely ** apply the update to the target database, then it is committed ** and made visible to other database clients at this point. diff --git a/manifest b/manifest index 505e6916bc..f64d688e27 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Add\sthe\s"ota_delta()"\sfeature\sfor\sdelta-compressed\supdates. -D 2014-11-20T19:19:02.502 +C Add\ssupport\sfor\supdating\svirtual\stables\svia\sota. +D 2014-11-21T10:46:23.101 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in cf57f673d77606ab0f2d9627ca52a9ba1464146a F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -132,9 +132,10 @@ F ext/ota/ota5.test ad0799daf8923ddebffe75ae8c5504ca90b7fadb F ext/ota/ota6.test 82f1f757ec9b2ad07d6de4060b8e3ba8e44dfdd3 F ext/ota/ota7.test 1fe2c5761705374530e29f70c39693076028221a F ext/ota/ota8.test cd70e63a0c29c45c0906692827deafa34638feda +F ext/ota/ota9.test d9ad30ccb4e08f878e382876fe67752309538af9 F ext/ota/otafault.test be02466863015a583cc0ceb6aca871a5e6f7a71b -F ext/ota/sqlite3ota.c edeea10871d1307ff9ee9ccc765ba4031b507509 -F ext/ota/sqlite3ota.h 08b276fc9f56c04cdb454cf7aefa41c29361ed7a +F ext/ota/sqlite3ota.c 07ef7b72358ed422b69a10e4702ab131041e2896 +F ext/ota/sqlite3ota.h 8c09973d27fba7fa34f2d1bf0606d2bd420fe123 F ext/rtree/README 6315c0d73ebf0ec40dedb5aa0e942bc8b54e3761 F ext/rtree/rtree.c 57bec53e1a677ab74217fe1f20a58c3a47261d6b F ext/rtree/rtree.h 834dbcb82dc85b2481cde6a07cdadfddc99e9b9e @@ -1218,7 +1219,7 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 55066a1171cbd3077f5e6c8ceb2745e810d9476e -R 97efd71a02c88dd0da29f19b3d083930 +P c64dcd1788f5cc7db197a0ec4ab0981f34a72c6b +R 5a2fe82c5b487561e46e520bd6653b40 U dan -Z 1557aa5ff604851a6b4517de0e4ff6aa +Z b7e67cf183a3b3e32028fa6f7bcf265c diff --git a/manifest.uuid b/manifest.uuid index d045752a5f..2901801d1e 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -c64dcd1788f5cc7db197a0ec4ab0981f34a72c6b \ No newline at end of file +4dfcfe543945aa60a7ac397a3bdb0ac9e20ef7b6 \ No newline at end of file -- 2.47.2