Multi tenancy / Entity relationship

[tdtappe]

I use multi tenancy in one database with Activiti by prefixing process definitions and executions with some tenant identity. This seems to work so far. Now in case I need to remove a tenant from the database or doing a backup of only the tenant's data what about the dependencies of all those Activiti tables? Ok - some relations are quite obvious because there are foreign keys defined. But some relations are not known to the database. What are these?
Or even more specific: Given the tenant prefix "TENANT-XYZ" what do for instance select statements for all tables look like to get the desired records for just this tenant?

–Heiko

[frederikherema1]

There is no easy way to query other entities (eg. process, tasks) using a query that keeps in mind the processDefinitionKeyLike(…). However, you can use a special process-variable (that is set when the process is started by eg. an execution-listener or in the code that calls the startProcessInstance…) indicating what tenant the process is in, and query tasks and executions/processes based on that variable.

Other entities, don't have this kind of support built in, however with process-definitions, deployments (using category, e.g..), tasks and executions/processes covered, you can jump pretty far.

[tdtappe]

I see. I will check wether the process-variable approach can help me.

But just to make sure I understand what you say: You don't think it's possible (out of the box) to have plain sql select statements that filter all the data belonging to a specific tenant?

This is what I think might work (so far):
- act_re_procdef
select * from act_re_procdef where key_ like :tenant
- act_ru_execution
select * from act_ru_execution where proc_def_id_ like :tenant
- act_ru_task
select * from act_ru_task where proc_def_id_ like :tenant
- act_ru_event_subscr
select event.* from act_ru_event_subscr event join act_ru_execution exec on event.execution_id_ = exec.id_ where exec.proc_def_id_ like :tenant
- act_ru_identitylink
select * from act_ru_identitylink where proc_def_id_ like :tenant
- act_ru_variable
select var.* from act_ru_variable var join act_ru_execution exec on var.execution_id_ = exec.id_ where exec.proc_def_id_ like :tenant
- act_re_deployment
select deploy.* from act_re_deployment deploy where
exists(select 1 from act_re_procdef procdef where procdef.key_ like :tenant and procdef.deployment_id_ = deploy.id_)
or exists(select 1 from act_ru_execution exec join act_ru_variable var on var.execution_id_ = exec.id_ join act_ge_bytearray bytearray on var.bytearray_id_ = bytearray.id_ where bytearray.deployment_id_ = deploy.id_ and exec.proc_def_id_ like :tenant)
- act_ge_bytearray
select bytearray.* from act_ge_bytearray bytearray where
exists(select 1 from act_ru_execution exec join act_ru_variable var on var.execution_id_ = exec.id_ where var.bytearray_id_ = bytearray.id_ and exec.proc_def_id_ like :tenant)

[jbarrez]

Sure, plain sql will give you the possibility … but you won't be able to use the out of the box Java API of Activiti.

[tdtappe]

No problem. As it's all just for a technical backup/restore I do not get much of a headache because of this 🙂

So being possible what do all the dependencies between the entities/tables look like so that I can build my sql select statements (for each table) to get only those records belong to a specific tenant.

[frederikherema1]

Not sure what you mean by "dependencies"… You mean an ERD (we don't have that currently)?

[tdtappe]

Yes, that's what I am looking for. So that I can build proper select statements.

[jbarrez]

We don't have such a diagram available. However, many tools out there can easily generate it from the DDL or database schema if you need it.

[tdtappe]

Sorry for the late reply. I was on vacation 🙂

Correct me if I'm wrong. But the problem seems to be that not all relationships are modelled via foreign keys. So some dependencies aren't obvious resp. explicit.

[jbarrez]

That's true. Everything in the runtime tables have foreign keys.

Everything in the historical tables don't have foreign keys, for performance reasons.