/ Check-in [e2114df1]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:When creating a new archive entry, have zipfile store UTC instead of local time in the legacy MS-DOS format timestamp field.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | zipfile-timestamp-fix
Files: files | file ages | folders
SHA3-256:e2114df18383d111dd5fbac902e08b42a7f4b2b2d6f7bf29574a3722e4a4dad5
User & Date: dan 2018-01-31 19:13:31
Context
2018-01-31
19:45
Fix a test case in zipfile.test. Closed-Leaf check-in: 4eb5b24c user: dan tags: zipfile-timestamp-fix
19:13
When creating a new archive entry, have zipfile store UTC instead of local time in the legacy MS-DOS format timestamp field. check-in: e2114df1 user: dan tags: zipfile-timestamp-fix
16:50
Improve the omit-left-join optimization so that it works in some cases when the RHS is subject to a UNIQUE but not NOT NULL constraint. check-in: 02ba8a7b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/misc/zipfile.c.

682
683
684
685
686
687
688
689
690
691



692
693
694
695
696
697
698

699
700




701





702







703
704
705
706
707
708
709
710


711
712
713
714
715
716



717
















718
719
720
721
722
723
724
725
726





727
728
729
730
731
732
733
**   File modification date:
**     Bits 00-04: day
**     Bits 05-08: month (1-12)
**     Bits 09-15: years from 1980 
**
** https://msdn.microsoft.com/en-us/library/9kkf9tah.aspx
*/
static time_t zipfileMtime(ZipfileCDS *pCDS){
  struct tm t;
  memset(&t, 0, sizeof(t));



  t.tm_sec = (pCDS->mTime & 0x1F)*2;
  t.tm_min = (pCDS->mTime >> 5) & 0x2F;
  t.tm_hour = (pCDS->mTime >> 11) & 0x1F;

  t.tm_mday = (pCDS->mDate & 0x1F);
  t.tm_mon = ((pCDS->mDate >> 5) & 0x0F) - 1;
  t.tm_year = 80 + ((pCDS->mDate >> 9) & 0x7F);


  return mktime(&t);




}













/*
** The opposite of zipfileMtime(). This function populates the mTime and
** mDate fields of the CDS structure passed as the first argument according
** to the UNIX timestamp value passed as the second.
*/
static void zipfileMtimeToDos(ZipfileCDS *pCds, u32 mUnixTime){
  time_t t = (time_t)mUnixTime;
  struct tm res;



#if !defined(_WIN32) && !defined(WIN32)
  localtime_r(&t, &res);
#else
  memcpy(&res, localtime(&t), sizeof(struct tm));
#endif




















  pCds->mTime = (u16)(
    (res.tm_sec / 2) + 
    (res.tm_min << 5) +
    (res.tm_hour << 11));

  pCds->mDate = (u16)(
    (res.tm_mday-1) +
    ((res.tm_mon+1) << 5) +
    ((res.tm_year-80) << 9));





}

/*
** If aBlob is not NULL, then it is a pointer to a buffer (nBlob bytes in
** size) containing an entire zip archive image. Or, if aBlob is NULL,
** then pFile is a file-handle open on a zip file. In either case, this
** function creates a ZipfileEntry object based on the zip archive entry







|
|
|
>
>
>
|
|
|

<
<
<
>

<
>
>
>
>
|
>
>
>
>
>

>
>
>
>
>
>
>






<
<
>
>

<
<
<
<
<
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
<

<
<
<
<
>
>
>
>
>







682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698



699
700

701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724


725
726
727





728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748



749




750
751
752
753
754
755
756
757
758
759
760
761
**   File modification date:
**     Bits 00-04: day
**     Bits 05-08: month (1-12)
**     Bits 09-15: years from 1980 
**
** https://msdn.microsoft.com/en-us/library/9kkf9tah.aspx
*/
static u32 zipfileMtime(ZipfileCDS *pCDS){
  int Y = (1980 + ((pCDS->mDate >> 9) & 0x7F));
  int M = ((pCDS->mDate >> 5) & 0x0F);
  int D = (pCDS->mDate & 0x1F);
  int B = -13;

  int sec = (pCDS->mTime & 0x1F)*2;
  int min = (pCDS->mTime >> 5) & 0x3F;
  int hr = (pCDS->mTime >> 11) & 0x1F;




  /* JD = INT(365.25 * (Y+4716)) + INT(30.6001 * (M+1)) + D + B - 1524.5 */


  /* Calculate the JD in seconds for noon on the day in question */
  if( M<3 ){
    Y = Y-1;
    M = M+12;
  }
  i64 JD = (i64)(24*60*60) * (
      (int)(365.25 * (Y + 4716))
    + (int)(30.6001 * (M + 1))
    + D + B - 1524
  );

  /* Correct the JD for the time within the day */
  JD += (hr-12) * 3600 + min * 60 + sec;

  /* Convert JD to unix timestamp (the JD epoch is 2440587.5) */
  return (u32)(JD - (i64)(24405875) * 24*60*6);
}

/*
** The opposite of zipfileMtime(). This function populates the mTime and
** mDate fields of the CDS structure passed as the first argument according
** to the UNIX timestamp value passed as the second.
*/
static void zipfileMtimeToDos(ZipfileCDS *pCds, u32 mUnixTime){


  /* Convert unix timestamp to JD (2440588 is noon on 1/1/1970) */
  i64 JD = (i64)2440588 + mUnixTime / (24*60*60);






  int A, B, C, D, E;
  int yr, mon, day;
  int hr, min, sec;

  A = (int)((JD - 1867216.25)/36524.25);
  A = JD + 1 + A - (A/4);
  B = A + 1524;
  C = (int)((B - 122.1)/365.25);
  D = (36525*(C&32767))/100;
  E = (int)((B-D)/30.6001);

  day = B - D - (int)(30.6001*E);
  mon = (E<14 ? E-1 : E-13);
  yr = mon>2 ? C-4716 : C-4715;

  hr = (mUnixTime % (24*60*60)) / (60*60);
  min = (mUnixTime % (60*60)) / 60;
  sec = (mUnixTime % 60);

  pCds->mDate = (u16)(day + (mon << 5) + ((yr-1980) << 9));
  pCds->mTime = (u16)(sec/2 + (min<<5) + (hr<<11));








  assert( mUnixTime<315507600 
       || mUnixTime==zipfileMtime(pCds) 
       || ((mUnixTime % 2) && mUnixTime-1==zipfileMtime(pCds)) 
       /* || (mUnixTime % 2) */
  );
}

/*
** If aBlob is not NULL, then it is a pointer to a buffer (nBlob bytes in
** size) containing an entire zip archive image. Or, if aBlob is NULL,
** then pFile is a file-handle open on a zip file. In either case, this
** function creates a ZipfileEntry object based on the zip archive entry

Changes to test/zipfile.test.

43
44
45
46
47
48
49












50
51
52
53
54
55
56
...
363
364
365
366
367
368
369

370




371





































372
373
      FROM fsdir('test_unzip') 
      WHERE name!='test_unzip'
      ORDER BY name
    }]
    set res
  }
}













# Argument $file is the name of a zip archive on disk. This function
# executes test cases to check that the results of each of the following 
# are the same:
#
#         SELECT * FROM zipfile($file)
#         SELECT * FROM zipfile( readfile($file) )
................................................................................
do_catchsql_test 4.1 {
  CREATE VIRTUAL TABLE yyy USING zipfile();
} {1 {zipfile constructor requires one argument}}
do_catchsql_test 4.2 {
  CREATE VIRTUAL TABLE yyy USING zipfile('test.zip', 'test.zip');
} {1 {zipfile constructor requires one argument}}













































finish_test








>
>
>
>
>
>
>
>
>
>
>
>







 







>

>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
...
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
      FROM fsdir('test_unzip') 
      WHERE name!='test_unzip'
      ORDER BY name
    }]
    set res
  }
}


# The argument is a blob (not a hex string) containing a zip archive.
# This proc removes the extended timestamp fields from the archive
# and returns the result.
#
proc remove_timestamps {blob} {
  set hex [binary encode hex $blob]
  set hex [string map {55540500 00000500} $hex]
  binary decode hex $hex
}


# Argument $file is the name of a zip archive on disk. This function
# executes test cases to check that the results of each of the following 
# are the same:
#
#         SELECT * FROM zipfile($file)
#         SELECT * FROM zipfile( readfile($file) )
................................................................................
do_catchsql_test 4.1 {
  CREATE VIRTUAL TABLE yyy USING zipfile();
} {1 {zipfile constructor requires one argument}}
do_catchsql_test 4.2 {
  CREATE VIRTUAL TABLE yyy USING zipfile('test.zip', 'test.zip');
} {1 {zipfile constructor requires one argument}}

#--------------------------------------------------------------------------

db func rt remove_timestamps
do_execsql_test 5.0 {
  WITH c(name,mtime,data) AS (
    SELECT 'a.txt', 946684800, 'abc'
  )
  SELECT name,mtime,data FROM zipfile(
    ( SELECT rt( zipfile(name,NULL,mtime,data) ) FROM c )
  )
} {
  a.txt 946684800 abc
}

if {[info vars ::UNZIP]!=""} { 
  load_static_extension db fileio
  forcedelete test.zip
  do_test 6.0 {
    execsql {
      WITH c(name,mtime,data) AS (
        SELECT 'a.txt', 946684800, 'abc' UNION ALL
        SELECT 'b.txt', 1000000000, 'abc' UNION ALL
        SELECT 'c.txt', 1111111000, 'abc'
      )
      SELECT writefile('test.zip',
          ( SELECT rt ( zipfile(name,NULL,mtime,data) ) FROM c )
      );
    }
    forcedelete test_unzip
    file mkdir test_unzip
    exec unzip -d test_unzip test.zip

    db eval {
      SELECT name, mtime FROM fsdir('test_unzip') WHERE name!='test_unzip'
      ORDER BY name
    }
  } [list {*}{
    test_unzip/a.txt 946684800
    test_unzip/b.txt 1000000000 
    test_unzip/c.txt 1111111000 
  }]
}


finish_test

Changes to test/zipfile2.test.

66
67
68
69
70
71
72



73
74
75
76
77
78


79
80
81
82
83
84
85
..
94
95
96
97
98
99
100

101
102
103
104
105
106
107
...
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

138
139
140
141
142
143
144
145
146
...
155
156
157
158
159
160
161

162
163
164
165
  140000080000D4A52BEC09F3B6E0110000001100000005000900000000000000
  0000A48100000000612E747874555405000140420F00504B01021E0314000008
  0000D4A52BECD98916A71100000011000000050009000000000000000000A481
  3D000000622E747874555405000140420F00504B050600000000020002007800
  00007A0000000000
}




do_execsql_test 3.1 {
  WITH contents(name,mtime,data) AS (
    VALUES('a.txt', 1000000, 'contents of a.txt') UNION ALL
    VALUES('b.txt', 1000000, 'contents of b.txt')
  ) SELECT quote( zipfile(name,NULL,mtime,data) ) FROM contents;
} [blobliteral $archive]



set blob [blob $archive]
do_execsql_test 3.2 {
  SELECT name,mtime,data FROM zipfile($blob)
} {
  a.txt 1000000 {contents of a.txt} 
  b.txt 1000000 {contents of b.txt}
................................................................................
  set a [string replace $archive $idx [expr $idx+3] 0000]
  set blob [blob $a]
  do_catchsql_test 3.3.$i {
    SELECT name,mtime,data FROM zipfile($blob)
  } {/1 .*/}
}


set L [findall 5554 $archive]
for {set i 0} {$i < [llength $L]} {incr i} {
  set idx [lindex $L $i]
  set a [string replace $archive $idx [expr $idx+3] 1234]
  set blob [blob $a]
  do_execsql_test 3.4.$i {
    SELECT name,data FROM zipfile($blob)
................................................................................
    SELECT name,data FROM zipfile($blob)
  } {
    a.txt {contents of a.txt} 
    b.txt {contents of b.txt}
  }
}

if 0 {
set blob [db one {
  WITH contents(name,mtime,data) AS (
    VALUES('a.txt', 1000000, 'aaaaaaaaaaaaaaaaaaaaaaa')
  ) SELECT quote( zipfile(name,NULL,mtime,data) ) FROM contents;
}]
set blob [string range $blob 2 end]
set blob [string range $blob 0 end-1]
while {[string length $blob]>0} {
  puts [string range $blob 0 63]
  set blob [string range $blob 64 end]
}

exit
}

set archive2 {
  504B0304140000080800D4A52BEC08F54C6E050000001700000005000900612E
  747874555405000140420F004B4CC40A00504B01021E03140000080800D4A52B
  EC08F54C6E0500000017000000050009000000000000000000A4810000000061
  2E747874555405000140420F00504B050600000000010001003C000000310000
  000000
................................................................................
set L [findall 17000000 $archive2]
set a $archive2
foreach i $L { set a [string replace $a $i [expr $i+7] 16000000] }
set blob [blob $a]
do_catchsql_test 4.1 {
  SELECT name,mtime,data,method FROM zipfile($blob)
} {1 {inflate() failed (0)}}



finish_test








>
>
>
|
|
|
|
|
|
>
>







 







>







 







<
|
|
|
|
|
|
|
|
|
|
<
>
|
<







 







>




66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
..
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
125
126
127
128
129
130
131

132
133
134
135
136
137
138
139
140
141

142
143

144
145
146
147
148
149
150
...
159
160
161
162
163
164
165
166
167
168
169
170
  140000080000D4A52BEC09F3B6E0110000001100000005000900000000000000
  0000A48100000000612E747874555405000140420F00504B01021E0314000008
  0000D4A52BECD98916A71100000011000000050009000000000000000000A481
  3D000000622E747874555405000140420F00504B050600000000020002007800
  00007A0000000000
}

if 0 {
  # This test is broken - the archive generated is slightly different
  # depending on the zlib version used.
  do_execsql_test 3.1 {
    WITH contents(name,mtime,data) AS (
        VALUES('a.txt', 1000000, 'contents of a.txt') UNION ALL
        VALUES('b.txt', 1000000, 'contents of b.txt')
    ) SELECT quote( zipfile(name,NULL,mtime,data) ) FROM contents;
  } [blobliteral $archive]
}


set blob [blob $archive]
do_execsql_test 3.2 {
  SELECT name,mtime,data FROM zipfile($blob)
} {
  a.txt 1000000 {contents of a.txt} 
  b.txt 1000000 {contents of b.txt}
................................................................................
  set a [string replace $archive $idx [expr $idx+3] 0000]
  set blob [blob $a]
  do_catchsql_test 3.3.$i {
    SELECT name,mtime,data FROM zipfile($blob)
  } {/1 .*/}
}

# Change the "extra info id" for all extended-timestamp fields.
set L [findall 5554 $archive]
for {set i 0} {$i < [llength $L]} {incr i} {
  set idx [lindex $L $i]
  set a [string replace $archive $idx [expr $idx+3] 1234]
  set blob [blob $a]
  do_execsql_test 3.4.$i {
    SELECT name,data FROM zipfile($blob)
................................................................................
    SELECT name,data FROM zipfile($blob)
  } {
    a.txt {contents of a.txt} 
    b.txt {contents of b.txt}
  }
}


# set blob [db one {
#   WITH contents(name,mtime,data) AS (
#     VALUES('a.txt', 1000000, 'aaaaaaaaaaaaaaaaaaaaaaa')
#   ) SELECT quote( zipfile(name,NULL,mtime,data) ) FROM contents;
# }]
# set blob [string range $blob 2 end]
# set blob [string range $blob 0 end-1]
# while {[string length $blob]>0} {
#   puts [string range $blob 0 63]
#   set blob [string range $blob 64 end]

# }
# exit


set archive2 {
  504B0304140000080800D4A52BEC08F54C6E050000001700000005000900612E
  747874555405000140420F004B4CC40A00504B01021E03140000080800D4A52B
  EC08F54C6E0500000017000000050009000000000000000000A4810000000061
  2E747874555405000140420F00504B050600000000010001003C000000310000
  000000
................................................................................
set L [findall 17000000 $archive2]
set a $archive2
foreach i $L { set a [string replace $a $i [expr $i+7] 16000000] }
set blob [blob $a]
do_catchsql_test 4.1 {
  SELECT name,mtime,data,method FROM zipfile($blob)
} {1 {inflate() failed (0)}}



finish_test