SchemaCreation considering TablePrefix

[catandgrep]

Hi folks,

is there a need for extending DbSchema creation routines to take care of the table prefix and database schema? The current release does not allow schema creation with prefixes by the activiti engine. Instead admins need to manually create/update all the tables.

It would be quiet handy, to start a dedicated engine (e.g. with non-tx data source) and execute multiple schema creations through the management service for different runtime engines (e.g. for different tenants in larger activiti scenarios).

Is there any special reason why schema creation was not parameterized yet? If not I would contribute the change, but i would might need some help to test it on all databases. Most part of the change would go into DbSqlSession and (due to additional placeholders) to the schema sql files.

The management service of course already has the functionality to trigger schema creation/update, but without prefix.

Tell me what you think.

Regards
Andreas
——–
agito

[jbarrez]

The reason why we didn't do it, is because we see that in most companies the creation of the database is not allowed by applications itself.
In many companies, this is the job of the DBA, who will inspect the DDL's and tweak where needed. As such, that is why we don't prioritize it.

Also, this would not be an easy fix, because we would need to find a way to keep the DDL files the way they are now, while also providing a way to add the prefix. For the runtime tables it is easier, as we can simply do it in the MyBatis mappings. Do you have an idea how you'd tackle it?

[catandgrep]

The way to handle existing ddl can be implemented without redundancy. Why not simply add placeholders into the scripts before each table name and then replace these placeholders with schema and tableprefix upon DDL execution.

A placeholder could be {ACT_PREFIX}, so the DDL would look similar to this


create table {ACT_PREFIX}ACT_GE_PROPERTY (
    NAME_ varchar(64) not null,
    VALUE_ varchar(300),
    REV_ integer,
    primary key (NAME_)
);

Any line where a table name is mentioned would contain the placeholder.

Replacement implementation would fit quiet well into DbSqlSession.addSqlStatementPiece() where each line is handled.

As there are 2 properties available on the configuration, there would be 3 cases for the replacement:

Schema = null, TablePrefix = null >> ACT_PREFIX = ""

Schema = "MYSCHEMA", TablePrefix = null >> ACT_PREFIX = "MYSCHEMA." << watch the '.' at the end

Schema = null, TablePrefix = "MYTABLEPREFIX" >> ACT_PREFIX = "MYTABLEPREFIX"

Any more concerns 

The remark about DbAdmins is of course one side of the customer world, others like it well integrated into the application server, or they prefer admin cockpits within a larger bpm platform solution. Especially looking at cloud solutions the latter might be expected from a bpm solution.

[jbarrez]

Any more concerns

Sure, I've thought of that approach (it is the same as the one for the MyBatis mapping files). But then you can't just take the files and execute them directly, as you'd always need to go through a 'search and replace' phase.

The remark about DbAdmins is of course one side of the customer world, others like it well integrated into the application server, or they prefer admin cockpits within a larger bpm platform solution. Especially looking at cloud solutions the latter might be expected from a bpm solution.

Of course. But in our experience, Activiti is typically used in enterprise environments with dba's.
However, I do agree that in development mode it should be easier.

That being said, what is wrong with doing (eg on Oracle)

ALTER SESSION SET CURRENT_SCHEMA = <schema name>
And then execute all the DDL's?

I'll ping the other core developers and see what their opinion is.

[meyerd]

I am with Joram on this.

You can also have a look at the following unit test:
org.activiti.engine.test.db.DatabaseTablePrefixTest

[catandgrep]

Just as an information:
It's not all enterprise environments, that handle DB actions that strict up to production, so that only db admins are allowed to create and update schemas in production. SAP Java Servers for example have their own packaging type for DB Schema deployment, and it's always a developer task to create such deployment artifacts. (As always) SAP has introduced an additional abstraction layer (called Java Dictionary) to ensure consistency across db vendors and to block incompatible schema updates.

However from activity point fo view, I see two kinds of 'table prefix'-situations, and please correct me if I get them wrong:
1. Schema creation within isolated DB SCHEMAs. That's what you mean by executing ALTER SESSION SET CURRENT_SCHEMA = <schema name> or within org.activiti.engine.test.db.DatabaseTablePrefixTest.
2. Schema creation within the same DB SCHEMA but with different table name prefixes. So the table names have an additional prefix that do NOT necessarily specify the schema.

Especially the last one would not work with ALTER SESSION statement.

Both scenarios are supported by activiti-runtime components, but not yet by the 'schema-handling'-part. Isn't that a gap that needs to be filled?

I see your point, that executing the plain sql files won't be possible when we have placeholders in there. But wouldn't it be another idea, to do it the other way around then: So when invoking the schema creation methods, they take care of adding the prefix during execution.

What do you think?

[jbarrez]

I see your point, that executing the plain sql files won't be possible when we have placeholders in there. But wouldn't it be another idea, to do it the other way around then: So when invoking the schema creation methods, they take care of adding the prefix during execution.

Sure, I understand that suggestion. But my point still remains: then we make it easier for developers (and only those with specific environments) and make it harder for DBA's. I would be fine with a solution that doesn't interfere with the plain sql DDL files for a dba. Or do you see a suggestion that is smart enough to inject those prefixes when invoking the schema?

The only solution which could work is if we centralise all our database schema in something like Liquibase (http://www.liquibase.org/). That way, we can generate the DDL's for the dba's and also generate other files to be used at runtime. But such a thing would be a very costly thing to do, time-wise and is currently not on our priority list …

[catandgrep]

Or do you see a suggestion that is smart enough to inject those prefixes when invoking the schema?

Yup - why not keep it simple instead of using something like Liquibase:
Let's assume the names of all activiti tables is a constant string array. During script execution each line of the current DDL script can be parsed and checked, if any table name is part of the line. If so AND if an prefix/schema is defined, the necessary piece will be injected: Either with a simple string operation, or by using a regular expression - which I prefer.

This piece of code can be placed in org.activiti.engine.impl.db.DbSqlSession and more specifically in method addSqlStatementPiece(String sqlStatement, String line), or a new method that is called from within addSqlStatementPiece.

[catandgrep]

There have been recent commits regarding schema creation/update refactoring with liquibase.

Are you also considering custom tableprefix / schema handling within that refactoring?

TablePrefix/schema is already available on the engineConfiguration, but I don't see them to come into play so far for schema creation/update.

[mortezaadi]

I think schema creation and table prefixes are two different concern. Schema creation should be handled by DBA however table creation can be done in application level (to make the job easier in development, perhaps ).
I found a way to create tables with prefixes and keep the DDL files the way they are now. all activiti tables already have a prefix "ACT_" so replacing the prefix can do the job.

https://github.com/Activiti/Activiti/pull/172