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

   1 #
   2 # CLDS stored procedures
   3 #
   4 #
   5 USE cldsdb4;
   6
   7 DROP PROCEDURE IF EXISTS upd_event;
   8 DROP PROCEDURE IF EXISTS ins_event;
   9 DROP PROCEDURE IF EXISTS del_all_model_instances;
  10 DROP PROCEDURE IF EXISTS del_model_instance;
  11 DROP PROCEDURE IF EXISTS ins_model_instance;
  12 DROP PROCEDURE IF EXISTS set_model;
  13 DROP PROCEDURE IF EXISTS get_model;
  14 DROP PROCEDURE IF EXISTS get_model_template;
  15 DROP PROCEDURE IF EXISTS set_template;
  16 DROP PROCEDURE IF EXISTS get_template;
  17 DELIMITER //
  18 CREATE PROCEDURE get_template
  19   (IN v_template_name VARCHAR(80),
  20    OUT v_template_id VARCHAR(36),
  21    OUT v_template_bpmn_id VARCHAR(36),
  22    OUT v_template_bpmn_userid VARCHAR(8),
  23    OUT v_template_bpmn_text MEDIUMTEXT,
  24    OUT v_template_image_id VARCHAR(36),
  25    OUT v_template_image_userid VARCHAR(8),
  26    OUT v_template_image_text MEDIUMTEXT,
  27    OUT v_template_doc_id VARCHAR(36),
  28    OUT v_template_doc_userid VARCHAR(8),
  29    OUT v_template_doc_text MEDIUMTEXT)
  30 BEGIN
  31   SELECT t.template_id,
  32                  tb.template_bpmn_id,
  33                  tb.userid,
  34                  tb.template_bpmn_text,
  35                  ti.template_image_id,
  36                  ti.userid,
  37                  ti.template_image_text,
  38                  td.template_doc_id,
  39                  td.userid,
  40                  td.template_doc_text
  41     INTO v_template_id,
  42          v_template_bpmn_id,
  43          v_template_bpmn_userid,
  44          v_template_bpmn_text,
  45          v_template_image_id,
  46          v_template_image_userid,
  47          v_template_image_text,
  48          v_template_doc_id,
  49          v_template_doc_userid,
  50          v_template_doc_text
  51     FROM template t,
  52          template_bpmn tb,
  53                  template_image ti,
  54                  template_doc td
  55     WHERE t.template_bpmn_id = tb.template_bpmn_id
  56           AND t.template_image_id = ti.template_image_id
  57           AND t.template_doc_id = td.template_doc_id
  58       AND t.template_name = v_template_name;
  59 END;
  60 CREATE PROCEDURE set_template
  61   (IN v_template_name VARCHAR(80),
  62    IN v_userid VARCHAR(8),
  63    IN v_template_bpmn_text MEDIUMTEXT,
  64    IN v_template_image_text MEDIUMTEXT,
  65    IN v_template_doc_text MEDIUMTEXT,
  66    OUT v_template_id VARCHAR(36),
  67    OUT v_template_bpmn_id VARCHAR(36),
  68    OUT v_template_bpmn_userid VARCHAR(8),
  69    OUT v_template_image_id VARCHAR(36),
  70    OUT v_template_image_userid VARCHAR(8),
  71    OUT v_template_doc_id VARCHAR(36),
  72    OUT v_template_doc_userid VARCHAR(8))
  73 BEGIN
  74   DECLARE v_old_template_bpmn_text MEDIUMTEXT;
  75   DECLARE v_old_template_image_text MEDIUMTEXT;
  76   DECLARE v_old_template_doc_text MEDIUMTEXT;
  77   SET v_template_id = NULL;
  78   CALL get_template(
  79     v_template_name,
  80     v_template_id,
  81     v_template_bpmn_id,
  82     v_template_bpmn_userid,
  83     v_old_template_bpmn_text,
  84     v_template_image_id,
  85     v_template_image_userid,
  86     v_old_template_image_text,
  87     v_template_doc_id,
  88     v_template_doc_userid,
  89     v_old_template_doc_text);
  90   IF v_template_id IS NULL THEN
  91     BEGIN
  92           SET v_template_id = UUID();
  93       INSERT INTO template
  94             (template_id, template_name)
  95             VALUES (v_template_id, v_template_name);
  96         END;
  97   END IF;
  98   IF v_template_bpmn_id IS NULL OR v_template_bpmn_text <> v_old_template_bpmn_text THEN
  99         SET v_template_bpmn_id = UUID();
 100     INSERT INTO template_bpmn
 101           (template_bpmn_id, template_id, template_bpmn_text, userid)
 102           VALUES (v_template_bpmn_id, v_template_id, v_template_bpmn_text, v_userid);
 103         SET v_template_bpmn_userid = v_userid;
 104   END IF;
 105   IF v_template_image_id IS NULL OR v_template_image_text <> v_old_template_image_text THEN
 106         SET v_template_image_id = UUID();
 107     INSERT INTO template_image
 108           (template_image_id, template_id, template_image_text, userid)
 109           VALUES (v_template_image_id, v_template_id, v_template_image_text, v_userid);
 110         SET v_template_image_userid = v_userid;
 111   END IF;
 112   IF v_template_doc_id IS NULL OR v_template_doc_text <> v_old_template_doc_text THEN
 113         SET v_template_doc_id = UUID();
 114     INSERT INTO template_doc
 115           (template_doc_id, template_id, template_doc_text, userid)
 116           VALUES (v_template_doc_id, v_template_id, v_template_doc_text, v_userid);
 117         SET v_template_doc_userid = v_userid;
 118   END IF;
 119   UPDATE template
 120     SET template_bpmn_id = v_template_bpmn_id,
 121             template_image_id = v_template_image_id,
 122             template_doc_id = v_template_doc_id
 123     WHERE template_id = v_template_id;
 124 END;
 125 CREATE PROCEDURE get_model
 126   (IN v_model_name VARCHAR(80),
 127    OUT v_control_name_prefix VARCHAR(80),
 128    INOUT v_control_name_uuid VARCHAR(36),
 129    OUT v_model_id VARCHAR(36),
 130    OUT v_template_name VARCHAR(80),
 131    OUT v_template_id VARCHAR(36),
 132    OUT v_model_prop_id VARCHAR(36),
 133    OUT v_model_prop_userid VARCHAR(8),
 134    OUT v_model_prop_text MEDIUMTEXT,
 135    OUT v_model_blueprint_id VARCHAR(36),
 136    OUT v_model_blueprint_userid VARCHAR(8),
 137    OUT v_model_blueprint_text MEDIUMTEXT,
 138    OUT v_event_id VARCHAR(36),
 139    OUT v_action_cd VARCHAR(80),
 140    OUT v_action_state_cd VARCHAR(80),
 141    OUT v_event_process_instance_id VARCHAR(80),
 142    OUT v_event_userid VARCHAR(8))
 143 BEGIN
 144   SELECT m.control_name_prefix,
 145                  m.control_name_uuid,
 146                  m.model_id,
 147                  t.template_name,
 148                  m.template_id,
 149                  mp.model_prop_id,
 150                  mp.userid,
 151                  mp.model_prop_text,
 152                  mb.model_blueprint_id,
 153                  mb.userid,
 154                  mb.model_blueprint_text,
 155                  e.event_id,
 156                  e.action_cd,
 157                  e.action_state_cd,
 158                  e.process_instance_id,
 159                  e.userid
 160     INTO v_control_name_prefix,
 161          v_control_name_uuid,
 162                  v_model_id,
 163                  v_template_name,
 164          v_template_id,
 165          v_model_prop_id,
 166          v_model_prop_userid,
 167          v_model_prop_text,
 168          v_model_blueprint_id,
 169          v_model_blueprint_userid,
 170          v_model_blueprint_text,
 171          v_event_id,
 172          v_action_cd,
 173                  v_action_state_cd,
 174          v_event_process_instance_id,
 175          v_event_userid
 176     FROM model m,
 177                  template t,
 178                  model_properties mp,
 179                  model_blueprint mb,
 180                  event e
 181     WHERE m.template_id = t.template_id
 182           AND m.model_prop_id = mp.model_prop_id
 183           AND m.model_blueprint_id = mb.model_blueprint_id
 184           AND m.event_id = e.event_id
 185       AND (m.model_name = v_model_name
 186       OR  m.control_name_uuid = v_control_name_uuid);
 187     SELECT model_instance_id,
 188            vm_name,
 189            location,
 190            timestamp
 191     FROM model_instance
 192     WHERE model_id = v_model_id
 193     ORDER BY 2;
 194 END;
 195 CREATE PROCEDURE get_model_template
 196   (IN v_model_name VARCHAR(80),
 197    OUT v_control_name_prefix VARCHAR(80),
 198    INOUT v_control_name_uuid VARCHAR(36),
 199    OUT v_model_id VARCHAR(36),
 200    OUT v_template_name VARCHAR(80),
 201    OUT v_template_id VARCHAR(36),
 202    OUT v_model_prop_id VARCHAR(36),
 203    OUT v_model_prop_userid VARCHAR(8),
 204    OUT v_model_prop_text MEDIUMTEXT,
 205    OUT v_model_blueprint_id VARCHAR(36),
 206    OUT v_model_blueprint_userid VARCHAR(8),
 207    OUT v_model_blueprint_text MEDIUMTEXT,
 208    OUT v_template_bpmn_id VARCHAR(36),
 209    OUT v_template_bpmn_userid VARCHAR(8),
 210    OUT v_template_bpmn_text MEDIUMTEXT,
 211    OUT v_template_image_id VARCHAR(36),
 212    OUT v_template_image_userid VARCHAR(8),
 213    OUT v_template_image_text MEDIUMTEXT,
 214    OUT v_template_doc_id VARCHAR(36),
 215    OUT v_template_doc_userid VARCHAR(8),
 216    OUT v_template_doc_text MEDIUMTEXT,
 217    OUT v_event_id VARCHAR(36),
 218    OUT v_action_cd VARCHAR(80),
 219    OUT v_action_state_cd VARCHAR(80),
 220    OUT v_event_process_instance_id VARCHAR(80),
 221    OUT v_event_userid VARCHAR(8))
 222 BEGIN
 223   CALL get_model(
 224     v_model_name,
 225     v_control_name_prefix,
 226     v_control_name_uuid,
 227     v_model_id,
 228     v_template_name,
 229     v_template_id,
 230     v_model_prop_id,
 231     v_model_prop_userid,
 232     v_model_prop_text,
 233     v_model_blueprint_id,
 234     v_model_blueprint_userid,
 235     v_model_blueprint_text,
 236         v_event_id,
 237         v_action_cd,
 238         v_action_state_cd,
 239         v_event_process_instance_id,
 240         v_event_userid);
 241   CALL get_template(
 242     v_template_name,
 243     v_template_id,
 244     v_template_bpmn_id,
 245     v_template_bpmn_userid,
 246     v_template_bpmn_text,
 247     v_template_image_id,
 248     v_template_image_userid,
 249     v_template_image_text,
 250     v_template_doc_id,
 251     v_template_doc_userid,
 252     v_template_doc_text);
 253   END;
 254 CREATE PROCEDURE set_model
 255   (IN v_model_name VARCHAR(80),
 256    IN v_template_id VARCHAR(36),
 257    IN v_userid VARCHAR(8),
 258    IN v_model_prop_text MEDIUMTEXT,
 259    IN v_model_blueprint_text MEDIUMTEXT,
 260    INOUT v_control_name_prefix VARCHAR(80),
 261    INOUT v_control_name_uuid VARCHAR(36),
 262    OUT v_model_id VARCHAR(36),
 263    OUT v_model_prop_id VARCHAR(36),
 264    OUT v_model_prop_userid VARCHAR(8),
 265    OUT v_model_blueprint_id VARCHAR(36),
 266    OUT v_model_blueprint_userid VARCHAR(8),
 267    OUT v_event_id VARCHAR(36),
 268    OUT v_action_cd VARCHAR(80),
 269    OUT v_action_state_cd VARCHAR(80),
 270    OUT v_event_process_instance_id VARCHAR(80),
 271    OUT v_event_userid VARCHAR(8))
 272 BEGIN
 273   DECLARE v_old_template_name VARCHAR(80);
 274   DECLARE v_old_template_id VARCHAR(36);
 275   DECLARE v_old_control_name_prefix VARCHAR(80);
 276   DECLARE v_old_control_name_uuid VARCHAR(36);
 277   DECLARE v_old_model_prop_text MEDIUMTEXT;
 278   DECLARE v_old_model_blueprint_text MEDIUMTEXT;
 279   SET v_model_id = NULL;
 280   CALL get_model(
 281     v_model_name,
 282     v_old_control_name_prefix,
 283     v_old_control_name_uuid,
 284     v_model_id,
 285     v_old_template_name,
 286     v_old_template_id,
 287     v_model_prop_id,
 288     v_model_prop_userid,
 289     v_old_model_prop_text,
 290     v_model_blueprint_id,
 291     v_model_blueprint_userid,
 292     v_old_model_blueprint_text,
 293         v_event_id,
 294         v_action_cd,
 295         v_action_state_cd,
 296         v_event_process_instance_id,
 297         v_event_userid);
 298   IF v_model_id IS NULL THEN
 299     BEGIN
 300       # UUID can be provided initially but cannot be updated
 301           # if not provided (this is expected) then it will be set here
 302       IF v_control_name_uuid IS NULL THEN
 303             SET v_control_name_uuid = UUID();
 304           END IF;
 305       SET v_model_id = v_control_name_uuid;
 306       INSERT INTO model
 307             (model_id, model_name, template_id, control_name_prefix, control_name_uuid)
 308             VALUES (v_model_id, v_model_name, v_template_id, v_control_name_prefix, v_control_name_uuid);
 309           # since just created model, insert CREATED event as initial default event
 310           SET v_action_cd = 'CREATE';
 311           SET v_action_state_cd = 'COMPLETED';
 312           SET v_event_userid = v_userid;
 313       SET v_event_id = UUID();
 314       INSERT INTO event
 315             (event_id, model_id, action_cd, action_state_cd, userid)
 316             VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_userid);
 317           UPDATE model
 318                 SET event_id = v_event_id
 319                 WHERE model_id = v_model_id;
 320         END;
 321   ELSE
 322     BEGIN
 323           # use old control_name_prefix if null value is provided
 324       IF v_control_name_prefix IS NULL THEN
 325              SET v_control_name_prefix = v_old_control_name_prefix;
 326           END IF;
 327           # UUID can not be updated after initial insert
 328           SET v_control_name_uuid = v_old_control_name_uuid;
 329         END;
 330   END IF;
 331   IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN
 332         SET v_model_prop_id = UUID();
 333     INSERT INTO model_properties
 334           (model_prop_id, model_id, model_prop_text, userid)
 335           VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_userid);
 336         SET v_model_prop_userid = v_userid;
 337   END IF;
 338   IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN
 339         SET v_model_blueprint_id = UUID();
 340     INSERT INTO model_blueprint
 341           (model_blueprint_id, model_id, model_blueprint_text, userid)
 342           VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_userid);
 343         SET v_model_blueprint_userid = v_userid;
 344   END IF;
 345   UPDATE model
 346     SET control_name_prefix = v_control_name_prefix,
 347             model_prop_id = v_model_prop_id,
 348             model_blueprint_id = v_model_blueprint_id
 349     WHERE model_id = v_model_id;
 350 END;
 351 CREATE PROCEDURE ins_model_instance
 352   (IN v_control_name_uuid VARCHAR(36),
 353    IN v_vm_name VARCHAR(250),
 354    IN v_location VARCHAR(250),
 355    OUT v_model_id VARCHAR(36),
 356    OUT v_model_instance_id VARCHAR(36))
 357 BEGIN
 358    SELECT m.model_id
 359     INTO v_model_id
 360     FROM model m
 361     WHERE m.control_name_uuid = v_control_name_uuid;
 362   SET v_model_instance_id = UUID();
 363   INSERT INTO model_instance
 364         (model_instance_id, model_id, vm_name, location)
 365         VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location);
 366 END;
 367 CREATE PROCEDURE del_model_instance
 368   (IN v_control_name_uuid VARCHAR(36),
 369    IN v_vm_name VARCHAR(250),
 370    OUT v_model_id VARCHAR(36),
 371    OUT v_model_instance_id VARCHAR(36))
 372 BEGIN
 373    SELECT m.model_id, i.model_instance_id
 374     INTO v_model_id,
 375          v_model_instance_id
 376     FROM model m,
 377          model_instance i
 378     WHERE m.model_id = i.model_id
 379      AND  m.control_name_uuid = v_control_name_uuid
 380      AND  i.vm_name = v_vm_name;
 381   DELETE FROM model_instance
 382   WHERE model_instance_id = v_model_instance_id;
 383 END;
 384 CREATE PROCEDURE del_all_model_instances
 385   (IN v_control_name_uuid VARCHAR(36),
 386    OUT v_model_id VARCHAR(36))
 387 BEGIN
 388   SELECT m.model_id
 389     INTO v_model_id
 390     FROM model m
 391     WHERE m.control_name_uuid = v_control_name_uuid;
 392   DELETE FROM model_instance
 393   WHERE model_id = v_model_id;
 394 END;
 395 CREATE PROCEDURE ins_event
 396   (IN v_model_name VARCHAR(80),
 397    IN v_control_name_prefix VARCHAR(80),
 398    IN v_control_name_uuid VARCHAR(36),
 399    IN v_userid VARCHAR(8),
 400    IN v_action_cd VARCHAR(80),
 401    IN v_action_state_cd VARCHAR(80),
 402    IN v_process_instance_id VARCHAR(80),
 403    OUT v_model_id VARCHAR(36),
 404    OUT v_event_id VARCHAR(36))
 405 BEGIN
 406   DECLARE v_prev_event_id VARCHAR(36);
 407   SELECT m.model_id,
 408                  m.event_id
 409     INTO v_model_id,
 410          v_prev_event_id
 411     FROM model m
 412     WHERE m.model_name = v_model_name
 413           OR  m.control_name_uuid = v_control_name_uuid;
 414   SET v_event_id = UUID();
 415   INSERT INTO event
 416         (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, userid)
 417         VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_prev_event_id, v_process_instance_id, v_userid);
 418   UPDATE model
 419         SET event_id = v_event_id
 420         WHERE model_id = v_model_id;
 421 END;
 422 CREATE PROCEDURE upd_event
 423   (IN v_event_id VARCHAR(36),
 424    IN v_process_instance_id VARCHAR(80))
 425 BEGIN
 426   UPDATE event
 427         SET process_instance_id = v_process_instance_id
 428         WHERE event_id = v_event_id;
 429 END
 430 //
 431 DELIMITER ;