Change Management Schedule Optimization

Adding Database Project for Change Management Schedule Optimization

Change-Id: I03370ea4d80ed0444f986b8c7e9e32dbad206106
Issue-ID: OPTFRA-351
Signed-off-by: RamaPrasad Amaranarayana (ra5425) <ra5425@att.com>

4 files changed, 392 insertions, 0 deletions

diff --git a/cmso-database/src/main/java/org/onap/optf/cmso/liquibase/LiquibaseApplication.java b/cmso-database/src/main/java/org/onap/optf/cmso/liquibase/LiquibaseApplication.java
new file mode 100644
index 0000000..e3a581d
--- /dev/null
+++ b/cmso-database/src/main/java/org/onap/optf/cmso/liquibase/LiquibaseApplication.java
@@ -0,0 +1,47 @@
+/*

+ * Copyright © 2017-2018 AT&T Intellectual Property.

+ * Modifications Copyright © 2018 IBM.

+ * 

+ * Licensed under the Apache License, Version 2.0 (the "License");

+ * you may not use this file except in compliance with the License.

+ * You may obtain a copy of the License at

+ * 

+ *         http://www.apache.org/licenses/LICENSE-2.0

+ * 

+ * Unless required by applicable law or agreed to in writing, software

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+ * 

+ * 

+ * Unless otherwise specified, all documentation contained herein is licensed

+ * under the Creative Commons License, Attribution 4.0 Intl. (the "License");

+ * you may not use this documentation except in compliance with the License.

+ * You may obtain a copy of the License at

+ * 

+ *         https://creativecommons.org/licenses/by/4.0/

+ * 

+ * Unless required by applicable law or agreed to in writing, documentation

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+*/

+

+package org.onap.optf.cmso.liquibase;

+

+import org.springframework.boot.SpringApplication;

+import org.springframework.boot.autoconfigure.EnableAutoConfiguration;

+import org.springframework.boot.autoconfigure.SpringBootApplication;

+import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

+import org.springframework.context.annotation.ComponentScan;

+

+@SpringBootApplication

+@ComponentScan(basePackages = "org.onap.optf.cmso.liquibase")

+public class LiquibaseApplication {

+

+    public static void main(String[] args) {

+        SpringApplication.run(LiquibaseApplication.class, args);

+    }

+}

diff --git a/cmso-database/src/main/java/org/onap/optf/cmso/liquibase/LiquibaseData.java b/cmso-database/src/main/java/org/onap/optf/cmso/liquibase/LiquibaseData.java
new file mode 100644
index 0000000..842c70c
--- /dev/null
+++ b/cmso-database/src/main/java/org/onap/optf/cmso/liquibase/LiquibaseData.java
@@ -0,0 +1,77 @@
+/*

+ * Copyright © 2017-2018 AT&T Intellectual Property.

+ * Modifications Copyright © 2018 IBM.

+ * 

+ * Licensed under the Apache License, Version 2.0 (the "License");

+ * you may not use this file except in compliance with the License.

+ * You may obtain a copy of the License at

+ * 

+ *         http://www.apache.org/licenses/LICENSE-2.0

+ * 

+ * Unless required by applicable law or agreed to in writing, software

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+ * 

+ * 

+ * Unless otherwise specified, all documentation contained herein is licensed

+ * under the Creative Commons License, Attribution 4.0 Intl. (the "License");

+ * you may not use this documentation except in compliance with the License.

+ * You may obtain a copy of the License at

+ * 

+ *         https://creativecommons.org/licenses/by/4.0/

+ * 

+ * Unless required by applicable law or agreed to in writing, documentation

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+*/

+

+package org.onap.optf.cmso.liquibase;

+

+import javax.sql.DataSource;

+import org.springframework.beans.factory.annotation.Value;

+import org.springframework.boot.context.properties.ConfigurationProperties;

+import org.springframework.boot.jdbc.DataSourceBuilder;

+import org.springframework.context.annotation.Bean;

+import org.springframework.context.annotation.Configuration;

+import org.springframework.stereotype.Component;

+import liquibase.integration.spring.SpringLiquibase;

+

+/*

+ * Make sure following exceptions before build:

+ * Build errors

+ * java.net.ConnectException: Connection refused: connect (DB Connection)

+ * Make sure SQL files syntax are correct

+ * Always place new SQL files in src\main\resources\dbchangelog folder

+ * Please dont delete previous scripts

+ * Make sure to follow naming convention for SQL file (1806-cmso-v2-schema.sql)

+ * Please make sure to give author name and id for new SQL scripts (eg: changeSet author="bg4702"

+ * id="unique id")

+ ** IMPORTANT: Always do testing in local before pushing changes to Env's**

+ */

+

+@Component

+@Configuration

+public class LiquibaseData {

+

+    @Value("${changeLogFile}")

+    private String changeLogFile;

+

+    @Bean

+    public SpringLiquibase liquibase() {

+        SpringLiquibase liquibase = new SpringLiquibase();

+        liquibase.setDataSource(dataSource());

+        liquibase.setChangeLog("classpath:" + changeLogFile);

+        return liquibase;

+    }

+

+    @Bean

+    @ConfigurationProperties(prefix = "spring.datasource")

+    public DataSource dataSource() {

+        return DataSourceBuilder.create().build();

+    }

+

+}

diff --git a/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1-schema.sql b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1-schema.sql
new file mode 100644
index 0000000..4a99994
--- /dev/null
+++ b/cmso-database/src/main/resources/cmso-dbchangelog/onap-cmso-v1-schema.sql
@@ -0,0 +1,220 @@
+
+CREATE DATABASE IF NOT EXISTS CMSCHEDULER;
+USE CMSCHEDULER;
+
+CREATE TABLE IF NOT EXISTS DOMAINS (
+    domain VARCHAR(256) NOT NULL,
+    CONSTRAINT PK_DOMAINS PRIMARY KEY (domain)
+)  ENGINE=INNODB;
+
+CREATE UNIQUE INDEX DOMAIN_UNIQUE ON DOMAINS (domain ASC);
+
+
+-- -----------------------------------------------------
+-- Table SCHEDULES
+-- -----------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS SCHEDULES (
+    id INT NOT NULL AUTO_INCREMENT,
+    domain VARCHAR(256) NOT NULL,
+    schedule_id VARCHAR(256) NULL,
+    schedule_name VARCHAR(256) NULL,
+    user_id VARCHAR(45) NULL,
+    status VARCHAR(45) NOT NULL,
+    create_date_time BIGINT NULL,
+    schedule_info MEDIUMTEXT NOT NULL,
+    schedule MEDIUMTEXT NULL,
+    optimizer_status VARCHAR(45) NULL,
+    optimizer_message MEDIUMTEXT NULL,
+    optimizer_date_time BIGINT NULL,
+    optimizer_return_date_time BIGINT NULL,
+    optimizer_attempts_to_schedule INT NOT NULL DEFAULT 0,
+    optimizer_transaction_id VARCHAR(128) NULL,
+    delete_date_time BIGINT NULL,
+    CONSTRAINT PK_SCHEDULES PRIMARY KEY (id),
+    CONSTRAINT FK_SCHEDULES_DOMAIN FOREIGN KEY (domain)
+        REFERENCES DOMAINS (domain)
+        ON DELETE NO ACTION ON UPDATE CASCADE
+)  ENGINE=INNODB;
+CREATE UNIQUE INDEX SCHEDULE_KEY ON SCHEDULES (domain ASC, schedule_id ASC);
+
+CREATE UNIQUE INDEX OPTIMIZER_TRANSACTION_ID_UNIQUE ON SCHEDULES (optimizer_transaction_id ASC);
+
+
+-- -----------------------------------------------------
+-- Table SCHEDULE_EVENTS
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS SCHEDULE_EVENTS (
+    id INT(11) NOT NULL AUTO_INCREMENT,
+    schedules_id INT(11) NOT NULL,
+    event_time BIGINT(20) NOT NULL,
+    reminder_time BIGINT(20) NOT NULL,
+    domain VARCHAR(45) NULL DEFAULT NULL,
+    event_text MEDIUMTEXT NULL DEFAULT NULL,
+    status VARCHAR(45) NULL DEFAULT NULL,
+    CONSTRAINT PK_SCHEDULE_EVENTS PRIMARY KEY (id),
+    CONSTRAINT FK_SCHEDULE_EVENTS_SCHEDULES FOREIGN KEY (schedules_id)
+        REFERENCES SCHEDULES (id)
+        ON DELETE NO ACTION ON UPDATE NO ACTION
+)  ENGINE=INNODB;
+
+CREATE INDEX FK_SCHEDULE_EVENTS_SCHEDULES_IDX ON SCHEDULE_EVENTS (schedules_id ASC);
+
+CREATE INDEX SEQ_SVENTS ON SCHEDULE_EVENTS (reminder_time ASC);
+
+
+
+-- -----------------------------------------------------
+-- Table DOMAIN_DATA
+-- -----------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS DOMAIN_DATA (
+    id INT NOT NULL AUTO_INCREMENT,
+    schedules_id INT NOT NULL,
+    name VARCHAR(128) NULL,
+    value MEDIUMTEXT NULL,
+    CONSTRAINT PK_DOMAIN_DATA PRIMARY KEY (id),
+    CONSTRAINT FK_DOMAIN_DATA_SCHEDULES FOREIGN KEY (schedules_id)
+        REFERENCES SCHEDULES (id)
+        ON DELETE NO ACTION ON UPDATE NO ACTION
+)  ENGINE=INNODB;
+
+CREATE UNIQUE INDEX ID_UNIQUE ON DOMAIN_DATA (id ASC);
+
+CREATE INDEX FK_DOMAIN_DATA_SCHEDULES_IDX ON DOMAIN_DATA (schedules_id ASC);
+
+
+-- -----------------------------------------------------
+-- Table APPROVAL_TYPES
+-- -----------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS APPROVAL_TYPES (
+    id INT NOT NULL AUTO_INCREMENT,
+    domain VARCHAR(256) NOT NULL,
+    approval_type VARCHAR(45) NOT NULL,
+    approval_count INT NOT NULL DEFAULT 1,
+    CONSTRAINT PK_APPROVAL_TYPES PRIMARY KEY (id),
+    CONSTRAINT FK_APPROVAL_TYPES_DOMAIN FOREIGN KEY (domain)
+        REFERENCES DOMAINS (domain)
+        ON DELETE NO ACTION ON UPDATE CASCADE
+)  ENGINE=INNODB; 
+CREATE INDEX FK_DOMAIN_IDX ON APPROVAL_TYPES (domain ASC);
+
+
+-- -----------------------------------------------------
+-- Table SCHEDULE_APPROVALS
+-- -----------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS SCHEDULE_APPROVALS (
+    id INT NOT NULL AUTO_INCREMENT,
+    schedules_id INT NOT NULL,
+    user_id VARCHAR(45) NOT NULL,
+    approval_type_id INT NOT NULL,
+    status VARCHAR(45) NOT NULL DEFAULT 'Pending Approval',
+    approval_date_time BIGINT NULL,
+    CONSTRAINT PK_SCHEDULE_APPROVALS PRIMARY KEY (id),
+    CONSTRAINT FK_SCHEDULE_APPROVALS_SCHEDULES FOREIGN KEY (schedules_id)
+        REFERENCES SCHEDULES (id)
+        ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT FK_SCHEDULE_APPROVALS_APPROVAL_TYPES FOREIGN KEY (approval_type_id)
+        REFERENCES APPROVAL_TYPES (id)
+        ON DELETE NO ACTION ON UPDATE NO ACTION
+)  ENGINE=INNODB;
+
+CREATE INDEX FK_SCHEDULE_APPROVALS_SCHEDULES_IDX ON SCHEDULE_APPROVALS (schedules_id ASC);
+
+CREATE INDEX FK_APPROVAL_TYPES_IDX ON SCHEDULE_APPROVALS (approval_type_id ASC);
+
+
+-- -----------------------------------------------------
+-- Table CHANGE_MANAGEMENT_GROUPS
+-- -----------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS CHANGE_MANAGEMENT_GROUPS (
+    id INT NOT NULL AUTO_INCREMENT,
+    group_id VARCHAR(45) NULL,
+    schedules_id INT NULL,
+    start_time BIGINT NULL,
+    finish_time BIGINT NULL,
+    last_instance_start_time BIGINT NULL,
+    normal_duration_in_secs INT NULL,
+    additional_duration_in_secs INT NULL,
+    concurrency_limit INT NULL,
+    policy_id VARCHAR(256) NULL,
+    CONSTRAINT PK_CHANGE_MANAGEMENT_GROUPS PRIMARY KEY (id),
+    CONSTRAINT FK_CHANGE_MANAGEMENT_GROUPS_SCHEDULES FOREIGN KEY (schedules_id)
+        REFERENCES SCHEDULES (id)
+        ON DELETE NO ACTION ON UPDATE NO ACTION
+)  ENGINE=INNODB;
+
+CREATE INDEX FK_SCHEDULES_IDX ON CHANGE_MANAGEMENT_GROUPS (schedules_id ASC);
+
+CREATE UNIQUE INDEX CHANGE_MANAGEMENT_GROUP_ID_UNIQUE ON CHANGE_MANAGEMENT_GROUPS (schedules_id ASC, group_id ASC);
+
+
+-- -----------------------------------------------------
+-- Table CHANGE_MANAGEMENT_SCHEDULES
+-- -----------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS CHANGE_MANAGEMENT_SCHEDULES (
+    id INT NOT NULL AUTO_INCREMENT,
+    change_management_groups_id INT NULL,
+    vnf_name VARCHAR(256) NOT NULL,
+    vnf_id VARCHAR(256) NULL,
+    status VARCHAR(45) NOT NULL,
+    start_time BIGINT NULL,
+    finish_time BIGINT NULL,
+    mso_request_id VARCHAR(45) NULL,
+    mso_status VARCHAR(45) NULL,
+    mso_message MEDIUMTEXT NULL,
+    mso_time BIGINT NULL,
+    dispatcher_instance VARCHAR(128) NULL,
+    dispatch_time BIGINT NULL,
+    execution_completed_time BIGINT NULL,
+    status_message MEDIUMTEXT NULL,
+    tm_change_id VARCHAR(15) NULL,
+    tm_approval_status VARCHAR(45) NULL,
+    tm_status VARCHAR(45) NULL,
+    CONSTRAINT PK_CHANGE_MANAGEMENT_SCHEDULES PRIMARY KEY (id),
+    CONSTRAINT FK_CHANGE_MANAGEMENT_SCHEDULES_CHANGE_MANAGEMENT_GROUP FOREIGN KEY (change_management_groups_id)
+        REFERENCES CHANGE_MANAGEMENT_GROUPS (id)
+        ON DELETE NO ACTION ON UPDATE NO ACTION
+)  ENGINE=INNODB;
+
+CREATE INDEX FK_CHANGE_MANAGEMENT_GROUP_CHANGE_MANAGEMENT_SCHEDULES_IDX ON CHANGE_MANAGEMENT_SCHEDULES (change_management_groups_id ASC);
+
+CREATE UNIQUE INDEX CHANGE_MANAGEMENT_SCHEDULES_VNF_NAME_UNIQUE ON CHANGE_MANAGEMENT_SCHEDULES (change_management_groups_id ASC, vnf_name ASC);
+
+
+-- -----------------------------------------------------
+-- Table CHANGE_MANAGEMENT_CHANGE_WINDOWS
+-- -----------------------------------------------------
+
+CREATE TABLE IF NOT EXISTS CHANGE_MANAGEMENT_CHANGE_WINDOWS (
+    id INT NOT NULL AUTO_INCREMENT,
+    change_management_groups_id INT NOT NULL,
+    start_time BIGINT NULL,
+    finish_time BIGINT NULL,
+    CONSTRAINT PK_CHANGE_MANAGEMENT_CHANGE_WINDOWS PRIMARY KEY (id),
+    CONSTRAINT FK_CHANGE_WINDOWS_CHANGE_MANAGEMENT_GROUPS1 FOREIGN KEY (change_management_groups_id)
+        REFERENCES CHANGE_MANAGEMENT_GROUPS (id)
+        ON DELETE NO ACTION ON UPDATE NO ACTION
+)  ENGINE=INNODB;
+
+CREATE INDEX FK_CHANGE_WINDOWS_CHANGE_MANAGEMENT_GROUPS1_IDX on CHANGE_MANAGEMENT_CHANGE_WINDOWS (change_management_groups_id ASC);
+
+
+
+
+-- comment SQL_SAFE_UPDATES code in dev setup (testing)
+-- uncomment in IT, IST, E2E Env's 
+SET SQL_SAFE_UPDATES = 0;
+
+DELETE FROM DOMAINS;
+
+INSERT INTO DOMAINS (`domain`) VALUES ('ChangeManagement');
+
+
+DELETE FROM APPROVAL_TYPES;
+
+INSERT INTO APPROVAL_TYPES (`domain`, `approval_type`, `approval_count`) VALUES ('ChangeManagement', 'Tier 2', '1');
\ No newline at end of file
diff --git a/cmso-database/src/main/resources/cmso-liquibase-changeLog.xml b/cmso-database/src/main/resources/cmso-liquibase-changeLog.xml
new file mode 100644
index 0000000..0bc23c1
--- /dev/null
+++ b/cmso-database/src/main/resources/cmso-liquibase-changeLog.xml
@@ -0,0 +1,48 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>

+<!--

+  Copyright © 2017-2018 AT&T Intellectual Property.

+  Modifications Copyright © 2018 IBM.

+  

+  Licensed under the Apache License, Version 2.0 (the "License");

+  you may not use this file except in compliance with the License.

+  You may obtain a copy of the License at

+  

+          http://www.apache.org/licenses/LICENSE-2.0

+  

+  Unless required by applicable law or agreed to in writing, software

+  distributed under the License is distributed on an "AS IS" BASIS,

+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+  See the License for the specific language governing permissions and

+  limitations under the License.

+  

+  

+  Unless otherwise specified, all documentation contained herein is licensed

+  under the Creative Commons License, Attribution 4.0 Intl. (the "License");

+  you may not use this documentation except in compliance with the License.

+  You may obtain a copy of the License at

+  

+          https://creativecommons.org/licenses/by/4.0/

+  

+  Unless required by applicable law or agreed to in writing, documentation

+  distributed under the License is distributed on an "AS IS" BASIS,

+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+  See the License for the specific language governing permissions and

+  limitations under the License.

+-->

+

+<databaseChangeLog

+	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"

+	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"

+	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

+	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">

+

+	<changeSet author="ATT" id="cmsched-v1-schema1">

+		<preConditions onFail="MARK_RAN">

+			<not>

+				<tableExists schemaName="cmso" tableName="schedules" />

+			</not>

+		</preConditions>

+		<sqlFile path="cmso-dbchangelog/onap-cmso-v1-schema.sql" />

+	</changeSet>

+

+</databaseChangeLog>