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!

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 #21 - How to passivate/activate ADF Application Module class instance variables

Introduction

If you are using instance variables in your Application Module (AM) class, as part of some algorithm in your application and you expect these variables to persist from one user request to another so that the algorithm works as expected, passivation (and activation) support for these variables must be added. This FAQ will show you how to do this and at the same time build a "mini sub-framework" to avoid duplication of the basic passivation-activation code that you must write for all your AMs in your project.


Main Theme

Some Introductory Information

Application Module pooling is the process of acquiring an AM from and releasing it back to a pool. This pool of AMs contains a limited number of AMs defined in a configuration file. When all AMs have been associated with user sessions, an already-used AM must be re-used. For this to happen, the existing data associated with the AM must be saved. The passivation process saves information used by the AM into the passivation store -  usually a database, while the activation process does exactly the opposite, restores the state of the  AM from the passivation store. The information is saved as an XML document. Take a look at FAQ #14 - How to test an Application Module for activation-safe in ADF 11g for additional information regarding the passivation and activation processes.

You can add custom passivation and activation logic in your AM class by overriding the ApplicationModuleImpl methods passivateState() and activateState() respectively. The passivateState() method should create the necessary XML elements for each AM instance variable that you need to passivate. The activateState()  method should detect the specific XML elements that identify the instance variables in the passivated XML document and restore them back into the instance variables.


A "mini sub-framework" for passivating/activating Application Module instance variables

If you are using multiple AMs, you can avoid code duplication of this basic implementation of the passivateState() and activateState() methods by putting it in a base AM class. Then derive all of your other AMs from this base AM. The base AM class can then provide activation/passivation "triggers" to which the derived AMs can respond. The implementation of passivateState()  in the base AM should look like this:

As you can see, after calling super.passivateState() to allow the framework to do its own processing, we call onStartPassivation() to signal derived AMs that a passivation process is taking place. Derived AMs should override onStartPassivation() in order to provide a list of the identifiers - each identifier tied to a specific instance variable - to be passivated. Below is an example implementation of onStartPassivation() by a derived AM class. 

In this case a single passivation identifier call EMPLOYEE_LIST_ID is returned to indicate a list of Employees class instance variable.

passivateState() then loops for all passivation identifiers, creates an XML node for each identifier in the XML passivation document and calls onPassivate() passing the passivation identifier as an argument to it. A  derived AM will override this method in order to return the passivation data for the specific passivation identifier. An example implementation is shown below:

In this case the passivation identifier is checked and if it matches the expected identifier, the passivation data is supplied. 

Finally, passivateState() calls onEndPassivation()  to indicate the end of the passivation process.

The reverse process of activation is similar. activateState() calls onStartActivation() to get the activation identifiers. It then loops looking for each activation identifier in the activated XML document. If the identifier is found, calls onActivate() supplying the activation data along with its activation identifier as arguments. The derived AM can identify the instance variable being activated - based on the activation identifier, and restore it.

To test the framework, bring up the Edit Business Components Configuration dialog and  disable Application Module Pooling as it shown below. You do this by right-clicking on the AM in the Application Navigator and selecting Configurations... Then you click on Edit... to edit the specific configuration.

 This will in effect initiate a passivation-activation cycle for each request when the application is running. Finally save the configuration, enable debug logging on the console and run the application. You should see your AM class instance variables being activated.

To further explore this framework, the source code is supplied below.

Conclusion

If you are using class instance variables in the BC layer and you want their values to persit from one request to another, then passivating-activating these variables becomes necessary. The ADF BC framework allows you to do this by overriding the passivateState() and activateState() methods.

Until the next time, keep on JDeveloping!

Code

http://jdeveloperfaq.googlecode.com/files/JDeveloperFAQNo21.rar

FAQ #20 - How to refactor ADF components in JDeveloper

Introduction

When it comes to refactoring ADF components in JDeveloper, most ADF developers come to know the refactoring features when they rename, move or delete Business Components (BC) objects. Yet fewer know that additional options exist in JDeveloper to refactor object attributes, attribute domain types, bindings, JSF pages and more.


Main Theme

Refactoring ADF BC objects

Refactoring ADF BC objects is available through the Rename... and Move... menu selections of  the Refactor menu. You can display the Refactor menu when you right-click on the BC component in the Application Navigation window.

If you choose to rename a component, JDeveloper will present a Rename dialog. In this dialog you can specify the new name for the object and choose to preview the changes that will be generated by the refactoring process.

It is a good idea to preview the changes, when the Preview option is available. When you click Preview in this case,  a Rename Log window will open listing all the changes that will be made by the refactoring process.

You can browse through the changes by clicking the Synchronize with Editor button This is a toggle button. It will open the applicable source file as you click through the changes in the Log window. When you are done reviewing the changes, click on the Do Refactoring button to apply the changes. If you decide for whatever reason not to go ahead with the refactoring, just close the Rename Log window.

Choose Move... from the Refactor menu to move the BC object to another package. This will present a Move dialog, where you can specify the new package name for the object to be moved.

Deleting a BC object is not available under the Refactor menu. It is available as a separate Delete menu option in the same context menu.

When you select Delete to delete a BC object,  a Confirm Delete dialog will be presented. If the BC object you are about to delete is used throughout the project, a Show Usages button will be also be available in the  Confirm Delete dialog and a Log window will open displaying all of the object's usages.

To view the object's usages on the Confirm Delete dialog itself, click on the Show Usages button.

To individually inspect each object usage, first click No to dismiss the Confirm Delete dialog. Then in the Log window click the Synchronize with Editor toggle button  and browse through the object's usages. As you browse through the object's usages, the corresponding source file will be opened and you will be positioned exactly where the object is being referenced in the source.

 Refactoring ADF BC Object Attributes

Refactoring ADF BC object attributes allows you to rename, delete and change the domain type of a BC object's attribute. Moving an attribute is not supported. To refactor an attribute, bring-up the object's overview editor by double-clicking on the object in the Application Navigator window and select the Attributes pane. Then right-click on the attribute and select Rename... or Delete from the context menu that is displayed.

In both cases - renaming or deleting the attribute, you can preview the usages - as described earlier before proceeding with the refactoring process. Consider also selecting Find Usages... from the context menu to review the attribute usages without doing any refactoring.

In addition to renaming and deleting an attribute, you can also refactor the attribute domain type by selecting Change Type.. from the context menu. This will display a Change type dialog from where you can select the new domain type for the attribute. You can also choose to preview the changes in the Change type Log window. This window will open when you select Preview and click OK.

Refactoring Data Bindings

You can refactor bound objects in the Data Binding Definition page. To do so, bring up the Page Definition File associated with a JSF page and in the Overview tab right-click on the specific binding that you want to refactor. Then choose Rename... or Delete from the Refactor menu.

Again, before you delete a binding you can verify its usages by clicking the View Usages button in the Delete Safely dialog that is displayed. This will open the Delete Safely Log window where you can review the usages. Click on the Synchronize with Editor toggle button  to bring up each usage into the source editor.

Refactoring JSF Pages

You can refactor a JSF page by right-clicking on the page in the Application Navigator and selecting Rename..., Move... or Delete from the Refactor menu. JDeveloper will update all references to the page in task flows and in the DataBindings.cpx mappings when renaming or moving the page.

Conclusion

The refactoring options in JDeveloper are quite powerful and can help substantially during the development process. They range from refactoring data components objects, object attributes, attribute domain types, data bindings, JSF pages and files. For more information about refactoring and their limitations I suggest that you check out the Fusion Developer's Guide - referenced below.

Until the next time, keep on JDeveloping!


Reference

Oracle® Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework 11g Release 1 (11.1.1) Part Number B31974-02

FAQ #15 - How to synchronize ADF Entity Objects with database changes in JDeveloper

Introduction

For ADF applications, as the database schema evolves, there will be a need to synchronize the corresponding Entity and View objects involved, to reflect the changes in the schema. This is a pretty straightforward task in JDeveloper; there are a few things to watch out however.

Main Theme

The task of synchronizing an Entity Object to its respective database table is done by simply right-clicking on the Entity Object in the Application Navigator and selecting Synchronize with Database...

Selecting to synchronize, will bring up the Synchronize with Database dialog where the actions that require synchronization, if any, will be displayed. For the HR schema, the screen below indicates that there are no synchronization actions for the Employee Entity Object since no changes were done to the EMPLOYEES table.

The consequences of the Entity and View Object synchronization differ for the following cases:

• when new fields are added to the database table
• when existing fields are removed from the database table
• when existing fields of the database table are modified

New fields are added to the database table

When new fields are added to the database table, the synchronization feature will determine the new fields and upon synchronization it will add them to the Entity Object. The screen below shows the effect of synchronizing the Employee Entity Object when a new field - MIDDLE_NAME, the employee's middle name - is added to the EMPLOYEES database table of the HR schema.

To reflect this change in the Employee Entity, you can click the Synchronize All (or Synchronize for selected changes) button. JDeveloper proceeds with the synchronization and a new attribute - MiddleName - is added to the Entity Object. This can be verified by reviewing the Attributes page of the Employee Entity Object.

In all cases, synchronization applies only to the Entity Object. The newly added attribute must be added manually to all View Objects that refer the synchronized Entity. This can be done by opening each View Object and in the Attributes page selecting Add Attribute from Entity....

This will bring up the Attributes dialog from where we can shuttle the new attribute from the Available to the Selected list. This is effect will add the new attribute to the View Object as well.

Existing fields are removed from the database table

When a field is removed from the database table, the synchronization feature will not actually remove the corresponding attribute from the Entity Object but instead will make the attribute transient. The screen below shows what happens when we synchronize the Employee Entity Object after we remove the MIDDLE_NAME field from the EMPLOYEES table.

After the synchronization process we can verify that indeed the MiddleName attribute has been made transient and remove it manually ourselves. The attribute indeed is not mapped to the removed table column. Instead it displays a "Synchronize:Fix_SQL" string in the column.

If the attribute is used somewhere else in the project, attempting to delete it will display a "Usages were found" warning message.

Clicking on the View Usages button will determine where the attribute is referenced and display these usages in the Log window. In this case it is used in the Employees View Object.

We need to ensure that we remove the attribute ourselves from everywhere else that is used in the project.


Existing fields of the database table are modified


The synchronization feature will also determine and synchronize the Entity Object for any changes to the precision of the database table fields. For example, after changing the size of the EMPLOYEE table LAST_NAME column from 25 characters to 30 and synchronizing we will see the following:

Reviewing the LastName attribute in the Employee Entity Attributes page reflects this change. The change is also propagated to all View Objects that reference the LastName attribute as it shown below:

The synchronization feature will also detect changes to a database table's column data type. In this case however, although the changes to column's data type will be reflected to the Database Column Type of the Entity Object, the change will not be reflected to the Entity Object's attribute type and you will have to make this change by yourself. To demonstrate this case we will add a VARCHAR2 column called PHONE_EXTENSION to the EMPLOYEE table and then change its data type to a NUMBER. In this case the synchronization feature determines the change in data type as it is shown below.

Reviewing the synchronization results however, we can see that although the change is reflected in the Database Column Type of the Entity Object's PhoneExtension attribute, it is not applied to the Entity's attribute type, which retains the old data type (String).

In this case we will need to make the change to the appropriate data type for the attribute ourselves. This also applies to all View Objects where the specific attribute is referenced.


Conclusion

The ADF Entity synchronization feature in JDeveloper comes pretty handy when updating your Entity Objects to reflect changes to their corresponding tables in the database schema. Be aware however, that depending on whether columns are added, deleted or modified, the synchronization feature will behave slightly different.

Until the next time, keep on JDeveloping!