diff options
author | sebdet <sebastien.determe@intl.att.com> | 2019-09-10 12:25:47 +0200 |
---|---|---|
committer | sebdet <sebastien.determe@intl.att.com> | 2019-09-10 12:25:47 +0200 |
commit | f0d7a9645a2b773df5d278d3858ce36a61f23645 (patch) | |
tree | 69052ec34191afd65f8f52d6e2fc1fe289d820d0 /extra/sql/bulkload/clds-stored-procedures.sql | |
parent | 0387f232b4a56e98092519ccc2e0ee7985f6701c (diff) |
Remove dead code
Removal of deprecated v1 interface code, sql cleaned up as well, old
model removed from database
Issue-ID: CLAMP-487
Change-Id: I4a9f14de9ea1c1b76c522581d75cf4b011c2aa01
Signed-off-by: sebdet <sebastien.determe@intl.att.com>
Diffstat (limited to 'extra/sql/bulkload/clds-stored-procedures.sql')
-rw-r--r-- | extra/sql/bulkload/clds-stored-procedures.sql | 534 |
1 files changed, 0 insertions, 534 deletions
diff --git a/extra/sql/bulkload/clds-stored-procedures.sql b/extra/sql/bulkload/clds-stored-procedures.sql deleted file mode 100644 index b48e86afe..000000000 --- a/extra/sql/bulkload/clds-stored-procedures.sql +++ /dev/null @@ -1,534 +0,0 @@ -# -# CLDS stored procedures -# - -USE cldsdb4; - -DROP PROCEDURE IF EXISTS upd_event; -DROP PROCEDURE IF EXISTS ins_event; -DROP PROCEDURE IF EXISTS del_all_model_instances; -DROP PROCEDURE IF EXISTS del_model_instance; -DROP PROCEDURE IF EXISTS ins_model_instance; -DROP PROCEDURE IF EXISTS set_model; -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), - OUT v_template_id VARCHAR(36), - OUT v_template_bpmn_id VARCHAR(36), - OUT v_template_bpmn_user_id VARCHAR(80), - OUT v_template_bpmn_text MEDIUMTEXT, - OUT v_template_image_id VARCHAR(36), - OUT v_template_image_user_id VARCHAR(80), - OUT v_template_image_text MEDIUMTEXT, - OUT v_template_doc_id VARCHAR(36), - OUT v_template_doc_user_id VARCHAR(80), - OUT v_template_doc_text MEDIUMTEXT) -BEGIN - SELECT t.template_id, - tb.template_bpmn_id, - tb.user_id, - tb.template_bpmn_text, - ti.template_image_id, - ti.user_id, - ti.template_image_text, - td.template_doc_id, - td.user_id, - td.template_doc_text - INTO v_template_id, - v_template_bpmn_id, - v_template_bpmn_user_id, - v_template_bpmn_text, - v_template_image_id, - v_template_image_user_id, - v_template_image_text, - v_template_doc_id, - v_template_doc_user_id, - v_template_doc_text - FROM template t, - template_bpmn tb, - template_image ti, - template_doc td - WHERE t.template_bpmn_id = tb.template_bpmn_id - AND t.template_image_id = ti.template_image_id - AND t.template_doc_id = td.template_doc_id - AND t.template_name = v_template_name; -END; -CREATE PROCEDURE set_template - (IN v_template_name VARCHAR(80), - IN v_user_id VARCHAR(80), - IN v_template_bpmn_text MEDIUMTEXT, - IN v_template_image_text MEDIUMTEXT, - IN v_template_doc_text MEDIUMTEXT, - OUT v_template_id VARCHAR(36), - OUT v_template_bpmn_id VARCHAR(36), - OUT v_template_bpmn_user_id VARCHAR(80), - OUT v_template_image_id VARCHAR(36), - OUT v_template_image_user_id VARCHAR(80), - OUT v_template_doc_id VARCHAR(36), - OUT v_template_doc_user_id VARCHAR(80)) -BEGIN - DECLARE v_old_template_bpmn_text MEDIUMTEXT; - DECLARE v_old_template_image_text MEDIUMTEXT; - DECLARE v_old_template_doc_text MEDIUMTEXT; - SET v_template_id = NULL; - CALL get_template( - v_template_name, - v_template_id, - v_template_bpmn_id, - v_template_bpmn_user_id, - v_old_template_bpmn_text, - v_template_image_id, - v_template_image_user_id, - v_old_template_image_text, - v_template_doc_id, - v_template_doc_user_id, - v_old_template_doc_text); - IF v_template_id IS NULL THEN - BEGIN - SET v_template_id = UUID(); - INSERT INTO template - (template_id, template_name) - VALUES (v_template_id, v_template_name); - END; - END IF; - IF v_template_bpmn_id IS NULL OR v_template_bpmn_text <> v_old_template_bpmn_text THEN - SET v_template_bpmn_id = UUID(); - INSERT INTO template_bpmn - (template_bpmn_id, template_id, template_bpmn_text, user_id) - VALUES (v_template_bpmn_id, v_template_id, v_template_bpmn_text, v_user_id); - SET v_template_bpmn_user_id = v_user_id; - END IF; - IF v_template_image_id IS NULL OR v_template_image_text <> v_old_template_image_text THEN - SET v_template_image_id = UUID(); - INSERT INTO template_image - (template_image_id, template_id, template_image_text, user_id) - VALUES (v_template_image_id, v_template_id, v_template_image_text, v_user_id); - SET v_template_image_user_id = v_user_id; - END IF; - IF v_template_doc_id IS NULL OR v_template_doc_text <> v_old_template_doc_text THEN - SET v_template_doc_id = UUID(); - INSERT INTO template_doc - (template_doc_id, template_id, template_doc_text, user_id) - VALUES (v_template_doc_id, v_template_id, v_template_doc_text, v_user_id); - SET v_template_doc_user_id = v_user_id; - END IF; - UPDATE template - SET template_bpmn_id = v_template_bpmn_id, - template_image_id = v_template_image_id, - template_doc_id = v_template_doc_id - WHERE template_id = v_template_id; -END; -CREATE PROCEDURE get_model - (IN v_model_name VARCHAR(80), - OUT v_control_name_prefix VARCHAR(80), - INOUT v_control_name_uuid VARCHAR(36), - OUT v_model_id VARCHAR(36), - OUT v_service_type_id VARCHAR(80), - OUT v_deployment_id VARCHAR(80), - OUT v_deployment_status_url VARCHAR(300), - OUT v_template_name VARCHAR(80), - OUT v_template_id VARCHAR(36), - OUT v_model_prop_id VARCHAR(36), - OUT v_model_prop_user_id VARCHAR(80), - OUT v_model_prop_text MEDIUMTEXT, - OUT v_model_blueprint_id VARCHAR(36), - OUT v_model_blueprint_user_id VARCHAR(80), - OUT v_model_blueprint_text MEDIUMTEXT, - OUT v_event_id VARCHAR(36), - OUT v_action_cd VARCHAR(80), - OUT v_action_state_cd VARCHAR(80), - OUT v_event_process_instance_id VARCHAR(80), - OUT v_event_user_id VARCHAR(80)) -BEGIN - SELECT m.control_name_prefix, - m.control_name_uuid, - m.model_id, - m.service_type_id, - m.deployment_id, - m.deployment_status_url, - t.template_name, - m.template_id, - mp.model_prop_id, - mp.user_id, - mp.model_prop_text, - mb.model_blueprint_id, - mb.user_id, - mb.model_blueprint_text, - e.event_id, - e.action_cd, - e.action_state_cd, - e.process_instance_id, - e.user_id - INTO v_control_name_prefix, - v_control_name_uuid, - v_model_id, - v_service_type_id, - v_deployment_id, - v_deployment_status_url, - v_template_name, - v_template_id, - v_model_prop_id, - v_model_prop_user_id, - v_model_prop_text, - v_model_blueprint_id, - v_model_blueprint_user_id, - v_model_blueprint_text, - v_event_id, - v_action_cd, - v_action_state_cd, - v_event_process_instance_id, - v_event_user_id - FROM model m, - template t, - model_properties mp, - model_blueprint mb, - event e - WHERE m.template_id = t.template_id - AND m.model_prop_id = mp.model_prop_id - AND m.model_blueprint_id = mb.model_blueprint_id - AND m.event_id = e.event_id - AND (m.model_name = v_model_name - OR m.control_name_uuid = v_control_name_uuid); - SELECT model_instance_id, - vm_name, - location, - timestamp - FROM model_instance - WHERE model_id = v_model_id - ORDER BY 2; -END; -CREATE PROCEDURE get_model_template - (IN v_model_name VARCHAR(80), - OUT v_control_name_prefix VARCHAR(80), - INOUT v_control_name_uuid VARCHAR(36), - OUT v_model_id VARCHAR(36), - OUT v_service_type_id VARCHAR(80), - OUT v_deployment_id VARCHAR(80), - OUT v_deployment_status_url VARCHAR(300), - OUT v_template_name VARCHAR(80), - OUT v_template_id VARCHAR(36), - OUT v_model_prop_id VARCHAR(36), - OUT v_model_prop_user_id VARCHAR(80), - OUT v_model_prop_text MEDIUMTEXT, - OUT v_model_blueprint_id VARCHAR(36), - OUT v_model_blueprint_user_id VARCHAR(80), - OUT v_model_blueprint_text MEDIUMTEXT, - OUT v_template_bpmn_id VARCHAR(36), - OUT v_template_bpmn_user_id VARCHAR(80), - OUT v_template_bpmn_text MEDIUMTEXT, - OUT v_template_image_id VARCHAR(36), - OUT v_template_image_user_id VARCHAR(80), - OUT v_template_image_text MEDIUMTEXT, - OUT v_template_doc_id VARCHAR(36), - OUT v_template_doc_user_id VARCHAR(80), - OUT v_template_doc_text MEDIUMTEXT, - OUT v_event_id VARCHAR(36), - OUT v_action_cd VARCHAR(80), - OUT v_action_state_cd VARCHAR(80), - OUT v_event_process_instance_id VARCHAR(80), - OUT v_event_user_id VARCHAR(80)) -BEGIN - CALL get_model( - v_model_name, - v_control_name_prefix, - v_control_name_uuid, - v_model_id, - v_service_type_id, - v_deployment_id, - v_deployment_status_url, - v_template_name, - v_template_id, - v_model_prop_id, - v_model_prop_user_id, - v_model_prop_text, - v_model_blueprint_id, - v_model_blueprint_user_id, - v_model_blueprint_text, - v_event_id, - v_action_cd, - v_action_state_cd, - v_event_process_instance_id, - v_event_user_id); - CALL get_template( - v_template_name, - v_template_id, - v_template_bpmn_id, - v_template_bpmn_user_id, - v_template_bpmn_text, - v_template_image_id, - v_template_image_user_id, - v_template_image_text, - v_template_doc_id, - v_template_doc_user_id, - v_template_doc_text); - END; -CREATE PROCEDURE set_model - (IN v_model_name VARCHAR(80), - IN v_template_id VARCHAR(36), - IN v_user_id VARCHAR(80), - IN v_model_prop_text MEDIUMTEXT, - IN v_model_blueprint_text MEDIUMTEXT, - IN v_service_type_id VARCHAR(80), - IN v_deployment_id VARCHAR(80), - IN v_deployment_status_url VARCHAR(300), - INOUT v_control_name_prefix VARCHAR(80), - INOUT v_control_name_uuid VARCHAR(36), - OUT v_model_id VARCHAR(36), - OUT v_model_prop_id VARCHAR(36), - OUT v_model_prop_user_id VARCHAR(80), - OUT v_model_blueprint_id VARCHAR(36), - OUT v_model_blueprint_user_id VARCHAR(80), - OUT v_event_id VARCHAR(36), - OUT v_action_cd VARCHAR(80), - OUT v_action_state_cd VARCHAR(80), - OUT v_event_process_instance_id VARCHAR(80), - OUT v_event_user_id VARCHAR(80)) -BEGIN - DECLARE v_old_template_name VARCHAR(80); - DECLARE v_old_template_id VARCHAR(36); - DECLARE v_old_control_name_prefix VARCHAR(80); - DECLARE v_old_control_name_uuid VARCHAR(36); - DECLARE v_old_model_prop_text MEDIUMTEXT; - DECLARE v_old_model_blueprint_text MEDIUMTEXT; - DECLARE v_old_service_type_id VARCHAR(80); - DECLARE v_old_deployment_id VARCHAR(80); - DECLARE v_old_deployment_status_url VARCHAR(300); - SET v_model_id = NULL; - CALL get_model( - v_model_name, - v_old_control_name_prefix, - v_old_control_name_uuid, - v_model_id, - v_old_service_type_id, - v_old_deployment_id, - v_old_deployment_status_url, - v_old_template_name, - v_old_template_id, - v_model_prop_id, - v_model_prop_user_id, - v_old_model_prop_text, - v_model_blueprint_id, - v_model_blueprint_user_id, - v_old_model_blueprint_text, - v_event_id, - v_action_cd, - v_action_state_cd, - v_event_process_instance_id, - v_event_user_id); - IF v_model_id IS NULL THEN - BEGIN - # UUID can be provided initially but cannot be updated - # if not provided (this is expected) then it will be set here - IF v_control_name_uuid IS NULL THEN - SET v_control_name_uuid = UUID(); - END IF; - SET v_model_id = v_control_name_uuid; - INSERT INTO model - (model_id, model_name, template_id, control_name_prefix, control_name_uuid, service_type_id, deployment_id, deployment_status_url) - VALUES (v_model_id, v_model_name, v_template_id, v_control_name_prefix, v_control_name_uuid, v_service_type_id, v_deployment_id,v_deployment_status_url); - # since just created model, insert CREATED event as initial default event - SET v_action_cd = 'CREATE'; - SET v_action_state_cd = 'COMPLETED'; - SET v_event_user_id = v_user_id; - SET v_event_id = UUID(); - INSERT INTO event - (event_id, model_id, action_cd, action_state_cd, user_id) - VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_user_id); - UPDATE model - SET event_id = v_event_id - WHERE model_id = v_model_id; - END; - ELSE - BEGIN - # use old control_name_prefix if null value is provided - IF v_control_name_prefix IS NULL THEN - SET v_control_name_prefix = v_old_control_name_prefix; - END IF; - # UUID can not be updated after initial insert - SET v_control_name_uuid = v_old_control_name_uuid; - END; - END IF; - IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN - SET v_model_prop_id = UUID(); - INSERT INTO model_properties - (model_prop_id, model_id, model_prop_text, user_id) - VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_user_id); - SET v_model_prop_user_id = v_user_id; - END IF; - IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN - SET v_model_blueprint_id = UUID(); - INSERT INTO model_blueprint - (model_blueprint_id, model_id, model_blueprint_text, user_id) - VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_user_id); - SET v_model_blueprint_user_id = v_user_id; - END IF; - UPDATE model - SET control_name_prefix = v_control_name_prefix, - model_prop_id = v_model_prop_id, - model_blueprint_id = v_model_blueprint_id, - service_type_id = v_service_type_id, - deployment_id = v_deployment_id, - deployment_status_url = v_deployment_status_url - WHERE model_id = v_model_id; -END; -CREATE PROCEDURE ins_model_instance - (IN v_control_name_uuid VARCHAR(36), - IN v_vm_name VARCHAR(250), - IN v_location VARCHAR(250), - OUT v_model_id VARCHAR(36), - OUT v_model_instance_id VARCHAR(36)) -BEGIN - SELECT m.model_id - INTO v_model_id - FROM model m - WHERE m.control_name_uuid = v_control_name_uuid; - SET v_model_instance_id = UUID(); - INSERT INTO model_instance - (model_instance_id, model_id, vm_name, location) - VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location); -END; -CREATE PROCEDURE del_model_instance - (IN v_control_name_uuid VARCHAR(36), - IN v_vm_name VARCHAR(250), - OUT v_model_id VARCHAR(36), - OUT v_model_instance_id VARCHAR(36)) -BEGIN - SELECT m.model_id, i.model_instance_id - INTO v_model_id, - v_model_instance_id - FROM model m, - model_instance i - WHERE m.model_id = i.model_id - AND m.control_name_uuid = v_control_name_uuid - AND i.vm_name = v_vm_name; - DELETE FROM model_instance - WHERE model_instance_id = v_model_instance_id; -END; -CREATE PROCEDURE del_all_model_instances - (IN v_control_name_uuid VARCHAR(36), - OUT v_model_id VARCHAR(36)) -BEGIN - SELECT m.model_id - INTO v_model_id - FROM model m - WHERE m.control_name_uuid = v_control_name_uuid; - DELETE FROM model_instance - WHERE model_id = v_model_id; -END; -CREATE PROCEDURE ins_event - (IN v_model_name VARCHAR(80), - IN v_control_name_prefix VARCHAR(80), - IN v_control_name_uuid VARCHAR(36), - IN v_user_id VARCHAR(80), - IN v_action_cd VARCHAR(80), - IN v_action_state_cd VARCHAR(80), - IN v_process_instance_id VARCHAR(80), - OUT v_model_id VARCHAR(36), - OUT v_event_id VARCHAR(36)) -BEGIN - DECLARE v_prev_event_id VARCHAR(36); - SELECT m.model_id, - m.event_id - INTO v_model_id, - v_prev_event_id - FROM model m - WHERE m.model_name = v_model_name - OR m.control_name_uuid = v_control_name_uuid; - SET v_event_id = UUID(); - INSERT INTO event - (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, user_id) - VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_prev_event_id, v_process_instance_id, v_user_id); - UPDATE model - SET event_id = v_event_id - WHERE model_id = v_model_id; -END; -CREATE PROCEDURE upd_event - (IN v_event_id VARCHAR(36), - IN v_process_instance_id VARCHAR(80)) -BEGIN - UPDATE event - SET process_instance_id = v_process_instance_id - WHERE event_id = v_event_id; -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 ; |