Post by Randall Smith(o) Allow humans to view the contents of a DB without custom tools.
SQLite database file are binary. That is a necessity in any format
that needs to store binary data. On the other hand, the SQLite
database file format is carefully and fully documented
(https://www.sqlite.org/fileformat2.html) and there have been
multiple, independent implementations of readers and writers for that
file format. SQLite databases are one of only three formats (the
others being JSON and CSV) recommended by the US Library of Congress
for archival storage of datasets.
(https://www.sqlite.org/locrsf.html). The SQLite database library is
the second mostly widely deployed bit of software in the world -
second only to zlib - so the tools needed to read SQLite are probably
already available on your system. SQLite is baked into every Mac and
Windows machine. SQLite is not a thoroughly baked into Linux
machines, but it is still pretty common.
Text files are also opaque binaries in the sense that they are stored
using a binary encoding on a disk drive or SSD. They seem less opaque
because you have tools easily at hand (a filesystem and "cat") to
access them. The point is this: Tools to access SQLite are also
widely available. Perhaps not quite as widely as "cat", but nearly
so.
"Opaque" vs. "non-opaque" is not a binary property of data files. It
is a question of degree. A text file might seem less opaque than a
database, but that depends to some extent on the text that it
contains. Try reading the HTML for a typical website. Or trying
reading the XML that is at the core of a Word document or Power-Point
presentation. Those files are all text, but they seem pretty opaque
to me.
Post by Randall Smith(o) Have a way to see what has changed between V1 and V2 of a database,
e.g., for a "change review."
The "sqldiff" utility program will do this for you. Just as with the
unix "diff" command, the "sqldiff" shows you (in human-readable form)
the difference between two SQLite database files. The output takes
the form of SQL statements that will transform the first file into the
second.
Post by Randall Smith(o) Have a way to merge two independent sets of database changes into a
single result in an understandable way.
The sqldiff command will do this. If you have a baseline database B,
and two separate derivative databases D1 and D2, you can merge those
changes together by computing the differences in B->D1 and applying
those changes to D2. Or compute the differences from B->D2 and apply
those changes to D1. As with "patch" or "diff3", there is the
possibility of merge conflicts, but you a clean merge surprisingly
often.
Post by Randall Smith(o) Have a way to make changes (update, insert, delete) to the DB data in a
pinch without specialized tools.
I guess it all comes down to how you define "specialized". At some
point, tools become sufficiently ubiquitous and common-place that they
cease to be specialized. The SQLite command-line shell may have
reached that threshold. If not, it is certainly close. SQLite is
certainly not obscure or esoteric. It comes installed by default on
just about every computer you can purchase today.
--
D. Richard Hipp
***@sqlite.org