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

[tdtappe]

Oh, I see. Thanks for clarification.
Is there some better way than just "guessing" to find out about the relationships in the "historical" part?

[jbarrez]

Most of them should be table_name_id when it references other tables

[tdtappe]

Sorry for bothering you again. But the references aren't as obvious to me as expected. So could you please have a look at what I've put together so far? Things unclear to me are marked with a "?". "=>" stands for a foreign key. "->" might be a reference without a foreign key definition.

<blockcode>
act_ge_bytearray (id_)
======================
deployment_id_ (=> act_re_deployment)

act_ge_property (name_)
=======================

act_hi_actinst (id_)
====================
proc_def_id_ (-> act_re_procdef?)
proc_inst_id_ (-> act_ru_execution?)
execution_id_ (-> act_ru_execution?)
act_id_ (-> ?)
task_id_ (-> act_ru_task?)
call_proc_inst_id_ (-> act_ru_execution?)
assignee_ (-> act_id_user?)

act_hi_attachment (id_)
=======================
user_id_ (-> act_id_user?)
task_id_ (-> act_ru_task?)
proc_inst_id_ (-> act_ru_execution?)
content_id_ (-> ?)

act_hi_comment (id_)
====================
user_id_ (-> act_id_user?)
task_id_ (-> act_ru_task?)
proc_inst_id_ (-> act_ru_execution?)

act_hi_detail (id_)
===================
proc_inst_id_ (-> act_ru_execution?)
execution_id_ (-> act_ru_execution?)
task_id_ (-> act_ru_task?)
act_inst_id_ (-> ?)
bytearray_id_ (-> act_ge_bytearray?)

act_hi_procinst (id_)
=====================
proc_inst_id_ (-> act_ru_execution?)
proc_def_id_ (-> act_re_procdef?)
start_user_id_ (-> act_id_user?)
start_act_id_ (-> ?)
end_act_id_ (-> ?)
super_process_instance_id_ (-> act_ru_execution?)

act_hi_taskinst (id_)
=====================
proc_def_id_ (-> act_re_procdef?)
proc_inst_id_ (-> act_ru_execution?)
execution_id_ (-> act_ru_execution?)
parent_task_id_ (-> act_ru_task?)
assignee_ (-> act_id_user?)

act_hi_varinst (id_)
====================
proc_inst_id_ (-> act_ru_execution)
execution_id_ (-> act_ru_execution)
task_id_ (-> act_ru_task)
bytearray_id_ (-> act_ge_bytearray)

act_id_group (id_)
==================

act_id_info (id_)
=================
user_id_ (-> act_id_user?)
parent_id_ (-> ?)

act_id_membership (user_id_, group_id_)
=======================================
user_id_ (=> act_id_user)
group_id_ (=> act_id_group)

act_id_user (id_)
=================
picture_id_ (-> act_ge_bytearray?)

act_re_deployment (id_)
=======================

act_re_model (id_)
==================
deployment_id_ (=> act_re_deployment)
editor_source_value_id_ (=> act_ge_bytearray)
editor_source_extra_value_id_ (=> act_ge_bytearray)

act_re_procdef (id_)
====================
deployment_id_ (-> act_re_deployment?)

act_ru_event_subscr (id_)
=========================
execution_id_ (=> act_ru_execution)
proc_inst_id_ (-> act_ru_execution?)
activity_id_ (-> ?)

act_ru_execution (id_)
======================
parent_id_ (=> act_ru_execution)
proc_def_id_ (=> act_re_procdef)
proc_inst_id_ (=> act_ru_execution)
super_exec_ (=> act_ru_execution)
act_id_ (-> ?)

act_ru_identitylink (id_)
=========================
proc_def_id_ (=> act_re_procdef)
task_id_ (=> act_ru_task)
group_id_ (-> act_id_group?)
user_id_ (-> act_id_user?)

act_ru_job (id_)
================
exception_stack_id_ (=> act_ge_bytearray)
execution_id_ (-> act_ru_execution?)
process_instance_id_ (-> act_ru_execution?)
proc_def_id_ (-> act_re_procdef?)


act_ru_task (id_)
=================
execution_id_ (=> act_ru_execution)
proc_def_id_ (=> act_re_procdef)
proc_inst_id_ (=> act_ru_execution)
parent_task_id_ (-> act_ru_task?)

act_ru_variable (id_)
=====================
bytearray_id_ (=> act_ge_bytearray)
execution_id_ (=> act_ru_execution)
proc_inst_id_ (=> act_ru_execution)
task_id_ (-> act_ru_task?)
</blockcode>

[frederikherema1]

In short, we try to make naming as clear as possible, so all assumptions you made for tables referenced is correct. Only for users/groups, there is no hard link with the identity-tables, as you can plug in alternative identity-management (e.g. LDAP) that doesn't use the act_id_* tables.