Users and Groups where Groups can be members of groups

PremissionEntities.sql

/*
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_GroupMembership]'))
DROP VIEW [dbo].[V_GroupMembership]
GO




CREATE VIEW dbo.V_GroupMembership 
AS
*/ 
WITH CTE AS 
(
	SELECT 
		 T_PermissionEntity.Id
		,T_PermissionEntity.Name
		,T_PermissionEntity.IsGroup
		,T_PermissionEntity.IsUser
		,0 AS Level 
		,'/' + CAST(T_PermissionEntity.Id AS varchar(MAX)) + '/' AS Path -- MS 
		--,array[T_PermissionEntity.Id] AS Path -- PG 
	FROM T_PermissionEntity
	WHERE T_PermissionEntity.IsUser = 1 
	--AND T_PermissionEntity.Name = 'User 666' 
	--AND T_PermissionEntity.Id = 101 
	--AND T_PermissionEntity.Id = 102
	--AND T_PermissionEntity.Id = 103
	
	UNION ALL 
	
	SELECT 
		 T_PermissionEntity.Id
		,T_PermissionEntity.Name
		,T_PermissionEntity.IsGroup
		,T_PermissionEntity.IsUser
		,CTE.Level + 1 AS Level 
		,CTE.Path + CAST(T_PermissionEntity.Id AS varchar(MAX))+ '/' AS Path -- MS 
		--,CTE.Path || T_PermissionEntity.Id AS Path -- PG
	FROM CTE 
	
	INNER JOIN T_PermissionEntityMap 
		ON T_PermissionEntityMap.Id_Child = CTE.Id 
		
	INNER JOIN T_PermissionEntity 
		ON T_PermissionEntity.Id = T_PermissionEntityMap.Id_Parent 
		AND T_PermissionEntity.IsGroup = 1 
		-- NoCycle 
		AND CTE.Path NOT LIKE ('%/' + CAST(T_PermissionEntity.Id AS varchar(20))+ '/%') -- MS 
		--AND T_PermissionEntity.Id <> ALL (CTE.Path) -- PG 
		-- CONNECT BY NOCYCLE PRIOR 
) 
SELECT TOP 9999999 * FROM CTE 
WHERE Path LIKE '/666%' 

ORDER BY Level, Path 
OPTION (MAXRECURSION 0) 


-- http://stackoverflow.com/questions/7105879/graph-problems-connect-by-nocycle-prior-replacement-in-sql-server
CREATE TABLE dbo.T_PermissionEntity
(
	 Id bigint NOT NULL 
	,Name national character varying(255) NULL 
	,IsGroup bit NOT NULL 
	,IsUser bit NOT NULL 
	,CONSTRAINT PK_T_PermissionEntity PRIMARY KEY (Id ASC)
);

CREATE TABLE dbo.T_PermissionEntityMap
(
	 Id bigint NOT NULL 
	,Id_Parent bigint NOT NULL 
	,Id_Child bigint NOT NULL 
	,CONSTRAINT PK_T_PermissionEntityMap PRIMARY KEY (Id ASC)
);

ALTER TABLE dbo.T_PermissionEntityMap 
WITH CHECK ADD CONSTRAINT FK_T_PermissionEntityMap_Id_Parent_T_PermissionEntity 
FOREIGN KEY(Id_Parent) REFERENCES dbo.T_PermissionEntity(Id) 
;

ALTER TABLE dbo.T_PermissionEntityMap CHECK CONSTRAINT FK_T_PermissionEntityMap_Id_Parent_T_PermissionEntity
;


ALTER TABLE dbo.T_PermissionEntityMap 
WITH CHECK ADD CONSTRAINT FK_T_PermissionEntityMap_Id_Child_T_PermissionEntity 
FOREIGN KEY(Id_Child) REFERENCES dbo.T_PermissionEntity(Id) 
;

ALTER TABLE dbo.T_PermissionEntityMap CHECK CONSTRAINT FK_T_PermissionEntityMap_Id_Child_T_PermissionEntity
;




DELETE FROM T_PermissionEntity;
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (1, N'Gruppe 1', 1, 0);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (2, N'Gruppe 2', 1, 0);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (3, N'Gruppe 3', 1, 0);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (4, N'Gruppe 4', 1, 0);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (5, N'Gruppe 5', 1, 0);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (101, N'User1', 0, 1);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (102, N'User 2', 0, 1);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (103, N'User 3', 0, 1);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (666, N'User 666', 0, 1);
INSERT T_PermissionEntity (Id, Name, IsGroup, IsUser) VALUES (777, N'User777', 0, 1);

DELETE FROM T_PermissionEntityMap;
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (1, 1, 101);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (2, 2, 102);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (3, 3, 103);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (4, 4, 1);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (5, 4, 2);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (6, 4, 3);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (7, 5, 1);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (8, 1, 666);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (9, 2, 666);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (10, 4, 5);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (11, 3, 666);
INSERT T_PermissionEntityMap (Id, Id_Parent, Id_Child) VALUES (12, 5, 4);