You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ranger.apache.org by ve...@apache.org on 2016/06/21 03:07:55 UTC
[2/2] incubator-ranger git commit: RANGER-1040 : Add unique key
constraint on user_name column of x_user table
RANGER-1040 : Add unique key constraint on user_name column of x_user table
Signed-off-by: Velmurugan Periasamy <ve...@apache.org>
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 <pr...@freestoneinfotech.com>
Authored: Fri Jun 17 23:55:56 2016 +0530
Committer: Velmurugan Periasamy <ve...@apache.org>
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