configuring database schema to something ELSE than 'public'

[ttfrttfr]

I am using Postgresql and I would like to put the activiti database in another schema than the default "public" schema.
I am using Spring as configuration of engine and I did not find any property to configure this.

can you help?

Sorry if the question was already asked many times, I have searched the forum for "database schema, modification, change etc…" and of course it brings too many results.

Thanks,

Thomas

[korshak]

I use Postgresql also and have the same problem
So I should create db schema and run script for tables creation by hand, right?
My question cause -
Where I can get this script for tables creation?
Can I print it by Activiti ?

[jbarrez]

> Where I can get this script for tables creation?

They are in the zip file you can download from the activiti.org website

[jdaugustine]

Hi,
I'm running Activiti ProcessEngine embedded inside my Java application.
I've succeeded in doing that.
Works GREAT by the way. EXACTLY what I want.

However, my initial implementation is running against Activiti tables installed in the "public" schema.
When I try to use Activiti tables inside my own schema ("gdb"), I get the following error during application startup:

<blockcode>
—-
2015-08-25 19:01:38.712  INFO 3779 — [           main] o.a.engine.impl.ProcessEngineImpl        : ProcessEngine default created
2015-08-25 19:01:38.714  INFO 3779 — [           main] o.a.engine.impl.jobexecutor.JobExecutor  : Starting up the JobExecutor[org.activiti.engine.impl.jobexecutor.DefaultJobExecutor].
2015-08-25 19:01:38.715  INFO 3779 — [       Thread-2] o.a.e.i.j.AcquireJobsRunnableImpl        : JobExecutor[org.activiti.engine.impl.jobexecutor.DefaultJobExecutor] starting to acquire jobs
2015-08-25 19:01:38.822  INFO 3779 — [           main] o.a.e.impl.bpmn.deployer.BpmnDeployer    : Processing resource diagrams/one-task-process.bpmn20.xml
2015-08-25 19:01:39.058  WARN 3779 — [           main] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'activitiRepositoryService' defined in com.sm.gis.gdm.GdmServer: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.activiti.engine.RepositoryService]: Factory method 'activitiRepositoryService' threw exception; nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: relation "act_re_procdef" does not exist
  Position: 53
### The error may exist in org/activiti/db/mapping/entity/Job.xml
### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectJobByTypeAndProcessDefinitionKeyNoTenantId-Inline
### The error occurred while setting parameters
### SQL: select J.*     from gdb.ACT_RU_JOB J     inner join ACT_RE_PROCDEF P on J.PROC_DEF_ID_ = P.ID_     where J.HANDLER_TYPE_ = ?      and P.KEY_ = ?     and (P.TENANT_ID_ = ''  or P.TENANT_ID_ is null)
### Cause: org.postgresql.util.PSQLException: ERROR: relation "act_re_procdef" does not exist
—–
</blockcode>

The error occurs during during application startup.

I'm using…
. Activiti 5.18.0
. Postgres 9.4
. Spring Boot 1.2.5

I dropped all the Activiti tables from the "public" schema.
I created all the Activiti tables, indexes, etc. using the DDL supplied in the 5.18.0 download. (I edited the DDL to prefix the tables with "gdb.".)

I have confirmed that "act_re_procdef" does not exist and that "gdb.act_re_procdef" does.

I'm using the following configuration…
<blockcode>
SpringProcessEngineConfiguration config = new SpringProcessEngineConfiguration();
config.setTransactionManager( transactionManager() );
config.setDataSource( atomikosJdbcConnectionFactory() );
config.setDatabaseType("postgres");
config.setDatabaseSchemaUpdate("none");
config.setDatabaseSchema("gdb");
config.setDatabaseTablePrefix("gdb.");
config.setDeploymentMode("default");
config.setJobExecutorActivate(true);
</blockcode>
I may be configuring something wrong… but I'm a bit confused because, looking at the SQL in the exception message, the "gdb" prefix was applied to one table ("from gdb.ACT_RU_JOB"), but not to another ("inner join ACT_RE_PROCDEF").

Any guidance would be appreciated.
Thanks!
PS. I'm new to Activiti and to this forum. I believe this existing topic was the best place to post my question, but if I should post it elsewhere, just let me know. Thanks.

[jdaugustine]

Has anyone seen something similar to what I described in my Tue 8/25/15 post to this thread?
Does anyone have a suggestion on where else I could get help?

[vasile_dirla]

Hi,
please provide a unit test for this (I'm interested to have all your engine settings and stuff)

I guess you already have the Activiti sources, if not download them and have a look into the  file Job.xml

locate this:
<code>
<select id="selectJobByTypeAndProcessDefinitionKeyNoTenantId" parameterType="org.activiti.engine.impl.db.ListQueryParameterObject" resultMap="jobResultMap">
    select J.*
    from ${prefix}ACT_RU_JOB J
    inner join ACT_RE_PROCDEF P on J.PROC_DEF_ID_ = P.ID_
    where J.HANDLER_TYPE_ = #{parameter.handlerType}
    and P.KEY_ = #{parameter.processDefinitionKey}
    and (P.TENANT_ID_ = ''  or P.TENANT_ID_ is null) 
  </select>
</code>

and change it with:

<code>
<select id="selectJobByTypeAndProcessDefinitionKeyNoTenantId" parameterType="org.activiti.engine.impl.db.ListQueryParameterObject" resultMap="jobResultMap">
    select J.*
    from ${prefix}ACT_RU_JOB J
    inner join ${prefix}ACT_RE_PROCDEF P on J.PROC_DEF_ID_ = P.ID_
    where J.HANDLER_TYPE_ = #{parameter.handlerType}
    and P.KEY_ = #{parameter.processDefinitionKey}
    and (P.TENANT_ID_ = ''  or P.TENANT_ID_ is null) 
  </select>
</code>

I guess it should work.

I'll try to reproduce it either.

[vasile_dirla]

Hi,
yes just reproduced this behaviour.
I opened an issue and also created a PR for this.
see: https://github.com/vasiledirla/Activiti/commit/c7421ae48b910b73ee1196fefb2049f5e89e9fa7

[jdaugustine]

Thank you, Vasile. I'll follow the PR for this.

[jubedus]

Hi there,

  The post looks fairly old. Hopefully, somebody can help me with a similar problem.

  I'm switching my Activiti 5.19.0 + Spring Boot application to Oracle database. By looking at the Activiti source code, I can see that is supports database table prefix and it's also specified in MyBatis configuration. However, I can't figure out how to set it thru configuration. I have the following config in my application.properties.

spring.activiti.databaseSchema=GM_ACTIVITI              #<— This one works
spring.activiti.databaseTablePrefix=GM_ACTIVITI.        #<— This one does not work
spring.activiti.tablePrefixIsSchema=true                       #<— This one does not work

Any idea how I can set databaseTablePrefix and tablePrefixIsSchema? Thanks a lot in advance!