diff options
| -rw-r--r-- | manifest | 11 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | tool/fast_vacuum.c | 228 |
3 files changed, 235 insertions, 6 deletions
@@ -1,5 +1,5 @@ -C Change\sthe\s".dump"\scommand\sin\sthe\scommand-line\sshell\sso\sthat\sit\sCOMMITs\nif\sthere\sare\sdatabase\scorruption\serrors\sbut\sinvokes\sROLLBACK\son\sany\sother\nkind\sof\serror. -D 2013-10-01T15:30:05.555 +C Add\sthe\s"fast_vacuum.c"\sdemonstration\sprogram\sto\sthe\stool/\ssubdirectgory. +D 2013-10-01T19:02:56.492 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 5e41da95d92656a5004b03d3576e8b226858a28e F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -1075,6 +1075,7 @@ F tool/build-shell.sh 950f47c6174f1eea171319438b93ba67ff5bf367 F tool/checkSpacing.c 810e51703529a204fc4e1eb060e9ab663e3c06d2 F tool/diffdb.c 7524b1b5df217c20cd0431f6789851a4e0cb191b F tool/extract.c 054069d81b095fbdc189a6f5d4466e40380505e2 +F tool/fast_vacuum.c 9051e839fd48666e2445dab3272b949a739ae6dd F tool/fragck.tcl 5265a95126abcf6ab357f7efa544787e5963f439 F tool/genfkey.README cf68fddd4643bbe3ff8e31b8b6d8b0a1b85e20f4 F tool/genfkey.test 4196a8928b78f51d54ef58e99e99401ab2f0a7e5 @@ -1117,7 +1118,7 @@ F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh fbc018d67fd7395f440c28f33ef0f94420226381 F tool/wherecosttest.c f407dc4c79786982a475261866a161cd007947ae F tool/win/sqlite.vsix 030f3eeaf2cb811a3692ab9c14d021a75ce41fff -P 9c9fa151e9a3bdab0264c08b66cbcc89d61bfca5 -R 9ff7757bda2b86fabee2936e154616af +P 473234632ff7617680ab151076153f5c1088e55b +R 9adaa284cfb79b24d0278e44a5cb5af8 U drh -Z 9ff7be50e2937033d9684ee83bcaaf82 +Z 58c24793b78d731684b9ef9e115423c6 diff --git a/manifest.uuid b/manifest.uuid index 91cde9c57..cfc22a0f7 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -473234632ff7617680ab151076153f5c1088e55b
\ No newline at end of file +2b60320d57a4638ebf97a2854527649c671d108e
\ No newline at end of file diff --git a/tool/fast_vacuum.c b/tool/fast_vacuum.c new file mode 100644 index 000000000..3cdda62db --- /dev/null +++ b/tool/fast_vacuum.c @@ -0,0 +1,228 @@ +/* +** 2013-10-01 +** +** 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 program implements a high-speed version of the VACUUM command. +** It repacks an SQLite database to remove as much unused space as +** possible and to relocate content sequentially in the file. +** +** This program runs faster and uses less temporary disk space than the +** built-in VACUUM command. On the other hand, this program has a number +** of important restrictions relative to the built-in VACUUM command. +** +** (1) The caller must ensure that no other processes are accessing the +** database file while the vacuum is taking place. The usual SQLite +** file locking is insufficient for this. The caller must use +** external means to make sure only this one routine is reading and +** writing the database. +** +** (2) Database reconfiguration such as page size or auto_vacuum changes +** are not supported by this utility. +** +** (3) The database file might be renamed if a power loss or crash +** occurs at just the wrong moment. Recovery must be prepared to +** to deal with the possibly changed filename. +** +** This program is intended as a *Demonstration Only*. The intent of this +** program is to provide example code that application developers can use +** when creating similar functionality in their applications. +** +** To compile this program: +** +** cc fast_vacuum.c sqlite3.c +** +** Add whatever linker options are required. (Example: "-ldl -lpthread"). +** Then to run the program: +** +** ./a.out file-to-vacuum +** +*/ +#include "sqlite3.h" +#include <stdio.h> +#include <stdlib.h> + +/* +** Finalize a prepared statement. If an error has occurred, print the +** error message and exit. +*/ +static void vacuumFinalize(sqlite3_stmt *pStmt){ + sqlite3 *db = sqlite3_db_handle(pStmt); + int rc = sqlite3_finalize(pStmt); + if( rc ){ + fprintf(stderr, "finalize error: %s\n", sqlite3_errmsg(db)); + exit(1); + } +} + +/* +** Execute zSql on database db. The SQL text i +*/ +static void execSql(sqlite3 *db, const char *zSql){ + sqlite3_stmt *pStmt; + if( !zSql ){ + fprintf(stderr, "out of memory!\n"); + exit(1); + } + printf("%s;\n", zSql); + if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){ + fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); + exit(1); + } + sqlite3_step(pStmt); + vacuumFinalize(pStmt); +} + +/* +** Execute zSql on database db. The statement returns exactly +** one column. Execute this as SQL on the same database. +*/ +static void execExecSql(sqlite3 *db, const char *zSql){ + sqlite3_stmt *pStmt; + int rc; + + printf("%s;\n", zSql); + rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); + if( rc!=SQLITE_OK ){ + fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); + exit(1); + } + while( SQLITE_ROW==sqlite3_step(pStmt) ){ + execSql(db, (char*)sqlite3_column_text(pStmt, 0)); + } + vacuumFinalize(pStmt); +} + + +int main(int argc, char **argv){ + sqlite3 *db; /* Connection to the database file */ + int rc; /* Return code from SQLite interface calls */ + sqlite3_uint64 r; /* A random number */ + const char *zDbToVacuum; /* Database to be vacuumed */ + char *zBackupDb; /* Backup copy of the original database */ + char *zTempDb; /* Temporary database */ + char *zSql; /* An SQL statement */ + + if( argc!=2 ){ + fprintf(stderr, "Usage: %s DATABASE\n", argv[0]); + return 1; + } + + /* Identify the database file to be vacuumed and open it. + */ + zDbToVacuum = argv[1]; + printf("-- open database file \"%s\"\n", zDbToVacuum); + rc = sqlite3_open(zDbToVacuum, &db); + if( rc ){ + fprintf(stderr, "%s: %s\n", zDbToVacuum, sqlite3_errstr(rc)); + return 1; + } + + /* Create names for two other files. zTempDb will be a new database + ** into which we construct a vacuumed copy of zDbToVacuum. zBackupDb + ** will be a new name for zDbToVacuum after it is vacuumed. + */ + sqlite3_randomness(sizeof(r), &r); + zTempDb = sqlite3_mprintf("%s-vacuum-%016llx", zDbToVacuum, r); + zBackupDb = sqlite3_mprintf("%s-backup-%016llx", zDbToVacuum, r); + + /* Attach the zTempDb database to the database connection. + */ + zSql = sqlite3_mprintf("ATTACH '%q' AS vacuum_db;", zTempDb); + execSql(db, zSql); + sqlite3_free(zSql); + + /* TODO: + ** Set the page_size and auto_vacuum mode for zTempDb here, if desired. + */ + + /* The vacuum will occur inside of a transaction. Set writable_schema + ** to ON so that we can directly update the sqlite_master table in the + ** zTempDb database. + */ + execSql(db, "PRAGMA writable_schema=ON"); + execSql(db, "BEGIN"); + + + /* Query the schema of the main database. Create a mirror schema + ** in the temporary database. + */ + execExecSql(db, + "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) " + " FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'" + " AND rootpage>0" + ); + execExecSql(db, + "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)" + " FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %'" + ); + execExecSql(db, + "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) " + " FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'" + ); + + /* Loop through the tables in the main database. For each, do + ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy + ** the contents to the temporary database. + */ + execExecSql(db, + "SELECT 'INSERT INTO vacuum_db.' || quote(name) " + "|| ' SELECT * FROM main.' || quote(name) " + "FROM main.sqlite_master " + "WHERE type = 'table' AND name!='sqlite_sequence' " + " AND rootpage>0" + ); + + /* Copy over the sequence table + */ + execExecSql(db, + "SELECT 'DELETE FROM vacuum_db.' || quote(name) " + "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence'" + ); + execExecSql(db, + "SELECT 'INSERT INTO vacuum_db.' || quote(name) " + "|| ' SELECT * FROM main.' || quote(name) " + "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence'" + ); + + /* Copy the triggers, views, and virtual tables from the main database + ** over to the temporary database. None of these objects has any + ** associated storage, so all we have to do is copy their entries + ** from the SQLITE_MASTER table. + */ + execSql(db, + "INSERT INTO vacuum_db.sqlite_master " + " SELECT type, name, tbl_name, rootpage, sql" + " FROM main.sqlite_master" + " WHERE type='view' OR type='trigger'" + " OR (type='table' AND rootpage=0)" + ); + + /* Commit the transaction and close the database + */ + execSql(db, "COMMIT"); + printf("-- close database\n"); + sqlite3_close(db); + + + /* At this point, zDbToVacuum is unchanged. zTempDb contains a + ** vacuumed copy of zDbToVacuum. Rearrange filenames so that + ** zTempDb becomes thenew zDbToVacuum. + */ + printf("-- rename \"%s\" to \"%s\"\n", zDbToVacuum, zBackupDb); + rename(zDbToVacuum, zBackupDb); + printf("-- rename \"%s\" to \"%s\"\n", zTempDb, zDbToVacuum); + rename(zTempDb, zDbToVacuum); + + /* Release allocated memory */ + sqlite3_free(zTempDb); + sqlite3_free(zBackupDb); + return 0; +} |