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 to generate 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 approach 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

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 LIMIT 1

If you want to see all revisions just do:

SELECT * FROM table WHERE companyId = X

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

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 5

There is an open source (MIT license) framework to build CRM and ERP type of database driven web applications. You can download EPESI from http://epe.si/ available also on SourceFroge: http://sourceforge.net/projects/epesi/

EPESI's CRUD engine - the Record Browser - has a very efficient record history as well as other features like advanced permission system (down to a field level) and much more.

A single recordset stores data in as many as 10 tables and it is database agnostic (uses PHPAdoDB). A table called recordset_data stores "raw" data and history of changes is stored in 2 additional tables: recordset_edit_history and recordset_edit_history_data.

Record's edit_history has the following structure:

• ID (Primary key for this table, index)
• Edited on (timestamp: 2008-05-14 15:18:15)
• Edited by: (user ID)

Record's edit_history_data has the following structure:

• edit_id = ID from edit history
• field = the name of the field that was changed
• old_value = value of the field that changed.

It is a very fast and efficient engine and it stores changes not on the record but on a single field level.

Answer 6

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.