/ Check-in [083f9e62]
Login

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

Overview
Comment:Enhance the VACUUM command so that it can operate on an attached database.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 083f9e6270fa4faa402b91231271da4f3915c79f
User & Date: drh 2016-08-19 15:15:55
Context
2016-08-22
20:49
Add an experimental module to detect conflicts between sessions changesets. check-in: 0c9fd6b7 user: dan tags: changebatch
20:10
Add support for the SQLITE_DBCONFIG_MAINDBNAME configuration option. check-in: 78395193 user: drh tags: trunk
2016-08-19
15:41
Merge recent enhancements from trunk. check-in: b1787236 user: drh tags: rowvalue
15:17
Merge the VACUUM attached database enhancement from trunk. Closed-Leaf check-in: 12d5e38d user: drh tags: dbconfig_maindbname
15:15
Enhance the VACUUM command so that it can operate on an attached database. check-in: 083f9e62 user: drh tags: trunk
15:12
Disable row counting on queries run while doing a VACUUM. Closed-Leaf check-in: ad35ef11 user: drh tags: vacuum-attached-db
2016-08-18
22:19
Change the name of Db.zName to Db.zDbSName for improved long-term code maintainability. check-in: cb9865e1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

   769    769       *pUnqual = pName2;
   770    770       iDb = sqlite3FindDb(db, pName1);
   771    771       if( iDb<0 ){
   772    772         sqlite3ErrorMsg(pParse, "unknown database %T", pName1);
   773    773         return -1;
   774    774       }
   775    775     }else{
   776         -    assert( db->init.iDb==0 || db->init.busy );
          776  +    assert( db->init.iDb==0 || db->init.busy || (db->flags & SQLITE_Vacuum)!=0);
   777    777       iDb = db->init.iDb;
   778    778       *pUnqual = pName1;
   779    779     }
   780    780     return iDb;
   781    781   }
   782    782   
   783    783   /*
................................................................................
  2007   2007       sqlite3DbFree(db, zStmt);
  2008   2008       sqlite3ChangeCookie(pParse, iDb);
  2009   2009   
  2010   2010   #ifndef SQLITE_OMIT_AUTOINCREMENT
  2011   2011       /* Check to see if we need to create an sqlite_sequence table for
  2012   2012       ** keeping track of autoincrement keys.
  2013   2013       */
  2014         -    if( p->tabFlags & TF_Autoincrement ){
         2014  +    if( (p->tabFlags & TF_Autoincrement)!=0 ){
  2015   2015         Db *pDb = &db->aDb[iDb];
  2016   2016         assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  2017   2017         if( pDb->pSchema->pSeqTab==0 ){
  2018   2018           sqlite3NestedParse(pParse,
  2019   2019             "CREATE TABLE %Q.sqlite_sequence(name,seq)",
  2020   2020             pDb->zDbSName
  2021   2021           );

Changes to src/insert.c.

   196    196     return 0;
   197    197   }
   198    198   
   199    199   #ifndef SQLITE_OMIT_AUTOINCREMENT
   200    200   /*
   201    201   ** Locate or create an AutoincInfo structure associated with table pTab
   202    202   ** which is in database iDb.  Return the register number for the register
   203         -** that holds the maximum rowid.
          203  +** that holds the maximum rowid.  Return zero if pTab is not an AUTOINCREMENT
          204  +** table.  (Also return zero when doing a VACUUM since we do not want to
          205  +** update the AUTOINCREMENT counters during a VACUUM.)
   204    206   **
   205    207   ** There is at most one AutoincInfo structure per table even if the
   206    208   ** same table is autoincremented multiple times due to inserts within
   207    209   ** triggers.  A new AutoincInfo structure is created if this is the
   208    210   ** first use of table pTab.  On 2nd and subsequent uses, the original
   209    211   ** AutoincInfo structure is used.
   210    212   **
................................................................................
   219    221   */
   220    222   static int autoIncBegin(
   221    223     Parse *pParse,      /* Parsing context */
   222    224     int iDb,            /* Index of the database holding pTab */
   223    225     Table *pTab         /* The table we are writing to */
   224    226   ){
   225    227     int memId = 0;      /* Register holding maximum rowid */
   226         -  if( pTab->tabFlags & TF_Autoincrement ){
          228  +  if( (pTab->tabFlags & TF_Autoincrement)!=0
          229  +   && (pParse->db->flags & SQLITE_Vacuum)==0
          230  +  ){
   227    231       Parse *pToplevel = sqlite3ParseToplevel(pParse);
   228    232       AutoincInfo *pInfo;
   229    233   
   230    234       pInfo = pToplevel->pAinc;
   231    235       while( pInfo && pInfo->pTab!=pTab ){ pInfo = pInfo->pNext; }
   232    236       if( pInfo==0 ){
   233    237         pInfo = sqlite3DbMallocRawNN(pParse->db, sizeof(*pInfo));

Changes to src/parse.y.

  1281   1281   //
  1282   1282   cmd ::= DROP INDEX ifexists(E) fullname(X).   {sqlite3DropIndex(pParse, X, E);}
  1283   1283   
  1284   1284   ///////////////////////////// The VACUUM command /////////////////////////////
  1285   1285   //
  1286   1286   %ifndef SQLITE_OMIT_VACUUM
  1287   1287   %ifndef SQLITE_OMIT_ATTACH
  1288         -cmd ::= VACUUM.                {sqlite3Vacuum(pParse);}
  1289         -cmd ::= VACUUM nm.             {sqlite3Vacuum(pParse);}
         1288  +cmd ::= VACUUM.                {sqlite3Vacuum(pParse,0);}
         1289  +cmd ::= VACUUM nm(X).          {sqlite3Vacuum(pParse,&X);}
  1290   1290   %endif  SQLITE_OMIT_ATTACH
  1291   1291   %endif  SQLITE_OMIT_VACUUM
  1292   1292   
  1293   1293   ///////////////////////////// The PRAGMA command /////////////////////////////
  1294   1294   //
  1295   1295   %ifndef SQLITE_OMIT_PRAGMA
  1296   1296   cmd ::= PRAGMA nm(X) dbnm(Z).                {sqlite3Pragma(pParse,&X,&Z,0,0);}

Changes to src/prepare.c.

    69     69     }else if( sqlite3_strnicmp(argv[2],"create ",7)==0 ){
    70     70       /* Call the parser to process a CREATE TABLE, INDEX or VIEW.
    71     71       ** But because db->init.busy is set to 1, no VDBE code is generated
    72     72       ** or executed.  All the parser does is build the internal data
    73     73       ** structures that describe the table, index, or view.
    74     74       */
    75     75       int rc;
           76  +    u8 saved_iDb = db->init.iDb;
    76     77       sqlite3_stmt *pStmt;
    77     78       TESTONLY(int rcp);            /* Return code from sqlite3_prepare() */
    78     79   
    79     80       assert( db->init.busy );
    80     81       db->init.iDb = iDb;
    81     82       db->init.newTnum = sqlite3Atoi(argv[1]);
    82     83       db->init.orphanTrigger = 0;
    83     84       TESTONLY(rcp = ) sqlite3_prepare(db, argv[2], -1, &pStmt, 0);
    84     85       rc = db->errCode;
    85     86       assert( (rc&0xFF)==(rcp&0xFF) );
    86         -    db->init.iDb = 0;
           87  +    db->init.iDb = saved_iDb;
           88  +    assert( saved_iDb==0 || (db->flags & SQLITE_Vacuum)!=0 );
    87     89       if( SQLITE_OK!=rc ){
    88     90         if( db->init.orphanTrigger ){
    89     91           assert( iDb==1 );
    90     92         }else{
    91     93           pData->rc = rc;
    92     94           if( rc==SQLITE_NOMEM ){
    93     95             sqlite3OomFault(db);

Changes to src/sqliteInt.h.

  3697   3697   #define LOCATE_VIEW    0x01
  3698   3698   #define LOCATE_NOERR   0x02
  3699   3699   Table *sqlite3LocateTable(Parse*,u32 flags,const char*, const char*);
  3700   3700   Table *sqlite3LocateTableItem(Parse*,u32 flags,struct SrcList_item *);
  3701   3701   Index *sqlite3FindIndex(sqlite3*,const char*, const char*);
  3702   3702   void sqlite3UnlinkAndDeleteTable(sqlite3*,int,const char*);
  3703   3703   void sqlite3UnlinkAndDeleteIndex(sqlite3*,int,const char*);
  3704         -void sqlite3Vacuum(Parse*);
  3705         -int sqlite3RunVacuum(char**, sqlite3*);
         3704  +void sqlite3Vacuum(Parse*,Token*);
         3705  +int sqlite3RunVacuum(char**, sqlite3*, int);
  3706   3706   char *sqlite3NameFromToken(sqlite3*, Token*);
  3707   3707   int sqlite3ExprCompare(Expr*, Expr*, int);
  3708   3708   int sqlite3ExprListCompare(ExprList*, ExprList*, int);
  3709   3709   int sqlite3ExprImpliesExpr(Expr*, Expr*, int);
  3710   3710   void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*);
  3711   3711   void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*);
  3712   3712   int sqlite3ExprCoveredByIndex(Expr*, int iCur, Index *pIdx);

Changes to src/vacuum.c.

    14     14   ** Most of the code in this file may be omitted by defining the
    15     15   ** SQLITE_OMIT_VACUUM macro.
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include "vdbeInt.h"
    19     19   
    20     20   #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
    21         -/*
    22         -** Finalize a prepared statement.  If there was an error, store the
    23         -** text of the error message in *pzErrMsg.  Return the result code.
    24         -*/
    25         -static int vacuumFinalize(sqlite3 *db, sqlite3_stmt *pStmt, char **pzErrMsg){
    26         -  int rc;
    27         -  rc = sqlite3VdbeFinalize((Vdbe*)pStmt);
    28         -  if( rc ){
    29         -    sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
    30         -  }
    31         -  return rc;
    32         -}
    33     21   
    34     22   /*
    35         -** Execute zSql on database db. Return an error code.
           23  +** Execute zSql on database db.
           24  +**
           25  +** If zSql returns rows, then each row will have exactly one
           26  +** column.  (This will only happen if zSql begins with "SELECT".)
           27  +** Take each row of result and call execSql() again recursively.
           28  +**
           29  +** The execSqlF() routine does the same thing, except it accepts
           30  +** a format string as its third argument
    36     31   */
    37     32   static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
    38     33     sqlite3_stmt *pStmt;
    39         -  VVA_ONLY( int rc; )
    40         -  if( !zSql ){
    41         -    return SQLITE_NOMEM_BKPT;
    42         -  }
    43         -  if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
    44         -    sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
    45         -    return sqlite3_errcode(db);
    46         -  }
    47         -  VVA_ONLY( rc = ) sqlite3_step(pStmt);
    48         -  assert( rc!=SQLITE_ROW || (db->flags&SQLITE_CountRows) );
    49         -  return vacuumFinalize(db, pStmt, pzErrMsg);
    50         -}
    51         -
    52         -/*
    53         -** Execute zSql on database db. The statement returns exactly
    54         -** one column. Execute this as SQL on the same database.
    55         -*/
    56         -static int execExecSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
    57         -  sqlite3_stmt *pStmt;
    58     34     int rc;
    59     35   
    60         -  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
           36  +  /* printf("SQL: [%s]\n", zSql); fflush(stdout); */
           37  +  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
    61     38     if( rc!=SQLITE_OK ) return rc;
    62         -
    63         -  while( SQLITE_ROW==sqlite3_step(pStmt) ){
    64         -    rc = execSql(db, pzErrMsg, (char*)sqlite3_column_text(pStmt, 0));
    65         -    if( rc!=SQLITE_OK ){
    66         -      vacuumFinalize(db, pStmt, pzErrMsg);
    67         -      return rc;
           39  +  while( SQLITE_ROW==(rc = sqlite3_step(pStmt)) ){
           40  +    const char *zSubSql = (const char*)sqlite3_column_text(pStmt,0);
           41  +    assert( sqlite3_strnicmp(zSql,"SELECT",6)==0 );
           42  +    if( zSubSql ){
           43  +      assert( zSubSql[0]!='S' );
           44  +      rc = execSql(db, pzErrMsg, zSubSql);
           45  +      if( rc!=SQLITE_OK ) break;
    68     46       }
    69     47     }
    70         -
    71         -  return vacuumFinalize(db, pStmt, pzErrMsg);
           48  +  assert( rc!=SQLITE_ROW );
           49  +  if( rc==SQLITE_DONE ) rc = SQLITE_OK;
           50  +  if( rc ){
           51  +    sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
           52  +  }
           53  +  (void)sqlite3_finalize(pStmt);
           54  +  return rc;
           55  +}
           56  +static int execSqlF(sqlite3 *db, char **pzErrMsg, const char *zSql, ...){
           57  +  char *z;
           58  +  va_list ap;
           59  +  int rc;
           60  +  va_start(ap, zSql);
           61  +  z = sqlite3VMPrintf(db, zSql, ap);
           62  +  va_end(ap);
           63  +  if( z==0 ) return SQLITE_NOMEM;
           64  +  rc = execSql(db, pzErrMsg, z);
           65  +  sqlite3DbFree(db, z);
           66  +  return rc;
    72     67   }
    73     68   
    74     69   /*
    75     70   ** The VACUUM command is used to clean up the database,
    76     71   ** collapse free space, etc.  It is modelled after the VACUUM command
    77     72   ** in PostgreSQL.  The VACUUM command works as follows:
    78     73   **
................................................................................
    97     92   ** the copy of step (3) were replaced by deleting the original database
    98     93   ** and renaming the transient database as the original.  But that will
    99     94   ** not work if other processes are attached to the original database.
   100     95   ** And a power loss in between deleting the original and renaming the
   101     96   ** transient would cause the database file to appear to be deleted
   102     97   ** following reboot.
   103     98   */
   104         -void sqlite3Vacuum(Parse *pParse){
           99  +void sqlite3Vacuum(Parse *pParse, Token *pNm){
   105    100     Vdbe *v = sqlite3GetVdbe(pParse);
   106         -  if( v ){
   107         -    sqlite3VdbeAddOp2(v, OP_Vacuum, 0, 0);
   108         -    sqlite3VdbeUsesBtree(v, 0);
          101  +  int iDb = pNm ? sqlite3TwoPartName(pParse, pNm, pNm, &pNm) : 0;
          102  +  if( v && (iDb>=2 || iDb==0) ){
          103  +    sqlite3VdbeAddOp1(v, OP_Vacuum, iDb);
          104  +    sqlite3VdbeUsesBtree(v, iDb);
   109    105     }
   110    106     return;
   111    107   }
   112    108   
   113    109   /*
   114    110   ** This routine implements the OP_Vacuum opcode of the VDBE.
   115    111   */
   116         -int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
          112  +int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db, int iDb){
   117    113     int rc = SQLITE_OK;     /* Return code from service routines */
   118    114     Btree *pMain;           /* The database being vacuumed */
   119    115     Btree *pTemp;           /* The temporary database we vacuum into */
   120         -  char *zSql = 0;         /* SQL statements */
   121    116     int saved_flags;        /* Saved value of the db->flags */
   122    117     int saved_nChange;      /* Saved value of db->nChange */
   123    118     int saved_nTotalChange; /* Saved value of db->nTotalChange */
   124    119     u8 saved_mTrace;        /* Saved trace settings */
   125    120     Db *pDb = 0;            /* Database to detach at end of vacuum */
   126    121     int isMemDb;            /* True if vacuuming a :memory: database */
   127    122     int nRes;               /* Bytes of reserved space at the end of each page */
   128    123     int nDb;                /* Number of attached databases */
          124  +  const char *zDbMain;    /* Schema name of database to vacuum */
   129    125   
   130    126     if( !db->autoCommit ){
   131    127       sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
   132    128       return SQLITE_ERROR;
   133    129     }
   134    130     if( db->nVdbeActive>1 ){
   135    131       sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
................................................................................
   139    135     /* Save the current value of the database flags so that it can be 
   140    136     ** restored before returning. Then set the writable-schema flag, and
   141    137     ** disable CHECK and foreign key constraints.  */
   142    138     saved_flags = db->flags;
   143    139     saved_nChange = db->nChange;
   144    140     saved_nTotalChange = db->nTotalChange;
   145    141     saved_mTrace = db->mTrace;
   146         -  db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin;
   147         -  db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder);
          142  +  db->flags |= (SQLITE_WriteSchema | SQLITE_IgnoreChecks
          143  +                 | SQLITE_PreferBuiltin | SQLITE_Vacuum);
          144  +  db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder | SQLITE_CountRows);
   148    145     db->mTrace = 0;
   149    146   
   150         -  pMain = db->aDb[0].pBt;
          147  +  zDbMain = db->aDb[iDb].zDbSName;
          148  +  pMain = db->aDb[iDb].pBt;
   151    149     isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
   152    150   
   153    151     /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
   154    152     ** can be set to 'off' for this file, as it is not recovered if a crash
   155    153     ** occurs anyway. The integrity of the database is maintained by a
   156    154     ** (possibly synchronous) transaction opened on the main database before
   157    155     ** sqlite3BtreeCopyFile() is called.
................................................................................
   161    159     ** that actually made the VACUUM run slower.  Very little journalling
   162    160     ** actually occurs when doing a vacuum since the vacuum_db is initially
   163    161     ** empty.  Only the journal header is written.  Apparently it takes more
   164    162     ** time to parse and run the PRAGMA to turn journalling off than it does
   165    163     ** to write the journal header file.
   166    164     */
   167    165     nDb = db->nDb;
   168         -  if( sqlite3TempInMemory(db) ){
   169         -    zSql = "ATTACH ':memory:' AS vacuum_db;";
   170         -  }else{
   171         -    zSql = "ATTACH '' AS vacuum_db;";
   172         -  }
   173         -  rc = execSql(db, pzErrMsg, zSql);
   174         -  if( db->nDb>nDb ){
   175         -    pDb = &db->aDb[db->nDb-1];
   176         -    assert( strcmp(pDb->zDbSName,"vacuum_db")==0 );
   177         -  }
          166  +  rc = execSql(db, pzErrMsg, "ATTACH''AS vacuum_db");
   178    167     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   179         -  pTemp = db->aDb[db->nDb-1].pBt;
          168  +  assert( (db->nDb-1)==nDb );
          169  +  pDb = &db->aDb[nDb];
          170  +  assert( strcmp(pDb->zDbSName,"vacuum_db")==0 );
          171  +  pTemp = pDb->pBt;
   180    172   
   181    173     /* The call to execSql() to attach the temp database has left the file
   182    174     ** locked (as there was more than one active statement when the transaction
   183    175     ** to read the schema was concluded. Unlock it here so that this doesn't
   184    176     ** cause problems for the call to BtreeSetPageSize() below.  */
   185    177     sqlite3BtreeCommit(pTemp);
   186    178   
................................................................................
   193    185       int nKey;
   194    186       char *zKey;
   195    187       sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey);
   196    188       if( nKey ) db->nextPagesize = 0;
   197    189     }
   198    190   #endif
   199    191   
   200         -  sqlite3BtreeSetCacheSize(pTemp, db->aDb[0].pSchema->cache_size);
          192  +  sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size);
   201    193     sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0));
   202         -  rc = execSql(db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF");
   203         -  if( rc!=SQLITE_OK ) goto end_of_vacuum;
          194  +  sqlite3BtreeSetPagerFlags(pTemp, PAGER_SYNCHRONOUS_OFF);
   204    195   
   205    196     /* Begin a transaction and take an exclusive lock on the main database
   206    197     ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
   207    198     ** to ensure that we do not try to change the page-size on a WAL database.
   208    199     */
   209         -  rc = execSql(db, pzErrMsg, "BEGIN;");
          200  +  rc = execSql(db, pzErrMsg, "BEGIN");
   210    201     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   211    202     rc = sqlite3BtreeBeginTrans(pMain, 2);
   212    203     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   213    204   
   214    205     /* Do not attempt to change the page size for a WAL database */
   215    206     if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
   216    207                                                  ==PAGER_JOURNALMODE_WAL ){
................................................................................
   229    220     sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
   230    221                                              sqlite3BtreeGetAutoVacuum(pMain));
   231    222   #endif
   232    223   
   233    224     /* Query the schema of the main database. Create a mirror schema
   234    225     ** in the temporary database.
   235    226     */
   236         -  rc = execExecSql(db, pzErrMsg,
   237         -      "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) "
   238         -      "  FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
   239         -      "   AND coalesce(rootpage,1)>0"
          227  +  db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */
          228  +  rc = execSqlF(db, pzErrMsg,
          229  +      "SELECT sql FROM \"%w\".sqlite_master"
          230  +      " WHERE type='table'AND name<>'sqlite_sequence'"
          231  +      " AND coalesce(rootpage,1)>0",
          232  +      zDbMain
          233  +  );
          234  +  if( rc!=SQLITE_OK ) goto end_of_vacuum;
          235  +  rc = execSqlF(db, pzErrMsg,
          236  +      "SELECT sql FROM \"%w\".sqlite_master"
          237  +      " WHERE type='index' AND length(sql)>10",
          238  +      zDbMain
   240    239     );
   241    240     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   242         -  rc = execExecSql(db, pzErrMsg,
   243         -      "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)"
   244         -      "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
   245         -  if( rc!=SQLITE_OK ) goto end_of_vacuum;
   246         -  rc = execExecSql(db, pzErrMsg,
   247         -      "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) "
   248         -      "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
   249         -  if( rc!=SQLITE_OK ) goto end_of_vacuum;
          241  +  db->init.iDb = 0;
   250    242   
   251    243     /* Loop through the tables in the main database. For each, do
   252    244     ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
   253    245     ** the contents to the temporary database.
   254    246     */
   255         -  assert( (db->flags & SQLITE_Vacuum)==0 );
   256         -  db->flags |= SQLITE_Vacuum;
   257         -  rc = execExecSql(db, pzErrMsg,
   258         -      "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
   259         -      "|| ' SELECT * FROM main.' || quote(name) || ';'"
   260         -      "FROM main.sqlite_master "
   261         -      "WHERE type = 'table' AND name!='sqlite_sequence' "
   262         -      "  AND coalesce(rootpage,1)>0"
          247  +  rc = execSqlF(db, pzErrMsg,
          248  +      "SELECT'INSERT INTO vacuum_db.'||quote(name)"
          249  +      "||' SELECT*FROM\"%w\".'||quote(name)"
          250  +      "FROM vacuum_db.sqlite_master "
          251  +      "WHERE type='table'AND coalesce(rootpage,1)>0",
          252  +      zDbMain
   263    253     );
   264    254     assert( (db->flags & SQLITE_Vacuum)!=0 );
   265    255     db->flags &= ~SQLITE_Vacuum;
   266    256     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   267         -
   268         -  /* Copy over the sequence table
   269         -  */
   270         -  rc = execExecSql(db, pzErrMsg,
   271         -      "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
   272         -      "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "
   273         -  );
   274         -  if( rc!=SQLITE_OK ) goto end_of_vacuum;
   275         -  rc = execExecSql(db, pzErrMsg,
   276         -      "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
   277         -      "|| ' SELECT * FROM main.' || quote(name) || ';' "
   278         -      "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';"
   279         -  );
   280         -  if( rc!=SQLITE_OK ) goto end_of_vacuum;
   281         -
   282    257   
   283    258     /* Copy the triggers, views, and virtual tables from the main database
   284    259     ** over to the temporary database.  None of these objects has any
   285    260     ** associated storage, so all we have to do is copy their entries
   286    261     ** from the SQLITE_MASTER table.
   287    262     */
   288         -  rc = execSql(db, pzErrMsg,
   289         -      "INSERT INTO vacuum_db.sqlite_master "
   290         -      "  SELECT type, name, tbl_name, rootpage, sql"
   291         -      "    FROM main.sqlite_master"
   292         -      "   WHERE type='view' OR type='trigger'"
   293         -      "      OR (type='table' AND rootpage=0)"
          263  +  rc = execSqlF(db, pzErrMsg,
          264  +      "INSERT INTO vacuum_db.sqlite_master"
          265  +      " SELECT*FROM \"%w\".sqlite_master"
          266  +      " WHERE type IN('view','trigger')"
          267  +      " OR(type='table'AND rootpage=0)",
          268  +      zDbMain
   294    269     );
   295    270     if( rc ) goto end_of_vacuum;
   296    271   
   297    272     /* At this point, there is a write transaction open on both the 
   298    273     ** vacuum database and the main database. Assuming no error occurs,
   299    274     ** both transactions are closed by this block - the main database
   300    275     ** transaction by sqlite3BtreeCopyFile() and the other by an explicit
................................................................................
   340    315     }
   341    316   
   342    317     assert( rc==SQLITE_OK );
   343    318     rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
   344    319   
   345    320   end_of_vacuum:
   346    321     /* Restore the original value of db->flags */
          322  +  db->init.iDb = 0;
   347    323     db->flags = saved_flags;
   348    324     db->nChange = saved_nChange;
   349    325     db->nTotalChange = saved_nTotalChange;
   350    326     db->mTrace = saved_mTrace;
   351    327     sqlite3BtreeSetPageSize(pMain, -1, -1, 1);
   352    328   
   353    329     /* Currently there is an SQL level transaction open on the vacuum

Changes to src/vdbe.c.

  6258   6258     sqlite3VdbeChangeEncoding(pOut, encoding);
  6259   6259     if( rc ) goto abort_due_to_error;
  6260   6260     break;
  6261   6261   };
  6262   6262   #endif /* SQLITE_OMIT_PRAGMA */
  6263   6263   
  6264   6264   #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
  6265         -/* Opcode: Vacuum * * * * *
         6265  +/* Opcode: Vacuum P1 * * * *
  6266   6266   **
  6267         -** Vacuum the entire database.  This opcode will cause other virtual
  6268         -** machines to be created and run.  It may not be called from within
  6269         -** a transaction.
         6267  +** Vacuum the entire database P1.  P1 is 0 for "main", and 2 or more
         6268  +** for an attached database.  The "temp" database may not be vacuumed.
  6270   6269   */
  6271   6270   case OP_Vacuum: {
  6272   6271     assert( p->readOnly==0 );
  6273         -  rc = sqlite3RunVacuum(&p->zErrMsg, db);
         6272  +  rc = sqlite3RunVacuum(&p->zErrMsg, db, pOp->p1);
  6274   6273     if( rc ) goto abort_due_to_error;
  6275   6274     break;
  6276   6275   }
  6277   6276   #endif
  6278   6277   
  6279   6278   #if !defined(SQLITE_OMIT_AUTOVACUUM)
  6280   6279   /* Opcode: IncrVacuum P1 P2 * * *

Changes to test/e_vacuum.test.

   198    198     ATTACH 'test.db2' AS aux;
   199    199     PRAGMA aux.page_size = 1024;
   200    200     CREATE TABLE aux.t3 AS SELECT * FROM t1;
   201    201     DELETE FROM t3;
   202    202   } {}
   203    203   set original_size [file size test.db2]
   204    204   
   205         -# Try everything we can think of to get the aux database vacuumed:
          205  +# Vacuuming the main database does not affect aux
   206    206   do_execsql_test e_vacuum-2.1.3 { VACUUM } {}
   207         -do_execsql_test e_vacuum-2.1.4 { VACUUM aux } {}
   208         -do_execsql_test e_vacuum-2.1.5 { VACUUM 'test.db2' } {}
   209         -
   210         -# Despite our efforts, space in the aux database has not been reclaimed:
   211    207   do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1
   212    208   
   213    209   # EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of
   214    210   # entries in any tables that do not have an explicit INTEGER PRIMARY
   215    211   # KEY.
   216    212   #
   217    213   #   Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when

Added test/vacuum5.test.

            1  +# 2016-08-19
            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  +# This file implements a test for VACUUM on attached databases.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +# If the VACUUM statement is disabled in the current build, skip all
           19  +# the tests in this file.
           20  +#
           21  +ifcapable !vacuum {
           22  +  finish_test
           23  +  return
           24  +}
           25  +
           26  +forcedelete test2.db test3.db
           27  +do_execsql_test vacuum5-1.1 {
           28  +  CREATE TABLE main.t1(a,b);
           29  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
           30  +    INSERT INTO t1(a,b) SELECT x, randomblob(1000) FROM c;
           31  +  CREATE TEMP TABLE ttemp(x,y);
           32  +  INSERT INTO ttemp SELECT * FROM t1;
           33  +  ATTACH 'test2.db' AS x2;
           34  +  ATTACH 'test3.db' AS x3;
           35  +  CREATE TABLE x2.t2(c,d);
           36  +  INSERT INTO t2 SELECT * FROM t1;
           37  +  CREATE TABLE x3.t3(e,f);
           38  +  INSERT INTO t3 SELECT * FROM t1;
           39  +  DELETE FROM t1 WHERE (rowid%3)!=0;
           40  +  DELETE FROM t2 WHERE (rowid%4)!=0;
           41  +  DELETE FROM t3 WHERE (rowid%5)!=0;
           42  +  PRAGMA main.integrity_check;
           43  +  PRAGMA x2.integrity_check;
           44  +  PRAGMA x3.integrity_check;
           45  +} {ok ok ok}
           46  +set size1 [file size test.db]
           47  +set size2 [file size test2.db]
           48  +set size3 [file size test3.db]
           49  +
           50  +do_execsql_test vacuum5-1.2.1 {
           51  +  VACUUM main;
           52  +} {}
           53  +do_test vacuum5-1.2.2 {
           54  +  expr {[file size test.db]<$size1}
           55  +} {1}
           56  +do_test vacuum5-1.2.3 {
           57  +  file size test2.db
           58  +} $size2
           59  +do_test vacuum5-1.2.4 {
           60  +  file size test3.db
           61  +} $size3
           62  +set size1 [file size test.db]
           63  +do_execsql_test vacuum-1.2.5 {
           64  +  DELETE FROM t1;
           65  +  PRAGMA main.integrity_check;
           66  +} {ok}
           67  +
           68  +do_execsql_test vacuum5-1.3.1 {
           69  +  VACUUM x2;
           70  +} {}
           71  +do_test vacuum5-1.3.2 {
           72  +  file size test.db
           73  +} $size1
           74  +do_test vacuum5-1.3.3 {
           75  +  expr {[file size test2.db]<$size2}
           76  +} 1
           77  +do_test vacuum5-1.3.4 {
           78  +  file size test3.db
           79  +} $size3
           80  +set size2 [file size test2.db]
           81  +do_execsql_test vacuum-1.3.5 {
           82  +  DELETE FROM t2;
           83  +  PRAGMA x2.integrity_check;
           84  +} {ok}
           85  +
           86  +do_execsql_test vacuum5-1.4.1 {
           87  +  VACUUM x3;
           88  +} {}
           89  +do_test vacuum5-1.3.2 {
           90  +  file size test.db
           91  +} $size1
           92  +do_test vacuum5-1.3.3 {
           93  +  file size test2.db
           94  +} $size2
           95  +do_test vacuum5-1.3.4 {
           96  +  expr {[file size test3.db]<$size3}
           97  +} 1
           98  +
           99  +# VACUUM is a no-op on the TEMP table
          100  +#
          101  +set sizeTemp [db one {PRAGMA temp.page_count}]
          102  +do_execsql_test vacuum5-1.4.1 {
          103  +  VACUUM temp;
          104  +} {}
          105  +do_execsql_test vacuum5-1.4.2 {
          106  +  PRAGMA temp.page_count;
          107  +} $sizeTemp
          108  +
          109  +do_catchsql_test vacuum5-2.0 {
          110  +  VACUUM olaf;
          111  +} {1 {unknown database olaf}}