Generic way to handle Soft Delete

Posted by HusainD



Using ADF 11g (should work in 10g as well)
There are many cases where we need to mark a row as deleted by setting a flag in database instead of physically deleting it. Fusion developers guide gives a way to achieve this http://download.oracle.com/docs/cd/E15051_01/web.1111/b31974/bcadveo.htm#CEGIBHBC.
However we needed to have this feature in almost all the table mandating a generic solution for soft delete. 
The following code can be downloaded from here


Heres how I approached it:
  1. Create a new History Column for SoftDelete
  2. Create Base Class for Entity Implementation Object and override 2 methods of EntityObjectImpl: remove(), doDML()
  3. Create an application to test our work

Step 1: Create a new History Column - soft delete


  • In Jdeveloper Tools > Preferences > Business Components > History Types. Add a new History Type "soft delete" with id 11 (Note that type id's 1-10 is reserved.





Step 2: Create Base EntityImpl class
  • Create a new Fusion ADF Application and add a new java class that extends oracle.jbo.server.EntityImpl.




  • In the BaseEntityImpl override the following methods:
    • remove() - To set the value of column with History type 11 to deleted value.
    • doDML() - During delete, to force an entity object to be updated instead of deleted. And during insert to default the value of column with History type 11 to active value.



    private static final byte SOFT_DELETE_TYPE = 11;

    public int getSoftDeleteColumn() {
        int colIndex = -1;
        for (AttributeDef def : getEntityDef().getAttributeDefs()) {
            if (((AttributeDefImpl)def).getHistoryKind() == SOFT_DELETE_TYPE) {
                colIndex = def.getIndex();
            }
        }
        return colIndex;
    }

    @Override
    public void remove() {
        int deleteCol = getSoftDeleteColumn();
        if(deleteCol != -1) {
            setAttribute(deleteCol, "Y");
        }

        super.remove();
    }

    @Override
    protected void doDML(int operation, TransactionEvent transactionEvent) {
        int deleteCol = getSoftDeleteColumn();
        if (EntityImpl.DML_DELETE == operation && deleteCol != -1) {
            operation = DML_UPDATE;
        }
        if(EntityImpl.DML_INSERT == operation && deleteCol != -1) {
            setAttribute(deleteCol, "N");
        }
        super.doDML(operation, transactionEvent);

    }



Step 3: Now to the good stuff - Test the base class

  • In Oracle XE HR schema add delete_flag to employees table.
    • alter table employees
      add delete_flag varchar2(1)

      update employees
      set delete_flag = 'N'


  • Set the default base entityImpl class for the application using Application > Default Project Properties.


  • Create business components (EO, VO & AM) for employees table.



  • In the EmployeesEO set the history column for "DeleteFlag" to "soft delete".


  • Run the Application Module to test insert and delete.







 

Labels:

2 comments:

It is very good and very useful for most of the common CRUD applications.


Few suggestions :

(A).if soft deleted rows are shown:

(1). make all columns nonupdateable
ex: disabled : #{bindings.DeleteFlag.inputValue=="Y"}

(2). modify the lable for "delete/remove" button for deleted rows fetched to "Reactivate", and vice versa.
ex: text: #{bindings.DeleteFlag.inputValue=="Y" ? "Reactivate" : "Delete"}

(3). add the code in the doDML method to support reactivation action
ex: toggle functionality

(B). if needed, do not fetch the soft deleted rows during query execution

Anonymous said...

November 10, 2009 at 6:41:00 AM PST  

Thanks,

I will try to incorporate some of the suggestions soon.

- Husain

HusainD said...

November 16, 2009 at 7:00:00 AM PST  

Post a Comment

Newer Post Older Post Home

Subscribe to: Post Comments (Atom)