SQLite getting started and Analysis (9)-VACUUM command Analysis

The vacuum command is an extended function of SQLite. It imitates the same commands in PostgreSQL. If VACUUM is called with a table name or index name, the table or index will be sorted. In SQLite 1.0, the VACUUM command calls gdbm_reorganize () to sort back-end database files.

GDBM backend is removed from SQLITE 2.0.0, and VACUUM is invalid. In version 2.8.1, VACUUM is implemented again. The index name or table name is ignored.

When an object (table, index, or trigger) in the database is revoked, a blank space is left. It makes the database larger than the required size, but can speed up the insertion. Real-time insertion and deletion can make the database file structure messy and slow the access to the database content. The VACUUM Command copies the master database file to the temporary database and reloads it from the temporary database to the master database to sort out the database files. This will remove blank pages, arrange the table data adjacent to each other, and sort out the database file structure. You cannot perform the preceding operations on the attached database file.

This command does not work if active transactions exist. This command is invalid for the in-memory database.

In SQLite3.1, you can use the auto-vacuum mode to replace the VACUUM command and use auto_vacuum pragma to enable this mode.

Use of VACUUM: VACUUM main
After performing the VACUUM operation on the data discussed in section 3.4, only the first page is left.

VACUUM implementation:

Code Implementation (vcuum. c ): Int sqlite3RunVacuum (char ** pzErrMsg, sqlite3 * db ){
/// // Step 4 /// //////////////////////////////////////// /////
ZSql = sqlite3MPrintf ("ATTACH '% Q' AS vacuum_db;", zTemp );
// Create a temporary database vacuum_db
Rc = execSql (db, zSql );
/// // Step 4 /// //////////////////////////////////////// /////
// Start a transaction
Rc = execSql (db, "begin exclusive ;");
/// // Step 4 /// //////////////////////////////////////// /////
// Create all tables, indexes, and views of the main database in vacuum_db, and insert data of all tables, indexes, and views in the main database
// Go to vacuum_db. Create a main image in vacuum_db.
Rc = execExecSql (db,
"SELECT 'create TABLE vacuum_db. '| substr (SQL, 14,00000000 )"
"FROM sqlite_master WHERE type = 'table' AND name! = 'Sqlite _ sequence '");
If (rc! = SQLITE_ OK) goto end_of_vacuum;
Rc = execExecSql (db,
"SELECT 'create INDEX vacuum_db. '| substr (SQL, 14,00000000 )"
"FROM sqlite_master WHERE SQL LIKE 'create INDEX % '");
If (rc! = SQLITE_ OK) goto end_of_vacuum;
Rc = execExecSql (db,
"SELECT 'create unique index vacuum_db. '| substr (SQL, usd0000 )"
"FROM sqlite_master WHERE SQL LIKE 'create UNIQUE INDEX % '");
If (rc! = SQLITE_ OK) goto end_of_vacuum;
Rc = execExecSql (db,
"SELECT 'create VIEW vacuum_db. '| substr (SQL )"
"FROM sqlite_master WHERE type = 'view '"
);
Rc = execExecSql (db,
"SELECT 'insert INTO vacuum_db. '| quote (name )"
"| 'Select * from' | quote (name) | ';'"
"FROM sqlite_master"
"WHERE type = 'table' AND name! = 'Sqlite _ sequence ';"
);
/// // Step 4 /// //////////////////////////////////////// /////
// Copy the data of the data file corresponding to the vacumm_db database to the data file corresponding to the main database, one page and one page
Rc = sqlite3BtreeCopyFile (pMain, pTemp );
/// // Step 4 /// //////////////////////////////////////// /////
// Submit the transaction
Rc = sqlite3BtreeCommit (pTemp );
If (rc! = SQLITE_ OK) goto end_of_vacuum;
Rc = sqlite3BtreeCommit (pMain );
}