Documentation Source Text

  }
  qualified-table-name {
     stack
       {line {optx /schema-name .} /table-name {opt AS /alias}}
       {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }
  vacuum-stmt {
      line VACUUM {opt /schema-name}
  }
  comment-syntax {
    or
      {line -- {loop nil /anything-except-newline} 
           {or /newline /end-of-input}}
      {line /* {loop nil /anything-except-*/}
           {or */ /end-of-input}}

  }
  qualified-table-name {
     stack
       {line {optx /schema-name .} /table-name {opt AS /alias}}
       {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }
  vacuum-stmt {
      line VACUUM {opt /schema-name} {opt INTO /filename}
  }
  comment-syntax {
    or
      {line -- {loop nil /anything-except-newline} 
           {or /newline /end-of-input}}
      {line /* {loop nil /anything-except-*/}
           {or */ /end-of-input}}

<p>
  The online backup API is [sqlite3_backup_init | documented here].
  The remainder of this page contains two C language examples illustrating 
  common uses of the API and discussions thereof. Reading these examples
  is no substitute for reading the API documentation!






<h2>Example 1: Loading and Saving In-Memory Databases</h2>

<tcl>

proc C_Code {text} {
  hd_puts "<pre>\n"
  set iLine 0

<p>
  The online backup API is [sqlite3_backup_init | documented here].
  The remainder of this page contains two C language examples illustrating 
  common uses of the API and discussions thereof. Reading these examples
  is no substitute for reading the API documentation!

<p>
  Update: The [VACUUM INTO] command introduced in 
  SQLite version 3.27.0 ([dateof:3.27.0]) can serve as an
  alternative to the backup API.

<h2>Example 1: Loading and Saving In-Memory Databases</h2>

<tcl>

proc C_Code {text} {
  hd_puts "<pre>\n"
  set iLine 0

  <li> <p> ^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. ^Running VACUUM ensures that each table and
     index is largely stored contiguously within the database file. ^In some
     cases, VACUUM may also reduce the number of partially filled pages in
     the database, reducing the size of the database file further.









  <li> <p> ^Normally, the database [page_size] and whether or not the database
     supports [auto_vacuum] must be configured before the database file is
     actually created. ^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 pragma|page_size]  and/or 
     [auto_vacuum|pragma auto_vacuum] pragmas and then immediately VACUUMing
................................................................................
<yyterm>schema-name</yyterm> to the VACUUM statement.

<p><b>Compatibility Warning:</b> The ability to vacuum attached databases was
added in [version 3.15.0] ([dateof:3.15.0]).  Prior to that, a 
<yyterm>schema-name</yyterm> added to the
VACUUM statement would be silently ignored and the "main" schema would be
vacuumed.</p>








































<p>^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. ^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. ^This means that when VACUUMing a database, 
as much as twice the size of the original database file is required in free
disk space.





<p>^The VACUUM command may change the [ROWID | ROWIDs] of entries in any
tables that do not have an explicit [INTEGER PRIMARY KEY].
</p>

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

  <li> <p> ^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. ^Running VACUUM ensures that each table and
     index is largely stored contiguously within the database file. ^In some
     cases, VACUUM may also reduce the number of partially filled pages in
     the database, reducing the size of the database file further.

  <li> <p> When content is deleted from an SQLite database, the content is not
     usually erased but rather the space used to hold the content is marked as
     being available for reuse.  This can allow deleted content to be recovered
     by a hacker or by forensic analysis.  Running VACUUM will clean the database
     of all traces of deleted content, thus preventing an adversary from recovering
     deleted content.  Using VACUUM in this way is an alternative to setting
     [PRAGMA security_delete=ON]. 

  <li> <p> ^Normally, the database [page_size] and whether or not the database
     supports [auto_vacuum] must be configured before the database file is
     actually created. ^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 pragma|page_size]  and/or 
     [auto_vacuum|pragma auto_vacuum] pragmas and then immediately VACUUMing
................................................................................
<yyterm>schema-name</yyterm> to the VACUUM statement.

<p><b>Compatibility Warning:</b> The ability to vacuum attached databases was
added in [version 3.15.0] ([dateof:3.15.0]).  Prior to that, a 
<yyterm>schema-name</yyterm> added to the
VACUUM statement would be silently ignored and the "main" schema would be
vacuumed.</p>

<tcl>hd_fragment vacuuminto {VACUUM INTO}</tcl>
<h3>VACUUM with an INTO clause</h3>

<p>^If the INTO clause is included, then the original database file is
unchanged and a new database is created in the filename given by the
argument to the INTO clause.  The new database will contain the same
logical content as the original database, fully vacuumed.

<p>
The VACUUM command with an INTO clause is an alternative to the
[backup API] for generating backup copies of a live database.
The advantage of using VACUUM INTO is that the resulting backup
database is minimal in size and hence the amount of filesystem
I/O may be reduced.  Also, all deleted content is purged from the
backup, leaving behind no forensic traces.  On the other hand,
the [backup API] uses fewer CPU cycles and can be executed
incrementally.

<p>
^The filename in the INTO clause can be an arbitrary SQL expression
that evaluates to a string.
^The file named by the INTO clause must not previously exist, or
else it must be an empty file, or the VACUUM INTO command will
fail with an error.

<p>
The VACUUM INTO command is transactional in the sense that
the generated output database is a consistent snapshot of the
orgininal database.  However, if the VACUUM INTO command is
interrupted by a unplanned shutdown or power lose, in that
case the generated output database might not be complete.
Also, SQLite does not invoke fsync() or FlushFileBuffers()
on the generated database to ensure that it has reached
non-volatile storage before completing.


<tcl>hd_fragment howvacuumworks {how vacuum works}</tcl>
<h3>How VACUUM works</h3>

<p>^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. ^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. ^This means that when VACUUMing a database, 
as much as twice the size of the original database file is required in free
disk space.

<p>^The VACUUM INTO command works the same way except that it uses the file
named on the INTO clause in place of the temporary database and omits the
step of copying the vacuumed database back over top of the original database.

<p>^The VACUUM command may change the [ROWID | ROWIDs] of entries in any
tables that do not have an explicit [INTEGER PRIMARY KEY].
</p>

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