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.HashMap;
33 import java.util.List;
36 import javax.sql.DataSource;
38 import org.onap.clamp.clds.model.CldsDbServiceCache;
39 import org.onap.clamp.clds.model.CldsDictionary;
40 import org.onap.clamp.clds.model.CldsDictionaryItem;
41 import org.onap.clamp.clds.model.CldsEvent;
42 import org.onap.clamp.clds.model.CldsModel;
43 import org.onap.clamp.clds.model.CldsModelInstance;
44 import org.onap.clamp.clds.model.CldsModelProp;
45 import org.onap.clamp.clds.model.CldsMonitoringDetails;
46 import org.onap.clamp.clds.model.CldsServiceData;
47 import org.onap.clamp.clds.model.CldsTemplate;
48 import org.onap.clamp.clds.model.CldsToscaModel;
49 import org.onap.clamp.clds.model.ValueItem;
50 import org.springframework.dao.EmptyResultDataAccessException;
51 import org.springframework.jdbc.core.JdbcTemplate;
52 import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
53 import org.springframework.jdbc.core.namedparam.SqlParameterSource;
54 import org.springframework.jdbc.core.simple.SimpleJdbcCall;
55 import org.springframework.stereotype.Repository;
58 * Data Access for CLDS Model tables.
60 @Repository("cldsDao")
61 public class CldsDao {
63 private static final EELFLogger logger = EELFManager.getInstance().getLogger(CldsDao.class);
64 private JdbcTemplate jdbcTemplateObject;
65 private SimpleJdbcCall procGetModel;
66 private SimpleJdbcCall procGetModelTemplate;
67 private SimpleJdbcCall procSetModel;
68 private SimpleJdbcCall procInsEvent;
69 private SimpleJdbcCall procUpdEvent;
70 private SimpleJdbcCall procSetTemplate;
71 private SimpleJdbcCall procGetTemplate;
72 private SimpleJdbcCall procDelAllModelInstances;
73 private SimpleJdbcCall procInsModelInstance;
74 private SimpleJdbcCall procDeleteModel;
75 private static final String HEALTHCHECK = "Select 1";
76 private static final String V_CONTROL_NAME_PREFIX = "v_control_name_prefix";
77 private static final String V_CONTROL_NAME_UUID = "v_control_name_uuid";
79 private SimpleJdbcCall procInsertToscaModel;
80 private SimpleJdbcCall procInsertNewToscaModelVersion;
81 private SimpleJdbcCall procInsertDictionary;
82 private SimpleJdbcCall procInsertDictionaryElement;
84 private static final String DATE_FORMAT = "MM-dd-yyyy HH:mm:ss";
87 * Log message when instantiating
90 logger.info("CldsDao instantiating...");
94 * When dataSource is provided, instantiate spring jdbc objects.
96 public void setDataSource(DataSource dataSource) {
97 this.jdbcTemplateObject = new JdbcTemplate(dataSource);
98 this.procGetModel = new SimpleJdbcCall(dataSource).withProcedureName("get_model");
99 this.procGetModelTemplate = new SimpleJdbcCall(dataSource).withProcedureName("get_model_template");
100 this.procSetModel = new SimpleJdbcCall(dataSource).withProcedureName("set_model");
101 this.procInsEvent = new SimpleJdbcCall(dataSource).withProcedureName("ins_event");
102 this.procUpdEvent = new SimpleJdbcCall(dataSource).withProcedureName("upd_event");
103 this.procGetTemplate = new SimpleJdbcCall(dataSource).withProcedureName("get_template");
104 this.procSetTemplate = new SimpleJdbcCall(dataSource).withProcedureName("set_template");
105 this.procInsModelInstance = new SimpleJdbcCall(dataSource).withProcedureName("ins_model_instance");
106 this.procDelAllModelInstances = new SimpleJdbcCall(dataSource).withProcedureName("del_all_model_instances");
107 this.procDeleteModel = new SimpleJdbcCall(dataSource).withProcedureName("del_model");
108 this.procInsertToscaModel = new SimpleJdbcCall(dataSource).withProcedureName("set_tosca_model");
109 this.procInsertNewToscaModelVersion = new SimpleJdbcCall(dataSource)
110 .withProcedureName("set_new_tosca_model_version");
111 this.procInsertDictionary = new SimpleJdbcCall(dataSource).withProcedureName("set_dictionary");
112 this.procInsertDictionaryElement = new SimpleJdbcCall(dataSource).withProcedureName("set_dictionary_elements");
116 * Get a model from the database given the model name.
118 public CldsModel getModel(String modelName) {
119 return getModel(modelName, null);
123 * Get a model from the database given the controlNameUuid.
125 public CldsModel getModelByUuid(String controlNameUuid) {
126 return getModel(null, controlNameUuid);
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 and template information from the database given the model name.
146 public CldsModel getModelTemplate(String modelName) {
147 CldsModel model = new CldsModel();
148 model.setName(modelName);
149 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName);
150 Map<String, Object> out = logSqlExecution(procGetModelTemplate, in);
151 populateModelProperties(model, out);
152 Map<String, Object> modelResults = logSqlExecution(procGetModel, in);
153 Object modelResultObject = modelResults.get("#result-set-1");
154 if (modelResultObject instanceof ArrayList) {
155 for (Object currModelInstance : (List<Object>) modelResultObject) {
156 if (currModelInstance instanceof HashMap) {
157 HashMap<String, String> modelInstanceMap = (HashMap<String, String>) currModelInstance;
158 CldsModelInstance modelInstance = new CldsModelInstance();
159 modelInstance.setModelInstanceId(modelInstanceMap.get("model_instance_id"));
160 modelInstance.setVmName(modelInstanceMap.get("vm_name"));
161 modelInstance.setLocation(modelInstanceMap.get("location"));
162 model.getCldsModelInstanceList().add(modelInstance);
163 logger.info("value of currModel: {}", currModelInstance);
171 * Update model in the database using parameter values and return updated model
178 public CldsModel setModel(CldsModel model, String userid) {
179 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", model.getName())
180 .addValue("v_template_id", model.getTemplateId()).addValue("v_user_id", userid)
181 .addValue("v_model_prop_text", model.getPropText())
182 .addValue("v_model_blueprint_text", model.getBlueprintText())
183 .addValue("v_service_type_id", model.getTypeId()).addValue("v_deployment_id", model.getDeploymentId())
184 .addValue("v_deployment_status_url", model.getDeploymentStatusUrl())
185 .addValue("v_control_name_prefix", model.getControlNamePrefix())
186 .addValue(V_CONTROL_NAME_UUID, model.getControlNameUuid());
187 Map<String, Object> out = logSqlExecution(procSetModel, in);
188 model.setControlNamePrefix((String) out.get(V_CONTROL_NAME_PREFIX));
189 model.setControlNameUuid((String) out.get(V_CONTROL_NAME_UUID));
190 model.setId((String) (out.get("v_model_id")));
191 model.getEvent().setId((String) (out.get("v_event_id")));
192 model.getEvent().setActionCd((String) out.get("v_action_cd"));
193 model.getEvent().setActionStateCd((String) out.get("v_action_state_cd"));
194 model.getEvent().setProcessInstanceId((String) out.get("v_event_process_instance_id"));
195 model.getEvent().setUserid((String) out.get("v_event_user_id"));
200 * Inserts new modelInstance in the database using parameter values and return
201 * updated model object.
204 * @param modelInstancesList
207 public void insModelInstance(CldsModel model, List<CldsModelInstance> modelInstancesList) {
208 // Delete all existing model instances for given controlNameUUID
209 logger.debug("deleting instances for: {}", model.getControlNameUuid());
210 delAllModelInstances(model.getControlNameUuid());
211 if (modelInstancesList == null) {
212 logger.debug("modelInstancesList == null");
214 for (CldsModelInstance currModelInstance : modelInstancesList) {
215 logger.debug("v_control_name_uuid={}", model.getControlNameUuid());
216 logger.debug("v_vm_name={}", currModelInstance.getVmName());
217 logger.debug("v_location={}", currModelInstance.getLocation());
218 SqlParameterSource in = new MapSqlParameterSource()
219 .addValue(V_CONTROL_NAME_UUID, model.getControlNameUuid())
220 .addValue("v_vm_name", currModelInstance.getVmName())
221 .addValue("v_location", currModelInstance.getLocation());
222 Map<String, Object> out = logSqlExecution(procInsModelInstance, in);
223 model.setId((String) (out.get("v_model_id")));
224 CldsModelInstance modelInstance = new CldsModelInstance();
225 modelInstance.setLocation(currModelInstance.getLocation());
226 modelInstance.setVmName(currModelInstance.getVmName());
227 modelInstance.setModelInstanceId((String) (out.get("v_model_instance_id")));
228 model.getCldsModelInstanceList().add(modelInstance);
234 * Insert an event in the database - require either modelName or
235 * controlNamePrefix/controlNameUuid.
238 * @param controlNamePrefix
239 * @param controlNameUuid
243 public CldsEvent insEvent(String modelName, String controlNamePrefix, String controlNameUuid, CldsEvent cldsEvent) {
244 CldsEvent event = new CldsEvent();
245 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName)
246 .addValue(V_CONTROL_NAME_PREFIX, controlNamePrefix).addValue(V_CONTROL_NAME_UUID, controlNameUuid)
247 .addValue("v_user_id", cldsEvent.getUserid()).addValue("v_action_cd", cldsEvent.getActionCd())
248 .addValue("v_action_state_cd", cldsEvent.getActionStateCd())
249 .addValue("v_process_instance_id", cldsEvent.getProcessInstanceId());
250 Map<String, Object> out = logSqlExecution(procInsEvent, in);
251 event.setId((String) (out.get("v_event_id")));
255 private String delAllModelInstances(String controlNameUUid) {
256 SqlParameterSource in = new MapSqlParameterSource().addValue(V_CONTROL_NAME_UUID, controlNameUUid);
257 Map<String, Object> out = logSqlExecution(procDelAllModelInstances, in);
258 return (String) (out.get("v_model_id"));
262 * Update event with process instance id.
265 * @param processInstanceId
267 public void updEvent(String eventId, String processInstanceId) {
268 SqlParameterSource in = new MapSqlParameterSource().addValue("v_event_id", eventId)
269 .addValue("v_process_instance_id", processInstanceId);
270 logSqlExecution(procUpdEvent, in);
274 * Return list of model names
276 * @return model names
278 public List<ValueItem> getModelNames() {
279 String sql = "SELECT model_name FROM model ORDER BY 1;";
280 return jdbcTemplateObject.query(sql, new ValueItemMapper());
284 * Update template in the database using parameter values and return updated
290 public void setTemplate(CldsTemplate template, String userid) {
291 SqlParameterSource in = new MapSqlParameterSource().addValue("v_template_name", template.getName())
292 .addValue("v_user_id", userid).addValue("v_template_bpmn_text", template.getBpmnText())
293 .addValue("v_template_image_text", template.getImageText())
294 .addValue("v_template_doc_text", template.getPropText());
295 Map<String, Object> out = logSqlExecution(procSetTemplate, in);
296 template.setId((String) (out.get("v_template_id")));
297 template.setBpmnUserid((String) (out.get("v_template_bpmn_user_id")));
298 template.setBpmnId((String) (out.get("v_template_bpmn_id")));
299 template.setImageId((String) (out.get("v_template_image_id")));
300 template.setImageUserid((String) out.get("v_template_image_user_id"));
301 template.setPropId((String) (out.get("v_template_doc_id")));
302 template.setPropUserid((String) out.get("v_template_doc_user_id"));
306 * Return list of template names
308 * @return template names
310 public List<ValueItem> getTemplateNames() {
311 String sql = "SELECT template_name FROM template ORDER BY 1;";
312 return jdbcTemplateObject.query(sql, new ValueItemMapper());
316 * Get a template from the database given the model name.
318 * @param templateName
321 public CldsTemplate getTemplate(String templateName) {
322 CldsTemplate template = new CldsTemplate();
323 template.setName(templateName);
324 SqlParameterSource in = new MapSqlParameterSource().addValue("v_template_name", templateName);
325 Map<String, Object> out = logSqlExecution(procGetTemplate, in);
326 template.setId((String) (out.get("v_template_id")));
327 template.setBpmnUserid((String) (out.get("v_template_bpmn_user_id")));
328 template.setBpmnId((String) (out.get("v_template_bpmn_id")));
329 template.setBpmnText((String) (out.get("v_template_bpmn_text")));
330 template.setImageId((String) (out.get("v_template_image_id")));
331 template.setImageUserid((String) out.get("v_template_image_user_id"));
332 template.setImageText((String) out.get("v_template_image_text"));
333 template.setPropId((String) (out.get("v_template_doc_id")));
334 template.setPropUserid((String) out.get("v_template_doc_user_id"));
335 template.setPropText((String) out.get("v_template_doc_text"));
339 public void clearServiceCache() {
340 String clearCldsServiceCacheSql = "TRUNCATE clds_service_cache";
341 jdbcTemplateObject.execute(clearCldsServiceCacheSql);
344 public CldsServiceData getCldsServiceCache(String invariantUUID) {
345 CldsServiceData cldsServiceData = null;
347 String getCldsServiceSQL = "SELECT * , TIMESTAMPDIFF(SECOND, timestamp, CURRENT_TIMESTAMP()) FROM clds_service_cache where invariant_service_id = ? ";
348 cldsServiceData = jdbcTemplateObject.queryForObject(getCldsServiceSQL, new Object[] { invariantUUID },
349 new CldsServiceDataMapper());
350 if (cldsServiceData != null) {
351 logger.info("CldsServiceData found in cache for Service Invariant ID:"
352 + cldsServiceData.getServiceInvariantUUID());
353 return cldsServiceData;
355 logger.warn("CldsServiceData not found in cache for Service Invariant ID:" + invariantUUID);
358 } catch (EmptyResultDataAccessException e) {
359 logger.info("CldsServiceData not found in cache for Service Invariant ID: " + invariantUUID);
360 logger.debug("CldsServiceData not found in cache for Service Invariant ID: " + invariantUUID, e);
365 public void setCldsServiceCache(CldsDbServiceCache cldsDBServiceCache) {
366 if (cldsDBServiceCache != null && cldsDBServiceCache.getInvariantId() != null
367 && cldsDBServiceCache.getServiceId() != null) {
368 String invariantUuid = cldsDBServiceCache.getInvariantId();
369 String serviceUuid = cldsDBServiceCache.getServiceId();
370 InputStream is = cldsDBServiceCache.getCldsDataInstream();
371 String insertCldsServiceCacheSql = "INSERT INTO clds_service_cache"
372 + "(invariant_service_id,service_id,timestamp,object_data) VALUES"
373 + "(?,?,CURRENT_TIMESTAMP,?) ON DUPLICATE KEY UPDATE invariant_service_id = VALUES(invariant_service_id) , timestamp = CURRENT_TIMESTAMP , object_data = VALUES(object_data) ";
374 jdbcTemplateObject.update(insertCldsServiceCacheSql, invariantUuid, serviceUuid, is);
378 private static Map<String, Object> logSqlExecution(SimpleJdbcCall call, SqlParameterSource source) {
380 return call.execute(source);
381 } catch (Exception e) {
382 logger.error("Exception occured in " + source.getClass().getCanonicalName() + ": " + e);
387 public void doHealthCheck() {
388 jdbcTemplateObject.execute(HEALTHCHECK);
392 * Method to get deployed/active models with model properties.
394 * @return list of CldsModelProp
396 public List<CldsModelProp> getDeployedModelProperties() {
397 List<CldsModelProp> cldsModelPropList = new ArrayList<>();
398 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 "
399 + "WHERE m.model_prop_id = mp.model_prop_id and m.event_id = e.event_id and e.action_cd = 'DEPLOY'";
400 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(modelsSql);
401 CldsModelProp cldsModelProp = null;
402 for (Map<String, Object> row : rows) {
403 cldsModelProp = new CldsModelProp();
404 cldsModelProp.setId((String) row.get("model_id"));
405 cldsModelProp.setName((String) row.get("model_name"));
406 cldsModelProp.setPropId((String) row.get("model_prop_id"));
407 cldsModelProp.setPropText((String) row.get("model_prop_text"));
408 cldsModelPropList.add(cldsModelProp);
410 return cldsModelPropList;
414 * Method to get deployed/active models with model properties.
416 * @return list of CLDS-Monitoring-Details: CLOSELOOP_NAME | Close loop name
417 * used in the CLDS application (prefix: ClosedLoop- + unique ClosedLoop
418 * ID) MODEL_NAME | Model Name in CLDS application SERVICE_TYPE_ID |
419 * TypeId returned from the DCAE application when the ClosedLoop is
420 * submitted (DCAEServiceTypeRequest generated in DCAE application).
421 * DEPLOYMENT_ID | Id generated when the ClosedLoop is deployed in DCAE.
422 * TEMPLATE_NAME | Template used to generate the ClosedLoop model.
423 * ACTION_CD | Current state of the ClosedLoop in CLDS application.
425 public List<CldsMonitoringDetails> getCLDSMonitoringDetails() {
426 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
427 List<CldsMonitoringDetails> cldsMonitoringDetailsList = new ArrayList<>();
428 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 "
429 + "FROM MODEL M, TEMPLATE T, EVENT E " + "WHERE M.TEMPLATE_ID = T.TEMPLATE_ID AND M.EVENT_ID = E.EVENT_ID "
430 + "ORDER BY ACTION_CD";
431 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(modelsSql);
432 CldsMonitoringDetails cldsMonitoringDetails = null;
433 for (Map<String, Object> row : rows) {
434 cldsMonitoringDetails = new CldsMonitoringDetails();
435 cldsMonitoringDetails.setCloseloopName((String) row.get("CLOSELOOP_NAME"));
436 cldsMonitoringDetails.setModelName((String) row.get("MODEL_NAME"));
437 cldsMonitoringDetails.setServiceTypeId((String) row.get("SERVICE_TYPE_ID"));
438 cldsMonitoringDetails.setDeploymentId((String) row.get("DEPLOYMENT_ID"));
439 cldsMonitoringDetails.setTemplateName((String) row.get("TEMPLATE_NAME"));
440 cldsMonitoringDetails.setAction((String) row.get("ACTION_CD"));
441 cldsMonitoringDetails.setUserid((String) row.get("USER_ID"));
442 cldsMonitoringDetails.setTimestamp(sdf.format(row.get("TIMESTAMP")));
443 cldsMonitoringDetailsList.add(cldsMonitoringDetails);
445 return cldsMonitoringDetailsList;
449 * Method to delete model from database.
453 public void deleteModel(String modelName) {
454 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName);
455 logSqlExecution(procDeleteModel, in);
458 private void populateModelProperties(CldsModel model, Map out) {
459 model.setControlNamePrefix((String) out.get(V_CONTROL_NAME_PREFIX));
460 model.setControlNameUuid((String) out.get(V_CONTROL_NAME_UUID));
461 model.setId((String) (out.get("v_model_id")));
462 model.setTemplateId((String) (out.get("v_template_id")));
463 model.setTemplateName((String) (out.get("v_template_name")));
464 model.setBpmnText((String) out.get("v_template_bpmn_text"));
465 model.setPropText((String) out.get("v_model_prop_text"));
466 model.setImageText((String) out.get("v_template_image_text"));
467 model.setDocText((String) out.get("v_template_doc_text"));
468 model.setBlueprintText((String) out.get("v_model_blueprint_text"));
469 model.getEvent().setId((String) (out.get("v_event_id")));
470 model.getEvent().setActionCd((String) out.get("v_action_cd"));
471 model.getEvent().setActionStateCd((String) out.get("v_action_state_cd"));
472 model.getEvent().setProcessInstanceId((String) out.get("v_event_process_instance_id"));
473 model.getEvent().setUserid((String) out.get("v_event_user_id"));
474 model.setTypeId((String) out.get("v_service_type_id"));
475 model.setDeploymentId((String) out.get("v_deployment_id"));
476 model.setDeploymentStatusUrl((String) out.get("v_deployment_status_url"));
480 * Method to retrieve a tosca models by Policy Type from database.
483 * @return List of CldsToscaModel
485 public List<CldsToscaModel> getAllToscaModels() {
486 return getToscaModel(null, null);
490 * Method to retrieve a tosca models by Policy Type from database.
493 * @return List of CldsToscaModel
495 public List<CldsToscaModel> getToscaModelByPolicyType(String policyType) {
496 return getToscaModel(null, policyType);
500 * Method to retrieve a tosca models by toscaModelName, version from database.
503 * @return List of CldsToscaModel
505 public List<CldsToscaModel> getToscaModelByName(String toscaModelName) {
506 return getToscaModel(toscaModelName, null);
509 // Retrieve the latest tosca model for a policy type or by tosca model name
511 private List<CldsToscaModel> getToscaModel(String toscaModelName, String policyType) {
512 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
513 List<CldsToscaModel> cldsToscaModels = new ArrayList<>();
515 String toscaModelSql = "SELECT tm.tosca_model_name, tm.tosca_model_id, tm.policy_type, tmr.tosca_model_revision_id, tmr.tosca_model_json, tmr.version, tmr.user_id, tmr.createdTimestamp, tmr.lastUpdatedTimestamp "
516 + ((toscaModelName != null) ? (", tmr.tosca_model_yaml ") : " ")
517 + "FROM tosca_model tm, tosca_model_revision tmr WHERE tm.tosca_model_id = tmr.tosca_model_id "
518 + ((toscaModelName != null) ? (" AND tm.tosca_model_name = '" + toscaModelName + "'") : " ")
519 + ((policyType != null) ? (" AND tm.policy_type = '" + policyType + "'") : " ")
520 + "AND tmr.version = (select max(version) from tosca_model_revision st where tmr.tosca_model_id=st.tosca_model_id)";
522 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(toscaModelSql);
525 rows.forEach(row -> {
526 CldsToscaModel cldsToscaModel = new CldsToscaModel();
527 cldsToscaModel.setId((String) row.get("tosca_model_id"));
528 cldsToscaModel.setPolicyType((String) row.get("policy_type"));
529 cldsToscaModel.setToscaModelName((String) row.get("tosca_model_name"));
530 cldsToscaModel.setUserId((String) row.get("user_id"));
531 cldsToscaModel.setRevisionId((String) row.get("tosca_model_revision_id"));
532 cldsToscaModel.setToscaModelJson((String) row.get("tosca_model_json"));
533 cldsToscaModel.setVersion(((Double) row.get("version")));
534 cldsToscaModel.setCreatedDate(sdf.format(row.get("createdTimestamp")));
535 cldsToscaModel.setLastUpdatedDate(sdf.format(row.get("lastUpdatedTimestamp")));
536 if (toscaModelName != null) {
537 cldsToscaModel.setToscaModelYaml((String) row.get("tosca_model_yaml"));
539 cldsToscaModels.add(cldsToscaModel);
542 return cldsToscaModels;
546 * Method to upload a new version of Tosca Model Yaml in Database
548 * @param cldsToscaModel
550 * @return CldsToscaModel
553 public CldsToscaModel updateToscaModelWithNewVersion(CldsToscaModel cldsToscaModel, String userId) {
554 SqlParameterSource in = new MapSqlParameterSource().addValue("v_tosca_model_id", cldsToscaModel.getId())
555 .addValue("v_version", cldsToscaModel.getVersion())
556 .addValue("v_tosca_model_yaml", cldsToscaModel.getToscaModelYaml())
557 .addValue("v_tosca_model_json", cldsToscaModel.getToscaModelJson()).addValue("v_user_id", userId);
558 Map<String, Object> out = logSqlExecution(procInsertNewToscaModelVersion, in);
559 cldsToscaModel.setRevisionId((String) (out.get("v_revision_id")));
560 return cldsToscaModel;
564 * Method to upload a new Tosca model Yaml in DB. Default version is 1.0
566 * @param cldsToscaModel
568 * @return CldsToscaModel
570 public CldsToscaModel insToscaModel(CldsToscaModel cldsToscaModel, String userId) {
571 SqlParameterSource in = new MapSqlParameterSource()
572 .addValue("v_tosca_model_name", cldsToscaModel.getToscaModelName())
573 .addValue("v_policy_type", cldsToscaModel.getPolicyType())
574 .addValue("v_tosca_model_yaml", cldsToscaModel.getToscaModelYaml())
575 .addValue("v_tosca_model_json", cldsToscaModel.getToscaModelJson())
576 .addValue("v_version", cldsToscaModel.getVersion()).addValue("v_user_id", userId);
577 Map<String, Object> out = logSqlExecution(procInsertToscaModel, in);
578 cldsToscaModel.setId((String) (out.get("v_tosca_model_id")));
579 cldsToscaModel.setRevisionId((String) (out.get("v_revision_id")));
580 cldsToscaModel.setUserId((String) out.get("v_user_id"));
581 return cldsToscaModel;
585 * Method to insert a new Dictionary in Database
587 * @param cldsDictionary
589 public void insDictionary(CldsDictionary cldsDictionary) {
590 SqlParameterSource in = new MapSqlParameterSource()
591 .addValue("v_dictionary_name", cldsDictionary.getDictionaryName())
592 .addValue("v_user_id", cldsDictionary.getCreatedBy());
593 Map<String, Object> out = logSqlExecution(procInsertDictionary, in);
594 cldsDictionary.setDictionaryId((String) (out.get("v_dictionary_id")));
598 * Method to update Dictionary with new info in Database
600 * @param dictionaryId
601 * @param cldsDictionary
604 public void updateDictionary(String dictionaryId, CldsDictionary cldsDictionary, String userId) {
606 String dictionarySql = "UPDATE dictionary " + "SET dictionary_name = '" + cldsDictionary.getDictionaryName()
607 + "', modified_by = '" + userId + "'" + "WHERE dictionary_id = '" + dictionaryId + "'";
608 jdbcTemplateObject.update(dictionarySql);
609 cldsDictionary.setUpdatedBy(userId);
613 * Method to get list of Dictionaries from the Database
615 * @param dictionaryId
616 * @param dictionaryName
619 public List<CldsDictionary> getDictionary(String dictionaryId, String dictionaryName) {
620 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
621 List<CldsDictionary> dictionaries = new ArrayList<>();
622 String dictionarySql = "SELECT dictionary_id, dictionary_name, created_by, modified_by, timestamp FROM dictionary"
623 + ((dictionaryId != null || dictionaryName != null)
624 ? (" WHERE " + ((dictionaryName != null) ? ("dictionary_name = '" + dictionaryName + "'") : "")
625 + ((dictionaryId != null && dictionaryName != null) ? (" AND ") : "")
626 + ((dictionaryId != null) ? ("dictionary_id = '" + dictionaryId + "'") : ""))
629 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(dictionarySql);
632 rows.forEach(row -> {
633 CldsDictionary cldsDictionary = new CldsDictionary();
634 cldsDictionary.setDictionaryId((String) row.get("dictionary_id"));
635 cldsDictionary.setDictionaryName((String) row.get("dictionary_name"));
636 cldsDictionary.setCreatedBy((String) row.get("created_by"));
637 cldsDictionary.setUpdatedBy((String) row.get("modified_by"));
638 cldsDictionary.setLastUpdatedDate(sdf.format(row.get("timestamp")));
639 dictionaries.add(cldsDictionary);
646 * Method to insert a new Dictionary Element for given dictionary in Database
648 * @param cldsDictionaryItem
651 public void insDictionarElements(CldsDictionaryItem cldsDictionaryItem, String userId) {
652 SqlParameterSource in = new MapSqlParameterSource()
653 .addValue("v_dictionary_id", cldsDictionaryItem.getDictionaryId())
654 .addValue("v_dict_element_name", cldsDictionaryItem.getDictElementName())
655 .addValue("v_dict_element_short_name", cldsDictionaryItem.getDictElementShortName())
656 .addValue("v_dict_element_description", cldsDictionaryItem.getDictElementDesc())
657 .addValue("v_dict_element_type", cldsDictionaryItem.getDictElementType()).addValue("v_user_id", userId);
658 Map<String, Object> out = logSqlExecution(procInsertDictionaryElement, in);
659 cldsDictionaryItem.setDictElementId((String) (out.get("v_dict_element_id")));
663 * Method to update Dictionary Elements with new info for a given dictionary in
666 * @param dictionaryElementId
667 * @param cldsDictionaryItem
670 public void updateDictionaryElements(String dictionaryElementId, CldsDictionaryItem cldsDictionaryItem,
673 String dictionarySql = "UPDATE dictionary_elements SET dict_element_name = '"
674 + cldsDictionaryItem.getDictElementName() + "', dict_element_short_name = '"
675 + cldsDictionaryItem.getDictElementShortName() + "', dict_element_description= '"
676 + cldsDictionaryItem.getDictElementDesc() + "', dict_element_type = '"
677 + cldsDictionaryItem.getDictElementType() + "', modified_by = '" + userId + "' "
678 + "WHERE dict_element_id = '" + dictionaryElementId + "'";
679 jdbcTemplateObject.update(dictionarySql);
680 cldsDictionaryItem.setUpdatedBy(userId);
684 * Method to get list of all dictionary elements for a given dictionary in the
687 * @param dictionaryName
688 * @param dictionaryId
689 * @param dictElementShortName
692 public List<CldsDictionaryItem> getDictionaryElements(String dictionaryName, String dictionaryId,
693 String dictElementShortName) {
694 SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
695 List<CldsDictionaryItem> dictionaryItems = new ArrayList<>();
696 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 "
697 + "FROM dictionary_elements de, dictionary d WHERE de.dictionary_id = d.dictionary_id "
698 + ((dictionaryId != null) ? (" AND d.dictionary_id = '" + dictionaryId + "'") : "")
699 + ((dictElementShortName != null) ? (" AND de.dict_element_short_name = '" + dictElementShortName + "'")
701 + ((dictionaryName != null) ? (" AND dictionary_name = '" + dictionaryName + "'") : "");
703 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(dictionarySql);
706 rows.forEach(row -> {
707 CldsDictionaryItem dictionaryItem = new CldsDictionaryItem();
708 dictionaryItem.setDictElementId((String) row.get("dict_element_id"));
709 dictionaryItem.setDictionaryId((String) row.get("dictionary_id"));
710 dictionaryItem.setDictElementName((String) row.get("dict_element_name"));
711 dictionaryItem.setDictElementShortName((String) row.get("dict_element_short_name"));
712 dictionaryItem.setDictElementDesc((String) row.get("dict_element_description"));
713 dictionaryItem.setDictElementType((String) row.get("dict_element_type"));
714 dictionaryItem.setCreatedBy((String) row.get("created_by"));
715 dictionaryItem.setUpdatedBy((String) row.get("modified_by"));
716 dictionaryItem.setLastUpdatedDate(sdf.format(row.get("timestamp")));
717 dictionaryItems.add(dictionaryItem);
720 return dictionaryItems;
724 * Method to get Map of all dictionary elements with key as dictionary short
725 * name and value as the full name
727 * @param dictionaryElementType
728 * @return Map of dictionary elements as key value pair
730 public Map<String, String> getDictionaryElementsByType(String dictionaryElementType) {
731 Map<String, String> dictionaryItems = new HashMap<>();
732 String dictionarySql = "SELECT dict_element_name, dict_element_short_name " + "FROM dictionary_elements "
733 + "WHERE dict_element_type = '" + dictionaryElementType + "'";
735 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(dictionarySql);
738 rows.forEach(row -> {
739 dictionaryItems.put(((String) row.get("dict_element_short_name")),
740 ((String) row.get("dict_element_name")));
743 return dictionaryItems;