This is an automated email from the ASF dual-hosted git repository.
mehul pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git
The following commit(s) were added to refs/heads/master by this push:
new 34a948c RANGER-3067: Schema changes to improve performance of chained
plugin feature
34a948c is described below
commit 34a948c34ab135e75edba1a48f56fdeca2ad3c74
Author: Mahesh Bandal <[email protected]>
AuthorDate: Mon Nov 9 11:44:17 2020 +0530
RANGER-3067: Schema changes to improve performance of chained plugin feature
Signed-off-by: Mehul Parikh <[email protected]>
---
.../optimized/current/ranger_core_db_mysql.sql | 2 +
.../050-create-index-for-resource-signature.sql | 40 ++++++++++++++++++
.../optimized/current/ranger_core_db_oracle.sql | 2 +
.../050-create-index-for-resource-signature.sql | 37 +++++++++++++++++
.../optimized/current/ranger_core_db_postgres.sql | 2 +
.../050-create-index-for-resource-signature.sql | 45 ++++++++++++++++++++
.../current/ranger_core_db_sqlanywhere.sql | 3 ++
.../050-create-index-for-resource-signature.sql | 26 ++++++++++++
.../optimized/current/ranger_core_db_sqlserver.sql | 7 ++++
.../050-create-index-for-resource-signature.sql | 48 ++++++++++++++++++++++
10 files changed, 212 insertions(+)
diff --git a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
index 71e0019..b3a219b 100644
--- a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
+++ b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
@@ -1636,6 +1636,7 @@ CREATE TABLE `x_rms_service_resource` (
CONSTRAINT `x_rms_service_res_FK_service_id` FOREIGN KEY (`service_id`)
REFERENCES `x_service` (`id`)
);
CREATE INDEX x_rms_service_resource_IDX_service_id ON
x_rms_service_resource(service_id);
+CREATE INDEX x_rms_service_resource_IDX_resource_signature ON
x_rms_service_resource(resource_signature);
CREATE TABLE `x_rms_notification` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
@@ -1745,6 +1746,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
('047',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('048',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('049',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('050',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_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)
diff --git
a/security-admin/db/mysql/patches/050-create-index-for-resource-signature.sql
b/security-admin/db/mysql/patches/050-create-index-for-resource-signature.sql
new file mode 100644
index 0000000..8c62a5c
--- /dev/null
+++
b/security-admin/db/mysql/patches/050-create-index-for-resource-signature.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.
+
+drop procedure if exists create_index_for_x_rms_service_resource;
+
+delimiter ;;
+create procedure create_index_for_x_rms_service_resource() begin
+if not exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE
table_schema=DATABASE() AND table_name='x_rms_service_resource' AND
index_name='x_rms_service_resource_IDX_resource_signature') then
+ CREATE INDEX x_rms_service_resource_IDX_resource_signature ON
x_rms_service_resource(resource_signature);
+ end if;
+end;;
+
+delimiter ;
+call create_index_for_x_rms_service_resource();
+
+drop procedure if exists delete_rms_tables;
+delimiter ;;
+create procedure delete_rms_tables() begin
+ SET FOREIGN_KEY_CHECKS = 0;
+ truncate table x_rms_mapping_provider;
+ truncate table x_rms_notification;
+ truncate table x_rms_resource_mapping;
+ truncate table x_rms_service_resource;
+ SET FOREIGN_KEY_CHECKS = 1;
+end;;
+
+delimiter ;
+call delete_rms_tables();
diff --git
a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
index 0137a9d..b72f233 100644
--- a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
+++ b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
@@ -1863,6 +1863,7 @@ CONSTRAINT x_rms_svc_res_FK_service_id FOREIGN KEY
(service_id) REFERENCES x_ser
);
CREATE INDEX x_rms_svc_res_IDX_service_id ON
x_rms_service_resource(service_id);
+CREATE INDEX x_rms_svc_res_IDX_resource_signature ON
x_rms_service_resource(resource_signature);
CREATE TABLE x_rms_notification (
id NUMBER(20) NOT NULL,
@@ -1980,6 +1981,7 @@ INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,act
INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, '047',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, '048',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, '049',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
+INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, '050',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h
(id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES
(X_DB_VERSION_H_SEQ.nextval, 'DB_PATCHES',sys_extract_utc(systimestamp),'Ranger
1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_user_module_perm
(id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
(X_USER_MODULE_PERM_SEQ.nextval,getXportalUIdByLoginId('admin'),getModulesIdByName('Reports'),sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),getXportalUIdByLoginId('admin'),getXportalUIdByLoginId('admin'),1);
diff --git
a/security-admin/db/oracle/patches/050-create-index-for-resource-signature.sql
b/security-admin/db/oracle/patches/050-create-index-for-resource-signature.sql
new file mode 100644
index 0000000..6974f7c
--- /dev/null
+++
b/security-admin/db/oracle/patches/050-create-index-for-resource-signature.sql
@@ -0,0 +1,37 @@
+-- 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.
+-- sync_source_info CLOB NOT NULL,
+
+DECLARE
+ v_index_exists number:=0;
+BEGIN
+ SELECT COUNT(*) INTO v_index_exists FROM USER_INDEXES WHERE INDEX_NAME
= upper('x_rms_svc_res_IDX_resource_signature') AND TABLE_NAME=
upper('x_rms_service_resource');
+ IF (v_index_exists = 0) THEN
+ execute IMMEDIATE 'CREATE INDEX
x_rms_svc_res_IDX_resource_signature ON
x_rms_service_resource(resource_signature)';
+ commit;
+ END IF;
+END;/
+
+BEGIN
+ execute IMMEDIATE 'ALTER TABLE x_rms_resource_mapping DISABLE
CONSTRAINT x_rms_res_map_FK_hl_res_id';
+ execute IMMEDIATE 'ALTER TABLE x_rms_resource_mapping DISABLE
CONSTRAINT x_rms_res_map_FK_ll_res_id';
+ execute IMMEDIATE 'truncate table x_rms_mapping_provider';
+ execute IMMEDIATE 'truncate table x_rms_notification';
+ execute IMMEDIATE 'truncate table x_rms_resource_mapping';
+ execute IMMEDIATE 'truncate table x_rms_service_resource';
+ execute IMMEDIATE 'ALTER TABLE x_rms_resource_mapping ENABLE CONSTRAINT
x_rms_res_map_FK_hl_res_id';
+ execute IMMEDIATE 'ALTER TABLE x_rms_resource_mapping ENABLE CONSTRAINT
x_rms_res_map_FK_ll_res_id';
+ commit;
+END;/
diff --git
a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
index d00af37..b2f1409 100644
--- a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
+++ b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
@@ -1807,6 +1807,7 @@ CREATE INDEX x_ugsync_audit_info_uname ON
x_ugsync_audit_info(user_name);
CREATE INDEX x_data_hist_idx_objid_objclstype ON
x_data_hist(obj_id,obj_class_type);
CREATE INDEX x_rms_service_resource_IDX_service_id ON
x_rms_service_resource(service_id);
+CREATE INDEX x_rms_service_resource_IDX_resource_signature ON
x_rms_service_resource(resource_signature);
CREATE INDEX x_rms_notification_IDX_notification_id ON
x_rms_notification(notification_id);
CREATE INDEX x_rms_notification_IDX_hms_name_notification_id ON
x_rms_notification(hms_name, notification_id);
CREATE INDEX x_rms_notification_IDX_hl_service_id ON
x_rms_notification(hl_service_id);
@@ -1883,6 +1884,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
('047',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
('048',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
('049',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
('050',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
diff --git
a/security-admin/db/postgres/patches/050-create-index-for-resource-signature.sql
b/security-admin/db/postgres/patches/050-create-index-for-resource-signature.sql
new file mode 100644
index 0000000..f4b60bd
--- /dev/null
+++
b/security-admin/db/postgres/patches/050-create-index-for-resource-signature.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.
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION create_index_for_x_rms_service_resource()
+RETURNS void AS $$
+DECLARE
+ v_index_exists integer := 0;
+BEGIN
+ select count(*) into v_index_exists from pg_class where relname =
'x_rms_service_resource_idx_resource_signature';
+ IF v_index_exists = 0 THEN
+ CREATE INDEX x_rms_service_resource_IDX_resource_signature ON
x_rms_service_resource(resource_signature);
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+CREATE OR REPLACE FUNCTION delete_rms_tables()
+RETURNS void AS $$
+BEGIN
+ truncate table x_rms_mapping_provider;
+ truncate table x_rms_notification;
+ truncate table x_rms_resource_mapping;
+ truncate table x_rms_service_resource CASCADE;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select create_index_for_x_rms_service_resource();
+select 'delimiter end';
+
+select delete_rms_tables();
+select 'delimiter end';
diff --git
a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
index 7791a9a..030ea48 100644
---
a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
+++
b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
@@ -2106,6 +2106,7 @@ CONSTRAINT x_rms_notification_FK_hl_service_id FOREIGN
KEY(hl_service_id) REFERE
CONSTRAINT x_rms_notification_FK_ll_service_id FOREIGN KEY(ll_service_id)
REFERENCES x_service(id)
);
CREATE INDEX x_rms_service_resource_IDX_service_id ON
x_rms_service_resource(service_id);
+CREATE INDEX x_rms_service_resource_IDX_resource_signature ON
x_rms_service_resource(resource_signature);
GO
CREATE TABLE dbo.x_rms_notification (
@@ -2264,6 +2265,8 @@ INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active
GO
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('049',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('050',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
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');
GO
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
(dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
diff --git
a/security-admin/db/sqlanywhere/patches/050-create-index-for-resource-signature.sql
b/security-admin/db/sqlanywhere/patches/050-create-index-for-resource-signature.sql
new file mode 100644
index 0000000..27633a6
--- /dev/null
+++
b/security-admin/db/sqlanywhere/patches/050-create-index-for-resource-signature.sql
@@ -0,0 +1,26 @@
+-- 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 INDEX IF NOT EXISTS x_rms_service_resource_IDX_resource_signature ON
x_rms_service_resource(resource_signature);
+GO
+
+BEGIN
+ TRUNCATE TABLE dbo.x_rms_mapping_provider;
+ TRUNCATE TABLE dbo.x_rms_notification;
+ TRUNCATE TABLE dbo.x_rms_resource_mapping;
+ TRUNCATE TABLE dbo.x_rms_service_resource;
+END
+GO
+EXIT
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 addb566..7afabba 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
@@ -3932,6 +3932,12 @@ CREATE NONCLUSTERED INDEX
[x_rms_service_resource_IDX_service_id] ON [x_rms_serv
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE
= OFF) ON [PRIMARY]
GO
+CREATE NONCLUSTERED INDEX [x_rms_service_resource_IDX_resource_signature] ON
[x_rms_service_resource]
+(
+ [resource_signature] ASC
+)
+WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE
= OFF) ON [PRIMARY]
+GO
SET ANSI_NULLS ON
@@ -4096,6 +4102,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
('047',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
('048',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
('049',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
('050',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
(dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Resource
Based
Policies'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
diff --git
a/security-admin/db/sqlserver/patches/050-create-index-for-resource-signature.sql
b/security-admin/db/sqlserver/patches/050-create-index-for-resource-signature.sql
new file mode 100644
index 0000000..51b64ee
--- /dev/null
+++
b/security-admin/db/sqlserver/patches/050-create-index-for-resource-signature.sql
@@ -0,0 +1,48 @@
+-- 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.
+GO
+IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name =
'x_rms_service_resource_IDX_resource_signature' AND object_id =
OBJECT_ID('x_rms_service_resource'))
+BEGIN
+ CREATE NONCLUSTERED INDEX
[x_rms_service_resource_IDX_resource_signature] ON [x_rms_service_resource]
+ (
+ [resource_signature] ASC
+ )
+ WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF,
ONLINE = OFF) ON [PRIMARY]
+END
+Go
+
+IF (OBJECT_ID('x_rms_res_map_FK_hl_res_id') IS NOT NULL)
+BEGIN
+ ALTER TABLE [dbo].[x_rms_resource_mapping] DROP CONSTRAINT
x_rms_res_map_FK_hl_res_id
+END
+GO
+IF (OBJECT_ID('x_rms_res_map_FK_ll_res_id') IS NOT NULL)
+BEGIN
+ ALTER TABLE [dbo].[x_rms_resource_mapping] DROP CONSTRAINT
x_rms_res_map_FK_ll_res_id
+END
+GO
+
+BEGIN
+ TRUNCATE TABLE [dbo].[x_rms_mapping_provider];
+ TRUNCATE TABLE [dbo].[x_rms_notification];
+ TRUNCATE TABLE [dbo].[x_rms_resource_mapping];
+ TRUNCATE TABLE [dbo].[x_rms_service_resource];
+ ALTER TABLE [dbo].[x_rms_resource_mapping] WITH CHECK ADD CONSTRAINT
[x_rms_res_map_FK_hl_res_id] FOREIGN KEY([hl_resource_id])
+ REFERENCES [dbo].[x_rms_service_resource] ([id]);
+ ALTER TABLE [dbo].[x_rms_resource_mapping] WITH CHECK ADD CONSTRAINT
[x_rms_res_map_FK_ll_res_id] FOREIGN KEY([ll_resource_id])
+ REFERENCES [dbo].[x_rms_service_resource] ([id]);
+END
+GO
+EXIT;
