Debian Codegraph @

www/zipfile.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>The SQLite Zipfile Module</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 The SQLite Zipfile Module
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
126 <div class="fancy-toc1"><a href="#obtaining_and_compiling_zipfile">2. Obtaining and Compiling Zipfile</a></div>
127 <div class="fancy-toc1"><a href="#using_zipfile">3. Using Zipfile</a></div>
128 <div class="fancy-toc2"><a href="#table_valued_function_read_only_access_">3.1. Table-Valued Function (read-only access)</a></div>
129 <div class="fancy-toc2"><a href="#virtual_table_interface_read_write_access_">3.2. Virtual Table Interface (read/write access)</a></div>
130 <div class="fancy-toc3"><a href="#adding_entries_to_a_zip_archive">3.2.1. Adding Entries to a Zip Archive</a></div>
131 <div class="fancy-toc3"><a href="#_deleting_zip_archive_entries_">3.2.2. Deleting Zip Archive Entries </a></div>
132 <div class="fancy-toc3"><a href="#_updating_existing_zip_archive_entries_">3.2.3. Updating Existing Zip Archive Entries </a></div>
133 <div class="fancy-toc2"><a href="#_the_zipfile_aggregate_function_">3.3. The zipfile() Aggregate Function </a></div>
134 </div>
135 </details>
136 </div>
137
138
139
140
141 <h1 id="overview"><span>1. </span>Overview</h1>
142
143 <p> The zipfile module provides read/write access to simple
144 <a href="https://en.wikipedia.org/wiki/Zip_%28file_format%29">ZIP archives</a>.
145 The current implementation has the following restrictions:
146
147 </p><ul>
148 <li> Does not support encryption.
149 </li><li> Does not support ZIP archives that span multiple files.
150 </li><li> Does not support zip64 extensions.
151 </li><li> The only compression algorithm supported is
152 <a href="https://zlib.net">"deflate"</a>.
153 </li></ul>
154
155 <p> Some or all of these restrictions may be removed in the future.
156
157 </p><h1 id="obtaining_and_compiling_zipfile"><span>2. </span>Obtaining and Compiling Zipfile</h1>
158
159 <p>The code for the zipfile module is found in the
160 <a href="https://sqlite.org/src/file/ext/misc/zipfile.c">ext/misc/zipfile.c</a>
161 file of the
162 <a href="https://sqlite.org/src">main SQLite source tree</a>.
163 It may be compiled into an SQLite
164 <a href="loadext.html">loadable extension</a> using a command like:
165
166 </p><div class="codeblock"><pre>gcc -g -fPIC -shared zipfile.c -o zipfile.so
167 </pre></div>
168
169 <p>Alternatively, the zipfile.c file may be compiled into the application.
170 In this case, the following function should be invoked to register the
171 extension with each new database connection:
172
173 </p><div class="codeblock"><pre>int sqlite3_zipfile_init(sqlite3 *db, void*, void*);
174 </pre></div>
175
176 <p> The first argument passed should be the database handle to register the
177 extension with. The second and third arguments should both be passed 0.
178
179 </p><p> Zipfile is included in most builds of the <a href="cli.html">command-line shell</a>.
180
181 </p><h1 id="using_zipfile"><span>3. </span>Using Zipfile</h1>
182
183 <p>The zipfile module provides three similar interfaces for accessing, updating
184 and creating zip file archives:
185
186 </p><ol>
187 <li> A table-valued function, which provides read-only access to existing
188 archives, either from the file-system or in-memory.
189 </li><li> A virtual table, which provides read and write access to archives
190 stored in the file-system.
191 </li><li> An SQL aggregate function, which can be used to create new archives
192 in memory.
193 </li></ol>
194
195 <p>The zipfile module provides two similar interfaces for accessing zip
196 archives. A table-valued function, which provides read-only access to
197 existing archives, and a virtual table interface, which provides both
198 read and write access.
199
200 </p><h2 id="table_valued_function_read_only_access_"><span>3.1. </span>Table-Valued Function (read-only access)</h2>
201
202 <p>For reading existing zip archives, the Zipfile module provides a
203 <a href="vtab.html#tabfunc2">table-valued function</a> that accepts a single argument. If the argument
204 is a text value, then it is a path to a zip archive to read from the
205 file-system. Or, if the argument is an SQL blob, then it is the zip
206 archive data itself.
207
208 </p><p>For example, to inspect the contents of zip archive "test.zip" from
209 the current directory:
210
211 </p><div class="codeblock"><pre>SELECT * FROM zipfile('test.zip');
212 </pre></div>
213
214 <p>Or, from the SQLite shell tool (the <a href="cli.html#fileio">readfile()</a>
215 function reads the contents of a file from the file-system and returns it as a
216 blob):
217
218 </p><div class="codeblock"><pre>SELECT * FROM zipfile( readfile('test.zip') );
219 </pre></div>
220
221 <p>The table-valued function returns one row for each record (file,
222 directory or symbolic link) in the zip archive. Each row has the
223 following columns:
224
225 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
226 <tr style="text-align:left"><th>Column&nbsp;Name</th><th>Contents
227 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>name </td><td> File name/path for the zip file record.
228 </td></tr><tr style="text-align:left"><td>mode </td><td> UNIX mode, as returned by stat(2) for the zip file record (an
229 integer). This identifies the type of record (file, directory
230 or symbolic link), and the associated user/group/all
231 permissions.
232 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>mtime </td><td> UTC timestamp, in seconds since the UNIX epoch (an integer).
233 </td></tr><tr style="text-align:left"><td>sz </td><td> Size of associated data in bytes after it has been
234 uncompressed (an integer).
235 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>rawdata </td><td> Raw (possibly compressed) data associated with zip file
236 entry (a blob).
237 </td></tr><tr style="text-align:left"><td>data </td><td> If the compression method for the record is either 0 or 8
238 (see below), then the uncompressed data associated with the
239 zip file entry. Or, if the compression method is not 0 or 8,
240 this column contains a NULL value.
241 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>method </td><td> The compression method used to compress the data (an
242 integer). The value 0 indicates that the data is stored
243 in the zip archive without compression. 8 means the
244 raw deflate algorithm.
245 </td></tr></table>
246
247 <h2 id="virtual_table_interface_read_write_access_"><span>3.2. </span>Virtual Table Interface (read/write access)</h2>
248
249 <p>In order to create or modify an existing zip file, a "zipfile" virtual
250 table must be created in the database schema. The CREATE VIRTUAL TABLE
251 statement expects a path to the zip file as its only argument. For example, to
252 write to zip file "test.zip" in the current directory, a zipfile table may be
253 created using:
254
255 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.zip USING zipfile('test.zip');
256 </pre></div>
257
258 <p>Such a virtual table has the same columns as the table-valued function
259 described in the previous section. It may be read from using a SELECT
260 statement in the same way as the table-valued function can.
261
262 </p><p>Using the virtual table interface, new entries may be added to a zip
263 archive by inserting new rows into the virtual table. Entries may be
264 removed by deleting rows or modified by updating them.
265
266 </p><a name="adding_entries_to_a_zip_archive"></a>
267 <h3 tags="Adding to Zip" id="adding_entries_to_a_zip_archive"><span>3.2.1. </span>Adding Entries to a Zip Archive</h3>
268
269 <p>Entries may be added to a zip archive by inserting new rows. The easiest
270 way to do this is to specify values for the "name" and "data" columns only and
271 have zipfile fill in sensible defaults for other fields. To insert a directory
272 into the archive, set the "data" column to NULL. For example, to add the
273 directory "dir1" and the file "m.txt" containing the text "abcdefghi" to zip
274 archive "test.zip":
275
276 </p><div class="codeblock"><pre>INSERT INTO temp.zip(name, data) VALUES('dir1', NULL); <i>-- Add directory </i>
277 INSERT INTO temp.zip(name, data) VALUES('m.txt', 'abcdefghi'); <i>-- Add regular file </i>
278 </pre></div>
279
280 <p>When a directory is inserted, if the "name" value does not end with
281 a '/' character, the zipfile module appends one. This is necessary for
282 compatibility with other programs (most notably "info-zip") that
283 manipulate zip archives.
284
285 </p><p>To insert a symbolic link, the user must also supply a "mode" value.
286 For example, to add a symbolic link from "link.txt" to "m.txt":
287
288 </p><div class="codeblock"><pre>INSERT INTO temp.zip(name, mode, data) VALUES('link.txt', 'lrwxrw-rw-', 'm.txt');
289 </pre></div>
290
291 <p>The following rules and caveats apply to the values specified as part of
292 each INSERT statement:
293
294 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
295 <tr style="text-align:left"><th>Columns </th><th> Notes
296 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td> name
297 </td><td> A non-NULL text value must be specified for the name column.
298 It is an error if the specified name already exists in the
299 archive.
300
301 </td></tr><tr style="text-align:left"><td> mode
302 </td><td> If NULL is inserted into the mode column, then the mode of the
303 new archive entry is automatically set to either 33188 (-rw-r--r--)
304 or 16877 (drwxr-xr-x), depending on whether or not the values
305 specified for columns "sz", "data" and "rawdata" indicate that
306 the new entry is a directory.<br><br>
307
308 If the specified value is an integer (or text that looks like
309 an integer), it is inserted verbatim. If the value is not a valid UNIX
310 mode, some programs may behave unexpectedly when extracting files
311 from the archive.<br><br>
312
313 Finally, if the value specified for this column is not an integer
314 or a NULL, then it is assumed to be a UNIX permissions string similar
315 to those output by the "ls -l" command (e.g. "-rw-r--r--", "drwxr-xr-x"
316 etc.). In this case, if the string cannot be parsed it is an error.
317
318 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td> mtime
319 </td><td> If NULL is inserted into the mtime column, then the timestamp
320 of the new entry is set to the current time. Otherwise, the specified
321 value is interpreted as an integer and used as is.
322
323 </td></tr><tr style="text-align:left"><td> sz
324 </td><td> This column must be set to NULL. If a non-NULL value is inserted into
325 this column, or if a new non-NULL value is provided using an UPDATE
326 statement, it is an error.
327
328 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td> rawdata
329 </td><td> This column must be set to NULL. If a non-NULL value is inserted into
330 this column, or if a new non-NULL value is provided using an UPDATE
331 statement, it is an error.
332
333 </td></tr><tr style="text-align:left"><td> data
334 </td><td>
335 To insert a directory into the archive, this field must be set to
336 NULL. In this case if a value was explicitly specified for the "mode"
337 column, then it must be consistent with a directory (i.e. it must be
338 true that (mode & 0040000)=0040000). <br><br>
339
340 Otherwise, the value inserted into this field is the file contents
341 for a regular file, or the target of a symbolic link.
342 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td> method
343 </td><td>
344 This field must be set one of integer values 0 and 8, or else to
345 NULL. <br><br>
346
347 For a directory entry, any value inserted into this field is ignored.
348 Otherwise, if it is set to 0, then the file data or symbolic link
349 target is stored as is in the zip archive and the compression method
350 set to 0. If it is set to 8, then the file data or link target is
351 compressed using deflate compression before it is stored and the
352 compression method set to 8. Finally, if a NULL value is written
353 to this field, the zipfile module automatically decides whether
354 or not to compress the data before storing it.
355 </td></tr></table>
356
357 <p> Specifying an explicit value for the rowid field as part of an INSERT
358 statement is not supported. Any value supplied is ignored.
359
360 </p><h3 id="_deleting_zip_archive_entries_"><span>3.2.2. </span> Deleting Zip Archive Entries </h3>
361
362 <p>Records may be removed from an existing zip archive by deleting the
363 corresponding rows. For example, to remove file "m.txt" from zip archive
364 "test.zip" using the virtual table created above:
365
366 </p><div class="codeblock"><pre>DELETE FROM temp.zip WHERE name = 'm.txt';
367 </pre></div>
368
369 <p>Note that deleting records from a zip archive does not reclaim the
370 space used within the archive - it merely removes an entry from the
371 archives "Central Directory Structure", making the entry inaccessible.
372 One way to work around this inefficiency is to create a new zip
373 archive based on the contents of the edited archive. For example, after
374 editing the archive accessed via virtual table temp.zzz:
375
376 </p><div class="codeblock"><pre><i>-- Create a new, empty, archive: </i>
377 CREATE VIRTUAL TABLE temp.newzip USING zipfile('new.zip');
378
379 <i>-- Copy the contents of the existing archive into the new archive</i>
380 INSERT INTO temp.newzip(name, mode, mtime, data, method)
381 SELECT name, mode, mtime, data, method FROM temp.zzz;
382 </pre></div>
383
384 <h3 id="_updating_existing_zip_archive_entries_"><span>3.2.3. </span> Updating Existing Zip Archive Entries </h3>
385
386 <p>Existing zip archive entries may be modified using UPDATE statements.
387
388 </p><p>The three leftmost columns of a zipfile virtual table, "name", "mode"
389 and "mtime", may each be set to any value that may be inserted into the same
390 column (see above). If either "mode" or "mtime" is set to NULL, the final
391 value is determined as described for an INSERT of a NULL value - the current
392 time for "mtime" and either 33188 or 16877 for "mode", depending on whether
393 or not the values specified for the next four columns of the zipfile table
394 indicate that the entry is a directory or a file.
395
396 </p><p>It is an error to attempt to set the sz or rawdata field to any value
397 other than NULL.
398
399 </p><p>The data and method columns may also be set as described for an INSERT
400 above.
401
402 </p><h2 id="_the_zipfile_aggregate_function_"><span>3.3. </span> The zipfile() Aggregate Function </h2>
403
404 <p> New zip archives may be constructed entirely within memory using the
405 zipfile() aggregate function. Each row visited by the aggregate function
406 adds an entry to the zip archive. The value returned is a blob containing
407 the entire archive image.
408
409 </p><p> The zipfile() aggregate function may be called with 2, 4 or 5
410 arguments. If it is called with 5 arguments, then the entry added to
411 the archive is equivalent to inserting the same values into the "name",
412 "mode", "mtime", "data" and "method" columns of a zipfile virtual table.
413
414 </p><p> If zipfile() is invoked with 2 arguments, then the entry added to
415 the archive is equivalent to that added by inserting the same two values into
416 the "name" and "data" columns of a zipfile virtual table, with all
417 other values set to NULL. If invoked with 4 arguments, it is equivalent
418 to inserting the 4 values into the "name", "mode", "mtime" and "data"
419 columns. In other words, the following pairs of queries are equivalent:
420
421 </p><div class="codeblock"><pre>SELECT zipfile(name, data) ...
422 SELECT zipfile(name, NULL, NULL, data, NULL) ...
423
424 SELECT zipfile(name, mode, mtime, data) ...
425 SELECT zipfile(name, mode, mtime, data, NULL) ...
426 </pre></div>
427
428 <p> For example, to create an archive containing two text files, "a.txt" and
429 "b.txt", containing the text "abc" and "123" respectively:
430
431 </p><div class="codeblock"><pre>WITH contents(name, data) AS (
432 VALUES('a.txt', 'abc') UNION ALL
433 VALUES('b.txt', '123')
434 )
435 SELECT zipfile(name, data) FROM contents;
436 </pre></div>
437 <p align="center"><small><i>This page was last updated on 2023-06-07 13:17:51Z </small></i></p>
438