1 /* Copyright © 2017 AT&T, Amdocs, Bell Canada
3 * Licensed under the Apache License, Version 2.0 (the "License");
4 * you may not use this file except in compliance with the License.
5 * You may obtain a copy of the License at
7 * http://www.apache.org/licenses/LICENSE-2.0
9 * Unless required by applicable law or agreed to in writing, software
10 * distributed under the License is distributed on an "AS IS" BASIS,
11 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12 * See the License for the specific language governing permissions and
13 * limitations under the License.
17 # CLDS stored procedures
22 DROP PROCEDURE IF EXISTS upd_event;
23 DROP PROCEDURE IF EXISTS ins_event;
24 DROP PROCEDURE IF EXISTS del_all_model_instances;
25 DROP PROCEDURE IF EXISTS del_model_instance;
26 DROP PROCEDURE IF EXISTS ins_model_instance;
27 DROP PROCEDURE IF EXISTS set_model;
28 DROP PROCEDURE IF EXISTS get_model;
29 DROP PROCEDURE IF EXISTS get_model_template;
30 DROP PROCEDURE IF EXISTS set_template;
31 DROP PROCEDURE IF EXISTS get_template;
33 CREATE PROCEDURE get_template
34 (IN v_template_name VARCHAR(80),
35 OUT v_template_id VARCHAR(36),
36 OUT v_template_bpmn_id VARCHAR(36),
37 OUT v_template_bpmn_user_id VARCHAR(80),
38 OUT v_template_bpmn_text MEDIUMTEXT,
39 OUT v_template_image_id VARCHAR(36),
40 OUT v_template_image_user_id VARCHAR(80),
41 OUT v_template_image_text MEDIUMTEXT,
42 OUT v_template_doc_id VARCHAR(36),
43 OUT v_template_doc_user_id VARCHAR(80),
44 OUT v_template_doc_text MEDIUMTEXT)
49 tb.template_bpmn_text,
52 ti.template_image_text,
58 v_template_bpmn_user_id,
61 v_template_image_user_id,
62 v_template_image_text,
64 v_template_doc_user_id,
70 WHERE t.template_bpmn_id = tb.template_bpmn_id
71 AND t.template_image_id = ti.template_image_id
72 AND t.template_doc_id = td.template_doc_id
73 AND t.template_name = v_template_name;
75 CREATE PROCEDURE set_template
76 (IN v_template_name VARCHAR(80),
77 IN v_user_id VARCHAR(80),
78 IN v_template_bpmn_text MEDIUMTEXT,
79 IN v_template_image_text MEDIUMTEXT,
80 IN v_template_doc_text MEDIUMTEXT,
81 OUT v_template_id VARCHAR(36),
82 OUT v_template_bpmn_id VARCHAR(36),
83 OUT v_template_bpmn_user_id VARCHAR(80),
84 OUT v_template_image_id VARCHAR(36),
85 OUT v_template_image_user_id VARCHAR(80),
86 OUT v_template_doc_id VARCHAR(36),
87 OUT v_template_doc_user_id VARCHAR(80))
89 DECLARE v_old_template_bpmn_text MEDIUMTEXT;
90 DECLARE v_old_template_image_text MEDIUMTEXT;
91 DECLARE v_old_template_doc_text MEDIUMTEXT;
92 SET v_template_id = NULL;
97 v_template_bpmn_user_id,
98 v_old_template_bpmn_text,
100 v_template_image_user_id,
101 v_old_template_image_text,
103 v_template_doc_user_id,
104 v_old_template_doc_text);
105 IF v_template_id IS NULL THEN
107 SET v_template_id = UUID();
109 (template_id, template_name)
110 VALUES (v_template_id, v_template_name);
113 IF v_template_bpmn_id IS NULL OR v_template_bpmn_text <> v_old_template_bpmn_text THEN
114 SET v_template_bpmn_id = UUID();
115 INSERT INTO template_bpmn
116 (template_bpmn_id, template_id, template_bpmn_text, user_id)
117 VALUES (v_template_bpmn_id, v_template_id, v_template_bpmn_text, v_user_id);
118 SET v_template_bpmn_user_id = v_user_id;
120 IF v_template_image_id IS NULL OR v_template_image_text <> v_old_template_image_text THEN
121 SET v_template_image_id = UUID();
122 INSERT INTO template_image
123 (template_image_id, template_id, template_image_text, user_id)
124 VALUES (v_template_image_id, v_template_id, v_template_image_text, v_user_id);
125 SET v_template_image_user_id = v_user_id;
127 IF v_template_doc_id IS NULL OR v_template_doc_text <> v_old_template_doc_text THEN
128 SET v_template_doc_id = UUID();
129 INSERT INTO template_doc
130 (template_doc_id, template_id, template_doc_text, user_id)
131 VALUES (v_template_doc_id, v_template_id, v_template_doc_text, v_user_id);
132 SET v_template_doc_user_id = v_user_id;
135 SET template_bpmn_id = v_template_bpmn_id,
136 template_image_id = v_template_image_id,
137 template_doc_id = v_template_doc_id
138 WHERE template_id = v_template_id;
140 CREATE PROCEDURE get_model
141 (IN v_model_name VARCHAR(80),
142 OUT v_control_name_prefix VARCHAR(80),
143 INOUT v_control_name_uuid VARCHAR(36),
144 OUT v_model_id VARCHAR(36),
145 OUT v_service_type_id VARCHAR(80),
146 OUT v_deployment_id VARCHAR(80),
147 OUT v_template_name VARCHAR(80),
148 OUT v_template_id VARCHAR(36),
149 OUT v_model_prop_id VARCHAR(36),
150 OUT v_model_prop_user_id VARCHAR(80),
151 OUT v_model_prop_text MEDIUMTEXT,
152 OUT v_model_blueprint_id VARCHAR(36),
153 OUT v_model_blueprint_user_id VARCHAR(80),
154 OUT v_model_blueprint_text MEDIUMTEXT,
155 OUT v_event_id VARCHAR(36),
156 OUT v_action_cd VARCHAR(80),
157 OUT v_action_state_cd VARCHAR(80),
158 OUT v_event_process_instance_id VARCHAR(80),
159 OUT v_event_user_id VARCHAR(80))
161 SELECT m.control_name_prefix,
171 mb.model_blueprint_id,
173 mb.model_blueprint_text,
177 e.process_instance_id,
179 INTO v_control_name_prefix,
187 v_model_prop_user_id,
189 v_model_blueprint_id,
190 v_model_blueprint_user_id,
191 v_model_blueprint_text,
195 v_event_process_instance_id,
202 WHERE m.template_id = t.template_id
203 AND m.model_prop_id = mp.model_prop_id
204 AND m.model_blueprint_id = mb.model_blueprint_id
205 AND m.event_id = e.event_id
206 AND (m.model_name = v_model_name
207 OR m.control_name_uuid = v_control_name_uuid);
208 SELECT model_instance_id,
213 WHERE model_id = v_model_id
216 CREATE PROCEDURE get_model_template
217 (IN v_model_name VARCHAR(80),
218 OUT v_control_name_prefix VARCHAR(80),
219 INOUT v_control_name_uuid VARCHAR(36),
220 OUT v_model_id VARCHAR(36),
221 OUT v_service_type_id VARCHAR(80),
222 OUT v_deployment_id VARCHAR(80),
223 OUT v_template_name VARCHAR(80),
224 OUT v_template_id VARCHAR(36),
225 OUT v_model_prop_id VARCHAR(36),
226 OUT v_model_prop_user_id VARCHAR(80),
227 OUT v_model_prop_text MEDIUMTEXT,
228 OUT v_model_blueprint_id VARCHAR(36),
229 OUT v_model_blueprint_user_id VARCHAR(80),
230 OUT v_model_blueprint_text MEDIUMTEXT,
231 OUT v_template_bpmn_id VARCHAR(36),
232 OUT v_template_bpmn_user_id VARCHAR(80),
233 OUT v_template_bpmn_text MEDIUMTEXT,
234 OUT v_template_image_id VARCHAR(36),
235 OUT v_template_image_user_id VARCHAR(80),
236 OUT v_template_image_text MEDIUMTEXT,
237 OUT v_template_doc_id VARCHAR(36),
238 OUT v_template_doc_user_id VARCHAR(80),
239 OUT v_template_doc_text MEDIUMTEXT,
240 OUT v_event_id VARCHAR(36),
241 OUT v_action_cd VARCHAR(80),
242 OUT v_action_state_cd VARCHAR(80),
243 OUT v_event_process_instance_id VARCHAR(80),
244 OUT v_event_user_id VARCHAR(80))
248 v_control_name_prefix,
256 v_model_prop_user_id,
258 v_model_blueprint_id,
259 v_model_blueprint_user_id,
260 v_model_blueprint_text,
264 v_event_process_instance_id,
270 v_template_bpmn_user_id,
271 v_template_bpmn_text,
273 v_template_image_user_id,
274 v_template_image_text,
276 v_template_doc_user_id,
277 v_template_doc_text);
279 CREATE PROCEDURE set_model
280 (IN v_model_name VARCHAR(80),
281 IN v_template_id VARCHAR(36),
282 IN v_user_id VARCHAR(80),
283 IN v_model_prop_text MEDIUMTEXT,
284 IN v_model_blueprint_text MEDIUMTEXT,
285 IN v_service_type_id VARCHAR(80),
286 IN v_deployment_id VARCHAR(80),
287 INOUT v_control_name_prefix VARCHAR(80),
288 INOUT v_control_name_uuid VARCHAR(36),
289 OUT v_model_id VARCHAR(36),
290 OUT v_model_prop_id VARCHAR(36),
291 OUT v_model_prop_user_id VARCHAR(80),
292 OUT v_model_blueprint_id VARCHAR(36),
293 OUT v_model_blueprint_user_id VARCHAR(80),
294 OUT v_event_id VARCHAR(36),
295 OUT v_action_cd VARCHAR(80),
296 OUT v_action_state_cd VARCHAR(80),
297 OUT v_event_process_instance_id VARCHAR(80),
298 OUT v_event_user_id VARCHAR(80))
300 DECLARE v_old_template_name VARCHAR(80);
301 DECLARE v_old_template_id VARCHAR(36);
302 DECLARE v_old_control_name_prefix VARCHAR(80);
303 DECLARE v_old_control_name_uuid VARCHAR(36);
304 DECLARE v_old_model_prop_text MEDIUMTEXT;
305 DECLARE v_old_model_blueprint_text MEDIUMTEXT;
306 DECLARE v_old_service_type_id VARCHAR(80);
307 DECLARE v_old_deployment_id VARCHAR(80);
308 SET v_model_id = NULL;
311 v_old_control_name_prefix,
312 v_old_control_name_uuid,
314 v_old_service_type_id,
319 v_model_prop_user_id,
320 v_old_model_prop_text,
321 v_model_blueprint_id,
322 v_model_blueprint_user_id,
323 v_old_model_blueprint_text,
327 v_event_process_instance_id,
329 IF v_model_id IS NULL THEN
331 # UUID can be provided initially but cannot be updated
332 # if not provided (this is expected) then it will be set here
333 IF v_control_name_uuid IS NULL THEN
334 SET v_control_name_uuid = UUID();
336 SET v_model_id = v_control_name_uuid;
338 (model_id, model_name, template_id, control_name_prefix, control_name_uuid, service_type_id, deployment_id)
339 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);
340 # since just created model, insert CREATED event as initial default event
341 SET v_action_cd = 'CREATE';
342 SET v_action_state_cd = 'COMPLETED';
343 SET v_event_user_id = v_user_id;
344 SET v_event_id = UUID();
346 (event_id, model_id, action_cd, action_state_cd, user_id)
347 VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_user_id);
349 SET event_id = v_event_id
350 WHERE model_id = v_model_id;
354 # use old control_name_prefix if null value is provided
355 IF v_control_name_prefix IS NULL THEN
356 SET v_control_name_prefix = v_old_control_name_prefix;
358 # UUID can not be updated after initial insert
359 SET v_control_name_uuid = v_old_control_name_uuid;
362 IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN
363 SET v_model_prop_id = UUID();
364 INSERT INTO model_properties
365 (model_prop_id, model_id, model_prop_text, user_id)
366 VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_user_id);
367 SET v_model_prop_user_id = v_user_id;
369 IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN
370 SET v_model_blueprint_id = UUID();
371 INSERT INTO model_blueprint
372 (model_blueprint_id, model_id, model_blueprint_text, user_id)
373 VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_user_id);
374 SET v_model_blueprint_user_id = v_user_id;
377 SET control_name_prefix = v_control_name_prefix,
378 model_prop_id = v_model_prop_id,
379 model_blueprint_id = v_model_blueprint_id,
380 service_type_id = v_service_type_id,
381 deployment_id = v_deployment_id
382 WHERE model_id = v_model_id;
384 CREATE PROCEDURE ins_model_instance
385 (IN v_control_name_uuid VARCHAR(36),
386 IN v_vm_name VARCHAR(250),
387 IN v_location VARCHAR(250),
388 OUT v_model_id VARCHAR(36),
389 OUT v_model_instance_id VARCHAR(36))
394 WHERE m.control_name_uuid = v_control_name_uuid;
395 SET v_model_instance_id = UUID();
396 INSERT INTO model_instance
397 (model_instance_id, model_id, vm_name, location)
398 VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location);
400 CREATE PROCEDURE del_model_instance
401 (IN v_control_name_uuid VARCHAR(36),
402 IN v_vm_name VARCHAR(250),
403 OUT v_model_id VARCHAR(36),
404 OUT v_model_instance_id VARCHAR(36))
406 SELECT m.model_id, i.model_instance_id
411 WHERE m.model_id = i.model_id
412 AND m.control_name_uuid = v_control_name_uuid
413 AND i.vm_name = v_vm_name;
414 DELETE FROM model_instance
415 WHERE model_instance_id = v_model_instance_id;
417 CREATE PROCEDURE del_all_model_instances
418 (IN v_control_name_uuid VARCHAR(36),
419 OUT v_model_id VARCHAR(36))
424 WHERE m.control_name_uuid = v_control_name_uuid;
425 DELETE FROM model_instance
426 WHERE model_id = v_model_id;
428 CREATE PROCEDURE ins_event
429 (IN v_model_name VARCHAR(80),
430 IN v_control_name_prefix VARCHAR(80),
431 IN v_control_name_uuid VARCHAR(36),
432 IN v_user_id VARCHAR(80),
433 IN v_action_cd VARCHAR(80),
434 IN v_action_state_cd VARCHAR(80),
435 IN v_process_instance_id VARCHAR(80),
436 OUT v_model_id VARCHAR(36),
437 OUT v_event_id VARCHAR(36))
439 DECLARE v_prev_event_id VARCHAR(36);
445 WHERE m.model_name = v_model_name
446 OR m.control_name_uuid = v_control_name_uuid;
447 SET v_event_id = UUID();
449 (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, user_id)
450 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);
452 SET event_id = v_event_id
453 WHERE model_id = v_model_id;
455 CREATE PROCEDURE upd_event
456 (IN v_event_id VARCHAR(36),
457 IN v_process_instance_id VARCHAR(80))
460 SET process_instance_id = v_process_instance_id
461 WHERE event_id = v_event_id;