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. 16 ** 17 ** $Id: vacuum.c,v 1.21 2004/06/03 16:08:42 danielk1977 Exp $ 18 */ 19 #include "sqliteInt.h" 20 #include "os.h" 21 22 #if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM 23 /* 24 ** Generate a random name of 20 character in length. 25 */ 26 static void randomName(unsigned char *zBuf){ 27 static const unsigned char zChars[] = 28 "abcdefghijklmnopqrstuvwxyz" 29 "0123456789"; 30 int i; 31 sqlite3Randomness(20, zBuf); 32 for(i=0; i<20; i++){ 33 zBuf[i] = zChars[ zBuf[i]%(sizeof(zChars)-1) ]; 34 } 35 } 36 37 /* 38 ** Execute zSql on database db. Return an error code. 39 */ 40 static int execSql(sqlite3 *db, const char *zSql){ 41 sqlite3_stmt *pStmt; 42 if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ 43 return sqlite3_errcode(db); 44 } 45 while( SQLITE_ROW==sqlite3_step(pStmt) ); 46 return sqlite3_finalize(pStmt); 47 } 48 49 /* 50 ** Execute zSql on database db. The statement returns exactly 51 ** one column. Execute this as SQL on the same database. 52 */ 53 static int execExecSql(sqlite3 *db, const char *zSql){ 54 sqlite3_stmt *pStmt; 55 int rc; 56 57 rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); 58 if( rc!=SQLITE_OK ) return rc; 59 60 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 61 rc = execSql(db, sqlite3_column_text(pStmt, 0)); 62 if( rc!=SQLITE_OK ){ 63 sqlite3_finalize(pStmt); 64 return rc; 65 } 66 } 67 68 return sqlite3_finalize(pStmt); 69 } 70 71 #endif 72 73 /* 74 ** The non-standard VACUUM command is used to clean up the database, 75 ** collapse free space, etc. It is modelled after the VACUUM command 76 ** in PostgreSQL. 77 ** 78 ** In version 1.0.x of SQLite, the VACUUM command would call 79 ** gdbm_reorganize() on all the database tables. But beginning 80 ** with 2.0.0, SQLite no longer uses GDBM so this command has 81 ** become a no-op. 82 */ 83 void sqlite3Vacuum(Parse *pParse, Token *pTableName){ 84 Vdbe *v = sqlite3GetVdbe(pParse); 85 sqlite3VdbeAddOp(v, OP_Vacuum, 0, 0); 86 return; 87 } 88 89 /* 90 ** This routine implements the OP_Vacuum opcode of the VDBE. 91 */ 92 int sqlite3RunVacuum(char **pzErrMsg, sqlite *db){ 93 int rc = SQLITE_OK; /* Return code from service routines */ 94 #if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM 95 const char *zFilename; /* full pathname of the database file */ 96 int nFilename; /* number of characters in zFilename[] */ 97 char *zTemp = 0; /* a temporary file in same directory as zFilename */ 98 int i; /* Loop counter */ 99 Btree *pTemp; 100 101 char *zSql = 0; 102 sqlite3_stmt *pStmt = 0; 103 104 if( !db->autoCommit ){ 105 sqlite3SetString(pzErrMsg, "cannot VACUUM from within a transaction", 106 (char*)0); 107 rc = SQLITE_ERROR; 108 goto end_of_vacuum; 109 } 110 111 /* Get the full pathname of the database file and create a 112 ** temporary filename in the same directory as the original file. 113 */ 114 zFilename = sqlite3BtreeGetFilename(db->aDb[0].pBt); 115 if( zFilename==0 ){ 116 /* The in-memory database. Do nothing. */ 117 goto end_of_vacuum; 118 } 119 nFilename = strlen(zFilename); 120 zTemp = sqliteMalloc( nFilename+100 ); 121 if( zTemp==0 ){ 122 rc = SQLITE_NOMEM; 123 goto end_of_vacuum; 124 } 125 strcpy(zTemp, zFilename); 126 for(i=0; i<10; i++){ 127 zTemp[nFilename] = '-'; 128 randomName((unsigned char*)&zTemp[nFilename+1]); 129 if( !sqlite3OsFileExists(zTemp) ) break; 130 } 131 132 /* Attach the temporary database as 'vacuum' */ 133 zSql = sqlite3MPrintf("ATTACH '%s' AS vacuum_db;", zTemp); 134 if( !zSql ){ 135 rc = SQLITE_NOMEM; 136 goto end_of_vacuum; 137 } 138 rc = execSql(db, zSql); 139 sqliteFree(zSql); 140 zSql = 0; 141 if( rc!=SQLITE_OK ) goto end_of_vacuum; 142 143 /* Begin a transaction */ 144 rc = execSql(db, "BEGIN;"); 145 if( rc!=SQLITE_OK ) goto end_of_vacuum; 146 147 /* Query the schema of the main database. Create a mirror schema 148 ** in the temporary database. 149 */ 150 rc = execExecSql(db, 151 "SELECT 'CREATE ' || type || ' vacuum_db.' || " 152 "substr(sql, length(type)+9, 1000000) " 153 "FROM sqlite_master " 154 "WHERE type != 'trigger' AND sql IS NOT NULL " 155 "ORDER BY (type != 'table');" 156 ); 157 if( rc!=SQLITE_OK ) goto end_of_vacuum; 158 159 /* Loop through the tables in the main database. For each, do 160 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy 161 ** the contents to the temporary database. 162 */ 163 rc = execExecSql(db, 164 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " 165 "|| ' SELECT * FROM ' || quote(name) || ';'" 166 "FROM sqlite_master " 167 "WHERE type = 'table';" 168 ); 169 if( rc!=SQLITE_OK ) goto end_of_vacuum; 170 171 /* Copy the triggers from the main database to the temporary database. 172 ** This was deferred before in case the triggers interfered with copying 173 ** the data. It's possible the indices should be deferred until this 174 ** point also. 175 */ 176 rc = execExecSql(db, 177 "SELECT 'CREATE ' || type || ' vacuum_db.' || " 178 "substr(sql, length(type)+9, 1000000) " 179 "FROM sqlite_master " 180 "WHERE type = 'trigger' AND sql IS NOT NULL;" 181 ); 182 if( rc!=SQLITE_OK ) goto end_of_vacuum; 183 184 185 /* At this point, unless the main db was completely empty, there is now a 186 ** transaction open on the vacuum database, but not on the main database. 187 ** Open a btree level transaction on the main database. This allows a 188 ** call to sqlite3BtreeCopyFile(). The main database btree level 189 ** transaction is then committed, so the SQL level never knows it was 190 ** opened for writing. This way, the SQL transaction used to create the 191 ** temporary database never needs to be committed. 192 */ 193 pTemp = db->aDb[db->nDb-1].pBt; 194 if( sqlite3BtreeIsInTrans(pTemp) ){ 195 Btree *pMain = db->aDb[0].pBt; 196 u32 meta; 197 198 assert( 0==sqlite3BtreeIsInTrans(pMain) ); 199 rc = sqlite3BtreeBeginTrans(db->aDb[0].pBt, 1, 0); 200 if( rc!=SQLITE_OK ) goto end_of_vacuum; 201 202 /* Copy Btree meta values 3 and 4. These correspond to SQL layer meta 203 ** values 2 and 3, the default values of a couple of pragmas. 204 */ 205 rc = sqlite3BtreeGetMeta(pMain, 3, &meta); 206 if( rc!=SQLITE_OK ) goto end_of_vacuum; 207 rc = sqlite3BtreeUpdateMeta(pTemp, 3, meta); 208 if( rc!=SQLITE_OK ) goto end_of_vacuum; 209 rc = sqlite3BtreeGetMeta(pMain, 4, &meta); 210 if( rc!=SQLITE_OK ) goto end_of_vacuum; 211 rc = sqlite3BtreeUpdateMeta(pTemp, 4, meta); 212 if( rc!=SQLITE_OK ) goto end_of_vacuum; 213 214 rc = sqlite3BtreeCopyFile(pMain, pTemp); 215 rc = sqlite3BtreeCommit(pMain); 216 } 217 218 end_of_vacuum: 219 execSql(db, "DETACH vacuum_db;"); 220 execSql(db, "ROLLBACK;"); 221 if( zTemp ){ 222 sqlite3OsDelete(zTemp); 223 sqliteFree(zTemp); 224 } 225 if( zSql ) sqliteFree( zSql ); 226 if( pStmt ) sqlite3_finalize( pStmt ); 227 #endif 228 return rc; 229 } 230