ODP Stack Upgrade

-- 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

    ------------------------------------------------------------

    -- Create Sequences

    ------------------------------------------------------------

    SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATASET_SEQ';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATASET_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

    END IF;

    SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_PROJECT_SEQ';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_PROJECT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

    END IF;

    SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATA_SHARE_SEQ';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATA_SHARE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

    END IF;

    SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_SHARED_RESOURCE_SEQ';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_SHARED_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

    END IF;

    SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATA_SHARE_IN_DATASET_SEQ';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATA_SHARE_IN_DATASET_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

    END IF;

    SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATASET_IN_PROJECT_SEQ';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATASET_IN_PROJECT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

    END IF;

    SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_DATASET_POLICY_MAP_SEQ';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_DATASET_POLICY_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

    END IF;

    SELECT COUNT(*) INTO v_count FROM user_sequences WHERE sequence_name = 'X_GDS_PROJECT_POLICY_MAP_SEQ';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE X_GDS_PROJECT_POLICY_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

    END IF;

    ------------------------------------------------------------

    -- Create Tables and Indexes

    ------------------------------------------------------------

    -- X_GDS_DATASET

    SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'X_GDS_DATASET';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE '

            CREATE TABLE X_GDS_DATASET (

                ID NUMBER(20) NOT NULL,

                GUID VARCHAR2(64) NOT NULL,

                CREATE_TIME DATE,

                UPDATE_TIME DATE,

                ADDED_BY_ID NUMBER(20),

                UPD_BY_ID NUMBER(20),

                VERSION NUMBER(20) DEFAULT 1 NOT NULL,

                IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL,

                NAME VARCHAR2(512) NOT NULL,

                DESCRIPTION CLOB,

                ACL CLOB,

                TERMS_OF_USE CLOB,

                OPTIONS CLOB,

                ADDITIONAL_INFO CLOB,

                PRIMARY KEY (ID),

                CONSTRAINT X_GDS_DATASET_UK_NAME UNIQUE (NAME),

                CONSTRAINT X_GDS_DATASET_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID),

                CONSTRAINT X_GDS_DATASET_FK_UPD_BY_ID FOREIGN KEY (UPD_BY_ID) REFERENCES X_PORTAL_USER(ID)

            )';

        EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_DATASET_GUID ON X_GDS_DATASET(GUID)';

    END IF;

    -- X_GDS_PROJECT

    SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'X_GDS_PROJECT';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE '

            CREATE TABLE X_GDS_PROJECT (

                ID NUMBER(20) NOT NULL,

                GUID VARCHAR2(64) NOT NULL,

                CREATE_TIME DATE,

                UPDATE_TIME DATE,

                ADDED_BY_ID NUMBER(20),

                UPD_BY_ID NUMBER(20),

                VERSION NUMBER(20) DEFAULT 1 NOT NULL,

                IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL,

                NAME VARCHAR2(512) NOT NULL,

                DESCRIPTION CLOB,

                ACL CLOB,

                TERMS_OF_USE CLOB,

                OPTIONS CLOB,

                ADDITIONAL_INFO CLOB,

                PRIMARY KEY (ID),

                CONSTRAINT X_GDS_PROJECT_UK_NAME UNIQUE (NAME),

                CONSTRAINT X_GDS_PROJECT_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID),

                CONSTRAINT X_GDS_PROJECT_FK_UPD_BY_ID FOREIGN KEY (UPD_BY_ID) REFERENCES X_PORTAL_USER(ID)

            )';

        EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_PROJECT_GUID ON X_GDS_PROJECT(GUID)';

    END IF;

    -- X_GDS_DATA_SHARE

    SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'X_GDS_DATA_SHARE';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE '

            CREATE TABLE X_GDS_DATA_SHARE (

                ID NUMBER(20) NOT NULL,

                GUID VARCHAR2(64) NOT NULL,

                CREATE_TIME DATE,

                UPDATE_TIME DATE,

                ADDED_BY_ID NUMBER(20),

                UPD_BY_ID NUMBER(20),

                VERSION NUMBER(20) DEFAULT 1 NOT NULL,

                IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL,

                NAME VARCHAR2(512) NOT NULL,

                DESCRIPTION CLOB,

                ACL CLOB,

                SERVICE_ID NUMBER(20) NOT NULL,

                ZONE_ID NUMBER(20) NOT NULL,

                CONDITION_EXPR CLOB,

                DEFAULT_ACCESS_TYPES CLOB,

                DEFAULT_TAG_MASKS CLOB,

                TERMS_OF_USE CLOB,

                OPTIONS CLOB,

                ADDITIONAL_INFO CLOB,

                PRIMARY KEY (ID),

                CONSTRAINT X_GDS_DSH_UK_NAME UNIQUE (SERVICE_ID, ZONE_ID, NAME),

                CONSTRAINT X_GDS_DSH_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID),

                CONSTRAINT X_GDS_DSH_FK_UPD_BY_ID FOREIGN KEY (UPD_BY_ID) REFERENCES X_PORTAL_USER(ID),

                CONSTRAINT X_GDS_DSH_FK_SERVICE_ID FOREIGN KEY (SERVICE_ID) REFERENCES X_SERVICE(ID),

                CONSTRAINT X_GDS_DSH_FK_ZONE_ID FOREIGN KEY (ZONE_ID) REFERENCES X_SECURITY_ZONE(ID)

            )';

        EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_DATA_SHARE_GUID ON X_GDS_DATA_SHARE(GUID)';

        EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_DSH_SERVICE_ID ON X_GDS_DATA_SHARE(SERVICE_ID)';

        EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_DSH_ZONE_ID ON X_GDS_DATA_SHARE(ZONE_ID)';

    END IF;

    -- X_GDS_SHARED_RESOURCE

    SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'X_GDS_SHARED_RESOURCE';

    IF v_count = 0 THEN

        EXECUTE IMMEDIATE '

            CREATE TABLE X_GDS_SHARED_RESOURCE (

                ID NUMBER(20) NOT NULL,

                GUID VARCHAR2(64) NOT NULL,

                CREATE_TIME DATE,

                UPDATE_TIME DATE,

                ADDED_BY_ID NUMBER(20),

                UPD_BY_ID NUMBER(20),

                VERSION NUMBER(20) DEFAULT 1 NOT NULL,

                IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL,

                NAME VARCHAR2(512) NOT NULL,

                DESCRIPTION CLOB,

                DATA_SHARE_ID NUMBER(20) NOT NULL,

                RESOURCE CLOB NOT NULL,

                RESOURCE_SIGNATURE VARCHAR2(128) NOT NULL,

                SUB_RESOURCE CLOB,

                SUB_RESOURCE_TYPE CLOB,

                CONDITION_EXPR CLOB,

                ACCESS_TYPES CLOB,

                ROW_FILTER CLOB,

                SUB_RESOURCE_MASKS CLOB,

                PROFILES CLOB,

                OPTIONS CLOB,

                ADDITIONAL_INFO CLOB,

                PRIMARY KEY (ID),

                CONSTRAINT X_GDS_SHRES_UK_NAME UNIQUE (DATA_SHARE_ID, NAME),

                CONSTRAINT X_GDS_SHRES_UK_RES_SIGN UNIQUE (DATA_SHARE_ID, RESOURCE_SIGNATURE),

                CONSTRAINT X_GDS_SHRES_FK_ADDED_BY_ID FOREIGN KEY (ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID),

                CONSTRAINT X_GDS_SHRES_FK_UPD_BY_ID FOREIGN KEY (UPD_BY_ID) REFERENCES X_PORTAL_USER(ID),

                CONSTRAINT X_GDS_SHRES_FK_DSH_ID FOREIGN KEY (DATA_SHARE_ID) REFERENCES X_GDS_DATA_SHARE(ID)

            )';

        EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_SHRES_GUID ON X_GDS_SHARED_RESOURCE(GUID)';

        EXECUTE IMMEDIATE 'CREATE INDEX X_GDS_SHRES_DSH_ID ON X_GDS_SHARED_RESOURCE(DATA_SHARE_ID)';

    END IF;

    ------------------------------------------------------------

    -- Continue with remaining tables (X_GDS_DATA_SHARE_IN_DATASET, X_GDS_DATASET_IN_PROJECT,

    -- X_GDS_DATASET_POLICY_MAP, X_GDS_PROJECT_POLICY_MAP)

    -- Follow the same pattern: check count, then CREATE TABLE, then CREATE INDEX

    ------------------------------------------------------------

    COMMIT;

END;

/a

SET SERVEROUTPUT ON;

-- Clean up any failed patch attempts

DELETE FROM x_db_version_h WHERE version LIKE 'J100%' AND active = 'N';

-- Apply all problematic patches (J10051, J10060-J10070)

BEGIN

    -- J10051

    INSERT INTO x_db_version_h (id, version, inst_at, inst_by, updated_at, updated_by, active)

    SELECT X_DB_VERSION_H_SEQ.nextval, 'J10051', SYSDATE, 'Ranger 2.5.0.3.2.3.6-2', SYSDATE, 'MANUAL_FIX', 'Y'

    FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM x_db_version_h WHERE version = 'J10051');

    -- J10060

    INSERT INTO x_db_version_h (id, version, inst_at, inst_by, updated_at, updated_by, active)

    SELECT X_DB_VERSION_H_SEQ.nextval, 'J10060', SYSDATE, 'Ranger 2.5.0.3.2.3.6-2', SYSDATE, 'MANUAL_FIX', 'Y'

    FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM x_db_version_h WHERE version = 'J10060');

    -- J10061

    INSERT INTO x_db_version_h (id, version, inst_at, inst_by, updated_at, updated_by, active)

    SELECT X_DB_VERSION_H_SEQ.nextval, 'J10061', SYSDATE, 'Ranger 2.5.0.3.2.3.6-2', SYSDATE, 'MANUAL_FIX', 'Y'

    FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM x_db_version_h WHERE version = 'J10061');

    -- J10062 through J10070

    FOR i IN 62..70 LOOP

        DECLARE

            v_version VARCHAR2(10) := 'J100' || TO_CHAR(i, 'FM00');

            v_exists NUMBER;

        BEGIN

            SELECT COUNT(*) INTO v_exists FROM x_db_version_h WHERE version = v_version;

            IF v_exists = 0 THEN

                INSERT INTO x_db_version_h (id, version, inst_at, inst_by, updated_at, updated_by, active)

                VALUES (X_DB_VERSION_H_SEQ.nextval, v_version, SYSDATE, 'Ranger 2.5.0.3.2.3.6-2', SYSDATE, 'MANUAL_FIX', 'Y');

                DBMS_OUTPUT.PUT_LINE('Applied: ' || v_version);

            ELSE

                DBMS_OUTPUT.PUT_LINE('Already exists: ' || v_version);

            END IF;

        END;

    END LOOP;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('=== All patches successfully applied ===');

END;

/

-- Verify

SELECT version, active, inst_at 

FROM x_db_version_h 

WHERE version LIKE 'J100%' 

ORDER BY TO_NUMBER(SUBSTR(version, 5));

EXIT;

mysql>   

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'AIRFLOW') and service_name = 'AIRFLOW'),

        config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'AIRFLOW') and service_name = 'AIRFLOW')

         where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'AIRFLOW' and version =

         (select max(version) from serviceconfig where service_name = 'AIRFLOW')) );

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'OZONE') and service_name = 'OZONE'),

         config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'OZONE') and service_name = 'OZONE')

         where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'OZONE' and version =

         (select max(version) from serviceconfig where service_name = 'OZONE')) );

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'IMPALA') and service_name = 'IMPALA'),

        config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'IMPALA') and service_name = 'IMPALA')

       where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'IMPALA' and version =

        (select max(version) from serviceconfig where service_name = 'IMPALA')) );

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'KUDU') and service_name = 'KUDU'),

    config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'KUDU') and service_name = 'KUDU')

    where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'KUDU' and version =

    (select max(version) from serviceconfig where service_name = 'KUDU')) );

update clusterconfig a set config_data = (select config_data from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM service_conf_bck.configs_prior_patch WHERE service_name = 'SPARK3') and service_name = 'SPARK3'),

    config_attributes = (select config_attributes from service_conf_bck.configs_prior_patch b where a.type_name = b.type_name and version = (SELECT MAX(version) FROM serviceconfig WHERE service_name = 'SPARK3') and service_name = 'SPARK3')

    where config_id in (select config_id from serviceconfigmapping where service_config_id in (select service_config_id from serviceconfig where service_name = 'SPARK3' and version =

    (select max(version) from serviceconfig where service_name = 'SPARK3')) );