FAQ #37 - How to create audit trail records using ADFBC, Pt. 2

Introduction

In this second part of creating historical audit trail records using ADFBC we will see how to integrate, in a generic way, the stored procedures introduced in part 1 of these series. For more information on the database schema and stored procedures that are required to support this technique refer to FAQ #36 - How to create audit trail records using ADFBC, Pt. 1.

Main Theme

As explained in part 1 (FAQ #36 - How to create audit trail records using ADFBC, Pt. 1), historical audit trails are generated by calling the stored procedures AUDIT_TABLE and AUDIT_DETAILS. Both of these procedures are part of the AUDIT_PKG database package.

The AUDIT_TABLE procedure is called to create a historical audit trail of the corresponding table. As explained in part 1, corresponding history tables are created for each table being audited. These history tables conform to the following naming convention: table_name_HISTORY where table_name is the table being audited.  For example the corresponding history REGIONS table is called REGIONS_HISTORY.

To create a generic implementation, we will need to create framework extension classes for both the oracle.jbo.server.EntityImpl and oracle.jbo.server.ApplicationModuleImpl classes. Once these framework extension classes are created ensure that the ADFBC project settings are changed to reflect this fact. You do this by bringing up the Project Properties dialog and selecting ADF Business Components | Base Classes as it is shown in the picture below:

The next step is to override the EntityImpl doDML() method for the EntityImpl extension framework class and add the following code to it:

Note a few things about this overridden doDML() method:

1. We call getEntityDef().getProperty(DISABLE_AUDIT_PROPERTY) to determine whether auditing for the specific entity object has been disabled. This is controlled by the presence of an entity object property identified by the constant DISABLE_AUDIT_PROPERTY. As long as this property has not been defined in the entity object, auditing is enabled by default.
2. We call getEntityDef().getSourceType() to determine the source type of the entity object. We will audit entity objects based on database tables only. This is done by comparing the return value of getEntityDef().getSourceType() to DBOBJ_TYPE_TABLE. DBOBJ_TYPE_TABLE is a constant defined in oracle.jbo.server.EntityDefImpl indicating that the entity object is based on a database table.
3. The first parameter required by the AUDIT_TABLE stored procedure is the user's current session identifier. This can be retrieved by calling SessionImpl.findOrCreateSessionContextManager().getCurrentSession().getId(). Take a look at this post for information on this: Bit #39 - Retrieving the current session id at the ADFBC layer.
4. The second parameter passed to AUDIT_TABLE stored procedure is the commit identifier. We would like to keep track changes for each commit done in the same user session. We retrieve the commit identifier from a database sequence by calling the helper getCommitId() (see source code below).
5. We pass the DML operation as a string (INSERT, UPDATE or DELETE) based on the value of the operation parameter passed to doDML() by the ADFBC framework.
6. We call getEntityDef().getSource() to get the name of the table associated with the entity object.
7. Finally we call the getEntityId() helper (see code below) to get the entity object's primary key. Note that we convert the primary key to a string before passing as a parameter to the AUDIT_TABLE stored procedure.

Here is the code for the getCommitId() and getEntityId() helpers:

Notice a couple of things about these helper methods:

1. In getEntityId() we iterate through the entity object attributes in order to determine the primary key attribute. We do this by first calling getStructureDef().getAttributeDefs() to get the attribute definitions and then by calling isPrimaryKey() for each attribute to determine if it is a primary key attribute. If it is, we call getAttribute() to retrieve and return its value. Notice that you will have to expand this implementation if you have an entity object with a composite primary key.
2. In getCommitId() we add the commit identifier returned from the database sequence to the session user data by calling getDBTransaction().getSession().getUserData(). We do this because we will need the same commit identifier at a later stage when calling the AUDIT_DETAILS stored procedure.

As explained in part 1 of this post, to create the specific details of the audit trail we need to call AUDIT_PKG.AUDIT_DETAILS stored procedure. To do this, override the afterCommit() in the ApplicationModuleImpl framework extension class and add the following code to it:

Notice in this case how we call getSession().getUserData().get(ExtEntityImpl.COMMIT_ID) to get the same commit identifier and pass it as a parameter to the AUDIT_DETAILS stored procedure. Also, notice that we remove the commit identifier from the session user data once we are done with calling the stored procedure.

Conclusion

In this two-part series we've seen a custom implementation of creating a historical audit trail based on a stored procedure implementation in the database. Notice that this technique is fairly generic as it does not require any additional implementation in the specific entity objects used throughout the application. As mentioned above, auditing is enabled by default and can be disabled by the presence of a property defined in the entity object. This can be easily reversed so that auditing is not done by default. If you found this useful, let me know so I can email you or post the source code as well.

Until the next time, have fun JDeveloping!

FAQ #36 - How to create audit trail records using ADFBC, Pt. 1

Introduction

ADFBC allows for the creation of historical audit trail records at the entity object level via the use of history columns. By default the framework allows for keeping track of historical information for attributes indicated as Created On, Created By, Modified On, Modified By and Version Number. This is explained in section 4.10.12 How to Track Created and Modified Dates Using the History Column of the Fusion Developer's Guide for Oracle Application Development Framework 11g Release 2 guide. The guide also explains how to create your own custom history types in section 4.16 Creating New History Types. In this post we will detail a generic technique to create historical audit trail records using a PL/SQL stored procedure in the database. For this we will use the HR schema and demonstrate how to create an audit trail for the REGIONS and COUNTRIES tables.

Main Theme

Schema changes

Before you start on implementing an audit trail in your ADF application you need to decide on what specific information you need to create a historical trail on. This might include creating audit trail information for specific tables and/or specific columns in these tables. For the sake of this post, I will demonstrate how to create historical information for the REGIONS and COUNTRIES tables in the HR schema. For this purpose you will need to create corresponding REGIONS_HISTORY and COUNTRIES_HISTORY tables. These tables will hold the historic information. Here is a structure of the REGIONS_HISTORY table used for this implementation:

This table holds the historical data (columns REGION_ID and REGION_NAME) and information related to audit trail such as the user session that created the audit trail (SESSION_ID), the commit number within the session (COMMIT_ID), the type of audit operation, i.e. INSERT, UPDATE, DELETE (OPERATION) and a link to the HISTORY table (HISTORY_ID). The HISTORY table provides the ability to browse for the complete audit trail and its structure is shown below:

The table holds such information as the date/time of the specific audit trail, the user that has created the audit trail and the user session identifier. For each HISTORY record there are a number of history lines that detail the specific changes. This detailed information is stored in a table called HISTORY_LINE. The structure of the HISTORY_LINE table is shown below:

As you can see, this table holds audit trail information specific for each change done to the table being audited. The information is kept in this table on a per session commit basis, i.e. for each commit done in the user session. Some of the columns of interest are the following:

• HISTORY_TABLE - The name of the table that was audited
• FIELD_NAME - The name of the column with the HISTORY_TABLE that was audited
• OPERATION - The operation on the column, i.e. INSERT, UPDATE, DELETE
• VALUE_OLD - The original column data value
• VALUE_NEW - The new column data value

Given the information in the HISTORY_LINE table we will be able to link to the corresponding history table (REGIONS_HISTORY, COUNTRIES_HISTORY, etc.) and be able to present a "picture" of what the data in the table looked like prior to the change. We can then compare it with the latest data in the corresponding table (REGIONS, COUNTRIES, etc.). Here an example of what the data in the HISTORY_LINE table might look like:

PL/SQL support

Historical audit trail information is created by calling two separate PL/SQL procedures called AUDIT_TABLE and AUDIT_DETAILS. These procedures have been implemented as part of the AUDIT_PKG in the database. AUDIT_TABLE is called to create the corresponding _HISTORY (REGIONS_HISTORY, COUNTRIES_HISTORY) table row while AUDIT_DETAILS is called to create the HISTORY and HISTORY_LINE rows. This is what the AUDIT_TABLE procedure looks like:

The code uses dynamic SQL to call the specific table audit procedures. The AUDIT_PKG package declares an array called tables_to_audit which defines the specific tables to audit. If the table is to be audited it must be added to this array. Here is its declaration for this example:

Part of this implementation requires that you provide the specific table audit procedures. These procedures are called dynamically by AUDIT_TABLE as mentioned above. The specific audit procedures must conform to the following naming format: AUDIT_table_name, where table_name is the table being audited. For the REGIONS table for instance, the procedure should be called AUDIT_REGIONS. Here is an example implementation of AUDIT_REGIONS:

The AUDIT_PKG.AUDIT_TABLE procedure will be called from the ADFBC layer to create an audit record for the specific table. The procedure is expecting the current session identifier, a commit identifier, the audit operation (INSERT, UPDATE, DELETE), the table name being audited and the table's primary key. Since the table's primary key is specified as a VARCHAR2 type, it will need to converted to this type if it is of different type. In this case also note the limitation of creating an audit trail for a table that uses a composite primary key. We will seen in part 2 of this post how this is done.

As mentioned earlier, the AUDIT_DETAILS procedure creates or locates the HISTORY record for the specific user session and uses dynamic SQL to create the individual HISTORY_LINE rows. It is shown below:

In this case you will also need to implement the specific audit details procedures to generate the HISTORY_LINE rows for the columns that were affected for each specific table. These procedures must conform to the following naming format since they are dynamically called: AUDIT_table_name_DETAILS, where table_name is the table being audited. Here is an example implementation of the AUDIT_REGIONS_DETAILS:

These procedures determine the differences between the original table (i.e. REGIONS) and the _HISTORY table (i.e. REGIONS_HISTORY) that have occurred for the specific commit session and produce the data in the HISTORY_LINE table.

The AUDIT_PKG.AUDIT_DETAILS procedure will be called from the ADFBC layer specifying the user's session identifier, the commit identifier and the user name. We will see this in the second part of this post.

Conclusion

We will conclude in the next part where we will see how to hook up this implementation to the ADFBC framework. We will do this in a generic way that will require minimal changes only to framework extension classes.

Until then, enjoy JDeveloping!