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;
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_user_id VARCHAR(80),
23 OUT v_template_bpmn_text MEDIUMTEXT,
24 OUT v_template_image_id VARCHAR(36),
25 OUT v_template_image_user_id VARCHAR(80),
26 OUT v_template_image_text MEDIUMTEXT,
27 OUT v_template_doc_id VARCHAR(36),
28 OUT v_template_doc_user_id VARCHAR(80),
29 OUT v_template_doc_text MEDIUMTEXT)
34 tb.template_bpmn_text,
37 ti.template_image_text,
43 v_template_bpmn_user_id,
46 v_template_image_user_id,
47 v_template_image_text,
49 v_template_doc_user_id,
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;
60 CREATE PROCEDURE set_template
61 (IN v_template_name VARCHAR(80),
62 IN v_user_id VARCHAR(80),
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_user_id VARCHAR(80),
69 OUT v_template_image_id VARCHAR(36),
70 OUT v_template_image_user_id VARCHAR(80),
71 OUT v_template_doc_id VARCHAR(36),
72 OUT v_template_doc_user_id VARCHAR(80))
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;
82 v_template_bpmn_user_id,
83 v_old_template_bpmn_text,
85 v_template_image_user_id,
86 v_old_template_image_text,
88 v_template_doc_user_id,
89 v_old_template_doc_text);
90 IF v_template_id IS NULL THEN
92 SET v_template_id = UUID();
94 (template_id, template_name)
95 VALUES (v_template_id, v_template_name);
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, user_id)
102 VALUES (v_template_bpmn_id, v_template_id, v_template_bpmn_text, v_user_id);
103 SET v_template_bpmn_user_id = v_user_id;
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, user_id)
109 VALUES (v_template_image_id, v_template_id, v_template_image_text, v_user_id);
110 SET v_template_image_user_id = v_user_id;
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, user_id)
116 VALUES (v_template_doc_id, v_template_id, v_template_doc_text, v_user_id);
117 SET v_template_doc_user_id = v_user_id;
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;
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_service_type_id VARCHAR(80),
131 OUT v_deployment_id VARCHAR(80),
132 OUT v_template_name VARCHAR(80),
133 OUT v_template_id VARCHAR(36),
134 OUT v_model_prop_id VARCHAR(36),
135 OUT v_model_prop_user_id VARCHAR(80),
136 OUT v_model_prop_text MEDIUMTEXT,
137 OUT v_model_blueprint_id VARCHAR(36),
138 OUT v_model_blueprint_user_id VARCHAR(80),
139 OUT v_model_blueprint_text MEDIUMTEXT,
140 OUT v_event_id VARCHAR(36),
141 OUT v_action_cd VARCHAR(80),
142 OUT v_action_state_cd VARCHAR(80),
143 OUT v_event_process_instance_id VARCHAR(80),
144 OUT v_event_user_id VARCHAR(80))
146 SELECT m.control_name_prefix,
156 mb.model_blueprint_id,
158 mb.model_blueprint_text,
162 e.process_instance_id,
164 INTO v_control_name_prefix,
172 v_model_prop_user_id,
174 v_model_blueprint_id,
175 v_model_blueprint_user_id,
176 v_model_blueprint_text,
180 v_event_process_instance_id,
187 WHERE m.template_id = t.template_id
188 AND m.model_prop_id = mp.model_prop_id
189 AND m.model_blueprint_id = mb.model_blueprint_id
190 AND m.event_id = e.event_id
191 AND (m.model_name = v_model_name
192 OR m.control_name_uuid = v_control_name_uuid);
193 SELECT model_instance_id,
198 WHERE model_id = v_model_id
201 CREATE PROCEDURE get_model_template
202 (IN v_model_name VARCHAR(80),
203 OUT v_control_name_prefix VARCHAR(80),
204 INOUT v_control_name_uuid VARCHAR(36),
205 OUT v_model_id VARCHAR(36),
206 OUT v_service_type_id VARCHAR(80),
207 OUT v_deployment_id VARCHAR(80),
208 OUT v_template_name VARCHAR(80),
209 OUT v_template_id VARCHAR(36),
210 OUT v_model_prop_id VARCHAR(36),
211 OUT v_model_prop_user_id VARCHAR(80),
212 OUT v_model_prop_text MEDIUMTEXT,
213 OUT v_model_blueprint_id VARCHAR(36),
214 OUT v_model_blueprint_user_id VARCHAR(80),
215 OUT v_model_blueprint_text MEDIUMTEXT,
216 OUT v_template_bpmn_id VARCHAR(36),
217 OUT v_template_bpmn_user_id VARCHAR(80),
218 OUT v_template_bpmn_text MEDIUMTEXT,
219 OUT v_template_image_id VARCHAR(36),
220 OUT v_template_image_user_id VARCHAR(80),
221 OUT v_template_image_text MEDIUMTEXT,
222 OUT v_template_doc_id VARCHAR(36),
223 OUT v_template_doc_user_id VARCHAR(80),
224 OUT v_template_doc_text MEDIUMTEXT,
225 OUT v_event_id VARCHAR(36),
226 OUT v_action_cd VARCHAR(80),
227 OUT v_action_state_cd VARCHAR(80),
228 OUT v_event_process_instance_id VARCHAR(80),
229 OUT v_event_user_id VARCHAR(80))
233 v_control_name_prefix,
241 v_model_prop_user_id,
243 v_model_blueprint_id,
244 v_model_blueprint_user_id,
245 v_model_blueprint_text,
249 v_event_process_instance_id,
255 v_template_bpmn_user_id,
256 v_template_bpmn_text,
258 v_template_image_user_id,
259 v_template_image_text,
261 v_template_doc_user_id,
262 v_template_doc_text);
264 CREATE PROCEDURE set_model
265 (IN v_model_name VARCHAR(80),
266 IN v_template_id VARCHAR(36),
267 IN v_user_id VARCHAR(80),
268 IN v_model_prop_text MEDIUMTEXT,
269 IN v_model_blueprint_text MEDIUMTEXT,
270 IN v_service_type_id VARCHAR(80),
271 IN v_deployment_id VARCHAR(80),
272 INOUT v_control_name_prefix VARCHAR(80),
273 INOUT v_control_name_uuid VARCHAR(36),
274 OUT v_model_id VARCHAR(36),
275 OUT v_model_prop_id VARCHAR(36),
276 OUT v_model_prop_user_id VARCHAR(80),
277 OUT v_model_blueprint_id VARCHAR(36),
278 OUT v_model_blueprint_user_id VARCHAR(80),
279 OUT v_event_id VARCHAR(36),
280 OUT v_action_cd VARCHAR(80),
281 OUT v_action_state_cd VARCHAR(80),
282 OUT v_event_process_instance_id VARCHAR(80),
283 OUT v_event_user_id VARCHAR(80))
285 DECLARE v_old_template_name VARCHAR(80);
286 DECLARE v_old_template_id VARCHAR(36);
287 DECLARE v_old_control_name_prefix VARCHAR(80);
288 DECLARE v_old_control_name_uuid VARCHAR(36);
289 DECLARE v_old_model_prop_text MEDIUMTEXT;
290 DECLARE v_old_model_blueprint_text MEDIUMTEXT;
291 DECLARE v_old_service_type_id VARCHAR(80);
292 DECLARE v_old_deployment_id VARCHAR(80);
293 SET v_model_id = NULL;
296 v_old_control_name_prefix,
297 v_old_control_name_uuid,
299 v_old_service_type_id,
304 v_model_prop_user_id,
305 v_old_model_prop_text,
306 v_model_blueprint_id,
307 v_model_blueprint_user_id,
308 v_old_model_blueprint_text,
312 v_event_process_instance_id,
314 IF v_model_id IS NULL THEN
316 # UUID can be provided initially but cannot be updated
317 # if not provided (this is expected) then it will be set here
318 IF v_control_name_uuid IS NULL THEN
319 SET v_control_name_uuid = UUID();
321 SET v_model_id = v_control_name_uuid;
323 (model_id, model_name, template_id, control_name_prefix, control_name_uuid, service_type_id, deployment_id)
324 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);
325 # since just created model, insert CREATED event as initial default event
326 SET v_action_cd = 'CREATE';
327 SET v_action_state_cd = 'COMPLETED';
328 SET v_event_user_id = v_user_id;
329 SET v_event_id = UUID();
331 (event_id, model_id, action_cd, action_state_cd, user_id)
332 VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_user_id);
334 SET event_id = v_event_id
335 WHERE model_id = v_model_id;
339 # use old control_name_prefix if null value is provided
340 IF v_control_name_prefix IS NULL THEN
341 SET v_control_name_prefix = v_old_control_name_prefix;
343 # UUID can not be updated after initial insert
344 SET v_control_name_uuid = v_old_control_name_uuid;
347 IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN
348 SET v_model_prop_id = UUID();
349 INSERT INTO model_properties
350 (model_prop_id, model_id, model_prop_text, user_id)
351 VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_user_id);
352 SET v_model_prop_user_id = v_user_id;
354 IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN
355 SET v_model_blueprint_id = UUID();
356 INSERT INTO model_blueprint
357 (model_blueprint_id, model_id, model_blueprint_text, user_id)
358 VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_user_id);
359 SET v_model_blueprint_user_id = v_user_id;
362 SET control_name_prefix = v_control_name_prefix,
363 model_prop_id = v_model_prop_id,
364 model_blueprint_id = v_model_blueprint_id,
365 service_type_id = v_service_type_id,
366 deployment_id = v_deployment_id
367 WHERE model_id = v_model_id;
369 CREATE PROCEDURE ins_model_instance
370 (IN v_control_name_uuid VARCHAR(36),
371 IN v_vm_name VARCHAR(250),
372 IN v_location VARCHAR(250),
373 OUT v_model_id VARCHAR(36),
374 OUT v_model_instance_id VARCHAR(36))
379 WHERE m.control_name_uuid = v_control_name_uuid;
380 SET v_model_instance_id = UUID();
381 INSERT INTO model_instance
382 (model_instance_id, model_id, vm_name, location)
383 VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location);
385 CREATE PROCEDURE del_model_instance
386 (IN v_control_name_uuid VARCHAR(36),
387 IN v_vm_name VARCHAR(250),
388 OUT v_model_id VARCHAR(36),
389 OUT v_model_instance_id VARCHAR(36))
391 SELECT m.model_id, i.model_instance_id
396 WHERE m.model_id = i.model_id
397 AND m.control_name_uuid = v_control_name_uuid
398 AND i.vm_name = v_vm_name;
399 DELETE FROM model_instance
400 WHERE model_instance_id = v_model_instance_id;
402 CREATE PROCEDURE del_all_model_instances
403 (IN v_control_name_uuid VARCHAR(36),
404 OUT v_model_id VARCHAR(36))
409 WHERE m.control_name_uuid = v_control_name_uuid;
410 DELETE FROM model_instance
411 WHERE model_id = v_model_id;
413 CREATE PROCEDURE ins_event
414 (IN v_model_name VARCHAR(80),
415 IN v_control_name_prefix VARCHAR(80),
416 IN v_control_name_uuid VARCHAR(36),
417 IN v_user_id VARCHAR(80),
418 IN v_action_cd VARCHAR(80),
419 IN v_action_state_cd VARCHAR(80),
420 IN v_process_instance_id VARCHAR(80),
421 OUT v_model_id VARCHAR(36),
422 OUT v_event_id VARCHAR(36))
424 DECLARE v_prev_event_id VARCHAR(36);
430 WHERE m.model_name = v_model_name
431 OR m.control_name_uuid = v_control_name_uuid;
432 SET v_event_id = UUID();
434 (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, user_id)
435 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);
437 SET event_id = v_event_id
438 WHERE model_id = v_model_id;
440 CREATE PROCEDURE upd_event
441 (IN v_event_id VARCHAR(36),
442 IN v_process_instance_id VARCHAR(80))
445 SET process_instance_id = v_process_instance_id
446 WHERE event_id = v_event_id;