Hacker News Reader

> SQLite databases have a binary format which is known to change periodically. An attempt to read a SQLite file in 100 years would have to know the exact version of the file, and be able to reach back through history to find that version of SQLite (or a compatible version), and find a way to compile it for whatever CPU architecture exists in 100 years.

I contend this is not as big of a problem as you make it sound. First of all, the sqlite file format states its version in a string at the beginning of the file, so determining the version of a file is trivial even without any special tools. The sqlite source code is extremely widely distributed. Between linux distros and the many projects which, for better or worse, embed sqlite in their source trees, there are easily over a million copies of sqlite3.c floating around. The chances of all of these copies being lost, even in a catastrophic event, is negligible. Even if we did somehow lose every copy of sqlite3.c, reverse engineering a binary format is far from impossible. In fact the old binary MS Office file formats were reverse engineered by multiple groups.

The chances of us losing the ability to compile C code are even more negligible. If human civilization has fallen that far, we will probably be too busy killing each other with sharp sticks over drinkable water to care about reading some hundred year old documents.

Not that I disagree that sqlite is fairly ubiquitous today, but remember that not that long ago, DbaseII, dbm, and BerkleyDB were fairly ubiquitous.

I give you "the relevant XKCD":

https://xkcd.com/1909/

Somewhat more practically, a relevant DLib Magazine [0] quote: "not all file formats are suitable for long term preservation, even if they have an open specification. Some lossy and compressed file formats pose a higher risk of total loss if even a single bit is lost."

And from the Library of Congress [1], in the context of format preferences for text with structural markup: "XML or SGML using standard or well-known DTD or schema appropriate to a particular textual genre."

[0] http://www.dlib.org/dlib/july16/houghton/07houghton.html [1] http://www.digitalpreservation.gov/series/challenge/

> "not all file formats are suitable for long term preservation, even if they have an open specification. Some lossy and compressed file formats pose a higher risk of total loss if even a single bit is lost."

Wouldn't this issue apply more to OpenDocument, which is compressed (into a ZIP archive), than SQLite, which (at least by default) is not?

But then, I question the appropriateness of the advice. If you're serious about archiving, you should be using error-correcting codes in some form, so that the archived data will remain recoverable bit-for-bit even with a large number of bit errors in the underlying medium. To be honest, I'm not that familiar with long-term archiving practices, but if you have some kind of RAID setup, that should give you both error correction (for bit errors) and drive redundancy (for loss of entire drives). Alternatively, you could use dedicated ECC tools like par2.

True, most data that gets preserved will probably be preserved by chance, by people who are not serious about archiving, and may not take sufficient steps to prevent errors. But they're also not going to choose a format for optimal archiving either, so you're kind of stuck with the fact that many modern file formats have built-in compression and/or checksums, and thus don't hold up well when corrupted. We could keep the issue in mind when designing new formats, but is resilience to corruption really worth the additional storage cost of leaving data uncompressed? Or perhaps we could design formats to have built-in ECC instead of just checksums, but that would also waste space...

For those who are worried about the length of SQLite support, SQLite is being used in the Airbus A350

https://twitter.com/copiousfreetime/status/67583454330408140...

This means that it has long term support for the life of the airframe which for passenger airliners can easily by 50+ years (see https://en.wikipedia.org/wiki/Boeing_737 which is still flying after 50 years).

Companies like that often just buy up a stock of the hardware they need and stuff it in a warehouse, at least when it's in danger of being discontinued. They don't need to maintain the source code at all. They just need to be able to keep installing the binary.

Another solution to this problem would just be to bundle a txt description of the format with each file. So e.g. have the first n bytes be a plain text description of the sqlite format, and the rest be the DB in sqlite format.

May as well just bundle SQLite while you're at it.

Hmm? I mean sure, but the point is that adding a few kb of text to the file is a more lightweight solution. Less than the overhead of using xml, probably.

Lower overhead today, but in the original context, it's still a greater overhead if/when SQLite is no longer active. At that point, you have to write a binary parser just to get back the RDBMS, at which point you have to reverse-engineer how the tables were built in order to re-create the document.

An interesting read on the topic and challenges of digital preservation: http://www.dl.slis.tsukuba.ac.jp/DLjournal/No_20/1-hedstrom/...

The SQLite binary format is simple and well-documented, so writing a binary parser would be a pretty straightforward exercise.

>at which point you have to reverse-engineer how the tables were built in order to re-create the document.

I don't understand what you mean by this. All you have to do is examine the contents of the various tables. It's no different to examining the contents of an XML file. In either case, how easy it is to reconstruct the document will depend on how well designed the format is.

> It's reasonable to assume that someone could, 100 years from now, unzip an OpenDocument file.

LOL said the thousands of people with old dusty Zip disks sitting in boxes in their attics. That was only 25 or so years ago.