ranger-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From prad...@apache.org
Subject ranger git commit: RANGER-1765:Add unique key constraint in x_group and x_group_users table
Date Wed, 13 Sep 2017 14:08:11 GMT
Repository: ranger
Updated Branches:
  refs/heads/master 2aa96bf6e -> b83b16879


RANGER-1765:Add unique key constraint in x_group and x_group_users table


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

Branch: refs/heads/master
Commit: b83b168791ad7adb8d46032d0c111ee86c004fb8
Parents: 2aa96bf
Author: Bhavik Patel <bhavikpatel552@gmail.com>
Authored: Mon Sep 4 17:48:50 2017 +0530
Committer: pradeep <pradeep@apache.org>
Committed: Wed Sep 13 19:24:16 2017 +0530

----------------------------------------------------------------------
 ...8-add-unique-constraint-on-table-x_group.sql | 45 +++++++++++++++++++
 ...8-add-unique-constraint-on-table-x_group.sql | 46 ++++++++++++++++++++
 ...8-add-unique-constraint-on-table-x_group.sql | 43 ++++++++++++++++++
 ...8-add-unique-constraint-on-table-x_group.sql | 44 +++++++++++++++++++
 .../current/ranger_core_db_sqlserver.sql        | 22 ++++++++--
 ...8-add-unique-constraint-on-table-x_group.sql | 40 +++++++++++++++++
 6 files changed, 237 insertions(+), 3 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/ranger/blob/b83b1687/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
new file mode 100644
index 0000000..2c5be73
--- /dev/null
+++ b/security-admin/db/mysql/patches/028-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 IGNORE 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 IGNORE 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/b83b1687/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
new file mode 100644
index 0000000..d512465
--- /dev/null
+++ b/security-admin/db/oracle/patches/028-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/b83b1687/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
new file mode 100644
index 0000000..501ec2e
--- /dev/null
+++ b/security-admin/db/postgres/patches/028-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/b83b1687/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
new file mode 100644
index 0000000..c39e68d
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/028-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/b83b1687/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
index 68d3a05..2bb4a79 100644
--- a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
+++ b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
@@ -453,6 +453,10 @@ IF (OBJECT_ID('x_service_version_info_service_id') IS NOT NULL)
 BEGIN
     ALTER TABLE [dbo].[x_service_version_info] DROP CONSTRAINT x_service_version_info_service_id
 END
+IF (OBJECT_ID('x_group_UK_group_name') IS NOT NULL)
+BEGIN
+    ALTER TABLE [dbo].[x_group] DROP CONSTRAINT x_group_UK_group_name
+END
 IF (OBJECT_ID('x_plugin_info_UK') IS NOT NULL)
 BEGIN
     ALTER TABLE [dbo].[x_plugin_info] DROP CONSTRAINT x_plugin_info_UK
@@ -851,7 +855,7 @@ CREATE TABLE [dbo].[x_group](
         [update_time] [datetime2] DEFAULT NULL NULL,
         [added_by_id] [bigint] DEFAULT NULL NULL,
         [upd_by_id] [bigint] DEFAULT NULL NULL,
-        [group_name] [varchar](1024) NOT NULL,
+        [group_name] [varchar](767) NOT NULL,
         [descr] [varchar](4000) NOT NULL,
         [status] [int] DEFAULT 0  NOT NULL,
         [group_type] [int] DEFAULT 0 NOT NULL,
@@ -861,6 +865,10 @@ CREATE TABLE [dbo].[x_group](
 PRIMARY KEY CLUSTERED
 (
         [id] ASC
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS
= ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+CONSTRAINT [x_group$x_group_UK_group_name] UNIQUE NONCLUSTERED
+(
+        [group_name] ASC
 )WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS
= ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 
@@ -915,12 +923,17 @@ CREATE TABLE [dbo].[x_group_users](
         [update_time] [datetime2] DEFAULT NULL NULL,
         [added_by_id] [bigint] DEFAULT NULL NULL,
         [upd_by_id] [bigint] DEFAULT NULL NULL,
-        [group_name] [varchar](1024) NOT NULL,
+        [group_name] [varchar](767) NOT NULL,
         [p_group_id] [bigint] DEFAULT NULL NULL,
         [user_id] [bigint] DEFAULT NULL NULL,
 PRIMARY KEY CLUSTERED
 (
         [id] ASC
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS
= ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+CONSTRAINT [[x_group_users$x_group_users_UK_uid_gname] UNIQUE NONCLUSTERED
+(
+        [user_id] ASC,
+	[group_name] ASC
 )WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS
= ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 
@@ -2975,6 +2988,7 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('025',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('026',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('027',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('028',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES (1,3,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,1);
 INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES (1,1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,1);
@@ -3000,9 +3014,11 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('J10004',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('J10005',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('J10006',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('J10007',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('J10008',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('JAVA_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
 CREATE VIEW [dbo].[vx_trx_log] AS
 select x_trx_log.id AS id,x_trx_log.create_time AS create_time,x_trx_log.update_time AS update_time,x_trx_log.added_by_id
AS added_by_id,x_trx_log.upd_by_id AS upd_by_id,x_trx_log.class_type AS class_type,x_trx_log.object_id
AS object_id,x_trx_log.parent_object_id AS parent_object_id,x_trx_log.parent_object_class_type
AS parent_object_class_type,x_trx_log.attr_name AS attr_name,x_trx_log.parent_object_name
AS parent_object_name,x_trx_log.object_name AS object_name,x_trx_log.prev_val AS prev_val,x_trx_log.new_val
AS new_val,x_trx_log.trx_id AS trx_id,x_trx_log.action AS action,x_trx_log.sess_id AS sess_id,x_trx_log.req_id
AS req_id,x_trx_log.sess_type AS sess_type from x_trx_log
 where id in(select min(x_trx_log.id) from x_trx_log group by x_trx_log.trx_id)
-GO
\ No newline at end of file
+GO

http://git-wip-us.apache.org/repos/asf/ranger/blob/b83b1687/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
new file mode 100644
index 0000000..7559976
--- /dev/null
+++ b/security-admin/db/sqlserver/patches/028-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