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
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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>SQLite Archive Files</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
SQLite Archive Files
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
<div class="fancy-toc2"><a href="#database_as_container_object">1.1. Database As Container Object</a></div>
<div class="fancy-toc2"><a href="#applications_using_sqlite_archives">1.2. Applications Using SQLite Archives</a></div>
<div class="fancy-toc1"><a href="#advantages_of_sqlite_archives">2. Advantages Of SQLite Archives</a></div>
<div class="fancy-toc1"><a href="#disadvantages_of_sqlite_archives">3. Disadvantages Of SQLite Archives</a></div>
<div class="fancy-toc1"><a href="#managing_an_sqlite_archive_from_the_command_line">4. Managing An SQLite Archive From The Command-Line</a></div>
<div class="fancy-toc2"><a href="#other_command_line_tools">4.1. Other command-line tools</a></div>
<div class="fancy-toc1"><a href="#managing_sqlite_archives_from_application_code">5. Managing SQLite Archives From Application Code</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="introduction"><span>1. </span>Introduction</h1>
<p>
An "SQLite Archive" is a file container similar to a
<a href="https://en.wikipedia.org/wiki/Zip_(file_format)">ZIP archive</a> or
<a href="https://en.wikipedia.org/wiki/Tar_(computing)">Tarball</a> but
based on an SQLite database.
</p><p>
An SQLite Archive is an ordinary SQLite database file that contains the
following table as part of its schema:
</p><div class="codeblock"><pre>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
);
</pre></div>
<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 <a href="http://zlib.net/">Deflate</a>, though not always. If the
"sz" field is equal to the size of the "data" field, then the content
is stored uncompressed.
<a name="dbasobj"></a>
</p><h2 id="database_as_container_object"><span>1.1. </span>Database As Container Object</h2>
<p>
An SQLite Archive is one example of a more general
idea that an SQLite database can behave as a container object holding
lots of smaller data components.
</p><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
directory and say "this is the database".
</p><p>
SQLite, in contrast, stores all content in a <a href="fileformat2.html">single file on disk</a>.
That single file is something you can point to and say
"this is the database". It behaves as an object.
An SQLite database file can be copied, renamed, sent as an
email attachment, passed as the argument a POST HTTP request,
or otherwise treated as other data object such as an image,
document, or media file.
</p><p>
Studies show that many applications already use
SQLite as a container object. For example,
<a href="https://odin.cse.buffalo.edu/papers/2015/TPCTC-sqlite-final.pdf">Kennedy</a>
(no relation to the <a href="crew.html#dan">SQLite developer</a>) reports that 14% of
Android applications never write to their SQLite databases. It is
believed that these applications are downloading entire databases
from the cloud and then using the information locally as needed. In other
words, the applications are using SQLite not so much as a database but as
a queryable wire-transfer format.
</p><h2 id="applications_using_sqlite_archives"><span>1.2. </span>Applications Using SQLite Archives</h2>
<p>
The <a href="https://fossil-scm.org/">Fossil Distributed Version Control</a> system
provides users with the option to download check-ins as either Tarballs,
ZIP Archives, or SQLite Archives.
</p><h1 id="advantages_of_sqlite_archives"><span>2. </span>Advantages Of SQLite Archives</h1>
<ol>
<li><p>
An SQLite Archive is flexible.
ZIP Archives and Tarballs are limited to storing only files. An
SQLite Archive stores files plus whatever other tabular
and/or relational data seems useful to the application.
</p></li><li><p>
An SQLite Archive is transactional.
Updates are atomic and durable, even if there are 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.
</p></li><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.
</p></li><li><p>
An SQLite Archive can be queried using a high-level query language (SQL).
Some examples:
</p><ul>
<li> What is the total size of all files in the archive whose names
end in ".h" or ".cpp"?
</li><li> What percentage of the files are compressed by less than 25%?
</li><li> How many executable files are in the archive?
</li></ul>
Questions like these (and countless others) can be answered without
having to uncompress or extract any content.
</li><li><p>
Applications that already use SQLite for other purposes can easily
add support for SQLite Archives using a small extension
(<a href="https://sqlite.org/src/file/ext/misc/sqlar.c">https://sqlite.org/src/file/ext/misc/sqlar.c</a>) 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.
</p></li><li><p>
An SQLite Archive can work around firewall-imposed censorship.
For example, certain file types that are considered "dangerous"
(examples: DLLs) will be
<a href="https://support.google.com/mail/answer/6590">blocked by Gmail</a>
and probably many other email services and firewalls, even if the
files are wrapped inside a ZIP Archive or Tarball.
But these firewalls usually do not (yet) know about SQLite Archives and
so content can be put inside an SQLite Archive to evade censorship.
</p></li></ol>
<h1 id="disadvantages_of_sqlite_archives"><span>3. </span>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.
</p></li><li><p>
Since 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 Archive 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 smaller
than either ZIP or SQLite Archives.
</p><p>
As an example, the following table show the relative sizes for an
SQLite Archive, a ZIP Archive, and a Tarball of the 1,743 files
in the SQLite 3.22.0 source tree:
</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
<tr style="text-align:left"><td>SQLite Archive</td><td>10,754,048
</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>ZIP Archive (using Info-ZIP 3.0)</td><td>10,662,365
</td></tr><tr style="text-align:left"><td>ZIP Archive (using <a href="zipfile.html">zipfile</a>)</td><td>10,390,215
</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>Tarball</td><td> 9,781,109
</td></tr></table>
</li><li><p>
An SQLite Archive supports only the <a href="https://zlib.net/">Deflate</a> compression
method. Tarballs and ZIP Archive support a wider assortment of
compression methods.
</p></li></ol>
<a name="cltools"></a>
<h1 id="managing_an_sqlite_archive_from_the_command_line"><span>4. </span>Managing An SQLite Archive From The Command-Line</h1>
<p>
The recommended way of creating, updating, listing, and extracting
an SQLite Archive is to use the <a href="cli.html">sqlite3.exe command-line shell</a>
for SQLite <a href="releaselog/3_23_0.html">version 3.23.0</a> (2018-04-02) or later. This CLI
supports the -A command-line option that allows easy management
of SQLite Archives.
The CLI for SQLite <a href="releaselog/3_22_0.html">version 3.22.0</a> (2018-01-22) has the
<a href="cli.html#sqlar">.archive command</a> for managing SQLite Archives, but that requires
interacting with the shell.
</p><p>
To list all of the files in an SQLite Archive named "example.sqlar"
using one of these commands:
</p><div class="codeblock"><pre>sqlite3 example.sqlar -At
sqlite3 example.sqlar -Atv
</pre></div>
<p>
To extract all files from an SQLite Archive named "example.sqlar":
</p><div class="codeblock"><pre>sqlite3 example.sqlar -Ax
</pre></div>
<p>
To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt
files in the current directory:
</p><div class="codeblock"><pre>sqlite3 alltxt.sqlar -Ac *.txt
</pre></div>
<p>
To add or update files in an existing SQLite Archive:
</p><div class="codeblock"><pre>sqlite3 example.sqlar -Au *.md
</pre></div>
<p>
For usage hints and a summary of all options, simply give the <a href="cli.html">CLI</a>
the -A option with no additional arguments:
</p><div class="codeblock"><pre>sqlite3 -A
</pre></div>
<p>
All of these commands work the same way if the filename argument is
is a ZIP Archive instead of an SQLite database.
</p><h2 id="other_command_line_tools"><span>4.1. </span>Other command-line tools</h2>
<p>
Just as there is the "zip" program to manage ZIP Archives, and the
"tar" program to manage Tarballs, the
<a href="https://sqlite.org/sqlar">"sqlar" program</a> 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 <a href="https://github.com/libfuse/libfuse">Fuse Filesystem</a>.
</p><h1 id="managing_sqlite_archives_from_application_code"><span>5. </span>Managing SQLite Archives From Application Code</h1>
<p>
Applications can easily read or write SQLite Archives by linking against
SQLite and including the
<a href="https://sqlite.org/src/file/ext/misc/sqlar.c">ext/misc/sqlar.c</a> extension
to handle the compression and decompression. The sqlar.c extension
creates two new SQL functions.
</p><dl>
<dt><b>sqlar_compress(X)</b></dt>
<dd><p>
The sqlar_compress(X) function attempts to compress a copy of the
blob X using the <a href="https://zlib.net/">Default</a> algorithm and returns the
result as a blob. If the input X is not a compressible blob, then
a copy of X is returned. This routine is used when inserting content
into an SQLite Archive.
</p></dd><dt><b>sqlar_uncompress(Y,SZ)</b></dt>
<dd><p>
The sqlar_uncompress(Y,SZ) function will undo the compression accomplished
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.
</p></dd></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:
</p><div class="codeblock"><pre>INSERT INTO sqlar(name,mode,mtime,sz,data)
VALUES ($name,$mode,strftime('%s',$mtime),
length($content),sqlar_compress($content));
</pre></div>
<p>
Extract an entry from the SQLite Archive using code like this:
</p><div class="codeblock"><pre>SELECT name, mode, datetime(mtime,'unixepoch'), sqlar_uncompress(data,sz)
FROM sqlar
WHERE ...;
</pre></div>
<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.
</p>
|
