*** DRAFT ***

SQLite Requirement Matrix Details
lang_vacuum.html

Index Summary Markup Original


R-14563-40327-52895-35906-18480-59427-48133-58183 tcl slt th3 src

Canonical usage: syntax/vacuum-stmt.html

th3/cov1/vacuum01.test:13

/* IMP: R-14563-40327 */
# EVIDENCE-OF: R-14563-40327 -- syntax diagram vacuum-stmt

R-51469-36013-06451-37309-14378-27141-44520-41230 tcl slt th3 src

Unless SQLite is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages.

tcl/e_vacuum.test:72

/* IMP: R-51469-36013 */
# EVIDENCE-OF: R-51469-36013 Unless SQLite is running in
# "auto_vacuum=FULL" mode, when a large amount of data is deleted from
# the database file it leaves behind empty space, or "free" database
# pages.

R-60541-63059-13982-01066-19481-07947-17056-17575 tcl slt th3 src

Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.

tcl/e_vacuum.test:77

/* IMP: R-60541-63059 */
# EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database
# reclaims this space and reduces the size of the database file.

R-50943-18433-57597-04082-04676-55042-26161-32461 tcl slt th3 src

Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file.

tcl/e_vacuum.test:112

/* IMP: R-50943-18433 */
# EVIDENCE-OF: R-50943-18433 Frequent inserts, updates, and deletes can
# cause the database file to become fragmented - where data for a single
# table or index is scattered around the database file.

R-05791-54928-02769-08459-30778-25258-29853-24680 tcl slt th3 src

Running VACUUM ensures that each table and index is largely stored contiguously within the database file.

tcl/e_vacuum.test:116

/* IMP: R-05791-54928 */
# EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and
# index is largely stored contiguously within the database file.

R-52747-41357-44885-51166-33553-21008-38098-18892 tcl slt th3 src

In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further.

/* IMP: R-52747-41357 */
# EVIDENCE-OF: R-52747-41357 In some cases, VACUUM may also reduce the
# number of partially filled pages in the database, reducing the size of
# the database file further.

R-20474-44465-10719-25363-46434-31101-34097-17120 tcl slt th3 src

Normally, the database page_size and whether or not the database supports auto_vacuum must be configured before the database file is actually created.

tcl/e_vacuum.test:148

/* IMP: R-20474-44465 */
# EVIDENCE-OF: R-20474-44465 Normally, the database page_size and
# whether or not the database supports auto_vacuum must be configured
# before the database file is actually created.

R-08570-19916-06094-14315-21140-23229-64814-49543 tcl slt th3 src

However, when not in write-ahead log mode, the page_size and/or auto_vacuum properties of an existing database may be changed by using the page_size and/or pragma auto_vacuum pragmas and then immediately VACUUMing the database.

tcl/e_vacuum.test:163

/* IMP: R-08570-19916 */
# EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode,
# the page_size and/or auto_vacuum properties of an existing database
# may be changed by using the page_size and/or pragma auto_vacuum
# pragmas and then immediately VACUUMing the database.

R-48521-51450-40029-38325-35476-53863-12365-01087 tcl slt th3 src

When in write-ahead log mode, only the auto_vacuum support property can be changed using VACUUM.

tcl/e_vacuum.test:176

/* IMP: R-48521-51450 */
# EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the
# auto_vacuum support property can be changed using VACUUM.

R-55119-57913-09912-49020-08762-55611-23100-00371 tcl slt th3 src

By default, VACUUM only works only on the main database.

tcl/e_vacuum.test:193

/* IMP: R-55119-57913 */
# EVIDENCE-OF: R-55119-57913 By default, VACUUM only works only on the
# main database.

R-36598-60500-12425-47640-29499-27621-13512-27511 tcl slt th3 src

Attached databases can be vacuumed by appending the appropriate schema-name to the VACUUM statement.

tcl/e_vacuum.test:209

/* IMP: R-36598-60500 */
# EVIDENCE-OF: R-36598-60500 Attached databases can be vacuumed by
# appending the appropriate schema-name to the VACUUM statement.

R-27809-56550-37281-65484-19482-39813-11020-02299 tcl slt th3 src

The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file.

/* IMP: R-27809-56550 */
# EVIDENCE-OF: R-27809-56550 The VACUUM command works by copying the
# contents of the database into a temporary database file and then
# overwriting the original with the contents of the temporary file.

R-48974-49065-61621-06146-19676-25971-04970-57642 tcl slt th3 src

When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction.

/* IMP: R-48974-49065 */
# EVIDENCE-OF: R-48974-49065 When overwriting the original, a rollback
# journal or write-ahead log WAL file is used just as it would be for
# any other database transaction.

R-13903-46567-21188-22822-60227-64496-63410-06513 tcl slt th3 src

This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space.

/* IMP: R-13903-46567 */
# EVIDENCE-OF: R-13903-46567 This means that when VACUUMing a database,
# as much as twice the size of the original database file is required in
# free disk space.

R-17495-17419-60080-02439-41171-12294-54829-31710 tcl slt th3 src

The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.

tcl/e_vacuum.test:214   th3/cov1/vacuum01.test:78

/* IMP: R-17495-17419 */
# EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of
# entries in any tables that do not have an explicit INTEGER PRIMARY
# KEY.

R-49563-33883-18823-43578-33471-11395-18869-08978 tcl slt th3 src

A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.

tcl/e_vacuum.test:248

/* IMP: R-49563-33883 */
# EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open
# transaction, or if there are one or more active SQL statements when it
# is run.

R-55138-13241-38479-09846-46068-29398-26502-36438 tcl slt th3 src

An alternative to using the VACUUM command to reclaim space after data has been deleted is auto-vacuum mode, enabled using the auto_vacuum pragma.

tcl/e_vacuum.test:275

/* IMP: R-55138-13241 */
# EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command
# to reclaim space after data has been deleted is auto-vacuum mode,
# enabled using the auto_vacuum pragma.

R-64844-34873-54978-44161-37358-50690-61472-16160 tcl slt th3 src

When auto_vacuum is enabled for a database free pages may be reclaimed after deleting data, causing the file to shrink, without rebuilding the entire database using VACUUM.

tcl/e_vacuum.test:284

/* IMP: R-64844-34873 */
# EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database
# free pages may be reclaimed after deleting data, causing the file to
# shrink, without rebuilding the entire database using VACUUM.

R-41548-42669-47727-24774-07572-16450-39601-00236 tcl slt th3 src

However, using auto_vacuum can lead to extra database file fragmentation.

/* IMP: R-41548-42669 */
# EVIDENCE-OF: R-41548-42669 However, using auto_vacuum can lead to
# extra database file fragmentation.

R-49389-26640-51376-48756-08197-40049-61248-27840 tcl slt th3 src

And auto_vacuum does not compact partially filled pages of the database as VACUUM does.

/* IMP: R-49389-26640 */
# EVIDENCE-OF: R-49389-26640 And auto_vacuum does not compact partially
# filled pages of the database as VACUUM does.