Flowable and cockroachdb

Flowable Engine
1

Flowable now supports cockroachdb as a datastore. One of the key features of cockroachdb is the ability to geo-partition data. To use this feature, extra columns are needed as part of the primary key, e.g. a REGION column. I understand that the persistence layer in flowable is pluggable but I’m not sure what exactly would be needed to geo-partition the data? Has any thinking been done on this already?

2

To answer my own question, it seems like I can write my own mybatis schema creation files and then modify the mapping files. I just did a quick test by adding REGION_ to ACT_RU_EXECUTION.

CREATE TABLE ACT_RU_EXECUTION (
    REGION_ varchar(10),
    ID_ varchar(64),
    REV_ integer,
    PROC_INST_ID_ varchar(64),
    BUSINESS_KEY_ varchar(255),
    PARENT_ID_ varchar(64),
    PROC_DEF_ID_ varchar(64),
    SUPER_EXEC_ varchar(64),
    ROOT_PROC_INST_ID_ varchar(64),
    ACT_ID_ varchar(255),
    IS_ACTIVE_ boolean,
    IS_CONCURRENT_ boolean,
    IS_SCOPE_ boolean,
    IS_EVENT_SCOPE_ boolean,
    IS_MI_ROOT_ boolean,
    SUSPENSION_STATE_ integer,
    CACHED_ENT_STATE_ integer,
    TENANT_ID_ varchar(255) default '',
    NAME_ varchar(255),
    START_ACT_ID_ varchar(255),
    START_TIME_ timestamp,
    START_USER_ID_ varchar(255),
    LOCK_TIME_ timestamp,
    IS_COUNT_ENABLED_ boolean,
    EVT_SUBSCR_COUNT_ integer, 
    TASK_COUNT_ integer, 
    JOB_COUNT_ integer, 
    TIMER_JOB_COUNT_ integer,
    SUSP_JOB_COUNT_ integer,
    DEADLETTER_JOB_COUNT_ integer,
    VAR_COUNT_ integer, 
    ID_LINK_COUNT_ integer,
    CALLBACK_ID_ varchar(255),
    CALLBACK_TYPE_ varchar(255),
    primary key (REGION_, ID_)
);

And then modified the mapping files like this:

  <insert id="insertExecution" parameterType="org.flowable.engine.impl.persistence.entity.ExecutionEntityImpl">
    insert into ${prefix}ACT_RU_EXECUTION (REGION_, ID_, REV_, PROC_INST_ID_, BUSINESS_KEY_, PROC_DEF_ID_, ACT_ID_, IS_ACTIVE_, IS_CONCURRENT_,
                                           IS_SCOPE_,IS_EVENT_SCOPE_, IS_MI_ROOT_, PARENT_ID_, SUPER_EXEC_, ROOT_PROC_INST_ID_, SUSPENSION_STATE_,
                                           TENANT_ID_, NAME_, START_ACT_ID_, START_TIME_, START_USER_ID_, IS_COUNT_ENABLED_, EVT_SUBSCR_COUNT_,
                                           TASK_COUNT_, JOB_COUNT_, TIMER_JOB_COUNT_, SUSP_JOB_COUNT_, DEADLETTER_JOB_COUNT_, VAR_COUNT_, ID_LINK_COUNT_,
                                           CALLBACK_ID_, CALLBACK_TYPE_)
    values (
      ${@com.unimarket.RegionSupplier@getRegion()},
      #{id ,jdbcType=VARCHAR},
      1,
      #{processInstanceId, jdbcType=VARCHAR},
      #{businessKey, jdbcType=VARCHAR},
      #{processDefinitionId ,jdbcType=VARCHAR},
      #{activityId ,jdbcType=VARCHAR},
      #{isActive ,jdbcType=BOOLEAN},
      #{isConcurrent ,jdbcType=BOOLEAN},
      #{isScope ,jdbcType=BOOLEAN},
      #{isEventScope ,jdbcType=BOOLEAN},
      #{isMultiInstanceRoot, jdbcType=BOOLEAN},
      #{parentId, jdbcType=VARCHAR},
      #{superExecutionId, jdbcType=VARCHAR},
      #{rootProcessInstanceId, jdbcType=VARCHAR},
      #{suspensionState, jdbcType=INTEGER},
      #{tenantId, jdbcType=VARCHAR},
      #{name, jdbcType=VARCHAR},
      #{startActivityId, jdbcType=VARCHAR},
      #{startTime, jdbcType=TIMESTAMP},
      #{startUserId, jdbcType=VARCHAR},
      #{isCountEnabled, jdbcType=BOOLEAN},
      #{eventSubscriptionCount, jdbcType=INTEGER},
      #{taskCount, jdbcType=INTEGER},
      #{jobCount, jdbcType=INTEGER},
      #{timerJobCount, jdbcType=INTEGER},
      #{suspendedJobCount, jdbcType=INTEGER},
      #{deadLetterJobCount, jdbcType=INTEGER},
      #{variableCount, jdbcType=INTEGER},
      #{identityLinkCount, jdbcType=INTEGER},
      #{callbackId, jdbcType=VARCHAR},
      #{callbackType, jdbcType=VARCHAR}
    )
  </insert>

Where ${@com.unimarket.RegionSupplier@getRegion()} reads the region via a static method.

These changes worked in my very simple test but to implement it fully, the REGION_ column would need to be added to all primary keys and all foreign key references would need to be changed. This is all possible but would be a lot of work and maintenance. Also, the ${@com.unimarket.RegionSupplier@getRegion()} is a bit of a hack, it would be much nicer if the region was part of the model or there was at least a better way to provide it.

Any thoughts would be appreciated?

Regards,
Damien

3

Thanks @damienhollis for writing up your findings, quite impressed you found your way around the code so quickly!

It’s definitely an idea that has been going through our minds when doing the cockroachdb integration. Instead of a static method which is quite limited (as you write yourself), a generic ‘region callback’ could be added to the engine that would pass the entity at hand and allow to determine a region. Or it could ignore that entity and work on basis of some other parameter.

The main ‘problem’ is that Flowable uses one Mybatis mapping for all databases and these columns would not be used in the other databases. Maybe it’s okay to add them there … but it needs a bit of thinking.

Also, I’m definitely interested in hearing about your use case for this. In case you would be interested in talking about this and pushing this further, send me a DM.