About a week ago we released a new component for Joomla named JAudit.
Running upon Joomla 2.5 and Joomla 3.x this is a comprehensive auditing component that makes use of the underlying database to log changes made to the Joomla tables. It does not require any changes to Joomla core code and should work without impacting any Joomla functionality. The component requires that the system has been granted the appropriate database privilege to create database triggers.
We have long believed that if you are using a fully featured database upon your Joomla site it seems ‘silly’ to not make use of the features that the database provides. Databases these days are very feature rich and it is only necessary to look at the possibilities of these features to realise just how much one is missing out upon. One analogy that brings this home, might be ‘Would you pay for a Ferrari and only drive it around in first gear?’
Unfortunately some hosting providers either do not understand databases or are unwilling to allow their users to make fuller use of the database features, and restrict what they are able to do. This is also one of the reasons why most Joomla components do not make the best use of the underlying database instead relying upon the basic structure of a set of tables, with a primary key on each, usually based upon an integer value. Things such as ‘views’, ‘procedures’, ‘functions’, etc. are totally alien concepts to many.
Our component uses one of the database features known as database triggers, making it possible to ‘capture’ changes made upon database tables and ‘save’ these ‘changes’ for later analysis.
A database trigger is best described as procedural code that is automatically executed in response to certain events on a particular table (or view) in a database. A trigger is often used for maintaining the integrity of the information on the database. Examples of this type of usage would be something like when a new record (representing a new worker) is added to the employees table, a trigger could be used to also automatically create new records in the tables that represent taxes, vacations and salaries for the new employee. Database triggers are supported by most (all?) databases that we have used over the years.
MySQL the most commonly used database by Joomla has supported database triggers since release 5.0.2, so it is hardly something new. MySQL supports three trigger types:
- Insert Trigger
- Update Trigger
- Delete Trigger
There are some restriction placed there by MySQL which are that it
- allows only one trigger of each type on each table (i.e. one before insert, one after insert, one before update, one after update, one before delete and one after delete).
- MySQL triggers do NOT fire outside of a statement (i.e. API's, foreign key cascades)
The component only possesses a back end administrative interface. There is no (current) requirement for any front end interaction defined. The whole purpose of an auditing package is to record changes and make then available to an authorised and approved individual or role for review and for any appropriate action to be initiated if required.
As befits an auditing package, no editing of the change records is permitted even by Joomla administrators. Be aware though that people with direct database access can still make changes to audit records, so protection of the underlying database access has to be ensured to prevent these types of changes from occurring.
The real work of the component is performed by the underlying database (MySQL), with the Joomla interface serving to initially create the required database procedural code, and to provide a viewing mechanism.
This initial release does not permit any changing in the generated triggers which could easily occur if they are modified incorrectly and may well cause performance problems. An error in a trigger may prevent an item from being saved in a table for example. This restriction is expected to be lifted in later versions.
undefined