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;
19 CREATE PROCEDURE get_template
20 (IN v_template_name VARCHAR(80),
21 OUT v_template_id VARCHAR(36),
22 OUT v_template_bpmn_id VARCHAR(36),
23 OUT v_template_bpmn_user_id VARCHAR(80),
24 OUT v_template_bpmn_text MEDIUMTEXT,
25 OUT v_template_image_id VARCHAR(36),
26 OUT v_template_image_user_id VARCHAR(80),
27 OUT v_template_image_text MEDIUMTEXT,
28 OUT v_template_doc_id VARCHAR(36),
29 OUT v_template_doc_user_id VARCHAR(80),
30 OUT v_template_doc_text MEDIUMTEXT)
35 tb.template_bpmn_text,
38 ti.template_image_text,
44 v_template_bpmn_user_id,
47 v_template_image_user_id,
48 v_template_image_text,
50 v_template_doc_user_id,
56 WHERE t.template_bpmn_id = tb.template_bpmn_id
57 AND t.template_image_id = ti.template_image_id
58 AND t.template_doc_id = td.template_doc_id
59 AND t.template_name = v_template_name;
61 CREATE PROCEDURE set_template
62 (IN v_template_name VARCHAR(80),
63 IN v_user_id VARCHAR(80),
64 IN v_template_bpmn_text MEDIUMTEXT,
65 IN v_template_image_text MEDIUMTEXT,
66 IN v_template_doc_text MEDIUMTEXT,
67 OUT v_template_id VARCHAR(36),
68 OUT v_template_bpmn_id VARCHAR(36),
69 OUT v_template_bpmn_user_id VARCHAR(80),
70 OUT v_template_image_id VARCHAR(36),
71 OUT v_template_image_user_id VARCHAR(80),
72 OUT v_template_doc_id VARCHAR(36),
73 OUT v_template_doc_user_id VARCHAR(80))
75 DECLARE v_old_template_bpmn_text MEDIUMTEXT;
76 DECLARE v_old_template_image_text MEDIUMTEXT;
77 DECLARE v_old_template_doc_text MEDIUMTEXT;
78 SET v_template_id = NULL;
83 v_template_bpmn_user_id,
84 v_old_template_bpmn_text,
86 v_template_image_user_id,
87 v_old_template_image_text,
89 v_template_doc_user_id,
90 v_old_template_doc_text);
91 IF v_template_id IS NULL THEN
93 SET v_template_id = UUID();
95 (template_id, template_name)
96 VALUES (v_template_id, v_template_name);
99 IF v_template_bpmn_id IS NULL OR v_template_bpmn_text <> v_old_template_bpmn_text THEN
100 SET v_template_bpmn_id = UUID();
101 INSERT INTO template_bpmn
102 (template_bpmn_id, template_id, template_bpmn_text, user_id)
103 VALUES (v_template_bpmn_id, v_template_id, v_template_bpmn_text, v_user_id);
104 SET v_template_bpmn_user_id = v_user_id;
106 IF v_template_image_id IS NULL OR v_template_image_text <> v_old_template_image_text THEN
107 SET v_template_image_id = UUID();
108 INSERT INTO template_image
109 (template_image_id, template_id, template_image_text, user_id)
110 VALUES (v_template_image_id, v_template_id, v_template_image_text, v_user_id);
111 SET v_template_image_user_id = v_user_id;
113 IF v_template_doc_id IS NULL OR v_template_doc_text <> v_old_template_doc_text THEN
114 SET v_template_doc_id = UUID();
115 INSERT INTO template_doc
116 (template_doc_id, template_id, template_doc_text, user_id)
117 VALUES (v_template_doc_id, v_template_id, v_template_doc_text, v_user_id);
118 SET v_template_doc_user_id = v_user_id;
121 SET template_bpmn_id = v_template_bpmn_id,
122 template_image_id = v_template_image_id,
123 template_doc_id = v_template_doc_id
124 WHERE template_id = v_template_id;
126 CREATE PROCEDURE get_model
127 (IN v_model_name VARCHAR(80),
128 OUT v_control_name_prefix VARCHAR(80),
129 INOUT v_control_name_uuid VARCHAR(36),
130 OUT v_model_id VARCHAR(36),
131 OUT v_service_type_id VARCHAR(80),
132 OUT v_deployment_id VARCHAR(80),
133 OUT v_template_name VARCHAR(80),
134 OUT v_template_id VARCHAR(36),
135 OUT v_model_prop_id VARCHAR(36),
136 OUT v_model_prop_user_id VARCHAR(80),
137 OUT v_model_prop_text MEDIUMTEXT,
138 OUT v_model_blueprint_id VARCHAR(36),
139 OUT v_model_blueprint_user_id VARCHAR(80),
140 OUT v_model_blueprint_text MEDIUMTEXT,
141 OUT v_event_id VARCHAR(36),
142 OUT v_action_cd VARCHAR(80),
143 OUT v_action_state_cd VARCHAR(80),
144 OUT v_event_process_instance_id VARCHAR(80),
145 OUT v_event_user_id VARCHAR(80))
147 SELECT m.control_name_prefix,
157 mb.model_blueprint_id,
159 mb.model_blueprint_text,
163 e.process_instance_id,
165 INTO v_control_name_prefix,
173 v_model_prop_user_id,
175 v_model_blueprint_id,
176 v_model_blueprint_user_id,
177 v_model_blueprint_text,
181 v_event_process_instance_id,
188 WHERE m.template_id = t.template_id
189 AND m.model_prop_id = mp.model_prop_id
190 AND m.model_blueprint_id = mb.model_blueprint_id
191 AND m.event_id = e.event_id
192 AND (m.model_name = v_model_name
193 OR m.control_name_uuid = v_control_name_uuid);
194 SELECT model_instance_id,
199 WHERE model_id = v_model_id
202 CREATE PROCEDURE get_model_template
203 (IN v_model_name VARCHAR(80),
204 OUT v_control_name_prefix VARCHAR(80),
205 INOUT v_control_name_uuid VARCHAR(36),
206 OUT v_model_id VARCHAR(36),
207 OUT v_service_type_id VARCHAR(80),
208 OUT v_deployment_id VARCHAR(80),
209 OUT v_template_name VARCHAR(80),
210 OUT v_template_id VARCHAR(36),
211 OUT v_model_prop_id VARCHAR(36),
212 OUT v_model_prop_user_id VARCHAR(80),
213 OUT v_model_prop_text MEDIUMTEXT,
214 OUT v_model_blueprint_id VARCHAR(36),
215 OUT v_model_blueprint_user_id VARCHAR(80),
216 OUT v_model_blueprint_text MEDIUMTEXT,
217 OUT v_template_bpmn_id VARCHAR(36),
218 OUT v_template_bpmn_user_id VARCHAR(80),
219 OUT v_template_bpmn_text MEDIUMTEXT,
220 OUT v_template_image_id VARCHAR(36),
221 OUT v_template_image_user_id VARCHAR(80),
222 OUT v_template_image_text MEDIUMTEXT,
223 OUT v_template_doc_id VARCHAR(36),
224 OUT v_template_doc_user_id VARCHAR(80),
225 OUT v_template_doc_text MEDIUMTEXT,
226 OUT v_event_id VARCHAR(36),
227 OUT v_action_cd VARCHAR(80),
228 OUT v_action_state_cd VARCHAR(80),
229 OUT v_event_process_instance_id VARCHAR(80),
230 OUT v_event_user_id VARCHAR(80))
234 v_control_name_prefix,
242 v_model_prop_user_id,
244 v_model_blueprint_id,
245 v_model_blueprint_user_id,
246 v_model_blueprint_text,
250 v_event_process_instance_id,
256 v_template_bpmn_user_id,
257 v_template_bpmn_text,
259 v_template_image_user_id,
260 v_template_image_text,
262 v_template_doc_user_id,
263 v_template_doc_text);
265 CREATE PROCEDURE set_model
266 (IN v_model_name VARCHAR(80),
267 IN v_template_id VARCHAR(36),
268 IN v_user_id VARCHAR(80),
269 IN v_model_prop_text MEDIUMTEXT,
270 IN v_model_blueprint_text MEDIUMTEXT,
271 IN v_service_type_id VARCHAR(80),
272 IN v_deployment_id VARCHAR(80),
273 INOUT v_control_name_prefix VARCHAR(80),
274 INOUT v_control_name_uuid VARCHAR(36),
275 OUT v_model_id VARCHAR(36),
276 OUT v_model_prop_id VARCHAR(36),
277 OUT v_model_prop_user_id VARCHAR(80),
278 OUT v_model_blueprint_id VARCHAR(36),
279 OUT v_model_blueprint_user_id VARCHAR(80),
280 OUT v_event_id VARCHAR(36),
281 OUT v_action_cd VARCHAR(80),
282 OUT v_action_state_cd VARCHAR(80),
283 OUT v_event_process_instance_id VARCHAR(80),
284 OUT v_event_user_id VARCHAR(80))
286 DECLARE v_old_template_name VARCHAR(80);
287 DECLARE v_old_template_id VARCHAR(36);
288 DECLARE v_old_control_name_prefix VARCHAR(80);
289 DECLARE v_old_control_name_uuid VARCHAR(36);
290 DECLARE v_old_model_prop_text MEDIUMTEXT;
291 DECLARE v_old_model_blueprint_text MEDIUMTEXT;
292 DECLARE v_old_service_type_id VARCHAR(80);
293 DECLARE v_old_deployment_id VARCHAR(80);
294 SET v_model_id = NULL;
297 v_old_control_name_prefix,
298 v_old_control_name_uuid,
300 v_old_service_type_id,
305 v_model_prop_user_id,
306 v_old_model_prop_text,
307 v_model_blueprint_id,
308 v_model_blueprint_user_id,
309 v_old_model_blueprint_text,
313 v_event_process_instance_id,
315 IF v_model_id IS NULL THEN
317 # UUID can be provided initially but cannot be updated
318 # if not provided (this is expected) then it will be set here
319 IF v_control_name_uuid IS NULL THEN
320 SET v_control_name_uuid = UUID();
322 SET v_model_id = v_control_name_uuid;
324 (model_id, model_name, template_id, control_name_prefix, control_name_uuid, service_type_id, deployment_id)
325 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);
326 # since just created model, insert CREATED event as initial default event
327 SET v_action_cd = 'CREATE';
328 SET v_action_state_cd = 'COMPLETED';
329 SET v_event_user_id = v_user_id;
330 SET v_event_id = UUID();
332 (event_id, model_id, action_cd, action_state_cd, user_id)
333 VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_user_id);
335 SET event_id = v_event_id
336 WHERE model_id = v_model_id;
340 # use old control_name_prefix if null value is provided
341 IF v_control_name_prefix IS NULL THEN
342 SET v_control_name_prefix = v_old_control_name_prefix;
344 # UUID can not be updated after initial insert
345 SET v_control_name_uuid = v_old_control_name_uuid;
348 IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN
349 SET v_model_prop_id = UUID();
350 INSERT INTO model_properties
351 (model_prop_id, model_id, model_prop_text, user_id)
352 VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_user_id);
353 SET v_model_prop_user_id = v_user_id;
355 IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN
356 SET v_model_blueprint_id = UUID();
357 INSERT INTO model_blueprint
358 (model_blueprint_id, model_id, model_blueprint_text, user_id)
359 VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_user_id);
360 SET v_model_blueprint_user_id = v_user_id;
363 SET control_name_prefix = v_control_name_prefix,
364 model_prop_id = v_model_prop_id,
365 model_blueprint_id = v_model_blueprint_id,
366 service_type_id = v_service_type_id,
367 deployment_id = v_deployment_id
368 WHERE model_id = v_model_id;
370 CREATE PROCEDURE ins_model_instance
371 (IN v_control_name_uuid VARCHAR(36),
372 IN v_vm_name VARCHAR(250),
373 IN v_location VARCHAR(250),
374 OUT v_model_id VARCHAR(36),
375 OUT v_model_instance_id VARCHAR(36))
380 WHERE m.control_name_uuid = v_control_name_uuid;
381 SET v_model_instance_id = UUID();
382 INSERT INTO model_instance
383 (model_instance_id, model_id, vm_name, location)
384 VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location);
386 CREATE PROCEDURE del_model_instance
387 (IN v_control_name_uuid VARCHAR(36),
388 IN v_vm_name VARCHAR(250),
389 OUT v_model_id VARCHAR(36),
390 OUT v_model_instance_id VARCHAR(36))
392 SELECT m.model_id, i.model_instance_id
397 WHERE m.model_id = i.model_id
398 AND m.control_name_uuid = v_control_name_uuid
399 AND i.vm_name = v_vm_name;
400 DELETE FROM model_instance
401 WHERE model_instance_id = v_model_instance_id;
403 CREATE PROCEDURE del_all_model_instances
404 (IN v_control_name_uuid VARCHAR(36),
405 OUT v_model_id VARCHAR(36))
410 WHERE m.control_name_uuid = v_control_name_uuid;
411 DELETE FROM model_instance
412 WHERE model_id = v_model_id;
414 CREATE PROCEDURE ins_event
415 (IN v_model_name VARCHAR(80),
416 IN v_control_name_prefix VARCHAR(80),
417 IN v_control_name_uuid VARCHAR(36),
418 IN v_user_id VARCHAR(80),
419 IN v_action_cd VARCHAR(80),
420 IN v_action_state_cd VARCHAR(80),
421 IN v_process_instance_id VARCHAR(80),
422 OUT v_model_id VARCHAR(36),
423 OUT v_event_id VARCHAR(36))
425 DECLARE v_prev_event_id VARCHAR(36);
431 WHERE m.model_name = v_model_name
432 OR m.control_name_uuid = v_control_name_uuid;
433 SET v_event_id = UUID();
435 (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, user_id)
436 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);
438 SET event_id = v_event_id
439 WHERE model_id = v_model_id;
441 CREATE PROCEDURE upd_event
442 (IN v_event_id VARCHAR(36),
443 IN v_process_instance_id VARCHAR(80))
446 SET process_instance_id = v_process_instance_id
447 WHERE event_id = v_event_id;
449 CREATE PROCEDURE del_model (IN v_model_name VARCHAR(80))
451 DECLARE v_model_id VARCHAR(36);
452 SELECT model_id INTO v_model_id from model where model_name = v_model_name;
453 UPDATE model set event_id = null, model_blueprint_id = null, model_prop_id = null where model_id = v_model_id;
454 DELETE from event where model_id = v_model_id;
455 DELETE from model_blueprint where model_id = v_model_id;
456 DELETE from model_properties where model_id = v_model_id;
457 DELETE from model where model_id = v_model_id;