summaryrefslogtreecommitdiffstats
path: root/www/sqlar.html
diff options
context:
space:
mode:
Diffstat (limited to 'www/sqlar.html')
-rw-r--r--www/sqlar.html442
1 files changed, 442 insertions, 0 deletions
diff --git a/www/sqlar.html b/www/sqlar.html
new file mode 100644
index 0000000..8d3f32c
--- /dev/null
+++ b/www/sqlar.html
@@ -0,0 +1,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">&#x25ba;</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 = "&#x25bc;";
+} else {
+sub.style.display = "none";
+mk.innerHTML = "&#x25ba;";
+}
+}
+</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>&#x2007;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
+string or blob X using the <a href="https://zlib.net/">Default</a> algorithm and
+returns the result as a blob. If the input X is incompressible, 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><p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/sqlar.in?m=f269c832360dc8a51">2020-12-02 18:42:06</a> UTC </small></i></p>
+