Activiti 5.0 Engine ported to SQL Server 2008

[k_kunti]

Hi All

I was trying to port Activiti 5.0 (ENGINE ONLY) to Microsoft SQL server (as mentioned in the WIKI); got some encouraging results:


Tests run: 460, Failures: 2, Errors: 0, Skipped: 0

These two cases relates to

1. classname="org.activiti.engine.test.api.repository.ProcessDefinitionQueryTest" name="testQueryByNameLike"

2. classname="org.activiti.engine.test.api.runtime.RuntimeServiceTest" name="testNonUniqueBusinessKey"… unique business key

I have in included the files in the zip file (MS SQL SERVER Support\TestSuiteErrorDetails)

I have also included scripts for create and dropping of database; Please check if something needs to be modified here.


Have modified one test suite class : org.activiti.examples.mgmt.ManagementServiceTest; to include appropriate data time for SQL server  assertOneOf(new String [] {"TIMESTAMP", "TIMESTAMP(6)","DATETIME"}. Please find the class at location: MS SQL SERVER Support\Test_Suite_class_change\org\activiti\examples\mgmt


Have added one new class (MssqlDbMetaDataHandler.java) and modified one existing class (TableMetaDataCacheHandler.java), I have included these classes at location:

MS SQL SERVER Support\activiti-engine\org\activiti\engine\impl\db

The code was built in Activiti 5.0 tag , SVN details can be found in: Activiti 5.0 tag URL.txt. The command to run the code is included in: command.txt

There is some discussion on the web on creation of (MS SQL server specific) unique constraints on a column that allows multiple NULL…the URL is included in the document  Link for SQL for unique constraint Business Key.txt…. I tried this one but runs into issues.


HELP Needed from Activity team and Community on the following:

1. Run the test to confirm the results I am getting, correct any mistakes made in the SQL.
2. Creation of MYbatis SQL server query for Link for SQL for unique constraint Business Key.txtclassname="org.activiti.engine.test.api.repository.ProcessDefinitionQueryTest" name="testQueryByNameLike"


3. Help regarding solving: classname="org.activiti.engine.test.api.runtime.RuntimeServiceTest" name="testNonUniqueBusinessKey"… unique business key

4.Tying any loose end.

Thanks in advance

Krish

[ronald_van_kuij]

thanks so far. I created ACT-542. hopefully others will help to

[thilka]

Found some information about the behaviour of SQL Server. It seems like SQL Server does not support the ANSI Standard (allowing multiple nulls in unique constraints) until now.

See this link:
http://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-mul...

Tobias

[thilka]

By the way:

I tried your patches with the current 5.1 release and SQL Server 2008. Besides the issue with the unique constraint ACT_UNIQ_RU_BUS_KEY, the tests of our application run properly with SQL Server 2008.

Hope we will have the SQL Server support in 5.2.

Tobias

[k_kunti]

Hi

Everyone thank you . it will be great if we can include it in the 5.2 release ….

Thanks gain
krish

[tombaeyens]

Krich,

It would be great if you could sign a contributor agreement so that we can accept your contribution.
Instructions can be found here: http://docs.codehaus.org/pages/viewpage.action?pageId=163872771

Then we can help you commit these changes on a branch so that we can review and adopt your work in the main codebase.

[k_kunti]

Hi

I checked the issue related to::::  

2. Creation of MYbatis SQL server query for Link for SQL for unique constraint Business Key.txtclassname="org.activiti.engine.test.api.repository.ProcessDefinitionQueryTest" name="testQueryByNameLike"

Looks like SQL server default vocabulary is case insensitive and hence LIKE expression matching will result case insensitive results. I validated the same by running query in my SQL server installation.

So this issue is inherent to default SQL server installation…. any suggestions regarding how to proceed on this one.

Thanks in advance
Krish

[k_kunti]

Hi Tom/Joram

I have applied for developer membership application in Codehaus Activiti page; as well as got permission from my employer towards making this contribution. I will be mailing you the agreement soon.

Kindly approve.

Thank you
Krish

[redwood7]

In SQL Server 2008 you can use filtered indexes for the ACT_UNIQ_RU_BUS_KEY and ACT_UNIQ_HI_BUS_KEY. So instead of removing those constraints entirely from the MSSQL create script, create them like this:


CREATE UNIQUE INDEX ACT_UNIQ_RU_BUS_KEY
ON dbo.ACT_RU_EXECUTION(PROC_DEF_ID_,BUSINESS_KEY_)
WHERE BUSINESS_KEY_ IS NOT NULL;
   
CREATE UNIQUE INDEX ACT_UNIQ_HI_BUS_KEY
ON dbo.ACT_HI_PROCINST(PROC_DEF_ID_,BUSINESS_KEY_)
WHERE BUSINESS_KEY_ IS NOT NULL;


[k_kunti]

Hi Redwood

Thank you, I will change the script and re run the test cases, to see the impact.

Krish