How to set variable db schema prefix on multi-schema configuration

Hi,

I am trying to setup a multi-schema engine. My tenants, reside in the same database but in separate schemas. The question is how to dynamically set the schema so that the right tenant tables are accessed.

Thanks

I want to add that setting the schema as part of the url is not an option for me because, this is not allowed. I am interested in finding out whether flowable allows such a behavior dynamically when the tenant is set. In other words, I would like to have flowable set the dynamically a schema prefix when current tenant is set.

Thanks,

I just want to add that my datasources for the different tenants are non-conventional meaning that I do not need to provide url, username and password to create, I just provide a datasource object that provide an implementation of the DataSource interface.

Hi,

Did you look at the MultiSchemaMultiTenantProcessEngineConfiguration?
This blog post describes the usage in more detail:

http://www.jorambarrez.be/blog/2015/10/06/multi-tenancy-separate-database-schemas-in-activiti/

Best regards,

Tijs

Hi Tijs,

I am already using MultiSchemaMultiTenantProcessEngineConfiguration. My problem is that my datasource is a non conventional as mentioned above and is almost the same except the tenantId. Consequently, to access a specific tenant data, I have to provide the schema name within the query. I tried to set a database prefix on the MultiSchemaMultiTenantProcessEngineConfiguration, However, this is wide configuration and it will apply for each tenant and will cause trouble.

What I need is to be able to the set the schema prefix, when the tenant is set using the TenantInfoHolder. Is there a way to do that? Is it easy to change activiti engine so that it sets the database prefix when the tenant is set. If this is possible where to start?

Thank you!

Dan

Hi Dan,

Ok, can you elaborate a bit on which database you are using exactly, just to understand the full context?
In the getConnection method of the TenantAwareDataSource class you could set a specific schema on the connection based on the current tenant id for example. This would currently mean that you need to implement a custom version of the TenantAwareDataSource, but if this works we can make this pluggable to prevent needing custom classes.

The databaseSchemaPrefix is engine wide, and therefore not the best approach I think. Setting the required schema on the connection should do the trick.

Best regards,

Tijs

Hi Tijs,

I tried to set the schema on the connection, but it is not allowed by the underlying app security which supports the multi-tenancy model we are using. Yes, our multi-tenancy model is very efficient in terms of connection pooling and leaves the schema setup to be decided upon when the actual sql query is executed. This is orthogonal to the ways datasource are provisioned for different tenants. The only solution for me is to set the schema when the tenant is set unfortunately. I do not know how to that. I do not know from where to start. I know that activiti relies on mybatis for persistence which is another layer of indirection that I need to figure out how to tam.

Thanks,

Dan

Hi Dan,

Ok interesting. The databaseTablePrefix property is used for this purpose, but it’s static for the whole Engine. The reason for that is mainly because of the parsing of the MyBatis configuration files. If we would not do this, this would need to happen every time a sql statement is executed, which is of course very inefficient. I’m thinking of an alternative where you would have different MyBatis Configuration instances per tenant. But I don’t know how many tenants you would need.

For your needs you would need to render the sql statement including the tenant schema prefix every time. This could be done with a specific variable and a lot of if / else statements in the MyBatis mapping files perhaps.

Best regards,

Tijs