diff options
Diffstat (limited to 'www/zipfile.html')
| -rw-r--r-- | www/zipfile.html | 454 |
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">►</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 = "▼"; +} else { +sub.style.display = "none"; +mk.innerHTML = "►"; +} +} +</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 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> + |