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_deployment_status_url VARCHAR(300),
138 OUT v_template_name VARCHAR(80),
139 OUT v_template_id VARCHAR(36),
140 OUT v_model_prop_id VARCHAR(36),
141 OUT v_model_prop_user_id VARCHAR(80),
142 OUT v_model_prop_text MEDIUMTEXT,
143 OUT v_model_blueprint_id VARCHAR(36),
144 OUT v_model_blueprint_user_id VARCHAR(80),
145 OUT v_model_blueprint_text MEDIUMTEXT,
146 OUT v_event_id VARCHAR(36),
147 OUT v_action_cd VARCHAR(80),
148 OUT v_action_state_cd VARCHAR(80),
149 OUT v_event_process_instance_id VARCHAR(80),
150 OUT v_event_user_id VARCHAR(80))
152 SELECT m.control_name_prefix,
157 m.deployment_status_url,
163 mb.model_blueprint_id,
165 mb.model_blueprint_text,
169 e.process_instance_id,
171 INTO v_control_name_prefix,
176 v_deployment_status_url,
180 v_model_prop_user_id,
182 v_model_blueprint_id,
183 v_model_blueprint_user_id,
184 v_model_blueprint_text,
188 v_event_process_instance_id,
195 WHERE m.template_id = t.template_id
196 AND m.model_prop_id = mp.model_prop_id
197 AND m.model_blueprint_id = mb.model_blueprint_id
198 AND m.event_id = e.event_id
199 AND (m.model_name = v_model_name
200 OR m.control_name_uuid = v_control_name_uuid);
201 SELECT model_instance_id,
206 WHERE model_id = v_model_id
209 CREATE PROCEDURE get_model_template
210 (IN v_model_name VARCHAR(80),
211 OUT v_control_name_prefix VARCHAR(80),
212 INOUT v_control_name_uuid VARCHAR(36),
213 OUT v_model_id VARCHAR(36),
214 OUT v_service_type_id VARCHAR(80),
215 OUT v_deployment_id VARCHAR(80),
216 OUT v_deployment_status_url VARCHAR(300),
217 OUT v_template_name VARCHAR(80),
218 OUT v_template_id VARCHAR(36),
219 OUT v_model_prop_id VARCHAR(36),
220 OUT v_model_prop_user_id VARCHAR(80),
221 OUT v_model_prop_text MEDIUMTEXT,
222 OUT v_model_blueprint_id VARCHAR(36),
223 OUT v_model_blueprint_user_id VARCHAR(80),
224 OUT v_model_blueprint_text MEDIUMTEXT,
225 OUT v_template_bpmn_id VARCHAR(36),
226 OUT v_template_bpmn_user_id VARCHAR(80),
227 OUT v_template_bpmn_text MEDIUMTEXT,
228 OUT v_template_image_id VARCHAR(36),
229 OUT v_template_image_user_id VARCHAR(80),
230 OUT v_template_image_text MEDIUMTEXT,
231 OUT v_template_doc_id VARCHAR(36),
232 OUT v_template_doc_user_id VARCHAR(80),
233 OUT v_template_doc_text MEDIUMTEXT,
234 OUT v_event_id VARCHAR(36),
235 OUT v_action_cd VARCHAR(80),
236 OUT v_action_state_cd VARCHAR(80),
237 OUT v_event_process_instance_id VARCHAR(80),
238 OUT v_event_user_id VARCHAR(80))
242 v_control_name_prefix,
247 v_deployment_status_url,
251 v_model_prop_user_id,
253 v_model_blueprint_id,
254 v_model_blueprint_user_id,
255 v_model_blueprint_text,
259 v_event_process_instance_id,
265 v_template_bpmn_user_id,
266 v_template_bpmn_text,
268 v_template_image_user_id,
269 v_template_image_text,
271 v_template_doc_user_id,
272 v_template_doc_text);
274 CREATE PROCEDURE set_model
275 (IN v_model_name VARCHAR(80),
276 IN v_template_id VARCHAR(36),
277 IN v_user_id VARCHAR(80),
278 IN v_model_prop_text MEDIUMTEXT,
279 IN v_model_blueprint_text MEDIUMTEXT,
280 IN v_service_type_id VARCHAR(80),
281 IN v_deployment_id VARCHAR(80),
282 IN v_deployment_status_url VARCHAR(300),
283 INOUT v_control_name_prefix VARCHAR(80),
284 INOUT v_control_name_uuid VARCHAR(36),
285 OUT v_model_id VARCHAR(36),
286 OUT v_model_prop_id VARCHAR(36),
287 OUT v_model_prop_user_id VARCHAR(80),
288 OUT v_model_blueprint_id VARCHAR(36),
289 OUT v_model_blueprint_user_id VARCHAR(80),
290 OUT v_event_id VARCHAR(36),
291 OUT v_action_cd VARCHAR(80),
292 OUT v_action_state_cd VARCHAR(80),
293 OUT v_event_process_instance_id VARCHAR(80),
294 OUT v_event_user_id VARCHAR(80))
296 DECLARE v_old_template_name VARCHAR(80);
297 DECLARE v_old_template_id VARCHAR(36);
298 DECLARE v_old_control_name_prefix VARCHAR(80);
299 DECLARE v_old_control_name_uuid VARCHAR(36);
300 DECLARE v_old_model_prop_text MEDIUMTEXT;
301 DECLARE v_old_model_blueprint_text MEDIUMTEXT;
302 DECLARE v_old_service_type_id VARCHAR(80);
303 DECLARE v_old_deployment_id VARCHAR(80);
304 DECLARE v_old_deployment_status_url VARCHAR(300);
305 SET v_model_id = NULL;
308 v_old_control_name_prefix,
309 v_old_control_name_uuid,
311 v_old_service_type_id,
313 v_old_deployment_status_url,
317 v_model_prop_user_id,
318 v_old_model_prop_text,
319 v_model_blueprint_id,
320 v_model_blueprint_user_id,
321 v_old_model_blueprint_text,
325 v_event_process_instance_id,
327 IF v_model_id IS NULL THEN
329 # UUID can be provided initially but cannot be updated
330 # if not provided (this is expected) then it will be set here
331 IF v_control_name_uuid IS NULL THEN
332 SET v_control_name_uuid = UUID();
334 SET v_model_id = v_control_name_uuid;
336 (model_id, model_name, template_id, control_name_prefix, control_name_uuid, service_type_id, deployment_id, deployment_status_url)
337 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,v_deployment_status_url);
338 # since just created model, insert CREATED event as initial default event
339 SET v_action_cd = 'CREATE';
340 SET v_action_state_cd = 'COMPLETED';
341 SET v_event_user_id = v_user_id;
342 SET v_event_id = UUID();
344 (event_id, model_id, action_cd, action_state_cd, user_id)
345 VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_user_id);
347 SET event_id = v_event_id
348 WHERE model_id = v_model_id;
352 # use old control_name_prefix if null value is provided
353 IF v_control_name_prefix IS NULL THEN
354 SET v_control_name_prefix = v_old_control_name_prefix;
356 # UUID can not be updated after initial insert
357 SET v_control_name_uuid = v_old_control_name_uuid;
360 IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN
361 SET v_model_prop_id = UUID();
362 INSERT INTO model_properties
363 (model_prop_id, model_id, model_prop_text, user_id)
364 VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_user_id);
365 SET v_model_prop_user_id = v_user_id;
367 IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN
368 SET v_model_blueprint_id = UUID();
369 INSERT INTO model_blueprint
370 (model_blueprint_id, model_id, model_blueprint_text, user_id)
371 VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_user_id);
372 SET v_model_blueprint_user_id = v_user_id;
375 SET control_name_prefix = v_control_name_prefix,
376 model_prop_id = v_model_prop_id,
377 model_blueprint_id = v_model_blueprint_id,
378 service_type_id = v_service_type_id,
379 deployment_id = v_deployment_id,
380 deployment_status_url = v_deployment_status_url
381 WHERE model_id = v_model_id;
383 CREATE PROCEDURE ins_model_instance
384 (IN v_control_name_uuid VARCHAR(36),
385 IN v_vm_name VARCHAR(250),
386 IN v_location VARCHAR(250),
387 OUT v_model_id VARCHAR(36),
388 OUT v_model_instance_id VARCHAR(36))
393 WHERE m.control_name_uuid = v_control_name_uuid;
394 SET v_model_instance_id = UUID();
395 INSERT INTO model_instance
396 (model_instance_id, model_id, vm_name, location)
397 VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location);
399 CREATE PROCEDURE del_model_instance
400 (IN v_control_name_uuid VARCHAR(36),
401 IN v_vm_name VARCHAR(250),
402 OUT v_model_id VARCHAR(36),
403 OUT v_model_instance_id VARCHAR(36))
405 SELECT m.model_id, i.model_instance_id
410 WHERE m.model_id = i.model_id
411 AND m.control_name_uuid = v_control_name_uuid
412 AND i.vm_name = v_vm_name;
413 DELETE FROM model_instance
414 WHERE model_instance_id = v_model_instance_id;
416 CREATE PROCEDURE del_all_model_instances
417 (IN v_control_name_uuid VARCHAR(36),
418 OUT v_model_id VARCHAR(36))
423 WHERE m.control_name_uuid = v_control_name_uuid;
424 DELETE FROM model_instance
425 WHERE model_id = v_model_id;
427 CREATE PROCEDURE ins_event
428 (IN v_model_name VARCHAR(80),
429 IN v_control_name_prefix VARCHAR(80),
430 IN v_control_name_uuid VARCHAR(36),
431 IN v_user_id VARCHAR(80),
432 IN v_action_cd VARCHAR(80),
433 IN v_action_state_cd VARCHAR(80),
434 IN v_process_instance_id VARCHAR(80),
435 OUT v_model_id VARCHAR(36),
436 OUT v_event_id VARCHAR(36))
438 DECLARE v_prev_event_id VARCHAR(36);
444 WHERE m.model_name = v_model_name
445 OR m.control_name_uuid = v_control_name_uuid;
446 SET v_event_id = UUID();
448 (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, user_id)
449 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);
451 SET event_id = v_event_id
452 WHERE model_id = v_model_id;
454 CREATE PROCEDURE upd_event
455 (IN v_event_id VARCHAR(36),
456 IN v_process_instance_id VARCHAR(80))
459 SET process_instance_id = v_process_instance_id
460 WHERE event_id = v_event_id;
462 CREATE PROCEDURE del_model
463 (IN v_model_name VARCHAR(80))
465 DECLARE v_model_id VARCHAR(36);
466 SELECT model_id INTO v_model_id from model where model_name = v_model_name;
467 UPDATE model set event_id = null, model_blueprint_id = null, model_prop_id = null where model_id = v_model_id;
468 DELETE from event where model_id = v_model_id;
469 DELETE from model_blueprint where model_id = v_model_id;
470 DELETE from model_properties where model_id = v_model_id;
471 DELETE from model where model_id = v_model_id;
474 CREATE PROCEDURE set_new_tosca_model_version
475 (IN v_tosca_model_id VARCHAR(36),
477 IN v_tosca_model_yaml MEDIUMTEXT,
478 IN v_tosca_model_json MEDIUMTEXT,
479 IN v_user_id VARCHAR(80),
480 OUT v_revision_id VARCHAR(36))
482 SET v_revision_id = UUID();
483 INSERT INTO tosca_model_revision
484 (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id)
485 VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id);
488 CREATE PROCEDURE set_tosca_model
489 (IN v_tosca_model_name VARCHAR(80),
490 IN v_policy_type VARCHAR(80),
491 IN v_user_id VARCHAR(80),
492 IN v_tosca_model_yaml MEDIUMTEXT,
493 IN v_tosca_model_json MEDIUMTEXT,
495 OUT v_tosca_model_id VARCHAR(36),
496 OUT v_revision_id VARCHAR(36))
498 SET v_tosca_model_id = UUID();
499 INSERT INTO tosca_model
500 (tosca_model_id, tosca_model_name, policy_type, user_id)
501 VALUES (v_tosca_model_id, v_tosca_model_name, v_policy_type, v_user_id);
502 SET v_revision_id = UUID();
503 INSERT INTO tosca_model_revision
504 (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id)
505 VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id);
508 CREATE PROCEDURE set_dictionary
509 (IN v_dictionary_name VARCHAR(80),
510 IN v_user_id VARCHAR(80),
511 OUT v_dictionary_id VARCHAR(36))
513 SET v_dictionary_id = UUID();
514 INSERT INTO dictionary
515 (dictionary_id, dictionary_name, created_by, modified_by)
516 VALUES (v_dictionary_id, v_dictionary_name, v_user_id, v_user_id);
519 CREATE PROCEDURE set_dictionary_elements
520 (IN v_dictionary_id VARCHAR(36),
521 IN v_dict_element_name VARCHAR(250),
522 IN v_dict_element_short_name VARCHAR(80),
523 IN v_dict_element_description VARCHAR(250),
524 IN v_dict_element_type VARCHAR(80),
525 IN v_user_id VARCHAR(80),
526 OUT v_dict_element_id VARCHAR(36))
528 SET v_dict_element_id = UUID();
529 INSERT INTO dictionary_elements
530 (dict_element_id, dictionary_id, dict_element_name, dict_element_short_name, dict_element_description, dict_element_type, created_by, modified_by)
531 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);