Documentation Source Text

Check-in [130193a5b9]
Login

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

Overview
Comment:Preliminary documentation for VACUUM INTO
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:130193a5b9a1ab7485cf6eb705685db7d2fe6682ce6700ce820f21958fcec5aa
User & Date: drh 2018-12-09 22:23:53
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to art/syntax/bubble-generator-data.tcl.

   508    508     }
   509    509     qualified-table-name {
   510    510        stack
   511    511          {line {optx /schema-name .} /table-name {opt AS /alias}}
   512    512          {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
   513    513     }
   514    514     vacuum-stmt {
   515         -      line VACUUM {opt /schema-name}
          515  +      line VACUUM {opt /schema-name} {opt INTO /filename}
   516    516     }
   517    517     comment-syntax {
   518    518       or
   519    519         {line -- {loop nil /anything-except-newline} 
   520    520              {or /newline /end-of-input}}
   521    521         {line /* {loop nil /anything-except-*/}
   522    522              {or */ /end-of-input}}

Changes to art/syntax/vacuum-stmt.gif.

cannot compute difference between binary files

Changes to pages/backup.in.

    58     58   
    59     59   <p>
    60     60     The online backup API is [sqlite3_backup_init | documented here].
    61     61     The remainder of this page contains two C language examples illustrating 
    62     62     common uses of the API and discussions thereof. Reading these examples
    63     63     is no substitute for reading the API documentation!
    64     64   
           65  +<p>
           66  +  Update: The [VACUUM INTO] command introduced in 
           67  +  SQLite version 3.27.0 ([dateof:3.27.0]) can serve as an
           68  +  alternative to the backup API.
           69  +
    65     70   <h2>Example 1: Loading and Saving In-Memory Databases</h2>
    66     71   
    67     72   <tcl>
    68     73   
    69     74   proc C_Code {text} {
    70     75     hd_puts "<pre>\n"
    71     76     set iLine 0

Changes to pages/lang.in.

  4956   4956   
  4957   4957     <li> <p> ^Frequent inserts, updates, and deletes can cause the database file
  4958   4958        to become fragmented - where data for a single table or index is scattered 
  4959   4959        around the database file. ^Running VACUUM ensures that each table and
  4960   4960        index is largely stored contiguously within the database file. ^In some
  4961   4961        cases, VACUUM may also reduce the number of partially filled pages in
  4962   4962        the database, reducing the size of the database file further.
         4963  +
         4964  +  <li> <p> When content is deleted from an SQLite database, the content is not
         4965  +     usually erased but rather the space used to hold the content is marked as
         4966  +     being available for reuse.  This can allow deleted content to be recovered
         4967  +     by a hacker or by forensic analysis.  Running VACUUM will clean the database
         4968  +     of all traces of deleted content, thus preventing an adversary from recovering
         4969  +     deleted content.  Using VACUUM in this way is an alternative to setting
         4970  +     [PRAGMA security_delete=ON]. 
  4963   4971   
  4964   4972     <li> <p> ^Normally, the database [page_size] and whether or not the database
  4965   4973        supports [auto_vacuum] must be configured before the database file is
  4966   4974        actually created. ^However, when not in [write-ahead log] mode, the 
  4967   4975        [page_size] and/or [auto_vacuum] properties of an existing database may be
  4968   4976        changed by using the [page_size pragma|page_size]  and/or 
  4969   4977        [auto_vacuum|pragma auto_vacuum] pragmas and then immediately VACUUMing
................................................................................
  4976   4984   <yyterm>schema-name</yyterm> to the VACUUM statement.
  4977   4985   
  4978   4986   <p><b>Compatibility Warning:</b> The ability to vacuum attached databases was
  4979   4987   added in [version 3.15.0] ([dateof:3.15.0]).  Prior to that, a 
  4980   4988   <yyterm>schema-name</yyterm> added to the
  4981   4989   VACUUM statement would be silently ignored and the "main" schema would be
  4982   4990   vacuumed.</p>
         4991  +
         4992  +<tcl>hd_fragment vacuuminto {VACUUM INTO}</tcl>
         4993  +<h3>VACUUM with an INTO clause</h3>
         4994  +
         4995  +<p>^If the INTO clause is included, then the original database file is
         4996  +unchanged and a new database is created in the filename given by the
         4997  +argument to the INTO clause.  The new database will contain the same
         4998  +logical content as the original database, fully vacuumed.
         4999  +
         5000  +<p>
         5001  +The VACUUM command with an INTO clause is an alternative to the
         5002  +[backup API] for generating backup copies of a live database.
         5003  +The advantage of using VACUUM INTO is that the resulting backup
         5004  +database is minimal in size and hence the amount of filesystem
         5005  +I/O may be reduced.  Also, all deleted content is purged from the
         5006  +backup, leaving behind no forensic traces.  On the other hand,
         5007  +the [backup API] uses fewer CPU cycles and can be executed
         5008  +incrementally.
         5009  +
         5010  +<p>
         5011  +^The filename in the INTO clause can be an arbitrary SQL expression
         5012  +that evaluates to a string.
         5013  +^The file named by the INTO clause must not previously exist, or
         5014  +else it must be an empty file, or the VACUUM INTO command will
         5015  +fail with an error.
         5016  +
         5017  +<p>
         5018  +The VACUUM INTO command is transactional in the sense that
         5019  +the generated output database is a consistent snapshot of the
         5020  +orgininal database.  However, if the VACUUM INTO command is
         5021  +interrupted by a unplanned shutdown or power lose, in that
         5022  +case the generated output database might not be complete.
         5023  +Also, SQLite does not invoke fsync() or FlushFileBuffers()
         5024  +on the generated database to ensure that it has reached
         5025  +non-volatile storage before completing.
         5026  +
         5027  +
         5028  +<tcl>hd_fragment howvacuumworks {how vacuum works}</tcl>
         5029  +<h3>How VACUUM works</h3>
  4983   5030   
  4984   5031   <p>^The VACUUM command works by copying the contents of the database into
  4985   5032   a temporary database file and then overwriting the original with the 
  4986   5033   contents of the temporary file. ^When overwriting the original, a rollback
  4987   5034   journal or [write-ahead log] WAL file is used just as it would be for any
  4988   5035   other database transaction. ^This means that when VACUUMing a database, 
  4989   5036   as much as twice the size of the original database file is required in free
  4990   5037   disk space.
  4991   5038   
         5039  +<p>^The VACUUM INTO command works the same way except that it uses the file
         5040  +named on the INTO clause in place of the temporary database and omits the
         5041  +step of copying the vacuumed database back over top of the original database.
         5042  +
  4992   5043   <p>^The VACUUM command may change the [ROWID | ROWIDs] of entries in any
  4993   5044   tables that do not have an explicit [INTEGER PRIMARY KEY].
  4994   5045   </p>
  4995   5046   
  4996   5047   <p>^A VACUUM will fail if there is an open transaction, or if there are one or
  4997   5048   more active SQL statements when it is run.
  4998   5049