CODEWITHSUNDEEP
sqlmysqldatabaserefactoring-databases
Zak
Zak

Reputation: 25205

Does a version control database storage engine exist?

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...

Upvotes: 14

Views: 608

Answers (10)

Erwin Smout
Erwin Smout

Reputation: 18408

"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.

Upvotes: 0

Amirshk
Amirshk

Reputation: 8258

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.

Upvotes: 1

Bite code
Bite code

Reputation: 596593

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.

Upvotes: 1

Johrn
Johrn

Reputation: 1440

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.

Upvotes: 1

Mike McKay
Mike McKay

Reputation: 2626

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.

Upvotes: 1

AJ.
AJ.

Reputation: 3102

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

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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

Upvotes: 1

mrjoltcola
mrjoltcola

Reputation: 20842

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.

Upvotes: 3

Jonathan Leffler
Jonathan Leffler

Reputation: 753455

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.

Upvotes: 0

APC
APC

Reputation: 146179

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).

Upvotes: 3

Related Questions