RANGER-1040 : Add unique key constraint on user_name column of x_user table
Signed-off-by: Velmurugan Periasamy <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/incubator-ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ranger/commit/6e07ed2c Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/6e07ed2c Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/6e07ed2c Branch: refs/heads/master Commit: 6e07ed2c9306c8541df24dcfb5f27324b80d45be Parents: 04378e3 Author: pradeep agrawal <[email protected]> Authored: Fri Jun 17 23:55:56 2016 +0530 Committer: Velmurugan Periasamy <[email protected]> Committed: Mon Jun 20 23:07:42 2016 -0400 ---------------------------------------------------------------------- ...23-add-unique-constraint-on-table-x_user.sql | 35 ++++++++++++++++++++ ...23-add-unique-constraint-on-table-x_user.sql | 32 ++++++++++++++++++ ...23-add-unique-constraint-on-table-x_user.sql | 32 ++++++++++++++++++ ...23-add-unique-constraint-on-table-x_user.sql | 31 +++++++++++++++++ ...23-add-unique-constraint-on-table-x_user.sql | 28 ++++++++++++++++ 5 files changed, 158 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6e07ed2c/security-admin/db/mysql/patches/023-add-unique-constraint-on-table-x_user.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/023-add-unique-constraint-on-table-x_user.sql b/security-admin/db/mysql/patches/023-add-unique-constraint-on-table-x_user.sql new file mode 100644 index 0000000..ea7c8a3 --- /dev/null +++ b/security-admin/db/mysql/patches/023-add-unique-constraint-on-table-x_user.sql @@ -0,0 +1,35 @@ +-- 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_username; + +delimiter ;; +create procedure create_unique_constraint_on_username() 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_user' and column_name='user_name') then + /* check unique constraint exist on user_name column or not */ + if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_user' and column_name='user_name' and column_key='UNI') then + if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_user' and constraint_name='x_user_UK_user_name') then + ALTER TABLE x_user MODIFY COLUMN user_name varchar(767) NOT NULL, ADD CONSTRAINT x_user_UK_user_name UNIQUE(user_name(767)); + end if; + end if; + end if; +end;; + +delimiter ; +call create_unique_constraint_on_username(); + +drop procedure if exists create_unique_constraint_on_username; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6e07ed2c/security-admin/db/oracle/patches/023-add-unique-constraint-on-table-x_user.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/023-add-unique-constraint-on-table-x_user.sql b/security-admin/db/oracle/patches/023-add-unique-constraint-on-table-x_user.sql new file mode 100644 index 0000000..022aa4c --- /dev/null +++ b/security-admin/db/oracle/patches/023-add-unique-constraint-on-table-x_user.sql @@ -0,0 +1,32 @@ +-- 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; +BEGIN + select count(*) into v_count from user_tab_cols where table_name='X_USER' and column_name='USER_NAME'; + if (v_count = 1) then + v_count:=0; + select count(*) into v_count from user_constraints where table_name='X_USER' and constraint_name='X_USER_UK_USER_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_USER' and column_name='USER_NAME' and index_name='X_USER_UK_USER_NAME'; + if (v_count = 0) then + execute immediate 'ALTER TABLE x_user MODIFY(user_name VARCHAR(767)) ADD CONSTRAINT x_user_UK_user_name UNIQUE (user_name)'; + end if; + commit; + end if; + end if; +end;/ http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6e07ed2c/security-admin/db/postgres/patches/023-add-unique-constraint-on-table-x_user.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/patches/023-add-unique-constraint-on-table-x_user.sql b/security-admin/db/postgres/patches/023-add-unique-constraint-on-table-x_user.sql new file mode 100644 index 0000000..f05674b --- /dev/null +++ b/security-admin/db/postgres/patches/023-add-unique-constraint-on-table-x_user.sql @@ -0,0 +1,32 @@ +-- 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; +BEGIN + select attnum into v_attnum from pg_attribute where attrelid in(select oid from pg_class where relname='x_user') and attname='user_name'; + IF v_attnum > 0 THEN + IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_user') and conname='x_user_uk_user_name' and contype='u') THEN + IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_user') and indkey[0]=v_attnum) THEN + ALTER TABLE x_user ALTER COLUMN user_name TYPE VARCHAR(767),ADD CONSTRAINT x_user_UK_user_name UNIQUE(user_name); + END IF; + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; +select create_unique_constraint_on_username(); +select 'delimiter end'; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6e07ed2c/security-admin/db/sqlanywhere/patches/023-add-unique-constraint-on-table-x_user.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlanywhere/patches/023-add-unique-constraint-on-table-x_user.sql b/security-admin/db/sqlanywhere/patches/023-add-unique-constraint-on-table-x_user.sql new file mode 100644 index 0000000..e99c2b1 --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/023-add-unique-constraint-on-table-x_user.sql @@ -0,0 +1,31 @@ +-- 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; + IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_user' and cname='user_name') THEN + IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_user_UK_user_name') THEN + select table_id into tableID from SYS.SYSTAB where table_name = 'x_user'; + select column_id into columnID from SYS.SYSTABCOL where table_id=tableID and column_name = 'user_name'; + IF NOT EXISTS(select * from SYS.SYSIDXCOL where table_id=tableID and column_id=columnID) THEN + ALTER TABLE dbo.x_user ALTER user_name varchar(767) NOT NULL; + ALTER TABLE dbo.x_user ADD CONSTRAINT x_user_UK_user_name UNIQUE NONCLUSTERED (user_name); + END IF; + END IF; + END IF; +END +GO +exit http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/6e07ed2c/security-admin/db/sqlserver/patches/023-add-unique-constraint-on-table-x_user.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/patches/023-add-unique-constraint-on-table-x_user.sql b/security-admin/db/sqlserver/patches/023-add-unique-constraint-on-table-x_user.sql new file mode 100644 index 0000000..7b707b4 --- /dev/null +++ b/security-admin/db/sqlserver/patches/023-add-unique-constraint-on-table-x_user.sql @@ -0,0 +1,28 @@ +-- 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_user' and column_name = 'user_name') +BEGIN + IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_user' and column_name='user_name' and constraint_name = 'x_user$x_user_UK_user_name') + BEGIN + IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_user' and constraint_name = 'x_user$x_user_UK_user_name' and CONSTRAINT_TYPE='UNIQUE') + BEGIN + ALTER TABLE [dbo].[x_user] ALTER COLUMN [user_name] [varchar](767) NOT NULL; + ALTER TABLE [dbo].[x_user] ADD CONSTRAINT [x_user$x_user_UK_user_name] UNIQUE ([user_name]); + END + END +END +GO +exit
