Add a audit trail that can be shown online

[zukunft]

The request is to add an audit trial to p4a applications. To log which user has changed which data and when. I suggest the save this information in a table like this

CREATE TABLE IF NOT EXISTS log_data (
table_name varchar(200) NOT NULL,
row_id int(11) NOT NULL,
field_name varchar(200) DEFAULT NULL,
log_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
user_id int(11) NOT NULL,
old_value text,
new_value text NOT NULL
)

This way the changes of each record can be shown on the masks. Displaying this information based on the standard SQL logging would take much more time I guess.

Would be nice if this could be a feature linked to p4a.

It looks to me that this can be done by adding this code at /p4a/objects/data_sources/db_source.php at function saveRow

        if (is_string($pks)) {
            $pk_value = $this->fields->$pks->getNewValue();
            while($field = $this->fields->nextItem()) {
                if ($field->getSchema() != $schema) continue;
                if ($field->getTable() != $table) continue;
                if ($field->getAliasOf()) {
                    $name = $field->getAliasOf();
                } else {
                    $name = $field->getName();
                }
                    if (isset($this->_tables_metadata[$table]['metadata'][$name]) and
                    !$field->isReadOnly() and
                    !array_key_exists($name, $this->_multivalue_fields)) {
                        $logNewValue = $field->getNewValue();
                        $logValue = $field->getValue();
                        if ($logNewValue <> $logValue) {
				if( isset( $_SESSION['log_user'] ) ) {
					$log_user = $_SESSION['log_user'];
				} else {
					$log_user = 0;
				}
				$db->adapter->query(" INSERT INTO log_data "
								 . "  (table_name, "
								 . "             row_id, "
								 . "             field_name, "
						                 . "             user_name, "
								 . "             old_value, "
								 . "             new_value) "
								 . " VALUES (?, ?, ?, ?, ?, ?)", 
								 array($table, 
									 $pk_value, 
									 $name, 
									 $log_user, 
									 $logValue, 
									 $logNewValue));
                        }
                }
            }
        }

and at "deleteRow" add

						if (isset($this->_tables_metadata[$table]['metadata'][$name]) and
						!$field->isReadOnly() and
						!array_key_exists($name, $this->_multivalue_fields)) {
                                                        if( isset( $_SESSION['log_user'] ) ) {
                                                                $log_user = $_SESSION['log_user'];
                                                        } else {
                                                                $log_user = 0;
                                                        }
							$logValue = $field->getValue();
							$db->adapter->query("INSERT INTO log_data(table_name, row_id, field_name, user_name, old_value, new_value) VALUES(?, ?, ?, ?, ?, 'deleted')", array($table, $pk_value, $name, $log_user, $logValue));

This implies that after the login the session var must be set. So each app using it must have the code line
$_SESSION['log_user'] = $username;
after the successful login.