topic Re: SchemaCreation considering TablePrefix in Alfresco Archive

SchemaCreation considering TablePrefix

catandgrep — Wed, 14 Nov 2012 22:02:56 GMT

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 st

Re: SchemaCreation considering TablePrefix

jbarrez — Thu, 15 Nov 2012 18:28:19 GMT

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?

Re: SchemaCreation considering TablePrefix

catandgrep — Thu, 15 Nov 2012 20:52:42 GMT

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:

ACT_PREFIX = ""

ACT_PREFIX = "MYSCHEMA."

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.

Re: SchemaCreation considering TablePrefix

jbarrez — Fri, 16 Nov 2012 18:08:06 GMT

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.

Re: SchemaCreation considering TablePrefix

meyerd — Mon, 19 Nov 2012 08:35:55 GMT

I am with Joram on this.

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

Re: SchemaCreation considering TablePrefix

catandgrep — Mon, 19 Nov 2012 10:40:58 GMT

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?

Re: SchemaCreation considering TablePrefix

jbarrez — Mon, 19 Nov 2012 20:18:42 GMT

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 …

Re: SchemaCreation considering TablePrefix

catandgrep — Mon, 19 Nov 2012 22:01:20 GMT

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.

Re: SchemaCreation considering TablePrefix

catandgrep — Thu, 07 Feb 2013 08:07:37 GMT

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.

Re: SchemaCreation considering TablePrefix

mortezaadi — Sun, 10 Nov 2013 00:19:37 GMT

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

Re: SchemaCreation considering TablePrefix

jkronegg — Wed, 23 Apr 2014 07:37:57 GMT

I understand the idea of keeping the ".sql" scripts free of placeholders for raw usage in database, but the current situation is a bit embarassing in 5.15.1: you change the schema or databaseTablePrefix in a configuration with automatic table creation and get ugly exceptions about "Table <yourPrefix>XXX not found" because the table was created without the <yourPrefix> prefix. This makes you think that Activiti has some issues. I had to use the debugger to see how the table creation is done and look on the forum to find this post after several keyword search on Google and on the forum. I don't think most people evaluating Activiti will do that…

Why not have a kind of automatic placeholder for table creation in the DbSqlSession#executeSchemaResource instead of having them in the ".sql" file ?
The `sqlStatement` variable could be modified as such:
<code>
String prefix = … // determined from dbSqlSessionFactory.getDatabaseTablePrefix() and dbSqlSessionFactory.getDatabaseSchema ;
sqlStatement = sqlStatement.replaceFirst("create table (?!"+prefix+")", "create table "+prefix); // if there is no prefix, we add it
</code>

The alternative would be to raise an error when a configuration mismatch is present e.g.
<code>
if (isAutomaticSchemaCreationOrUpdateSpecified && isSchemaOrDatabaseTablePrefixSpecified) {
throw new IllegalArgumentException("automatic schema creation/update is not allowed with custom schema or databaseTablePrefix");
}
</code>

Re: SchemaCreation considering TablePrefix

frederikherema1 — Thu, 24 Apr 2014 07:46:47 GMT

The replacing approach should be done with a lot of constructs (alter table, add index, …) and is error-prone IMHO. Having a warning/error might make sense, although the javadocs on ProcessEngineConfiguration are rather clear on the behaviour:


 /**
   * 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
   */
  protected String databaseTablePrefix = "";
  
  /**
   * In some situations you want to set the schema to use for table checks / generation if the database metadata
   * doesn't return that correctly, see https://jira.codehaus.org/browse/ACT-1220,
   * https://jira.codehaus.org/browse/ACT-1062
   */
  protected String databaseSchema = null;

Re: SchemaCreation considering TablePrefix

jkronegg — Thu, 24 Apr 2014 20:28:45 GMT

You're right, I hadn't thought about all the SQL constructs and the javadoc describes the issue. However, the javadoc is on the field and is not displayed when when you look at the getter/setter in an IDE such as Eclipse and only a few people will read the code before using the product.

I'm in favor of raising an exception because this would clearly notify the developper that some misconfiguration occured (the warning may not be seen). And this would prevent to polluate the database with table without prefixes when the developper wants tables with prefix.

But you could even make it friendlier to the community by adding a <code>protected String transformSqlStatement(String sqlStatement) { return sqlStatement; }</code> which is called on the "sqlStatement" just before executing it, and provide a way to make the DbSqlSession pluggable so that developpers can use their own implementation with an overrided "transformSqlStatement" method.

Julien

Re: SchemaCreation considering TablePrefix

frederikherema1 — Fri, 25 Apr 2014 08:16:01 GMT

Julien, makes sense. Any chance you're willing to contribute this fix to activiti, since you're deep into it already

Source-jar's and javadoc-jars should be in maven central (since 5.14), so if you're using maven in your IDE, it should download the sources and show javadoc.

Re: SchemaCreation considering TablePrefix

jkronegg — Thu, 08 May 2014 22:14:58 GMT

Having studied the table generation deeper, I don't think its possible to have both the exception check and the "transformSqlStatement" if a "transformation plugin" is present because we will not be aware of the plugin behavior (would it implement the prefix stuff or only format the query for pretty printing when logged?).

Moreover, the behavior is really difficult to predict since the developper may want to change the configuration from one version to the other. For example if he first uses databaseTablePrefix=A then for an update a databaseTablePrefix=B (both with schemaUpdate=true), the script should drop A*, then create B*. So the "transformSqlRequest plugin" would need to know both prefixes to build the tables. Definitely a difficult task…

I ended up with the following patch:
<code>
— C:/partage/Softs/Java/Librairies/Activiti/activiti-5.15.1/libs/activiti-engine-5.15.1-sources/org/activiti/engine/impl/db/DbSqlSession.java Fri May 09 00:15:00 2014
+++ C:/Dev/workspace/activiti-src/activiti-engine-5.15.1-sources/org/activiti/engine/impl/db/DbSqlSession.java Fri May 09 00:03:13 2014
@@ -32,6 +32,7 @@
import java.util.regex.Pattern;

import org.activiti.engine.ActivitiException;
+import org.activiti.engine.ActivitiIllegalArgumentException;
import org.activiti.engine.ActivitiOptimisticLockingException;
import org.activiti.engine.ActivitiWrongDbException;
import org.activiti.engine.ProcessEngine;
@@ -1124,6 +1125,12 @@

   public void performSchemaOperationsProcessEngineBuild() {
     String databaseSchemaUpdate = Context.getProcessEngineConfiguration().getDatabaseSchemaUpdate();
+    String databaseTablePrefix = Context.getProcessEngineConfiguration().getDatabaseTablePrefix();
+ if (!org.activiti.engine.ProcessEngineConfiguration.DB_SCHEMA_UPDATE_FALSE.equals(databaseSchemaUpdate) &&
+ databaseTablePrefix!=null && !databaseTablePrefix.isEmpty()) {
+     // update/update_create_drop/drop_create not compatible with prefix => raise error
+ throw new ActivitiIllegalArgumentException("databaseTablePrefix is not supported when using databaseSchemaUpdate, see http://forums.activiti.org/content/schemacreation-considering-tableprefix");
+    }
     if (ProcessEngineConfigurationImpl.DB_SCHEMA_UPDATE_DROP_CREATE.equals(databaseSchemaUpdate)) {
       try {
         dbSchemaDrop();
</code>

It probably miss the trick between the databaseSchema and the databaseTablePrefix.