/ Check-in [c64dcd17]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the "ota_delta()" feature for delta-compressed updates.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: c64dcd1788f5cc7db197a0ec4ab0981f34a72c6b
User & Date: dan 2014-11-20 19:19:02
Context
2014-11-21
10:46
Add support for updating virtual tables via ota. check-in: 4dfcfe54 user: dan tags: ota-update
2014-11-20
19:19
Add the "ota_delta()" feature for delta-compressed updates. check-in: c64dcd17 user: dan tags: ota-update
17:37
Update the ota extension so that it can be used to update tables with external PRIMARY KEY indexes. check-in: 55066a11 user: dan tags: ota-update
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/ota/ota8.test.

            1  +# 2014 November 20
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test the ota_delta() feature.
           13  +#
           14  +
           15  +if {![info exists testdir]} {
           16  +  set testdir [file join [file dirname [info script]] .. .. test]
           17  +}
           18  +source $testdir/tester.tcl
           19  +set ::testprefix ota8
           20  +
           21  +do_execsql_test 1.0 {
           22  +  CREATE TABLE t1(x, y PRIMARY KEY, z);
           23  +  INSERT INTO t1 VALUES(NULL, 1, 'one');
           24  +  INSERT INTO t1 VALUES(NULL, 2, 'two');
           25  +  INSERT INTO t1 VALUES(NULL, 3, 'three');
           26  +  CREATE INDEX i1z ON t1(z, x);
           27  +}
           28  +
           29  +do_test 1.1 {
           30  +  forcedelete ota.db
           31  +  sqlite3 db2 ota.db
           32  +  db2 eval {
           33  +    CREATE TABLE data_t1(x, y, z, ota_control);
           34  +    INSERT INTO data_t1 VALUES('a',    1, '_i'      , 'x.d');
           35  +    INSERT INTO data_t1 VALUES('b',    2, 2         , '..x');
           36  +    INSERT INTO data_t1 VALUES('_iii', 3, '-III'    , 'd.d');
           37  +  }
           38  +  db2 close
           39  +} {}
           40  +
           41  +do_test 1.2.1 {
           42  +  sqlite3ota ota test.db ota.db
           43  +  ota step
           44  +} {SQLITE_ERROR}
           45  +do_test 1.2.2 {
           46  +  list [catch {ota close} msg] $msg
           47  +} {1 {SQLITE_ERROR - no such function: ota_delta}}
           48  +
           49  +proc ota_delta {orig new} {
           50  + return "${orig}${new}"
           51  +}
           52  +
           53  +do_test 1.3.1 {
           54  +  while 1 {
           55  +    sqlite3ota ota test.db ota.db
           56  +    ota create_ota_delta
           57  +    set rc [ota step]
           58  +    if {$rc != "SQLITE_OK"} break
           59  +    ota close
           60  +  }
           61  +  ota close
           62  +} {SQLITE_DONE}
           63  +
           64  +do_execsql_test 1.3.2 {
           65  +  SELECT * FROM t1
           66  +} {
           67  +  a    1 one_i
           68  +  {}   2 2
           69  +  _iii 3 three-III
           70  +}
           71  +integrity_check 1.3.3
           72  +
           73  +
           74  +finish_test
           75  +

Changes to ext/ota/sqlite3ota.c.

   442    442         p->zErrmsg = sqlite3_mprintf("table %s has no PRIMARY KEY", pIter->zTbl);
   443    443         p->rc = SQLITE_ERROR;
   444    444       }
   445    445     }
   446    446   
   447    447     return p->rc;
   448    448   }
          449  +
          450  +/*
          451  +** This is a wrapper around "sqlite3_mprintf(zFmt, ...)". If an OOM occurs,
          452  +** an error code is stored in the OTA handle passed as the first argument.
          453  +*/
          454  +static char *otaMPrintfAndCollectError(sqlite3ota *p, const char *zFmt, ...){
          455  +  char *zSql = 0;
          456  +  va_list ap;
          457  +  va_start(ap, zFmt);
          458  +  if( p->rc==SQLITE_OK ){
          459  +    zSql = sqlite3_vmprintf(zFmt, ap);
          460  +    if( zSql==0 ) p->rc = SQLITE_NOMEM;
          461  +  }
          462  +  va_end(ap);
          463  +  return zSql;
          464  +}
   449    465   
   450    466   /*
   451    467   ** This function constructs and returns a pointer to a nul-terminated 
   452    468   ** string containing some SQL clause or list based on one or more of the 
   453    469   ** column names currently stored in the pIter->azTblCol[] array.
   454    470   **
   455    471   ** If an OOM error is encountered, NULL is returned and an error code
................................................................................
   565    581   ** stored in the (p->nCol+1)'th column. Set the error code and error message
   566    582   ** of the OTA handle to something reflecting this.
   567    583   */
   568    584   static void otaBadControlError(sqlite3ota *p){
   569    585     p->rc = SQLITE_ERROR;
   570    586     p->zErrmsg = sqlite3_mprintf("Invalid ota_control value");
   571    587   }
          588  +
   572    589   
   573    590   static char *otaObjIterGetSetlist(
   574    591     sqlite3ota *p,
   575    592     OtaObjIter *pIter,
   576    593     const char *zMask
   577    594   ){
   578    595     char *zList = 0;
................................................................................
   580    597       int i;
   581    598   
   582    599       if( strlen(zMask)!=pIter->nTblCol ){
   583    600         otaBadControlError(p);
   584    601       }else{
   585    602         const char *zSep = "";
   586    603         for(i=0; i<pIter->nTblCol; i++){
   587         -        if( zMask[i]=='x' ){
   588         -          zList = sqlite3_mprintf("%z%s%s=?%d", 
          604  +        char c = zMask[i];
          605  +        if( c=='x' ){
          606  +          zList = otaMPrintfAndCollectError(p, "%z%s%s=?%d", 
   589    607                 zList, zSep, pIter->azTblCol[i], i+1
   590    608             );
   591         -          if( zList==0 ){
   592         -            p->rc = SQLITE_NOMEM;
   593         -            break;
   594         -          }
          609  +          zSep = ", ";
          610  +        }
          611  +        if( c=='d' ){
          612  +          zList = otaMPrintfAndCollectError(p, "%z%s%s=ota_delta(%s, ?%d)", 
          613  +              zList, zSep, pIter->azTblCol[i], pIter->azTblCol[i], i+1
          614  +          );
   595    615             zSep = ", ";
   596    616           }
   597    617         }
   598    618       }
   599    619     }
   600    620     return zList;
   601    621   }
................................................................................
  1384   1404       }
  1385   1405   
  1386   1406       otaFreeState(pState);
  1387   1407     }
  1388   1408   
  1389   1409     return p;
  1390   1410   }
         1411  +
         1412  +/*
         1413  +** Return the database handle used by pOta.
         1414  +*/
         1415  +sqlite3 *sqlite3ota_db(sqlite3ota *pOta){
         1416  +  return (pOta ? pOta->db : 0);
         1417  +}
  1391   1418   
  1392   1419   /*
  1393   1420   ** Close the OTA handle.
  1394   1421   */
  1395   1422   int sqlite3ota_close(sqlite3ota *p, char **pzErrmsg){
  1396   1423     int rc;
  1397   1424     if( p ){
................................................................................
  1445   1472   
  1446   1473   #ifdef SQLITE_TEST 
  1447   1474   
  1448   1475   #include <tcl.h>
  1449   1476   
  1450   1477   /* From main.c (apparently...) */
  1451   1478   extern const char *sqlite3ErrName(int);
         1479  +
         1480  +void test_ota_delta(sqlite3_context *pCtx, int nArg, sqlite3_value **apVal){
         1481  +  Tcl_Interp *interp = (Tcl_Interp*)sqlite3_user_data(pCtx);
         1482  +  Tcl_Obj *pScript;
         1483  +  int i;
         1484  +
         1485  +  pScript = Tcl_NewObj();
         1486  +  Tcl_IncrRefCount(pScript);
         1487  +  Tcl_ListObjAppendElement(0, pScript, Tcl_NewStringObj("ota_delta", -1));
         1488  +  for(i=0; i<nArg; i++){
         1489  +    sqlite3_value *pIn = apVal[i];
         1490  +    const char *z = (const char*)sqlite3_value_text(pIn);
         1491  +    Tcl_ListObjAppendElement(0, pScript, Tcl_NewStringObj(z, -1));
         1492  +  }
         1493  +
         1494  +  if( TCL_OK==Tcl_EvalObjEx(interp, pScript, TCL_GLOBAL_ONLY) ){
         1495  +    const char *z = Tcl_GetStringResult(interp);
         1496  +    sqlite3_result_text(pCtx, z, -1, SQLITE_TRANSIENT);
         1497  +  }else{
         1498  +    Tcl_BackgroundError(interp);
         1499  +  }
         1500  +
         1501  +  Tcl_DecrRefCount(pScript);
         1502  +}
         1503  +
  1452   1504   
  1453   1505   static int test_sqlite3ota_cmd(
  1454   1506     ClientData clientData,
  1455   1507     Tcl_Interp *interp,
  1456   1508     int objc,
  1457   1509     Tcl_Obj *CONST objv[]
  1458   1510   ){
  1459   1511     int ret = TCL_OK;
  1460   1512     sqlite3ota *pOta = (sqlite3ota*)clientData;
  1461         -  const char *azMethod[] = { "step", "close", 0 };
         1513  +  const char *azMethod[] = { "step", "close", "create_ota_delta", 0 };
  1462   1514     int iMethod;
  1463   1515   
  1464   1516     if( objc!=2 ){
  1465   1517       Tcl_WrongNumArgs(interp, 1, objv, "METHOD");
  1466   1518       return TCL_ERROR;
  1467   1519     }
  1468   1520     if( Tcl_GetIndexFromObj(interp, objv[1], azMethod, "method", 0, &iMethod) ){
................................................................................
  1490   1542             Tcl_AppendResult(interp, " - ", zErrmsg, 0);
  1491   1543             sqlite3_free(zErrmsg);
  1492   1544           }
  1493   1545           ret = TCL_ERROR;
  1494   1546         }
  1495   1547         break;
  1496   1548       }
         1549  +
         1550  +    case 2: /* create_ota_delta */ {
         1551  +      sqlite3 *db = sqlite3ota_db(pOta);
         1552  +      int rc = sqlite3_create_function(
         1553  +          db, "ota_delta", -1, SQLITE_UTF8, (void*)interp, test_ota_delta, 0, 0
         1554  +      );
         1555  +      Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
         1556  +      ret = (rc==SQLITE_OK ? TCL_OK : TCL_ERROR);
         1557  +      break;
         1558  +    }
  1497   1559   
  1498   1560       default: /* seems unlikely */
  1499   1561         assert( !"cannot happen" );
  1500   1562         break;
  1501   1563     }
  1502   1564   
  1503   1565     return ret;

Changes to ext/ota/sqlite3ota.h.

   128    128   **
   129    129   **   UPDATE t1 SET c = 'usa' WHERE a = 4;
   130    130   **
   131    131   ** is represented by the data_t1 row created by:
   132    132   **
   133    133   **   INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..x');
   134    134   **
          135  +** Instead of an 'x' character, characters of the ota_control value specified
          136  +** for UPDATEs may also be set to 'd'. In this case, instead of updating the
          137  +** target table with the value stored in the corresponding data_% column, the
          138  +** user-defined SQL function "ota_delta()" is invoked and the result stored in
          139  +** the target table column. ota_delta() is invoked with two arguments - the
          140  +** original value currently stored in the target table column and the 
          141  +** value specified in the data_xxx table.
          142  +**
          143  +** For example, this row:
          144  +**
          145  +**   INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..d');
          146  +**
          147  +** is similar to an UPDATE statement such as: 
          148  +**
          149  +**   UPDATE t1 SET c = ota_delta(c, 'usa') WHERE a = 4;
   135    150   **
   136    151   ** USAGE
   137    152   **
   138    153   ** The API declared below allows an application to apply an OTA update 
   139    154   ** stored on disk to an existing target database. Essentially, the 
   140    155   ** application:
   141    156   **
................................................................................
   177    192   **
   178    193   ** Argument zTarget is the path to the target database. Argument zOta is
   179    194   ** the path to the OTA database. Each call to this function must be matched
   180    195   ** by a call to sqlite3ota_close().
   181    196   */
   182    197   sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta);
   183    198   
          199  +/*
          200  +** Obtain the underlying database handle used by the OTA extension.
          201  +**
          202  +** The only argument passed to this function must be a valid, open, OTA
          203  +** handle. This function returns the database handle used by OTA for all
          204  +** operations on the target and source databases. This may be useful in 
          205  +** two scenarios:
          206  +**
          207  +**   * If the data_xxx tables in the OTA source database are virtual 
          208  +**     tables, or if any of the tables being updated are virtual tables,
          209  +**     the application may need to call sqlite3_create_module() on
          210  +**     the db handle to register the required virtual table implementations.
          211  +**
          212  +**   * If the application uses the "ota_delta()" feature described above,
          213  +**     it must use sqlite3_create_function() or similar to register the
          214  +**     ota_delta() implementation with OTA.
          215  +*/
          216  +sqlite3 *sqlite3ota_db(sqlite3ota*);
          217  +
   184    218   /*
   185    219   ** Do some work towards applying the OTA update to the target db. 
   186    220   **
   187    221   ** Return SQLITE_DONE if the update has been completely applied, or 
   188    222   ** SQLITE_OK if no error occurs but there remains work to do to apply
   189    223   ** the OTA update. If an error does occur, some other error code is 
   190    224   ** returned.