Documentation Source Text

Check-in [130193a5b9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Preliminary documentation for VACUUM INTO
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 130193a5b9a1ab7485cf6eb705685db7d2fe6682ce6700ce820f21958fcec5aa
User & Date: drh 2018-12-09 22:23:53.507
Context
2018-12-09
22:30
Omit the sponsor links from the homepage. (check-in: 3c035c0759 user: drh tags: trunk)
22:23
Preliminary documentation for VACUUM INTO (check-in: 130193a5b9 user: drh tags: trunk)
2018-12-06
15:20
Enhanced ALTER TABLE documentation, and in particular show the dangers of not following the generalized 12-step ALTER TABLE procedure precisely. (check-in: 4ffbc3f635 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to art/syntax/bubble-generator-data.tcl.
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
  }
  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}}







|







508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
  }
  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}}
Changes to art/syntax/vacuum-stmt.gif.

cannot compute difference between binary files

Changes to pages/backup.in.
58
59
60
61
62
63
64





65
66
67
68
69
70
71

<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







>
>
>
>
>







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

<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
Changes to pages/lang.in.
4956
4957
4958
4959
4960
4961
4962








4963
4964
4965
4966
4967
4968
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981
4982







































4983
4984
4985
4986
4987
4988
4989
4990
4991




4992
4993
4994
4995
4996
4997
4998

  <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
     the database. ^When in [write-ahead log] mode, only the [auto_vacuum]
     support property can be changed using VACUUM.
</ul>

<p>^By default, VACUUM only works only on the main database.
^[ATTACH|Attached databases] can be vacuumed by appending the appropriate
<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.








>
>
>
>
>
>
>
>




















>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









>
>
>
>







4956
4957
4958
4959
4960
4961
4962
4963
4964
4965
4966
4967
4968
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981
4982
4983
4984
4985
4986
4987
4988
4989
4990
4991
4992
4993
4994
4995
4996
4997
4998
4999
5000
5001
5002
5003
5004
5005
5006
5007
5008
5009
5010
5011
5012
5013
5014
5015
5016
5017
5018
5019
5020
5021
5022
5023
5024
5025
5026
5027
5028
5029
5030
5031
5032
5033
5034
5035
5036
5037
5038
5039
5040
5041
5042
5043
5044
5045
5046
5047
5048
5049

  <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
     the database. ^When in [write-ahead log] mode, only the [auto_vacuum]
     support property can be changed using VACUUM.
</ul>

<p>^By default, VACUUM only works only on the main database.
^[ATTACH|Attached databases] can be vacuumed by appending the appropriate
<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.