“You received without payment; give without payment” (Matt. 10:8).
I will reply to myself describing the steps needed to integrate JBPM with Sybase :
1- extend the SybaseDialect like the below:
import java.sql.Types;
import org.hibernate.dialect.SybaseASE157Dialect;
import org.hibernate.type.descriptor.sql.LongVarbinaryTypeDescriptor;
import org.hibernate.type.descriptor.sql.LongVarcharTypeDescriptor;
import org.hibernate.type.descriptor.sql.SqlTypeDescriptor;
public class MySybaseDialect extends SybaseASE157Dialect
{
@Override
protected SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode)
{
switch (sqlCode)
{
case Types.CLOB:
return LongVarcharTypeDescriptor.INSTANCE;
case Types.BLOB:
return LongVarbinaryTypeDescriptor.INSTANCE;
default:
return super.getSqlTypeDescriptorOverride(sqlCode);
}
}
@Override
public String getForUpdateString()
{
return " ";
}
@Override
public int getMaxAliasLength()
{
return 20;
}
}
2- adjust table names in DashbuilderBootstrap to be case sensitive like below, this class should be replaced under
jbpm-console.war\WEB-INF\classes\org\kie\workbench\backend\DashbuilderBootstrap.class :
@Startup
@ApplicationScoped
public class DashbuilderBootstrap {
private static final Logger logger = LoggerFactory.getLogger(DashbuilderBootstrap.class);
private String jbpmDatasource = System.getProperty("org.kie.ds.jndi", "java:jboss/datasources/ExampleDS");
public static final String HUMAN_TASKS_DATASET = "jbpmHumanTasks";
public static final String HUMAN_TASKS_TABLE = "AuditTaskImpl";
public static final String PROCESS_INSTANCE_DATASET = "jbpmProcessInstances";
public static final String PROCESS_INSTANCE_TABLE = "ProcessInstanceLog";
public static final String HUMAN_TASKS_WITH_USER_DATASET = "jbpmHumanTasksWithUser";
public static final String HUMAN_TASKS_WITH_ADMIN_DATASET = "jbpmHumanTasksWithAdmin";
public static final String REQUEST_LIST_DATASET = "jbpmRequestList";
public static final String REQUEST_LIST_TABLE = "RequestInfo";
public static final String PROCESS_INSTANCE_WITH_VARIABLES_DATASET = "jbpmProcessInstancesWithVariables";
public static final String TASKS_MONITORING_DATASET = "tasksMonitoring";
public static final String PROCESSES_MONITORING_DATASET = "processesMonitoring";
@Inject
protected DataSetDefRegistry dataSetDefRegistry;
@PostConstruct
protected void init() {
// figure out data source JNDI name
findDataSourceJNDI();
registerDataSetDefinitions();
}
protected void registerDataSetDefinitions() {
DataSetDef humanTasksDef = DataSetFactory.newSQLDataSetDef()
.uuid(HUMAN_TASKS_DATASET)
.name("Human tasks")
.dataSource(jbpmDatasource)
.dbTable(HUMAN_TASKS_TABLE, false)
.date(DataSetTasksListGridViewImpl.COLUMN_ACTIVATIONTIME)
.label(DataSetTasksListGridViewImpl.COLUMN_ACTUALOWNER)
.label(DataSetTasksListGridViewImpl.COLUMN_CREATEDBY)
.date(DataSetTasksListGridViewImpl.COLUMN_CREATEDON)
.label(DataSetTasksListGridViewImpl.COLUMN_DEPLOYMENTID)
.text(DataSetTasksListGridViewImpl.COLUMN_DESCRIPTION)
.date(DataSetTasksListGridViewImpl.COLUMN_DUEDATE)
.label(DataSetTasksListGridViewImpl.COLUMN_NAME)
.number(DataSetTasksListGridViewImpl.COLUMN_PARENTID)
.number(DataSetTasksListGridViewImpl.COLUMN_PRIORITY)
.label(DataSetTasksListGridViewImpl.COLUMN_PROCESSID)
.number(DataSetTasksListGridViewImpl.COLUMN_PROCESSINSTANCEID)
.number(DataSetTasksListGridViewImpl.COLUMN_PROCESSSESSIONID)
.label(DataSetTasksListGridViewImpl.COLUMN_STATUS)
.number(DataSetTasksListGridViewImpl.COLUMN_TASKID)
.number(DataSetTasksListGridViewImpl.COLUMN_WORKITEMID)
.buildDef();
DataSetDef processInstancesDef = DataSetFactory.newSQLDataSetDef()
.uuid(PROCESS_INSTANCE_DATASET)
.name("Process Instances")
.dataSource(jbpmDatasource)
.dbTable(PROCESS_INSTANCE_TABLE, false)
.number(DataSetProcessInstanceListViewImpl.COLUMN_PROCESSINSTANCEID)
.label(DataSetProcessInstanceListViewImpl.COLUMN_PROCESSID)
.date(DataSetProcessInstanceListViewImpl.COLUMN_START)
.date(DataSetProcessInstanceListViewImpl.COLUMN_END)
.number(DataSetProcessInstanceListViewImpl.COLUMN_STATUS)
.number(DataSetProcessInstanceListViewImpl.COLUMN_PARENTPROCESSINSTANCEID)
.label(DataSetProcessInstanceListViewImpl.COLUMN_OUTCOME)
.number(DataSetProcessInstanceListViewImpl.COLUMN_DURATION)
.label(DataSetProcessInstanceListViewImpl.COLUMN_IDENTITY)
.label(DataSetProcessInstanceListViewImpl.COLUMN_PROCESSVERSION)
.label(DataSetProcessInstanceListViewImpl.COLUMN_PROCESSNAME)
.label(DataSetProcessInstanceListViewImpl.COLUMN_CORRELATIONKEY)
.label(DataSetProcessInstanceListViewImpl.COLUMN_EXTERNALID)
.label(DataSetProcessInstanceListViewImpl.COLUMN_PROCESSINSTANCEDESCRIPTION)
.buildDef();
DataSetDef humanTasksWithUserDef = DataSetFactory.newSQLDataSetDef()
.uuid(HUMAN_TASKS_WITH_USER_DATASET)
.name("Human tasks and users")
.dataSource(jbpmDatasource)
.dbSQL("select t.activationTime, t.actualOwner, t.createdBy, "
+ "t.createdOn, t.deploymentId, t.description, t.dueDate, "
+ "t.name, t.parentId, t.priority, t.processId, t.processInstanceId, "
+ "t.processSessionId, t.status, t.taskId, t.workItemId, oe.id oeid "
+ "from AuditTaskImpl t, "
+ "PeopleAssignments_PotOwners po, "
+ "OrganizationalEntity oe "
+ "where t.id = po.task_id and po.entity_id = oe.id", false)
.date(DataSetTasksListGridViewImpl.COLUMN_ACTIVATIONTIME)
.label(DataSetTasksListGridViewImpl.COLUMN_ACTUALOWNER)
.label(DataSetTasksListGridViewImpl.COLUMN_CREATEDBY)
.date(DataSetTasksListGridViewImpl.COLUMN_CREATEDON)
.label(DataSetTasksListGridViewImpl.COLUMN_DEPLOYMENTID)
.text(DataSetTasksListGridViewImpl.COLUMN_DESCRIPTION)
.date(DataSetTasksListGridViewImpl.COLUMN_DUEDATE)
.label(DataSetTasksListGridViewImpl.COLUMN_NAME)
.number(DataSetTasksListGridViewImpl.COLUMN_PARENTID)
.number(DataSetTasksListGridViewImpl.COLUMN_PRIORITY)
.label(DataSetTasksListGridViewImpl.COLUMN_PROCESSID)
.number(DataSetTasksListGridViewImpl.COLUMN_PROCESSINSTANCEID)
.number(DataSetTasksListGridViewImpl.COLUMN_PROCESSSESSIONID)
.label(DataSetTasksListGridViewImpl.COLUMN_STATUS)
.label(DataSetTasksListGridViewImpl.COLUMN_TASKID) //declaring as label(even though it's numeric) because needs apply groupby and Group by number not supported
.number(DataSetTasksListGridViewImpl.COLUMN_WORKITEMID)
.label(DataSetTasksListGridViewImpl.COLUMN_ORGANIZATIONAL_ENTITY)
.buildDef();
DataSetDef humanTaskWithAdminDef = DataSetFactory.newSQLDataSetDef()
.uuid(HUMAN_TASKS_WITH_ADMIN_DATASET)
.name("Human tasks and admins")
.dataSource(jbpmDatasource)
.dbSQL("select t.activationTime, t.actualOwner, t.createdBy, "
+ "t.createdOn, t.deploymentId, t.description, t.dueDate, "
+ "t.name, t.parentId, t.priority, t.processId, t.processInstanceId, "
+ "t.processSessionId, t.status, t.taskId, t.workItemId, oe.id oeid "
+ "from AuditTaskImpl t, "
+ "PeopleAssignments_BAs bas, "
+ "OrganizationalEntity oe "
+ "where t.id = bas.task_id and bas.entity_id = oe.id", false)
.date(DataSetTasksListGridViewImpl.COLUMN_ACTIVATIONTIME)
.label(DataSetTasksListGridViewImpl.COLUMN_ACTUALOWNER)
.label(DataSetTasksListGridViewImpl.COLUMN_CREATEDBY)
.date(DataSetTasksListGridViewImpl.COLUMN_CREATEDON)
.label(DataSetTasksListGridViewImpl.COLUMN_DEPLOYMENTID)
.text(DataSetTasksListGridViewImpl.COLUMN_DESCRIPTION)
.date(DataSetTasksListGridViewImpl.COLUMN_DUEDATE)
.label(DataSetTasksListGridViewImpl.COLUMN_NAME)
.number(DataSetTasksListGridViewImpl.COLUMN_PARENTID)
.number(DataSetTasksListGridViewImpl.COLUMN_PRIORITY)
.label(DataSetTasksListGridViewImpl.COLUMN_PROCESSID)
.number(DataSetTasksListGridViewImpl.COLUMN_PROCESSINSTANCEID)
.number(DataSetTasksListGridViewImpl.COLUMN_PROCESSSESSIONID)
.label(DataSetTasksListGridViewImpl.COLUMN_STATUS)
.label(DataSetTasksListGridViewImpl.COLUMN_TASKID) //declaring as label(even though it's numeric) because needs apply groupby and Group by number not supported
.number(DataSetTasksListGridViewImpl.COLUMN_WORKITEMID)
.label(DataSetTasksListGridViewImpl.COLUMN_ORGANIZATIONAL_ENTITY)
.buildDef();
DataSetDef requestListDef = DataSetFactory.newSQLDataSetDef()
.uuid(REQUEST_LIST_DATASET)
.name("Request List")
.dataSource(jbpmDatasource)
.dbTable(REQUEST_LIST_TABLE, false)
.number( RequestListViewImpl.COLUMN_ID )
.date(RequestListViewImpl.COLUMN_TIMESTAMP)
.label(RequestListViewImpl.COLUMN_STATUS)
.label(RequestListViewImpl.COLUMN_COMMANDNAME)
.label(RequestListViewImpl.COLUMN_MESSAGE)
.label(RequestListViewImpl.COLUMN_BUSINESSKEY)
.buildDef();
DataSetDef processWithVariablesDef = DataSetFactory.newSQLDataSetDef()
.uuid(PROCESS_INSTANCE_WITH_VARIABLES_DATASET)
.name("Variable for Evalution Process Instances")
.dataSource(jbpmDatasource)
.dbSQL("select pil.processInstanceId pid,\n" +
" pil.processId pname,\n" +
" v.id varid,\n" +
" v.variableId varname,\n" +
" v.value varvalue\n" +
"from ProcessInstanceLog pil\n" +
" inner join (select vil.processInstanceId ,vil.variableId, MAX(vil.id) maxvilid FROM VariableInstanceLog vil\n" +
" GROUP BY vil.processInstanceId, vil.variableId) x\n" +
" on (x.processInstanceId =pil.processInstanceId)\n" +
" INNER JOIN VariableInstanceLog v\n" +
" ON (v.variableId = x.variableId AND v.id = x.maxvilid )", false)
.number("pid")
.label("pname")
.number("varid")
.label("varname")
.label("varvalue")
.buildDef();
DataSetDef processMonitoringDef = DataSetFactory.newSQLDataSetDef()
.uuid(PROCESSES_MONITORING_DATASET)
.name("Processes monitoring")
.dataSource(jbpmDatasource)
.dbTable(PROCESS_INSTANCE_TABLE, true)
.buildDef();
DataSetDef taskMonitoringDef = DataSetFactory.newSQLDataSetDef()
.uuid(TASKS_MONITORING_DATASET)
.name("Tasks monitoring")
.dataSource(jbpmDatasource)
.dbSQL("select p.processname, t.* " +
"from processinstancelog p inner join bamtasksummary t " +
"on (t.processinstanceid = p.processinstanceid)", true)
.buildDef();
// Hide all these internal data set from end user view
humanTasksDef.setPublic(false);
processInstancesDef.setPublic(false);
humanTasksWithUserDef.setPublic(false);
humanTaskWithAdminDef.setPublic(false);
requestListDef.setPublic(false);
processWithVariablesDef.setPublic(false);
processMonitoringDef.setPublic(false);
taskMonitoringDef.setPublic(false);
// Register the data set definitions
dataSetDefRegistry.registerDataSetDef(humanTasksDef);
dataSetDefRegistry.registerDataSetDef(processInstancesDef);
dataSetDefRegistry.registerDataSetDef(humanTasksWithUserDef);
dataSetDefRegistry.registerDataSetDef(humanTaskWithAdminDef);
dataSetDefRegistry.registerDataSetDef(requestListDef);
dataSetDefRegistry.registerDataSetDef(processWithVariablesDef);
dataSetDefRegistry.registerDataSetDef(processMonitoringDef);
dataSetDefRegistry.registerDataSetDef(taskMonitoringDef);
}
protected void findDataSourceJNDI() {
try {
XMLInputFactory factory = XMLInputFactory.newInstance();
XMLStreamReader reader = factory.createXMLStreamReader(Thread.currentThread().getContextClassLoader().getResourceAsStream("META-INF/persistence.xml"));
while (reader.hasNext()) {
int event = reader.next();
switch (event) {
case XMLStreamConstants.START_ELEMENT:
if ("jta-data-source".equals(reader.getLocalName())) {
jbpmDatasource = reader.getElementText();
return;
}
break;
}
}
} catch (XMLStreamException e) {
logger.warn("Unable to find out JNDI name fo data source to be used for data sets due to {} using default {}", e);
}
}
}
3- Patch the class DataSetLookupServicesImpl to fix the problem of VARCHAR to INT conversion in process instance grid like the below:
@ApplicationScoped
@Service
public class DataSetLookupServicesImpl implements DataSetLookupServices {
@Inject
protected Logger log;
@Inject
protected ExceptionManager exceptionManager;
@Inject
protected BackendDataSetManager dataSetManager;
@Inject
protected BackendUUIDGenerator backendUUIDGenerator;
@Inject
protected DataSetDefDeployer dataSetDefDeployer;
@PostConstruct
protected void init() {
// By default, enable the register of data set definitions stored into the deployment folder.
ServletContext servletContext = RpcContext.getHttpSession().getServletContext();
if (!dataSetDefDeployer.isRunning() && servletContext != null) {
String dir = servletContext.getRealPath("WEB-INF/datasets");
if (dir != null && new File(dir).exists()) {
dir = dir.replaceAll("\\\\", "/");
dataSetDefDeployer.deploy(dir);
}
}
}
public DataSet lookupDataSet(DataSetLookup lookup) throws Exception {
DataSet _d = null;
try {
String lookUpUUID = lookup.getDataSetUUID();
if("jbpmProcessInstances".equals(lookUpUUID))
{
List<DataSetFilter> filterList = lookup.getOperationList(DataSetFilter.class);
if(filterList != null && !filterList.isEmpty())
{
for(DataSetFilter filter : filterList)
{
List<ColumnFilter> colFilterList = filter.getColumnFilterList();
if(colFilterList != null && !colFilterList.isEmpty())
{
for(ColumnFilter colFil : colFilterList)
{
String columnId = colFil.getColumnId();
if("status".equals(columnId) && colFil instanceof CoreFunctionFilter)
{
CoreFunctionFilter coreFuncFil = (CoreFunctionFilter) colFil;
List paramList = coreFuncFil.getParameters();
List paramListNew = new ArrayList<Integer>();
if(paramList != null && !paramList.isEmpty())
{
for(Object paramObject : paramList)
{
if(paramObject instanceof String)
{
paramListNew.add(Integer.valueOf(paramObject.toString()));
}
}
}
coreFuncFil.setParameters(paramListNew);
}
}
}
}
}
}
_d = dataSetManager.lookupDataSet(lookup);
} catch (DataSetLookupException e) {
throw exceptionManager.handleException(e);
}
return _d;
}
public DataSet lookupDataSet(DataSetDef def, DataSetLookup lookup) throws Exception {
try {
// Although if using a not registered definition, it must have an uuid set for performing lookups.
if (def.getUUID() == null) {
final String uuid = backendUUIDGenerator.newUuid();
def.setUUID(uuid);
lookup.setDataSetUUID(uuid);
}
return dataSetManager.resolveProvider(def)
.lookupDataSet(def, lookup);
} catch (Exception e) {
throw exceptionManager.handleException(e);
}
}
public DataSetMetadata lookupDataSetMetadata(String uuid) throws Exception {
DataSetMetadata _d = null;
try {
_d = dataSetManager.getDataSetMetadata(uuid);
} catch (DataSetLookupException e) {
throw exceptionManager.handleException(e);
}
return _d;
}
}
