rameeshm commented on code in PR #1138: URL: https://github.com/apache/ranger/pull/1138#discussion_r3865330238
########## security-admin/db/oracle/patches/078-audit-partition-plan-global-state.sql: ########## @@ -0,0 +1,86 @@ +-- 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 OR REPLACE FUNCTION getXportalUIdByLoginId(input_val IN VARCHAR2) +RETURN NUMBER IS +BEGIN +DECLARE + myid Number := 0; +BEGIN + SELECT x_portal_user.id INTO myid FROM x_portal_user WHERE x_portal_user.login_id = input_val; + RETURN myid; +END; +END; +/ + +DECLARE + t_count number := 0; + v_admin_id number; + v_audit_user_id number; + v_plan_count number := 0; + v_user_count number := 0; + v_role_count number := 0; + v_xuser_count number := 0; + v_varchar_count number := 0; + v_plan_json CLOB := '{"version":1,"topic":"ranger_audits","topicPartitionCount":9,"plugins":{},"buffer":{"partitions":[1,2,3,4,5,6,7,8,9]}}'; + sql_stmt VARCHAR2(4000); +BEGIN + SELECT count(*) INTO t_count FROM user_tables WHERE table_name = 'X_RANGER_GLOBAL_STATE'; + IF (t_count > 0) THEN + SELECT count(*) INTO v_varchar_count FROM user_tab_columns + WHERE table_name = 'X_RANGER_GLOBAL_STATE' AND column_name = 'APP_DATA' AND data_type = 'VARCHAR2'; + IF (v_varchar_count > 0) THEN + EXECUTE IMMEDIATE 'ALTER TABLE x_ranger_global_state MODIFY (app_data CLOB)'; Review Comment: Oracle does not allow directly changing a VARCHAR2 column to a CLOB using the ALTER TABLE ... MODIFY statement. Executing this will result in an ORA-22858: invalid alteration of datatype error, which will cause the DB patch to fail during an upgrade. we should consider IF (v_varchar_count > 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE x_ranger_global_state ADD (app_data_clob CLOB)'; EXECUTE IMMEDIATE 'UPDATE x_ranger_global_state SET app_data_clob = app_data'; EXECUTE IMMEDIATE 'ALTER TABLE x_ranger_global_state DROP COLUMN app_data'; EXECUTE IMMEDIATE 'ALTER TABLE x_ranger_global_state RENAME COLUMN app_data_clob TO app_data'; END IF; --- Please check this out. ########## security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql: ########## @@ -597,7 +597,7 @@ CREATE TABLE IF NOT EXISTS `x_ranger_global_state`( `upd_by_id` bigint(20) NULL DEFAULT NULL, `version` bigint(20) NULL DEFAULT NULL, `state_name` varchar(255) NOT NULL, -`app_data` varchar(255) NULL DEFAULT NULL, +`app_data` TEXT NULL DEFAULT NULL, Review Comment: Please check this to MEDIUMTEXT or LONGTEXT as the value seem to grow which may result in truncation of the value -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
