2 * ============LICENSE_START=======================================================
4 * ================================================================================
5 * Copyright (C) 2017-2018 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.io.InputStream;
30 import java.text.SimpleDateFormat;
31 import java.util.ArrayList;
32 import java.util.Arrays;
33 import java.util.Collections;
34 import java.util.HashMap;
35 import java.util.List;
37 import java.util.Optional;
38 import java.util.stream.Collectors;
40 import javax.sql.DataSource;
42 import org.onap.clamp.clds.model.CldsDbServiceCache;
43 import org.onap.clamp.clds.model.CldsDictionary;
44 import org.onap.clamp.clds.model.CldsDictionaryItem;
45 import org.onap.clamp.clds.model.CldsEvent;
46 import org.onap.clamp.clds.model.CldsModel;
47 import org.onap.clamp.clds.model.CldsModelInstance;
48 import org.onap.clamp.clds.model.CldsModelProp;
49 import org.onap.clamp.clds.model.CldsMonitoringDetails;
50 import org.onap.clamp.clds.model.CldsServiceData;
51 import org.onap.clamp.clds.model.CldsTemplate;
52 import org.onap.clamp.clds.model.CldsToscaModel;
53 import org.onap.clamp.clds.model.ValueItem;
54 import org.springframework.dao.EmptyResultDataAccessException;
55 import org.springframework.jdbc.core.JdbcTemplate;
56 import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
57 import org.springframework.jdbc.core.namedparam.SqlParameterSource;
58 import org.springframework.jdbc.core.simple.SimpleJdbcCall;
59 import org.springframework.stereotype.Repository;
62 * Data Access for CLDS Model tables.
64 @Repository("cldsDao")
65 public class CldsDao {
67 private static final EELFLogger logger = EELFManager.getInstance().getLogger(CldsDao.class);
68 private JdbcTemplate jdbcTemplateObject;
69 private SimpleJdbcCall procGetModel;
70 private SimpleJdbcCall procGetModelTemplate;
71 private SimpleJdbcCall procSetModel;
72 private SimpleJdbcCall procInsEvent;
73 private SimpleJdbcCall procUpdEvent;
74 private SimpleJdbcCall procSetTemplate;
75 private SimpleJdbcCall procGetTemplate;
76 private SimpleJdbcCall procDelAllModelInstances;
77 private SimpleJdbcCall procInsModelInstance;
78 private SimpleJdbcCall procDeleteModel;
79 private static final String HEALTHCHECK = "Select 1";
80 private static final String V_CONTROL_NAME_PREFIX = "v_control_name_prefix";
81 private static final String V_CONTROL_NAME_UUID = "v_control_name_uuid";
83 private SimpleJdbcCall procInsertToscaModel;
84 private SimpleJdbcCall procInsertNewToscaModelVersion;
85 private SimpleJdbcCall procInsertDictionary;
86 private SimpleJdbcCall procInsertDictionaryElement;
88 private static final String DATE_FORMAT = "MM-dd-yyyy HH:mm:ss";
90 * Log message when instantiating
93 logger.info("CldsDao instantiating...");
97 * 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.
121 public CldsModel getModel(String modelName) {
122 return getModel(modelName, null);
126 * Get a model from the database given the controlNameUuid.
128 public CldsModel getModelByUuid(String controlNameUuid) {
129 return getModel(null, controlNameUuid);
132 // Get a model from the database given the model name or a controlNameUuid.
133 private CldsModel getModel(String modelName, String controlNameUuid) {
134 CldsModel model = new CldsModel();
135 model.setName(modelName);
136 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName)
137 .addValue(V_CONTROL_NAME_UUID, controlNameUuid);
138 Map<String, Object> out = logSqlExecution(procGetModel, in);
139 populateModelProperties(model, out);
144 * Get a model and template information from the database given the model name.
149 public CldsModel getModelTemplate(String modelName) {
150 CldsModel model = new CldsModel();
151 model.setName(modelName);
152 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName);
153 Map<String, Object> out = logSqlExecution(procGetModelTemplate, in);
154 populateModelProperties(model, out);
155 Map<String, Object> modelResults = logSqlExecution(procGetModel, in);
156 Object modelResultObject = modelResults.get("#result-set-1");
157 if (modelResultObject instanceof ArrayList) {
158 for (Object currModelInstance : (List<Object>) modelResultObject) {
159 if (currModelInstance instanceof HashMap) {
160 HashMap<String, String> modelInstanceMap = (HashMap<String, String>) currModelInstance;
161 CldsModelInstance modelInstance = new CldsModelInstance();
162 modelInstance.setModelInstanceId(modelInstanceMap.get("model_instance_id"));
163 modelInstance.setVmName(modelInstanceMap.get("vm_name"));
164 modelInstance.setLocation(modelInstanceMap.get("location"));
165 model.getCldsModelInstanceList().add(modelInstance);
166 logger.info("value of currModel: {}", currModelInstance);
174 * Update model in the database using parameter values and return updated model
181 public CldsModel setModel(CldsModel model, String userid) {
182 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", model.getName())
183 .addValue("v_template_id", model.getTemplateId()).addValue("v_user_id", userid)
184 .addValue("v_model_prop_text", model.getPropText())
185 .addValue("v_model_blueprint_text", model.getBlueprintText())
186 .addValue("v_service_type_id", model.getTypeId()).addValue("v_deployment_id", model.getDeploymentId())
187 .addValue("v_control_name_prefix", model.getControlNamePrefix())
188 .addValue(V_CONTROL_NAME_UUID, model.getControlNameUuid());
189 Map<String, Object> out = logSqlExecution(procSetModel, in);
190 model.setControlNamePrefix((String) out.get(V_CONTROL_NAME_PREFIX));
191 model.setControlNameUuid((String) out.get(V_CONTROL_NAME_UUID));
192 model.setId((String) (out.get("v_model_id")));
193 model.getEvent().setId((String) (out.get("v_event_id")));
194 model.getEvent().setActionCd((String) out.get("v_action_cd"));
195 model.getEvent().setActionStateCd((String) out.get("v_action_state_cd"));
196 model.getEvent().setProcessInstanceId((String) out.get("v_event_process_instance_id"));
197 model.getEvent().setUserid((String) out.get("v_event_user_id"));
202 * Inserts new modelInstance in the database using parameter values and return
203 * updated model object.
206 * @param modelInstancesList
209 public void insModelInstance(CldsModel model, List<CldsModelInstance> modelInstancesList) {
210 // Delete all existing model instances for given controlNameUUID
211 logger.debug("deleting instances for: {}", model.getControlNameUuid());
212 delAllModelInstances(model.getControlNameUuid());
213 if (modelInstancesList == null) {
214 logger.debug("modelInstancesList == null");
216 for (CldsModelInstance currModelInstance : modelInstancesList) {
217 logger.debug("v_control_name_uuid={}", model.getControlNameUuid());
218 logger.debug("v_vm_name={}", currModelInstance.getVmName());
219 logger.debug("v_location={}", currModelInstance.getLocation());
220 SqlParameterSource in = new MapSqlParameterSource()
221 .addValue(V_CONTROL_NAME_UUID, model.getControlNameUuid())
222 .addValue("v_vm_name", currModelInstance.getVmName())
223 .addValue("v_location", currModelInstance.getLocation());
224 Map<String, Object> out = logSqlExecution(procInsModelInstance, in);
225 model.setId((String) (out.get("v_model_id")));
226 CldsModelInstance modelInstance = new CldsModelInstance();
227 modelInstance.setLocation(currModelInstance.getLocation());
228 modelInstance.setVmName(currModelInstance.getVmName());
229 modelInstance.setModelInstanceId((String) (out.get("v_model_instance_id")));
230 model.getCldsModelInstanceList().add(modelInstance);
236 * Insert an event in the database - require either modelName or
237 * controlNamePrefix/controlNameUuid.
240 * @param controlNamePrefix
241 * @param controlNameUuid
245 public CldsEvent insEvent(String modelName, String controlNamePrefix, String controlNameUuid, CldsEvent cldsEvent) {
246 CldsEvent event = new CldsEvent();
247 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName)
248 .addValue(V_CONTROL_NAME_PREFIX, controlNamePrefix).addValue(V_CONTROL_NAME_UUID, controlNameUuid)
249 .addValue("v_user_id", cldsEvent.getUserid()).addValue("v_action_cd", cldsEvent.getActionCd())
250 .addValue("v_action_state_cd", cldsEvent.getActionStateCd())
251 .addValue("v_process_instance_id", cldsEvent.getProcessInstanceId());
252 Map<String, Object> out = logSqlExecution(procInsEvent, in);
253 event.setId((String) (out.get("v_event_id")));
257 private String delAllModelInstances(String controlNameUUid) {
258 SqlParameterSource in = new MapSqlParameterSource().addValue(V_CONTROL_NAME_UUID, controlNameUUid);
259 Map<String, Object> out = logSqlExecution(procDelAllModelInstances, in);
260 return (String) (out.get("v_model_id"));
264 * Update event with process instance id.
267 * @param processInstanceId
269 public void updEvent(String eventId, String processInstanceId) {
270 SqlParameterSource in = new MapSqlParameterSource().addValue("v_event_id", eventId)
271 .addValue("v_process_instance_id", processInstanceId);
272 logSqlExecution(procUpdEvent, in);
276 * Return list of model names
278 * @return model names
280 public List<ValueItem> getModelNames() {
281 String sql = "SELECT model_name FROM model ORDER BY 1;";
282 return jdbcTemplateObject.query(sql, new ValueItemMapper());
286 * Update template in the database using parameter values and return updated
292 public void setTemplate(CldsTemplate template, String userid) {
293 SqlParameterSource in = new MapSqlParameterSource().addValue("v_template_name", template.getName())
294 .addValue("v_user_id", userid).addValue("v_template_bpmn_text", template.getBpmnText())
295 .addValue("v_template_image_text", template.getImageText())
296 .addValue("v_template_doc_text", template.getPropText());
297 Map<String, Object> out = logSqlExecution(procSetTemplate, in);
298 template.setId((String) (out.get("v_template_id")));
299 template.setBpmnUserid((String) (out.get("v_template_bpmn_user_id")));
300 template.setBpmnId((String) (out.get("v_template_bpmn_id")));
301 template.setImageId((String) (out.get("v_template_image_id")));
302 template.setImageUserid((String) out.get("v_template_image_user_id"));
303 template.setPropId((String) (out.get("v_template_doc_id")));
304 template.setPropUserid((String) out.get("v_template_doc_user_id"));
308 * Return list of template names
310 * @return template names
312 public List<ValueItem> getTemplateNames() {
313 String sql = "SELECT template_name FROM template ORDER BY 1;";
314 return jdbcTemplateObject.query(sql, new ValueItemMapper());
318 * Get a template from the database given the model name.
320 * @param templateName
323 public CldsTemplate getTemplate(String templateName) {
324 CldsTemplate template = new CldsTemplate();
325 template.setName(templateName);
326 SqlParameterSource in = new MapSqlParameterSource().addValue("v_template_name", templateName);
327 Map<String, Object> out = logSqlExecution(procGetTemplate, in);
328 template.setId((String) (out.get("v_template_id")));
329 template.setBpmnUserid((String) (out.get("v_template_bpmn_user_id")));
330 template.setBpmnId((String) (out.get("v_template_bpmn_id")));
331 template.setBpmnText((String) (out.get("v_template_bpmn_text")));
332 template.setImageId((String) (out.get("v_template_image_id")));
333 template.setImageUserid((String) out.get("v_template_image_user_id"));
334 template.setImageText((String) out.get("v_template_image_text"));
335 template.setPropId((String) (out.get("v_template_doc_id")));
336 template.setPropUserid((String) out.get("v_template_doc_user_id"));
337 template.setPropText((String) out.get("v_template_doc_text"));
341 public void clearServiceCache() {
342 String clearCldsServiceCacheSql = "TRUNCATE clds_service_cache";
343 jdbcTemplateObject.execute(clearCldsServiceCacheSql);
346 public CldsServiceData getCldsServiceCache(String invariantUUID) {
347 CldsServiceData cldsServiceData = null;
349 String getCldsServiceSQL = "SELECT * , TIMESTAMPDIFF(SECOND, timestamp, CURRENT_TIMESTAMP()) FROM clds_service_cache where invariant_service_id = ? ";
350 cldsServiceData = jdbcTemplateObject.queryForObject(getCldsServiceSQL, new Object[] { invariantUUID },
351 new CldsServiceDataMapper());
352 if (cldsServiceData != null) {
353 logger.info("CldsServiceData found in cache for Service Invariant ID:"
354 + cldsServiceData.getServiceInvariantUUID());
355 return cldsServiceData;
357 logger.warn("CldsServiceData not found in cache for Service Invariant ID:" + invariantUUID);
360 } catch (EmptyResultDataAccessException e) {
361 logger.info("CldsServiceData not found in cache for Service Invariant ID: " + invariantUUID);
362 logger.debug("CldsServiceData not found in cache for Service Invariant ID: " + invariantUUID, e);
367 public void setCldsServiceCache(CldsDbServiceCache cldsDBServiceCache) {
368 if (cldsDBServiceCache != null && cldsDBServiceCache.getInvariantId() != null
369 && cldsDBServiceCache.getServiceId() != null) {
370 String invariantUuid = cldsDBServiceCache.getInvariantId();
371 String serviceUuid = cldsDBServiceCache.getServiceId();
372 InputStream is = cldsDBServiceCache.getCldsDataInstream();
373 String insertCldsServiceCacheSql = "INSERT INTO clds_service_cache"
374 + "(invariant_service_id,service_id,timestamp,object_data) VALUES"
375 + "(?,?,CURRENT_TIMESTAMP,?) ON DUPLICATE KEY UPDATE invariant_service_id = VALUES(invariant_service_id) , timestamp = CURRENT_TIMESTAMP , object_data = VALUES(object_data) ";
376 jdbcTemplateObject.update(insertCldsServiceCacheSql, invariantUuid, serviceUuid, is);
380 private static Map<String, Object> logSqlExecution(SimpleJdbcCall call, SqlParameterSource source) {
382 return call.execute(source);
383 } catch (Exception e) {
384 logger.error("Exception occured in " + source.getClass().getCanonicalName() + ": " + e);
389 public void doHealthCheck() {
390 jdbcTemplateObject.execute(HEALTHCHECK);
394 * Method to get deployed/active models with model properties.
396 * @return list of CldsModelProp
398 public List<CldsModelProp> getDeployedModelProperties() {
399 List<CldsModelProp> cldsModelPropList = new ArrayList<>();
400 String modelsSql = "select m.model_id, m.model_name, mp.model_prop_id, mp.model_prop_text FROM model m, model_properties mp, event e "
401 + "WHERE m.model_prop_id = mp.model_prop_id and m.event_id = e.event_id and e.action_cd = 'DEPLOY'";
402 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(modelsSql);
403 CldsModelProp cldsModelProp = null;
404 for (Map<String, Object> row : rows) {
405 cldsModelProp = new CldsModelProp();
406 cldsModelProp.setId((String) row.get("model_id"));
407 cldsModelProp.setName((String) row.get("model_name"));
408 cldsModelProp.setPropId((String) row.get("model_prop_id"));
409 cldsModelProp.setPropText((String) row.get("model_prop_text"));
410 cldsModelPropList.add(cldsModelProp);
412 return cldsModelPropList;
416 * Method to get deployed/active models with model properties.
418 * @return list of CLDS-Monitoring-Details: CLOSELOOP_NAME | Close loop name
419 * used in the CLDS application (prefix: ClosedLoop- + unique ClosedLoop
420 * ID) MODEL_NAME | Model Name in CLDS application SERVICE_TYPE_ID |
421 * TypeId returned from the DCAE application when the ClosedLoop is
422 * submitted (DCAEServiceTypeRequest generated in DCAE application).
423 * DEPLOYMENT_ID | Id generated when the ClosedLoop is deployed in DCAE.
424 * TEMPLATE_NAME | Template used to generate the ClosedLoop model.
425 * ACTION_CD | Current state of the ClosedLoop in CLDS application.
427 public List<CldsMonitoringDetails> getCLDSMonitoringDetails() {
428 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
429 List<CldsMonitoringDetails> cldsMonitoringDetailsList = new ArrayList<>();
430 String modelsSql = "SELECT CONCAT(M.CONTROL_NAME_PREFIX, M.CONTROL_NAME_UUID) AS CLOSELOOP_NAME , M.MODEL_NAME, M.SERVICE_TYPE_ID, M.DEPLOYMENT_ID, T.TEMPLATE_NAME, E.ACTION_CD, E.USER_ID, E.TIMESTAMP "
431 + "FROM MODEL M, TEMPLATE T, EVENT E " + "WHERE M.TEMPLATE_ID = T.TEMPLATE_ID AND M.EVENT_ID = E.EVENT_ID "
432 + "ORDER BY ACTION_CD";
433 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(modelsSql);
434 CldsMonitoringDetails cldsMonitoringDetails = null;
435 for (Map<String, Object> row : rows) {
436 cldsMonitoringDetails = new CldsMonitoringDetails();
437 cldsMonitoringDetails.setCloseloopName((String) row.get("CLOSELOOP_NAME"));
438 cldsMonitoringDetails.setModelName((String) row.get("MODEL_NAME"));
439 cldsMonitoringDetails.setServiceTypeId((String) row.get("SERVICE_TYPE_ID"));
440 cldsMonitoringDetails.setDeploymentId((String) row.get("DEPLOYMENT_ID"));
441 cldsMonitoringDetails.setTemplateName((String) row.get("TEMPLATE_NAME"));
442 cldsMonitoringDetails.setAction((String) row.get("ACTION_CD"));
443 cldsMonitoringDetails.setUserid((String) row.get("USER_ID"));
444 cldsMonitoringDetails.setTimestamp(sdf.format(row.get("TIMESTAMP")));
445 cldsMonitoringDetailsList.add(cldsMonitoringDetails);
447 return cldsMonitoringDetailsList;
451 * Method to delete model from database.
455 public void deleteModel(String modelName) {
456 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName);
457 logSqlExecution(procDeleteModel, in);
460 private void populateModelProperties(CldsModel model, Map out) {
461 model.setControlNamePrefix((String) out.get(V_CONTROL_NAME_PREFIX));
462 model.setControlNameUuid((String) out.get(V_CONTROL_NAME_UUID));
463 model.setId((String) (out.get("v_model_id")));
464 model.setTemplateId((String) (out.get("v_template_id")));
465 model.setTemplateName((String) (out.get("v_template_name")));
466 model.setBpmnText((String) out.get("v_template_bpmn_text"));
467 model.setPropText((String) out.get("v_model_prop_text"));
468 model.setImageText((String) out.get("v_template_image_text"));
469 model.setDocText((String) out.get("v_template_doc_text"));
470 model.setBlueprintText((String) out.get("v_model_blueprint_text"));
471 model.getEvent().setId((String) (out.get("v_event_id")));
472 model.getEvent().setActionCd((String) out.get("v_action_cd"));
473 model.getEvent().setActionStateCd((String) out.get("v_action_state_cd"));
474 model.getEvent().setProcessInstanceId((String) out.get("v_event_process_instance_id"));
475 model.getEvent().setUserid((String) out.get("v_event_user_id"));
476 model.setTypeId((String) out.get("v_service_type_id"));
477 model.setDeploymentId((String) out.get("v_deployment_id"));
481 * Method to retrieve a tosca models by Policy Type from database.
484 * @return List of CldsToscaModel
486 public List<CldsToscaModel> getAllToscaModels() {
487 return getToscaModel(null, null);
491 * Method to retrieve a tosca models by Policy Type from database.
494 * @return List of CldsToscaModel
496 public List<CldsToscaModel> getToscaModelByPolicyType(String policyType) {
497 return getToscaModel(null, policyType);
501 * Method to retrieve a tosca models by toscaModelName, version from database.
504 * @return List of CldsToscaModel
506 public List<CldsToscaModel> getToscaModelByName(String toscaModelName) {
507 return getToscaModel(toscaModelName, null);
510 // Retrieve the latest tosca model for a policy type or by tosca model name
512 private List<CldsToscaModel> getToscaModel(String toscaModelName, String policyType) {
513 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
514 List<CldsToscaModel> cldsToscaModels = new ArrayList<>();
515 MapSqlParameterSource params = new MapSqlParameterSource();
517 String toscaModelSql = "SELECT tm.tosca_model_name, tm.tosca_model_id, tm.policy_type, tmr.tosca_model_revision_id, tmr.version, tmr.user_id, tmr.createdTimestamp, tmr.lastUpdatedTimestamp, tmr.tosca_model_yaml FROM tosca_model tm, tosca_model_revision tmr WHERE tm.tosca_model_id = tmr.tosca_model_id ";
518 if (toscaModelName != null) {
519 toscaModelSql += " AND tm.tosca_model_name = :toscaModelName";
520 params.addValue("toscaModelName", toscaModelName);
522 if (policyType != null) {
523 toscaModelSql += " AND tm.policy_type = :policyType";
524 params.addValue("policyType", policyType);
526 toscaModelSql += " AND tmr.version = (select max(version) from tosca_model_revision st where tmr.tosca_model_id=st.tosca_model_id)";
528 Optional.ofNullable(jdbcTemplateObject.queryForList(toscaModelSql, params)).orElse(Collections.emptyList()).forEach(row -> {
529 CldsToscaModel cldsToscaModel = new CldsToscaModel();
530 cldsToscaModel.setId((String) row.get("tosca_model_id"));
531 cldsToscaModel.setPolicyType((String) row.get("policy_type"));
532 cldsToscaModel.setToscaModelName((String) row.get("tosca_model_name"));
533 cldsToscaModel.setUserId((String) row.get("user_id"));
534 cldsToscaModel.setRevisionId((String) row.get("tosca_model_revision_id"));
535 cldsToscaModel.setVersion(((Double) row.get("version")));
536 cldsToscaModel.setCreatedDate(sdf.format(row.get("createdTimestamp")));
537 cldsToscaModel.setToscaModelYaml((String) row.get("tosca_model_yaml"));
538 cldsToscaModels.add(cldsToscaModel);
540 return cldsToscaModels;
544 * Method to upload a new version of Tosca Model Yaml in Database
546 * @param cldsToscaModel
548 * @return CldsToscaModel
551 public CldsToscaModel updateToscaModelWithNewVersion(CldsToscaModel cldsToscaModel, String userId) {
552 SqlParameterSource in = new MapSqlParameterSource().addValue("v_tosca_model_id", cldsToscaModel.getId())
553 .addValue("v_version", cldsToscaModel.getVersion())
554 .addValue("v_tosca_model_yaml", cldsToscaModel.getToscaModelYaml())
555 .addValue("v_tosca_model_json", cldsToscaModel.getToscaModelJson()).addValue("v_user_id", userId);
556 Map<String, Object> out = logSqlExecution(procInsertNewToscaModelVersion, in);
557 cldsToscaModel.setRevisionId((String) (out.get("v_revision_id")));
558 return cldsToscaModel;
562 * Method to upload a new Tosca model Yaml in DB. Default version is 1.0
564 * @param cldsToscaModel
566 * @return CldsToscaModel
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
587 public void insDictionary(CldsDictionary cldsDictionary) {
588 SqlParameterSource in = new MapSqlParameterSource()
589 .addValue("v_dictionary_name", cldsDictionary.getDictionaryName())
590 .addValue("v_user_id", cldsDictionary.getCreatedBy());
591 Map<String, Object> out = logSqlExecution(procInsertDictionary, in);
592 cldsDictionary.setDictionaryId((String) (out.get("v_dictionary_id")));
596 * Method to update Dictionary with new info in Database
598 * @param dictionaryId
599 * @param cldsDictionary
602 public void updateDictionary(String dictionaryId, CldsDictionary cldsDictionary, String userId) {
603 String dictionarySql = "UPDATE dictionary SET dictionary_name = :dictionary_name, modified_by = :modified_by WHERE dictionary_id = :dictionary_id";
604 SqlParameterSource namedParameters = new MapSqlParameterSource()
605 .addValue("dictionary_name", cldsDictionary.getDictionaryName()).addValue("modified_by", userId)
606 .addValue("dictionary_id", dictionaryId);
607 jdbcTemplateObject.update(dictionarySql, namedParameters);
608 cldsDictionary.setUpdatedBy(userId);
612 * Method to get list of Dictionaries from the Database
614 * @param dictionaryId
615 * @param dictionaryName
618 public List<CldsDictionary> getDictionary(String dictionaryId, String dictionaryName) {
619 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
620 List<CldsDictionary> dictionaries = new ArrayList<>();
621 String dictionarySql = "SELECT dictionary_id, dictionary_name, created_by, modified_by, timestamp FROM dictionary WHERE ";
622 MapSqlParameterSource namedParameters = new MapSqlParameterSource();
623 Optional.ofNullable(dictionaryName).ifPresent(dn -> namedParameters.addValue("dictionary_name", dn));
624 Optional.ofNullable(dictionaryId).ifPresent(dn -> namedParameters.addValue("dictionary_id", dn));
625 dictionarySql += Optional.ofNullable(namedParameters.getParameterNames()).filter(a -> a.length > 0)
626 .map(Arrays::stream).map(s -> s.map(param -> param + " = :" + param).collect(Collectors.joining(" AND ")))
629 Optional.ofNullable(jdbcTemplateObject.queryForList(dictionarySql, namedParameters)).orElse(Collections.emptyList()).forEach(row -> {
630 CldsDictionary cldsDictionary = new CldsDictionary();
631 cldsDictionary.setDictionaryId((String) row.get("dictionary_id"));
632 cldsDictionary.setDictionaryName((String) row.get("dictionary_name"));
633 cldsDictionary.setCreatedBy((String) row.get("created_by"));
634 cldsDictionary.setUpdatedBy((String) row.get("modified_by"));
635 cldsDictionary.setLastUpdatedDate(sdf.format(row.get("timestamp")));
636 dictionaries.add(cldsDictionary);
642 * Method to insert a new Dictionary Element for given dictionary in Database
644 * @param cldsDictionaryItem
647 public void insDictionarElements(CldsDictionaryItem cldsDictionaryItem, String userId) {
648 SqlParameterSource in = new MapSqlParameterSource()
649 .addValue("v_dictionary_id", cldsDictionaryItem.getDictionaryId())
650 .addValue("v_dict_element_name", cldsDictionaryItem.getDictElementName())
651 .addValue("v_dict_element_short_name", cldsDictionaryItem.getDictElementShortName())
652 .addValue("v_dict_element_description", cldsDictionaryItem.getDictElementDesc())
653 .addValue("v_dict_element_type", cldsDictionaryItem.getDictElementType()).addValue("v_user_id", userId);
654 Map<String, Object> out = logSqlExecution(procInsertDictionaryElement, in);
655 cldsDictionaryItem.setDictElementId((String) (out.get("v_dict_element_id")));
659 * Method to update Dictionary Elements with new info for a given dictionary in
662 * @param dictionaryElementId
663 * @param cldsDictionaryItem
666 public void updateDictionaryElements(String dictionaryElementId, CldsDictionaryItem cldsDictionaryItem,
669 String dictionarySql = "UPDATE dictionary_elements SET dict_element_name = :dict_element_name, dict_element_short_name = :dict_element_short_name, dict_element_description = :dict_element_description,dict_element_type=:dict_element_type, modified_by = :modified_by WHERE dict_element_id = :dict_element_id";
670 SqlParameterSource namedParameters = new MapSqlParameterSource()
671 .addValue("dict_element_name", cldsDictionaryItem.getDictElementName())
672 .addValue("dict_element_short_name", cldsDictionaryItem.getDictElementShortName())
673 .addValue("dict_element_description", cldsDictionaryItem.getDictElementDesc())
674 .addValue("dict_element_type", cldsDictionaryItem.getDictElementType())
675 .addValue("modified_by", userId)
676 .addValue("dict_element_id", dictionaryElementId);
677 jdbcTemplateObject.update(dictionarySql, namedParameters);
678 cldsDictionaryItem.setUpdatedBy(userId);
682 * Method to get list of all dictionary elements for a given dictionary in the
685 * @param dictionaryName
686 * @param dictionaryId
687 * @param dictElementShortName
690 public List<CldsDictionaryItem> getDictionaryElements(String dictionaryName, String dictionaryId,
691 String dictElementShortName) {
692 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
693 List<CldsDictionaryItem> dictionaryItems = new ArrayList<>();
694 MapSqlParameterSource namedParameters = new MapSqlParameterSource();
695 String dictionarySql = "SELECT de.dict_element_id, de.dictionary_id, de.dict_element_name, de.dict_element_short_name, de.dict_element_description, de.dict_element_type, de.created_by, de.modified_by, de.timestamp "
696 + "FROM dictionary_elements de, dictionary d WHERE de.dictionary_id = d.dictionary_id ";
697 if (dictionaryId != null) {
698 dictionarySql+=" AND d.dictionary_id = :dictionaryId";
699 namedParameters.addValue("dictionaryId", dictionaryId);
701 if (dictElementShortName!=null) {
702 dictionarySql+=" AND de.dict_element_short_name = :dictElementShortName";
703 namedParameters.addValue("dictElementShortName", dictElementShortName);
705 if (dictionaryName!=null) {
706 dictionarySql+=" AND dictionary_name = :dictionaryName";
707 namedParameters.addValue("dictionaryName", dictionaryName);
710 Optional.ofNullable(jdbcTemplateObject.queryForList(dictionarySql,namedParameters)).orElse(Collections.emptyList()).forEach(row -> {
711 CldsDictionaryItem dictionaryItem = new CldsDictionaryItem();
712 dictionaryItem.setDictElementId((String) row.get("dict_element_id"));
713 dictionaryItem.setDictionaryId((String) row.get("dictionary_id"));
714 dictionaryItem.setDictElementName((String) row.get("dict_element_name"));
715 dictionaryItem.setDictElementShortName((String) row.get("dict_element_short_name"));
716 dictionaryItem.setDictElementDesc((String) row.get("dict_element_description"));
717 dictionaryItem.setDictElementType((String) row.get("dict_element_type"));
718 dictionaryItem.setCreatedBy((String) row.get("created_by"));
719 dictionaryItem.setUpdatedBy((String) row.get("modified_by"));
720 dictionaryItem.setLastUpdatedDate(sdf.format(row.get("timestamp")));
721 dictionaryItems.add(dictionaryItem);
723 return dictionaryItems;