Migrating State Transitions from Roles to Permissions

posted Mar 26, 2015, 8:15 AM by Eric Patrick
Early version of the ObjectState table included RoleRequired, enabling a power user to determine which role was required to effect a state transition.  This has been deprecated in favor of PermissionRequired, enabling a power use to grant a permissions to a specific transition to multiple roles.

To migrate from one to the other, run the query below. It will:
  • Create SystemFunctions based on {ImportFormTemplate}{StatusFrom}To{StatusTo}
  • Grant the appropriate Role permission to the function
  • Set the ObjectState.PermissionRequired column to the new value


DECLARE @T TABLE (ID int, RoleRequired nvarchar(50), Permission nvarchar(50))
INSERT INTO @T 

SELECT
ObjectStateID,
RoleRequired,
REPLACE(
REPLACE(
REPLACE(LEFT(ImportFormTEmplate, 50-LEN(ISNULL(LEFT(StatusFrom,15), 'None'))-LEN(ISNULL(LEFT(StatusTo,15), 'None'))) + ISNULL(LEFT(StatusFrom, 15), 'None') + 'To' + ISNULL(LEFT(StatusTo,15), 'None'), ' ', ''),
'/', ''
),
'-', ''
)
FROM ObjectState
INNER JOIN ImportFormTemplate
ON ImportFormTemplateID = ObjectState.TemplateID
WHERE RoleREquired IS NOT NULL
AND PermissionRequired IS NULL

SELECT * FROM @T

INSERT INTO SystemFunction (SystemFunction)
SELECT Permission FROM @T AS T WHERE NOT EXISTS (
SELECT 1 FROM SystemFunction WHERE SystemFunction = Permission
)

-- 
-- INSERT INTO SystemPermission (SystemRoleID, SystemFunctionID, UpdatedPersonID, UpdatedDate)
SELECT SystemRole.SystemRoleID,
SystemFunction.SystemFunctionID, 
1,
GETDATE()
FROM @T AS T
INNER JOIN SystemRole
ON SystemRole.SystemRole = T.RoleREquired
LEFT OUTER JOIN SystemFunction
ON SystemFunction.SystemFunction = T.Permission
WHERE NOT EXISTS (
SELECT 1 FROM SystemPermission
WHERE SystemRoleID = SystemRole.SystemRoleID
AND SystemFunctionID = SystemFunction.SystemFunctionID
)

UPDATE ObjectState
SET PermissionRequired = Permission
FROM @T AS T
INNER JOIN OBjectState
ON ObjectSTateID= T.ID

Comments