2 * ============LICENSE_START=======================================================
4 * ================================================================================
5 * Copyright (C) 2017-2019 AT&T Intellectual Property. All rights
7 * ================================================================================
8 * Licensed under the Apache License, Version 2.0 (the "License");
9 * you may not use this file except in compliance with the License.
10 * You may obtain a copy of the License at
12 * http://www.apache.org/licenses/LICENSE-2.0
14 * Unless required by applicable law or agreed to in writing, software
15 * distributed under the License is distributed on an "AS IS" BASIS,
16 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 * See the License for the specific language governing permissions and
18 * limitations under the License.
19 * ============LICENSE_END============================================
20 * ===================================================================
24 package org.onap.clamp.clds.dao;
26 import com.att.eelf.configuration.EELFLogger;
27 import com.att.eelf.configuration.EELFManager;
29 import java.text.SimpleDateFormat;
30 import java.util.ArrayList;
31 import java.util.HashMap;
32 import java.util.List;
35 import javax.sql.DataSource;
37 import org.onap.clamp.clds.model.CldsDictionary;
38 import org.onap.clamp.clds.model.CldsDictionaryItem;
39 import org.onap.clamp.clds.model.CldsEvent;
40 import org.onap.clamp.clds.model.CldsModel;
41 import org.onap.clamp.clds.model.CldsModelInstance;
42 import org.onap.clamp.clds.model.CldsModelProp;
43 import org.onap.clamp.clds.model.CldsMonitoringDetails;
44 import org.onap.clamp.clds.model.CldsTemplate;
45 import org.onap.clamp.clds.model.CldsToscaModel;
46 import org.onap.clamp.clds.model.ValueItem;
47 import org.springframework.beans.factory.annotation.Autowired;
48 import org.springframework.beans.factory.annotation.Qualifier;
49 import org.springframework.jdbc.core.JdbcTemplate;
50 import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
51 import org.springframework.jdbc.core.namedparam.SqlParameterSource;
52 import org.springframework.jdbc.core.simple.SimpleJdbcCall;
53 import org.springframework.stereotype.Repository;
56 * Data Access for CLDS Model tables.
58 @Repository("cldsDao")
59 public class CldsDao {
61 private static final EELFLogger logger = EELFManager.getInstance().getLogger(CldsDao.class);
62 private JdbcTemplate jdbcTemplateObject;
63 private SimpleJdbcCall procGetModel;
64 private SimpleJdbcCall procGetModelTemplate;
65 private SimpleJdbcCall procSetModel;
66 private SimpleJdbcCall procInsEvent;
67 private SimpleJdbcCall procUpdEvent;
68 private SimpleJdbcCall procSetTemplate;
69 private SimpleJdbcCall procGetTemplate;
70 private SimpleJdbcCall procDelAllModelInstances;
71 private SimpleJdbcCall procInsModelInstance;
72 private SimpleJdbcCall procDeleteModel;
73 private static final String HEALTHCHECK = "Select 1";
74 private static final String V_CONTROL_NAME_PREFIX = "v_control_name_prefix";
75 private static final String V_CONTROL_NAME_UUID = "v_control_name_uuid";
77 private SimpleJdbcCall procInsertToscaModel;
78 private SimpleJdbcCall procInsertNewToscaModelVersion;
79 private SimpleJdbcCall procInsertDictionary;
80 private SimpleJdbcCall procInsertDictionaryElement;
82 private static final String DATE_FORMAT = "MM-dd-yyyy HH:mm:ss";
85 * Log message when instantiating.
88 public CldsDao(@Qualifier("cldsDataSource") DataSource dataSource) {
89 logger.info("CldsDao instantiating...");
90 setDataSource(dataSource);
94 * When dataSource is provided, instantiate spring jdbc objects.
99 public void setDataSource(DataSource dataSource) {
100 this.jdbcTemplateObject = new JdbcTemplate(dataSource);
101 this.procGetModel = new SimpleJdbcCall(dataSource).withProcedureName("get_model");
102 this.procGetModelTemplate = new SimpleJdbcCall(dataSource).withProcedureName("get_model_template");
103 this.procSetModel = new SimpleJdbcCall(dataSource).withProcedureName("set_model");
104 this.procInsEvent = new SimpleJdbcCall(dataSource).withProcedureName("ins_event");
105 this.procUpdEvent = new SimpleJdbcCall(dataSource).withProcedureName("upd_event");
106 this.procGetTemplate = new SimpleJdbcCall(dataSource).withProcedureName("get_template");
107 this.procSetTemplate = new SimpleJdbcCall(dataSource).withProcedureName("set_template");
108 this.procInsModelInstance = new SimpleJdbcCall(dataSource).withProcedureName("ins_model_instance");
109 this.procDelAllModelInstances = new SimpleJdbcCall(dataSource).withProcedureName("del_all_model_instances");
110 this.procDeleteModel = new SimpleJdbcCall(dataSource).withProcedureName("del_model");
111 this.procInsertToscaModel = new SimpleJdbcCall(dataSource).withProcedureName("set_tosca_model");
112 this.procInsertNewToscaModelVersion = new SimpleJdbcCall(dataSource)
113 .withProcedureName("set_new_tosca_model_version");
114 this.procInsertDictionary = new SimpleJdbcCall(dataSource).withProcedureName("set_dictionary");
115 this.procInsertDictionaryElement = new SimpleJdbcCall(dataSource).withProcedureName("set_dictionary_elements");
119 * Get a model from the database given the model name.
125 public CldsModel getModel(String modelName) {
126 return getModel(modelName, null);
129 // Get a model from the database given the model name or a controlNameUuid.
130 private CldsModel getModel(String modelName, String controlNameUuid) {
131 CldsModel model = new CldsModel();
132 model.setName(modelName);
133 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName)
134 .addValue(V_CONTROL_NAME_UUID, controlNameUuid);
135 Map<String, Object> out = logSqlExecution(procGetModel, in);
136 populateModelProperties(model, out);
141 * Get a model from the database given the controlNameUuid.
143 * @param controlNameUuid
144 * the control name uuid
145 * @return the model by uuid
147 public CldsModel getModelByUuid(String controlNameUuid) {
148 return getModel(null, controlNameUuid);
152 * Get a model and template information from the database given the model name.
156 * @return model model template
159 public CldsModel getModelTemplate(String modelName) {
160 CldsModel model = new CldsModel();
161 model.setName(modelName);
162 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName);
163 Map<String, Object> out = logSqlExecution(procGetModelTemplate, in);
164 populateModelProperties(model, out);
165 Map<String, Object> modelResults = logSqlExecution(procGetModel, in);
166 Object modelResultObject = modelResults.get("#result-set-1");
167 if (modelResultObject instanceof ArrayList) {
168 for (Object currModelInstance : (List<Object>) modelResultObject) {
169 if (currModelInstance instanceof HashMap) {
170 HashMap<String, String> modelInstanceMap = (HashMap<String, String>) currModelInstance;
171 CldsModelInstance modelInstance = new CldsModelInstance();
172 modelInstance.setModelInstanceId(modelInstanceMap.get("model_instance_id"));
173 modelInstance.setVmName(modelInstanceMap.get("vm_name"));
174 modelInstance.setLocation(modelInstanceMap.get("location"));
175 model.getCldsModelInstanceList().add(modelInstance);
176 logger.info("value of currModel: {}", currModelInstance);
184 * Update model in the database using parameter values and return updated model
193 public CldsModel setModel(CldsModel model, String userid) {
194 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", model.getName())
195 .addValue("v_template_id", model.getTemplateId()).addValue("v_user_id", userid)
196 .addValue("v_model_prop_text", model.getPropText())
197 .addValue("v_model_blueprint_text", model.getBlueprintText())
198 .addValue("v_service_type_id", model.getTypeId()).addValue("v_deployment_id", model.getDeploymentId())
199 .addValue("v_deployment_status_url", model.getDeploymentStatusUrl())
200 .addValue("v_control_name_prefix", model.getControlNamePrefix())
201 .addValue(V_CONTROL_NAME_UUID, model.getControlNameUuid());
202 Map<String, Object> out = logSqlExecution(procSetModel, in);
203 model.setControlNamePrefix((String) out.get(V_CONTROL_NAME_PREFIX));
204 model.setControlNameUuid((String) out.get(V_CONTROL_NAME_UUID));
205 model.setId((String) (out.get("v_model_id")));
206 model.getEvent().setId((String) (out.get("v_event_id")));
207 model.getEvent().setActionCd((String) out.get("v_action_cd"));
208 model.getEvent().setActionStateCd((String) out.get("v_action_state_cd"));
209 model.getEvent().setProcessInstanceId((String) out.get("v_event_process_instance_id"));
210 model.getEvent().setUserid((String) out.get("v_event_user_id"));
215 * Inserts new modelInstance in the database using parameter values and return
216 * updated model object.
220 * @param modelInstancesList
221 * the model instances list
223 public void insModelInstance(CldsModel model, List<CldsModelInstance> modelInstancesList) {
224 // Delete all existing model instances for given controlNameUUID
225 logger.debug("deleting instances for: {}", model.getControlNameUuid());
226 delAllModelInstances(model.getControlNameUuid());
227 if (modelInstancesList == null) {
228 logger.debug("modelInstancesList == null");
230 for (CldsModelInstance currModelInstance : modelInstancesList) {
231 logger.debug("v_control_name_uuid={}", model.getControlNameUuid());
232 logger.debug("v_vm_name={}", currModelInstance.getVmName());
233 logger.debug("v_location={}", currModelInstance.getLocation());
234 SqlParameterSource in = new MapSqlParameterSource()
235 .addValue(V_CONTROL_NAME_UUID, model.getControlNameUuid())
236 .addValue("v_vm_name", currModelInstance.getVmName())
237 .addValue("v_location", currModelInstance.getLocation());
238 Map<String, Object> out = logSqlExecution(procInsModelInstance, in);
239 model.setId((String) (out.get("v_model_id")));
240 CldsModelInstance modelInstance = new CldsModelInstance();
241 modelInstance.setLocation(currModelInstance.getLocation());
242 modelInstance.setVmName(currModelInstance.getVmName());
243 modelInstance.setModelInstanceId((String) (out.get("v_model_instance_id")));
244 model.getCldsModelInstanceList().add(modelInstance);
250 * Insert an event in the database - require either modelName or
251 * controlNamePrefix/controlNameUuid.
255 * @param controlNamePrefix
256 * the control name prefix
257 * @param controlNameUuid
258 * the control name uuid
263 public CldsEvent insEvent(String modelName, String controlNamePrefix, String controlNameUuid, CldsEvent cldsEvent) {
264 CldsEvent event = new CldsEvent();
265 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName)
266 .addValue(V_CONTROL_NAME_PREFIX, controlNamePrefix).addValue(V_CONTROL_NAME_UUID, controlNameUuid)
267 .addValue("v_user_id", cldsEvent.getUserid()).addValue("v_action_cd", cldsEvent.getActionCd())
268 .addValue("v_action_state_cd", cldsEvent.getActionStateCd())
269 .addValue("v_process_instance_id", cldsEvent.getProcessInstanceId());
270 Map<String, Object> out = logSqlExecution(procInsEvent, in);
271 event.setId((String) (out.get("v_event_id")));
275 private String delAllModelInstances(String controlNameUUid) {
276 SqlParameterSource in = new MapSqlParameterSource().addValue(V_CONTROL_NAME_UUID, controlNameUUid);
277 Map<String, Object> out = logSqlExecution(procDelAllModelInstances, in);
278 return (String) (out.get("v_model_id"));
282 * Update event with process instance id.
286 * @param processInstanceId
287 * the process instance id
289 public void updEvent(String eventId, String processInstanceId) {
290 SqlParameterSource in = new MapSqlParameterSource().addValue("v_event_id", eventId)
291 .addValue("v_process_instance_id", processInstanceId);
292 logSqlExecution(procUpdEvent, in);
296 * Return list of model names.
298 * @return model names
300 public List<ValueItem> getModelNames() {
301 String sql = "SELECT model_name FROM model ORDER BY 1;";
302 return jdbcTemplateObject.query(sql, new ValueItemMapper());
306 * Update template in the database using parameter values and return updated
314 public void setTemplate(CldsTemplate template, String userid) {
315 SqlParameterSource in = new MapSqlParameterSource().addValue("v_template_name", template.getName())
316 .addValue("v_user_id", userid).addValue("v_template_bpmn_text", template.getBpmnText())
317 .addValue("v_template_image_text", template.getImageText())
318 .addValue("v_template_doc_text", template.getPropText());
319 Map<String, Object> out = logSqlExecution(procSetTemplate, in);
320 template.setId((String) (out.get("v_template_id")));
321 template.setBpmnUserid((String) (out.get("v_template_bpmn_user_id")));
322 template.setBpmnId((String) (out.get("v_template_bpmn_id")));
323 template.setImageId((String) (out.get("v_template_image_id")));
324 template.setImageUserid((String) out.get("v_template_image_user_id"));
325 template.setPropId((String) (out.get("v_template_doc_id")));
326 template.setPropUserid((String) out.get("v_template_doc_user_id"));
330 * Return list of template names.
332 * @return template names
334 public List<ValueItem> getTemplateNames() {
335 String sql = "SELECT template_name FROM template ORDER BY 1;";
336 return jdbcTemplateObject.query(sql, new ValueItemMapper());
340 * Get a template from the database given the model name.
342 * @param templateName
344 * @return model template
346 public CldsTemplate getTemplate(String templateName) {
347 CldsTemplate template = new CldsTemplate();
348 template.setName(templateName);
349 SqlParameterSource in = new MapSqlParameterSource().addValue("v_template_name", templateName);
350 Map<String, Object> out = logSqlExecution(procGetTemplate, in);
351 template.setId((String) (out.get("v_template_id")));
352 template.setBpmnUserid((String) (out.get("v_template_bpmn_user_id")));
353 template.setBpmnId((String) (out.get("v_template_bpmn_id")));
354 template.setBpmnText((String) (out.get("v_template_bpmn_text")));
355 template.setImageId((String) (out.get("v_template_image_id")));
356 template.setImageUserid((String) out.get("v_template_image_user_id"));
357 template.setImageText((String) out.get("v_template_image_text"));
358 template.setPropId((String) (out.get("v_template_doc_id")));
359 template.setPropUserid((String) out.get("v_template_doc_user_id"));
360 template.setPropText((String) out.get("v_template_doc_text"));
364 private static Map<String, Object> logSqlExecution(SimpleJdbcCall call, SqlParameterSource source) {
366 return call.execute(source);
367 } catch (Exception e) {
368 logger.error("Exception occured in " + source.getClass().getCanonicalName() + ": " + e);
376 public void doHealthCheck() {
377 jdbcTemplateObject.execute(HEALTHCHECK);
381 * Method to get deployed/active models with model properties.
383 * @return list of CldsModelProp
385 public List<CldsModelProp> getDeployedModelProperties() {
386 List<CldsModelProp> cldsModelPropList = new ArrayList<>();
387 String modelsSql = "select m.model_id, m.model_name, mp.model_prop_id, mp.model_prop_text FROM model m, "
388 + "model_properties mp, event e "
389 + "WHERE m.model_prop_id = mp.model_prop_id and m.event_id = e.event_id and e.action_cd = 'DEPLOY'";
390 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(modelsSql);
391 CldsModelProp cldsModelProp = null;
392 for (Map<String, Object> row : rows) {
393 cldsModelProp = new CldsModelProp();
394 cldsModelProp.setId((String) row.get("model_id"));
395 cldsModelProp.setName((String) row.get("model_name"));
396 cldsModelProp.setPropId((String) row.get("model_prop_id"));
397 cldsModelProp.setPropText((String) row.get("model_prop_text"));
398 cldsModelPropList.add(cldsModelProp);
400 return cldsModelPropList;
404 * Method to get deployed/active models with model properties.
406 * @return list of CLDS-Monitoring-Details: CLOSELOOP_NAME | Close loop name
407 * used in the CLDS application (prefix: ClosedLoop- + unique ClosedLoop
408 * ID) MODEL_NAME | Model Name in CLDS application SERVICE_TYPE_ID |
409 * TypeId returned from the DCAE application when the ClosedLoop is
410 * submitted (DCAEServiceTypeRequest generated in DCAE application).
411 * DEPLOYMENT_ID | Id generated when the ClosedLoop is deployed in DCAE.
412 * TEMPLATE_NAME | Template used to generate the ClosedLoop model.
413 * ACTION_CD | Current state of the ClosedLoop in CLDS application.
415 public List<CldsMonitoringDetails> getCldsMonitoringDetails() {
416 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
417 List<CldsMonitoringDetails> cldsMonitoringDetailsList = new ArrayList<>();
418 String modelsSql = "SELECT CONCAT(M.CONTROL_NAME_PREFIX, M.CONTROL_NAME_UUID) AS CLOSELOOP_NAME , "
419 + "M.MODEL_NAME, M.SERVICE_TYPE_ID, M.DEPLOYMENT_ID, T.TEMPLATE_NAME, E.ACTION_CD, E.USER_ID, E.TIMESTAMP "
420 + "FROM MODEL M, TEMPLATE T, EVENT E " + "WHERE M.TEMPLATE_ID = T.TEMPLATE_ID AND M.EVENT_ID = E.EVENT_ID "
421 + "ORDER BY ACTION_CD";
422 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(modelsSql);
423 CldsMonitoringDetails cldsMonitoringDetails = null;
424 for (Map<String, Object> row : rows) {
425 cldsMonitoringDetails = new CldsMonitoringDetails();
426 cldsMonitoringDetails.setCloseloopName((String) row.get("CLOSELOOP_NAME"));
427 cldsMonitoringDetails.setModelName((String) row.get("MODEL_NAME"));
428 cldsMonitoringDetails.setServiceTypeId((String) row.get("SERVICE_TYPE_ID"));
429 cldsMonitoringDetails.setDeploymentId((String) row.get("DEPLOYMENT_ID"));
430 cldsMonitoringDetails.setTemplateName((String) row.get("TEMPLATE_NAME"));
431 cldsMonitoringDetails.setAction((String) row.get("ACTION_CD"));
432 cldsMonitoringDetails.setUserid((String) row.get("USER_ID"));
433 cldsMonitoringDetails.setTimestamp(sdf.format(row.get("TIMESTAMP")));
434 cldsMonitoringDetailsList.add(cldsMonitoringDetails);
436 return cldsMonitoringDetailsList;
440 * Method to delete model from database.
445 public void deleteModel(String modelName) {
446 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName);
447 logSqlExecution(procDeleteModel, in);
450 private void populateModelProperties(CldsModel model, Map out) {
451 model.setControlNamePrefix((String) out.get(V_CONTROL_NAME_PREFIX));
452 model.setControlNameUuid((String) out.get(V_CONTROL_NAME_UUID));
453 model.setId((String) (out.get("v_model_id")));
454 model.setTemplateId((String) (out.get("v_template_id")));
455 model.setTemplateName((String) (out.get("v_template_name")));
456 model.setBpmnText((String) out.get("v_template_bpmn_text"));
457 model.setPropText((String) out.get("v_model_prop_text"));
458 model.setImageText((String) out.get("v_template_image_text"));
459 model.setDocText((String) out.get("v_template_doc_text"));
460 model.setBlueprintText((String) out.get("v_model_blueprint_text"));
461 model.getEvent().setId((String) (out.get("v_event_id")));
462 model.getEvent().setActionCd((String) out.get("v_action_cd"));
463 model.getEvent().setActionStateCd((String) out.get("v_action_state_cd"));
464 model.getEvent().setProcessInstanceId((String) out.get("v_event_process_instance_id"));
465 model.getEvent().setUserid((String) out.get("v_event_user_id"));
466 model.setTypeId((String) out.get("v_service_type_id"));
467 model.setDeploymentId((String) out.get("v_deployment_id"));
468 model.setDeploymentStatusUrl((String) out.get("v_deployment_status_url"));
472 * Method to retrieve a tosca models by Policy Type from database.
474 * @return List of CldsToscaModel
476 public List<CldsToscaModel> getAllToscaModels() {
477 return getToscaModel(null, null);
481 * Method to retrieve a tosca models by Policy Type from database.
485 * @return List of CldsToscaModel
487 public List<CldsToscaModel> getToscaModelByPolicyType(String policyType) {
488 return getToscaModel(null, policyType);
492 * Method to retrieve a tosca models by toscaModelName, version from database.
494 * @param toscaModelName
495 * the tosca model name
496 * @return List of CldsToscaModel
498 public List<CldsToscaModel> getToscaModelByName(String toscaModelName) {
499 return getToscaModel(toscaModelName, null);
502 // Retrieve the latest tosca model for a policy type or by tosca model name
504 private List<CldsToscaModel> getToscaModel(String toscaModelName, String policyType) {
505 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
506 List<CldsToscaModel> cldsToscaModels = new ArrayList<>();
508 String toscaModelSql = "SELECT tm.tosca_model_name, tm.tosca_model_id, tm.policy_type, "
509 + "tmr.tosca_model_revision_id, tmr.tosca_model_json, tmr.version, tmr.user_id, tmr.createdTimestamp, "
510 + "tmr.lastUpdatedTimestamp " + ((toscaModelName != null) ? (", tmr.tosca_model_yaml ") : " ")
511 + "FROM tosca_model tm, tosca_model_revision tmr WHERE tm.tosca_model_id = tmr.tosca_model_id "
512 + ((toscaModelName != null) ? (" AND tm.tosca_model_name = '" + toscaModelName + "'") : " ")
513 + ((policyType != null) ? (" AND tm.policy_type = '" + policyType + "'") : " ")
514 + "AND tmr.version = (select max(version) from tosca_model_revision st "
515 + "where tmr.tosca_model_id=st.tosca_model_id)";
517 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(toscaModelSql);
520 rows.forEach(row -> {
521 CldsToscaModel cldsToscaModel = new CldsToscaModel();
522 cldsToscaModel.setId((String) row.get("tosca_model_id"));
523 cldsToscaModel.setPolicyType((String) row.get("policy_type"));
524 cldsToscaModel.setToscaModelName((String) row.get("tosca_model_name"));
525 cldsToscaModel.setUserId((String) row.get("user_id"));
526 cldsToscaModel.setRevisionId((String) row.get("tosca_model_revision_id"));
527 cldsToscaModel.setToscaModelJson((String) row.get("tosca_model_json"));
528 cldsToscaModel.setVersion(((Double) row.get("version")));
529 cldsToscaModel.setCreatedDate(sdf.format(row.get("createdTimestamp")));
530 cldsToscaModel.setLastUpdatedDate(sdf.format(row.get("lastUpdatedTimestamp")));
531 if (toscaModelName != null) {
532 cldsToscaModel.setToscaModelYaml((String) row.get("tosca_model_yaml"));
534 cldsToscaModels.add(cldsToscaModel);
537 return cldsToscaModels;
541 * Method to upload a new version of Tosca Model Yaml in Database.
543 * @param cldsToscaModel
544 * the clds tosca model
547 * @return CldsToscaModel clds tosca model
549 public CldsToscaModel updateToscaModelWithNewVersion(CldsToscaModel cldsToscaModel, String userId) {
550 SqlParameterSource in = new MapSqlParameterSource().addValue("v_tosca_model_id", cldsToscaModel.getId())
551 .addValue("v_version", cldsToscaModel.getVersion())
552 .addValue("v_tosca_model_yaml", cldsToscaModel.getToscaModelYaml())
553 .addValue("v_tosca_model_json", cldsToscaModel.getToscaModelJson()).addValue("v_user_id", userId);
554 Map<String, Object> out = logSqlExecution(procInsertNewToscaModelVersion, in);
555 cldsToscaModel.setRevisionId((String) (out.get("v_revision_id")));
556 return cldsToscaModel;
560 * Method to upload a new Tosca model Yaml in DB. Default version is 1.0
562 * @param cldsToscaModel
563 * the clds tosca model
566 * @return CldsToscaModel clds tosca model
568 public CldsToscaModel insToscaModel(CldsToscaModel cldsToscaModel, String userId) {
569 SqlParameterSource in = new MapSqlParameterSource()
570 .addValue("v_tosca_model_name", cldsToscaModel.getToscaModelName())
571 .addValue("v_policy_type", cldsToscaModel.getPolicyType())
572 .addValue("v_tosca_model_yaml", cldsToscaModel.getToscaModelYaml())
573 .addValue("v_tosca_model_json", cldsToscaModel.getToscaModelJson())
574 .addValue("v_version", cldsToscaModel.getVersion()).addValue("v_user_id", userId);
575 Map<String, Object> out = logSqlExecution(procInsertToscaModel, in);
576 cldsToscaModel.setId((String) (out.get("v_tosca_model_id")));
577 cldsToscaModel.setRevisionId((String) (out.get("v_revision_id")));
578 cldsToscaModel.setUserId((String) out.get("v_user_id"));
579 return cldsToscaModel;
583 * Method to insert a new Dictionary in Database.
585 * @param cldsDictionary
586 * the clds dictionary
588 public void insDictionary(CldsDictionary cldsDictionary) {
589 SqlParameterSource in = new MapSqlParameterSource()
590 .addValue("v_dictionary_name", cldsDictionary.getDictionaryName())
591 .addValue("v_user_id", cldsDictionary.getCreatedBy());
592 Map<String, Object> out = logSqlExecution(procInsertDictionary, in);
593 cldsDictionary.setDictionaryId((String) (out.get("v_dictionary_id")));
597 * Method to update Dictionary with new info in Database.
599 * @param dictionaryId
601 * @param cldsDictionary
602 * the clds dictionary
606 public void updateDictionary(String dictionaryId, CldsDictionary cldsDictionary, String userId) {
608 String dictionarySql = "UPDATE dictionary " + "SET dictionary_name = '" + cldsDictionary.getDictionaryName()
609 + "', modified_by = '" + userId + "'" + "WHERE dictionary_id = '" + dictionaryId + "'";
610 jdbcTemplateObject.update(dictionarySql);
611 cldsDictionary.setUpdatedBy(userId);
615 * Method to get list of Dictionaries from the Database.
617 * @param dictionaryId
619 * @param dictionaryName
620 * the dictionary name
623 public List<CldsDictionary> getDictionary(String dictionaryId, String dictionaryName) {
624 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
625 List<CldsDictionary> dictionaries = new ArrayList<>();
626 String dictionarySql = "SELECT dictionary_id, dictionary_name, created_by, modified_by, "
627 + "timestamp FROM dictionary"
628 + ((dictionaryId != null || dictionaryName != null)
629 ? (" WHERE " + ((dictionaryName != null) ? ("dictionary_name = '" + dictionaryName + "'") : "")
630 + ((dictionaryId != null && dictionaryName != null) ? (" AND ") : "")
631 + ((dictionaryId != null) ? ("dictionary_id = '" + dictionaryId + "'") : ""))
634 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(dictionarySql);
637 rows.forEach(row -> {
638 CldsDictionary cldsDictionary = new CldsDictionary();
639 cldsDictionary.setDictionaryId((String) row.get("dictionary_id"));
640 cldsDictionary.setDictionaryName((String) row.get("dictionary_name"));
641 cldsDictionary.setCreatedBy((String) row.get("created_by"));
642 cldsDictionary.setUpdatedBy((String) row.get("modified_by"));
643 cldsDictionary.setLastUpdatedDate(sdf.format(row.get("timestamp")));
644 dictionaries.add(cldsDictionary);
651 * Method to insert a new Dictionary Element for given dictionary in Database.
653 * @param cldsDictionaryItem
654 * the clds dictionary item
658 public void insDictionarElements(CldsDictionaryItem cldsDictionaryItem, String userId) {
659 SqlParameterSource in = new MapSqlParameterSource()
660 .addValue("v_dictionary_id", cldsDictionaryItem.getDictionaryId())
661 .addValue("v_dict_element_name", cldsDictionaryItem.getDictElementName())
662 .addValue("v_dict_element_short_name", cldsDictionaryItem.getDictElementShortName())
663 .addValue("v_dict_element_description", cldsDictionaryItem.getDictElementDesc())
664 .addValue("v_dict_element_type", cldsDictionaryItem.getDictElementType()).addValue("v_user_id", userId);
665 Map<String, Object> out = logSqlExecution(procInsertDictionaryElement, in);
666 cldsDictionaryItem.setDictElementId((String) (out.get("v_dict_element_id")));
670 * Method to update Dictionary Elements with new info for a given dictionary in
673 * @param dictionaryElementId
674 * the dictionary element id
675 * @param cldsDictionaryItem
676 * the clds dictionary item
680 public void updateDictionaryElements(String dictionaryElementId, CldsDictionaryItem cldsDictionaryItem,
683 String dictionarySql = "UPDATE dictionary_elements SET dict_element_name = '"
684 + cldsDictionaryItem.getDictElementName() + "', dict_element_short_name = '"
685 + cldsDictionaryItem.getDictElementShortName() + "', dict_element_description= '"
686 + cldsDictionaryItem.getDictElementDesc() + "', dict_element_type = '"
687 + cldsDictionaryItem.getDictElementType() + "', modified_by = '" + userId + "' "
688 + "WHERE dict_element_id = '" + dictionaryElementId + "'";
689 jdbcTemplateObject.update(dictionarySql);
690 cldsDictionaryItem.setUpdatedBy(userId);
694 * Method to get list of all dictionary elements for a given dictionary in the
697 * @param dictionaryName
698 * the dictionary name
699 * @param dictionaryId
701 * @param dictElementShortName
702 * the dict element short name
703 * @return dictionary elements
705 public List<CldsDictionaryItem> getDictionaryElements(String dictionaryName, String dictionaryId,
706 String dictElementShortName) {
707 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
708 List<CldsDictionaryItem> dictionaryItems = new ArrayList<>();
709 String dictionarySql = "SELECT de.dict_element_id, de.dictionary_id, de.dict_element_name, "
710 + "de.dict_element_short_name, de.dict_element_description, de.dict_element_type, de.created_by, "
711 + "de.modified_by, de.timestamp "
712 + "FROM dictionary_elements de, dictionary d WHERE de.dictionary_id = d.dictionary_id "
713 + ((dictionaryId != null) ? (" AND d.dictionary_id = '" + dictionaryId + "'") : "")
714 + ((dictElementShortName != null) ? (" AND de.dict_element_short_name = '" + dictElementShortName + "'")
716 + ((dictionaryName != null) ? (" AND dictionary_name = '" + dictionaryName + "'") : "");
718 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(dictionarySql);
721 rows.forEach(row -> {
722 CldsDictionaryItem dictionaryItem = new CldsDictionaryItem();
723 dictionaryItem.setDictElementId((String) row.get("dict_element_id"));
724 dictionaryItem.setDictionaryId((String) row.get("dictionary_id"));
725 dictionaryItem.setDictElementName((String) row.get("dict_element_name"));
726 dictionaryItem.setDictElementShortName((String) row.get("dict_element_short_name"));
727 dictionaryItem.setDictElementDesc((String) row.get("dict_element_description"));
728 dictionaryItem.setDictElementType((String) row.get("dict_element_type"));
729 dictionaryItem.setCreatedBy((String) row.get("created_by"));
730 dictionaryItem.setUpdatedBy((String) row.get("modified_by"));
731 dictionaryItem.setLastUpdatedDate(sdf.format(row.get("timestamp")));
732 dictionaryItems.add(dictionaryItem);
735 return dictionaryItems;
739 * Method to get Map of all dictionary elements with key as dictionary short
740 * name and value as the full name.
742 * @param dictionaryElementType
743 * the dictionary element type
744 * @return Map of dictionary elements as key value pair
746 public Map<String, String> getDictionaryElementsByType(String dictionaryElementType) {
747 Map<String, String> dictionaryItems = new HashMap<>();
748 String dictionarySql = "SELECT dict_element_name, dict_element_short_name " + "FROM dictionary_elements "
749 + "WHERE dict_element_type = '" + dictionaryElementType + "'";
751 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(dictionarySql);
754 rows.forEach(row -> {
755 dictionaryItems.put(((String) row.get("dict_element_short_name")),
756 ((String) row.get("dict_element_name")));
759 return dictionaryItems;