Hacker News

absolutely adore the mbtiles format! thank you for creating that.

Yes! Gus (the developer) also has made & maintained FMDB for many years, a nice Cocoa wrapper for the SQLite bindings.

https://github.com/ccgus/fmdb

Recently reverse engineered the Money Pro backup format, it's a binary file with SQLite with some additional XML information backed in. It feels like they're purposefully making it harder for users to export their data in a useful format, especially after the changes they made to their financial model.

I am not really classically trained on the subject but I think this is the idea behind relational storage, it is to have better extraction options, you don't have to treat your data as a single document at a time.

Naively, most data looks hierarchical and the instinctive reaction is to make your file format match. But if you think of this as a set of documents stacked on top of each other if you take the data as a bunch of 90 degree slices down through the stack now your data is relational, you loose the nice native hierarchical format, but you gain all sorts of interesting analysis and extraction options.

It is too bad relational data types tend to be so poorly represented in our programming languages, generally everything has to be mapped back to a hierarchical type.

Maybe not as obvious for those without formal education in """database normalization""" but it's pretty trivial to convert from a tree structure to a flat table structure using foreign key relations. Recursive queries aren't even that difficult in SQLite, so self-referential data can be represented cleanly too, if not a bit more difficult to write. IME most applications "tree structures" aren't self-referential and are better formalized as distinct entities with one-to-one relationships (ie. a subtree gets a table).

There's always the lazy approach of storing JSON blobs in TEXT fields, but I personally shy away from that because you lose out on a huge part of the benefits of using a SQL DB in the first place, most importantly migrations and querying/indexing.

I had some json data that I wanted an annotation interface for. So I asked codex to put it into sqlite and make a little annotation webserver. It worked quickly/easily and without hassle. Sqlite supports queries over json-like objects.

Maybe a very simple document oriented db would have been better?

My biggest gripe is that the sqlite cli is brutally minimal (makes sense given design), but I probably should have been using a nicer cli.

You do know, that you can create more than one table in SQLite and have references from one to another? Even recursive references work

[flagged]

There's FUSE-using Sqlitefs & WebDAV-using Wddbfs.

Is there a software solution to mounting any DB as a filesystem?

I think the developers had the same idea https://fossil-scm.org/

It can be compressed, see https://sqlite.org/sqlar.html

Planning on putting a license on it? I habitually ignore repos without a LICENSE file in them.

It does, here's a schema from an image I just saved with the latest version. Pretty simple.

  CREATE TABLE image_attributes ( name text, value blob);
  CREATE TABLE layers (id text, parent_id text, sequence integer, uti text, name text, data blob);
  CREATE TABLE layer_attributes ( id text, name text, value blob);

Messages uses it too on Mac; was using it to do some convoluted text search on my history

- archiver format to stow multiple files in one; your actual files (in your choice of format(s)) go inside

- files can be individually extracted, in any order, from the archive

- thousands of implementations available, in every language and every architecture. no more than 32KiB RAM needed for decompression

- absolutely no possibility of patent challenges

If all you need is a bag of named blobs and you just want quick reasonable compression supported across all platforms, why not?

If you don't need any table/relational data and are always happy to rewrite the entire file on every save, ZIP is a perfectly fine choice.

It's easier than e.g. a SQLite file with a bunch of individually gzipped blobs.

ZIP isn’t an application format, it’s a container, no? You store files with any format in a .zip, and that’s what applications do - they read files with other formats out of the .zip. What are your goals; what else would you pick, and why? What are the questionable choices you refer to?

Because Windows can view and extract them out of the box without installing any additional applications. If it supported anything better out of the box I'd guess people would use that instead.

AMD/Xilinx Vivado uses ZIP format to compress design checkpoints. They just give them a .dcp extension though.

It works well enough. What could, for instance, epubs gain by having another base format instead?

I think most format use "gzip" instead of "zip".

Same as .zip, .xml, .json and many others.

Doesn't mean that whatever the app stores inside will remain backward compatible which is the harder problem to solve.

I worked @fzakaria on developing that idea. It actually worked surprisingly well. The benefits are mostly in the ability to analyze the binary afterward though rather than any measurable benefit in load time or anything like that though. I don’t have the repo for the musl-based loader handy, but here’s the one for the virtual table plugin for SQLite to read from raw ELF files: https://github.com/fzakaria/sqlelf

Forget elf, imagine having a SQLite file that stores elf, exe and DMG binaries. I would not mind working on something like this.

Or a replacement for Access

wonder if this would make hot-swap functions easier, if every function had its own section and every section was in the db