SQLite

#
#***********************************************************************
#

source [file join [file dirname [info script]] rbu_common.tcl]
set ::testprefix rbuprogress


proc create_db_file {filename sql} {
  forcedelete $filename
  sqlite3 tmpdb $filename  
  tmpdb eval $sql
  tmpdb close
}

# Create a simple RBU database. That expects to write to a table:
#
#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
#
proc create_rbu1 {filename} {
  create_db_file $filename {


    CREATE TABLE data_t1(a, b, c, rbu_control);
    INSERT INTO data_t1 VALUES(1, 2, 3, 0);
    INSERT INTO data_t1 VALUES(2, 'two', 'three', 0);
    INSERT INTO data_t1 VALUES(3, NULL, 8.2, 0);

    CREATE TABLE rbu_count(tbl, cnt);
    INSERT INTO rbu_count VALUES('data_t1', 3);
  }

  return $filename
}


do_execsql_test 1.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
}

do_test 1.1 {
  create_rbu1 rbu.db
  sqlite3rbu rbu test.db rbu.db
  rbu bp_progress
} {0 0}
do_test 1.2 { rbu step ; rbu bp_progress } {3333 0}
do_test 1.3 { rbu step ; rbu bp_progress } {6666 0}
do_test 1.4 { rbu step ; rbu bp_progress } {10000 0}
do_test 1.5 { rbu step ; rbu bp_progress } {10000 0}
do_test 1.6 { rbu step ; rbu bp_progress } {10000 0}
do_test 1.7 { rbu step ; rbu bp_progress } {10000 5000}
do_test 1.8 { rbu step ; rbu bp_progress } {10000 10000}
do_test 1.9 { rbu step ; rbu bp_progress } {10000 10000}

do_test 1.10 {
  rbu close
} {SQLITE_DONE}

#-------------------------------------------------------------------------
#
proc do_sp_test {tn bReopen target rbu reslist} {
  uplevel [list do_test $tn [subst -nocommands {
    if {$bReopen==0} { sqlite3rbu rbu $target $rbu }
    set res [list]
    while 1 {
      if {$bReopen} { sqlite3rbu rbu $target $rbu }
      set rc [rbu step]
      if {[set rc] != "SQLITE_OK"} { error "error 1" }
      lappend res [lindex [rbu bp_progress] 0]
      if {[lindex [set res] end]==10000} break
      if {$bReopen} { rbu close }
    }
    if {[set res] != [list $reslist]} {
      error "1. reslist incorrect (expect=$reslist got=[set res])"
    }

    # One step to clean up the temporary tables used to update the only
    # target table in the rbu database. And one more to move the *-oal 
    # file to *-wal. After each of these steps, the progress remains
    # at "10000 0".
    #
    if {[lindex [list $reslist] 0]!=-1} {
      rbu step
      set res [rbu bp_progress]
      if {[set res] != [list 10000 0]} {
        error "2. reslist incorrect (expect=10000 0 got=[set res])"
      }
    }

    rbu step
    set res [rbu bp_progress]
    if {[set res] != [list 10000 0]} {
      error "3. reslist incorrect (expect=10000 0 got=[set res])"
    }

    # Do the checkpoint.
    while {[rbu step]=="SQLITE_OK"} { 
      foreach {a b} [rbu bp_progress] {}
      if {[set a]!=10000 || [set b]<=0 || [set b]>10000} {
        error "4. reslist incorrect (expect=10000 1..10000 got=[set a] [set b])"
      }
    }

    set res [rbu bp_progress]
    if {[set res] != [list 10000 10000]} {
      error "5. reslist is incorrect (expect=10000 10000 got=[set res])"
    }

    rbu close
  }] {SQLITE_DONE}]
}








foreach {bReopen} { 0 1 } {

  reset_db
  do_test 2.$bReopen.1.0 {
    execsql {
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
    }

      INSERT INTO data_t1 VALUES(4, NULL, 4, '.xx');
  
      CREATE TABLE rbu_count(tbl, cnt);
      INSERT INTO rbu_count VALUES('data_t1', 1);
    }
  } {}
  do_sp_test 2.$bReopen.5.1 $bReopen test.db rbu.db {10000}

  reset_db
  do_test 2.$bReopen.6.0 {
    execsql { 
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      CREATE INDEX i1 ON t1(b);
      INSERT INTO t1 VALUES(1, 1, 1);
      INSERT INTO t1 VALUES(2, 2, 2);
      INSERT INTO t1 VALUES(3, 3, 3);
    }
    create_db_file rbu.db {
      CREATE TABLE data_t1(a, b, c, rbu_control);
      INSERT INTO data_t1 VALUES(4, 4, 4, 0);
      INSERT INTO data_t1 VALUES(2, NULL, NULL, 1);
      INSERT INTO data_t1 VALUES(5, NULL, NULL, 1);
    }
  } {}
  do_sp_test 2.$bReopen.6.1 $bReopen test.db rbu.db {-1 -1 -1 -1 -1 10000}
}

#-------------------------------------------------------------------------
# The following tests verify that the API works when resuming an update
# during the incremental checkpoint stage.
#
proc do_phase2_test {tn bReopen target rbu nStep} {
  uplevel [list do_test $tn [subst -nocommands {

    # Build the OAL/WAL file:
    sqlite3rbu rbu $target $rbu
    while {[lindex [rbu bp_progress] 0]<10000} { 
      set rc [rbu step]
      if {"SQLITE_OK" != [set rc]} { rbu close }
    }

    # Clean up the temp tables and move the *-oal file to *-wal.
    rbu step
    rbu step

    for {set i 0} {[set i] < $nStep} {incr i} {
      if {$bReopen} {
        rbu close
        sqlite3rbu rbu $target $rbu
      }
      rbu step
      set res [rbu bp_progress]
      set expect [expr (1 + [set i]) * 10000 / $nStep]
      if {[lindex [set res] 1] != [set expect]} {
        error "Have [set res], expected 10000 [set expect]"
      }
    }

    set rc [rbu step]
    if {[set rc] != "SQLITE_DONE"} {
      error "Have [set rc], expected SQLITE_DONE" 
    }

    rbu close
  }] {SQLITE_DONE}]
}

foreach bReopen {0 1} {
  do_test 3.$bReopen.1.0 {
    reset_db
    execsql {
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
      CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
      CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
      CREATE TABLE t4(a INTEGER PRIMARY KEY, b);
    }
    create_db_file rbu.db {
      CREATE TABLE data_t1(a, b, rbu_control);
      CREATE TABLE data_t2(a, b, rbu_control);
      CREATE TABLE data_t3(a, b, rbu_control);
      CREATE TABLE data_t4(a, b, rbu_control);
      INSERT INTO data_t1 VALUES(1, 2, 0);
      INSERT INTO data_t2 VALUES(1, 2, 0);
      INSERT INTO data_t3 VALUES(1, 2, 0);
      INSERT INTO data_t4 VALUES(1, 2, 0);
  
      CREATE TABLE rbu_count(tbl, cnt);
      INSERT INTO rbu_count VALUES('data_t1', 1);
      INSERT INTO rbu_count VALUES('data_t2', 1);
      INSERT INTO rbu_count VALUES('data_t3', 1);
      INSERT INTO rbu_count VALUES('data_t4', 1);
    }
  } {}
  do_phase2_test 3.$bReopen.1.1 $bReopen test.db rbu.db 5
}


finish_test

struct RbuFrame {
  u32 iDbPage;
  u32 iWalFrame;
};

/*
** RBU handle.
**
** nPhaseOneStep:
**   If the RBU database contains an rbu_count table, this value is set to
**   a running estimate of the number of b-tree operations required to 
**   finish populating the *-oal file. This allows the sqlite3_bp_progress()
**   API to calculate the permyriadage progress of populating the *-oal file
**   using the formula:
**
**     permyriadage = (10000 * nProgress) / nPhaseOneStep
**
**   nPhaseOneStep is initialized to the sum of:
**
**     nRow * (nIndex + 1)
**
**   for all source tables in the RBU database, where nRow is the number
**   of rows in the source table and nIndex the number of indexes on the
**   corresponding target database table.
**
**   This estimate is accurate if the RBU update consists entirely of
**   INSERT operations. However, it is inaccurate if:
**
**     * the RBU update contains any UPDATE operations. If the PK specified
**       for an UPDATE operation does not exist in the target table, then
**       no b-tree operations are required on index b-trees. Or if the 
**       specified PK does exist, then (nIndex*2) such operations are
**       required (one delete and one insert on each index b-tree).
**
**     * the RBU update contains any DELETE operations for which the specified
**       PK does not exist. In this case no operations are required on index
**       b-trees.
**
**     * the RBU update contains REPLACE operations. These are similar to
**       UPDATE operations.
**
**   nPhaseOneStep is updated to account for the conditions above during the
**   first pass of each source table. The updated nPhaseOneStep value is
**   stored in the rbu_state table if the RBU update is suspended.
*/
struct sqlite3rbu {
  int eStage;                     /* Value of RBU_STATE_STAGE field */
  sqlite3 *dbMain;                /* target database handle */
  sqlite3 *dbRbu;                 /* rbu database handle */
  char *zTarget;                  /* Path to target db */
  char *zRbu;                     /* Path to rbu db */

        break;

      case RBU_STATE_OALSZ:
        pRet->iOalSz = (u32)sqlite3_column_int64(pStmt, 1);
        break;

      case RBU_STATE_PHASEONESTEP:
        pRet->nPhaseOneStep = sqlite3_column_int64(pStmt, 1);
        break;

      default:
        rc = SQLITE_CORRUPT;
        break;
    }
  }

  if( p->zVfsName ){
    sqlite3rbu_destroy_vfs(p->zVfsName);
    p->zVfsName = 0;
  }
}

/*
** This user-defined SQL function is invoked with a single argument - the
** name of a table expected to appear in the target database. It returns
** the number of auxilliary indexes on the table.
*/
static void rbuIndexCntFunc(
  sqlite3_context *pCtx, 
  int nVal,
  sqlite3_value **apVal
){
  sqlite3rbu *p = (sqlite3rbu*)sqlite3_user_data(pCtx);

** updates) that have been performed on the target database since the
** current RBU update was started.
*/
sqlite3_int64 sqlite3rbu_progress(sqlite3rbu *pRbu){
  return pRbu->nProgress;
}

/*
** Return permyriadage progress indications for the two main stages of
** an RBU update.
*/
void sqlite3rbu_bp_progress(sqlite3rbu *p, int *pnOne, int *pnTwo){
  const int MAX_PROGRESS = 10000;
  switch( p->eStage ){
    case RBU_STAGE_OAL:
      if( p->nPhaseOneStep>0 ){
        *pnOne = (int)(MAX_PROGRESS * (i64)p->nProgress/(i64)p->nPhaseOneStep);
      }else{
        *pnOne = -1;

/*
** Return the total number of key-value operations (inserts, deletes or 
** updates) that have been performed on the target database since the
** current RBU update was started.
*/
sqlite3_int64 sqlite3rbu_progress(sqlite3rbu *pRbu);

/*
** Obtain permyriadage (permyriadage is to 10000 as percentage is to 100) 
** progress indications for the two stages of an RBU update. This API may
** be useful for driving GUI progress indicators and similar.
**
** An RBU update is divided into two stages:
**
**   * Stage 1, in which changes are accumulated in an oal/wal file, and
**   * Stage 2, in which the contents of the wal file are copied into the
**     main database.
**
** The update is visible to non-RBU clients during stage 2. During stage 1
** non-RBU reader clients may see the original database.
**
** If this API is called during stage 2 of the update, output variable 
** (*pnOne) is set to 10000 to indicate that stage 1 has finished and (*pnTwo)
** to a value between 0 and 10000 to indicate the permyriadage progress of
** stage 2. A value of 5000 indicates that stage 2 is half finished, 
** 9000 indicates that it is 90% finished, and so on.
**
** If this API is called during stage 1 of the update, output variable 
** (*pnTwo) is set to 0 to indicate that stage 2 has not yet started. The
** value to which (*pnOne) is set depends on whether or not the RBU 
** database contains an "rbu_count" table. The rbu_count table, if it 
** exists, must contain the same columns as the following:
**
**   CREATE TABLE rbu_count(tbl TEXT PRIMARY KEY, cnt INTEGER) WITHOUT ROWID;
**
** There must be one row in the table for each source (data_xxx) table within
** the RBU database. The 'tbl' column should contain the name of the source
** table. The 'cnt' column should contain the number of rows within the
** source table.
**
** If the rbu_count table is present and populated correctly and this
** API is called during stage 1, the *pnOne output variable is set to the
** permyriadage progress of the same stage. If the rbu_count table does
** not exist, then (*pnOne) is set to -1 during stage 1. If the rbu_count
** table exists but is not correctly populated, the value of the *pnOne
** output variable during stage 1 is undefined.
*/
void sqlite3rbu_bp_progress(sqlite3rbu *pRbu, int *pnOne, int *pnTwo);

/*
** Create an RBU VFS named zName that accesses the underlying file-system
** via existing VFS zParent. Or, if the zParent parameter is passed NULL, 
** then the new RBU VFS uses the default system VFS to access the file-system.
** The new object is registered as a non-default VFS with SQLite before 
** returning.

    const char *zUsage;
  } aCmd[] = {
    {"step", 2, ""},              /* 0 */
    {"close", 2, ""},             /* 1 */
    {"create_rbu_delta", 2, ""},  /* 2 */
    {"savestate", 2, ""},         /* 3 */
    {"dbMain_eval", 3, "SQL"},    /* 4 */
    {"bp_progress", 2, ""},    /* 5 */
    {0,0,0}
  };
  int iCmd;

  if( objc<2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "METHOD");
    return TCL_ERROR;

      if( rc!=SQLITE_OK ){
        Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3_errmsg(db), -1));
        ret = TCL_ERROR;
      }
      break;
    }

    case 5: /* bp_progress */ {
      int one, two;
      Tcl_Obj *pObj;
      sqlite3rbu_bp_progress(pRbu, &one, &two);

      pObj = Tcl_NewObj();
      Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(one));
      Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(two));
      Tcl_SetObjResult(interp, pObj);
      break;
    }