12 ms·

SQLite Archive Files (2018)

166 points by alfonsodev 5y ago

reacweb 5y ago
When I read "If the input X is incompressible, then a copy of X is returned", I worry that this is broken. If I archive a file, then extract it from the archive, I can not be sure to obtain the same file. If the file is already compressed at the beginning, it will be decompressed at the end. 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.
- kybernetikos 5y ago
  I think your concern is dealt with in the immediately following section: >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.
  - reacweb 5y ago
    Ok, I was wrong. My review was too brief.
- mbreese 5y ago
  > 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. 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.
  - formerly_proven 5y ago
    It's still a really annoying design, one should really explicitly communicate if and what compression method was used for a given blob of data. Here another DB column would have been a very easy way.
  - lifthrasiir 5y ago
    Note that this kind of fallback is also prevalent in compression stream formats including zlib which sqlar uses, so the archive format doesn't need to reimplement the same fallback. The only reason it might be useful is the opportunistic support for random access for uncompressed data.
kybernetikos 5y ago
Given how crazy the zip file format is, and the claim that sqlite is faster than the filesystem for small files (https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html) this seems pretty reasonable to me. 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.
- proto-n 5y ago
  I wonder if the particular slowness on windows could be related to how closing file handles is slow on windows because of blocking antivirus checks. Alas I can't recall where I read about this, it was some kind of "a few things I learned over many years of programming" blogpost.
  - marcodiego 5y ago
    I saw windows take many seconds to compile a hello world. Compile it for the arduino took even longer, probably because it needed to generate more files.
  - noxer 5y ago
    That's why you turn AVs off either completely or at least exclude your own code folder. It wont ever do anything useful and worse even I had it delete/quarantine my own binaries in the past because it somehow got triggered. Also by default it happily uploads all you debug exe files to MS and executes them in their sandbox environment. Absolutely unwanted behavior if you ask me.
  - Isthatablackgsd 5y ago
    This is true. Even moving folders from one drive to other drive will trigger it. I download pictures and videos weekly (average 100 images a week). Often my browsers will hang when it trying to save the file to the folder because it was waiting for Windows Security to finish their scanning. Windows are proactive (sometime too much) with their securities because of the history of how we handle their securities updates and many many (thousands of many) unpatched Windows OS out there infecting with malware and randomware. Fortunately it have an exclusion list, so the list can add any file, folder, file type and process.
parhamn 5y ago
Very cool idea! I'm a bit torn whether the format should concern itself with compression. Seems like a useful general blob container strategy, might be prudent to leave compression to the consumer?
- genocidicbunny 5y ago
  I've hand-rolled something very similar to this, and I just used an extra 'tag' column that stored some extra info, often the compression type. 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.
  - kybernetikos 5y ago
    That seems like a nice effect of storing the uncompressed size along with the blob, and using that to control decompression. You can always choose to store uncompressed blobs, and the size will always be the same so sqlite will not attempt to decompress the blob. It's not quite the same as rolling your own compression, but it's pretty easy to see how the format could be extended to support that.
    - genocidicbunny 5y ago
      Using the size to trigger compression vs no-op is clever, but doesn't help when you have different compression methods that you want to use. 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.
m_ke 5y ago
Would love something like that for storing large image datasets for computer vision. Storing embeddings, predictions and metadata in a contiguous format with compression support, ANN indexing support and SQL would be amazing.
- noxer 5y ago
  There is an SQLite compression and encryption "add-on" which seems to do what you want. Check the official website. Hint: You have to buy a license to use it legally.
  - deleted 5y ago
    [deleted]
lifthrasiir 5y ago
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.
  - 5y ago
noxer 5y ago
I wish they would build compression directly into SQLite. I use SQLite as a log store mostly dumping JSON data in it. Due to the lack of compression the DB is probably 10 times the size it could be.
- Cthulhu_ 5y ago
  You could probably pass the data through a gzip filter before storage if you don't need the data to be indexable while in the database. I know, that's an extra step, but in most programming languages it's fairly straightforward. And to be honest, if you need to search through or index JSON blobs in a database you need to reconsider your design.
  - nicoburns 5y ago
    > 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.
  - mbreese 5y ago
    I would imagine SQLite would do a lot of random access IO, which would make this difficult even without indexing. 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.
    - masklinn 5y ago
      > I would imagine SQLite would do a lot of random access IO, which would make this difficult even without indexing. 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).
cxr 5y ago
For an honest assessment, difficulty of implementation—and, accordingly, lack of diversity in implementations—should be be listed in the "Disadvantages" section. (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.)
- bastawhiz 5y ago
  Sorry, how exactly do you create a "self extracting" archive file with only text and HTML?
  - deleted 5y ago
    [deleted]
  - deleted 5y ago
    [deleted]
  - a1369209993 5y ago
    Something like: <script>document.write("<textarea>...</textarea>");</script> presumably? (I'd assume they meant self-extracting when run as a shell script or something, but they did say "to rely on the ubiquity of commodity web browsers".)
  - cxr 5y ago
    Sounds like a trick question, but Netscape's latest beta from September includes scripting support. Rumor has it that they're going to rename it to JavaScript later this year since Sun is pushing for strategic alignment with their Java platform. (You can use it to script e.g. applets). But you can embed the decompression code directly in the file, writing it in the scripting language itself—no compiled bytecode (or even a Java plugin) is necessary.
edwintorok 5y ago
Would be interesting if this was expanded to support more compression formats. E.g. zstd. Gzip is quite an old format and zstd is a lot quicker to decompress.
- chrisseaton 5y ago
  I don't think it uses the gzip format does it? It just uses deflate.
  - lifthrasiir 5y ago
    It's actually the zlib format. The linked post says DEFLATE, but the linked source code correctly mentions zlib instead.
  - masklinn 5y ago
    It doesn't use the gzip file format, but it uses the gzip compression format (which is just DEFLATE).
- rini17 5y ago
  Yes I did double check there's no column to indicate compressed format, no idea how they want to extend it in future. Maybe just autodetect from the contents? However that could fail if you want to store already deflated files. 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.
  - mark-r 5y ago
    Since compression can produce any bit combination with equal probability, the only way to reliably auto-detect from the contents is to put some kind of fixed flag at the front. At that point you get more flexibility by making it a new column in the table instead. It doesn't seem like it would be hard to extend this with a compression type and mime type if you need those. 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.
ComputerGuru 5y ago
I think the title could use a (2018) appended to it, just so no one thinks this is a new thing that will be pushed on them or something.
- dang 5y ago
  Ok, added. Thanks!
say_it_as_it_is 5y ago
S3Lite?
PostThisTooFast 5y ago
I don't see why this is better than simply implementing a table like this yourself.

166 points by alfonsodev 5y ago

When I read "If the input X is incompressible, then a copy of X is returned", I worry that this is broken. If I archive a file, then extract it from the archive, I can not be sure to obtain the same file. If the file is already compressed at the beginning, it will be decompressed at the end. 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.

kybernetikos 5y ago
I think your concern is dealt with in the immediately following section: >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.
- reacweb 5y ago
  Ok, I was wrong. My review was too brief.
mbreese 5y ago
> 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. 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.
- formerly_proven 5y ago
  It's still a really annoying design, one should really explicitly communicate if and what compression method was used for a given blob of data. Here another DB column would have been a very easy way.
- lifthrasiir 5y ago
  Note that this kind of fallback is also prevalent in compression stream formats including zlib which sqlar uses, so the archive format doesn't need to reimplement the same fallback. The only reason it might be useful is the opportunistic support for random access for uncompressed data.

kybernetikos 5y ago

Given how crazy the zip file format is, and the claim that sqlite is faster than the filesystem for small files (https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html) this seems pretty reasonable to me. 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.

proto-n 5y ago
I wonder if the particular slowness on windows could be related to how closing file handles is slow on windows because of blocking antivirus checks. Alas I can't recall where I read about this, it was some kind of "a few things I learned over many years of programming" blogpost.
- marcodiego 5y ago
  I saw windows take many seconds to compile a hello world. Compile it for the arduino took even longer, probably because it needed to generate more files.
- noxer 5y ago
  That's why you turn AVs off either completely or at least exclude your own code folder. It wont ever do anything useful and worse even I had it delete/quarantine my own binaries in the past because it somehow got triggered. Also by default it happily uploads all you debug exe files to MS and executes them in their sandbox environment. Absolutely unwanted behavior if you ask me.
- Isthatablackgsd 5y ago
  This is true. Even moving folders from one drive to other drive will trigger it. I download pictures and videos weekly (average 100 images a week). Often my browsers will hang when it trying to save the file to the folder because it was waiting for Windows Security to finish their scanning. Windows are proactive (sometime too much) with their securities because of the history of how we handle their securities updates and many many (thousands of many) unpatched Windows OS out there infecting with malware and randomware. Fortunately it have an exclusion list, so the list can add any file, folder, file type and process.

parhamn 5y ago

Very cool idea! I'm a bit torn whether the format should concern itself with compression. Seems like a useful general blob container strategy, might be prudent to leave compression to the consumer?

genocidicbunny 5y ago
I've hand-rolled something very similar to this, and I just used an extra 'tag' column that stored some extra info, often the compression type. 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.
- kybernetikos 5y ago
  That seems like a nice effect of storing the uncompressed size along with the blob, and using that to control decompression. You can always choose to store uncompressed blobs, and the size will always be the same so sqlite will not attempt to decompress the blob. It's not quite the same as rolling your own compression, but it's pretty easy to see how the format could be extended to support that.
  - genocidicbunny 5y ago
    Using the size to trigger compression vs no-op is clever, but doesn't help when you have different compression methods that you want to use. 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.

m_ke 5y ago

Would love something like that for storing large image datasets for computer vision. Storing embeddings, predictions and metadata in a contiguous format with compression support, ANN indexing support and SQL would be amazing.

noxer 5y ago
There is an SQLite compression and encryption "add-on" which seems to do what you want. Check the official website. Hint: You have to buy a license to use it legally.
- deleted 5y ago
  [deleted]

lifthrasiir 5y ago

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.
- 5y ago

noxer 5y ago

I wish they would build compression directly into SQLite. I use SQLite as a log store mostly dumping JSON data in it. Due to the lack of compression the DB is probably 10 times the size it could be.

Cthulhu_ 5y ago
You could probably pass the data through a gzip filter before storage if you don't need the data to be indexable while in the database. I know, that's an extra step, but in most programming languages it's fairly straightforward. And to be honest, if you need to search through or index JSON blobs in a database you need to reconsider your design.
- nicoburns 5y ago
  > 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.
- mbreese 5y ago
  I would imagine SQLite would do a lot of random access IO, which would make this difficult even without indexing. 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.
  - masklinn 5y ago
    > I would imagine SQLite would do a lot of random access IO, which would make this difficult even without indexing. 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).

cxr 5y ago

For an honest assessment, difficulty of implementation—and, accordingly, lack of diversity in implementations—should be be listed in the "Disadvantages" section. (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.)

bastawhiz 5y ago
Sorry, how exactly do you create a "self extracting" archive file with only text and HTML?
- deleted 5y ago
  [deleted]
- deleted 5y ago
  [deleted]
- a1369209993 5y ago
  Something like: <script>document.write("<textarea>...</textarea>");</script> presumably? (I'd assume they meant self-extracting when run as a shell script or something, but they did say "to rely on the ubiquity of commodity web browsers".)
- cxr 5y ago
  Sounds like a trick question, but Netscape's latest beta from September includes scripting support. Rumor has it that they're going to rename it to JavaScript later this year since Sun is pushing for strategic alignment with their Java platform. (You can use it to script e.g. applets). But you can embed the decompression code directly in the file, writing it in the scripting language itself—no compiled bytecode (or even a Java plugin) is necessary.

edwintorok 5y ago

Would be interesting if this was expanded to support more compression formats. E.g. zstd. Gzip is quite an old format and zstd is a lot quicker to decompress.

chrisseaton 5y ago
I don't think it uses the gzip format does it? It just uses deflate.
- lifthrasiir 5y ago
  It's actually the zlib format. The linked post says DEFLATE, but the linked source code correctly mentions zlib instead.
- masklinn 5y ago
  It doesn't use the gzip file format, but it uses the gzip compression format (which is just DEFLATE).
rini17 5y ago
Yes I did double check there's no column to indicate compressed format, no idea how they want to extend it in future. Maybe just autodetect from the contents? However that could fail if you want to store already deflated files. 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.
- mark-r 5y ago
  Since compression can produce any bit combination with equal probability, the only way to reliably auto-detect from the contents is to put some kind of fixed flag at the front. At that point you get more flexibility by making it a new column in the table instead. It doesn't seem like it would be hard to extend this with a compression type and mime type if you need those. 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.

ComputerGuru 5y ago

I think the title could use a (2018) appended to it, just so no one thinks this is a new thing that will be pushed on them or something.

dang 5y ago
Ok, added. Thanks!

say_it_as_it_is 5y ago

S3Lite?

PostThisTooFast 5y ago

I don't see why this is better than simply implementing a table like this yourself.