Debian Codegraph @

www/sqlar.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>SQLite Archive Files</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 SQLite Archive Files
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
126 <div class="fancy-toc2"><a href="#database_as_container_object">1.1. Database As Container Object</a></div>
127 <div class="fancy-toc2"><a href="#applications_using_sqlite_archives">1.2. Applications Using SQLite Archives</a></div>
128 <div class="fancy-toc1"><a href="#advantages_of_sqlite_archives">2. Advantages Of SQLite Archives</a></div>
129 <div class="fancy-toc1"><a href="#disadvantages_of_sqlite_archives">3. Disadvantages Of SQLite Archives</a></div>
130 <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>
131 <div class="fancy-toc2"><a href="#other_command_line_tools">4.1. Other command-line tools</a></div>
132 <div class="fancy-toc1"><a href="#managing_sqlite_archives_from_application_code">5. Managing SQLite Archives From Application Code</a></div>
133 </div>
134 </details>
135 </div>
136
137
138
139
140 <h1 id="introduction"><span>1. </span>Introduction</h1>
141
142 <p>
143 An "SQLite Archive" is a file container similar to a
144 <a href="https://en.wikipedia.org/wiki/Zip_(file_format)">ZIP archive</a> or
145 <a href="https://en.wikipedia.org/wiki/Tar_(computing)">Tarball</a> but
146 based on an SQLite database.
147
148 </p><p>
149 An SQLite Archive is an ordinary SQLite database file that contains the
150 following table as part of its schema:
151
152 </p><div class="codeblock"><pre>CREATE TABLE sqlar(
153 name TEXT PRIMARY KEY, -- name of the file
154 mode INT, -- access permissions
155 mtime INT, -- last modification time
156 sz INT, -- original file size
157 data BLOB -- compressed content
158 );
159 </pre></div>
160
161 <p>
162 Each row of the SQLAR table holds the content of a single file.
163 The filename (the full pathname relative to the root of the archive)
164 is in the "name" field.
165 The "mode" field is an integer which is the unix-style access permissions
166 for the file. "mtime" is the modification time of the file in seconds
167 since 1970. "sz" is the original uncompressed size of the file.
168 The "data" field contains the file content. The content is usually
169 compressed using <a href="http://zlib.net/">Deflate</a>, though not always. If the
170 "sz" field is equal to the size of the "data" field, then the content
171 is stored uncompressed.
172
173 <a name="dbasobj"></a>
174
175 </p><h2 id="database_as_container_object"><span>1.1. </span>Database As Container Object</h2>
176
177 <p>
178 An SQLite Archive is one example of a more general
179 idea that an SQLite database can behave as a container object holding
180 lots of smaller data components.
181
182 </p><p>
183 With client/server databases like PostgreSQL or Oracle, users and
184 developers tend to think of the database as a service or a "node", not
185 as an object. This is because the database content is spread out across
186 multiple files on the server, or possibly across multiple servers in a
187 service cluster. One cannot point to a single file or even a single
188 directory and say "this is the database".
189
190 </p><p>
191 SQLite, in contrast, stores all content in a <a href="fileformat2.html">single file on disk</a>.
192 That single file is something you can point to and say
193 "this is the database". It behaves as an object.
194 An SQLite database file can be copied, renamed, sent as an
195 email attachment, passed as the argument for a POST HTTP request,
196 or otherwise treated as other data objects such as an image,
197 document, or media file.
198
199 </p><p>
200 Studies show that many applications already use
201 SQLite as a container object. For example,
202 <a href="https://odin.cse.buffalo.edu/papers/2015/TPCTC-sqlite-final.pdf">Kennedy</a>
203 (no relation to the SQLite developer with the same last name)
204 reports that 14% of
205 Android applications never write to their SQLite databases. It is
206 believed that these applications are downloading entire databases
207 from the cloud and then using the information locally as needed. In other
208 words, the applications are using SQLite not so much as a database but as
209 a queryable wire-transfer format.
210
211 </p><h2 id="applications_using_sqlite_archives"><span>1.2. </span>Applications Using SQLite Archives</h2>
212
213 <p>
214 The <a href="https://fossil-scm.org/">Fossil Distributed Version Control</a> system
215 provides users with the option to download check-ins as either Tarballs,
216 ZIP Archives, or SQLite Archives.
217 </p><h1 id="advantages_of_sqlite_archives"><span>2. </span>Advantages Of SQLite Archives</h1>
218
219 <ol>
220 <li><p>
221 An SQLite Archive is flexible.
222 ZIP Archives and Tarballs are limited to storing only files. An
223 SQLite Archive stores files plus whatever other tabular
224 and/or relational data seems useful to the application.
225
226 </p></li><li><p>
227 An SQLite Archive is transactional.
228 Updates are atomic and durable, even if there are crashes
229 or power losses in the middle of the update.
230 Readers see a consistent and unchanging version of the content even
231 if some other process is simultaneously updating the archive.
232
233 </p></li><li><p>
234 An SQLite Archive can be updated incrementally.
235 Individual files can be added or removed or replaced without having
236 to rewrite the entire archive.
237
238 </p></li><li><p>
239 An SQLite Archive can be queried using a high-level query language (SQL).
240 Some examples:
241 </p><ul>
242 <li> What is the total size of all files in the archive whose names
243 end in ".h" or ".cpp"?
244 </li><li> What percentage of the files are compressed by less than 25%?
245 </li><li> How many executable files are in the archive?
246 </li></ul>
247 Questions like these (and countless others) can be answered without
248 having to uncompress or extract any content.
249
250 </li><li><p>
251 Applications that already use SQLite for other purposes can easily
252 add support for SQLite Archives using a small extension
253 (<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
254 and decompression of content. Even this tiny extension can be omitted
255 if the files in the archive are uncompressed. In contrast, supporting
256 ZIP Archives and/or Tarballs requires either separate libraries or
257 lots of extra custom code, or sometimes both.
258
259 </p></li><li><p>
260 An SQLite Archive can work around firewall-imposed censorship.
261 For example, certain file types that are considered "dangerous"
262 (examples: DLLs) will be
263 <a href="https://support.google.com/mail/answer/6590">blocked by Gmail</a>
264 and probably many other email services and firewalls, even if the
265 files are wrapped inside a ZIP Archive or Tarball.
266 But these firewalls usually do not (yet) know about SQLite Archives and
267 so content can be put inside an SQLite Archive to evade censorship.
268 </p></li></ol>
269
270 <h1 id="disadvantages_of_sqlite_archives"><span>3. </span>Disadvantages Of SQLite Archives</h1>
271
272 <ol>
273 <li><p>
274 The SQLite Archive is a relatively new format. It was first described in
275 2014. ZIP Archives and Tarballs, on the other hand, have been around
276 for decades and are well-entrenched as standard formats. Most programmers
277 know what a ZIP Archive or Tarball is, but if you say "SQLite Archive" you
278 are more likely to get a reply of "What?" Tooling to process ZIP Archives
279 and Tarballs is more likely to be installed on stock computers.
280
281 </p></li><li><p>
282 Since an SQLite database is a more general format (it is designed to do
283 much more than simply store a bunch of files) it is not as compact as either
284 the ZIP Archive or Tarball formats. An SQLite Archive is usually about 1%
285 larger than the equivalent ZIP Archive. Tarballs are compressed as a single
286 unit rather than compressing each file separately as is done by both
287 SQLite and ZIP Archives. For these reason, Tarballs tend to be smaller
288 than either ZIP or SQLite Archives.
289 </p><p>
290 As an example, the following table show the relative sizes for an
291 SQLite Archive, a ZIP Archive, and a Tarball of the 1,743 files
292 in the SQLite 3.22.0 source tree:
293 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
294 <tr style="text-align:left"><td>SQLite Archive</td><td>10,754,048
295 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>ZIP Archive (using Info-ZIP 3.0)</td><td>10,662,365
296 </td></tr><tr style="text-align:left"><td>ZIP Archive (using <a href="zipfile.html">zipfile</a>)</td><td>10,390,215
297 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>Tarball</td><td>&#x2007;9,781,109
298 </td></tr></table>
299
300 </li><li><p>
301 An SQLite Archive supports only the <a href="https://zlib.net/">Deflate</a> compression
302 method. Tarballs and ZIP Archive support a wider assortment of
303 compression methods.
304 </p></li></ol>
305
306 <a name="cltools"></a>
307
308 <h1 id="managing_an_sqlite_archive_from_the_command_line"><span>4. </span>Managing An SQLite Archive From The Command-Line</h1>
309
310 <p>
311 The recommended way of creating, updating, listing, and extracting
312 an SQLite Archive is to use the <a href="cli.html">sqlite3.exe command-line shell</a>
313 for SQLite <a href="releaselog/3_23_0.html">version 3.23.0</a> (2018-04-02) or later. This CLI
314 supports the -A command-line option that allows easy management
315 of SQLite Archives.
316 The CLI for SQLite <a href="releaselog/3_22_0.html">version 3.22.0</a> (2018-01-22) has the
317 <a href="cli.html#sqlar">.archive command</a> for managing SQLite Archives, but that requires
318 interacting with the shell.
319
320 </p><p>
321 To list all of the files in an SQLite Archive named "example.sqlar"
322 using one of these commands:
323
324 </p><div class="codeblock"><pre>sqlite3 example.sqlar -At
325 sqlite3 example.sqlar -Atv
326 </pre></div>
327
328 <p>
329 To extract all files from an SQLite Archive named "example.sqlar":
330
331 </p><div class="codeblock"><pre>sqlite3 example.sqlar -Ax
332 </pre></div>
333
334 <p>
335 To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt
336 files in the current directory:
337
338 </p><div class="codeblock"><pre>sqlite3 alltxt.sqlar -Ac *.txt
339 </pre></div>
340
341 <p>
342 To add or update files in an existing SQLite Archive:
343
344 </p><div class="codeblock"><pre>sqlite3 example.sqlar -Au *.md
345 </pre></div>
346
347 <p>
348 For usage hints and a summary of all options, simply give the <a href="cli.html">CLI</a>
349 the -A option with no additional arguments:
350
351 </p><div class="codeblock"><pre>sqlite3 -A
352 </pre></div>
353
354 <p>
355 All of these commands work the same way if the filename argument
356 is a ZIP Archive instead of an SQLite database.
357
358 </p><h2 id="other_command_line_tools"><span>4.1. </span>Other command-line tools</h2>
359
360 <p>
361 Just as there is the "zip" program to manage ZIP Archives, and the
362 "tar" program to manage Tarballs, the
363 <a href="https://sqlite.org/sqlar">"sqlar" program</a> exists to manage SQL Archives.
364 The "sqlar" program is able to create a new SQLite Archive, list the
365 content of an existing archive, add or remove files from the archive,
366 and/or extract files from the archive.
367 A separate "sqlarfs" program is able to mount the SQLite Archive as
368 a <a href="https://github.com/libfuse/libfuse">Fuse Filesystem</a>.
369
370 </p><h1 id="managing_sqlite_archives_from_application_code"><span>5. </span>Managing SQLite Archives From Application Code</h1>
371
372 <p>
373 Applications can easily read or write SQLite Archives by linking against
374 SQLite and including the
375 <a href="https://sqlite.org/src/file/ext/misc/sqlar.c">ext/misc/sqlar.c</a> extension
376 to handle the compression and decompression. The sqlar.c extension
377 creates two new SQL functions.
378
379 </p><dl>
380 <dt><b>sqlar_compress(X)</b></dt>
381 <dd><p>
382 The sqlar_compress(X) function attempts to compress a copy of the
383 blob X using the <a href="https://zlib.net/">Default</a> algorithm and returns the
384 result as a blob. If the input X is not a compressible blob, then
385 a copy of X is returned. This routine is used when inserting content
386 into an SQLite Archive.
387 </p></dd><dt><b>sqlar_uncompress(Y,SZ)</b></dt>
388 <dd><p>
389 The sqlar_uncompress(Y,SZ) function will undo the compression accomplished
390 by sqlar_compress(X). The Y parameter is the compressed content (the output
391 from a prior call to sqlar_compress()) and SZ is the original uncompressed
392 size of the input X that generated Y. If SZ is less than or equal to the
393 size of Y, that indicates that no compression occurred, and so
394 sqlar_uncompress(Y,SZ) returns a copy of Y. Otherwise, sqlar_uncompress(Y,SZ)
395 runs the Inflate algorithm on Y to uncompress it and restore it to its
396 original form and returns the uncompressed content.
397 This routine is used when extracting content from an SQLite Archive.
398 </p></dd></dl>
399
400 <p>
401 Using the two routines above, it is simple for applications to insert
402 new records into or extract existing records from an SQLite Archive.
403 Insert a new record into an SQLite Archive using code like this:
404
405 </p><div class="codeblock"><pre>INSERT INTO sqlar(name,mode,mtime,sz,data)
406 VALUES ($name,$mode,strftime('%s',$mtime),
407 length($content),sqlar_compress($content));
408 </pre></div>
409
410 <p>
411 Extract an entry from the SQLite Archive using code like this:
412
413 </p><div class="codeblock"><pre>SELECT name, mode, datetime(mtime,'unixepoch'), sqlar_uncompress(data,sz)
414 FROM sqlar
415 WHERE ...;
416 </pre></div>
417
418 <p>
419 The code above is for the general case. For the special case of an
420 SQLite Archive that only stores uncompressed or uncompressible content
421 (this might come up, for example, in an SQLite Archive that stores only
422 JPEG, GIF, and/or PNG images) then the content can be inserted into
423 and extracted from the database without using the sqlar_compress()
424 and sqlar_uncompress() functions, and the sqlar.c extension is not
425 required.
426 </p><p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
427