Documentation Source Text

Check-in [2e4f0d8cda]
Login

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: 2e4f0d8cdae85241c21ad1d750d54e7a5a4a317a3f270d43a2a551cd3ed7e093
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
Unified Diff Ignore Whitespace Patch
Changes to pages/sqlar.in.
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/|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>







|







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
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
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







|


<
<
<
<
<
<
<
<
|
<
<
<
<
<
<
<
|
<
<
<
<
<
|







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.