Increase lenght of String process variables

[bernd_ruecker]

Hey guys.

We should increase the possible size for Strings as process variables in the database! Since in most projects I see today we save XML (instead of objects at least) 255 bytes are a bit limiting 😉 And especially with Test cases it is hard to change that on-the-fly with the in memory H2.

Can we change the default to TEXT? So we have enough space?

Cheers
Bernd

[bernd_ruecker]

Quick addition: I added this to the setUp of my tests to get it working as a workaround, but that's a bit hacky:


    ProcessEngineConfigurationImpl processEngineConfiguration = ((ProcessEngineImpl)processEngine).getProcessEngineConfiguration();     SqlSession session = processEngineConfiguration.getDbSqlSessionFactory().getSqlSessionFactory().openSession();
    Connection connection = session.getConnection();
    Statement jdbcStatement = connection.createStatement();
    jdbcStatement.execute("ALTER TABLE ACT_RU_VARIABLE ALTER TEXT_ CLOB");
    jdbcStatement.close();


[jbarrez]

There is already some discussion done on this topic: http://jira.codehaus.org/browse/ACT-614

Basically switching to TEXT would disallow querying on those variables, so that is not a real option.

[bernd_ruecker]

Eh? You can still query on a text field, or why not? Or does some database disallow that? Which ones?

My expectation always was, that the database handle these nuts and bolts, so I don't have to care about 😉

[frederikherema1]

The type TEXT is actually a CLOB on other DB's. Oracle has no support for this (only through DBMS_LOB package), and think there were others as well. When I implemented the execution/process-instance query based on process-variables (variableValueEquals, variableValueNotEquals, variableValueLike(string)) I did some research on this. So thinking the DB will do the nuts and bolts isn't always working

What do you think about the suggestion I made here: http://jira.codehaus.org/browse/ACT-236 ? Automagically uses BLOB if too long, best of both worlds?

Another solution could be the folowing:

The org.activiti.engine.impl.variable.StringType should overflow all characters between 255 and 510 into the TEXT2_ column.
StringType getValue() returns concatenated string including TEXT1_ and TEXT2_ columns.
Querying for String-values will continue to work untill 510 (eg. executionQuery.variableValueEquals('str', 'XXX')
When String is longer than 510 characters, false is returned when calling 'isAbleToStore(…)'. The String will eventually be stored by the SerializableType.
This way, strings can grow transparantly larger than the limitation. Offcourse, the limitaion for variable-value-querying should be docced.

WDYT?

[bernd_ruecker]

I don't think that is a good option. if you think of XML as payload, you almost always have more than 510  characters. And storing that as BLOB is a pain in the ass (and I guess querying for that is not possible as well then).

Can all databases maybe handle a VARCHAR(10000) or something like this? That would be still much better than a blob. Actually before I have a blob I even would prefer living with the hack in the test cases and tune the database myself 😉 It always surprises me, what Oracle cannot handle 😉

[mproch]

I think there are already some vendor-specific sql mappings, so maybe we could consider adding one more for being able to query clob contents in db vendor specific way (although I think it won't be v.effective)?

On the other hand, Bernd, do you actually consider querying these xmls stored in text fields by value? I think usually it's not reasonable thing to do - I mean, one usually wants to use xpath or some similar stuff then. And if you just want to retrieve/set values easily by means of get/setVariable, then I think that Frederik's proposition is OK - the fact that the value is stored in (C)Lob is then transparent.
At least this is how I understand it

[frederikherema1]

Bernd, limit on oracle is 4000 in nvarchar2 (and about 2000 in UTF-8 I think). Not sure about others though..

From a usage perspective, I don't think it matters how it is stored underlying, as long as you can store long variables and short ones I guess How is storing a blob a pain in the ass? This is handled transparently (if you serialize the string), exactly the nuts and bolts you were reffering to.

[bernd_ruecker]

Ever tried to look into serialized values on the database? Sooner or later you will face this situation. It is not about runtime, it is about monitoring and operations.

At least serializing a String is less evil than own Java classes, but anyway I don't like it. Then I would go for the 2000 characters and document how to change this.

[frederikherema1]

Bernd, yes I did -> My MySQL Administrator even displays images in the DB-column inspector as well as text in binary columns