summaryrefslogtreecommitdiffstats
path: root/www/zipfile.html
diff options
context:
space:
mode:
Diffstat (limited to 'www/zipfile.html')
-rw-r--r--www/zipfile.html454
1 files changed, 454 insertions, 0 deletions
diff --git a/www/zipfile.html b/www/zipfile.html
new file mode 100644
index 0000000..069f5ce
--- /dev/null
+++ b/www/zipfile.html
@@ -0,0 +1,454 @@
+<!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>The SQLite Zipfile Module</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">
+The SQLite Zipfile Module
+</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="#overview">1. Overview</a></div>
+<div class="fancy-toc1"><a href="#obtaining_and_compiling_zipfile">2. Obtaining and Compiling Zipfile</a></div>
+<div class="fancy-toc1"><a href="#using_zipfile">3. Using Zipfile</a></div>
+<div class="fancy-toc2"><a href="#table_valued_function_read_only_access_">3.1. Table-Valued Function (read-only access)</a></div>
+<div class="fancy-toc2"><a href="#virtual_table_interface_read_write_access_">3.2. Virtual Table Interface (read/write access)</a></div>
+<div class="fancy-toc3"><a href="#adding_entries_to_a_zip_archive">3.2.1. Adding Entries to a Zip Archive</a></div>
+<div class="fancy-toc3"><a href="#_deleting_zip_archive_entries_">3.2.2. Deleting Zip Archive Entries </a></div>
+<div class="fancy-toc3"><a href="#_updating_existing_zip_archive_entries_">3.2.3. Updating Existing Zip Archive Entries </a></div>
+<div class="fancy-toc2"><a href="#_the_zipfile_aggregate_function_">3.3. The zipfile() Aggregate Function </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="overview"><span>1. </span>Overview</h1>
+
+<p> The zipfile module provides read/write access to simple
+<a href="https://en.wikipedia.org/wiki/Zip_%28file_format%29">ZIP archives</a>.
+The current implementation has the following restrictions:
+
+</p><ul>
+ <li> Does not support encryption.
+ </li><li> Does not support ZIP archives that span multiple files.
+ </li><li> Does not support zip64 extensions.
+ </li><li> The only compression algorithm supported is
+ <a href="https://zlib.net">"deflate"</a>.
+</li></ul>
+
+<p> Some or all of these restrictions may be removed in the future.
+
+</p><h1 id="obtaining_and_compiling_zipfile"><span>2. </span>Obtaining and Compiling Zipfile</h1>
+
+<p>The code for the zipfile module is found in the
+<a href="https://sqlite.org/src/file/ext/misc/zipfile.c">ext/misc/zipfile.c</a>
+file of the
+<a href="https://sqlite.org/src">main SQLite source tree</a>.
+It may be compiled into an SQLite
+<a href="loadext.html">loadable extension</a> using a command like:
+
+</p><div class="codeblock"><pre>gcc -g -fPIC -shared zipfile.c -o zipfile.so
+</pre></div>
+
+<p>Alternatively, the zipfile.c file may be compiled into the application.
+In this case, the following function should be invoked to register the
+extension with each new database connection:
+
+</p><div class="codeblock"><pre>int sqlite3_zipfile_init(sqlite3 *db, void*, void*);
+</pre></div>
+
+<p> The first argument passed should be the database handle to register the
+extension with. The second and third arguments should both be passed 0.
+
+</p><p> Zipfile is included in most builds of the <a href="cli.html">command-line shell</a>.
+
+</p><h1 id="using_zipfile"><span>3. </span>Using Zipfile</h1>
+
+<p>The zipfile module provides three similar interfaces for accessing, updating
+and creating zip file archives:
+
+</p><ol>
+ <li> A table-valued function, which provides read-only access to existing
+ archives, either from the file-system or in-memory.
+ </li><li> A virtual table, which provides read and write access to archives
+ stored in the file-system.
+ </li><li> An SQL aggregate function, which can be used to create new archives
+ in memory.
+</li></ol>
+
+<p>The zipfile module provides two similar interfaces for accessing zip
+archives. A table-valued function, which provides read-only access to
+existing archives, and a virtual table interface, which provides both
+read and write access.
+
+</p><h2 id="table_valued_function_read_only_access_"><span>3.1. </span>Table-Valued Function (read-only access)</h2>
+
+<p>For reading existing zip archives, the Zipfile module provides a
+<a href="vtab.html#tabfunc2">table-valued function</a> that accepts a single argument. If the argument
+is a text value, then it is a path to a zip archive to read from the
+file-system. Or, if the argument is an SQL blob, then it is the zip
+archive data itself.
+
+</p><p>For example, to inspect the contents of zip archive "test.zip" from
+the current directory:
+
+</p><div class="codeblock"><pre>SELECT * FROM zipfile('test.zip');
+</pre></div>
+
+<p>Or, from the SQLite shell tool (the <a href="cli.html#fileio">readfile()</a>
+function reads the contents of a file from the file-system and returns it as a
+blob):
+
+</p><div class="codeblock"><pre>SELECT * FROM zipfile( readfile('test.zip') );
+</pre></div>
+
+<p>The table-valued function returns one row for each record (file,
+directory or symbolic link) in the zip archive. Each row has the
+following columns:
+
+</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
+<tr style="text-align:left"><th>Column&nbsp;Name</th><th>Contents
+</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>name </td><td> File name/path for the zip file record.
+</td></tr><tr style="text-align:left"><td>mode </td><td> UNIX mode, as returned by stat(2) for the zip file record (an
+ integer). This identifies the type of record (file, directory
+ or symbolic link), and the associated user/group/all
+ permissions.
+</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>mtime </td><td> UTC timestamp, in seconds since the UNIX epoch (an integer).
+</td></tr><tr style="text-align:left"><td>sz </td><td> Size of associated data in bytes after it has been
+ uncompressed (an integer).
+</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>rawdata </td><td> Raw (possibly compressed) data associated with zip file
+ entry (a blob).
+</td></tr><tr style="text-align:left"><td>data </td><td> If the compression method for the record is either 0 or 8
+ (see below), then the uncompressed data associated with the
+ zip file entry. Or, if the compression method is not 0 or 8,
+ this column contains a NULL value.
+</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>method </td><td> The compression method used to compress the data (an
+ integer). The value 0 indicates that the data is stored
+ in the zip archive without compression. 8 means the
+ raw deflate algorithm.
+</td></tr></table>
+
+<h2 id="virtual_table_interface_read_write_access_"><span>3.2. </span>Virtual Table Interface (read/write access)</h2>
+
+<p>In order to create or modify an existing zip file, a "zipfile" virtual
+table must be created in the database schema. The CREATE VIRTUAL TABLE
+statement expects a path to the zip file as its only argument. For example, to
+write to zip file "test.zip" in the current directory, a zipfile table may be
+created using:
+
+</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.zip USING zipfile('test.zip');
+</pre></div>
+
+<p>Such a virtual table has the same columns as the table-valued function
+described in the previous section. It may be read from using a SELECT
+statement in the same way as the table-valued function can.
+
+</p><p>Using the virtual table interface, new entries may be added to a zip
+archive by inserting new rows into the virtual table. Entries may be
+removed by deleting rows or modified by updating them.
+
+</p><a name="adding_entries_to_a_zip_archive"></a>
+<h3 tags="Adding to Zip" id="adding_entries_to_a_zip_archive"><span>3.2.1. </span>Adding Entries to a Zip Archive</h3>
+
+<p>Entries may be added to a zip archive by inserting new rows. The easiest
+way to do this is to specify values for the "name" and "data" columns only and
+have zipfile fill in sensible defaults for other fields. To insert a directory
+into the archive, set the "data" column to NULL. For example, to add the
+directory "dir1" and the file "m.txt" containing the text "abcdefghi" to zip
+archive "test.zip":
+
+</p><div class="codeblock"><pre>INSERT INTO temp.zip(name, data) VALUES('dir1', NULL); <i>-- Add directory </i>
+INSERT INTO temp.zip(name, data) VALUES('m.txt', 'abcdefghi'); <i>-- Add regular file </i>
+</pre></div>
+
+<p>When a directory is inserted, if the "name" value does not end with
+a '/' character, the zipfile module appends one. This is necessary for
+compatibility with other programs (most notably "info-zip") that
+manipulate zip archives.
+
+</p><p>To insert a symbolic link, the user must also supply a "mode" value.
+For example, to add a symbolic link from "link.txt" to "m.txt":
+
+</p><div class="codeblock"><pre>INSERT INTO temp.zip(name, mode, data) VALUES('link.txt', 'lrwxrw-rw-', 'abcdefghi');
+</pre></div>
+
+<p>The following rules and caveats apply to the values specified as part of
+each INSERT statement:
+
+</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
+<tr style="text-align:left"><th>Columns </th><th> Notes
+</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td> name
+ </td><td> A non-NULL text value must be specified for the name column.
+ It is an error if the specified name already exists in the
+ archive.
+
+</td></tr><tr style="text-align:left"><td> mode
+ </td><td> If NULL is inserted into the mode column, then the mode of the
+ new archive entry is automatically set to either 33188 (-rw-r--r--)
+ or 16877 (drwxr-xr-x), depending on whether or not the values
+ specified for columns "sz", "data" and "rawdata" indicate that
+ the new entry is a directory.<br><br>
+
+ If the specified value is an integer (or text that looks like
+ an integer), it is inserted verbatim. If the value is not a valid UNIX
+ mode, some programs may behave unexpectedly when extracting files
+ from the archive.<br><br>
+
+ Finally, if the value specified for this column is not an integer
+ or a NULL, then it is assumed to be a UNIX permissions string similar
+ to those output by the "ls -l" command (e.g. "-rw-r--r--", "drwxr-xr-x"
+ etc.). In this case, if the string cannot be parsed it is an error.
+
+</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td> mtime
+</td><td> If NULL is inserted into the mtime column, then the timestamp
+ of the new entry is set to the current time. Otherwise, the specified
+ value is interpreted as an integer and used as is.
+
+</td></tr><tr style="text-align:left"><td> sz
+</td><td> This column must be set to NULL. If a non-NULL value is inserted into
+ this column, or if a new non-NULL value is provided using an UPDATE
+ statement, it is an error.
+
+</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td> rawdata
+</td><td> This column must be set to NULL. If a non-NULL value is inserted into
+ this column, or if a new non-NULL value is provided using an UPDATE
+ statement, it is an error.
+
+</td></tr><tr style="text-align:left"><td> data
+</td><td>
+ To insert a directory into the archive, this field must be set to
+ NULL. In this case if a value was explicitly specified for the "mode"
+ column, then it must be consistent with a directory (i.e. it must be
+ true that (mode & 0040000)=0040000). <br><br>
+
+ Otherwise, the value inserted into this field is the file contents
+ for a regular file, or the target of a symbolic link.
+</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td> method
+</td><td>
+ This field must be set one of integer values 0 and 8, or else to
+ NULL. <br><br>
+
+ For a directory entry, any value inserted into this field is ignored.
+ Otherwise, if it is set to 0, then the file data or symbolic link
+ target is stored as is in the zip archive and the compression method
+ set to 0. If it is set to 8, then the file data or link target is
+ compressed using deflate compression before it is stored and the
+ compression method set to 8. Finally, if a NULL value is written
+ to this field, the zipfile module automatically decides whether
+ or not to compress the data before storing it.
+</td></tr></table>
+
+<p> Specifying an explicit value for the rowid field as part of an INSERT
+statement is not supported. Any value supplied is ignored.
+
+</p><h3 id="_deleting_zip_archive_entries_"><span>3.2.2. </span> Deleting Zip Archive Entries </h3>
+
+<p>Records may be removed from an existing zip archive by deleting the
+corresponding rows. For example, to remove file "m.txt" from zip archive
+"test.zip" using the virtual table created above:
+
+</p><div class="codeblock"><pre>DELETE FROM temp.zip WHERE name = 'm.txt';
+</pre></div>
+
+<p>Note that deleting records from a zip archive does not reclaim the
+space used within the archive - it merely removes an entry from the
+archives "Central Directory Structure", making the entry inaccessible.
+One way to work around this inefficiency is to create a new zip
+archive based on the contents of the edited archive. For example, after
+editing the archive accessed via virtual table temp.zzz:
+
+</p><div class="codeblock"><pre><i>-- Create a new, empty, archive: </i>
+CREATE VIRTUAL TABLE temp.newzip USING zipfile('new.zip');
+
+<i>-- Copy the contents of the existing archive into the new archive</i>
+INSERT INTO temp.newzip(name, mode, mtime, data, method)
+ SELECT name, mode, mtime, data, method FROM temp.zzz;
+</pre></div>
+
+<h3 id="_updating_existing_zip_archive_entries_"><span>3.2.3. </span> Updating Existing Zip Archive Entries </h3>
+
+<p>Existing zip archive entries may be modified using UPDATE statements.
+
+</p><p>The three leftmost columns of a zipfile virtual table, "name", "mode"
+and "mtime", may each be set to any value that may be inserted into the same
+column (see above). If either "mode" or "mtime" is set to NULL, the final
+value is determined as described for an INSERT of a NULL value - the current
+time for "mtime" and either 33188 or 16877 for "mode", depending on whether
+or not the values specified for the next four columns of the zipfile table
+indicate that the entry is a directory or a file.
+
+</p><p>It is an error to attempt to set the sz or rawdata field to any value
+other than NULL.
+
+</p><p>The data and method columns may also be set as described for an INSERT
+above.
+
+</p><h2 id="_the_zipfile_aggregate_function_"><span>3.3. </span> The zipfile() Aggregate Function </h2>
+
+<p> New zip archives may be constructed entirely within memory using the
+zipfile() aggregate function. Each row visited by the aggregate function
+adds an entry to the zip archive. The value returned is a blob containing
+the entire archive image.
+
+</p><p> The zipfile() aggregate function may be called with 2, 4 or 5
+arguments. If it is called with 5 arguments, then the entry added to
+the archive is equivalent to inserting the same values into the "name",
+"mode", "mtime", "data" and "method" columns of a zipfile virtual table.
+
+</p><p> If zipfile() is invoked with 2 arguments, then the entry added to
+the archive is equivalent to that added by inserting the same two values into
+the "name" and "data" columns of a zipfile virtual table, with all
+other values set to NULL. If invoked with 4 arguments, it is equivalent
+to inserting the 4 values into the "name", "mode", "mtime" and "data"
+columns. In other words, the following pairs of queries are equivalent:
+
+</p><div class="codeblock"><pre>SELECT zipfile(name, data) ...
+SELECT zipfile(name, NULL, NULL, data, NULL) ...
+
+SELECT zipfile(name, mode, mtime, data) ...
+SELECT zipfile(name, mode, mtime, data, NULL) ...
+</pre></div>
+
+<p> For example, to create an archive containing two text files, "a.txt" and
+"b.txt", containing the text "abc" and "123" respectively:
+
+</p><div class="codeblock"><pre>WITH contents(name, data) AS (
+ VALUES('a.txt', 'abc') UNION ALL
+ VALUES('b.txt', '123')
+)
+SELECT zipfile(name, data) FROM contents;
+</pre></div>
+<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/zipfile.in?m=515b8b11ec4c73b8f">2018-03-10 12:09:19</a> UTC </small></i></p>
+