3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
13 package require Tcl 8.6
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix zipfile
22 if {[catch {load_static_extension db zipfile} error]} {
23 puts "Skipping zipfile tests, hit load error: $error"
26 if {[catch {load_static_extension db fileio} error]} {
27 puts "Skipping zipfile tests, hit load error: $error"
33 fconfigure $fd -translation binary -encoding binary
39 unset -nocomplain ::UNZIP
41 if {[catch {exec unzip} msg]==0 && \
42 [regexp -line {^UnZip \d+\.\d+ .*? Info-ZIP\.} $msg]} {
44 proc fix_stat_mode {name mode} {
45 if {$::tcl_platform(platform)=="windows"} {
47 # NOTE: Set or unset the write bits of the file permissions
48 # based on the read-only attribute because the Win32
49 # version of UnZip does this.
51 set writebits 0x12; # 0o22
53 if {[file attributes $name -readonly]} {
54 set result [expr {$result | $writebits}]
56 set result [expr {$result & ~$writebits}]
63 proc do_unzip {file} {
64 forcedelete test_unzip
66 exec $::UNZIP -d test_unzip $file
68 db func modefix fix_stat_mode
71 SELECT replace(name,'test_unzip/',''),modefix(name,mode),mtime,data
72 FROM fsdir('test_unzip')
73 WHERE name!='test_unzip'
81 # The argument is a blob (not a hex string) containing a zip archive.
82 # This proc removes the extended timestamp fields from the archive
83 # and returns the result.
85 proc remove_timestamps {blob} {
86 set hex [binary encode hex $blob]
87 set hex [string map {55540500 00000500} $hex]
88 binary decode hex $hex
92 # Argument $file is the name of a zip archive on disk. This function
93 # executes test cases to check that the results of each of the following
96 # SELECT * FROM zipfile($file)
97 # SELECT * FROM zipfile( readfile($file) )
98 # SELECT * FROM zipfile(
99 # (SELECT zipfile(name,mode,mtime,data,method) FROM zipfile($file))
102 proc do_zipfile_blob_test {tn file} {
105 set q1 {SELECT name,mode,mtime,method,quote(data) FROM zipfile($file)}
106 set q2 {SELECT name,mode,mtime,method,quote(data) FROM zipfile( r($file) )}
107 set q3 {SELECT name,mode,mtime,method,quote(data) FROM zipfile(
108 ( SELECT zipfile(name,mode,mtime,data,method) FROM zipfile($file) )
119 uplevel [list do_test $tn.1 [list set {} $r2] $r1]
120 uplevel [list do_test $tn.2 [list set {} $r3] $r1]
123 # Argument $file is a zip file on disk. This command runs tests to:
125 # 1. Unpack the archive with unix command [unzip] and compare the
126 # results to reading the same archive using the zipfile() table
129 # 2. Creates a new archive with the same contents using the zipfile()
130 # aggregate function as follows:
132 # SELECT writefile('test_unzip.zip',
133 # ( SELECT zipfile(name,mode,mtime,data,method) FROM zipfile($file) )
136 # Then tests that unpacking the new archive using [unzip] produces
137 # the same results as in (1).
139 proc do_unzip_test {tn file} {
140 db func sss strip_slash
143 SELECT writefile('test_unzip.zip',
144 ( SELECT zipfile(name,mode,mtime,data,method) FROM zipfile($file) )
149 SELECT sss(name),mode,mtime,data FROM zipfile($file) ORDER BY name
151 set r2 [do_unzip $file]
152 set r3 [do_unzip test_unzip.zip]
154 uplevel [list do_test $tn.1 [list set {} $r2] $r1]
155 uplevel [list do_test $tn.2 [list set {} $r3] $r1]
157 proc strip_slash {in} { regsub {/$} $in {} }
159 proc do_zip_tests {tn file} {
160 uplevel do_zipfile_blob_test $tn.1 $file
161 uplevel do_unzip_test $tn.2 $file
165 do_execsql_test 1.0 {
166 CREATE VIRTUAL TABLE temp.zz USING zipfile('test.zip');
167 PRAGMA table_info(zz);
178 do_catchsql_test 1.1.0.1 {
179 INSERT INTO zz(name, mode, mtime, sz, rawdata, method)
180 VALUES('f.txt', '-rw-r--r--', 1000000000, 5, 'abcde', 0);
181 } {1 {rawdata must be NULL}}
182 do_catchsql_test 1.1.0.2 {
183 INSERT INTO zz(name, mtime, sz, data, method)
184 VALUES('g.txt', 1000000002, 5, '12345', 0);
185 } {1 {sz must be NULL}}
186 do_catchsql_test 1.1.0.3 {
187 INSERT INTO zz(name, mtime, rawdata, method)
188 VALUES('g.txt', 1000000002, '12345', 0);
189 } {1 {rawdata must be NULL}}
190 do_catchsql_test 1.1.0.4 {
191 INSERT INTO zz(name, data, method)
192 VALUES('g.txt', '12345', 7);
193 } {1 {unknown compression method: 7}}
195 do_execsql_test 1.1.1 {
196 INSERT INTO zz(name, mode, mtime, data, method)
197 VALUES('f.txt', '-rw-r--r--', 1000000000, 'abcde', 0);
199 do_execsql_test 1.1.2 {
200 INSERT INTO zz(name, mode, mtime, data, method)
201 VALUES('g.txt', NULL, 1000000002, '12345', 0);
204 do_execsql_test 1.2 {
205 SELECT name, mtime, data FROM zipfile('test.zip')
207 f.txt 1000000000 abcde
208 g.txt 1000000002 12345
210 do_zip_tests 1.2a test.zip
212 do_execsql_test 1.3 {
213 INSERT INTO zz(name, mode, mtime, data) VALUES('h.txt',
214 '-rw-r--r--', 1000000004, 'aaaaaaaaaabbbbbbbbbb'
217 do_zip_tests 1.3a test.zip
219 do_execsql_test 1.4 {
220 SELECT name, mtime, data, method FROM zipfile('test.zip');
222 f.txt 1000000000 abcde 0
223 g.txt 1000000002 12345 0
224 h.txt 1000000004 aaaaaaaaaabbbbbbbbbb 8
228 do_execsql_test 1.4.1 {
229 SELECT name, json_extract( zipfile_cds(z) , '$.crc32')!=0
230 FROM zipfile('test.zip');
237 do_catchsql_test 1.4.2 {
238 SELECT zipfile_cds(mode) FROM zipfile('test.zip');
241 do_execsql_test 1.5.1 {
243 INSERT INTO zz(name, mode, mtime, data, method)
244 VALUES('i.txt', '-rw-r--r--', 1000000006, 'zxcvb', 0);
247 } {f.txt g.txt h.txt i.txt}
248 do_execsql_test 1.5.2 {
250 } {f.txt g.txt h.txt i.txt}
251 do_execsql_test 1.5.3 {
252 SELECT data FROM zz WHERE name='i.txt';
255 do_execsql_test 1.6.0 {
256 DELETE FROM zz WHERE name='g.txt';
258 } {f.txt h.txt i.txt}
260 do_execsql_test 1.6.1 {
261 SELECT name, mode, mtime, data, method FROM zipfile('test.zip');
263 f.txt 33188 1000000000 abcde 0
264 h.txt 33188 1000000004 aaaaaaaaaabbbbbbbbbb 8
265 i.txt 33188 1000000006 zxcvb 0
267 do_zip_tests 1.6.1a test.zip
269 do_execsql_test 1.6.2 {
270 UPDATE zz SET mtime=4 WHERE name='i.txt';
271 SELECT name, mode, mtime, data, method FROM zipfile('test.zip');
273 f.txt 33188 1000000000 abcde 0
274 h.txt 33188 1000000004 aaaaaaaaaabbbbbbbbbb 8
275 i.txt 33188 4 zxcvb 0
278 if {$::tcl_platform(platform)=="unix"} {
282 set modes -rw-r--r--; # no execute bits on Win32
286 do_execsql_test 1.6.3 {
287 UPDATE zz SET mode=$modes WHERE name='h.txt';
288 SELECT name, mode, mtime, data, method FROM zipfile('test.zip');
289 } [string map [list %perms% $perms] {
290 f.txt 33188 1000000000 abcde 0
291 h.txt %perms% 1000000004 aaaaaaaaaabbbbbbbbbb 8
292 i.txt 33188 4 zxcvb 0
294 do_zip_tests 1.6.3a test.zip
296 do_execsql_test 1.6.4 {
297 UPDATE zz SET name = 'blue.txt' WHERE name='f.txt';
298 SELECT name, mode, mtime, data, method FROM zipfile('test.zip');
299 } [string map [list %perms% $perms] {
300 blue.txt 33188 1000000000 abcde 0
301 h.txt %perms% 1000000004 aaaaaaaaaabbbbbbbbbb 8
302 i.txt 33188 4 zxcvb 0
304 do_zip_tests 1.6.4a test.zip
306 do_execsql_test 1.6.5 {
307 UPDATE zz SET data = 'edcba' WHERE name='blue.txt';
308 SELECT name, mode, mtime, data, method FROM zipfile('test.zip');
309 } [string map [list %perms% $perms] {
310 blue.txt 33188 1000000000 edcba 0
311 h.txt %perms% 1000000004 aaaaaaaaaabbbbbbbbbb 8
312 i.txt 33188 4 zxcvb 0
315 do_execsql_test 1.6.6 {
316 UPDATE zz SET mode=NULL, data = NULL WHERE name='blue.txt';
317 SELECT name, mode, mtime, data, method FROM zipfile('test.zip');
318 } [string map [list %perms% $perms] {
319 blue.txt/ 16877 1000000000 {} 0
320 h.txt %perms% 1000000004 aaaaaaaaaabbbbbbbbbb 8
321 i.txt 33188 4 zxcvb 0
324 do_catchsql_test 1.6.7 {
325 UPDATE zz SET data=NULL WHERE name='i.txt'
326 } {1 {zipfile: mode does not match data}}
327 do_execsql_test 1.6.8 {
328 SELECT name, mode, mtime, data, method FROM zipfile('test.zip');
329 } [string map [list %perms% $perms] {
330 blue.txt/ 16877 1000000000 {} 0
331 h.txt %perms% 1000000004 aaaaaaaaaabbbbbbbbbb 8
332 i.txt 33188 4 zxcvb 0
335 do_execsql_test 1.6.9 {
336 UPDATE zz SET data = '' WHERE name='i.txt';
337 SELECT name,mode,mtime,data,method from zipfile('test.zip');
338 } [string map [list %perms% $perms] {
339 blue.txt/ 16877 1000000000 {} 0
340 h.txt %perms% 1000000004 aaaaaaaaaabbbbbbbbbb 8
344 do_execsql_test 1.6.10 {
345 SELECT a.name, a.data
346 FROM zz AS a, zz AS b
347 WHERE a.name=+b.name AND +a.mode=b.mode
350 h.txt aaaaaaaaaabbbbbbbbbb
354 do_execsql_test 1.6.11 {
355 SELECT name, data FROM zz WHERE name LIKE '%txt'
357 h.txt aaaaaaaaaabbbbbbbbbb
361 do_execsql_test 1.7 {
366 #-------------------------------------------------------------------------
370 load_static_extension db fileio
371 load_static_extension db zipfile
372 do_execsql_test 2.1 {
373 CREATE VIRTUAL TABLE zzz USING zipfile('test.zip');
374 INSERT INTO zzz(name, mode) VALUES('dirname', 'drwxr-xr-x');
375 SELECT name, mode, data FROM zzz;
376 } {dirname/ 16877 {}}
377 do_execsql_test 2.2 {
378 INSERT INTO zzz(name, data) VALUES('dirname2', NULL);
379 INSERT INTO zzz(name, data) VALUES('dirname2/file1.txt', 'abcdefghijklmnop');
380 SELECT name, mode, data FROM zzz;
384 dirname2/file1.txt 33188 abcdefghijklmnop
387 do_catchsql_test 2.3 {
388 UPDATE zzz SET name = 'dirname3' WHERE name = 'dirname/';
390 do_execsql_test 2.4 {
391 SELECT name, mode, data FROM zzz;
395 dirname2/file1.txt 33188 abcdefghijklmnop
397 do_zip_tests 2.4a test.zip
399 # Check that the [unzip] utility can unpack our archive.
401 if {[info exists ::UNZIP]} {
405 if {$::tcl_platform(platform)=="unix"} {
410 set rc [catch { exec $::UNZIP test.zip > $null } msg]
413 do_test 2.5.2 { file isdir dirname3 } 1
414 do_test 2.5.3 { file isdir dirname2 } 1
415 do_test 2.5.4 { file isdir dirname2/file1.txt } 0
417 set fd [open dirname2/file1.txt]
424 #-------------------------------------------------------------------------
427 load_static_extension db zipfile
428 load_static_extension db fileio
430 do_execsql_test 3.0 {
431 CREATE VIRTUAL TABLE temp.x1 USING zipfile('test.zip');
432 INSERT INTO x1(name, data) VALUES('dir1/', NULL);
433 INSERT INTO x1(name, data) VALUES('file1', '1234');
434 INSERT INTO x1(name, data) VALUES('dir1/file2', '5678');
441 do_catchsql_test 3.1.$tn.0 {
442 INSERT INTO x1(name, data) VALUES($fname, NULL);
443 } [list 1 "duplicate name: \"$fname/\""]
444 do_catchsql_test 3.1.$tn.1 {
445 INSERT INTO x1(name, data) VALUES($fname || '/', NULL);
446 } [list 1 "duplicate name: \"$fname/\""]
447 do_catchsql_test 3.1.$tn.2 {
448 INSERT INTO x1(name, data) VALUES($fname, 'abcd');
449 } [list 1 "duplicate name: \"$fname\""]
452 do_catchsql_test 3.2 {
454 } {1 {no such column: rowid}}
456 #-------------------------------------------------------------------------
457 # Test some error conditions.
459 do_catchsql_test 4.1 {
460 CREATE VIRTUAL TABLE yyy USING zipfile();
461 } {1 {zipfile constructor requires one argument}}
462 do_catchsql_test 4.2 {
463 CREATE VIRTUAL TABLE yyy USING zipfile('test.zip', 'test.zip');
464 } {1 {zipfile constructor requires one argument}}
466 do_catchsql_test 4.3 {
467 SELECT * FROM zipfile()
468 } {1 {zipfile() function requires an argument}}
470 do_catchsql_test 4.4 {
471 SELECT * FROM zipfile('/path/that/does/not/exist')
472 } {1 {cannot open file: /path/that/does/not/exist}}
479 do_catchsql_test 4.5.$tn {
480 WITH m(m) AS ( SELECT $mode)
481 SELECT zipfile('a.txt', m, 1000, 'xyz') FROM m
482 } [list 1 "zipfile: parse error in mode: $mode"]
485 do_catchsql_test 4.6 {
486 WITH c(name,data) AS ( SELECT 'a.txt', 'abc')
487 SELECT zipfile(name) FROM c
488 } {1 {wrong number of arguments to function zipfile()}}
490 do_catchsql_test 4.7 {
491 WITH c(name,data) AS (
492 SELECT 'a.txt', 'abc' UNION ALL
495 SELECT zipfile(name,data) FROM c
496 } {1 {first argument to zipfile() must be non-NULL}}
498 do_catchsql_test 4.8 {
499 WITH c(name,data,method) AS (
500 SELECT 'a.txt', 'abc', 0
501 UNION SELECT 'b.txt', 'def', 8
502 UNION SELECT 'c.txt', 'ghi', 16
504 SELECT zipfile(name,NULL,NULL,data,method) FROM c
505 } {1 {illegal method value: 16}}
507 do_catchsql_test 4.9 {
508 WITH c(name,data) AS (
509 SELECT 'a.txt', 'abc'
510 UNION SELECT 'b.txt', 'def'
511 UNION SELECT 'c.txt/', 'ghi'
513 SELECT zipfile(name,NULL,NULL,data) FROM c
514 } {1 {non-directory name must not end with /}}
516 #--------------------------------------------------------------------------
518 db func rt remove_timestamps
519 do_execsql_test 5.0 {
520 WITH c(name,mtime,data) AS (
521 SELECT 'a.txt', 946684800, 'abc'
523 SELECT name,mtime,data FROM zipfile(
524 ( SELECT rt( zipfile(name,NULL,mtime,data,NULL) ) FROM c )
530 if {[info exists ::UNZIP]} {
532 forcedelete test1.zip test2.zip
535 WITH c(name,mtime,data) AS (
536 SELECT 'a.txt', 946684800, 'abc' UNION ALL
537 SELECT 'b.txt', 1000000000, 'abc' UNION ALL
538 SELECT 'c.txt', 1111111000, 'abc'
540 SELECT writefile('test1.zip', rt( zipfile(name, NULL, mtime, data) ) ),
541 writefile('test2.zip', ( zipfile(name, NULL, mtime, data) ) )
544 forcedelete test_unzip
545 file mkdir test_unzip
546 exec $::UNZIP -d test_unzip test1.zip
549 SELECT name, strftime('%s', mtime, 'unixepoch', 'localtime')
550 FROM fsdir('test_unzip') WHERE name!='test_unzip'
554 test_unzip/a.txt 946684800
555 test_unzip/b.txt 1000000000
556 test_unzip/c.txt 1111111000
559 # fsdir() issue reported on the mailing list on 2018-03-14 by Jack Thaw.
562 SELECT sum(name LIKE '%/a.txt')
563 FROM (VALUES(1),(2),(3)) CROSS JOIN fsdir('test_unzip')
567 do_execsql_test 6.1 {
568 SELECT name, mtime, data FROM zipfile('test1.zip')
576 forcedelete test_unzip
577 file mkdir test_unzip
578 exec $::UNZIP -d test_unzip test2.zip
582 FROM fsdir('test_unzip') WHERE name!='test_unzip'
586 test_unzip/a.txt 946684800
587 test_unzip/b.txt 1000000000
588 test_unzip/c.txt 1111111000
591 do_execsql_test 6.3 {
592 SELECT name, mtime, sz, rawdata, data FROM zipfile('test2.zip')
594 a.txt 946684800 3 abc abc
595 b.txt 1000000000 3 abc abc
596 c.txt 1111111000 3 abc abc
601 #-------------------------------------------------------------------------
602 # Force an IO error by truncating the zip archive to zero bytes in size
603 # while it is being read.
607 WITH c(name,data) AS (
608 SELECT '1', randomblob(1000000) UNION ALL
609 SELECT '2', randomblob(1000000) UNION ALL
610 SELECT '3', randomblob(1000000)
612 SELECT writefile('test.zip', zipfile(name, data) ) FROM c;
616 db eval { SELECT name, data FROM zipfile('test.zip') } {
617 if {$name==2} { close [open test.zip w+] }
620 } {1 {error in fread()}}
623 do_execsql_test 8.0.1 {
624 CREATE VIRTUAL TABLE zz USING zipfile('test.zip');
626 INSERT INTO zz(name, data) VALUES('a.txt', '1');
627 INSERT INTO zz(name, data) VALUES('b.txt', '2');
628 INSERT INTO zz(name, data) VALUES('c.txt', '1');
629 INSERT INTO zz(name, data) VALUES('d.txt', '2');
630 SELECT name, data FROM zz;
632 a.txt 1 b.txt 2 c.txt 1 d.txt 2
635 db eval { SELECT name, data FROM zz } {
636 if { $data=="2" } { db eval { DELETE FROM zz WHERE name=$name } }
638 execsql { SELECT name, data FROM zz }
641 db eval { SELECT name, data FROM zz } {
642 db eval { DELETE FROM zz WHERE name=$name }
644 execsql { SELECT name, data FROM zz }
648 do_execsql_test 8.1.1 {
649 CREATE VIRTUAL TABLE nogood USING zipfile('test_unzip');
651 do_catchsql_test 8.1.2 {
652 INSERT INTO nogood(name, data) VALUES('abc', 'def');
653 } {1 {zipfile: failed to open file test_unzip for writing}}
655 do_execsql_test 8.2.1 {
658 CREATE VIRTUAL TABLE nogood USING zipfile('test_unzip');
660 do_catchsql_test 8.2.2 {
661 INSERT INTO nogood(name, data) VALUES('abc', 'def');
662 } {1 {zipfile: failed to open file test_unzip for writing}}
663 do_execsql_test 8.2.3 {
668 do_execsql_test 8.3.1 {
670 CREATE VIRTUAL TABLE ok USING zipfile('test.zip');
671 INSERT INTO ok(name, data) VALUES ('sqlite3', 'elf');
675 #-------------------------------------------------------------------------
676 # Test that the zipfile aggregate correctly adds and removes "/" from
677 # the ends of directory file names.
678 do_execsql_test 9.0 {
680 VALUES('dir1') UNION ALL
681 VALUES('dir2/') UNION ALL
682 VALUES('dir3//') UNION ALL
683 VALUES('dir4///') UNION ALL
686 SELECT name FROM zipfile((SELECT zipfile(nm, NULL) FROM src))
687 } {dir1/ dir2/ dir3/ dir4/ /}
689 #-------------------------------------------------------------------------
690 # INSERT OR REPLACE and INSERT OR IGNORE
693 forcedelete test.zip test.db
695 load_static_extension db zipfile
696 load_static_extension db fileio
698 do_execsql_test 10.0 {
699 CREATE VIRTUAL TABLE z USING zipfile('test.zip');
701 do_catchsql_test 10.1 {
702 INSERT INTO z(name,data) VALUES('a0','one'),('a0','two');
703 } {1 {duplicate name: "a0"}}
704 do_execsql_test 10.2 {
705 SELECT name, data FROM z;
707 do_execsql_test 10.3 {
708 REPLACE INTO z(name,data) VALUES('a0','three'),('a0','four');
710 do_execsql_test 10.4 {
711 SELECT name, data FROM z;
713 do_execsql_test 10.5 {
714 INSERT OR IGNORE INTO z(name,data) VALUES('a0','five'),('a0','six');
716 do_execsql_test 10.6 {
717 SELECT name, data FROM z;
720 do_execsql_test 11.1 {
723 do_execsql_test 11.2 {
724 SELECT name, data FROM z;
726 do_execsql_test 11.3 {
727 INSERT INTO z (name,data) VALUES ('b0','one');
728 SELECT name, data FROM z;
730 do_execsql_test 11.4 {
731 UPDATE z SET name = 'b1' WHERE name = 'b0';
732 SELECT name, data FROM z;
734 do_execsql_test 11.5 {
735 INSERT INTO z (name,data) VALUES ('b0','one');
736 SELECT name, data FROM z ORDER BY name;
738 do_catchsql_test 11.6 {
739 UPDATE z SET name = 'b1' WHERE name = 'b0';
740 } {1 {duplicate name: "b1"}}
741 do_execsql_test 11.7 {
742 UPDATE z SET data = 'two' WHERE name = 'b0';
743 SELECT name, data FROM z ORDER BY name;
745 do_catchsql_test 11.8 {
746 UPDATE z SET name = 'b1';
747 } {1 {duplicate name: "b1"}}
748 do_catchsql_test 11.9 {
749 UPDATE z SET name = 'b2';
750 } {1 {duplicate name: "b2"}}
751 do_execsql_test 11.10 {
752 UPDATE z SET name = name;
753 SELECT name, data FROM z ORDER BY name;
755 do_execsql_test 11.11 {
756 UPDATE z SET name = name || 'suffix';
757 SELECT name, data FROM z ORDER BY name;
758 } {b0suffix two b2suffix one}