diff options
Diffstat (limited to 'extra/sql/bulkload')
| -rw-r--r-- | extra/sql/bulkload/clds-create-db-objects.sql | 225 | ||||
| -rw-r--r-- | extra/sql/bulkload/clds-stored-procedures.sql | 534 | ||||
| -rw-r--r-- | extra/sql/bulkload/create-db.sql | 11 |
3 files changed, 11 insertions, 759 deletions
diff --git a/extra/sql/bulkload/clds-create-db-objects.sql b/extra/sql/bulkload/clds-create-db-objects.sql deleted file mode 100644 index aa3aa65e..00000000 --- a/extra/sql/bulkload/clds-create-db-objects.sql +++ /dev/null @@ -1,225 +0,0 @@ -# -# Create CLDS database objects (tables, etc.) -# -# -CREATE DATABASE `cldsdb4`; -USE `cldsdb4`; -DROP USER 'clds'; -CREATE USER 'clds'; -GRANT ALL on cldsdb4.* to 'clds' identified by 'sidnnd83K' with GRANT OPTION; -GRANT SELECT on mysql.proc TO 'clds'; -FLUSH PRIVILEGES; - - -CREATE TABLE template ( - template_id VARCHAR(36) NOT NULL, - template_name VARCHAR(80) NOT NULL, - template_bpmn_id VARCHAR(36) NULL, - template_image_id VARCHAR(36) NULL, - template_doc_id VARCHAR(36) NULL, - PRIMARY KEY (template_id), - UNIQUE (template_name) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; - -CREATE TABLE template_bpmn ( - template_bpmn_id VARCHAR(36) NOT NULL, - template_id VARCHAR(36) NOT NULL, - template_bpmn_text MEDIUMTEXT NOT NULL, - user_id VARCHAR(80), - timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (template_bpmn_id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; - -CREATE TABLE template_image ( - template_image_id VARCHAR(36) NOT NULL, - template_id VARCHAR(36) NOT NULL, - template_image_text MEDIUMTEXT NULL, - user_id VARCHAR(80), - timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (template_image_id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; - -CREATE TABLE template_doc ( - template_doc_id VARCHAR(36) NOT NULL, - template_id VARCHAR(36) NOT NULL, - template_doc_text MEDIUMTEXT NULL, - user_id VARCHAR(80), - timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (template_doc_id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; - -CREATE TABLE model ( - model_id VARCHAR(36) NOT NULL, - model_name VARCHAR(80) NOT NULL, - template_id VARCHAR(36) NULL, - model_prop_id VARCHAR(36) NULL, - model_blueprint_id VARCHAR(36) NULL, - event_id VARCHAR(36) NULL, - control_name_prefix VARCHAR(80) NULL, - control_name_uuid VARCHAR(36) NOT NULL, - service_type_id VARCHAR(80) NULL, - deployment_id VARCHAR(80) NULL, - deployment_status_url VARCHAR(300) NULL, - PRIMARY KEY (model_id), - UNIQUE (model_name), - UNIQUE (control_name_uuid), - UNIQUE (service_type_id), - UNIQUE (deployment_id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; - -CREATE TABLE model_properties ( - model_prop_id VARCHAR(36) NOT NULL, - model_id VARCHAR(36) NOT NULL, - model_prop_text MEDIUMTEXT NULL, - user_id VARCHAR(80), - timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (model_prop_id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; - -CREATE TABLE model_blueprint ( - model_blueprint_id VARCHAR(36) NOT NULL, - model_id VARCHAR(36) NOT NULL, - model_blueprint_text MEDIUMTEXT NULL, - user_id VARCHAR(80), - timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (model_blueprint_id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; - -CREATE TABLE model_instance ( - model_instance_id VARCHAR(36) NOT NULL, - model_id VARCHAR(36) NOT NULL, - vm_name VARCHAR(250) NOT NULL, - location VARCHAR(250) NULL, - timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (model_instance_id), - UNIQUE (model_id, vm_name) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; - -CREATE TABLE event ( - event_id VARCHAR(36) NOT NULL, - model_id VARCHAR(36) NULL, - action_cd VARCHAR(80) NOT NULL, - action_state_cd VARCHAR(80) NULL, - prev_event_id VARCHAR(36) NULL, - process_instance_id VARCHAR(80) NULL, - user_id VARCHAR(80) NULL, - timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (event_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) - REFERENCES template_bpmn (template_bpmn_id); - -ALTER TABLE template - ADD CONSTRAINT template_image_id_fkey01 - FOREIGN KEY (template_image_id) - REFERENCES template_image (template_image_id); - -ALTER TABLE template - ADD CONSTRAINT template_doc_id_fkey01 - FOREIGN KEY (template_doc_id) - REFERENCES template_doc (template_doc_id); - -ALTER TABLE template_bpmn - ADD CONSTRAINT template_id_fkey02 - FOREIGN KEY (template_id) - REFERENCES template (template_id); - -ALTER TABLE template_image - ADD CONSTRAINT template_id_fkey03 - FOREIGN KEY (template_id) - REFERENCES template (template_id); - -ALTER TABLE template_doc - ADD CONSTRAINT template_id_fkey04 - FOREIGN KEY (template_id) - REFERENCES template (template_id); - -ALTER TABLE model - ADD CONSTRAINT template_id_fkey01 - FOREIGN KEY (template_id) - REFERENCES template (template_id); - -ALTER TABLE model - ADD CONSTRAINT model_prop_id_fkey01 - FOREIGN KEY (model_prop_id) - REFERENCES model_properties (model_prop_id); - -ALTER TABLE model - ADD CONSTRAINT model_blueprint_id_fkey01 - FOREIGN KEY (model_blueprint_id) - REFERENCES model_blueprint (model_blueprint_id); - -ALTER TABLE model - ADD CONSTRAINT event_id_fkey01 - FOREIGN KEY (event_id) - REFERENCES event (event_id); - -ALTER TABLE model_properties - ADD CONSTRAINT model_id_fkey01 - FOREIGN KEY (model_id) - REFERENCES model (model_id); - -ALTER TABLE model_blueprint - ADD CONSTRAINT model_id_fkey02 - FOREIGN KEY (model_id) - REFERENCES model (model_id); - -ALTER TABLE model_instance - ADD CONSTRAINT model_id_fkey04 - FOREIGN KEY (model_id) - REFERENCES model (model_id); - -ALTER TABLE event - ADD CONSTRAINT model_id_fkey03 - FOREIGN KEY (model_id) - REFERENCES model (model_id); diff --git a/extra/sql/bulkload/clds-stored-procedures.sql b/extra/sql/bulkload/clds-stored-procedures.sql deleted file mode 100644 index b48e86af..00000000 --- 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 ; diff --git a/extra/sql/bulkload/create-db.sql b/extra/sql/bulkload/create-db.sql new file mode 100644 index 00000000..ea4d97c1 --- /dev/null +++ b/extra/sql/bulkload/create-db.sql @@ -0,0 +1,11 @@ +# +# Create CLDS database objects (tables, etc.) +# +# +CREATE DATABASE `cldsdb4`; +USE `cldsdb4`; +DROP USER 'clds'; +CREATE USER 'clds'; +GRANT ALL on cldsdb4.* to 'clds' identified by 'sidnnd83K' with GRANT OPTION; +FLUSH PRIVILEGES; + |