topic Re: configuring database schema to something ELSE than 'public' in Alfresco Archive

configuring database schema to something ELSE than 'public'

ttfrttfr — Thu, 18 Oct 2012 16:09:38 GMT

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 f

Re: configuring database schema to something ELSE than 'public'

jbarrez — Fri, 19 Oct 2012 05:54:46 GMT

On the processEngineConfiguration bean you can set a table prefix:

/**
   * Allows configuring a database table prefix which is used for all runtime operations of the process engine.
   * For example, if you specify a prefix named 'PRE1.', activiti will query for executions in a table named
   * 'PRE1.ACT_RU_EXECUTION_'.
   *
   * <p />
   * <strong>NOTE: the prefix is not respected by automatic database schema management. If you use
   * {@link ProcessEngineConfiguration#DB_SCHEMA_UPDATE_CREATE_DROP}
   * or {@link ProcessEngineConfiguration#DB_SCHEMA_UPDATE_TRUE}, activiti will create the database tables
   * using the default names, regardless of the prefix configured here.</strong>
   *
   * @since 5.9
   */
public ProcessEngineConfiguration setDatabaseTablePrefix(String databaseTablePrefix) {
    this.databaseTablePrefix = databaseTablePrefix;
    return this;
}

I know this works on other database to change the schema. Would that work for postgres too (not an expert on postgres)?

Re: configuring database schema to something ELSE than 'public'

ttfrttfr — Fri, 19 Oct 2012 09:07:31 GMT

I did not succeed in configuring this property through the Spring processEngineConfiguration, it says this property is unknown.
I assume I should then setting it up at runtime…

Re: configuring database schema to something ELSE than 'public'

jbarrez — Mon, 22 Oct 2012 08:52:50 GMT

Are you sure? SpringProcessEngineConfiguration extends from ProcessEngineConfigurationImpl, which has

public ProcessEngineConfiguration setDatabaseTablePrefix(String databaseTablePrefix) {
this.databaseTablePrefix = databaseTablePrefix;
return this;
}

So, <property name="databaseTablePrefix" value="blah" /> should work …

Re: configuring database schema to something ELSE than 'public'

udhayakumar_t — Wed, 24 Jul 2013 11:50:17 GMT

where to find the tables used in activiti? Currently i am trying to setup activiti in local using MYSQL database. In the user guide, the following sets are mentioned.

Add the activiti-engine jars to your classpath
Add a suitable database driver
Add an Activiti configuration file (activiti.cfg.xml) to your classpath, pointing to your database (see database configuration section)
Execute the main method of the DbSchemaCreate class

However, i couldn't find the class DbSchemaCreate nowhere

Re: configuring database schema to something ELSE than 'public'

jbarrez — Thu, 01 Aug 2013 10:17:32 GMT

The easiest way is letting the engine do it for you, by adding this property to your process engine confi:

<code>
<property name="databaseSchemaUpdate" value="true" />
</code>

Re: configuring database schema to something ELSE than 'public'

arlen_bales — Wed, 14 Aug 2013 15:59:20 GMT

databaseTablePrefix - this property does not provide any functionality on schemas, this is simply a TABLE prefix.
databaseSchema - schema for activiti tables.
databaseSchemaUpdate - true, will create the default DB if not present or will update it.

database creation/update - database creation will not take into account databaseSchema, it will create everything into "public" schema.
For instance if you DB2 and public schema is R2, tables will be created there but queries after deploy will fail if you wont have databaseSchema = R2.

Re: configuring database schema to something ELSE than 'public'

jbarrez — Tue, 03 Sep 2013 08:50:56 GMT

In DB2, I believe the 'currentSchema' is configured through the jdbc connection, right?

Re: configuring database schema to something ELSE than 'public'

balunadig — Thu, 10 Apr 2014 21:49:09 GMT

Using activiti 5.14 and H2 database. I would like to be able to create the activiti tables in a named schema.

I have specified "databaseSchema" as follows in my camel-config.xml

property name="databaseSchema" value="activitiSchema"

And I get "couldn't create db schema" when the war gets deployed…

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.activiti.engine.ActivitiException: couldn't create db schema:
create table ACT_GE_PROPERTY (
NAME_ varchar(64),
VALUE_ varchar(300),
REV_ integer,
primary key (NAME_)
)

Re: configuring database schema to something ELSE than 'public'

jbarrez — Tue, 22 Apr 2014 10:18:29 GMT

H2 doesn't work like that. In h2 your jdbc url determines the schema automatically.

Re: configuring database schema to something ELSE than 'public'

korshak — Thu, 14 Aug 2014 08:16:23 GMT

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 ?

Re: configuring database schema to something ELSE than 'public'

jbarrez — Tue, 19 Aug 2014 09:33:48 GMT

> Where I can get this script for tables creation?

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

Re: configuring database schema to something ELSE than 'public'

jdaugustine — Wed, 26 Aug 2015 00:05:00 GMT

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.

Re: configuring database schema to something ELSE than 'public'

jdaugustine — Fri, 28 Aug 2015 16:03:50 GMT

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?

Re: configuring database schema to something ELSE than 'public'

vasile_dirla — Fri, 28 Aug 2015 17:56:37 GMT

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.

Re: configuring database schema to something ELSE than 'public'

vasile_dirla — Fri, 28 Aug 2015 19:26:47 GMT

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

Re: configuring database schema to something ELSE than 'public'

jdaugustine — Fri, 28 Aug 2015 19:49:57 GMT

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

Re: configuring database schema to something ELSE than 'public'

jubedus — Fri, 04 Mar 2016 14:06:12 GMT

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!