How to keep history of record updates in MySQL?

Question

I have to create a code in PHP that will allow me to keep the history of record updates in MySQL database so I can find by date an old revision.

Here is the example of what I actualy want to achive: http://en.wikipedia.org/w/index.php?title=Tunisia&action=history

The data are mostly Numbers that we record about the company for generating reports and to extract indices.

I plan to use codeigniter for it's simplicity and I'm looking for idea about a framework or an opensource project that use the same approche to keep history of modifications in the database.

Answer 1

That is not logging the actual SQL updates. It is logging the updates of the data. It will in effect be storing every revision of the page, and just providing the latest one by default. The SQL code that was used for it was not stored.

Thus you will have to take a similar approach. Every time the data changes, you will need to work out how the data has changed and store this 'patch' data. You will probably be best storing the latest version, rather then having to work through all the patches to get to it. This means you will be able to see something like

! created file
* added data to cell D4 'product descript' D5 'set of pens' E5 '£5.99'
* added data to cell D6 'toaster' E5 '£10'
& changed data in cell D4 'Product Description'

Each of these changes would need to be stored with a time stamp or when they where done. You will also need to work out your own scheeme for storing the changes to data.

Another simpler solution, would be to use a wiki engine, that provides all of the functionality you want, if you are willing to work in a web page. You may need to spend some time to make it work better for your needs, letting people edit it from a more finished view, rather then a raw wiki.

Answer 2

You have to use an extra layer between your application and the db. You can make a very simple on your own (instead of direct calling mysql_query you call a function made by you that wraps it and that tracks updates), or use some existing ORM. In pseudo-code

my_mysql_query($query){
    if($query is an update){
        //Log stuff before query
    }
    $r = mysql_query($query);

    if ($r && $query is an update){
        //Log stuff after query
    }
    return $r;
}

And then in your application you call my_mysql_query instead of mysql_query. You can check if the table is the one you want to track and you can copy the row in a copy of the original table.

If you use Doctrine ORM, you can use its Event Listeners to get what you want.

Answer 3

One simple way to keep version history is to create basically an identical table (eg. with _version suffix). Both of the tables would have a version field, which for the main table you increment for every update you do. The version table would have a composite primary key on (id, version).

Whenever you do an update on the actual table, you also INSERT a new row in the version table with duplicate data. Whenever you want to find the version history, all you need to do is something such as SELECT * FROM content_version WHERE id = CONTENT_ID ORDER BY version.

If you use something like Doctrine ORM, it has a behavior that does this for you automatically via event listeners. You can check it out here: http://www.doctrine-project.org/documentation/manual/1_2/en/behaviors#core-behaviors:versionable

Answer 4

If I understand correctly, you're saving just a "number" in a specific table, and you want the revision history of that number?

I'd say: write your datamodel so that it contains a history of that number. So you not only record the latest value of that number but also keep any previous values.

A simple way of doing this is by having a table with the following fields:

• id
• companyId
• number
• timestamp

If you want to know the current number, just do

SELECT * FROM table WHERE companyId = X ORDER BY timestamp DESC

The first one is the current. If you want to see all revisions just do:

SELECT * FROM table WHERE companyId = X

Answer 5

The easiest solution (depending on your specific needs) would probably be to add an on update/insert/delete trigger to your table, so you can perform extra logging when data is inserted/updated/deleted. That way even manual interventions on the db will be covered...

Check http://dev.mysql.com/doc/refman/5.1/en/triggers.html for more information.