drh | c11d4f9 | 2003-04-06 21:08:24 +0000 | [diff] [blame] | 1 | /* |
| 2 | ** 2003 April 6 |
| 3 | ** |
| 4 | ** The author disclaims copyright to this source code. In place of |
| 5 | ** a legal notice, here is a blessing: |
| 6 | ** |
| 7 | ** May you do good and not evil. |
| 8 | ** May you find forgiveness for yourself and forgive others. |
| 9 | ** May you share freely, never taking more than you give. |
| 10 | ** |
| 11 | ************************************************************************* |
| 12 | ** This file contains code used to implement the VACUUM command. |
| 13 | ** |
| 14 | ** Most of the code in this file may be omitted by defining the |
| 15 | ** SQLITE_OMIT_VACUUM macro. |
drh | c11d4f9 | 2003-04-06 21:08:24 +0000 | [diff] [blame] | 16 | */ |
| 17 | #include "sqliteInt.h" |
danielk1977 | f420804 | 2005-12-06 17:48:31 +0000 | [diff] [blame] | 18 | #include "vdbeInt.h" |
drh | c11d4f9 | 2003-04-06 21:08:24 +0000 | [diff] [blame] | 19 | |
drh | fdbcdee | 2007-03-27 14:44:50 +0000 | [diff] [blame] | 20 | #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH) |
drh | cda455b | 2010-02-24 19:23:56 +0000 | [diff] [blame] | 21 | |
| 22 | /* |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 23 | ** Execute zSql on database db. |
| 24 | ** |
drh | 6a754dc | 2016-08-19 15:12:38 +0000 | [diff] [blame] | 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. |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 28 | ** |
| 29 | ** The execSqlF() routine does the same thing, except it accepts |
| 30 | ** a format string as its third argument |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 31 | */ |
drh | cda455b | 2010-02-24 19:23:56 +0000 | [diff] [blame] | 32 | static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){ |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 33 | sqlite3_stmt *pStmt; |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 34 | int rc; |
| 35 | |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 36 | /* printf("SQL: [%s]\n", zSql); fflush(stdout); */ |
| 37 | rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0); |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 38 | if( rc!=SQLITE_OK ) return rc; |
drh | 6a754dc | 2016-08-19 15:12:38 +0000 | [diff] [blame] | 39 | while( SQLITE_ROW==(rc = sqlite3_step(pStmt)) ){ |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 40 | const char *zSubSql = (const char*)sqlite3_column_text(pStmt,0); |
drh | 6a754dc | 2016-08-19 15:12:38 +0000 | [diff] [blame] | 41 | assert( sqlite3_strnicmp(zSql,"SELECT",6)==0 ); |
drh | 34b27ed | 2018-05-03 21:51:30 +0000 | [diff] [blame] | 42 | /* The secondary SQL must be one of CREATE TABLE, CREATE INDEX, |
| 43 | ** or INSERT. Historically there have been attacks that first |
| 44 | ** corrupt the sqlite_master.sql field with other kinds of statements |
| 45 | ** then run VACUUM to get those statements to execute at inappropriate |
| 46 | ** times. */ |
| 47 | if( zSubSql |
| 48 | && (strncmp(zSubSql,"CRE",3)==0 || strncmp(zSubSql,"INS",3)==0) |
| 49 | ){ |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 50 | rc = execSql(db, pzErrMsg, zSubSql); |
| 51 | if( rc!=SQLITE_OK ) break; |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 52 | } |
| 53 | } |
drh | 6a754dc | 2016-08-19 15:12:38 +0000 | [diff] [blame] | 54 | assert( rc!=SQLITE_ROW ); |
| 55 | if( rc==SQLITE_DONE ) rc = SQLITE_OK; |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 56 | if( rc ){ |
| 57 | sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db)); |
| 58 | } |
| 59 | (void)sqlite3_finalize(pStmt); |
| 60 | return rc; |
| 61 | } |
| 62 | static int execSqlF(sqlite3 *db, char **pzErrMsg, const char *zSql, ...){ |
| 63 | char *z; |
| 64 | va_list ap; |
| 65 | int rc; |
| 66 | va_start(ap, zSql); |
| 67 | z = sqlite3VMPrintf(db, zSql, ap); |
| 68 | va_end(ap); |
| 69 | if( z==0 ) return SQLITE_NOMEM; |
| 70 | rc = execSql(db, pzErrMsg, z); |
| 71 | sqlite3DbFree(db, z); |
| 72 | return rc; |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 73 | } |
| 74 | |
drh | c11d4f9 | 2003-04-06 21:08:24 +0000 | [diff] [blame] | 75 | /* |
drh | 8b8d28dd | 2013-09-30 11:01:28 +0000 | [diff] [blame] | 76 | ** The VACUUM command is used to clean up the database, |
drh | c11d4f9 | 2003-04-06 21:08:24 +0000 | [diff] [blame] | 77 | ** collapse free space, etc. It is modelled after the VACUUM command |
drh | 8b8d28dd | 2013-09-30 11:01:28 +0000 | [diff] [blame] | 78 | ** in PostgreSQL. The VACUUM command works as follows: |
drh | c11d4f9 | 2003-04-06 21:08:24 +0000 | [diff] [blame] | 79 | ** |
drh | 8b8d28dd | 2013-09-30 11:01:28 +0000 | [diff] [blame] | 80 | ** (1) Create a new transient database file |
| 81 | ** (2) Copy all content from the database being vacuumed into |
| 82 | ** the new transient database file |
| 83 | ** (3) Copy content from the transient database back into the |
| 84 | ** original database. |
| 85 | ** |
| 86 | ** The transient database requires temporary disk space approximately |
| 87 | ** equal to the size of the original database. The copy operation of |
| 88 | ** step (3) requires additional temporary disk space approximately equal |
| 89 | ** to the size of the original database for the rollback journal. |
| 90 | ** Hence, temporary disk space that is approximately 2x the size of the |
peter.d.reid | 60ec914 | 2014-09-06 16:39:46 +0000 | [diff] [blame] | 91 | ** original database is required. Every page of the database is written |
drh | 8b8d28dd | 2013-09-30 11:01:28 +0000 | [diff] [blame] | 92 | ** approximately 3 times: Once for step (2) and twice for step (3). |
| 93 | ** Two writes per page are required in step (3) because the original |
| 94 | ** database content must be written into the rollback journal prior to |
| 95 | ** overwriting the database with the vacuumed content. |
| 96 | ** |
| 97 | ** Only 1x temporary space and only 1x writes would be required if |
drh | 86a11b8 | 2014-11-07 13:24:29 +0000 | [diff] [blame] | 98 | ** the copy of step (3) were replaced by deleting the original database |
drh | 8b8d28dd | 2013-09-30 11:01:28 +0000 | [diff] [blame] | 99 | ** and renaming the transient database as the original. But that will |
| 100 | ** not work if other processes are attached to the original database. |
| 101 | ** And a power loss in between deleting the original and renaming the |
| 102 | ** transient would cause the database file to appear to be deleted |
| 103 | ** following reboot. |
drh | c11d4f9 | 2003-04-06 21:08:24 +0000 | [diff] [blame] | 104 | */ |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 105 | void sqlite3Vacuum(Parse *pParse, Token *pNm, Expr *pInto){ |
danielk1977 | 4adee20 | 2004-05-08 08:23:19 +0000 | [diff] [blame] | 106 | Vdbe *v = sqlite3GetVdbe(pParse); |
drh | eeea412 | 2017-02-18 13:47:11 +0000 | [diff] [blame] | 107 | int iDb = 0; |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 108 | if( v==0 ) goto build_vacuum_end; |
drh | 29e7800 | 2019-04-23 22:00:39 +0000 | [diff] [blame] | 109 | if( pParse->nErr ) goto build_vacuum_end; |
drh | eeea412 | 2017-02-18 13:47:11 +0000 | [diff] [blame] | 110 | if( pNm ){ |
| 111 | #ifndef SQLITE_BUG_COMPATIBLE_20160819 |
| 112 | /* Default behavior: Report an error if the argument to VACUUM is |
| 113 | ** not recognized */ |
| 114 | iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm); |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 115 | if( iDb<0 ) goto build_vacuum_end; |
drh | eeea412 | 2017-02-18 13:47:11 +0000 | [diff] [blame] | 116 | #else |
| 117 | /* When SQLITE_BUG_COMPATIBLE_20160819 is defined, unrecognized arguments |
| 118 | ** to VACUUM are silently ignored. This is a back-out of a bug fix that |
| 119 | ** occurred on 2016-08-19 (https://www.sqlite.org/src/info/083f9e6270). |
| 120 | ** The buggy behavior is required for binary compatibility with some |
| 121 | ** legacy applications. */ |
| 122 | iDb = sqlite3FindDb(pParse->db, pNm); |
| 123 | if( iDb<0 ) iDb = 0; |
| 124 | #endif |
| 125 | } |
| 126 | if( iDb!=1 ){ |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 127 | int iIntoReg = 0; |
drh | ee751fa | 2019-01-02 14:34:46 +0000 | [diff] [blame] | 128 | if( pInto && sqlite3ResolveSelfReference(pParse,0,0,pInto,0)==0 ){ |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 129 | iIntoReg = ++pParse->nMem; |
| 130 | sqlite3ExprCode(pParse, pInto, iIntoReg); |
drh | b0b7db9 | 2018-12-07 17:28:28 +0000 | [diff] [blame] | 131 | } |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 132 | sqlite3VdbeAddOp2(v, OP_Vacuum, iDb, iIntoReg); |
| 133 | sqlite3VdbeUsesBtree(v, iDb); |
danielk1977 | 96fb0dd | 2004-06-30 09:49:22 +0000 | [diff] [blame] | 134 | } |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 135 | build_vacuum_end: |
| 136 | sqlite3ExprDelete(pParse->db, pInto); |
drh | 6f8c91c | 2003-12-07 00:24:35 +0000 | [diff] [blame] | 137 | return; |
| 138 | } |
| 139 | |
| 140 | /* |
| 141 | ** This routine implements the OP_Vacuum opcode of the VDBE. |
| 142 | */ |
drh | 6775257 | 2019-04-04 15:25:52 +0000 | [diff] [blame] | 143 | SQLITE_NOINLINE int sqlite3RunVacuum( |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 144 | char **pzErrMsg, /* Write error message here */ |
| 145 | sqlite3 *db, /* Database connection */ |
| 146 | int iDb, /* Which attached DB to vacuum */ |
drh | d0f820a | 2019-03-19 20:42:42 +0000 | [diff] [blame] | 147 | sqlite3_value *pOut /* Write results here, if not NULL. VACUUM INTO */ |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 148 | ){ |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 149 | int rc = SQLITE_OK; /* Return code from service routines */ |
drh | f2a611c | 2004-09-05 00:33:43 +0000 | [diff] [blame] | 150 | Btree *pMain; /* The database being vacuumed */ |
drh | 0058d84 | 2006-10-10 13:07:36 +0000 | [diff] [blame] | 151 | Btree *pTemp; /* The temporary database we vacuum into */ |
drh | 70d5dfb | 2018-12-06 16:50:55 +0000 | [diff] [blame] | 152 | u32 saved_mDbFlags; /* Saved value of db->mDbFlags */ |
| 153 | u64 saved_flags; /* Saved value of db->flags */ |
drh | e63b2c2 | 2008-05-21 13:44:13 +0000 | [diff] [blame] | 154 | int saved_nChange; /* Saved value of db->nChange */ |
| 155 | int saved_nTotalChange; /* Saved value of db->nTotalChange */ |
drh | d0f820a | 2019-03-19 20:42:42 +0000 | [diff] [blame] | 156 | u32 saved_openFlags; /* Saved value of db->openFlags */ |
drh | 3d2a529 | 2016-07-13 22:55:01 +0000 | [diff] [blame] | 157 | u8 saved_mTrace; /* Saved trace settings */ |
drh | 0058d84 | 2006-10-10 13:07:36 +0000 | [diff] [blame] | 158 | Db *pDb = 0; /* Database to detach at end of vacuum */ |
danielk1977 | 43996e8 | 2009-05-30 10:46:10 +0000 | [diff] [blame] | 159 | int isMemDb; /* True if vacuuming a :memory: database */ |
drh | acd0781 | 2010-05-10 14:10:57 +0000 | [diff] [blame] | 160 | int nRes; /* Bytes of reserved space at the end of each page */ |
| 161 | int nDb; /* Number of attached databases */ |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 162 | const char *zDbMain; /* Schema name of database to vacuum */ |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 163 | const char *zOut; /* Name of output file */ |
danielk1977 | 3a3f38e | 2005-05-22 06:49:56 +0000 | [diff] [blame] | 164 | |
drh | 663d56d | 2009-01-22 23:04:45 +0000 | [diff] [blame] | 165 | if( !db->autoCommit ){ |
| 166 | sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction"); |
drh | eacc881 | 2019-04-04 18:20:25 +0000 | [diff] [blame] | 167 | return SQLITE_ERROR; /* IMP: R-12218-18073 */ |
drh | 663d56d | 2009-01-22 23:04:45 +0000 | [diff] [blame] | 168 | } |
drh | 4f7d3a5 | 2013-06-27 23:54:02 +0000 | [diff] [blame] | 169 | if( db->nVdbeActive>1 ){ |
dan | 099d147 | 2010-09-24 09:32:45 +0000 | [diff] [blame] | 170 | sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress"); |
drh | eacc881 | 2019-04-04 18:20:25 +0000 | [diff] [blame] | 171 | return SQLITE_ERROR; /* IMP: R-15610-35227 */ |
dan | 099d147 | 2010-09-24 09:32:45 +0000 | [diff] [blame] | 172 | } |
drh | d0f820a | 2019-03-19 20:42:42 +0000 | [diff] [blame] | 173 | saved_openFlags = db->openFlags; |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 174 | if( pOut ){ |
| 175 | if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){ |
| 176 | sqlite3SetString(pzErrMsg, db, "non-text filename"); |
| 177 | return SQLITE_ERROR; |
| 178 | } |
| 179 | zOut = (const char*)sqlite3_value_text(pOut); |
drh | d0f820a | 2019-03-19 20:42:42 +0000 | [diff] [blame] | 180 | db->openFlags &= ~SQLITE_OPEN_READONLY; |
| 181 | db->openFlags |= SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE; |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 182 | }else{ |
| 183 | zOut = ""; |
| 184 | } |
drh | 663d56d | 2009-01-22 23:04:45 +0000 | [diff] [blame] | 185 | |
dan | 75cbd98 | 2009-09-21 16:06:03 +0000 | [diff] [blame] | 186 | /* Save the current value of the database flags so that it can be |
| 187 | ** restored before returning. Then set the writable-schema flag, and |
| 188 | ** disable CHECK and foreign key constraints. */ |
drh | 0cd2d4c | 2005-11-03 02:15:02 +0000 | [diff] [blame] | 189 | saved_flags = db->flags; |
drh | 6775257 | 2019-04-04 15:25:52 +0000 | [diff] [blame] | 190 | saved_mDbFlags = db->mDbFlags; |
drh | e63b2c2 | 2008-05-21 13:44:13 +0000 | [diff] [blame] | 191 | saved_nChange = db->nChange; |
| 192 | saved_nTotalChange = db->nTotalChange; |
drh | 3d2a529 | 2016-07-13 22:55:01 +0000 | [diff] [blame] | 193 | saved_mTrace = db->mTrace; |
drh | 8257aa8 | 2017-07-26 19:59:13 +0000 | [diff] [blame] | 194 | db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks; |
drh | 6775257 | 2019-04-04 15:25:52 +0000 | [diff] [blame] | 195 | db->mDbFlags |= DBFLAG_PreferBuiltin | DBFLAG_Vacuum; |
drh | d5b44d6 | 2018-12-06 17:06:02 +0000 | [diff] [blame] | 196 | db->flags &= ~(u64)(SQLITE_ForeignKeys | SQLITE_ReverseOrder |
drh | 0f0d3dd | 2018-11-06 19:26:04 +0000 | [diff] [blame] | 197 | | SQLITE_Defensive | SQLITE_CountRows); |
drh | 1637a51 | 2016-07-13 23:18:27 +0000 | [diff] [blame] | 198 | db->mTrace = 0; |
drh | 45a304e | 2003-04-25 02:43:08 +0000 | [diff] [blame] | 199 | |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 200 | zDbMain = db->aDb[iDb].zDbSName; |
| 201 | pMain = db->aDb[iDb].pBt; |
danielk1977 | 43996e8 | 2009-05-30 10:46:10 +0000 | [diff] [blame] | 202 | isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain)); |
drh | 13bff81 | 2003-04-15 01:19:47 +0000 | [diff] [blame] | 203 | |
danielk1977 | 46c43ed | 2004-06-30 06:30:25 +0000 | [diff] [blame] | 204 | /* Attach the temporary database as 'vacuum_db'. The synchronous pragma |
| 205 | ** can be set to 'off' for this file, as it is not recovered if a crash |
| 206 | ** occurs anyway. The integrity of the database is maintained by a |
| 207 | ** (possibly synchronous) transaction opened on the main database before |
| 208 | ** sqlite3BtreeCopyFile() is called. |
| 209 | ** |
| 210 | ** An optimisation would be to use a non-journaled pager. |
drh | 4697988 | 2008-04-30 16:38:23 +0000 | [diff] [blame] | 211 | ** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but |
| 212 | ** that actually made the VACUUM run slower. Very little journalling |
| 213 | ** actually occurs when doing a vacuum since the vacuum_db is initially |
| 214 | ** empty. Only the journal header is written. Apparently it takes more |
| 215 | ** time to parse and run the PRAGMA to turn journalling off than it does |
| 216 | ** to write the journal header file. |
danielk1977 | 46c43ed | 2004-06-30 06:30:25 +0000 | [diff] [blame] | 217 | */ |
drh | acd0781 | 2010-05-10 14:10:57 +0000 | [diff] [blame] | 218 | nDb = db->nDb; |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 219 | rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS vacuum_db", zOut); |
drh | d0f820a | 2019-03-19 20:42:42 +0000 | [diff] [blame] | 220 | db->openFlags = saved_openFlags; |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 221 | if( rc!=SQLITE_OK ) goto end_of_vacuum; |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 222 | assert( (db->nDb-1)==nDb ); |
| 223 | pDb = &db->aDb[nDb]; |
| 224 | assert( strcmp(pDb->zDbSName,"vacuum_db")==0 ); |
| 225 | pTemp = pDb->pBt; |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 226 | if( pOut ){ |
drh | 7464f57 | 2018-12-07 23:48:41 +0000 | [diff] [blame] | 227 | sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp)); |
| 228 | i64 sz = 0; |
| 229 | if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){ |
| 230 | rc = SQLITE_ERROR; |
| 231 | sqlite3SetString(pzErrMsg, db, "output file already exists"); |
| 232 | goto end_of_vacuum; |
| 233 | } |
drh | 6775257 | 2019-04-04 15:25:52 +0000 | [diff] [blame] | 234 | db->mDbFlags |= DBFLAG_VacuumInto; |
drh | 7464f57 | 2018-12-07 23:48:41 +0000 | [diff] [blame] | 235 | } |
drh | ad0961b | 2015-02-21 00:19:25 +0000 | [diff] [blame] | 236 | nRes = sqlite3BtreeGetOptimalReserve(pMain); |
drh | cdb7a0f | 2008-06-17 01:03:25 +0000 | [diff] [blame] | 237 | |
| 238 | /* A VACUUM cannot change the pagesize of an encrypted database. */ |
| 239 | #ifdef SQLITE_HAS_CODEC |
| 240 | if( db->nextPagesize ){ |
| 241 | extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*); |
| 242 | int nKey; |
| 243 | char *zKey; |
drh | 76cf858 | 2017-06-16 13:43:37 +0000 | [diff] [blame] | 244 | sqlite3CodecGetKey(db, iDb, (void**)&zKey, &nKey); |
drh | cdb7a0f | 2008-06-17 01:03:25 +0000 | [diff] [blame] | 245 | if( nKey ) db->nextPagesize = 0; |
| 246 | } |
| 247 | #endif |
| 248 | |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 249 | sqlite3BtreeSetCacheSize(pTemp, db->aDb[iDb].pSchema->cache_size); |
drh | 9d60875 | 2016-07-26 04:49:43 +0000 | [diff] [blame] | 250 | sqlite3BtreeSetSpillSize(pTemp, sqlite3BtreeSetSpillSize(pMain,0)); |
dan | 75ba676 | 2016-11-02 14:50:19 +0000 | [diff] [blame] | 251 | sqlite3BtreeSetPagerFlags(pTemp, PAGER_SYNCHRONOUS_OFF|PAGER_CACHESPILL); |
dan | f602963 | 2012-02-28 17:57:34 +0000 | [diff] [blame] | 252 | |
| 253 | /* Begin a transaction and take an exclusive lock on the main database |
| 254 | ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below, |
| 255 | ** to ensure that we do not try to change the page-size on a WAL database. |
| 256 | */ |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 257 | rc = execSql(db, pzErrMsg, "BEGIN"); |
dan | f602963 | 2012-02-28 17:57:34 +0000 | [diff] [blame] | 258 | if( rc!=SQLITE_OK ) goto end_of_vacuum; |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 259 | rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0); |
dan | f602963 | 2012-02-28 17:57:34 +0000 | [diff] [blame] | 260 | if( rc!=SQLITE_OK ) goto end_of_vacuum; |
| 261 | |
drh | 811bdbd | 2010-05-05 03:39:53 +0000 | [diff] [blame] | 262 | /* Do not attempt to change the page size for a WAL database */ |
drh | 0b9b430 | 2010-06-11 17:01:24 +0000 | [diff] [blame] | 263 | if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain)) |
| 264 | ==PAGER_JOURNALMODE_WAL ){ |
drh | 811bdbd | 2010-05-05 03:39:53 +0000 | [diff] [blame] | 265 | db->nextPagesize = 0; |
| 266 | } |
| 267 | |
drh | ce4869f | 2009-04-02 20:16:58 +0000 | [diff] [blame] | 268 | if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes, 0) |
| 269 | || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes, 0)) |
drh | 5dc348a | 2009-05-05 17:37:22 +0000 | [diff] [blame] | 270 | || NEVER(db->mallocFailed) |
danielk1977 | f653d78 | 2008-03-20 11:04:21 +0000 | [diff] [blame] | 271 | ){ |
mistachkin | fad3039 | 2016-02-13 23:43:46 +0000 | [diff] [blame] | 272 | rc = SQLITE_NOMEM_BKPT; |
danielk1977 | 884c5b3 | 2007-03-06 16:03:55 +0000 | [diff] [blame] | 273 | goto end_of_vacuum; |
| 274 | } |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 275 | |
danielk1977 | 42741be | 2005-01-08 12:42:39 +0000 | [diff] [blame] | 276 | #ifndef SQLITE_OMIT_AUTOVACUUM |
drh | ddac25c | 2007-12-05 01:38:23 +0000 | [diff] [blame] | 277 | sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac : |
| 278 | sqlite3BtreeGetAutoVacuum(pMain)); |
danielk1977 | 42741be | 2005-01-08 12:42:39 +0000 | [diff] [blame] | 279 | #endif |
| 280 | |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 281 | /* Query the schema of the main database. Create a mirror schema |
| 282 | ** in the temporary database. |
| 283 | */ |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 284 | db->init.iDb = nDb; /* force new CREATE statements into vacuum_db */ |
| 285 | rc = execSqlF(db, pzErrMsg, |
| 286 | "SELECT sql FROM \"%w\".sqlite_master" |
| 287 | " WHERE type='table'AND name<>'sqlite_sequence'" |
drh | 34b27ed | 2018-05-03 21:51:30 +0000 | [diff] [blame] | 288 | " AND coalesce(rootpage,1)>0", |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 289 | zDbMain |
drh | 66b224c | 2006-09-11 11:13:26 +0000 | [diff] [blame] | 290 | ); |
danielk1977 | 27c7743 | 2004-11-22 13:35:41 +0000 | [diff] [blame] | 291 | if( rc!=SQLITE_OK ) goto end_of_vacuum; |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 292 | rc = execSqlF(db, pzErrMsg, |
| 293 | "SELECT sql FROM \"%w\".sqlite_master" |
drh | 34b27ed | 2018-05-03 21:51:30 +0000 | [diff] [blame] | 294 | " WHERE type='index'", |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 295 | zDbMain |
| 296 | ); |
danielk1977 | 27c7743 | 2004-11-22 13:35:41 +0000 | [diff] [blame] | 297 | if( rc!=SQLITE_OK ) goto end_of_vacuum; |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 298 | db->init.iDb = 0; |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 299 | |
| 300 | /* Loop through the tables in the main database. For each, do |
drh | 397308d | 2009-10-20 15:01:58 +0000 | [diff] [blame] | 301 | ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 302 | ** the contents to the temporary database. |
| 303 | */ |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 304 | rc = execSqlF(db, pzErrMsg, |
| 305 | "SELECT'INSERT INTO vacuum_db.'||quote(name)" |
| 306 | "||' SELECT*FROM\"%w\".'||quote(name)" |
| 307 | "FROM vacuum_db.sqlite_master " |
| 308 | "WHERE type='table'AND coalesce(rootpage,1)>0", |
| 309 | zDbMain |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 310 | ); |
drh | 8257aa8 | 2017-07-26 19:59:13 +0000 | [diff] [blame] | 311 | assert( (db->mDbFlags & DBFLAG_Vacuum)!=0 ); |
drh | 6775257 | 2019-04-04 15:25:52 +0000 | [diff] [blame] | 312 | db->mDbFlags &= ~DBFLAG_Vacuum; |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 313 | if( rc!=SQLITE_OK ) goto end_of_vacuum; |
| 314 | |
drh | fdd48a7 | 2006-09-11 23:45:48 +0000 | [diff] [blame] | 315 | /* Copy the triggers, views, and virtual tables from the main database |
| 316 | ** over to the temporary database. None of these objects has any |
| 317 | ** associated storage, so all we have to do is copy their entries |
| 318 | ** from the SQLITE_MASTER table. |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 319 | */ |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 320 | rc = execSqlF(db, pzErrMsg, |
| 321 | "INSERT INTO vacuum_db.sqlite_master" |
| 322 | " SELECT*FROM \"%w\".sqlite_master" |
| 323 | " WHERE type IN('view','trigger')" |
| 324 | " OR(type='table'AND rootpage=0)", |
| 325 | zDbMain |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 326 | ); |
drh | fdd48a7 | 2006-09-11 23:45:48 +0000 | [diff] [blame] | 327 | if( rc ) goto end_of_vacuum; |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 328 | |
dan | c5f20a0 | 2011-10-07 16:57:59 +0000 | [diff] [blame] | 329 | /* At this point, there is a write transaction open on both the |
| 330 | ** vacuum database and the main database. Assuming no error occurs, |
| 331 | ** both transactions are closed by this block - the main database |
| 332 | ** transaction by sqlite3BtreeCopyFile() and the other by an explicit |
| 333 | ** call to sqlite3BtreeCommit(). |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 334 | */ |
drh | 5dc348a | 2009-05-05 17:37:22 +0000 | [diff] [blame] | 335 | { |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 336 | u32 meta; |
drh | 8cbd373 | 2005-02-12 00:19:30 +0000 | [diff] [blame] | 337 | int i; |
| 338 | |
| 339 | /* This array determines which meta meta values are preserved in the |
| 340 | ** vacuum. Even entries are the meta value number and odd entries |
| 341 | ** are an increment to apply to the meta value after the vacuum. |
| 342 | ** The increment is used to increase the schema cookie so that other |
| 343 | ** connections to the same database will know to reread the schema. |
| 344 | */ |
| 345 | static const unsigned char aCopy[] = { |
danielk1977 | 0d19f7a | 2009-06-03 11:25:07 +0000 | [diff] [blame] | 346 | BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */ |
| 347 | BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size */ |
| 348 | BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */ |
| 349 | BTREE_USER_VERSION, 0, /* Preserve the user version */ |
drh | b8a67ec | 2013-05-01 20:36:23 +0000 | [diff] [blame] | 350 | BTREE_APPLICATION_ID, 0, /* Preserve the application id */ |
drh | 8cbd373 | 2005-02-12 00:19:30 +0000 | [diff] [blame] | 351 | }; |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 352 | |
drh | c9ac5ca | 2005-11-04 22:03:30 +0000 | [diff] [blame] | 353 | assert( 1==sqlite3BtreeIsInTrans(pTemp) ); |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 354 | assert( pOut!=0 || 1==sqlite3BtreeIsInTrans(pMain) ); |
danielk1977 | 1d850a7 | 2004-05-31 08:26:49 +0000 | [diff] [blame] | 355 | |
drh | 8cbd373 | 2005-02-12 00:19:30 +0000 | [diff] [blame] | 356 | /* Copy Btree meta values */ |
danielk1977 | 00e1361 | 2008-11-17 19:18:54 +0000 | [diff] [blame] | 357 | for(i=0; i<ArraySize(aCopy); i+=2){ |
drh | 5dc348a | 2009-05-05 17:37:22 +0000 | [diff] [blame] | 358 | /* GetMeta() and UpdateMeta() cannot fail in this context because |
| 359 | ** we already have page 1 loaded into cache and marked dirty. */ |
danielk1977 | 602b466 | 2009-07-02 07:47:33 +0000 | [diff] [blame] | 360 | sqlite3BtreeGetMeta(pMain, aCopy[i], &meta); |
drh | 8cbd373 | 2005-02-12 00:19:30 +0000 | [diff] [blame] | 361 | rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]); |
drh | 5dc348a | 2009-05-05 17:37:22 +0000 | [diff] [blame] | 362 | if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum; |
drh | 8cbd373 | 2005-02-12 00:19:30 +0000 | [diff] [blame] | 363 | } |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 364 | |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 365 | if( pOut==0 ){ |
drh | b0b7db9 | 2018-12-07 17:28:28 +0000 | [diff] [blame] | 366 | rc = sqlite3BtreeCopyFile(pMain, pTemp); |
drh | b0b7db9 | 2018-12-07 17:28:28 +0000 | [diff] [blame] | 367 | } |
danielk1977 | 369f27e | 2004-06-15 11:40:04 +0000 | [diff] [blame] | 368 | if( rc!=SQLITE_OK ) goto end_of_vacuum; |
drh | c9ac5ca | 2005-11-04 22:03:30 +0000 | [diff] [blame] | 369 | rc = sqlite3BtreeCommit(pTemp); |
| 370 | if( rc!=SQLITE_OK ) goto end_of_vacuum; |
danielk1977 | 06249db | 2008-08-23 16:17:55 +0000 | [diff] [blame] | 371 | #ifndef SQLITE_OMIT_AUTOVACUUM |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 372 | if( pOut==0 ){ |
drh | b0b7db9 | 2018-12-07 17:28:28 +0000 | [diff] [blame] | 373 | sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp)); |
| 374 | } |
danielk1977 | 06249db | 2008-08-23 16:17:55 +0000 | [diff] [blame] | 375 | #endif |
drh | a5f6683 | 2003-04-18 02:31:04 +0000 | [diff] [blame] | 376 | } |
drh | 13bff81 | 2003-04-15 01:19:47 +0000 | [diff] [blame] | 377 | |
drh | 5dc348a | 2009-05-05 17:37:22 +0000 | [diff] [blame] | 378 | assert( rc==SQLITE_OK ); |
drh | 2f6239e | 2018-12-08 00:43:08 +0000 | [diff] [blame] | 379 | if( pOut==0 ){ |
drh | b0b7db9 | 2018-12-07 17:28:28 +0000 | [diff] [blame] | 380 | rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1); |
| 381 | } |
danielk1977 | f653d78 | 2008-03-20 11:04:21 +0000 | [diff] [blame] | 382 | |
drh | 13bff81 | 2003-04-15 01:19:47 +0000 | [diff] [blame] | 383 | end_of_vacuum: |
drh | 0cd2d4c | 2005-11-03 02:15:02 +0000 | [diff] [blame] | 384 | /* Restore the original value of db->flags */ |
drh | 9ef5e77 | 2016-08-19 14:20:56 +0000 | [diff] [blame] | 385 | db->init.iDb = 0; |
drh | 8257aa8 | 2017-07-26 19:59:13 +0000 | [diff] [blame] | 386 | db->mDbFlags = saved_mDbFlags; |
drh | 0cd2d4c | 2005-11-03 02:15:02 +0000 | [diff] [blame] | 387 | db->flags = saved_flags; |
drh | e63b2c2 | 2008-05-21 13:44:13 +0000 | [diff] [blame] | 388 | db->nChange = saved_nChange; |
| 389 | db->nTotalChange = saved_nTotalChange; |
drh | 3d2a529 | 2016-07-13 22:55:01 +0000 | [diff] [blame] | 390 | db->mTrace = saved_mTrace; |
drh | 16adb77 | 2010-05-05 04:11:44 +0000 | [diff] [blame] | 391 | sqlite3BtreeSetPageSize(pMain, -1, -1, 1); |
danielk1977 | 3a3f38e | 2005-05-22 06:49:56 +0000 | [diff] [blame] | 392 | |
danielk1977 | 46c43ed | 2004-06-30 06:30:25 +0000 | [diff] [blame] | 393 | /* Currently there is an SQL level transaction open on the vacuum |
| 394 | ** database. No locks are held on any other files (since the main file |
| 395 | ** was committed at the btree level). So it safe to end the transaction |
| 396 | ** by manually setting the autoCommit flag to true and detaching the |
| 397 | ** vacuum database. The vacuum_db journal file is deleted when the pager |
| 398 | ** is closed by the DETACH. |
| 399 | */ |
| 400 | db->autoCommit = 1; |
danielk1977 | 261919c | 2005-12-06 12:52:59 +0000 | [diff] [blame] | 401 | |
danielk1977 | 0203bde | 2006-01-11 16:10:20 +0000 | [diff] [blame] | 402 | if( pDb ){ |
danielk1977 | 0203bde | 2006-01-11 16:10:20 +0000 | [diff] [blame] | 403 | sqlite3BtreeClose(pDb->pBt); |
danielk1977 | 0203bde | 2006-01-11 16:10:20 +0000 | [diff] [blame] | 404 | pDb->pBt = 0; |
| 405 | pDb->pSchema = 0; |
danielk1977 | 96fb0dd | 2004-06-30 09:49:22 +0000 | [diff] [blame] | 406 | } |
danielk1977 | f420804 | 2005-12-06 17:48:31 +0000 | [diff] [blame] | 407 | |
drh | 7464f57 | 2018-12-07 23:48:41 +0000 | [diff] [blame] | 408 | /* This both clears the schemas and reduces the size of the db->aDb[] |
| 409 | ** array. */ |
| 410 | sqlite3ResetAllSchemasOfConnection(db); |
drh | 89dec81 | 2005-04-28 19:03:37 +0000 | [diff] [blame] | 411 | |
danielk1977 | 3df6b25 | 2004-05-29 10:23:19 +0000 | [diff] [blame] | 412 | return rc; |
drh | 3f7d4e4 | 2004-07-24 14:35:58 +0000 | [diff] [blame] | 413 | } |
drh | c7792fa | 2011-04-02 16:28:52 +0000 | [diff] [blame] | 414 | |
drh | fdbcdee | 2007-03-27 14:44:50 +0000 | [diff] [blame] | 415 | #endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */ |