Developer Series - Custom Data Models

cancel
Showing results for 
Search instead for 
Did you mean: 

Developer Series - Custom Data Models

gravitonian
Alfresco Employee
3 6 16.3K

Table of Contents                   

 

Introduction

Alfresco Process Services solution supports integration with external databases. It is possible to both read from the database and write to the database during the execution of a process instance.

 

There are two ways in which you can talk to the external database, via automatically generated CRUD operations or via manually coded CRUD operations (note. DELETE is not currently supported):

 

In the above picture data is manipulated in table A via custom coded SQL code. This is needed in situations such as when you have a database table that uses a compound primary key, which is not supported in the auto-generated SQL. It is also needed when you for example have a scenario where you make an INSERT that requires you to update another row to be consistent. Table B on the other hand is managed entirely via auto-generated CRUD operations. And there is no need to write any read or write code.

 

As can be seen in the above picture, there is a specific service task type that should be used when writing to an external database, it is called Store Entity task. Each of these store entity tasks execute in their own transaction, which is important to understand, because if the process instance crashes when executing the “Store Entity B Automatically” task in the above sample, then the database might be in an inconsistent state.

 

As well as writing data to the database it is also possible to set up a user task form to map form fields to database column values. If the form field is mapped to a primary key then it will automatically trigger a read in the database.

 

Alfresco Process Services uses the concept of Entities internally to map between a logical representation of the data and the physical structure of the database table. An entity does not have to map all columns in a table, only those that are used. And if using automatically generated CRUD, then the statements will be generated accordingly. These internal entities are contained in what’s called a Custom Data Model.

Source Code

Source code for the Alfresco Process Services Developer Series can be found here.

Prerequisites

Before starting with your Custom Data Model implementation make sure to set up a proper Extension project.

Implementing a process that integrates with an external database

This section walks you through the steps needed to create a process that uses an external MySQL database to fetch and store data in multiple tables.

 

The tables that we want to work with looks like this:

 

mysql> describe employees;

+------------+---------------+------+-----+---------+-------+

| Field      | Type          | Null | Key | Default | Extra |

+------------+---------------+------+-----+---------+-------+

| emp_no     | int(11)       | NO   | PRI | NULL    |       |

| birth_date | date          | NO   |     | NULL    |       |

| first_name | varchar(14)   | NO   |     | NULL    |       |

| last_name  | varchar(16)   | NO   |     | NULL    |       |

| gender     | enum('M','F') | NO   |     | NULL    |       |

| hire_date  | date          | NO   |     | NULL    |       |

+------------+---------------+------+-----+---------+-------+

6 rows in set (0.14 sec)

 

mysql> describe salaries;

+-----------+---------+------+-----+---------+-------+

| Field     | Type    | Null | Key | Default | Extra |

+-----------+---------+------+-----+---------+-------+

| emp_no    | int(11) | NO   | PRI | NULL    |       |

| salary    | int(11) | NO   |     | NULL    |       |

| from_date | date    | NO   | PRI | NULL    |       |

| to_date   | date    | NO   |     | NULL    |       |

+-----------+---------+------+-----+---------+-------+

4 rows in set (0.00 sec)

 

These tables are part of the MySQL sample employees database that can be downloaded from this site. If you are going to implement this process at the same time as you read this article, then now is the time to download and set up the employees database.

 

Note. there is currently a bug that prevents tables with PKs that are INTs to be stored correctly. So for example, if you have a PostgreSQL employees table with PK defined as:

emp_no int primary key not null,

Then that will not work at the moment, If you have control over the table, alter it so the PK is long.

Overview

The process that we will implement will enable a user to update an employee's first name and last name. It will also be possible to set a new salary as active from a specific date. The process definition looks like this:

The start form will have a field where we can specify the Employee No (the PK into the employees table):

 

 

When the start form is completed Activiti will automatically read the employee’s current first name and last name from the employees table and make these available as process variables. If the Employee No is not found, then the Entity/process variable will be null.

 

The current salary will also be fetched but via code we have to write manually. The Update Employee form will then be displayed:

 

 

The First name and Last name fields are automatically populated. We can then update those if needed. Here we can also specify a new salary and from what date it should be active. When completing this form an Approve Employee Update form is displayed:

 

 

This form shows suggested new data and the old/current data. Clicking Approved in this form starts the update of the two tables via two separate Store Entity Task nodes. Each one executing in its own transaction. Note that a Store Entity Task node can manage only one entity.

 

We will see logging as follows from the custom data model service implementation:

 

08:30:59,898 [http-nio-8080-exec-5] INFO  com.activiti.extension.bean.CustomDataModelServiceImpl  - getMappedValue() EntityDefinition [Name=Salary][TableName=salaries][Id=null][Attributes=4] [fieldName=Employee No] [variableValue=10001]

08:30:59,899 [http-nio-8080-exec-5] INFO  com.activiti.extension.bean.CustomDataModelServiceImpl  - getMappedValue() Response: {"Employee No":10001,"From Date":"2002-06-22T00:00:00Z","To Date":"2016-12-01T00:00:00Z","Salary":88958}

08:30:59,938 [http-nio-8080-exec-5] INFO  com.activiti.extension.bean.CustomDataModelServiceImpl  - getMappedValue() EntityDefinition [Name=Salary][TableName=salaries][Id=null][Attributes=4] [fieldName=Employee No] [variableValue=10001]

08:30:59,940 [http-nio-8080-exec-5] INFO  com.activiti.extension.bean.CustomDataModelServiceImpl  - getMappedValue() Response: {"Employee No":10001,"From Date":"2002-06-22T00:00:00Z","To Date":"2016-12-01T00:00:00Z","Salary":88958}

08:31:00,023 [http-nio-8080-exec-5] INFO  com.activiti.extension.bean.CustomDataModelServiceImpl  - getMappedValue() EntityDefinition [Name=Salary][TableName=salaries][Id=null][Attributes=4] [fieldName=Employee No] [variableValue=10001]

08:31:00,024 [http-nio-8080-exec-5] INFO  com.activiti.extension.bean.CustomDataModelServiceImpl  - getMappedValue() Response: {"Employee No":10001,"From Date":"2002-06-22T00:00:00Z","To Date":"2016-12-01T00:00:00Z","Salary":88958}

08:31:00,024 [http-nio-8080-exec-5] INFO  com.activiti.extension.bean.CustomDataModelServiceImpl  - storeEntity() EntityDefinition [Name=Salary][TableName=salaries][Id=null][Attributes=4] [dataModel=Employee Custom] [attributeDefinitionsAndValues=4]

Configure the external database

Before we can do anything with external database tables we need to first configure connection parameters for it. So it can do a basic JDBC connection to it. This is done via the Identity Management application (note. you need to be logged in as admin):

 

 

Click on this application so you see this screen:

 

 

Now click on the Tenants menu item, this takes you to this screen:

 

 

Here click on Data sources followed by a click on the + sign to the right to add a new data source:

 

Now, set up the connection parameters for your MySQL employees database. Then click Save. This database configuration can now be used by Activiti when doing the automatic CRUD operations. And it is also used when mapping the entities that will be automatically managed (i.e. the employees table entity).

 

Note. if you are only going to use manually managed entities, such as we plan to do with the salaries table, then you don’t need to set up this data source.

Download and Install the JDBC driver

Whether we use automatically managed entities, like we plan on doing for the employees table, or manually managed entities, like for the salaries table. We always need to download and install the JDBC driver that will be used to talk to the database. This driver will be used both by Activiti auto generated SQL statements and our custom coded SQL statements.

 

Download for example the mysql-connector-java-5.1.40-bin.jar file and put it in the <activiti-install-dir>/tomcat/lib directory.

Creating Custom Data Models and Logical Entities

Before we start creating any process model we need to first sort out the logical entities that we will be using in the process model. They will live in a custom data model. As we are going to use both an automatically managed entity (mapping to the employees table) and a manually managed entity (mapping to the salaries table), it will be required to have one data model per entity. The reason for this is that a data model can only contain either automatically managed entities, referred to as Database Entities, or manually managed entities, referred to as Custom Entities.

Creating the Data Model and Database Entity for the employees table

Let start with the Database entity for the employees table. Click on the App Designer application:

 

This displays the following screen:

 

 

Then click on the Data Models menu item:

 

 

Now, click on the Create Data Model button in the upper right corner:

 

 

Enter the name and description for the data model and then click the Create new data model button:

 

 

First select the Data source (the one we added earlier on). Then click Add entity to start adding the logical entity that should map to the employees table. Give the entity a logical name, such as Employee and then specify what database table it maps to, in this case employees. Then start mapping the columns in the table.

 

In the above picture we can just see the primary key column mapping. The Required property needs to be checked as the PK does not accept null values. Note also that the Database generated value property doesn't need to be checked since the employees table does not have the emp_no PK set as AUTO_INCREMENT.

 

Add also the following column mappings:

 

For first name:

 

And for last name:

 

And finally the gender column:

 

Note that we have not mapped all columns in the employees table. This is because Alfresco Process Services currently have a problem with date columns. We will only read and update the emp_no, first_name, last_name, and gender columns in this example. If you need to read and write all columns in a table that has a compound PK, or date columns, then use a Custom Entity instead as we will with the salaries table.

 

Note. if you will be using these entity attribute names when configuring visibility conditions in a form, or in sequence flow conditions, then using spaces in the Attribute name might cause problems, see this jira, and this one.

Creating the Data Model and Custom Entity for the salaries table

Click on the Create Data Model button:

 

Enter the name and description for the data model and then click Create new data model button:

 

 

Now, we are going to manage the CRUD operations for this entity manually, with code we write. So to enable this click the Custom button in the upper left side. This disables the automatic CRUD operations, and the link to the Data source, and sets this entity up to be managed via an implementation of the com.activiti.api.datamodel.AlfrescoCustomDataModelService interface. The implementation class will be called every time that an Activity needs to read or write a Salary entity.

 

We add the attributes that should make up the logical entity in the same way we did for the Employee entity, only difference is that we don’t map them to a table column as was needed when CRUD operations should be created automatically. But we need to match to what we want to store in the salaries table. Continue with the other attributes, first the from and to dates:

 

Then the Salary attribute:

 

This finishes the custom data model definitions. We are now ready to start implementing the process model.

Configuring form and data model mappings for the Start Task

The start form for the start task will be used to collect the unique ID for the employee that should be updated:

 

 

This form is very straightforward with a single number field.

 

The start task also need to have a mapping of this form field to some entity fields:

 

 

Click on the Form field to data model mapping property:

 

Start by mapping the Employee No form field to the Employee entity and its Employee No attribute, which is mapped to the PK in the employees table. Call the process variable that will contain the automatically fetched Employee entity selectedEmployee. We will then be able to refer to this process variable object in future tasks/nodes.

 

Then map the Employee No form field again to the Salary entity and its Employee No attribute, which will be part of the PK to lookup the current salary for the employee in the code that we will write (we will also use current date to compare against from and to date to find current salary):

 

 

That completes the Start Task.

Configuring the form for the Update Employee Task

The form for the update employee task will be used to update the employee's name and salary:

 

 

The form starts with a Display value field at the top that will show currently selected employee no. Then we got two Text fields for updating first and last name. And at the bottom we got a Number field for updating the salary and a Date field to specify from when this new salary should be active. There are also two headers that are not necessary but makes it look a bit nicer.

 

To have the top field show the Employee No for the selected employee we have to configure it a bit:

 

 

Click on the Variable button to the right, this makes all the process variables available, including the auto fetched employee entity. Select to display the selectedEmployee.Employee No attribute of the entity. We can see that the currentSalary entity is also available, but it will not work until we have the backing AlfrescoCustomDataModelService implemented.

 

What we would like also to happen when this form is displayed is that the first and last name fields should be pre-populated with current values. Just before you open a form that you are working on, you should see a screen looking something like this:

 

 

Instead of clicking on the Open button click on the Map Variables > button:

 

Here we can configure what value should be used to pre-populate a form field, and what process variable should contain the form field value when the form is completed. For example, for the First Name field we want it to be pre-populated with the selectedEmployee.First Name source process variable. And when the form is completed we want a new target process variable called newFirstName to contain the value from the form field. We can configure the last name field in the same way.

 

This completes the Update Employee task.

Configuring the form for the Approve Employee Update Task

The Approve Employee task should just show the approver what new names and salary that have been suggested. The approver then clicks Approve to trigger the update to the database, or reject to cancel the update. The form looks like this:

 

This form basically contains a bunch of Display value fields that shows all the updated employee data for the approver. The newFirstName, newLastName, New Salary, and New From Date are all form field values. The others are displayed from variables.

 

For example, newFirstName is configured as follows:

 

 

And an example of a Variable display looks like this:

 

When we have finished configuring all the fields we need to also configure the Approved and Rejected outcomes as follows:

 

Now, while we are at it, Save the form and configure the Approve sequence flow condition as follows:

 

 

 

And then the Rejected flow condition as follows:

 

 

This completes the Approve Employee Update task and the following sequence flows.

Configuring the Update Employee Table Store Entity Task

Now we are ready to store the updated employee information, let’s start with the Employee entity, which will be automatically saved to the employees table via our Date Model database entity mapping.

 

We only need to do a little bit of configuration for this to work, click on the Update Employee Table store entity task:

 

 

Then click on the Attribute mapping property for the store entity task:

 

 

Basically what we need to do here is configure what values we want to use when updating (or creating a new row). First we need to however select the custom data model, which is Employee DB for the automatically managed Employee entity. Then select the Employee entity (there is only one entity defined in the data model so far). Then we click on the Existing variable button as we are doing an UPDATE. And then select what process variable contains existing data such as PK, select selectedEmployee.

 

Now configure/map each Entity Attribute to the value that should be used in the update. We use the existing PK and Gender values, but pick the new first name and last name, which would be form fields when we pick them to the right.

 

That’s it for this Store Entity Task.

Configuring the Update Salary Table Store Entity Task

The last Store Entity task will be used to store the updated salary for the employee. Storing this entity is going to be managed by us manually, we need to do some coding for it to work.

 

But let’s first configure the Attribute mappings so we are clear on what we want to store:

 

 

First select the custom data model, which is Employee Custom for the manually managed Salary entity. Then select the Salary entity (there is only one entity defined in this data model too). Then we click on Existing variable button as we are using values from an existing salary record, basically just the Employee No. And then select what process variable contains existing data such as PK, select currentSalary.

 

Now configure/map each Entity Attribute to the value that should be used in the insert (we are going to create a new entry in the salaries table). We use the existing PK, but pick the new salary and from date, which would be form fields when we pick them to the right. For the to date we want it to be indefinitely until a new salary is added, so we choose Static value to the right and enter ‘9999-01-01’.

 

That’s it for this Store Entity Task. It will not work though until we have implemented next section.

Add code to handle read and write of the custom Salary entity

Now, we have opted to handle the read and write of Salary entities to the database manually. That’s why this entity is configured as a Custom Entity and not mapped to a specific database table in the custom data model.

 

When the Activiti engine needs to read or write a Custom Entity it will call an implementation of the com.activiti.api.datamodel.AlfrescoCustomDataModelService interface. So we need to supply one.

 

Here is one implementation that uses the Spring JDBC template, which is good as it is thread safe and takes care of closing resources etc:

 

package com.activiti.extension.bean;

import com.activiti.api.datamodel.AlfrescoCustomDataModelService;
import com.activiti.model.editor.datamodel.DataModelDefinitionRepresentation;
import com.activiti.model.editor.datamodel.DataModelEntityRepresentation;
import com.activiti.runtime.activiti.bean.datamodel.AttributeMappingWrapper;
import com.activiti.variable.VariableEntityWrapper;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class CustomDataModelServiceImpl implements AlfrescoCustomDataModelService {
  private static Logger logger = LoggerFactory.getLogger(CustomDataModelServiceImpl.class);

  /**
   * Database table names
   */

  private static final String SALARIES_TABLE_NAME = "salaries";

  /**
   * Mapping entity into JSON
   */

  @Autowired
  protected ObjectMapper objectMapper;

  /**
   * Use Spring JDBC Template for database access
   */

  private SimpleJdbcTemplate jdbcTemplate;

  /**
   * Salary Data Transfer Object (DTO)
   */

  private class Salary {
      private long empNo;
      private Date fromDate;
      private Date toDate;
      private long salary;

      public long getEmpNo() { return empNo; }
      public void setEmpNo(long empNo) { this.empNo = empNo; }
      public Date getFromDate() { return fromDate; }
      public void setFromDate(Date fromDate) { this.fromDate = fromDate; }
      public Date getToDate() { return toDate; }
      public void setToDate(Date toDate) { this.toDate = toDate; }
      public long getSalary() { return salary; }
      public void setSalary(long salary) { this.salary = salary; }
  }

  /**
   * Salary Spring JDBC Row Mapper
   */

  class SalaryRowMapper implements RowMapper {
      public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
          Salary salary = new Salary();
          salary.setEmpNo(rs.getLong("emp_no"));
          salary.setFromDate(rs.getDate("from_date"));
          salary.setToDate(rs.getDate("to_date"));
          salary.setSalary(rs.getLong("salary"));

          return salary;
      }
  }

  /**
   * CREATE USER employees@localhost IDENTIFIED BY '1234';
   * GRANT ALL PRIVILEGES ON employees.* TO employees@localhost IDENTIFIED BY '1234';
   * FLUSH PRIVILEGES;
   */

  public CustomDataModelServiceImpl() {
      BasicDataSource ds = new BasicDataSource();
      ds.setDriverClassName("com.mysql.jdbc.Driver");
      ds.setUrl("jdbc:mysql://localhost:3306/employees");
      ds.setUsername("employees");
      ds.setPassword("1234");

      jdbcTemplate = new SimpleJdbcTemplate(ds);
  }

  /**
   * This method is called when Activiti wants to fetch a row from the database table
   * that has been mapped as a "Custom" entity.
   *
   * @param entityDefinition the definition of the "custom" entity that was mapped in the Custom Data Model (e.g. Salary)
   * @param fieldName the entity field that represents the PK (e.g. Employee No)
   * @param fieldValue the entity field value (e.g. 10001)
   * @return an object representing the fetched data
   */

  @Override
  public ObjectNode getMappedValue(DataModelEntityRepresentation entityDefinition,
                                   String fieldName, Object fieldValue) {
      logger.info("getMappedValue() EntityDefinition [Name=" + entityDefinition.getName() +
              "][TableName=" + SALARIES_TABLE_NAME +
              "][Id=" + entityDefinition.getId() +
              "][Attributes=" + entityDefinition.getAttributes().size() +
              "] [fieldName=" + fieldName +
              "] [variableValue=" + fieldValue + "]");

      // Check if are to get something from the salaries table
      if (StringUtils.equals(entityDefinition.getTableName(), SALARIES_TABLE_NAME)) {
          // Fetch the Salary row we are looking for
          Long employeeNo = (Long) fieldValue;
          Date currentDate = new Date();

          String sql = "SELECT * FROM " + SALARIES_TABLE_NAME +
                  " WHERE emp_no = ? and from_date <= ? and to_date > ?";

          Salary salary = (Salary) jdbcTemplate.queryForObject(
                  sql, new SalaryRowMapper(), new Object[]{employeeNo, currentDate, currentDate});

          // The following fields have to match the attributes set up in the Custom Data Model
          // and what is used in the form that will display them
          SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss"); // The expected entity date format is ISO-8601
          ObjectNode fetchedSalaryRowAsJSON = objectMapper.createObjectNode();
          fetchedSalaryRowAsJSON.put("Employee No", salary.getEmpNo());
          fetchedSalaryRowAsJSON.put("From Date", sdf.format(salary.getFromDate()) + "Z"); // see https://github.com/FasterXML/jackson-databind/issues/338
          fetchedSalaryRowAsJSON.put("To Date", sdf.format(salary.getToDate()) + "Z");
          fetchedSalaryRowAsJSON.put("Salary", salary.getSalary());

          logger.info("getMappedValue() Response: " + fetchedSalaryRowAsJSON.toString());

          return fetchedSalaryRowAsJSON;
      }

      return null;
  }

  @Override
  public VariableEntityWrapper getVariableEntity(String keyValue, String variableName,
                                                 String processDefinitionId,
                                                 DataModelEntityRepresentation entityValue) {
      logger.info("getVariableEntity() Entity [Name=" + entityValue.getName() +
              "][TableName=" + SALARIES_TABLE_NAME + "][Id=" + entityValue.getId() +
              "][Attributes=" + entityValue.getAttributes().size() +
              "] [keyValue=" + keyValue +
              "] [variableName=" + variableName + "]");

      return null;
  }

  /**
   * This method is called when Activiti wants to store a "Custom" entity in the
   * database.
   *
   * @param attributeDefinitionsAndValues attributes that will become the column values
   * @param entityDefinition the definition of the "custom" entity that was mapped in the Custom Data Model (e.g. Salary)
   * @param dataModel the custom data model that contains the "custom" entity definition
   * @return
   */

  @Override
  public String storeEntity(List<AttributeMappingWrapper> attributeDefinitionsAndValues,
                            DataModelEntityRepresentation entityDefinition,
                            DataModelDefinitionRepresentation dataModel) {
      logger.info("storeEntity() EntityDefinition [Name=" + entityDefinition.getName() +
              "][TableName=" + SALARIES_TABLE_NAME + "][Id=" + entityDefinition.getId() +
              "][Attributes=" + entityDefinition.getAttributes().size() +
              "] [dataModel=" + dataModel.getName() +
              "] [attributeDefinitionsAndValues=" + attributeDefinitionsAndValues.size() + "]");

      // Check if we are to store something in the salaries table
      if (StringUtils.equals(entityDefinition.getTableName(), SALARIES_TABLE_NAME)) {
          // Set up a map of all the column names and values
          Map<String, Object> parameters = new HashMap<String, Object>();
          for (AttributeMappingWrapper attributeMappingWrapper : attributeDefinitionsAndValues) {
              // Get the column name = mapped name
              // And the column value = attr value
              parameters.put(attributeMappingWrapper.getAttribute().getMappedName(),
                      attributeMappingWrapper.getValue());
          }

          // Update current salary entry to previous
          SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
          String sql = "UPDATE " + SALARIES_TABLE_NAME +
                  " SET to_date = '" +  sdf.format(parameters.get("from_date")) + "'" +
                  " WHERE emp_no = " + parameters.get("emp_no") +
                  " AND to_date = '9999-01-01'";
          jdbcTemplate.update(sql);

          // Insert new salary entry
          sql = "INSERT INTO " + SALARIES_TABLE_NAME +
                  " (emp_no, from_date, to_date, salary) VALUES (:emp_no, :from_date, :to_date, :salary)";
          jdbcTemplate.update(sql, parameters);
      }

      return null;
  }
}

 

Note that this class is in the automatically scanned package com.activiti.extension.bean so it will be picked up as a bean as it is annotated with @Service.

 

The getMappedValue() method is called by Activiti when it wants to read and populate a Salary entity, or any other custom entity, so we need to check that it wants to fetch a Salary entity. Passed in is primary key information in the fieldValue. Because the salaries table have a compound PK we use current date to compare with from_to and to_date to get one row in the response. There is also, as mentioned, some date problems that we work around.

 

When Activiti wants to store a Salary entity (i.e. when we get to the Update Salary Table task) it will call the storeEntity() method. It will call this method for all custom entities that should be stored, so we need to check that it is the Salary entity that should be stored. Here we do a bit more and also update current latest salaries entry before inserting a new row. So this is quite useful, we can do more than one operation when a store is initiated, we have full control.

 

For this to build you need to also add the following dependency in the extension project pom.xml:

 

<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>${spring.version}</version>
  <scope>provided</scope>
</dependency>
6 Comments