ranger-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From v..@apache.org
Subject [2/2] ranger git commit: RANGER-1820: Duplicate entries should be deleted before creation of unique index on x_group and x_group_users table
Date Mon, 02 Oct 2017 15:35:41 GMT
RANGER-1820: Duplicate entries should be deleted before creation of unique index on x_group and x_group_users table

Signed-off-by: Velmurugan Periasamy <vel@apache.org>


Project: http://git-wip-us.apache.org/repos/asf/ranger/repo
Commit: http://git-wip-us.apache.org/repos/asf/ranger/commit/2d1d5c03
Tree: http://git-wip-us.apache.org/repos/asf/ranger/tree/2d1d5c03
Diff: http://git-wip-us.apache.org/repos/asf/ranger/diff/2d1d5c03

Branch: refs/heads/ranger-0.7
Commit: 2d1d5c0357b60e3bcc601a8f35ea3e6e4101202f
Parents: 186233c
Author: pradeep <pradeep@apache.org>
Authored: Sat Sep 30 22:34:36 2017 +0530
Committer: Velmurugan Periasamy <vel@apache.org>
Committed: Mon Oct 2 11:31:50 2017 -0400

----------------------------------------------------------------------
 ...8-add-unique-constraint-on-table-x_group.sql | 45 ---------
 .../028-delete-xgroup-duplicate-references.sql  | 96 ++++++++++++++++++++
 ...9-add-unique-constraint-on-table-x_group.sql | 45 +++++++++
 ...8-add-unique-constraint-on-table-x_group.sql | 46 ----------
 .../028-delete-xgroup-duplicate-references.sql  | 65 +++++++++++++
 ...9-add-unique-constraint-on-table-x_group.sql | 46 ++++++++++
 ...8-add-unique-constraint-on-table-x_group.sql | 43 ---------
 .../028-delete-xgroup-duplicate-references.sql  | 68 ++++++++++++++
 ...9-add-unique-constraint-on-table-x_group.sql | 43 +++++++++
 ...8-add-unique-constraint-on-table-x_group.sql | 44 ---------
 .../028-delete-xgroup-duplicate-references.sql  | 75 +++++++++++++++
 ...9-add-unique-constraint-on-table-x_group.sql | 44 +++++++++
 ...8-add-unique-constraint-on-table-x_group.sql | 40 --------
 .../028-delete-xgroup-duplicate-references.sql  | 81 +++++++++++++++++
 ...9-add-unique-constraint-on-table-x_group.sql | 40 ++++++++
 15 files changed, 603 insertions(+), 218 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/mysql/patches/028-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/mysql/patches/028-add-unique-constraint-on-table-x_group.sql b/security-admin/db/mysql/patches/028-add-unique-constraint-on-table-x_group.sql
deleted file mode 100644
index 078fb99..0000000
--- a/security-admin/db/mysql/patches/028-add-unique-constraint-on-table-x_group.sql
+++ /dev/null
@@ -1,45 +0,0 @@
--- 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.
-
-drop procedure if exists create_unique_constraint_on_groupname;
-
-delimiter ;;
-create procedure create_unique_constraint_on_groupname() begin
-DECLARE loginID bigint(20);
- /* check tables exist or not */
-        if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_group' and column_name='group_name') then
-                /* check unique constraint exist on group_name column or not */
-                if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_group' and column_name='group_name' and column_key='UNI') then
-                        if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_group' and constraint_name='x_group_UK_group_name') then
-                                ALTER TABLE x_group ADD UNIQUE INDEX x_group_UK_group_name(group_name(767));
---	 			ALTER TABLE x_group MODIFY COLUMN group_name varchar(767) NOT NULL, ADD CONSTRAINT x_group_UK_group_name UNIQUE(group_name(767));
-                        end if;
-                end if;
-        end if;
-        if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_group_users' and column_name='group_name') then
-                if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_group_users' and column_name='user_id') then
-                /* check unique constraint exist on group_name column or not */
-                        if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_group_users' and constraint_name='x_group_users_UK_uid_gname') then
-                                ALTER TABLE x_group_users ADD UNIQUE INDEX x_group_users_UK_uid_gname(user_id,group_name(740));
--- 				ALTER TABLE x_group_users MODIFY COLUMN group_name varchar(767), ADD CONSTRAINT x_group_users_UK_uid_gname UNIQUE(user_id,group_name(767));
-                        end if;
-                end if;
-        end if;
-end;;
-
-delimiter ;
-call create_unique_constraint_on_groupname();
-
-drop procedure if exists create_unique_constraint_on_groupname;

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/mysql/patches/028-delete-xgroup-duplicate-references.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/mysql/patches/028-delete-xgroup-duplicate-references.sql b/security-admin/db/mysql/patches/028-delete-xgroup-duplicate-references.sql
new file mode 100644
index 0000000..811c5bb
--- /dev/null
+++ b/security-admin/db/mysql/patches/028-delete-xgroup-duplicate-references.sql
@@ -0,0 +1,96 @@
+-- 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.
+
+-- --------------------------------------------------------------------------------
+-- Procedure which shall remove duplicate entries from x_group table
+-- Duplicate entries were previously created due to unavailablity of unique index
+-- --------------------------------------------------------------------------------
+DELIMITER $$
+
+DROP PROCEDURE if exists deleteXGroupDuplicateReferences $$
+CREATE PROCEDURE `deleteXGroupDuplicateReferences`()
+BEGIN
+Block1: BEGIN
+
+DECLARE donecursor1 INT;
+DECLARE group_name1 varchar(1024);
+DECLARE mingroupid1 bigint;
+DECLARE id2 bigint;
+
+DECLARE cursor1 CURSOR FOR 
+	SELECT group_name,min(id) FROM x_group GROUP BY group_name HAVING count(group_name)>1;
+
+DECLARE CONTINUE HANDLER FOR NOT FOUND SET donecursor1 = 1;
+	OPEN cursor1;
+		REPEAT
+		FETCH cursor1 into group_name1, mingroupid1;
+			Block2: BEGIN
+				DECLARE donecursor2 INT DEFAULT 0;
+				DECLARE cursor2 CURSOR FOR SELECT id FROM x_group WHERE group_name= group_name1 AND id > mingroupid1;
+				DECLARE CONTINUE HANDLER FOR NOT FOUND SET donecursor2 = 1;
+				OPEN cursor2;
+				REPEAT
+				FETCH cursor2 INTO id2;
+					UPDATE x_group_users SET p_group_id=mingroupid1 where p_group_id=id2;
+				UNTIL donecursor2 END REPEAT;
+				CLOSE cursor2;
+			END Block2;
+		UNTIL donecursor1 END REPEAT;
+	CLOSE cursor1;
+END Block1;
+
+Block3: BEGIN
+
+DECLARE donecursor3 INT;
+DECLARE group_name3 varchar(1024);
+DECLARE user_id3 bigint;
+DECLARE minrowid3 bigint;
+
+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;
+
+DECLARE CONTINUE HANDLER FOR NOT FOUND SET donecursor3 = 1;
+	OPEN cursor3;
+		REPEAT
+		FETCH cursor3 into group_name3, user_id3, minrowid3;
+			DELETE FROM x_group_users WHERE group_name=group_name3 AND user_id=user_id3 AND id > minrowid3;
+		UNTIL donecursor3 END REPEAT;
+	CLOSE cursor3;
+END Block3;
+
+Block4: BEGIN
+
+DECLARE donecursor4 INT;
+DECLARE group_name4 varchar(1024);
+DECLARE group_id4 bigint;
+DECLARE minrowid4 bigint;
+
+DECLARE cursor4 CURSOR FOR 
+	SELECT group_name,min(id) FROM x_group GROUP BY group_name HAVING count(1)>1;
+
+DECLARE CONTINUE HANDLER FOR NOT FOUND SET donecursor4 = 1;
+	OPEN cursor4;
+		REPEAT
+		FETCH cursor4 into group_name4, minrowid4;
+			DELETE FROM x_group WHERE group_name=group_name4 AND id > minrowid4;
+		UNTIL donecursor4 END REPEAT;
+	CLOSE cursor4;
+END Block4;
+
+END $$
+DELIMITER ;
+call deleteXGroupDuplicateReferences();
+
+DROP PROCEDURE if exists deleteXGroupDuplicateReferences;

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/mysql/patches/029-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/mysql/patches/029-add-unique-constraint-on-table-x_group.sql b/security-admin/db/mysql/patches/029-add-unique-constraint-on-table-x_group.sql
new file mode 100644
index 0000000..078fb99
--- /dev/null
+++ b/security-admin/db/mysql/patches/029-add-unique-constraint-on-table-x_group.sql
@@ -0,0 +1,45 @@
+-- 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.
+
+drop procedure if exists create_unique_constraint_on_groupname;
+
+delimiter ;;
+create procedure create_unique_constraint_on_groupname() begin
+DECLARE loginID bigint(20);
+ /* check tables exist or not */
+        if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_group' and column_name='group_name') then
+                /* check unique constraint exist on group_name column or not */
+                if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_group' and column_name='group_name' and column_key='UNI') then
+                        if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_group' and constraint_name='x_group_UK_group_name') then
+                                ALTER TABLE x_group ADD UNIQUE INDEX x_group_UK_group_name(group_name(767));
+--	 			ALTER TABLE x_group MODIFY COLUMN group_name varchar(767) NOT NULL, ADD CONSTRAINT x_group_UK_group_name UNIQUE(group_name(767));
+                        end if;
+                end if;
+        end if;
+        if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_group_users' and column_name='group_name') then
+                if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_group_users' and column_name='user_id') then
+                /* check unique constraint exist on group_name column or not */
+                        if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_group_users' and constraint_name='x_group_users_UK_uid_gname') then
+                                ALTER TABLE x_group_users ADD UNIQUE INDEX x_group_users_UK_uid_gname(user_id,group_name(740));
+-- 				ALTER TABLE x_group_users MODIFY COLUMN group_name varchar(767), ADD CONSTRAINT x_group_users_UK_uid_gname UNIQUE(user_id,group_name(767));
+                        end if;
+                end if;
+        end if;
+end;;
+
+delimiter ;
+call create_unique_constraint_on_groupname();
+
+drop procedure if exists create_unique_constraint_on_groupname;

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/oracle/patches/028-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/028-add-unique-constraint-on-table-x_group.sql b/security-admin/db/oracle/patches/028-add-unique-constraint-on-table-x_group.sql
deleted file mode 100644
index d512465..0000000
--- a/security-admin/db/oracle/patches/028-add-unique-constraint-on-table-x_group.sql
+++ /dev/null
@@ -1,46 +0,0 @@
--- 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.
-DECLARE
-        v_count number:=0;
-        gu_count number:=0;
-BEGIN
-        select count(*) into v_count from user_tab_cols where table_name='X_GROUP' and column_name='GROUP_NAME';
-        if (v_count = 1) then
-                v_count:=0;
-                select count(*) into v_count from user_constraints where table_name='X_GROUP' and constraint_name='X_GROUP_UK_GROUP_NAME' and constraint_type='U';
-                if (v_count = 0) then
-                        v_count:=0;
-                        select count(*) into v_count from user_ind_columns WHERE table_name='X_GROUP' and column_name='GROUP_NAME' and index_name='X_GROUP_UK_GROUP_NAME';
-                        if (v_count = 0) then
-                                execute immediate 'ALTER TABLE x_group MODIFY(group_name VARCHAR(767)) ADD CONSTRAINT x_group_UK_group_name UNIQUE (group_name)';
-                        end if;
-                        commit;
-                end if;
-        end if;
-
-        select count(*) into gu_count from user_tab_cols where table_name='X_GROUP_USERS' and column_name='GROUP_NAME';
-        if (gu_count = 1) then
-                gu_count:=0;
-                select count(*) into gu_count from user_constraints where table_name='X_GROUP_USERS' and constraint_name='X_GROUP_USERS_UK_UID_GNAME' and constraint_type='U';
-                if (gu_count = 0) then
-                        gu_count:=0;
-                        select count(*) into gu_count from user_ind_columns WHERE table_name='X_GROUP_USERS' and column_name='GROUP_NAME' and index_name='X_GROUP_USERS_UK_UID_GNAME';
-                        if (gu_count = 0) then
-                                execute immediate 'ALTER TABLE x_group_users MODIFY(group_name VARCHAR(767)) ADD CONSTRAINT x_group_users_uk_uid_gname UNIQUE (user_id,group_name)';
-                        end if;
-                        commit;
-                end if;
-        end if;
-end;/

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/oracle/patches/028-delete-xgroup-duplicate-references.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/028-delete-xgroup-duplicate-references.sql b/security-admin/db/oracle/patches/028-delete-xgroup-duplicate-references.sql
new file mode 100644
index 0000000..7c017f9
--- /dev/null
+++ b/security-admin/db/oracle/patches/028-delete-xgroup-duplicate-references.sql
@@ -0,0 +1,65 @@
+-- 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.
+
+DECLARE
+	donecursor1 number:=0;
+	group_name1 VARCHAR2(1024);
+	mingroupid1 number:=0;
+	id2 number:=0;
+    group_name3 VARCHAR2(1024);
+	user_id3 number:=0;
+	minrowid3 number:=0;
+	group_name4 VARCHAR2(1024);
+	group_id4 number:=0;
+	minrowid4 number:=0;
+
+	CURSOR cursor1 IS 
+	SELECT group_name, min(id) FROM x_group GROUP BY group_name HAVING count(group_name)>1;
+	CURSOR cursor2 IS 
+	SELECT id FROM x_group WHERE group_name = group_name1 AND id > mingroupid1;
+	CURSOR cursor3 IS 
+	SELECT group_name,user_id,min(id) FROM x_group_users GROUP BY group_name,user_id HAVING count(1)>1;
+	CURSOR cursor4 IS 
+	SELECT group_name,min(id) FROM x_group GROUP BY group_name HAVING count(1)>1;
+
+BEGIN 
+   OPEN cursor1;
+   LOOP
+   FETCH cursor1 into group_name1, mingroupid1;
+      EXIT WHEN cursor1%notfound;
+      OPEN cursor2;
+      LOOP
+         FETCH cursor2 INTO id2;
+         	EXIT WHEN cursor2%notfound;
+         	UPDATE x_group_users SET p_group_id=mingroupid1 where p_group_id=id2;
+      END LOOP;
+      CLOSE cursor2;
+   END LOOP;
+   CLOSE cursor1;
+   OPEN cursor3;
+   LOOP
+   FETCH cursor3 into group_name3, user_id3, minrowid3;
+      EXIT WHEN cursor3%notfound;
+      	DELETE FROM x_group_users WHERE group_name=group_name3 AND user_id=user_id3 AND id > minrowid3;
+   END LOOP;
+   CLOSE cursor3;
+   OPEN cursor4;
+   LOOP
+   FETCH cursor4 into group_name4, minrowid4;
+      EXIT WHEN cursor4%notfound;
+      	DELETE FROM x_group WHERE group_name=group_name4 AND id > minrowid4;
+   END LOOP;
+   CLOSE cursor4;
+END;/
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/oracle/patches/029-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/029-add-unique-constraint-on-table-x_group.sql b/security-admin/db/oracle/patches/029-add-unique-constraint-on-table-x_group.sql
new file mode 100644
index 0000000..d512465
--- /dev/null
+++ b/security-admin/db/oracle/patches/029-add-unique-constraint-on-table-x_group.sql
@@ -0,0 +1,46 @@
+-- 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.
+DECLARE
+        v_count number:=0;
+        gu_count number:=0;
+BEGIN
+        select count(*) into v_count from user_tab_cols where table_name='X_GROUP' and column_name='GROUP_NAME';
+        if (v_count = 1) then
+                v_count:=0;
+                select count(*) into v_count from user_constraints where table_name='X_GROUP' and constraint_name='X_GROUP_UK_GROUP_NAME' and constraint_type='U';
+                if (v_count = 0) then
+                        v_count:=0;
+                        select count(*) into v_count from user_ind_columns WHERE table_name='X_GROUP' and column_name='GROUP_NAME' and index_name='X_GROUP_UK_GROUP_NAME';
+                        if (v_count = 0) then
+                                execute immediate 'ALTER TABLE x_group MODIFY(group_name VARCHAR(767)) ADD CONSTRAINT x_group_UK_group_name UNIQUE (group_name)';
+                        end if;
+                        commit;
+                end if;
+        end if;
+
+        select count(*) into gu_count from user_tab_cols where table_name='X_GROUP_USERS' and column_name='GROUP_NAME';
+        if (gu_count = 1) then
+                gu_count:=0;
+                select count(*) into gu_count from user_constraints where table_name='X_GROUP_USERS' and constraint_name='X_GROUP_USERS_UK_UID_GNAME' and constraint_type='U';
+                if (gu_count = 0) then
+                        gu_count:=0;
+                        select count(*) into gu_count from user_ind_columns WHERE table_name='X_GROUP_USERS' and column_name='GROUP_NAME' and index_name='X_GROUP_USERS_UK_UID_GNAME';
+                        if (gu_count = 0) then
+                                execute immediate 'ALTER TABLE x_group_users MODIFY(group_name VARCHAR(767)) ADD CONSTRAINT x_group_users_uk_uid_gname UNIQUE (user_id,group_name)';
+                        end if;
+                        commit;
+                end if;
+        end if;
+end;/

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/postgres/patches/028-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/028-add-unique-constraint-on-table-x_group.sql b/security-admin/db/postgres/patches/028-add-unique-constraint-on-table-x_group.sql
deleted file mode 100644
index 501ec2e..0000000
--- a/security-admin/db/postgres/patches/028-add-unique-constraint-on-table-x_group.sql
+++ /dev/null
@@ -1,43 +0,0 @@
--- 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.
-select 'delimiter start';
-CREATE OR REPLACE FUNCTION create_unique_constraint_on_username()
-RETURNS void AS $$
-DECLARE
- v_attnum integer := 0;
-gu_attnum integer := 0;
-BEGIN
- select attnum into v_attnum from pg_attribute where attrelid in(select oid from pg_class where relname='x_group') and attname='group_name';
- IF v_attnum > 0 THEN
-        IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_group') and conname='x_group_uk_group_name' and contype='u') THEN
-                IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_group') and indkey[0]=v_attnum) THEN
-                        ALTER TABLE x_group ALTER COLUMN group_name TYPE VARCHAR(767),ADD CONSTRAINT x_group_UK_group_name UNIQUE(group_name);
-                END IF;
-        END IF;
- END IF;
-
-select attnum into gu_attnum from pg_attribute where attrelid in(select oid from pg_class where relname='x_group_users') and attname='group_name';
- IF gu_attnum > 0 THEN
-        IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_group_users') and conname='x_group_users_UK_uid_gname' and contype='u') THEN
-                IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_group_users') and indkey[0]=gu_attnum) THEN
-                        ALTER TABLE x_group_users ALTER COLUMN group_name TYPE VARCHAR(767),ADD CONSTRAINT x_group_users_UK_uid_gname UNIQUE(user_id,group_name);
-                END IF;
-        END IF;
- END IF;
-
-END;
-$$ LANGUAGE plpgsql;
-select create_unique_constraint_on_username();
-select 'delimiter end';

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/postgres/patches/028-delete-xgroup-duplicate-references.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/028-delete-xgroup-duplicate-references.sql b/security-admin/db/postgres/patches/028-delete-xgroup-duplicate-references.sql
new file mode 100644
index 0000000..6fbb49e
--- /dev/null
+++ b/security-admin/db/postgres/patches/028-delete-xgroup-duplicate-references.sql
@@ -0,0 +1,68 @@
+-- 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.
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION delete_xgroup_duplicate_references()
+RETURNS void AS $$
+DECLARE
+	donecursor1 BIGINT:=0;
+	group_name1 VARCHAR(1024);
+	mingroupid1 BIGINT:=0;
+	id2 BIGINT:=0;
+	group_name3 VARCHAR(1024);
+	user_id3 BIGINT:=0;
+	minrowid3 BIGINT:=0;
+	group_name4 VARCHAR(1024);
+	group_id4 BIGINT:=0;
+	minrowid4 BIGINT:=0;
+
+	cursor1 cursor for SELECT group_name, min(id) FROM x_group GROUP BY group_name HAVING count(group_name)>1;
+	cursor2 cursor for SELECT id FROM x_group WHERE group_name = group_name1 AND id > mingroupid1;
+	cursor3 cursor for SELECT group_name,user_id,min(id) FROM x_group_users GROUP BY group_name,user_id HAVING count(1)>1;
+	cursor4 cursor for SELECT group_name,min(id) FROM x_group GROUP BY group_name HAVING count(1)>1;
+
+BEGIN 
+	OPEN cursor1;
+	LOOP
+		FETCH cursor1 into group_name1, mingroupid1;
+		EXIT WHEN NOT FOUND;
+	  	OPEN cursor2;
+	  	LOOP
+			FETCH cursor2 INTO id2;
+			EXIT WHEN NOT FOUND;
+			UPDATE x_group_users SET p_group_id=mingroupid1 where p_group_id=id2;
+		END LOOP;
+		CLOSE cursor2;
+	END LOOP;
+	CLOSE cursor1;
+
+	OPEN cursor3;
+	LOOP
+		FETCH cursor3 into group_name3, user_id3, minrowid3;
+		EXIT WHEN NOT FOUND;
+		DELETE FROM x_group_users WHERE group_name=group_name3 AND user_id=user_id3 AND id > minrowid3;
+	END LOOP;
+	CLOSE cursor3;
+
+	OPEN cursor4;
+	LOOP
+		FETCH cursor4 into group_name4, minrowid4;
+		EXIT WHEN NOT FOUND;
+	  	DELETE FROM x_group WHERE group_name=group_name4 AND id > minrowid4;
+	END LOOP;
+	CLOSE cursor4;
+END;
+$$ LANGUAGE plpgsql;
+select delete_xgroup_duplicate_references();
+select 'delimiter end';
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/postgres/patches/029-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/029-add-unique-constraint-on-table-x_group.sql b/security-admin/db/postgres/patches/029-add-unique-constraint-on-table-x_group.sql
new file mode 100644
index 0000000..501ec2e
--- /dev/null
+++ b/security-admin/db/postgres/patches/029-add-unique-constraint-on-table-x_group.sql
@@ -0,0 +1,43 @@
+-- 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.
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION create_unique_constraint_on_username()
+RETURNS void AS $$
+DECLARE
+ v_attnum integer := 0;
+gu_attnum integer := 0;
+BEGIN
+ select attnum into v_attnum from pg_attribute where attrelid in(select oid from pg_class where relname='x_group') and attname='group_name';
+ IF v_attnum > 0 THEN
+        IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_group') and conname='x_group_uk_group_name' and contype='u') THEN
+                IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_group') and indkey[0]=v_attnum) THEN
+                        ALTER TABLE x_group ALTER COLUMN group_name TYPE VARCHAR(767),ADD CONSTRAINT x_group_UK_group_name UNIQUE(group_name);
+                END IF;
+        END IF;
+ END IF;
+
+select attnum into gu_attnum from pg_attribute where attrelid in(select oid from pg_class where relname='x_group_users') and attname='group_name';
+ IF gu_attnum > 0 THEN
+        IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_group_users') and conname='x_group_users_UK_uid_gname' and contype='u') THEN
+                IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_group_users') and indkey[0]=gu_attnum) THEN
+                        ALTER TABLE x_group_users ALTER COLUMN group_name TYPE VARCHAR(767),ADD CONSTRAINT x_group_users_UK_uid_gname UNIQUE(user_id,group_name);
+                END IF;
+        END IF;
+ END IF;
+
+END;
+$$ LANGUAGE plpgsql;
+select create_unique_constraint_on_username();
+select 'delimiter end';

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/sqlanywhere/patches/028-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/028-add-unique-constraint-on-table-x_group.sql b/security-admin/db/sqlanywhere/patches/028-add-unique-constraint-on-table-x_group.sql
deleted file mode 100644
index c39e68d..0000000
--- a/security-admin/db/sqlanywhere/patches/028-add-unique-constraint-on-table-x_group.sql
+++ /dev/null
@@ -1,44 +0,0 @@
--- 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.
-BEGIN
-DECLARE tableID INT = 0;
-DECLARE columnID INT = 0;
-DECLARE guTableID INT = 0;
-DECLARE guColumnID INT = 0;
-        IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_group' and cname='group_name') THEN
-                IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_group_UK_group_name') THEN
-                        select table_id into tableID from SYS.SYSTAB where table_name = 'x_group';
-                        select column_id into columnID from SYS.SYSTABCOL where table_id=tableID and column_name = 'group_name';
-                        IF NOT EXISTS(select * from SYS.SYSIDXCOL where table_id=tableID and column_id=columnID) THEN
-                                ALTER TABLE dbo.x_group ALTER group_name varchar(767) NOT NULL;
-                                ALTER TABLE dbo.x_group ADD CONSTRAINT x_group_UK_group_name UNIQUE NONCLUSTERED (group_name);
-                        END IF;
-                END IF;
-        END IF;
-        IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_group_users' and cname='group_name') THEN
-                IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_group_users_UK_uid_gname') THEN
-                        select table_id into guTableID from SYS.SYSTAB where table_name = 'x_group_users';
-                        select column_id into guColumnID from SYS.SYSTABCOL where table_id=guTableID and column_name = 'group_name';
-                        IF NOT EXISTS(select * from SYS.SYSIDXCOL where table_id=guTableID and column_id=guColumnID) THEN
-                                ALTER TABLE dbo.x_group_users ALTER group_name varchar(767) NOT NULL;
-                                alter table dbo.x_group_users drop constraint x_group_users_FK_user_id;
-                                ALTER TABLE dbo.x_group_users ALTER user_id bigint NOT NULL;
-                                ALTER TABLE dbo.x_group_users ADD CONSTRAINT x_group_users_FK_user_id FOREIGN KEY(user_id) REFERENCES dbo.x_user (id);
-                                ALTER TABLE dbo.x_group_users ADD CONSTRAINT x_group_users_UK_uid_gname UNIQUE NONCLUSTERED (user_id,group_name);
-                        END IF;
-                END IF;
-        END IF;
-END
-GO

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/sqlanywhere/patches/028-delete-xgroup-duplicate-references.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/028-delete-xgroup-duplicate-references.sql b/security-admin/db/sqlanywhere/patches/028-delete-xgroup-duplicate-references.sql
new file mode 100644
index 0000000..46ef910
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/028-delete-xgroup-duplicate-references.sql
@@ -0,0 +1,75 @@
+-- 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.
+
+CREATE OR REPLACE PROCEDURE delete_xgroup_duplicate_references()
+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;
+	DECLARE cursor2 CURSOR FOR SELECT id FROM x_group WHERE group_name = group_name1 AND id > mingroupid1;
+	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;
+	DECLARE cursor4 CURSOR FOR SELECT group_name,min(id) FROM x_group GROUP BY group_name HAVING count(1)>1;
+
+	SET donecursor1=0;
+	SET mingroupid1=0;
+	SET id2=0;
+	SET user_id3=0;
+	SET minrowid3=0;
+	SET group_id4=0;
+	SET minrowid4=0;
+
+	OPEN cursor1;
+	loopc1: LOOP
+		FETCH cursor1 INTO group_name1, mingroupid1;
+		IF SQLCODE <> 0 THEN LEAVE loopc1 END IF;
+		OPEN cursor2;
+		loopc2: LOOP
+			FETCH cursor2 INTO id2;
+			IF SQLCODE <> 0 THEN LEAVE loopc2 END IF;
+			UPDATE x_group_users SET p_group_id=mingroupid1 where p_group_id=id2;
+		END LOOP;
+		CLOSE cursor2;
+	END LOOP;
+	CLOSE cursor1;
+
+	OPEN cursor3;
+	loopc3: LOOP
+		FETCH cursor3 INTO group_name3, user_id3, minrowid3;
+		IF SQLCODE <> 0 THEN LEAVE loopc3 END IF;
+		DELETE FROM x_group_users WHERE group_name=group_name3 AND user_id=user_id3 AND id > minrowid3;
+	END LOOP;
+	CLOSE cursor3;
+
+	OPEN cursor4;
+	loopc4: LOOP
+		FETCH cursor4 INTO group_name4, minrowid4;
+		IF SQLCODE <> 0 THEN LEAVE loopc4 END IF;
+	  	DELETE FROM x_group WHERE group_name=group_name4 AND id > minrowid4;
+	END LOOP;
+	CLOSE cursor4;
+END;
+GO
+EXEC delete_xgroup_duplicate_references;
+GO
+exit
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/sqlanywhere/patches/029-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/029-add-unique-constraint-on-table-x_group.sql b/security-admin/db/sqlanywhere/patches/029-add-unique-constraint-on-table-x_group.sql
new file mode 100644
index 0000000..c39e68d
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/029-add-unique-constraint-on-table-x_group.sql
@@ -0,0 +1,44 @@
+-- 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.
+BEGIN
+DECLARE tableID INT = 0;
+DECLARE columnID INT = 0;
+DECLARE guTableID INT = 0;
+DECLARE guColumnID INT = 0;
+        IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_group' and cname='group_name') THEN
+                IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_group_UK_group_name') THEN
+                        select table_id into tableID from SYS.SYSTAB where table_name = 'x_group';
+                        select column_id into columnID from SYS.SYSTABCOL where table_id=tableID and column_name = 'group_name';
+                        IF NOT EXISTS(select * from SYS.SYSIDXCOL where table_id=tableID and column_id=columnID) THEN
+                                ALTER TABLE dbo.x_group ALTER group_name varchar(767) NOT NULL;
+                                ALTER TABLE dbo.x_group ADD CONSTRAINT x_group_UK_group_name UNIQUE NONCLUSTERED (group_name);
+                        END IF;
+                END IF;
+        END IF;
+        IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_group_users' and cname='group_name') THEN
+                IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_group_users_UK_uid_gname') THEN
+                        select table_id into guTableID from SYS.SYSTAB where table_name = 'x_group_users';
+                        select column_id into guColumnID from SYS.SYSTABCOL where table_id=guTableID and column_name = 'group_name';
+                        IF NOT EXISTS(select * from SYS.SYSIDXCOL where table_id=guTableID and column_id=guColumnID) THEN
+                                ALTER TABLE dbo.x_group_users ALTER group_name varchar(767) NOT NULL;
+                                alter table dbo.x_group_users drop constraint x_group_users_FK_user_id;
+                                ALTER TABLE dbo.x_group_users ALTER user_id bigint NOT NULL;
+                                ALTER TABLE dbo.x_group_users ADD CONSTRAINT x_group_users_FK_user_id FOREIGN KEY(user_id) REFERENCES dbo.x_user (id);
+                                ALTER TABLE dbo.x_group_users ADD CONSTRAINT x_group_users_UK_uid_gname UNIQUE NONCLUSTERED (user_id,group_name);
+                        END IF;
+                END IF;
+        END IF;
+END
+GO

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/sqlserver/patches/028-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlserver/patches/028-add-unique-constraint-on-table-x_group.sql b/security-admin/db/sqlserver/patches/028-add-unique-constraint-on-table-x_group.sql
deleted file mode 100644
index 7559976..0000000
--- a/security-admin/db/sqlserver/patches/028-add-unique-constraint-on-table-x_group.sql
+++ /dev/null
@@ -1,40 +0,0 @@
-
--- 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 EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_group' and column_name = 'group_name')
-BEGIN
-        IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_group' and column_name='group_name' and constraint_name = 'x_group$x_group_UK_group_name')
-    BEGIN
-            IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_group' and constraint_name = 'x_group$x_group_UK_group_name' and CONSTRAINT_TYPE='UNIQUE')
-            BEGIN
-                ALTER TABLE [dbo].[x_group] ALTER COLUMN [group_name] [varchar](767) NOT NULL;
-                ALTER TABLE [dbo].[x_group] ADD CONSTRAINT [x_group$x_group_UK_group_name] UNIQUE ([group_name]);
-            END
-    END
-END
-GO
-IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_group_users' and column_name = 'group_name')
-BEGIN
-        IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_group_users' and column_name='group_name' and constraint_name = 'x_group_users$x_group_users_UK_uid_gname')
-    BEGIN
-            IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_group_users' and constraint_name = 'x_group_users$x_group_users_UK_uid_gname' and CONSTRAINT_TYPE='UNIQUE')
-            BEGIN
-                ALTER TABLE [dbo].[x_group_users] ALTER COLUMN [group_name] [varchar](767) NOT NULL;
-                ALTER TABLE [dbo].[x_group_users] ADD CONSTRAINT [x_group_users$x_group_users_UK_uid_gname] UNIQUE (user_id,group_name);
-            END
-    END
-END
-GO
-exit

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/sqlserver/patches/028-delete-xgroup-duplicate-references.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlserver/patches/028-delete-xgroup-duplicate-references.sql b/security-admin/db/sqlserver/patches/028-delete-xgroup-duplicate-references.sql
new file mode 100644
index 0000000..e8c56b0
--- /dev/null
+++ b/security-admin/db/sqlserver/patches/028-delete-xgroup-duplicate-references.sql
@@ -0,0 +1,81 @@
+-- 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
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/ranger/blob/2d1d5c03/security-admin/db/sqlserver/patches/029-add-unique-constraint-on-table-x_group.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlserver/patches/029-add-unique-constraint-on-table-x_group.sql b/security-admin/db/sqlserver/patches/029-add-unique-constraint-on-table-x_group.sql
new file mode 100644
index 0000000..7559976
--- /dev/null
+++ b/security-admin/db/sqlserver/patches/029-add-unique-constraint-on-table-x_group.sql
@@ -0,0 +1,40 @@
+
+-- 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 EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_group' and column_name = 'group_name')
+BEGIN
+        IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_group' and column_name='group_name' and constraint_name = 'x_group$x_group_UK_group_name')
+    BEGIN
+            IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_group' and constraint_name = 'x_group$x_group_UK_group_name' and CONSTRAINT_TYPE='UNIQUE')
+            BEGIN
+                ALTER TABLE [dbo].[x_group] ALTER COLUMN [group_name] [varchar](767) NOT NULL;
+                ALTER TABLE [dbo].[x_group] ADD CONSTRAINT [x_group$x_group_UK_group_name] UNIQUE ([group_name]);
+            END
+    END
+END
+GO
+IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_group_users' and column_name = 'group_name')
+BEGIN
+        IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_group_users' and column_name='group_name' and constraint_name = 'x_group_users$x_group_users_UK_uid_gname')
+    BEGIN
+            IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_group_users' and constraint_name = 'x_group_users$x_group_users_UK_uid_gname' and CONSTRAINT_TYPE='UNIQUE')
+            BEGIN
+                ALTER TABLE [dbo].[x_group_users] ALTER COLUMN [group_name] [varchar](767) NOT NULL;
+                ALTER TABLE [dbo].[x_group_users] ADD CONSTRAINT [x_group_users$x_group_users_UK_uid_gname] UNIQUE (user_id,group_name);
+            END
+    END
+END
+GO
+exit


Mime
View raw message