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: |
130193a5b9a1ab7485cf6eb705685db7 |
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
Changes to art/syntax/bubble-generator-data.tcl.
︙ | ︙ | |||
508 509 510 511 512 513 514 | } 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 { | | | 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. |
︙ | ︙ |