extra/sql/bulkload/clds-stored-procedures.sql

# 
# 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 ;