SQLite

Check-in [c64dcd1788]
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
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: c64dcd1788f5cc7db197a0ec4ab0981f34a72c6b
User & Date: dan 2014-11-20 19:19:02.502
Context
2014-11-21
10:46
Add support for updating virtual tables via ota. (check-in: 4dfcfe5439 user: dan tags: ota-update)
2014-11-20
19:19
Add the "ota_delta()" feature for delta-compressed updates. (check-in: c64dcd1788 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: 55066a1171 user: dan tags: ota-update)
Changes
Unified Diff Ignore Whitespace Patch
Added ext/ota/ota8.test.






















































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
# 2014 November 20
#
# 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 the ota_delta() feature.
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
set ::testprefix ota8

do_execsql_test 1.0 {
  CREATE TABLE t1(x, y PRIMARY KEY, z);
  INSERT INTO t1 VALUES(NULL, 1, 'one');
  INSERT INTO t1 VALUES(NULL, 2, 'two');
  INSERT INTO t1 VALUES(NULL, 3, 'three');
  CREATE INDEX i1z ON t1(z, x);
}

do_test 1.1 {
  forcedelete ota.db
  sqlite3 db2 ota.db
  db2 eval {
    CREATE TABLE data_t1(x, y, z, ota_control);
    INSERT INTO data_t1 VALUES('a',    1, '_i'      , 'x.d');
    INSERT INTO data_t1 VALUES('b',    2, 2         , '..x');
    INSERT INTO data_t1 VALUES('_iii', 3, '-III'    , 'd.d');
  }
  db2 close
} {}

do_test 1.2.1 {
  sqlite3ota ota test.db ota.db
  ota step
} {SQLITE_ERROR}
do_test 1.2.2 {
  list [catch {ota close} msg] $msg
} {1 {SQLITE_ERROR - no such function: ota_delta}}

proc ota_delta {orig new} {
 return "${orig}${new}"
}

do_test 1.3.1 {
  while 1 {
    sqlite3ota ota test.db ota.db
    ota create_ota_delta
    set rc [ota step]
    if {$rc != "SQLITE_OK"} break
    ota close
  }
  ota close
} {SQLITE_DONE}

do_execsql_test 1.3.2 {
  SELECT * FROM t1
} {
  a    1 one_i
  {}   2 2
  _iii 3 three-III
}
integrity_check 1.3.3


finish_test

Changes to ext/ota/sqlite3ota.c.
442
443
444
445
446
447
448
















449
450
451
452
453
454
455
      p->zErrmsg = sqlite3_mprintf("table %s has no PRIMARY KEY", pIter->zTbl);
      p->rc = SQLITE_ERROR;
    }
  }

  return p->rc;
}

















/*
** This function constructs and returns a pointer to a nul-terminated 
** string containing some SQL clause or list based on one or more of the 
** column names currently stored in the pIter->azTblCol[] array.
**
** If an OOM error is encountered, NULL is returned and an error code







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







442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
      p->zErrmsg = sqlite3_mprintf("table %s has no PRIMARY KEY", pIter->zTbl);
      p->rc = SQLITE_ERROR;
    }
  }

  return p->rc;
}

/*
** This is a wrapper around "sqlite3_mprintf(zFmt, ...)". If an OOM occurs,
** an error code is stored in the OTA handle passed as the first argument.
*/
static char *otaMPrintfAndCollectError(sqlite3ota *p, const char *zFmt, ...){
  char *zSql = 0;
  va_list ap;
  va_start(ap, zFmt);
  if( p->rc==SQLITE_OK ){
    zSql = sqlite3_vmprintf(zFmt, ap);
    if( zSql==0 ) p->rc = SQLITE_NOMEM;
  }
  va_end(ap);
  return zSql;
}

/*
** This function constructs and returns a pointer to a nul-terminated 
** string containing some SQL clause or list based on one or more of the 
** column names currently stored in the pIter->azTblCol[] array.
**
** If an OOM error is encountered, NULL is returned and an error code
565
566
567
568
569
570
571

572
573
574
575
576
577
578
579
580
581
582
583
584
585
586

587
588
589
590
591
592
593
594




595
596
597
598
599
600
601
** stored in the (p->nCol+1)'th column. Set the error code and error message
** of the OTA handle to something reflecting this.
*/
static void otaBadControlError(sqlite3ota *p){
  p->rc = SQLITE_ERROR;
  p->zErrmsg = sqlite3_mprintf("Invalid ota_control value");
}


static char *otaObjIterGetSetlist(
  sqlite3ota *p,
  OtaObjIter *pIter,
  const char *zMask
){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    int i;

    if( strlen(zMask)!=pIter->nTblCol ){
      otaBadControlError(p);
    }else{
      const char *zSep = "";
      for(i=0; i<pIter->nTblCol; i++){

        if( zMask[i]=='x' ){
          zList = sqlite3_mprintf("%z%s%s=?%d", 
              zList, zSep, pIter->azTblCol[i], i+1
          );
          if( zList==0 ){
            p->rc = SQLITE_NOMEM;
            break;
          }




          zSep = ", ";
        }
      }
    }
  }
  return zList;
}







>















>
|
|


<
|
<
|
>
>
>
>







581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608

609

610
611
612
613
614
615
616
617
618
619
620
621
** stored in the (p->nCol+1)'th column. Set the error code and error message
** of the OTA handle to something reflecting this.
*/
static void otaBadControlError(sqlite3ota *p){
  p->rc = SQLITE_ERROR;
  p->zErrmsg = sqlite3_mprintf("Invalid ota_control value");
}


static char *otaObjIterGetSetlist(
  sqlite3ota *p,
  OtaObjIter *pIter,
  const char *zMask
){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    int i;

    if( strlen(zMask)!=pIter->nTblCol ){
      otaBadControlError(p);
    }else{
      const char *zSep = "";
      for(i=0; i<pIter->nTblCol; i++){
        char c = zMask[i];
        if( c=='x' ){
          zList = otaMPrintfAndCollectError(p, "%z%s%s=?%d", 
              zList, zSep, pIter->azTblCol[i], i+1
          );

          zSep = ", ";

        }
        if( c=='d' ){
          zList = otaMPrintfAndCollectError(p, "%z%s%s=ota_delta(%s, ?%d)", 
              zList, zSep, pIter->azTblCol[i], pIter->azTblCol[i], i+1
          );
          zSep = ", ";
        }
      }
    }
  }
  return zList;
}
1384
1385
1386
1387
1388
1389
1390







1391
1392
1393
1394
1395
1396
1397
    }

    otaFreeState(pState);
  }

  return p;
}








/*
** Close the OTA handle.
*/
int sqlite3ota_close(sqlite3ota *p, char **pzErrmsg){
  int rc;
  if( p ){







>
>
>
>
>
>
>







1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
    }

    otaFreeState(pState);
  }

  return p;
}

/*
** Return the database handle used by pOta.
*/
sqlite3 *sqlite3ota_db(sqlite3ota *pOta){
  return (pOta ? pOta->db : 0);
}

/*
** Close the OTA handle.
*/
int sqlite3ota_close(sqlite3ota *p, char **pzErrmsg){
  int rc;
  if( p ){
1445
1446
1447
1448
1449
1450
1451

























1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468

#ifdef SQLITE_TEST 

#include <tcl.h>

/* From main.c (apparently...) */
extern const char *sqlite3ErrName(int);


























static int test_sqlite3ota_cmd(
  ClientData clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int ret = TCL_OK;
  sqlite3ota *pOta = (sqlite3ota*)clientData;
  const char *azMethod[] = { "step", "close", 0 };
  int iMethod;

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "METHOD");
    return TCL_ERROR;
  }
  if( Tcl_GetIndexFromObj(interp, objv[1], azMethod, "method", 0, &iMethod) ){







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









|







1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520

#ifdef SQLITE_TEST 

#include <tcl.h>

/* From main.c (apparently...) */
extern const char *sqlite3ErrName(int);

void test_ota_delta(sqlite3_context *pCtx, int nArg, sqlite3_value **apVal){
  Tcl_Interp *interp = (Tcl_Interp*)sqlite3_user_data(pCtx);
  Tcl_Obj *pScript;
  int i;

  pScript = Tcl_NewObj();
  Tcl_IncrRefCount(pScript);
  Tcl_ListObjAppendElement(0, pScript, Tcl_NewStringObj("ota_delta", -1));
  for(i=0; i<nArg; i++){
    sqlite3_value *pIn = apVal[i];
    const char *z = (const char*)sqlite3_value_text(pIn);
    Tcl_ListObjAppendElement(0, pScript, Tcl_NewStringObj(z, -1));
  }

  if( TCL_OK==Tcl_EvalObjEx(interp, pScript, TCL_GLOBAL_ONLY) ){
    const char *z = Tcl_GetStringResult(interp);
    sqlite3_result_text(pCtx, z, -1, SQLITE_TRANSIENT);
  }else{
    Tcl_BackgroundError(interp);
  }

  Tcl_DecrRefCount(pScript);
}


static int test_sqlite3ota_cmd(
  ClientData clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int ret = TCL_OK;
  sqlite3ota *pOta = (sqlite3ota*)clientData;
  const char *azMethod[] = { "step", "close", "create_ota_delta", 0 };
  int iMethod;

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "METHOD");
    return TCL_ERROR;
  }
  if( Tcl_GetIndexFromObj(interp, objv[1], azMethod, "method", 0, &iMethod) ){
1490
1491
1492
1493
1494
1495
1496










1497
1498
1499
1500
1501
1502
1503
          Tcl_AppendResult(interp, " - ", zErrmsg, 0);
          sqlite3_free(zErrmsg);
        }
        ret = TCL_ERROR;
      }
      break;
    }











    default: /* seems unlikely */
      assert( !"cannot happen" );
      break;
  }

  return ret;







>
>
>
>
>
>
>
>
>
>







1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
          Tcl_AppendResult(interp, " - ", zErrmsg, 0);
          sqlite3_free(zErrmsg);
        }
        ret = TCL_ERROR;
      }
      break;
    }

    case 2: /* create_ota_delta */ {
      sqlite3 *db = sqlite3ota_db(pOta);
      int rc = sqlite3_create_function(
          db, "ota_delta", -1, SQLITE_UTF8, (void*)interp, test_ota_delta, 0, 0
      );
      Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
      ret = (rc==SQLITE_OK ? TCL_OK : TCL_ERROR);
      break;
    }

    default: /* seems unlikely */
      assert( !"cannot happen" );
      break;
  }

  return ret;
Changes to ext/ota/sqlite3ota.h.
128
129
130
131
132
133
134















135
136
137
138
139
140
141
**
**   UPDATE t1 SET c = 'usa' WHERE a = 4;
**
** is represented by the data_t1 row created by:
**
**   INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..x');
**















**
** USAGE
**
** The API declared below allows an application to apply an OTA update 
** stored on disk to an existing target database. Essentially, the 
** application:
**







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







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
**
**   UPDATE t1 SET c = 'usa' WHERE a = 4;
**
** is represented by the data_t1 row created by:
**
**   INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..x');
**
** Instead of an 'x' character, characters of the ota_control value specified
** for UPDATEs may also be set to 'd'. In this case, instead of updating the
** target table with the value stored in the corresponding data_% column, the
** user-defined SQL function "ota_delta()" is invoked and the result stored in
** the target table column. ota_delta() is invoked with two arguments - the
** original value currently stored in the target table column and the 
** value specified in the data_xxx table.
**
** For example, this row:
**
**   INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..d');
**
** is similar to an UPDATE statement such as: 
**
**   UPDATE t1 SET c = ota_delta(c, 'usa') WHERE a = 4;
**
** USAGE
**
** The API declared below allows an application to apply an OTA update 
** stored on disk to an existing target database. Essentially, the 
** application:
**
177
178
179
180
181
182
183



















184
185
186
187
188
189
190
**
** Argument zTarget is the path to the target database. Argument zOta is
** the path to the OTA database. Each call to this function must be matched
** by a call to sqlite3ota_close().
*/
sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta);




















/*
** Do some work towards applying the OTA update to the target db. 
**
** Return SQLITE_DONE if the update has been completely applied, or 
** SQLITE_OK if no error occurs but there remains work to do to apply
** the OTA update. If an error does occur, some other error code is 
** returned. 







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







192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
**
** Argument zTarget is the path to the target database. Argument zOta is
** the path to the OTA database. Each call to this function must be matched
** by a call to sqlite3ota_close().
*/
sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta);

/*
** Obtain the underlying database handle used by the OTA extension.
**
** The only argument passed to this function must be a valid, open, OTA
** handle. This function returns the database handle used by OTA for all
** operations on the target and source databases. This may be useful in 
** two scenarios:
**
**   * If the data_xxx tables in the OTA source database are virtual 
**     tables, or if any of the tables being updated are virtual tables,
**     the application may need to call sqlite3_create_module() on
**     the db handle to register the required virtual table implementations.
**
**   * If the application uses the "ota_delta()" feature described above,
**     it must use sqlite3_create_function() or similar to register the
**     ota_delta() implementation with OTA.
*/
sqlite3 *sqlite3ota_db(sqlite3ota*);

/*
** Do some work towards applying the OTA update to the target db. 
**
** Return SQLITE_DONE if the update has been completely applied, or 
** SQLITE_OK if no error occurs but there remains work to do to apply
** the OTA update. If an error does occur, some other error code is 
** returned.