/ Check-in [e109b27e]
Login

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

Overview
Comment:Add support for update statements to sqlite3ota.c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1:e109b27e4d66b83e1a804e7556d9c91aa37fea28
User & Date: dan 2014-09-08 17:50:35
Context
2014-09-15
10:44
Add OP_Affinity opcodes to the VMs generated by sqlite3_index_writer(). check-in: b9b38cb8 user: dan tags: ota-update
2014-09-08
17:50
Add support for update statements to sqlite3ota.c. check-in: e109b27e user: dan tags: ota-update
2014-09-06
20:19
Add support for delete operations to the ota extension. check-in: f988234b user: dan tags: ota-update
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/ota/ota1.test.

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
...
220
221
222
223
224
225
226

227








































228
229
  return $filename
}

# Create a simple OTA database. That expects to write to a table:
#
#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
#


proc create_ota4 {filename} {
  forcedelete $filename
  sqlite3 ota1 $filename  
  ota1 eval {
    CREATE TABLE data_t1(a, b, c, ota_control);
    INSERT INTO data_t1 VALUES(1, 2, 3, 0);
    INSERT INTO data_t1 VALUES(2, NULL, 5, 1);
    INSERT INTO data_t1 VALUES(3, 8, 9, 0);
    INSERT INTO data_t1 VALUES(4, NULL, 11, 1);
  }
  ota1 close
  return $filename
}




















# Run the OTA in file $ota on target database $target until completion.
#
proc run_ota {target ota} {
  sqlite3ota ota $target $ota
  while { [ota step]=="SQLITE_OK" } {}
  ota close
................................................................................
      6 hello xyz
    }
  
    do_execsql_test 4.$tn2.$tn.3 { PRAGMA integrity_check } ok
  }
}











































finish_test








>
>













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







 







>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


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
76
77
78
79
...
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
  return $filename
}

# Create a simple OTA database. That expects to write to a table:
#
#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
#
# This OTA includes both insert and delete operations.
#
proc create_ota4 {filename} {
  forcedelete $filename
  sqlite3 ota1 $filename  
  ota1 eval {
    CREATE TABLE data_t1(a, b, c, ota_control);
    INSERT INTO data_t1 VALUES(1, 2, 3, 0);
    INSERT INTO data_t1 VALUES(2, NULL, 5, 1);
    INSERT INTO data_t1 VALUES(3, 8, 9, 0);
    INSERT INTO data_t1 VALUES(4, NULL, 11, 1);
  }
  ota1 close
  return $filename
}

# Create a simple OTA database. That expects to write to a table:
#
#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
#
# This OTA includes update statements.
#
proc create_ota5 {filename} {
  forcedelete $filename
  sqlite3 ota5 $filename  
  ota5 eval {
    CREATE TABLE data_t1(a, b, c, d, ota_control);
    INSERT INTO data_t1 VALUES(1, NULL, NULL, 5, '...x');  -- SET d = 5
    INSERT INTO data_t1 VALUES(2, NULL, 10, 5, '..xx');    -- SET c=10, d = 5
    INSERT INTO data_t1 VALUES(3, 11, NULL, NULL, '.x..'); -- SET b=11
  }
  ota5 close
  return $filename
}

# Run the OTA in file $ota on target database $target until completion.
#
proc run_ota {target ota} {
  sqlite3ota ota $target $ota
  while { [ota step]=="SQLITE_OK" } {}
  ota close
................................................................................
      6 hello xyz
    }
  
    do_execsql_test 4.$tn2.$tn.3 { PRAGMA integrity_check } ok
  }
}

#-------------------------------------------------------------------------
#
foreach {tn2 cmd} {1 run_ota 2 step_ota} {
  foreach {tn schema} {
    1 {
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
    }
    2 {
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
      CREATE INDEX i1 ON t1(d);
      CREATE INDEX i2 ON t1(d, c);
      CREATE INDEX i3 ON t1(d, c, b);
      CREATE INDEX i4 ON t1(b);
      CREATE INDEX i5 ON t1(c);
      CREATE INDEX i6 ON t1(c, b);
    }
  } {
    reset_db
    execsql $schema
    execsql {
      INSERT INTO t1 VALUES(1, 2, 3, 4);
      INSERT INTO t1 VALUES(2, 5, 6, 7);
      INSERT INTO t1 VALUES(3, 8, 9, 10);
    }
  
    do_test 5.$tn2.$tn.1 {
      create_ota5 ota.db
      $cmd test.db ota.db
    } {SQLITE_DONE}
    
    do_execsql_test 5.$tn2.$tn.2 {
      SELECT * FROM t1 ORDER BY a ASC;
    } {
      1 2 3 5
      2 5 10 5
      3 11 9 10
    }
  
    do_execsql_test 5.$tn2.$tn.3 { PRAGMA integrity_check } ok
  }
}

finish_test

Changes to ext/ota/sqlite3ota.c.

74
75
76
77
78
79
80




81
82
83
84
85
86
87
...
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
200
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
...
408
409
410
411
412
413
414
415

416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
...
436
437
438
439
440
441
442

443
444
445
446
447
448
449
450






































451
452
453
454
455
456
457
...
509
510
511
512
513
514
515

516
517
518
519
520
521
522
523
524
525
526
527
528

529
530
531
532
533
534
535
...
557
558
559
560
561
562
563

564
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
602
603
604
605
...
624
625
626
627
628
629
630


631
632
633
634
635
636
637
...
666
667
668
669
670
671
672



673


674

675

676
677






678



679
680
681
682
683
684
685

686
687
688
689
690
691
692
693
694
695
696
697
698
699

700
701
702
703
704
705
706
...
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
  int iVisit;                     /* Number of points visited, incl. current */

  /* Statements created by otaObjIterPrepareAll() */
  int nCol;                       /* Number of columns in current object */
  sqlite3_stmt *pSelect;          /* Source data */
  sqlite3_stmt *pInsert;          /* Statement for INSERT operations */
  sqlite3_stmt *pDelete;          /* Statement for DELETE ops */




};

/*
** OTA handle.
*/
struct sqlite3ota {
  sqlite3 *db;                    /* "main" -> target db, "ota" -> ota db */
................................................................................
    sqlite3_free(pIter->azTblCol[i]);
  }
  sqlite3_free(pIter->azTblCol);
  sqlite3_free(pIter->abTblPk);
  pIter->azTblCol = 0;
  pIter->abTblPk = 0;
  pIter->nTblCol = 0;


















}

/*
** Clean up any resources allocated as part of the iterator object passed
** as the only argument.
*/
static void otaObjIterFinalize(OtaObjIter *pIter){

  sqlite3_finalize(pIter->pTblIter);
  sqlite3_finalize(pIter->pIdxIter);
  sqlite3_finalize(pIter->pSelect);
  sqlite3_finalize(pIter->pInsert);
  sqlite3_finalize(pIter->pDelete);
  otaObjIterFreeCols(pIter);
  memset(pIter, 0, sizeof(OtaObjIter));
}

/*
** Advance the iterator to the next position.
**
................................................................................
** error code is returned.
*/
static int otaObjIterNext(sqlite3ota *p, OtaObjIter *pIter){
  int rc = p->rc;
  if( rc==SQLITE_OK ){

    /* Free any SQLite statements used while processing the previous object */ 
    sqlite3_finalize(pIter->pSelect);
    sqlite3_finalize(pIter->pInsert);
    sqlite3_finalize(pIter->pDelete);
    pIter->pSelect = 0;
    pIter->pInsert = 0;
    pIter->pDelete = 0;
    pIter->nCol = 0;

    if( pIter->bCleanup ){
      otaObjIterFreeCols(pIter);
      pIter->bCleanup = 0;
      rc = sqlite3_step(pIter->pTblIter);
      if( rc!=SQLITE_ROW ){
        rc = sqlite3_reset(pIter->pTblIter);
................................................................................
    }
  }
  return zList;
}

static char *otaObjIterGetOldlist(
  sqlite3ota *p, 
  OtaObjIter *pIter

){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    const char *zSep = "";
    int i;
    for(i=0; i<pIter->nTblCol; i++){
      zList = sqlite3_mprintf("%z%sold.%s", zList, zSep, pIter->azTblCol[i]);
      zSep = ", ";
      if( zList==0 ){
        p->rc = SQLITE_NOMEM;
        break;
      }
    }
  }
  return zList;
................................................................................
){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    const char *zSep = "";
    int i;
    for(i=0; i<pIter->nTblCol; i++){
      if( pIter->abTblPk[i] ){

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






































  }
  return zList;
}

static char *otaObjIterGetBindlist(sqlite3ota *p, int nBind){
  char *zRet = 0;
  if( p->rc==SQLITE_OK ){
................................................................................

      /* Create the SELECT statement to read keys in sorted order */
      zCollist = otaObjIterGetCollist(p, pIter, pIter->nCol, aiCol);
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz,
            sqlite3_mprintf(
              "SELECT %s, ota_control FROM ota.'data_%q' "

                "UNION ALL "
              "SELECT %s, ota_control FROM ota.'ota_tmp_%q' "
              "ORDER BY %s%s",
              zCollist, pIter->zTbl, 
              zCollist, pIter->zTbl, 
              zCollist, zLimit
            )
        );
      }
    }else{
      char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol);
      char *zWhere = otaObjIterGetWhere(p, pIter);
      char *zOldlist = otaObjIterGetOldlist(p, pIter);

      zCollist = otaObjIterGetCollist(p, pIter, pIter->nTblCol, 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(
................................................................................
        );
      }

      if( p->rc==SQLITE_OK ){
        otaMPrintfExec(p, 
            "CREATE TABLE IF NOT EXISTS ota.'ota_tmp_%q' AS "
            "SELECT * 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) VALUES(2, %s);"
            "END;"












            , pIter->zTbl, pIter->zTbl, pIter->zTbl, pIter->zTbl, pIter->zTbl,
            zCollist, zOldlist

        );
      }












      sqlite3_free(zWhere);
      sqlite3_free(zOldlist);

      sqlite3_free(zBindings);
    }
    sqlite3_free(zCollist);
    sqlite3_free(zLimit);
  }
  
  return p->rc;
}

#define OTA_INSERT     1
#define OTA_DELETE     2
#define OTA_IDX_DELETE 3

#define OTA_UPDATE     4

/*
** The SELECT statement iterating through the keys for the current object
** (p->objiter.pSelect) currently points to a valid row. However, there
** is something wrong with the ota_control value in the ota_control value
** 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");





















}

/*
** The SELECT statement iterating through the keys for the current object
** (p->objiter.pSelect) currently points to a valid row. This function
** determines the type of operation requested by this row and returns
** one of the following values to indicate the result:
................................................................................
      int iVal = sqlite3_column_int(p->objiter.pSelect, iCol);
      if( iVal==0 ){
        res = OTA_INSERT;
      }else if( iVal==1 ){
        res = OTA_DELETE;
      }else if( iVal==2 ){
        res = OTA_IDX_DELETE;


      }
      break;
    }

    case SQLITE_TEXT:
      *pzMask = (const char*)sqlite3_column_text(p->objiter.pSelect, iCol);
      res = OTA_UPDATE;
................................................................................

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

    if( pIter->zIdx==0 && eType==OTA_IDX_DELETE ){
      otaBadControlError(p);
    }



    else if( eType==OTA_INSERT || eType==OTA_IDX_DELETE ){


      sqlite3_stmt *pWriter;

      assert( eType!=OTA_UPDATE );


      pWriter = (eType==OTA_INSERT)?pIter->pInsert:pIter->pDelete;






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



        sqlite3_value *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_DELETE && pIter->zIdx==0 ){

      int iVar = 1;
      assert( pIter->zIdx==0 );
      assert( pIter->nCol==pIter->nTblCol );
      for(i=0; i<pIter->nCol; i++){
        if( pIter->abTblPk[i] ){
          sqlite3_value *pVal = sqlite3_column_value(pIter->pSelect, i);
          sqlite3_bind_value(pIter->pDelete, iVar++, pVal);
        }
      }
      sqlite3_step(pIter->pDelete);
      p->rc = resetAndCollectError(pIter->pDelete, &p->zErrmsg);
    }else if( eType==OTA_UPDATE ){
      p->rc = SQLITE_ERROR;
      p->zErrmsg = sqlite3_mprintf("not yet");

    }else{
      /* no-op */
      assert( eType==OTA_DELETE && pIter->zIdx );
    }
  }

  return p->rc;
................................................................................
    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 ){







>
>
>
>







 







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







>


<
<
<







 







|
<
<
<
<
<
<







 







|
>



|


|
|







 







>
|







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







 







>












|
>







 







>




>
>
>
>
>
>
>
>
>
>
>
>
|
|
>


>
>
>
>
>
>
>
>
>
>
>



>












>
|

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







 







>
>







 







>
>
>
|
>
>

>

>

<
>
>
>
>
>
>

>
>
>





<
|
>
|
|
<
|
<

|

<
|
|
<
<
<
>







 







|







74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
...
179
180
181
182
183
184
185
186
187
188
189
190
191
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
...
224
225
226
227
228
229
230
231






232
233
234
235
236
237
238
...
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
...
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
...
563
564
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
...
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671

672





673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
...
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
...
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
...
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
  int iVisit;                     /* Number of points visited, incl. current */

  /* Statements created by otaObjIterPrepareAll() */
  int nCol;                       /* Number of columns in current object */
  sqlite3_stmt *pSelect;          /* Source data */
  sqlite3_stmt *pInsert;          /* Statement for INSERT operations */
  sqlite3_stmt *pDelete;          /* Statement for DELETE ops */

  /* Last UPDATE used (for PK b-tree updates only), or NULL. */
  char *zMask;                    /* Copy of update mask used with pUpdate */
  sqlite3_stmt *pUpdate;          /* Last update statement (or NULL) */
};

/*
** OTA handle.
*/
struct sqlite3ota {
  sqlite3 *db;                    /* "main" -> target db, "ota" -> ota db */
................................................................................
    sqlite3_free(pIter->azTblCol[i]);
  }
  sqlite3_free(pIter->azTblCol);
  sqlite3_free(pIter->abTblPk);
  pIter->azTblCol = 0;
  pIter->abTblPk = 0;
  pIter->nTblCol = 0;
  sqlite3_free(pIter->zMask);
  pIter->zMask = 0;
}

/*
** Finalize all statements and free all allocations that are specific to
** the current object (table/index pair).
*/
static void otaObjIterClearStatements(OtaObjIter *pIter){
  sqlite3_finalize(pIter->pSelect);
  sqlite3_finalize(pIter->pInsert);
  sqlite3_finalize(pIter->pDelete);
  sqlite3_finalize(pIter->pUpdate);
  pIter->pSelect = 0;
  pIter->pInsert = 0;
  pIter->pDelete = 0;
  pIter->pUpdate = 0;
  pIter->nCol = 0;
}

/*
** Clean up any resources allocated as part of the iterator object passed
** as the only argument.
*/
static void otaObjIterFinalize(OtaObjIter *pIter){
  otaObjIterClearStatements(pIter);
  sqlite3_finalize(pIter->pTblIter);
  sqlite3_finalize(pIter->pIdxIter);



  otaObjIterFreeCols(pIter);
  memset(pIter, 0, sizeof(OtaObjIter));
}

/*
** Advance the iterator to the next position.
**
................................................................................
** error code is returned.
*/
static int otaObjIterNext(sqlite3ota *p, OtaObjIter *pIter){
  int rc = p->rc;
  if( rc==SQLITE_OK ){

    /* Free any SQLite statements used while processing the previous object */ 
    otaObjIterClearStatements(pIter);







    if( pIter->bCleanup ){
      otaObjIterFreeCols(pIter);
      pIter->bCleanup = 0;
      rc = sqlite3_step(pIter->pTblIter);
      if( rc!=SQLITE_ROW ){
        rc = sqlite3_reset(pIter->pTblIter);
................................................................................
    }
  }
  return zList;
}

static char *otaObjIterGetOldlist(
  sqlite3ota *p, 
  OtaObjIter *pIter,
  const char *zObj
){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    const char *zS = "";
    int i;
    for(i=0; i<pIter->nTblCol; i++){
      zList = sqlite3_mprintf("%z%s%s.%s", zList, zS, zObj, pIter->azTblCol[i]);
      zS = ", ";
      if( zList==0 ){
        p->rc = SQLITE_NOMEM;
        break;
      }
    }
  }
  return zList;
................................................................................
){
  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;
}

/*
** The SELECT statement iterating through the keys for the current object
** (p->objiter.pSelect) currently points to a valid row. However, there
** is something wrong with the ota_control value in the ota_control value
** 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
          );
          zSep = ", ";
        }
      }
    }
  }
  return zList;
}

static char *otaObjIterGetBindlist(sqlite3ota *p, int nBind){
  char *zRet = 0;
  if( p->rc==SQLITE_OK ){
................................................................................

      /* Create the SELECT statement to read keys in sorted order */
      zCollist = otaObjIterGetCollist(p, pIter, pIter->nCol, aiCol);
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz,
            sqlite3_mprintf(
              "SELECT %s, ota_control FROM ota.'data_%q' "
              "WHERE typeof(ota_control)='integer' AND ota_control!=1 "
                "UNION ALL "
              "SELECT %s, ota_control FROM ota.'ota_tmp_%q' "
              "ORDER BY %s%s",
              zCollist, pIter->zTbl, 
              zCollist, pIter->zTbl, 
              zCollist, zLimit
            )
        );
      }
    }else{
      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);
      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(
................................................................................
        );
      }

      if( p->rc==SQLITE_OK ){
        otaMPrintfExec(p, 
            "CREATE TABLE IF NOT EXISTS ota.'ota_tmp_%q' AS "
            "SELECT * 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) VALUES(2, %s);"
            "END;"

            "CREATE TEMP TRIGGER ota_update1_%q BEFORE UPDATE ON main.%Q "
            "BEGIN "
            "  INSERT INTO 'ota_tmp_%q'(ota_control, %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) VALUES(3, %s);"
            "END;"

            , pIter->zTbl, pIter->zTbl, 
            pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOldlist, 
            pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOldlist, 
            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{
          memset(pIter->zMask, 0, nMask);
        }
      }

      sqlite3_free(zWhere);
      sqlite3_free(zOldlist);
      sqlite3_free(zNewlist);
      sqlite3_free(zBindings);
    }
    sqlite3_free(zCollist);
    sqlite3_free(zLimit);
  }
  
  return p->rc;
}

#define OTA_INSERT     1
#define OTA_DELETE     2
#define OTA_IDX_DELETE 3
#define OTA_IDX_INSERT 4
#define OTA_UPDATE     5


static int otaGetUpdateStmt(





  sqlite3ota *p, 
  OtaObjIter *pIter, 
  const char *zMask,
  sqlite3_stmt **ppStmt
){
  if( pIter->pUpdate && strcmp(zMask, pIter->zMask)==0 ){
    *ppStmt = pIter->pUpdate;
  }else{
    char *zWhere = otaObjIterGetWhere(p, pIter);
    char *zSet = otaObjIterGetSetlist(p, pIter, zMask);
    char *zUpdate = 0;
    sqlite3_finalize(pIter->pUpdate);
    pIter->pUpdate = 0;
    if( p->rc==SQLITE_OK ){
      zUpdate = sqlite3_mprintf("UPDATE %Q SET %s WHERE %s", 
          pIter->zTbl, zSet, zWhere
      );
      p->rc = prepareFreeAndCollectError(
          p->db, &pIter->pUpdate, &p->zErrmsg, zUpdate
      );
      *ppStmt = pIter->pUpdate;
    }
    if( p->rc==SQLITE_OK ){
      memcpy(pIter->zMask, zMask, pIter->nTblCol);
    }
    sqlite3_free(zWhere);
    sqlite3_free(zSet);
  }
  return p->rc;
}

/*
** The SELECT statement iterating through the keys for the current object
** (p->objiter.pSelect) currently points to a valid row. This function
** determines the type of operation requested by this row and returns
** one of the following values to indicate the result:
................................................................................
      int iVal = sqlite3_column_int(p->objiter.pSelect, iCol);
      if( iVal==0 ){
        res = OTA_INSERT;
      }else if( iVal==1 ){
        res = OTA_DELETE;
      }else if( iVal==2 ){
        res = OTA_IDX_DELETE;
      }else if( iVal==3 ){
        res = OTA_IDX_INSERT;
      }
      break;
    }

    case SQLITE_TEXT:
      *pzMask = (const char*)sqlite3_column_text(p->objiter.pSelect, iCol);
      res = OTA_UPDATE;
................................................................................

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

    if( pIter->zIdx==0 && eType==OTA_IDX_DELETE ){
      otaBadControlError(p);
    }
    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++){
        if( eType==SQLITE_DELETE && pIter->zIdx==0 && pIter->abTblPk[i]==0 ){
          continue;
        }
        sqlite3_value *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 );
    }
  }

  return p->rc;
................................................................................
    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 ){