/ Changes On Branch rbu-percent-progress
Login

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

Changes In Branch rbu-percent-progress Excluding Merge-Ins

This is equivalent to a diff from 41c29c12 to a1132dd9

2016-03-19
17:48
Add the sqlite3rbu_bp_progress() API to the RBU extension. Used to obtain the percentage progress of an RBU update. (check-in: 209e31c7 user: dan tags: trunk)
17:09
Fix a problem detecting invalid values in the rbu_control column of an rbu database table. (Closed-Leaf check-in: a1132dd9 user: dan tags: rbu-percent-progress)
16:21
Update the sqldiff tool so that it generates an rbu_count table. (check-in: 1f7afb6e user: dan tags: rbu-percent-progress)
15:34
Merge latest trunk changes, including fixes to test script rbudiff.test, into this branch. (check-in: 734fc68f user: dan tags: rbu-percent-progress)
15:13
Fix another problem in test script rbudiff.test. (check-in: 41c29c12 user: dan tags: trunk)
14:53
Fix test scripts sqldiff.test and rbudiff.test so that they work with the --testdir option. (check-in: 1ffe3cde user: dan tags: trunk)

Changes to ext/rbu/rbu1.test.

   607    607            CREATE TABLE rbu.data_t1(a, b, rbu_control);
   608    608            INSERT INTO rbu.data_t1 VALUES(1, 2, 4);
   609    609          } {SQLITE_ERROR - invalid rbu_control value}
   610    610   
   611    611          9 {
   612    612            CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID;
   613    613            CREATE TABLE rbu.data_t1(a, b, rbu_control);
   614         -         INSERT INTO rbu.data_t1 VALUES(1, 2, 2);
          614  +         INSERT INTO rbu.data_t1 VALUES(1, 2, 3);
   615    615          } {SQLITE_ERROR - invalid rbu_control value}
   616    616   
   617    617          10 {
   618    618            CREATE TABLE t2(a, b);
   619    619            CREATE TABLE rbu.data_t1(a, b, rbu_control);
   620    620            INSERT INTO rbu.data_t1 VALUES(1, 2, 2);
   621    621          } {SQLITE_ERROR - no such table: t1}

Changes to ext/rbu/rbudiff.test.

    32     32       rbu close
    33     33       if {$rc != "SQLITE_OK"} break
    34     34     }
    35     35     set rc
    36     36   }
    37     37   
    38     38   proc apply_rbudiff {sql target} {
           39  +  test_rbucount $sql
    39     40     forcedelete rbu.db
    40     41     sqlite3 rbudb rbu.db
    41     42     rbudb eval $sql
    42     43     rbudb close
    43     44     step_rbu $target rbu.db
    44     45   }
           46  +
           47  +# The only argument is the output of an [sqldiff -rbu] run. This command
           48  +# tests that the contents of the rbu_count table is correct. An exception
           49  +# is thrown if it is not.
           50  +#
           51  +proc test_rbucount {sql} {
           52  +  sqlite3 tmpdb ""
           53  +  tmpdb eval $sql
           54  +  tmpdb eval {
           55  +    SELECT name FROM sqlite_master WHERE name LIKE 'data%' AND type='table'
           56  +  } {
           57  +    set a [tmpdb eval "SELECT count(*) FROM $name"]
           58  +    set b [tmpdb eval {SELECT cnt FROM rbu_count WHERE tbl = $name}]
           59  +    if {$a != $b} { 
           60  +      tmpdb close
           61  +      error "rbu_count error - tbl = $name" 
           62  +    }
           63  +  }
           64  +  tmpdb close
           65  +  return ""
           66  +}
    45     67   
    46     68   proc rbudiff_cksum {db1} {
    47     69     set txt ""
    48     70   
    49     71     sqlite3 dbtmp $db1
    50     72     foreach tbl [dbtmp eval {SELECT name FROM sqlite_master WHERE type='table'}] {
    51     73       set cols [list]

Added ext/rbu/rbuprogress.test.

            1  +# 2016 March 18
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +source [file join [file dirname [info script]] rbu_common.tcl]
           14  +set ::testprefix rbuprogress
           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  +
           24  +# Create a simple RBU database. That expects to write to a table:
           25  +#
           26  +#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           27  +#
           28  +proc create_rbu1 {filename} {
           29  +  create_db_file $filename {
           30  +    CREATE TABLE data_t1(a, b, c, rbu_control);
           31  +    INSERT INTO data_t1 VALUES(1, 2, 3, 0);
           32  +    INSERT INTO data_t1 VALUES(2, 'two', 'three', 0);
           33  +    INSERT INTO data_t1 VALUES(3, NULL, 8.2, 0);
           34  +
           35  +    CREATE TABLE rbu_count(tbl, cnt);
           36  +    INSERT INTO rbu_count VALUES('data_t1', 3);
           37  +  }
           38  +  return $filename
           39  +}
           40  +
           41  +
           42  +do_execsql_test 1.0 {
           43  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           44  +}
           45  +
           46  +do_test 1.1 {
           47  +  create_rbu1 rbu.db
           48  +  sqlite3rbu rbu test.db rbu.db
           49  +  rbu bp_progress
           50  +} {0 0}
           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}
           59  +
           60  +do_test 1.10 {
           61  +  rbu close
           62  +} {SQLITE_DONE}
           63  +
           64  +#-------------------------------------------------------------------------
           65  +#
           66  +proc do_sp_test {tn bReopen target rbu reslist} {
           67  +  uplevel [list do_test $tn [subst -nocommands {
           68  +    if {$bReopen==0} { sqlite3rbu rbu $target $rbu }
           69  +    set res [list]
           70  +    while 1 {
           71  +      if {$bReopen} { sqlite3rbu rbu $target $rbu }
           72  +      set rc [rbu step]
           73  +      if {[set rc] != "SQLITE_OK"} { rbu close ; error "error 1" }
           74  +      lappend res [lindex [rbu bp_progress] 0]
           75  +      if {[lindex [set res] end]==10000} break
           76  +      if {$bReopen} { rbu close }
           77  +    }
           78  +    if {[set res] != [list $reslist]} {
           79  +      rbu close
           80  +      error "1. reslist incorrect (expect=$reslist got=[set res])"
           81  +    }
           82  +
           83  +    # One step to clean up the temporary tables used to update the only
           84  +    # target table in the rbu database. And one more to move the *-oal 
           85  +    # file to *-wal. After each of these steps, the progress remains
           86  +    # at "10000 0".
           87  +    #
           88  +    if {[lindex [list $reslist] 0]!=-1} {
           89  +      rbu step
           90  +      set res [rbu bp_progress]
           91  +      if {[set res] != [list 10000 0]} {
           92  +        rbu close
           93  +        error "2. reslist incorrect (expect=10000 0 got=[set res])"
           94  +      }
           95  +    }
           96  +
           97  +    rbu step
           98  +    set res [rbu bp_progress]
           99  +    if {[set res] != [list 10000 0]} {
          100  +      rbu close
          101  +      error "3. reslist incorrect (expect=10000 0 got=[set res])"
          102  +    }
          103  +
          104  +    # Do the checkpoint.
          105  +    while {[rbu step]=="SQLITE_OK"} { 
          106  +      foreach {a b} [rbu bp_progress] {}
          107  +      if {[set a]!=10000 || [set b]<=0 || [set b]>10000} {
          108  +        rbu close
          109  +        error "4. reslist incorrect (expect=10000 1..10000 got=[set a] [set b])"
          110  +      }
          111  +    }
          112  +
          113  +    set res [rbu bp_progress]
          114  +    if {[set res] != [list 10000 10000]} {
          115  +      rbu close
          116  +      error "5. reslist is incorrect (expect=10000 10000 got=[set res])"
          117  +    }
          118  +
          119  +    rbu close
          120  +  }] {SQLITE_DONE}]
          121  +}
          122  +
          123  +foreach {bReopen} { 0 1 } {
          124  +  reset_db
          125  +  do_test 2.$bReopen.1.0 {
          126  +    execsql {
          127  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          128  +    }
          129  +    create_db_file rbu.db {
          130  +      CREATE TABLE data_t1(a, b, c, rbu_control);
          131  +      INSERT INTO data_t1 VALUES(4, 4, 4, 0);
          132  +      INSERT INTO data_t1 VALUES(5, 5, 5, 0);
          133  +  
          134  +      CREATE TABLE rbu_count(tbl, cnt);
          135  +      INSERT INTO rbu_count VALUES('data_t1', 2);
          136  +    }
          137  +  } {}
          138  +  do_sp_test 2.$bReopen.1.1 $bReopen test.db rbu.db {5000 10000}
          139  +  
          140  +  reset_db
          141  +  do_test 2.$bReopen.2.0 {
          142  +    execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) }
          143  +    create_rbu1 rbu.db
          144  +  } {rbu.db}
          145  +  do_sp_test 2.$bReopen.2.1 $bReopen test.db rbu.db {3333 6666 10000}
          146  +  
          147  +  reset_db
          148  +  do_test 2.$bReopen.3.0 {
          149  +    execsql { 
          150  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          151  +      CREATE INDEX i1 ON t1(b);
          152  +      INSERT INTO t1 VALUES(1, 1, 1);
          153  +      INSERT INTO t1 VALUES(2, 2, 2);
          154  +      INSERT INTO t1 VALUES(3, 3, 3);
          155  +    }
          156  +    create_db_file rbu.db {
          157  +      CREATE TABLE data_t1(a, b, c, rbu_control);
          158  +      INSERT INTO data_t1 VALUES(4, 4, 4, 0);
          159  +      INSERT INTO data_t1 VALUES(2, NULL, NULL, 1);
          160  +      INSERT INTO data_t1 VALUES(5, NULL, NULL, 1);
          161  +  
          162  +      CREATE TABLE rbu_count(tbl, cnt);
          163  +      INSERT INTO rbu_count VALUES('data_t1', 3);
          164  +    }
          165  +  } {}
          166  +  do_sp_test 2.$bReopen.3.1 $bReopen test.db rbu.db {1666 3333 6000 8000 10000}
          167  +  
          168  +  reset_db
          169  +  do_test 2.$bReopen.4.0 {
          170  +    execsql { 
          171  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          172  +      CREATE INDEX i1 ON t1(b);
          173  +      INSERT INTO t1 VALUES(1, 1, 1);
          174  +      INSERT INTO t1 VALUES(2, 2, 2);
          175  +      INSERT INTO t1 VALUES(3, 3, 3);
          176  +    }
          177  +    create_db_file rbu.db {
          178  +      CREATE TABLE data_t1(a, b, c, rbu_control);
          179  +      INSERT INTO data_t1 VALUES(2, 4, 4, '.xx');
          180  +  
          181  +      CREATE TABLE rbu_count(tbl, cnt);
          182  +      INSERT INTO rbu_count VALUES('data_t1', 1);
          183  +    }
          184  +  } {}
          185  +  do_sp_test 2.$bReopen.4.1 $bReopen test.db rbu.db {3333 6666 10000}
          186  +  
          187  +  reset_db
          188  +  do_test 2.$bReopen.5.0 {
          189  +    execsql { 
          190  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          191  +      CREATE INDEX i1 ON t1(b);
          192  +      INSERT INTO t1 VALUES(1, 1, 1);
          193  +      INSERT INTO t1 VALUES(2, 2, 2);
          194  +      INSERT INTO t1 VALUES(3, 3, 3);
          195  +    }
          196  +    create_db_file rbu.db {
          197  +      CREATE TABLE data_t1(a, b, c, rbu_control);
          198  +      INSERT INTO data_t1 VALUES(4, NULL, 4, '.xx');
          199  +  
          200  +      CREATE TABLE rbu_count(tbl, cnt);
          201  +      INSERT INTO rbu_count VALUES('data_t1', 1);
          202  +    }
          203  +  } {}
          204  +  do_sp_test 2.$bReopen.5.1 $bReopen test.db rbu.db {10000}
          205  +
          206  +  reset_db
          207  +  do_test 2.$bReopen.6.0 {
          208  +    execsql { 
          209  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          210  +      CREATE INDEX i1 ON t1(b);
          211  +      INSERT INTO t1 VALUES(1, 1, 1);
          212  +      INSERT INTO t1 VALUES(2, 2, 2);
          213  +      INSERT INTO t1 VALUES(3, 3, 3);
          214  +    }
          215  +    create_db_file rbu.db {
          216  +      CREATE TABLE data_t1(a, b, c, rbu_control);
          217  +      INSERT INTO data_t1 VALUES(4, 4, 4, 0);
          218  +      INSERT INTO data_t1 VALUES(2, NULL, NULL, 1);
          219  +      INSERT INTO data_t1 VALUES(5, NULL, NULL, 1);
          220  +    }
          221  +  } {}
          222  +  do_sp_test 2.$bReopen.6.1 $bReopen test.db rbu.db {-1 -1 -1 -1 -1 10000}
          223  +}
          224  +
          225  +#-------------------------------------------------------------------------
          226  +# The following tests verify that the API works when resuming an update
          227  +# during the incremental checkpoint stage.
          228  +#
          229  +proc do_phase2_test {tn bReopen target rbu nStep} {
          230  +  uplevel [list do_test $tn [subst -nocommands {
          231  +
          232  +    # Build the OAL/WAL file:
          233  +    sqlite3rbu rbu $target $rbu
          234  +    while {[lindex [rbu bp_progress] 0]<10000} { 
          235  +      set rc [rbu step]
          236  +      if {"SQLITE_OK" != [set rc]} { rbu close }
          237  +    }
          238  +
          239  +    # Clean up the temp tables and move the *-oal file to *-wal.
          240  +    rbu step
          241  +    rbu step
          242  +
          243  +    for {set i 0} {[set i] < $nStep} {incr i} {
          244  +      if {$bReopen} {
          245  +        rbu close
          246  +        sqlite3rbu rbu $target $rbu
          247  +      }
          248  +      rbu step
          249  +      set res [rbu bp_progress]
          250  +      set expect [expr (1 + [set i]) * 10000 / $nStep]
          251  +      if {[lindex [set res] 1] != [set expect]} {
          252  +        error "Have [set res], expected 10000 [set expect]"
          253  +      }
          254  +    }
          255  +
          256  +    set rc [rbu step]
          257  +    if {[set rc] != "SQLITE_DONE"} {
          258  +      error "Have [set rc], expected SQLITE_DONE" 
          259  +    }
          260  +
          261  +    rbu close
          262  +  }] {SQLITE_DONE}]
          263  +}
          264  +
          265  +foreach bReopen {0 1} {
          266  +  do_test 3.$bReopen.1.0 {
          267  +    reset_db
          268  +    execsql {
          269  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          270  +      CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
          271  +      CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
          272  +      CREATE TABLE t4(a INTEGER PRIMARY KEY, b);
          273  +    }
          274  +    create_db_file rbu.db {
          275  +      CREATE TABLE data_t1(a, b, rbu_control);
          276  +      CREATE TABLE data_t2(a, b, rbu_control);
          277  +      CREATE TABLE data_t3(a, b, rbu_control);
          278  +      CREATE TABLE data_t4(a, b, rbu_control);
          279  +      INSERT INTO data_t1 VALUES(1, 2, 0);
          280  +      INSERT INTO data_t2 VALUES(1, 2, 0);
          281  +      INSERT INTO data_t3 VALUES(1, 2, 0);
          282  +      INSERT INTO data_t4 VALUES(1, 2, 0);
          283  +  
          284  +      CREATE TABLE rbu_count(tbl, cnt);
          285  +      INSERT INTO rbu_count VALUES('data_t1', 1);
          286  +      INSERT INTO rbu_count VALUES('data_t2', 1);
          287  +      INSERT INTO rbu_count VALUES('data_t3', 1);
          288  +      INSERT INTO rbu_count VALUES('data_t4', 1);
          289  +    }
          290  +  } {}
          291  +  do_phase2_test 3.$bReopen.1.1 $bReopen test.db rbu.db 5
          292  +}
          293  +
          294  +
          295  +foreach {bReopen} { 0 1 } {
          296  +  foreach {tn tbl} {
          297  +    ipk { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) }
          298  +    wr  { CREATE TABLE t1(a INT PRIMARY KEY, b, c) WITHOUT ROWID }
          299  +    pk  { CREATE TABLE t1(a INT PRIMARY KEY, b, c) }
          300  +  } {
          301  +
          302  +    foreach {tn2 rbusql r1 r3} {
          303  +      1 {
          304  +        CREATE TABLE data0_t1(a, b, c, rbu_control);
          305  +        INSERT INTO data0_t1 VALUES(15, 15, 15, 0);
          306  +        INSERT INTO data0_t1 VALUES(20, 20, 20, 0);
          307  +        CREATE TABLE rbu_count(tbl, cnt);
          308  +        INSERT INTO rbu_count VALUES('data0_t1', 2); 
          309  +      } 
          310  +      {2500 5000 7500 10000}
          311  +      {1666 3333 5000 6666 8333 10000}
          312  +
          313  +      2 {
          314  +        CREATE TABLE data0_t1(a, b, c, rbu_control);
          315  +        INSERT INTO data0_t1 VALUES(10, 10, 10, 2);
          316  +        CREATE TABLE rbu_count(tbl, cnt);
          317  +        INSERT INTO rbu_count VALUES('data0_t1', 1); 
          318  +      } 
          319  +      {3333 6666 10000}
          320  +      {2000 4000 6000 8000 10000}
          321  +
          322  +      3 {
          323  +        CREATE TABLE data0_t1(a, b, c, rbu_control);
          324  +        INSERT INTO data0_t1 VALUES(7, 7, 7, 2);
          325  +        INSERT INTO data0_t1 VALUES(10, 10, 10, 2);
          326  +        CREATE TABLE rbu_count(tbl, cnt);
          327  +        INSERT INTO rbu_count VALUES('data0_t1', 2); 
          328  +      } 
          329  +      {2500 4000 6000 8000 10000}
          330  +      {1666 2500 3750 5000 6250 7500 8750 10000}
          331  +
          332  +    } {
          333  +
          334  +      reset_db ; execsql $tbl
          335  +      do_test 4.$tn.$bReopen.$tn2.0 {
          336  +        execsql {
          337  +          CREATE INDEX t1c ON t1(c);
          338  +          INSERT INTO t1 VALUES(1, 1, 1);
          339  +          INSERT INTO t1 VALUES(5, 5, 5);
          340  +          INSERT INTO t1 VALUES(10, 10, 10);
          341  +        }
          342  +        create_db_file rbu.db $rbusql
          343  +      } {}
          344  +
          345  +      set R(ipk) $r1
          346  +      set R(wr) $r1
          347  +      set R(pk) $r3
          348  +      do_sp_test 4.$tn.$bReopen.$tn2.1 $bReopen test.db rbu.db $R($tn)
          349  +    }
          350  +  }
          351  +}
          352  +
          353  +foreach {bReopen} { 0 1 } {
          354  +  foreach {tn tbl} {
          355  +    nopk { 
          356  +      CREATE TABLE t1(a, b, c);
          357  +      CREATE INDEX t1c ON t1(c);
          358  +    }
          359  +    vtab { 
          360  +      CREATE VIRTUAL TABLE t1 USING fts5(a, b, c);
          361  +    }
          362  +  } {
          363  +
          364  +    foreach {tn2 rbusql r1 r2} {
          365  +      1 {
          366  +        CREATE TABLE data0_t1(a, b, c, rbu_rowid, rbu_control);
          367  +        INSERT INTO data0_t1 VALUES(15, 15, 15, 4, 0);
          368  +        INSERT INTO data0_t1 VALUES(20, 20, 20, 5, 0);
          369  +        CREATE TABLE rbu_count(tbl, cnt);
          370  +        INSERT INTO rbu_count VALUES('data0_t1', 2); 
          371  +      } 
          372  +      {2500 5000 7500 10000}
          373  +      {5000 10000}
          374  +
          375  +      2 {
          376  +        CREATE TABLE data0_t1(rbu_rowid, a, b, c, rbu_control);
          377  +        INSERT INTO data0_t1 VALUES(0, 7, 7, 7, 2);
          378  +        INSERT INTO data0_t1 VALUES(2, 10, 10, 10, 2);
          379  +        CREATE TABLE rbu_count(tbl, cnt);
          380  +        INSERT INTO rbu_count VALUES('data0_t1', 2); 
          381  +      } 
          382  +      {2500 4000 6000 8000 10000}
          383  +      {5000 10000}
          384  +
          385  +      3 {
          386  +        CREATE TABLE data0_t1(rbu_rowid, a, b, c, rbu_control);
          387  +        INSERT INTO data0_t1 VALUES(1, NULL, NULL, NULL, 1);
          388  +        INSERT INTO data0_t1 VALUES(2, NULL, NULL, 7, '..x');
          389  +        CREATE TABLE rbu_count(tbl, cnt);
          390  +        INSERT INTO rbu_count VALUES('data0_t1', 2); 
          391  +      } 
          392  +      {2500 4000 6000 8000 10000}
          393  +      {5000 10000}
          394  +    } {
          395  +
          396  +      reset_db ; execsql $tbl
          397  +      do_test 5.$tn.$bReopen.$tn2.0 {
          398  +        execsql {
          399  +          INSERT INTO t1 VALUES(1, 1, 1);
          400  +          INSERT INTO t1 VALUES(5, 5, 5);
          401  +          INSERT INTO t1 VALUES(10, 10, 10);
          402  +        }
          403  +        create_db_file rbu.db $rbusql
          404  +      } {}
          405  +
          406  +      set R(nopk) $r1
          407  +      set R(vtab) $r2
          408  +      do_sp_test 5.$tn.$bReopen.$tn2.1 $bReopen test.db rbu.db $R($tn)
          409  +    }
          410  +  }
          411  +}
          412  +
          413  +
          414  +finish_test
          415  +

Changes to ext/rbu/sqlite3rbu.c.

   143    143   ** RBU_STATE_COOKIE:
   144    144   **   Valid if STAGE==1. The current change-counter cookie value in the 
   145    145   **   target db file.
   146    146   **
   147    147   ** RBU_STATE_OALSZ:
   148    148   **   Valid if STAGE==1. The size in bytes of the *-oal file.
   149    149   */
   150         -#define RBU_STATE_STAGE       1
   151         -#define RBU_STATE_TBL         2
   152         -#define RBU_STATE_IDX         3
   153         -#define RBU_STATE_ROW         4
   154         -#define RBU_STATE_PROGRESS    5
   155         -#define RBU_STATE_CKPT        6
   156         -#define RBU_STATE_COOKIE      7
   157         -#define RBU_STATE_OALSZ       8
          150  +#define RBU_STATE_STAGE        1
          151  +#define RBU_STATE_TBL          2
          152  +#define RBU_STATE_IDX          3
          153  +#define RBU_STATE_ROW          4
          154  +#define RBU_STATE_PROGRESS     5
          155  +#define RBU_STATE_CKPT         6
          156  +#define RBU_STATE_COOKIE       7
          157  +#define RBU_STATE_OALSZ        8
          158  +#define RBU_STATE_PHASEONESTEP 9
   158    159   
   159    160   #define RBU_STAGE_OAL         1
   160    161   #define RBU_STAGE_MOVE        2
   161    162   #define RBU_STAGE_CAPTURE     3
   162    163   #define RBU_STAGE_CKPT        4
   163    164   #define RBU_STAGE_DONE        5
   164    165   
................................................................................
   196    197     char *zTbl;
   197    198     char *zIdx;
   198    199     i64 iWalCksum;
   199    200     int nRow;
   200    201     i64 nProgress;
   201    202     u32 iCookie;
   202    203     i64 iOalSz;
          204  +  i64 nPhaseOneStep;
   203    205   };
   204    206   
   205    207   struct RbuUpdateStmt {
   206    208     char *zMask;                    /* Copy of update mask used with pUpdate */
   207    209     sqlite3_stmt *pUpdate;          /* Last update statement (or NULL) */
   208    210     RbuUpdateStmt *pNext;
   209    211   };
................................................................................
   240    242     int bCleanup;                   /* True in "cleanup" state */
   241    243     const char *zTbl;               /* Name of target db table */
   242    244     const char *zDataTbl;           /* Name of rbu db table (or null) */
   243    245     const char *zIdx;               /* Name of target db index (or null) */
   244    246     int iTnum;                      /* Root page of current object */
   245    247     int iPkTnum;                    /* If eType==EXTERNAL, root of PK index */
   246    248     int bUnique;                    /* Current index is unique */
          249  +  int nIndex;                     /* Number of aux. indexes on table zTbl */
   247    250   
   248    251     /* Statements created by rbuObjIterPrepareAll() */
   249    252     int nCol;                       /* Number of columns in current object */
   250    253     sqlite3_stmt *pSelect;          /* Source data */
   251    254     sqlite3_stmt *pInsert;          /* Statement for INSERT operations */
   252    255     sqlite3_stmt *pDelete;          /* Statement for DELETE ops */
   253    256     sqlite3_stmt *pTmpInsert;       /* Insert into rbu_tmp_$zDataTbl */
................................................................................
   293    296   struct RbuFrame {
   294    297     u32 iDbPage;
   295    298     u32 iWalFrame;
   296    299   };
   297    300   
   298    301   /*
   299    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.
   300    340   */
   301    341   struct sqlite3rbu {
   302    342     int eStage;                     /* Value of RBU_STATE_STAGE field */
   303    343     sqlite3 *dbMain;                /* target database handle */
   304    344     sqlite3 *dbRbu;                 /* rbu database handle */
   305    345     char *zTarget;                  /* Path to target db */
   306    346     char *zRbu;                     /* Path to rbu db */
................................................................................
   310    350     char *zErrmsg;                  /* Error message if rc!=SQLITE_OK */
   311    351     int nStep;                      /* Rows processed for current object */
   312    352     int nProgress;                  /* Rows processed for all objects */
   313    353     RbuObjIter objiter;             /* Iterator for skipping through tbl/idx */
   314    354     const char *zVfsName;           /* Name of automatically created rbu vfs */
   315    355     rbu_file *pTargetFd;            /* File handle open on target db */
   316    356     i64 iOalSz;
          357  +  i64 nPhaseOneStep;
   317    358   
   318    359     /* The following state variables are used as part of the incremental
   319    360     ** checkpoint stage (eStage==RBU_STAGE_CKPT). See comments surrounding
   320    361     ** function rbuSetupCheckpoint() for details.  */
   321    362     u32 iMaxFrame;                  /* Largest iWalFrame value in aFrame[] */
   322    363     u32 mLock;
   323    364     int nFrame;                     /* Entries in aFrame[] array */
................................................................................
  1140   1181     if( p->rc==SQLITE_OK ){
  1141   1182       memcpy(pIter->abIndexed, pIter->abTblPk, sizeof(u8)*pIter->nTblCol);
  1142   1183       p->rc = prepareFreeAndCollectError(p->dbMain, &pList, &p->zErrmsg,
  1143   1184           sqlite3_mprintf("PRAGMA main.index_list = %Q", pIter->zTbl)
  1144   1185       );
  1145   1186     }
  1146   1187   
         1188  +  pIter->nIndex = 0;
  1147   1189     while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pList) ){
  1148   1190       const char *zIdx = (const char*)sqlite3_column_text(pList, 1);
  1149   1191       sqlite3_stmt *pXInfo = 0;
  1150   1192       if( zIdx==0 ) break;
  1151   1193       p->rc = prepareFreeAndCollectError(p->dbMain, &pXInfo, &p->zErrmsg,
  1152   1194           sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", zIdx)
  1153   1195       );
  1154   1196       while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){
  1155   1197         int iCid = sqlite3_column_int(pXInfo, 1);
  1156   1198         if( iCid>=0 ) pIter->abIndexed[iCid] = 1;
  1157   1199       }
  1158   1200       rbuFinalize(p, pXInfo);
  1159   1201       bIndex = 1;
         1202  +    pIter->nIndex++;
         1203  +  }
         1204  +
         1205  +  if( pIter->eType==RBU_PK_WITHOUT_ROWID ){
         1206  +    /* "PRAGMA index_list" includes the main PK b-tree */
         1207  +    pIter->nIndex--;
  1160   1208     }
  1161   1209   
  1162   1210     rbuFinalize(p, pList);
  1163   1211     if( bIndex==0 ) pIter->abIndexed = 0;
  1164   1212   }
  1165   1213   
  1166   1214   
................................................................................
  1266   1314           iOrder++;
  1267   1315         }
  1268   1316       }
  1269   1317   
  1270   1318       rbuFinalize(p, pStmt);
  1271   1319       rbuObjIterCacheIndexedCols(p, pIter);
  1272   1320       assert( pIter->eType!=RBU_PK_VTAB || pIter->abIndexed==0 );
         1321  +    assert( pIter->eType!=RBU_PK_VTAB || pIter->nIndex==0 );
  1273   1322     }
  1274   1323   
  1275   1324     return p->rc;
  1276   1325   }
  1277   1326   
  1278   1327   /*
  1279   1328   ** This function constructs and returns a pointer to a nul-terminated 
................................................................................
  1818   1867     sqlite3_context *pCtx, 
  1819   1868     int nVal,
  1820   1869     sqlite3_value **apVal
  1821   1870   ){
  1822   1871     sqlite3rbu *p = sqlite3_user_data(pCtx);
  1823   1872     int rc = SQLITE_OK;
  1824   1873     int i;
         1874  +
         1875  +  assert( sqlite3_value_int(apVal[0])!=0
         1876  +      || p->objiter.eType==RBU_PK_EXTERNAL 
         1877  +      || p->objiter.eType==RBU_PK_NONE 
         1878  +  );
         1879  +  if( sqlite3_value_int(apVal[0])!=0 ){
         1880  +    p->nPhaseOneStep += p->objiter.nIndex;
         1881  +  }
  1825   1882   
  1826   1883     for(i=0; rc==SQLITE_OK && i<nVal; i++){
  1827   1884       rc = sqlite3_bind_value(p->objiter.pTmpInsert, i+1, apVal[i]);
  1828   1885     }
  1829   1886     if( rc==SQLITE_OK ){
  1830   1887       sqlite3_step(p->objiter.pTmpInsert);
  1831   1888       rc = sqlite3_reset(p->objiter.pTmpInsert);
................................................................................
  2563   2620     RbuObjIter *pIter = &p->objiter;
  2564   2621     sqlite3_value *pVal;
  2565   2622     sqlite3_stmt *pWriter;
  2566   2623     int i;
  2567   2624   
  2568   2625     assert( p->rc==SQLITE_OK );
  2569   2626     assert( eType!=RBU_DELETE || pIter->zIdx==0 );
         2627  +  assert( eType==RBU_DELETE || eType==RBU_IDX_DELETE
         2628  +       || eType==RBU_INSERT || eType==RBU_IDX_INSERT
         2629  +  );
         2630  +
         2631  +  /* If this is a delete, decrement nPhaseOneStep by nIndex. If the DELETE
         2632  +  ** statement below does actually delete a row, nPhaseOneStep will be
         2633  +  ** incremented by the same amount when SQL function rbu_tmp_insert()
         2634  +  ** is invoked by the trigger.  */
         2635  +  if( eType==RBU_DELETE ){
         2636  +    p->nPhaseOneStep -= p->objiter.nIndex;
         2637  +  }
  2570   2638   
  2571   2639     if( eType==RBU_IDX_DELETE || eType==RBU_DELETE ){
  2572   2640       pWriter = pIter->pDelete;
  2573   2641     }else{
  2574   2642       pWriter = pIter->pInsert;
  2575   2643     }
  2576   2644   
................................................................................
  2634   2702     if( eType ){
  2635   2703       assert( eType==RBU_INSERT     || eType==RBU_DELETE
  2636   2704            || eType==RBU_REPLACE    || eType==RBU_IDX_DELETE
  2637   2705            || eType==RBU_IDX_INSERT || eType==RBU_UPDATE
  2638   2706       );
  2639   2707       assert( eType!=RBU_UPDATE || pIter->zIdx==0 );
  2640   2708   
  2641         -    if( pIter->zIdx==0 && eType==RBU_IDX_DELETE ){
         2709  +    if( pIter->zIdx==0 && (eType==RBU_IDX_DELETE || eType==RBU_IDX_INSERT) ){
  2642   2710         rbuBadControlError(p);
  2643   2711       }
  2644   2712       else if( eType==RBU_REPLACE ){
  2645         -      if( pIter->zIdx==0 ) rbuStepOneOp(p, RBU_DELETE);
         2713  +      if( pIter->zIdx==0 ){
         2714  +        p->nPhaseOneStep += p->objiter.nIndex;
         2715  +        rbuStepOneOp(p, RBU_DELETE);
         2716  +      }
  2646   2717         if( p->rc==SQLITE_OK ) rbuStepOneOp(p, RBU_INSERT);
  2647   2718       }
  2648   2719       else if( eType!=RBU_UPDATE ){
  2649   2720         rbuStepOneOp(p, eType);
  2650   2721       }
  2651   2722       else{
  2652   2723         sqlite3_value *pVal;
  2653   2724         sqlite3_stmt *pUpdate = 0;
  2654   2725         assert( eType==RBU_UPDATE );
         2726  +      p->nPhaseOneStep -= p->objiter.nIndex;
  2655   2727         rbuGetUpdateStmt(p, pIter, zMask, &pUpdate);
  2656   2728         if( pUpdate ){
  2657   2729           int i;
  2658   2730           for(i=0; p->rc==SQLITE_OK && i<pIter->nCol; i++){
  2659   2731             char c = zMask[pIter->aiSrcOrder[i]];
  2660   2732             pVal = sqlite3_column_value(pIter->pSelect, i);
  2661   2733             if( pIter->abTblPk[i] || c!='.' ){
................................................................................
  2725   2797             "(%d, %d), "
  2726   2798             "(%d, %Q), "
  2727   2799             "(%d, %Q), "
  2728   2800             "(%d, %d), "
  2729   2801             "(%d, %d), "
  2730   2802             "(%d, %lld), "
  2731   2803             "(%d, %lld), "
         2804  +          "(%d, %lld), "
  2732   2805             "(%d, %lld) ",
  2733   2806             p->zStateDb,
  2734   2807             RBU_STATE_STAGE, eStage,
  2735   2808             RBU_STATE_TBL, p->objiter.zTbl, 
  2736   2809             RBU_STATE_IDX, p->objiter.zIdx, 
  2737   2810             RBU_STATE_ROW, p->nStep, 
  2738   2811             RBU_STATE_PROGRESS, p->nProgress,
  2739   2812             RBU_STATE_CKPT, p->iWalCksum,
  2740   2813             RBU_STATE_COOKIE, (i64)p->pTargetFd->iCookie,
  2741         -          RBU_STATE_OALSZ, p->iOalSz
         2814  +          RBU_STATE_OALSZ, p->iOalSz,
         2815  +          RBU_STATE_PHASEONESTEP, p->nPhaseOneStep
  2742   2816         )
  2743   2817       );
  2744   2818       assert( pInsert==0 || rc==SQLITE_OK );
  2745   2819   
  2746   2820       if( rc==SQLITE_OK ){
  2747   2821         sqlite3_step(pInsert);
  2748   2822         rc = sqlite3_finalize(pInsert);
................................................................................
  2921   2995           pRet->iCookie = (u32)sqlite3_column_int64(pStmt, 1);
  2922   2996           break;
  2923   2997   
  2924   2998         case RBU_STATE_OALSZ:
  2925   2999           pRet->iOalSz = (u32)sqlite3_column_int64(pStmt, 1);
  2926   3000           break;
  2927   3001   
         3002  +      case RBU_STATE_PHASEONESTEP:
         3003  +        pRet->nPhaseOneStep = sqlite3_column_int64(pStmt, 1);
         3004  +        break;
         3005  +
  2928   3006         default:
  2929   3007           rc = SQLITE_CORRUPT;
  2930   3008           break;
  2931   3009       }
  2932   3010     }
  2933   3011     rc2 = sqlite3_finalize(pStmt);
  2934   3012     if( rc==SQLITE_OK ) rc = rc2;
................................................................................
  3027   3105   */
  3028   3106   static void rbuDeleteVfs(sqlite3rbu *p){
  3029   3107     if( p->zVfsName ){
  3030   3108       sqlite3rbu_destroy_vfs(p->zVfsName);
  3031   3109       p->zVfsName = 0;
  3032   3110     }
  3033   3111   }
         3112  +
         3113  +/*
         3114  +** This user-defined SQL function is invoked with a single argument - the
         3115  +** name of a table expected to appear in the target database. It returns
         3116  +** the number of auxilliary indexes on the table.
         3117  +*/
         3118  +static void rbuIndexCntFunc(
         3119  +  sqlite3_context *pCtx, 
         3120  +  int nVal,
         3121  +  sqlite3_value **apVal
         3122  +){
         3123  +  sqlite3rbu *p = (sqlite3rbu*)sqlite3_user_data(pCtx);
         3124  +  sqlite3_stmt *pStmt = 0;
         3125  +  char *zErrmsg = 0;
         3126  +  int rc;
         3127  +
         3128  +  assert( nVal==1 );
         3129  +  
         3130  +  rc = prepareFreeAndCollectError(p->dbMain, &pStmt, &zErrmsg, 
         3131  +      sqlite3_mprintf("SELECT count(*) FROM sqlite_master "
         3132  +        "WHERE type='index' AND tbl_name = %Q", sqlite3_value_text(apVal[0]))
         3133  +  );
         3134  +  if( rc!=SQLITE_OK ){
         3135  +    sqlite3_result_error(pCtx, zErrmsg, -1);
         3136  +  }else{
         3137  +    int nIndex = 0;
         3138  +    if( SQLITE_ROW==sqlite3_step(pStmt) ){
         3139  +      nIndex = sqlite3_column_int(pStmt, 0);
         3140  +    }
         3141  +    rc = sqlite3_finalize(pStmt);
         3142  +    if( rc==SQLITE_OK ){
         3143  +      sqlite3_result_int(pCtx, nIndex);
         3144  +    }else{
         3145  +      sqlite3_result_error(pCtx, sqlite3_errmsg(p->dbMain), -1);
         3146  +    }
         3147  +  }
         3148  +
         3149  +  sqlite3_free(zErrmsg);
         3150  +}
         3151  +
         3152  +/*
         3153  +** If the RBU database contains the rbu_count table, use it to initialize
         3154  +** the sqlite3rbu.nPhaseOneStep variable. The schema of the rbu_count table
         3155  +** is assumed to contain the same columns as:
         3156  +**
         3157  +**   CREATE TABLE rbu_count(tbl TEXT PRIMARY KEY, cnt INTEGER) WITHOUT ROWID;
         3158  +**
         3159  +** There should be one row in the table for each data_xxx table in the
         3160  +** database. The 'tbl' column should contain the name of a data_xxx table,
         3161  +** and the cnt column the number of rows it contains.
         3162  +**
         3163  +** sqlite3rbu.nPhaseOneStep is initialized to the sum of (1 + nIndex) * cnt
         3164  +** for all rows in the rbu_count table, where nIndex is the number of 
         3165  +** indexes on the corresponding target database table.
         3166  +*/
         3167  +static void rbuInitPhaseOneSteps(sqlite3rbu *p){
         3168  +  if( p->rc==SQLITE_OK ){
         3169  +    sqlite3_stmt *pStmt = 0;
         3170  +    int bExists = 0;                /* True if rbu_count exists */
         3171  +
         3172  +    p->nPhaseOneStep = -1;
         3173  +
         3174  +    p->rc = sqlite3_create_function(p->dbRbu, 
         3175  +        "rbu_index_cnt", 1, SQLITE_UTF8, (void*)p, rbuIndexCntFunc, 0, 0
         3176  +    );
         3177  +  
         3178  +    /* Check for the rbu_count table. If it does not exist, or if an error
         3179  +    ** occurs, nPhaseOneStep will be left set to -1. */
         3180  +    if( p->rc==SQLITE_OK ){
         3181  +      p->rc = prepareAndCollectError(p->dbRbu, &pStmt, &p->zErrmsg,
         3182  +          "SELECT 1 FROM sqlite_master WHERE tbl_name = 'rbu_count'"
         3183  +      );
         3184  +    }
         3185  +    if( p->rc==SQLITE_OK ){
         3186  +      if( SQLITE_ROW==sqlite3_step(pStmt) ){
         3187  +        bExists = 1;
         3188  +      }
         3189  +      p->rc = sqlite3_finalize(pStmt);
         3190  +    }
         3191  +  
         3192  +    if( p->rc==SQLITE_OK && bExists ){
         3193  +      p->rc = prepareAndCollectError(p->dbRbu, &pStmt, &p->zErrmsg,
         3194  +          "SELECT sum(cnt * (1 + rbu_index_cnt(rbu_target_name(tbl))))"
         3195  +          "FROM rbu_count"
         3196  +      );
         3197  +      if( p->rc==SQLITE_OK ){
         3198  +        if( SQLITE_ROW==sqlite3_step(pStmt) ){
         3199  +          p->nPhaseOneStep = sqlite3_column_int64(pStmt, 0);
         3200  +        }
         3201  +        p->rc = sqlite3_finalize(pStmt);
         3202  +      }
         3203  +    }
         3204  +  }
         3205  +}
  3034   3206   
  3035   3207   /*
  3036   3208   ** Open and return a new RBU handle. 
  3037   3209   */
  3038   3210   sqlite3rbu *sqlite3rbu_open(
  3039   3211     const char *zTarget, 
  3040   3212     const char *zRbu,
................................................................................
  3073   3245       if( p->rc==SQLITE_OK ){
  3074   3246         pState = rbuLoadState(p);
  3075   3247         assert( pState || p->rc!=SQLITE_OK );
  3076   3248         if( p->rc==SQLITE_OK ){
  3077   3249   
  3078   3250           if( pState->eStage==0 ){ 
  3079   3251             rbuDeleteOalFile(p);
         3252  +          rbuInitPhaseOneSteps(p);
  3080   3253             p->eStage = RBU_STAGE_OAL;
  3081   3254           }else{
  3082   3255             p->eStage = pState->eStage;
         3256  +          p->nPhaseOneStep = pState->nPhaseOneStep;
  3083   3257           }
  3084   3258           p->nProgress = pState->nProgress;
  3085   3259           p->iOalSz = pState->iOalSz;
  3086   3260         }
  3087   3261       }
  3088   3262       assert( p->rc!=SQLITE_OK || p->eStage!=0 );
  3089   3263   
................................................................................
  3238   3412   ** Return the total number of key-value operations (inserts, deletes or 
  3239   3413   ** updates) that have been performed on the target database since the
  3240   3414   ** current RBU update was started.
  3241   3415   */
  3242   3416   sqlite3_int64 sqlite3rbu_progress(sqlite3rbu *pRbu){
  3243   3417     return pRbu->nProgress;
  3244   3418   }
         3419  +
         3420  +/*
         3421  +** Return permyriadage progress indications for the two main stages of
         3422  +** an RBU update.
         3423  +*/
         3424  +void sqlite3rbu_bp_progress(sqlite3rbu *p, int *pnOne, int *pnTwo){
         3425  +  const int MAX_PROGRESS = 10000;
         3426  +  switch( p->eStage ){
         3427  +    case RBU_STAGE_OAL:
         3428  +      if( p->nPhaseOneStep>0 ){
         3429  +        *pnOne = (int)(MAX_PROGRESS * (i64)p->nProgress/(i64)p->nPhaseOneStep);
         3430  +      }else{
         3431  +        *pnOne = -1;
         3432  +      }
         3433  +      *pnTwo = 0;
         3434  +      break;
         3435  +
         3436  +    case RBU_STAGE_MOVE:
         3437  +      *pnOne = MAX_PROGRESS;
         3438  +      *pnTwo = 0;
         3439  +      break;
         3440  +
         3441  +    case RBU_STAGE_CKPT:
         3442  +      *pnOne = MAX_PROGRESS;
         3443  +      *pnTwo = (int)(MAX_PROGRESS * (i64)p->nStep / (i64)p->nFrame);
         3444  +      break;
         3445  +
         3446  +    case RBU_STAGE_DONE:
         3447  +      *pnOne = MAX_PROGRESS;
         3448  +      *pnTwo = MAX_PROGRESS;
         3449  +      break;
         3450  +
         3451  +    default:
         3452  +      assert( 0 );
         3453  +  }
         3454  +}
  3245   3455   
  3246   3456   int sqlite3rbu_savestate(sqlite3rbu *p){
  3247   3457     int rc = p->rc;
  3248   3458     
  3249   3459     if( rc==SQLITE_DONE ) return SQLITE_OK;
  3250   3460   
  3251   3461     assert( p->eStage>=RBU_STAGE_OAL && p->eStage<=RBU_STAGE_DONE );

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  +/*
          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);
          444  +
   403    445   /*
   404    446   ** Create an RBU VFS named zName that accesses the underlying file-system
   405    447   ** via existing VFS zParent. Or, if the zParent parameter is passed NULL, 
   406    448   ** then the new RBU VFS uses the default system VFS to access the file-system.
   407    449   ** The new object is registered as a non-default VFS with SQLite before 
   408    450   ** returning.
   409    451   **

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  +    {"bp_progress", 2, ""},    /* 5 */
    69     70       {0,0,0}
    70     71     };
    71     72     int iCmd;
    72     73   
    73     74     if( objc<2 ){
    74     75       Tcl_WrongNumArgs(interp, 1, objv, "METHOD");
    75     76       return TCL_ERROR;
................................................................................
   131    132         int rc = sqlite3_exec(db, Tcl_GetString(objv[2]), 0, 0, 0);
   132    133         if( rc!=SQLITE_OK ){
   133    134           Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3_errmsg(db), -1));
   134    135           ret = TCL_ERROR;
   135    136         }
   136    137         break;
   137    138       }
          139  +
          140  +    case 5: /* bp_progress */ {
          141  +      int one, two;
          142  +      Tcl_Obj *pObj;
          143  +      sqlite3rbu_bp_progress(pRbu, &one, &two);
          144  +
          145  +      pObj = Tcl_NewObj();
          146  +      Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(one));
          147  +      Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(two));
          148  +      Tcl_SetObjResult(interp, pObj);
          149  +      break;
          150  +    }
   138    151   
   139    152       default: /* seems unlikely */
   140    153         assert( !"cannot happen" );
   141    154         break;
   142    155     }
   143    156   
   144    157     return ret;

Changes to tool/sqldiff.c.

  1240   1240     char **azCol;                   /* NULL terminated array of col names */
  1241   1241     int i;
  1242   1242     int nCol;
  1243   1243     Str ct = {0, 0, 0};             /* The "CREATE TABLE data_xxx" statement */
  1244   1244     Str sql = {0, 0, 0};            /* Query to find differences */
  1245   1245     Str insert = {0, 0, 0};         /* First part of output INSERT statement */
  1246   1246     sqlite3_stmt *pStmt = 0;
         1247  +  int nRow = 0;                   /* Total rows in data_xxx table */
  1247   1248   
  1248   1249     /* --rbu mode must use real primary keys. */
  1249   1250     g.bSchemaPK = 1;
  1250   1251   
  1251   1252     /* Check that the schemas of the two tables match. Exit early otherwise. */
  1252   1253     checkSchemasMatch(zTab);
  1253   1254   
................................................................................
  1285   1286       if( ct.z ){
  1286   1287         fprintf(out, "%s\n", ct.z);
  1287   1288         strFree(&ct);
  1288   1289       }
  1289   1290   
  1290   1291       /* Output the first part of the INSERT statement */
  1291   1292       fprintf(out, "%s", insert.z);
         1293  +    nRow++;
  1292   1294   
  1293   1295       if( sqlite3_column_type(pStmt, nCol)==SQLITE_INTEGER ){
  1294   1296         for(i=0; i<=nCol; i++){
  1295   1297           if( i>0 ) fprintf(out, ", ");
  1296   1298           printQuoted(out, sqlite3_column_value(pStmt, i));
  1297   1299         }
  1298   1300       }else{
................................................................................
  1338   1340       }
  1339   1341   
  1340   1342       /* And the closing bracket of the insert statement */
  1341   1343       fprintf(out, ");\n");
  1342   1344     }
  1343   1345   
  1344   1346     sqlite3_finalize(pStmt);
         1347  +  if( nRow>0 ){
         1348  +    Str cnt = {0, 0, 0};
         1349  +    strPrintf(&cnt, "INSERT INTO rbu_count VALUES('data_%q', %d);", zTab, nRow);
         1350  +    fprintf(out, "%s\n", cnt.z);
         1351  +    strFree(&cnt);
         1352  +  }
  1345   1353   
  1346   1354     strFree(&ct);
  1347   1355     strFree(&sql);
  1348   1356     strFree(&insert);
  1349   1357   }
  1350   1358   
  1351   1359   /*
................................................................................
  1852   1860     }
  1853   1861     rc = sqlite3_exec(g.db, "SELECT * FROM aux.sqlite_master", 0, 0, &zErrMsg);
  1854   1862     if( rc || zErrMsg ){
  1855   1863       cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb2);
  1856   1864     }
  1857   1865   
  1858   1866     if( neverUseTransaction ) useTransaction = 0;
  1859         -  if( useTransaction ) printf("BEGIN TRANSACTION;\n");
         1867  +  if( useTransaction ) fprintf(out, "BEGIN TRANSACTION;\n");
         1868  +  if( xDiff==rbudiff_one_table ){
         1869  +    fprintf(out, "CREATE TABLE IF NOT EXISTS rbu_count"
         1870  +           "(tbl TEXT PRIMARY KEY COLLATE NOCASE, cnt INTEGER) "
         1871  +           "WITHOUT ROWID;\n"
         1872  +    );
         1873  +  }
  1860   1874     if( zTab ){
  1861   1875       xDiff(zTab, out);
  1862   1876     }else{
  1863   1877       /* Handle tables one by one */
  1864   1878       pStmt = db_prepare(
  1865   1879         "SELECT name FROM main.sqlite_master\n"
  1866   1880         " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"