| OLD | NEW |
|---|
| (Empty) |
| 1 /* | |
| 2 ** 2013-10-01 | |
| 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 ** | |
| 13 ** This program implements a high-speed version of the VACUUM command. | |
| 14 ** It repacks an SQLite database to remove as much unused space as | |
| 15 ** possible and to relocate content sequentially in the file. | |
| 16 ** | |
| 17 ** This program runs faster and uses less temporary disk space than the | |
| 18 ** built-in VACUUM command. On the other hand, this program has a number | |
| 19 ** of important restrictions relative to the built-in VACUUM command. | |
| 20 ** | |
| 21 ** (1) The caller must ensure that no other processes are accessing the | |
| 22 ** database file while the vacuum is taking place. The usual SQLite | |
| 23 ** file locking is insufficient for this. The caller must use | |
| 24 ** external means to make sure only this one routine is reading and | |
| 25 ** writing the database. | |
| 26 ** | |
| 27 ** (2) Database reconfiguration such as page size or auto_vacuum changes | |
| 28 ** are not supported by this utility. | |
| 29 ** | |
| 30 ** (3) The database file might be renamed if a power loss or crash | |
| 31 ** occurs at just the wrong moment. Recovery must be prepared to | |
| 32 ** to deal with the possibly changed filename. | |
| 33 ** | |
| 34 ** This program is intended as a *Demonstration Only*. The intent of this | |
| 35 ** program is to provide example code that application developers can use | |
| 36 ** when creating similar functionality in their applications. | |
| 37 ** | |
| 38 ** To compile this program: | |
| 39 ** | |
| 40 ** cc fast_vacuum.c sqlite3.c | |
| 41 ** | |
| 42 ** Add whatever linker options are required. (Example: "-ldl -lpthread"). | |
| 43 ** Then to run the program: | |
| 44 ** | |
| 45 ** ./a.out file-to-vacuum | |
| 46 ** | |
| 47 */ | |
| 48 #include "sqlite3.h" | |
| 49 #include <stdio.h> | |
| 50 #include <stdlib.h> | |
| 51 | |
| 52 /* | |
| 53 ** Finalize a prepared statement. If an error has occurred, print the | |
| 54 ** error message and exit. | |
| 55 */ | |
| 56 static void vacuumFinalize(sqlite3_stmt *pStmt){ | |
| 57 sqlite3 *db = sqlite3_db_handle(pStmt); | |
| 58 int rc = sqlite3_finalize(pStmt); | |
| 59 if( rc ){ | |
| 60 fprintf(stderr, "finalize error: %s\n", sqlite3_errmsg(db)); | |
| 61 exit(1); | |
| 62 } | |
| 63 } | |
| 64 | |
| 65 /* | |
| 66 ** Execute zSql on database db. The SQL text is printed to standard | |
| 67 ** output. If an error occurs, print an error message and exit the | |
| 68 ** process. | |
| 69 */ | |
| 70 static void execSql(sqlite3 *db, const char *zSql){ | |
| 71 sqlite3_stmt *pStmt; | |
| 72 if( !zSql ){ | |
| 73 fprintf(stderr, "out of memory!\n"); | |
| 74 exit(1); | |
| 75 } | |
| 76 printf("%s;\n", zSql); | |
| 77 if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ | |
| 78 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); | |
| 79 exit(1); | |
| 80 } | |
| 81 sqlite3_step(pStmt); | |
| 82 vacuumFinalize(pStmt); | |
| 83 } | |
| 84 | |
| 85 /* | |
| 86 ** Execute zSql on database db. The zSql statement returns exactly | |
| 87 ** one column. Execute this return value as SQL on the same database. | |
| 88 ** | |
| 89 ** The zSql statement is printed on standard output prior to being | |
| 90 ** run. If any errors occur, an error is printed and the process | |
| 91 ** exits. | |
| 92 */ | |
| 93 static void execExecSql(sqlite3 *db, const char *zSql){ | |
| 94 sqlite3_stmt *pStmt; | |
| 95 int rc; | |
| 96 | |
| 97 printf("%s;\n", zSql); | |
| 98 rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); | |
| 99 if( rc!=SQLITE_OK ){ | |
| 100 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); | |
| 101 exit(1); | |
| 102 } | |
| 103 while( SQLITE_ROW==sqlite3_step(pStmt) ){ | |
| 104 execSql(db, (char*)sqlite3_column_text(pStmt, 0)); | |
| 105 } | |
| 106 vacuumFinalize(pStmt); | |
| 107 } | |
| 108 | |
| 109 | |
| 110 int main(int argc, char **argv){ | |
| 111 sqlite3 *db; /* Connection to the database file */ | |
| 112 int rc; /* Return code from SQLite interface calls */ | |
| 113 sqlite3_uint64 r; /* A random number */ | |
| 114 const char *zDbToVacuum; /* Database to be vacuumed */ | |
| 115 char *zBackupDb; /* Backup copy of the original database */ | |
| 116 char *zTempDb; /* Temporary database */ | |
| 117 char *zSql; /* An SQL statement */ | |
| 118 | |
| 119 if( argc!=2 ){ | |
| 120 fprintf(stderr, "Usage: %s DATABASE\n", argv[0]); | |
| 121 return 1; | |
| 122 } | |
| 123 | |
| 124 /* Identify the database file to be vacuumed and open it. | |
| 125 */ | |
| 126 zDbToVacuum = argv[1]; | |
| 127 printf("-- open database file \"%s\"\n", zDbToVacuum); | |
| 128 rc = sqlite3_open(zDbToVacuum, &db); | |
| 129 if( rc ){ | |
| 130 fprintf(stderr, "%s: %s\n", zDbToVacuum, sqlite3_errstr(rc)); | |
| 131 return 1; | |
| 132 } | |
| 133 | |
| 134 /* Create names for two other files. zTempDb will be a new database | |
| 135 ** into which we construct a vacuumed copy of zDbToVacuum. zBackupDb | |
| 136 ** will be a new name for zDbToVacuum after it is vacuumed. | |
| 137 */ | |
| 138 sqlite3_randomness(sizeof(r), &r); | |
| 139 zTempDb = sqlite3_mprintf("%s-vacuum-%016llx", zDbToVacuum, r); | |
| 140 zBackupDb = sqlite3_mprintf("%s-backup-%016llx", zDbToVacuum, r); | |
| 141 | |
| 142 /* Attach the zTempDb database to the database connection. | |
| 143 */ | |
| 144 zSql = sqlite3_mprintf("ATTACH '%q' AS vacuum_db;", zTempDb); | |
| 145 execSql(db, zSql); | |
| 146 sqlite3_free(zSql); | |
| 147 | |
| 148 /* TODO: | |
| 149 ** Set the page_size and auto_vacuum mode for zTempDb here, if desired. | |
| 150 */ | |
| 151 | |
| 152 /* The vacuum will occur inside of a transaction. Set writable_schema | |
| 153 ** to ON so that we can directly update the sqlite_master table in the | |
| 154 ** zTempDb database. | |
| 155 */ | |
| 156 execSql(db, "PRAGMA writable_schema=ON"); | |
| 157 execSql(db, "BEGIN"); | |
| 158 | |
| 159 | |
| 160 /* Query the schema of the main database. Create a mirror schema | |
| 161 ** in the temporary database. | |
| 162 */ | |
| 163 execExecSql(db, | |
| 164 "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " | |
| 165 " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" | |
| 166 " AND rootpage>0" | |
| 167 ); | |
| 168 execExecSql(db, | |
| 169 "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" | |
| 170 " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %'" | |
| 171 ); | |
| 172 execExecSql(db, | |
| 173 "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " | |
| 174 " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'" | |
| 175 ); | |
| 176 | |
| 177 /* Loop through the tables in the main database. For each, do | |
| 178 ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy | |
| 179 ** the contents to the temporary database. | |
| 180 */ | |
| 181 execExecSql(db, | |
| 182 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " | |
| 183 "|| ' SELECT * FROM main.' || quote(name) " | |
| 184 "FROM main.sqlite_master " | |
| 185 "WHERE type = 'table' AND name!='sqlite_sequence' " | |
| 186 " AND rootpage>0" | |
| 187 ); | |
| 188 | |
| 189 /* Copy over the sequence table | |
| 190 */ | |
| 191 execExecSql(db, | |
| 192 "SELECT 'DELETE FROM vacuum_db.' || quote(name) " | |
| 193 "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence'" | |
| 194 ); | |
| 195 execExecSql(db, | |
| 196 "SELECT 'INSERT INTO vacuum_db.' || quote(name) " | |
| 197 "|| ' SELECT * FROM main.' || quote(name) " | |
| 198 "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence'" | |
| 199 ); | |
| 200 | |
| 201 /* Copy the triggers, views, and virtual tables from the main database | |
| 202 ** over to the temporary database. None of these objects has any | |
| 203 ** associated storage, so all we have to do is copy their entries | |
| 204 ** from the SQLITE_MASTER table. | |
| 205 */ | |
| 206 execSql(db, | |
| 207 "INSERT INTO vacuum_db.sqlite_master " | |
| 208 " SELECT type, name, tbl_name, rootpage, sql" | |
| 209 " FROM main.sqlite_master" | |
| 210 " WHERE type='view' OR type='trigger'" | |
| 211 " OR (type='table' AND rootpage=0)" | |
| 212 ); | |
| 213 | |
| 214 /* Commit the transaction and close the database | |
| 215 */ | |
| 216 execSql(db, "COMMIT"); | |
| 217 printf("-- close database\n"); | |
| 218 sqlite3_close(db); | |
| 219 | |
| 220 | |
| 221 /* At this point, zDbToVacuum is unchanged. zTempDb contains a | |
| 222 ** vacuumed copy of zDbToVacuum. Rearrange filenames so that | |
| 223 ** zTempDb becomes thenew zDbToVacuum. | |
| 224 */ | |
| 225 printf("-- rename \"%s\" to \"%s\"\n", zDbToVacuum, zBackupDb); | |
| 226 rename(zDbToVacuum, zBackupDb); | |
| 227 printf("-- rename \"%s\" to \"%s\"\n", zTempDb, zDbToVacuum); | |
| 228 rename(zTempDb, zDbToVacuum); | |
| 229 | |
| 230 /* Release allocated memory */ | |
| 231 sqlite3_free(zTempDb); | |
| 232 sqlite3_free(zBackupDb); | |
| 233 return 0; | |
| 234 } | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
