History logger
This is very important part of application, especially if any company or organization have a large number of employees and when those employees are dealing with sensitive data.
In our case we have contract form which can be created and updated from the list of employees who have privilege to do so. So there is more then one person who can change contract details.
How we can track contract changes?
For example, we can track dates of contract changes and persons who made those changes, but that can not give us detailed list of changes for every field and every value. We can only see that something is changed by someone on a specific date.
The answer is that we need to track, beside dates and users, EVERY FIELD of our contract.
There are two types of design solutions for this problem, in general:
- Audit table(s)
- A single table with the fields id | table | column | row | old_value | new_value | timestamp | userid.
- Multiple tables like #1 except without the table column.
- Multiple tables that mirror the schema of the original tables to track.
- Changes on business logic and persistence level
- For every table that we are tracking we will create composite key (tablename_ID, tablename_VALID_FROM, tablename_VALID_UNTIL) and we would also add tablename_STATUS and tablename_USER fields.
More details here: https://www.quora.com/What-is-the-best-design-for-an-audit-history-table
Every time you want to change a customer record, instead of updating the record, you set the VALID_UNTIL on the current record to NOW(), and insert a new record with a VALID_FROM (now) and a null VALID_UNTIL. You set the "_USER" status to the login ID of the current user (if you need to keep that). If the customer needs to be deleted, you use the _STATUS flag to indicate this - you may never delete records from this table...
More details here: http://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records
You can also google : https://www.google.rs/search?q=php+mysql+history+tracking+fields
Second solution was not acceptable because project was largely completed (and contract (as a real world entity) is combined of many tables from our database, which I need to track as a one logical entity) so I chose Audit table as a solution. A single table with the fields id | table | column | row | old_value | new_value | timestamp | userid... modified and adopted to project specific design.
This would track all changes to all tables in a single place and has the benefit of minimizing the number of tables. It does make querying a little difficult, but not impossible.
Impementation:
- Create “tracking” table
- Create triggers for update,insert and delete actions for all tables related to contract fields
- create trigger generators
- create triggers
- Create queries to restore contracts history
Tracking table
Example:
We have tables: contracts, rooms and contracts_rooms. All these tables creates a part of logical unity contract. We will create, for example, a trigger for table contracts_rooms and fields would be filled with data something like this:
Code for trigger generator would be something like this:
#!/bin/sh
#databasename contracts_rooms
db="$1";
shift
echo "DELIMITER ;;"
for table in "$@"
do
sql="show columns from $table"
columns=`mysql -uroot -p123 -B -N -e "$sql" "$db" | cut -f 1`
echo "CREATE TRIGGER ${table}_update_log"
echo "BEFORE UPDATE ON $table"
echo "FOR EACH ROW BEGIN"
echo "SET @userId= (SELECT edit_user_id FROM contracts WHERE id=OLD.contract_id limit 0,1);"
for column in $columns
do
echo " IF (OLD.$column != NEW.$column) THEN"
echo " INSERT INTO contractmodels_trackings (MODEL, MODEL_ID,MODEL_SUBJECT, MODEL_SUBJECT_ID,MODEL_OBJECT,MODEL_OBJECT_ID,COLUMN_NAME, BEFORE_CHANGE, AFTER_CHANGE, CREATED_AT, USER_ID,ACTION)"
echo " VALUES ('contracts_rooms', OLD.id,'contracts',OLD.contract_id,'rooms',OLD.room_id, \"$column\",OLD.$column, NEW.$column, NOW(),@userId,'UPDATE');" # @userId
echo " END IF;"
done
echo "END;;"
done
And now, the last step is to create triggers from generators.
#!/bin/bash
#This script must be located in the folder where all generators are
FILES=*
DATABASE=etm_live
ABSPATH=$(pwd)
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
for f in $FILES
do
filename="$f"
#skip this script
if [ $filename != "trigger_generator.sh" ] && [ $filename != "trigger_generator.sh~" ]; then
echo "Starting $f shell script..."
#get the table name from the script name
#generate_triggers_for_table [0]
#table_name [1]
#_log_typeoftrigger.sh [2]
substring=""
result0="${filename/triggers_generators\//$substring}"
result1="${result0/generate_triggers_for_table_/$substring}"
result2="${result1/_log_delete.sh/$substring}"
result3="${result2/_log_insert.sh/$substring}"
table="${result3/_log_update.sh/$substring}"
#SQL code for trigger generation append to trigger.sql file inside tmp folder
./"$result0" "$DATABASE" "$table" >> /tmp/triggers.sql
fi;
done
`mysql -uroot -p123 -B -N --skip-column-names etm_live -e 'SHOW TRIGGERS;' | cut -f1 | sed -r 's/(.*)/DROP TRIGGER IF EXISTS \1;/' | mysql -uroot -p123 etm_live;`
`mysql -uroot -p123 etm_live < /tmp/triggers.sql`
Now when we have our “log” table we can restore our data relying on contract update time.
We will need a list of every changed contract and a list of all changes for a contract.
For every contract change we have row in this table. Every change have form same as original contract form with one difference – that form is disabled.
