From c31f3567a46ec467cf0f296e2df59e6aa949635c Mon Sep 17 00:00:00 2001 From: "Determe, Sebastien (sd378r)" Date: Mon, 5 Nov 2018 13:47:07 +0100 Subject: [PATCH] Update the SQL Update SQL to preload CLamp mariadb Issue-ID: CLAMP-244 Change-Id: Ie7ed263acdc2ff492aced81b4109690e94d503a3 Signed-off-by: Determe, Sebastien (sd378r) --- .../bulkload/clds-create-db-objects.sql | 47 +++++++++++++ .../bulkload/clds-stored-procedures.sql | 78 +++++++++++++++++++++- .../drop/clds-drop-db-objects.sql | 6 ++ 3 files changed, 130 insertions(+), 1 deletion(-) diff --git a/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/bulkload/clds-create-db-objects.sql b/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/bulkload/clds-create-db-objects.sql index 308ec7da62..8bb0214f6e 100644 --- a/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/bulkload/clds-create-db-objects.sql +++ b/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/bulkload/clds-create-db-objects.sql @@ -129,6 +129,53 @@ CREATE TABLE clds_service_cache ( PRIMARY KEY (invariant_service_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; +CREATE TABLE IF NOT EXISTS tosca_model ( + tosca_model_id VARCHAR(36) NOT NULL, + tosca_model_name VARCHAR(80) NOT NULL, + policy_type VARCHAR(80) NULL, + user_id VARCHAR(80), + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (tosca_model_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +CREATE TABLE IF NOT EXISTS tosca_model_revision ( + tosca_model_revision_id VARCHAR(36) NOT NULL, + tosca_model_id VARCHAR(36) NOT NULL, + version DOUBLE NOT NULL DEFAULT 1, + tosca_model_yaml MEDIUMTEXT NULL, + tosca_model_json MEDIUMTEXT NULL, + user_id VARCHAR(80), + createdTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + lastUpdatedTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (tosca_model_revision_id), + CONSTRAINT tosca_model_revision_ukey UNIQUE KEY (tosca_model_id, version), + CONSTRAINT tosca_model_revision_fkey01 FOREIGN KEY (tosca_model_id) REFERENCES tosca_model (tosca_model_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +CREATE TABLE IF NOT EXISTS dictionary ( + dictionary_id VARCHAR(36) NOT NULL, + dictionary_name VARCHAR(80) NOT NULL, + created_by VARCHAR(80), + modified_by VARCHAR(80), + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (dictionary_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + +CREATE TABLE IF NOT EXISTS dictionary_elements ( + dict_element_id VARCHAR(36) NOT NULL, + dictionary_id VARCHAR(36) NOT NULL, + dict_element_name VARCHAR(250) NOT NULL, + dict_element_short_name VARCHAR(80) NOT NULL, + dict_element_description VARCHAR(250), + dict_element_type VARCHAR(80) NOT NULL, + created_by VARCHAR(80), + modified_by VARCHAR(80), + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (dict_element_id), + CONSTRAINT dictionary_elements_ukey UNIQUE KEY (dict_element_name, dict_element_short_name), + CONSTRAINT dictionary_elements_ukey_fkey01 FOREIGN KEY (dictionary_id) REFERENCES dictionary (dictionary_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; + ALTER TABLE template ADD CONSTRAINT template_bpmn_id_fkey01 FOREIGN KEY (template_bpmn_id) diff --git a/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/bulkload/clds-stored-procedures.sql b/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/bulkload/clds-stored-procedures.sql index fb131cc371..f35d9da573 100644 --- a/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/bulkload/clds-stored-procedures.sql +++ b/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/bulkload/clds-stored-procedures.sql @@ -29,6 +29,11 @@ DROP PROCEDURE IF EXISTS get_model; DROP PROCEDURE IF EXISTS get_model_template; DROP PROCEDURE IF EXISTS set_template; DROP PROCEDURE IF EXISTS get_template; +DROP PROCEDURE IF EXISTS del_model; +DROP PROCEDURE IF EXISTS set_new_tosca_model_version; +DROP PROCEDURE IF EXISTS set_tosca_model; +DROP PROCEDURE IF EXISTS set_dictionary; +DROP PROCEDURE IF EXISTS set_dictionary_elements; DELIMITER // CREATE PROCEDURE get_template (IN v_template_name VARCHAR(80), @@ -459,6 +464,77 @@ BEGIN UPDATE event SET process_instance_id = v_process_instance_id WHERE event_id = v_event_id; -END +END; +CREATE PROCEDURE del_model +(IN v_model_name VARCHAR(80)) +BEGIN + DECLARE v_model_id VARCHAR(36); + SELECT model_id INTO v_model_id from model where model_name = v_model_name; + UPDATE model set event_id = null, model_blueprint_id = null, model_prop_id = null where model_id = v_model_id; + DELETE from event where model_id = v_model_id; + DELETE from model_blueprint where model_id = v_model_id; + DELETE from model_properties where model_id = v_model_id; + DELETE from model where model_id = v_model_id; +END; + +CREATE PROCEDURE set_new_tosca_model_version + (IN v_tosca_model_id VARCHAR(36), + IN v_version DOUBLE, + IN v_tosca_model_yaml MEDIUMTEXT, + IN v_tosca_model_json MEDIUMTEXT, + IN v_user_id VARCHAR(80), + OUT v_revision_id VARCHAR(36)) +BEGIN + SET v_revision_id = UUID(); + INSERT INTO tosca_model_revision + (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id) + VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id); +END; + +CREATE PROCEDURE set_tosca_model + (IN v_tosca_model_name VARCHAR(80), + IN v_policy_type VARCHAR(80), + IN v_user_id VARCHAR(80), + IN v_tosca_model_yaml MEDIUMTEXT, + IN v_tosca_model_json MEDIUMTEXT, + IN v_version DOUBLE, + OUT v_tosca_model_id VARCHAR(36), + OUT v_revision_id VARCHAR(36)) +BEGIN + SET v_tosca_model_id = UUID(); + INSERT INTO tosca_model + (tosca_model_id, tosca_model_name, policy_type, user_id) + VALUES (v_tosca_model_id, v_tosca_model_name, v_policy_type, v_user_id); + SET v_revision_id = UUID(); + INSERT INTO tosca_model_revision + (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id) + VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id); +END; + +CREATE PROCEDURE set_dictionary + (IN v_dictionary_name VARCHAR(80), + IN v_user_id VARCHAR(80), + OUT v_dictionary_id VARCHAR(36)) +BEGIN + SET v_dictionary_id = UUID(); + INSERT INTO dictionary + (dictionary_id, dictionary_name, created_by, modified_by) + VALUES (v_dictionary_id, v_dictionary_name, v_user_id, v_user_id); +END; + +CREATE PROCEDURE set_dictionary_elements + (IN v_dictionary_id VARCHAR(36), + IN v_dict_element_name VARCHAR(250), + IN v_dict_element_short_name VARCHAR(80), + IN v_dict_element_description VARCHAR(250), + IN v_dict_element_type VARCHAR(80), + IN v_user_id VARCHAR(80), + OUT v_dict_element_id VARCHAR(36)) +BEGIN + SET v_dict_element_id = UUID(); + INSERT INTO dictionary_elements + (dict_element_id, dictionary_id, dict_element_name, dict_element_short_name, dict_element_description, dict_element_type, created_by, modified_by) + VALUES (v_dict_element_id, v_dictionary_id, v_dict_element_name, v_dict_element_short_name, v_dict_element_description, v_dict_element_type, v_user_id, v_user_id); +END; // DELIMITER ; diff --git a/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/drop/clds-drop-db-objects.sql b/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/drop/clds-drop-db-objects.sql index eec9d52160..0412373d01 100644 --- a/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/drop/clds-drop-db-objects.sql +++ b/kubernetes/clamp/charts/mariadb/resources/config/mariadb/docker-entrypoint-initdb.d/drop/clds-drop-db-objects.sql @@ -46,3 +46,9 @@ DROP TABLE template_doc; DROP TABLE template_image; DROP TABLE template_bpmn; DROP TABLE template; + +DROP TABLE dictionary_elements; +DROP TABLE dictionary; +DROP TABLE tosca_model_revision; +DROP TABLE tosca_model; + -- 2.16.6