SQLite

# 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

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
** an error does occur, an error code and error message are also left in 
** the OTA handle.
*/
static int otaObjIterGetCols(sqlite3ota *p, OtaObjIter *pIter){
  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);
        if( pIter->azTblCol[nCol]==0 ) p->rc = SQLITE_NOMEM;
        nCol++;
      }
    }
    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 ){

**   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.