aboutsummaryrefslogtreecommitdiffstats
path: root/packages/root-pack-extras
diff options
context:
space:
mode:
authorBenjamin, Max (mb388a) <mb388a@us.att.com>2018-07-30 15:56:09 -0400
committerBenjamin, Max (mb388a) <mb388a@us.att.com>2018-07-31 11:09:25 -0400
commit5a6a6de6f1a26a1897e4917a0df613e25a24eb70 (patch)
tree59a968f27b4b603aacc9d5e7b51fb598aeec5321 /packages/root-pack-extras
parentb6dc38501f3b746426b42d9de4cc883d894149e8 (diff)
Containerization feature of SO
Change-Id: I95381232eeefcd247a66a5cec370a8ce1c288e18 Issue-ID: SO-670 Signed-off-by: Benjamin, Max (mb388a) <mb388a@us.att.com>
Diffstat (limited to 'packages/root-pack-extras')
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/.gitignore1
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8.0-ee.sql1226
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mariadb_engine_7.8_patch_7.8.0_to_7.8.2.sql7
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/camunda/mysql_create_camunda_admin.sql18
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_add_constraints.sql (renamed from packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_add_constraints.sql)0
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_timestamp_mso_db.sql (renamed from packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalog_timestamp_mso_db.sql)0
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/site_status_updated_timestamp.sql (renamed from packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/site_status_updated_timestamp.sql)0
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V2.10__UpdateNorthboundRequestToUseInstance.sql24
-rw-r--r--packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/sub-sql-files/catalogdb/V3.0__UpdateOrchFlowTableWithATTFlows.sql16
-rw-r--r--packages/root-pack-extras/config-resources/mysql/db-sql-scripts/camunda/archive_mariadb_camunda_tables.sql1330
-rw-r--r--packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1702.37_drop1_to_1707.40_drop1.sql58
-rw-r--r--packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgrade-1707.40_drop1_to_1707.41_drop1.sql2700
-rw-r--r--packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB-upgradeScript-1707.41.1_to_1710.44.1.sql522
-rw-r--r--packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_data_load_1710.46.1_to_1802.48.1.sql34
-rw-r--r--packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_catalog_schema_upgrade_1710.46.1_to_1802.48.1.sql42
-rw-r--r--packages/root-pack-extras/config-resources/mysql/db-sql-scripts/upgrade/MariaDB_onap_mso_requests_schema_upgrade_1710.46.1_to_1802.48.1.sql198
16 files changed, 3725 insertions, 2451 deletions
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/sub-sql-files/catalog_add_constraints.sql b/packages/root-pack-extras/config-resources/mariadb/db-sql-scripts/old-scripts/catalog_add_constraints.sql
index a5e9834de2..a5e9834de2 100644
--- 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/old-scripts/catalog_add_constraints.sql
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/old-scripts/catalog_timestamp_mso_db.sql
index bc88adcc70..bc88adcc70 100644
--- 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/old-scripts/catalog_timestamp_mso_db.sql
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/old-scripts/site_status_updated_timestamp.sql
index 3b2de4c0b7..3b2de4c0b7 100644
--- 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/old-scripts/site_status_updated_timestamp.sql
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/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 <db_name>;
-
-/*
-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 <db_name>;
-
-
-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 <db_name>;
+
+/*
+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 <db_name>;
+
+
+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