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 <[email protected]> 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 <[email protected]> Authored: Sat Sep 30 22:34:36 2017 +0530 Committer: Velmurugan Periasamy <[email protected]> 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
