Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Continuing work on the sqlar.html document. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
2e4f0d8cdae85241c21ad1d750d54e7a |
User & Date: | drh 2018-03-08 13:39:30.381 |
Context
2018-03-08
| ||
14:50 | New alternative TCLINC and TCLLIB suggestions in the Makefile. (check-in: 4cef637367 user: drh tags: trunk) | |
13:39 | Continuing work on the sqlar.html document. (check-in: 2e4f0d8cda user: drh tags: trunk) | |
12:18 | Merge updates from the 3.22.0 release. (check-in: 2fa11f57a9 user: drh tags: trunk) | |
Changes
Changes to pages/sqlar.in.
︙ | ︙ | |||
30 31 32 33 34 35 36 | 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 | | | 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | 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/|Deflate], 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> |
︙ | ︙ | |||
63 64 65 66 67 68 69 | 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 | | < < < < < < < < | < < < < < < < | < < < < < | | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | 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 <h2>Applications Using SQLite Archives</h2> <p> The [https://fossil-scm.org/|Fossil Distributed Version Control] system provides users with the option to download check-ins as either Tarballs, ZIP Archives, or SQLite Archives. <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 |
︙ | ︙ | |||
151 152 153 154 155 156 157 158 | 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> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 | 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. <li><p> An SQLite Archive supports only the [https://zlib.net/|Deflate] compression method. Tarballs and ZIP Archive support a wider assortment of compression methods. </ol> <h1>Managing An SQLite Archive From The Command-Line</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. <h2>Command-line examples</h2> <p> To extract all files from an SQLite Archive named "example.sqlar": <codeblock> sqlite3 example.sqlar -Ax </codeblock> <p> To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt files in the current directory: <codeblock> sqlite3 alltxt.sql -Ac *.txt </codeblock> <h1>Managing SQLite Archives From Application Code</h1> <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. The sqlar.c extension creates two new SQL functions. <dl> <dt><b>sqlar_compress(X)</b></dt> <dd><p> The sqlar_compress(X) function attempts to compress a copy of the string or blob X using the [https://zlib.net/|Default] algorithm and returns the result as a blob. If the input X is uncompressible, then a copy of X is returned. This routine is used when inserting content into an SQLite Archive. <dt><b>sqlar_uncompress(Y,SZ)</b></dt> <dd><p> The sqlar_uncompress(Y,SZ) function will undo the compression accomplishd by sqlar_compress(X). The Y parameter is the compressed content (the output from a prior call to sqlar_compress()) and SZ is the original uncompressed size of the input X that generated Y. If SZ is less than or equal to the size of Y, that indicates that no compression occurred, and so sqlar_uncompress(Y,SZ) returns a copy of Y. Otherwise, sqlar_uncompress(Y,SZ) runs the Inflate algorithm on Y to uncompress it and restore it to its original form and returns the uncompressed content. This routine is used when extracting content from an SQLite Archive. </dl> <p> Using the two routines above, it is simple for applications to insert new records into or extract existing records from an SQLite Archive. Insert a new into an SQLite Archive using code like this: <codeblock> INSERT INTO sqlar(name,mode,mtime,sz,data) VALUES ($name,$mode,strftime('%s',$mtime), length($content),sqlar_compress($content)); </codeblock> <p> Extract an entry from the SQLite Archive using code like this: <codeblock> SELECT name, mode, datetime(mtime,'unixepoch'), sqlar_uncompress(data,sz) FROM sqlar WHERE ...; </codeblock> <p> The code above is for the general case. For the special case of an SQLite Archive that only stores uncompressed or uncompressible content (this might come up, for example, in an SQLite Archive that stores only JPEG, GIF, and/or PNG images) then the content can be inserted into and extracted from the database without using the sqlar_compress() and sqlar_uncompress() functions, and the sqlar.c extension is not required. |