security-admin/db/sqlserver/patches/028-delete-xgroup-duplicate-references.sql

-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements.  See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License.  You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

IF (OBJECT_ID('delete_xgroup_duplicate_references') IS NOT NULL)
BEGIN
    DROP PROCEDURE [dbo].[delete_xgroup_duplicate_references]
END
GO
CREATE PROCEDURE delete_xgroup_duplicate_references
AS BEGIN
	DECLARE @donecursor1 bigint
	DECLARE @group_name1 varchar(1024)
	DECLARE @mingroupid1 bigint
	DECLARE @id2 bigint
	DECLARE @group_name3 varchar(1024)
	DECLARE @user_id3 bigint
	DECLARE @minrowid3 bigint
	DECLARE @group_name4 varchar(1024)
	DECLARE @group_id4 bigint
	DECLARE @minrowid4 bigint
	DECLARE cursor1 CURSOR FOR SELECT group_name, min(id) FROM x_group GROUP BY group_name HAVING count(group_name)>1
	OPEN cursor1
		FETCH NEXT FROM cursor1 INTO @group_name1, @mingroupid1
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			DECLARE cursor2 CURSOR FOR SELECT id FROM x_group WHERE group_name = @group_name1 AND id > @mingroupid1
			OPEN cursor2
				FETCH NEXT FROM cursor2 INTO @id2
				WHILE (@@FETCH_STATUS = 0)
				BEGIN
					UPDATE x_group_users SET p_group_id=@mingroupid1 where p_group_id=@id2
					FETCH NEXT FROM cursor2 INTO @id2 
				END
			CLOSE cursor2
			DEALLOCATE cursor2
			FETCH NEXT FROM cursor1 INTO @group_name1, @mingroupid1
		END
	CLOSE cursor1
	DEALLOCATE cursor1
	
	DECLARE cursor3 CURSOR FOR SELECT group_name,user_id,min(id) FROM x_group_users GROUP BY group_name,user_id HAVING count(1)>1
	OPEN cursor3
		FETCH NEXT FROM cursor3 INTO @group_name3, @user_id3, @minrowid3
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			DELETE FROM x_group_users WHERE group_name=@group_name3 AND user_id=@user_id3 AND id > @minrowid3
			FETCH NEXT FROM cursor3 INTO @group_name3, @user_id3, @minrowid3
		END
	CLOSE cursor3
	DEALLOCATE cursor3

	DECLARE cursor4 CURSOR FOR SELECT group_name,min(id) FROM x_group GROUP BY group_name HAVING count(1)>1
	OPEN cursor4
		FETCH NEXT FROM cursor4 INTO @group_name4, @minrowid4
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
	  		DELETE FROM x_group WHERE group_name=@group_name4 AND id > @minrowid4
	  		FETCH NEXT FROM cursor4 INTO @group_name4, @minrowid4
		END
	CLOSE cursor4
	DEALLOCATE cursor4
END
GO
IF (OBJECT_ID('delete_xgroup_duplicate_references') IS NOT NULL)
BEGIN
	EXEC delete_xgroup_duplicate_references
END
GO
exit