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;
32 DROP PROCEDURE IF EXISTS del_model;
33 DROP PROCEDURE IF EXISTS set_new_tosca_model_version;
34 DROP PROCEDURE IF EXISTS set_tosca_model;
35 DROP PROCEDURE IF EXISTS set_dictionary;
36 DROP PROCEDURE IF EXISTS set_dictionary_elements;
38 CREATE PROCEDURE get_template
39 (IN v_template_name VARCHAR(80),
40 OUT v_template_id VARCHAR(36),
41 OUT v_template_bpmn_id VARCHAR(36),
42 OUT v_template_bpmn_user_id VARCHAR(80),
43 OUT v_template_bpmn_text MEDIUMTEXT,
44 OUT v_template_image_id VARCHAR(36),
45 OUT v_template_image_user_id VARCHAR(80),
46 OUT v_template_image_text MEDIUMTEXT,
47 OUT v_template_doc_id VARCHAR(36),
48 OUT v_template_doc_user_id VARCHAR(80),
49 OUT v_template_doc_text MEDIUMTEXT)
54 tb.template_bpmn_text,
57 ti.template_image_text,
63 v_template_bpmn_user_id,
66 v_template_image_user_id,
67 v_template_image_text,
69 v_template_doc_user_id,
75 WHERE t.template_bpmn_id = tb.template_bpmn_id
76 AND t.template_image_id = ti.template_image_id
77 AND t.template_doc_id = td.template_doc_id
78 AND t.template_name = v_template_name;
80 CREATE PROCEDURE set_template
81 (IN v_template_name VARCHAR(80),
82 IN v_user_id VARCHAR(80),
83 IN v_template_bpmn_text MEDIUMTEXT,
84 IN v_template_image_text MEDIUMTEXT,
85 IN v_template_doc_text MEDIUMTEXT,
86 OUT v_template_id VARCHAR(36),
87 OUT v_template_bpmn_id VARCHAR(36),
88 OUT v_template_bpmn_user_id VARCHAR(80),
89 OUT v_template_image_id VARCHAR(36),
90 OUT v_template_image_user_id VARCHAR(80),
91 OUT v_template_doc_id VARCHAR(36),
92 OUT v_template_doc_user_id VARCHAR(80))
94 DECLARE v_old_template_bpmn_text MEDIUMTEXT;
95 DECLARE v_old_template_image_text MEDIUMTEXT;
96 DECLARE v_old_template_doc_text MEDIUMTEXT;
97 SET v_template_id = NULL;
102 v_template_bpmn_user_id,
103 v_old_template_bpmn_text,
105 v_template_image_user_id,
106 v_old_template_image_text,
108 v_template_doc_user_id,
109 v_old_template_doc_text);
110 IF v_template_id IS NULL THEN
112 SET v_template_id = UUID();
114 (template_id, template_name)
115 VALUES (v_template_id, v_template_name);
118 IF v_template_bpmn_id IS NULL OR v_template_bpmn_text <> v_old_template_bpmn_text THEN
119 SET v_template_bpmn_id = UUID();
120 INSERT INTO template_bpmn
121 (template_bpmn_id, template_id, template_bpmn_text, user_id)
122 VALUES (v_template_bpmn_id, v_template_id, v_template_bpmn_text, v_user_id);
123 SET v_template_bpmn_user_id = v_user_id;
125 IF v_template_image_id IS NULL OR v_template_image_text <> v_old_template_image_text THEN
126 SET v_template_image_id = UUID();
127 INSERT INTO template_image
128 (template_image_id, template_id, template_image_text, user_id)
129 VALUES (v_template_image_id, v_template_id, v_template_image_text, v_user_id);
130 SET v_template_image_user_id = v_user_id;
132 IF v_template_doc_id IS NULL OR v_template_doc_text <> v_old_template_doc_text THEN
133 SET v_template_doc_id = UUID();
134 INSERT INTO template_doc
135 (template_doc_id, template_id, template_doc_text, user_id)
136 VALUES (v_template_doc_id, v_template_id, v_template_doc_text, v_user_id);
137 SET v_template_doc_user_id = v_user_id;
140 SET template_bpmn_id = v_template_bpmn_id,
141 template_image_id = v_template_image_id,
142 template_doc_id = v_template_doc_id
143 WHERE template_id = v_template_id;
145 CREATE PROCEDURE get_model
146 (IN v_model_name VARCHAR(80),
147 OUT v_control_name_prefix VARCHAR(80),
148 INOUT v_control_name_uuid VARCHAR(36),
149 OUT v_model_id VARCHAR(36),
150 OUT v_service_type_id VARCHAR(80),
151 OUT v_deployment_id VARCHAR(80),
152 OUT v_template_name VARCHAR(80),
153 OUT v_template_id VARCHAR(36),
154 OUT v_model_prop_id VARCHAR(36),
155 OUT v_model_prop_user_id VARCHAR(80),
156 OUT v_model_prop_text MEDIUMTEXT,
157 OUT v_model_blueprint_id VARCHAR(36),
158 OUT v_model_blueprint_user_id VARCHAR(80),
159 OUT v_model_blueprint_text MEDIUMTEXT,
160 OUT v_event_id VARCHAR(36),
161 OUT v_action_cd VARCHAR(80),
162 OUT v_action_state_cd VARCHAR(80),
163 OUT v_event_process_instance_id VARCHAR(80),
164 OUT v_event_user_id VARCHAR(80))
166 SELECT m.control_name_prefix,
176 mb.model_blueprint_id,
178 mb.model_blueprint_text,
182 e.process_instance_id,
184 INTO v_control_name_prefix,
192 v_model_prop_user_id,
194 v_model_blueprint_id,
195 v_model_blueprint_user_id,
196 v_model_blueprint_text,
200 v_event_process_instance_id,
207 WHERE m.template_id = t.template_id
208 AND m.model_prop_id = mp.model_prop_id
209 AND m.model_blueprint_id = mb.model_blueprint_id
210 AND m.event_id = e.event_id
211 AND (m.model_name = v_model_name
212 OR m.control_name_uuid = v_control_name_uuid);
213 SELECT model_instance_id,
218 WHERE model_id = v_model_id
221 CREATE PROCEDURE get_model_template
222 (IN v_model_name VARCHAR(80),
223 OUT v_control_name_prefix VARCHAR(80),
224 INOUT v_control_name_uuid VARCHAR(36),
225 OUT v_model_id VARCHAR(36),
226 OUT v_service_type_id VARCHAR(80),
227 OUT v_deployment_id VARCHAR(80),
228 OUT v_template_name VARCHAR(80),
229 OUT v_template_id VARCHAR(36),
230 OUT v_model_prop_id VARCHAR(36),
231 OUT v_model_prop_user_id VARCHAR(80),
232 OUT v_model_prop_text MEDIUMTEXT,
233 OUT v_model_blueprint_id VARCHAR(36),
234 OUT v_model_blueprint_user_id VARCHAR(80),
235 OUT v_model_blueprint_text MEDIUMTEXT,
236 OUT v_template_bpmn_id VARCHAR(36),
237 OUT v_template_bpmn_user_id VARCHAR(80),
238 OUT v_template_bpmn_text MEDIUMTEXT,
239 OUT v_template_image_id VARCHAR(36),
240 OUT v_template_image_user_id VARCHAR(80),
241 OUT v_template_image_text MEDIUMTEXT,
242 OUT v_template_doc_id VARCHAR(36),
243 OUT v_template_doc_user_id VARCHAR(80),
244 OUT v_template_doc_text MEDIUMTEXT,
245 OUT v_event_id VARCHAR(36),
246 OUT v_action_cd VARCHAR(80),
247 OUT v_action_state_cd VARCHAR(80),
248 OUT v_event_process_instance_id VARCHAR(80),
249 OUT v_event_user_id VARCHAR(80))
253 v_control_name_prefix,
261 v_model_prop_user_id,
263 v_model_blueprint_id,
264 v_model_blueprint_user_id,
265 v_model_blueprint_text,
269 v_event_process_instance_id,
275 v_template_bpmn_user_id,
276 v_template_bpmn_text,
278 v_template_image_user_id,
279 v_template_image_text,
281 v_template_doc_user_id,
282 v_template_doc_text);
284 CREATE PROCEDURE set_model
285 (IN v_model_name VARCHAR(80),
286 IN v_template_id VARCHAR(36),
287 IN v_user_id VARCHAR(80),
288 IN v_model_prop_text MEDIUMTEXT,
289 IN v_model_blueprint_text MEDIUMTEXT,
290 IN v_service_type_id VARCHAR(80),
291 IN v_deployment_id VARCHAR(80),
292 INOUT v_control_name_prefix VARCHAR(80),
293 INOUT v_control_name_uuid VARCHAR(36),
294 OUT v_model_id VARCHAR(36),
295 OUT v_model_prop_id VARCHAR(36),
296 OUT v_model_prop_user_id VARCHAR(80),
297 OUT v_model_blueprint_id VARCHAR(36),
298 OUT v_model_blueprint_user_id VARCHAR(80),
299 OUT v_event_id VARCHAR(36),
300 OUT v_action_cd VARCHAR(80),
301 OUT v_action_state_cd VARCHAR(80),
302 OUT v_event_process_instance_id VARCHAR(80),
303 OUT v_event_user_id VARCHAR(80))
305 DECLARE v_old_template_name VARCHAR(80);
306 DECLARE v_old_template_id VARCHAR(36);
307 DECLARE v_old_control_name_prefix VARCHAR(80);
308 DECLARE v_old_control_name_uuid VARCHAR(36);
309 DECLARE v_old_model_prop_text MEDIUMTEXT;
310 DECLARE v_old_model_blueprint_text MEDIUMTEXT;
311 DECLARE v_old_service_type_id VARCHAR(80);
312 DECLARE v_old_deployment_id VARCHAR(80);
313 SET v_model_id = NULL;
316 v_old_control_name_prefix,
317 v_old_control_name_uuid,
319 v_old_service_type_id,
324 v_model_prop_user_id,
325 v_old_model_prop_text,
326 v_model_blueprint_id,
327 v_model_blueprint_user_id,
328 v_old_model_blueprint_text,
332 v_event_process_instance_id,
334 IF v_model_id IS NULL THEN
336 # UUID can be provided initially but cannot be updated
337 # if not provided (this is expected) then it will be set here
338 IF v_control_name_uuid IS NULL THEN
339 SET v_control_name_uuid = UUID();
341 SET v_model_id = v_control_name_uuid;
343 (model_id, model_name, template_id, control_name_prefix, control_name_uuid, service_type_id, deployment_id)
344 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);
345 # since just created model, insert CREATED event as initial default event
346 SET v_action_cd = 'CREATE';
347 SET v_action_state_cd = 'COMPLETED';
348 SET v_event_user_id = v_user_id;
349 SET v_event_id = UUID();
351 (event_id, model_id, action_cd, action_state_cd, user_id)
352 VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_user_id);
354 SET event_id = v_event_id
355 WHERE model_id = v_model_id;
359 # use old control_name_prefix if null value is provided
360 IF v_control_name_prefix IS NULL THEN
361 SET v_control_name_prefix = v_old_control_name_prefix;
363 # UUID can not be updated after initial insert
364 SET v_control_name_uuid = v_old_control_name_uuid;
367 IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN
368 SET v_model_prop_id = UUID();
369 INSERT INTO model_properties
370 (model_prop_id, model_id, model_prop_text, user_id)
371 VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_user_id);
372 SET v_model_prop_user_id = v_user_id;
374 IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN
375 SET v_model_blueprint_id = UUID();
376 INSERT INTO model_blueprint
377 (model_blueprint_id, model_id, model_blueprint_text, user_id)
378 VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_user_id);
379 SET v_model_blueprint_user_id = v_user_id;
382 SET control_name_prefix = v_control_name_prefix,
383 model_prop_id = v_model_prop_id,
384 model_blueprint_id = v_model_blueprint_id,
385 service_type_id = v_service_type_id,
386 deployment_id = v_deployment_id
387 WHERE model_id = v_model_id;
389 CREATE PROCEDURE ins_model_instance
390 (IN v_control_name_uuid VARCHAR(36),
391 IN v_vm_name VARCHAR(250),
392 IN v_location VARCHAR(250),
393 OUT v_model_id VARCHAR(36),
394 OUT v_model_instance_id VARCHAR(36))
399 WHERE m.control_name_uuid = v_control_name_uuid;
400 SET v_model_instance_id = UUID();
401 INSERT INTO model_instance
402 (model_instance_id, model_id, vm_name, location)
403 VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location);
405 CREATE PROCEDURE del_model_instance
406 (IN v_control_name_uuid VARCHAR(36),
407 IN v_vm_name VARCHAR(250),
408 OUT v_model_id VARCHAR(36),
409 OUT v_model_instance_id VARCHAR(36))
411 SELECT m.model_id, i.model_instance_id
416 WHERE m.model_id = i.model_id
417 AND m.control_name_uuid = v_control_name_uuid
418 AND i.vm_name = v_vm_name;
419 DELETE FROM model_instance
420 WHERE model_instance_id = v_model_instance_id;
422 CREATE PROCEDURE del_all_model_instances
423 (IN v_control_name_uuid VARCHAR(36),
424 OUT v_model_id VARCHAR(36))
429 WHERE m.control_name_uuid = v_control_name_uuid;
430 DELETE FROM model_instance
431 WHERE model_id = v_model_id;
433 CREATE PROCEDURE ins_event
434 (IN v_model_name VARCHAR(80),
435 IN v_control_name_prefix VARCHAR(80),
436 IN v_control_name_uuid VARCHAR(36),
437 IN v_user_id VARCHAR(80),
438 IN v_action_cd VARCHAR(80),
439 IN v_action_state_cd VARCHAR(80),
440 IN v_process_instance_id VARCHAR(80),
441 OUT v_model_id VARCHAR(36),
442 OUT v_event_id VARCHAR(36))
444 DECLARE v_prev_event_id VARCHAR(36);
450 WHERE m.model_name = v_model_name
451 OR m.control_name_uuid = v_control_name_uuid;
452 SET v_event_id = UUID();
454 (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, user_id)
455 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);
457 SET event_id = v_event_id
458 WHERE model_id = v_model_id;
460 CREATE PROCEDURE upd_event
461 (IN v_event_id VARCHAR(36),
462 IN v_process_instance_id VARCHAR(80))
465 SET process_instance_id = v_process_instance_id
466 WHERE event_id = v_event_id;
468 CREATE PROCEDURE del_model
469 (IN v_model_name VARCHAR(80))
471 DECLARE v_model_id VARCHAR(36);
472 SELECT model_id INTO v_model_id from model where model_name = v_model_name;
473 UPDATE model set event_id = null, model_blueprint_id = null, model_prop_id = null where model_id = v_model_id;
474 DELETE from event where model_id = v_model_id;
475 DELETE from model_blueprint where model_id = v_model_id;
476 DELETE from model_properties where model_id = v_model_id;
477 DELETE from model where model_id = v_model_id;
480 CREATE PROCEDURE set_new_tosca_model_version
481 (IN v_tosca_model_id VARCHAR(36),
483 IN v_tosca_model_yaml MEDIUMTEXT,
484 IN v_tosca_model_json MEDIUMTEXT,
485 IN v_user_id VARCHAR(80),
486 OUT v_revision_id VARCHAR(36))
488 SET v_revision_id = UUID();
489 INSERT INTO tosca_model_revision
490 (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id)
491 VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id);
494 CREATE PROCEDURE set_tosca_model
495 (IN v_tosca_model_name VARCHAR(80),
496 IN v_policy_type VARCHAR(80),
497 IN v_user_id VARCHAR(80),
498 IN v_tosca_model_yaml MEDIUMTEXT,
499 IN v_tosca_model_json MEDIUMTEXT,
501 OUT v_tosca_model_id VARCHAR(36),
502 OUT v_revision_id VARCHAR(36))
504 SET v_tosca_model_id = UUID();
505 INSERT INTO tosca_model
506 (tosca_model_id, tosca_model_name, policy_type, user_id)
507 VALUES (v_tosca_model_id, v_tosca_model_name, v_policy_type, v_user_id);
508 SET v_revision_id = UUID();
509 INSERT INTO tosca_model_revision
510 (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id)
511 VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id);
514 CREATE PROCEDURE set_dictionary
515 (IN v_dictionary_name VARCHAR(80),
516 IN v_user_id VARCHAR(80),
517 OUT v_dictionary_id VARCHAR(36))
519 SET v_dictionary_id = UUID();
520 INSERT INTO dictionary
521 (dictionary_id, dictionary_name, created_by, modified_by)
522 VALUES (v_dictionary_id, v_dictionary_name, v_user_id, v_user_id);
525 CREATE PROCEDURE set_dictionary_elements
526 (IN v_dictionary_id VARCHAR(36),
527 IN v_dict_element_name VARCHAR(250),
528 IN v_dict_element_short_name VARCHAR(80),
529 IN v_dict_element_description VARCHAR(250),
530 IN v_dict_element_type VARCHAR(80),
531 IN v_user_id VARCHAR(80),
532 OUT v_dict_element_id VARCHAR(36))
534 SET v_dict_element_id = UUID();
535 INSERT INTO dictionary_elements
536 (dict_element_id, dictionary_id, dict_element_name, dict_element_short_name, dict_element_description, dict_element_type, created_by, modified_by)
537 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);