RANGER-2529: Create index on obj_id and obj_class_type column of x_data_hist table
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 a6b033a..377e61f 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
@@ -1028,7 +1028,8 @@
`from_time` datetime NOT NULL,
`to_time` datetime DEFAULT NULL,
`content` MEDIUMTEXT NOT NULL,
-primary key (`id`)
+primary key (`id`),
+KEY `x_data_hist_idx_objid_objclstype` (`obj_id`,`obj_class_type`)
)ROW_FORMAT=DYNAMIC;
CREATE TABLE `x_modules_master` (
@@ -1648,6 +1649,7 @@
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('039',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 ('040',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 ('041',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 ('042',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/042-add-index-on-xdatahist-table.sql b/security-admin/db/mysql/patches/042-add-index-on-xdatahist-table.sql
new file mode 100644
index 0000000..fa66f4c
--- /dev/null
+++ b/security-admin/db/mysql/patches/042-add-index-on-xdatahist-table.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.
+
+drop procedure if exists create_index_on_x_data_hist;
+
+delimiter ;;
+create procedure create_index_on_x_data_hist() begin
+ /* check tables exist or not */
+ if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_data_hist' and column_name in('obj_id', 'obj_class_type')) then
+ /* check index exist on id and obj_class_type column or not */
+ if not exists (select * from information_schema.statistics where table_schema=database() and table_name = 'x_data_hist' and column_name in('obj_id', 'obj_class_type')) then
+ ALTER TABLE x_data_hist ADD INDEX x_data_hist_idx_objid_objclstype(obj_id, obj_class_type);
+ end if;
+ end if;
+end;;
+
+delimiter ;
+call create_index_on_x_data_hist();
+
+drop procedure if exists create_index_on_x_data_hist;
\ No newline at end of file
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 a4439d2..bd5c6a4 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
@@ -1752,6 +1752,7 @@
CREATE INDEX x_ugsync_audit_info_etime ON x_ugsync_audit_info(event_time);
CREATE INDEX x_ugsync_audit_info_sync_src ON x_ugsync_audit_info(sync_source);
CREATE INDEX x_ugsync_audit_info_uname ON x_ugsync_audit_info(user_name);
+CREATE INDEX x_data_hist_idx_objid_clstype ON x_data_hist(obj_id,obj_class_type);
commit;
CREATE OR REPLACE FUNCTION getModulesIdByName(inputval IN VARCHAR2)
@@ -1841,6 +1842,7 @@
INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '039',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, '040',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, '041',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, '042',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);
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('Resource Based Policies'),sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),getXportalUIdByLoginId('admin'),getXportalUIdByLoginId('admin'),1);
diff --git a/security-admin/db/oracle/patches/042-add-index-on-xdatahist-table.sql b/security-admin/db/oracle/patches/042-add-index-on-xdatahist-table.sql
new file mode 100644
index 0000000..9c0b04d
--- /dev/null
+++ b/security-admin/db/oracle/patches/042-add-index-on-xdatahist-table.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.
+
+DECLARE
+ v_count number:=0;
+BEGIN
+ select count(*) into v_count from user_tab_cols where table_name='X_DATA_HIST' and column_name IN('OBJ_ID', 'OBJ_CLASS_TYPE');
+ if (v_count = 2) THEN
+ v_count:=0;
+ select count(*) into v_count from user_ind_columns where table_name='X_DATA_HIST' and column_name IN('OBJ_ID', 'OBJ_CLASS_TYPE') and index_name='X_DATA_HIST_IDX_OBJID_CLSTYPE';
+ if (v_count = 0) THEN
+ execute immediate 'CREATE INDEX x_data_hist_idx_objid_clstype ON x_data_hist(obj_id, obj_class_type)';
+ commit;
+ end if;
+ 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 2d5a891..9a863e2 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
@@ -1703,6 +1703,7 @@
CREATE INDEX x_ugsync_audit_info_etime ON x_ugsync_audit_info(event_time);
CREATE INDEX x_ugsync_audit_info_sync_src ON x_ugsync_audit_info(sync_source);
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 OR REPLACE FUNCTION getXportalUIdByLoginId(input_val varchar(100))
RETURNS bigint LANGUAGE SQL AS $$ SELECT x_portal_user.id FROM x_portal_user
@@ -1765,6 +1766,7 @@
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('039',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 ('040',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 ('041',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 ('042',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/042-add-index-on-xdatahist-table.sql b/security-admin/db/postgres/patches/042-add-index-on-xdatahist-table.sql
new file mode 100755
index 0000000..dde309b
--- /dev/null
+++ b/security-admin/db/postgres/patches/042-add-index-on-xdatahist-table.sql
@@ -0,0 +1,33 @@
+-- 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_on_x_data_hist()
+RETURNS void AS $$
+DECLARE
+ v_attnum1 integer := 0;
+ v_attnum2 integer := 0;
+BEGIN
+ select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_data_hist') and attname in('obj_id');
+ select attnum into v_attnum2 from pg_attribute where attrelid in(select oid from pg_class where relname='x_data_hist') and attname in('obj_class_type');
+ IF v_attnum1 > 0 and v_attnum2 > 0 THEN
+ IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_data_hist') and indkey[0]=v_attnum1 and indkey[1]=v_attnum2) THEN
+ CREATE INDEX x_data_hist_idx_objid_objclstype ON x_data_hist(obj_id,obj_class_type);
+ END IF;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+select create_index_on_x_data_hist();
+select 'delimiter end';
\ No newline at end of file
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 f2e2840..5ec62fb 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
@@ -2009,6 +2009,8 @@
GO
CREATE NONCLUSTERED INDEX x_ugsync_audit_info_uname ON dbo.x_ugsync_audit_info(user_name ASC)
GO
+CREATE NONCLUSTERED INDEX x_data_hist_idx_objid_objclstype ON dbo.x_data_hist(obj_id ASC, obj_class_type ASC)
+GO
CREATE OR REPLACE FUNCTION dbo.getXportalUIdByLoginId (input_val CHAR(60))
RETURNS INTEGER
@@ -2124,6 +2126,8 @@
GO
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('041',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 ('042',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/042-add-index-on-xdatahist-table.sql b/security-admin/db/sqlanywhere/patches/042-add-index-on-xdatahist-table.sql
new file mode 100644
index 0000000..3da5eff
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/042-add-index-on-xdatahist-table.sql
@@ -0,0 +1,30 @@
+-- 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 columnID1 INT = 0;
+DECLARE columnID2 INT = 0;
+DECLARE guTableID INT = 0;
+DECLARE guColumnID INT = 0;
+ IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_data_hist' and cname in('obj_id', 'obj_class_type')) THEN
+ select table_id into tableID from SYS.SYSTAB where table_name = 'x_data_hist';
+ select column_id into columnID1 from SYS.SYSTABCOL where table_id=tableID and column_name = 'obj_id';
+ select column_id into columnID2 from SYS.SYSTABCOL where table_id=tableID and column_name = 'obj_class_type';
+ IF NOT EXISTS(select * from SYS.SYSIDXCOL where table_id=tableID and column_id in (columnID1, columnID2)) THEN
+ CREATE NONCLUSTERED INDEX x_data_hist_idx_objid_objclstype ON dbo.x_data_hist(obj_id ASC, obj_class_type ASC);
+ END IF;
+ END IF;
+END
+GO
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 be1e8a3..ee41452 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
@@ -3740,6 +3740,10 @@
(
[user_name] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
+CREATE NONCLUSTERED INDEX [x_data_hist_idx_objid_objclstype] ON [x_data_hist]
+(
+ [obj_id] ASC,[obj_class_type] ASC
+)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
SET ANSI_NULLS ON
GO
@@ -3856,6 +3860,7 @@
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('039',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 ('040',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 ('041',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 ('042',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/042-add-index-on-xdatahist-table.sql b/security-admin/db/sqlserver/patches/042-add-index-on-xdatahist-table.sql
new file mode 100644
index 0000000..39696d4
--- /dev/null
+++ b/security-admin/db/sqlserver/patches/042-add-index-on-xdatahist-table.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.
+
+IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_data_hist' and column_name in('obj_id', 'obj_class_type'))
+BEGIN
+ IF NOT EXISTS(select * from sys.indexes where name='x_data_hist_idx_objid_objclstype')
+ BEGIN
+ CREATE NONCLUSTERED INDEX [x_data_hist_idx_objid_objclstype] ON [x_data_hist]([obj_id] ASC, [obj_class_type] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
+ END
+END
+GO
+exit
\ No newline at end of file
