SQLite Archive Files (2018)
sqlite.org
sqlite.org
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.
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 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.
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.
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 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.
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?
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.
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).
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.)
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.
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.
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.
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.
I think that’s still pretty impressive.
In particular, development repositories with many many small source files often have horrendously slow copying/deleting behaviour (particularly on windows) even on fast disks. I wonder if sqlite archive files would be a better way to store them.
Fortunately it have an exclusion list, so the list can add any file, folder, file type and process.
FWIW, at the bottom of the linked page it says that you can skip the sqlar_compress/uncompress functions and just roll your own:
> The code above is for the general case. For the special case of an SQLite Archive that only stores uncompressed or uncompressible content (this might come up, for example, in an SQLite Archive that stores only JPEG, GIF, and/or PNG images) then the content can be inserted into and extracted from the database without using the sqlar_compress() and sqlar_uncompress() functions, and the sqlar.c extension is not required.
For example, you be using this as an archive format for a game, and you may want to store the texture data using a different compression method than the ai scripts or the audio data. At that point, you definitely need another piece of data to clue you in. A tag field is useful then, though I've also seen filename/file extension-based methods as well.
(It's interesting that applications against censorship are brought up. Difficulty of implementation has consequences here, too. In order to effectively use SQLite as an archive format, the receiving end will need the SQLite software. By comparison, it's pretty trivial to craft a polyglot file that is both plain text and HTML and is self-extracting and assumes no software on the other end except to rely on the ubiquity of commodity web browsers. Always bet on text.)
<script>document.write("<textarea>...</textarea>");</script>
Disclaimer: I made it and it's not production ready
See my comment https://news.ycombinator.com/item?id=28670543
And to be honest, if you need to search through or index JSON blobs in a database you need to reconsider your design.
As a quick-and-dirty logging solution it makes quite a lot of sense. You can add whatever fields you want to your structured logs, and if you find you need to search on a particular field, just add an index for that field.
That p much voids all the benefit of using SQLite.
>And to be honest, if you need to search through or index JSON blobs in a database you need to reconsider your design.
This is absolutely intentional the log contains information to manually re-play certain events in case of an fatal error mostly for debug purpose or performance benchmarks.
This means 99.99% of the data will never be needed and eventually will be discarded.
But when I need to re-play data I need to be able to query for exactly the data I need. Each JSON "blob" (which is actually store as TEXT in SQLite) has a few additional fields to quickly filter off 95% or more of unwanted data. The rest SQLite simply reads completely to separate wanted and unwanted entries further.
With a DB size around 100GB I get the data I need by reading 5GB or less. That's perfectly fine for my use case even if I would let the DB grow quite a lot larger it would be just fine.
But there is a gzip “variant” that we use in genomics called bgzip that supports this. It’s basically a chunked gzip file (multiple gzip records concatenated together) with an extra flag (in the gzip header) for the uncompressed length of each chunk. Using this information, you can do random IO on the compressed file while only uncompressing the chunks necessary. I’m sure other compression formats have the same support.
To individual blobs? Seems unlikely.
Posgres automatically compress large blobs, it's part of the TOAST system:
> The TOAST management code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than TOAST_TUPLE_TARGET bytes (also normally 2 kB, adjustable) or no more gains can be had.
So if a TOAST-able value is larger than TOAST_TUPLE_THRESHOLD it'll get compressed (using pglz by default, from 14.0 LZ4 will finally be supported), and if it's still above TOAST_TUPLE_TARGET it'll get stored out-of-line (otherwise it gets stored inline).
The blobs in the sqlite archive are compressed, but one of the critiques was that the compression was better when you used multiple files to build the compression dictionary (zip vs tarbal).
And now I realize that I probably replied to the wrong comment...
I'd also like (nullable) mimetype column. Can be handy for example to store encoding for text files.
On the other side, come people call for file permissions... I'm not a fan, too system-dependent.
I question the need for multiple compression types though, the difference between the default and the best won't be great enough to be worth the hassle. Especially if it leaves you open to a patent troll.
Maybe I am wrong. I didn't know this tool. My brief review of the documentation leads me to believe that it has an obvious problem.
>The Y parameter is the compressed content (the output from a prior call to sqlar_compress()) and SZ is the original uncompressed size of the input X that generated Y. If SZ is less than or equal to the size of Y, that indicates that no compression occurred, and so sqlar_uncompress(Y,SZ) returns a copy of Y.
The format stores both the possibly compressed blob and the original size. From those two pieces of information it can always return the correct original file.
This is pretty common for compression tools. If the input is incompressible (or not compressible by X%), then the original data is stored. In this case, the code is checking the stored size against the uncompressed size. If they are equal, then uncompress is a noop.
My take away is that there is a zlib compression function built into SQLite. Which can be pretty handy.
Another benefit I can see is that because the SQLite database has a flexible schema, you could add new features to the archive while maintaining backwards compatibility. For example, if you wanted to add a SHA1 hash to each record, you should be able to, while still allowing older tools to read the updated file.