diff options
Diffstat (limited to 'extra/sql/bulkload/clds-stored-procedures.sql')
| -rw-r--r-- | extra/sql/bulkload/clds-stored-procedures.sql | 431 |
1 files changed, 431 insertions, 0 deletions
diff --git a/extra/sql/bulkload/clds-stored-procedures.sql b/extra/sql/bulkload/clds-stored-procedures.sql new file mode 100644 index 00000000..41cc75e1 --- /dev/null +++ b/extra/sql/bulkload/clds-stored-procedures.sql @@ -0,0 +1,431 @@ +# +# 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; +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_userid VARCHAR(8), + OUT v_template_bpmn_text MEDIUMTEXT, + OUT v_template_image_id VARCHAR(36), + OUT v_template_image_userid VARCHAR(8), + OUT v_template_image_text MEDIUMTEXT, + OUT v_template_doc_id VARCHAR(36), + OUT v_template_doc_userid VARCHAR(8), + OUT v_template_doc_text MEDIUMTEXT) +BEGIN + SELECT t.template_id, + tb.template_bpmn_id, + tb.userid, + tb.template_bpmn_text, + ti.template_image_id, + ti.userid, + ti.template_image_text, + td.template_doc_id, + td.userid, + td.template_doc_text + INTO v_template_id, + v_template_bpmn_id, + v_template_bpmn_userid, + v_template_bpmn_text, + v_template_image_id, + v_template_image_userid, + v_template_image_text, + v_template_doc_id, + v_template_doc_userid, + 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_userid VARCHAR(8), + 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_userid VARCHAR(8), + OUT v_template_image_id VARCHAR(36), + OUT v_template_image_userid VARCHAR(8), + OUT v_template_doc_id VARCHAR(36), + OUT v_template_doc_userid VARCHAR(8)) +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_userid, + v_old_template_bpmn_text, + v_template_image_id, + v_template_image_userid, + v_old_template_image_text, + v_template_doc_id, + v_template_doc_userid, + 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, userid) + VALUES (v_template_bpmn_id, v_template_id, v_template_bpmn_text, v_userid); + SET v_template_bpmn_userid = v_userid; + 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, userid) + VALUES (v_template_image_id, v_template_id, v_template_image_text, v_userid); + SET v_template_image_userid = v_userid; + 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, userid) + VALUES (v_template_doc_id, v_template_id, v_template_doc_text, v_userid); + SET v_template_doc_userid = v_userid; + 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_template_name VARCHAR(80), + OUT v_template_id VARCHAR(36), + OUT v_model_prop_id VARCHAR(36), + OUT v_model_prop_userid VARCHAR(8), + OUT v_model_prop_text MEDIUMTEXT, + OUT v_model_blueprint_id VARCHAR(36), + OUT v_model_blueprint_userid VARCHAR(8), + 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_userid VARCHAR(8)) +BEGIN + SELECT m.control_name_prefix, + m.control_name_uuid, + m.model_id, + t.template_name, + m.template_id, + mp.model_prop_id, + mp.userid, + mp.model_prop_text, + mb.model_blueprint_id, + mb.userid, + mb.model_blueprint_text, + e.event_id, + e.action_cd, + e.action_state_cd, + e.process_instance_id, + e.userid + INTO v_control_name_prefix, + v_control_name_uuid, + v_model_id, + v_template_name, + v_template_id, + v_model_prop_id, + v_model_prop_userid, + v_model_prop_text, + v_model_blueprint_id, + v_model_blueprint_userid, + v_model_blueprint_text, + v_event_id, + v_action_cd, + v_action_state_cd, + v_event_process_instance_id, + v_event_userid + 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_template_name VARCHAR(80), + OUT v_template_id VARCHAR(36), + OUT v_model_prop_id VARCHAR(36), + OUT v_model_prop_userid VARCHAR(8), + OUT v_model_prop_text MEDIUMTEXT, + OUT v_model_blueprint_id VARCHAR(36), + OUT v_model_blueprint_userid VARCHAR(8), + OUT v_model_blueprint_text MEDIUMTEXT, + OUT v_template_bpmn_id VARCHAR(36), + OUT v_template_bpmn_userid VARCHAR(8), + OUT v_template_bpmn_text MEDIUMTEXT, + OUT v_template_image_id VARCHAR(36), + OUT v_template_image_userid VARCHAR(8), + OUT v_template_image_text MEDIUMTEXT, + OUT v_template_doc_id VARCHAR(36), + OUT v_template_doc_userid VARCHAR(8), + 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_userid VARCHAR(8)) +BEGIN + CALL get_model( + v_model_name, + v_control_name_prefix, + v_control_name_uuid, + v_model_id, + v_template_name, + v_template_id, + v_model_prop_id, + v_model_prop_userid, + v_model_prop_text, + v_model_blueprint_id, + v_model_blueprint_userid, + v_model_blueprint_text, + v_event_id, + v_action_cd, + v_action_state_cd, + v_event_process_instance_id, + v_event_userid); + CALL get_template( + v_template_name, + v_template_id, + v_template_bpmn_id, + v_template_bpmn_userid, + v_template_bpmn_text, + v_template_image_id, + v_template_image_userid, + v_template_image_text, + v_template_doc_id, + v_template_doc_userid, + v_template_doc_text); + END; +CREATE PROCEDURE set_model + (IN v_model_name VARCHAR(80), + IN v_template_id VARCHAR(36), + IN v_userid VARCHAR(8), + IN v_model_prop_text MEDIUMTEXT, + IN v_model_blueprint_text MEDIUMTEXT, + 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_userid VARCHAR(8), + OUT v_model_blueprint_id VARCHAR(36), + OUT v_model_blueprint_userid VARCHAR(8), + 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_userid VARCHAR(8)) +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; + 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_template_name, + v_old_template_id, + v_model_prop_id, + v_model_prop_userid, + v_old_model_prop_text, + v_model_blueprint_id, + v_model_blueprint_userid, + v_old_model_blueprint_text, + v_event_id, + v_action_cd, + v_action_state_cd, + v_event_process_instance_id, + v_event_userid); + 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) + VALUES (v_model_id, v_model_name, v_template_id, v_control_name_prefix, v_control_name_uuid); + # 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_userid = v_userid; + SET v_event_id = UUID(); + INSERT INTO event + (event_id, model_id, action_cd, action_state_cd, userid) + VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_userid); + 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, userid) + VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_userid); + SET v_model_prop_userid = v_userid; + 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, userid) + VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_userid); + SET v_model_blueprint_userid = v_userid; + 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 + 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_userid VARCHAR(8), + 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, userid) + VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_prev_event_id, v_process_instance_id, v_userid); + 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 +// +DELIMITER ; |