Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the documentation about SQLite Archive files. Updates to the change log. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
f49396b55b475a040ed1e2a78840c37e |
User & Date: | drh 2018-03-08 00:33:08.861 |
Context
2018-03-08
| ||
12:18 | Merge updates from the 3.22.0 release. (check-in: 2fa11f57a9 user: drh tags: trunk) | |
00:33 | Add the documentation about SQLite Archive files. Updates to the change log. (check-in: f49396b55b user: drh tags: trunk) | |
2018-02-28
| ||
22:21 | Enhance the sessions documentation to show the methods of objects. (check-in: a0b50e2c3d user: drh tags: trunk) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | global nChng aChng xrefChng set aChng($nChng) [list $date $desc $options] set xrefChng($date) $nChng incr nChng } chng {2018-00-00 (3.23.0)} { <li> Recognize TRUE and FALSE as constants. (For compatibility, if there are columns named "true" or "false", then the identifiers refer to the columns rather than Boolean constants.) <li> Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE. <li> Improve the omit-left-join optimization so that it works in cases where the right-hand table is UNIQUE but not necessarily NOT NULL. <li> The "alternate-form-2" flag ("!") on the [built-in printf] implemention now causes string substitutions to measure the width and precision in characters instead of bytes. <li> If the [xColumn] method in a [virtual table] implementation returns an error message using [sqlite3_result_error()] then give that error message preference over internally-generated messages. <li> Bug fixes: <ol type='a'> <li> Fix the parser to acceept valid [row value] syntax. Ticket [https://www.sqlite.org/src/info/7310e2fb3d046a5|7310e2fb3d046a5] <li> Fix the query planner so that it takes into account dependencies in the arguments to table-valued functions in subexpressions in the WHERE clause. | > > > > | 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | global nChng aChng xrefChng set aChng($nChng) [list $date $desc $options] set xrefChng($date) $nChng incr nChng } chng {2018-00-00 (3.23.0)} { <li> Add the [sqlite3_serialize()] and [sqlite3_deserialize()] interfaced when the [SQLITE_ENABLE_DESERIALIZE] compile-time option is used. <li> Recognize TRUE and FALSE as constants. (For compatibility, if there are columns named "true" or "false", then the identifiers refer to the columns rather than Boolean constants.) <li> Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE. <li> Improve the omit-left-join optimization so that it works in cases where the right-hand table is UNIQUE but not necessarily NOT NULL. <li> The "alternate-form-2" flag ("!") on the [built-in printf] implemention now causes string substitutions to measure the width and precision in characters instead of bytes. <li> If the [xColumn] method in a [virtual table] implementation returns an error message using [sqlite3_result_error()] then give that error message preference over internally-generated messages. <li> Added the -A command-line option to the [CLI] to make it easier to manage [SQLite Archive files]. <li> Bug fixes: <ol type='a'> <li> Fix the parser to acceept valid [row value] syntax. Ticket [https://www.sqlite.org/src/info/7310e2fb3d046a5|7310e2fb3d046a5] <li> Fix the query planner so that it takes into account dependencies in the arguments to table-valued functions in subexpressions in the WHERE clause. |
︙ | ︙ | |||
124 125 126 127 128 129 130 | <li> Use the strcspn() C-library routine to speed up the LIKE and GLOB operators. </ol> <li> Improvements to the [command-line shell]: <ol type='a'> <li> The ".schema" command shows the structure of virtual tables. <li> Added support for reading and writing | | | 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 | <li> Use the strcspn() C-library routine to speed up the LIKE and GLOB operators. </ol> <li> Improvements to the [command-line shell]: <ol type='a'> <li> The ".schema" command shows the structure of virtual tables. <li> Added support for reading and writing [SQLite Archive] files using the [.archive command]. <li> Added the experimental [.expert command] <li> Added the ".eqp trigger" variant of the ".eqp" command <li> Enhance the ".lint fkey-indexes" command so that it works with [WITHOUT ROWID] tables. <li> If the filename argument to the shell is a ZIP archive rather than an SQLite database, then the shell automatically opens that ZIP |
︙ | ︙ |
Changes to pages/cli.in.
︙ | ︙ | |||
864 865 866 867 868 869 870 | does not already exists, then appends entries that check the SHA3 hash of the content of all tables. Subsequent runs of ".selftest" will verify that the database has not been changed in any way. To generates tests to verify that a subset of the tables are unchanged, simply run ".selftest --init" then [DELETE] the selftest rows that refer to tables that are not constant. | | | | 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 | does not already exists, then appends entries that check the SHA3 hash of the content of all tables. Subsequent runs of ".selftest" will verify that the database has not been changed in any way. To generates tests to verify that a subset of the tables are unchanged, simply run ".selftest --init" then [DELETE] the selftest rows that refer to tables that are not constant. <tcl>hd_fragment sqlar {.archive command}</tcl> <h1>SQLAR Archive Support</h1> <p>The ".archive" dot-command (often abbreviated as ".ar") provides built-in support for the [SQLite Archive format]. The interface is similar to that of the "tar" command on unix systems. Each invocation of the ".ar" command must specify a single command option. The following commands are available: <table striped=1> <tr><th style="width:15ex">Option<th style="width:17ex">Long Option<th>Purpose <tr><td>-c<td>--create<td>Create a new archive containing specified files. |
︙ | ︙ |
Changes to pages/compile.in.
︙ | ︙ | |||
814 815 816 817 818 819 820 821 822 823 824 825 826 827 | This option enables the [https://sqlite.org/src/file/src/dbpage.c|sqlite_dbpage virtual table]. } COMPILE_OPTION {SQLITE_ENABLE_DBSTAT_VTAB} { This option enables the [dbstat virtual table]. } COMPILE_OPTION {SQLITE_ENABLE_EXPLAIN_COMMENTS} { This option adds extra logic to SQLite that inserts comment text into the output of [EXPLAIN]. These extra comments use extra memory, thus making [prepared statements] larger and very slightly slower, and so they are turned off by default and in most application. But some applications, such as the [command-line shell] for SQLite, value clarity of EXPLAIN output | > > > > > > > > | 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 | This option enables the [https://sqlite.org/src/file/src/dbpage.c|sqlite_dbpage virtual table]. } COMPILE_OPTION {SQLITE_ENABLE_DBSTAT_VTAB} { This option enables the [dbstat virtual table]. } COMPILE_OPTION {SQLITE_ENABLE_DESERIALIZE} { This option enables the [sqlite3_serialize()] and [sqlite3_deserialize()] interfaces. <p> Future releases of SQLite might enable those interfaces by default and instead offer an SQLITE_OMIT_DESERIALIZE option to leave them out. } COMPILE_OPTION {SQLITE_ENABLE_EXPLAIN_COMMENTS} { This option adds extra logic to SQLite that inserts comment text into the output of [EXPLAIN]. These extra comments use extra memory, thus making [prepared statements] larger and very slightly slower, and so they are turned off by default and in most application. But some applications, such as the [command-line shell] for SQLite, value clarity of EXPLAIN output |
︙ | ︙ |
Added pages/sqlar.in.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | <title>SQLite Archive Files</title> <tcl>hd_keywords {SQLAR} {SQLite Archive} {SQL Archive} \ {SQLite Archive format} {SQLite Archive files} </tcl> <table_of_contents> <h1>Introduction</h1> <p> An "SQLite Archive" (also sometimes called an "SQL Archive" or an "SQLAR") is a file container similar to a [https://en.wikipedia.org/wiki/Zip_(file_format)|ZIP archive] or [https://en.wikipedia.org/wiki/Tar_(computing)|Tarball] but based on an SQLite database. <p> An SQLite Archive is an ordinary SQLite database file that contains the following table as part of its schema: <codeblock> CREATE TABLE sqlar( name TEXT PRIMARY KEY, -- name of the file mode INT, -- access permissions mtime INT, -- last modification time sz INT, -- original file size data BLOB -- compressed content ); </codeblock> <p> Each row of the SQLAR table holds the content of a single file. The filename (the full pathname relative to the root of the archive) is in the "name" field. The "mode" field is an integer which is the unix-style access permissions for the file. "mtime" is the modification time of the file in seconds since 1970. "sz" is the original uncompressed size of the file. The "data" field contains the file content. The content is usually compressed using [http://zlib.net/|zlib], though not always. If the The "sz" field is equal to the size of the "data" field, then the content is stored uncompressed. <tcl>hd_fragment dbasobj {database as object}</tcl> <h2>Database As Object</h2> <p> The concept of an SQLite Archive is one specific example of a more general idea that an SQLite database can behave as an object. <p> With client/server databases like PostgreSQL or Oracle, users and developers tend to think of the database as a service or a "node", not as an object. This is because the database content is spread out across multiple files on the server, or possibly across multiple servers in a service cluster. One cannot point to a single file or even a single diretory and say "this is the database". <p> SQLite, in contrast, stores all content in a [file format|single file on disk]. That single file is something you can point to and say "this is the database". It behaves as an object. Just like an image or document or media file, the database file can be copied, renamed, sent as an email attachment, passed as the argument a POST HTTP request, or otherwise treated as any other data object is normally treated. <p> It is as if the content for a client/server database is firmly bolted to the floor of the data-center, whereas the content for an SQLite database is free to flitter about the internet <h1>Using And Controlling An SQLite Archive</h1> <p> Just as there is the "zip" program to manage ZIP Archives, and the "tar" program to manage Tarballs, the [https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives. The "sqlar" program is able to create a new SQLite Archive, list the content of an existing archive, add or remove files from the archive, and/or extract files from the archive. A separate "sqlarfs" program is able to mount the SQLite Archive as a [https://github.com/libfuse/libfuse|Fuse Filesystem]. <p> Beginning with SQLite [version 3.22.0] ([dateof:3.22.0]), SQLite Archives can also be added using the generate [CLI|"sqlite3.exe" command-line tool] using the [.archive command]. Beginning with SQLite [version 3.23.0] ([dateof:3.23.0]), the [CLI|sqlite3.exe] is extended with new command-line options for processing SQLite Archives that make it even easier to use. <p> Applications can easily read or write SQLite Archives by linking against SQLite and including the [https://sqlite.org/src/file/ext/misc/sqlar.c|ext/misc/sqlar.c] extension to handle the compression and decompression. <h1>Advantages Of SQLite Archives</h1> <ol> <li><p> An SQLite Archive stores much more than a ZIP Archive or Tarball. ZIP Archives and Tarballs are limited to storing only files. An SQLite Archive stores files but also whatever other tabular and/or relational data seems useful to the application. <li><p> An SQLite Archive is transactional. Updates are atomic and durable, even if there are unexpected crashes or power losses in the middle of the update. Readers see a consistent and unchanging version of the content even is some other process is simultaneously updating the archive. <li><p> An SQLite Archive can be updated incrementally. Individual files can be added or removed or replaced without having to rewrite the entire archive. <li><p> An SQLite Archive can be queried using a high-level query language (SQL). Some examples: <ul> <li> What is the total size of all files in the archive whose names end in ".h" or ".cpp"? <li> What percentage of the files are compressed by less than 25%? <li> How many executable files are in the archive? </ul> Questions like these (and countless others) can be answered without having to uncompress or extract any content. <li><p> Applications that already use SQLite for other purposes can easily add support for SQLite Archives using a small extension ([https://sqlite.org/src/ext/misc/sqlar.c]) to handle the compression and decompression of content. Even this tiny extension can be omitted if the files in the archive are uncompressed. In contrast, supporting ZIP Archives and/or Tarballs requires either separate libraries or lots of extra custom code, or sometimes both. </ol> <h1>Disadvantages Of SQLite Archives</h1> <ol> <li><p> The SQLite Archive is a relatively new format. It was first described in in 2014. ZIP Archives and Tarballs, on the other hand, have been around for decades and are well-entrenched as standard formats. Most programmers know what a ZIP Archive or Tarball is, but if you say "SQLite Archive" you are more likely to get a reply of "What?" Tooling to process ZIP Archives and Tarballs is more likely to be installed on stock computers. <li><p> Single an SQLite database is a more general format (it is designed to do much more than simply store a bunch of files) it is not as compact as either the ZIP Archive or Tarball formats. An SQLite Achive is usually about 1% larger than the equivalent ZIP Archive. Tarballs are compressed as a single unit rather than compressing each file separately as is done by both SQLite and ZIP Archives. For these reason, Tarballs tend to be much smaller than either ZIP or SQLite Archives. </ol> |