mysql (almost) complete auditing

Question

I'm looking for a way of making simple event log for my tables. I have few tables that can be changed by various users, and I want to keep track on:

- who made the change
- when 
- what was before update
- what is the new value
- which table and which record & column

somthing like will be great:

20:00:00 | john | update | products | 113 | product_name | "xbox" | "xbox 360"
20:00:10 | jim  | update | products | 113 | product_name | "xbox 360" | ""
20:01:00 | jim  | delete | products | 113

So i read that triggers could be the answer but as far as I read it seems that I need to have a complete new table for each column I want to keep track on. Triggers are not perfect for this job also because I want to log who made the change, and from what I read this is not possible.

I thought of making 3 different functions for CRUD (insert, update, delete), and just before making the query, to check what is changed and to make the log and then run the query. But from here it seems to be very slow and complicated.

Is there another better way ?

Thanks

---

Ok, I checked again the triggers and its not what I was looking for, so I wrote simple functions that will check for each query you want to log if the new values are different, and if so - it logs it.

The main problem is, and I didn't measure it, but it obviously slower.

first you need to make a new mysql table as follow:

• id (a_i, primary)
• creation_date (datetime)
• user_id (int)
• table_name (tinytext)
• record_id (int)
• cell_name (tinytext)
• action_type (tinytext)
• old_value (text)
• new_value (text)

after that, write the functions. I didn't write yet the 'INSERT' and 'DELETE' sections but I think i should be much easier.

function log_query($action_type, $table, $values, $parameters){

if ($action_type == 'UPDATE'){

    log_updates($action_type, $table, $values, $parameters);

    $query = "UPDATE $table SET ";
    foreach ($values as $key => $value){
        $query .= $key."='";
        $query .= $value."', ";
    }
    unset($value);

    $query = substr($query, 0, -2);

    $query .= ' WHERE ';

    foreach ($parameters as $key => $value){
        $query .= $key."='";
        $query .= $value."' AND ";
    }
    unset($value);

    $query = substr($query, 0, -4);

    $result = mysql_query($query);

    }
} 

and :

function log_updates($action_type, $table, $values, $parameters){
$where = " WHERE ";
$user_id = '1234'; //example
foreach ($parameters as $key => $value){
        $where .= $key."='";
        $where .= $value."' AND ";
}
unset($value);

$where = substr($where, 0, -4);

foreach ($values as $key => $value){
    $result = mysql_query("SELECT $key, id FROM $table $where");

    $row = mysql_fetch_row($result);
    $old_value = $row[0];
    $record_id = $row[1];

    if ($action_type == 'UPDATE'){
        if ($old_value != $value){
            $logger = mysql_query("INSERT INTO auditing (event_date, action_type, user_id, table_name, record_id, cell_name, old_value, new_value)
                                    VALUES (NOW(), '$action_type', $user_id, '$table', '$record_id', '$key', '$old_value', '$value')");
            if (!$logger) echo mysql_error();
        }
    } 


    }
    unset($value);

}

to call the functions you need to first order the parameters to find the specific row, and the new values in to arrays, and after that call the log_query function:

$update = Array('name' => 'barbara', 'description' => 'new name');
$parameters = Array('id' => '1', 'name' => 'barbi');
log_query('UPDATE', 'checktable', $update, $parameters);

This will actualy will check if the 'name' has changed and if the description is changed. for each one, if it change, it will insert new record into 'auditing' table specifying the exact change. after loging the change, it will run the update query. in our example:

UPDATE checktable SET name='barbara', description='new name' WHERE id='1' AND name='barbi'

hope this is helping. It tested for now and works. If there will be updates - I'll post it here.

Answer 1

Hmm, I was also thinking about this.

• Having a table per table-to-keep revisions for would not be that much of a problem for me personally, but hey.
• Username can be kept with user-defined variables I believe, (after a session start issue something like SET @user='someone', and use that.
• As longs as there are triggers after INSERT, UPDATE and DELETE, getting the previous / next values is a simple query, I'll only store the OLD values.

In short, for a table with coluns (a,b,c) I'd create a table with columns (user_id,modtime,a,b,c).

Major drawbacks:

• batch updates are slow (so choose your tables to keep revisions for carefully)
• data duplication deluxe, you'll / I'll have to have enough storage space
• 'related' data does not trigger a revision (i.e: altering a group_members table doesn't really alter a groups table, while you may want to keep that as a point in time for groups rather then delve through group_members alterations.

All in all it seems a good deal to me, but as I've seldomly seen it in practise there must be compelling reasons why its bad, so I'll await those answers.