/ Check-in [4dfcfe54]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add support for updating virtual tables via ota.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: 4dfcfe543945aa60a7ac397a3bdb0ac9e20ef7b6
User & Date: dan 2014-11-21 10:46:23
Context
2014-11-21
11:22
Changes to comments in sqlite3ota.h. check-in: 14139542 user: dan tags: ota-update
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added ext/ota/ota9.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
# 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

Changes to ext/ota/sqlite3ota.c.

99
100
101
102
103
104
105

106
107
108
109
110
111
112
...
223
224
225
226
227
228
229

230
231
232
233
234
235
236
...
386
387
388
389
390
391
392













































393
394
395
396
397
398
399
...
404
405
406
407
408
409
410
411

412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
...
435
436
437
438
439
440
441




442
443

444
445
446
447
448
449
450
...
553
554
555
556
557
558
559



560
561
562
563
564

565

566
567
568
569
570
571
572
573
574
575
576
...
659
660
661
662
663
664
665


666
667
668
669
670
671
672
...
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717

718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739



740
741

742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774


775
776
777
778
779
780
781
782
783
...
997
998
999
1000
1001
1002
1003

1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022










1023
1024
1025

1026
1027
1028
1029
1030
1031
1032




1033
1034
1035
1036
1037
1038
1039
....
1074
1075
1076
1077
1078
1079
1080

1081

1082
1083
1084
1085
1086
1087
1088
struct OtaObjIter {
  sqlite3_stmt *pTblIter;         /* Iterate through tables */
  sqlite3_stmt *pIdxIter;         /* Index iterator */
  int nTblCol;                    /* Size of azTblCol[] array */
  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 */


  /* 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 iVisit;                     /* Number of points visited, incl. current */

................................................................................
  sqlite3_free(pIter->abTblPk);
  pIter->azTblCol = 0;
  pIter->abTblPk = 0;
  pIter->nTblCol = 0;
  sqlite3_free(pIter->zMask);
  pIter->zMask = 0;
  pIter->bRowid = 0;

}

/*
** Finalize all statements and free all allocations that are specific to
** the current object (table/index pair).
*/
static void otaObjIterClearStatements(OtaObjIter *pIter){
................................................................................
      p->rc = sqlite3_exec(p->db, zSql, 0, 0, &p->zErrmsg);
      sqlite3_free(zSql);
    }
  }
  va_end(ap);
  return p->rc;
}














































/*
** If they are not already populated, populate the pIter->azTblCol[],
** pIter->abTblPk[], pIter->nTblCol and pIter->bRowid variables according to
** the table that the iterator currently points to.
**
** Return SQLITE_OK if successful, or an SQLite error code otherwise. If
................................................................................
  if( pIter->azTblCol==0 ){
    sqlite3_stmt *pStmt;
    char *zSql;
    int nCol = 0;
    int bSeenPk = 0;
    int rc2;                      /* sqlite3_finalize() return value */

    assert( pIter->bRowid==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;
      }

      if( p->rc==SQLITE_OK ){
        const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
        int iPk = sqlite3_column_int(pStmt, 5);
        pIter->abTblPk[nCol] = (iPk!=0);
        if( iPk ) bSeenPk = 1;
        if( iPk<0 ) pIter->bRowid = 1;
        pIter->azTblCol[nCol] = otaQuoteName(zName);
................................................................................
      }
    }
    pIter->nTblCol = nCol;
    rc2 = sqlite3_finalize(pStmt);
    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;

    }
  }

  return p->rc;
}

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

static char *otaObjIterGetWhere(
  sqlite3ota *p, 
  OtaObjIter *pIter
){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){



    const char *zSep = "";
    int i;
    for(i=0; i<pIter->nTblCol; 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;
        }
      }
    }
  }
  return zList;
}

................................................................................
      zLimit = sqlite3_mprintf(" LIMIT -1 OFFSET %d", nOffset);
      if( !zLimit ) p->rc = SQLITE_NOMEM;
    }

    if( zIdx ){
      int *aiCol;                 /* Column map */
      const char **azColl;        /* Collation sequences */



      /* Create the index writers */
      if( p->rc==SQLITE_OK ){
        p->rc = sqlite3_index_writer(
            p->db, 0, zIdx, &pIter->pInsert, &azColl, &aiCol, &pIter->nCol
        );
      }
................................................................................
              zCollist, pIter->zTbl, 
              zCollist, zLimit
          );
        }
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz, zSql);
      }
    }else{
      const char *zOtaRowid = (pIter->bRowid ? ", ota_rowid" : "");
      char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol);
      char *zWhere = otaObjIterGetWhere(p, pIter);
      char *zOldlist = otaObjIterGetOldlist(p, pIter, "old");
      char *zNewlist = otaObjIterGetOldlist(p, pIter, "new");
      zCollist = otaObjIterGetCollist(p, pIter, pIter->nTblCol, 0, 0);
      pIter->nCol = pIter->nTblCol;

      /* Create the SELECT statement to read keys from data_xxx */
      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)

        );
      }

      /* Create the INSERT statement to write to the target PK b-tree */
      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
            )
        );
      }

      /* Create the DELETE statement to write to the target PK b-tree */
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pDelete, pz,
            sqlite3_mprintf(
              "DELETE FROM main.%Q WHERE %s", pIter->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 ){
        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;"
            , pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zNewlist
        );
      }





      /* Allocate space required for the zMask field. */
      if( p->rc==SQLITE_OK ){
        int nMask = pIter->nTblCol+1;
        pIter->zMask = (char*)sqlite3_malloc(nMask);
        if( pIter->zMask==0 ){
          p->rc = SQLITE_NOMEM;
................................................................................
    }
    else if( 
        eType==OTA_INSERT 
     || eType==OTA_DELETE
     || eType==OTA_IDX_DELETE 
     || eType==OTA_IDX_INSERT
    ){

      sqlite3_stmt *pWriter;

      assert( eType!=OTA_UPDATE );
      assert( eType!=OTA_DELETE || pIter->zIdx==0 );

      if( eType==OTA_IDX_DELETE || eType==OTA_DELETE ){
        pWriter = pIter->pDelete;
      }else{
        pWriter = pIter->pInsert;
      }

      for(i=0; i<pIter->nCol; 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);
      }










      sqlite3_step(pWriter);
      p->rc = resetAndCollectError(pWriter, &p->zErrmsg);
    }else if( eType==OTA_UPDATE ){

      sqlite3_stmt *pUpdate = 0;
      otaGetUpdateStmt(p, pIter, zMask, &pUpdate);
      if( pUpdate ){
        for(i=0; i<pIter->nCol; i++){
          sqlite3_value *pVal = sqlite3_column_value(pIter->pSelect, i);
          sqlite3_bind_value(pUpdate, i+1, pVal);
        }




        sqlite3_step(pUpdate);
        p->rc = resetAndCollectError(pUpdate, &p->zErrmsg);
      }
    }else{
      /* no-op */
      assert( eType==OTA_DELETE && pIter->zIdx );
    }
................................................................................
        OtaObjIter *pIter = &p->objiter;
        while( p && p->rc==SQLITE_OK && pIter->zTbl ){

          if( pIter->bCleanup ){
            /* 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);

          }else{
            otaObjIterPrepareAll(p, pIter, 0);

            /* Advance to the next row to process. */
            if( p->rc==SQLITE_OK ){
              int rc = sqlite3_step(pIter->pSelect);
              if( rc==SQLITE_ROW ){







>







 







>







 







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







 







|
>



|
<
<
<
<
<
<
<
<
<
<







 







>
>
>
>
|
|
>







 







>
>
>
|
|
|
|
|
>
|
>
|
<
<
<







 







>
>







 







|
|










|
|
>







|
|








|




>
>
>
|
|
>
|
|

|
|
|
|

|
|
|
|

|
|
|
|
|
<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
|
<







 







>












<






>
>
>
>
>
>
>
>
>
>



>




|


>
>
>
>







 







>
|
>







99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
...
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
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
438
439
440
441
442
443
444
445
446
...
451
452
453
454
455
456
457
458
459
460
461
462
463










464
465
466
467
468
469
470
...
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
...
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
...
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
...
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811

812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828

829
830
831
832
833
834
835
....
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068

1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
....
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
struct OtaObjIter {
  sqlite3_stmt *pTblIter;         /* Iterate through tables */
  sqlite3_stmt *pIdxIter;         /* Index iterator */
  int nTblCol;                    /* Size of azTblCol[] array */
  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 */
  const char *zTbl;               /* Name of target db table */
  const char *zIdx;               /* Name of target db index (or null) */
  int iVisit;                     /* Number of points visited, incl. current */

................................................................................
  sqlite3_free(pIter->abTblPk);
  pIter->azTblCol = 0;
  pIter->abTblPk = 0;
  pIter->nTblCol = 0;
  sqlite3_free(pIter->zMask);
  pIter->zMask = 0;
  pIter->bRowid = 0;
  pIter->bVtab = 0;
}

/*
** Finalize all statements and free all allocations that are specific to
** the current object (table/index pair).
*/
static void otaObjIterClearStatements(OtaObjIter *pIter){
................................................................................
      p->rc = sqlite3_exec(p->db, zSql, 0, 0, &p->zErrmsg);
      sqlite3_free(zSql);
    }
  }
  va_end(ap);
  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
** the table that the iterator currently points to.
**
** Return SQLITE_OK if successful, or an SQLite error code otherwise. If
................................................................................
  if( pIter->azTblCol==0 ){
    sqlite3_stmt *pStmt;
    char *zSql;
    int nCol = 0;
    int bSeenPk = 0;
    int rc2;                      /* sqlite3_finalize() return value */

    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) ){
      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);
        pIter->abTblPk[nCol] = (iPk!=0);
        if( iPk ) bSeenPk = 1;
        if( iPk<0 ) pIter->bRowid = 1;
        pIter->azTblCol[nCol] = otaQuoteName(zName);
................................................................................
      }
    }
    pIter->nTblCol = nCol;
    rc2 = sqlite3_finalize(pStmt);
    if( p->rc==SQLITE_OK ) p->rc = rc2;

    if( p->rc==SQLITE_OK && bSeenPk==0 ){
      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;
      }
    }
  }

  return p->rc;
}

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

static char *otaObjIterGetWhere(
  sqlite3ota *p, 
  OtaObjIter *pIter
){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    if( pIter->bVtab ){
      zList = otaMPrintfAndCollectError(p, "rowid = ?%d", pIter->nTblCol+1);
    }else{
      const char *zSep = "";
      int i;
      for(i=0; i<pIter->nTblCol; 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 ";



        }
      }
    }
  }
  return zList;
}

................................................................................
      zLimit = sqlite3_mprintf(" LIMIT -1 OFFSET %d", nOffset);
      if( !zLimit ) p->rc = SQLITE_NOMEM;
    }

    if( zIdx ){
      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(
            p->db, 0, zIdx, &pIter->pInsert, &azColl, &aiCol, &pIter->nCol
        );
      }
................................................................................
              zCollist, pIter->zTbl, 
              zCollist, zLimit
          );
        }
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz, zSql);
      }
    }else{
      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");
      zCollist = otaObjIterGetCollist(p, pIter, pIter->nTblCol, 0, 0);
      pIter->nCol = pIter->nTblCol;

      /* Create the SELECT statement to read keys from data_xxx */
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz,
            sqlite3_mprintf(
              "SELECT %s, ota_control%s FROM ota.'data_%q'%s", 
              zCollist, (pIter->bVtab ? ", ota_rowid" : ""), zTbl, zLimit
            )
        );
      }

      /* Create the INSERT statement to write to the target PK b-tree */
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pInsert, pz,
            sqlite3_mprintf(
              "INSERT INTO main.%Q(%s%s) VALUES(%s)", 
              zTbl, zCollist, (pIter->bVtab ? ", rowid" : ""), zBindings
            )
        );
      }

      /* Create the DELETE statement to write to the target PK b-tree */
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pDelete, pz,
            sqlite3_mprintf(
              "DELETE FROM main.%Q WHERE %s", zTbl, zWhere
            )
        );
      }

      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, 
            "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%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;"
            , 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 ){
        int nMask = pIter->nTblCol+1;
        pIter->zMask = (char*)sqlite3_malloc(nMask);
        if( pIter->zMask==0 ){
          p->rc = SQLITE_NOMEM;
................................................................................
    }
    else if( 
        eType==OTA_INSERT 
     || eType==OTA_DELETE
     || eType==OTA_IDX_DELETE 
     || eType==OTA_IDX_INSERT
    ){
      sqlite3_value *pVal;
      sqlite3_stmt *pWriter;

      assert( eType!=OTA_UPDATE );
      assert( eType!=OTA_DELETE || pIter->zIdx==0 );

      if( eType==OTA_IDX_DELETE || eType==OTA_DELETE ){
        pWriter = pIter->pDelete;
      }else{
        pWriter = pIter->pInsert;
      }

      for(i=0; i<pIter->nCol; i++){

        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 <cols>, 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; i<pIter->nCol; 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);
      }
    }else{
      /* no-op */
      assert( eType==OTA_DELETE && pIter->zIdx );
    }
................................................................................
        OtaObjIter *pIter = &p->objiter;
        while( p && p->rc==SQLITE_OK && pIter->zTbl ){

          if( pIter->bCleanup ){
            /* 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.  */
            if( pIter->bVtab==0 ){
              otaMPrintfExec(p, "DELETE FROM ota.'ota_tmp_%q'", pIter->zTbl);
            }
          }else{
            otaObjIterPrepareAll(p, pIter, 0);

            /* Advance to the next row to process. */
            if( p->rc==SQLITE_OK ){
              int rc = sqlite3_step(pIter->pSelect);
              if( rc==SQLITE_ROW ){

Changes to ext/ota/sqlite3ota.h.

89
90
91
92
93
94
95












96
97
98
99
100
101
102
...
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
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
**   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE);
**
** Then the OTA database should contain:
**
**   CREATE TABLE data_t1(a INTEGER, b TEXT, c, ota_control);
**
** The order of the columns in the data_% table does not matter.












**
** 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
** other columns should be set to the values that make up the new record 
** to insert. 
**
................................................................................
** For each row to UPDATE from 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 a value of type text.
** The real primary key values identifying the row to update should be 
** 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
** 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
** be set to ".". For example, given the tables above, the update 
** statement:
**
**   UPDATE t1 SET c = 'usa' WHERE a = 4;
................................................................................
** 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:
**
**     1) Opens an OTA handle using the sqlite3ota_open() function.
**




**     2) 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
**        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. 
**        Otherwise, the state of the OTA update application is saved
**        in the OTA database for later resumption.
**
** See comments below for more detail on APIs.







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







 







|







 







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









>
>
>
>
|




|







89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
...
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
**   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE);
**
** Then the OTA database should contain:
**
**   CREATE TABLE data_t1(a INTEGER, b TEXT, c, ota_control);
**
** 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
** other columns should be set to the values that make up the new record 
** to insert. 
**
................................................................................
** For each row to UPDATE from 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 a value of type text.
** The real primary key values identifying the row to update should be 
** 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 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
** be set to ".". For example, given the tables above, the update 
** statement:
**
**   UPDATE t1 SET c = 'usa' WHERE a = 4;
................................................................................
** 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;
**
** 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 
** stored on disk to an existing target database. Essentially, the 
** application:
**
**     1) Opens an OTA handle using the sqlite3ota_open() function.
**
**     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.
**
**     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. 
**        Otherwise, the state of the OTA update application is saved
**        in the OTA database for later resumption.
**
** See comments below for more detail on APIs.