Short but performant logger

pisisler

Hi all,

I would like to share the method I am logging changes, referenced from here: https://editor.datatables.net/manual/php/events#Logging-changes and discussed in here: https://datatables.net/forums/discussion/46968/robust-audit-logging/

What I made differently is; keep the footprint very short and omit the need of querying the database for changes by sending the old values to the server in the first place.

This will log what is deleted, what is created an what is changed.

These are the steps:

1- You need to be using idSrc to set a unique identifier (like id). If you don't use a custom id, then you will need to extract row id from DT_RowId at the server side. Or if you don't want both ways, you can use one of your field's value as unique identifier but recommend against if you are not using it as a read-only field; because it wouldn't be "unique" if it is editable. (I used id in the example below.)

2- You need to be using row scope rather then cell and you need to set submit: "allIfChanged" in formOptions.

3- Send the old values to the server along with the new ones. To do this, you need a custom data callback in ajax. Here is up now:

    formOptions: {
        inline: {
            submit: 'allIfChanged',
        }
    },
    idSrc: "id"
    ajax: {
        url: "/your/path/to/server-side.php",
        data: function (d) {
            if (editor.modifier() != null) {
                d.old_values = {};
                table.rows(editor.modifier()).data().each(function (row) {
                    d.old_values[row.id] = row; // Change "id" to whatever you like in idSrc
                });
            }
        }
    }

Now at the server side, you will have $_POST['old_values'] which includes all of the edited rows' old values in a format like array(id => array(...), other_id => array())

4- You need to instantiate the custom unique identifier (i.e. id) at server side:

Field::inst('id')->set(false),

Important: Don't forget to alter your custom validators according to idSrc to let them play good with multi-editing. You can use the forth parameter (which is to be $host) of the custom validator to extract $host['id'] to allow you to make sure which row you are validating. Like for example:

Field::inst('price')->validator(function ($value, $row, $field, $host) {
    // Do your validation
    // Old values of this row is reachable in $_POST['old_values'][$host['id']]
    // Don't forget to make it failsafe of course
}

5- Add event listeners to your editor instance:

    })->on('postEdit', function ($editor, $id, $values, $row) {
        log_change($editor->db(), 'edit', $_POST['old_values'][$id], $row);
    })->on('postCreate', function ($editor, $id, $values, $row) {
        // Now rows don't have "old" data.
        log_change($editor->db(), 'create', [], $row);
    })->on('postRemove', function ($editor, $id, $values) {
        // Deleted rows don't have "new" data
        log_change($editor->db(), 'delete', $values, []);
    });

6- Finally your logger:

function log_change($db, $action, $id, $old_values, $new_values) {
 
    $change_log = '';
    // Log the complete row if this is a deletion or creation; otherwise log only changes
    $changes = $action == 'delete' ? $old_values : array_diff_assoc($new_values, $old_values);
 
    // Nothing has changed
    if (count($changes) === 0)
        return;
 
    foreach ($changes as $field => $value)
        $change_log .= "<b>$field:</b> " . ($action == 'edit' ? "$old_values[$field] => $new_values[$field]" : $value) . ', ';
 
    $db->insert('logs', array(
        'type' => $action,
        'id' => $id,
        'changes' => substr($change_log, 0, -2),
        'create_date' => date('Y-m-d H:i:s'),
        'create_user' => $_SESSION['user'],
        'ip' => $_SERVER['REMOTE_ADDR'],
    ));
}

7- You would probably like to add a log rotation like deleting logs older than some days.

colin

Nice, thanks for posting,

Colin

pisisler

A correction: I forgot to add $id parameter to the log_change() calls in the event listeners.