Does a version control database storage engine exist?

Question

I was just wondering if a storage engine type existed that allowed you to do version control on row level contents. For instance, if I have a simple table with ID, name, value, and ID is the PK, I could see that row 354 started as (354, "zak", "test")v1 then was updated to be (354, "zak", "this is version 2 of the value")v2 , and could see a change history on the row with something like select history (value) where ID = 354.

It's kind of an esoteric thing, but it would beat having to keep writing these separate history tables and functions every time a change is made...

Answer 1

It seems you are looking more for auditing features. Oracle and several other DBMS have full auditing features. But many DBAs still end up implementing trigger based row auditing. It all depends on your needs.

Oracle supports several granularities of auditing that are easy to configure from the command line.

I see you tagged as MySQL, but asked about any storage engine. Anyway, other answers are saying the same thing, so I'm going delete this post as originally it was about the flashback features.

Answer 2

You can achieve similar behavior with triggers (search for "triggers to catch all database changes") - particularly if they implement SQL92 INFORMATION_SCHEMA.

Otherwise I'd agree with mrjoltcola

Edit: The only gotcha I'd mention with MySQL and triggers is that (as of the latest community version I downloaded) it requires the user account have the SUPER privilege, which can make things a little ugly

Answer 3

Obviously you are really after a MySQL solution, so this probably won't help you much, but Oracle has a feature called Total Recall (more formally Flashback Archive) which automates the process you are currently hand-rolling. The Archive is a set of compressed tables which are populated with changes automatically, and queryable with a simple AS OF syntax.

Naturally being Oracle they charge for it: it needs an additional license on top of the Enterprise Edition, alas. Find out more (PDF).

Answer 4

One approximation to this is a temporal database - which allows you to see the status of the whole database at different times in the past. I'm not sure that wholly answers your question though; it would not allow you to see the contents of Row 1 at time t1 while simultaneously letting you look at the contents of Row 2 at a separate time t2.

Answer 5

Oracle and Sql Server both call this feature Change Data Capture. There is no equivalent for MySql at this time.

Answer 6

I think Big table, the Google DB engine, does something like that : it associate a timestamp with every update of a row.

Maybe you can try Google App Engine.

There is a Google paper explaining how Big Table works.

Answer 7

CouchDB has full versioning for every change made, but it is part of the NOSQL world, so would probably be a pretty crazy shift from what you are currently doing.

Answer 8

The wikipedia article on google's bigtable mentions that it allows versioning by adding a time dimension to the tables:

Each table has multiple dimensions (one of which is a field for time, allowing versioning).

There are also links there to several non-google implementations of a bigtable-type dbms.

Answer 9

The book Refactoring Databases has some insights on the matter.

But it also points out there is no real solution currently, other then carefully making changes and managing them manually.

Answer 10

"It's kind of an esoteric thing, but it would beat having to keep writing these separate history tables and functions every time a change is made..."

I wouldn't call audit trails (which is obviously what you're talking of) an "esoteric thing" ...

And : there is still a difference between the history of database updates, and the history of reality. Historical database tables should really be used to reflect the history of reality, NOT the history of database updates.

The history of database updates is already kept by the DBMS in its logs and journals. If someone needs to inquire the history of database upates, then he/she should really resort to the logs and journals, not to any kind of application-level construct that can NEVER provide sufficient guarantee that it reflects ALL updates.