Repository: incubator-ranger Updated Branches: refs/heads/ranger-0.5 709f6ffac -> f47cbd13d
RANGER-706 : Optimize audit db upgrade patches to minimize timeout issues Project: http://git-wip-us.apache.org/repos/asf/incubator-ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ranger/commit/f47cbd13 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/f47cbd13 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/f47cbd13 Branch: refs/heads/ranger-0.5 Commit: f47cbd13d9049462cf685748cf81be95c858da60 Parents: 709f6ff Author: Gautam Borad <[email protected]> Authored: Wed Oct 28 13:53:37 2015 +0530 Committer: Gautam Borad <[email protected]> Committed: Thu Oct 29 18:59:41 2015 +0530 ---------------------------------------------------------------------- .../patches/audit/011-auditcolumnssize.sql | 11 ++----- .../patches/audit/015-auditlogaggregation.sql | 20 ++++-------- .../patches/audit/011-auditcolumnssize.sql | 14 ++------ .../patches/audit/015-auditlogaggregation.sql | 34 ++++++-------------- 4 files changed, 20 insertions(+), 59 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f47cbd13/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql b/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql index b38ae8c..a8c7b81 100644 --- a/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql +++ b/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql @@ -18,14 +18,9 @@ drop procedure if exists increase_column_size_of_xa_access_audit_table; delimiter ;; create procedure increase_column_size_of_xa_access_audit_table() begin - /* change request_data data size from 2000 to 4000 */ - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'request_data' and data_type='varchar' and CHARACTER_MAXIMUM_LENGTH=2000) then - ALTER TABLE `xa_access_audit` CHANGE `request_data` `request_data` VARCHAR(4000) NULL DEFAULT NULL ; - end if; - - /* change resource_path data size from 2000 to 4000 */ - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'resource_path' and data_type='varchar' and CHARACTER_MAXIMUM_LENGTH=2000) then - ALTER TABLE `xa_access_audit` CHANGE `resource_path` `resource_path` VARCHAR(4000) NULL DEFAULT NULL ; + /* change request_data and resource_path column size from 2000 to 4000 */ + if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and (column_name = 'request_data' or column_name = 'resource_path') and data_type='varchar' and CHARACTER_MAXIMUM_LENGTH=2000) then + ALTER TABLE `xa_access_audit` CHANGE `request_data` `request_data` VARCHAR(4000) NULL DEFAULT NULL,CHANGE `resource_path` `resource_path` VARCHAR(4000) NULL DEFAULT NULL; end if; end;; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f47cbd13/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql b/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql index c88c5b4..7990f86 100644 --- a/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql +++ b/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql @@ -20,19 +20,11 @@ create procedure add_columns_to_support_audit_log_aggregation() begin if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit') then if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'seq_num') then - ALTER TABLE `xa_access_audit` ADD `seq_num` bigint NULL DEFAULT 0; - end if; - end if; - - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit') then - if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'event_count') then - ALTER TABLE `xa_access_audit` ADD `event_count` bigint NULL DEFAULT 1; - end if; - end if; - - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit') then - if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'event_dur_ms') then - ALTER TABLE `xa_access_audit` ADD `event_dur_ms` bigint NULL DEFAULT 1; + if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'event_count') then + if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'event_dur_ms') then + ALTER TABLE `xa_access_audit` ADD `seq_num` bigint NULL DEFAULT 0,ADD `event_count` bigint NULL DEFAULT 1,ADD `event_dur_ms` bigint NULL DEFAULT 1; + end if; + end if; end if; end if; @@ -41,4 +33,4 @@ end;; delimiter ; call add_columns_to_support_audit_log_aggregation(); -drop procedure if exists add_columns_to_support_audit_log_aggregation; \ No newline at end of file +drop procedure if exists add_columns_to_support_audit_log_aggregation; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f47cbd13/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql b/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql index 35a9320..ad60187 100644 --- a/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql +++ b/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql @@ -19,21 +19,11 @@ DECLARE BEGIN Select count(*) into v_column_exists from user_tab_cols - where column_name = upper('REQUEST_DATA') + where (column_name = upper('REQUEST_DATA') or column_name = upper('RESOURCE_PATH')) and table_name = upper('XA_ACCESS_AUDIT') and DATA_TYPE='VARCHAR2' and DATA_LENGTH=2000; if (v_column_exists = 1) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT modify(REQUEST_DATA VARCHAR(4000) DEFAULT NULL)'; - commit; - end if; - v_column_exists:=0; - Select count(*) into v_column_exists - from user_tab_cols - where column_name = upper('RESOURCE_PATH') - and table_name = upper('XA_ACCESS_AUDIT') and DATA_TYPE='VARCHAR2' and DATA_LENGTH=2000; - - if (v_column_exists = 1) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT modify(RESOURCE_PATH VARCHAR(4000) DEFAULT NULL)'; + execute immediate 'ALTER TABLE XA_ACCESS_AUDIT modify(REQUEST_DATA VARCHAR(4000) DEFAULT NULL,RESOURCE_PATH VARCHAR(4000) DEFAULT NULL)'; commit; end if; end;/ \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f47cbd13/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql b/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql index 756ee61..5c099e8 100644 --- a/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql +++ b/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql @@ -14,43 +14,27 @@ -- limitations under the License. DECLARE - v_column_exists number := 0; + v_column1_exists number := 0; + v_column2_exists number := 0; + v_column3_exists number := 0; BEGIN - Select count(*) into v_column_exists + Select count(*) into v_column1_exists from user_tab_cols where column_name = upper('seq_num') and table_name = upper('XA_ACCESS_AUDIT'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD seq_num NUMBER(20) DEFAULT 0 NULL'; - commit; - end if; -end;/ - -DECLARE - v_column_exists number := 0; -BEGIN - Select count(*) into v_column_exists + Select count(*) into v_column2_exists from user_tab_cols where column_name = upper('event_count') and table_name = upper('XA_ACCESS_AUDIT'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD event_count NUMBER(20) DEFAULT 1 NULL'; - commit; - end if; -end;/ - -DECLARE - v_column_exists number := 0; -BEGIN - Select count(*) into v_column_exists + Select count(*) into v_column3_exists from user_tab_cols where column_name = upper('event_dur_ms') and table_name = upper('XA_ACCESS_AUDIT'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD event_dur_ms NUMBER(20) DEFAULT 1 NULL'; + if (v_column1_exists = 0) AND (v_column2_exists = 0) AND (v_column3_exists = 0) then + execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD (seq_num NUMBER(20) DEFAULT 0 NULL,event_count NUMBER(20) DEFAULT 1 NULL,event_dur_ms NUMBER(20) DEFAULT 1 NULL)'; commit; end if; -end;/ \ No newline at end of file +end;/
