Database with version history?

Question

I'm developing a game with a component-based architecture. I want to store entity descriptions in a SQLite database for compactness and convenience. Obviously the backend code is (and the database schema will be) under version control. Since important behaviour is in the database, and because multiple people will be working on it, I feel that it too should be properly versioned.

So far my only solutions are:

• Make periodic backups of the database.
  This would be out of line with the existing version control setup, and thus error-prone.

• Dump the database to a diffable format and version that.
  This requires importing said format, which is akin to compiling, which should be avoided.

I'd like cleaner alternatives within the existing setup, or a compelling reason to switch to another. Is there a nice C++ library for manipulating a text-based (i.e., diffable, and preferably not XML) database?

Answer 1

If you are set on storing this information in a database, I would actually suggest storing the SQL to load the data itself in version control, and having your build tool (say, your Makefile) load this into a fresh database whenever the text file changes.

You'll get readable diffs (see the actual edits) and leaves your "source code" in an easy human format. There are a number of problems with checkin in either binary files (your option 1) or computer-generated files (option 2).

With the first one, you lose diffs and merges. That would be a deal breaker to me - when two people both make changes, what do you do?

With the second one, your diffs and merges will become insanely confusing. The backup tool probably dumps rows in "database order" -- not particularly well defined, and subject to change over time. This will cause terrifying merges. Even if it does do things in a stable order, it is probably going to be strictly less readable than just writing the load SQL by hand and managing that.