diff options
Diffstat (limited to 'www/sqlar.html')
| -rw-r--r-- | www/sqlar.html | 442 |
1 files changed, 442 insertions, 0 deletions
diff --git a/www/sqlar.html b/www/sqlar.html new file mode 100644 index 0000000..a097a39 --- /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">►</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> + |