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_deployment_status_url VARCHAR(300),
153 OUT v_template_name VARCHAR(80),
154 OUT v_template_id VARCHAR(36),
155 OUT v_model_prop_id VARCHAR(36),
156 OUT v_model_prop_user_id VARCHAR(80),
157 OUT v_model_prop_text MEDIUMTEXT,
158 OUT v_model_blueprint_id VARCHAR(36),
159 OUT v_model_blueprint_user_id VARCHAR(80),
160 OUT v_model_blueprint_text MEDIUMTEXT,
161 OUT v_event_id VARCHAR(36),
162 OUT v_action_cd VARCHAR(80),
163 OUT v_action_state_cd VARCHAR(80),
164 OUT v_event_process_instance_id VARCHAR(80),
165 OUT v_event_user_id VARCHAR(80))
167 SELECT m.control_name_prefix,
172 m.deployment_status_url,
178 mb.model_blueprint_id,
180 mb.model_blueprint_text,
184 e.process_instance_id,
186 INTO v_control_name_prefix,
191 v_deployment_status_url,
195 v_model_prop_user_id,
197 v_model_blueprint_id,
198 v_model_blueprint_user_id,
199 v_model_blueprint_text,
203 v_event_process_instance_id,
210 WHERE m.template_id = t.template_id
211 AND m.model_prop_id = mp.model_prop_id
212 AND m.model_blueprint_id = mb.model_blueprint_id
213 AND m.event_id = e.event_id
214 AND (m.model_name = v_model_name
215 OR m.control_name_uuid = v_control_name_uuid);
216 SELECT model_instance_id,
221 WHERE model_id = v_model_id
224 CREATE PROCEDURE get_model_template
225 (IN v_model_name VARCHAR(80),
226 OUT v_control_name_prefix VARCHAR(80),
227 INOUT v_control_name_uuid VARCHAR(36),
228 OUT v_model_id VARCHAR(36),
229 OUT v_service_type_id VARCHAR(80),
230 OUT v_deployment_id VARCHAR(80),
231 OUT v_deployment_status_url VARCHAR(300),
232 OUT v_template_name VARCHAR(80),
233 OUT v_template_id VARCHAR(36),
234 OUT v_model_prop_id VARCHAR(36),
235 OUT v_model_prop_user_id VARCHAR(80),
236 OUT v_model_prop_text MEDIUMTEXT,
237 OUT v_model_blueprint_id VARCHAR(36),
238 OUT v_model_blueprint_user_id VARCHAR(80),
239 OUT v_model_blueprint_text MEDIUMTEXT,
240 OUT v_template_bpmn_id VARCHAR(36),
241 OUT v_template_bpmn_user_id VARCHAR(80),
242 OUT v_template_bpmn_text MEDIUMTEXT,
243 OUT v_template_image_id VARCHAR(36),
244 OUT v_template_image_user_id VARCHAR(80),
245 OUT v_template_image_text MEDIUMTEXT,
246 OUT v_template_doc_id VARCHAR(36),
247 OUT v_template_doc_user_id VARCHAR(80),
248 OUT v_template_doc_text MEDIUMTEXT,
249 OUT v_event_id VARCHAR(36),
250 OUT v_action_cd VARCHAR(80),
251 OUT v_action_state_cd VARCHAR(80),
252 OUT v_event_process_instance_id VARCHAR(80),
253 OUT v_event_user_id VARCHAR(80))
257 v_control_name_prefix,
262 v_deployment_status_url,
266 v_model_prop_user_id,
268 v_model_blueprint_id,
269 v_model_blueprint_user_id,
270 v_model_blueprint_text,
274 v_event_process_instance_id,
280 v_template_bpmn_user_id,
281 v_template_bpmn_text,
283 v_template_image_user_id,
284 v_template_image_text,
286 v_template_doc_user_id,
287 v_template_doc_text);
289 CREATE PROCEDURE set_model
290 (IN v_model_name VARCHAR(80),
291 IN v_template_id VARCHAR(36),
292 IN v_user_id VARCHAR(80),
293 IN v_model_prop_text MEDIUMTEXT,
294 IN v_model_blueprint_text MEDIUMTEXT,
295 IN v_service_type_id VARCHAR(80),
296 IN v_deployment_id VARCHAR(80),
297 IN v_deployment_status_url VARCHAR(300),
298 INOUT v_control_name_prefix VARCHAR(80),
299 INOUT v_control_name_uuid VARCHAR(36),
300 OUT v_model_id VARCHAR(36),
301 OUT v_model_prop_id VARCHAR(36),
302 OUT v_model_prop_user_id VARCHAR(80),
303 OUT v_model_blueprint_id VARCHAR(36),
304 OUT v_model_blueprint_user_id VARCHAR(80),
305 OUT v_event_id VARCHAR(36),
306 OUT v_action_cd VARCHAR(80),
307 OUT v_action_state_cd VARCHAR(80),
308 OUT v_event_process_instance_id VARCHAR(80),
309 OUT v_event_user_id VARCHAR(80))
311 DECLARE v_old_template_name VARCHAR(80);
312 DECLARE v_old_template_id VARCHAR(36);
313 DECLARE v_old_control_name_prefix VARCHAR(80);
314 DECLARE v_old_control_name_uuid VARCHAR(36);
315 DECLARE v_old_model_prop_text MEDIUMTEXT;
316 DECLARE v_old_model_blueprint_text MEDIUMTEXT;
317 DECLARE v_old_service_type_id VARCHAR(80);
318 DECLARE v_old_deployment_id VARCHAR(80);
319 DECLARE v_old_deployment_status_url VARCHAR(300);
320 SET v_model_id = NULL;
323 v_old_control_name_prefix,
324 v_old_control_name_uuid,
326 v_old_service_type_id,
328 v_old_deployment_status_url,
332 v_model_prop_user_id,
333 v_old_model_prop_text,
334 v_model_blueprint_id,
335 v_model_blueprint_user_id,
336 v_old_model_blueprint_text,
340 v_event_process_instance_id,
342 IF v_model_id IS NULL THEN
344 # UUID can be provided initially but cannot be updated
345 # if not provided (this is expected) then it will be set here
346 IF v_control_name_uuid IS NULL THEN
347 SET v_control_name_uuid = UUID();
349 SET v_model_id = v_control_name_uuid;
351 (model_id, model_name, template_id, control_name_prefix, control_name_uuid, service_type_id, deployment_id, deployment_status_url)
352 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);
353 # since just created model, insert CREATED event as initial default event
354 SET v_action_cd = 'CREATE';
355 SET v_action_state_cd = 'COMPLETED';
356 SET v_event_user_id = v_user_id;
357 SET v_event_id = UUID();
359 (event_id, model_id, action_cd, action_state_cd, user_id)
360 VALUES (v_event_id, v_model_id, v_action_cd, v_action_state_cd, v_event_user_id);
362 SET event_id = v_event_id
363 WHERE model_id = v_model_id;
367 # use old control_name_prefix if null value is provided
368 IF v_control_name_prefix IS NULL THEN
369 SET v_control_name_prefix = v_old_control_name_prefix;
371 # UUID can not be updated after initial insert
372 SET v_control_name_uuid = v_old_control_name_uuid;
375 IF v_model_prop_id IS NULL OR v_model_prop_text <> v_old_model_prop_text THEN
376 SET v_model_prop_id = UUID();
377 INSERT INTO model_properties
378 (model_prop_id, model_id, model_prop_text, user_id)
379 VALUES (v_model_prop_id, v_model_id, v_model_prop_text, v_user_id);
380 SET v_model_prop_user_id = v_user_id;
382 IF v_model_blueprint_id IS NULL OR v_model_blueprint_text <> v_old_model_blueprint_text THEN
383 SET v_model_blueprint_id = UUID();
384 INSERT INTO model_blueprint
385 (model_blueprint_id, model_id, model_blueprint_text, user_id)
386 VALUES (v_model_blueprint_id, v_model_id, v_model_blueprint_text, v_user_id);
387 SET v_model_blueprint_user_id = v_user_id;
390 SET control_name_prefix = v_control_name_prefix,
391 model_prop_id = v_model_prop_id,
392 model_blueprint_id = v_model_blueprint_id,
393 service_type_id = v_service_type_id,
394 deployment_id = v_deployment_id,
395 deployment_status_url = v_deployment_status_url
396 WHERE model_id = v_model_id;
398 CREATE PROCEDURE ins_model_instance
399 (IN v_control_name_uuid VARCHAR(36),
400 IN v_vm_name VARCHAR(250),
401 IN v_location VARCHAR(250),
402 OUT v_model_id VARCHAR(36),
403 OUT v_model_instance_id VARCHAR(36))
408 WHERE m.control_name_uuid = v_control_name_uuid;
409 SET v_model_instance_id = UUID();
410 INSERT INTO model_instance
411 (model_instance_id, model_id, vm_name, location)
412 VALUES (v_model_instance_id, v_model_id, v_vm_name, v_location);
414 CREATE PROCEDURE del_model_instance
415 (IN v_control_name_uuid VARCHAR(36),
416 IN v_vm_name VARCHAR(250),
417 OUT v_model_id VARCHAR(36),
418 OUT v_model_instance_id VARCHAR(36))
420 SELECT m.model_id, i.model_instance_id
425 WHERE m.model_id = i.model_id
426 AND m.control_name_uuid = v_control_name_uuid
427 AND i.vm_name = v_vm_name;
428 DELETE FROM model_instance
429 WHERE model_instance_id = v_model_instance_id;
431 CREATE PROCEDURE del_all_model_instances
432 (IN v_control_name_uuid VARCHAR(36),
433 OUT v_model_id VARCHAR(36))
438 WHERE m.control_name_uuid = v_control_name_uuid;
439 DELETE FROM model_instance
440 WHERE model_id = v_model_id;
442 CREATE PROCEDURE ins_event
443 (IN v_model_name VARCHAR(80),
444 IN v_control_name_prefix VARCHAR(80),
445 IN v_control_name_uuid VARCHAR(36),
446 IN v_user_id VARCHAR(80),
447 IN v_action_cd VARCHAR(80),
448 IN v_action_state_cd VARCHAR(80),
449 IN v_process_instance_id VARCHAR(80),
450 OUT v_model_id VARCHAR(36),
451 OUT v_event_id VARCHAR(36))
453 DECLARE v_prev_event_id VARCHAR(36);
459 WHERE m.model_name = v_model_name
460 OR m.control_name_uuid = v_control_name_uuid;
461 SET v_event_id = UUID();
463 (event_id, model_id, action_cd, action_state_cd, prev_event_id, process_instance_id, user_id)
464 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);
466 SET event_id = v_event_id
467 WHERE model_id = v_model_id;
469 CREATE PROCEDURE upd_event
470 (IN v_event_id VARCHAR(36),
471 IN v_process_instance_id VARCHAR(80))
474 SET process_instance_id = v_process_instance_id
475 WHERE event_id = v_event_id;
477 CREATE PROCEDURE del_model
478 (IN v_model_name VARCHAR(80))
480 DECLARE v_model_id VARCHAR(36);
481 SELECT model_id INTO v_model_id from model where model_name = v_model_name;
482 UPDATE model set event_id = null, model_blueprint_id = null, model_prop_id = null where model_id = v_model_id;
483 DELETE from event where model_id = v_model_id;
484 DELETE from model_blueprint where model_id = v_model_id;
485 DELETE from model_properties where model_id = v_model_id;
486 DELETE from model where model_id = v_model_id;
489 CREATE PROCEDURE set_new_tosca_model_version
490 (IN v_tosca_model_id VARCHAR(36),
492 IN v_tosca_model_yaml MEDIUMTEXT,
493 IN v_tosca_model_json MEDIUMTEXT,
494 IN v_user_id VARCHAR(80),
495 OUT v_revision_id VARCHAR(36))
497 SET v_revision_id = UUID();
498 INSERT INTO tosca_model_revision
499 (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id)
500 VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id);
503 CREATE PROCEDURE set_tosca_model
504 (IN v_tosca_model_name VARCHAR(80),
505 IN v_policy_type VARCHAR(80),
506 IN v_user_id VARCHAR(80),
507 IN v_tosca_model_yaml MEDIUMTEXT,
508 IN v_tosca_model_json MEDIUMTEXT,
510 OUT v_tosca_model_id VARCHAR(36),
511 OUT v_revision_id VARCHAR(36))
513 SET v_tosca_model_id = UUID();
514 INSERT INTO tosca_model
515 (tosca_model_id, tosca_model_name, policy_type, user_id)
516 VALUES (v_tosca_model_id, v_tosca_model_name, v_policy_type, v_user_id);
517 SET v_revision_id = UUID();
518 INSERT INTO tosca_model_revision
519 (tosca_model_revision_id, tosca_model_id, version, tosca_model_yaml, tosca_model_json, user_id)
520 VALUES (v_revision_id, v_tosca_model_id, v_version, v_tosca_model_yaml, v_tosca_model_json, v_user_id);
523 CREATE PROCEDURE set_dictionary
524 (IN v_dictionary_name VARCHAR(80),
525 IN v_user_id VARCHAR(80),
526 OUT v_dictionary_id VARCHAR(36))
528 SET v_dictionary_id = UUID();
529 INSERT INTO dictionary
530 (dictionary_id, dictionary_name, created_by, modified_by)
531 VALUES (v_dictionary_id, v_dictionary_name, v_user_id, v_user_id);
534 CREATE PROCEDURE set_dictionary_elements
535 (IN v_dictionary_id VARCHAR(36),
536 IN v_dict_element_name VARCHAR(250),
537 IN v_dict_element_short_name VARCHAR(80),
538 IN v_dict_element_description VARCHAR(250),
539 IN v_dict_element_type VARCHAR(80),
540 IN v_user_id VARCHAR(80),
541 OUT v_dict_element_id VARCHAR(36))
543 SET v_dict_element_id = UUID();
544 INSERT INTO dictionary_elements
545 (dict_element_id, dictionary_id, dict_element_name, dict_element_short_name, dict_element_description, dict_element_type, created_by, modified_by)
546 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);