Documentation Source Text

<title>The SQLite Zipfile Module</title>
<table_of_contents>

<h1>Overview</h1>

<h1>Obtaining and Compiling Zipfile</h1>

<h1>Using Zipfile</h1>

<h2>Reading</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>
  SELECT * FROM zipfile('test.zip');
</codeblock>

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

<table striped=1>
<tr><th>Column&nbsp;Name<th>Contents
<tr><td>name <td> File name/path for the zip file record.
<tr><td>mode <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.
<tr><td>mtime <td> UTC timestamp, in seconds since the UNIX epoch (an integer).
<tr><td>sz    <td> Size of associated data in bytes after it has been 
                   uncompressed (an integer).
<tr><td>rawdata <td> Raw (possibly compressed) data associated with zip file
                     entry (a blob).
<tr><td>data <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.
<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>Writing</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. 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>Similarly, entries may be added to a zip archive by inserting new rows.
For example, to add file "m.txt" containing the text "abcdefghi" to zip
archive "test.zip":

<codeblock>
  INSERT INTO temp.zip(name, data) VALUES('m.txt', '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. 
       Usually, it is an error if the specified name already exists in the
       archive. Or, if the insert statement is actually a "REPLACE" or "INSERT
       OR REPLACE" statement, then the existing archive member is removed
       before adding the new entry.

<tr><td> mode
  <td> If NULL is inserted into the mode column, then the mode of the
       new archive entry is automatically set to 33188 (-rw-r--r--). This
       is a regular non-executable file writable by its owner and readable
       by all others. <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> The data associated with the new entry is specified by providing 
       values for the final four columns of the zipfile virtual 
       table - sz, rawdata, data and method.  Only the following combinations
       are valid:<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.
       <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> UPDATE statements are not supported by zipfile virtual tables. Nor is
    specifying an explicit value for the rowid field as part of an INSERT
    statement.