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!

FAQ #35 - How to set default values for View object row attributes

Introduction

In this post we will see how to set default values to View object attributes. There are a number of places where you can do this:

• In the overridden View object row create() method
• Declaratively using a Groovy expression
• In the attribute getter method

To demonstrate each case, consider the use case of setting the employee’s hire date to the current date for a newly created View object row.

Main Theme

Setting default attribute values in the overriden ViewImpl create()

To set the default value for a View object attribute in the overridden create() method, follow these steps:

• Create a View Row custom Java implementation class for the Employees View object.
• Open the EmployeesRowImpl.java custom View Row Java implementation class and override the create() method using the Override Methods… button – the green left arrow on the editor toolbar.
• To set the default employee’s hire date to today’s date, add the following code to create() immediately after the call to super.create():

              // set the default hire date to today
             this.setHireDate((Date)Date.getCurrentDate());

Setting default attribute values with a Groovy expression

To set the attribute default value using a Groovy expression, follow these steps:

• Open the Employees View object definition and go to the Attributes page.
• Select the attribute that you want to initialize – HireDate in this case, and click on the Edit selected attribute(s) button (the pen icon).
• On the Edit Attribute dialog select the View Attribute node.
• Select Expression for the Value Type radio button in the Attribute section.
• Enter the following Groovy expression in the Value field: adf.currentDate

Returning a default value from the attribute getter

Another way to set a default value for a View object attribute is in the attribute getter method. Follow these steps to set the default employee hire date:

• Locate the View object attribute getter in the View object custom row implementation class. In this example is the getHireDate() method in EmployeesRowImpl.java.
• Replace the existing code in getHireDate() with the following:

          // get the HireDate attribute value
          Date hireDate = (Date)getAttributeInternal(HIREDATE);
          // check for null and return today's date if needed
          return (hireDate == null) ? (Date)Date.getCurrentDate() : hireDate;

Note that using this technique we don’t actually set the attribute value; rather we return a default value, which can be subsequently applied to the attribute. Also notice that this is done only if the attribute does not already have a value (the check for null).

Actually setting an attribute value in a getter is not a recommended practice.

Attribute dependency

A common use case related to this topic is setting an attribute’s value based on the value of another related attribute. Consider for instance the use case where the employee’s commission should be set to a certain default value if the employee is part of the Sales department. Also consider the case where the employee’s commission should be cleared if the employee is not part of the Sales department. In addition to accomplishing this task with Groovy as stated earlier, it can also be implemented in the employee’s DepartmentId setter, i.e. in the setDepartmentId() method as it is shown below:

    public void setDepartmentId(Number value) {
        // set the department identifier
        setAttributeInternal(DEPARTMENTID, value);
        // set employee's commission based on employee's department
        try {
            // check for Sales department
            if (value != null && SALES_DEPARTMENT_ID == value.intValue()) {
                // if the commission has not been set yet
                if (this.getCommissionPct() == null) {
                    // set commission to default
                    this.setCommissionPct(new Number(DEFAULT_COMMISSION));
                }
            } else {
                // clear commission for non Sales department
                this.setCommissionPct(null);
            }
        } catch (SQLException e) {
            // log the exception
            LOGGER.severe(e);
        }
    }

Conclusion

As you can see, there are a number of different ways for initializing your View object row attributes. Which one you choose depends on the specific use case at hand. It is common on large projects that a combination of all (and more) is used. Setting a standard approach could be appropriate in this case.

Until the next time, keep on JDeveloping!

FAQ #34 - How to utilize WebLogic Work Managers for long-running ADF processes, Pt. 1

Introduction

The WebLogic Work Manager functionality along with its supporting Java API - CommonJ, can greatly improve performance for those long running processes in your ADF applications, especially for those notorious ones - you know, the one that cause "stuck" threads? This is true because these long running processes can now run asynchronously on a specific Work Manager that you define, one that is configured to ignore "stuck" threads. Let's take a look.


Main Theme

Adding and configuring a Work Manager in WebLogic

You define a Work Manager in WebLogic by selecting Work Managers under the Environment node in the Domain Structure window and clicking New on the Summary of Work Managers page on the right on the Global Work Managers, Request Classes and Constraints table.

On the Select Work Manager Definition type page select Work Manager and click Next.

On the Work Manager Properties page enter the name of the Work Manager and press Next.

On the Select deployment targets page select the Work Manager targets and click Finish to complete the creation of the Work Manager.

The Work Manager will now appear in the Summary of Work Managers table. Click on it to bring up the Configuration page. In it select Ignore Stuck Threads and click Save. This will configure the Work Manager to ignore "stuck" threads for long running processes. We won't be changing the other configuration settings for this Work Manager.

Configuring the Work Manager for your application

To configure the Work Manager for your application, edit the web.xml file and enter the following resource reference:

<resource-ref>
   <res-ref-name>wm/TestWorkManager</res-ref-name>
   <res-type>commonj.work.WorkManager</res-type>
   <res-auth>Container</res-auth>
   <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>


This is necessary so that you will be able to access the Work Manager programmatically as it shown below.

Programmatically submitting works to the Work Manager


Here is an example of a long running process in an ADF application and a good candidate to produce a "stuck" thread:

    public void selectAll() {
          RowSetIterator iterator = getMyViewObject().createRowSetIterator(null);

          iterator.reset();
         
          // loop over 1,000,000 records...
          while (iterator.hasNext()) {
              MyViewObject myViewObject = (MyViewObject)iterator.next();
              myViewObject.setProcessed(true);
          }
          iterator.closeRowSetIterator();
    }

This is a method in the Application Module that loops over a large number of records - possibly millions - in order to set some boolean flag. Below we will re-write the code using the Work Manager Java API CommonJ.

First you will need to add support for the CommonJ API in your ADF application. The CommonJ API is located in the weblogic.jar library, so use the Libraries and Classpath option in the Project Properties and add it. The library can be found in the wlserver_10.3/server/lib directory in your WebLogic installation directory.

Now modify the method above to the following:

        public void selectAll() {
        try {
          //get Work Manager from local JNDI
          InitialContext ctx = new InitialContext();
          WorkManager workManager = (WorkManager)ctx.lookup("java:comp/env/wm/TestWorkManager");
          //create instance of Work and WorkListener implementations
          Work work = new SelectAllWorkImpl(getMyViewObject().createRowSetIterator(null));
          WorkListener workListener=new SelectAllWorkListener();
          //create work item for execution
          WorkItem workItem = workManager.schedule(work, workListener);
          //create list of WorkItems that we want to execute
          List workItemList=new ArrayList();
          workItemList.add(workItem);
          //run the work items in parallel; don't wait
          workManager.waitForAll(workItemList, WorkManager.IMMEDIATE);
          //check results; use only if you are blocking
          //SelectAllWorkImpl workImpl= (SelectAllWorkImpl)workItem.getResult();
        } catch (NamingException e) {
        } catch (WorkException e) {
        } catch (InterruptedException e) {
        }
    }



Conclusion

In this first installment of discovering Work Managers in WebLogic, we've seen how to create one and how to configure it and use it in your application. We will wrap up this topic on the next installment. There we will explain the code above and provide the implementations of Work and WorkListener interfaces. This is indeed a promising feature that should help in certain cases where long running processes can cause "stuck" threads.

Until the next time, keep on JDeveloping!