Wednesday 20 April 2016

data model versioning in time using Oracle ADF 12c

Versioning of the applications’ source code is a stanard nowadays. Teams are working simultanously on it and the code itself is changing within the time. And, at each point of time, you can go back to one of the previous versions. The same is with business requirement and applications should be able to trace business requirements history. They should allow switch to the previous version. But also it should be done easier than a continous reimplementation. Let’s look on an example of an artificial insurance company (because of my employment history I bestow sentiment to the sector and people that I’ve been working with). It needs to struggle with several technical challenges; below there will be a solution that can be applied. The example is simplified to be just a background for technical point of view. 

Insurance premium calculation is based on client’s and car’s data. Client needs to fulfill first and last name, as for the car only a model is required. Policy creation date is saved automatically from the system date. We’re assuming also that make of the vehicle is dictionary based, which contains "Model 1" and "Model 2" values. After some period of time business analysts report a change request – there will be a new brand of the cars introduced to our market and we need to extend the dictionary with "Model 3" value. Moreover, for the statistics purposes (and to cut marketing costs at the end) we need to collect the lead source. The business is growing rapidly, the clients’ portfolio is getting bigger and bigger. But that implies also on the number of reported claims. Actuarial team using big data tools mine the data and point new risk factor. Yellow cars are appearing the most often as the accident’s perpetrators. The change request specifies a car color as a dictionary based field that must be added to the questionnaire. It’ll contain just two values: "Yellow" and "Other than yellow".

After removing business keywords from the description above it could be understood as a common product lifecycle. The product is a set of attributes, the attributes and related business rules are changing in the time. Some are added, some are removed, rules are changing – the product is evolving.

Despite appearances the change requests here are not trivial and the implementation can be error prone. From the technical point of view there can be distinguished two types of the changes. First one implies filtering of the result set basing on policy creation date. In other words it is versioning data in time. In that scenario it can be simplified to plain view criteria with binded effective date variable. Second change is not just about filtering. It is about changing the logical model of the data, database structures must be extended with another column. The mandatory constraint is conditional and depends on the date. It is about data model versioning in time. Both of the change types with related business rules can be put under one keyword – business product configuration management.

ADF framework in the declarative way supports solutions of such problems. The risk of implementation error is minimized because of the declarative approach. It must be added that data versioning challenge can be resolved with an effective date approach. That topic is widely described in the “Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework” documentation in “Creating a Business Domain Layer Using Entity Objects” chapter[1]. The way of solving the problem that I’d like to share is no using effective dates. I would like to focus mostly on the business and consider the product configuration management as a whole set of changes that may be applied during its lifecycle. Comprehensive solution should be the less error prone as possible, should minimize the costs but also should be simple to develop and flexible. 

The common accusation to the framework is that after rebuilding the Model, ViewController layer must be redeveloped is not true. ViewController must be understood as one-of-many views in the MVC pattern. Please note that you can expose your business services as SDO[2]  which also should be considered as a View layer.

So much about the introduction, now let’s go to the implementation to prove that it is really doable in an easy way. In the first step please create the database schema with a dictionary and the product tables.


CREATE USER DMV IDENTIFIED BY DMV;
GRANT ALL PRIVILEGES TO DMV;

CREATE TABLE "CAR_MODEL_DICT"
(        "ID" NUMBER NOT NULL ENABLE,
    "NAME" VARCHAR2(1024 BYTE) NOT NULL ENABLE,
    CONSTRAINT "CAR_MODEL_DICT_PK" PRIMARY KEY ("ID")
);
COMMENT ON COLUMN "CAR_MODEL_DICT"."ID" IS 'primary key';
COMMENT ON COLUMN "CAR_MODEL_DICT"."NAME" IS 'model name';

INSERT INTO "CAR_MODEL_DICT" (ID, NAME) VALUES ('1', 'Model 1');
INSERT INTO "CAR_MODEL_DICT" (ID, NAME) VALUES ('2', 'Model 2');

CREATE TABLE "MTPL_POLICY"
   (     "ID" NUMBER NOT NULL ENABLE,
         "CLIENT_NAME" VARCHAR2(1024 BYTE) NOT NULL ENABLE,
         "CAR_MODEL_FK" NUMBER NOT NULL ENABLE,
         "CREATE_DATE" DATE NOT NULL ENABLE,
          CONSTRAINT "MTPL_POLICY_PK" PRIMARY KEY ("ID"),
          CONSTRAINT "MTPL_POLICY_FK1" FOREIGN KEY ("CAR_MODEL_FK") REFERENCES "CAR_MODEL_DICT" ("ID") ENABLE
   );

COMMENT ON COLUMN "MTPL_POLICY"."ID" IS 'primary key';
COMMENT ON COLUMN "MTPL_POLICY"."CLIENT_NAME" IS 'client''s name';
COMMENT ON COLUMN "MTPL_POLICY"."CAR_MODEL_FK" IS 'car model (foreign key)';
COMMENT ON COLUMN "MTPL_POLICY"."CREATE_DATE" IS 'policy creation date';

CREATE SEQUENCE policy_pk_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

 CREATE OR REPLACE TRIGGER policy_pk_trigger
  BEFORE INSERT
  ON mtpl_policy
  FOR EACH ROW
  WHEN (new.id is null)
DECLARE
  v_id mtpl_policy.id%TYPE;
BEGIN
  SELECT policy_pk_seq.nextval INTO v_id FROM DUAL;
  :new.id := v_id;
END policy_pk_trigger;
/

INSERT INTO "MTPL_POLICY" (CLIENT_NAME, CAR_MODEL_FK, CREATE_DATE) VALUES ('First Client', '1', TO_DATE('2014-04-01', 'YYYY-MM-DD'));

In the JDeveloper 12c please create the new ADF Fusion Web application with model and viewController projects. Please use the defaults in both of the wizards.

Please use the ”Create business components from tables” wizard to create the database connection and BC4J layer (entities, view objects and application module). On the entity level, define the primary key (from the sequence) and system date as policy creation date. In MtplPolicyView editor create LOV based on CarModelDictView. Reorganize data model structure in the Application Module. 

In the viewController project create the front-end of the application – a page with the input fields and buttons for navigation between policies, save changes, and create or delete rows. Initial version of the application has been developed successfully.
























After that first change request appeared. The product configuration must be extended with new dictionary value. To distinguish result sets among different dates each row should have begun and end date defined which in the next step will be a part of effective date approach. Alternatively, and just in that case, instead of effective date you can use just plain ViewCriteria. The workload intensity will be a little bit lower. But let’s stop and think for a moment for a quick and not-real-life scenario - we know all changes that will appear in the future. The solution must be unified for both data and data model versioning changes. It must be clever and lazy – simple, flexible and declarative. Do not create new entities, view objects with complex where clauses to split the product versions, different pages and task flows to pick the best UI and so on. Business rules shouldn't be implemented in managed beans (it is against best practices, reusability is low – think about other Views like SDO based view layers). The conclusion is that the solution must be defined in the Model layer. The question is how to avoid complex if/then/else clauses in Java or Groove code in BC4J layer? 

The solution is in the “Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework” documentation: Sometimes a single database table stores information about several different kinds of logically related objects[3]. Going further: In situations where common information exists across related objects, it may be convenient to represent these different types of entity objects using an inheritance hierarchy[4]. MTPL_POLICY table stores logically related objects – insurance policies. The bigger part of the data is common across the versions, such as name, surname or car model. There are also some business differences among them – one group can handle two and other group of policies handles three models of a car, part of the rows must have color column fulfilled. From the business point of view the difference is based on the policy creation date. Time periods can between two dates can be enumerated and they can be treated as product configuration version numbers. The BC4J layer must introduce Version attribute - a number, which will be a discriminator value for inheritance purposes, for existing rows it'll be calculated by a database stored function. 

The entity level inheritance is limited with a constraint - business rules in the subtype objects cannot be less restrictive than rules defined in base object. If you look on the Updateable attribute which in the base entity will be set to “While new”, in the subtype it can be set to “While new” or “Never”. It can't be set to less restrictive “Always” value. Mandatory field in the base entity cannot be optional in the subtype (opposite approach is valid). Attribute which is mandatory starting from the non-base entity can be added in two ways:
  • new column is optional - it contains NULL value for “old” versions and NOT NULL value for “new” versions, in BC4J in the base entity it'll be optional and set to mandatory just in selected cases
  • new column is mandatory in all of the cases with default business value “not applicable”.

It is a project decision and it should be consistent across the application. Here it will be implemented first approach with tightening the rules.

Having such theoretical background there is a moment for the implementation. Please create a database stored function for version number calculation and extend the dictionary table with new Version column. The code snippet that realizes above changes can be found below:


create or replace function resolve_configuration_version(input_date date) return number as
begin
  if (input_date >= TO_DATE('2015-01-01', 'YYYY-MM-DD')) then
    return 1;
  end if;
  return 0;
end;
/

alter table mtpl_policy add (lead_source varchar2(1024 byte));
/

alter table car_model_dict add (version number default -1 not null enable);
COMMENT ON COLUMN "CAR_MODEL_DICT"."VERSION" IS 'version of product configuration';
update car_model_dict set version=0;
INSERT INTO "CAR_MODEL_DICT" (ID, NAME, VERSION) VALUES ('3', 'Model 1', 1);
INSERT INTO "CAR_MODEL_DICT" (ID, NAME, VERSION) VALUES ('4', 'Model 2', 1);
INSERT INTO "CAR_MODEL_DICT" (ID, NAME, VERSION) VALUES ('5', 'Model 3', 1);

INSERT INTO "MTPL_POLICY" (CLIENT_NAME, CAR_MODEL_FK, CREATE_DATE) VALUES ('Second Client', '3', TO_DATE('2015-05-01', 'YYYY-MM-DD'));

In the dictionary, the Version column value is equal to product configuration version number. There is no script for adding similar column to the MTPL_POLICY table. To maximize the flexibility of the solution, version won't be persisted in the database. On the entity level attribute will be calculated using resolve_configuration_version function and marked as Polymorphic Discriminator. At the moment the implementation is divided into two ways. Right click on the MtplPolicy and pick “New extended object” from the context menu. In the entity editor select Version attribute and click “Override” (it is located in the top right corner). Set the default value to 0. Additionally change in the XML source code Expression attribute in the same way as it is defined in the base entity[5]. Using the same procedure, create MtplPolicyEx_1, but there the discriminator value should be set to 1.








Next, the base entity is synchronized with the database in order to get the LEAD_SOURCE definition. Please note that after synchronizing of the base entity both subtype entities in Attributes sections already have LeadSource attribute. In MtplPolicyEx_0 entity click Override button to set field as not updateable and in the UI Hints tab attibute Display is changed to Hide. Because of that for “old” policies field won't be editable and even won't be rendered (it requires small change in the JSPX file). In MtplPolicyEx_1 LeadSource attribute will be rendered and Mandatory checkbox must be checked.







Entity part is done. Next in the stack is ViewObject level. Please open MtplPolicyView and go to "Entity objects" section. In the Selected part of the shuttle component please select MtplPolicy and click "Subtypes..." in the top right corner. In the popup window, using another shuttle component, please move to the right two subtypes created in previous steps. On the Attributes section please add two attributes from entity - Version and LeadSource. Version must be set as “Polymorphic Discriminator” with Entity type radio button set and 1 as a default version. Default would be used for newly created rows.




The last thing to do is the update of the car model dictionary to the newest version – entity must be synchronized (to get the Version attribute), CarModelDictView must expose Version attribute and new ViewCriteria must be added in order to filter dictionary values. Accessor in MtplPolicyView must apply the ViewCriteria and pass Version number to the ViewCriteria binded variable using Groove.






 
In the viewController part new field must be drag and dropped from the Data Control on the page as an input text. In the source (or using Property Inspector) please add rendered attribute, which is based on the value set in UI Hints on entity level. In order to handle rendered properly, the container above must listen on navigation buttons to partially refresh containees.

Now the application can be started and checked against business rules for two pre-defined records.







Please note that dictionary values are multiplicated. From the business point of view “Model 1” in version 0 is exactly the same as “Model 1” in version 1. The same difficulty must be handled external systems which are communicating with our domain application. Oracle Rules can be an example of such external system, business logic there can be used for automatic underwriting process. Using labels is not the best approach, there can be a lot of error prone typo errors, business partners may use uppercase labels and so on. The problem may be solved with business keys like “M1”. Database should implement the unique constraint of business key and version number pair. 

One of assumptions here is not to persist version number to maximize flexibility - the product configuration number is stored in one place, the stored PL/SQL function. Another difficulty here may be related with foreign keys. As a solution for database integrity constraint database developers can use virtual columns that will calculate unique business keys and avoid persisted data redundancy. 

Both of the considered remarks should be defined at the design level. Optimization of such solutions is not the main goal of this article.


create or replace function resolve_configuration_version(input_date date) return number as
begin
  if (input_date >= TO_DATE('2016-01-01', 'YYYY-MM-DD')) then
    return 2;
  elsif (input_date >= TO_DATE('2015-01-01', 'YYYY-MM-DD')) then
    return 1;
  end if;
  return 0;
end;
/

CREATE TABLE "CAR_COLOR_DICT"
(        "ID" NUMBER NOT NULL ENABLE,
    "NAME" VARCHAR2(1024 BYTE) NOT NULL ENABLE,
         "VERSION" NUMBER NOT NULL ENABLE,
    CONSTRAINT "CAR_COLOR_DICT_PK" PRIMARY KEY ("ID")
);
COMMENT ON COLUMN "CAR_COLOR_DICT"."ID" IS 'primary key';
COMMENT ON COLUMN "CAR_COLOR_DICT"."NAME" IS 'car color name';
COMMENT ON COLUMN "CAR_COLOR_DICT"."VERSION" IS 'version of product configuration';
INSERT INTO "CAR_COLOR_DICT" (ID, NAME, VERSION) VALUES ('1', 'Other than yellow', 2);
INSERT INTO "CAR_COLOR_DICT" (ID, NAME, VERSION) VALUES ('2', 'Yellow', 2);

ALTER TABLE "MTPL_POLICY" ADD
("CAR_COLOR_FK" NUMBER,
 CONSTRAINT "MTPL_POLICY_FK2" FOREIGN KEY ("CAR_COLOR_FK") REFERENCES "CAR_COLOR_DICT" ("ID") ENABLE
);
COMMENT ON COLUMN "MTPL_POLICY"."CAR_COLOR_FK" IS 'car color (foreign key)';

INSERT INTO "CAR_MODEL_DICT" (ID, NAME, VERSION) VALUES ('6', 'Model 1', 2);
INSERT INTO "CAR_MODEL_DICT" (ID, NAME, VERSION) VALUES ('7', 'Model 2', 2);
INSERT INTO "CAR_MODEL_DICT" (ID, NAME, VERSION) VALUES ('8', 'Model 3', 2);

INSERT INTO "MTPL_POLICY" (CLIENT_NAME, CAR_MODEL_FK, CREATE_DATE, CAR_COLOR_FK) VALUES ('Third Client', '6', TO_DATE('2016-03-01', 'YYYY-MM-DD'), 2);

Next important change request reported by business is to add car color question. It is a new, very important risk factor. Code snippet above is updating database logical model with new column. On the database level color value can be null, the business rules will be checked in the middleware. After synchronizing base entity, both subtypes are updated with CarColorFk attribute. Because in previous time periods (previous versions) there was no such business concept like color, in the subtypes it must be set as not updateable and hidden. The business rules are at the moment backward compatible.




 
Now we need to create another subtype with _2 suffix. Analogously to previous subtypes, discriminator attribute must be overriden with default value set to 2[6]. CarColorFk also must be overriden and marked as Mandatory.




Similary to previous dictionary and according to according to best practices[7], for the color dictionary there is created entity and view object using defaults in the wizards. The database table already contains Version column. CarColorDictView is enriched with view criteria to filter rows valid for the provided product configuration number. 



Pre-requisites for MtplPolicyView are already done. In the Entity Objects tab for MtplPolicy add new supported entity subtype. In Attributes tab please update default value of discriminator attribute to 2. Additionaly please add CarColorFk attribute from entity and define an LOV.







 
Using just several clicks and keyboard values business layer is fully prepared to handle car color values. Now is the time for viewController. On the test page, using drag&drop method draw CarColorFk as "ADF Select One Choice". In the JSP code, there is rendered attribute, just one that is not fulfilled by default. Without it the field will be rendered as read only, but to improve user experience, it'd be better to hide it. The assumption here is that model layer's UI Hints are used also to control rendered attribute (as in this article).



The application is ready to run.





Each version is working properly, each version is using its business rules and render hints. Last quick look on the projects' structure:




As it can be seen, aint one Java class file. Not trivial requirement of data model versioning can be developed in a short time, 100% declaratively, without even one line of Java code.

The example above is limited to very simple requirement changes. Each subtype entity allows for defining separate rule sets not only on the attribute level but also on the entity or a transaction level. If the Java implementation classes will be created for subtypes, they'll extend Java implementation of the base entity.


[1] http://docs.oracle.com/cd/E12839_01/web.1111/b31974/bcentities.htm
[2] Service Data Objects, the JSR-235 standard, https://jcp.org/en/jsr/detail?id=235
[3] http://docs.oracle.com/cd/E17904_01/web.1111/b31974/bcentities.htm#ADFFD211, chapter 4.10.14 (How to Set the Discriminator Attribute for Entity Object Inheritance Hierarchies)
[4] Ibid.
[5] It is not supported in the JDeveloper IDE from the wizards...
[6] please remember about the Expression attribute in the source
[7] entity based view objects should be used instead of query based, even for dictionary read only purposes

No comments:

Post a Comment