From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Thu, 16 Oct 2014 22:51:35 -0400 Subject: Imported Upstream version 3.2.0 --- tool/fast_vacuum.c | 234 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 234 insertions(+) create mode 100644 tool/fast_vacuum.c (limited to 'tool/fast_vacuum.c') diff --git a/tool/fast_vacuum.c b/tool/fast_vacuum.c new file mode 100644 index 0000000..6a50dcc --- /dev/null +++ b/tool/fast_vacuum.c @@ -0,0 +1,234 @@ +/* +** 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 +#include + +/* +** 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 is printed to standard +** output. If an error occurs, print an error message and exit the +** process. +*/ +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 zSql statement returns exactly +** one column. Execute this return value as SQL on the same database. +** +** The zSql statement is printed on standard output prior to being +** run. If any errors occur, an error is printed and the process +** exits. +*/ +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; +} -- cgit v1.2.3