Documentation Source Text

<title>The SQLite Zipfile Module</title>
<tcl>hd_keywords {Zipfile virtual table} {zipfile}</tcl>
<table_of_contents>

<h1>Overview</h1>

<p> The zipfile module provides read/write access to simple zip archives.
At time of writing, simple zip archives are roughly those that:

<ul>
  <li> Do not use encryption, 
  <li> Do not span multiple files, and
  <li> Do not use zip64 extensions.
</ul>

<p> Some or all of these restrictions may be removed at some point
in the future.

<h1>Obtaining and Compiling Zipfile</h1>

<p>The code for the zipfile module is found in the ext/misc/zipfile.c file of
the main SQLite source tree. It may be compiled into an SQLite 
[loadable extension] using a command like:

<codeblock>
    gcc -g -fPIC -shared zipfile.c -o zipfile.so
</codeblock>

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

<codeblock>
  int sqlite3_zipfile_init(sqlite3 *db, void*, void*);
</codeblock>

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

<h1>Using Zipfile</h1>

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

<h2>Table-Valued Function (read-only access)</h2>

<p>For reading existing zip archives, the Zipfile module provides a
[table-valued function] that accepts a single argument - the path to the zip
archive to be read. For example, to inspect the contents of zip archive
"test.zip" from the current directory:

<codeblock>

                  this column contains a NULL value.
<tr><td>method <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. 
</table>

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

<codeblock>
  CREATE VIRTUAL TABLE temp.zip USING zipfile('test.zip');
</codeblock>

<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>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. UPDATE statements are not supported by zipfile
virtual tables. 




<h3 tags="Adding to Zip">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":

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

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

<codeblock>
  INSERT INTO temp.zip(name, mode, data) VALUES('link.txt', 'lrwx-rw-rw', 'abcdefghi');
</codeblock>

<p>The following rules and caveats apply to the values specified as part of
each INSERT statement:

<table striped=1>
<tr><th>Columns <th> Notes
<tr><td> name
  <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. 



<tr><td> mode
  <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.

<tr><td> mtime
<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.

<tr><td> sz, rawdata, data, method
<td>
       To insert a directory into the archive, all four of these fields
       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, these fields are used to specify the contents of a
       regular file or the target of a symbolic link. There are three
       different ways to do this, depending on whether compressed or
       uncompressed data is being inserted, and whether or not zipfile should
       automatically choose a compression algorithm and compress the data:<br><br>

       <b>sz=NULL, rawdata=NULL, data&ne;NULL, method=NULL</b>:
       In this case the value specified for the "data" column is stored as the
       associated data in the archive. Zipfile automatically compresses it
       using the deflate algorithm (method=8) if doing so would be advantageous.
       Otherwise, if the data is not compressible, it is stored verbatim in
       the archive (method=0).
       <br><br>

       <b>sz=NULL, rawdata=NULL, data&ne;NULL, method IN (0,8)</b>: 
       In this case the value supplied for the "method" column must
       be either 0 or 8. In either case the value specified for the "data"
       column is stored as the associated data, compressed if 8 was specified
       for the "method" column, or as is otherwise.
       <br><br>

       <b>sz&ne;NULL, rawdata&ne;NULL, data=NULL, method&ne;NULL</b>: 
       In this case the value supplied for column "rawdata" must be
       already compressed according to the integer method supplied for "method"
       (which can take any unsigned 16-bit value, not just 0 or 8). The size of
       the uncompressed data (an integer) must be specified for column "sz".
</table>


<p> Specifying an explicit value for the rowid field as part of an INSERT
statement is not supported. Any value supplied is ignored.

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

<codeblock>
  DELETE FROM temp.zip WHERE name = 'm.txt';
</codeblock>

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

<codeblock>
  <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, sz, rawdata, method) 
      SELECT name, mode, mtime, sz, rawdata, method FROM temp.zzz;
</codeblock>