2 # CLDS stored procedures
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;
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)
39 tb.template_bpmn_text,
42 ti.template_image_text,
48 v_template_bpmn_user_id,
51 v_template_image_user_id,
52 v_template_image_text,
54 v_template_doc_user_id,
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;
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))
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;
87 v_template_bpmn_user_id,
88 v_old_template_bpmn_text,
90 v_template_image_user_id,
91 v_old_template_image_text,
93 v_template_doc_user_id,
94 v_old_template_doc_text);
95 IF v_template_id IS NULL THEN
97 SET v_template_id = UUID();
99 (template_id, template_name)
100 VALUES (v_template_id, v_template_name);
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;
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;
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;
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;
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))
151 SELECT m.control_name_prefix,
161 mb.model_blueprint_id,
163 mb.model_blueprint_text,
167 e.process_instance_id,
169 INTO v_control_name_prefix,
177 v_model_prop_user_id,
179 v_model_blueprint_id,
180 v_model_blueprint_user_id,
181 v_model_blueprint_text,
185 v_event_process_instance_id,
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,
203 WHERE model_id = v_model_id
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))
238 v_control_name_prefix,
246 v_model_prop_user_id,
248 v_model_blueprint_id,
249 v_model_blueprint_user_id,
250 v_model_blueprint_text,
254 v_event_process_instance_id,
260 v_template_bpmn_user_id,
261 v_template_bpmn_text,
263 v_template_image_user_id,
264 v_template_image_text,
266 v_template_doc_user_id,
267 v_template_doc_text);
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))
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;
301 v_old_control_name_prefix,
302 v_old_control_name_uuid,
304 v_old_service_type_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,
317 v_event_process_instance_id,
319 IF v_model_id IS NULL THEN
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();
326 SET v_model_id = v_control_name_uuid;
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();
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);
339 SET event_id = v_event_id
340 WHERE model_id = v_model_id;
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;
348 # UUID can not be updated after initial insert
349 SET v_control_name_uuid = v_old_control_name_uuid;
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;
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;
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;
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))
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);
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))
396 SELECT m.model_id, i.model_instance_id
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;
407 CREATE PROCEDURE del_all_model_instances
408 (IN v_control_name_uuid VARCHAR(36),
409 OUT v_model_id VARCHAR(36))
414 WHERE m.control_name_uuid = v_control_name_uuid;
415 DELETE FROM model_instance
416 WHERE model_id = v_model_id;
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))
429 DECLARE v_prev_event_id VARCHAR(36);
435 WHERE m.model_name = v_model_name
436 OR m.control_name_uuid = v_control_name_uuid;
437 SET v_event_id = UUID();
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);
442 SET event_id = v_event_id
443 WHERE model_id = v_model_id;
445 CREATE PROCEDURE upd_event
446 (IN v_event_id VARCHAR(36),
447 IN v_process_instance_id VARCHAR(80))
450 SET process_instance_id = v_process_instance_id
451 WHERE event_id = v_event_id;
453 CREATE PROCEDURE del_model (IN v_model_name VARCHAR(80))
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;
464 CREATE PROCEDURE set_new_tosca_model_version
465 (IN v_tosca_model_id VARCHAR(36),
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))
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);
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,
485 OUT v_tosca_model_id VARCHAR(36),
486 OUT v_revision_id VARCHAR(36))
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);
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))
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);
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))
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);