Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -1365,12 +1365,16 @@ ///////////////////////////// The VACUUM command ///////////////////////////// // %ifndef SQLITE_OMIT_VACUUM %ifndef SQLITE_OMIT_ATTACH -cmd ::= VACUUM. {sqlite3Vacuum(pParse,0);} -cmd ::= VACUUM nm(X). {sqlite3Vacuum(pParse,&X);} +%type vinto {Expr*} +%destructor vinto {sqlite3ExprDelete(pParse->db, $$);} +cmd ::= VACUUM vinto(Y). {sqlite3Vacuum(pParse,0,Y);} +cmd ::= VACUUM nm(X) vinto(Y). {sqlite3Vacuum(pParse,&X,Y);} +vinto(A) ::= INTO expr(X). {A = X;} +vinto(A) ::= . {A = 0;} %endif SQLITE_OMIT_ATTACH %endif SQLITE_OMIT_VACUUM ///////////////////////////// The PRAGMA command ///////////////////////////// // Index: src/shell.c.in ================================================================== --- src/shell.c.in +++ src/shell.c.in @@ -3375,10 +3375,11 @@ #ifndef SQLITE_OMIT_AUTHORIZATION ".auth ON|OFF Show authorizer callbacks", #endif ".backup ?DB? FILE Backup DB (default \"main\") to FILE", " --append Use the appendvfs", + " --async Write to FILE without a journal and without fsync()", ".bail on|off Stop after hitting an error. Default OFF", ".binary on|off Turn binary output on or off. Default OFF", ".cd DIRECTORY Change the working directory to DIRECTORY", ".changes on|off Show number of rows changed by SQL", ".check GLOB Fail if output since .testcase does not match", @@ -5838,17 +5839,21 @@ const char *zDestFile = 0; const char *zDb = 0; sqlite3 *pDest; sqlite3_backup *pBackup; int j; + int bAsync = 0; const char *zVfs = 0; for(j=1; jdb, zDb); if( pBackup==0 ){ utf8_printf(stderr, "Error: %s\n", sqlite3_errmsg(pDest)); close_db(pDest); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3983,12 +3983,12 @@ Table *sqlite3LocateTable(Parse*,u32 flags,const char*, const char*); Table *sqlite3LocateTableItem(Parse*,u32 flags,struct SrcList_item *); Index *sqlite3FindIndex(sqlite3*,const char*, const char*); void sqlite3UnlinkAndDeleteTable(sqlite3*,int,const char*); void sqlite3UnlinkAndDeleteIndex(sqlite3*,int,const char*); -void sqlite3Vacuum(Parse*,Token*); -int sqlite3RunVacuum(char**, sqlite3*, int); +void sqlite3Vacuum(Parse*,Token*,Expr*); +int sqlite3RunVacuum(char**, sqlite3*, int, sqlite3_value*); char *sqlite3NameFromToken(sqlite3*, Token*); int sqlite3ExprCompare(Parse*,Expr*, Expr*, int); int sqlite3ExprCompareSkip(Expr*, Expr*, int); int sqlite3ExprListCompare(ExprList*, ExprList*, int); int sqlite3ExprImpliesExpr(Parse*,Expr*, Expr*, int); Index: src/vacuum.c ================================================================== --- src/vacuum.c +++ src/vacuum.c @@ -100,20 +100,20 @@ ** not work if other processes are attached to the original database. ** And a power loss in between deleting the original and renaming the ** transient would cause the database file to appear to be deleted ** following reboot. */ -void sqlite3Vacuum(Parse *pParse, Token *pNm){ +void sqlite3Vacuum(Parse *pParse, Token *pNm, Expr *pInto){ Vdbe *v = sqlite3GetVdbe(pParse); int iDb = 0; - if( v==0 ) return; + if( v==0 ) goto build_vacuum_end; if( pNm ){ #ifndef SQLITE_BUG_COMPATIBLE_20160819 /* Default behavior: Report an error if the argument to VACUUM is ** not recognized */ iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm); - if( iDb<0 ) return; + if( iDb<0 ) goto build_vacuum_end; #else /* When SQLITE_BUG_COMPATIBLE_20160819 is defined, unrecognized arguments ** to VACUUM are silently ignored. This is a back-out of a bug fix that ** occurred on 2016-08-19 (https://www.sqlite.org/src/info/083f9e6270). ** The buggy behavior is required for binary compatibility with some @@ -121,20 +121,32 @@ iDb = sqlite3FindDb(pParse->db, pNm); if( iDb<0 ) iDb = 0; #endif } if( iDb!=1 ){ - sqlite3VdbeAddOp1(v, OP_Vacuum, iDb); + int iIntoReg = 0; + if( pInto ){ + iIntoReg = ++pParse->nMem; + sqlite3ExprCode(pParse, pInto, iIntoReg); + } + sqlite3VdbeAddOp2(v, OP_Vacuum, iDb, iIntoReg); sqlite3VdbeUsesBtree(v, iDb); } +build_vacuum_end: + sqlite3ExprDelete(pParse->db, pInto); return; } /* ** This routine implements the OP_Vacuum opcode of the VDBE. */ -int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db, int iDb){ +int sqlite3RunVacuum( + char **pzErrMsg, /* Write error message here */ + sqlite3 *db, /* Database connection */ + int iDb, /* Which attached DB to vacuum */ + sqlite3_value *pOut /* Write results here, if not NULL */ +){ int rc = SQLITE_OK; /* Return code from service routines */ Btree *pMain; /* The database being vacuumed */ Btree *pTemp; /* The temporary database we vacuum into */ u32 saved_mDbFlags; /* Saved value of db->mDbFlags */ u64 saved_flags; /* Saved value of db->flags */ @@ -144,19 +156,29 @@ Db *pDb = 0; /* Database to detach at end of vacuum */ int isMemDb; /* True if vacuuming a :memory: database */ int nRes; /* Bytes of reserved space at the end of each page */ int nDb; /* Number of attached databases */ const char *zDbMain; /* Schema name of database to vacuum */ + const char *zOut; /* Name of output file */ if( !db->autoCommit ){ sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction"); return SQLITE_ERROR; } if( db->nVdbeActive>1 ){ sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress"); return SQLITE_ERROR; } + if( pOut ){ + if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){ + sqlite3SetString(pzErrMsg, db, "non-text filename"); + return SQLITE_ERROR; + } + zOut = (const char*)sqlite3_value_text(pOut); + }else{ + zOut = ""; + } /* Save the current value of the database flags so that it can be ** restored before returning. Then set the writable-schema flag, and ** disable CHECK and foreign key constraints. */ saved_flags = db->flags; @@ -187,16 +209,25 @@ ** empty. Only the journal header is written. Apparently it takes more ** time to parse and run the PRAGMA to turn journalling off than it does ** to write the journal header file. */ nDb = db->nDb; - rc = execSql(db, pzErrMsg, "ATTACH''AS vacuum_db"); + rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS vacuum_db", zOut); if( rc!=SQLITE_OK ) goto end_of_vacuum; assert( (db->nDb-1)==nDb ); pDb = &db->aDb[nDb]; assert( strcmp(pDb->zDbSName,"vacuum_db")==0 ); pTemp = pDb->pBt; + if( pOut ){ + sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp)); + i64 sz = 0; + if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){ + rc = SQLITE_ERROR; + sqlite3SetString(pzErrMsg, db, "output file already exists"); + goto end_of_vacuum; + } + } nRes = sqlite3BtreeGetOptimalReserve(pMain); /* A VACUUM cannot change the pagesize of an encrypted database. */ #ifdef SQLITE_HAS_CODEC if( db->nextPagesize ){ @@ -216,11 +247,11 @@ ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below, ** to ensure that we do not try to change the page-size on a WAL database. */ rc = execSql(db, pzErrMsg, "BEGIN"); if( rc!=SQLITE_OK ) goto end_of_vacuum; - rc = sqlite3BtreeBeginTrans(pMain, 2, 0); + rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0); if( rc!=SQLITE_OK ) goto end_of_vacuum; /* Do not attempt to change the page size for a WAL database */ if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain)) ==PAGER_JOURNALMODE_WAL ){ @@ -311,11 +342,11 @@ BTREE_USER_VERSION, 0, /* Preserve the user version */ BTREE_APPLICATION_ID, 0, /* Preserve the application id */ }; assert( 1==sqlite3BtreeIsInTrans(pTemp) ); - assert( 1==sqlite3BtreeIsInTrans(pMain) ); + assert( pOut!=0 || 1==sqlite3BtreeIsInTrans(pMain) ); /* Copy Btree meta values */ for(i=0; iflags */ db->init.iDb = 0; db->mDbFlags = saved_mDbFlags; Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -6682,18 +6682,23 @@ break; }; #endif /* SQLITE_OMIT_PRAGMA */ #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH) -/* Opcode: Vacuum P1 * * * * +/* Opcode: Vacuum P1 P2 * * * ** ** Vacuum the entire database P1. P1 is 0 for "main", and 2 or more ** for an attached database. The "temp" database may not be vacuumed. +** +** If P2 is not zero, then it is a register holding a string which is +** the file into which the result of vacuum should be written. When +** P2 is zero, the vacuum overwrites the original database. */ case OP_Vacuum: { assert( p->readOnly==0 ); - rc = sqlite3RunVacuum(&p->zErrMsg, db, pOp->p1); + rc = sqlite3RunVacuum(&p->zErrMsg, db, pOp->p1, + pOp->p2 ? &aMem[pOp->p2] : 0); if( rc ) goto abort_due_to_error; break; } #endif Index: test/shell1.test ================================================================== --- test/shell1.test +++ test/shell1.test @@ -254,11 +254,11 @@ catchcmd "test.db" ".backup FOO BAR" } {1 {Error: unknown database FOO}} do_test shell1-3.1.4 { # too many arguments catchcmd "test.db" ".backup FOO BAR BAD" -} {1 {Usage: .backup ?DB? ?--append? FILENAME}} +} {1 {Usage: .backup ?DB? ?OPTIONS? FILENAME}} # .bail ON|OFF Stop after hitting an error. Default OFF do_test shell1-3.2.1 { catchcmd "test.db" ".bail" } {1 {Usage: .bail on|off}} ADDED test/vacuum-into.test Index: test/vacuum-into.test ================================================================== --- /dev/null +++ test/vacuum-into.test @@ -0,0 +1,69 @@ +# 2018-12-07 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the VACUUM INTO statement. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# If the VACUUM statement is disabled in the current build, skip all +# the tests in this file. +# +ifcapable {!vacuum} { + omit_test vacuum.test {Compiled with SQLITE_OMIT_VACUUM} + finish_test + return +} + +forcedelete out.db +do_execsql_test vacuum-into-100 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) + INSERT INTO t1(a,b) SELECT x, randomblob(600) FROM c; + CREATE INDEX t1b ON t1(b); + DELETE FROM t1 WHERE a%2; + SELECT count(*), sum(a), sum(length(b)) FROM t1; +} {50 2550 30000} +do_execsql_test vacuum-into-110 { + VACUUM main INTO 'out.db'; +} {} +sqlite3 db2 out.db +do_test vacuum-into-120 { + db2 eval {SELECT count(*), sum(a), sum(length(b)) FROM t1} +} {50 2550 30000} +do_catchsql_test vacuum-into-130 { + VACUUM INTO 'out.db'; +} {1 {output file already exists}} +forcedelete out2.db +do_catchsql_test vacuum-into-140 { + VACUUM INTO 'out2.db'; +} {0 {}} +do_catchsql_test vacuum-into-150 { + VACUUM INTO 'out2.db'; +} {1 {output file already exists}} + +do_catchsql_test vacuum-into-200 { + VACUUM main INTO ':memory:'; +} {0 {}} + +# The INTO argument can be an arbitrary expression. +# +do_execsql_test vacuum-into-300 { + CREATE TABLE t2(name TEXT); + INSERT INTO t2 VALUES(':memory:'); + VACUUM main INTO (SELECT name FROM t2); +} {} +do_catchsql_test vacuum-into-310 { + VACUUM INTO null; +} {1 {non-text filename}} + +finish_test