Audit Table for TNG Places

From TNG_Wiki
Jump to navigation Jump to search

Problem

For Site Administrators who allow other users to modify their data, the question "how can I track changes to places and enforce integrity with place entry" seems to pop up regularly on the email list and forum. Sure there’s the Admin log, although helpful in providing when and by whom edits were made, it doesn’t really provide enough information on which actual details may have been modified.


Proposed Solution

Create an audit table that can capture not only the updated place information, but also the original data before modification. Ideally this audit table would store the original place details before editing and the new place details after editing, the date and time of the changes and the changer details, allowing the Site Administrator to know exactly which place entries have been modified.

How? Well, we can achieve the above by the use of special database scripting called Triggers and Stored Procedures.



Stop2.png

The use of triggers and stored procedures should be attempted by experienced users only and should always be tested thoroughly on an offline database before implementing on your public site.




Database Triggers

A database trigger is special code that resides within the actual database that gets automatically activated on a particular table immediately after a database event, such as when an update, insert or delete query is executed.

The most common uses for triggers are:

  • To capture data changes for audit logs.
  • To perform calculations.
  • To enforce data integrity.


Stored Procedures

A stored procedure is a set of SQL statements stored within the database.

The most common uses for stored procedures are:

  • Enhanced database Security.
  • Performance improvements.
  • Can be called by triggers to execute specialised SQL statements on the database.



Stop2.png

MySQL has only supported triggers and stored procedures since version 5.0.2, with improvements made in versions 5.1 and 5.5.





Before continuing further check your version of MySQL from either:

  1. The TNG Administration - Setup - Diagnostics tab
  2. Or by executing the following statement in a MySQL Sql editor:


SHOW VARIABLES LIKE "%version%";


Also ensure you have been granted the privileges/user rights to create triggers as not all hosts allow trigger creation.

You can check your privileges by entering SHOW GRANTS in a MySQL Sql editor.


Triggers and Stored Procedures The Good, The Bad, The Ugly

So now we know what they are, it’s important to note that it’s not always sunny in the land of database triggers & stored procedures. Every database administrator will have their own opinion on the use of triggers & stored procedures, some love them others loathe them, so let’s start with :

The Ugly

As mentioned above, triggers & stored procedures reside in the actual database, so they’re pretty much hidden from view. So one day you’ll add a trigger and everything will hum along nicely for a while, maybe a few years. Then one day you’ll modify something, maybe a column name or database schema then NOTHING...everything stops working.

TIP: If things suddenly stop working, especially after database schema/structure modifications or site upgrades, ALWAYS remember to check your trigger & stored procedure code.

Too many triggers on too many tables will result in a performance downgrade. If you are one of the lucky ones with 100,000+ people in your database and 100’s of contributors beavering away on your database all day long then things may slow down considerably. Remember a trigger can be executed on every row update, insert and deletion. Triggers can also increase lock times while they update fields effecting user experience and overall database performance.

TIP: Know what you want to audit. Don’t place triggers on every table that fire on every column change..you will kill not only your database, but your sites overall performance will suffer as well.

Stored procedures & triggers can be notoriously hard to troubleshoot as they provide little feedback when things go wrong. Never, Never add a trigger to a public database without testing it thoroughly first.

TIP: Only experienced users should attempt to implement triggers & stored procedures.

The Bad

Using triggers & stored procedures adds another code layer to your existing TNG set up. So now not only do you need coding knowledge/experience in php, html, css, but database scripting as well.

Triggers & procedures add an additional level of complexity and maintenance overhead to your website especially if Darrin includes database schema changes as part of a future TNG upgrade.

The Good

No modding of files required - Allows advanced users to add specialised code to the database without changing any of the standard php files that ship with TNG.

Reliability - If coded correctly and maintained along with the regular site updates they perform their task reliably and allow us to do some powerful & funky stuff with our databases.



Stop2.png

Users who frequently update their site with bulk Gedcom imports should avoid the use of triggers. Triggers fire on every row update, insert or delete which could have serious consequences for your site when importing many changes via a gedcom import.




In the current version of MySQL there is no easy way to disable a trigger once implemented. Triggers can be dropped (deleted) via phpMyadmin or similar database administrative tool.

So after all that, if your still keen...


Let's Get Started

These are the steps we will complete:

  1. Create a new table called tng_place_audit.
  2. Create a trigger on the tng_people table to capture updates to the birth place & death place columns.
  3. Create a stored procedure that will populate the modifications made to theses place fields to the tng_place_audit table.
  4. Create a few audit reports using SQL.

NOTE : Instructions for each of steps will be shown in Navicat for MySQL (9.1.11) & phpMyAdmin (3.5.8).


Step 1. Create a new table called tng_palce_audit

Enter the following code in your MySQL Database Admin tool, e.g. phpMyAdmin, to create the tng_place_audit table :


Table Creation Script


Step 2. Create a trigger on the tng_people table

Currently MySQL supports three types of triggers :

  • Insert triggers
  • Update triggers
  • Delete triggers


MySQL’s handling of triggers is not as slick or as robust as some of the other major players such as SQL Server, Oracle & Firebird. MySQL allows only one trigger of the above types on each table.

In MySQL each of the trigger types can be ‘fired’ once either before or after an event. E.G.) before insert, after insert, before update, after update, before delete and after delete.

In this guide we will only be working with after update triggers, that is capturing changes AFTER the data has been UPDATED.


The beauty of update triggers is that they allow capturing of field values before an update is performed and after the update has been completed by using the OLD and NEW keywords.


The OLD keyword refers to existing records before the data is changed. The NEW keyword refers to the new records after the data is changed.


The purpose of our ‘after update trigger’ is:

  1. Monitor changes in the birthplace & deathplace fields of the tng_people table.
  2. Call a Stored Procedure.
  3. Pass variables to the called Stored Procedure.


First let’s create the trigger, then we can examine the code.


In phpMyAdmin (3.5.8)

  • Select your TNG Database
  • Select the tng_people table then select the Triggers tab
  • Select "Add Trigger"
    • Enter tr_update as the Trigger name
    • Ensure tng_people appears as the table name
    • For Time select After
    • For Event select Update


Enter the following code in the definition box, then press the "GO" button:

BEGIN
IF OLD.birthplace <> NEW.birthplace THEN
  CALL sp_place_audit(OLD.ID, NEW.birthplace, OLD.birthplace, 'birth');
END IF;
IF OLD.deathplace <> NEW.deathplace THEN
  CALL sp_place_audit(OLD.ID, NEW.deathplace, OLD.deathplace, 'death');
END IF;
END


phpMyAdmin Trigger Creation


NOTE : Definer should be your database username e.g.) yourusername@localhost


To create the trigger in Navicat for MySQL (9.1.11):

  • Connect to your TNG Database
  • Select the tng_people table and click the "Design Table" button.
  • Click the Triggers tab then click the "Add Trigger" Button
    • Under Name enter tr_update for the trigger name
    • Under Fires select After
    • Select the check box under Update


Navicat for MySQL Trigger Creation


In the Definition section enter the following code, then click the Save Button:


BEGIN
IF OLD.birthplace <> NEW.birthplace THEN
  CALL sp_place_audit(OLD.ID, NEW.birthplace, OLD.birthplace, 'birth');
END IF;
IF OLD.deathplace <> NEW.deathplace THEN
  CALL sp_place_audit(OLD.ID, NEW.deathplace, OLD.deathplace, 'death');
END IF;
END


Your screen should now look like the following :


Navicat for MySql Trigger Definition


We have now added a trigger that will fire AFTER UPDATE on every row of the tng_people table.


Ok, lets step through the trigger definition code and explain what it does:


All MySQL triggers must start with a BEGIN statement and end with an END statement

BEGIN

. .trigger code goes here .

END


The code between the BEGIN and END statements defines what the trigger will do.


The first line after the BEGIN statement limits the trigger to the birthplace column in the tng_people table and is a typical IF THEN statement.

IF OLD.birthplace <> NEW.birthplace THEN


To translate into normal language :


After an update on the tng_people table

IF the existing (OLD) birthplace data is not equal to the updated (NEW) birthplace data THEN Execute the next statement


The next line in our trigger code executes a Stored Procedure named sp_place_audit that we will create in Step 3 and passes four values to this Stored Procedure:

CALL sp_place_audit(OLD.ID, NEW.birthplace, OLD.birthplace, 'birth');


So to translate what we have so far in normal language:

After update on the tng_people table

IF the existing (OLD) birthplace data is not equal to the updated (NEW) birthplace data THEN

Execute the Stored Procedure called sp_place_audit with the following values from the tng_people table :

OLD.ID = Existing ID of the row of the birthplace field that is being updated.

NEW.birthplace = Updated or new data from the birthplace column.

OLD.birthplace = Existing or old data from the birthplace column.

‘birth’ = A label which identifies the update as being performed on the birthplace column.


As with all IF THEN blocks the next line:

END IF;

Closes the IF statement.


The next IF-THEN-END IF block performs exactly the same functions as the first block, but this time on the death place field in the tng_people table.


So the full code in normal language :

After update on the tng_people table

IF the existing (OLD) birthplace data is not equal to the updated (NEW) birthplace data THEN

Execute the Stored Procedure called sp_place_audit with the following values (OLD.ID, NEW.birthplace, OLD.birthplace, ‘birth’)

END IF

IF the existing (OLD) deathplace data is not equal to the updated (NEW) deathplace data THEN

Execute the Stored Procedure called sp_place_audit with the following values (OLD.ID, NEW.deathplace, OLD.deathplace, ‘death’)

END IF


NOTE : All updates to other columns in the tng_people table will be ignored as we have limited the trigger to only monitor changes to the birthplace and deathplace columns.


Step 3. Create the Stored Procedure

As we saw in Step 2 our trigger when fired after update on the tng_people table calls the stored procedure sp_place_audit. The purpose of our stored procedure is :

  • Receive values from our After Update trigger
  • Retrieve additional information from the tng_people table
  • Insert all values into the tng_place_audit table


To Create our stored procedure in phpMyAdmin (3.5.8)

  • Select your TNG Database
  • Select the "Routines" tab
  • Under New, click "Add Routine"


Enter the following details:

  • Routine Name: sp_place_audit
  • Type: Select Procedure from the drop down.

Enter the following Parameters as per the below screen shot: (Note: Use the Add Paramter button to add each parameter)


phpMyAdmin Stored Procedure Parameters


In the definition box enter the following code:


Stored Procedure Code


Enter the following to complete our procedure creation:

Definer: yourusername@localhost (Substitute your real username for yourusername)

Security Type: Select Definer from the drop down

SQL Data Access: Select Contains SQL from the drop down

Your screen should now look like the following:


phpMyAdmin Stored Procedure Creation


Important Note on Parameters : Since we are passing values from our trigger to the procedure we need to define these values as Parameters to be used by the Stored Procedure:

Each parameter value contains four parts: MODE parameter_name parameter_type(size)

  • MODE - Can be either IN, OUT or INOUT. We will be using IN which signifies that the value for the parameter will be received inbound by the stored procedure.
  • parameter_name - The name of the parameter.
  • parameter_type - Parameter data type.
  • (size) - Size.


To create our stored procedure in Navicat for MySQL (9.1.11):

  • Connect to your TNG Database
  • Click the "Function" button located on the main toolbar
  • Click the "New Function" button
Navicat for MySql Create Stored Procedure

Enter the following code under the Definition tab:


Stored Procedure Code


Your screen should now look like the following:


Navicat for MySQL Stored Procedure Definition


In Navicat for MySQL enter the parameters, each separated with a comma, into the parameter box located below the Stored Procedure definition window :


Navicat for MySQL Parameter Entry


Ok, now let’s step through the Stored Procedure definition code and explain what it does:

Like triggers MySQL stored procedures must start with a BEGIN statement and end with an END statement

BEGIN

.procedure code goes here

END


The first section of our procedure code defines local variables, their data type and size :


Declare Variables


We are using these variables to store additional values from the tng_people table that will eventually end up in our tng_place_audit table.


The next section includes a typical SQL SELECT statement that retrieves values from the tng_people table and populates the above variables with these values.


Retrieve Values


Most importantly take note of the WHERE clause in the above SELECT statement: WHERE ID = id_value;

id_value is one of our inbound parameters defined above and its value is being passed from the AFTER UPDATE TRIGGER implemented in Step 2:

For birthplace updates : CALL sp_place_audit(OLD.ID, NEW.birthplace, OLD.birthplace, 'birth');

For deathplace updates : CALL sp_place_audit(OLD.ID, NEW.deathplace, OLD.deathplace, 'death');

The OLD.ID is the existing ID of the row where the birthplace and deathplace records are being updated. The value of OLD.ID is being passed to our stored procedure as the inbound parameter id_value.


In the next section we are defining whether the user has entered new data into the birthplace and deathplace fields or just updating existing data.


Check for new or updates


The old_value has already been defined as one of our inbound parameters so it receives its value from our trigger in Step 2 as well:

For birthplace updates CALL sp_place_audit(OLD.ID, NEW.birthplace, OLD.birthplace, 'birth');

For deathplace updates CALL sp_place_audit(OLD.ID, NEW.deathplace, OLD.deathplace, 'death');

OLD.birthplace and OLD.deathplace contain the existing data from these columns in the tng_people table.


The above IF statement checks the value of the inbound old_value.


If old_value is null or empty then we know that no data existed in this field before the update trigger was fired, so our user must be entering new data into either the birthplace or deathplace field in the tng_people table, so we can SET vAction = ‘new’

If the old_value is not null or empty, then old_value must hold the existing value from either the birthplace or deathplace field, so our user must be changing existing data in either the birthplace or deathplace field, so we can SET vAction = ‘update’


Depending on the above the value of the local variable vAction, is set to either ‘new’ or ‘update’.


In the next section we will now insert all values into the tng_place_audit table using a typical SQL INSERT statement:


SQL Insert


Let’s review our values being inserted in the above SQL and determine where they are populated from :

Variables Type Values Received From
VPid Local Variable Stored Procedure - SELECT Statement - tng_people.personID
event_value Inbound Parameter Trigger tr_update - With a SET value of either birth or death
old_value Inbound Parameter Trigger tr_update - OLD.birthplace or OLD.deathplace data
new_value Inbound Parameter Trigger tr_update - NEW.birthplace or NEW.deathplace data
vGedcom Local Variable Stored Procedure - SELECT Statement - tng_people.gedcom
vChangedby Local Variable Stored Procedure - SELECT Statement - tng_people.changedby
vChangedate Local Variable Stored Procedure - SELECT Statement - tng_people.changedate
vAction Local Variable Stored Procedure - With a SET value of either new or update


Once users start making modifications to the birthplace and deathplace data, the trigger will be fired, calling our stored procedure, and existing and updated birth and death place values will be captured and stored in the tng_place_audit table:

tng_place_audit table


Step 4. Creating an Audit Report

The tng_place_audit table can be joined with other TNG tables to create audit reports to track changes made to the birthplace and deathplace fields.


To Report latest Birth & Death Place changes by a particular user:

SELECT a.persfamID AS ID,
CONCAT(p.firstname,' ',p.lastname) AS Name,
a.event AS Event_Type,
a.action AS Action,
a.old_place AS Old_Place_Detail,
a.new_place AS New_Place_Detail,
a.changedby As User,
a.modified As Change_Date
FROM tng_place_audit a
INNER JOIN tng_people p ON a.persfamID = p.personID AND a.gedcom = p.gedcom
WHERE a.changedby = 'username' AND a.gedcom = 'yourtreename'
ORDER BY a.modified DESC;

Note: Change username to a user name in your database and yourtreename to the name of your actual tree.


To Report Most Recently Entered New Birth & Death Places:

SELECT a.persfamID AS ID,
CONCAT(p.firstname,' ',p.lastname) AS Name,
a.event AS Event_Type,
a.new_place AS New_Place_Detail,
a.changedby As User,
a.modified As Change_Date
FROM tng_place_audit a
INNER JOIN tng_people p ON a.persfamID = p.personID AND a.gedcom = p.gedcom
WHERE a.action = 'new' AND a.gedcom = 'yourtreename'
ORDER BY a.modified DESC;

Note: Change yourtreename to the name of your actual tree.

Summary

Though I have limited the after update trigger to only two place columns, it could be easily expanded to include all place columns from the tng_people table. By implementing triggers and stored procedures one can add additional advanced functionality to the database.


A few things to remember if deciding to use triggers:

  • Triggers should implemented by experienced users only.
  • Check you have been granted privileges to execute triggers on your database (not all hosts allow it).
  • Keep trigger action to a minimum to avoid database performance issues.
  • Always fully test triggers in a test environment before implementing on a public site.
  • Sites with a high number of active contributors and editors should carefully access and monitor the effect of triggers on overall database and site performance.
  • Review all future TNG updates carefully to determine if they will affect any existing triggers or stored procedures.
  • Most importantly never use a trigger on one table to execute a trigger on another table. Cascading triggers are a bad design technique and a dangerous threat to database integrity.
  • Triggers are still relatively new to MySQL, implement triggers at your own risk!.


The following site administrators should avoid the use of triggers on their database:

  • Sites that frequently update their database with full gedcom imports.


This guide was produced & tested using the following software:

  • Navicat for MySQL Version 9.1.11
  • MySQL Version 5.5.27 and Version 5.1.70
  • phpMyAdmin Version 3.5.8 and Version 3.5.2.2
  • TNG 9.2.2


--Mark 05:07, 10 July 2013 (CDT)