topic Re: Multi tenancy / Entity relationship in Alfresco Archive

Multi tenancy / Entity relationship

tdtappe — Fri, 05 Jul 2013 13:23:56 GMT

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 Activit

Re: Multi tenancy / Entity relationship

frederikherema1 — Tue, 09 Jul 2013 07:50:43 GMT

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.

Re: Multi tenancy / Entity relationship

tdtappe — Thu, 11 Jul 2013 09:14:14 GMT

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)

Re: Multi tenancy / Entity relationship

jbarrez — Fri, 12 Jul 2013 07:23:22 GMT

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.

Re: Multi tenancy / Entity relationship

tdtappe — Fri, 12 Jul 2013 09:04:54 GMT

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.

Re: Multi tenancy / Entity relationship

frederikherema1 — Wed, 17 Jul 2013 09:01:45 GMT

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

Re: Multi tenancy / Entity relationship

tdtappe — Thu, 18 Jul 2013 13:15:14 GMT

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

Re: Multi tenancy / Entity relationship

jbarrez — Wed, 24 Jul 2013 07:25:32 GMT

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.

Re: Multi tenancy / Entity relationship

tdtappe — Mon, 12 Aug 2013 13:25:51 GMT

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.

Re: Multi tenancy / Entity relationship

jbarrez — Wed, 14 Aug 2013 09:58:30 GMT

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

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

Re: Multi tenancy / Entity relationship

tdtappe — Wed, 14 Aug 2013 10:01:45 GMT

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

Re: Multi tenancy / Entity relationship

jbarrez — Wed, 14 Aug 2013 12:01:26 GMT

Most of them should be table_name_id when it references other tables

Re: Multi tenancy / Entity relationship

tdtappe — Tue, 27 Aug 2013 13:14:24 GMT

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>

Re: Multi tenancy / Entity relationship

frederikherema1 — Wed, 04 Sep 2013 08:42:35 GMT

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.