/ Check-in [1a1b69e8]
Login

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

Overview
Comment:Change the name of the new API on this branch to "sqlite3_bp_progress". Add tests and documentation for the same.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rbu-percent-progress
Files: files | file ages | folders
SHA1: 1a1b69e87eb7d18f76f5b733e44da75136a686b6
User & Date: dan 2016-03-18 18:56:45
Context
2016-03-18
20:12
Add further tests for sqlite3rbu_bp_progress(). Fix a problem in handling WITHOUT ROWID tables in the same. check-in: 65e02368 user: dan tags: rbu-percent-progress
18:56
Change the name of the new API on this branch to "sqlite3_bp_progress". Add tests and documentation for the same. check-in: 1a1b69e8 user: dan tags: rbu-percent-progress
10:29
Add tests for the changes on this branch. Fix a problem with calls to the new progress indicator API made after an rbu update has been resumed. check-in: bf823217 user: dan tags: rbu-percent-progress
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rbu/rbuprogress.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12     12   
    13     13   source [file join [file dirname [info script]] rbu_common.tcl]
    14     14   set ::testprefix rbuprogress
    15     15   
           16  +
           17  +proc create_db_file {filename sql} {
           18  +  forcedelete $filename
           19  +  sqlite3 tmpdb $filename  
           20  +  tmpdb eval $sql
           21  +  tmpdb close
           22  +}
           23  +
    16     24   # Create a simple RBU database. That expects to write to a table:
    17     25   #
    18     26   #   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
    19     27   #
    20     28   proc create_rbu1 {filename} {
    21         -  forcedelete $filename
    22         -  sqlite3 rbu1 $filename  
    23         -  rbu1 eval {
           29  +  create_db_file $filename {
    24     30       CREATE TABLE data_t1(a, b, c, rbu_control);
    25     31       INSERT INTO data_t1 VALUES(1, 2, 3, 0);
    26     32       INSERT INTO data_t1 VALUES(2, 'two', 'three', 0);
    27     33       INSERT INTO data_t1 VALUES(3, NULL, 8.2, 0);
    28     34   
    29     35       CREATE TABLE rbu_count(tbl, cnt);
    30     36       INSERT INTO rbu_count VALUES('data_t1', 3);
    31     37     }
    32         -  rbu1 close
    33     38     return $filename
    34     39   }
    35     40   
    36     41   
    37     42   do_execsql_test 1.0 {
    38     43     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
    39     44   }
    40     45   
    41     46   do_test 1.1 {
    42     47     create_rbu1 rbu.db
    43     48     sqlite3rbu rbu test.db rbu.db
    44         -  rbu stage_progress
           49  +  rbu bp_progress
    45     50   } {0 0}
    46         -do_test 1.2 { rbu step ; rbu stage_progress } {3333 0}
    47         -do_test 1.3 { rbu step ; rbu stage_progress } {6666 0}
    48         -do_test 1.4 { rbu step ; rbu stage_progress } {10000 0}
    49         -do_test 1.5 { rbu step ; rbu stage_progress } {10000 0}
    50         -do_test 1.6 { rbu step ; rbu stage_progress } {10000 0}
    51         -do_test 1.7 { rbu step ; rbu stage_progress } {10000 5000}
    52         -do_test 1.8 { rbu step ; rbu stage_progress } {10000 10000}
    53         -do_test 1.9 { rbu step ; rbu stage_progress } {10000 10000}
           51  +do_test 1.2 { rbu step ; rbu bp_progress } {3333 0}
           52  +do_test 1.3 { rbu step ; rbu bp_progress } {6666 0}
           53  +do_test 1.4 { rbu step ; rbu bp_progress } {10000 0}
           54  +do_test 1.5 { rbu step ; rbu bp_progress } {10000 0}
           55  +do_test 1.6 { rbu step ; rbu bp_progress } {10000 0}
           56  +do_test 1.7 { rbu step ; rbu bp_progress } {10000 5000}
           57  +do_test 1.8 { rbu step ; rbu bp_progress } {10000 10000}
           58  +do_test 1.9 { rbu step ; rbu bp_progress } {10000 10000}
    54     59   
    55     60   do_test 1.10 {
    56     61     rbu close
    57     62   } {SQLITE_DONE}
    58     63   
    59     64   #-------------------------------------------------------------------------
    60     65   #
................................................................................
    62     67     uplevel [list do_test $tn [subst -nocommands {
    63     68       if {$bReopen==0} { sqlite3rbu rbu $target $rbu }
    64     69       set res [list]
    65     70       while 1 {
    66     71         if {$bReopen} { sqlite3rbu rbu $target $rbu }
    67     72         set rc [rbu step]
    68     73         if {[set rc] != "SQLITE_OK"} { error "error 1" }
    69         -      lappend res [lindex [rbu stage_progress] 0]
           74  +      lappend res [lindex [rbu bp_progress] 0]
    70     75         if {[lindex [set res] end]==10000} break
    71     76         if {$bReopen} { rbu close }
    72     77       }
    73     78       if {[set res] != [list $reslist]} {
    74     79         error "1. reslist incorrect (expect=$reslist got=[set res])"
    75     80       }
    76     81   
    77     82       # One step to clean up the temporary tables used to update the only
    78     83       # target table in the rbu database. And one more to move the *-oal 
    79     84       # file to *-wal. After each of these steps, the progress remains
    80     85       # at "10000 0".
    81     86       #
    82         -    rbu step
    83         -    set res [rbu stage_progress]
    84         -    if {[set res] != [list 10000 0]} {
    85         -      error "2. reslist incorrect (expect=10000 0 got=[set res])"
           87  +    if {[lindex [list $reslist] 0]!=-1} {
           88  +      rbu step
           89  +      set res [rbu bp_progress]
           90  +      if {[set res] != [list 10000 0]} {
           91  +        error "2. reslist incorrect (expect=10000 0 got=[set res])"
           92  +      }
    86     93       }
           94  +
    87     95       rbu step
    88         -    set res [rbu stage_progress]
           96  +    set res [rbu bp_progress]
    89     97       if {[set res] != [list 10000 0]} {
    90     98         error "3. reslist incorrect (expect=10000 0 got=[set res])"
    91     99       }
    92    100   
    93    101       # Do the checkpoint.
    94    102       while {[rbu step]=="SQLITE_OK"} { 
    95         -      foreach {a b} [rbu stage_progress] {}
          103  +      foreach {a b} [rbu bp_progress] {}
    96    104         if {[set a]!=10000 || [set b]<=0 || [set b]>10000} {
    97    105           error "4. reslist incorrect (expect=10000 1..10000 got=[set a] [set b])"
    98    106         }
    99    107       }
   100    108   
   101         -    set res [rbu stage_progress]
          109  +    set res [rbu bp_progress]
   102    110       if {[set res] != [list 10000 10000]} {
   103    111         error "5. reslist is incorrect (expect=10000 10000 got=[set res])"
   104    112       }
   105    113   
   106    114       rbu close
   107    115     }] {SQLITE_DONE}]
   108    116   }
   109    117   
   110         -proc create_db_file {filename sql} {
   111         -  forcedelete $filename
   112         -  sqlite3 tmpdb $filename  
   113         -  tmpdb eval $sql
   114         -  tmpdb close
   115         -}
   116         -
   117    118   foreach {bReopen} { 0 1 } {
   118    119   
   119    120     reset_db
   120    121     do_test 2.$bReopen.1.0 {
   121    122       execsql {
   122    123         CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
   123    124       }
................................................................................
   193    194         INSERT INTO data_t1 VALUES(4, NULL, 4, '.xx');
   194    195     
   195    196         CREATE TABLE rbu_count(tbl, cnt);
   196    197         INSERT INTO rbu_count VALUES('data_t1', 1);
   197    198       }
   198    199     } {}
   199    200     do_sp_test 2.$bReopen.5.1 $bReopen test.db rbu.db {10000}
          201  +
          202  +  reset_db
          203  +  do_test 2.$bReopen.6.0 {
          204  +    execsql { 
          205  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          206  +      CREATE INDEX i1 ON t1(b);
          207  +      INSERT INTO t1 VALUES(1, 1, 1);
          208  +      INSERT INTO t1 VALUES(2, 2, 2);
          209  +      INSERT INTO t1 VALUES(3, 3, 3);
          210  +    }
          211  +    create_db_file rbu.db {
          212  +      CREATE TABLE data_t1(a, b, c, rbu_control);
          213  +      INSERT INTO data_t1 VALUES(4, 4, 4, 0);
          214  +      INSERT INTO data_t1 VALUES(2, NULL, NULL, 1);
          215  +      INSERT INTO data_t1 VALUES(5, NULL, NULL, 1);
          216  +    }
          217  +  } {}
          218  +  do_sp_test 2.$bReopen.6.1 $bReopen test.db rbu.db {-1 -1 -1 -1 -1 10000}
          219  +}
          220  +
          221  +#-------------------------------------------------------------------------
          222  +# The following tests verify that the API works when resuming an update
          223  +# during the incremental checkpoint stage.
          224  +#
          225  +proc do_phase2_test {tn bReopen target rbu nStep} {
          226  +  uplevel [list do_test $tn [subst -nocommands {
          227  +
          228  +    # Build the OAL/WAL file:
          229  +    sqlite3rbu rbu $target $rbu
          230  +    while {[lindex [rbu bp_progress] 0]<10000} { 
          231  +      set rc [rbu step]
          232  +      if {"SQLITE_OK" != [set rc]} { rbu close }
          233  +    }
          234  +
          235  +    # Clean up the temp tables and move the *-oal file to *-wal.
          236  +    rbu step
          237  +    rbu step
          238  +
          239  +    for {set i 0} {[set i] < $nStep} {incr i} {
          240  +      if {$bReopen} {
          241  +        rbu close
          242  +        sqlite3rbu rbu $target $rbu
          243  +      }
          244  +      rbu step
          245  +      set res [rbu bp_progress]
          246  +      set expect [expr (1 + [set i]) * 10000 / $nStep]
          247  +      if {[lindex [set res] 1] != [set expect]} {
          248  +        error "Have [set res], expected 10000 [set expect]"
          249  +      }
          250  +    }
          251  +
          252  +    set rc [rbu step]
          253  +    if {[set rc] != "SQLITE_DONE"} {
          254  +      error "Have [set rc], expected SQLITE_DONE" 
          255  +    }
          256  +
          257  +    rbu close
          258  +  }] {SQLITE_DONE}]
          259  +}
          260  +
          261  +foreach bReopen {0 1} {
          262  +  do_test 3.$bReopen.1.0 {
          263  +    reset_db
          264  +    execsql {
          265  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          266  +      CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
          267  +      CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
          268  +      CREATE TABLE t4(a INTEGER PRIMARY KEY, b);
          269  +    }
          270  +    create_db_file rbu.db {
          271  +      CREATE TABLE data_t1(a, b, rbu_control);
          272  +      CREATE TABLE data_t2(a, b, rbu_control);
          273  +      CREATE TABLE data_t3(a, b, rbu_control);
          274  +      CREATE TABLE data_t4(a, b, rbu_control);
          275  +      INSERT INTO data_t1 VALUES(1, 2, 0);
          276  +      INSERT INTO data_t2 VALUES(1, 2, 0);
          277  +      INSERT INTO data_t3 VALUES(1, 2, 0);
          278  +      INSERT INTO data_t4 VALUES(1, 2, 0);
          279  +  
          280  +      CREATE TABLE rbu_count(tbl, cnt);
          281  +      INSERT INTO rbu_count VALUES('data_t1', 1);
          282  +      INSERT INTO rbu_count VALUES('data_t2', 1);
          283  +      INSERT INTO rbu_count VALUES('data_t3', 1);
          284  +      INSERT INTO rbu_count VALUES('data_t4', 1);
          285  +    }
          286  +  } {}
          287  +  do_phase2_test 3.$bReopen.1.1 $bReopen test.db rbu.db 5
   200    288   }
          289  +
   201    290   
   202    291   finish_test
   203    292   

Changes to ext/rbu/sqlite3rbu.c.

   296    296   struct RbuFrame {
   297    297     u32 iDbPage;
   298    298     u32 iWalFrame;
   299    299   };
   300    300   
   301    301   /*
   302    302   ** RBU handle.
          303  +**
          304  +** nPhaseOneStep:
          305  +**   If the RBU database contains an rbu_count table, this value is set to
          306  +**   a running estimate of the number of b-tree operations required to 
          307  +**   finish populating the *-oal file. This allows the sqlite3_bp_progress()
          308  +**   API to calculate the permyriadage progress of populating the *-oal file
          309  +**   using the formula:
          310  +**
          311  +**     permyriadage = (10000 * nProgress) / nPhaseOneStep
          312  +**
          313  +**   nPhaseOneStep is initialized to the sum of:
          314  +**
          315  +**     nRow * (nIndex + 1)
          316  +**
          317  +**   for all source tables in the RBU database, where nRow is the number
          318  +**   of rows in the source table and nIndex the number of indexes on the
          319  +**   corresponding target database table.
          320  +**
          321  +**   This estimate is accurate if the RBU update consists entirely of
          322  +**   INSERT operations. However, it is inaccurate if:
          323  +**
          324  +**     * the RBU update contains any UPDATE operations. If the PK specified
          325  +**       for an UPDATE operation does not exist in the target table, then
          326  +**       no b-tree operations are required on index b-trees. Or if the 
          327  +**       specified PK does exist, then (nIndex*2) such operations are
          328  +**       required (one delete and one insert on each index b-tree).
          329  +**
          330  +**     * the RBU update contains any DELETE operations for which the specified
          331  +**       PK does not exist. In this case no operations are required on index
          332  +**       b-trees.
          333  +**
          334  +**     * the RBU update contains REPLACE operations. These are similar to
          335  +**       UPDATE operations.
          336  +**
          337  +**   nPhaseOneStep is updated to account for the conditions above during the
          338  +**   first pass of each source table. The updated nPhaseOneStep value is
          339  +**   stored in the rbu_state table if the RBU update is suspended.
   303    340   */
   304    341   struct sqlite3rbu {
   305    342     int eStage;                     /* Value of RBU_STATE_STAGE field */
   306    343     sqlite3 *dbMain;                /* target database handle */
   307    344     sqlite3 *dbRbu;                 /* rbu database handle */
   308    345     char *zTarget;                  /* Path to target db */
   309    346     char *zRbu;                     /* Path to rbu db */
................................................................................
  2953   2990           break;
  2954   2991   
  2955   2992         case RBU_STATE_OALSZ:
  2956   2993           pRet->iOalSz = (u32)sqlite3_column_int64(pStmt, 1);
  2957   2994           break;
  2958   2995   
  2959   2996         case RBU_STATE_PHASEONESTEP:
  2960         -        pRet->nPhaseOneStep = (u32)sqlite3_column_int64(pStmt, 1);
         2997  +        pRet->nPhaseOneStep = sqlite3_column_int64(pStmt, 1);
  2961   2998           break;
  2962   2999   
  2963   3000         default:
  2964   3001           rc = SQLITE_CORRUPT;
  2965   3002           break;
  2966   3003       }
  2967   3004     }
................................................................................
  3064   3101     if( p->zVfsName ){
  3065   3102       sqlite3rbu_destroy_vfs(p->zVfsName);
  3066   3103       p->zVfsName = 0;
  3067   3104     }
  3068   3105   }
  3069   3106   
  3070   3107   /*
  3071         -**
         3108  +** This user-defined SQL function is invoked with a single argument - the
         3109  +** name of a table expected to appear in the target database. It returns
         3110  +** the number of auxilliary indexes on the table.
  3072   3111   */
  3073   3112   static void rbuIndexCntFunc(
  3074   3113     sqlite3_context *pCtx, 
  3075   3114     int nVal,
  3076   3115     sqlite3_value **apVal
  3077   3116   ){
  3078   3117     sqlite3rbu *p = (sqlite3rbu*)sqlite3_user_data(pCtx);
................................................................................
  3367   3406   ** updates) that have been performed on the target database since the
  3368   3407   ** current RBU update was started.
  3369   3408   */
  3370   3409   sqlite3_int64 sqlite3rbu_progress(sqlite3rbu *pRbu){
  3371   3410     return pRbu->nProgress;
  3372   3411   }
  3373   3412   
  3374         -void sqlite3rbu_stage_progress(sqlite3rbu *p, int *pnOne, int *pnTwo){
         3413  +/*
         3414  +** Return permyriadage progress indications for the two main stages of
         3415  +** an RBU update.
         3416  +*/
         3417  +void sqlite3rbu_bp_progress(sqlite3rbu *p, int *pnOne, int *pnTwo){
  3375   3418     const int MAX_PROGRESS = 10000;
  3376   3419     switch( p->eStage ){
  3377   3420       case RBU_STAGE_OAL:
  3378   3421         if( p->nPhaseOneStep>0 ){
  3379   3422           *pnOne = (int)(MAX_PROGRESS * (i64)p->nProgress/(i64)p->nPhaseOneStep);
  3380   3423         }else{
  3381   3424           *pnOne = -1;

Changes to ext/rbu/sqlite3rbu.h.

   396    396   /*
   397    397   ** Return the total number of key-value operations (inserts, deletes or 
   398    398   ** updates) that have been performed on the target database since the
   399    399   ** current RBU update was started.
   400    400   */
   401    401   sqlite3_int64 sqlite3rbu_progress(sqlite3rbu *pRbu);
   402    402   
   403         -void sqlite3rbu_stage_progress(sqlite3rbu *pRbu, int *pnOne, int *pnTwo);
          403  +/*
          404  +** Obtain permyriadage (permyriadage is to 10000 as percentage is to 100) 
          405  +** progress indications for the two stages of an RBU update. This API may
          406  +** be useful for driving GUI progress indicators and similar.
          407  +**
          408  +** An RBU update is divided into two stages:
          409  +**
          410  +**   * Stage 1, in which changes are accumulated in an oal/wal file, and
          411  +**   * Stage 2, in which the contents of the wal file are copied into the
          412  +**     main database.
          413  +**
          414  +** The update is visible to non-RBU clients during stage 2. During stage 1
          415  +** non-RBU reader clients may see the original database.
          416  +**
          417  +** If this API is called during stage 2 of the update, output variable 
          418  +** (*pnOne) is set to 10000 to indicate that stage 1 has finished and (*pnTwo)
          419  +** to a value between 0 and 10000 to indicate the permyriadage progress of
          420  +** stage 2. A value of 5000 indicates that stage 2 is half finished, 
          421  +** 9000 indicates that it is 90% finished, and so on.
          422  +**
          423  +** If this API is called during stage 1 of the update, output variable 
          424  +** (*pnTwo) is set to 0 to indicate that stage 2 has not yet started. The
          425  +** value to which (*pnOne) is set depends on whether or not the RBU 
          426  +** database contains an "rbu_count" table. The rbu_count table, if it 
          427  +** exists, must contain the same columns as the following:
          428  +**
          429  +**   CREATE TABLE rbu_count(tbl TEXT PRIMARY KEY, cnt INTEGER) WITHOUT ROWID;
          430  +**
          431  +** There must be one row in the table for each source (data_xxx) table within
          432  +** the RBU database. The 'tbl' column should contain the name of the source
          433  +** table. The 'cnt' column should contain the number of rows within the
          434  +** source table.
          435  +**
          436  +** If the rbu_count table is present and populated correctly and this
          437  +** API is called during stage 1, the *pnOne output variable is set to the
          438  +** permyriadage progress of the same stage. If the rbu_count table does
          439  +** not exist, then (*pnOne) is set to -1 during stage 1. If the rbu_count
          440  +** table exists but is not correctly populated, the value of the *pnOne
          441  +** output variable during stage 1 is undefined.
          442  +*/
          443  +void sqlite3rbu_bp_progress(sqlite3rbu *pRbu, int *pnOne, int *pnTwo);
   404    444   
   405    445   /*
   406    446   ** Create an RBU VFS named zName that accesses the underlying file-system
   407    447   ** via existing VFS zParent. Or, if the zParent parameter is passed NULL, 
   408    448   ** then the new RBU VFS uses the default system VFS to access the file-system.
   409    449   ** The new object is registered as a non-default VFS with SQLite before 
   410    450   ** returning.

Changes to ext/rbu/test_rbu.c.

    62     62       const char *zUsage;
    63     63     } aCmd[] = {
    64     64       {"step", 2, ""},              /* 0 */
    65     65       {"close", 2, ""},             /* 1 */
    66     66       {"create_rbu_delta", 2, ""},  /* 2 */
    67     67       {"savestate", 2, ""},         /* 3 */
    68     68       {"dbMain_eval", 3, "SQL"},    /* 4 */
    69         -    {"stage_progress", 2, ""},    /* 5 */
           69  +    {"bp_progress", 2, ""},    /* 5 */
    70     70       {0,0,0}
    71     71     };
    72     72     int iCmd;
    73     73   
    74     74     if( objc<2 ){
    75     75       Tcl_WrongNumArgs(interp, 1, objv, "METHOD");
    76     76       return TCL_ERROR;
................................................................................
   133    133         if( rc!=SQLITE_OK ){
   134    134           Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3_errmsg(db), -1));
   135    135           ret = TCL_ERROR;
   136    136         }
   137    137         break;
   138    138       }
   139    139   
   140         -    case 5: /* stage_progress */ {
          140  +    case 5: /* bp_progress */ {
   141    141         int one, two;
   142    142         Tcl_Obj *pObj;
   143         -      sqlite3rbu_stage_progress(pRbu, &one, &two);
          143  +      sqlite3rbu_bp_progress(pRbu, &one, &two);
   144    144   
   145    145         pObj = Tcl_NewObj();
   146    146         Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(one));
   147    147         Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(two));
   148    148         Tcl_SetObjResult(interp, pObj);
   149    149         break;
   150    150       }