Implementing Auditing Using a Separate Table

In the example Overriding the Edit Object we saw how to write edits on a database view to its underlying table.

In a similar mode it's possible to attach an audit edit configuration item to an existing edit configuration item and have write attribute values from the parent edit configuration table, and additional computed values, written to an associated (audit) table.

See the public.graffiti table is defined in Database Objects.

A new table, public.graffiti_audit is defined as follows:

DROP TABLE IF EXISTS public.graffiti_audit;

CREATE TABLE public.graffiti_audit (
  ID            serial not null primary key,
  GRAFFITIID    int, -- foreign key to graffiti table's id column
  DESCRIPTION   text,
  STATUS        varchar(100),
  FIELDVERIFIED varchar(5),
  REPORTEDBY    varchar(100),
  REPORTEDON    timestamp,
  CREATEDBY     varchar(100),
  CREATEDON     timestamp,
  MODIFIEDBY    varchar(100),
  MODIFIEDON    timestamp,
  GEOM          text -- at the moment this is text, not actual Geometry, in WKT format
);

An edit configuration for the base public.graffititable is as follows.

<edit:config id="audit.table.edit">
  <entity>graffiti</entity>
  <label>Grafitti (Audit Table)</label>
  <description>Report graffiti for removal</description>
  <geometry>
    <point minimum="1" maximum="1"/>
  </geometry>
  <parameter id="id" hidden="true" label="Id" column="id" value="auto()"/>
  <parameter id="description" label="Description" column="description" controlType="textarea"/>
  	<parameter id="status">
  <label>Status (List)</label>
  <controlType>listbox</controlType>
  <column>status</column>
  <defaultValue>N</defaultValue>
  <list value="N" label="New"/>
  <list value="V" label="Verified"/>
  <list value="S" label="Scheduled"/>
  <list value="R" label="Removed"/>
</parameter>
<parameter id="fieldverified">
    <label>Field Verified</label>
    <controlType>checkbox</controlType>
    <column>fieldverified</column>
    <trueValue>Y</trueValue>
    <falseValue>N</falseValue>
  </parameter>
<parameter id="reportedby">
  <label>Reporter</label>
  <controlType>list-box</controlType>
  <dataSet>lu.staff</dataSet>
  <allowNewValues>true</allowNewValues>
  <column>reportedby</column>
  <valuecolumn>name</valuecolumn>	
  <labelcolumn>name</labelcolumn>
</parameter>
  <parameter id="reportedon">
      <hidden>true</hidden>
      <column>reportedon</column>
      <value>datetime()</value>
      <updatable>true</updatable>
  </parameter>
</edit:config>

The following edit:audit edit configuration implements auditing of the public.graffiti table which enables the writing of values to the public.graffiti_audit table.

The configuration requires that the table and the datasourcewhere it is located be defined.

An optional list of parameters that correspond to the columns in that table can also be defined.

The parameter columns can derive their values either from the values entered by the user as a parameter in the original audit.edit configuration, or as a formula using a value tag or as a hard coded value.

The mode tag describes which actions cause an audit record to be created. The modetag is specified as a comma separated list containing up to three possible values create, update or delete. If mode was set as follows,<mode>create</mode>, an audit record would only be written when a new entity is created. The default, if not specified, is <mode>create,update,delete</mode>.

<edit:audit id="audit.audit">
    <mode>create,update,delete</mode> 
    <edit>audit.edit</edit>
    <datasource>ds.postgresql</datasource>
    <table>public.graffiti_audit</table>
	<parameter name="id" 			  column="graffitiid" value="id()"/>
    <parameter column="description"   parameter="description"/>
    <parameter column="status"        parameter="status"/>
    <parameter column="fieldverified" parameter="fieldverified"/>	
    <parameter column="reportedby"    parameter="reportedby"/>
    <parameter column="reportedon"    parameter="reportedon"/>
    <parameter column="createdby"     value="userid()"/>
    <parameter column="createdon"     value="datetime()"/>
    <parameter column="geom"          value="geometry()"/> <!-- at the moment this is text, not actual Geometry, in WKT format -->
</edit:audit>

Parameters in edit configurations can be setup with or without a column attribute. If there is a column specified then the value will be written to the corresponding column in the underlying spatial table, if there is no column specified then no value will be written during this phase. However, the value the user enters for the parameter, in both cases, is available to be written as part of an audit configuration.

Creating a new point record in the public.graffiti table when selecting the audit.edit configuration is as follows:

New Graffiti Point Near a Tree

The following records are written to the database.

Graffiti Table

Graffiti Table Record

Graffiti Audit Table

Note that the graffitiid column value is the same as the id value in the base graffiti table.

Currently only writing new records to the audit table is supported: existing audit records cannot be updated.

However, if you edit an existing base table record, as in the following example where Fred Flintstone is changed to Wilma Flintstone, a new audit record is written.

A new record is written to the public.graffiti_audit table:

<< Previous

Next >>