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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
7dd48c10790a7b9c4165214399c261a0 |
User & Date: | dan 2016-04-25 19:25:12.645 |
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: d819bfbd46 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: 7dd48c1079 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: ec215f94ac user: drh tags: trunk) | |
Changes
Changes to ext/rbu/rbuvacuum.test.
︙ | ︙ | |||
251 252 253 254 255 256 257 | execsql " INSERT INTO t$i VALUES(1, 2, 3); INSERT INTO t$i VALUES(4, 5, 6); " } } {} do_rbu_vacuum_test 1.10.2 $step | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 | execsql " INSERT INTO t$i VALUES(1, 2, 3); INSERT INTO t$i VALUES(4, 5, 6); " } } {} do_rbu_vacuum_test 1.10.2 $step # Database with empty tables. # reset_db do_execsql_test 1.11.1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE TABLE t2(a INTEGER PRIMARY KEY, b); CREATE TABLE t3(a INTEGER PRIMARY KEY, b); CREATE TABLE t4(a INTEGER PRIMARY KEY, b); INSERT INTO t4 VALUES(1, 2); } do_rbu_vacuum_test 1.11.2 $step do_execsql_test 1.11.3 { SELECT * FROM t1; SELECT * FROM t2; SELECT * FROM t3; SELECT * FROM t4; } {1 2} reset_db do_execsql_test 1.12.1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE TABLE t2(a INTEGER PRIMARY KEY, b); CREATE TABLE t3(a INTEGER PRIMARY KEY, b); CREATE TABLE t4(a INTEGER PRIMARY KEY, b); INSERT INTO t1 VALUES(1, 2); } do_rbu_vacuum_test 1.12.2 $step do_execsql_test 1.12.3 { SELECT * FROM t1; SELECT * FROM t2; SELECT * FROM t3; SELECT * FROM t4; } {1 2} } set ::testprefix rbuvacuum #------------------------------------------------------------------------- # Test some error cases: # # 2.1.* the db being vacuumed being in wal mode already. # 2.2.* database modified mid vacuum. # |
︙ | ︙ | |||
305 306 307 308 309 310 311 312 313 314 315 | sqlite3rbu_vacuum rbu test.db state.db rbu step } {SQLITE_BUSY} do_test 2.2.$i.2 { list [catch { rbu close } msg] $msg } {1 {SQLITE_BUSY - database modified during rbu vacuum}} } catch { db close } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 | sqlite3rbu_vacuum rbu test.db state.db rbu step } {SQLITE_BUSY} do_test 2.2.$i.2 { list [catch { rbu close } msg] $msg } {1 {SQLITE_BUSY - database modified during rbu vacuum}} } #------------------------------------------------------------------------- # Test that a database that uses custom collation sequences can be RBU # vacuumed. # reset_db forcedelete state.db proc noop {args} {} proc length_cmp {x y} { set n1 [string length $x] set n2 [string length $y] return [expr $n1 - $n2] } sqlite3_create_collation_v2 db length length_cmp noop do_execsql_test 3.0 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); INSERT INTO t1 VALUES(1, 'i'); INSERT INTO t1 VALUES(2, 'iiii'); INSERT INTO t1 VALUES(3, 'ii'); INSERT INTO t1 VALUES(4, 'iii'); SELECT a FROM t1 ORDER BY b COLLATE length; } {1 3 4 2} do_execsql_test 3.1 { CREATE INDEX i1 ON t1(b COLLATE length); } do_test 3.2 { sqlite3rbu_vacuum rbu test.db state.db while {[rbu step]=="SQLITE_OK"} {} list [catch { rbu close } msg] $msg } {1 {SQLITE_ERROR - no such collation sequence: length}} do_test 3.3 { sqlite3rbu_vacuum rbu test.db state.db set db1 [rbu db 0] sqlite3_create_collation_v2 $db1 length length_cmp noop while {[rbu step]=="SQLITE_OK"} {} list [catch { rbu close } msg] $msg } {1 {SQLITE_ERROR - no such collation sequence: length}} do_test 3.4 { sqlite3rbu_vacuum rbu test.db state.db set db1 [rbu db 1] sqlite3_create_collation_v2 $db1 length length_cmp noop while {[rbu step]=="SQLITE_OK"} {} list [catch { rbu close } msg] $msg } {1 {SQLITE_ERROR - no such collation sequence: length}} do_test 3.5 { sqlite3rbu_vacuum rbu test.db state.db set db1 [rbu db 0] set db2 [rbu db 1] sqlite3_create_collation_v2 $db1 length length_cmp noop sqlite3_create_collation_v2 $db2 length length_cmp noop while {[rbu step]=="SQLITE_OK"} {} list [catch { rbu close } msg] $msg } {0 SQLITE_DONE} catch { db close } finish_test |
Changes to ext/rbu/sqlite3rbu.c.
︙ | ︙ | |||
3076 3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 3094 3095 3096 3097 3098 | sqlite3_step(pInsert); rc = sqlite3_finalize(pInsert); } if( rc!=SQLITE_OK ) p->rc = rc; } } /* ** Step the RBU object. */ int sqlite3rbu_step(sqlite3rbu *p){ if( p ){ switch( p->eStage ){ case RBU_STAGE_OAL: { RbuObjIter *pIter = &p->objiter; while( p->rc==SQLITE_OK && pIter->zTbl ){ if( pIter->bCleanup ){ /* Clean up the rbu_tmp_xxx table for the previous table. It ** cannot be dropped as there are currently active SQL statements. ** But the contents can be deleted. */ if( rbuIsVacuum(p)==0 && pIter->abIndexed ){ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3076 3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 3094 3095 3096 3097 3098 3099 3100 3101 3102 3103 3104 3105 3106 3107 3108 3109 3110 3111 3112 3113 3114 3115 3116 3117 3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 3187 3188 3189 3190 3191 3192 3193 | sqlite3_step(pInsert); rc = sqlite3_finalize(pInsert); } if( rc!=SQLITE_OK ) p->rc = rc; } } /* ** The second argument passed to this function is the name of a PRAGMA ** setting - "page_size", "auto_vacuum", "user_version" or "application_id". ** This function executes the following on sqlite3rbu.dbRbu: ** ** "PRAGMA main.$zPragma" ** ** where $zPragma is the string passed as the second argument, then ** on sqlite3rbu.dbMain: ** ** "PRAGMA main.$zPragma = $val" ** ** where $val is the value returned by the first PRAGMA invocation. ** ** In short, it copies the value of the specified PRAGMA setting from ** dbRbu to dbMain. */ static void rbuCopyPragma(sqlite3rbu *p, const char *zPragma){ if( p->rc==SQLITE_OK ){ sqlite3_stmt *pPragma = 0; p->rc = prepareFreeAndCollectError(p->dbRbu, &pPragma, &p->zErrmsg, sqlite3_mprintf("PRAGMA main.%s", zPragma) ); if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pPragma) ){ p->rc = rbuMPrintfExec(p, p->dbMain, "PRAGMA main.%s = %d", zPragma, sqlite3_column_int(pPragma, 0) ); } rbuFinalize(p, pPragma); } } /* ** The RBU handle passed as the only argument has just been opened and ** the state database is empty. If this RBU handle was opened for an ** RBU vacuum operation, create the schema in the target db. */ static void rbuCreateTargetSchema(sqlite3rbu *p){ sqlite3_stmt *pSql = 0; sqlite3_stmt *pInsert = 0; assert( rbuIsVacuum(p) ); p->rc = sqlite3_exec(p->dbMain, "PRAGMA writable_schema=1", 0,0, &p->zErrmsg); if( p->rc==SQLITE_OK ){ p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, "SELECT sql FROM sqlite_master WHERE sql!='' AND rootpage!=0" " AND name!='sqlite_sequence' " " ORDER BY type DESC" ); } while( p->rc==SQLITE_OK && sqlite3_step(pSql)==SQLITE_ROW ){ const char *zSql = (const char*)sqlite3_column_text(pSql, 0); p->rc = sqlite3_exec(p->dbMain, zSql, 0, 0, &p->zErrmsg); } rbuFinalize(p, pSql); if( p->rc!=SQLITE_OK ) return; if( p->rc==SQLITE_OK ){ p->rc = prepareAndCollectError(p->dbRbu, &pSql, &p->zErrmsg, "SELECT * FROM sqlite_master WHERE rootpage=0 OR rootpage IS NULL" ); } if( p->rc==SQLITE_OK ){ p->rc = prepareAndCollectError(p->dbMain, &pInsert, &p->zErrmsg, "INSERT INTO sqlite_master VALUES(?,?,?,?,?)" ); } while( p->rc==SQLITE_OK && sqlite3_step(pSql)==SQLITE_ROW ){ int i; for(i=0; i<5; i++){ sqlite3_bind_value(pInsert, i+1, sqlite3_column_value(pSql, i)); } sqlite3_step(pInsert); p->rc = sqlite3_reset(pInsert); } if( p->rc==SQLITE_OK ){ p->rc = sqlite3_exec(p->dbMain, "PRAGMA writable_schema=0",0,0,&p->zErrmsg); } rbuFinalize(p, pSql); rbuFinalize(p, pInsert); } /* ** Step the RBU object. */ int sqlite3rbu_step(sqlite3rbu *p){ if( p ){ switch( p->eStage ){ case RBU_STAGE_OAL: { RbuObjIter *pIter = &p->objiter; /* If this is an RBU vacuum operation and the state table was empty ** when this handle was opened, create the target database schema. */ if( rbuIsVacuum(p) && p->nProgress==0 && p->rc==SQLITE_OK ){ rbuCreateTargetSchema(p); rbuCopyPragma(p, "user_version"); rbuCopyPragma(p, "application_id"); } while( p->rc==SQLITE_OK && pIter->zTbl ){ if( pIter->bCleanup ){ /* Clean up the rbu_tmp_xxx table for the previous table. It ** cannot be dropped as there are currently active SQL statements. ** But the contents can be deleted. */ if( rbuIsVacuum(p)==0 && pIter->abIndexed ){ |
︙ | ︙ | |||
3367 3368 3369 3370 3371 3372 3373 | } p->rc = sqlite3_finalize(pStmt); } } } } | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 3462 3463 3464 3465 3466 3467 3468 3469 3470 3471 3472 3473 3474 3475 | } p->rc = sqlite3_finalize(pStmt); } } } } static sqlite3rbu *openRbuHandle( const char *zTarget, const char *zRbu, const char *zState ){ sqlite3rbu *p; |
︙ | ︙ | |||
3565 3566 3567 3568 3569 3570 3571 | if( p->rc==SQLITE_OK ){ int frc = sqlite3_file_control(db, "main", SQLITE_FCNTL_ZIPVFS, 0); if( frc==SQLITE_OK ){ p->rc = sqlite3_exec(db, "PRAGMA journal_mode=off",0,0,&p->zErrmsg); } } | < < < < < < < < | 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 3587 | if( p->rc==SQLITE_OK ){ int frc = sqlite3_file_control(db, "main", SQLITE_FCNTL_ZIPVFS, 0); if( frc==SQLITE_OK ){ p->rc = sqlite3_exec(db, "PRAGMA journal_mode=off",0,0,&p->zErrmsg); } } /* Point the object iterator at the first object */ if( p->rc==SQLITE_OK ){ p->rc = rbuObjIterFirst(p, &p->objiter); } /* If the RBU database contains no data_xxx tables, declare the RBU ** update finished. */ |
︙ | ︙ |
Changes to ext/rbu/test_rbu.c.
︙ | ︙ | |||
16 17 18 19 20 21 22 | #if defined(SQLITE_TEST) #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_RBU) #include "sqlite3rbu.h" #include <tcl.h> #include <assert.h> | | > | 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | #if defined(SQLITE_TEST) #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_RBU) #include "sqlite3rbu.h" #include <tcl.h> #include <assert.h> /* From main.c */ extern const char *sqlite3ErrName(int); extern int sqlite3TestMakePointerStr(Tcl_Interp*, char*, void*); void test_rbu_delta(sqlite3_context *pCtx, int nArg, sqlite3_value **apVal){ Tcl_Interp *interp = (Tcl_Interp*)sqlite3_user_data(pCtx); Tcl_Obj *pScript; int i; pScript = Tcl_NewObj(); |
︙ | ︙ | |||
62 63 64 65 66 67 68 | const char *zUsage; } aCmd[] = { {"step", 2, ""}, /* 0 */ {"close", 2, ""}, /* 1 */ {"create_rbu_delta", 2, ""}, /* 2 */ {"savestate", 2, ""}, /* 3 */ {"dbMain_eval", 3, "SQL"}, /* 4 */ | | > | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | const char *zUsage; } aCmd[] = { {"step", 2, ""}, /* 0 */ {"close", 2, ""}, /* 1 */ {"create_rbu_delta", 2, ""}, /* 2 */ {"savestate", 2, ""}, /* 3 */ {"dbMain_eval", 3, "SQL"}, /* 4 */ {"bp_progress", 2, ""}, /* 5 */ {"db", 3, "RBU"}, /* 6 */ {0,0,0} }; int iCmd; if( objc<2 ){ Tcl_WrongNumArgs(interp, 1, objv, "METHOD"); return TCL_ERROR; |
︙ | ︙ | |||
144 145 146 147 148 149 150 151 152 153 154 155 156 157 | pObj = Tcl_NewObj(); Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(one)); Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(two)); Tcl_SetObjResult(interp, pObj); break; } default: /* seems unlikely */ assert( !"cannot happen" ); break; } return ret; | > > > > > > > > > > > > > > > > | 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 | pObj = Tcl_NewObj(); Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(one)); Tcl_ListObjAppendElement(interp, pObj, Tcl_NewIntObj(two)); Tcl_SetObjResult(interp, pObj); break; } case 6: /* db */ { int bArg; if( Tcl_GetBooleanFromObj(interp, objv[2], &bArg) ){ ret = TCL_ERROR; }else{ char zBuf[50]; sqlite3 *db = sqlite3rbu_db(pRbu, bArg); if( sqlite3TestMakePointerStr(interp, zBuf, (void*)db) ){ ret = TCL_ERROR; }else{ Tcl_SetResult(interp, zBuf, TCL_VOLATILE); } } break; } default: /* seems unlikely */ assert( !"cannot happen" ); break; } return ret; |
︙ | ︙ |