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) --- .../mariadb/db-sql-scripts/.gitignore | 1 + .../camunda/mariadb_engine_7.8.0-ee.sql | 1226 +++++++++ .../mariadb_engine_7.8_patch_7.8.0_to_7.8.2.sql | 7 + .../camunda/mysql_create_camunda_admin.sql | 18 +- .../old-scripts/catalog_add_constraints.sql | 45 + .../old-scripts/catalog_timestamp_mso_db.sql | 19 + .../old-scripts/site_status_updated_timestamp.sql | 1 + .../sub-sql-files/catalog_add_constraints.sql | 45 - .../sub-sql-files/catalog_timestamp_mso_db.sql | 19 - ...V2.10__UpdateNorthboundRequestToUseInstance.sql | 24 + .../V3.0__UpdateOrchFlowTableWithATTFlows.sql | 16 + .../site_status_updated_timestamp.sql | 1 - .../camunda/archive_mariadb_camunda_tables.sql | 1330 +++++----- ...iaDB-upgrade-1702.37_drop1_to_1707.40_drop1.sql | 58 +- ...iaDB-upgrade-1707.40_drop1_to_1707.41_drop1.sql | 2700 ++++++++++---------- ...ariaDB-upgradeScript-1707.41.1_to_1710.44.1.sql | 522 ++-- ...so_catalog_data_load_1710.46.1_to_1802.48.1.sql | 34 +- ...talog_schema_upgrade_1710.46.1_to_1802.48.1.sql | 42 +- ...uests_schema_upgrade_1710.46.1_to_1802.48.1.sql | 198 +- 19 files changed, 3790 insertions(+), 2516 deletions(-) create mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/.gitignore create mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8.0-ee.sql create mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8_patch_7.8.0_to_7.8.2.sql create mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_add_constraints.sql create mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_timestamp_mso_db.sql create mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/site_status_updated_timestamp.sql delete mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_add_constraints.sql delete mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_timestamp_mso_db.sql create mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V2.10__UpdateNorthboundRequestToUseInstance.sql create mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V3.0__UpdateOrchFlowTableWithATTFlows.sql delete mode 100644 packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/site_status_updated_timestamp.sql (limited to 'packages/root-pack-extras') diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/.gitignore b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/.gitignore new file mode 100644 index 0000000000..568f8e96a3 --- /dev/null +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/.gitignore @@ -0,0 +1 @@ +/main-schemas/ diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8.0-ee.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8.0-ee.sql new file mode 100644 index 0000000000..712b58860f --- /dev/null +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8.0-ee.sql @@ -0,0 +1,1226 @@ +# Start of Statements added for MSO +DROP DATABASE IF EXISTS `camundabpmn`; + +CREATE DATABASE /*!32312 IF NOT EXISTS*/ `camundabpmn` /*!40100 DEFAULT CHARACTER SET latin1 */; + +USE `camundabpmn`; + +# DROP USER IF EXISTS 'camunda'; +#delete from mysql.user where User='camunda'; +#CREATE USER 'camunda'; +#GRANT ALL on camundabpmn.* to 'camunda' identified by 'camunda123' with GRANT OPTION; +FLUSH PRIVILEGES; +# End of Statements added for MSO + + +create table ACT_GE_PROPERTY ( + NAME_ varchar(64), + VALUE_ varchar(300), + REV_ integer, + primary key (NAME_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +insert into ACT_GE_PROPERTY +values ('schema.version', 'fox', 1); + +insert into ACT_GE_PROPERTY +values ('schema.history', 'create(fox)', 1); + +insert into ACT_GE_PROPERTY +values ('next.dbid', '1', 1); + +insert into ACT_GE_PROPERTY +values ('deployment.lock', '0', 1); + +insert into ACT_GE_PROPERTY +values ('history.cleanup.job.lock', '0', 1); + +insert into ACT_GE_PROPERTY +values ('startup.lock', '0', 1); + +create table ACT_GE_BYTEARRAY ( + ID_ varchar(64), + REV_ integer, + NAME_ varchar(255), + DEPLOYMENT_ID_ varchar(64), + BYTES_ LONGBLOB, + GENERATED_ TINYINT, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RE_DEPLOYMENT ( + ID_ varchar(64), + NAME_ varchar(255), + DEPLOY_TIME_ timestamp(3), + SOURCE_ varchar(255), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_EXECUTION ( + ID_ varchar(64), + REV_ integer, + PROC_INST_ID_ varchar(64), + BUSINESS_KEY_ varchar(255), + PARENT_ID_ varchar(64), + PROC_DEF_ID_ varchar(64), + SUPER_EXEC_ varchar(64), + SUPER_CASE_EXEC_ varchar(64), + CASE_INST_ID_ varchar(64), + ACT_ID_ varchar(255), + ACT_INST_ID_ varchar(64), + IS_ACTIVE_ TINYINT, + IS_CONCURRENT_ TINYINT, + IS_SCOPE_ TINYINT, + IS_EVENT_SCOPE_ TINYINT, + SUSPENSION_STATE_ integer, + CACHED_ENT_STATE_ integer, + SEQUENCE_COUNTER_ bigint, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_JOB ( + ID_ varchar(64) NOT NULL, + REV_ integer, + TYPE_ varchar(255) NOT NULL, + LOCK_EXP_TIME_ timestamp(3) NULL, + LOCK_OWNER_ varchar(255), + EXCLUSIVE_ boolean, + EXECUTION_ID_ varchar(64), + PROCESS_INSTANCE_ID_ varchar(64), + PROCESS_DEF_ID_ varchar(64), + PROCESS_DEF_KEY_ varchar(255), + RETRIES_ integer, + EXCEPTION_STACK_ID_ varchar(64), + EXCEPTION_MSG_ varchar(4000), + DUEDATE_ timestamp(3) NULL, + REPEAT_ varchar(255), + HANDLER_TYPE_ varchar(255), + HANDLER_CFG_ varchar(4000), + DEPLOYMENT_ID_ varchar(64), + SUSPENSION_STATE_ integer NOT NULL DEFAULT 1, + JOB_DEF_ID_ varchar(64), + PRIORITY_ bigint NOT NULL DEFAULT 0, + SEQUENCE_COUNTER_ bigint, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_JOBDEF ( + ID_ varchar(64) NOT NULL, + REV_ integer, + PROC_DEF_ID_ varchar(64), + PROC_DEF_KEY_ varchar(255), + ACT_ID_ varchar(255), + JOB_TYPE_ varchar(255) NOT NULL, + JOB_CONFIGURATION_ varchar(255), + SUSPENSION_STATE_ integer, + JOB_PRIORITY_ bigint, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RE_PROCDEF ( + ID_ varchar(64) not null, + REV_ integer, + CATEGORY_ varchar(255), + NAME_ varchar(255), + KEY_ varchar(255) not null, + VERSION_ integer not null, + DEPLOYMENT_ID_ varchar(64), + RESOURCE_NAME_ varchar(4000), + DGRM_RESOURCE_NAME_ varchar(4000), + HAS_START_FORM_KEY_ TINYINT, + SUSPENSION_STATE_ integer, + TENANT_ID_ varchar(64), + VERSION_TAG_ varchar(64), + HISTORY_TTL_ integer, + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_TASK ( + ID_ varchar(64), + REV_ integer, + EXECUTION_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + PROC_DEF_ID_ varchar(64), + CASE_EXECUTION_ID_ varchar(64), + CASE_INST_ID_ varchar(64), + CASE_DEF_ID_ varchar(64), + NAME_ varchar(255), + PARENT_TASK_ID_ varchar(64), + DESCRIPTION_ varchar(4000), + TASK_DEF_KEY_ varchar(255), + OWNER_ varchar(255), + ASSIGNEE_ varchar(255), + DELEGATION_ varchar(64), + PRIORITY_ integer, + CREATE_TIME_ timestamp(3), + DUE_DATE_ datetime(3), + FOLLOW_UP_DATE_ datetime(3), + SUSPENSION_STATE_ integer, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_IDENTITYLINK ( + ID_ varchar(64), + REV_ integer, + GROUP_ID_ varchar(255), + TYPE_ varchar(255), + USER_ID_ varchar(255), + TASK_ID_ varchar(64), + PROC_DEF_ID_ varchar(64), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_VARIABLE ( + ID_ varchar(64) not null, + REV_ integer, + TYPE_ varchar(255) not null, + NAME_ varchar(255) not null, + EXECUTION_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + CASE_EXECUTION_ID_ varchar(64), + CASE_INST_ID_ varchar(64), + TASK_ID_ varchar(64), + BYTEARRAY_ID_ varchar(64), + DOUBLE_ double, + LONG_ bigint, + TEXT_ LONGBLOB, + TEXT2_ LONGBLOB, + VAR_SCOPE_ varchar(64) not null, + SEQUENCE_COUNTER_ bigint, + IS_CONCURRENT_LOCAL_ TINYINT, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_EVENT_SUBSCR ( + ID_ varchar(64) not null, + REV_ integer, + EVENT_TYPE_ varchar(255) not null, + EVENT_NAME_ varchar(255), + EXECUTION_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + ACTIVITY_ID_ varchar(255), + CONFIGURATION_ varchar(255), + CREATED_ timestamp(3) not null, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_INCIDENT ( + ID_ varchar(64) not null, + REV_ integer not null, + INCIDENT_TIMESTAMP_ timestamp(3) not null, + INCIDENT_MSG_ varchar(4000), + INCIDENT_TYPE_ varchar(255) not null, + EXECUTION_ID_ varchar(64), + ACTIVITY_ID_ varchar(255), + PROC_INST_ID_ varchar(64), + PROC_DEF_ID_ varchar(64), + CAUSE_INCIDENT_ID_ varchar(64), + ROOT_CAUSE_INCIDENT_ID_ varchar(64), + CONFIGURATION_ varchar(255), + TENANT_ID_ varchar(64), + JOB_DEF_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_AUTHORIZATION ( + ID_ varchar(64) not null, + REV_ integer not null, + TYPE_ integer not null, + GROUP_ID_ varchar(255), + USER_ID_ varchar(255), + RESOURCE_TYPE_ integer not null, + RESOURCE_ID_ varchar(255), + PERMS_ integer, + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_FILTER ( + ID_ varchar(64) not null, + REV_ integer not null, + RESOURCE_TYPE_ varchar(255) not null, + NAME_ varchar(255) not null, + OWNER_ varchar(255), + QUERY_ LONGTEXT not null, + PROPERTIES_ LONGTEXT, + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_METER_LOG ( + ID_ varchar(64) not null, + NAME_ varchar(64) not null, + REPORTER_ varchar(255), + VALUE_ bigint, + TIMESTAMP_ timestamp(3), + MILLISECONDS_ bigint DEFAULT 0, + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_EXT_TASK ( + ID_ varchar(64) not null, + REV_ integer not null, + WORKER_ID_ varchar(255), + TOPIC_NAME_ varchar(255), + RETRIES_ integer, + ERROR_MSG_ varchar(4000), + ERROR_DETAILS_ID_ varchar(64), + LOCK_EXP_TIME_ timestamp(3) NULL, + SUSPENSION_STATE_ integer, + EXECUTION_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + PROC_DEF_ID_ varchar(64), + PROC_DEF_KEY_ varchar(255), + ACT_ID_ varchar(255), + ACT_INST_ID_ varchar(64), + TENANT_ID_ varchar(64), + PRIORITY_ bigint NOT NULL DEFAULT 0, + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_RU_BATCH ( + ID_ varchar(64) not null, + REV_ integer not null, + TYPE_ varchar(255), + TOTAL_JOBS_ integer, + JOBS_CREATED_ integer, + JOBS_PER_SEED_ integer, + INVOCATIONS_PER_JOB_ integer, + SEED_JOB_DEF_ID_ varchar(64), + BATCH_JOB_DEF_ID_ varchar(64), + MONITOR_JOB_DEF_ID_ varchar(64), + SUSPENSION_STATE_ integer, + CONFIGURATION_ varchar(255), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create index ACT_IDX_EXEC_BUSKEY on ACT_RU_EXECUTION(BUSINESS_KEY_); +create index ACT_IDX_EXEC_TENANT_ID on ACT_RU_EXECUTION(TENANT_ID_); +create index ACT_IDX_TASK_CREATE on ACT_RU_TASK(CREATE_TIME_); +create index ACT_IDX_TASK_ASSIGNEE on ACT_RU_TASK(ASSIGNEE_); +create index ACT_IDX_TASK_TENANT_ID on ACT_RU_TASK(TENANT_ID_); +create index ACT_IDX_IDENT_LNK_USER on ACT_RU_IDENTITYLINK(USER_ID_); +create index ACT_IDX_IDENT_LNK_GROUP on ACT_RU_IDENTITYLINK(GROUP_ID_); +create index ACT_IDX_EVENT_SUBSCR_CONFIG_ on ACT_RU_EVENT_SUBSCR(CONFIGURATION_); +create index ACT_IDX_EVENT_SUBSCR_TENANT_ID on ACT_RU_EVENT_SUBSCR(TENANT_ID_); +create index ACT_IDX_VARIABLE_TASK_ID on ACT_RU_VARIABLE(TASK_ID_); +create index ACT_IDX_VARIABLE_TENANT_ID on ACT_RU_VARIABLE(TENANT_ID_); +create index ACT_IDX_ATHRZ_PROCEDEF on ACT_RU_IDENTITYLINK(PROC_DEF_ID_); +create index ACT_IDX_INC_CONFIGURATION on ACT_RU_INCIDENT(CONFIGURATION_); +create index ACT_IDX_INC_TENANT_ID on ACT_RU_INCIDENT(TENANT_ID_); +-- CAM-5914 +create index ACT_IDX_JOB_EXECUTION_ID on ACT_RU_JOB(EXECUTION_ID_); +-- this index needs to be limited in mariadb see CAM-6938 +create index ACT_IDX_JOB_HANDLER on ACT_RU_JOB(HANDLER_TYPE_(100),HANDLER_CFG_(155)); +create index ACT_IDX_JOB_PROCINST on ACT_RU_JOB(PROCESS_INSTANCE_ID_); +create index ACT_IDX_JOB_TENANT_ID on ACT_RU_JOB(TENANT_ID_); +create index ACT_IDX_JOBDEF_TENANT_ID on ACT_RU_JOBDEF(TENANT_ID_); + +-- new metric milliseconds column +CREATE INDEX ACT_IDX_METER_LOG_MS ON ACT_RU_METER_LOG(MILLISECONDS_); +CREATE INDEX ACT_IDX_METER_LOG_NAME_MS ON ACT_RU_METER_LOG(NAME_, MILLISECONDS_); +CREATE INDEX ACT_IDX_METER_LOG_REPORT ON ACT_RU_METER_LOG(NAME_, REPORTER_, MILLISECONDS_); + +-- old metric timestamp column +CREATE INDEX ACT_IDX_METER_LOG_TIME ON ACT_RU_METER_LOG(TIMESTAMP_); +CREATE INDEX ACT_IDX_METER_LOG ON ACT_RU_METER_LOG(NAME_, TIMESTAMP_); + +create index ACT_IDX_EXT_TASK_TOPIC on ACT_RU_EXT_TASK(TOPIC_NAME_); +create index ACT_IDX_EXT_TASK_TENANT_ID on ACT_RU_EXT_TASK(TENANT_ID_); +create index ACT_IDX_EXT_TASK_PRIORITY ON ACT_RU_EXT_TASK(PRIORITY_); +create index ACT_IDX_EXT_TASK_ERR_DETAILS ON ACT_RU_EXT_TASK(ERROR_DETAILS_ID_); +create index ACT_IDX_AUTH_GROUP_ID ON ACT_RU_AUTHORIZATION(GROUP_ID_); +create index ACT_IDX_JOB_JOB_DEF_ID on ACT_RU_JOB(JOB_DEF_ID_); + +alter table ACT_GE_BYTEARRAY + add constraint ACT_FK_BYTEARR_DEPL + foreign key (DEPLOYMENT_ID_) + references ACT_RE_DEPLOYMENT (ID_); + +alter table ACT_RU_EXECUTION + add constraint ACT_FK_EXE_PROCINST + foreign key (PROC_INST_ID_) + references ACT_RU_EXECUTION (ID_) on delete cascade on update cascade; + +alter table ACT_RU_EXECUTION + add constraint ACT_FK_EXE_PARENT + foreign key (PARENT_ID_) + references ACT_RU_EXECUTION (ID_); + +alter table ACT_RU_EXECUTION + add constraint ACT_FK_EXE_SUPER + foreign key (SUPER_EXEC_) + references ACT_RU_EXECUTION (ID_); + +alter table ACT_RU_EXECUTION + add constraint ACT_FK_EXE_PROCDEF + foreign key (PROC_DEF_ID_) + references ACT_RE_PROCDEF (ID_); + +alter table ACT_RU_IDENTITYLINK + add constraint ACT_FK_TSKASS_TASK + foreign key (TASK_ID_) + references ACT_RU_TASK (ID_); + +alter table ACT_RU_IDENTITYLINK + add constraint ACT_FK_ATHRZ_PROCEDEF + foreign key (PROC_DEF_ID_) + references ACT_RE_PROCDEF(ID_); + +alter table ACT_RU_TASK + add constraint ACT_FK_TASK_EXE + foreign key (EXECUTION_ID_) + references ACT_RU_EXECUTION (ID_); + +alter table ACT_RU_TASK + add constraint ACT_FK_TASK_PROCINST + foreign key (PROC_INST_ID_) + references ACT_RU_EXECUTION (ID_); + +alter table ACT_RU_TASK + add constraint ACT_FK_TASK_PROCDEF + foreign key (PROC_DEF_ID_) + references ACT_RE_PROCDEF (ID_); + +alter table ACT_RU_VARIABLE + add constraint ACT_FK_VAR_EXE + foreign key (EXECUTION_ID_) + references ACT_RU_EXECUTION (ID_); + +alter table ACT_RU_VARIABLE + add constraint ACT_FK_VAR_PROCINST + foreign key (PROC_INST_ID_) + references ACT_RU_EXECUTION(ID_); + +alter table ACT_RU_VARIABLE + add constraint ACT_FK_VAR_BYTEARRAY + foreign key (BYTEARRAY_ID_) + references ACT_GE_BYTEARRAY (ID_); + +alter table ACT_RU_JOB + add constraint ACT_FK_JOB_EXCEPTION + foreign key (EXCEPTION_STACK_ID_) + references ACT_GE_BYTEARRAY (ID_); + +alter table ACT_RU_EVENT_SUBSCR + add constraint ACT_FK_EVENT_EXEC + foreign key (EXECUTION_ID_) + references ACT_RU_EXECUTION(ID_); + +alter table ACT_RU_INCIDENT + add constraint ACT_FK_INC_EXE + foreign key (EXECUTION_ID_) + references ACT_RU_EXECUTION (ID_); + +alter table ACT_RU_INCIDENT + add constraint ACT_FK_INC_PROCINST + foreign key (PROC_INST_ID_) + references ACT_RU_EXECUTION (ID_); + +alter table ACT_RU_INCIDENT + add constraint ACT_FK_INC_PROCDEF + foreign key (PROC_DEF_ID_) + references ACT_RE_PROCDEF (ID_); + +alter table ACT_RU_INCIDENT + add constraint ACT_FK_INC_CAUSE + foreign key (CAUSE_INCIDENT_ID_) + references ACT_RU_INCIDENT (ID_) on delete cascade on update cascade; + +alter table ACT_RU_INCIDENT + add constraint ACT_FK_INC_RCAUSE + foreign key (ROOT_CAUSE_INCIDENT_ID_) + references ACT_RU_INCIDENT (ID_) on delete cascade on update cascade; + +alter table ACT_RU_EXT_TASK + add constraint ACT_FK_EXT_TASK_ERROR_DETAILS + foreign key (ERROR_DETAILS_ID_) + references ACT_GE_BYTEARRAY (ID_); + +create index ACT_IDX_INC_JOB_DEF on ACT_RU_INCIDENT(JOB_DEF_ID_); +alter table ACT_RU_INCIDENT + add constraint ACT_FK_INC_JOB_DEF + foreign key (JOB_DEF_ID_) + references ACT_RU_JOBDEF (ID_); + +alter table ACT_RU_AUTHORIZATION + add constraint ACT_UNIQ_AUTH_USER + unique (USER_ID_,TYPE_,RESOURCE_TYPE_,RESOURCE_ID_); + +alter table ACT_RU_AUTHORIZATION + add constraint ACT_UNIQ_AUTH_GROUP + unique (GROUP_ID_,TYPE_,RESOURCE_TYPE_,RESOURCE_ID_); + +alter table ACT_RU_VARIABLE + add constraint ACT_UNIQ_VARIABLE + unique (VAR_SCOPE_, NAME_); + +alter table ACT_RU_EXT_TASK + add constraint ACT_FK_EXT_TASK_EXE + foreign key (EXECUTION_ID_) + references ACT_RU_EXECUTION (ID_); + +create index ACT_IDX_BATCH_SEED_JOB_DEF ON ACT_RU_BATCH(SEED_JOB_DEF_ID_); +alter table ACT_RU_BATCH + add constraint ACT_FK_BATCH_SEED_JOB_DEF + foreign key (SEED_JOB_DEF_ID_) + references ACT_RU_JOBDEF (ID_); + +create index ACT_IDX_BATCH_MONITOR_JOB_DEF ON ACT_RU_BATCH(MONITOR_JOB_DEF_ID_); +alter table ACT_RU_BATCH + add constraint ACT_FK_BATCH_MONITOR_JOB_DEF + foreign key (MONITOR_JOB_DEF_ID_) + references ACT_RU_JOBDEF (ID_); + +create index ACT_IDX_BATCH_JOB_DEF ON ACT_RU_BATCH(BATCH_JOB_DEF_ID_); +alter table ACT_RU_BATCH + add constraint ACT_FK_BATCH_JOB_DEF + foreign key (BATCH_JOB_DEF_ID_) + references ACT_RU_JOBDEF (ID_); + +-- indexes for deadlock problems - https://app.camunda.com/jira/browse/CAM-2567 -- +create index ACT_IDX_INC_CAUSEINCID on ACT_RU_INCIDENT(CAUSE_INCIDENT_ID_); +create index ACT_IDX_INC_EXID on ACT_RU_INCIDENT(EXECUTION_ID_); +create index ACT_IDX_INC_PROCDEFID on ACT_RU_INCIDENT(PROC_DEF_ID_); +create index ACT_IDX_INC_PROCINSTID on ACT_RU_INCIDENT(PROC_INST_ID_); +create index ACT_IDX_INC_ROOTCAUSEINCID on ACT_RU_INCIDENT(ROOT_CAUSE_INCIDENT_ID_); +-- index for deadlock problem - https://app.camunda.com/jira/browse/CAM-4440 -- +create index ACT_IDX_AUTH_RESOURCE_ID on ACT_RU_AUTHORIZATION(RESOURCE_ID_); +-- index to prevent deadlock on fk constraint - https://app.camunda.com/jira/browse/CAM-5440 -- +create index ACT_IDX_EXT_TASK_EXEC on ACT_RU_EXT_TASK(EXECUTION_ID_); + +-- indexes to improve deployment +create index ACT_IDX_BYTEARRAY_NAME on ACT_GE_BYTEARRAY(NAME_); +create index ACT_IDX_DEPLOYMENT_NAME on ACT_RE_DEPLOYMENT(NAME_); +create index ACT_IDX_DEPLOYMENT_TENANT_ID on ACT_RE_DEPLOYMENT(TENANT_ID_); +create index ACT_IDX_JOBDEF_PROC_DEF_ID ON ACT_RU_JOBDEF(PROC_DEF_ID_); +create index ACT_IDX_JOB_HANDLER_TYPE ON ACT_RU_JOB(HANDLER_TYPE_); +create index ACT_IDX_EVENT_SUBSCR_EVT_NAME ON ACT_RU_EVENT_SUBSCR(EVENT_NAME_); +create index ACT_IDX_PROCDEF_DEPLOYMENT_ID ON ACT_RE_PROCDEF(DEPLOYMENT_ID_); +create index ACT_IDX_PROCDEF_TENANT_ID ON ACT_RE_PROCDEF(TENANT_ID_); +create index ACT_IDX_PROCDEF_VER_TAG ON ACT_RE_PROCDEF(VERSION_TAG_); +-- create case definition table -- +create table ACT_RE_CASE_DEF ( + ID_ varchar(64) not null, + REV_ integer, + CATEGORY_ varchar(255), + NAME_ varchar(255), + KEY_ varchar(255) not null, + VERSION_ integer not null, + DEPLOYMENT_ID_ varchar(64), + RESOURCE_NAME_ varchar(4000), + DGRM_RESOURCE_NAME_ varchar(4000), + TENANT_ID_ varchar(64), + HISTORY_TTL_ integer, + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +-- create case execution table -- +create table ACT_RU_CASE_EXECUTION ( + ID_ varchar(64) NOT NULL, + REV_ integer, + CASE_INST_ID_ varchar(64), + SUPER_CASE_EXEC_ varchar(64), + SUPER_EXEC_ varchar(64), + BUSINESS_KEY_ varchar(255), + PARENT_ID_ varchar(64), + CASE_DEF_ID_ varchar(64), + ACT_ID_ varchar(255), + PREV_STATE_ integer, + CURRENT_STATE_ integer, + REQUIRED_ boolean, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +-- create case sentry part table -- + +create table ACT_RU_CASE_SENTRY_PART ( + ID_ varchar(64) NOT NULL, + REV_ integer, + CASE_INST_ID_ varchar(64), + CASE_EXEC_ID_ varchar(64), + SENTRY_ID_ varchar(255), + TYPE_ varchar(255), + SOURCE_CASE_EXEC_ID_ varchar(64), + STANDARD_EVENT_ varchar(255), + SOURCE_ varchar(255), + VARIABLE_EVENT_ varchar(255), + VARIABLE_NAME_ varchar(255), + SATISFIED_ boolean, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +-- create index on business key -- +create index ACT_IDX_CASE_EXEC_BUSKEY on ACT_RU_CASE_EXECUTION(BUSINESS_KEY_); + +-- create foreign key constraints on ACT_RU_CASE_EXECUTION -- +alter table ACT_RU_CASE_EXECUTION + add constraint ACT_FK_CASE_EXE_CASE_INST + foreign key (CASE_INST_ID_) + references ACT_RU_CASE_EXECUTION(ID_) on delete cascade on update cascade; + +alter table ACT_RU_CASE_EXECUTION + add constraint ACT_FK_CASE_EXE_PARENT + foreign key (PARENT_ID_) + references ACT_RU_CASE_EXECUTION(ID_); + +alter table ACT_RU_CASE_EXECUTION + add constraint ACT_FK_CASE_EXE_CASE_DEF + foreign key (CASE_DEF_ID_) + references ACT_RE_CASE_DEF(ID_); + +-- create foreign key constraints on ACT_RU_VARIABLE -- +alter table ACT_RU_VARIABLE + add constraint ACT_FK_VAR_CASE_EXE + foreign key (CASE_EXECUTION_ID_) + references ACT_RU_CASE_EXECUTION(ID_); + +alter table ACT_RU_VARIABLE + add constraint ACT_FK_VAR_CASE_INST + foreign key (CASE_INST_ID_) + references ACT_RU_CASE_EXECUTION(ID_); + +-- create foreign key constraints on ACT_RU_TASK -- +alter table ACT_RU_TASK + add constraint ACT_FK_TASK_CASE_EXE + foreign key (CASE_EXECUTION_ID_) + references ACT_RU_CASE_EXECUTION(ID_); + +alter table ACT_RU_TASK + add constraint ACT_FK_TASK_CASE_DEF + foreign key (CASE_DEF_ID_) + references ACT_RE_CASE_DEF(ID_); + +-- create foreign key constraints on ACT_RU_CASE_SENTRY_PART -- +alter table ACT_RU_CASE_SENTRY_PART + add constraint ACT_FK_CASE_SENTRY_CASE_INST + foreign key (CASE_INST_ID_) + references ACT_RU_CASE_EXECUTION(ID_); + +alter table ACT_RU_CASE_SENTRY_PART + add constraint ACT_FK_CASE_SENTRY_CASE_EXEC + foreign key (CASE_EXEC_ID_) + references ACT_RU_CASE_EXECUTION(ID_); + +create index ACT_IDX_CASE_DEF_TENANT_ID on ACT_RE_CASE_DEF(TENANT_ID_); +create index ACT_IDX_CASE_EXEC_TENANT_ID on ACT_RU_CASE_EXECUTION(TENANT_ID_); +-- create decision definition table -- +create table ACT_RE_DECISION_DEF ( + ID_ varchar(64) not null, + REV_ integer, + CATEGORY_ varchar(255), + NAME_ varchar(255), + KEY_ varchar(255) not null, + VERSION_ integer not null, + DEPLOYMENT_ID_ varchar(64), + RESOURCE_NAME_ varchar(4000), + DGRM_RESOURCE_NAME_ varchar(4000), + DEC_REQ_ID_ varchar(64), + DEC_REQ_KEY_ varchar(255), + TENANT_ID_ varchar(64), + HISTORY_TTL_ integer, + VERSION_TAG_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +-- create decision requirements definition table -- +create table ACT_RE_DECISION_REQ_DEF ( + ID_ varchar(64) NOT NULL, + REV_ integer, + CATEGORY_ varchar(255), + NAME_ varchar(255), + KEY_ varchar(255) NOT NULL, + VERSION_ integer NOT NULL, + DEPLOYMENT_ID_ varchar(64), + RESOURCE_NAME_ varchar(4000), + DGRM_RESOURCE_NAME_ varchar(4000), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +alter table ACT_RE_DECISION_DEF + add constraint ACT_FK_DEC_REQ + foreign key (DEC_REQ_ID_) + references ACT_RE_DECISION_REQ_DEF(ID_); + +create index ACT_IDX_DEC_DEF_TENANT_ID on ACT_RE_DECISION_DEF(TENANT_ID_); +create index ACT_IDX_DEC_DEF_REQ_ID on ACT_RE_DECISION_DEF(DEC_REQ_ID_); +create index ACT_IDX_DEC_REQ_DEF_TENANT_ID on ACT_RE_DECISION_REQ_DEF(TENANT_ID_); +create table ACT_HI_PROCINST ( + ID_ varchar(64) not null, + PROC_INST_ID_ varchar(64) not null, + BUSINESS_KEY_ varchar(255), + PROC_DEF_KEY_ varchar(255), + PROC_DEF_ID_ varchar(64) not null, + START_TIME_ datetime(3) not null, + END_TIME_ datetime(3), + DURATION_ bigint, + START_USER_ID_ varchar(255), + START_ACT_ID_ varchar(255), + END_ACT_ID_ varchar(255), + SUPER_PROCESS_INSTANCE_ID_ varchar(64), + SUPER_CASE_INSTANCE_ID_ varchar(64), + CASE_INST_ID_ varchar(64), + DELETE_REASON_ varchar(4000), + TENANT_ID_ varchar(64), + STATE_ varchar(255), + primary key (ID_), + unique (PROC_INST_ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_ACTINST ( + ID_ varchar(64) not null, + PARENT_ACT_INST_ID_ varchar(64), + PROC_DEF_KEY_ varchar(255), + PROC_DEF_ID_ varchar(64) not null, + PROC_INST_ID_ varchar(64) not null, + EXECUTION_ID_ varchar(64) not null, + ACT_ID_ varchar(255) not null, + TASK_ID_ varchar(64), + CALL_PROC_INST_ID_ varchar(64), + CALL_CASE_INST_ID_ varchar(64), + ACT_NAME_ varchar(255), + ACT_TYPE_ varchar(255) not null, + ASSIGNEE_ varchar(64), + START_TIME_ datetime(3) not null, + END_TIME_ datetime(3), + DURATION_ bigint, + ACT_INST_STATE_ integer, + SEQUENCE_COUNTER_ bigint, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_TASKINST ( + ID_ varchar(64) not null, + TASK_DEF_KEY_ varchar(255), + PROC_DEF_KEY_ varchar(255), + PROC_DEF_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + EXECUTION_ID_ varchar(64), + CASE_DEF_KEY_ varchar(255), + CASE_DEF_ID_ varchar(64), + CASE_INST_ID_ varchar(64), + CASE_EXECUTION_ID_ varchar(64), + ACT_INST_ID_ varchar(64), + NAME_ varchar(255), + PARENT_TASK_ID_ varchar(64), + DESCRIPTION_ varchar(4000), + OWNER_ varchar(255), + ASSIGNEE_ varchar(255), + START_TIME_ datetime(3) not null, + END_TIME_ datetime(3), + DURATION_ bigint, + DELETE_REASON_ varchar(4000), + PRIORITY_ integer, + DUE_DATE_ datetime(3), + FOLLOW_UP_DATE_ datetime(3), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_VARINST ( + ID_ varchar(64) not null, + PROC_DEF_KEY_ varchar(255), + PROC_DEF_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + EXECUTION_ID_ varchar(64), + ACT_INST_ID_ varchar(64), + CASE_DEF_KEY_ varchar(255), + CASE_DEF_ID_ varchar(64), + CASE_INST_ID_ varchar(64), + CASE_EXECUTION_ID_ varchar(64), + TASK_ID_ varchar(64), + NAME_ varchar(255) not null, + VAR_TYPE_ varchar(100), + REV_ integer, + BYTEARRAY_ID_ varchar(64), + DOUBLE_ double, + LONG_ bigint, + TEXT_ LONGBLOB, + TEXT2_ LONGBLOB, + TENANT_ID_ varchar(64), + STATE_ varchar(20), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_DETAIL ( + ID_ varchar(64) not null, + TYPE_ varchar(255) not null, + PROC_DEF_KEY_ varchar(255), + PROC_DEF_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + EXECUTION_ID_ varchar(64), + CASE_DEF_KEY_ varchar(255), + CASE_DEF_ID_ varchar(64), + CASE_INST_ID_ varchar(64), + CASE_EXECUTION_ID_ varchar(64), + TASK_ID_ varchar(64), + ACT_INST_ID_ varchar(64), + VAR_INST_ID_ varchar(64), + NAME_ varchar(255) not null, + VAR_TYPE_ varchar(255), + REV_ integer, + TIME_ datetime(3) not null, + BYTEARRAY_ID_ varchar(64), + DOUBLE_ double, + LONG_ bigint, + TEXT_ LONGBLOB, + TEXT2_ LONGBLOB, + SEQUENCE_COUNTER_ bigint, + TENANT_ID_ varchar(64), + OPERATION_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_IDENTITYLINK ( + ID_ varchar(64) not null, + TIMESTAMP_ timestamp(3) not null, + TYPE_ varchar(255), + USER_ID_ varchar(255), + GROUP_ID_ varchar(255), + TASK_ID_ varchar(64), + PROC_DEF_ID_ varchar(64), + OPERATION_TYPE_ varchar(64), + ASSIGNER_ID_ varchar(64), + PROC_DEF_KEY_ varchar(255), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_COMMENT ( + ID_ varchar(64) not null, + TYPE_ varchar(255), + TIME_ datetime(3) not null, + USER_ID_ varchar(255), + TASK_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + ACTION_ varchar(255), + MESSAGE_ varchar(4000), + FULL_MSG_ LONGBLOB, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_ATTACHMENT ( + ID_ varchar(64) not null, + REV_ integer, + USER_ID_ varchar(255), + NAME_ varchar(255), + DESCRIPTION_ varchar(4000), + TYPE_ varchar(255), + TASK_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + URL_ varchar(4000), + CONTENT_ID_ varchar(64), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_OP_LOG ( + ID_ varchar(64) not null, + DEPLOYMENT_ID_ varchar(64), + PROC_DEF_ID_ varchar(64), + PROC_DEF_KEY_ varchar(255), + PROC_INST_ID_ varchar(64), + EXECUTION_ID_ varchar(64), + CASE_DEF_ID_ varchar(64), + CASE_INST_ID_ varchar(64), + CASE_EXECUTION_ID_ varchar(64), + TASK_ID_ varchar(64), + JOB_ID_ varchar(64), + JOB_DEF_ID_ varchar(64), + BATCH_ID_ varchar(64), + USER_ID_ varchar(255), + TIMESTAMP_ timestamp(3) not null, + OPERATION_TYPE_ varchar(64), + OPERATION_ID_ varchar(64), + ENTITY_TYPE_ varchar(30), + PROPERTY_ varchar(64), + ORG_VALUE_ varchar(4000), + NEW_VALUE_ varchar(4000), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_INCIDENT ( + ID_ varchar(64) not null, + PROC_DEF_KEY_ varchar(255), + PROC_DEF_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + EXECUTION_ID_ varchar(64), + CREATE_TIME_ timestamp(3) not null, + END_TIME_ timestamp(3) null, + INCIDENT_MSG_ varchar(4000), + INCIDENT_TYPE_ varchar(255) not null, + ACTIVITY_ID_ varchar(255), + CAUSE_INCIDENT_ID_ varchar(64), + ROOT_CAUSE_INCIDENT_ID_ varchar(64), + CONFIGURATION_ varchar(255), + INCIDENT_STATE_ integer, + TENANT_ID_ varchar(64), + JOB_DEF_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_JOB_LOG ( + ID_ varchar(64) not null, + TIMESTAMP_ timestamp(3) not null, + JOB_ID_ varchar(64) not null, + JOB_DUEDATE_ timestamp(3) NULL, + JOB_RETRIES_ integer, + JOB_PRIORITY_ bigint NOT NULL DEFAULT 0, + JOB_EXCEPTION_MSG_ varchar(4000), + JOB_EXCEPTION_STACK_ID_ varchar(64), + JOB_STATE_ integer, + JOB_DEF_ID_ varchar(64), + JOB_DEF_TYPE_ varchar(255), + JOB_DEF_CONFIGURATION_ varchar(255), + ACT_ID_ varchar(255), + EXECUTION_ID_ varchar(64), + PROCESS_INSTANCE_ID_ varchar(64), + PROCESS_DEF_ID_ varchar(64), + PROCESS_DEF_KEY_ varchar(255), + DEPLOYMENT_ID_ varchar(64), + SEQUENCE_COUNTER_ bigint, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_BATCH ( + ID_ varchar(64) not null, + TYPE_ varchar(255), + TOTAL_JOBS_ integer, + JOBS_PER_SEED_ integer, + INVOCATIONS_PER_JOB_ integer, + SEED_JOB_DEF_ID_ varchar(64), + MONITOR_JOB_DEF_ID_ varchar(64), + BATCH_JOB_DEF_ID_ varchar(64), + TENANT_ID_ varchar(64), + START_TIME_ datetime(3) not null, + END_TIME_ datetime(3), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_EXT_TASK_LOG ( + ID_ varchar(64) not null, + TIMESTAMP_ timestamp(3) not null, + EXT_TASK_ID_ varchar(64) not null, + RETRIES_ integer, + TOPIC_NAME_ varchar(255), + WORKER_ID_ varchar(255), + PRIORITY_ bigint NOT NULL DEFAULT 0, + ERROR_MSG_ varchar(4000), + ERROR_DETAILS_ID_ varchar(64), + ACT_ID_ varchar(255), + ACT_INST_ID_ varchar(64), + EXECUTION_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + PROC_DEF_ID_ varchar(64), + PROC_DEF_KEY_ varchar(255), + TENANT_ID_ varchar(64), + STATE_ integer, + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create index ACT_IDX_HI_PRO_INST_END on ACT_HI_PROCINST(END_TIME_); +create index ACT_IDX_HI_PRO_I_BUSKEY on ACT_HI_PROCINST(BUSINESS_KEY_); +create index ACT_IDX_HI_PRO_INST_TENANT_ID on ACT_HI_PROCINST(TENANT_ID_); +create index ACT_IDX_HI_PRO_INST_PROC_DEF_KEY on ACT_HI_PROCINST(PROC_DEF_KEY_); + +create index ACT_IDX_HI_ACT_INST_START on ACT_HI_ACTINST(START_TIME_); +create index ACT_IDX_HI_ACT_INST_END on ACT_HI_ACTINST(END_TIME_); +create index ACT_IDX_HI_ACT_INST_PROCINST on ACT_HI_ACTINST(PROC_INST_ID_, ACT_ID_); +create index ACT_IDX_HI_ACT_INST_COMP on ACT_HI_ACTINST(EXECUTION_ID_, ACT_ID_, END_TIME_, ID_); +create index ACT_IDX_HI_ACT_INST_STATS on ACT_HI_ACTINST(PROC_DEF_ID_, ACT_ID_, END_TIME_, ACT_INST_STATE_); +create index ACT_IDX_HI_ACT_INST_TENANT_ID on ACT_HI_ACTINST(TENANT_ID_); +create index ACT_IDX_HI_ACT_INST_PROC_DEF_KEY on ACT_HI_ACTINST(PROC_DEF_KEY_); + +create index ACT_IDX_HI_TASK_INST_TENANT_ID on ACT_HI_TASKINST(TENANT_ID_); +create index ACT_IDX_HI_TASK_INST_PROC_DEF_KEY on ACT_HI_TASKINST(PROC_DEF_KEY_); +create index ACT_IDX_HI_TASKINST_PROCINST on ACT_HI_TASKINST(PROC_INST_ID_); +create index ACT_IDX_HI_TASKINSTID_PROCINST on ACT_HI_TASKINST(ID_,PROC_INST_ID_); + +create index ACT_IDX_HI_DETAIL_PROC_INST on ACT_HI_DETAIL(PROC_INST_ID_); +create index ACT_IDX_HI_DETAIL_ACT_INST on ACT_HI_DETAIL(ACT_INST_ID_); +create index ACT_IDX_HI_DETAIL_CASE_INST on ACT_HI_DETAIL(CASE_INST_ID_); +create index ACT_IDX_HI_DETAIL_CASE_EXEC on ACT_HI_DETAIL(CASE_EXECUTION_ID_); +create index ACT_IDX_HI_DETAIL_TIME on ACT_HI_DETAIL(TIME_); +create index ACT_IDX_HI_DETAIL_NAME on ACT_HI_DETAIL(NAME_); +create index ACT_IDX_HI_DETAIL_TASK_ID on ACT_HI_DETAIL(TASK_ID_); +create index ACT_IDX_HI_DETAIL_TENANT_ID on ACT_HI_DETAIL(TENANT_ID_); +create index ACT_IDX_HI_DETAIL_PROC_DEF_KEY on ACT_HI_DETAIL(PROC_DEF_KEY_); +create index ACT_IDX_HI_DETAIL_BYTEAR on ACT_HI_DETAIL(BYTEARRAY_ID_); + +create index ACT_IDX_HI_IDENT_LNK_USER on ACT_HI_IDENTITYLINK(USER_ID_); +create index ACT_IDX_HI_IDENT_LNK_GROUP on ACT_HI_IDENTITYLINK(GROUP_ID_); +create index ACT_IDX_HI_IDENT_LNK_TENANT_ID on ACT_HI_IDENTITYLINK(TENANT_ID_); +create index ACT_IDX_HI_IDENT_LNK_PROC_DEF_KEY on ACT_HI_IDENTITYLINK(PROC_DEF_KEY_); +create index ACT_IDX_HI_IDENT_LINK_TASK on ACT_HI_IDENTITYLINK(TASK_ID_); + +create index ACT_IDX_HI_PROCVAR_PROC_INST on ACT_HI_VARINST(PROC_INST_ID_); +create index ACT_IDX_HI_PROCVAR_NAME_TYPE on ACT_HI_VARINST(NAME_, VAR_TYPE_); +create index ACT_IDX_HI_CASEVAR_CASE_INST on ACT_HI_VARINST(CASE_INST_ID_); +create index ACT_IDX_HI_VAR_INST_TENANT_ID on ACT_HI_VARINST(TENANT_ID_); +create index ACT_IDX_HI_VAR_INST_PROC_DEF_KEY on ACT_HI_VARINST(PROC_DEF_KEY_); +create index ACT_IDX_HI_VARINST_BYTEAR on ACT_HI_VARINST(BYTEARRAY_ID_); + +create index ACT_IDX_HI_INCIDENT_TENANT_ID on ACT_HI_INCIDENT(TENANT_ID_); +create index ACT_IDX_HI_INCIDENT_PROC_DEF_KEY on ACT_HI_INCIDENT(PROC_DEF_KEY_); +create index ACT_IDX_HI_INCIDENT_PROCINST on ACT_HI_INCIDENT(PROC_INST_ID_); + +create index ACT_IDX_HI_JOB_LOG_PROCINST on ACT_HI_JOB_LOG(PROCESS_INSTANCE_ID_); +create index ACT_IDX_HI_JOB_LOG_PROCDEF on ACT_HI_JOB_LOG(PROCESS_DEF_ID_); +create index ACT_IDX_HI_JOB_LOG_TENANT_ID on ACT_HI_JOB_LOG(TENANT_ID_); +create index ACT_IDX_HI_JOB_LOG_JOB_DEF_ID on ACT_HI_JOB_LOG(JOB_DEF_ID_); +create index ACT_IDX_HI_JOB_LOG_PROC_DEF_KEY on ACT_HI_JOB_LOG(PROCESS_DEF_KEY_); +create index ACT_IDX_HI_JOB_LOG_EX_STACK on ACT_HI_JOB_LOG(JOB_EXCEPTION_STACK_ID_); + +create index ACT_HI_EXT_TASK_LOG_PROCINST on ACT_HI_EXT_TASK_LOG(PROC_INST_ID_); +create index ACT_HI_EXT_TASK_LOG_PROCDEF on ACT_HI_EXT_TASK_LOG(PROC_DEF_ID_); +create index ACT_HI_EXT_TASK_LOG_PROC_DEF_KEY on ACT_HI_EXT_TASK_LOG(PROC_DEF_KEY_); +create index ACT_HI_EXT_TASK_LOG_TENANT_ID on ACT_HI_EXT_TASK_LOG(TENANT_ID_); +create index ACT_IDX_HI_EXTTASKLOG_ERRORDET on ACT_HI_EXT_TASK_LOG(ERROR_DETAILS_ID_); + +create index ACT_IDX_HI_OP_LOG_PROCINST on ACT_HI_OP_LOG(PROC_INST_ID_); +create index ACT_IDX_HI_OP_LOG_PROCDEF on ACT_HI_OP_LOG(PROC_DEF_ID_); + +create index ACT_IDX_HI_COMMENT_TASK on ACT_HI_COMMENT(TASK_ID_); +create index ACT_IDX_HI_COMMENT_PROCINST on ACT_HI_COMMENT(PROC_INST_ID_); + +create index ACT_IDX_HI_ATTACHMENT_CONTENT on ACT_HI_ATTACHMENT(CONTENT_ID_); +create index ACT_IDX_HI_ATTACHMENT_PROCINST on ACT_HI_ATTACHMENT(PROC_INST_ID_); +create index ACT_IDX_HI_ATTACHMENT_TASK on ACT_HI_ATTACHMENT(TASK_ID_); +create table ACT_HI_CASEINST ( + ID_ varchar(64) not null, + CASE_INST_ID_ varchar(64) not null, + BUSINESS_KEY_ varchar(255), + CASE_DEF_ID_ varchar(64) not null, + CREATE_TIME_ datetime(3) not null, + CLOSE_TIME_ datetime(3), + DURATION_ bigint, + STATE_ integer, + CREATE_USER_ID_ varchar(255), + SUPER_CASE_INSTANCE_ID_ varchar(64), + SUPER_PROCESS_INSTANCE_ID_ varchar(64), + TENANT_ID_ varchar(64), + primary key (ID_), + unique (CASE_INST_ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_HI_CASEACTINST ( + ID_ varchar(64) not null, + PARENT_ACT_INST_ID_ varchar(64), + CASE_DEF_ID_ varchar(64) not null, + CASE_INST_ID_ varchar(64) not null, + CASE_ACT_ID_ varchar(255) not null, + TASK_ID_ varchar(64), + CALL_PROC_INST_ID_ varchar(64), + CALL_CASE_INST_ID_ varchar(64), + CASE_ACT_NAME_ varchar(255), + CASE_ACT_TYPE_ varchar(255), + CREATE_TIME_ datetime(3) not null, + END_TIME_ datetime(3), + DURATION_ bigint, + STATE_ integer, + REQUIRED_ boolean, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create index ACT_IDX_HI_CAS_I_CLOSE on ACT_HI_CASEINST(CLOSE_TIME_); +create index ACT_IDX_HI_CAS_I_BUSKEY on ACT_HI_CASEINST(BUSINESS_KEY_); +create index ACT_IDX_HI_CAS_I_TENANT_ID on ACT_HI_CASEINST(TENANT_ID_); +create index ACT_IDX_HI_CAS_A_I_CREATE on ACT_HI_CASEACTINST(CREATE_TIME_); +create index ACT_IDX_HI_CAS_A_I_END on ACT_HI_CASEACTINST(END_TIME_); +create index ACT_IDX_HI_CAS_A_I_COMP on ACT_HI_CASEACTINST(CASE_ACT_ID_, END_TIME_, ID_); +create index ACT_IDX_HI_CAS_A_I_CASEINST on ACT_HI_CASEACTINST(CASE_INST_ID_, CASE_ACT_ID_); +create index ACT_IDX_HI_CAS_A_I_TENANT_ID on ACT_HI_CASEACTINST(TENANT_ID_); +-- create history decision instance table -- +create table ACT_HI_DECINST ( + ID_ varchar(64) NOT NULL, + DEC_DEF_ID_ varchar(64) NOT NULL, + DEC_DEF_KEY_ varchar(255) NOT NULL, + DEC_DEF_NAME_ varchar(255), + PROC_DEF_KEY_ varchar(255), + PROC_DEF_ID_ varchar(64), + PROC_INST_ID_ varchar(64), + CASE_DEF_KEY_ varchar(255), + CASE_DEF_ID_ varchar(64), + CASE_INST_ID_ varchar(64), + ACT_INST_ID_ varchar(64), + ACT_ID_ varchar(255), + EVAL_TIME_ datetime(3) not null, + COLLECT_VALUE_ double, + USER_ID_ varchar(255), + ROOT_DEC_INST_ID_ varchar(64), + DEC_REQ_ID_ varchar(64), + DEC_REQ_KEY_ varchar(255), + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +-- create history decision input table -- +create table ACT_HI_DEC_IN ( + ID_ varchar(64) NOT NULL, + DEC_INST_ID_ varchar(64) NOT NULL, + CLAUSE_ID_ varchar(64), + CLAUSE_NAME_ varchar(255), + VAR_TYPE_ varchar(100), + BYTEARRAY_ID_ varchar(64), + DOUBLE_ double, + LONG_ bigint, + TEXT_ LONGBLOB, + TEXT2_ LONGBLOB, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +-- create history decision output table -- +create table ACT_HI_DEC_OUT ( + ID_ varchar(64) NOT NULL, + DEC_INST_ID_ varchar(64) NOT NULL, + CLAUSE_ID_ varchar(64), + CLAUSE_NAME_ varchar(255), + RULE_ID_ varchar(64), + RULE_ORDER_ integer, + VAR_NAME_ varchar(255), + VAR_TYPE_ varchar(100), + BYTEARRAY_ID_ varchar(64), + DOUBLE_ double, + LONG_ bigint, + TEXT_ LONGBLOB, + TEXT2_ LONGBLOB, + TENANT_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + + +create index ACT_IDX_HI_DEC_INST_ID on ACT_HI_DECINST(DEC_DEF_ID_); +create index ACT_IDX_HI_DEC_INST_KEY on ACT_HI_DECINST(DEC_DEF_KEY_); +create index ACT_IDX_HI_DEC_INST_PI on ACT_HI_DECINST(PROC_INST_ID_); +create index ACT_IDX_HI_DEC_INST_CI on ACT_HI_DECINST(CASE_INST_ID_); +create index ACT_IDX_HI_DEC_INST_ACT on ACT_HI_DECINST(ACT_ID_); +create index ACT_IDX_HI_DEC_INST_ACT_INST on ACT_HI_DECINST(ACT_INST_ID_); +create index ACT_IDX_HI_DEC_INST_TIME on ACT_HI_DECINST(EVAL_TIME_); +create index ACT_IDX_HI_DEC_INST_TENANT_ID on ACT_HI_DECINST(TENANT_ID_); +create index ACT_IDX_HI_DEC_INST_ROOT_ID on ACT_HI_DECINST(ROOT_DEC_INST_ID_); +create index ACT_IDX_HI_DEC_INST_REQ_ID on ACT_HI_DECINST(DEC_REQ_ID_); +create index ACT_IDX_HI_DEC_INST_REQ_KEY on ACT_HI_DECINST(DEC_REQ_KEY_); + + +create index ACT_IDX_HI_DEC_IN_INST on ACT_HI_DEC_IN(DEC_INST_ID_); +create index ACT_IDX_HI_DEC_IN_CLAUSE on ACT_HI_DEC_IN(DEC_INST_ID_, CLAUSE_ID_); + +create index ACT_IDX_HI_DEC_OUT_INST on ACT_HI_DEC_OUT(DEC_INST_ID_); +create index ACT_IDX_HI_DEC_OUT_RULE on ACT_HI_DEC_OUT(RULE_ORDER_, CLAUSE_ID_); + +-- mariadb_identity_7.8.0-ee + +create table ACT_ID_GROUP ( + ID_ varchar(64), + REV_ integer, + NAME_ varchar(255), + TYPE_ varchar(255), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_ID_MEMBERSHIP ( + USER_ID_ varchar(64), + GROUP_ID_ varchar(64), + primary key (USER_ID_, GROUP_ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_ID_USER ( + ID_ varchar(64), + REV_ integer, + FIRST_ varchar(255), + LAST_ varchar(255), + EMAIL_ varchar(255), + PWD_ varchar(255), + SALT_ varchar(255), + PICTURE_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_ID_INFO ( + ID_ varchar(64), + REV_ integer, + USER_ID_ varchar(64), + TYPE_ varchar(64), + KEY_ varchar(255), + VALUE_ varchar(255), + PASSWORD_ LONGBLOB, + PARENT_ID_ varchar(255), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_ID_TENANT ( + ID_ varchar(64), + REV_ integer, + NAME_ varchar(255), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +create table ACT_ID_TENANT_MEMBER ( + ID_ varchar(64) not null, + TENANT_ID_ varchar(64) not null, + USER_ID_ varchar(64), + GROUP_ID_ varchar(64), + primary key (ID_) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +alter table ACT_ID_MEMBERSHIP + add constraint ACT_FK_MEMB_GROUP + foreign key (GROUP_ID_) + references ACT_ID_GROUP (ID_); + +alter table ACT_ID_MEMBERSHIP + add constraint ACT_FK_MEMB_USER + foreign key (USER_ID_) + references ACT_ID_USER (ID_); + +alter table ACT_ID_TENANT_MEMBER + add constraint ACT_UNIQ_TENANT_MEMB_USER + unique (TENANT_ID_, USER_ID_); + +alter table ACT_ID_TENANT_MEMBER + add constraint ACT_UNIQ_TENANT_MEMB_GROUP + unique (TENANT_ID_, GROUP_ID_); + +alter table ACT_ID_TENANT_MEMBER + add constraint ACT_FK_TENANT_MEMB + foreign key (TENANT_ID_) + references ACT_ID_TENANT (ID_); + +alter table ACT_ID_TENANT_MEMBER + add constraint ACT_FK_TENANT_MEMB_USER + foreign key (USER_ID_) + references ACT_ID_USER (ID_); + +alter table ACT_ID_TENANT_MEMBER + add constraint ACT_FK_TENANT_MEMB_GROUP + foreign key (GROUP_ID_) + references ACT_ID_GROUP (ID_); + +-- additional changes for MSO + +-- Table for the urn mapping entries +CREATE TABLE `mso_urn_mapping` ( +`NAME_` VARCHAR(64) NOT NULL COLLATE 'utf8_bin', +`VALUE_` VARCHAR(300) NULL DEFAULT NULL COLLATE 'utf8_bin', +`REV_` INT(11) NULL DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +-- setting history level from full (id=3) to audit (id=2) +update act_ge_property set value_='2' where name_='historyLevel'; \ No newline at end of file diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8_patch_7.8.0_to_7.8.2.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8_patch_7.8.0_to_7.8.2.sql new file mode 100644 index 0000000000..783695750b --- /dev/null +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8_patch_7.8.0_to_7.8.2.sql @@ -0,0 +1,7 @@ +-- 7.8.0 to 7.8.2 upgrade + +USE `camundabpmn`; +-- https://app.camunda.com/jira/browse/CAM-8485 +drop index ACT_IDX_HI_ACT_INST_STATS on ACT_HI_ACTINST; +create index ACT_IDX_HI_ACT_INST_STATS on ACT_HI_ACTINST(PROC_DEF_ID_, PROC_INST_ID_, ACT_ID_, END_TIME_, ACT_INST_STATE_); +create index ACT_IDX_HI_PRO_INST_PROC_TIME on ACT_HI_PROCINST(START_TIME_, END_TIME_); diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mysql_create_camunda_admin.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mysql_create_camunda_admin.sql index 3beeaf1140..4472fbefcf 100644 --- a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mysql_create_camunda_admin.sql +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mysql_create_camunda_admin.sql @@ -1,9 +1,9 @@ -USE camundabpmn; - -INSERT INTO `act_id_user` VALUES ('admin',1,'admin','user','camundaadmin@openecomp.org','{SHA}Y7MVubSDgzJeaulJRLN2dFyNCyc=',NULL); - -INSERT INTO `act_id_group` VALUES ('camunda-admin',1,'camunda BPM Administrators','SYSTEM'); - -INSERT INTO `act_id_membership` VALUES ('admin','camunda-admin'); - -INSERT INTO `act_ru_authorization` VALUES ('4ca68335-b7c5-11e6-b411-0242ac110003',1,1,NULL,'admin',1,'admin',2147483647),('4ca91b46-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,2,'camunda-admin',2),('4cab3e27-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,0,'*',2147483647),('4cadd638-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,1,'*',2147483647),('4caf0eb9-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,2,'*',2147483647),('4caff91a-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,3,'*',2147483647),('4cb10a8b-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,4,'*',2147483647),('4cb2430c-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,5,'*',2147483647),('4cb32d6d-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,6,'*',2147483647),('4cb43ede-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,7,'*',2147483647),('4cb5293f-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,8,'*',2147483647),('4cb5ec90-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,9,'*',2147483647); +USE camundabpmn; + +INSERT INTO `act_id_user` VALUES ('admin',1,'admin','user','camundaadmin@openecomp.org','{SHA}Y7MVubSDgzJeaulJRLN2dFyNCyc=',NULL); + +INSERT INTO `act_id_group` VALUES ('camunda-admin',1,'camunda BPM Administrators','SYSTEM'); + +INSERT INTO `act_id_membership` VALUES ('admin','camunda-admin'); + +INSERT INTO `act_ru_authorization` VALUES ('4ca68335-b7c5-11e6-b411-0242ac110003',1,1,NULL,'admin',1,'admin',2147483647),('4ca91b46-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,2,'camunda-admin',2),('4cab3e27-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,0,'*',2147483647),('4cadd638-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,1,'*',2147483647),('4caf0eb9-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,2,'*',2147483647),('4caff91a-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,3,'*',2147483647),('4cb10a8b-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,4,'*',2147483647),('4cb2430c-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,5,'*',2147483647),('4cb32d6d-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,6,'*',2147483647),('4cb43ede-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,7,'*',2147483647),('4cb5293f-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,8,'*',2147483647),('4cb5ec90-b7c5-11e6-b411-0242ac110003',1,1,'camunda-admin',NULL,9,'*',2147483647); diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_add_constraints.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_add_constraints.sql new file mode 100644 index 0000000000..a5e9834de2 --- /dev/null +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_add_constraints.sql @@ -0,0 +1,45 @@ +USE `mso_catalog`; + +ALTER TABLE `mso_catalog`.`service_to_allotted_resources` + ADD INDEX `fk_service_to_allotted_resources__service_model_uuid_idx` (`SERVICE_MODEL_UUID` ASC), + ADD INDEX `fk_service_to_allotted_resources__allotted_resource_customiz_idx` (`AR_MODEL_CUSTOMIZATION_UUID` ASC), + ADD CONSTRAINT `fk_service_to_allotted_resources__service__service_name_ver_id` + FOREIGN KEY (`SERVICE_MODEL_UUID`) + REFERENCES `mso_catalog`.`service` (`SERVICE_NAME_VERSION_ID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + ADD CONSTRAINT `fk_service_to_allotted_resources__allotted_resource_customizat1` + FOREIGN KEY (`AR_MODEL_CUSTOMIZATION_UUID`) + REFERENCES `mso_catalog`.`allotted_resource_customization` (`MODEL_CUSTOMIZATION_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + + +ALTER TABLE `mso_catalog`.`service_to_networks` + ADD INDEX `fk_service_to_networks__service_model_uuid_idx` (`SERVICE_MODEL_UUID` ASC), + ADD INDEX `fk_service_to_networks__network_resource_customization1_idx` (`NETWORK_MODEL_CUSTOMIZATION_UUID` ASC), + ADD CONSTRAINT `fk_service_to_networks__service__service_name_version_id` + FOREIGN KEY (`SERVICE_MODEL_UUID`) + REFERENCES `mso_catalog`.`service` (`SERVICE_NAME_VERSION_ID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + ADD CONSTRAINT `fk_service_to_networks__network_resource_customization1` + FOREIGN KEY (`NETWORK_MODEL_CUSTOMIZATION_UUID`) + REFERENCES `mso_catalog`.`network_resource_customization` (`MODEL_CUSTOMIZATION_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + + +ALTER TABLE `mso_catalog`.`vf_module` + ADD INDEX `UK_model_customization_uuid__asdc_service_model_version` (`MODEL_CUSTOMIZATION_UUID` ASC, `ASDC_SERVICE_MODEL_VERSION` ASC); + +ALTER TABLE `mso_catalog`.`vnf_resource` + ADD UNIQUE INDEX `UK_model_customization_uuid__asdc_service_model_version` (`MODEL_CUSTOMIZATION_UUID` ASC, `ASDC_SERVICE_MODEL_VERSION` ASC); + +ALTER TABLE `mso_catalog`.`network_resource_customization` + ADD INDEX `fk_network_resource_customization__network_resource_id_idx` (`NETWORK_RESOURCE_ID` ASC), + ADD CONSTRAINT `fk_network_resource_customization__network_resource__id` + FOREIGN KEY (`NETWORK_RESOURCE_ID`) + REFERENCES `mso_catalog`.`network_resource` (`id`) + ON DELETE CASCADE + ON UPDATE CASCADE; \ No newline at end of file diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_timestamp_mso_db.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_timestamp_mso_db.sql new file mode 100644 index 0000000000..bc88adcc70 --- /dev/null +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_timestamp_mso_db.sql @@ -0,0 +1,19 @@ +USE `mso_catalog`; + +ALTER TABLE HEAT_ENVIRONMENT MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE NETWORK_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE NETWORK_RESOURCE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE SERVICE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE VNF_COMPONENTS MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE VNF_COMPONENTS_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE VNF_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE VNF_RESOURCE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE HEAT_FILES MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE HEAT_TEMPLATE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE VF_MODULE MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE SERVICE_RECIPE MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE NETWORK_RESOURCE_CUSTOMIZATION MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE ALLOTTED_RESOURCE_CUSTOMIZATION MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE SERVICE_TO_ALLOTTED_RESOURCES MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; +ALTER TABLE SERVICE_TO_NETWORKS MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; + diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/site_status_updated_timestamp.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/site_status_updated_timestamp.sql new file mode 100644 index 0000000000..3b2de4c0b7 --- /dev/null +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/site_status_updated_timestamp.sql @@ -0,0 +1 @@ +ALTER TABLE SITE_STATUS MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_add_constraints.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_add_constraints.sql deleted file mode 100644 index a5e9834de2..0000000000 --- a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_add_constraints.sql +++ /dev/null @@ -1,45 +0,0 @@ -USE `mso_catalog`; - -ALTER TABLE `mso_catalog`.`service_to_allotted_resources` - ADD INDEX `fk_service_to_allotted_resources__service_model_uuid_idx` (`SERVICE_MODEL_UUID` ASC), - ADD INDEX `fk_service_to_allotted_resources__allotted_resource_customiz_idx` (`AR_MODEL_CUSTOMIZATION_UUID` ASC), - ADD CONSTRAINT `fk_service_to_allotted_resources__service__service_name_ver_id` - FOREIGN KEY (`SERVICE_MODEL_UUID`) - REFERENCES `mso_catalog`.`service` (`SERVICE_NAME_VERSION_ID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - ADD CONSTRAINT `fk_service_to_allotted_resources__allotted_resource_customizat1` - FOREIGN KEY (`AR_MODEL_CUSTOMIZATION_UUID`) - REFERENCES `mso_catalog`.`allotted_resource_customization` (`MODEL_CUSTOMIZATION_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - - -ALTER TABLE `mso_catalog`.`service_to_networks` - ADD INDEX `fk_service_to_networks__service_model_uuid_idx` (`SERVICE_MODEL_UUID` ASC), - ADD INDEX `fk_service_to_networks__network_resource_customization1_idx` (`NETWORK_MODEL_CUSTOMIZATION_UUID` ASC), - ADD CONSTRAINT `fk_service_to_networks__service__service_name_version_id` - FOREIGN KEY (`SERVICE_MODEL_UUID`) - REFERENCES `mso_catalog`.`service` (`SERVICE_NAME_VERSION_ID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - ADD CONSTRAINT `fk_service_to_networks__network_resource_customization1` - FOREIGN KEY (`NETWORK_MODEL_CUSTOMIZATION_UUID`) - REFERENCES `mso_catalog`.`network_resource_customization` (`MODEL_CUSTOMIZATION_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - - -ALTER TABLE `mso_catalog`.`vf_module` - ADD INDEX `UK_model_customization_uuid__asdc_service_model_version` (`MODEL_CUSTOMIZATION_UUID` ASC, `ASDC_SERVICE_MODEL_VERSION` ASC); - -ALTER TABLE `mso_catalog`.`vnf_resource` - ADD UNIQUE INDEX `UK_model_customization_uuid__asdc_service_model_version` (`MODEL_CUSTOMIZATION_UUID` ASC, `ASDC_SERVICE_MODEL_VERSION` ASC); - -ALTER TABLE `mso_catalog`.`network_resource_customization` - ADD INDEX `fk_network_resource_customization__network_resource_id_idx` (`NETWORK_RESOURCE_ID` ASC), - ADD CONSTRAINT `fk_network_resource_customization__network_resource__id` - FOREIGN KEY (`NETWORK_RESOURCE_ID`) - REFERENCES `mso_catalog`.`network_resource` (`id`) - ON DELETE CASCADE - ON UPDATE CASCADE; \ No newline at end of file diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_timestamp_mso_db.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_timestamp_mso_db.sql deleted file mode 100644 index bc88adcc70..0000000000 --- a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_timestamp_mso_db.sql +++ /dev/null @@ -1,19 +0,0 @@ -USE `mso_catalog`; - -ALTER TABLE HEAT_ENVIRONMENT MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE NETWORK_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE NETWORK_RESOURCE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE SERVICE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE VNF_COMPONENTS MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE VNF_COMPONENTS_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE VNF_RECIPE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE VNF_RESOURCE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE HEAT_FILES MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE HEAT_TEMPLATE MODIFY COLUMN CREATION_TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE VF_MODULE MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE SERVICE_RECIPE MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE NETWORK_RESOURCE_CUSTOMIZATION MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE ALLOTTED_RESOURCE_CUSTOMIZATION MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE SERVICE_TO_ALLOTTED_RESOURCES MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; -ALTER TABLE SERVICE_TO_NETWORKS MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP NOT NULL; - diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V2.10__UpdateNorthboundRequestToUseInstance.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V2.10__UpdateNorthboundRequestToUseInstance.sql new file mode 100644 index 0000000000..5ee4deb26e --- /dev/null +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V2.10__UpdateNorthboundRequestToUseInstance.sql @@ -0,0 +1,24 @@ +USE catalogdb; + + + +UPDATE northbound_request_ref_lookup SET ACTION = 'createInstance' WHERE MACRO_ACTION = 'Service-Create'; +UPDATE northbound_request_ref_lookup SET ACTION = 'deleteInstance' WHERE MACRO_ACTION = 'Service-Delete'; +UPDATE northbound_request_ref_lookup SET ACTION = 'assignInstance' WHERE MACRO_ACTION = 'Service-Macro-Assign'; +UPDATE northbound_request_ref_lookup SET ACTION = 'activateInstance' WHERE MACRO_ACTION = 'Service-Macro-Activate'; +UPDATE northbound_request_ref_lookup SET ACTION = 'unassignInstance' WHERE MACRO_ACTION = 'Service-Macro-Unassign'; +UPDATE northbound_request_ref_lookup SET ACTION = 'createInstance' WHERE MACRO_ACTION = 'Service-Macro-Create'; +UPDATE northbound_request_ref_lookup SET ACTION = 'deleteInstance' WHERE MACRO_ACTION = 'Service-Macro-Delete'; +UPDATE northbound_request_ref_lookup SET ACTION = 'createInstance' WHERE MACRO_ACTION = 'Network-Create'; +UPDATE northbound_request_ref_lookup SET ACTION = 'deleteInstance' WHERE MACRO_ACTION = 'Network-Delete'; +UPDATE northbound_request_ref_lookup SET ACTION = 'replaceInstance' WHERE MACRO_ACTION = 'VNF-Macro-Replace'; +UPDATE northbound_request_ref_lookup SET ACTION = 'createInstance' WHERE MACRO_ACTION = 'VNF-Create'; +UPDATE northbound_request_ref_lookup SET ACTION = 'deleteInstance' WHERE MACRO_ACTION = 'VNF-Delete'; +UPDATE northbound_request_ref_lookup SET ACTION = 'createInstance' WHERE MACRO_ACTION = 'VolumeGroup-Create'; +UPDATE northbound_request_ref_lookup SET ACTION = 'deleteInstance' WHERE MACRO_ACTION = 'VolumeGroup-Delete'; +UPDATE northbound_request_ref_lookup SET ACTION = 'createInstance' WHERE MACRO_ACTION = 'VFModule-Create'; +UPDATE northbound_request_ref_lookup SET ACTION = 'deleteInstance' WHERE MACRO_ACTION = 'VFModule-Delete'; +UPDATE northbound_request_ref_lookup SET ACTION = 'createInstance' WHERE MACRO_ACTION = 'NetworkCollection-Macro-Create'; +UPDATE northbound_request_ref_lookup SET ACTION = 'deleteInstance' WHERE MACRO_ACTION = 'NetworkCollection-Macro-Delete'; +UPDATE northbound_request_ref_lookup SET ACTION = 'deleteInstance' WHERE MACRO_ACTION = 'AVPNBonding-Macro-Delete'; +UPDATE northbound_request_ref_lookup SET ACTION = 'createInstance' WHERE MACRO_ACTION = 'AVPNBonding-Macro-Create'; \ No newline at end of file diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V3.0__UpdateOrchFlowTableWithATTFlows.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V3.0__UpdateOrchFlowTableWithATTFlows.sql new file mode 100644 index 0000000000..ec9848563a --- /dev/null +++ b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V3.0__UpdateOrchFlowTableWithATTFlows.sql @@ -0,0 +1,16 @@ +USE catalogdb; + +UPDATE orchestration_flow_reference SET FLOW_NAME = 'AssignServiceInstanceATTBB' WHERE FLOW_NAME = 'AssignServiceInstanceBB'; +UPDATE orchestration_flow_reference SET FLOW_NAME = 'ActivateServiceInstanceATTBB' WHERE FLOW_NAME = 'ActivateServiceInstanceBB'; +UPDATE orchestration_flow_reference SET FLOW_NAME = 'DeactivateServiceInstanceATTBB' WHERE FLOW_NAME = 'DeactivateServiceInstanceBB'; +UPDATE orchestration_flow_reference SET FLOW_NAME = 'UnassignServiceInstanceATTBB' WHERE FLOW_NAME = 'UnassignServiceInstanceBB'; + +UPDATE rainy_day_handler_macro SET FLOW_NAME = 'AssignServiceInstanceATTBB' WHERE FLOW_NAME = 'AssignServiceInstanceBB'; +UPDATE rainy_day_handler_macro SET FLOW_NAME = 'ActivateServiceInstanceATTBB' WHERE FLOW_NAME = 'ActivateServiceInstanceBB'; +UPDATE rainy_day_handler_macro SET FLOW_NAME = 'DeactivateServiceInstanceATTBB' WHERE FLOW_NAME = 'DeactivateServiceInstanceBB'; +UPDATE rainy_day_handler_macro SET FLOW_NAME = 'UnassignServiceInstanceATTBB' WHERE FLOW_NAME = 'UnassignServiceInstanceBB'; + +UPDATE building_block_detail SET BUILDING_BLOCK_NAME = 'AssignServiceInstanceATTBB' WHERE BUILDING_BLOCK_NAME = 'AssignServiceInstanceBB'; +UPDATE building_block_detail SET BUILDING_BLOCK_NAME = 'ActivateServiceInstanceATTBB' WHERE BUILDING_BLOCK_NAME = 'ActivateServiceInstanceBB'; +UPDATE building_block_detail SET BUILDING_BLOCK_NAME = 'DeactivateServiceInstanceATTBB' WHERE BUILDING_BLOCK_NAME = 'DeactivateServiceInstanceBB'; +UPDATE building_block_detail SET BUILDING_BLOCK_NAME = 'UnassignServiceInstanceATTBB' WHERE BUILDING_BLOCK_NAME = 'UnassignServiceInstanceBB'; \ No newline at end of file diff --git a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/site_status_updated_timestamp.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/site_status_updated_timestamp.sql deleted file mode 100644 index 3b2de4c0b7..0000000000 --- a/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/site_status_updated_timestamp.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE SITE_STATUS MODIFY COLUMN CREATION_TIMESTAMP datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; diff --git a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql index fe5ec22520..43a87916ce 100644 --- a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql +++ b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql @@ -1,665 +1,665 @@ --- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 -- -use camundabpmn; - -/* uncomment below statement and run for your db, e.g. : use camundabpmn; - */ --- use ; - -/* -Drop a archive tables -*/ -/*-- TMP_ARCHIVING_PROCINST */ -DROP TABLE IF EXISTS TMP_ARCHIVING_PROCINST; - -/*-- TMP_ARCHIVING_BYTEARRAY */ -DROP TABLE IF EXISTS TMP_ARCHIVING_BYTEARRAY; - -/*-- TMP LOG TABLE */ -DROP TABLE IF EXISTS TMPLOGTABLE; - -/* -- Camunda Hi Tables --*/ -DROP TABLE IF EXISTS Camunda_Hi_Tables; - -/* drop own extentions columns: -alter table ARCHIVE_ACT_HI_PROCINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -alter table ARCHIVE_ACT_HI_ACTINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -alter table ARCHIVE_ACT_HI_TASKINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -alter table ARCHIVE_ACT_HI_VARINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -alter table ARCHIVE_ACT_HI_DETAIL DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -alter table ARCHIVE_ACT_HI_COMMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -alter table ARCHIVE_ACT_HI_ATTACHMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -alter table ARCHIVE_ACT_HI_OP_LOG DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -alter table ARCHIVE_ACT_HI_INCIDENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); -*/ - -/*--#1 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_PROCINST; -/*--#2 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ACTINST; -/*--#3 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_TASKINST; -/*--#4 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_VARINST; -/*--#5 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_DETAIL; -/*--#6 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_COMMENT; -/*--#7 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ATTACHMENT; -/*--#8 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_OP_LOG; -/*--#9 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_HI_INCIDENT; -/*--#10 */ -DROP TABLE IF EXISTS ARCHIVE_ACT_GE_BYTEARRAY; - -/* drop PL SQL procedures: */ -DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY; -DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY; - -/*-- Sequence */ --- as sequence drop doesn't work automatically in MariaDB, use this procedure to drop sequence - DROP PROCEDURE IF EXISTS DropSequence; - -/*-- To Drop the MariaDB specific user defined procedures and functions */ -DROP FUNCTION IF EXISTS NextVal; -DROP PROCEDURE IF EXISTS CreateSequence; -DROP PROCEDURE IF EXISTS DropSequence; -DROP TABLE IF EXISTS _sequences; - -/* -- If only the sequence: STAT_EXECUTION_SEQ needs to be removed, uncomment and use below statements --*/ -/* - DELIMITER // - CREATE PROCEDURE DropSequence (vname VARCHAR(30)) - BEGIN - -- Drop the sequence - DELETE FROM _sequences WHERE name = vname; - END - // - DELIMITER ; - --- use the above procedure to drop sequence -CALL DropSequence('STAT_EXECUTION_SEQ'); -*/ - - - - - -/* - 1. Create starts - Add some Camunda Indexes to history schema part (for Archiving) -*/ -create INDEX IF NOT EXISTS IDX_ACT_HI_TASKINST_PIID ON ACT_HI_TASKINST (PROC_INST_ID_); -create INDEX IF NOT EXISTS IDX_ACT_HI_COMMENT_PIID ON ACT_HI_COMMENT (PROC_INST_ID_); -create INDEX IF NOT EXISTS IDX_ACT_HI_ATTACHMENT_PIID ON ACT_HI_ATTACHMENT (PROC_INST_ID_); -create INDEX IF NOT EXISTS IDX_ACT_HI_OP_LOG_PIID ON ACT_HI_OP_LOG (PROC_INST_ID_); -create INDEX IF NOT EXISTS IDX_ACT_HI_INCIDENT_PIID ON ACT_HI_INCIDENT (PROC_INST_ID_); -create INDEX IF NOT EXISTS IDX_ACT_HI_ACTINST_PIID ON ACT_HI_ACTINST(PROC_INST_ID_); - - -/* - 2. Create Archiving Tables in current schema -*/ - -/*-- TMP_ARCHIVING_PROCINST */ -CREATE TABLE TMP_ARCHIVING_PROCINST -( PROC_INST_ID_ varchar(64) not null, - END_TIME_ datetime(3) -); -CREATE INDEX AI_TMP_ARCH_PROCINST_PI_ID ON TMP_ARCHIVING_PROCINST(PROC_INST_ID_); - -/*-- TMP_ARCHIVING_BYTEARRAY */ -CREATE TABLE TMP_ARCHIVING_BYTEARRAY -( BYTEARRAY_ID_ varchar(64) not null, - PROC_INST_ID_ varchar(64) -); -CREATE INDEX AI_TMP_ARCH_BYTEARRAY_BAID ON TMP_ARCHIVING_BYTEARRAY(BYTEARRAY_ID_); - - -/*--#1 ARCHIVE_ACT_HI_PROCINST; */ -create TABLE ARCHIVE_ACT_HI_PROCINST -AS ( select * from ACT_HI_PROCINST where 1=0); - -create index AI_HI_PROCINST_END_TIME on ARCHIVE_ACT_HI_PROCINST(END_TIME_); -ALTER TABLE ARCHIVE_ACT_HI_PROCINST ADD CONSTRAINT ARCHIVE_ACT_HI_PROCINST_UQ UNIQUE ( PROC_INST_ID_); - -/*--#2 ARCHIVE_ACT_HI_ACTINST; */ -create TABLE ARCHIVE_ACT_HI_ACTINST -AS ( select * from ACT_HI_ACTINST where 1=0); - -create index AI_HI_ACTINST_PROC_INST_ID on ARCHIVE_ACT_HI_ACTINST(PROC_INST_ID_); -create index AI_HI_ACTINST_END_TIME on ARCHIVE_ACT_HI_ACTINST(END_TIME_); - -/*--#3 ARCHIVE_ACT_HI_TASKINST; */ -create TABLE ARCHIVE_ACT_HI_TASKINST -AS ( select * from ACT_HI_TASKINST where 1=0); - -create index AI_HI_TASKINST_PROC_INST_ID on ARCHIVE_ACT_HI_TASKINST(PROC_INST_ID_); -create index AI_HI_TASKINST_END_TIME on ARCHIVE_ACT_HI_TASKINST(END_TIME_); - -/*--#4 ARCHIVE_ACT_HI_VARINST; */ -create TABLE ARCHIVE_ACT_HI_VARINST -AS ( select * from ACT_HI_VARINST where 1=0); - -create index AI_HI_VARINST_PROC_INST_ID on ARCHIVE_ACT_HI_VARINST(PROC_INST_ID_); - -/*--#5 ARCHIVE_ACT_HI_DETAIL; */ -create TABLE ARCHIVE_ACT_HI_DETAIL -AS ( select * from ACT_HI_DETAIL where 1=0); - -create index AI_HI_DETAIL_PROC_INST_ID on ARCHIVE_ACT_HI_DETAIL(PROC_INST_ID_); -create index AI_HI_DETAIL_TIME on ARCHIVE_ACT_HI_DETAIL(TIME_); - -/*--#6 ARCHIVE_ACT_HI_COMMENT; */ -create TABLE ARCHIVE_ACT_HI_COMMENT -AS ( select * from ACT_HI_COMMENT where 1=0); - -create index AI_HI_COMMENT_PROC_INST_ID on ARCHIVE_ACT_HI_COMMENT(PROC_INST_ID_); -create index AI_HI_COMMENT_TIME on ARCHIVE_ACT_HI_COMMENT(TIME_); - -/*--#7 ARCHIVE_ACT_HI_ATTACHMENT; */ -create TABLE ARCHIVE_ACT_HI_ATTACHMENT -AS ( select * from ACT_HI_ATTACHMENT where 1=0); - -create index AI_HI_ATTACHMENT_PROC_INST_ID on ARCHIVE_ACT_HI_ATTACHMENT(PROC_INST_ID_); - -/*--#8 ARCHIVE_ACT_HI_OP_LOG; */ -create TABLE ARCHIVE_ACT_HI_OP_LOG -AS ( select * from ACT_HI_OP_LOG where 1=0); - -create index AI_HI_OP_LOG_PROC_INST_ID on ARCHIVE_ACT_HI_OP_LOG(PROC_INST_ID_); -create index AI_HI_OP_LOG_TIMESTAMP on ARCHIVE_ACT_HI_OP_LOG(TIMESTAMP_); - -/*--#9 ARCHIVE_ACT_HI_INCIDENT; */ -create TABLE ARCHIVE_ACT_HI_INCIDENT -AS ( select * from ACT_HI_INCIDENT where 1=0); - -create index AI_HI_INCIDENT_PROC_INST_ID on ARCHIVE_ACT_HI_INCIDENT(PROC_INST_ID_); - -/*--#10 ARCHIVE_ACT_GE_BYTEARRAY; */ -create TABLE ARCHIVE_ACT_GE_BYTEARRAY -AS ( select * from ACT_GE_BYTEARRAY where 1=0); - -create index AI_GE_BYTEARRAY_ID_ on ARCHIVE_ACT_GE_BYTEARRAY(ID_); - -/* ----------------------------------------------------------------------------- -Extend a ARCHIVE: Table by two attributes: STAT_EXECUTION_ID, STAT_EXECUTION_TS -*/ - -/* ---TEMPLATE: -alter table ARCHIVE_%TableName% - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_%TableName%_EXE_ID ON ARCHIVE_%TableName%(STAT_EXECUTION_ID); -*/ - - -/*--#1 ACT_HI_PROCINST */ -alter table ARCHIVE_ACT_HI_PROCINST - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_PROCINST_EXE_ID ON ARCHIVE_ACT_HI_PROCINST(STAT_EXECUTION_ID); - -/*--#2 ACT_HI_ACTINST */ -alter table ARCHIVE_ACT_HI_ACTINST - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_ACTINST_EXE_ID ON ARCHIVE_ACT_HI_ACTINST(STAT_EXECUTION_ID); - -/*--#3 ACT_HI_TASKINST */ -alter table ARCHIVE_ACT_HI_TASKINST - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_TASKINST_EXE_ID ON ARCHIVE_ACT_HI_TASKINST(STAT_EXECUTION_ID); - -/*--#4 ACT_HI_VARINST */ -alter table ARCHIVE_ACT_HI_VARINST - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_VARINST_EXE_ID ON ARCHIVE_ACT_HI_VARINST(STAT_EXECUTION_ID); - -/*--#5 ACT_HI_DETAIL */ -alter table ARCHIVE_ACT_HI_DETAIL - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_DETAIL_EXE_ID ON ARCHIVE_ACT_HI_DETAIL(STAT_EXECUTION_ID); - -/*--#6 ACT_HI_COMMENT */ -alter table ARCHIVE_ACT_HI_COMMENT - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_COMMENT_EXE_ID ON ARCHIVE_ACT_HI_COMMENT(STAT_EXECUTION_ID); - -/*--#7 ACT_HI_ATTACHMENT */ -alter table ARCHIVE_ACT_HI_ATTACHMENT - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_ATTACHMENT_EXE_ID ON ARCHIVE_ACT_HI_ATTACHMENT(STAT_EXECUTION_ID); - -/*--#8 ACT_HI_OP_LOG */ -alter table ARCHIVE_ACT_HI_OP_LOG - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_OP_LOG_EXE_ID ON ARCHIVE_ACT_HI_OP_LOG(STAT_EXECUTION_ID); - -/*--#9 ACT_HI_INCIDENT */ -alter table ARCHIVE_ACT_HI_INCIDENT - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_HI_INCIDENT_EXE_ID ON ARCHIVE_ACT_HI_INCIDENT(STAT_EXECUTION_ID); - -/*--#10 ACT_GE_BYTEARRAY */ -alter table ARCHIVE_ACT_GE_BYTEARRAY - add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); -CREATE INDEX AI_ACT_GE_BYTEARRAY_EXE_ID ON ARCHIVE_ACT_GE_BYTEARRAY(STAT_EXECUTION_ID); - - -/* -- Next Val as a user defined function needed only in MariaDB--*/ -DROP FUNCTION IF EXISTS NextVal; - DELIMITER // - CREATE FUNCTION NextVal (vname VARCHAR(30)) - RETURNS INT - BEGIN - -- Retrieve and update in single statement - UPDATE _sequences - SET next = next + 1 - WHERE name = vname; - - RETURN (SELECT next FROM _sequences LIMIT 1); - END - // - DELIMITER ; - -/* -- History tables for use in archive procedure, there is no array type in MariaDB --*/ - -Create Table Camunda_Hi_Tables (id_ INT NOT NULL, -TableName_ varchar(80) NOT NULL); - -Insert Into Camunda_Hi_Tables Values (1,'ACT_HI_PROCINST'); -Insert Into Camunda_Hi_Tables Values (2,'ACT_HI_ACTINST'); -Insert Into Camunda_Hi_Tables Values (3,'ACT_HI_TASKINST'); -Insert Into Camunda_Hi_Tables Values (4,'ACT_HI_VARINST'); -Insert Into Camunda_Hi_Tables Values (5,'ACT_HI_DETAIL'); -Insert Into Camunda_Hi_Tables Values (6,'ACT_HI_COMMENT'); -Insert Into Camunda_Hi_Tables Values (7,'ACT_HI_ATTACHMENT'); -Insert Into Camunda_Hi_Tables Values (8,'ACT_HI_OP_LOG'); -Insert Into Camunda_Hi_Tables Values (9,'ACT_HI_INCIDENT'); - -/*-- log table --*/ -CREATE TABLE TMPLOGTABLE (LogMessage Varchar(700)); - - -/* -- Below user defined functions and procedures needed only in MariaDB, they are in-built in Oracle --*/ -/*-- Create a sequence SP */ -DROP PROCEDURE IF EXISTS CreateSequence; - DELIMITER // - CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT) - BEGIN - -- Create a table to store sequences - CREATE TABLE IF NOT EXISTS _sequences - ( - name VARCHAR(70) NOT NULL UNIQUE, - next INT NOT NULL, - inc INT NOT NULL - ); - - -- Add the new sequence - INSERT INTO _sequences VALUES (name, start, inc); - END - // - DELIMITER ; - -/*-------------------------------------------------------------------------------------------------- - Add Meta to Archive - -------------------------------------------------------------------------------------------------- */ - -/* Create STAT_EXECUTION_SEQ: each Archive Entry has a same Execution ID during one Archiving Run */ -CALL CreateSequence('STAT_EXECUTION_SEQ', 1, 1); - - - -/* -ARCHIVE_CAMUNDA_HISTORY-Default Store Procedure starts -Camunda Version: 7.5.4-ee; MariaDB tested -Date: 11.30.2016 -Balaji Mudipalli, AJSC Camunda Team - -DOC.: --------------------------------------------------------------------------------------- -Create ARCHIVE_CAMUNDA_HISTORY StoreProcedure -function for archiving of history camunda tables. -*/ - -/* uncomment below statement and run for your db, e.g. : use camundabpmn; - */ - -DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY; - -DELIMITER // - -CREATE PROCEDURE ARCHIVE_CAMUNDA_HISTORY(IN IN_periodInDays INT, IN IN_maxProcessInstances INT) -MODIFIES SQL DATA - -BEGIN - DECLARE P_hiTableCount INT; - DECLARE P_executionId BIGINT; - DECLARE P_piProcessed DOUBLE; - DECLARE P_baProcessed DOUBLE; - DECLARE P_startDate DATE; - DECLARE P_executionDuration double; - - DECLARE not_found INT DEFAULT 0; - DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; - - /* START TRANSACTION */ - set P_startDate = sysdate(); - set P_executionId = NextVal('STAT_EXECUTION_SEQ'); - - INSERT INTO TMPLOGTABLE SELECT CONCAT('P_executionId value is ',P_executionId); - - DELETE FROM TMP_ARCHIVING_PROCINST; - DELETE FROM TMP_ARCHIVING_BYTEARRAY; - -- temp table -- - DELETE FROM TMPLOGTABLE; - - - /* 1. Set Default Value for Max Pi's */ - IF IN_maxProcessInstances = 0 THEN SET IN_maxProcessInstances = 1000; END IF; - IF IN_maxProcessInstances > 1000 THEN SET IN_maxProcessInstances = 1000; END IF; - - INSERT INTO TMPLOGTABLE SELECT CONCAT('IN_maxProcessInstances value is: ',IN_maxProcessInstances); - - /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */ - IF IN_maxProcessInstances = 0 THEN /* all */ - INSERT INTO TMP_ARCHIVING_PROCINST - SELECT hi.PROC_INST_ID_, hi.END_TIME_ - FROM ACT_HI_PROCINST hi - WHERE hi.END_TIME_ IS NOT NULL - AND hi.END_TIME_ <= ( DATE_SUB(SYSDATE(), INTERVAL IN_periodInDays DAY)); - - ELSE /* limit: IN_maxProcessInstances */ - INSERT INTO TMP_ARCHIVING_PROCINST - (PROC_INST_ID_, END_TIME_) ( - SELECT hi2.PROC_INST_ID_, hi2.END_TIME_ - FROM ACT_HI_PROCINST hi2 - WHERE hi2.END_TIME_ IS NOT NULL - AND hi2.END_TIME_ <= ( DATE_SUB(SYSDATE(), INTERVAL IN_periodInDays DAY)) - ) LIMIT IN_maxProcessInstances; - END IF; - - /* 3. Check PI's im TEMP if any found, ready for ACHIVING */ - select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST; - - IF P_piProcessed = 0 THEN - INSERT INTO TMPLOGTABLE SELECT CONCAT('P_piProcessed value is: ',P_piProcessed); - ROLLBACK; - /* 4. Move data from history to archive (insert to archive and delete in history) */ - ELSE - SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables; - INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount); - SET @i = 1; - WHILE @i < P_hiTableCount - DO - SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i; - INSERT INTO TMPLOGTABLE SELECT CONCAT('P_tableName: ', @P_tableName); - - Set @P_archiveTableName = Concat('ARCHIVE_',IFNULL(@P_tableName, '')); - INSERT INTO TMPLOGTABLE SELECT CONCAT('@P_archiveTableName: ', @P_archiveTableName); - - SET @query1 = CONCAT('INSERT INTO ', @P_archiveTableName , - ' SELECT hi3.*, ',P_executionId, ', NOW() FROM ', @P_tableName,' hi3 - WHERE hi3.PROC_INST_ID_ in ( SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)'); - INSERT INTO TMPLOGTABLE SELECT CONCAT('@query1: ', @query1); - - PREPARE stmt1 FROM @query1; - EXECUTE stmt1; - DEALLOCATE PREPARE stmt1; - - SET @query2 = CONCAT ('DELETE ACT FROM ',@P_tableName,' ACT INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ACT.PROC_INST_ID_ = TMP.PROC_INST_ID_'); - INSERT INTO TMPLOGTABLE SELECT CONCAT('@query2: ', @query2); - - PREPARE stmt2 FROM @query2; - EXECUTE stmt2; - DEALLOCATE PREPARE stmt2; - - SET @i = @i+1; - END WHILE; - /* select bytearray_ids */ - INSERT INTO TMP_ARCHIVING_BYTEARRAY - SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar - where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) - AND archvar.BYTEARRAY_ID_ is not null; - - INSERT INTO TMP_ARCHIVING_BYTEARRAY - SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar - where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) - AND archvar.BYTEARRAY_ID_ is not null; - - /* 5. Check Bytearrays im TEMP if any found, ready for ACHIVING */ - select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY; - - /* INSERT */ - INSERT INTO ARCHIVE_ACT_GE_BYTEARRAY - SELECT hi4.*, P_executionId, NOW() FROM ACT_GE_BYTEARRAY hi4 - WHERE hi4.ID_ in ( SELECT BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); - - /* DELETE */ - DELETE FROM ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); - - /* COMMIT TRANSACTION */ - COMMIT; - INSERT INTO TMPLOGTABLE SELECT CONCAT('P_executionId is ', P_executionId); - - set P_executionDuration = DATEDIFF(sysdate(), P_startDate); - - INSERT INTO TMPLOGTABLE SELECT CONCAT('SP success and P_executionDuration is ', ifnull((round(P_executionDuration*24*60*60, 1)), ''), ' sec.'); - END IF; - - END; -// - -DELIMITER ; - - - -/* -ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure starts -Camunda Version: 7.5.4-ee; MariaDB tested -Date: 11.30.2016 -Balaji Mudipalli, AJSC Camunda Team - -DOC.: --------------------------------------------------------------------------------------- -Create ROLLB_ARCHIVE_CAMUNDA_HISTORY StoreProcedure for ROLLBACK (RESTORE) -of archived Camunda history tables. -*/ - -/* uncomment below statement and run for your db, e.g. : use camundabpmn; - */ --- use ; - - -DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY; - -DELIMITER // -CREATE PROCEDURE ROLLB_ARCHIVE_CAMUNDA_HISTORY(IN IN_executionId_from INT, - IN IN_executionId_til INT, - IN IN_maxProcessInstances INT) -MODIFIES SQL DATA - -BEGIN - DECLARE P_hiTableCount INT; - DECLARE P_piProcessed DOUBLE; - DECLARE P_baProcessed DOUBLE; - DECLARE P_query VARCHAR(600); - DECLARE P_startDate DATETIME; - DECLARE P_executionDuration DOUBLE; - DECLARE P_result NVARCHAR(400); -DECLARE not_found INT DEFAULT 0; - DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; - - - /* START TRANSACTION */ - SET P_startDate = sysdate(); - DELETE FROM TMPLOGTABLE; - - INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: START EXECUTION: ' , ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , - '; PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , - '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , - '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); - - /* 1. Truncate TMP_ARCHIVING_PROCINST */ - - DELETE FROM TMP_ARCHIVING_PROCINST; - DELETE FROM TMP_ARCHIVING_BYTEARRAY; - - /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */ - IF IN_executionId_til = -1 THEN /* IN_executionId_from only */ - SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID = ' , IFNULL(IN_executionId_from, '')); - - ELSEIF IN_executionId_til = 0 THEN /* all from IN_executionId_from */ - SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID >= ' , IFNULL(IN_executionId_from, '')); - - ELSE /* between IN_executionId_from AND IN_executionId_til */ - SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID between ', IFNULL(IN_executionId_from, '') , ' AND ' , IFNULL(IN_executionId_til, '')); - END IF; - - IF IN_maxProcessInstances = 0 THEN /* all */ - - SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' ', Ifnull(P_query, '')); - - ELSE /* limit: IN_maxProcessInstances */ - SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' (PROC_INST_ID_, END_TIME_ ) ( ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' ', Ifnull(P_query, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ') LIMIT ', IFNULL(IN_maxProcessInstances, '')); - END IF; - - INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /fill temp table with PI candidates/ ' , Ifnull(P_query, '')); - - PREPARE stmt1 FROM @P_query1; - EXECUTE stmt1; - DEALLOCATE PREPARE stmt1; - - INSERT INTO TMPLOGTABLE SELECT CONCAT('.... rows inserted into TMP_ARCHIVING_PROCINST: ' , IFNULL((ROW_COUNT()), '')); - - /* 3. Fill TMP_ARCHIVING_BYTEARRAYS with candidates: */ - INSERT INTO TMP_ARCHIVING_BYTEARRAY - SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar - where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) - AND archvar.BYTEARRAY_ID_ is not null; - - INSERT INTO TMP_ARCHIVING_BYTEARRAY - SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar - where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) - AND archvar.BYTEARRAY_ID_ is not null; - - select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY; - INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ', IFNULL(P_baProcessed, '') ,' ByteArray candidates for rollback found!' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); - - - /* 4. Check PI's im TEMP ready for ROLLBACK */ - select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST; - - IF P_piProcessed = 0 THEN /* no candidates found */ - INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: NO ProcessInstance-Candidates for archive-Rollback found! '); - INSERT INTO TMPLOGTABLE SELECT CONCAT('Try TA-ROLLBACK ...'); - ROLLBACK; /*-- TMP_ARCHIVING_PROCINST un-Delete */ - INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-ROLLBACK DONE! ...' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); - - SET P_result = CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: NO ProcessInstance candidates for archive-Rollback found!', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , - '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , - '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); - - INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result); - - ELSE - INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ', IFNULL(P_piProcessed, '') ,' ProcessInstance candidates for Rollback found!'); - - /* LOOP over tables */ - SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables; - - INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount); - - SET @i = 1; - - WHILE @i < P_hiTableCount - DO - SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i; - - INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ####### Start restore from: ARCHIVE_' , IFNULL(@P_tableName, '') ,' ...'); - - SET @P_tableFields = CONCAT(''); - - select GROUP_CONCAT(column_name order by ordinal_position) - INTO @P_tableFields - from information_schema.columns - where table_schema = (select DATABASE()) AND TABLE_NAME = @P_tableName; - - /* INSERT */ - SET @P_query2 = CONCAT('INSERT INTO ', IFNULL(@P_tableName, '') , - ' SELECT ' , @P_tableFields, - ' FROM ARCHIVE_' , IFNULL(@P_tableName, '') , - ' WHERE PROC_INST_ID_ in ( SELECT tmp.PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST tmp)'); - - INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query2, '')); - - PREPARE stmt2 FROM @P_query2; - EXECUTE stmt2; - DEALLOCATE PREPARE stmt2; - - INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), '')); - - /* DELETE */ - INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: Delete in Archive: ARCHIVE_' , IFNULL(@P_tableName, '') ,' ...'); - /* SET @P_query3 = CONCAT(' DELETE FROM ARCHIVE_' , IFNULL(@P_tableName, '') , ' WHERE PROC_INST_ID_ in (select PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)'); */ - SET @P_query3 = CONCAT('DELETE ARCH FROM ARCHIVE_' , IFNULL(@P_tableName, '') , - ' ARCH INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ARCH.PROC_INST_ID_ = TMP.PROC_INST_ID_'); - INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): ' , Ifnull(@P_query3, '')); - PREPARE stmt3 FROM @P_query3; - EXECUTE stmt3; - DEALLOCATE PREPARE stmt3; - - INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); - - SET @i = @i+1; - END WHILE; - - /* INSERT */ - SET @P_tableFields2 = CONCAT(''); /* reset, becouse had some problems with double columns */ - /* fetch table column names into P_tableFields : */ - select GROUP_CONCAT(COLUMN_NAME order by ordinal_position) - INTO @P_tableFields2 - from information_schema.columns - where table_schema = (select DATABASE()) AND TABLE_NAME = 'ACT_GE_BYTEARRAY'; - - SET @P_query4 = CONCAT('INSERT INTO ACT_GE_BYTEARRAY ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' SELECT ' , IFNULL(@P_tableFields2, '') ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' FROM ARCHIVE_ACT_GE_BYTEARRAY' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' WHERE ID_ in ( SELECT tmp.BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY tmp)'); - INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query4, '')); - PREPARE stmt4 FROM @P_query4; - EXECUTE stmt4; - DEALLOCATE PREPARE stmt4; - INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), '')); - - /* DELETE */ - /* DELETE FROM ARCHIVE_ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); */ - DELETE AAGB FROM ARCHIVE_ACT_GE_BYTEARRAY AAGB INNER JOIN TMP_ARCHIVING_BYTEARRAY TMP_B ON AAGB.ID_ = TMP_B.BYTEARRAY_ID_; - INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); - - /* COMMIT TRANSACTION */ - INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: PIs processed: ' , IFNULL(P_piProcessed, '')) ; - COMMIT; - INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-COMMIT DONE!' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); - - SET P_executionDuration = DATEDIFF(sysdate(), P_startDate); - - SET P_result = CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: EXECUTED (commited) successfully! ' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , '; Duration: ' , ifnull((round(P_executionDuration*24*60*60, 1)), ''), ' sec.' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '') , - ' PIs processed: ' , IFNULL(P_piProcessed, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), - ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , - '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , - '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); - - INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result); - END IF; - END; -// - -DELIMITER ; +-- Fix for https://itrack.web.att.com/browse/AJSCCMDA-90 -- +use camundabpmn; + +/* uncomment below statement and run for your db, e.g. : use camundabpmn; + */ +-- use ; + +/* +Drop a archive tables +*/ +/*-- TMP_ARCHIVING_PROCINST */ +DROP TABLE IF EXISTS TMP_ARCHIVING_PROCINST; + +/*-- TMP_ARCHIVING_BYTEARRAY */ +DROP TABLE IF EXISTS TMP_ARCHIVING_BYTEARRAY; + +/*-- TMP LOG TABLE */ +DROP TABLE IF EXISTS TMPLOGTABLE; + +/* -- Camunda Hi Tables --*/ +DROP TABLE IF EXISTS Camunda_Hi_Tables; + +/* drop own extentions columns: +alter table ARCHIVE_ACT_HI_PROCINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +alter table ARCHIVE_ACT_HI_ACTINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +alter table ARCHIVE_ACT_HI_TASKINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +alter table ARCHIVE_ACT_HI_VARINST DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +alter table ARCHIVE_ACT_HI_DETAIL DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +alter table ARCHIVE_ACT_HI_COMMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +alter table ARCHIVE_ACT_HI_ATTACHMENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +alter table ARCHIVE_ACT_HI_OP_LOG DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +alter table ARCHIVE_ACT_HI_INCIDENT DROP (STAT_EXECUTION_ID, STAT_EXECUTION_TS); +*/ + +/*--#1 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_PROCINST; +/*--#2 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ACTINST; +/*--#3 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_TASKINST; +/*--#4 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_VARINST; +/*--#5 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_DETAIL; +/*--#6 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_COMMENT; +/*--#7 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_ATTACHMENT; +/*--#8 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_OP_LOG; +/*--#9 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_HI_INCIDENT; +/*--#10 */ +DROP TABLE IF EXISTS ARCHIVE_ACT_GE_BYTEARRAY; + +/* drop PL SQL procedures: */ +DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY; +DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY; + +/*-- Sequence */ +-- as sequence drop doesn't work automatically in MariaDB, use this procedure to drop sequence + DROP PROCEDURE IF EXISTS DropSequence; + +/*-- To Drop the MariaDB specific user defined procedures and functions */ +DROP FUNCTION IF EXISTS NextVal; +DROP PROCEDURE IF EXISTS CreateSequence; +DROP PROCEDURE IF EXISTS DropSequence; +DROP TABLE IF EXISTS _sequences; + +/* -- If only the sequence: STAT_EXECUTION_SEQ needs to be removed, uncomment and use below statements --*/ +/* + DELIMITER // + CREATE PROCEDURE DropSequence (vname VARCHAR(30)) + BEGIN + -- Drop the sequence + DELETE FROM _sequences WHERE name = vname; + END + // + DELIMITER ; + +-- use the above procedure to drop sequence +CALL DropSequence('STAT_EXECUTION_SEQ'); +*/ + + + + + +/* + 1. Create starts - Add some Camunda Indexes to history schema part (for Archiving) +*/ +create INDEX IF NOT EXISTS IDX_ACT_HI_TASKINST_PIID ON ACT_HI_TASKINST (PROC_INST_ID_); +create INDEX IF NOT EXISTS IDX_ACT_HI_COMMENT_PIID ON ACT_HI_COMMENT (PROC_INST_ID_); +create INDEX IF NOT EXISTS IDX_ACT_HI_ATTACHMENT_PIID ON ACT_HI_ATTACHMENT (PROC_INST_ID_); +create INDEX IF NOT EXISTS IDX_ACT_HI_OP_LOG_PIID ON ACT_HI_OP_LOG (PROC_INST_ID_); +create INDEX IF NOT EXISTS IDX_ACT_HI_INCIDENT_PIID ON ACT_HI_INCIDENT (PROC_INST_ID_); +create INDEX IF NOT EXISTS IDX_ACT_HI_ACTINST_PIID ON ACT_HI_ACTINST(PROC_INST_ID_); + + +/* + 2. Create Archiving Tables in current schema +*/ + +/*-- TMP_ARCHIVING_PROCINST */ +CREATE TABLE TMP_ARCHIVING_PROCINST +( PROC_INST_ID_ varchar(64) not null, + END_TIME_ datetime(3) +); +CREATE INDEX AI_TMP_ARCH_PROCINST_PI_ID ON TMP_ARCHIVING_PROCINST(PROC_INST_ID_); + +/*-- TMP_ARCHIVING_BYTEARRAY */ +CREATE TABLE TMP_ARCHIVING_BYTEARRAY +( BYTEARRAY_ID_ varchar(64) not null, + PROC_INST_ID_ varchar(64) +); +CREATE INDEX AI_TMP_ARCH_BYTEARRAY_BAID ON TMP_ARCHIVING_BYTEARRAY(BYTEARRAY_ID_); + + +/*--#1 ARCHIVE_ACT_HI_PROCINST; */ +create TABLE ARCHIVE_ACT_HI_PROCINST +AS ( select * from ACT_HI_PROCINST where 1=0); + +create index AI_HI_PROCINST_END_TIME on ARCHIVE_ACT_HI_PROCINST(END_TIME_); +ALTER TABLE ARCHIVE_ACT_HI_PROCINST ADD CONSTRAINT ARCHIVE_ACT_HI_PROCINST_UQ UNIQUE ( PROC_INST_ID_); + +/*--#2 ARCHIVE_ACT_HI_ACTINST; */ +create TABLE ARCHIVE_ACT_HI_ACTINST +AS ( select * from ACT_HI_ACTINST where 1=0); + +create index AI_HI_ACTINST_PROC_INST_ID on ARCHIVE_ACT_HI_ACTINST(PROC_INST_ID_); +create index AI_HI_ACTINST_END_TIME on ARCHIVE_ACT_HI_ACTINST(END_TIME_); + +/*--#3 ARCHIVE_ACT_HI_TASKINST; */ +create TABLE ARCHIVE_ACT_HI_TASKINST +AS ( select * from ACT_HI_TASKINST where 1=0); + +create index AI_HI_TASKINST_PROC_INST_ID on ARCHIVE_ACT_HI_TASKINST(PROC_INST_ID_); +create index AI_HI_TASKINST_END_TIME on ARCHIVE_ACT_HI_TASKINST(END_TIME_); + +/*--#4 ARCHIVE_ACT_HI_VARINST; */ +create TABLE ARCHIVE_ACT_HI_VARINST +AS ( select * from ACT_HI_VARINST where 1=0); + +create index AI_HI_VARINST_PROC_INST_ID on ARCHIVE_ACT_HI_VARINST(PROC_INST_ID_); + +/*--#5 ARCHIVE_ACT_HI_DETAIL; */ +create TABLE ARCHIVE_ACT_HI_DETAIL +AS ( select * from ACT_HI_DETAIL where 1=0); + +create index AI_HI_DETAIL_PROC_INST_ID on ARCHIVE_ACT_HI_DETAIL(PROC_INST_ID_); +create index AI_HI_DETAIL_TIME on ARCHIVE_ACT_HI_DETAIL(TIME_); + +/*--#6 ARCHIVE_ACT_HI_COMMENT; */ +create TABLE ARCHIVE_ACT_HI_COMMENT +AS ( select * from ACT_HI_COMMENT where 1=0); + +create index AI_HI_COMMENT_PROC_INST_ID on ARCHIVE_ACT_HI_COMMENT(PROC_INST_ID_); +create index AI_HI_COMMENT_TIME on ARCHIVE_ACT_HI_COMMENT(TIME_); + +/*--#7 ARCHIVE_ACT_HI_ATTACHMENT; */ +create TABLE ARCHIVE_ACT_HI_ATTACHMENT +AS ( select * from ACT_HI_ATTACHMENT where 1=0); + +create index AI_HI_ATTACHMENT_PROC_INST_ID on ARCHIVE_ACT_HI_ATTACHMENT(PROC_INST_ID_); + +/*--#8 ARCHIVE_ACT_HI_OP_LOG; */ +create TABLE ARCHIVE_ACT_HI_OP_LOG +AS ( select * from ACT_HI_OP_LOG where 1=0); + +create index AI_HI_OP_LOG_PROC_INST_ID on ARCHIVE_ACT_HI_OP_LOG(PROC_INST_ID_); +create index AI_HI_OP_LOG_TIMESTAMP on ARCHIVE_ACT_HI_OP_LOG(TIMESTAMP_); + +/*--#9 ARCHIVE_ACT_HI_INCIDENT; */ +create TABLE ARCHIVE_ACT_HI_INCIDENT +AS ( select * from ACT_HI_INCIDENT where 1=0); + +create index AI_HI_INCIDENT_PROC_INST_ID on ARCHIVE_ACT_HI_INCIDENT(PROC_INST_ID_); + +/*--#10 ARCHIVE_ACT_GE_BYTEARRAY; */ +create TABLE ARCHIVE_ACT_GE_BYTEARRAY +AS ( select * from ACT_GE_BYTEARRAY where 1=0); + +create index AI_GE_BYTEARRAY_ID_ on ARCHIVE_ACT_GE_BYTEARRAY(ID_); + +/* ----------------------------------------------------------------------------- +Extend a ARCHIVE: Table by two attributes: STAT_EXECUTION_ID, STAT_EXECUTION_TS +*/ + +/* +--TEMPLATE: +alter table ARCHIVE_%TableName% + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_%TableName%_EXE_ID ON ARCHIVE_%TableName%(STAT_EXECUTION_ID); +*/ + + +/*--#1 ACT_HI_PROCINST */ +alter table ARCHIVE_ACT_HI_PROCINST + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_PROCINST_EXE_ID ON ARCHIVE_ACT_HI_PROCINST(STAT_EXECUTION_ID); + +/*--#2 ACT_HI_ACTINST */ +alter table ARCHIVE_ACT_HI_ACTINST + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_ACTINST_EXE_ID ON ARCHIVE_ACT_HI_ACTINST(STAT_EXECUTION_ID); + +/*--#3 ACT_HI_TASKINST */ +alter table ARCHIVE_ACT_HI_TASKINST + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_TASKINST_EXE_ID ON ARCHIVE_ACT_HI_TASKINST(STAT_EXECUTION_ID); + +/*--#4 ACT_HI_VARINST */ +alter table ARCHIVE_ACT_HI_VARINST + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_VARINST_EXE_ID ON ARCHIVE_ACT_HI_VARINST(STAT_EXECUTION_ID); + +/*--#5 ACT_HI_DETAIL */ +alter table ARCHIVE_ACT_HI_DETAIL + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_DETAIL_EXE_ID ON ARCHIVE_ACT_HI_DETAIL(STAT_EXECUTION_ID); + +/*--#6 ACT_HI_COMMENT */ +alter table ARCHIVE_ACT_HI_COMMENT + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_COMMENT_EXE_ID ON ARCHIVE_ACT_HI_COMMENT(STAT_EXECUTION_ID); + +/*--#7 ACT_HI_ATTACHMENT */ +alter table ARCHIVE_ACT_HI_ATTACHMENT + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_ATTACHMENT_EXE_ID ON ARCHIVE_ACT_HI_ATTACHMENT(STAT_EXECUTION_ID); + +/*--#8 ACT_HI_OP_LOG */ +alter table ARCHIVE_ACT_HI_OP_LOG + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_OP_LOG_EXE_ID ON ARCHIVE_ACT_HI_OP_LOG(STAT_EXECUTION_ID); + +/*--#9 ACT_HI_INCIDENT */ +alter table ARCHIVE_ACT_HI_INCIDENT + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_HI_INCIDENT_EXE_ID ON ARCHIVE_ACT_HI_INCIDENT(STAT_EXECUTION_ID); + +/*--#10 ACT_GE_BYTEARRAY */ +alter table ARCHIVE_ACT_GE_BYTEARRAY + add (STAT_EXECUTION_ID bigint, STAT_EXECUTION_TS timestamp(0) DEFAULT CURRENT_TIMESTAMP ); +CREATE INDEX AI_ACT_GE_BYTEARRAY_EXE_ID ON ARCHIVE_ACT_GE_BYTEARRAY(STAT_EXECUTION_ID); + + +/* -- Next Val as a user defined function needed only in MariaDB--*/ +DROP FUNCTION IF EXISTS NextVal; + DELIMITER // + CREATE FUNCTION NextVal (vname VARCHAR(30)) + RETURNS INT + BEGIN + -- Retrieve and update in single statement + UPDATE _sequences + SET next = next + 1 + WHERE name = vname; + + RETURN (SELECT next FROM _sequences LIMIT 1); + END + // + DELIMITER ; + +/* -- History tables for use in archive procedure, there is no array type in MariaDB --*/ + +Create Table Camunda_Hi_Tables (id_ INT NOT NULL, +TableName_ varchar(80) NOT NULL); + +Insert Into Camunda_Hi_Tables Values (1,'ACT_HI_PROCINST'); +Insert Into Camunda_Hi_Tables Values (2,'ACT_HI_ACTINST'); +Insert Into Camunda_Hi_Tables Values (3,'ACT_HI_TASKINST'); +Insert Into Camunda_Hi_Tables Values (4,'ACT_HI_VARINST'); +Insert Into Camunda_Hi_Tables Values (5,'ACT_HI_DETAIL'); +Insert Into Camunda_Hi_Tables Values (6,'ACT_HI_COMMENT'); +Insert Into Camunda_Hi_Tables Values (7,'ACT_HI_ATTACHMENT'); +Insert Into Camunda_Hi_Tables Values (8,'ACT_HI_OP_LOG'); +Insert Into Camunda_Hi_Tables Values (9,'ACT_HI_INCIDENT'); + +/*-- log table --*/ +CREATE TABLE TMPLOGTABLE (LogMessage Varchar(700)); + + +/* -- Below user defined functions and procedures needed only in MariaDB, they are in-built in Oracle --*/ +/*-- Create a sequence SP */ +DROP PROCEDURE IF EXISTS CreateSequence; + DELIMITER // + CREATE PROCEDURE CreateSequence (name VARCHAR(30), start INT, inc INT) + BEGIN + -- Create a table to store sequences + CREATE TABLE IF NOT EXISTS _sequences + ( + name VARCHAR(70) NOT NULL UNIQUE, + next INT NOT NULL, + inc INT NOT NULL + ); + + -- Add the new sequence + INSERT INTO _sequences VALUES (name, start, inc); + END + // + DELIMITER ; + +/*-------------------------------------------------------------------------------------------------- + Add Meta to Archive + -------------------------------------------------------------------------------------------------- */ + +/* Create STAT_EXECUTION_SEQ: each Archive Entry has a same Execution ID during one Archiving Run */ +CALL CreateSequence('STAT_EXECUTION_SEQ', 1, 1); + + + +/* +ARCHIVE_CAMUNDA_HISTORY-Default Store Procedure starts +Camunda Version: 7.5.4-ee; MariaDB tested +Date: 11.30.2016 +Balaji Mudipalli, AJSC Camunda Team + +DOC.: +-------------------------------------------------------------------------------------- +Create ARCHIVE_CAMUNDA_HISTORY StoreProcedure -function for archiving of history camunda tables. +*/ + +/* uncomment below statement and run for your db, e.g. : use camundabpmn; + */ + +DROP PROCEDURE IF EXISTS ARCHIVE_CAMUNDA_HISTORY; + +DELIMITER // + +CREATE PROCEDURE ARCHIVE_CAMUNDA_HISTORY(IN IN_periodInDays INT, IN IN_maxProcessInstances INT) +MODIFIES SQL DATA + +BEGIN + DECLARE P_hiTableCount INT; + DECLARE P_executionId BIGINT; + DECLARE P_piProcessed DOUBLE; + DECLARE P_baProcessed DOUBLE; + DECLARE P_startDate DATE; + DECLARE P_executionDuration double; + + DECLARE not_found INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; + + /* START TRANSACTION */ + set P_startDate = sysdate(); + set P_executionId = NextVal('STAT_EXECUTION_SEQ'); + + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_executionId value is ',P_executionId); + + DELETE FROM TMP_ARCHIVING_PROCINST; + DELETE FROM TMP_ARCHIVING_BYTEARRAY; + -- temp table -- + DELETE FROM TMPLOGTABLE; + + + /* 1. Set Default Value for Max Pi's */ + IF IN_maxProcessInstances = 0 THEN SET IN_maxProcessInstances = 1000; END IF; + IF IN_maxProcessInstances > 1000 THEN SET IN_maxProcessInstances = 1000; END IF; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('IN_maxProcessInstances value is: ',IN_maxProcessInstances); + + /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */ + IF IN_maxProcessInstances = 0 THEN /* all */ + INSERT INTO TMP_ARCHIVING_PROCINST + SELECT hi.PROC_INST_ID_, hi.END_TIME_ + FROM ACT_HI_PROCINST hi + WHERE hi.END_TIME_ IS NOT NULL + AND hi.END_TIME_ <= ( DATE_SUB(SYSDATE(), INTERVAL IN_periodInDays DAY)); + + ELSE /* limit: IN_maxProcessInstances */ + INSERT INTO TMP_ARCHIVING_PROCINST + (PROC_INST_ID_, END_TIME_) ( + SELECT hi2.PROC_INST_ID_, hi2.END_TIME_ + FROM ACT_HI_PROCINST hi2 + WHERE hi2.END_TIME_ IS NOT NULL + AND hi2.END_TIME_ <= ( DATE_SUB(SYSDATE(), INTERVAL IN_periodInDays DAY)) + ) LIMIT IN_maxProcessInstances; + END IF; + + /* 3. Check PI's im TEMP if any found, ready for ACHIVING */ + select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST; + + IF P_piProcessed = 0 THEN + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_piProcessed value is: ',P_piProcessed); + ROLLBACK; + /* 4. Move data from history to archive (insert to archive and delete in history) */ + ELSE + SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables; + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount); + SET @i = 1; + WHILE @i < P_hiTableCount + DO + SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i; + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_tableName: ', @P_tableName); + + Set @P_archiveTableName = Concat('ARCHIVE_',IFNULL(@P_tableName, '')); + INSERT INTO TMPLOGTABLE SELECT CONCAT('@P_archiveTableName: ', @P_archiveTableName); + + SET @query1 = CONCAT('INSERT INTO ', @P_archiveTableName , + ' SELECT hi3.*, ',P_executionId, ', NOW() FROM ', @P_tableName,' hi3 + WHERE hi3.PROC_INST_ID_ in ( SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)'); + INSERT INTO TMPLOGTABLE SELECT CONCAT('@query1: ', @query1); + + PREPARE stmt1 FROM @query1; + EXECUTE stmt1; + DEALLOCATE PREPARE stmt1; + + SET @query2 = CONCAT ('DELETE ACT FROM ',@P_tableName,' ACT INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ACT.PROC_INST_ID_ = TMP.PROC_INST_ID_'); + INSERT INTO TMPLOGTABLE SELECT CONCAT('@query2: ', @query2); + + PREPARE stmt2 FROM @query2; + EXECUTE stmt2; + DEALLOCATE PREPARE stmt2; + + SET @i = @i+1; + END WHILE; + /* select bytearray_ids */ + INSERT INTO TMP_ARCHIVING_BYTEARRAY + SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar + where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) + AND archvar.BYTEARRAY_ID_ is not null; + + INSERT INTO TMP_ARCHIVING_BYTEARRAY + SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar + where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) + AND archvar.BYTEARRAY_ID_ is not null; + + /* 5. Check Bytearrays im TEMP if any found, ready for ACHIVING */ + select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY; + + /* INSERT */ + INSERT INTO ARCHIVE_ACT_GE_BYTEARRAY + SELECT hi4.*, P_executionId, NOW() FROM ACT_GE_BYTEARRAY hi4 + WHERE hi4.ID_ in ( SELECT BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); + + /* DELETE */ + DELETE FROM ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); + + /* COMMIT TRANSACTION */ + COMMIT; + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_executionId is ', P_executionId); + + set P_executionDuration = DATEDIFF(sysdate(), P_startDate); + + INSERT INTO TMPLOGTABLE SELECT CONCAT('SP success and P_executionDuration is ', ifnull((round(P_executionDuration*24*60*60, 1)), ''), ' sec.'); + END IF; + + END; +// + +DELIMITER ; + + + +/* +ROLLB_ARCHIVE_CAMUNDA_HISTORY-StoreProcedure starts +Camunda Version: 7.5.4-ee; MariaDB tested +Date: 11.30.2016 +Balaji Mudipalli, AJSC Camunda Team + +DOC.: +-------------------------------------------------------------------------------------- +Create ROLLB_ARCHIVE_CAMUNDA_HISTORY StoreProcedure for ROLLBACK (RESTORE) +of archived Camunda history tables. +*/ + +/* uncomment below statement and run for your db, e.g. : use camundabpmn; + */ +-- use ; + + +DROP PROCEDURE IF EXISTS ROLLB_ARCHIVE_CAMUNDA_HISTORY; + +DELIMITER // +CREATE PROCEDURE ROLLB_ARCHIVE_CAMUNDA_HISTORY(IN IN_executionId_from INT, + IN IN_executionId_til INT, + IN IN_maxProcessInstances INT) +MODIFIES SQL DATA + +BEGIN + DECLARE P_hiTableCount INT; + DECLARE P_piProcessed DOUBLE; + DECLARE P_baProcessed DOUBLE; + DECLARE P_query VARCHAR(600); + DECLARE P_startDate DATETIME; + DECLARE P_executionDuration DOUBLE; + DECLARE P_result NVARCHAR(400); +DECLARE not_found INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1; + + + /* START TRANSACTION */ + SET P_startDate = sysdate(); + DELETE FROM TMPLOGTABLE; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: START EXECUTION: ' , ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , + '; PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , + '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , + '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); + + /* 1. Truncate TMP_ARCHIVING_PROCINST */ + + DELETE FROM TMP_ARCHIVING_PROCINST; + DELETE FROM TMP_ARCHIVING_BYTEARRAY; + + /* 2. Fill TMP_ARCHIVING_PROCINST with candidates: */ + IF IN_executionId_til = -1 THEN /* IN_executionId_from only */ + SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID = ' , IFNULL(IN_executionId_from, '')); + + ELSEIF IN_executionId_til = 0 THEN /* all from IN_executionId_from */ + SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID >= ' , IFNULL(IN_executionId_from, '')); + + ELSE /* between IN_executionId_from AND IN_executionId_til */ + SET P_query= CONCAT(' WHERE STAT_EXECUTION_ID between ', IFNULL(IN_executionId_from, '') , ' AND ' , IFNULL(IN_executionId_til, '')); + END IF; + + IF IN_maxProcessInstances = 0 THEN /* all */ + + SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' ', Ifnull(P_query, '')); + + ELSE /* limit: IN_maxProcessInstances */ + SET @P_query1 = CONCAT('INSERT INTO TMP_ARCHIVING_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' (PROC_INST_ID_, END_TIME_ ) ( ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' SELECT PROC_INST_ID_, END_TIME_ FROM ARCHIVE_ACT_HI_PROCINST ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' ', Ifnull(P_query, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ') LIMIT ', IFNULL(IN_maxProcessInstances, '')); + END IF; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /fill temp table with PI candidates/ ' , Ifnull(P_query, '')); + + PREPARE stmt1 FROM @P_query1; + EXECUTE stmt1; + DEALLOCATE PREPARE stmt1; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('.... rows inserted into TMP_ARCHIVING_PROCINST: ' , IFNULL((ROW_COUNT()), '')); + + /* 3. Fill TMP_ARCHIVING_BYTEARRAYS with candidates: */ + INSERT INTO TMP_ARCHIVING_BYTEARRAY + SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_VARINST archvar + where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) + AND archvar.BYTEARRAY_ID_ is not null; + + INSERT INTO TMP_ARCHIVING_BYTEARRAY + SELECT BYTEARRAY_ID_, PROC_INST_ID_ FROM ARCHIVE_ACT_HI_DETAIL archvar + where archvar.PROC_INST_ID_ in (SELECT PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST) + AND archvar.BYTEARRAY_ID_ is not null; + + select count(*) INTO P_baProcessed FROM TMP_ARCHIVING_BYTEARRAY; + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ', IFNULL(P_baProcessed, '') ,' ByteArray candidates for rollback found!' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + + /* 4. Check PI's im TEMP ready for ROLLBACK */ + select count(*) INTO P_piProcessed FROM TMP_ARCHIVING_PROCINST; + + IF P_piProcessed = 0 THEN /* no candidates found */ + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: NO ProcessInstance-Candidates for archive-Rollback found! '); + INSERT INTO TMPLOGTABLE SELECT CONCAT('Try TA-ROLLBACK ...'); + ROLLBACK; /*-- TMP_ARCHIVING_PROCINST un-Delete */ + INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-ROLLBACK DONE! ...' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + SET P_result = CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: NO ProcessInstance candidates for archive-Rollback found!', ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , + '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , + '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); + + INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result); + + ELSE + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ', IFNULL(P_piProcessed, '') ,' ProcessInstance candidates for Rollback found!'); + + /* LOOP over tables */ + SELECT COUNT(*)+1 INTO P_hiTableCount FROM camunda_hi_tables; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('P_hiTableCount value is ',P_hiTableCount); + + SET @i = 1; + + WHILE @i < P_hiTableCount + DO + SELECT TableName_ INTO @P_tableName FROM camunda_hi_tables WHERE id_ = @i; + + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: ####### Start restore from: ARCHIVE_' , IFNULL(@P_tableName, '') ,' ...'); + + SET @P_tableFields = CONCAT(''); + + select GROUP_CONCAT(column_name order by ordinal_position) + INTO @P_tableFields + from information_schema.columns + where table_schema = (select DATABASE()) AND TABLE_NAME = @P_tableName; + + /* INSERT */ + SET @P_query2 = CONCAT('INSERT INTO ', IFNULL(@P_tableName, '') , + ' SELECT ' , @P_tableFields, + ' FROM ARCHIVE_' , IFNULL(@P_tableName, '') , + ' WHERE PROC_INST_ID_ in ( SELECT tmp.PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST tmp)'); + + INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query2, '')); + + PREPARE stmt2 FROM @P_query2; + EXECUTE stmt2; + DEALLOCATE PREPARE stmt2; + + INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), '')); + + /* DELETE */ + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: Delete in Archive: ARCHIVE_' , IFNULL(@P_tableName, '') ,' ...'); + /* SET @P_query3 = CONCAT(' DELETE FROM ARCHIVE_' , IFNULL(@P_tableName, '') , ' WHERE PROC_INST_ID_ in (select PROC_INST_ID_ FROM TMP_ARCHIVING_PROCINST)'); */ + SET @P_query3 = CONCAT('DELETE ARCH FROM ARCHIVE_' , IFNULL(@P_tableName, '') , + ' ARCH INNER JOIN TMP_ARCHIVING_PROCINST TMP ON ARCH.PROC_INST_ID_ = TMP.PROC_INST_ID_'); + INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): ' , Ifnull(@P_query3, '')); + PREPARE stmt3 FROM @P_query3; + EXECUTE stmt3; + DEALLOCATE PREPARE stmt3; + + INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + SET @i = @i+1; + END WHILE; + + /* INSERT */ + SET @P_tableFields2 = CONCAT(''); /* reset, becouse had some problems with double columns */ + /* fetch table column names into P_tableFields : */ + select GROUP_CONCAT(COLUMN_NAME order by ordinal_position) + INTO @P_tableFields2 + from information_schema.columns + where table_schema = (select DATABASE()) AND TABLE_NAME = 'ACT_GE_BYTEARRAY'; + + SET @P_query4 = CONCAT('INSERT INTO ACT_GE_BYTEARRAY ' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' SELECT ' , IFNULL(@P_tableFields2, '') ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' FROM ARCHIVE_ACT_GE_BYTEARRAY' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' WHERE ID_ in ( SELECT tmp.BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY tmp)'); + INSERT INTO TMPLOGTABLE SELECT CONCAT('QUERY (before execute): /copy back to history table/ ' , Ifnull(@P_query4, '')); + PREPARE stmt4 FROM @P_query4; + EXECUTE stmt4; + DEALLOCATE PREPARE stmt4; + INSERT INTO TMPLOGTABLE SELECT Concat('.... rows inserted: ' , IFNULL((ROW_COUNT()), '')); + + /* DELETE */ + /* DELETE FROM ARCHIVE_ACT_GE_BYTEARRAY WHERE ID_ in (select BYTEARRAY_ID_ FROM TMP_ARCHIVING_BYTEARRAY); */ + DELETE AAGB FROM ARCHIVE_ACT_GE_BYTEARRAY AAGB INNER JOIN TMP_ARCHIVING_BYTEARRAY TMP_B ON AAGB.ID_ = TMP_B.BYTEARRAY_ID_; + INSERT INTO TMPLOGTABLE SELECT Concat('.... rows deleted: ' , IFNULL((ROW_COUNT()), '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + /* COMMIT TRANSACTION */ + INSERT INTO TMPLOGTABLE SELECT CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: PIs processed: ' , IFNULL(P_piProcessed, '')) ; + COMMIT; + INSERT INTO TMPLOGTABLE SELECT CONCAT('TA-COMMIT DONE!' ,ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '')); + + SET P_executionDuration = DATEDIFF(sysdate(), P_startDate); + + SET P_result = CONCAT('[ROLLB_ARCHIVE_CAMUNDA_HISTORY]: EXECUTED (commited) successfully! ' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ifnull(date_format(current_timestamp, '%d.%m.%Y %H:%i:%s ..FF3'), '') , '; Duration: ' , ifnull((round(P_executionDuration*24*60*60, 1)), ''), ' sec.' , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), '') , + ' PIs processed: ' , IFNULL(P_piProcessed, '') , ifnull(char(13 using ascii), ''),ifnull(char(10 using ascii), ''), + ' Used PARAMS: IN_executionId_from: ' , IFNULL(IN_executionId_from, '') , + '; IN_executionId_til: ' , IFNULL(IN_executionId_til, '') , + '; IN_maxProcessInstances: ' , IFNULL(IN_maxProcessInstances, '')); + + INSERT INTO TMPLOGTABLE SELECT CONCAT(P_result); + END IF; + END; +// + +DELIMITER ; diff --git a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1702.37_drop1_to_1707.40_drop1.sql b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1702.37_drop1_to_1707.40_drop1.sql index b5d862226a..1cadb75d5f 100644 --- a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1702.37_drop1_to_1707.40_drop1.sql +++ b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1702.37_drop1_to_1707.40_drop1.sql @@ -1,30 +1,30 @@ --- MSO Catalog DB: table 'service-recipe' ---- --- should update a row for create instance -UPDATE mso_catalog.service_recipe -SET ORCHESTRATION_URI = "/mso/async/services/CreateGenericALaCarteServiceInstance" -WHERE SERVICE_ID = 4 - AND ACTION = 'createInstance'; - --- should update a row for delete instance -UPDATE mso_catalog.service_recipe -SET ORCHESTRATION_URI = "/mso/async/services/DeleteGenericALaCarteServiceInstance" -WHERE SERVICE_ID = 4 - AND ACTION = 'deleteInstance'; - -SET SQL_SAFE_UPDATES = 0; - --- 1 coordinate this change with Dmitry when updating labs -UPDATE mso_catalog.service_recipe -SET orchestration_uri = "/mso/async/services/CreateGenericMacroServiceNetworkVnf" -WHERE orchestration_uri = "/mso/async/services/CreateViprAtmService"; - -UPDATE mso_catalog.service_recipe -SET orchestration_uri = "/mso/async/services/DeleteGenericMacroServiceNetworkVnf" -WHERE orchestration_uri = "/mso/async/services/DeleteViprAtmService"; - --- 2 network_recipe -UPDATE mso_catalog.network_recipe -SET orchestration_uri = '/mso/async/services/UpdateNetworkInstance' -WHERE network_type = 'VID_DEFAULT' AND action = 'updateInstance'; - +-- MSO Catalog DB: table 'service-recipe' ---- +-- should update a row for create instance +UPDATE mso_catalog.service_recipe +SET ORCHESTRATION_URI = "/mso/async/services/CreateGenericALaCarteServiceInstance" +WHERE SERVICE_ID = 4 + AND ACTION = 'createInstance'; + +-- should update a row for delete instance +UPDATE mso_catalog.service_recipe +SET ORCHESTRATION_URI = "/mso/async/services/DeleteGenericALaCarteServiceInstance" +WHERE SERVICE_ID = 4 + AND ACTION = 'deleteInstance'; + +SET SQL_SAFE_UPDATES = 0; + +-- 1 coordinate this change with Dmitry when updating labs +UPDATE mso_catalog.service_recipe +SET orchestration_uri = "/mso/async/services/CreateGenericMacroServiceNetworkVnf" +WHERE orchestration_uri = "/mso/async/services/CreateViprAtmService"; + +UPDATE mso_catalog.service_recipe +SET orchestration_uri = "/mso/async/services/DeleteGenericMacroServiceNetworkVnf" +WHERE orchestration_uri = "/mso/async/services/DeleteViprAtmService"; + +-- 2 network_recipe +UPDATE mso_catalog.network_recipe +SET orchestration_uri = '/mso/async/services/UpdateNetworkInstance' +WHERE network_type = 'VID_DEFAULT' AND action = 'updateInstance'; + SET SQL_SAFE_UPDATES = 1; \ No newline at end of file diff --git a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1707.40_drop1_to_1707.41_drop1.sql b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1707.40_drop1_to_1707.41_drop1.sql index 30da15fb79..add114da1e 100644 --- a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1707.40_drop1_to_1707.41_drop1.sql +++ b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1707.40_drop1_to_1707.41_drop1.sql @@ -1,1350 +1,1350 @@ --- MySQL Workbench Synchronization <<<1 --- Generated: April 2017 --- MariaDB-upgrade-1707.40_drop1_to_1707.41_drop1.sql - --- Turn off validation and alter schema <<<1 -BEGIN; - -SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; -SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; -SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; - -ALTER SCHEMA `mso_catalog` DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci ; --- >>>1 - --- FOREIGN KEYS <<<1 -ALTER TABLE `mso_catalog`.`heat_template` -- K <<<2 -DROP FOREIGN KEY `FK_ek5sot1q07taorbdmkvnveu98`; - -ALTER TABLE `mso_catalog`.`heat_template_params` -- K <<<2 -DROP FOREIGN KEY `FK_8sxvm215cw3tjfh3wni2y3myx`; - -ALTER TABLE `mso_catalog`.`service_recipe` -- K <<<2 -DROP FOREIGN KEY `FK_kv13yx013qtqkn94d5gkwbu3s`; - -ALTER TABLE `mso_catalog`.`network_resource_customization` -- K <<<2 -DROP FOREIGN KEY `fk_network_resource_customization__network_resource__id`; --- >>>1 - -UPDATE mso_catalog.heat_environment -- 7 UUID() * <<<1 -SET - description = CONCAT(description, '1707MIGRATED'), - asdc_uuid = (SELECT UUID()) -WHERE - asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; - --- DEBUGGING E2E <<<1 --- ERROR 1062 (23000) at line 40: Duplicate entry '53a70d06-f598-4375-9c3c-fcca1dea3f51' for key 'PRIMARY' -DELETE FROM `mso_catalog`.`heat_environment` where `ASDC_UUID` IN ('53a70d06-f598-4375-9c3c-fcca1dea3f51', 'adc9f8d5-e9d2-4180-994d-cbd59d6eb405'); --- >>>1 - --- heat_environment - * <<<1 -CREATE TABLE `mso_catalog`.`hetemp` ( -- <<<2 - `id` int(11), - `ARTIFACT_UUID` VARCHAR(200) - ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; - -INSERT INTO mso_catalog.hetemp SELECT id, asdc_uuid artifact_uuid FROM mso_catalog.heat_environment; -- <<<2 - -ALTER TABLE `mso_catalog`.`heat_environment` -- <<<2 -DROP COLUMN `ASDC_LABEL`, -DROP COLUMN `ASDC_RESOURCE_NAME`, -DROP COLUMN `id`, -CHANGE COLUMN `ASDC_UUID` `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, -CHANGE COLUMN `ARTIFACT_CHECKSUM` `ARTIFACT_CHECKSUM` VARCHAR(200) NOT NULL DEFAULT 'MANUAL RECORD' AFTER `BODY`, -CHANGE COLUMN `ENVIRONMENT` `BODY` LONGTEXT NOT NULL , -DROP PRIMARY KEY, -ADD PRIMARY KEY (`ARTIFACT_UUID`), -DROP INDEX `UK_a4jkta7hgpa99brceaxasnfqp` ; --- >>>1 - -UPDATE mso_catalog.heat_files -- 7 UUID() * <<<1 -SET - description = CONCAT(description, '1707MIGRATED'), - asdc_uuid = (SELECT UUID()) -WHERE - asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; - -ALTER TABLE `mso_catalog`.`heat_files` -- ^ <<<1 -MODIFY `id` INT, -DROP COLUMN `ASDC_RESOURCE_NAME`, -DROP COLUMN `ASDC_LABEL`, -DROP COLUMN `VNF_RESOURCE_ID`, -CHANGE COLUMN `ASDC_UUID` `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, -CHANGE COLUMN `FILE_NAME` `NAME` VARCHAR(200) NOT NULL AFTER `ARTIFACT_UUID`, -CHANGE COLUMN `VERSION` `VERSION` VARCHAR(20) NOT NULL AFTER `NAME`, -CHANGE COLUMN `ARTIFACT_CHECKSUM` `ARTIFACT_CHECKSUM` VARCHAR(200) NOT NULL DEFAULT 'MANUAL RECORD' AFTER `BODY`, -CHANGE COLUMN `FILE_BODY` `BODY` LONGTEXT NOT NULL , -DROP PRIMARY KEY, -ADD PRIMARY KEY (`ARTIFACT_UUID`), -DROP INDEX `UK_m23vfqc1tdvj7d6f0jjo4cl7e` ; - -CREATE TABLE IF NOT EXISTS `mso_catalog`.`temp_network_heat_template_lookup` ( -- V <<<1 - `NETWORK_RESOURCE_MODEL_NAME` VARCHAR(200) NOT NULL, - `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL, - `AIC_VERSION_MIN` VARCHAR(20) NOT NULL, - `AIC_VERSION_MAX` VARCHAR(20) NULL DEFAULT NULL, - PRIMARY KEY (`NETWORK_RESOURCE_MODEL_NAME`), - INDEX `fk_temp_network_heat_template_lookup__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC) -) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; - -UPDATE mso_catalog.heat_template -- 7 UUID() V <<<1 -SET - description = CONCAT(description, '1707MIGRATED'), - asdc_uuid = (SELECT UUID()) -WHERE - asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; - --- delete where network_resource_model_name is CONTRAIL_EXTERNAL or CONTRAIL_SHARED. Q spec 5/25 -INSERT INTO mso_catalog.temp_network_heat_template_lookup ( -- 3sc * b4 heat_template network_resource <<<1 - network_resource_model_name, - heat_template_artifact_uuid, - aic_version_min, - aic_version_max -) - SELECT - a.network_type, - b.asdc_uuid, - a.aic_version_min, - a.aic_version_max - FROM - mso_catalog.network_resource a, - mso_catalog.heat_template b - WHERE - a.template_id = b.id - AND a.network_type NOT IN ('CONTRAIL_EXTERNAL', 'CONTRAIL_SHARED'); - -ALTER TABLE `mso_catalog`.`heat_template` -- ^ <<<1 -MODIFY `id` INT, -DROP COLUMN `ASDC_LABEL`, -DROP COLUMN `CHILD_TEMPLATE_ID`, -DROP COLUMN `TEMPLATE_PATH`, -DROP COLUMN `ASDC_RESOURCE_NAME`, -CHANGE COLUMN `ASDC_UUID` `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, -CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL AFTER `VERSION`, -CHANGE COLUMN `ARTIFACT_CHECKSUM` `ARTIFACT_CHECKSUM` VARCHAR(200) NOT NULL DEFAULT 'MANUAL RECORD' AFTER `TIMEOUT_MINUTES`, -CHANGE COLUMN `TEMPLATE_NAME` `NAME` VARCHAR(200) NOT NULL , -CHANGE COLUMN `TEMPLATE_BODY` `BODY` LONGTEXT NOT NULL , -DROP PRIMARY KEY, -ADD PRIMARY KEY (`ARTIFACT_UUID`), -DROP INDEX `FK_ek5sot1q07taorbdmkvnveu98` , -DROP INDEX `UK_k1tq7vblss8ykiwhiltnkg6no` ; - -ALTER TABLE `mso_catalog`.`temp_network_heat_template_lookup` -- after alter heat_template ^ <<<1 - ADD CONSTRAINT `fk_temp_network_heat_template_lookup__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE RESTRICT - ON UPDATE CASCADE; --- >>>1 - --- heat_nested_template AFTER heat_template * <<<1 -CREATE TABLE `mso_catalog`.`hnttemp` ( -- <<<2 - `PARENT_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL , - `CHILD_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL, - `PROVIDER_RESOURCE_FILE` varchar(100) DEFAULT NULL - ) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -INSERT INTO mso_catalog.hnttemp ( -- <<<2 - PARENT_HEAT_TEMPLATE_UUID, - CHILD_HEAT_TEMPLATE_UUID, - PROVIDER_RESOURCE_FILE -) - SELECT - ht1.artifact_uuid PARENT_HEAT_TEMPLATE_UUID, - ht2.artifact_uuid CHILD_HEAT_TEMPLATE_UUID, - a.PROVIDER_RESOURCE_FILE - FROM - (SELECT * FROM mso_catalog.heat_nested_template) AS a - JOIN (SELECT * FROM mso_catalog.heat_template) AS ht1 ON a.parent_template_id = ht1.id - JOIN (SELECT * FROM mso_catalog.heat_template) AS ht2 ON a.child_template_id = ht2.id; - -DELETE FROM mso_catalog.heat_nested_template; -- <<<2 - -ALTER TABLE `mso_catalog`.`heat_nested_template` -- <<<2 -CHANGE COLUMN `PARENT_TEMPLATE_ID` `PARENT_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL , -CHANGE COLUMN `CHILD_TEMPLATE_ID` `CHILD_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL , -ADD INDEX `fk_heat_nested_template__heat_template2_idx` (`CHILD_HEAT_TEMPLATE_UUID` ASC); - -INSERT INTO mso_catalog.heat_nested_template SELECT * FROM mso_catalog.hnttemp; -- <<<2 - -DROP TABLE IF EXISTS mso_catalog.hnttemp; -- <<<2 - --- heat_template_params AFTER heat_template ^ <<<1 -CREATE TABLE IF NOT EXISTS `mso_catalog`.`htptemp` ( -- <<<2 - `PARAM_NAME` varchar(100) NOT NULL, - `IS_REQUIRED` bit(1) NOT NULL, - `PARAM_TYPE` varchar(20) DEFAULT NULL, - `PARAM_ALIAS` varchar(45) DEFAULT NULL, - `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -INSERT INTO mso_catalog.htptemp ( -- <<<2 - PARAM_NAME, - IS_REQUIRED, - PARAM_TYPE, - PARAM_ALIAS, - HEAT_TEMPLATE_ARTIFACT_UUID -) - SELECT - a.PARAM_NAME, - a.IS_REQUIRED, - a.PARAM_TYPE, - a.PARAM_ALIAS, - ht1.artifact_uuid HEAT_TEMPLATE_ARTIFACT_UUID - FROM - (SELECT * FROM mso_catalog.heat_template_params) AS a - JOIN (SELECT * FROM mso_catalog.heat_template) AS ht1 ON a.heat_template_id = ht1.id; - -DELETE FROM mso_catalog.heat_template_params; -- <<<2 - -ALTER TABLE `mso_catalog`.`heat_template_params` -- <<<2 -DROP COLUMN `id`, -CHANGE COLUMN `HEAT_TEMPLATE_ID` `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL , -DROP PRIMARY KEY, -ADD PRIMARY KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`, `PARAM_NAME`), -DROP INDEX `UK_pj3cwbmewecf0joqv2mvmbvw3` ; - -INSERT INTO mso_catalog.heat_template_params ( -- <<<2 - PARAM_NAME, - IS_REQUIRED, - PARAM_TYPE, - PARAM_ALIAS, - HEAT_TEMPLATE_ARTIFACT_UUID -) - SELECT - a.PARAM_NAME, - a.IS_REQUIRED, - a.PARAM_TYPE, - a.PARAM_ALIAS, - a.HEAT_TEMPLATE_ARTIFACT_UUID - FROM mso_catalog.htptemp a; - -DROP TABLE IF EXISTS mso_catalog.htptemp; -- <<<2 - --- >>>1 - -ALTER TABLE `mso_catalog`.`network_recipe` -- <<<1 -CHANGE COLUMN `NETWORK_TYPE` `MODEL_NAME` VARCHAR(20) NOT NULL ; - --- 1, 2 UPDATE SERVICE Before SERVICE * <<<1 -UPDATE `mso_catalog`.`service_recipe` -JOIN ( - SELECT - MAX(CAST((COALESCE(NULLIF(version_str, ''), '1.0')) AS DECIMAL(5,2))), - id, - service_name - FROM mso_catalog.service - WHERE service_name = "WAN Bonding" -) a -ON a.service_name = "WAN Bonding" -SET - `service_id` = a.id, - `action` = CASE - WHEN action = 'Layer3AddBonding' then 'createInstance' - WHEN action = 'Layer3DeleteBonding' then 'deleteInstance' - END -WHERE - `action` IN ('Layer3AddBonding', 'Layer3DeleteBonding'); - -UPDATE mso_catalog.service -- 2 <<<2 -SET - service_name_version_id = (SELECT UUID()), - description = CONCAT(description, '1707MIGRATED') -WHERE - service_name_version_id LIKE "MAN%" OR service_name_version_id is NULL OR service_name_version_id = ''; - -UPDATE mso_catalog.service -SET - model_invariant_uuid = (SELECT UUID()), - description = CONCAT(description, '1707MIGRATED') -WHERE - model_invariant_uuid LIKE 'MAN%' OR model_invariant_uuid is NULL OR model_invariant_uuid = ''; - --- service - from temporary table servtemp ^ <<<1 -CREATE TABLE `mso_catalog`.`servtemp` ( -- <<<2 - `id` int(11), - `MODEL_NAME` varchar(40) DEFAULT NULL, - `MODEL_VERSION` varchar(20) NOT NULL, - `DESCRIPTION` varchar(1200) DEFAULT NULL, - `CREATION_TIMESTAMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, - `MODEL_UUID` varchar(50) NOT NULL DEFAULT 'MANUAL_RECORD', - `MODEL_INVARIANT_UUID` varchar(200) NOT NULL DEFAULT 'MANUAL_RECORD' - ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; - -INSERT INTO mso_catalog.servtemp ( -- <<<2 - id, - MODEL_NAME, - MODEL_VERSION, - DESCRIPTION, - CREATION_TIMESTAMP, - MODEL_UUID, - MODEL_INVARIANT_UUID -) - SELECT - id, - SERVICE_NAME, - VERSION_STR, - DESCRIPTION, - CREATION_TIMESTAMP, - SERVICE_NAME_VERSION_ID, - MODEL_INVARIANT_UUID - FROM mso_catalog.service - WHERE SERVICE_NAME NOT IN ('Layer3AddBonding', 'Layer3DeleteBonding'); - -DELETE FROM mso_catalog.service; -- <<<2 - -ALTER TABLE `mso_catalog`.`service_to_allotted_resources` -- <<<2 - DROP FOREIGN KEY `fk_service_to_allotted_resources__service__service_name_ver_id`; - -ALTER TABLE `mso_catalog`.`service_to_networks` -- <<<2 - DROP FOREIGN KEY `fk_service_to_networks__service__service_name_version_id`; - -ALTER TABLE `mso_catalog`.`service` -- ^ <<<2 -MODIFY `id` INT, -DROP COLUMN `SERVICE_ID`, -DROP COLUMN `HTTP_METHOD`, -DROP COLUMN `SERVICE_NAME_VERSION_ID`, -ADD COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, -CHANGE COLUMN `MODEL_INVARIANT_UUID` `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL AFTER `MODEL_NAME`, -CHANGE COLUMN `SERVICE_NAME` `MODEL_NAME` VARCHAR(200) NOT NULL , -CHANGE COLUMN `VERSION_STR` `MODEL_VERSION` VARCHAR(20) NOT NULL , -ADD COLUMN `TOSCA_CSAR_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `CREATION_TIMESTAMP`, -DROP PRIMARY KEY, -ADD PRIMARY KEY (`MODEL_UUID`), -ADD INDEX `fk_service__tosca_csar1_idx` (`TOSCA_CSAR_ARTIFACT_UUID` ASC), -DROP INDEX `UK_service_name__service_name_version_id` ; - -INSERT INTO mso_catalog.service ( - id, CREATION_TIMESTAMP, DESCRIPTION, MODEL_INVARIANT_UUID, MODEL_NAME, MODEL_UUID, MODEL_VERSION -) -SELECT - id, CREATION_TIMESTAMP, DESCRIPTION, MODEL_INVARIANT_UUID, MODEL_NAME, MODEL_UUID, MODEL_VERSION -FROM mso_catalog.servtemp; -- >>>2 - -DROP TABLE IF EXISTS mso_catalog.servtemp; -- <<<2 - --- service_recipe - from temporary table srtemp - AFTER service ^ <<<1 -CREATE TABLE `mso_catalog`.`srtemp` ( -- <<<2 - `id` int(11) NOT NULL , - `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL, - `ACTION` varchar(40) NOT NULL, - `VERSION_STR` varchar(20) DEFAULT NULL, - `DESCRIPTION` varchar(1200) DEFAULT NULL, - `ORCHESTRATION_URI` varchar(256) NOT NULL, - `SERVICE_PARAM_XSD` varchar(2048) DEFAULT NULL, - `RECIPE_TIMEOUT` int(11) DEFAULT NULL, - `SERVICE_TIMEOUT_INTERIM` int(11) DEFAULT NULL, - `CREATION_TIMESTAMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP - ) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - --- ST-CreationTimestamp <<<2 --- ERROR 1292 (22007) at line 331: Incorrect datetime value: '0000-00-00 00:00:00' for column 'CREATION_TIMESTAMP' at row 1 -UPDATE `mso_catalog`.`service_recipe` set CREATION_TIMESTAMP = now() where cast(`CREATION_TIMESTAMP` as char(20)) = '0000-00-00 00:00:00'; --- >>>2 - -INSERT INTO mso_catalog.srtemp ( -- <<<2 - id, - SERVICE_MODEL_UUID, - ACTION, - VERSION_STR, - DESCRIPTION, - ORCHESTRATION_URI, - SERVICE_PARAM_XSD, - RECIPE_TIMEOUT, - SERVICE_TIMEOUT_INTERIM, - CREATION_TIMESTAMP -) - SELECT - a.id, - ht1.MODEL_UUID SERVICE_MODEL_UUID, - a.ACTION, - a.VERSION_STR, - a.DESCRIPTION, - a.ORCHESTRATION_URI, - a.SERVICE_PARAM_XSD, - a.RECIPE_TIMEOUT, - a.SERVICE_TIMEOUT_INTERIM, - a.CREATION_TIMESTAMP - FROM mso_catalog.service_recipe a - JOIN mso_catalog.service AS ht1 ON a.service_id = ht1.id; - -DELETE FROM mso_catalog.service_recipe; -- <<<2 - -ALTER TABLE `mso_catalog`.`service_recipe` -- <<<2 -CHANGE COLUMN `SERVICE_ID` `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, -ADD INDEX `fk_service_recipe__service1_idx` (`SERVICE_MODEL_UUID` ASC); - -INSERT INTO mso_catalog.service_recipe ( -- <<<2 - id, - SERVICE_MODEL_UUID, - ACTION, - VERSION_STR, - DESCRIPTION, - ORCHESTRATION_URI, - SERVICE_PARAM_XSD, - RECIPE_TIMEOUT, - SERVICE_TIMEOUT_INTERIM, - CREATION_TIMESTAMP -) -SELECT - id, - SERVICE_MODEL_UUID, - ACTION, - VERSION_STR, - DESCRIPTION, - ORCHESTRATION_URI, - SERVICE_PARAM_XSD, - RECIPE_TIMEOUT, - SERVICE_TIMEOUT_INTERIM, - CREATION_TIMESTAMP - FROM mso_catalog.srtemp; - -DROP TABLE IF EXISTS mso_catalog.srtemp; -- <<<2 - --- >>>1 - -DELETE FROM mso_catalog.vnf_components_recipe WHERE vnf_component_type = 'VOLUME_GROUP' and vnf_type != '*'; -- Q spec 5/25 <<<1 --- >>>1 - -DELETE FROM mso_catalog.vnf_resource WHERE id IN (2,3,4); -- 3 * <<<1 - -UPDATE mso_catalog.vnf_resource -- 4 * <<<1 -SET - model_name = model_customization_name, - asdc_uuid = '09cb25b0-f2f6-40ed-96bc-71ad43e42fc8', - model_invariant_uuid = '9fdda511-ffe3-4117-b3cc-cff9c1fc3fff' -WHERE - id=5; - -UPDATE mso_catalog.vnf_resource -- 6 set model_name * <<<1 -SET - model_name = vnf_type -WHERE - service_model_invariant_uuid IS NULL OR model_invariant_uuid = ''; - -UPDATE mso_catalog.vnf_resource -- 7 UUID() asdc_uuid * <<<1 -SET - asdc_uuid = (SELECT UUID()), - description = CONCAT(description, '1707MIGRATED') -WHERE - asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; - -UPDATE mso_catalog.vnf_resource -- 8 UUID() model_customization_uuid * <<<1 -SET - description = CONCAT(description, '1707MIGRATED'), - model_customization_uuid = (SELECT UUID()) -WHERE - model_customization_uuid LIKE "MAN%" OR model_customization_uuid is NULL OR model_customization_uuid = ''; - --- >>>1 -UPDATE mso_catalog.vnf_resource -- NOT IN SPEC * <<<1 -SET - model_customization_name = CONCAT('1707MIGRATED_', model_name) -WHERE - model_customization_name is NULL OR model_customization_name = ''; - --- 5 aka 8d delete each asdc_uuid except highest ASDC_SERVICE_MODEL_VERSION vnf_resource and cascade vf_module * <<<1 -CREATE TABLE mso_catalog.req5temp (`vnfs` INT(11) NOT NULL, `vfs` INT(11)); - --- delete VR and cascade VMs what have null/empty VR.service_model_invariant_uuid where vnf_name is NOT "BrocadeVce" -INSERT INTO mso_catalog.req5temp (vnfs, vfs) -- <<<2 - SELECT a.id, m.id - FROM mso_catalog.vnf_resource a - LEFT JOIN mso_catalog.vf_module m ON a.id = m.vnf_resource_id - WHERE (a.vnf_name != "BrocadeVce" OR a.vnf_name IS NULL) - AND (a.service_model_invariant_uuid is NULL OR a.service_model_invariant_uuid = ''); - -DELETE FROM mso_catalog.vnf_resource WHERE id = ANY(SELECT vnfs FROM mso_catalog.req5temp); -DELETE FROM mso_catalog.vf_module WHERE id = ANY(SELECT vfs FROM mso_catalog.req5temp); - -DELETE FROM mso_catalog.req5temp; -- <<<2 - -INSERT INTO mso_catalog.req5temp (vnfs, vfs) -- <<<2 - SELECT a.id, m.id - FROM mso_catalog.vnf_resource a - LEFT JOIN mso_catalog.vf_module m ON a.id = m.vnf_resource_id - JOIN ( - SELECT - MAX(CAST((COALESCE(NULLIF(asdc_service_model_version, ''), '1.0')) AS DECIMAL(5,2))) AS v, - asdc_uuid - FROM mso_catalog.vnf_resource - GROUP BY asdc_uuid - ) b - ON - a.asdc_uuid = b.asdc_uuid AND - CAST((COALESCE(NULLIF(a.asdc_service_model_version, ''), '1.0')) AS DECIMAL(5,2)) != b.v; --- >>>1 - -UPDATE mso_catalog.vf_module -- 7 UUID() asdc_uuid * <<<1 -SET - asdc_uuid = (SELECT UUID()), - description = CONCAT(description, '1707MIGRATED') -WHERE - asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; - -UPDATE mso_catalog.vf_module -- 8 UUID() model_customization_uuid * <<<1 -SET - description = CONCAT(description, '1707MIGRATED'), - model_customization_uuid = (SELECT UUID()) -WHERE - model_customization_uuid LIKE "MAN%" OR model_customization_uuid is NULL OR model_customization_uuid = ''; - --- VMC vf_module_customization * <<<1 -CREATE TABLE IF NOT EXISTS `mso_catalog`.`vf_module_customization` ( -- V <<<2 - `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, - `LABEL` VARCHAR(200) NULL DEFAULT NULL, - `INITIAL_COUNT` INT(11) NULL DEFAULT 0, - `MIN_INSTANCES` INT(11) NULL DEFAULT 0, - `MAX_INSTANCES` INT(11) NULL DEFAULT NULL, - `AVAILABILITY_ZONE_COUNT` INT(11) NULL DEFAULT NULL, - `HEAT_ENVIRONMENT_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL, - `VOL_ENVIRONMENT_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - `VF_MODULE_MODEL_UUID` VARCHAR(200) NOT NULL, - PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), - INDEX `fk_vf_module_customization__vf_module1_idx` (`VF_MODULE_MODEL_UUID` ASC), - INDEX `fk_vf_module_customization__heat_env__heat_environment1_idx` (`HEAT_ENVIRONMENT_ARTIFACT_UUID` ASC), - INDEX `fk_vf_module_customization__vol_env__heat_environment2_idx` (`VOL_ENVIRONMENT_ARTIFACT_UUID` ASC), - CONSTRAINT `fk_vf_module_customization__heat_env__heat_environment1` - FOREIGN KEY (`HEAT_ENVIRONMENT_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_environment` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - CONSTRAINT `fk_vf_module_customization__vol_env__heat_environment2` - FOREIGN KEY (`VOL_ENVIRONMENT_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_environment` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE -) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; - -CREATE TABLE mso_catalog.vfduptemp (`id` INT(11) NOT NULL); -- <<<2 - -INSERT INTO mso_catalog.vfduptemp (id) -- <<<2 -SELECT a.id -FROM mso_catalog.vf_module a -JOIN ( - SELECT - MAX(CAST((COALESCE(NULLIF(asdc_service_model_version, ''), '1.0')) AS DECIMAL(5,2))) AS ver, - model_customization_uuid mcu, - id vid - FROM mso_catalog.vf_module - GROUP BY model_customization_uuid - ) b -ON - a.model_customization_uuid = mcu - AND CAST((COALESCE(NULLIF(a.asdc_service_model_version, ''), '1.0')) AS DECIMAL(5,2)) != b.ver -ORDER BY a.model_customization_uuid; - -INSERT INTO mso_catalog.vf_module_customization ( -- <<<2 - model_customization_uuid, -- <<<3 - label, - initial_count, - min_instances, - max_instances, - heat_environment_artifact_uuid, - vol_environment_artifact_uuid, - vf_module_model_uuid -- >>>3 -) -SELECT - a.model_customization_uuid, - a.label, - a.initial_count, - a.min_instances, - a.max_instances, - ht1.artifact_uuid, - ht2.artifact_uuid, - a.asdc_uuid -FROM mso_catalog.vf_module a -LEFT JOIN mso_catalog.hetemp AS ht1 ON a.environment_id = ht1.id -LEFT JOIN mso_catalog.hetemp AS ht2 ON a.vol_environment_id = ht2.id -WHERE NOT EXISTS ( - SELECT 1 FROM mso_catalog.vfduptemp vdt - WHERE - a.id = vdt.id -); - -DROP TABLE IF EXISTS mso_catalog.vfduptemp; -- <<<2 - -DROP TABLE IF EXISTS mso_catalog.hetemp; -- <<<2 - --- >>>1 - --- AR ALLOTTED_RESOURCE <<<1 -CREATE TABLE IF NOT EXISTS `mso_catalog`.`allotted_resource` ( -- V <<<2 - `MODEL_UUID` VARCHAR(200) NOT NULL, - `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL, - `MODEL_VERSION` VARCHAR(20) NOT NULL, - `MODEL_NAME` VARCHAR(200) NOT NULL, - `TOSCA_NODE_TYPE` VARCHAR(200) NULL DEFAULT NULL, - `SUBCATEGORY` VARCHAR(200) NULL DEFAULT NULL, - `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`MODEL_UUID`)) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - - INSERT INTO `mso_catalog`.`allotted_resource` ( -- 2sc * <<<2 - model_uuid, - model_invariant_uuid, - model_version, - model_name, - description - ) - SELECT DISTINCT - model_uuid, - model_invariant_uuid, - model_version, - model_name, - description - FROM - mso_catalog.allotted_resource_customization; --- >>>1 - -ALTER TABLE `mso_catalog`.`allotted_resource_customization` -- ^ <<<1 -DROP COLUMN `DESCRIPTION`, -DROP COLUMN `MODEL_NAME`, -DROP COLUMN `MODEL_VERSION`, -DROP COLUMN `MODEL_INVARIANT_UUID`, -CHANGE COLUMN `MODEL_UUID` `AR_MODEL_UUID` VARCHAR(200) NOT NULL, -- ARC -CHANGE COLUMN `MODEL_INSTANCE_NAME` `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL AFTER `MODEL_CUSTOMIZATION_UUID`, -ADD COLUMN `PROVIDING_SERVICE_MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_INSTANCE_NAME`, -ADD COLUMN `TARGET_NETWORK_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `PROVIDING_SERVICE_MODEL_INVARIANT_UUID`, -ADD COLUMN `NF_TYPE` VARCHAR(200) NULL DEFAULT NULL AFTER `TARGET_NETWORK_ROLE`, -ADD COLUMN `NF_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_TYPE`, -ADD COLUMN `NF_FUNCTION` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_ROLE`, -ADD COLUMN `NF_NAMING_CODE` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_FUNCTION`, -ADD COLUMN `MIN_INSTANCES` INT(11) NULL DEFAULT NULL AFTER `NF_NAMING_CODE`, -ADD COLUMN `MAX_INSTANCES` INT(11) NULL DEFAULT NULL AFTER `MIN_INSTANCES`, -ADD INDEX `fk_allotted_resource_customization__allotted_resource1_idx` (`AR_MODEL_UUID` ASC); --- >>>1 - --- VRC vnf_resource_customization <<<1 --- vnftemp table <<<2 -CREATE TABLE `mso_catalog`.`vnftemp` AS - SELECT model_customization_uuid, service_model_invariant_uuid, asdc_service_model_version - FROM `mso_catalog`.`vnf_resource`; - -DROP TABLE IF EXISTS `mso_catalog`.`vnf_resource_customization`; -- <<<2 - -CREATE TABLE `mso_catalog`.`vnf_resource_customization` ( -- <<<2 - `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, - `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL, - `MIN_INSTANCES` INT(11) NULL DEFAULT NULL, - `MAX_INSTANCES` INT(11) NULL DEFAULT NULL, - `AVAILABILITY_ZONE_MAX_COUNT` INT(11) NULL DEFAULT NULL, - `NF_TYPE` VARCHAR(200) NULL DEFAULT NULL, - `NF_ROLE` VARCHAR(200) NULL DEFAULT NULL, - `NF_FUNCTION` VARCHAR(200) NULL DEFAULT NULL, - `NF_NAMING_CODE` VARCHAR(200) NULL DEFAULT NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - `VNF_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL, - PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), - INDEX `fk_vnf_resource_customization__vnf_resource1_idx` (`VNF_RESOURCE_MODEL_UUID` ASC) -) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; - -INSERT INTO mso_catalog.vnf_resource_customization ( -- <<<2 - model_customization_uuid, - model_instance_name, - vnf_resource_model_uuid -) - SELECT DISTINCT - a.model_customization_uuid, - ht1.model_customization_name, - ht1.asdc_uuid - FROM mso_catalog.vnftemp a - JOIN mso_catalog.vnf_resource AS ht1 ON - a.model_customization_uuid = ht1.model_customization_uuid AND - a.asdc_service_model_version = ht1.asdc_service_model_version; --- >>>1 - --- network_resource_customization * <<<1 -CREATE TABLE `mso_catalog`.`nrctemp` ( -- <<<2 - `MODEL_UUID` varchar(200) NOT NULL, - `MODEL_NAME` varchar(200) NOT NULL, - `MODEL_INVARIANT_UUID` varchar(200) NOT NULL, - `NETWORK_RESOURCE_ID` int(11) NOT NULL, - `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, - `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL, - `NETWORK_TECHNOLOGY` VARCHAR(45) NULL, - `NETWORK_TYPE` VARCHAR(45) NULL, - `NETWORK_ROLE` VARCHAR(200) NULL, - `NETWORK_SCOPE` VARCHAR(45) NULL, - `MODEL_VERSION` VARCHAR(20) NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - `NETWORK_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -INSERT INTO mso_catalog.nrctemp ( -- <<<2 - model_customization_uuid, - model_uuid, - model_invariant_uuid, - model_instance_name, - model_name, - network_resource_id, - model_version, - creation_timestamp, - network_resource_model_uuid -) - SELECT - model_customization_uuid, - model_uuid, - model_invariant_uuid, - model_instance_name, - model_name, - network_resource_id, - model_version, - creation_timestamp, - model_uuid - FROM mso_catalog.network_resource_customization; - -DELETE FROM mso_catalog.network_resource_customization; -- <<<2 - -ALTER TABLE `mso_catalog`.`network_resource_customization` -- <<<2 -DROP COLUMN `NETWORK_RESOURCE_ID`, -DROP COLUMN `MODEL_VERSION`, -DROP COLUMN `MODEL_INVARIANT_UUID`, -DROP COLUMN `MODEL_NAME`, -DROP COLUMN `MODEL_UUID`, -ADD COLUMN `NETWORK_TECHNOLOGY` VARCHAR(45) NULL DEFAULT NULL AFTER `MODEL_INSTANCE_NAME`, -ADD COLUMN `NETWORK_TYPE` VARCHAR(45) NULL DEFAULT NULL AFTER `NETWORK_TECHNOLOGY`, -ADD COLUMN `NETWORK_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `NETWORK_TYPE`, -ADD COLUMN `NETWORK_SCOPE` VARCHAR(45) NULL DEFAULT NULL AFTER `NETWORK_ROLE`, -ADD COLUMN `NETWORK_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, -DROP PRIMARY KEY, -ADD PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), -ADD INDEX `fk_network_resource_customization__network_resource1_idx` (`NETWORK_RESOURCE_MODEL_UUID` ASC), -DROP INDEX `fk_network_resource_customization__network_resource_id_idx`; --- >>>2 - -INSERT INTO mso_catalog.network_resource_customization ( -- <<<2 - model_customization_uuid, - model_instance_name, - creation_timestamp, - network_resource_model_uuid, - network_type -) - SELECT - a.model_customization_uuid, - a.model_instance_name, - a.creation_timestamp, - a.model_uuid, - a.network_type - FROM mso_catalog.nrctemp a; - --- DROP temp table later, after network_resource uses it <<<2 - --- >>>1 - --- network_resource * <<<1 -CREATE TABLE `mso_catalog`.`nrtemp` ( -- <<<2 - `MODEL_NAME` VARCHAR(200) NOT NULL, - `ORCHESTRATION_MODE` varchar(20) DEFAULT NULL, - `DESCRIPTION` varchar(1200) DEFAULT NULL, - `NEUTRON_NETWORK_TYPE` varchar(20) DEFAULT NULL, - `CREATION_TIMESTAMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, - `MODEL_VERSION` VARCHAR(20) NULL DEFAULT NULL, - `AIC_VERSION_MIN` varchar(20) NOT NULL, - `AIC_VERSION_MAX` varchar(20) DEFAULT NULL, - `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL, - `TOSCA_NODE_TYPE` VARCHAR(200) NULL DEFAULT NULL, - `TEMPLATE_ID` VARCHAR(200) - ) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - --- E2E-CreationTimestamp <<<2 --- ERROR 1292 (22007) at line 675: Incorrect datetime value: '0000-00-00 00:00:00' for column 'CREATION_TIMESTAMP' at row 1 -UPDATE `mso_catalog`.`network_resource` set CREATION_TIMESTAMP = now() where cast(`CREATION_TIMESTAMP` as char(20)) = '0000-00-00 00:00:00'; --- >>>2 - -INSERT INTO mso_catalog.nrtemp ( -- <<<2 - MODEL_NAME, - ORCHESTRATION_MODE, - DESCRIPTION, - NEUTRON_NETWORK_TYPE, - CREATION_TIMESTAMP, - MODEL_VERSION, - AIC_VERSION_MIN, - AIC_VERSION_MAX, - TEMPLATE_ID -) - SELECT - NETWORK_TYPE, - ORCHESTRATION_MODE, - DESCRIPTION, - NEUTRON_NETWORK_TYPE, - CREATION_TIMESTAMP, - VERSION_STR, - AIC_VERSION_MIN, - AIC_VERSION_MAX, - TEMPLATE_ID - FROM mso_catalog.network_resource; - -DELETE FROM mso_catalog.network_resource; -- <<<2 - -ALTER TABLE `mso_catalog`.`network_resource` -- <<<2 -DROP COLUMN `id`, -CHANGE COLUMN `VERSION_STR` `MODEL_VERSION` VARCHAR(20) NULL DEFAULT NULL, -CHANGE COLUMN `TEMPLATE_ID` `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL, -CHANGE COLUMN `NETWORK_TYPE` `MODEL_NAME` VARCHAR(200) NOT NULL, -CHANGE COLUMN `NEUTRON_NETWORK_TYPE` `NEUTRON_NETWORK_TYPE` VARCHAR(20) NULL DEFAULT NULL, -CHANGE COLUMN `ORCHESTRATION_MODE` `ORCHESTRATION_MODE` VARCHAR(20) NULL DEFAULT 'HEAT' AFTER `AIC_VERSION_MAX`, -CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `ORCHESTRATION_MODE`, -ADD COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, -ADD COLUMN `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_NAME`, -ADD COLUMN `TOSCA_NODE_TYPE` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_VERSION`, -DROP PRIMARY KEY, -ADD PRIMARY KEY (`MODEL_UUID`), -ADD INDEX `fk_network_resource__temp_network_heat_template_lookup1_idx` (`MODEL_NAME` ASC), -ADD INDEX `fk_network_resource__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), -DROP INDEX `UK_e5vlpk2xorqk7ogtg6wgw2eo6` ; - -INSERT INTO mso_catalog.network_resource ( -- <<<2 - model_name, - orchestration_mode, - description, - heat_template_artifact_uuid, - neutron_network_type, - creation_timestamp, - model_version, - aic_version_min, - aic_version_max, - model_uuid, - model_invariant_uuid -) - SELECT DISTINCT - ht2.model_name, - a.ORCHESTRATION_MODE, - a.DESCRIPTION, - ht1.ARTIFACT_UUID, - a.NEUTRON_NETWORK_TYPE, - a.CREATION_TIMESTAMP, - ht2.model_version, - a.AIC_VERSION_MIN, - a.AIC_VERSION_MAX, - ht2.model_uuid, - ht2.model_invariant_uuid - FROM mso_catalog.nrtemp a - JOIN mso_catalog.heat_template ht1 ON a.template_id = ht1.id - JOIN mso_catalog.nrctemp ht2 ON a.model_name = ht2.model_name - GROUP BY a.model_name; - -DROP TABLE IF EXISTS mso_catalog.nrtemp; -- <<<2 - -DROP TABLE IF EXISTS mso_catalog.nrctemp; -- <<<2 - --- >>>1 - --- VRC2VMC vnf_res_custom_to_vf_module_custom <<<1 -CREATE TABLE IF NOT EXISTS `mso_catalog`.`vnf_res_custom_to_vf_module_custom` ( -- <<<2 - `VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, - `VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID`, `VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID`), - INDEX `fk_vnf_res_custom_to_vf_module_custom__vf_module_customizat_idx` (`VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID` ASC), - CONSTRAINT `fk_vnf_res_custom_to_vf_module_custom__vf_module_customization1` - FOREIGN KEY (`VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID`) - REFERENCES `mso_catalog`.`vf_module_customization` (`MODEL_CUSTOMIZATION_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - CONSTRAINT `fk_vnf_res_custom_to_vf_module_custom__vnf_resource_customiza1` - FOREIGN KEY (`VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID`) - REFERENCES `mso_catalog`.`vnf_resource_customization` (`MODEL_CUSTOMIZATION_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -INSERT INTO mso_catalog.vnf_res_custom_to_vf_module_custom ( -- 6sc aka 8c <<<2 - vnf_resource_cust_model_customization_uuid, - vf_module_cust_model_customization_uuid, - creation_timestamp -) - SELECT DISTINCT - a.model_customization_uuid, - b.model_customization_uuid, - now() - FROM - mso_catalog.vnf_resource a, - mso_catalog.vf_module b - WHERE a.id = b.vnf_resource_id; --- >>>1 - --- VR vnf_resource After vrc2vmc and vrc ^ <<<1 --- ERROR 1292 (22007) : Incorrect datetime value: '0000-00-00 00:00:00' for column 'CREATION_TIMESTAMP' <<<2 -UPDATE `mso_catalog`.`vnf_resource` set CREATION_TIMESTAMP = now() where cast(`CREATION_TIMESTAMP` as char(20)) = '0000-00-00 00:00:00'; - -ALTER TABLE `mso_catalog`.`vnf_resource` -- after vrc2vmc and vrc ^ <<<2 -MODIFY `id` INT, -DROP COLUMN `MODEL_CUSTOMIZATION_UUID`, -DROP COLUMN `SERVICE_MODEL_INVARIANT_UUID`, -DROP COLUMN `MODEL_CUSTOMIZATION_NAME`, -DROP COLUMN `VNF_TYPE`, -DROP COLUMN `ASDC_SERVICE_MODEL_VERSION`, -DROP COLUMN `ENVIRONMENT_ID`, -DROP COLUMN `VERSION`, -DROP COLUMN `VNF_NAME`, -CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL, -CHANGE COLUMN `ORCHESTRATION_MODE` `ORCHESTRATION_MODE` VARCHAR(20) NOT NULL DEFAULT 'HEAT', -CHANGE COLUMN `AIC_VERSION_MIN` `AIC_VERSION_MIN` VARCHAR(20) NULL DEFAULT NULL, -CHANGE COLUMN `AIC_VERSION_MAX` `AIC_VERSION_MAX` VARCHAR(20) NULL DEFAULT NULL, -CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -CHANGE COLUMN `ASDC_UUID` `MODEL_UUID` VARCHAR(200) NOT NULL , -ADD COLUMN `TOSCA_NODE_TYPE` VARCHAR(200) NULL DEFAULT NULL, -ADD COLUMN `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL, -DROP PRIMARY KEY, -ADD INDEX `fk_vnf_resource__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), -DROP INDEX `UK_model_customization_uuid__asdc_service_model_version`, -DROP INDEX `UK_k10a0w7h4t0lnbynd3inkg67k`; - -UPDATE mso_catalog.vnf_resource a -- * <<<2 - LEFT JOIN mso_catalog.heat_template ht1 ON a.template_id = ht1.id -SET - heat_template_artifact_uuid = ht1.artifact_uuid; - --- Eliminate duplicates <<<2 -CREATE TABLE `mso_catalog`.`vrtemp` AS - -SELECT vr.* FROM `mso_catalog`.`vnf_resource` vr -WHERE vr.id NOT IN (SELECT vnfs FROM mso_catalog.req5temp) -GROUP BY MODEL_UUID; - -DROP TABLE `mso_catalog`.`vnf_resource`; -RENAME TABLE `mso_catalog`.`vrtemp` TO `mso_catalog`.`vnf_resource`; --- >>>1 - --- VF vf_module after VRC2VMC and VMC ^ <<<1 -CREATE TABLE IF NOT EXISTS `mso_catalog`.`vftemp` ( -- <<<2 - `id` int(11) NOT NULL, - `MODEL_UUID` VARCHAR(200) NOT NULL, - `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL, - `MODEL_VERSION` VARCHAR(20) NOT NULL, - `MODEL_NAME` VARCHAR(200) NOT NULL, - `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL, - `IS_BASE` INT(11) NOT NULL, - `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200), - `VOL_HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - `VNF_RESOURCE_MODEL_UUID` VARCHAR(200) - ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; - -INSERT INTO mso_catalog.vftemp ( -- <<<2 - id, -- <<<3 - model_uuid, - is_base, - model_name, - model_version, - creation_timestamp, - description, - heat_template_artifact_uuid, - vol_heat_template_artifact_uuid, - vnf_resource_model_uuid, - model_invariant_uuid -- >>>3 -) - SELECT - a.id, -- <<<3 - a.asdc_uuid, - a.is_base, - a.model_name, - a.model_version, - a.creation_timestamp, - a.description, - ht1.artifact_uuid heat_template_artifact_uuid, - ht2.artifact_uuid vol_heat_template_artifact_uuid, - vr1.model_uuid vnf_resource_model_uuid, - a.model_invariant_uuid -- >>>3 - FROM - (SELECT * FROM mso_catalog.vf_module) AS a - LEFT JOIN (SELECT * FROM mso_catalog.heat_template) AS ht1 ON a.template_id = ht1.id - LEFT JOIN (SELECT * FROM mso_catalog.heat_template) AS ht2 ON a.vol_template_id = ht2.id - JOIN (SELECT * FROM mso_catalog.vnf_resource) AS vr1 ON a.vnf_resource_id = vr1.id; - -DELETE FROM mso_catalog.vf_module; -- <<<2 - -ALTER TABLE `mso_catalog`.`vf_module` -- after vftemp vrc2vmc and vmc <<<2 -DROP COLUMN `LABEL`, -DROP COLUMN `INITIAL_COUNT`, -DROP COLUMN `MAX_INSTANCES`, -DROP COLUMN `MIN_INSTANCES`, -DROP COLUMN `MODEL_CUSTOMIZATION_UUID`, -DROP COLUMN `TYPE`, -DROP COLUMN `ASDC_SERVICE_MODEL_VERSION`, -DROP COLUMN `ENVIRONMENT_ID`, -DROP COLUMN `VNF_RESOURCE_ID`, -DROP COLUMN `VOL_ENVIRONMENT_ID`, -CHANGE COLUMN `id` `id` INT(11), -CHANGE COLUMN `MODEL_INVARIANT_UUID` `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_UUID`, -CHANGE COLUMN `MODEL_VERSION` `MODEL_VERSION` VARCHAR(20) NOT NULL AFTER `MODEL_INVARIANT_UUID`, -CHANGE COLUMN `IS_BASE` `IS_BASE` INT(11) NOT NULL AFTER `DESCRIPTION`, -CHANGE COLUMN `TEMPLATE_ID` `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `IS_BASE`, -CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `VOL_HEAT_TEMPLATE_ARTIFACT_UUID`, -CHANGE COLUMN `ASDC_UUID` `MODEL_UUID` VARCHAR(200) NOT NULL , -CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL , -CHANGE COLUMN `VOL_TEMPLATE_ID` `VOL_HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL , -ADD COLUMN `VNF_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, -DROP PRIMARY KEY, -ADD INDEX `fk_vf_module__vnf_resource1_idx` (`VNF_RESOURCE_MODEL_UUID` ASC), -ADD INDEX `fk_vf_module__heat_template_art_uuid__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), -ADD INDEX `fk_vf_module__vol_heat_template_art_uuid__heat_template2_idx` (`VOL_HEAT_TEMPLATE_ARTIFACT_UUID` ASC), -DROP INDEX `UK_model_customization_uuid__asdc_service_model_version` , -DROP INDEX `UK_o3bvdqspginaxlp4gxqohd44l` ; - -INSERT INTO mso_catalog.vf_module ( -- <<<2 - id, -- <<<3 - model_uuid, - is_base, - model_name, - model_version, - creation_timestamp, - description, - heat_template_artifact_uuid, - vol_heat_template_artifact_uuid, - vnf_resource_model_uuid, - model_invariant_uuid -- >>>3 -) - SELECT - id, -- <<<3 - model_uuid, - is_base, - model_name, - model_version, - creation_timestamp, - description, - heat_template_artifact_uuid, - vol_heat_template_artifact_uuid, - vnf_resource_model_uuid, - model_invariant_uuid -- >>>3 - FROM - mso_catalog.vftemp; - --- DROP vftemp later <<<2 - --- >>>1 - --- vnf_components_recipe AFTER vf_module ^ <<<1 -CREATE TABLE `mso_catalog`.`vcrtemp` ( -- <<<2 - `id` int(11) NOT NULL, - `VNF_TYPE` varchar(200) DEFAULT NULL, - `VNF_COMPONENT_TYPE` varchar(45) NOT NULL, - `ACTION` varchar(20) NOT NULL, - `SERVICE_TYPE` varchar(45) DEFAULT NULL, - `VERSION` varchar(20) DEFAULT NULL, - `DESCRIPTION` varchar(1200) DEFAULT NULL, - `ORCHESTRATION_URI` varchar(256) NOT NULL, - `VNF_COMPONENT_PARAM_XSD` varchar(2048) DEFAULT NULL, - `RECIPE_TIMEOUT` int(11) DEFAULT NULL, - `CREATION_TIMESTAMP` datetime DEFAULT CURRENT_TIMESTAMP, - `VF_MODULE_MODEL_UUID` VARCHAR(200) NULL DEFAULT NULL - ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; - -INSERT INTO mso_catalog.vcrtemp ( -- <<<2 - id, -- <<<3 - VNF_TYPE, - VNF_COMPONENT_TYPE, - ACTION, - SERVICE_TYPE, - VERSION, - DESCRIPTION, - ORCHESTRATION_URI, - VNF_COMPONENT_PARAM_XSD, - RECIPE_TIMEOUT, - CREATION_TIMESTAMP, - VF_MODULE_MODEL_UUID -- >>>3 -) - SELECT - a.id, -- <<<3 - a.VNF_TYPE, - a.VNF_COMPONENT_TYPE, - a.ACTION, - a.SERVICE_TYPE, - a.VERSION, - a.DESCRIPTION, - a.ORCHESTRATION_URI, - a.VNF_COMPONENT_PARAM_XSD, - a.RECIPE_TIMEOUT, - a.CREATION_TIMESTAMP, - COALESCE(ht1.model_uuid, a.vf_module_id) VF_MODULE_MODEL_UUID -- >>>3 - FROM mso_catalog.vnf_components_recipe a - LEFT JOIN mso_catalog.vftemp ht1 ON a.vf_module_id = CONVERT(ht1.id, CHAR(100)); - --- DROP vftemp later <<<2 - -DELETE FROM mso_catalog.vnf_components_recipe; -- <<<2 - -ALTER TABLE `mso_catalog`.`vnf_components_recipe` -- <<<2 -CHANGE COLUMN `VF_MODULE_ID` `VF_MODULE_MODEL_UUID` VARCHAR(200) NULL DEFAULT NULL; - -INSERT INTO mso_catalog.vnf_components_recipe SELECT * FROM mso_catalog.vcrtemp; -- <<<2 - -DROP TABLE IF EXISTS mso_catalog.vcrtemp; -- <<<2 - --- >>>1 - --- vf_module_to_heat_files AFTER vf_module heat_files ^ <<<1 -CREATE TABLE `mso_catalog`.`vmthftemp` ( -- <<<2 - VF_MODULE_MODEL_UUID VARCHAR(200) NOT NULL, - HEAT_FILES_ARTIFACT_UUID VARCHAR(200) NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -INSERT INTO mso_catalog.vmthftemp ( -- <<<2 - VF_MODULE_MODEL_UUID, - HEAT_FILES_ARTIFACT_UUID -) - SELECT DISTINCT - ht1.model_uuid, - ht2.artifact_uuid - FROM mso_catalog.vf_module_to_heat_files a - JOIN mso_catalog.vftemp ht1 ON a.vf_module_id = CONVERT(ht1.id, CHAR(100)) - JOIN mso_catalog.heat_files ht2 ON a.HEAT_FILES_ID = ht2.id; - -DROP TABLE IF EXISTS mso_catalog.vftemp; -- <<<2 - -DELETE FROM mso_catalog.vf_module_to_heat_files; -- <<<2 - -ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` -- <<<2 -CHANGE COLUMN `VF_MODULE_ID` `VF_MODULE_MODEL_UUID` VARCHAR(200) NOT NULL , -CHANGE COLUMN `HEAT_FILES_ID` `HEAT_FILES_ARTIFACT_UUID` VARCHAR(200) NOT NULL , -ADD INDEX `fk_vf_module_to_heat_files__heat_files__artifact_uuid1_idx` (`HEAT_FILES_ARTIFACT_UUID` ASC); - -INSERT INTO mso_catalog.vf_module_to_heat_files SELECT * FROM mso_catalog.vmthftemp; -- <<<2 - -DROP TABLE IF EXISTS mso_catalog.vmthftemp; -- <<<2 - --- >>>1 - --- S2RC service_to_resource_customizations` <<<1 -CREATE TABLE IF NOT EXISTS `mso_catalog`.`service_to_resource_customizations` ( -- V <<<2 - `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL, - `RESOURCE_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, - `MODEL_TYPE` VARCHAR(20) NOT NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - INDEX `fk_service_to_resource_cust__service_model_uuid_idx` (`SERVICE_MODEL_UUID` ASC), - PRIMARY KEY (`SERVICE_MODEL_UUID`, `RESOURCE_MODEL_CUSTOMIZATION_UUID`, `MODEL_TYPE`), - INDEX `fk_service_to_resource_cust__resource_model_customiz_uuid_idx` (`RESOURCE_MODEL_CUSTOMIZATION_UUID` ASC), - CONSTRAINT `fk_service_to_resource_cust__service__model_uuid0` - FOREIGN KEY (`SERVICE_MODEL_UUID`) - REFERENCES `mso_catalog`.`service` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE - ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; - -INSERT INTO mso_catalog.service_to_resource_customizations ( -- 4sc * <<<2 - service_model_uuid, - resource_model_customization_uuid, - model_type -) - SELECT - a.service_model_uuid, - a.network_model_customization_uuid, - "network" - FROM - mso_catalog.service_to_networks a; - -INSERT INTO mso_catalog.service_to_resource_customizations ( -- 5sc * <<<2 - service_model_uuid, - resource_model_customization_uuid, - model_type -) - SELECT - a.service_model_uuid, - a.ar_model_customization_uuid, - "allottedResource" - FROM - mso_catalog.service_to_allotted_resources a; - -INSERT INTO mso_catalog.service_to_resource_customizations ( -- 8a * <<<2 - service_model_uuid, - resource_model_customization_uuid, - model_type -) - SELECT - ht1.model_uuid, - a.model_customization_uuid, - "vnf" - FROM mso_catalog.vnftemp a - JOIN mso_catalog.service AS ht1 ON - a.service_model_invariant_uuid = ht1.model_invariant_uuid AND - a.asdc_service_model_version = ht1.model_version; - -ALTER TABLE `mso_catalog`.`service` -- * <<<2 -DROP COLUMN `SERVICE_VERSION`; - -DROP TABLE IF EXISTS mso_catalog.vnftemp; -- <<<2 - --- >>>1 - -CREATE TABLE IF NOT EXISTS `mso_catalog`.`tosca_csar` ( -- C <<<1 - `ARTIFACT_UUID` VARCHAR(200) NOT NULL, - `NAME` VARCHAR(200) NOT NULL, - `VERSION` VARCHAR(20) NOT NULL, - `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL, - `ARTIFACT_CHECKSUM` VARCHAR(200) NOT NULL, - `URL` VARCHAR(200) NOT NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`ARTIFACT_UUID`)) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; --- >>>1 - --- 5 aka 8d delete each asdc_uuid except highest ASDC_SERVICE_MODEL_VERSION vnf_resource and cascade vf_module * <<<1 --- DELETE FROM mso_catalog.vnf_resource WHERE id = ANY(SELECT vnfs FROM mso_catalog.req5temp); -DELETE FROM mso_catalog.vf_module WHERE id = ANY(SELECT vfs FROM mso_catalog.req5temp); -DROP TABLE mso_catalog.req5temp; --- >>>1 - -DROP TABLE IF EXISTS `mso_catalog`.`service_to_networks` ; -- D <<<1 - -DROP TABLE IF EXISTS `mso_catalog`.`service_to_allotted_resources` ; -- D <<<1 - --- >>>1 - --- Drop ID's <<<1 -ALTER TABLE `mso_catalog`.`heat_template` DROP COLUMN `id`; -ALTER TABLE `mso_catalog`.`heat_files` DROP COLUMN `id`; -ALTER TABLE `mso_catalog`.`service` DROP COLUMN `id`; -ALTER TABLE `mso_catalog`.`vnf_resource` DROP COLUMN `id`; -ALTER TABLE `mso_catalog`.`vf_module` DROP COLUMN `id`; --- >>>1 - --- FOREIGN KEYS <<<1 -ALTER TABLE `mso_catalog`.`heat_nested_template` -- K <<<2 -ADD CONSTRAINT `fk_heat_nested_template__parent_heat_temp_uuid__heat_template1` - FOREIGN KEY (`PARENT_HEAT_TEMPLATE_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, -ADD CONSTRAINT `fk_heat_nested_template__child_heat_temp_uuid__heat_template1` - FOREIGN KEY (`CHILD_HEAT_TEMPLATE_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`heat_template_params` -- K <<<2 -ADD CONSTRAINT `fk_heat_template_params__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`service` -- K <<<2 -ADD CONSTRAINT `fk_service__tosca_csar1` - FOREIGN KEY (`TOSCA_CSAR_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`tosca_csar` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`service_recipe` -- K <<<2 -ADD CONSTRAINT `fk_service_recipe__service1` - FOREIGN KEY (`SERVICE_MODEL_UUID`) - REFERENCES `mso_catalog`.`service` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`vnf_resource` -- K <<<2 - ADD PRIMARY KEY (`MODEL_UUID`), - DROP COLUMN `TEMPLATE_ID`, -ADD CONSTRAINT `fk_vnf_resource__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`vf_module` -- K <<<2 -ADD PRIMARY KEY (`MODEL_UUID`, `VNF_RESOURCE_MODEL_UUID`), -ADD CONSTRAINT `fk_vf_module__vnf_resource1` - FOREIGN KEY (`VNF_RESOURCE_MODEL_UUID`) - REFERENCES `mso_catalog`.`vnf_resource` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, -ADD CONSTRAINT `fk_vf_module__heat_template_art_uuid__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, -ADD CONSTRAINT `fk_vf_module__vol_heat_template_art_uuid__heat_template2` - FOREIGN KEY (`VOL_HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`vf_module_customization` -- after vf_module K <<<2 - ADD CONSTRAINT `fk_vf_module_customization__vf_module1` - FOREIGN KEY (`VF_MODULE_MODEL_UUID`) - REFERENCES `mso_catalog`.`vf_module` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` -- K <<<2 -ADD CONSTRAINT `fk_vf_module_to_heat_files__heat_files__artifact_uuid1` - FOREIGN KEY (`HEAT_FILES_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_files` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, -ADD CONSTRAINT `fk_vf_module_to_heat_files__vf_module__model_uuid1` - FOREIGN KEY (`VF_MODULE_MODEL_UUID`) - REFERENCES `mso_catalog`.`vf_module` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`allotted_resource_customization` -- K <<<2 -ADD CONSTRAINT `fk_allotted_resource_customization__allotted_resource1` - FOREIGN KEY (`AR_MODEL_UUID`) - REFERENCES `mso_catalog`.`allotted_resource` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`network_resource` -- K <<<2 -ADD CONSTRAINT `fk_network_resource__temp_network_heat_template_lookup__mod_nm1` - FOREIGN KEY (`MODEL_NAME`) - REFERENCES `mso_catalog`.`temp_network_heat_template_lookup` (`NETWORK_RESOURCE_MODEL_NAME`) - ON DELETE NO ACTION - ON UPDATE NO ACTION, -ADD CONSTRAINT `fk_network_resource__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE RESTRICT - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`network_resource_customization` -- K <<<2 -ADD CONSTRAINT `fk_network_resource_customization__network_resource1` - FOREIGN KEY (`NETWORK_RESOURCE_MODEL_UUID`) - REFERENCES `mso_catalog`.`network_resource` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`vnf_resource_customization` -- K <<<2 -ADD CONSTRAINT `fk_vnf_resource_customization__vnf_resource1` - FOREIGN KEY (`VNF_RESOURCE_MODEL_UUID`) - REFERENCES `mso_catalog`.`vnf_resource` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; --- >>>1 - --- turn validation back on <<<1 -SET SQL_MODE=@OLD_SQL_MODE; -SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; -SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; - -COMMIT; --- >>>1 - -/* -This file uses folds, set by last line. - -While reading this file, lines with the + are folded. - To unfold all: zR - To fold all: zM - -Move cursor to folded line: type in commands... - Toggle folding: za - Recursively: zA - -Vim help about folding -:help fold -*/ --- vim:foldmarker=<<<,>>>:foldenable:foldmethod=marker +-- MySQL Workbench Synchronization <<<1 +-- Generated: April 2017 +-- MariaDB-upgrade-1707.40_drop1_to_1707.41_drop1.sql + +-- Turn off validation and alter schema <<<1 +BEGIN; + +SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; +SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; + +ALTER SCHEMA `mso_catalog` DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci ; +-- >>>1 + +-- FOREIGN KEYS <<<1 +ALTER TABLE `mso_catalog`.`heat_template` -- K <<<2 +DROP FOREIGN KEY `FK_ek5sot1q07taorbdmkvnveu98`; + +ALTER TABLE `mso_catalog`.`heat_template_params` -- K <<<2 +DROP FOREIGN KEY `FK_8sxvm215cw3tjfh3wni2y3myx`; + +ALTER TABLE `mso_catalog`.`service_recipe` -- K <<<2 +DROP FOREIGN KEY `FK_kv13yx013qtqkn94d5gkwbu3s`; + +ALTER TABLE `mso_catalog`.`network_resource_customization` -- K <<<2 +DROP FOREIGN KEY `fk_network_resource_customization__network_resource__id`; +-- >>>1 + +UPDATE mso_catalog.heat_environment -- 7 UUID() * <<<1 +SET + description = CONCAT(description, '1707MIGRATED'), + asdc_uuid = (SELECT UUID()) +WHERE + asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; + +-- DEBUGGING E2E <<<1 +-- ERROR 1062 (23000) at line 40: Duplicate entry '53a70d06-f598-4375-9c3c-fcca1dea3f51' for key 'PRIMARY' +DELETE FROM `mso_catalog`.`heat_environment` where `ASDC_UUID` IN ('53a70d06-f598-4375-9c3c-fcca1dea3f51', 'adc9f8d5-e9d2-4180-994d-cbd59d6eb405'); +-- >>>1 + +-- heat_environment - * <<<1 +CREATE TABLE `mso_catalog`.`hetemp` ( -- <<<2 + `id` int(11), + `ARTIFACT_UUID` VARCHAR(200) + ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +INSERT INTO mso_catalog.hetemp SELECT id, asdc_uuid artifact_uuid FROM mso_catalog.heat_environment; -- <<<2 + +ALTER TABLE `mso_catalog`.`heat_environment` -- <<<2 +DROP COLUMN `ASDC_LABEL`, +DROP COLUMN `ASDC_RESOURCE_NAME`, +DROP COLUMN `id`, +CHANGE COLUMN `ASDC_UUID` `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, +CHANGE COLUMN `ARTIFACT_CHECKSUM` `ARTIFACT_CHECKSUM` VARCHAR(200) NOT NULL DEFAULT 'MANUAL RECORD' AFTER `BODY`, +CHANGE COLUMN `ENVIRONMENT` `BODY` LONGTEXT NOT NULL , +DROP PRIMARY KEY, +ADD PRIMARY KEY (`ARTIFACT_UUID`), +DROP INDEX `UK_a4jkta7hgpa99brceaxasnfqp` ; +-- >>>1 + +UPDATE mso_catalog.heat_files -- 7 UUID() * <<<1 +SET + description = CONCAT(description, '1707MIGRATED'), + asdc_uuid = (SELECT UUID()) +WHERE + asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; + +ALTER TABLE `mso_catalog`.`heat_files` -- ^ <<<1 +MODIFY `id` INT, +DROP COLUMN `ASDC_RESOURCE_NAME`, +DROP COLUMN `ASDC_LABEL`, +DROP COLUMN `VNF_RESOURCE_ID`, +CHANGE COLUMN `ASDC_UUID` `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, +CHANGE COLUMN `FILE_NAME` `NAME` VARCHAR(200) NOT NULL AFTER `ARTIFACT_UUID`, +CHANGE COLUMN `VERSION` `VERSION` VARCHAR(20) NOT NULL AFTER `NAME`, +CHANGE COLUMN `ARTIFACT_CHECKSUM` `ARTIFACT_CHECKSUM` VARCHAR(200) NOT NULL DEFAULT 'MANUAL RECORD' AFTER `BODY`, +CHANGE COLUMN `FILE_BODY` `BODY` LONGTEXT NOT NULL , +DROP PRIMARY KEY, +ADD PRIMARY KEY (`ARTIFACT_UUID`), +DROP INDEX `UK_m23vfqc1tdvj7d6f0jjo4cl7e` ; + +CREATE TABLE IF NOT EXISTS `mso_catalog`.`temp_network_heat_template_lookup` ( -- V <<<1 + `NETWORK_RESOURCE_MODEL_NAME` VARCHAR(200) NOT NULL, + `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL, + `AIC_VERSION_MIN` VARCHAR(20) NOT NULL, + `AIC_VERSION_MAX` VARCHAR(20) NULL DEFAULT NULL, + PRIMARY KEY (`NETWORK_RESOURCE_MODEL_NAME`), + INDEX `fk_temp_network_heat_template_lookup__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC) +) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +UPDATE mso_catalog.heat_template -- 7 UUID() V <<<1 +SET + description = CONCAT(description, '1707MIGRATED'), + asdc_uuid = (SELECT UUID()) +WHERE + asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; + +-- delete where network_resource_model_name is CONTRAIL_EXTERNAL or CONTRAIL_SHARED. Q spec 5/25 +INSERT INTO mso_catalog.temp_network_heat_template_lookup ( -- 3sc * b4 heat_template network_resource <<<1 + network_resource_model_name, + heat_template_artifact_uuid, + aic_version_min, + aic_version_max +) + SELECT + a.network_type, + b.asdc_uuid, + a.aic_version_min, + a.aic_version_max + FROM + mso_catalog.network_resource a, + mso_catalog.heat_template b + WHERE + a.template_id = b.id + AND a.network_type NOT IN ('CONTRAIL_EXTERNAL', 'CONTRAIL_SHARED'); + +ALTER TABLE `mso_catalog`.`heat_template` -- ^ <<<1 +MODIFY `id` INT, +DROP COLUMN `ASDC_LABEL`, +DROP COLUMN `CHILD_TEMPLATE_ID`, +DROP COLUMN `TEMPLATE_PATH`, +DROP COLUMN `ASDC_RESOURCE_NAME`, +CHANGE COLUMN `ASDC_UUID` `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, +CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL AFTER `VERSION`, +CHANGE COLUMN `ARTIFACT_CHECKSUM` `ARTIFACT_CHECKSUM` VARCHAR(200) NOT NULL DEFAULT 'MANUAL RECORD' AFTER `TIMEOUT_MINUTES`, +CHANGE COLUMN `TEMPLATE_NAME` `NAME` VARCHAR(200) NOT NULL , +CHANGE COLUMN `TEMPLATE_BODY` `BODY` LONGTEXT NOT NULL , +DROP PRIMARY KEY, +ADD PRIMARY KEY (`ARTIFACT_UUID`), +DROP INDEX `FK_ek5sot1q07taorbdmkvnveu98` , +DROP INDEX `UK_k1tq7vblss8ykiwhiltnkg6no` ; + +ALTER TABLE `mso_catalog`.`temp_network_heat_template_lookup` -- after alter heat_template ^ <<<1 + ADD CONSTRAINT `fk_temp_network_heat_template_lookup__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE RESTRICT + ON UPDATE CASCADE; +-- >>>1 + +-- heat_nested_template AFTER heat_template * <<<1 +CREATE TABLE `mso_catalog`.`hnttemp` ( -- <<<2 + `PARENT_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL , + `CHILD_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL, + `PROVIDER_RESOURCE_FILE` varchar(100) DEFAULT NULL + ) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +INSERT INTO mso_catalog.hnttemp ( -- <<<2 + PARENT_HEAT_TEMPLATE_UUID, + CHILD_HEAT_TEMPLATE_UUID, + PROVIDER_RESOURCE_FILE +) + SELECT + ht1.artifact_uuid PARENT_HEAT_TEMPLATE_UUID, + ht2.artifact_uuid CHILD_HEAT_TEMPLATE_UUID, + a.PROVIDER_RESOURCE_FILE + FROM + (SELECT * FROM mso_catalog.heat_nested_template) AS a + JOIN (SELECT * FROM mso_catalog.heat_template) AS ht1 ON a.parent_template_id = ht1.id + JOIN (SELECT * FROM mso_catalog.heat_template) AS ht2 ON a.child_template_id = ht2.id; + +DELETE FROM mso_catalog.heat_nested_template; -- <<<2 + +ALTER TABLE `mso_catalog`.`heat_nested_template` -- <<<2 +CHANGE COLUMN `PARENT_TEMPLATE_ID` `PARENT_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL , +CHANGE COLUMN `CHILD_TEMPLATE_ID` `CHILD_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL , +ADD INDEX `fk_heat_nested_template__heat_template2_idx` (`CHILD_HEAT_TEMPLATE_UUID` ASC); + +INSERT INTO mso_catalog.heat_nested_template SELECT * FROM mso_catalog.hnttemp; -- <<<2 + +DROP TABLE IF EXISTS mso_catalog.hnttemp; -- <<<2 + +-- heat_template_params AFTER heat_template ^ <<<1 +CREATE TABLE IF NOT EXISTS `mso_catalog`.`htptemp` ( -- <<<2 + `PARAM_NAME` varchar(100) NOT NULL, + `IS_REQUIRED` bit(1) NOT NULL, + `PARAM_TYPE` varchar(20) DEFAULT NULL, + `PARAM_ALIAS` varchar(45) DEFAULT NULL, + `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO mso_catalog.htptemp ( -- <<<2 + PARAM_NAME, + IS_REQUIRED, + PARAM_TYPE, + PARAM_ALIAS, + HEAT_TEMPLATE_ARTIFACT_UUID +) + SELECT + a.PARAM_NAME, + a.IS_REQUIRED, + a.PARAM_TYPE, + a.PARAM_ALIAS, + ht1.artifact_uuid HEAT_TEMPLATE_ARTIFACT_UUID + FROM + (SELECT * FROM mso_catalog.heat_template_params) AS a + JOIN (SELECT * FROM mso_catalog.heat_template) AS ht1 ON a.heat_template_id = ht1.id; + +DELETE FROM mso_catalog.heat_template_params; -- <<<2 + +ALTER TABLE `mso_catalog`.`heat_template_params` -- <<<2 +DROP COLUMN `id`, +CHANGE COLUMN `HEAT_TEMPLATE_ID` `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL , +DROP PRIMARY KEY, +ADD PRIMARY KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`, `PARAM_NAME`), +DROP INDEX `UK_pj3cwbmewecf0joqv2mvmbvw3` ; + +INSERT INTO mso_catalog.heat_template_params ( -- <<<2 + PARAM_NAME, + IS_REQUIRED, + PARAM_TYPE, + PARAM_ALIAS, + HEAT_TEMPLATE_ARTIFACT_UUID +) + SELECT + a.PARAM_NAME, + a.IS_REQUIRED, + a.PARAM_TYPE, + a.PARAM_ALIAS, + a.HEAT_TEMPLATE_ARTIFACT_UUID + FROM mso_catalog.htptemp a; + +DROP TABLE IF EXISTS mso_catalog.htptemp; -- <<<2 + +-- >>>1 + +ALTER TABLE `mso_catalog`.`network_recipe` -- <<<1 +CHANGE COLUMN `NETWORK_TYPE` `MODEL_NAME` VARCHAR(20) NOT NULL ; + +-- 1, 2 UPDATE SERVICE Before SERVICE * <<<1 +UPDATE `mso_catalog`.`service_recipe` +JOIN ( + SELECT + MAX(CAST((COALESCE(NULLIF(version_str, ''), '1.0')) AS DECIMAL(5,2))), + id, + service_name + FROM mso_catalog.service + WHERE service_name = "WAN Bonding" +) a +ON a.service_name = "WAN Bonding" +SET + `service_id` = a.id, + `action` = CASE + WHEN action = 'Layer3AddBonding' then 'createInstance' + WHEN action = 'Layer3DeleteBonding' then 'deleteInstance' + END +WHERE + `action` IN ('Layer3AddBonding', 'Layer3DeleteBonding'); + +UPDATE mso_catalog.service -- 2 <<<2 +SET + service_name_version_id = (SELECT UUID()), + description = CONCAT(description, '1707MIGRATED') +WHERE + service_name_version_id LIKE "MAN%" OR service_name_version_id is NULL OR service_name_version_id = ''; + +UPDATE mso_catalog.service +SET + model_invariant_uuid = (SELECT UUID()), + description = CONCAT(description, '1707MIGRATED') +WHERE + model_invariant_uuid LIKE 'MAN%' OR model_invariant_uuid is NULL OR model_invariant_uuid = ''; + +-- service - from temporary table servtemp ^ <<<1 +CREATE TABLE `mso_catalog`.`servtemp` ( -- <<<2 + `id` int(11), + `MODEL_NAME` varchar(40) DEFAULT NULL, + `MODEL_VERSION` varchar(20) NOT NULL, + `DESCRIPTION` varchar(1200) DEFAULT NULL, + `CREATION_TIMESTAMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODEL_UUID` varchar(50) NOT NULL DEFAULT 'MANUAL_RECORD', + `MODEL_INVARIANT_UUID` varchar(200) NOT NULL DEFAULT 'MANUAL_RECORD' + ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +INSERT INTO mso_catalog.servtemp ( -- <<<2 + id, + MODEL_NAME, + MODEL_VERSION, + DESCRIPTION, + CREATION_TIMESTAMP, + MODEL_UUID, + MODEL_INVARIANT_UUID +) + SELECT + id, + SERVICE_NAME, + VERSION_STR, + DESCRIPTION, + CREATION_TIMESTAMP, + SERVICE_NAME_VERSION_ID, + MODEL_INVARIANT_UUID + FROM mso_catalog.service + WHERE SERVICE_NAME NOT IN ('Layer3AddBonding', 'Layer3DeleteBonding'); + +DELETE FROM mso_catalog.service; -- <<<2 + +ALTER TABLE `mso_catalog`.`service_to_allotted_resources` -- <<<2 + DROP FOREIGN KEY `fk_service_to_allotted_resources__service__service_name_ver_id`; + +ALTER TABLE `mso_catalog`.`service_to_networks` -- <<<2 + DROP FOREIGN KEY `fk_service_to_networks__service__service_name_version_id`; + +ALTER TABLE `mso_catalog`.`service` -- ^ <<<2 +MODIFY `id` INT, +DROP COLUMN `SERVICE_ID`, +DROP COLUMN `HTTP_METHOD`, +DROP COLUMN `SERVICE_NAME_VERSION_ID`, +ADD COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, +CHANGE COLUMN `MODEL_INVARIANT_UUID` `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL AFTER `MODEL_NAME`, +CHANGE COLUMN `SERVICE_NAME` `MODEL_NAME` VARCHAR(200) NOT NULL , +CHANGE COLUMN `VERSION_STR` `MODEL_VERSION` VARCHAR(20) NOT NULL , +ADD COLUMN `TOSCA_CSAR_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `CREATION_TIMESTAMP`, +DROP PRIMARY KEY, +ADD PRIMARY KEY (`MODEL_UUID`), +ADD INDEX `fk_service__tosca_csar1_idx` (`TOSCA_CSAR_ARTIFACT_UUID` ASC), +DROP INDEX `UK_service_name__service_name_version_id` ; + +INSERT INTO mso_catalog.service ( + id, CREATION_TIMESTAMP, DESCRIPTION, MODEL_INVARIANT_UUID, MODEL_NAME, MODEL_UUID, MODEL_VERSION +) +SELECT + id, CREATION_TIMESTAMP, DESCRIPTION, MODEL_INVARIANT_UUID, MODEL_NAME, MODEL_UUID, MODEL_VERSION +FROM mso_catalog.servtemp; -- >>>2 + +DROP TABLE IF EXISTS mso_catalog.servtemp; -- <<<2 + +-- service_recipe - from temporary table srtemp - AFTER service ^ <<<1 +CREATE TABLE `mso_catalog`.`srtemp` ( -- <<<2 + `id` int(11) NOT NULL , + `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL, + `ACTION` varchar(40) NOT NULL, + `VERSION_STR` varchar(20) DEFAULT NULL, + `DESCRIPTION` varchar(1200) DEFAULT NULL, + `ORCHESTRATION_URI` varchar(256) NOT NULL, + `SERVICE_PARAM_XSD` varchar(2048) DEFAULT NULL, + `RECIPE_TIMEOUT` int(11) DEFAULT NULL, + `SERVICE_TIMEOUT_INTERIM` int(11) DEFAULT NULL, + `CREATION_TIMESTAMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP + ) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +-- ST-CreationTimestamp <<<2 +-- ERROR 1292 (22007) at line 331: Incorrect datetime value: '0000-00-00 00:00:00' for column 'CREATION_TIMESTAMP' at row 1 +UPDATE `mso_catalog`.`service_recipe` set CREATION_TIMESTAMP = now() where cast(`CREATION_TIMESTAMP` as char(20)) = '0000-00-00 00:00:00'; +-- >>>2 + +INSERT INTO mso_catalog.srtemp ( -- <<<2 + id, + SERVICE_MODEL_UUID, + ACTION, + VERSION_STR, + DESCRIPTION, + ORCHESTRATION_URI, + SERVICE_PARAM_XSD, + RECIPE_TIMEOUT, + SERVICE_TIMEOUT_INTERIM, + CREATION_TIMESTAMP +) + SELECT + a.id, + ht1.MODEL_UUID SERVICE_MODEL_UUID, + a.ACTION, + a.VERSION_STR, + a.DESCRIPTION, + a.ORCHESTRATION_URI, + a.SERVICE_PARAM_XSD, + a.RECIPE_TIMEOUT, + a.SERVICE_TIMEOUT_INTERIM, + a.CREATION_TIMESTAMP + FROM mso_catalog.service_recipe a + JOIN mso_catalog.service AS ht1 ON a.service_id = ht1.id; + +DELETE FROM mso_catalog.service_recipe; -- <<<2 + +ALTER TABLE `mso_catalog`.`service_recipe` -- <<<2 +CHANGE COLUMN `SERVICE_ID` `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, +ADD INDEX `fk_service_recipe__service1_idx` (`SERVICE_MODEL_UUID` ASC); + +INSERT INTO mso_catalog.service_recipe ( -- <<<2 + id, + SERVICE_MODEL_UUID, + ACTION, + VERSION_STR, + DESCRIPTION, + ORCHESTRATION_URI, + SERVICE_PARAM_XSD, + RECIPE_TIMEOUT, + SERVICE_TIMEOUT_INTERIM, + CREATION_TIMESTAMP +) +SELECT + id, + SERVICE_MODEL_UUID, + ACTION, + VERSION_STR, + DESCRIPTION, + ORCHESTRATION_URI, + SERVICE_PARAM_XSD, + RECIPE_TIMEOUT, + SERVICE_TIMEOUT_INTERIM, + CREATION_TIMESTAMP + FROM mso_catalog.srtemp; + +DROP TABLE IF EXISTS mso_catalog.srtemp; -- <<<2 + +-- >>>1 + +DELETE FROM mso_catalog.vnf_components_recipe WHERE vnf_component_type = 'VOLUME_GROUP' and vnf_type != '*'; -- Q spec 5/25 <<<1 +-- >>>1 + +DELETE FROM mso_catalog.vnf_resource WHERE id IN (2,3,4); -- 3 * <<<1 + +UPDATE mso_catalog.vnf_resource -- 4 * <<<1 +SET + model_name = model_customization_name, + asdc_uuid = '09cb25b0-f2f6-40ed-96bc-71ad43e42fc8', + model_invariant_uuid = '9fdda511-ffe3-4117-b3cc-cff9c1fc3fff' +WHERE + id=5; + +UPDATE mso_catalog.vnf_resource -- 6 set model_name * <<<1 +SET + model_name = vnf_type +WHERE + service_model_invariant_uuid IS NULL OR model_invariant_uuid = ''; + +UPDATE mso_catalog.vnf_resource -- 7 UUID() asdc_uuid * <<<1 +SET + asdc_uuid = (SELECT UUID()), + description = CONCAT(description, '1707MIGRATED') +WHERE + asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; + +UPDATE mso_catalog.vnf_resource -- 8 UUID() model_customization_uuid * <<<1 +SET + description = CONCAT(description, '1707MIGRATED'), + model_customization_uuid = (SELECT UUID()) +WHERE + model_customization_uuid LIKE "MAN%" OR model_customization_uuid is NULL OR model_customization_uuid = ''; + +-- >>>1 +UPDATE mso_catalog.vnf_resource -- NOT IN SPEC * <<<1 +SET + model_customization_name = CONCAT('1707MIGRATED_', model_name) +WHERE + model_customization_name is NULL OR model_customization_name = ''; + +-- 5 aka 8d delete each asdc_uuid except highest ASDC_SERVICE_MODEL_VERSION vnf_resource and cascade vf_module * <<<1 +CREATE TABLE mso_catalog.req5temp (`vnfs` INT(11) NOT NULL, `vfs` INT(11)); + +-- delete VR and cascade VMs what have null/empty VR.service_model_invariant_uuid where vnf_name is NOT "BrocadeVce" +INSERT INTO mso_catalog.req5temp (vnfs, vfs) -- <<<2 + SELECT a.id, m.id + FROM mso_catalog.vnf_resource a + LEFT JOIN mso_catalog.vf_module m ON a.id = m.vnf_resource_id + WHERE (a.vnf_name != "BrocadeVce" OR a.vnf_name IS NULL) + AND (a.service_model_invariant_uuid is NULL OR a.service_model_invariant_uuid = ''); + +DELETE FROM mso_catalog.vnf_resource WHERE id = ANY(SELECT vnfs FROM mso_catalog.req5temp); +DELETE FROM mso_catalog.vf_module WHERE id = ANY(SELECT vfs FROM mso_catalog.req5temp); + +DELETE FROM mso_catalog.req5temp; -- <<<2 + +INSERT INTO mso_catalog.req5temp (vnfs, vfs) -- <<<2 + SELECT a.id, m.id + FROM mso_catalog.vnf_resource a + LEFT JOIN mso_catalog.vf_module m ON a.id = m.vnf_resource_id + JOIN ( + SELECT + MAX(CAST((COALESCE(NULLIF(asdc_service_model_version, ''), '1.0')) AS DECIMAL(5,2))) AS v, + asdc_uuid + FROM mso_catalog.vnf_resource + GROUP BY asdc_uuid + ) b + ON + a.asdc_uuid = b.asdc_uuid AND + CAST((COALESCE(NULLIF(a.asdc_service_model_version, ''), '1.0')) AS DECIMAL(5,2)) != b.v; +-- >>>1 + +UPDATE mso_catalog.vf_module -- 7 UUID() asdc_uuid * <<<1 +SET + asdc_uuid = (SELECT UUID()), + description = CONCAT(description, '1707MIGRATED') +WHERE + asdc_uuid LIKE "MAN%" OR asdc_uuid is NULL OR asdc_uuid = ''; + +UPDATE mso_catalog.vf_module -- 8 UUID() model_customization_uuid * <<<1 +SET + description = CONCAT(description, '1707MIGRATED'), + model_customization_uuid = (SELECT UUID()) +WHERE + model_customization_uuid LIKE "MAN%" OR model_customization_uuid is NULL OR model_customization_uuid = ''; + +-- VMC vf_module_customization * <<<1 +CREATE TABLE IF NOT EXISTS `mso_catalog`.`vf_module_customization` ( -- V <<<2 + `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, + `LABEL` VARCHAR(200) NULL DEFAULT NULL, + `INITIAL_COUNT` INT(11) NULL DEFAULT 0, + `MIN_INSTANCES` INT(11) NULL DEFAULT 0, + `MAX_INSTANCES` INT(11) NULL DEFAULT NULL, + `AVAILABILITY_ZONE_COUNT` INT(11) NULL DEFAULT NULL, + `HEAT_ENVIRONMENT_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL, + `VOL_ENVIRONMENT_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `VF_MODULE_MODEL_UUID` VARCHAR(200) NOT NULL, + PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), + INDEX `fk_vf_module_customization__vf_module1_idx` (`VF_MODULE_MODEL_UUID` ASC), + INDEX `fk_vf_module_customization__heat_env__heat_environment1_idx` (`HEAT_ENVIRONMENT_ARTIFACT_UUID` ASC), + INDEX `fk_vf_module_customization__vol_env__heat_environment2_idx` (`VOL_ENVIRONMENT_ARTIFACT_UUID` ASC), + CONSTRAINT `fk_vf_module_customization__heat_env__heat_environment1` + FOREIGN KEY (`HEAT_ENVIRONMENT_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_environment` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_vf_module_customization__vol_env__heat_environment2` + FOREIGN KEY (`VOL_ENVIRONMENT_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_environment` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +CREATE TABLE mso_catalog.vfduptemp (`id` INT(11) NOT NULL); -- <<<2 + +INSERT INTO mso_catalog.vfduptemp (id) -- <<<2 +SELECT a.id +FROM mso_catalog.vf_module a +JOIN ( + SELECT + MAX(CAST((COALESCE(NULLIF(asdc_service_model_version, ''), '1.0')) AS DECIMAL(5,2))) AS ver, + model_customization_uuid mcu, + id vid + FROM mso_catalog.vf_module + GROUP BY model_customization_uuid + ) b +ON + a.model_customization_uuid = mcu + AND CAST((COALESCE(NULLIF(a.asdc_service_model_version, ''), '1.0')) AS DECIMAL(5,2)) != b.ver +ORDER BY a.model_customization_uuid; + +INSERT INTO mso_catalog.vf_module_customization ( -- <<<2 + model_customization_uuid, -- <<<3 + label, + initial_count, + min_instances, + max_instances, + heat_environment_artifact_uuid, + vol_environment_artifact_uuid, + vf_module_model_uuid -- >>>3 +) +SELECT + a.model_customization_uuid, + a.label, + a.initial_count, + a.min_instances, + a.max_instances, + ht1.artifact_uuid, + ht2.artifact_uuid, + a.asdc_uuid +FROM mso_catalog.vf_module a +LEFT JOIN mso_catalog.hetemp AS ht1 ON a.environment_id = ht1.id +LEFT JOIN mso_catalog.hetemp AS ht2 ON a.vol_environment_id = ht2.id +WHERE NOT EXISTS ( + SELECT 1 FROM mso_catalog.vfduptemp vdt + WHERE + a.id = vdt.id +); + +DROP TABLE IF EXISTS mso_catalog.vfduptemp; -- <<<2 + +DROP TABLE IF EXISTS mso_catalog.hetemp; -- <<<2 + +-- >>>1 + +-- AR ALLOTTED_RESOURCE <<<1 +CREATE TABLE IF NOT EXISTS `mso_catalog`.`allotted_resource` ( -- V <<<2 + `MODEL_UUID` VARCHAR(200) NOT NULL, + `MODEL_INVARIANT_UUID` VARCHAR(200) NOT NULL, + `MODEL_VERSION` VARCHAR(20) NOT NULL, + `MODEL_NAME` VARCHAR(200) NOT NULL, + `TOSCA_NODE_TYPE` VARCHAR(200) NULL DEFAULT NULL, + `SUBCATEGORY` VARCHAR(200) NULL DEFAULT NULL, + `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`MODEL_UUID`)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + + INSERT INTO `mso_catalog`.`allotted_resource` ( -- 2sc * <<<2 + model_uuid, + model_invariant_uuid, + model_version, + model_name, + description + ) + SELECT DISTINCT + model_uuid, + model_invariant_uuid, + model_version, + model_name, + description + FROM + mso_catalog.allotted_resource_customization; +-- >>>1 + +ALTER TABLE `mso_catalog`.`allotted_resource_customization` -- ^ <<<1 +DROP COLUMN `DESCRIPTION`, +DROP COLUMN `MODEL_NAME`, +DROP COLUMN `MODEL_VERSION`, +DROP COLUMN `MODEL_INVARIANT_UUID`, +CHANGE COLUMN `MODEL_UUID` `AR_MODEL_UUID` VARCHAR(200) NOT NULL, -- ARC +CHANGE COLUMN `MODEL_INSTANCE_NAME` `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL AFTER `MODEL_CUSTOMIZATION_UUID`, +ADD COLUMN `PROVIDING_SERVICE_MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_INSTANCE_NAME`, +ADD COLUMN `TARGET_NETWORK_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `PROVIDING_SERVICE_MODEL_INVARIANT_UUID`, +ADD COLUMN `NF_TYPE` VARCHAR(200) NULL DEFAULT NULL AFTER `TARGET_NETWORK_ROLE`, +ADD COLUMN `NF_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_TYPE`, +ADD COLUMN `NF_FUNCTION` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_ROLE`, +ADD COLUMN `NF_NAMING_CODE` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_FUNCTION`, +ADD COLUMN `MIN_INSTANCES` INT(11) NULL DEFAULT NULL AFTER `NF_NAMING_CODE`, +ADD COLUMN `MAX_INSTANCES` INT(11) NULL DEFAULT NULL AFTER `MIN_INSTANCES`, +ADD INDEX `fk_allotted_resource_customization__allotted_resource1_idx` (`AR_MODEL_UUID` ASC); +-- >>>1 + +-- VRC vnf_resource_customization <<<1 +-- vnftemp table <<<2 +CREATE TABLE `mso_catalog`.`vnftemp` AS + SELECT model_customization_uuid, service_model_invariant_uuid, asdc_service_model_version + FROM `mso_catalog`.`vnf_resource`; + +DROP TABLE IF EXISTS `mso_catalog`.`vnf_resource_customization`; -- <<<2 + +CREATE TABLE `mso_catalog`.`vnf_resource_customization` ( -- <<<2 + `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, + `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL, + `MIN_INSTANCES` INT(11) NULL DEFAULT NULL, + `MAX_INSTANCES` INT(11) NULL DEFAULT NULL, + `AVAILABILITY_ZONE_MAX_COUNT` INT(11) NULL DEFAULT NULL, + `NF_TYPE` VARCHAR(200) NULL DEFAULT NULL, + `NF_ROLE` VARCHAR(200) NULL DEFAULT NULL, + `NF_FUNCTION` VARCHAR(200) NULL DEFAULT NULL, + `NF_NAMING_CODE` VARCHAR(200) NULL DEFAULT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `VNF_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL, + PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), + INDEX `fk_vnf_resource_customization__vnf_resource1_idx` (`VNF_RESOURCE_MODEL_UUID` ASC) +) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +INSERT INTO mso_catalog.vnf_resource_customization ( -- <<<2 + model_customization_uuid, + model_instance_name, + vnf_resource_model_uuid +) + SELECT DISTINCT + a.model_customization_uuid, + ht1.model_customization_name, + ht1.asdc_uuid + FROM mso_catalog.vnftemp a + JOIN mso_catalog.vnf_resource AS ht1 ON + a.model_customization_uuid = ht1.model_customization_uuid AND + a.asdc_service_model_version = ht1.asdc_service_model_version; +-- >>>1 + +-- network_resource_customization * <<<1 +CREATE TABLE `mso_catalog`.`nrctemp` ( -- <<<2 + `MODEL_UUID` varchar(200) NOT NULL, + `MODEL_NAME` varchar(200) NOT NULL, + `MODEL_INVARIANT_UUID` varchar(200) NOT NULL, + `NETWORK_RESOURCE_ID` int(11) NOT NULL, + `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, + `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL, + `NETWORK_TECHNOLOGY` VARCHAR(45) NULL, + `NETWORK_TYPE` VARCHAR(45) NULL, + `NETWORK_ROLE` VARCHAR(200) NULL, + `NETWORK_SCOPE` VARCHAR(45) NULL, + `MODEL_VERSION` VARCHAR(20) NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `NETWORK_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO mso_catalog.nrctemp ( -- <<<2 + model_customization_uuid, + model_uuid, + model_invariant_uuid, + model_instance_name, + model_name, + network_resource_id, + model_version, + creation_timestamp, + network_resource_model_uuid +) + SELECT + model_customization_uuid, + model_uuid, + model_invariant_uuid, + model_instance_name, + model_name, + network_resource_id, + model_version, + creation_timestamp, + model_uuid + FROM mso_catalog.network_resource_customization; + +DELETE FROM mso_catalog.network_resource_customization; -- <<<2 + +ALTER TABLE `mso_catalog`.`network_resource_customization` -- <<<2 +DROP COLUMN `NETWORK_RESOURCE_ID`, +DROP COLUMN `MODEL_VERSION`, +DROP COLUMN `MODEL_INVARIANT_UUID`, +DROP COLUMN `MODEL_NAME`, +DROP COLUMN `MODEL_UUID`, +ADD COLUMN `NETWORK_TECHNOLOGY` VARCHAR(45) NULL DEFAULT NULL AFTER `MODEL_INSTANCE_NAME`, +ADD COLUMN `NETWORK_TYPE` VARCHAR(45) NULL DEFAULT NULL AFTER `NETWORK_TECHNOLOGY`, +ADD COLUMN `NETWORK_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `NETWORK_TYPE`, +ADD COLUMN `NETWORK_SCOPE` VARCHAR(45) NULL DEFAULT NULL AFTER `NETWORK_ROLE`, +ADD COLUMN `NETWORK_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, +DROP PRIMARY KEY, +ADD PRIMARY KEY (`MODEL_CUSTOMIZATION_UUID`), +ADD INDEX `fk_network_resource_customization__network_resource1_idx` (`NETWORK_RESOURCE_MODEL_UUID` ASC), +DROP INDEX `fk_network_resource_customization__network_resource_id_idx`; +-- >>>2 + +INSERT INTO mso_catalog.network_resource_customization ( -- <<<2 + model_customization_uuid, + model_instance_name, + creation_timestamp, + network_resource_model_uuid, + network_type +) + SELECT + a.model_customization_uuid, + a.model_instance_name, + a.creation_timestamp, + a.model_uuid, + a.network_type + FROM mso_catalog.nrctemp a; + +-- DROP temp table later, after network_resource uses it <<<2 + +-- >>>1 + +-- network_resource * <<<1 +CREATE TABLE `mso_catalog`.`nrtemp` ( -- <<<2 + `MODEL_NAME` VARCHAR(200) NOT NULL, + `ORCHESTRATION_MODE` varchar(20) DEFAULT NULL, + `DESCRIPTION` varchar(1200) DEFAULT NULL, + `NEUTRON_NETWORK_TYPE` varchar(20) DEFAULT NULL, + `CREATION_TIMESTAMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODEL_VERSION` VARCHAR(20) NULL DEFAULT NULL, + `AIC_VERSION_MIN` varchar(20) NOT NULL, + `AIC_VERSION_MAX` varchar(20) DEFAULT NULL, + `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL, + `TOSCA_NODE_TYPE` VARCHAR(200) NULL DEFAULT NULL, + `TEMPLATE_ID` VARCHAR(200) + ) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +-- E2E-CreationTimestamp <<<2 +-- ERROR 1292 (22007) at line 675: Incorrect datetime value: '0000-00-00 00:00:00' for column 'CREATION_TIMESTAMP' at row 1 +UPDATE `mso_catalog`.`network_resource` set CREATION_TIMESTAMP = now() where cast(`CREATION_TIMESTAMP` as char(20)) = '0000-00-00 00:00:00'; +-- >>>2 + +INSERT INTO mso_catalog.nrtemp ( -- <<<2 + MODEL_NAME, + ORCHESTRATION_MODE, + DESCRIPTION, + NEUTRON_NETWORK_TYPE, + CREATION_TIMESTAMP, + MODEL_VERSION, + AIC_VERSION_MIN, + AIC_VERSION_MAX, + TEMPLATE_ID +) + SELECT + NETWORK_TYPE, + ORCHESTRATION_MODE, + DESCRIPTION, + NEUTRON_NETWORK_TYPE, + CREATION_TIMESTAMP, + VERSION_STR, + AIC_VERSION_MIN, + AIC_VERSION_MAX, + TEMPLATE_ID + FROM mso_catalog.network_resource; + +DELETE FROM mso_catalog.network_resource; -- <<<2 + +ALTER TABLE `mso_catalog`.`network_resource` -- <<<2 +DROP COLUMN `id`, +CHANGE COLUMN `VERSION_STR` `MODEL_VERSION` VARCHAR(20) NULL DEFAULT NULL, +CHANGE COLUMN `TEMPLATE_ID` `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL, +CHANGE COLUMN `NETWORK_TYPE` `MODEL_NAME` VARCHAR(200) NOT NULL, +CHANGE COLUMN `NEUTRON_NETWORK_TYPE` `NEUTRON_NETWORK_TYPE` VARCHAR(20) NULL DEFAULT NULL, +CHANGE COLUMN `ORCHESTRATION_MODE` `ORCHESTRATION_MODE` VARCHAR(20) NULL DEFAULT 'HEAT' AFTER `AIC_VERSION_MAX`, +CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `ORCHESTRATION_MODE`, +ADD COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, +ADD COLUMN `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_NAME`, +ADD COLUMN `TOSCA_NODE_TYPE` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_VERSION`, +DROP PRIMARY KEY, +ADD PRIMARY KEY (`MODEL_UUID`), +ADD INDEX `fk_network_resource__temp_network_heat_template_lookup1_idx` (`MODEL_NAME` ASC), +ADD INDEX `fk_network_resource__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), +DROP INDEX `UK_e5vlpk2xorqk7ogtg6wgw2eo6` ; + +INSERT INTO mso_catalog.network_resource ( -- <<<2 + model_name, + orchestration_mode, + description, + heat_template_artifact_uuid, + neutron_network_type, + creation_timestamp, + model_version, + aic_version_min, + aic_version_max, + model_uuid, + model_invariant_uuid +) + SELECT DISTINCT + ht2.model_name, + a.ORCHESTRATION_MODE, + a.DESCRIPTION, + ht1.ARTIFACT_UUID, + a.NEUTRON_NETWORK_TYPE, + a.CREATION_TIMESTAMP, + ht2.model_version, + a.AIC_VERSION_MIN, + a.AIC_VERSION_MAX, + ht2.model_uuid, + ht2.model_invariant_uuid + FROM mso_catalog.nrtemp a + JOIN mso_catalog.heat_template ht1 ON a.template_id = ht1.id + JOIN mso_catalog.nrctemp ht2 ON a.model_name = ht2.model_name + GROUP BY a.model_name; + +DROP TABLE IF EXISTS mso_catalog.nrtemp; -- <<<2 + +DROP TABLE IF EXISTS mso_catalog.nrctemp; -- <<<2 + +-- >>>1 + +-- VRC2VMC vnf_res_custom_to_vf_module_custom <<<1 +CREATE TABLE IF NOT EXISTS `mso_catalog`.`vnf_res_custom_to_vf_module_custom` ( -- <<<2 + `VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, + `VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID`, `VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID`), + INDEX `fk_vnf_res_custom_to_vf_module_custom__vf_module_customizat_idx` (`VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID` ASC), + CONSTRAINT `fk_vnf_res_custom_to_vf_module_custom__vf_module_customization1` + FOREIGN KEY (`VF_MODULE_CUST_MODEL_CUSTOMIZATION_UUID`) + REFERENCES `mso_catalog`.`vf_module_customization` (`MODEL_CUSTOMIZATION_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_vnf_res_custom_to_vf_module_custom__vnf_resource_customiza1` + FOREIGN KEY (`VNF_RESOURCE_CUST_MODEL_CUSTOMIZATION_UUID`) + REFERENCES `mso_catalog`.`vnf_resource_customization` (`MODEL_CUSTOMIZATION_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +INSERT INTO mso_catalog.vnf_res_custom_to_vf_module_custom ( -- 6sc aka 8c <<<2 + vnf_resource_cust_model_customization_uuid, + vf_module_cust_model_customization_uuid, + creation_timestamp +) + SELECT DISTINCT + a.model_customization_uuid, + b.model_customization_uuid, + now() + FROM + mso_catalog.vnf_resource a, + mso_catalog.vf_module b + WHERE a.id = b.vnf_resource_id; +-- >>>1 + +-- VR vnf_resource After vrc2vmc and vrc ^ <<<1 +-- ERROR 1292 (22007) : Incorrect datetime value: '0000-00-00 00:00:00' for column 'CREATION_TIMESTAMP' <<<2 +UPDATE `mso_catalog`.`vnf_resource` set CREATION_TIMESTAMP = now() where cast(`CREATION_TIMESTAMP` as char(20)) = '0000-00-00 00:00:00'; + +ALTER TABLE `mso_catalog`.`vnf_resource` -- after vrc2vmc and vrc ^ <<<2 +MODIFY `id` INT, +DROP COLUMN `MODEL_CUSTOMIZATION_UUID`, +DROP COLUMN `SERVICE_MODEL_INVARIANT_UUID`, +DROP COLUMN `MODEL_CUSTOMIZATION_NAME`, +DROP COLUMN `VNF_TYPE`, +DROP COLUMN `ASDC_SERVICE_MODEL_VERSION`, +DROP COLUMN `ENVIRONMENT_ID`, +DROP COLUMN `VERSION`, +DROP COLUMN `VNF_NAME`, +CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL, +CHANGE COLUMN `ORCHESTRATION_MODE` `ORCHESTRATION_MODE` VARCHAR(20) NOT NULL DEFAULT 'HEAT', +CHANGE COLUMN `AIC_VERSION_MIN` `AIC_VERSION_MIN` VARCHAR(20) NULL DEFAULT NULL, +CHANGE COLUMN `AIC_VERSION_MAX` `AIC_VERSION_MAX` VARCHAR(20) NULL DEFAULT NULL, +CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +CHANGE COLUMN `ASDC_UUID` `MODEL_UUID` VARCHAR(200) NOT NULL , +ADD COLUMN `TOSCA_NODE_TYPE` VARCHAR(200) NULL DEFAULT NULL, +ADD COLUMN `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL, +DROP PRIMARY KEY, +ADD INDEX `fk_vnf_resource__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), +DROP INDEX `UK_model_customization_uuid__asdc_service_model_version`, +DROP INDEX `UK_k10a0w7h4t0lnbynd3inkg67k`; + +UPDATE mso_catalog.vnf_resource a -- * <<<2 + LEFT JOIN mso_catalog.heat_template ht1 ON a.template_id = ht1.id +SET + heat_template_artifact_uuid = ht1.artifact_uuid; + +-- Eliminate duplicates <<<2 +CREATE TABLE `mso_catalog`.`vrtemp` AS + +SELECT vr.* FROM `mso_catalog`.`vnf_resource` vr +WHERE vr.id NOT IN (SELECT vnfs FROM mso_catalog.req5temp) +GROUP BY MODEL_UUID; + +DROP TABLE `mso_catalog`.`vnf_resource`; +RENAME TABLE `mso_catalog`.`vrtemp` TO `mso_catalog`.`vnf_resource`; +-- >>>1 + +-- VF vf_module after VRC2VMC and VMC ^ <<<1 +CREATE TABLE IF NOT EXISTS `mso_catalog`.`vftemp` ( -- <<<2 + `id` int(11) NOT NULL, + `MODEL_UUID` VARCHAR(200) NOT NULL, + `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL, + `MODEL_VERSION` VARCHAR(20) NOT NULL, + `MODEL_NAME` VARCHAR(200) NOT NULL, + `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL, + `IS_BASE` INT(11) NOT NULL, + `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200), + `VOL_HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `VNF_RESOURCE_MODEL_UUID` VARCHAR(200) + ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +INSERT INTO mso_catalog.vftemp ( -- <<<2 + id, -- <<<3 + model_uuid, + is_base, + model_name, + model_version, + creation_timestamp, + description, + heat_template_artifact_uuid, + vol_heat_template_artifact_uuid, + vnf_resource_model_uuid, + model_invariant_uuid -- >>>3 +) + SELECT + a.id, -- <<<3 + a.asdc_uuid, + a.is_base, + a.model_name, + a.model_version, + a.creation_timestamp, + a.description, + ht1.artifact_uuid heat_template_artifact_uuid, + ht2.artifact_uuid vol_heat_template_artifact_uuid, + vr1.model_uuid vnf_resource_model_uuid, + a.model_invariant_uuid -- >>>3 + FROM + (SELECT * FROM mso_catalog.vf_module) AS a + LEFT JOIN (SELECT * FROM mso_catalog.heat_template) AS ht1 ON a.template_id = ht1.id + LEFT JOIN (SELECT * FROM mso_catalog.heat_template) AS ht2 ON a.vol_template_id = ht2.id + JOIN (SELECT * FROM mso_catalog.vnf_resource) AS vr1 ON a.vnf_resource_id = vr1.id; + +DELETE FROM mso_catalog.vf_module; -- <<<2 + +ALTER TABLE `mso_catalog`.`vf_module` -- after vftemp vrc2vmc and vmc <<<2 +DROP COLUMN `LABEL`, +DROP COLUMN `INITIAL_COUNT`, +DROP COLUMN `MAX_INSTANCES`, +DROP COLUMN `MIN_INSTANCES`, +DROP COLUMN `MODEL_CUSTOMIZATION_UUID`, +DROP COLUMN `TYPE`, +DROP COLUMN `ASDC_SERVICE_MODEL_VERSION`, +DROP COLUMN `ENVIRONMENT_ID`, +DROP COLUMN `VNF_RESOURCE_ID`, +DROP COLUMN `VOL_ENVIRONMENT_ID`, +CHANGE COLUMN `id` `id` INT(11), +CHANGE COLUMN `MODEL_INVARIANT_UUID` `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_UUID`, +CHANGE COLUMN `MODEL_VERSION` `MODEL_VERSION` VARCHAR(20) NOT NULL AFTER `MODEL_INVARIANT_UUID`, +CHANGE COLUMN `IS_BASE` `IS_BASE` INT(11) NOT NULL AFTER `DESCRIPTION`, +CHANGE COLUMN `TEMPLATE_ID` `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `IS_BASE`, +CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `VOL_HEAT_TEMPLATE_ARTIFACT_UUID`, +CHANGE COLUMN `ASDC_UUID` `MODEL_UUID` VARCHAR(200) NOT NULL , +CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL , +CHANGE COLUMN `VOL_TEMPLATE_ID` `VOL_HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL , +ADD COLUMN `VNF_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, +DROP PRIMARY KEY, +ADD INDEX `fk_vf_module__vnf_resource1_idx` (`VNF_RESOURCE_MODEL_UUID` ASC), +ADD INDEX `fk_vf_module__heat_template_art_uuid__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), +ADD INDEX `fk_vf_module__vol_heat_template_art_uuid__heat_template2_idx` (`VOL_HEAT_TEMPLATE_ARTIFACT_UUID` ASC), +DROP INDEX `UK_model_customization_uuid__asdc_service_model_version` , +DROP INDEX `UK_o3bvdqspginaxlp4gxqohd44l` ; + +INSERT INTO mso_catalog.vf_module ( -- <<<2 + id, -- <<<3 + model_uuid, + is_base, + model_name, + model_version, + creation_timestamp, + description, + heat_template_artifact_uuid, + vol_heat_template_artifact_uuid, + vnf_resource_model_uuid, + model_invariant_uuid -- >>>3 +) + SELECT + id, -- <<<3 + model_uuid, + is_base, + model_name, + model_version, + creation_timestamp, + description, + heat_template_artifact_uuid, + vol_heat_template_artifact_uuid, + vnf_resource_model_uuid, + model_invariant_uuid -- >>>3 + FROM + mso_catalog.vftemp; + +-- DROP vftemp later <<<2 + +-- >>>1 + +-- vnf_components_recipe AFTER vf_module ^ <<<1 +CREATE TABLE `mso_catalog`.`vcrtemp` ( -- <<<2 + `id` int(11) NOT NULL, + `VNF_TYPE` varchar(200) DEFAULT NULL, + `VNF_COMPONENT_TYPE` varchar(45) NOT NULL, + `ACTION` varchar(20) NOT NULL, + `SERVICE_TYPE` varchar(45) DEFAULT NULL, + `VERSION` varchar(20) DEFAULT NULL, + `DESCRIPTION` varchar(1200) DEFAULT NULL, + `ORCHESTRATION_URI` varchar(256) NOT NULL, + `VNF_COMPONENT_PARAM_XSD` varchar(2048) DEFAULT NULL, + `RECIPE_TIMEOUT` int(11) DEFAULT NULL, + `CREATION_TIMESTAMP` datetime DEFAULT CURRENT_TIMESTAMP, + `VF_MODULE_MODEL_UUID` VARCHAR(200) NULL DEFAULT NULL + ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +INSERT INTO mso_catalog.vcrtemp ( -- <<<2 + id, -- <<<3 + VNF_TYPE, + VNF_COMPONENT_TYPE, + ACTION, + SERVICE_TYPE, + VERSION, + DESCRIPTION, + ORCHESTRATION_URI, + VNF_COMPONENT_PARAM_XSD, + RECIPE_TIMEOUT, + CREATION_TIMESTAMP, + VF_MODULE_MODEL_UUID -- >>>3 +) + SELECT + a.id, -- <<<3 + a.VNF_TYPE, + a.VNF_COMPONENT_TYPE, + a.ACTION, + a.SERVICE_TYPE, + a.VERSION, + a.DESCRIPTION, + a.ORCHESTRATION_URI, + a.VNF_COMPONENT_PARAM_XSD, + a.RECIPE_TIMEOUT, + a.CREATION_TIMESTAMP, + COALESCE(ht1.model_uuid, a.vf_module_id) VF_MODULE_MODEL_UUID -- >>>3 + FROM mso_catalog.vnf_components_recipe a + LEFT JOIN mso_catalog.vftemp ht1 ON a.vf_module_id = CONVERT(ht1.id, CHAR(100)); + +-- DROP vftemp later <<<2 + +DELETE FROM mso_catalog.vnf_components_recipe; -- <<<2 + +ALTER TABLE `mso_catalog`.`vnf_components_recipe` -- <<<2 +CHANGE COLUMN `VF_MODULE_ID` `VF_MODULE_MODEL_UUID` VARCHAR(200) NULL DEFAULT NULL; + +INSERT INTO mso_catalog.vnf_components_recipe SELECT * FROM mso_catalog.vcrtemp; -- <<<2 + +DROP TABLE IF EXISTS mso_catalog.vcrtemp; -- <<<2 + +-- >>>1 + +-- vf_module_to_heat_files AFTER vf_module heat_files ^ <<<1 +CREATE TABLE `mso_catalog`.`vmthftemp` ( -- <<<2 + VF_MODULE_MODEL_UUID VARCHAR(200) NOT NULL, + HEAT_FILES_ARTIFACT_UUID VARCHAR(200) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO mso_catalog.vmthftemp ( -- <<<2 + VF_MODULE_MODEL_UUID, + HEAT_FILES_ARTIFACT_UUID +) + SELECT DISTINCT + ht1.model_uuid, + ht2.artifact_uuid + FROM mso_catalog.vf_module_to_heat_files a + JOIN mso_catalog.vftemp ht1 ON a.vf_module_id = CONVERT(ht1.id, CHAR(100)) + JOIN mso_catalog.heat_files ht2 ON a.HEAT_FILES_ID = ht2.id; + +DROP TABLE IF EXISTS mso_catalog.vftemp; -- <<<2 + +DELETE FROM mso_catalog.vf_module_to_heat_files; -- <<<2 + +ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` -- <<<2 +CHANGE COLUMN `VF_MODULE_ID` `VF_MODULE_MODEL_UUID` VARCHAR(200) NOT NULL , +CHANGE COLUMN `HEAT_FILES_ID` `HEAT_FILES_ARTIFACT_UUID` VARCHAR(200) NOT NULL , +ADD INDEX `fk_vf_module_to_heat_files__heat_files__artifact_uuid1_idx` (`HEAT_FILES_ARTIFACT_UUID` ASC); + +INSERT INTO mso_catalog.vf_module_to_heat_files SELECT * FROM mso_catalog.vmthftemp; -- <<<2 + +DROP TABLE IF EXISTS mso_catalog.vmthftemp; -- <<<2 + +-- >>>1 + +-- S2RC service_to_resource_customizations` <<<1 +CREATE TABLE IF NOT EXISTS `mso_catalog`.`service_to_resource_customizations` ( -- V <<<2 + `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL, + `RESOURCE_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, + `MODEL_TYPE` VARCHAR(20) NOT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + INDEX `fk_service_to_resource_cust__service_model_uuid_idx` (`SERVICE_MODEL_UUID` ASC), + PRIMARY KEY (`SERVICE_MODEL_UUID`, `RESOURCE_MODEL_CUSTOMIZATION_UUID`, `MODEL_TYPE`), + INDEX `fk_service_to_resource_cust__resource_model_customiz_uuid_idx` (`RESOURCE_MODEL_CUSTOMIZATION_UUID` ASC), + CONSTRAINT `fk_service_to_resource_cust__service__model_uuid0` + FOREIGN KEY (`SERVICE_MODEL_UUID`) + REFERENCES `mso_catalog`.`service` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE + ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; + +INSERT INTO mso_catalog.service_to_resource_customizations ( -- 4sc * <<<2 + service_model_uuid, + resource_model_customization_uuid, + model_type +) + SELECT + a.service_model_uuid, + a.network_model_customization_uuid, + "network" + FROM + mso_catalog.service_to_networks a; + +INSERT INTO mso_catalog.service_to_resource_customizations ( -- 5sc * <<<2 + service_model_uuid, + resource_model_customization_uuid, + model_type +) + SELECT + a.service_model_uuid, + a.ar_model_customization_uuid, + "allottedResource" + FROM + mso_catalog.service_to_allotted_resources a; + +INSERT INTO mso_catalog.service_to_resource_customizations ( -- 8a * <<<2 + service_model_uuid, + resource_model_customization_uuid, + model_type +) + SELECT + ht1.model_uuid, + a.model_customization_uuid, + "vnf" + FROM mso_catalog.vnftemp a + JOIN mso_catalog.service AS ht1 ON + a.service_model_invariant_uuid = ht1.model_invariant_uuid AND + a.asdc_service_model_version = ht1.model_version; + +ALTER TABLE `mso_catalog`.`service` -- * <<<2 +DROP COLUMN `SERVICE_VERSION`; + +DROP TABLE IF EXISTS mso_catalog.vnftemp; -- <<<2 + +-- >>>1 + +CREATE TABLE IF NOT EXISTS `mso_catalog`.`tosca_csar` ( -- C <<<1 + `ARTIFACT_UUID` VARCHAR(200) NOT NULL, + `NAME` VARCHAR(200) NOT NULL, + `VERSION` VARCHAR(20) NOT NULL, + `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL, + `ARTIFACT_CHECKSUM` VARCHAR(200) NOT NULL, + `URL` VARCHAR(200) NOT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`ARTIFACT_UUID`)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; +-- >>>1 + +-- 5 aka 8d delete each asdc_uuid except highest ASDC_SERVICE_MODEL_VERSION vnf_resource and cascade vf_module * <<<1 +-- DELETE FROM mso_catalog.vnf_resource WHERE id = ANY(SELECT vnfs FROM mso_catalog.req5temp); +DELETE FROM mso_catalog.vf_module WHERE id = ANY(SELECT vfs FROM mso_catalog.req5temp); +DROP TABLE mso_catalog.req5temp; +-- >>>1 + +DROP TABLE IF EXISTS `mso_catalog`.`service_to_networks` ; -- D <<<1 + +DROP TABLE IF EXISTS `mso_catalog`.`service_to_allotted_resources` ; -- D <<<1 + +-- >>>1 + +-- Drop ID's <<<1 +ALTER TABLE `mso_catalog`.`heat_template` DROP COLUMN `id`; +ALTER TABLE `mso_catalog`.`heat_files` DROP COLUMN `id`; +ALTER TABLE `mso_catalog`.`service` DROP COLUMN `id`; +ALTER TABLE `mso_catalog`.`vnf_resource` DROP COLUMN `id`; +ALTER TABLE `mso_catalog`.`vf_module` DROP COLUMN `id`; +-- >>>1 + +-- FOREIGN KEYS <<<1 +ALTER TABLE `mso_catalog`.`heat_nested_template` -- K <<<2 +ADD CONSTRAINT `fk_heat_nested_template__parent_heat_temp_uuid__heat_template1` + FOREIGN KEY (`PARENT_HEAT_TEMPLATE_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, +ADD CONSTRAINT `fk_heat_nested_template__child_heat_temp_uuid__heat_template1` + FOREIGN KEY (`CHILD_HEAT_TEMPLATE_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`heat_template_params` -- K <<<2 +ADD CONSTRAINT `fk_heat_template_params__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`service` -- K <<<2 +ADD CONSTRAINT `fk_service__tosca_csar1` + FOREIGN KEY (`TOSCA_CSAR_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`tosca_csar` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`service_recipe` -- K <<<2 +ADD CONSTRAINT `fk_service_recipe__service1` + FOREIGN KEY (`SERVICE_MODEL_UUID`) + REFERENCES `mso_catalog`.`service` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`vnf_resource` -- K <<<2 + ADD PRIMARY KEY (`MODEL_UUID`), + DROP COLUMN `TEMPLATE_ID`, +ADD CONSTRAINT `fk_vnf_resource__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`vf_module` -- K <<<2 +ADD PRIMARY KEY (`MODEL_UUID`, `VNF_RESOURCE_MODEL_UUID`), +ADD CONSTRAINT `fk_vf_module__vnf_resource1` + FOREIGN KEY (`VNF_RESOURCE_MODEL_UUID`) + REFERENCES `mso_catalog`.`vnf_resource` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, +ADD CONSTRAINT `fk_vf_module__heat_template_art_uuid__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, +ADD CONSTRAINT `fk_vf_module__vol_heat_template_art_uuid__heat_template2` + FOREIGN KEY (`VOL_HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`vf_module_customization` -- after vf_module K <<<2 + ADD CONSTRAINT `fk_vf_module_customization__vf_module1` + FOREIGN KEY (`VF_MODULE_MODEL_UUID`) + REFERENCES `mso_catalog`.`vf_module` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` -- K <<<2 +ADD CONSTRAINT `fk_vf_module_to_heat_files__heat_files__artifact_uuid1` + FOREIGN KEY (`HEAT_FILES_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_files` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, +ADD CONSTRAINT `fk_vf_module_to_heat_files__vf_module__model_uuid1` + FOREIGN KEY (`VF_MODULE_MODEL_UUID`) + REFERENCES `mso_catalog`.`vf_module` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`allotted_resource_customization` -- K <<<2 +ADD CONSTRAINT `fk_allotted_resource_customization__allotted_resource1` + FOREIGN KEY (`AR_MODEL_UUID`) + REFERENCES `mso_catalog`.`allotted_resource` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`network_resource` -- K <<<2 +ADD CONSTRAINT `fk_network_resource__temp_network_heat_template_lookup__mod_nm1` + FOREIGN KEY (`MODEL_NAME`) + REFERENCES `mso_catalog`.`temp_network_heat_template_lookup` (`NETWORK_RESOURCE_MODEL_NAME`) + ON DELETE NO ACTION + ON UPDATE NO ACTION, +ADD CONSTRAINT `fk_network_resource__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE RESTRICT + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`network_resource_customization` -- K <<<2 +ADD CONSTRAINT `fk_network_resource_customization__network_resource1` + FOREIGN KEY (`NETWORK_RESOURCE_MODEL_UUID`) + REFERENCES `mso_catalog`.`network_resource` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`vnf_resource_customization` -- K <<<2 +ADD CONSTRAINT `fk_vnf_resource_customization__vnf_resource1` + FOREIGN KEY (`VNF_RESOURCE_MODEL_UUID`) + REFERENCES `mso_catalog`.`vnf_resource` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; +-- >>>1 + +-- turn validation back on <<<1 +SET SQL_MODE=@OLD_SQL_MODE; +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; + +COMMIT; +-- >>>1 + +/* +This file uses folds, set by last line. + +While reading this file, lines with the + are folded. + To unfold all: zR + To fold all: zM + +Move cursor to folded line: type in commands... + Toggle folding: za + Recursively: zA + +Vim help about folding +:help fold +*/ +-- vim:foldmarker=<<<,>>>:foldenable:foldmethod=marker diff --git a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgradeScript-1707.41.1_to_1710.44.1.sql b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgradeScript-1707.41.1_to_1710.44.1.sql index 1501cbf655..0c1bc81bf0 100644 --- a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgradeScript-1707.41.1_to_1710.44.1.sql +++ b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgradeScript-1707.41.1_to_1710.44.1.sql @@ -1,261 +1,261 @@ --- MySQL Workbench Synchronization --- Generated: 2017-07-10 12:52 --- Model: New Model --- Version: 1.0 --- Project: Name of the project --- Author: mz1936 - -SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; -SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; -SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; - -ALTER SCHEMA `mso_catalog` DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci ; - -ALTER TABLE `mso_catalog`.`heat_nested_template` - DROP FOREIGN KEY `fk_heat_nested_template__child_heat_temp_uuid__heat_template1`; - -ALTER TABLE `mso_catalog`.`heat_template_params` - DROP FOREIGN KEY `fk_heat_template_params__heat_template1`; - -ALTER TABLE `mso_catalog`.`service_recipe` - DROP FOREIGN KEY `fk_service_recipe__service1`; - -ALTER TABLE `mso_catalog`.`vf_module` - DROP FOREIGN KEY `fk_vf_module__vol_heat_template_art_uuid__heat_template2`, - DROP FOREIGN KEY `fk_vf_module__heat_template_art_uuid__heat_template1`; - -ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` - DROP FOREIGN KEY `fk_vf_module_to_heat_files__heat_files__artifact_uuid1`, - DROP FOREIGN KEY `fk_vf_module_to_heat_files__vf_module__model_uuid1`; - -ALTER TABLE `mso_catalog`.`network_resource` - DROP FOREIGN KEY `fk_network_resource__heat_template1`; - -ALTER TABLE `mso_catalog`.`temp_network_heat_template_lookup` - DROP FOREIGN KEY `fk_temp_network_heat_template_lookup__heat_template1`; - -ALTER TABLE `mso_catalog`.`vf_module_customization` - DROP FOREIGN KEY `fk_vf_module_customization__vol_env__heat_environment2`, - DROP FOREIGN KEY `fk_vf_module_customization__heat_env__heat_environment1`; - -ALTER TABLE `mso_catalog`.`heat_environment` - MODIFY COLUMN `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, - MODIFY COLUMN `BODY` LONGTEXT NOT NULL AFTER `DESCRIPTION`, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`ARTIFACT_UUID`); - -ALTER TABLE `mso_catalog`.`heat_files` - MODIFY COLUMN `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, - MODIFY COLUMN `NAME` VARCHAR(200) NOT NULL AFTER `ARTIFACT_UUID`, - MODIFY COLUMN `BODY` LONGTEXT NOT NULL AFTER `DESCRIPTION`, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`ARTIFACT_UUID`); - -ALTER TABLE `mso_catalog`.`heat_nested_template` - MODIFY COLUMN `PARENT_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL FIRST, - MODIFY COLUMN `CHILD_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL AFTER `PARENT_HEAT_TEMPLATE_UUID`, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`PARENT_HEAT_TEMPLATE_UUID`, `CHILD_HEAT_TEMPLATE_UUID`), - DROP INDEX `fk_heat_nested_template__heat_template2_idx`, - ADD INDEX `fk_heat_nested_template__heat_template2_idx` (`CHILD_HEAT_TEMPLATE_UUID` ASC); - -ALTER TABLE `mso_catalog`.`heat_template` - MODIFY COLUMN `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, - MODIFY COLUMN `NAME` VARCHAR(200) NOT NULL AFTER `ARTIFACT_UUID`, - MODIFY COLUMN `BODY` LONGTEXT NOT NULL AFTER `DESCRIPTION`, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`ARTIFACT_UUID`); - -ALTER TABLE `mso_catalog`.`heat_template_params` - MODIFY COLUMN `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`, `PARAM_NAME`); - -ALTER TABLE `mso_catalog`.`network_recipe` - MODIFY COLUMN `MODEL_NAME` VARCHAR(20) NOT NULL AFTER `id`, - DROP INDEX `UK_rl4f296i0p8lyokxveaiwkayi`, - ADD UNIQUE INDEX `UK_rl4f296i0p8lyokxveaiwkayi` (`MODEL_NAME` ASC, `ACTION` ASC, `VERSION_STR` ASC); - -ALTER TABLE `mso_catalog`.`service` - ADD COLUMN `SERVICE_TYPE` VARCHAR(200) NULL DEFAULT NULL AFTER `DESCRIPTION`, - ADD COLUMN `SERVICE_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `SERVICE_TYPE`, - MODIFY COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, - MODIFY COLUMN `MODEL_NAME` VARCHAR(200) NOT NULL AFTER `MODEL_UUID`, - MODIFY COLUMN `MODEL_VERSION` VARCHAR(20) NOT NULL AFTER `MODEL_INVARIANT_UUID`, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`MODEL_UUID`), - ADD INDEX `fk_service__tosca_csar1_idx` (`TOSCA_CSAR_ARTIFACT_UUID` ASC), - DROP INDEX `fk_service__tosca_csar1_idx`; - -ALTER TABLE `mso_catalog`.`service_recipe` - MODIFY COLUMN `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, - DROP INDEX `fk_service_recipe__service1_idx`, - ADD INDEX `fk_service_recipe__service1_idx` (`SERVICE_MODEL_UUID` ASC), - DROP INDEX `UK_7fav5dkux2v8g9d2i5ymudlgc`, - ADD UNIQUE INDEX `UK_7fav5dkux2v8g9d2i5ymudlgc` (`SERVICE_MODEL_UUID` ASC, `ACTION` ASC); - -ALTER TABLE `mso_catalog`.`vf_module` - MODIFY COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, - MODIFY COLUMN `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `IS_BASE`, - MODIFY COLUMN `VOL_HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `HEAT_TEMPLATE_ARTIFACT_UUID`, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`MODEL_UUID`, `VNF_RESOURCE_MODEL_UUID`), - ADD INDEX `fk_vf_module__heat_template_art_uuid__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), - ADD INDEX `fk_vf_module__vol_heat_template_art_uuid__heat_template2_idx` (`VOL_HEAT_TEMPLATE_ARTIFACT_UUID` ASC), - DROP INDEX `fk_vf_module__vol_heat_template_art_uuid__heat_template2_idx`, - DROP INDEX `fk_vf_module__heat_template_art_uuid__heat_template1_idx`; - -ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` - MODIFY COLUMN `VF_MODULE_MODEL_UUID` VARCHAR(200) NOT NULL FIRST, - MODIFY COLUMN `HEAT_FILES_ARTIFACT_UUID` VARCHAR(200) NOT NULL AFTER `VF_MODULE_MODEL_UUID`, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`VF_MODULE_MODEL_UUID`, `HEAT_FILES_ARTIFACT_UUID`), - DROP INDEX `fk_vf_module_to_heat_files__heat_files__artifact_uuid1_idx`, - ADD INDEX `fk_vf_module_to_heat_files__heat_files__artifact_uuid1_idx` (`HEAT_FILES_ARTIFACT_UUID` ASC), - COMMENT = ''; - -ALTER TABLE `mso_catalog`.`vnf_components_recipe` - MODIFY COLUMN `VF_MODULE_MODEL_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `CREATION_TIMESTAMP`, - CHANGE COLUMN `VERSION` `VERSION` VARCHAR(20) NOT NULL, - DROP INDEX `UK_4dpdwddaaclhc11wxsb7h59ma`, - ADD UNIQUE INDEX `UK_4dpdwddaaclhc11wxsb7h59ma` (`VF_MODULE_MODEL_UUID` ASC, `VNF_COMPONENT_TYPE` ASC, `ACTION` ASC, `VERSION` ASC); - -ALTER TABLE `mso_catalog`.`vnf_resource` - MODIFY COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, - CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL AFTER `TOSCA_NODE_TYPE`, - CHANGE COLUMN `ORCHESTRATION_MODE` `ORCHESTRATION_MODE` VARCHAR(20) NOT NULL DEFAULT 'HEAT' AFTER `DESCRIPTION`, - CHANGE COLUMN `AIC_VERSION_MIN` `AIC_VERSION_MIN` VARCHAR(20) NULL DEFAULT NULL AFTER `ORCHESTRATION_MODE`, - CHANGE COLUMN `AIC_VERSION_MAX` `AIC_VERSION_MAX` VARCHAR(20) NULL DEFAULT NULL AFTER `AIC_VERSION_MIN`, - CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `AIC_VERSION_MAX`, - DROP PRIMARY KEY, - ADD PRIMARY KEY (`MODEL_UUID`), - DROP INDEX `fk_vnf_resource__heat_template1`, - ADD INDEX `fk_vnf_resource__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC); - -ALTER TABLE `mso_catalog`.`allotted_resource_customization` - MODIFY COLUMN `PROVIDING_SERVICE_MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_INSTANCE_NAME`, - MODIFY COLUMN `TARGET_NETWORK_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `PROVIDING_SERVICE_MODEL_INVARIANT_UUID`, - MODIFY COLUMN `NF_NAMING_CODE` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_FUNCTION`, - CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `MAX_INSTANCES`; - -ALTER TABLE `mso_catalog`.`vnf_resource_customization` - MODIFY COLUMN `NF_NAMING_CODE` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_FUNCTION`; - -ALTER TABLE `mso_catalog`.`network_resource` - CHANGE COLUMN `NEUTRON_NETWORK_TYPE` `NEUTRON_NETWORK_TYPE` VARCHAR(20) NULL DEFAULT NULL AFTER `TOSCA_NODE_TYPE`, - CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL AFTER `NEUTRON_NETWORK_TYPE`, - CHANGE COLUMN `HEAT_TEMPLATE_ARTIFACT_UUID` `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, - CHANGE COLUMN `MODEL_INVARIANT_UUID` `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL; - -ALTER TABLE `mso_catalog`.`temp_network_heat_template_lookup` - ADD INDEX `fk_temp_network_heat_template_lookup__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), - DROP INDEX `fk_temp_network_heat_template_lookup__heat_template1_idx`; - -ALTER TABLE `mso_catalog`.`vf_module_customization` - ADD INDEX `fk_vf_module_customization__heat_env__heat_environment1_idx` (`HEAT_ENVIRONMENT_ARTIFACT_UUID` ASC), - ADD INDEX `fk_vf_module_customization__vol_env__heat_environment2_idx` (`VOL_ENVIRONMENT_ARTIFACT_UUID` ASC), - DROP INDEX `fk_vf_module_customization__vol_env__heat_environment2_idx`, - DROP INDEX `fk_vf_module_customization__heat_env__heat_environment1_idx`; - -ALTER TABLE `mso_catalog`.`service_to_resource_customizations` - DROP INDEX `fk_service_to_resource_cust__resource_model_customiz_uuid_idx`; - -ALTER TABLE `mso_catalog`.`heat_nested_template` - DROP FOREIGN KEY `fk_heat_nested_template__parent_heat_temp_uuid__heat_template1`; - -ALTER TABLE `mso_catalog`.`heat_nested_template` - ADD CONSTRAINT `fk_heat_nested_template__parent_heat_temp_uuid__heat_template1` - FOREIGN KEY (`PARENT_HEAT_TEMPLATE_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - ADD CONSTRAINT `fk_heat_nested_template__child_heat_temp_uuid__heat_template1` - FOREIGN KEY (`CHILD_HEAT_TEMPLATE_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`heat_template_params` - ADD CONSTRAINT `fk_heat_template_params__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`service_recipe` - ADD CONSTRAINT `fk_service_recipe__service1` - FOREIGN KEY (`SERVICE_MODEL_UUID`) - REFERENCES `mso_catalog`.`service` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`vf_module` - ADD CONSTRAINT `fk_vf_module__heat_template_art_uuid__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - ADD CONSTRAINT `fk_vf_module__vol_heat_template_art_uuid__heat_template2` - FOREIGN KEY (`VOL_HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` - ADD CONSTRAINT `fk_vf_module_to_heat_files__heat_files__artifact_uuid1` - FOREIGN KEY (`HEAT_FILES_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_files` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - ADD CONSTRAINT `fk_vf_module_to_heat_files__vf_module__model_uuid1` - FOREIGN KEY (`VF_MODULE_MODEL_UUID`) - REFERENCES `mso_catalog`.`vf_module` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`network_resource` - ADD CONSTRAINT `fk_network_resource__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE RESTRICT - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`temp_network_heat_template_lookup` - ADD CONSTRAINT `fk_temp_network_heat_template_lookup__heat_template1` - FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) - ON DELETE RESTRICT - ON UPDATE CASCADE; - -ALTER TABLE `mso_catalog`.`vf_module_customization` - ADD CONSTRAINT `fk_vf_module_customization__heat_env__heat_environment1` - FOREIGN KEY (`HEAT_ENVIRONMENT_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_environment` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - ADD CONSTRAINT `fk_vf_module_customization__vol_env__heat_environment2` - FOREIGN KEY (`VOL_ENVIRONMENT_ARTIFACT_UUID`) - REFERENCES `mso_catalog`.`heat_environment` (`ARTIFACT_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE; - -INSERT INTO mso_catalog.SERVICE_RECIPE (ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT, SERVICE_MODEL_UUID) -VALUES ('activateInstance', '1.0', 'VID_DEFAULT activate', '/mso/async/services/ActivateGenericMacroService', 180, (SELECT model_uuid from mso_catalog.SERVICE where MODEL_NAME = 'VID_DEFAULT')); - -INSERT INTO mso_catalog.SERVICE_RECIPE (ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT, SERVICE_MODEL_UUID) -VALUES ('deactivateInstance', '1.0', 'VID_DEFAULT deactivate', '/mso/async/services/DeactivateGenericMacroService', 180, (SELECT model_uuid from mso_catalog.SERVICE where MODEL_NAME = 'VID_DEFAULT')); - -INSERT INTO mso_catalog.VNF_RECIPE(VNF_TYPE, ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT) -VALUES ('VID_DEFAULT', 'updateInstance', '1', 'VID_DEFAULT update', '/mso/async/services/UpdateVnfInfra', 180); - -INSERT INTO mso_catalog.VNF_RECIPE(VNF_TYPE, ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT) -VALUES ('VID_DEFAULT', 'replaceInstance', '1', 'VID_DEFAULT replace', '/mso/async/services/ReplaceVnfInfra', 180); - -INSERT INTO mso_catalog.VNF_COMPONENTS_RECIPE(VNF_COMPONENT_TYPE, ACTION, VERSION, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT, VF_MODULE_MODEL_UUID) -VALUES ('vfModule', 'replaceInstance', '1', 'VID_DEFAULT vfModule replace', '/mso/async/services/ReplaceVfModuleInfra', 180, 'VID_DEFAULT'); - -ALTER TABLE mso_requests.infra_active_requests modify LAST_MODIFIED_BY VARCHAR(100); - -SET SQL_MODE=@OLD_SQL_MODE; -SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; -SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; +-- MySQL Workbench Synchronization +-- Generated: 2017-07-10 12:52 +-- Model: New Model +-- Version: 1.0 +-- Project: Name of the project +-- Author: mz1936 + +SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; +SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; + +ALTER SCHEMA `mso_catalog` DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci ; + +ALTER TABLE `mso_catalog`.`heat_nested_template` + DROP FOREIGN KEY `fk_heat_nested_template__child_heat_temp_uuid__heat_template1`; + +ALTER TABLE `mso_catalog`.`heat_template_params` + DROP FOREIGN KEY `fk_heat_template_params__heat_template1`; + +ALTER TABLE `mso_catalog`.`service_recipe` + DROP FOREIGN KEY `fk_service_recipe__service1`; + +ALTER TABLE `mso_catalog`.`vf_module` + DROP FOREIGN KEY `fk_vf_module__vol_heat_template_art_uuid__heat_template2`, + DROP FOREIGN KEY `fk_vf_module__heat_template_art_uuid__heat_template1`; + +ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` + DROP FOREIGN KEY `fk_vf_module_to_heat_files__heat_files__artifact_uuid1`, + DROP FOREIGN KEY `fk_vf_module_to_heat_files__vf_module__model_uuid1`; + +ALTER TABLE `mso_catalog`.`network_resource` + DROP FOREIGN KEY `fk_network_resource__heat_template1`; + +ALTER TABLE `mso_catalog`.`temp_network_heat_template_lookup` + DROP FOREIGN KEY `fk_temp_network_heat_template_lookup__heat_template1`; + +ALTER TABLE `mso_catalog`.`vf_module_customization` + DROP FOREIGN KEY `fk_vf_module_customization__vol_env__heat_environment2`, + DROP FOREIGN KEY `fk_vf_module_customization__heat_env__heat_environment1`; + +ALTER TABLE `mso_catalog`.`heat_environment` + MODIFY COLUMN `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, + MODIFY COLUMN `BODY` LONGTEXT NOT NULL AFTER `DESCRIPTION`, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`ARTIFACT_UUID`); + +ALTER TABLE `mso_catalog`.`heat_files` + MODIFY COLUMN `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, + MODIFY COLUMN `NAME` VARCHAR(200) NOT NULL AFTER `ARTIFACT_UUID`, + MODIFY COLUMN `BODY` LONGTEXT NOT NULL AFTER `DESCRIPTION`, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`ARTIFACT_UUID`); + +ALTER TABLE `mso_catalog`.`heat_nested_template` + MODIFY COLUMN `PARENT_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL FIRST, + MODIFY COLUMN `CHILD_HEAT_TEMPLATE_UUID` VARCHAR(200) NOT NULL AFTER `PARENT_HEAT_TEMPLATE_UUID`, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`PARENT_HEAT_TEMPLATE_UUID`, `CHILD_HEAT_TEMPLATE_UUID`), + DROP INDEX `fk_heat_nested_template__heat_template2_idx`, + ADD INDEX `fk_heat_nested_template__heat_template2_idx` (`CHILD_HEAT_TEMPLATE_UUID` ASC); + +ALTER TABLE `mso_catalog`.`heat_template` + MODIFY COLUMN `ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, + MODIFY COLUMN `NAME` VARCHAR(200) NOT NULL AFTER `ARTIFACT_UUID`, + MODIFY COLUMN `BODY` LONGTEXT NOT NULL AFTER `DESCRIPTION`, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`ARTIFACT_UUID`); + +ALTER TABLE `mso_catalog`.`heat_template_params` + MODIFY COLUMN `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL FIRST, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`, `PARAM_NAME`); + +ALTER TABLE `mso_catalog`.`network_recipe` + MODIFY COLUMN `MODEL_NAME` VARCHAR(20) NOT NULL AFTER `id`, + DROP INDEX `UK_rl4f296i0p8lyokxveaiwkayi`, + ADD UNIQUE INDEX `UK_rl4f296i0p8lyokxveaiwkayi` (`MODEL_NAME` ASC, `ACTION` ASC, `VERSION_STR` ASC); + +ALTER TABLE `mso_catalog`.`service` + ADD COLUMN `SERVICE_TYPE` VARCHAR(200) NULL DEFAULT NULL AFTER `DESCRIPTION`, + ADD COLUMN `SERVICE_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `SERVICE_TYPE`, + MODIFY COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, + MODIFY COLUMN `MODEL_NAME` VARCHAR(200) NOT NULL AFTER `MODEL_UUID`, + MODIFY COLUMN `MODEL_VERSION` VARCHAR(20) NOT NULL AFTER `MODEL_INVARIANT_UUID`, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`MODEL_UUID`), + ADD INDEX `fk_service__tosca_csar1_idx` (`TOSCA_CSAR_ARTIFACT_UUID` ASC), + DROP INDEX `fk_service__tosca_csar1_idx`; + +ALTER TABLE `mso_catalog`.`service_recipe` + MODIFY COLUMN `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, + DROP INDEX `fk_service_recipe__service1_idx`, + ADD INDEX `fk_service_recipe__service1_idx` (`SERVICE_MODEL_UUID` ASC), + DROP INDEX `UK_7fav5dkux2v8g9d2i5ymudlgc`, + ADD UNIQUE INDEX `UK_7fav5dkux2v8g9d2i5ymudlgc` (`SERVICE_MODEL_UUID` ASC, `ACTION` ASC); + +ALTER TABLE `mso_catalog`.`vf_module` + MODIFY COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, + MODIFY COLUMN `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `IS_BASE`, + MODIFY COLUMN `VOL_HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `HEAT_TEMPLATE_ARTIFACT_UUID`, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`MODEL_UUID`, `VNF_RESOURCE_MODEL_UUID`), + ADD INDEX `fk_vf_module__heat_template_art_uuid__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), + ADD INDEX `fk_vf_module__vol_heat_template_art_uuid__heat_template2_idx` (`VOL_HEAT_TEMPLATE_ARTIFACT_UUID` ASC), + DROP INDEX `fk_vf_module__vol_heat_template_art_uuid__heat_template2_idx`, + DROP INDEX `fk_vf_module__heat_template_art_uuid__heat_template1_idx`; + +ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` + MODIFY COLUMN `VF_MODULE_MODEL_UUID` VARCHAR(200) NOT NULL FIRST, + MODIFY COLUMN `HEAT_FILES_ARTIFACT_UUID` VARCHAR(200) NOT NULL AFTER `VF_MODULE_MODEL_UUID`, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`VF_MODULE_MODEL_UUID`, `HEAT_FILES_ARTIFACT_UUID`), + DROP INDEX `fk_vf_module_to_heat_files__heat_files__artifact_uuid1_idx`, + ADD INDEX `fk_vf_module_to_heat_files__heat_files__artifact_uuid1_idx` (`HEAT_FILES_ARTIFACT_UUID` ASC), + COMMENT = ''; + +ALTER TABLE `mso_catalog`.`vnf_components_recipe` + MODIFY COLUMN `VF_MODULE_MODEL_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `CREATION_TIMESTAMP`, + CHANGE COLUMN `VERSION` `VERSION` VARCHAR(20) NOT NULL, + DROP INDEX `UK_4dpdwddaaclhc11wxsb7h59ma`, + ADD UNIQUE INDEX `UK_4dpdwddaaclhc11wxsb7h59ma` (`VF_MODULE_MODEL_UUID` ASC, `VNF_COMPONENT_TYPE` ASC, `ACTION` ASC, `VERSION` ASC); + +ALTER TABLE `mso_catalog`.`vnf_resource` + MODIFY COLUMN `MODEL_UUID` VARCHAR(200) NOT NULL FIRST, + CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL AFTER `TOSCA_NODE_TYPE`, + CHANGE COLUMN `ORCHESTRATION_MODE` `ORCHESTRATION_MODE` VARCHAR(20) NOT NULL DEFAULT 'HEAT' AFTER `DESCRIPTION`, + CHANGE COLUMN `AIC_VERSION_MIN` `AIC_VERSION_MIN` VARCHAR(20) NULL DEFAULT NULL AFTER `ORCHESTRATION_MODE`, + CHANGE COLUMN `AIC_VERSION_MAX` `AIC_VERSION_MAX` VARCHAR(20) NULL DEFAULT NULL AFTER `AIC_VERSION_MIN`, + CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `AIC_VERSION_MAX`, + DROP PRIMARY KEY, + ADD PRIMARY KEY (`MODEL_UUID`), + DROP INDEX `fk_vnf_resource__heat_template1`, + ADD INDEX `fk_vnf_resource__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC); + +ALTER TABLE `mso_catalog`.`allotted_resource_customization` + MODIFY COLUMN `PROVIDING_SERVICE_MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_INSTANCE_NAME`, + MODIFY COLUMN `TARGET_NETWORK_ROLE` VARCHAR(200) NULL DEFAULT NULL AFTER `PROVIDING_SERVICE_MODEL_INVARIANT_UUID`, + MODIFY COLUMN `NF_NAMING_CODE` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_FUNCTION`, + CHANGE COLUMN `CREATION_TIMESTAMP` `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `MAX_INSTANCES`; + +ALTER TABLE `mso_catalog`.`vnf_resource_customization` + MODIFY COLUMN `NF_NAMING_CODE` VARCHAR(200) NULL DEFAULT NULL AFTER `NF_FUNCTION`; + +ALTER TABLE `mso_catalog`.`network_resource` + CHANGE COLUMN `NEUTRON_NETWORK_TYPE` `NEUTRON_NETWORK_TYPE` VARCHAR(20) NULL DEFAULT NULL AFTER `TOSCA_NODE_TYPE`, + CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` VARCHAR(1200) NULL DEFAULT NULL AFTER `NEUTRON_NETWORK_TYPE`, + CHANGE COLUMN `HEAT_TEMPLATE_ARTIFACT_UUID` `HEAT_TEMPLATE_ARTIFACT_UUID` VARCHAR(200) NOT NULL AFTER `CREATION_TIMESTAMP`, + CHANGE COLUMN `MODEL_INVARIANT_UUID` `MODEL_INVARIANT_UUID` VARCHAR(200) NULL DEFAULT NULL; + +ALTER TABLE `mso_catalog`.`temp_network_heat_template_lookup` + ADD INDEX `fk_temp_network_heat_template_lookup__heat_template1_idx` (`HEAT_TEMPLATE_ARTIFACT_UUID` ASC), + DROP INDEX `fk_temp_network_heat_template_lookup__heat_template1_idx`; + +ALTER TABLE `mso_catalog`.`vf_module_customization` + ADD INDEX `fk_vf_module_customization__heat_env__heat_environment1_idx` (`HEAT_ENVIRONMENT_ARTIFACT_UUID` ASC), + ADD INDEX `fk_vf_module_customization__vol_env__heat_environment2_idx` (`VOL_ENVIRONMENT_ARTIFACT_UUID` ASC), + DROP INDEX `fk_vf_module_customization__vol_env__heat_environment2_idx`, + DROP INDEX `fk_vf_module_customization__heat_env__heat_environment1_idx`; + +ALTER TABLE `mso_catalog`.`service_to_resource_customizations` + DROP INDEX `fk_service_to_resource_cust__resource_model_customiz_uuid_idx`; + +ALTER TABLE `mso_catalog`.`heat_nested_template` + DROP FOREIGN KEY `fk_heat_nested_template__parent_heat_temp_uuid__heat_template1`; + +ALTER TABLE `mso_catalog`.`heat_nested_template` + ADD CONSTRAINT `fk_heat_nested_template__parent_heat_temp_uuid__heat_template1` + FOREIGN KEY (`PARENT_HEAT_TEMPLATE_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + ADD CONSTRAINT `fk_heat_nested_template__child_heat_temp_uuid__heat_template1` + FOREIGN KEY (`CHILD_HEAT_TEMPLATE_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`heat_template_params` + ADD CONSTRAINT `fk_heat_template_params__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`service_recipe` + ADD CONSTRAINT `fk_service_recipe__service1` + FOREIGN KEY (`SERVICE_MODEL_UUID`) + REFERENCES `mso_catalog`.`service` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`vf_module` + ADD CONSTRAINT `fk_vf_module__heat_template_art_uuid__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + ADD CONSTRAINT `fk_vf_module__vol_heat_template_art_uuid__heat_template2` + FOREIGN KEY (`VOL_HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`vf_module_to_heat_files` + ADD CONSTRAINT `fk_vf_module_to_heat_files__heat_files__artifact_uuid1` + FOREIGN KEY (`HEAT_FILES_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_files` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + ADD CONSTRAINT `fk_vf_module_to_heat_files__vf_module__model_uuid1` + FOREIGN KEY (`VF_MODULE_MODEL_UUID`) + REFERENCES `mso_catalog`.`vf_module` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`network_resource` + ADD CONSTRAINT `fk_network_resource__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE RESTRICT + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`temp_network_heat_template_lookup` + ADD CONSTRAINT `fk_temp_network_heat_template_lookup__heat_template1` + FOREIGN KEY (`HEAT_TEMPLATE_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_template` (`ARTIFACT_UUID`) + ON DELETE RESTRICT + ON UPDATE CASCADE; + +ALTER TABLE `mso_catalog`.`vf_module_customization` + ADD CONSTRAINT `fk_vf_module_customization__heat_env__heat_environment1` + FOREIGN KEY (`HEAT_ENVIRONMENT_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_environment` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + ADD CONSTRAINT `fk_vf_module_customization__vol_env__heat_environment2` + FOREIGN KEY (`VOL_ENVIRONMENT_ARTIFACT_UUID`) + REFERENCES `mso_catalog`.`heat_environment` (`ARTIFACT_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE; + +INSERT INTO mso_catalog.SERVICE_RECIPE (ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT, SERVICE_MODEL_UUID) +VALUES ('activateInstance', '1.0', 'VID_DEFAULT activate', '/mso/async/services/ActivateGenericMacroService', 180, (SELECT model_uuid from mso_catalog.SERVICE where MODEL_NAME = 'VID_DEFAULT')); + +INSERT INTO mso_catalog.SERVICE_RECIPE (ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT, SERVICE_MODEL_UUID) +VALUES ('deactivateInstance', '1.0', 'VID_DEFAULT deactivate', '/mso/async/services/DeactivateGenericMacroService', 180, (SELECT model_uuid from mso_catalog.SERVICE where MODEL_NAME = 'VID_DEFAULT')); + +INSERT INTO mso_catalog.VNF_RECIPE(VNF_TYPE, ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT) +VALUES ('VID_DEFAULT', 'updateInstance', '1', 'VID_DEFAULT update', '/mso/async/services/UpdateVnfInfra', 180); + +INSERT INTO mso_catalog.VNF_RECIPE(VNF_TYPE, ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT) +VALUES ('VID_DEFAULT', 'replaceInstance', '1', 'VID_DEFAULT replace', '/mso/async/services/ReplaceVnfInfra', 180); + +INSERT INTO mso_catalog.VNF_COMPONENTS_RECIPE(VNF_COMPONENT_TYPE, ACTION, VERSION, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT, VF_MODULE_MODEL_UUID) +VALUES ('vfModule', 'replaceInstance', '1', 'VID_DEFAULT vfModule replace', '/mso/async/services/ReplaceVfModuleInfra', 180, 'VID_DEFAULT'); + +ALTER TABLE mso_requests.infra_active_requests modify LAST_MODIFIED_BY VARCHAR(100); + +SET SQL_MODE=@OLD_SQL_MODE; +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; diff --git a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_data_load_1710.46.1_to_1802.48.1.sql b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_data_load_1710.46.1_to_1802.48.1.sql index d15d254464..d89795ba92 100644 --- a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_data_load_1710.46.1_to_1802.48.1.sql +++ b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_data_load_1710.46.1_to_1802.48.1.sql @@ -1,18 +1,18 @@ --- MSO-817 Insert new vnf_recipe records for "inPlaceSoftwareUpdate" and "applyUpdatedConfig" actions for VID_DEFAULT --- ----------------------------------------------------------- -SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; -SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; -SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; --- - -INSERT INTO mso_catalog.VNF_RECIPE ( - VNF_TYPE, ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT -) VALUES - ('VID_DEFAULT', 'inPlaceSoftwareUpdate', '1', 'VID_DEFAULT inPlaceSoftwareUpdate', '/mso/async/services/VnfInPlaceUpdate', 180), - ('VID_DEFAULT', 'applyUpdatedConfig', '1', 'VID_DEFAULT applyUpdatedConfig', '/mso/async/services/VnfConfigUpdate', 180); - --- -SET SQL_MODE=@OLD_SQL_MODE; -SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; -SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; +-- MSO-817 Insert new vnf_recipe records for "inPlaceSoftwareUpdate" and "applyUpdatedConfig" actions for VID_DEFAULT +-- ----------------------------------------------------------- +SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; +SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; +-- + +INSERT INTO mso_catalog.VNF_RECIPE ( + VNF_TYPE, ACTION, VERSION_STR, DESCRIPTION, ORCHESTRATION_URI, RECIPE_TIMEOUT +) VALUES + ('VID_DEFAULT', 'inPlaceSoftwareUpdate', '1', 'VID_DEFAULT inPlaceSoftwareUpdate', '/mso/async/services/VnfInPlaceUpdate', 180), + ('VID_DEFAULT', 'applyUpdatedConfig', '1', 'VID_DEFAULT applyUpdatedConfig', '/mso/async/services/VnfConfigUpdate', 180); + +-- +SET SQL_MODE=@OLD_SQL_MODE; +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; -- \ No newline at end of file diff --git a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_schema_upgrade_1710.46.1_to_1802.48.1.sql b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_schema_upgrade_1710.46.1_to_1802.48.1.sql index 26650bc4ab..ef6ec9eea7 100644 --- a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_schema_upgrade_1710.46.1_to_1802.48.1.sql +++ b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_schema_upgrade_1710.46.1_to_1802.48.1.sql @@ -1,22 +1,22 @@ --- MSO-1224 Add 2 new allottedResource columns in Catalog DB and return in catalog db adapter - AND - --- MSO-670 To support new ACTION value of "inPlaceSoftwareUpdate" --- increase ACTION column length to varchar(50) in all *_RECIPE tables in catalog db. --- ------------------------------------------------------------- -SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; -SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; -SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; --- - -ALTER TABLE `mso_catalog`.`network_recipe` CHANGE COLUMN `ACTION` `ACTION` VARCHAR(50) NOT NULL ; -ALTER TABLE `mso_catalog`.`service_recipe` CHANGE COLUMN `ACTION` `ACTION` VARCHAR(50) NOT NULL ; -ALTER TABLE `mso_catalog`.`vnf_components_recipe` CHANGE COLUMN `ACTION` `ACTION` VARCHAR(50) NOT NULL ; -ALTER TABLE `mso_catalog`.`vnf_recipe` CHANGE COLUMN `ACTION` `ACTION` VARCHAR(50) NOT NULL ; - -ALTER TABLE `mso_catalog`.`allotted_resource_customization` - ADD COLUMN `PROVIDING_SERVICE_MODEL_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_INSTANCE_NAME`, - ADD COLUMN `PROVIDING_SERVICE_MODEL_NAME` VARCHAR(200) NULL DEFAULT NULL AFTER `PROVIDING_SERVICE_MODEL_INVARIANT_UUID`; - --- -SET SQL_MODE=@OLD_SQL_MODE; -SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +-- MSO-1224 Add 2 new allottedResource columns in Catalog DB and return in catalog db adapter - AND - +-- MSO-670 To support new ACTION value of "inPlaceSoftwareUpdate" +-- increase ACTION column length to varchar(50) in all *_RECIPE tables in catalog db. +-- ------------------------------------------------------------- +SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; +SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; +-- + +ALTER TABLE `mso_catalog`.`network_recipe` CHANGE COLUMN `ACTION` `ACTION` VARCHAR(50) NOT NULL ; +ALTER TABLE `mso_catalog`.`service_recipe` CHANGE COLUMN `ACTION` `ACTION` VARCHAR(50) NOT NULL ; +ALTER TABLE `mso_catalog`.`vnf_components_recipe` CHANGE COLUMN `ACTION` `ACTION` VARCHAR(50) NOT NULL ; +ALTER TABLE `mso_catalog`.`vnf_recipe` CHANGE COLUMN `ACTION` `ACTION` VARCHAR(50) NOT NULL ; + +ALTER TABLE `mso_catalog`.`allotted_resource_customization` + ADD COLUMN `PROVIDING_SERVICE_MODEL_UUID` VARCHAR(200) NULL DEFAULT NULL AFTER `MODEL_INSTANCE_NAME`, + ADD COLUMN `PROVIDING_SERVICE_MODEL_NAME` VARCHAR(200) NULL DEFAULT NULL AFTER `PROVIDING_SERVICE_MODEL_INVARIANT_UUID`; + +-- +SET SQL_MODE=@OLD_SQL_MODE; +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; \ No newline at end of file diff --git a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_requests_schema_upgrade_1710.46.1_to_1802.48.1.sql b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_requests_schema_upgrade_1710.46.1_to_1802.48.1.sql index 6ac3fdfb92..a467fbafd5 100644 --- a/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_requests_schema_upgrade_1710.46.1_to_1802.48.1.sql +++ b/packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_requests_schema_upgrade_1710.46.1_to_1802.48.1.sql @@ -1,100 +1,100 @@ --- MSO-816 mso_requests DB changes to support tenant isolation --- ----------------------------------------------------------- -SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; -SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; -SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; --- - -ALTER TABLE `mso_requests`.`infra_active_requests` - ADD COLUMN `OPERATIONAL_ENV_ID` VARCHAR(45) NULL DEFAULT NULL AFTER `CONFIGURATION_NAME`, - ADD COLUMN `OPERATIONAL_ENV_NAME` VARCHAR(200) NULL DEFAULT NULL AFTER `OPERATIONAL_ENV_ID`, - CHANGE COLUMN `REQUEST_SCOPE` `REQUEST_SCOPE` VARCHAR(50) NOT NULL; - --- - -DROP TABLE IF EXISTS `mso_requests`.`activate_operational_env_per_distributionid_status`; -DROP TABLE IF EXISTS `mso_requests`.`activate_operational_env_service_model_distribution_status`; -DROP TABLE IF EXISTS `mso_requests`.`watchdog_distributionid_status`; -DROP TABLE IF EXISTS `mso_requests`.`watchdog_per_component_distribution_status`; -DROP TABLE IF EXISTS `mso_requests`.`watchdog_service_mod_ver_id_lookup`; - --- ----------------------------------------------------- --- Table `mso_requests`.`activate_operational_env_service_model_distribution_status` --- ----------------------------------------------------- -CREATE TABLE `mso_requests`.`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) NULL, - `RECOVERY_ACTION` VARCHAR(30) NULL, - `RETRY_COUNT_LEFT` INT(11) NULL, - `WORKLOAD_CONTEXT` VARCHAR(80) NOT NULL, - `CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - `MODIFY_TIME` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (`OPERATIONAL_ENV_ID`, `SERVICE_MODEL_VERSION_ID`, `REQUEST_ID`)) -ENGINE = InnoDB; - --- ----------------------------------------------------- --- Table `mso_requests`.`activate_operational_env_per_distributionid_status` --- ----------------------------------------------------- -CREATE TABLE `mso_requests`.`activate_operational_env_per_distributionid_status` ( - `DISTRIBUTION_ID` VARCHAR(45) NOT NULL, - `DISTRIBUTION_ID_STATUS` VARCHAR(45) NULL, - `DISTRIBUTION_ID_ERROR_REASON` VARCHAR(250) NULL, - `CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - `MODIFY_TIME` DATETIME NULL 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`), - INDEX `fk_activate_op_env_per_distributionid_status__aoesmds1_idx` (`OPERATIONAL_ENV_ID` ASC, `SERVICE_MODEL_VERSION_ID` ASC, `REQUEST_ID` ASC), - CONSTRAINT `fk_activate_op_env_per_distributionid_status__aoesmds1` - FOREIGN KEY (`OPERATIONAL_ENV_ID` , `SERVICE_MODEL_VERSION_ID` , `REQUEST_ID`) - REFERENCES `mso_requests`.`activate_operational_env_service_model_distribution_status` (`OPERATIONAL_ENV_ID` , `SERVICE_MODEL_VERSION_ID` , `REQUEST_ID`) - ON DELETE CASCADE - ON UPDATE CASCADE) -ENGINE = InnoDB; - --- ----------------------------------------------------- --- Table `mso_requests`.`watchdog_distributionid_status` --- ----------------------------------------------------- -CREATE TABLE `mso_requests`.`watchdog_distributionid_status` ( - `DISTRIBUTION_ID` VARCHAR(45) NOT NULL, - `DISTRIBUTION_ID_STATUS` VARCHAR(45) NULL, - `CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - `MODIFY_TIME` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (`DISTRIBUTION_ID`)) -ENGINE = InnoDB; - --- ----------------------------------------------------- --- Table `mso_requests`.`watchdog_per_component_distribution_status` --- ----------------------------------------------------- -CREATE TABLE `mso_requests`.`watchdog_per_component_distribution_status` ( - `DISTRIBUTION_ID` VARCHAR(45) NOT NULL, - `COMPONENT_NAME` VARCHAR(45) NOT NULL, - `COMPONENT_DISTRIBUTION_STATUS` VARCHAR(45) NULL, - `CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - `MODIFY_TIME` DATETIME NULL 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 `mso_requests`.`watchdog_distributionid_status` (`DISTRIBUTION_ID`) - ON DELETE CASCADE - ON UPDATE CASCADE) -ENGINE = InnoDB; - --- ----------------------------------------------------- --- Table `mso_requests`.`watchdog_service_mod_ver_id_lookup` --- ----------------------------------------------------- -CREATE TABLE `mso_requests`.`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, - PRIMARY KEY (`DISTRIBUTION_ID`)) -ENGINE = InnoDB; - --- -SET SQL_MODE=@OLD_SQL_MODE; -SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; -SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; +-- MSO-816 mso_requests DB changes to support tenant isolation +-- ----------------------------------------------------------- +SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; +SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; +-- + +ALTER TABLE `mso_requests`.`infra_active_requests` + ADD COLUMN `OPERATIONAL_ENV_ID` VARCHAR(45) NULL DEFAULT NULL AFTER `CONFIGURATION_NAME`, + ADD COLUMN `OPERATIONAL_ENV_NAME` VARCHAR(200) NULL DEFAULT NULL AFTER `OPERATIONAL_ENV_ID`, + CHANGE COLUMN `REQUEST_SCOPE` `REQUEST_SCOPE` VARCHAR(50) NOT NULL; + +-- + +DROP TABLE IF EXISTS `mso_requests`.`activate_operational_env_per_distributionid_status`; +DROP TABLE IF EXISTS `mso_requests`.`activate_operational_env_service_model_distribution_status`; +DROP TABLE IF EXISTS `mso_requests`.`watchdog_distributionid_status`; +DROP TABLE IF EXISTS `mso_requests`.`watchdog_per_component_distribution_status`; +DROP TABLE IF EXISTS `mso_requests`.`watchdog_service_mod_ver_id_lookup`; + +-- ----------------------------------------------------- +-- Table `mso_requests`.`activate_operational_env_service_model_distribution_status` +-- ----------------------------------------------------- +CREATE TABLE `mso_requests`.`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) NULL, + `RECOVERY_ACTION` VARCHAR(30) NULL, + `RETRY_COUNT_LEFT` INT(11) NULL, + `WORKLOAD_CONTEXT` VARCHAR(80) NOT NULL, + `CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`OPERATIONAL_ENV_ID`, `SERVICE_MODEL_VERSION_ID`, `REQUEST_ID`)) +ENGINE = InnoDB; + +-- ----------------------------------------------------- +-- Table `mso_requests`.`activate_operational_env_per_distributionid_status` +-- ----------------------------------------------------- +CREATE TABLE `mso_requests`.`activate_operational_env_per_distributionid_status` ( + `DISTRIBUTION_ID` VARCHAR(45) NOT NULL, + `DISTRIBUTION_ID_STATUS` VARCHAR(45) NULL, + `DISTRIBUTION_ID_ERROR_REASON` VARCHAR(250) NULL, + `CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` DATETIME NULL 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`), + INDEX `fk_activate_op_env_per_distributionid_status__aoesmds1_idx` (`OPERATIONAL_ENV_ID` ASC, `SERVICE_MODEL_VERSION_ID` ASC, `REQUEST_ID` ASC), + CONSTRAINT `fk_activate_op_env_per_distributionid_status__aoesmds1` + FOREIGN KEY (`OPERATIONAL_ENV_ID` , `SERVICE_MODEL_VERSION_ID` , `REQUEST_ID`) + REFERENCES `mso_requests`.`activate_operational_env_service_model_distribution_status` (`OPERATIONAL_ENV_ID` , `SERVICE_MODEL_VERSION_ID` , `REQUEST_ID`) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB; + +-- ----------------------------------------------------- +-- Table `mso_requests`.`watchdog_distributionid_status` +-- ----------------------------------------------------- +CREATE TABLE `mso_requests`.`watchdog_distributionid_status` ( + `DISTRIBUTION_ID` VARCHAR(45) NOT NULL, + `DISTRIBUTION_ID_STATUS` VARCHAR(45) NULL, + `CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`DISTRIBUTION_ID`)) +ENGINE = InnoDB; + +-- ----------------------------------------------------- +-- Table `mso_requests`.`watchdog_per_component_distribution_status` +-- ----------------------------------------------------- +CREATE TABLE `mso_requests`.`watchdog_per_component_distribution_status` ( + `DISTRIBUTION_ID` VARCHAR(45) NOT NULL, + `COMPONENT_NAME` VARCHAR(45) NOT NULL, + `COMPONENT_DISTRIBUTION_STATUS` VARCHAR(45) NULL, + `CREATE_TIME` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `MODIFY_TIME` DATETIME NULL 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 `mso_requests`.`watchdog_distributionid_status` (`DISTRIBUTION_ID`) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB; + +-- ----------------------------------------------------- +-- Table `mso_requests`.`watchdog_service_mod_ver_id_lookup` +-- ----------------------------------------------------- +CREATE TABLE `mso_requests`.`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, + PRIMARY KEY (`DISTRIBUTION_ID`)) +ENGINE = InnoDB; + +-- +SET SQL_MODE=@OLD_SQL_MODE; +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; -- \ No newline at end of file -- cgit 1.2.3-korg