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