From 5a6a6de6f1a26a1897e4917a0df613e25a24eb70 Mon Sep 17 00:00:00 2001 From: "Benjamin, Max (mb388a)" Date: Mon, 30 Jul 2018 15:56:09 -0400 Subject: Containerization feature of SO Change-Id: I95381232eeefcd247a66a5cec370a8ce1c288e18 Issue-ID: SO-670 Signed-off-by: Benjamin, Max (mb388a) --- .../resources/db/migration/V1__Base_version.sql | 155 +++++++++++++++++++++ .../V2.1__Add_Identifiers_Active_Requests.sql | 9 ++ .../db/migration/V2.2__Add_Operation_Status.sql | 30 ++++ .../V2.3__Remove_Invalid_requestdb_data.sql | 5 + .../V2.4__Activate_Requests_Nullable_Coulmns.sql | 6 + .../db/migration/V2.5__Archived_Infra_Requests.sql | 51 +++++++ .../main/resources/db/migration/V2.6__shedlock.sql | 9 ++ .../V2.7__Update_Status_Message_ColumnTypes.sql | 8 ++ 8 files changed, 273 insertions(+) create mode 100644 adapters/mso-requests-db-adapter/src/main/resources/db/migration/V1__Base_version.sql create mode 100644 adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.1__Add_Identifiers_Active_Requests.sql create mode 100644 adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.2__Add_Operation_Status.sql create mode 100644 adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.3__Remove_Invalid_requestdb_data.sql create mode 100644 adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.4__Activate_Requests_Nullable_Coulmns.sql create mode 100644 adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.5__Archived_Infra_Requests.sql create mode 100644 adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.6__shedlock.sql create mode 100644 adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.7__Update_Status_Message_ColumnTypes.sql (limited to 'adapters/mso-requests-db-adapter/src/main/resources/db') diff --git a/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V1__Base_version.sql b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V1__Base_version.sql new file mode 100644 index 0000000000..94f7d2bb75 --- /dev/null +++ b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V1__Base_version.sql @@ -0,0 +1,155 @@ +use requestdb; + +CREATE TABLE `activate_operational_env_service_model_distribution_status` ( + `OPERATIONAL_ENV_ID` varchar(45) NOT NULL, + `SERVICE_MODEL_VERSION_ID` varchar(45) NOT NULL, + `REQUEST_ID` varchar(45) NOT NULL, + `SERVICE_MOD_VER_FINAL_DISTR_STATUS` varchar(45) DEFAULT NULL, + `RECOVERY_ACTION` varchar(30) DEFAULT NULL, + `RETRY_COUNT_LEFT` int(11) DEFAULT NULL, + `WORKLOAD_CONTEXT` varchar(80) NOT NULL, + `CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`OPERATIONAL_ENV_ID`,`SERVICE_MODEL_VERSION_ID`,`REQUEST_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +CREATE TABLE `activate_operational_env_per_distributionid_status` ( + `DISTRIBUTION_ID` varchar(45) NOT NULL, + `DISTRIBUTION_ID_STATUS` varchar(45) DEFAULT NULL, + `DISTRIBUTION_ID_ERROR_REASON` varchar(250) DEFAULT NULL, + `CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, + `OPERATIONAL_ENV_ID` varchar(45) NOT NULL, + `SERVICE_MODEL_VERSION_ID` varchar(45) NOT NULL, + `REQUEST_ID` varchar(45) NOT NULL, + PRIMARY KEY (`DISTRIBUTION_ID`), + KEY `fk_activate_op_env_per_distributionid_status__aoesmds1_idx` (`OPERATIONAL_ENV_ID`,`SERVICE_MODEL_VERSION_ID`,`REQUEST_ID`), + CONSTRAINT `fk_activate_op_env_per_distributionid_status__aoesmds1` FOREIGN KEY (`OPERATIONAL_ENV_ID`, `SERVICE_MODEL_VERSION_ID`, `REQUEST_ID`) REFERENCES `activate_operational_env_service_model_distribution_status` (`OPERATIONAL_ENV_ID`, `SERVICE_MODEL_VERSION_ID`, `REQUEST_ID`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +CREATE TABLE `active_requests` ( + `REQUEST_ID` varchar(45) NOT NULL, + `CLIENT_REQUEST_ID` varchar(45) DEFAULT NULL, + `SERVICE_INSTANCE_ID` varchar(50) NOT NULL, + `SUBSCRIBER_NAME` varchar(200) DEFAULT NULL, + `REQUEST_URI` varchar(255) DEFAULT NULL, + `SERVICE_TYPE` varchar(65) NOT NULL, + `REQUEST_ACTION` varchar(45) NOT NULL, + `NOTIFICATION_URL` varchar(255) DEFAULT NULL, + `REQUEST_ID_IN_PROGRESS` varchar(45) DEFAULT NULL, + `START_TIME` datetime DEFAULT NULL, + `MODIFY_TIME` datetime DEFAULT NULL, + `COMPLETION_TIME` datetime DEFAULT NULL, + `RESPONSE_CODE` varchar(20) DEFAULT NULL, + `RESPONSE_BODY` longtext, + `STATUS` varchar(25) DEFAULT NULL, + `SERVICE_REQUEST_TIMEOUT` datetime DEFAULT NULL, + `FINAL_ERROR_CODE` varchar(20) DEFAULT NULL, + `FINAL_ERROR_MESSAGE` varchar(2000) DEFAULT NULL, + `ORDER_NUMBER` varchar(45) DEFAULT NULL, + `SOURCE` varchar(20) DEFAULT NULL, + `RESPONSE_STATUS` varchar(25) DEFAULT NULL, + `ORDER_VERSION` varchar(20) DEFAULT NULL, + `LAST_MODIFIED_BY` varchar(20) DEFAULT NULL, + `MOCARS_TICKET_NUM` varchar(200) DEFAULT NULL, + `REQUEST_BODY` longtext, + `REQUEST_SUB_ACTION` varchar(45) DEFAULT NULL, + `SDNC_CALLBACK_BPEL_URL` varchar(255) DEFAULT NULL, + `FEATURE_TYPE` varchar(255) DEFAULT NULL, + `FEATURE_INSTANCE_ID` varchar(255) DEFAULT NULL, + `REQUEST_TYPE` varchar(255) DEFAULT NULL, + `INTERIM_COMPLETION_TIME` datetime DEFAULT NULL, + `INTERIM_STAGE_COMPLETION` int(11) DEFAULT NULL, + `SERVICE_NAME_VERSION_ID` varchar(50) DEFAULT NULL, + `GLOBAL_SUBSCRIBER_ID` varchar(255) DEFAULT NULL, + `SERVICE_ID` varchar(50) DEFAULT NULL, + `SERVICE_VERSION` varchar(10) DEFAULT NULL, + `CORRELATOR` varchar(50) DEFAULT NULL, + PRIMARY KEY (`REQUEST_ID`), + UNIQUE KEY `UK_f0hdk7xbw5mb2trnxx0fvlh3x` (`CLIENT_REQUEST_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `infra_active_requests` ( + `REQUEST_ID` varchar(45) NOT NULL, + `CLIENT_REQUEST_ID` varchar(45) DEFAULT NULL, + `ACTION` varchar(45) DEFAULT NULL, + `REQUEST_STATUS` varchar(20) DEFAULT NULL, + `STATUS_MESSAGE` longtext, + `PROGRESS` bigint(20) DEFAULT NULL, + `START_TIME` datetime DEFAULT NULL, + `END_TIME` datetime DEFAULT NULL, + `SOURCE` varchar(45) DEFAULT NULL, + `VNF_ID` varchar(45) DEFAULT NULL, + `VNF_NAME` varchar(80) DEFAULT NULL, + `VNF_TYPE` varchar(200) DEFAULT NULL, + `SERVICE_TYPE` varchar(45) DEFAULT NULL, + `AIC_NODE_CLLI` varchar(11) DEFAULT NULL, + `TENANT_ID` varchar(45) DEFAULT NULL, + `PROV_STATUS` varchar(20) DEFAULT NULL, + `VNF_PARAMS` longtext, + `VNF_OUTPUTS` longtext, + `REQUEST_BODY` longtext, + `RESPONSE_BODY` longtext, + `LAST_MODIFIED_BY` varchar(100) DEFAULT NULL, + `MODIFY_TIME` datetime DEFAULT NULL, + `REQUEST_TYPE` varchar(20) DEFAULT NULL, + `VOLUME_GROUP_ID` varchar(45) DEFAULT NULL, + `VOLUME_GROUP_NAME` varchar(45) DEFAULT NULL, + `VF_MODULE_ID` varchar(45) DEFAULT NULL, + `VF_MODULE_NAME` varchar(200) DEFAULT NULL, + `VF_MODULE_MODEL_NAME` varchar(200) DEFAULT NULL, + `AAI_SERVICE_ID` varchar(50) DEFAULT NULL, + `AIC_CLOUD_REGION` varchar(11) DEFAULT NULL, + `CALLBACK_URL` varchar(200) DEFAULT NULL, + `CORRELATOR` varchar(80) DEFAULT NULL, + `NETWORK_ID` varchar(45) DEFAULT NULL, + `NETWORK_NAME` varchar(80) DEFAULT NULL, + `NETWORK_TYPE` varchar(80) DEFAULT NULL, + `REQUEST_SCOPE` varchar(50) NOT NULL, + `REQUEST_ACTION` varchar(45) NOT NULL DEFAULT 'unknown', + `SERVICE_INSTANCE_ID` varchar(45) DEFAULT NULL, + `SERVICE_INSTANCE_NAME` varchar(80) DEFAULT NULL, + `REQUESTOR_ID` varchar(50) DEFAULT NULL, + `CONFIGURATION_ID` varchar(45) DEFAULT NULL, + `CONFIGURATION_NAME` varchar(200) DEFAULT NULL, + `OPERATIONAL_ENV_ID` varchar(45) DEFAULT NULL, + `OPERATIONAL_ENV_NAME` varchar(200) DEFAULT NULL, + PRIMARY KEY (`REQUEST_ID`), + UNIQUE KEY `UK_bhu6w8p7wvur4pin0gjw2d5ak` (`CLIENT_REQUEST_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +CREATE TABLE `site_status` ( + `SITE_NAME` varchar(255) NOT NULL, + `STATUS` bit(1) DEFAULT NULL, + `CREATION_TIMESTAMP` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`SITE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `watchdog_distributionid_status` ( + `DISTRIBUTION_ID` varchar(45) NOT NULL, + `DISTRIBUTION_ID_STATUS` varchar(45) DEFAULT NULL, + `CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`DISTRIBUTION_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `watchdog_per_component_distribution_status` ( + `DISTRIBUTION_ID` varchar(45) NOT NULL, + `COMPONENT_NAME` varchar(45) NOT NULL, + `COMPONENT_DISTRIBUTION_STATUS` varchar(45) DEFAULT NULL, + `CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`DISTRIBUTION_ID`,`COMPONENT_NAME`), + CONSTRAINT `fk_watchdog_component_distribution_status_watchdog_distributi1` FOREIGN KEY (`DISTRIBUTION_ID`) REFERENCES `watchdog_distributionid_status` (`DISTRIBUTION_ID`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `watchdog_service_mod_ver_id_lookup` ( + `DISTRIBUTION_ID` varchar(45) NOT NULL, + `SERVICE_MODEL_VERSION_ID` varchar(45) NOT NULL, + `CREATE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` timestamp NULL DEFAULT NULL, + PRIMARY KEY (`DISTRIBUTION_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git a/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.1__Add_Identifiers_Active_Requests.sql b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.1__Add_Identifiers_Active_Requests.sql new file mode 100644 index 0000000000..8e6a767d2c --- /dev/null +++ b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.1__Add_Identifiers_Active_Requests.sql @@ -0,0 +1,9 @@ +use requestdb; + +ALTER TABLE active_requests +ADD VPN_ID varchar(200), +ADD PROGRESS BIGINT(20), +ADD STATUS_MESSAGE LONGTEXT, +ADD REQUESTED_SERVICE_NAME varchar(200), +ADD PRODUCT_FLAVOR varchar(200), +ADD SERVICE_INSTANCE_NAME varchar(80); diff --git a/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.2__Add_Operation_Status.sql b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.2__Add_Operation_Status.sql new file mode 100644 index 0000000000..1e96f614b5 --- /dev/null +++ b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.2__Add_Operation_Status.sql @@ -0,0 +1,30 @@ +use requestdb; + +create table if not exists operation_status ( + SERVICE_ID varchar(255) not null, + OPERATION_ID varchar(255) not null, + SERVICE_NAME varchar(255), + OPERATION_TYPE varchar(255), + USER_ID varchar(255), + RESULT varchar(255), + OPERATION_CONTENT varchar(255), + PROGRESS varchar(255), + REASON varchar(255), + OPERATE_AT datetime, + FINISHED_AT datetime, + primary key (SERVICE_ID,OPERATION_ID) +); + +create table resource_operation_status ( + SERVICE_ID varchar(255) not null, + OPERATION_ID varchar(255) not null, + RESOURCE_TEMPLATE_UUID varchar(255) not null, + OPER_TYPE varchar(255), + RESOURCE_INSTANCE_ID varchar(255), + JOB_ID varchar(255), + STATUS varchar(255), + PROGRESS varchar(255), + ERROR_CODE varchar(255) , + STATUS_DESCRIPOTION varchar(255) , + primary key (SERVICE_ID,OPERATION_ID,RESOURCE_TEMPLATE_UUID) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; \ No newline at end of file diff --git a/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.3__Remove_Invalid_requestdb_data.sql b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.3__Remove_Invalid_requestdb_data.sql new file mode 100644 index 0000000000..d361bcedd9 --- /dev/null +++ b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.3__Remove_Invalid_requestdb_data.sql @@ -0,0 +1,5 @@ +USE requestdb; + +delete from infra_active_requests where source != 'VID' and source != 'POLO'; + +delete from infra_active_requests where request_body like '<%'; diff --git a/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.4__Activate_Requests_Nullable_Coulmns.sql b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.4__Activate_Requests_Nullable_Coulmns.sql new file mode 100644 index 0000000000..32a9d61645 --- /dev/null +++ b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.4__Activate_Requests_Nullable_Coulmns.sql @@ -0,0 +1,6 @@ +USE requestdb; + +ALTER TABLE active_requests + MODIFY IF EXISTS SERVICE_INSTANCE_ID varchar(50) NULL, + MODIFY IF EXISTS REQUEST_ACTION varchar(45) NULL, + MODIFY IF EXISTS SERVICE_TYPE varchar(65) NULL; \ No newline at end of file diff --git a/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.5__Archived_Infra_Requests.sql b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.5__Archived_Infra_Requests.sql new file mode 100644 index 0000000000..b2568627a3 --- /dev/null +++ b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.5__Archived_Infra_Requests.sql @@ -0,0 +1,51 @@ +use requestdb; + +CREATE TABLE IF NOT EXISTS `archived_infra_requests` ( +`REQUEST_ID` VARCHAR(45) NOT NULL, +`CLIENT_REQUEST_ID` VARCHAR(45) NULL DEFAULT NULL, +`ACTION` VARCHAR(45) NULL, +`REQUEST_STATUS` VARCHAR(20) NULL DEFAULT NULL, +`STATUS_MESSAGE` LONGTEXT NULL DEFAULT NULL, +`PROGRESS` BIGINT(20) NULL DEFAULT NULL, +`START_TIME` DATETIME NULL DEFAULT NULL, +`END_TIME` DATETIME NULL DEFAULT NULL, +`SOURCE` VARCHAR(45) NULL DEFAULT NULL, +`VNF_ID` VARCHAR(45) NULL DEFAULT NULL, +`VNF_NAME` VARCHAR(80) NULL DEFAULT NULL, +`VNF_TYPE` VARCHAR(200) NULL DEFAULT NULL, +`SERVICE_TYPE` VARCHAR(45) NULL DEFAULT NULL, +`AIC_NODE_CLLI` VARCHAR(11) NULL DEFAULT NULL, +`TENANT_ID` VARCHAR(45) NULL DEFAULT NULL, +`PROV_STATUS` VARCHAR(20) NULL DEFAULT NULL, +`VNF_PARAMS` LONGTEXT NULL DEFAULT NULL, +`VNF_OUTPUTS` LONGTEXT NULL DEFAULT NULL, +`REQUEST_BODY` LONGTEXT NULL DEFAULT NULL, +`RESPONSE_BODY` LONGTEXT NULL DEFAULT NULL, +`LAST_MODIFIED_BY` VARCHAR(100) NULL DEFAULT NULL, +`MODIFY_TIME` DATETIME NULL DEFAULT NULL, +`REQUEST_TYPE` VARCHAR(20) NULL DEFAULT NULL, +`VOLUME_GROUP_ID` VARCHAR(45) NULL DEFAULT NULL, +`VOLUME_GROUP_NAME` VARCHAR(45) NULL DEFAULT NULL, +`VF_MODULE_ID` VARCHAR(45) NULL DEFAULT NULL, +`VF_MODULE_NAME` VARCHAR(200) NULL DEFAULT NULL, +`VF_MODULE_MODEL_NAME` VARCHAR(200) NULL DEFAULT NULL, +`AAI_SERVICE_ID` VARCHAR(50) NULL DEFAULT NULL, +`AIC_CLOUD_REGION` VARCHAR(11) NULL DEFAULT NULL, +`CALLBACK_URL` VARCHAR(200) NULL DEFAULT NULL, +`CORRELATOR` VARCHAR(80) NULL DEFAULT NULL, +`NETWORK_ID` VARCHAR(45) NULL DEFAULT NULL, +`NETWORK_NAME` VARCHAR(80) NULL DEFAULT NULL, +`NETWORK_TYPE` VARCHAR(80) NULL DEFAULT NULL, +`REQUEST_SCOPE` VARCHAR(45) NOT NULL DEFAULT 'unknown', +`REQUEST_ACTION` VARCHAR(45) NOT NULL DEFAULT 'unknown', +`SERVICE_INSTANCE_ID` VARCHAR(45) NULL DEFAULT NULL, +`SERVICE_INSTANCE_NAME` VARCHAR(80) NULL DEFAULT NULL, +`REQUESTOR_ID` VARCHAR(50) NULL DEFAULT NULL, +`CONFIGURATION_ID` VARCHAR(45) NULL, +`CONFIGURATION_NAME` VARCHAR(200) NULL, +`OPERATIONAL_ENV_ID` VARCHAR(45) NULL, +`OPERATIONAL_ENV_NAME` VARCHAR(200) NULL, +PRIMARY KEY (`REQUEST_ID`), +UNIQUE INDEX `UK_client_request_id` (`CLIENT_REQUEST_ID` ASC)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; diff --git a/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.6__shedlock.sql b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.6__shedlock.sql new file mode 100644 index 0000000000..76faf07f59 --- /dev/null +++ b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.6__shedlock.sql @@ -0,0 +1,9 @@ +use requestdb; + +CREATE TABLE SHEDLOCK( +NAME VARCHAR(64), +LOCK_UNTIL TIMESTAMP(3) NULL, +LOCKED_AT TIMESTAMP(3) NULL, +LOCKED_BY VARCHAR(255), +PRIMARY KEY (NAME) +); diff --git a/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.7__Update_Status_Message_ColumnTypes.sql b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.7__Update_Status_Message_ColumnTypes.sql new file mode 100644 index 0000000000..5433e397d9 --- /dev/null +++ b/adapters/mso-requests-db-adapter/src/main/resources/db/migration/V2.7__Update_Status_Message_ColumnTypes.sql @@ -0,0 +1,8 @@ +use requestdb; + +ALTER TABLE active_requests +MODIFY STATUS_MESSAGE LONGTEXT; + + +ALTER TABLE infra_active_requests +MODIFY STATUS_MESSAGE LONGTEXT; -- cgit 1.2.3-korg