Hacker News Reader

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.

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

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

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.

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.

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.

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.

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?

> 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.

Already there. E.g. https://github.com/guardianproject/libsqlfs but I'd also count something like s3ql where the data is stored as s3 objects.

> 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.

That's mentioned both in the article (3.2) and GP ("solid compression" is the term of art for a single compression stream for the entire archive).

> 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.)

> 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/

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.

I think I made it clear that ZIP encryption is "very bad" :-)

Totally fair! I think I'm mostly pushing back on the idea that archive formats with built-in encryption are a good idea.

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.

To be fair, they said that ZIP had very bad encryption.

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

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.

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.

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.