5 ms·

I'm generally supportive of SQLite's NIH syndrome---which is normally bad, but it can work if the trade-off is well researched and the resulting product is of h

I'm generally supportive of SQLite's NIH syndrome---which is normally bad, but it can work if the trade-off is well researched and the resulting product is of high quality---but this one is not. Specifically sqlar is a worse replacement of ZIP. It lacks pretty much every feature of modern compressed archive formats: filesystem and custom metadata besides from simple st_mode, solid compression, metadata compression, encryption and integrity check and so on. Therefore it can only be legitimately compared with ZIP, which does support custom metadata, very bad encryption and partial integrity check (via zlib) and only lacks the guaranteed encoding for file names. Even ignoring other formats it is not without a problem: for example the compression mode (DEFLATE vs. uncompressed) is implicitly indicated by `sz = length(data)` and I don't think it is a good idea. If I were designing sqlar and didn't want to spare an additional field I would have instead set sz to something negative so that it never collides with the compressed case (of course, if I had a chance I would just add a separate field instead). Pretty disappointing given other tools from the SQLite ecosystem.

m_eiman 5y ago
Implicitly indicated compression is also a "feature" of LZJB which we've used for firmware update files. Figuring out what was going on and fixing the issue when a bootloader suddenly started rejecting new firmware updates was interesting and annoying. The updates were processed in chunks, and rarely a chunk doesn't compress at all - which means that it took a long time before it happened the first time. Always fun to find issues with code that has "always" worked and suddenly doesn't any longer. https://en.wikipedia.org/wiki/LZJB https://en.wikipedia.org/wiki/LZJB
OskarS 5y ago
To add to that list: when you do a .tar.gz, the compression algorithm works across file boundaries (very useful when you have a lot of small files), but neither sqlar nor .zip does this. I think the idea of using SQLite as an application file format is excellent, and it's great that it can serve as a general store of files. As a general purpose compressed archive format, however, I agree it leaves a lot to be desired.
- lifthrasiir 5y ago
  Yeah, I even think a full file system based on SQLite format is possible; pretty much all ingredients are there, just waiting to be cooked. But it should have been cooked in the first place.
  - brandmeyer 5y ago
    I don't think so. An important missing element in SQLite's format is larger block support. Modern filesystems do this via extents. SQLite has a strictly fixed block size and no support for forming larger blocks from contiguous runs of basic blocks. Another missing element is that SQLite's support for large blobs is via linked lists of pages. That's fine for sequential access, but unacceptable for random access. For a database to be usable as a filesystem backend, it must at least be composable with itself without sacrificing performance. Lets say you built a kernel module or fuse module using sqlite as the backend. How well would sqlite itself perform on a file in this filesystem?
    - lifthrasiir 5y ago
      > Another missing element is that SQLite's support for large blobs is via linked lists of pages. This surprised me and you were correct, there is no guarantee that related payload overflow pages are consecutively ordered. I thought extents can be implemented with those pages but it would be pretty bad for random access as is. In fact one of the reasons I thought SQLite can function as a file system was a built-in support of random-accessible BLOBs, and some more research revealed that the support was based on the fact that SQLite loads the entire BLOB into memory. Oops.
  - tobias3 5y ago
    Already there. E.g. https://github.com/guardianproject/libsqlfs https://github.com/guardianproject/libsqlfs but I'd also count something like s3ql where the data is stored as s3 objects.
- conradev 5y ago
  That is a trade off in that you can no longer randomly access a single file without decompressing the entire stream. That is often desired for file archives. For use cases like genomics, though, reads from random offsets are common, hence the bgzf format used by BAM files which is a sequence of gzip chunks and an index. Lower compression ratio, but faster seeking.
mbreese 5y ago
But given that we are talking about using a SQLite database as a container, these features could in theory be added in the future while maintaining the ability for older versions to still be able to read the newer files. I think that’s still pretty impressive.
chousuke 5y ago
To me, this feature looks more like a convenience wrapper than anything else. Maybe it actually was useful for something and got implemented for that reason. You pretty much get everything it needs "for free" with the standard SQLite machinery. I can't imagine the code implementing this is a large burden at all.
bityard 5y ago
I don't think anybody is suggesting that SQLite archives are a suitable replacement for ZIP or tar + compressor of choice general-purpose compression duties. The docs for this seem to suggest that this extension is most useful when your application already heavily depends on SQLite and you need a place to store some files but don't want to introduce further OS-specific dependencies into your application. This is a low-effort solution to a rather specific problem. Which is why it's not part of SQLite proper. The extension itself is 124 lines of C including comments for heaven's sake.
- lifthrasiir 5y ago
  The extension only deals with the compression part. The actual operation happens in the shell which is intermingled with general archive support. And this suggests the most important fact: SQLite already supports an ordinary ZIP file via the zipfile virtual table [1]. Both zipfile and sqlar extensions are enabled when compiled with zlib, and it is not even like that sqlar came first (they are added roughly together AFAIK). Therefore if you have SQLite and want to have a cheap file archive support, you can just use zip instead of the inferior sqlar. [1] https://sqlite.org/zipfile.html https://sqlite.org/zipfile.html
slaymaker1907 5y ago
You forgot the biggest limitation of sqlar which is lack of support for large files (max is 4GB IIRC). I think this is really unfortunate since a sqlite based archive file format could be really neat and easily support a lot of the features you mention. Additionally, I think it is really cool to have the ability to inspect the file format using SQL. If sqlar were actually more usable, it would also be really neat for application formats since you could have additional metadata for your application in addition to the archive. Instead of having some magic MANIFEST file for Java jar files, that data could be stored in a separate table with its own indices.
tptacek 5y ago
ZIP encryption is an anti-feature, and you should probably never, ever use it. The problem is that the ZIP ecosystem is full of implementations of horrible insecure cipher constructions (even the modern implementations may support them for backwards compatibility). "Horrible" here means "breakable with a Perl script". We relied on ZIP encryption at a previous firm, because no client of ours could ever get PGP to work. But we had to train everyone to use 7z, and getting files back from clients was fraught (they might just click the "encrypt" button in whichever random ZIP program they used). If you want to encrypt an archive file, you should do it with a dedicated encryption program that you trust (Filippo Valsorda's `age` is a good bet, since it has good implementations in both Go and Rust, so it's very easy to integrate into designs, and also has a very clean, simple command line interface). You should not lean on ZIP's supposed AES support. The rest of this critique of `sqlar` might be valid! But I can't let the encryption thing pass unchallenged.
- lifthrasiir 5y ago
  I think I made it clear that ZIP encryption is "very bad" :-)
  - tptacek 5y ago
    Totally fair! I think I'm mostly pushing back on the idea that archive formats with built-in encryption are a good idea.
    - lifthrasiir 5y ago
      That's reasonable. Given that compression softwares are likely what typical people would use for encryption though, I'd like to see an actually good encrypted format design in a commonly used archival format. I'm even okay and more than welcome if that'd be just `age` plus some carefully selected headers.
- nextaccountic 5y ago
  To be fair, they said that ZIP had very bad encryption.
zzo38computer 5y ago
> Specifically sqlar is a worse replacement of ZIP. I mostly agree. However, it does have some advantages too, as mentioned in the linked article. (An alternative way to query archives by SQL would be to implement a virtual table; in this case it can work with any archive format. In the case of ZIP format, it already does this.) > filesystem and custom metadata besides from simple st_mode Since it is a SQLite database, you can add additional tables etc, so it isn't a problem. > solid compression, metadata compression I think that using separate archiving and compression is good, which would do these things. A separate program should be used for archive and for compression. However, in some circumstances, solid compression is undesirable (as mentioned before in HN). In some formats, solid compression is optional, but you might want an index, and there will need to be some way to find parts of the file by index. This is why in my own experimental concatenatable compression format, you can add an optional key frame index. > encryption Also encryption should be a separate program from archive and compression too, I should think. (However, there are extensions for SQLite for doing encryption, so it is possible to use those if wanted.) > the compression mode (DEFLATE vs. uncompressed) is implicitly indicated by `sz = length(data)` and I don't think it is a good idea I agree; it doesn't look like good to me either. (Although, the Free Hero Mesh code page file does something similar, but in that case the uncompressed sizes of lumps is always exactly 2048 bytes anyways, so there is no need to store a separate uncompressed size.)
- lifthrasiir 5y ago
  > Since it is a SQLite database, you can add additional tables etc, so it isn't a problem. You can add additional tables, but you can't add support for additional tables in the past versions. This is especially true because sqlar has no version specified. > I think that using separate archiving and compression is good, which would do these things. A separate program should be used for archive and for compression. I've heard this argument a lot and I think it is completely false. Archival and compression should go together, otherwise you would get suboptimal results. Archival then compression (i.e. the traditional tarballs) will disable random access to individual files and metadata. This hurts performance in pretty every use case except for the one-shot decompression. In particular this problem completely prohibits using archival then compression in container formats. You can reenable them by using random-accessible compression formats, but they are not common and less efficient than formats without that constraint. Compression then archival is much better than archival then compression because it essentially mimics the combined compression-archival format (I have used .bz2.tar in the past), but this mode is unpopular so you need to manually decompress them. And it is harder to use different compression algorithms for different formats; you should put that info to the file name or the metadata, and that would be no different from compression-archival formats. Some claim that the needs for solid compression demonstrate the problem of combined compression-archival formats. This is not true and even misleading. Archival then compression commonly results in worse compression than solid compressed archives even with the same algorithm. The gist of solid compression is a shared context for related files, so unless the archiver somehow reorders files it can't compete with combined archivers with solid compression. And if your archiver is doing that reordering, it is no longer decoupled from the compressor so it would be better to combine them. In addition to this, there are also alternatives to solid compression with different trade-offs, for example learned shared preset dictionaries (even zlib supports this, although unpopular). > Also encryption should be a separate program from archive and compression too, I should think. Yeah, I'm personally fine without encryption as it is really hard to get it right (for example, you should be prepared of metadata encryption and key stretching). I've listed it partly for the completeness and partly for the fact that it is probably the most approachable encryption software to end users. > (However, there are extensions for SQLite for doing encryption, so it is possible to use those if wanted.) And I think the official encryption extension [1] is proprietary. [1] https://www.sqlite.org/see/ https://www.sqlite.org/see/
banana_giraffe 5y ago
As I understand things, sqlar is more of a thought experiment or proof of concept type thing, not a "we've got a better solution than zip files" https://youtu.be/8y_ABXwYtuc?t=2135 https://youtu.be/8y_ABXwYtuc?t=2135 It's interesting, and maybe has some use cases, but not a generic solution. I've certainly used a hand-rolled version of this idea before to handle specific scenarios where it made sense.

m_eiman 5y ago

Implicitly indicated compression is also a "feature" of LZJB which we've used for firmware update files. Figuring out what was going on and fixing the issue when a bootloader suddenly started rejecting new firmware updates was interesting and annoying. The updates were processed in chunks, and rarely a chunk doesn't compress at all - which means that it took a long time before it happened the first time. Always fun to find issues with code that has "always" worked and suddenly doesn't any longer. https://en.wikipedia.org/wiki/LZJB https://en.wikipedia.org/wiki/LZJB

OskarS 5y ago

To add to that list: when you do a .tar.gz, the compression algorithm works across file boundaries (very useful when you have a lot of small files), but neither sqlar nor .zip does this. I think the idea of using SQLite as an application file format is excellent, and it's great that it can serve as a general store of files. As a general purpose compressed archive format, however, I agree it leaves a lot to be desired.

lifthrasiir 5y ago
Yeah, I even think a full file system based on SQLite format is possible; pretty much all ingredients are there, just waiting to be cooked. But it should have been cooked in the first place.
- brandmeyer 5y ago
  I don't think so. An important missing element in SQLite's format is larger block support. Modern filesystems do this via extents. SQLite has a strictly fixed block size and no support for forming larger blocks from contiguous runs of basic blocks. Another missing element is that SQLite's support for large blobs is via linked lists of pages. That's fine for sequential access, but unacceptable for random access. For a database to be usable as a filesystem backend, it must at least be composable with itself without sacrificing performance. Lets say you built a kernel module or fuse module using sqlite as the backend. How well would sqlite itself perform on a file in this filesystem?
  - lifthrasiir 5y ago
    > Another missing element is that SQLite's support for large blobs is via linked lists of pages. This surprised me and you were correct, there is no guarantee that related payload overflow pages are consecutively ordered. I thought extents can be implemented with those pages but it would be pretty bad for random access as is. In fact one of the reasons I thought SQLite can function as a file system was a built-in support of random-accessible BLOBs, and some more research revealed that the support was based on the fact that SQLite loads the entire BLOB into memory. Oops.
- tobias3 5y ago
  Already there. E.g. https://github.com/guardianproject/libsqlfs https://github.com/guardianproject/libsqlfs but I'd also count something like s3ql where the data is stored as s3 objects.
conradev 5y ago
That is a trade off in that you can no longer randomly access a single file without decompressing the entire stream. That is often desired for file archives. For use cases like genomics, though, reads from random offsets are common, hence the bgzf format used by BAM files which is a sequence of gzip chunks and an index. Lower compression ratio, but faster seeking.

mbreese 5y ago

But given that we are talking about using a SQLite database as a container, these features could in theory be added in the future while maintaining the ability for older versions to still be able to read the newer files. I think that’s still pretty impressive.

chousuke 5y ago

To me, this feature looks more like a convenience wrapper than anything else. Maybe it actually was useful for something and got implemented for that reason. You pretty much get everything it needs "for free" with the standard SQLite machinery. I can't imagine the code implementing this is a large burden at all.

bityard 5y ago

I don't think anybody is suggesting that SQLite archives are a suitable replacement for ZIP or tar + compressor of choice general-purpose compression duties. The docs for this seem to suggest that this extension is most useful when your application already heavily depends on SQLite and you need a place to store some files but don't want to introduce further OS-specific dependencies into your application. This is a low-effort solution to a rather specific problem. Which is why it's not part of SQLite proper. The extension itself is 124 lines of C including comments for heaven's sake.

lifthrasiir 5y ago
The extension only deals with the compression part. The actual operation happens in the shell which is intermingled with general archive support. And this suggests the most important fact: SQLite already supports an ordinary ZIP file via the zipfile virtual table [1]. Both zipfile and sqlar extensions are enabled when compiled with zlib, and it is not even like that sqlar came first (they are added roughly together AFAIK). Therefore if you have SQLite and want to have a cheap file archive support, you can just use zip instead of the inferior sqlar. [1] https://sqlite.org/zipfile.html https://sqlite.org/zipfile.html

slaymaker1907 5y ago

You forgot the biggest limitation of sqlar which is lack of support for large files (max is 4GB IIRC). I think this is really unfortunate since a sqlite based archive file format could be really neat and easily support a lot of the features you mention. Additionally, I think it is really cool to have the ability to inspect the file format using SQL. If sqlar were actually more usable, it would also be really neat for application formats since you could have additional metadata for your application in addition to the archive. Instead of having some magic MANIFEST file for Java jar files, that data could be stored in a separate table with its own indices.

tptacek 5y ago

ZIP encryption is an anti-feature, and you should probably never, ever use it. The problem is that the ZIP ecosystem is full of implementations of horrible insecure cipher constructions (even the modern implementations may support them for backwards compatibility). "Horrible" here means "breakable with a Perl script". We relied on ZIP encryption at a previous firm, because no client of ours could ever get PGP to work. But we had to train everyone to use 7z, and getting files back from clients was fraught (they might just click the "encrypt" button in whichever random ZIP program they used). If you want to encrypt an archive file, you should do it with a dedicated encryption program that you trust (Filippo Valsorda's `age` is a good bet, since it has good implementations in both Go and Rust, so it's very easy to integrate into designs, and also has a very clean, simple command line interface). You should not lean on ZIP's supposed AES support. The rest of this critique of `sqlar` might be valid! But I can't let the encryption thing pass unchallenged.

lifthrasiir 5y ago
I think I made it clear that ZIP encryption is "very bad" :-)
- tptacek 5y ago
  Totally fair! I think I'm mostly pushing back on the idea that archive formats with built-in encryption are a good idea.
  - lifthrasiir 5y ago
    That's reasonable. Given that compression softwares are likely what typical people would use for encryption though, I'd like to see an actually good encrypted format design in a commonly used archival format. I'm even okay and more than welcome if that'd be just `age` plus some carefully selected headers.
nextaccountic 5y ago
To be fair, they said that ZIP had very bad encryption.

zzo38computer 5y ago

> Specifically sqlar is a worse replacement of ZIP. I mostly agree. However, it does have some advantages too, as mentioned in the linked article. (An alternative way to query archives by SQL would be to implement a virtual table; in this case it can work with any archive format. In the case of ZIP format, it already does this.) > filesystem and custom metadata besides from simple st_mode Since it is a SQLite database, you can add additional tables etc, so it isn't a problem. > solid compression, metadata compression I think that using separate archiving and compression is good, which would do these things. A separate program should be used for archive and for compression. However, in some circumstances, solid compression is undesirable (as mentioned before in HN). In some formats, solid compression is optional, but you might want an index, and there will need to be some way to find parts of the file by index. This is why in my own experimental concatenatable compression format, you can add an optional key frame index. > encryption Also encryption should be a separate program from archive and compression too, I should think. (However, there are extensions for SQLite for doing encryption, so it is possible to use those if wanted.) > the compression mode (DEFLATE vs. uncompressed) is implicitly indicated by `sz = length(data)` and I don't think it is a good idea I agree; it doesn't look like good to me either. (Although, the Free Hero Mesh code page file does something similar, but in that case the uncompressed sizes of lumps is always exactly 2048 bytes anyways, so there is no need to store a separate uncompressed size.)

lifthrasiir 5y ago
> Since it is a SQLite database, you can add additional tables etc, so it isn't a problem. You can add additional tables, but you can't add support for additional tables in the past versions. This is especially true because sqlar has no version specified. > I think that using separate archiving and compression is good, which would do these things. A separate program should be used for archive and for compression. I've heard this argument a lot and I think it is completely false. Archival and compression should go together, otherwise you would get suboptimal results. Archival then compression (i.e. the traditional tarballs) will disable random access to individual files and metadata. This hurts performance in pretty every use case except for the one-shot decompression. In particular this problem completely prohibits using archival then compression in container formats. You can reenable them by using random-accessible compression formats, but they are not common and less efficient than formats without that constraint. Compression then archival is much better than archival then compression because it essentially mimics the combined compression-archival format (I have used .bz2.tar in the past), but this mode is unpopular so you need to manually decompress them. And it is harder to use different compression algorithms for different formats; you should put that info to the file name or the metadata, and that would be no different from compression-archival formats. Some claim that the needs for solid compression demonstrate the problem of combined compression-archival formats. This is not true and even misleading. Archival then compression commonly results in worse compression than solid compressed archives even with the same algorithm. The gist of solid compression is a shared context for related files, so unless the archiver somehow reorders files it can't compete with combined archivers with solid compression. And if your archiver is doing that reordering, it is no longer decoupled from the compressor so it would be better to combine them. In addition to this, there are also alternatives to solid compression with different trade-offs, for example learned shared preset dictionaries (even zlib supports this, although unpopular). > Also encryption should be a separate program from archive and compression too, I should think. Yeah, I'm personally fine without encryption as it is really hard to get it right (for example, you should be prepared of metadata encryption and key stretching). I've listed it partly for the completeness and partly for the fact that it is probably the most approachable encryption software to end users. > (However, there are extensions for SQLite for doing encryption, so it is possible to use those if wanted.) And I think the official encryption extension [1] is proprietary. [1] https://www.sqlite.org/see/ https://www.sqlite.org/see/

banana_giraffe 5y ago

As I understand things, sqlar is more of a thought experiment or proof of concept type thing, not a "we've got a better solution than zip files" https://youtu.be/8y_ABXwYtuc?t=2135 https://youtu.be/8y_ABXwYtuc?t=2135 It's interesting, and maybe has some use cases, but not a generic solution. I've certainly used a hand-rolled version of this idea before to handle specific scenarios where it made sense.