Bug 1272120

Clone of https://issues.jboss.org/browse/JBPM-4768

Hi Kris,
it seems that you can disable the constraints instead of drooping/recreating them:
SQL Server discussion: http://www.coderanch.com/t/463860/JDBC/databases/Foreign-Key-Restricts-Alter-data
DB2 discussion: http://stackoverflow.com/questions/421518/is-there-a-way-to-enable-disable-constraints-in-db2-v7

Disabling/Enabling is safer than dropping and also it is easier to maintain in scripts

Wasn't successful on disabling constraints on MySQL, if I do the following:

ALTER TABLE SessionInfo NOCHECK CONSTRAINT ALL;
ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0);
...

still getting:

 ALTER TABLE SessionInfo NOCHECK CONSTRAINT ALL
17:43:23.682 [main] DEBUG b.t.r.c.TransactionContextHelper - enlisting a JdbcPooledConnection from datasource jdbc/testDS1 in state ACCESSIBLE with usage count 1 wrapping SQLServerXAConnection:2 into null
17:43:23.682 [main] DEBUG b.t.r.c.TransactionContextHelper - in local transaction context, skipping enlistment
 ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0)
17:43:23.992 [main] DEBUG b.t.r.c.TransactionContextHelper - enlisting a JdbcPooledConnection from datasource jdbc/testDS1 in state ACCESSIBLE with usage count 1 wrapping SQLServerXAConnection:2 into null
17:43:23.992 [main] DEBUG b.t.r.c.TransactionContextHelper - in local transaction context, skipping enlistment
...

java.lang.RuntimeException: The object 'PK__SessionI__3213E83FB20D16EE' is dependent on column 'id'.

yes, I think we will not have only a solution to satisfy all databases . For MySQL seems that dropping the constraints is the solution:

http://stackoverflow.com/questions/13606469/cannot-change-column-used-in-a-foreign-key-constraint

I have a MySQL instance and it seems working even with data.

Sorry, I meant SQL server 2012, not mysql.  Afaik, no issues were reported on mysql with the existing scripts.

@Fspolti from GSS is looking to help with the script for DB2 and SQLServer. It should update this BZ or sent the PR still today or tomorrow

Hello Team.

I updated the SQL Server scripts and seems and it worked in my environment.
Changes:

Remove: ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0)

Add:

IF EXISTS (SELECT * FROM sys.objects WHERE type ='P' AND name = 'alter_table_session_info')
DROP PROCEDURE alter_table_session_info
-- Creating the procedure to delete the constraint from SessionInfo table
GO
CREATE PROCEDURE alter_table_session_info
AS
        DECLARE @const_name VARCHAR(255)
        DECLARE @sqlDroppingConstraint VARCHAR(255)
        DECLARE @sqlAlterTableSessionInfo VARCHAR(255)
        DECLARE @sqlRecriateConstraint VARCHAR(255)
 
        SELECT @const_name = (SELECT CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='SessionInfo')
        BEGIN
                -- dropping Constraint
                SELECT @sqlDroppingConstraint = 'ALTER TABLE SessionInfo DROP CONSTRAINT ' + @const_name
                EXEC (@sqlDroppingConstraint)
 
                -- Changing the SessionInfo table
                SELECT @sqlAlterTableSessionInfo = 'ALTER TABLE SessionInfo ALTER COLUMN id numeric(19,0)'
                EXEC (@sqlAlterTableSessionInfo)
 
                -- Recriating the constraint
                SELECT @sqlRecriateConstraint = 'SELECT @sqlRecriateConstraint = 'ALTER TABLE SessionInfo ADD ' + @const_name + 'PRIMARY KEY CLUSTERED ([id] ASC)'
                EXEC @sqlRecriateConstraint
        END
GO
-- Executing the procedure
EXECUTE alter_table_session_info
-- Deleting the procedure to clean it from database
DROP PROCEDURE alter_table_session_info
-- Recriating the Index
ALTER INDEX ALL ON SessionInfo REORGANIZE;


and where have something like it: UPDATE Task t SET name = (SELECT shortText FROM I18NText WHERE Task_Names_Id = t.id)
Change to UPDATE Task SET name = (SELECT shortText FROM I18NText WHERE Task_Names_Id = Task.id)

do the same to TaskEvent.




I am now verifying the DB2 script, when I get it finished I'll send the PR.

PR: https://github.com/droolsjbpm/jbpm/pull/318

Please validate.

master: https://github.com/droolsjbpm/jbpm/commit/b1b5c3480

6.3.x: http://github.com/droolsjbpm/jbpm/commit/b5b410edc

I set this to MODIFIED since it will be included in ER5.

Based on Ryan's comment, I'm moving this to ON_QA

Returning this. When there are no sessions stored in SessionInfo table, the datatype change fails. I made a PR for this [1].

[1] https://github.com/droolsjbpm/jbpm/pull/336.

My previous comment applies for DB2.

This PR was already merged in 6.3.x:
https://github.com/droolsjbpm/jbpm/commit/b29c4fd348b33821623930c4631db8d282ba8e35

Verified in 6.2.0.CR1.