This is an automated email from the ASF dual-hosted git repository.
abhay 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 1608f8c RANGER-3250: Add relevant indexes to database table to speed
up ingress processing of tagged entities
1608f8c is described below
commit 1608f8cec186880384f5a0c2f6c00adee6c91aa1
Author: Abhay Kulkarni <[email protected]>
AuthorDate: Tue Apr 27 13:23:18 2021 -0700
RANGER-3250: Add relevant indexes to database table to speed up ingress
processing of tagged entities
---
.../optimized/current/ranger_core_db_mysql.sql | 2 ++
.../051-create-index-for-service-resource.sql | 27 ++++++++++++++++++
.../optimized/current/ranger_core_db_oracle.sql | 3 ++
.../051-create-index-for-service-resource.sql | 25 +++++++++++++++++
.../optimized/current/ranger_core_db_postgres.sql | 3 ++
.../051-create-index-for-service-resource.sql | 32 ++++++++++++++++++++++
.../current/ranger_core_db_sqlanywhere.sql | 3 ++
.../051-create-index-for-service-resource.sql | 20 ++++++++++++++
.../optimized/current/ranger_core_db_sqlserver.sql | 6 ++++
.../052-create-index-for-service-resource.sql | 26 ++++++++++++++++++
10 files changed, 147 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 9d0cd9d..a42c2f1 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
@@ -1559,6 +1559,7 @@ CREATE TABLE IF NOT EXISTS `x_tag_change_log` (
primary key (`id`)
) ROW_FORMAT=DYNAMIC;
+CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON
x_service_resource(resource_signature);
CREATE INDEX x_tag_change_log_IDX_service_id ON x_tag_change_log(service_id);
CREATE INDEX x_tag_change_log_IDX_tag_version ON
x_tag_change_log(service_tags_version);
CREATE INDEX x_policy_change_log_IDX_service_id ON
x_policy_change_log(service_id);
@@ -1747,6 +1748,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
('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
('051',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/051-create-index-for-service-resource.sql
b/security-admin/db/mysql/patches/051-create-index-for-service-resource.sql
new file mode 100644
index 0000000..73dc7c3
--- /dev/null
+++ b/security-admin/db/mysql/patches/051-create-index-for-service-resource.sql
@@ -0,0 +1,27 @@
+-- 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_service_resource;
+
+delimiter ;;
+create procedure create_index_for_x_service_resource() begin
+if not exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE
table_schema=DATABASE() AND table_name='x_service_resource' AND
index_name='x_service_resource_IDX_resource_signature') then
+ CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON
x_service_resource(resource_signature);
+ end if;
+end;;
+
+delimiter ;
+call create_index_for_x_service_resource();
+
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 1904c68..149d94d 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
@@ -1235,6 +1235,8 @@ CONSTRAINT x_service_res_FK_added_by_id FOREIGN KEY
(added_by_id) REFERENCES x_p
CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES
x_portal_user (id)
);
+CREATE UNIQUE INDEX x_svc_res_IDX_res_sgn ON
x_service_resource(resource_signature);
+
CREATE TABLE x_tag_resource_map(
id NUMBER(20) NOT NULL,
guid VARCHAR(64) NOT NULL,
@@ -1982,6 +1984,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, '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, '051',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/051-create-index-for-service-resource.sql
b/security-admin/db/oracle/patches/051-create-index-for-service-resource.sql
new file mode 100644
index 0000000..ac1871e
--- /dev/null
+++ b/security-admin/db/oracle/patches/051-create-index-for-service-resource.sql
@@ -0,0 +1,25 @@
+-- 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_svc_res_IDX_res_sgn') AND TABLE_NAME= upper('x_service_resource');
+ IF (v_index_exists = 0) THEN
+ execute IMMEDIATE 'CREATE UNIQUE INDEX x_svc_res_IDX_res_sgn ON
x_service_resource(resource_signature)';
+ commit;
+ END IF;
+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 51ef67b..867bed5 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
@@ -1095,6 +1095,8 @@ CONSTRAINT x_service_res_FK_added_by_id FOREIGN KEY
(added_by_id) REFERENCES x_p
CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES
x_portal_user (id)
);
+CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON
x_service_resource(resource_signature);
+
CREATE SEQUENCE x_tag_resource_map_seq;
CREATE TABLE x_tag_resource_map(
id BIGINT NOT NULL,
@@ -1885,6 +1887,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
('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
('051',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/051-create-index-for-service-resource.sql
b/security-admin/db/postgres/patches/051-create-index-for-service-resource.sql
new file mode 100644
index 0000000..c432445
--- /dev/null
+++
b/security-admin/db/postgres/patches/051-create-index-for-service-resource.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_index_for_x_service_resource()
+RETURNS void AS $$
+DECLARE
+ v_index_exists integer := 0;
+BEGIN
+ select count(*) into v_index_exists from pg_class where relname =
'x_service_resource_idx_resource_signature';
+ IF v_index_exists = 0 THEN
+ CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature
ON x_service_resource(resource_signature);
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select create_index_for_x_service_resource();
+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 97ddb5d..fd30f96 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
@@ -943,6 +943,7 @@ CREATE TABLE dbo.x_service_resource(
CONSTRAINT x_service_res_PK_id PRIMARY KEY CLUSTERED(id),
CONSTRAINT x_service_res_UK_guid UNIQUE NONCLUSTERED (guid)
)
+CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON
x_service_resource(resource_signature);
GO
CREATE TABLE dbo.x_tag_resource_map(
id bigint IDENTITY NOT NULL,
@@ -2267,6 +2268,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
('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
('051',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/051-create-index-for-service-resource.sql
b/security-admin/db/sqlanywhere/patches/051-create-index-for-service-resource.sql
new file mode 100644
index 0000000..d6f5ddb
--- /dev/null
+++
b/security-admin/db/sqlanywhere/patches/051-create-index-for-service-resource.sql
@@ -0,0 +1,20 @@
+-- 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_service_resource_IDX_guid ON
x_service_resource(guid);
+CREATE INDEX IF NOT EXISTS x_service_resource_IDX_resource_signature ON
x_service_resource(resource_signature);
+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 d150150..276a57c 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
@@ -1966,6 +1966,11 @@ CONSTRAINT [x_service_resource$x_service_res_UK_guid]
UNIQUE NONCLUSTERED
(
[guid] ASC
)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY =
OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) ON [PRIMARY],
+CONSTRAINT [x_service_resource$x_service_res_IDX_resource_signature] UNIQUE
NONCLUSTERED
+(
+ [resource_signature] ASC
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY =
OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
@@ -4104,6 +4109,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
('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
('051',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
('052',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/052-create-index-for-service-resource.sql
b/security-admin/db/sqlserver/patches/052-create-index-for-service-resource.sql
new file mode 100644
index 0000000..301aa42
--- /dev/null
+++
b/security-admin/db/sqlserver/patches/052-create-index-for-service-resource.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.
+GO
+IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name =
'x_service_resource_IDX_resource_signature' AND object_id =
OBJECT_ID('x_service_resource'))
+BEGIN
+ CREATE UNIQUE INDEX NONCLUSTERED [x_service_res_IDX_resource_signature]
ON [x_service_resource]
+ (
+ [resource_signature] ASC
+ )
+ WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF,
ONLINE = OFF) ON [PRIMARY]
+END
+Go
+
+EXIT;
