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 DROP PROCEDURE IF EXISTS del_model;
  18 DROP PROCEDURE IF EXISTS set_new_tosca_model_version;
  19 DROP PROCEDURE IF EXISTS set_tosca_model;
  20 DROP PROCEDURE IF EXISTS set_dictionary;
  21 DROP PROCEDURE IF EXISTS set_dictionary_elements;
  22 DELIMITER //
  23 CREATE PROCEDURE get_template
  24   (IN v_template_name VARCHAR(80),
  25    OUT v_template_id VARCHAR(36),
  26    OUT v_template_bpmn_id VARCHAR(36),
  27    OUT v_template_bpmn_user_id VARCHAR(80),
  28    OUT v_template_bpmn_text MEDIUMTEXT,
  29    OUT v_template_image_id VARCHAR(36),
  30    OUT v_template_image_user_id VARCHAR(80),
  31    OUT v_template_image_text MEDIUMTEXT,
  32    OUT v_template_doc_id VARCHAR(36),
  33    OUT v_template_doc_user_id VARCHAR(80),
  34    OUT v_template_doc_text MEDIUMTEXT)
  35 BEGIN
  36   SELECT t.template_id,
  37                  tb.template_bpmn_id,
  38                  tb.user_id,
  39                  tb.template_bpmn_text,
  40                  ti.template_image_id,
  41                  ti.user_id,
  42                  ti.template_image_text,
  43                  td.template_doc_id,
  44                  td.user_id,
  45                  td.template_doc_text
  46     INTO v_template_id,
  47          v_template_bpmn_id,
  48          v_template_bpmn_user_id,
  49          v_template_bpmn_text,
  50          v_template_image_id,
  51          v_template_image_user_id,
  52          v_template_image_text,
  53          v_template_doc_id,
  54          v_template_doc_user_id,
  55          v_template_doc_text
  56     FROM template t,
  57          template_bpmn tb,
  58                  template_image ti,
  59                  template_doc td
  60     WHERE t.template_bpmn_id = tb.template_bpmn_id
  61           AND t.template_image_id = ti.template_image_id
  62           AND t.template_doc_id = td.template_doc_id
  63       AND t.template_name = v_template_name;
  64 END;
  65 CREATE PROCEDURE set_template
  66   (IN v_template_name VARCHAR(80),
  67    IN v_user_id VARCHAR(80),
  68    IN v_template_bpmn_text MEDIUMTEXT,
  69    IN v_template_image_text MEDIUMTEXT,
  70    IN v_template_doc_text MEDIUMTEXT,
  71    OUT v_template_id VARCHAR(36),
  72    OUT v_template_bpmn_id VARCHAR(36),
  73    OUT v_template_bpmn_user_id VARCHAR(80),
  74    OUT v_template_image_id VARCHAR(36),
  75    OUT v_template_image_user_id VARCHAR(80),
  76    OUT v_template_doc_id VARCHAR(36),
  77    OUT v_template_doc_user_id VARCHAR(80))
  78 BEGIN
  79   DECLARE v_old_template_bpmn_text MEDIUMTEXT;
  80   DECLARE v_old_template_image_text MEDIUMTEXT;
  81   DECLARE v_old_template_doc_text MEDIUMTEXT;
  82   SET v_template_id = NULL;
  83   CALL get_template(
  84     v_template_name,
  85     v_template_id,
  86     v_template_bpmn_id,
  87     v_template_bpmn_user_id,
  88     v_old_template_bpmn_text,
  89     v_template_image_id,
  90     v_template_image_user_id,
  91     v_old_template_image_text,
  92     v_template_doc_id,
  93     v_template_doc_user_id,
  94     v_old_template_doc_text);
  95   IF v_template_id IS NULL THEN
  96     BEGIN
  97           SET v_template_id = UUID();
  98       INSERT INTO template
  99             (template_id, template_name)
 100             VALUES (v_template_id, v_template_name);
 101         END;
 102   END IF;
 103   IF v_template_bpmn_id IS NULL OR v_template_bpmn_text <> v_old_template_bpmn_text THEN
 104         SET v_template_bpmn_id = UUID();
 105     INSERT INTO template_bpmn
 106           (template_bpmn_id, template_id, template_bpmn_text, user_id)
 107           VALUES (v_template_bpmn_id, v_template_id, v_template_bpmn_text, v_user_id);
 108         SET v_template_bpmn_user_id = v_user_id;
 109   END IF;
 110   IF v_template_image_id IS NULL OR v_template_image_text <> v_old_template_image_text THEN
 111         SET v_template_image_id = UUID();
 112     INSERT INTO template_image
 113           (template_image_id, template_id, template_image_text, user_id)
 114           VALUES (v_template_image_id, v_template_id, v_template_image_text, v_user_id);
 115         SET v_template_image_user_id = v_user_id;
 116   END IF;
 117   IF v_template_doc_id IS NULL OR v_template_doc_text <> v_old_template_doc_text THEN
 118         SET v_template_doc_id = UUID();
 119     INSERT INTO template_doc
 120           (template_doc_id, template_id, template_doc_text, user_id)
 121           VALUES (v_template_doc_id, v_template_id, v_template_doc_text, v_user_id);
 122         SET v_template_doc_user_id = v_user_id;
 123   END IF;
 124   UPDATE template
 125     SET template_bpmn_id = v_template_bpmn_id,
 126             template_image_id = v_template_image_id,
 127             template_doc_id = v_template_doc_id
 128     WHERE template_id = v_template_id;
 129 END;
 130 CREATE PROCEDURE get_model
 131   (IN v_model_name VARCHAR(80),
 132    OUT v_control_name_prefix VARCHAR(80),
 133    INOUT v_control_name_uuid VARCHAR(36),
 134    OUT v_model_id VARCHAR(36),
 135    OUT v_service_type_id VARCHAR(80),
 136    OUT v_deployment_id VARCHAR(80),
 137    OUT v_template_name VARCHAR(80),
 138    OUT v_template_id VARCHAR(36),
 139    OUT v_model_prop_id VARCHAR(36),
 140    OUT v_model_prop_user_id VARCHAR(80),
 141    OUT v_model_prop_text MEDIUMTEXT,
 142    OUT v_model_blueprint_id VARCHAR(36),
 143    OUT v_model_blueprint_user_id VARCHAR(80),
 144    OUT v_model_blueprint_text MEDIUMTEXT,
 145    OUT v_event_id VARCHAR(36),
 146    OUT v_action_cd VARCHAR(80),
 147    OUT v_action_state_cd VARCHAR(80),
 148    OUT v_event_process_instance_id VARCHAR(80),
 149    OUT v_event_user_id VARCHAR(80))
 150 BEGIN
 151   SELECT m.control_name_prefix,
 152                  m.control_name_uuid,
 153                  m.model_id,
 154                  m.service_type_id,
 155                  m.deployment_id,
 156                  t.template_name,
 157                  m.template_id,
 158                  mp.model_prop_id,
 159                  mp.user_id,
 160                  mp.model_prop_text,
 161                  mb.model_blueprint_id,
 162                  mb.user_id,
 163                  mb.model_blueprint_text,
 164                  e.event_id,
 165                  e.action_cd,
 166                  e.action_state_cd,
 167                  e.process_instance_id,
 168                  e.user_id
 169     INTO v_control_name_prefix,
 170          v_control_name_uuid,
 171                  v_model_id,
 172                  v_service_type_id,
 173                  v_deployment_id,
 174                  v_template_name,
 175          v_template_id,
 176          v_model_prop_id,
 177          v_model_prop_user_id,
 178          v_model_prop_text,
 179          v_model_blueprint_id,
 180          v_model_blueprint_user_id,
 181          v_model_blueprint_text,
 182          v_event_id,
 183          v_action_cd,
 184                  v_action_state_cd,
 185          v_event_process_instance_id,
 186          v_event_user_id
 187     FROM model m,
 188                  template t,
 189                  model_properties mp,
 190                  model_blueprint mb,
 191                  event e
 192     WHERE m.template_id = t.template_id
 193           AND m.model_prop_id = mp.model_prop_id
 194           AND m.model_blueprint_id = mb.model_blueprint_id
 195           AND m.event_id = e.event_id
 196       AND (m.model_name = v_model_name
 197       OR  m.control_name_uuid = v_control_name_uuid);
 198     SELECT model_instance_id,
 199            vm_name,
 200            location,
 201            timestamp
 202     FROM model_instance
 203     WHERE model_id = v_model_id
 204     ORDER BY 2;
 205 END;
 206 CREATE PROCEDURE get_model_template
 207   (IN v_model_name VARCHAR(80),
 208    OUT v_control_name_prefix VARCHAR(80),
 209    INOUT v_control_name_uuid VARCHAR(36),
 210    OUT v_model_id VARCHAR(36),
 211    OUT v_service_type_id VARCHAR(80),
 212    OUT v_deployment_id VARCHAR(80),
 213    OUT v_template_name VARCHAR(80),
 214    OUT v_template_id VARCHAR(36),
 215    OUT v_model_prop_id VARCHAR(36),
 216    OUT v_model_prop_user_id VARCHAR(80),
 217    OUT v_model_prop_text MEDIUMTEXT,
 218    OUT v_model_blueprint_id VARCHAR(36),
 219    OUT v_model_blueprint_user_id VARCHAR(80),
 220    OUT v_model_blueprint_text MEDIUMTEXT,
 221    OUT v_template_bpmn_id VARCHAR(36),
 222    OUT v_template_bpmn_user_id VARCHAR(80),
 223    OUT v_template_bpmn_text MEDIUMTEXT,
 224    OUT v_template_image_id VARCHAR(36),
 225    OUT v_template_image_user_id VARCHAR(80),
 226    OUT v_template_image_text MEDIUMTEXT,
 227    OUT v_template_doc_id VARCHAR(36),
 228    OUT v_template_doc_user_id VARCHAR(80),
 229    OUT v_template_doc_text MEDIUMTEXT,
 230    OUT v_event_id VARCHAR(36),
 231    OUT v_action_cd VARCHAR(80),
 232    OUT v_action_state_cd VARCHAR(80),
 233    OUT v_event_process_instance_id VARCHAR(80),
 234    OUT v_event_user_id VARCHAR(80))
 235 BEGIN
 236   CALL get_model(
 237     v_model_name,
 238     v_control_name_prefix,
 239     v_control_name_uuid,
 240     v_model_id,
 241         v_service_type_id,
 242         v_deployment_id,
 243     v_template_name,
 244     v_template_id,
 245     v_model_prop_id,
 246     v_model_prop_user_id,
 247     v_model_prop_text,
 248     v_model_blueprint_id,
 249     v_model_blueprint_user_id,
 250     v_model_blueprint_text,
 251         v_event_id,
 252         v_action_cd,
 253         v_action_state_cd,
 254         v_event_process_instance_id,
 255         v_event_user_id);
 256   CALL get_template(
 257     v_template_name,
 258     v_template_id,
 259     v_template_bpmn_id,
 260     v_template_bpmn_user_id,
 261     v_template_bpmn_text,
 262     v_template_image_id,
 263     v_template_image_user_id,
 264     v_template_image_text,
 265     v_template_doc_id,
 266     v_template_doc_user_id,
 267     v_template_doc_text);
 268   END;
 269 CREATE PROCEDURE set_model
 270   (IN v_model_name VARCHAR(80),
 271    IN v_template_id VARCHAR(36),
 272    IN v_user_id VARCHAR(80),
 273    IN v_model_prop_text MEDIUMTEXT,
 274    IN v_model_blueprint_text MEDIUMTEXT,
 275    IN v_service_type_id VARCHAR(80),
 276    IN v_deployment_id VARCHAR(80),
 277    INOUT v_control_name_prefix VARCHAR(80),
 278    INOUT v_control_name_uuid VARCHAR(36),
 279    OUT v_model_id VARCHAR(36),
 280    OUT v_model_prop_id VARCHAR(36),
 281    OUT v_model_prop_user_id VARCHAR(80),
 282    OUT v_model_blueprint_id VARCHAR(36),
 283    OUT v_model_blueprint_user_id VARCHAR(80),
 284    OUT v_event_id VARCHAR(36),
 285    OUT v_action_cd VARCHAR(80),
 286    OUT v_action_state_cd VARCHAR(80),
 287    OUT v_event_process_instance_id VARCHAR(80),
 288    OUT v_event_user_id VARCHAR(80))
 289 BEGIN
 290   DECLARE v_old_template_name VARCHAR(80);
 291   DECLARE v_old_template_id VARCHAR(36);
 292   DECLARE v_old_control_name_prefix VARCHAR(80);
 293   DECLARE v_old_control_name_uuid VARCHAR(36);
 294   DECLARE v_old_model_prop_text MEDIUMTEXT;
 295   DECLARE v_old_model_blueprint_text MEDIUMTEXT;
 296   DECLARE v_old_service_type_id VARCHAR(80);
 297   DECLARE v_old_deployment_id VARCHAR(80);
 298   SET v_model_id = NULL;
 299   CALL get_model(
 300     v_model_name,
 301     v_old_control_name_prefix,
 302     v_old_control_name_uuid,
 303     v_model_id,
 304         v_old_service_type_id,
 305         v_old_deployment_id,
 306     v_old_template_name,
 307     v_old_template_id,
 308     v_model_prop_id,
 309     v_model_prop_user_id,
 310     v_old_model_prop_text,
 311     v_model_blueprint_id,
 312     v_model_blueprint_user_id,
 313     v_old_model_blueprint_text,
 314         v_event_id,
 315         v_action_cd,
 316         v_action_state_cd,
 317         v_event_process_instance_id,
 318         v_event_user_id);
 319   IF v_model_id IS NULL THEN
 320     BEGIN
 321       # UUID can be provided initially but cannot be updated
 322           # if not provided (this is expected) then it will be set here
 323       IF v_control_name_uuid IS NULL THEN
 324             SET v_control_name_uuid = UUID();
 325           END IF;
 326       SET v_model_id = v_control_name_uuid;
 327       INSERT INTO model
 328             (model_id, model_name, template_id, control_name_prefix, control_name_uuid, service_type_id, deployment_id)
 329             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);
 330           # since just created model, insert CREATED event as initial default event
 331           SET v_action_cd = 'CREATE';
 332           SET v_action_state_cd = 'COMPLETED';
 333           SET v_event_user_id = v_user_id;
 334       SET v_event_id = UUID();
 335       INSERT INTO event
 336             (event_id, model_id, action_cd, action_state_cd, user_id)
 337             VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_user_id);
 338           UPDATE model
 339                 SET event_id = v_event_id
 340                 WHERE model_id = v_model_id;
 341         END;
 342   ELSE
 343     BEGIN
 344           # use old control_name_prefix if null value is provided
 345       IF v_control_name_prefix IS NULL THEN
 346              SET v_control_name_prefix = v_old_control_name_prefix;
 347           END IF;
 348           # UUID can not be updated after initial insert
 349           SET v_control_name_uuid = v_old_control_name_uuid;
 350         END;
 351   END IF;
 352   IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN
 353         SET v_model_prop_id = UUID();
 354     INSERT INTO model_properties
 355           (model_prop_id, model_id, model_prop_text, user_id)
 356           VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_user_id);
 357         SET v_model_prop_user_id = v_user_id;
 358   END IF;
 359   IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN
 360         SET v_model_blueprint_id = UUID();
 361     INSERT INTO model_blueprint
 362           (model_blueprint_id, model_id, model_blueprint_text, user_id)
 363           VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_user_id);
 364         SET v_model_blueprint_user_id = v_user_id;
 365   END IF;
 366   UPDATE model
 367     SET control_name_prefix = v_control_name_prefix,
 368             model_prop_id = v_model_prop_id,
 369             model_blueprint_id = v_model_blueprint_id,
 370             service_type_id = v_service_type_id,
 371             deployment_id = v_deployment_id
 372     WHERE model_id = v_model_id;
 373 END;
 374 CREATE PROCEDURE ins_model_instance
 375   (IN v_control_name_uuid VARCHAR(36),
 376    IN v_vm_name VARCHAR(250),
 377    IN v_location VARCHAR(250),
 378    OUT v_model_id VARCHAR(36),
 379    OUT v_model_instance_id VARCHAR(36))
 380 BEGIN
 381    SELECT m.model_id
 382     INTO v_model_id
 383     FROM model m
 384     WHERE m.control_name_uuid = v_control_name_uuid;
 385   SET v_model_instance_id = UUID();
 386   INSERT INTO model_instance
 387         (model_instance_id, model_id, vm_name, location)
 388         VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location);
 389 END;
 390 CREATE PROCEDURE del_model_instance
 391   (IN v_control_name_uuid VARCHAR(36),
 392    IN v_vm_name VARCHAR(250),
 393    OUT v_model_id VARCHAR(36),
 394    OUT v_model_instance_id VARCHAR(36))
 395 BEGIN
 396    SELECT m.model_id, i.model_instance_id
 397     INTO v_model_id,
 398          v_model_instance_id
 399     FROM model m,
 400          model_instance i
 401     WHERE m.model_id = i.model_id
 402      AND  m.control_name_uuid = v_control_name_uuid
 403      AND  i.vm_name = v_vm_name;
 404   DELETE FROM model_instance
 405   WHERE model_instance_id = v_model_instance_id;
 406 END;
 407 CREATE PROCEDURE del_all_model_instances
 408   (IN v_control_name_uuid VARCHAR(36),
 409    OUT v_model_id VARCHAR(36))
 410 BEGIN
 411   SELECT m.model_id
 412     INTO v_model_id
 413     FROM model m
 414     WHERE m.control_name_uuid = v_control_name_uuid;
 415   DELETE FROM model_instance
 416   WHERE model_id = v_model_id;
 417 END;
 418 CREATE PROCEDURE ins_event
 419   (IN v_model_name VARCHAR(80),
 420    IN v_control_name_prefix VARCHAR(80),
 421    IN v_control_name_uuid VARCHAR(36),
 422    IN v_user_id VARCHAR(80),
 423    IN v_action_cd VARCHAR(80),
 424    IN v_action_state_cd VARCHAR(80),
 425    IN v_process_instance_id VARCHAR(80),
 426    OUT v_model_id VARCHAR(36),
 427    OUT v_event_id VARCHAR(36))
 428 BEGIN
 429   DECLARE v_prev_event_id VARCHAR(36);
 430   SELECT m.model_id,
 431                  m.event_id
 432     INTO v_model_id,
 433          v_prev_event_id
 434     FROM model m
 435     WHERE m.model_name = v_model_name
 436           OR  m.control_name_uuid = v_control_name_uuid;
 437   SET v_event_id = UUID();
 438   INSERT INTO event
 439         (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, user_id)
 440         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);
 441   UPDATE model
 442         SET event_id = v_event_id
 443         WHERE model_id = v_model_id;
 444 END;
 445 CREATE PROCEDURE upd_event
 446   (IN v_event_id VARCHAR(36),
 447    IN v_process_instance_id VARCHAR(80))
 448 BEGIN
 449   UPDATE event
 450         SET process_instance_id = v_process_instance_id
 451         WHERE event_id = v_event_id;
 452 END;
 453 CREATE PROCEDURE del_model (IN v_model_name VARCHAR(80))
 454 BEGIN
 455     DECLARE v_model_id VARCHAR(36);
 456     SELECT model_id INTO v_model_id from model where model_name = v_model_name;
 457     UPDATE model set event_id = null, model_blueprint_id = null, model_prop_id = null where model_id = v_model_id;
 458         DELETE from event where model_id = v_model_id;
 459         DELETE from model_blueprint where model_id = v_model_id;
 460         DELETE from model_properties where model_id = v_model_id;
 461     DELETE from model where model_id = v_model_id;
 462 END
 463
 464 CREATE PROCEDURE set_new_tosca_model_version
 465   (IN v_tosca_model_id VARCHAR(36),
 466    IN v_version DOUBLE,
 467    IN v_tosca_model_yaml MEDIUMTEXT,
 468    IN v_tosca_model_json MEDIUMTEXT,
 469    IN v_user_id VARCHAR(80),
 470    OUT v_revision_id VARCHAR(36))
 471 BEGIN
 472   SET v_revision_id = UUID();
 473   INSERT INTO tosca_model_revision
 474     (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id)
 475     VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id);
 476 END;
 477
 478 CREATE PROCEDURE set_tosca_model
 479   (IN v_tosca_model_name VARCHAR(80),
 480    IN v_policy_type VARCHAR(80),
 481    IN v_user_id VARCHAR(80),
 482    IN v_tosca_model_yaml MEDIUMTEXT,
 483    IN v_tosca_model_json MEDIUMTEXT,
 484    IN v_version DOUBLE,
 485    OUT v_tosca_model_id VARCHAR(36),
 486    OUT v_revision_id VARCHAR(36))
 487 BEGIN
 488   SET v_tosca_model_id = UUID();
 489   INSERT INTO tosca_model
 490     (tosca_model_id, tosca_model_name, policy_type, user_id)
 491     VALUES (v_tosca_model_id, v_tosca_model_name, v_policy_type, v_user_id);
 492   SET v_revision_id = UUID();
 493   INSERT INTO tosca_model_revision
 494     (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id)
 495     VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id);
 496 END;
 497
 498 CREATE PROCEDURE set_dictionary
 499   (IN v_dictionary_name VARCHAR(80),
 500    IN v_user_id VARCHAR(80),
 501    OUT v_dictionary_id VARCHAR(36))
 502 BEGIN
 503   SET v_dictionary_id = UUID();
 504   INSERT INTO dictionary
 505     (dictionary_id, dictionary_name, created_by, modified_by)
 506     VALUES (v_dictionary_id, v_dictionary_name, v_user_id, v_user_id);
 507 END;
 508
 509 CREATE PROCEDURE set_dictionary_elements
 510   (IN v_dictionary_id VARCHAR(36),
 511    IN v_dict_element_name VARCHAR(250),
 512    IN v_dict_element_short_name VARCHAR(80),
 513    IN v_dict_element_description VARCHAR(250),
 514    IN v_dict_element_type VARCHAR(80),
 515    IN v_user_id VARCHAR(80),
 516    OUT v_dict_element_id VARCHAR(36))
 517 BEGIN
 518   SET v_dict_element_id = UUID();
 519   INSERT INTO dictionary_elements
 520     (dict_element_id, dictionary_id, dict_element_name, dict_element_short_name, dict_element_description, dict_element_type, created_by, modified_by)
 521     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);
 522 END;
 523 //
 524 DELIMITER ;