Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.
|Comment:||Add new documentation file zipfile.in.|
|Downloads:||Tarball | ZIP archive | SQL archive|
|Timelines:||family | ancestors | descendants | both | trunk|
|Files:||files | file ages | folders|
|User & Date:||dan 2018-01-08 12:05:58|
|01:25||Add the "docapp" makefile target for building the "docapp" application. check-in: d31880687e user: drh tags: trunk|
|12:05||Add new documentation file zipfile.in. check-in: 99c2fcc179 user: dan tags: trunk|
|19:28||Documentation on the sqlite_offset() SQL function. check-in: 4ce0a400c2 user: drh tags: trunk|
1 +<title>The SQLite Zipfile Module</title> 2 +<table_of_contents> 3 + 4 +<h1>Overview</h1> 5 + 6 +<h1>Obtaining and Compiling Zipfile</h1> 7 + 8 +<h1>Using Zipfile</h1> 9 + 10 +<h2>Reading</h2> 11 + 12 +<p>For reading existing zip archives, the Zipfile module provides a 13 +[table-valued function] that accepts a single argument - the path to the zip 14 +archive to be read. For example, to inspect the contents of zip archive 15 +"test.zip" from the current directory: 16 + 17 +<codeblock> 18 + SELECT * FROM zipfile('test.zip'); 19 +</codeblock> 20 + 21 +<p>The table-valued function returns one row for each record (file, 22 +directory or symbolic link) in the zip archive. Each row has the 23 +following columns: 24 + 25 +<table striped=1> 26 +<tr><th>Column Name<th>Contents 27 +<tr><td>name <td> File name/path for the zip file record. 28 +<tr><td>mode <td> UNIX mode, as returned by stat(2) for the zip file record (an 29 + integer). This identifies the type of record (file, directory 30 + or symbolic link), and the associated user/group/all 31 + permissions. 32 +<tr><td>mtime <td> UTC timestamp, in seconds since the UNIX epoch (an integer). 33 +<tr><td>sz <td> Size of associated data in bytes after it has been 34 + uncompressed (an integer). 35 +<tr><td>rawdata <td> Raw (possibly compressed) data associated with zip file 36 + entry (a blob). 37 +<tr><td>data <td> If the compression method for the record is either 0 or 8 38 + (see below), then the uncompressed data associated with the 39 + zip file entry. Or, if the compression method is not 0 or 8, 40 + this column contains a NULL value. 41 +<tr><td>method <td> The compression method used to compress the data (an 42 + integer). The value 0 indicates that the data is stored 43 + in the zip archive without compression. 8 means the 44 + raw deflate algorithm. 45 +</table> 46 + 47 +<h2>Writing</h2> 48 + 49 +<p>In order to create or modify an existing zip file, a "zipfile" virtual 50 +table must be created in the database schema. The CREATE VIRTUAL TABLE 51 +statement expects a path to the zip file as its only argument. For example, to 52 +write to zip file "test.zip" in the current directory, a zipfile table may be 53 +created using: 54 + 55 +<codeblock> 56 + CREATE VIRTUAL TABLE temp.zip USING zipfile('test.zip'); 57 +</codeblock> 58 + 59 +<p>Such a virtual table has the same columns as the table-valued function 60 +described in the previous section. Records may be removed from an existing 61 +zip archive by deleting the corresponding rows. For example, to remove file 62 +"m.txt" from zip archive "test.zip" using the virtual table created above: 63 + 64 +<codeblock> 65 + DELETE FROM temp.zip WHERE name = 'm.txt'; 66 +</codeblock> 67 + 68 +<p>Similarly, entries may be added to a zip archive by inserting new rows. 69 +For example, to add file "m.txt" containing the text "abcdefghi" to zip 70 +archive "test.zip": 71 + 72 +<codeblock> 73 + INSERT INTO temp.zip(name, data) VALUES('m.txt', 'abcdefghi'); 74 +</codeblock> 75 + 76 +<p>The following rules and caveats apply to the values specified as part of 77 +each INSERT statement: 78 + 79 +<table striped=1> 80 +<tr><th>Columns <th> Notes 81 +<tr><td> name 82 + <td> A non-NULL text value must be specified for the name column. 83 + Usually, it is an error if the specified name already exists in the 84 + archive. Or, if the insert statement is actually a "REPLACE" or "INSERT 85 + OR REPLACE" statement, then the existing archive member is removed 86 + before adding the new entry. 87 + 88 +<tr><td> mode 89 + <td> If NULL is inserted into the mode column, then the mode of the 90 + new archive entry is automatically set to 33188 (-rw-r--r--). This 91 + is a regular non-executable file writable by its owner and readable 92 + by all others. <br><br> 93 + 94 + If the specified value is an integer (or text that looks like 95 + an integer), it is inserted verbatim. If the value is not a valid UNIX 96 + mode, some programs may behave unexpectedly when extracting files 97 + from the archive.<br><br> 98 + 99 + Finally, if the value specified for this column is not an integer 100 + or a NULL, then it is assumed to be a UNIX permissions string similar 101 + to those output by the "ls -l" command (e.g. "-rw-r--r--", "drwxr-xr-x" 102 + etc.). In this case, if the string cannot be parsed it is an error. 103 + 104 +<tr><td> mtime 105 +<td> If NULL is inserted into the mtime column, then the timestamp 106 + of the new entry is set to the current time. Otherwise, the specified 107 + value is interpreted as an integer and used as is. 108 + 109 +<tr><td> sz, rawdata, data, method 110 + <td> The data associated with the new entry is specified by providing 111 + values for the final four columns of the zipfile virtual 112 + table - sz, rawdata, data and method. Only the following combinations 113 + are valid:<br><br> 114 + 115 + <b>sz=NULL, rawdata=NULL, data≠NULL, method=NULL</b>: 116 + In this case the value specified for the "data" column is stored as the 117 + associated data in the archive. Zipfile automatically compresses it 118 + using the deflate algorithm (method=8) if doing so would be advantageous. 119 + <br><br> 120 + 121 + <b>sz=NULL, rawdata=NULL, data≠NULL, method IN (0,8)</b>: 122 + In this case the value supplied for the "method" column must 123 + be either 0 or 8. In either case the value specified for the "data" 124 + column is stored as the associated data, compressed if 8 was specified 125 + for the "method" column, or as is otherwise. 126 + <br><br> 127 + 128 + <b>sz≠NULL, rawdata≠NULL, data=NULL, method≠NULL</b>: 129 + In this case the value supplied for column "rawdata" must be 130 + already compressed according to the integer method supplied for "method" 131 + (which can take any unsigned 16-bit value, not just 0 or 8). The size of 132 + the uncompressed data (an integer) must be specified for column "sz". 133 +</table> 134 + 135 +<p> UPDATE statements are not supported by zipfile virtual tables. Nor is 136 + specifying an explicit value for the rowid field as part of an INSERT 137 + statement. 138 + 139 + 140 + 141 +