/ Check-in [7dd48c10]
Login

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

Overview
Comment:Update the RBU vacuum code so that databases that use custom collation sequences can be vacuumed.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:7dd48c10790a7b9c4165214399c261a0aa701297
User & Date: dan 2016-04-25 19:25:12
Context
2016-04-26
13:13
Fix the "checksymbols" target in Makefile.in to be able to deal with the sqlite3changegroup family of interfaces. check-in: d819bfbd user: drh tags: trunk
2016-04-25
19:25
Update the RBU vacuum code so that databases that use custom collation sequences can be vacuumed. check-in: 7dd48c10 user: dan tags: trunk
02:20
When checking for the WHERE-clause push-down optimization, verify that all terms of the compound inner SELECT are non-aggregate, not just the last term. Fix for ticket [f7f8c97e97597]. check-in: ec215f94 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rbu/rbuvacuum.test.

   251    251         execsql "
   252    252           INSERT INTO t$i VALUES(1, 2, 3);
   253    253           INSERT INTO t$i VALUES(4, 5, 6);
   254    254         "
   255    255       }
   256    256     } {}
   257    257     do_rbu_vacuum_test 1.10.2 $step
          258  +
          259  +  # Database with empty tables.
          260  +  #
          261  +  reset_db
          262  +  do_execsql_test 1.11.1 {
          263  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          264  +    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
          265  +    CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
          266  +    CREATE TABLE t4(a INTEGER PRIMARY KEY, b);
          267  +    INSERT INTO t4 VALUES(1, 2);
          268  +  }
          269  +  do_rbu_vacuum_test 1.11.2 $step
          270  +  do_execsql_test 1.11.3 {
          271  +    SELECT * FROM t1;
          272  +    SELECT * FROM t2;
          273  +    SELECT * FROM t3;
          274  +    SELECT * FROM t4;
          275  +  } {1 2}
          276  +  reset_db
          277  +  do_execsql_test 1.12.1 {
          278  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          279  +    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
          280  +    CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
          281  +    CREATE TABLE t4(a INTEGER PRIMARY KEY, b);
          282  +    INSERT INTO t1 VALUES(1, 2);
          283  +  }
          284  +  do_rbu_vacuum_test 1.12.2 $step
          285  +  do_execsql_test 1.12.3 {
          286  +    SELECT * FROM t1;
          287  +    SELECT * FROM t2;
          288  +    SELECT * FROM t3;
          289  +    SELECT * FROM t4;
          290  +  } {1 2}
   258    291   }
          292  +set ::testprefix rbuvacuum
   259    293   
   260    294   #-------------------------------------------------------------------------
   261    295   # Test some error cases:
   262    296   #
   263    297   #   2.1.* the db being vacuumed being in wal mode already.
   264    298   #   2.2.* database modified mid vacuum.
   265    299   #
................................................................................
   305    339       sqlite3rbu_vacuum rbu test.db state.db 
   306    340       rbu step
   307    341     } {SQLITE_BUSY}
   308    342     do_test 2.2.$i.2 {
   309    343       list [catch { rbu close } msg] $msg
   310    344     } {1 {SQLITE_BUSY - database modified during rbu vacuum}}
   311    345   }
          346  +
          347  +#-------------------------------------------------------------------------
          348  +# Test that a database that uses custom collation sequences can be RBU
          349  +# vacuumed.
          350  +#
          351  +reset_db
          352  +forcedelete state.db
          353  +proc noop {args} {}
          354  +proc length_cmp {x y} {
          355  +  set n1 [string length $x]
          356  +  set n2 [string length $y]
          357  +  return [expr $n1 - $n2]
          358  +}
          359  +sqlite3_create_collation_v2 db length length_cmp noop
          360  +
          361  +do_execsql_test 3.0 {
          362  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          363  +  INSERT INTO t1 VALUES(1, 'i');
          364  +  INSERT INTO t1 VALUES(2, 'iiii');
          365  +  INSERT INTO t1 VALUES(3, 'ii');
          366  +  INSERT INTO t1 VALUES(4, 'iii');
          367  +  SELECT a FROM t1 ORDER BY b COLLATE length;
          368  +} {1 3 4 2}
          369  +do_execsql_test 3.1 {
          370  +  CREATE INDEX i1 ON t1(b COLLATE length);
          371  +}
          372  +
          373  +do_test 3.2 {
          374  +  sqlite3rbu_vacuum rbu test.db state.db
          375  +  while {[rbu step]=="SQLITE_OK"} {}
          376  +  list [catch { rbu close } msg] $msg
          377  +} {1 {SQLITE_ERROR - no such collation sequence: length}}
          378  +
          379  +do_test 3.3 {
          380  +  sqlite3rbu_vacuum rbu test.db state.db
          381  +  set db1 [rbu db 0]
          382  +  sqlite3_create_collation_v2 $db1 length length_cmp noop
          383  +  while {[rbu step]=="SQLITE_OK"} {}
          384  +  list [catch { rbu close } msg] $msg
          385  +} {1 {SQLITE_ERROR - no such collation sequence: length}}
          386  +
          387  +do_test 3.4 {
          388  +  sqlite3rbu_vacuum rbu test.db state.db
          389  +  set db1 [rbu db 1]
          390  +  sqlite3_create_collation_v2 $db1 length length_cmp noop
          391  +  while {[rbu step]=="SQLITE_OK"} {}
          392  +  list [catch { rbu close } msg] $msg
          393  +} {1 {SQLITE_ERROR - no such collation sequence: length}}
          394  +
          395  +do_test 3.5 {
          396  +  sqlite3rbu_vacuum rbu test.db state.db
          397  +  set db1 [rbu db 0]
          398  +  set db2 [rbu db 1]
          399  +
          400  +  sqlite3_create_collation_v2 $db1 length length_cmp noop
          401  +  sqlite3_create_collation_v2 $db2 length length_cmp noop
          402  +
          403  +  while {[rbu step]=="SQLITE_OK"} {}
          404  +  list [catch { rbu close } msg] $msg
          405  +} {0 SQLITE_DONE}
          406  +
   312    407   
   313    408   catch { db close }
   314    409   finish_test
   315    410   

Changes to ext/rbu/sqlite3rbu.c.

  3076   3076         sqlite3_step(pInsert);
  3077   3077         rc = sqlite3_finalize(pInsert);
  3078   3078       }
  3079   3079       if( rc!=SQLITE_OK ) p->rc = rc;
  3080   3080     }
  3081   3081   }
  3082   3082   
         3083  +
         3084  +/*
         3085  +** The second argument passed to this function is the name of a PRAGMA 
         3086  +** setting - "page_size", "auto_vacuum", "user_version" or "application_id".
         3087  +** This function executes the following on sqlite3rbu.dbRbu:
         3088  +**
         3089  +**   "PRAGMA main.$zPragma"
         3090  +**
         3091  +** where $zPragma is the string passed as the second argument, then
         3092  +** on sqlite3rbu.dbMain:
         3093  +**
         3094  +**   "PRAGMA main.$zPragma = $val"
         3095  +**
         3096  +** where $val is the value returned by the first PRAGMA invocation.
         3097  +**
         3098  +** In short, it copies the value  of the specified PRAGMA setting from
         3099  +** dbRbu to dbMain.
         3100  +*/
         3101  +static void rbuCopyPragma(sqlite3rbu *p, const char *zPragma){
         3102  +  if( p->rc==SQLITE_OK ){
         3103  +    sqlite3_stmt *pPragma = 0;
         3104  +    p->rc = prepareFreeAndCollectError(p->dbRbu, &pPragma, &p->zErrmsg, 
         3105  +        sqlite3_mprintf("PRAGMA main.%s", zPragma)
         3106  +    );
         3107  +    if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPragma) ){
         3108  +      p->rc = rbuMPrintfExec(p, p->dbMain, "PRAGMA main.%s = %d",
         3109  +          zPragma, sqlite3_column_int(pPragma, 0)
         3110  +      );
         3111  +    }
         3112  +    rbuFinalize(p, pPragma);
         3113  +  }
         3114  +}
         3115  +
         3116  +/*
         3117  +** The RBU handle passed as the only argument has just been opened and 
         3118  +** the state database is empty. If this RBU handle was opened for an
         3119  +** RBU vacuum operation, create the schema in the target db.
         3120  +*/
         3121  +static void rbuCreateTargetSchema(sqlite3rbu *p){
         3122  +  sqlite3_stmt *pSql = 0;
         3123  +  sqlite3_stmt *pInsert = 0;
         3124  +
         3125  +  assert( rbuIsVacuum(p) );
         3126  +  p->rc = sqlite3_exec(p->dbMain, "PRAGMA writable_schema=1", 0,0, &p->zErrmsg);
         3127  +  if( p->rc==SQLITE_OK ){
         3128  +    p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, 
         3129  +      "SELECT sql FROM sqlite_master WHERE sql!='' AND rootpage!=0"
         3130  +      " AND name!='sqlite_sequence' "
         3131  +      " ORDER BY type DESC"
         3132  +    );
         3133  +  }
         3134  +
         3135  +  while( p->rc==SQLITE_OK && sqlite3_step(pSql)==SQLITE_ROW ){
         3136  +    const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
         3137  +    p->rc = sqlite3_exec(p->dbMain, zSql, 0, 0, &p->zErrmsg);
         3138  +  }
         3139  +  rbuFinalize(p, pSql);
         3140  +  if( p->rc!=SQLITE_OK ) return;
         3141  +
         3142  +  if( p->rc==SQLITE_OK ){
         3143  +    p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, 
         3144  +        "SELECT * FROM sqlite_master WHERE rootpage=0 OR rootpage IS NULL" 
         3145  +    );
         3146  +  }
         3147  +
         3148  +  if( p->rc==SQLITE_OK ){
         3149  +    p->rc = prepareAndCollectError(p->dbMain, &pInsert, &p->zErrmsg, 
         3150  +        "INSERT INTO sqlite_master VALUES(?,?,?,?,?)"
         3151  +    );
         3152  +  }
         3153  +
         3154  +  while( p->rc==SQLITE_OK && sqlite3_step(pSql)==SQLITE_ROW ){
         3155  +    int i;
         3156  +    for(i=0; i<5; i++){
         3157  +      sqlite3_bind_value(pInsert, i+1, sqlite3_column_value(pSql, i));
         3158  +    }
         3159  +    sqlite3_step(pInsert);
         3160  +    p->rc = sqlite3_reset(pInsert);
         3161  +  }
         3162  +  if( p->rc==SQLITE_OK ){
         3163  +    p->rc = sqlite3_exec(p->dbMain, "PRAGMA writable_schema=0",0,0,&p->zErrmsg);
         3164  +  }
         3165  +
         3166  +  rbuFinalize(p, pSql);
         3167  +  rbuFinalize(p, pInsert);
         3168  +}
  3083   3169   
  3084   3170   /*
  3085   3171   ** Step the RBU object.
  3086   3172   */
  3087   3173   int sqlite3rbu_step(sqlite3rbu *p){
  3088   3174     if( p ){
  3089   3175       switch( p->eStage ){
  3090   3176         case RBU_STAGE_OAL: {
  3091   3177           RbuObjIter *pIter = &p->objiter;
         3178  +
         3179  +        /* If this is an RBU vacuum operation and the state table was empty
         3180  +        ** when this handle was opened, create the target database schema. */
         3181  +        if( rbuIsVacuum(p) && p->nProgress==0 && p->rc==SQLITE_OK ){
         3182  +          rbuCreateTargetSchema(p);
         3183  +          rbuCopyPragma(p, "user_version");
         3184  +          rbuCopyPragma(p, "application_id");
         3185  +        }
         3186  +
  3092   3187           while( p->rc==SQLITE_OK && pIter->zTbl ){
  3093   3188   
  3094   3189             if( pIter->bCleanup ){
  3095   3190               /* Clean up the rbu_tmp_xxx table for the previous table. It 
  3096   3191               ** cannot be dropped as there are currently active SQL statements.
  3097   3192               ** But the contents can be deleted.  */
  3098   3193               if( rbuIsVacuum(p)==0 && pIter->abIndexed ){
................................................................................
  3367   3462           }
  3368   3463           p->rc = sqlite3_finalize(pStmt);
  3369   3464         }
  3370   3465       }
  3371   3466     }
  3372   3467   }
  3373   3468   
  3374         -/*
  3375         -** The second argument passed to this function is the name of a PRAGMA 
  3376         -** setting - "page_size", "auto_vacuum", "user_version" or "application_id".
  3377         -** This function executes the following on sqlite3rbu.dbRbu:
  3378         -**
  3379         -**   "PRAGMA main.$zPragma"
  3380         -**
  3381         -** where $zPragma is the string passed as the second argument, then
  3382         -** on sqlite3rbu.dbMain:
  3383         -**
  3384         -**   "PRAGMA main.$zPragma = $val"
  3385         -**
  3386         -** where $val is the value returned by the first PRAGMA invocation.
  3387         -**
  3388         -** In short, it copies the value  of the specified PRAGMA setting from
  3389         -** dbRbu to dbMain.
  3390         -*/
  3391         -static void rbuCopyPragma(sqlite3rbu *p, const char *zPragma){
  3392         -  if( p->rc==SQLITE_OK ){
  3393         -    sqlite3_stmt *pPragma = 0;
  3394         -    p->rc = prepareFreeAndCollectError(p->dbRbu, &pPragma, &p->zErrmsg, 
  3395         -        sqlite3_mprintf("PRAGMA main.%s", zPragma)
  3396         -    );
  3397         -    if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPragma) ){
  3398         -      p->rc = rbuMPrintfExec(p, p->dbMain, "PRAGMA main.%s = %d",
  3399         -          zPragma, sqlite3_column_int(pPragma, 0)
  3400         -      );
  3401         -    }
  3402         -    rbuFinalize(p, pPragma);
  3403         -  }
  3404         -}
  3405         -
  3406         -/*
  3407         -** The RBU handle passed as the only argument has just been opened and 
  3408         -** the state database is empty. If this RBU handle was opened for an
  3409         -** RBU vacuum operation, create the schema in the target db.
  3410         -*/
  3411         -static void rbuCreateTargetSchema(sqlite3rbu *p){
  3412         -  sqlite3_stmt *pSql = 0;
  3413         -  sqlite3_stmt *pInsert = 0;
  3414         -
  3415         -  assert( rbuIsVacuum(p) );
  3416         -  p->rc = sqlite3_exec(p->dbMain, "PRAGMA writable_schema=1", 0,0, &p->zErrmsg);
  3417         -  if( p->rc==SQLITE_OK ){
  3418         -    p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, 
  3419         -      "SELECT sql FROM sqlite_master WHERE sql!='' AND rootpage!=0"
  3420         -      " AND name!='sqlite_sequence' "
  3421         -      " ORDER BY type DESC"
  3422         -    );
  3423         -  }
  3424         -
  3425         -  while( p->rc==SQLITE_OK && sqlite3_step(pSql)==SQLITE_ROW ){
  3426         -    const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
  3427         -    p->rc = sqlite3_exec(p->dbMain, zSql, 0, 0, &p->zErrmsg);
  3428         -  }
  3429         -  rbuFinalize(p, pSql);
  3430         -  if( p->rc!=SQLITE_OK ) return;
  3431         -
  3432         -  if( p->rc==SQLITE_OK ){
  3433         -    p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, 
  3434         -        "SELECT * FROM sqlite_master WHERE rootpage=0 OR rootpage IS NULL" 
  3435         -    );
  3436         -  }
  3437         -
  3438         -  if( p->rc==SQLITE_OK ){
  3439         -    p->rc = prepareAndCollectError(p->dbMain, &pInsert, &p->zErrmsg, 
  3440         -        "INSERT INTO sqlite_master VALUES(?,?,?,?,?)"
  3441         -    );
  3442         -  }
  3443         -
  3444         -  while( p->rc==SQLITE_OK && sqlite3_step(pSql)==SQLITE_ROW ){
  3445         -    int i;
  3446         -    for(i=0; i<5; i++){
  3447         -      sqlite3_bind_value(pInsert, i+1, sqlite3_column_value(pSql, i));
  3448         -    }
  3449         -    sqlite3_step(pInsert);
  3450         -    p->rc = sqlite3_reset(pInsert);
  3451         -  }
  3452         -  if( p->rc==SQLITE_OK ){
  3453         -    p->rc = sqlite3_exec(p->dbMain, "PRAGMA writable_schema=0",0,0,&p->zErrmsg);
  3454         -  }
  3455         -
  3456         -  rbuFinalize(p, pSql);
  3457         -  rbuFinalize(p, pInsert);
  3458         -}
  3459         -
  3460   3469   
  3461   3470   static sqlite3rbu *openRbuHandle(
  3462   3471     const char *zTarget, 
  3463   3472     const char *zRbu,
  3464   3473     const char *zState
  3465   3474   ){
  3466   3475     sqlite3rbu *p;
................................................................................
  3565   3574           if( p->rc==SQLITE_OK ){
  3566   3575             int frc = sqlite3_file_control(db, "main", SQLITE_FCNTL_ZIPVFS, 0);
  3567   3576             if( frc==SQLITE_OK ){
  3568   3577               p->rc = sqlite3_exec(db, "PRAGMA journal_mode=off",0,0,&p->zErrmsg);
  3569   3578             }
  3570   3579           }
  3571   3580   
  3572         -        /* If this is an RBU vacuum operation and the state table was empty
  3573         -        ** when this handle was opened, create the target database schema. */
  3574         -        if( p->rc==SQLITE_OK && pState->eStage==0 && rbuIsVacuum(p) ){
  3575         -          rbuCreateTargetSchema(p);
  3576         -          rbuCopyPragma(p, "user_version");
  3577         -          rbuCopyPragma(p, "application_id");
  3578         -        }
  3579         -
  3580   3581           /* Point the object iterator at the first object */
  3581   3582           if( p->rc==SQLITE_OK ){
  3582   3583             p->rc = rbuObjIterFirst(p, &p->objiter);
  3583   3584           }
  3584   3585   
  3585   3586           /* If the RBU database contains no data_xxx tables, declare the RBU
  3586   3587           ** update finished.  */

Changes to ext/rbu/test_rbu.c.

    16     16   #if defined(SQLITE_TEST)
    17     17   #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_RBU)
    18     18   
    19     19   #include "sqlite3rbu.h"
    20     20   #include <tcl.h>
    21     21   #include <assert.h>
    22     22   
    23         -/* From main.c (apparently...) */
           23  +/* From main.c */ 
    24     24   extern const char *sqlite3ErrName(int);
           25  +extern int sqlite3TestMakePointerStr(Tcl_Interp*, char*, void*);
    25     26   
    26     27   void test_rbu_delta(sqlite3_context *pCtx, int nArg, sqlite3_value **apVal){
    27     28     Tcl_Interp *interp = (Tcl_Interp*)sqlite3_user_data(pCtx);
    28     29     Tcl_Obj *pScript;
    29     30     int i;
    30     31   
    31     32     pScript = Tcl_NewObj();
................................................................................
    62     63       const char *zUsage;
    63     64     } aCmd[] = {
    64     65       {"step", 2, ""},              /* 0 */
    65     66       {"close", 2, ""},             /* 1 */
    66     67       {"create_rbu_delta", 2, ""},  /* 2 */
    67     68       {"savestate", 2, ""},         /* 3 */
    68     69       {"dbMain_eval", 3, "SQL"},    /* 4 */
    69         -    {"bp_progress", 2, ""},    /* 5 */
           70  +    {"bp_progress", 2, ""},       /* 5 */
           71  +    {"db", 3, "RBU"},             /* 6 */
    70     72       {0,0,0}
    71     73     };
    72     74     int iCmd;
    73     75   
    74     76     if( objc<2 ){
    75     77       Tcl_WrongNumArgs(interp, 1, objv, "METHOD");
    76     78       return TCL_ERROR;
................................................................................
   144    146   
   145    147         pObj = Tcl_NewObj();
   146    148         Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(one));
   147    149         Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(two));
   148    150         Tcl_SetObjResult(interp, pObj);
   149    151         break;
   150    152       }
          153  +
          154  +    case 6: /* db */ {
          155  +      int bArg;
          156  +      if( Tcl_GetBooleanFromObj(interp, objv[2], &bArg) ){
          157  +        ret = TCL_ERROR;
          158  +      }else{
          159  +        char zBuf[50];
          160  +        sqlite3 *db = sqlite3rbu_db(pRbu, bArg);
          161  +        if( sqlite3TestMakePointerStr(interp, zBuf, (void*)db) ){
          162  +          ret = TCL_ERROR;
          163  +        }else{
          164  +          Tcl_SetResult(interp, zBuf, TCL_VOLATILE);
          165  +        }
          166  +      }
          167  +      break;
          168  +    }
   151    169   
   152    170       default: /* seems unlikely */
   153    171         assert( !"cannot happen" );
   154    172         break;
   155    173     }
   156    174   
   157    175     return ret;