/*
 * Copyright (c) 2017, WSO2 Inc. (http://www.wso2.org) All Rights Reserved.
 *
 * WSO2 Inc. 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.
 */

-- WSO2 Message Broker v 3.1.0 to v 3.2.0 migration script for oracle --

/
UPDATE MB_EXCHANGE SET EXCHANGE_DATA = REPLACE(EXCHANGE_DATA, 'exchangeName=', 'messageRouterName=')
/
ALTER TABLE MB_QUEUE_MAPPING MODIFY QUEUE_NAME NOT NULL
/
DELETE FROM MB_DURABLE_SUBSCRIPTION
/
DROP TABLE MB_EXPIRATION_DATA
/
CREATE TABLE MB_EXPIRATION_DATA (
    MESSAGE_ID NUMBER(19) UNIQUE,
    EXPIRATION_TIME NUMBER(19),
    DLC_QUEUE_ID INT NOT NULL,
    MESSAGE_DESTINATION VARCHAR2(512) NOT NULL,
    CONSTRAINT FK_EXPIRATION_DATA FOREIGN KEY (MESSAGE_ID) REFERENCES MB_METADATA (MESSAGE_ID)
    ON DELETE CASCADE
)
/
CREATE TABLE MB_DTX_XID (
  INTERNAL_XID NUMBER(19) UNIQUE NOT NULL,
  NODE_ID VARCHAR2(512) NOT NULL,
  FORMAT_CODE NUMBER(19) NOT NULL,
  GLOBAL_ID RAW(260), -- AMQP-10 vbin8 type
  BRANCH_ID RAW(260), -- AMQP-10 vbin8 type
  CONSTRAINT PK_MB_DTX_XID PRIMARY KEY (INTERNAL_XID, NODE_ID)
)
/
CREATE TABLE MB_DTX_ENQUEUE_RECORD (
  INTERNAL_XID NUMBER(19) NOT NULL,
  MESSAGE_ID NUMBER(19) NOT NULL,
  MESSAGE_METADATA RAW(2000) NOT NULL,
  CONSTRAINT PK_MB_DTX_ENQUEUE_RECORD PRIMARY KEY (MESSAGE_ID),
  CONSTRAINT FK_MB_DTX_ENQUEUE_RECORD FOREIGN KEY (INTERNAL_XID) REFERENCES MB_DTX_XID (INTERNAL_XID)
  ON DELETE CASCADE
)
/
CREATE TABLE MB_DTX_DEQUEUE_RECORD (
  INTERNAL_XID NUMBER(19) NOT NULL,
  MESSAGE_ID NUMBER(19) NOT NULL,
  QUEUE_NAME VARCHAR2(512) NOT NULL,
  MESSAGE_METADATA RAW(2000) NOT NULL,
  CONSTRAINT PK_MB_DTX_DEQUEUE_RECORD PRIMARY KEY (MESSAGE_ID),
  CONSTRAINT FK_MB_DTX_DEQUEUE_RECORD FOREIGN KEY (INTERNAL_XID) REFERENCES MB_DTX_XID (INTERNAL_XID)
  ON DELETE CASCADE
)
/
CREATE TABLE MB_DTX_ENQUEUE_CONTENT (
  MESSAGE_ID NUMBER(19) NOT NULL,
  INTERNAL_XID NUMBER(19) NOT NULL,
  CONTENT_OFFSET INT NOT NULL,
  MESSAGE_CONTENT BLOB NOT NULL,
  CONSTRAINT PK_MB_DTX_ENQUEUE_CONTENT PRIMARY KEY (MESSAGE_ID,CONTENT_OFFSET),
  CONSTRAINT FK_MB_DTX_ENQUEUE_CONTENT FOREIGN KEY (MESSAGE_ID) REFERENCES MB_DTX_ENQUEUE_RECORD (MESSAGE_ID)
  ON DELETE CASCADE
)
/
CREATE TABLE MB_DTX_DEQUEUE_CONTENT (
  INTERNAL_XID NUMBER(19) NOT NULL,
  MESSAGE_ID NUMBER(19) NOT NULL,
  CONTENT_OFFSET INT NOT NULL,
  MESSAGE_CONTENT BLOB NOT NULL,
  CONSTRAINT PK_MB_DTX_DEQUEUE_CONTENT PRIMARY KEY (MESSAGE_ID,CONTENT_OFFSET),
  CONSTRAINT FK_MB_DTX_DEQUEUE_CONTENT FOREIGN KEY (MESSAGE_ID) REFERENCES MB_DTX_DEQUEUE_RECORD (MESSAGE_ID)
  ON DELETE CASCADE
)
/
CREATE TABLE MB_CLUSTER_EVENT (
    EVENT_ID NUMBER(19) NOT NULL,
    ORIGINATED_NODE_ID VARCHAR2(512) NOT NULL,
    DESTINED_NODE_ID VARCHAR2(512) NOT NULL,
    EVENT_ARTIFACT VARCHAR2(25) NOT NULL,
    EVENT_TYPE VARCHAR2(25) NOT NULL,
    EVENT_DETAILS VARCHAR2(1024) NOT NULL,
    EVENT_DESCRIPTION VARCHAR2(1024),
    CONSTRAINT PK_MB_CLUSTER_EVENT PRIMARY KEY (EVENT_ID)
)
/
CREATE SEQUENCE MB_CLUSTER_EVENT_ID_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER MB_CLUSTER_EVENT_ID_TRIGGER
    BEFORE INSERT ON MB_CLUSTER_EVENT
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN
        SELECT MB_CLUSTER_EVENT_ID_SEQUENCE.nextval INTO :NEW.EVENT_ID FROM dual;
    END;
/
CREATE TABLE MB_COORDINATOR_HEARTBEAT (
    ANCHOR INT NOT NULL,
    NODE_ID VARCHAR2(512) NOT NULL,
    LAST_HEARTBEAT NUMBER(19) NOT NULL,
    THRIFT_HOST VARCHAR2(512) NOT NULL,
    THRIFT_PORT INT NOT NULL,
    CONSTRAINT PK_MB_COORDINATOR_HEARTBEAT PRIMARY KEY (ANCHOR)
)
/
CREATE TABLE MB_NODE_HEARTBEAT (
    NODE_ID VARCHAR2(512) NOT NULL,
    LAST_HEARTBEAT NUMBER(19) NOT NULL,
    IS_NEW_NODE NUMBER(3) NOT NULL,
    CLUSTER_AGENT_HOST VARCHAR2(512) NOT NULL,
    CLUSTER_AGENT_PORT INT NOT NULL,
    CONSTRAINT PK_MB_NODE_HEARTBEAT PRIMARY KEY (NODE_ID)
)
/
CREATE TABLE MB_MEMBERSHIP (
    EVENT_ID NUMBER(19) NOT NULL,
    NODE_ID VARCHAR2(512) NOT NULL,
    CHANGE_TYPE NUMBER(3) NOT NULL,
    CHANGED_MEMBER_ID VARCHAR(512) NOT NULL,
    CONSTRAINT PK_MB_MEMBERSHIP PRIMARY KEY (EVENT_ID)
)
/
CREATE SEQUENCE MB_MEMBERSHIP_SEQUENCE START WITH 1 INCREMENT BY 1  NOCACHE
/
CREATE OR REPLACE TRIGGER MB_MEMBERSHIP_TRIGGER
    BEFORE INSERT ON MB_MEMBERSHIP
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN
        SELECT MB_MEMBERSHIP_SEQUENCE.nextval INTO :NEW.EVENT_ID FROM dual;
    END;
/
