User Group Mappings - MultiMember + Cycle, recursive workaround

UserGroupMappings.sql

/*
CREATE TABLE T_PermissionEntity
(
	Id bigint NOT NULL,
	Name national character varying(255) NULL,
	IsGroup bit NULL,
	IsUser bit NULL,
	CONSTRAINT PK_T_PermissionEntity PRIMARY KEY (Id)
);

GO

CREATE TABLE T_PermissionEntityMap 
( 
	Id bigint NOT NULL,
	Id_Parent bigint NULL,
	Id_Child bigint NULL,
	CONSTRAINT PK_T_PermissionEntityMap PRIMARY KEY (Id) 
)
;
*/


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.T_UserGroups') AND type in (N'U'))
EXECUTE('
CREATE TABLE dbo.T_UserGroups
(
	 Id bigint NOT NULL 
	,Name national character varying(255) NULL 
	,CONSTRAINT PK_T_UserGroups PRIMARY KEY (Id) 
) ; 
')

GO 


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.T_Users') AND type in (N'U'))
EXECUTE('
CREATE TABLE dbo.T_Users
(
	 Id bigint NOT NULL 
	,Name national character varying(255) NULL 
	,CONSTRAINT PK_T_Users PRIMARY KEY (Id)
) ;
')

GO



IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.T_User_Group_Union') AND type in (N'U'))
BEGIN 
EXECUTE('
CREATE TABLE dbo.T_User_Group_Union
(
	 Id bigint IDENTITY(1,1) NOT NULL 
	,Id_Group bigint NULL 
	,Id_User bigint NULL 
	,IsUser bit NOT NULL 
	,IsGroup bit NOT NULL 
	,CONSTRAINT PK_T_User_Group_Union PRIMARY KEY (Id) 
) ; 
') 
END 
GO


IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.[FK_T_User_Group_Union_T_UserGroups]') AND parent_object_id = OBJECT_ID(N'dbo.T_User_Group_Union'))
ALTER TABLE dbo.T_User_Group_Union DROP CONSTRAINT [FK_T_User_Group_Union_T_UserGroups]
GO

ALTER TABLE dbo.T_User_Group_Union  WITH CHECK ADD  CONSTRAINT [FK_T_User_Group_Union_T_UserGroups] FOREIGN KEY([Id_Group])
REFERENCES dbo.T_UserGroups (Id) 
	-- ON UPDATE CASCADE 
	-- ON DELETE CASCADE -- This is just a virtual table maintained by triggers, to circumvent cascade delete 
GO

ALTER TABLE dbo.T_User_Group_Union CHECK CONSTRAINT [FK_T_User_Group_Union_T_UserGroups]
GO




IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.[FK_T_User_Group_Union_T_Users]') AND parent_object_id = OBJECT_ID(N'dbo.T_User_Group_Union'))
ALTER TABLE dbo.T_User_Group_Union DROP CONSTRAINT [FK_T_User_Group_Union_T_Users]
GO

ALTER TABLE dbo.T_User_Group_Union  WITH CHECK ADD  CONSTRAINT [FK_T_User_Group_Union_T_Users] FOREIGN KEY([Id_User])
REFERENCES dbo.T_Users (Id) 
	-- ON UPDATE CASCADE 
	-- ON DELETE CASCADE -- This is just a pseudo-table maintained by triggers, to circumvent cascade delete 
GO

ALTER TABLE dbo.T_User_Group_Union CHECK CONSTRAINT [FK_T_User_Group_Union_T_Users]
GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.T_UsersGroupsMap') AND type in (N'U'))
EXECUTE('
CREATE TABLE dbo.T_UsersGroupsMap 
( 
	 Id bigint NOT NULL 
	,Id_Parent bigint NULL 
	,Id_Child bigint NULL 
	,CONSTRAINT PK_T_UsersGroupsMap PRIMARY KEY ( Id ASC ) 
); 
')
;



IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_T_UsersGroupsMap_Id_Child_T_UsersGroupsMap') AND parent_object_id = OBJECT_ID(N'dbo.T_UsersGroupsMap'))
ALTER TABLE dbo.T_UsersGroupsMap DROP CONSTRAINT FK_T_UsersGroupsMap_Id_Child_T_UsersGroupsMap
GO

ALTER TABLE dbo.T_UsersGroupsMap  WITH CHECK ADD  CONSTRAINT FK_T_UsersGroupsMap_Id_Child_T_UsersGroupsMap FOREIGN KEY(Id_Child)
REFERENCES dbo.T_User_Group_Union (Id) 
ON UPDATE CASCADE 
ON DELETE CASCADE
GO

ALTER TABLE dbo.T_UsersGroupsMap CHECK CONSTRAINT FK_T_UsersGroupsMap_Id_Child_T_UsersGroupsMap
GO





IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_T_UsersGroupsMap_Id_Parent_T_UserGroups') AND parent_object_id = OBJECT_ID(N'dbo.T_UsersGroupsMap'))
ALTER TABLE dbo.T_UsersGroupsMap DROP CONSTRAINT FK_T_UsersGroupsMap_Id_Parent_T_UserGroups
GO

ALTER TABLE dbo.T_UsersGroupsMap  WITH CHECK ADD  CONSTRAINT FK_T_UsersGroupsMap_Id_Parent_T_UserGroups FOREIGN KEY(Id_Parent)
REFERENCES dbo.T_UserGroups (Id) 
ON UPDATE CASCADE 
ON DELETE CASCADE 
GO

ALTER TABLE dbo.T_UsersGroupsMap CHECK CONSTRAINT FK_T_UsersGroupsMap_Id_Parent_T_UserGroups
GO


IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.TG_INSTEAD_DEL_T_UserGroups_DeleteEntriesInT_User_Group_Union'))
DROP TRIGGER dbo.TG_INSTEAD_DEL_T_UserGroups_DeleteEntriesInT_User_Group_Union
GO


CREATE TRIGGER dbo.TG_INSTEAD_DEL_T_UserGroups_DeleteEntriesInT_User_Group_Union
    ON dbo.T_UserGroups 
    INSTEAD OF DELETE
AS
BEGIN
	DELETE FROM dbo.T_User_Group_Union 
	WHERE T_User_Group_Union.Id_Group IN(SELECT deleted.Id FROM deleted)
	;
	DELETE FROM dbo.T_UserGroups 
	WHERE T_UserGroups.Id IN(SELECT deleted.Id FROM deleted)
END 


GO





/*
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.TG_DEL_T_UserGroups_DeleteEntriesInT_User_Group_Union'))
DROP TRIGGER dbo.TG_DEL_T_UserGroups_DeleteEntriesInT_User_Group_Union 
GO



CREATE TRIGGER dbo.TG_DEL_T_UserGroups_DeleteEntriesInT_User_Group_Union 
    ON dbo.T_UserGroups 
    FOR DELETE
AS
BEGIN
	DELETE FROM dbo.T_User_Group_Union 
	WHERE T_User_Group_Union.Id_Group IN(SELECT deleted.Id FROM deleted)
END 

GO
*/



IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.TG_INS_T_UserGroups_InsertEntriesInT_User_Group_Union'))
DROP TRIGGER dbo.TG_INS_T_UserGroups_InsertEntriesInT_User_Group_Union
GO



CREATE TRIGGER dbo.TG_INS_T_UserGroups_InsertEntriesInT_User_Group_Union
	ON dbo.T_UserGroups 
FOR INSERT
AS
BEGIN 
	INSERT INTO T_User_Group_Union(/*Id,*/Id_Group,Id_User, IsUser, IsGroup) 
    SELECT inserted.Id, NULL, 'false', 'true' 
    FROM inserted 
END 

GO





IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.TG_INS_T_Users_InsertEntriesInT_User_Group_Union'))
DROP TRIGGER dbo.TG_INS_T_Users_InsertEntriesInT_User_Group_Union
GO




CREATE TRIGGER dbo.TG_INS_T_Users_InsertEntriesInT_User_Group_Union
	ON dbo.T_Users 
FOR INSERT
AS
BEGIN 
	INSERT INTO T_User_Group_Union(/*Id,*/Id_Group,Id_User, IsUser, IsGroup) 
    SELECT NULL, inserted.Id, 'true', 'false' 
    FROM inserted 
END 


GO





IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.TG_INSTEAD_DEL_T_Users_DeleteEntriesInT_User_Group_Union'))
DROP TRIGGER dbo.TG_INSTEAD_DEL_T_Users_DeleteEntriesInT_User_Group_Union
GO



CREATE TRIGGER dbo.TG_INSTEAD_DEL_T_Users_DeleteEntriesInT_User_Group_Union
    ON dbo.T_Users 
    INSTEAD OF DELETE
AS
BEGIN
	DELETE FROM dbo.T_User_Group_Union 
	WHERE T_User_Group_Union.Id_User IN(SELECT deleted.Id FROM deleted)
	;
	DELETE FROM dbo.T_Users 
	WHERE T_Users.Id IN(SELECT deleted.Id FROM deleted)
END 


GO



----------------------------------------------




IF EXISTS 
( 
	SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE 
	WHERE (1=1) 
	AND CONSTRAINT_SCHEMA = 'dbo' 
	AND TABLE_NAME = 'T_UsersGroupsMap' 
	AND CONSTRAINT_NAME = 'CK_T_PermissionEntityMap_IsParentGroup' 
) 

ALTER TABLE dbo.T_UsersGroupsMap DROP CONSTRAINT CK_T_PermissionEntityMap_IsParentGroup 
GO


IF EXISTS 
( 
	SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE (1=1) 
	AND ROUTINE_TYPE = 'function' 
	AND SPECIFIC_SCHEMA = 'dbo' 
	AND SPECIFIC_NAME = 'fn_CHECK_T_PermissionEntityMap_IsParentGroup' 
) 
DROP FUNCTION dbo.fn_CHECK_T_PermissionEntityMap_IsParentGroup 
GO



CREATE FUNCTION dbo.fn_CHECK_T_PermissionEntityMap_IsParentGroup(@Id bigint, @Id_Parent AS bigint, @Id_Child as bigint) 
    RETURNS bit
AS
BEGIN  
	--RETURN (SELECT COALESCE(IsGroup, 'false') FROM T_PermissionEntity WHERE Id = @Id_Parent) 
	-- Id_Group = Groups ! 
	RETURN (SELECT COALESCE(IsGroup, 'false') FROM T_User_Group_Union WHERE T_User_Group_Union.Id_Group = @Id_Parent) 
END


GO


ALTER TABLE dbo.T_UsersGroupsMap  WITH NOCHECK ADD CONSTRAINT CK_T_PermissionEntityMap_IsParentGroup 
CHECK  
( 
    (
        dbo.fn_CHECK_T_PermissionEntityMap_IsParentGroup(Id, Id_Parent, Id_Child) = 1 
    )
)
GO


ALTER TABLE dbo.T_UsersGroupsMap CHECK CONSTRAINT CK_T_PermissionEntityMap_IsParentGroup 
GO



-- Testing
SELECT T_User_Group_Union.Id
      ,T_User_Group_Union.Id_Group
      ,T_User_Group_Union.Id_User
      ,T_User_Group_Union.IsUser
      ,T_User_Group_Union.IsGroup
      ,T_UserGroups.Id
      ,COALESCE(T_Users.Name, T_UserGroups.Name) AS Name 
FROM T_User_Group_Union

LEFT JOIN T_UserGroups 
	ON T_UserGroups.Id = T_User_Group_Union.ID_Group 
	
LEFT JOIN T_Users 
	ON T_Users.Id = T_User_Group_Union.ID_User 
	
	
ORDER BY IsUser, Name 


-- View Hierarchy
SELECT 
	 T_UsersGroupsMap.Id 
	,T_UsersGroupsMap.Id_Parent AS ParentGroupId 
	,Id_Child 
	
	
	,T_UserGroups.Name AS Parent 
	,COALESCE(T_Users.Name, ChildGroup.Name) AS Child 
	,ChildGroup.Id AS ChildGroupId 
	,T_Users.Id AS ChildUserId 
FROM T_UsersGroupsMap 
	
LEFT JOIN T_UserGroups 
	ON T_UserGroups.Id = T_UsersGroupsMap.Id_Parent 
	
LEFT JOIN T_User_Group_Union 
	ON T_User_Group_Union.Id = T_UsersGroupsMap.Id_Child 
	
LEFT JOIN T_UserGroups AS ChildGroup  
	ON ChildGroup.Id = T_User_Group_Union.Id_Group  
	
LEFT JOIN T_Users 
	ON T_Users.Id = T_User_Group_Union.Id_User 
	
ORDER BY Parent, Child 



-- -----------------------------


-- ----------------------------- Query -------------------------------

;WITH CTE AS 
(
	SELECT 
		 T_User_Group_Union.Id
		,Id_Parent
		,Id_Child
		,T_User_Group_Union.Id_Group 
		--,COALESCE(T_UserGroups.Name, T_Users.Name) as Name
		--,0 AS Level 
		,'/' + CAST(T_User_Group_Union.Id AS varchar(MAX)) + '/' AS Path -- MS 
	FROM T_UsersGroupsMap -- Id,Id_Parent,Id_Child

	LEFT JOIN T_User_Group_Union -- Id, Id_Group, Id_User
		ON T_User_Group_Union.Id = T_UsersGroupsMap.Id_Child
			
	--LEFT JOIN T_UserGroups ON T_UserGroups.ID = T_User_Group_Union.Id_Group 
	--LEFT JOIN T_Users ON T_Users.Id = T_User_Group_Union.Id_User 
		
	WHERE T_UsersGroupsMap.Id_Parent IN 
	(
		SELECT Id_Parent FROM T_UsersGroupsMap WHERE Id_Child = 
		(
			SELECT Id FROM T_User_Group_Union WHERE Id_User = 666
			-- SELECT Id FROM T_User_Group_Union WHERE Id_User = ( SELECT Id FROM T_Users WHERE Id = 666 --WHERE Name = 'User 666')
		) 
	) -- id_parent
	
	UNION ALL 
	
	SELECT 
		 T_User_Group_Union.Id
		,T_UsersGroupsMap.Id_Parent
		,T_UsersGroupsMap.Id_Child
		,T_User_Group_Union.Id_Group 
		--,T_UserGroups.Name
		--,CTE.Level + 1 AS Level 
		,CTE.Path + CAST(T_User_Group_Union.Id AS varchar(MAX))+ '/' AS Path -- MS 
	FROM CTE 
	
	INNER JOIN T_UsersGroupsMap 
		ON T_UsersGroupsMap.Id_Parent = CTE.Id_Group 
		
	INNER JOIN T_User_Group_Union
		ON T_User_Group_Union.Id = T_UsersGroupsMap.Id_Child 
		AND T_User_Group_Union.IsGroup = 1 
		-- NoCycle 
		AND CTE.Path NOT LIKE ('%/' + CAST(T_User_Group_Union.Id AS varchar(20))+ '/%') -- MS 
		-- CONNECT BY NOCYCLE PRIOR 
		
	--INNER JOIN T_UserGroups ON T_UserGroups.ID = T_User_Group_Union.Id_Group 
) 
SELECT 
	 Id 
	--,Name 
FROM CTE