Add support for schema prefixes for table names

Our company has a standard where the application server user name setup in the datasource is different than the owning schema. For example, the activity tables and application tables are in schema X but the application user is X_APP_USER. Activiti ends up creating and using the tables in X_APP_USER but we want to be able to have them created and used in X.

There are a couple of ways to do this that we've determined.
1) setup a login trigger for the Oracle user to call ALTER SESSION SET CURRENT_SCHEMA=X
2) write a DataSource wrapper that calls ALTER SESSION SET CURRENT_SCHEMA=X upon 'getConnection' (this works and is implemented)
3) alter Activiti to support schema prefixes (hence this jira issue).

4) add synonyms to X_APP_USER (doesn't work if tables are initially created)

Yeah, we want this too.

I am currently experimenting with adding a general table prefix, such that you can specify it on the process engine configuration:

ProcessEngineConfiguration.createStandaloneInMemProcessEngineConfiguration()
.setDatabaseTablePrefix("PF1_")
.buildProcessEngine();

This way, activiti prefixes all table names with PF1_, so ACT_RU_EXECUTION becomes PF1_ACT_RU_EXECUTION and so forth.

I am renaming this issue to broaden the scope. Name before was "Add support for Oracle schema prefixes for table names".

We could also think about allowing to to specify the prefix on a per table or per table group basis (different prefix for engine, history, identity). Is there a need for this?

Initial implementation committed on branch: http://fisheye.codehaus.org/changelog/activiti/?cs=3378

Our customers are in great need for this feature and so far, none of them has expressed the need to have different prefixes for different tables. So, as far as I can tell, one 'global' prefix for all tables suffices.

Great to see that this will come in 5.10!

Merged to trunk in http://fisheye.codehaus.org/changelog/activiti/?cs=3383

We decided to not extend support for table prexifes to the auto-schema management component. So activiti will not create the tables using the prefix.

The following unit test illustrates this in greater detail:

http://svn.codehaus.org/activiti/activiti/trunk/modules/activiti-engine/src/test/java/org/activiti/engine/test/db/DatabaseTablePrefixTest.java

I think this is a good decision. Typically, DBAs create the tables and thus they take care of the table prefixes.