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;
35 import java.util.stream.Collectors;
37 import javax.sql.DataSource;
39 import org.onap.clamp.clds.model.CldsDbServiceCache;
40 import org.onap.clamp.clds.model.CldsDictionary;
41 import org.onap.clamp.clds.model.CldsDictionaryItem;
42 import org.onap.clamp.clds.model.CldsEvent;
43 import org.onap.clamp.clds.model.CldsModel;
44 import org.onap.clamp.clds.model.CldsModelInstance;
45 import org.onap.clamp.clds.model.CldsModelProp;
46 import org.onap.clamp.clds.model.CldsMonitoringDetails;
47 import org.onap.clamp.clds.model.CldsServiceData;
48 import org.onap.clamp.clds.model.CldsTemplate;
49 import org.onap.clamp.clds.model.CldsToscaModel;
50 import org.onap.clamp.clds.model.CldsToscaModelDetails;
51 import org.onap.clamp.clds.model.CldsToscaModelRevision;
52 import org.onap.clamp.clds.model.ValueItem;
53 import org.springframework.dao.EmptyResultDataAccessException;
54 import org.springframework.jdbc.core.JdbcTemplate;
55 import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
56 import org.springframework.jdbc.core.namedparam.SqlParameterSource;
57 import org.springframework.jdbc.core.simple.SimpleJdbcCall;
58 import org.springframework.stereotype.Repository;
61 * Data Access for CLDS Model tables.
63 @Repository("cldsDao")
64 public class CldsDao {
66 private static final EELFLogger logger = EELFManager.getInstance().getLogger(CldsDao.class);
67 private JdbcTemplate jdbcTemplateObject;
68 private SimpleJdbcCall procGetModel;
69 private SimpleJdbcCall procGetModelTemplate;
70 private SimpleJdbcCall procSetModel;
71 private SimpleJdbcCall procInsEvent;
72 private SimpleJdbcCall procUpdEvent;
73 private SimpleJdbcCall procSetTemplate;
74 private SimpleJdbcCall procGetTemplate;
75 private SimpleJdbcCall procDelAllModelInstances;
76 private SimpleJdbcCall procInsModelInstance;
77 private SimpleJdbcCall procDeleteModel;
78 private static final String HEALTHCHECK = "Select 1";
79 private static final String V_CONTROL_NAME_PREFIX = "v_control_name_prefix";
80 private static final String V_CONTROL_NAME_UUID = "v_control_name_uuid";
82 private SimpleJdbcCall procInsertToscaModel;
83 private SimpleJdbcCall procInsertNewToscaModelVersion;
84 private SimpleJdbcCall procInsertDictionary;
85 private SimpleJdbcCall procInsertDictionaryElement;
89 * Log message when instantiating
92 logger.info("CldsDao instantiating...");
96 * When dataSource is provided, instantiate spring jdbc objects.
98 public void setDataSource(DataSource dataSource) {
99 this.jdbcTemplateObject = new JdbcTemplate(dataSource);
100 this.procGetModel = new SimpleJdbcCall(dataSource).withProcedureName("get_model");
101 this.procGetModelTemplate = new SimpleJdbcCall(dataSource).withProcedureName("get_model_template");
102 this.procSetModel = new SimpleJdbcCall(dataSource).withProcedureName("set_model");
103 this.procInsEvent = new SimpleJdbcCall(dataSource).withProcedureName("ins_event");
104 this.procUpdEvent = new SimpleJdbcCall(dataSource).withProcedureName("upd_event");
105 this.procGetTemplate = new SimpleJdbcCall(dataSource).withProcedureName("get_template");
106 this.procSetTemplate = new SimpleJdbcCall(dataSource).withProcedureName("set_template");
107 this.procInsModelInstance = new SimpleJdbcCall(dataSource).withProcedureName("ins_model_instance");
108 this.procDelAllModelInstances = new SimpleJdbcCall(dataSource).withProcedureName("del_all_model_instances");
109 this.procDeleteModel = new SimpleJdbcCall(dataSource).withProcedureName("del_model");
110 this.procInsertToscaModel = new SimpleJdbcCall(dataSource).withProcedureName("set_tosca_model");
111 this.procInsertNewToscaModelVersion = new SimpleJdbcCall(dataSource).withProcedureName("set_new_tosca_model_version");
112 this.procInsertDictionary = new SimpleJdbcCall(dataSource).withProcedureName("set_dictionary");
113 this.procInsertDictionaryElement = new SimpleJdbcCall(dataSource).withProcedureName("set_dictionary_elements");
117 * Get a model from the database given the model name.
119 public CldsModel getModel(String modelName) {
120 return getModel(modelName, null);
124 * Get a model from the database given the controlNameUuid.
126 public CldsModel getModelByUuid(String controlNameUuid) {
127 return getModel(null, controlNameUuid);
130 // Get a model from the database given the model name or a controlNameUuid.
131 private CldsModel getModel(String modelName, String controlNameUuid) {
132 CldsModel model = new CldsModel();
133 model.setName(modelName);
134 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName)
135 .addValue(V_CONTROL_NAME_UUID, controlNameUuid);
136 Map<String, Object> out = logSqlExecution(procGetModel, in);
137 populateModelProperties(model, out);
142 * Get a model and template information from the database given the model
148 public CldsModel getModelTemplate(String modelName) {
149 CldsModel model = new CldsModel();
150 model.setName(modelName);
151 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName);
152 Map<String, Object> out = logSqlExecution(procGetModelTemplate, in);
153 // todo : rationalize
154 populateModelProperties(model, out);
155 Map<String, Object> modelResults = logSqlExecution(procGetModel, in);
156 Object modelResultObject = modelResults.get("#result-set-1");
157 if (modelResultObject != null && modelResultObject instanceof ArrayList) {
158 List<Object> modelInstanceRs = (List<Object>) modelResultObject;
159 for (Object currModelInstance : modelInstanceRs) {
160 if (currModelInstance != null && currModelInstance instanceof HashMap) {
161 HashMap<String, String> modelInstanceMap = (HashMap<String, String>) currModelInstance;
162 CldsModelInstance modelInstance = new CldsModelInstance();
163 modelInstance.setModelInstanceId(modelInstanceMap.get("model_instance_id"));
164 modelInstance.setVmName(modelInstanceMap.get("vm_name"));
165 modelInstance.setLocation(modelInstanceMap.get("location"));
166 model.getCldsModelInstanceList().add(modelInstance);
167 logger.info("value of currModel: {}", currModelInstance);
175 * Update model in the database using parameter values and return updated
182 public CldsModel setModel(CldsModel model, String userid) {
183 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", model.getName())
184 .addValue("v_template_id", model.getTemplateId()).addValue("v_user_id", userid)
185 .addValue("v_model_prop_text", model.getPropText())
186 .addValue("v_model_blueprint_text", model.getBlueprintText())
187 .addValue("v_service_type_id", model.getTypeId()).addValue("v_deployment_id", model.getDeploymentId())
188 .addValue("v_control_name_prefix", model.getControlNamePrefix())
189 .addValue(V_CONTROL_NAME_UUID, model.getControlNameUuid());
190 Map<String, Object> out = logSqlExecution(procSetModel, in);
191 model.setControlNamePrefix((String) out.get(V_CONTROL_NAME_PREFIX));
192 model.setControlNameUuid((String) out.get(V_CONTROL_NAME_UUID));
193 model.setId((String) (out.get("v_model_id")));
194 model.getEvent().setId((String) (out.get("v_event_id")));
195 model.getEvent().setActionCd((String) out.get("v_action_cd"));
196 model.getEvent().setActionStateCd((String) out.get("v_action_state_cd"));
197 model.getEvent().setProcessInstanceId((String) out.get("v_event_process_instance_id"));
198 model.getEvent().setUserid((String) out.get("v_event_user_id"));
203 * Inserts new modelInstance in the database using parameter values and
204 * return updated model object.
207 * @param modelInstancesList
210 public void insModelInstance(CldsModel model, List<CldsModelInstance> modelInstancesList) {
211 // Delete all existing model instances for given controlNameUUID
212 logger.debug("deleting instances for: {}", model.getControlNameUuid());
213 delAllModelInstances(model.getControlNameUuid());
214 if (modelInstancesList == null) {
215 logger.debug("modelInstancesList == null");
217 for (CldsModelInstance currModelInstance : modelInstancesList) {
218 logger.debug("v_control_name_uuid={}", model.getControlNameUuid());
219 logger.debug("v_vm_name={}", currModelInstance.getVmName());
220 logger.debug("v_location={}", currModelInstance.getLocation());
221 SqlParameterSource in = new MapSqlParameterSource()
222 .addValue(V_CONTROL_NAME_UUID, model.getControlNameUuid())
223 .addValue("v_vm_name", currModelInstance.getVmName())
224 .addValue("v_location", currModelInstance.getLocation());
225 Map<String, Object> out = logSqlExecution(procInsModelInstance, in);
226 model.setId((String) (out.get("v_model_id")));
227 CldsModelInstance modelInstance = new CldsModelInstance();
228 modelInstance.setLocation(currModelInstance.getLocation());
229 modelInstance.setVmName(currModelInstance.getVmName());
230 modelInstance.setModelInstanceId((String) (out.get("v_model_instance_id")));
231 model.getCldsModelInstanceList().add(modelInstance);
237 * Insert an event in the database - require either modelName or
238 * controlNamePrefix/controlNameUuid.
241 * @param controlNamePrefix
242 * @param controlNameUuid
246 public CldsEvent insEvent(String modelName, String controlNamePrefix, String controlNameUuid, CldsEvent cldsEvent) {
247 CldsEvent event = new CldsEvent();
248 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName)
249 .addValue(V_CONTROL_NAME_PREFIX, controlNamePrefix).addValue(V_CONTROL_NAME_UUID, controlNameUuid)
250 .addValue("v_user_id", cldsEvent.getUserid()).addValue("v_action_cd", cldsEvent.getActionCd())
251 .addValue("v_action_state_cd", cldsEvent.getActionStateCd())
252 .addValue("v_process_instance_id", cldsEvent.getProcessInstanceId());
253 Map<String, Object> out = logSqlExecution(procInsEvent, in);
254 event.setId((String) (out.get("v_event_id")));
258 private String delAllModelInstances(String controlNameUUid) {
259 SqlParameterSource in = new MapSqlParameterSource().addValue(V_CONTROL_NAME_UUID, controlNameUUid);
260 Map<String, Object> out = logSqlExecution(procDelAllModelInstances, in);
261 return (String) (out.get("v_model_id"));
265 * Update event with process instance id.
268 * @param processInstanceId
270 public void updEvent(String eventId, String processInstanceId) {
271 SqlParameterSource in = new MapSqlParameterSource().addValue("v_event_id", eventId)
272 .addValue("v_process_instance_id", processInstanceId);
273 logSqlExecution(procUpdEvent, in);
277 * Return list of model names
279 * @return model names
281 public List<ValueItem> getBpmnNames() {
282 String sql = "SELECT model_name FROM model ORDER BY 1;";
283 return jdbcTemplateObject.query(sql, new ValueItemMapper());
287 * Update template in the database using parameter values and return updated
293 public void setTemplate(CldsTemplate template, String userid) {
294 SqlParameterSource in = new MapSqlParameterSource().addValue("v_template_name", template.getName())
295 .addValue("v_user_id", userid).addValue("v_template_bpmn_text", template.getBpmnText())
296 .addValue("v_template_image_text", template.getImageText())
297 .addValue("v_template_doc_text", template.getPropText());
298 Map<String, Object> out = logSqlExecution(procSetTemplate, in);
299 template.setId((String) (out.get("v_template_id")));
300 template.setBpmnUserid((String) (out.get("v_template_bpmn_user_id")));
301 template.setBpmnId((String) (out.get("v_template_bpmn_id")));
302 template.setImageId((String) (out.get("v_template_image_id")));
303 template.setImageUserid((String) out.get("v_template_image_user_id"));
304 template.setPropId((String) (out.get("v_template_doc_id")));
305 template.setPropUserid((String) out.get("v_template_doc_user_id"));
309 * Return list of template names
311 * @return template names
313 public List<ValueItem> getTemplateNames() {
314 String sql = "SELECT template_name FROM template ORDER BY 1;";
315 return jdbcTemplateObject.query(sql, new ValueItemMapper());
319 * Get a template from the database given the model name.
321 * @param templateName
324 public CldsTemplate getTemplate(String templateName) {
325 CldsTemplate template = new CldsTemplate();
326 template.setName(templateName);
327 SqlParameterSource in = new MapSqlParameterSource().addValue("v_template_name", templateName);
328 Map<String, Object> out = logSqlExecution(procGetTemplate, in);
329 template.setId((String) (out.get("v_template_id")));
330 template.setBpmnUserid((String) (out.get("v_template_bpmn_user_id")));
331 template.setBpmnId((String) (out.get("v_template_bpmn_id")));
332 template.setBpmnText((String) (out.get("v_template_bpmn_text")));
333 template.setImageId((String) (out.get("v_template_image_id")));
334 template.setImageUserid((String) out.get("v_template_image_user_id"));
335 template.setImageText((String) out.get("v_template_image_text"));
336 template.setPropId((String) (out.get("v_template_doc_id")));
337 template.setPropUserid((String) out.get("v_template_doc_user_id"));
338 template.setPropText((String) out.get("v_template_doc_text"));
342 public void clearServiceCache() {
343 String clearCldsServiceCacheSql = "TRUNCATE clds_service_cache";
344 jdbcTemplateObject.execute(clearCldsServiceCacheSql);
347 public CldsServiceData getCldsServiceCache(String invariantUUID) {
348 CldsServiceData cldsServiceData = null;
350 String getCldsServiceSQL = "SELECT * , TIMESTAMPDIFF(SECOND, timestamp, CURRENT_TIMESTAMP()) FROM clds_service_cache where invariant_service_id = ? ";
351 cldsServiceData = jdbcTemplateObject.queryForObject(getCldsServiceSQL, new Object[] { invariantUUID },
352 new CldsServiceDataMapper());
353 if (cldsServiceData != null) {
354 logger.info("CldsServiceData found in cache for Service Invariant ID:"
355 + cldsServiceData.getServiceInvariantUUID());
356 return cldsServiceData;
358 logger.warn("CldsServiceData not found in cache for Service Invariant ID:" + invariantUUID);
361 } catch (EmptyResultDataAccessException e) {
362 logger.info("CldsServiceData not found in cache for Service Invariant ID: " + invariantUUID);
363 logger.debug("CldsServiceData not found in cache for Service Invariant ID: " + invariantUUID, e);
368 public void setCldsServiceCache(CldsDbServiceCache cldsDBServiceCache) {
369 if (cldsDBServiceCache != null && cldsDBServiceCache.getInvariantId() != null
370 && cldsDBServiceCache.getServiceId() != null) {
371 String invariantUuid = cldsDBServiceCache.getInvariantId();
372 String serviceUuid = cldsDBServiceCache.getServiceId();
373 InputStream is = cldsDBServiceCache.getCldsDataInstream();
374 String insertCldsServiceCacheSql = "INSERT INTO clds_service_cache"
375 + "(invariant_service_id,service_id,timestamp,object_data) VALUES"
376 + "(?,?,CURRENT_TIMESTAMP,?) ON DUPLICATE KEY UPDATE invariant_service_id = VALUES(invariant_service_id) , timestamp = CURRENT_TIMESTAMP , object_data = VALUES(object_data) ";
377 jdbcTemplateObject.update(insertCldsServiceCacheSql, invariantUuid, serviceUuid, is);
381 private static Map<String, Object> logSqlExecution(SimpleJdbcCall call, SqlParameterSource source) {
383 return call.execute(source);
384 } catch (Exception e) {
385 logger.error("Exception occured in " + source.getClass().getCanonicalName() + ": " + e);
390 public void doHealthCheck() {
391 jdbcTemplateObject.execute(HEALTHCHECK);
395 * Method to get deployed/active models with model properties.
397 * @return list of CldsModelProp
399 public List<CldsModelProp> getDeployedModelProperties() {
400 List<CldsModelProp> cldsModelPropList = new ArrayList<>();
401 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 "
402 + "WHERE m.model_prop_id = mp.model_prop_id and m.event_id = e.event_id and e.action_cd = 'DEPLOY'";
403 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(modelsSql);
404 CldsModelProp cldsModelProp = null;
405 for (Map<String, Object> row : rows) {
406 cldsModelProp = new CldsModelProp();
407 cldsModelProp.setId((String) row.get("model_id"));
408 cldsModelProp.setName((String) row.get("model_name"));
409 cldsModelProp.setPropId((String) row.get("model_prop_id"));
410 cldsModelProp.setPropText((String) row.get("model_prop_text"));
411 cldsModelPropList.add(cldsModelProp);
413 return cldsModelPropList;
417 * Method to get deployed/active models with model properties.
419 * @return list of CLDS-Monitoring-Details: CLOSELOOP_NAME | Close loop name
420 * used in the CLDS application (prefix: ClosedLoop- + unique
421 * ClosedLoop ID) MODEL_NAME | Model Name in CLDS application
422 * SERVICE_TYPE_ID | TypeId returned from the DCAE application when
423 * the ClosedLoop is submitted (DCAEServiceTypeRequest generated in
424 * DCAE application). DEPLOYMENT_ID | Id generated when the
425 * ClosedLoop is deployed in DCAE. TEMPLATE_NAME | Template used to
426 * generate the ClosedLoop model. ACTION_CD | Current state of the
427 * ClosedLoop in CLDS application.
429 public List<CldsMonitoringDetails> getCLDSMonitoringDetails() {
430 SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
431 List<CldsMonitoringDetails> cldsMonitoringDetailsList = new ArrayList<CldsMonitoringDetails>();
432 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 "
433 + "FROM MODEL M, TEMPLATE T, EVENT E "
434 + "WHERE M.TEMPLATE_ID = T.TEMPLATE_ID AND M.EVENT_ID = E.EVENT_ID " + "ORDER BY ACTION_CD";
435 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(modelsSql);
436 CldsMonitoringDetails cldsMonitoringDetails = null;
437 for (Map<String, Object> row : rows) {
438 cldsMonitoringDetails = new CldsMonitoringDetails();
439 cldsMonitoringDetails.setCloseloopName((String) row.get("CLOSELOOP_NAME"));
440 cldsMonitoringDetails.setModelName((String) row.get("MODEL_NAME"));
441 cldsMonitoringDetails.setServiceTypeId((String) row.get("SERVICE_TYPE_ID"));
442 cldsMonitoringDetails.setDeploymentId((String) row.get("DEPLOYMENT_ID"));
443 cldsMonitoringDetails.setTemplateName((String) row.get("TEMPLATE_NAME"));
444 cldsMonitoringDetails.setAction((String) row.get("ACTION_CD"));
445 cldsMonitoringDetails.setUserid((String) row.get("USER_ID"));
446 cldsMonitoringDetails.setTimestamp(sdf.format(row.get("TIMESTAMP")));
447 cldsMonitoringDetailsList.add(cldsMonitoringDetails);
449 return cldsMonitoringDetailsList;
453 * Method to delete model from database.
457 public void deleteModel(String modelName) {
458 SqlParameterSource in = new MapSqlParameterSource().addValue("v_model_name", modelName);
459 logSqlExecution(procDeleteModel, in);
462 private void populateModelProperties(CldsModel model, Map out) {
463 // todo : rationalize
464 model.setControlNamePrefix((String) out.get(V_CONTROL_NAME_PREFIX));
465 model.setControlNameUuid((String) out.get(V_CONTROL_NAME_UUID));
466 model.setId((String) (out.get("v_model_id")));
467 model.setTemplateId((String) (out.get("v_template_id")));
468 model.setTemplateName((String) (out.get("v_template_name")));
469 model.setBpmnText((String) out.get("v_template_bpmn_text"));
470 model.setPropText((String) out.get("v_model_prop_text"));
471 model.setImageText((String) out.get("v_template_image_text"));
472 model.setDocText((String) out.get("v_template_doc_text"));
473 model.setBlueprintText((String) out.get("v_model_blueprint_text"));
474 model.getEvent().setId((String) (out.get("v_event_id")));
475 model.getEvent().setActionCd((String) out.get("v_action_cd"));
476 model.getEvent().setActionStateCd((String) out.get("v_action_state_cd"));
477 model.getEvent().setProcessInstanceId((String) out.get("v_event_process_instance_id"));
478 model.getEvent().setUserid((String) out.get("v_event_user_id"));
479 model.setTypeId((String) out.get("v_service_type_id"));
480 model.setDeploymentId((String) out.get("v_deployment_id"));
485 * Method to retrieve a tosca models by Policy Type from database.
488 * @return List of CldsToscaModel
490 public List<CldsToscaModel> getAllToscaModels() {
491 return getToscaModel(null, null);
495 * Method to retrieve a tosca models by Policy Type from database.
498 * @return List of CldsToscaModel
500 public List<CldsToscaModel> getToscaModelByPolicyType(String policyType) {
501 return getToscaModel(null, policyType);
505 * Method to retrieve a tosca models by toscaModelName, version from database.
508 * @return List of CldsToscaModel
510 public List<CldsToscaModel> getToscaModelByName(String toscaModelName) {
511 return getToscaModel(toscaModelName, null);
514 // Retrieve the latest tosca model for a policy type or by tosca model name
516 private List<CldsToscaModel> getToscaModel(String toscaModelName, String policyType) {
517 SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
518 List<CldsToscaModel> cldsToscaModels = new ArrayList<>();
520 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 "
521 + ((toscaModelName != null) ? (", tmr.tosca_model_yaml ") : " ")
522 + "FROM tosca_model tm, tosca_model_revision tmr WHERE tm.tosca_model_id = tmr.tosca_model_id "
523 + ((toscaModelName != null) ? (" AND tm.tosca_model_name = '" + toscaModelName + "'") : " ")
524 + ((policyType != null) ? (" AND tm.policy_type = '" + policyType + "'") : " ")
525 + "AND tmr.version = (select max(version) from tosca_model_revision st where tmr.tosca_model_id=st.tosca_model_id)";
527 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(toscaModelSql);
529 if (rows != null && rows.size() > 0) {
531 rows.stream().forEach(row -> {
532 CldsToscaModel cldsToscaModel = new CldsToscaModel();
533 cldsToscaModel.setId((String) row.get("tosca_model_id"));
534 cldsToscaModel.setPolicyType((String) row.get("policy_type"));
535 cldsToscaModel.setToscaModelName((String) row.get("tosca_model_name"));
536 cldsToscaModel.setUserId((String) row.get("user_id"));
537 cldsToscaModel.setRevisionId((String) row.get("tosca_model_revision_id"));
538 cldsToscaModel.setVersion(((Double) row.get("version")));
539 cldsToscaModel.setCreatedDate(sdf.format(row.get("createdTimestamp")));
540 cldsToscaModel.setLastUpdatedDate(sdf.format(row.get("lastUpdatedTimestamp")));
541 if (toscaModelName != null) {
542 cldsToscaModel.setToscaModelYaml((String) row.get("tosca_model_yaml"));
544 cldsToscaModels.add(cldsToscaModel);
548 return cldsToscaModels;
551 // Retrieve Tosca Models & its revisions by policy Type.
552 private List<CldsToscaModelDetails> getAllToscaModelVersion(String toscaModelName, String policyType,
554 SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
555 List<CldsToscaModelDetails> cldsToscaModelDetailsList = new ArrayList<>();
556 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 "
557 + "FROM tosca_model tm, tosca_model_revision tmr " + "WHERE tmr.tosca_model_id = tm.tosca_model_id "
558 + ((policyType != null) ? (" AND tm.policy_type = '" + policyType + "'") : " ")
559 + ((toscaModelName != null) ? (" AND tm.tosca_model_name = '" + toscaModelName + "'") : " ")
560 + ((version != null) ? (" AND tmr.version = '" + version + "'") : "");
562 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(toscaModelSql);
564 if (rows != null && rows.size() > 0) {
565 // Get list of all available modelIds
566 List<String> listofModelIds = new ArrayList<>();
567 for (Map<String, Object> r : rows) {
569 listofModelIds.add((String) r.get("tosca_model_id"));
572 // Filter Distinct elements using streams
573 listofModelIds = listofModelIds.stream().distinct().collect(Collectors.toList());
575 // TODO change logic using java8
576 for (String modelId : listofModelIds) {
577 CldsToscaModelDetails cldsToscaModelDetails = new CldsToscaModelDetails();
578 List<CldsToscaModelRevision> revisions = new ArrayList<>();
579 for (Map<String, Object> row : rows) {
580 String id = (String) row.get("tosca_model_id");
581 if (modelId.equalsIgnoreCase(id)) {
582 cldsToscaModelDetails.setId(id);
583 cldsToscaModelDetails.setPolicyType((String) row.get("policy_type"));
584 cldsToscaModelDetails.setToscaModelName((String) row.get("tosca_model_name"));
585 cldsToscaModelDetails.setUserId((String) row.get("user_id"));
587 CldsToscaModelRevision modelRevision = new CldsToscaModelRevision();
588 modelRevision.setRevisionId((String) row.get("tosca_model_revision_id"));
589 modelRevision.setVersion(((Double) row.get("version")));
590 modelRevision.setUserId((String) row.get("user_id"));
591 modelRevision.setCreatedDate(sdf.format(row.get("createdTimestamp")));
592 modelRevision.setLastUpdatedDate(sdf.format(row.get("lastUpdatedTimestamp")));
593 revisions.add(modelRevision);
596 cldsToscaModelDetails.setToscaModelRevisions(revisions);
597 cldsToscaModelDetailsList.add(cldsToscaModelDetails);
600 return cldsToscaModelDetailsList;
605 * Method to upload a new version of Tosca Model Yaml in Database
607 * @param cldsToscaModel
609 * @return CldsToscaModel
612 public CldsToscaModel updateToscaModelWithNewVersion(CldsToscaModel cldsToscaModel, String userId) {
613 SqlParameterSource in = new MapSqlParameterSource().addValue("v_tosca_model_id", cldsToscaModel.getId())
614 .addValue("v_version", cldsToscaModel.getVersion())
615 .addValue("v_tosca_model_yaml", cldsToscaModel.getToscaModelYaml())
616 .addValue("v_tosca_model_json", cldsToscaModel.getToscaModelJson()).addValue("v_user_id", userId);
617 Map<String, Object> out = logSqlExecution(procInsertNewToscaModelVersion, in);
618 cldsToscaModel.setRevisionId((String) (out.get("v_revision_id")));
619 return cldsToscaModel;
624 * Method to upload a new Tosca model Yaml in DB. Default version is 1.0
626 * @param cldsToscaModel
628 * @return CldsToscaModel
630 public CldsToscaModel insToscaModel(CldsToscaModel cldsToscaModel, String userId) {
631 SqlParameterSource in = new MapSqlParameterSource()
632 .addValue("v_tosca_model_name", cldsToscaModel.getToscaModelName())
633 .addValue("v_policy_type", cldsToscaModel.getPolicyType())
634 .addValue("v_tosca_model_yaml", cldsToscaModel.getToscaModelYaml())
635 .addValue("v_tosca_model_json", cldsToscaModel.getToscaModelJson())
636 .addValue("v_version", cldsToscaModel.getVersion()).addValue("v_user_id", userId);
637 Map<String, Object> out = logSqlExecution(procInsertToscaModel, in);
638 cldsToscaModel.setId((String) (out.get("v_tosca_model_id")));
639 cldsToscaModel.setRevisionId((String) (out.get("v_revision_id")));
640 cldsToscaModel.setUserId((String) out.get("v_user_id"));
641 return cldsToscaModel;
645 * Method to insert a new Dictionary in Database
647 * @param cldsDictionary
649 public void insDictionary(CldsDictionary cldsDictionary) {
650 SqlParameterSource in = new MapSqlParameterSource()
651 .addValue("v_dictionary_name", cldsDictionary.getDictionaryName())
652 .addValue("v_user_id", cldsDictionary.getCreatedBy());
653 Map<String, Object> out = logSqlExecution(procInsertDictionary, in);
654 cldsDictionary.setDictionaryId((String) (out.get("v_dictionary_id")));
659 * Method to update Dictionary with new info in Database
661 * @param dictionaryId
662 * @param cldsDictionary
665 public void updateDictionary(String dictionaryId, CldsDictionary cldsDictionary, String userId) {
667 String dictionarySql = "UPDATE dictionary " + "SET dictionary_name = '" + cldsDictionary.getDictionaryName()
668 + "', modified_by = '" + userId + "'" + "WHERE dictionary_id = '" + dictionaryId + "'";
669 jdbcTemplateObject.update(dictionarySql);
670 cldsDictionary.setUpdatedBy(userId);
675 * Method to get list of Dictionaries from the Database
677 * @param dictionaryId
678 * @param dictionaryName
681 public List<CldsDictionary> getDictionary(String dictionaryId, String dictionaryName) {
682 SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
683 List<CldsDictionary> dictionaries = new ArrayList<>();
684 String dictionarySql = "SELECT dictionary_id, dictionary_name, created_by, modified_by, timestamp FROM dictionary"
685 + ((dictionaryId != null || dictionaryName != null)
686 ? (" WHERE " + ((dictionaryName != null) ? ("dictionary_name = '" + dictionaryName + "'") : "")
687 + ((dictionaryId != null && dictionaryName != null) ? (" AND ") : "")
688 + ((dictionaryId != null) ? ("dictionary_id = '" + dictionaryId + "'") : "")): "");
690 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(dictionarySql);
692 if (rows != null && rows.size() > 0) {
693 rows.stream().forEach(row -> {
694 CldsDictionary cldsDictionary = new CldsDictionary();
695 cldsDictionary.setDictionaryId((String) row.get("dictionary_id"));
696 cldsDictionary.setDictionaryName((String) row.get("dictionary_name"));
697 cldsDictionary.setCreatedBy((String) row.get("created_by"));
698 cldsDictionary.setUpdatedBy((String) row.get("modified_by"));
699 cldsDictionary.setLastUpdatedDate(sdf.format(row.get("timestamp")));
700 dictionaries.add(cldsDictionary);
707 * Method to insert a new Dictionary Element for given dictionary in Database
709 * @param cldsDictionaryItem
712 public void insDictionarElements(CldsDictionaryItem cldsDictionaryItem, String userId) {
713 SqlParameterSource in = new MapSqlParameterSource()
714 .addValue("v_dictionary_id", cldsDictionaryItem.getDictionaryId())
715 .addValue("v_dict_element_name", cldsDictionaryItem.getDictElementName())
716 .addValue("v_dict_element_short_name", cldsDictionaryItem.getDictElementShortName())
717 .addValue("v_dict_element_description", cldsDictionaryItem.getDictElementDesc())
718 .addValue("v_dict_element_type", cldsDictionaryItem.getDictElementType())
719 .addValue("v_user_id", userId);
720 Map<String, Object> out = logSqlExecution(procInsertDictionaryElement, in);
721 cldsDictionaryItem.setDictElementId((String) (out.get("v_dict_element_id")));
726 * Method to update Dictionary Elements with new info for a given dictionary in Database
728 * @param dictionaryElementId
729 * @param cldsDictionaryItem
732 public void updateDictionaryElements(String dictionaryElementId, CldsDictionaryItem cldsDictionaryItem, String userId) {
734 String dictionarySql = "UPDATE dictionary_elements SET dict_element_name = '"
735 + cldsDictionaryItem.getDictElementName() + "', dict_element_short_name = '"
736 + cldsDictionaryItem.getDictElementShortName() + "', dict_element_description= '"
737 + cldsDictionaryItem.getDictElementDesc() + "', dict_element_type = '"
738 + cldsDictionaryItem.getDictElementType() + "', modified_by = '" + userId + "' "
739 + "WHERE dict_element_id = '" + dictionaryElementId + "'";
740 jdbcTemplateObject.update(dictionarySql);
741 cldsDictionaryItem.setUpdatedBy(userId);
745 * Method to get list of all dictionary elements for a given dictionary in the Database
747 * @param dictionaryName
748 * @param dictionaryId
749 * @param dictElementShortName
752 public List<CldsDictionaryItem> getDictionaryElements(String dictionaryName, String dictionaryId,
753 String dictElementShortName) {
754 SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
755 List<CldsDictionaryItem> dictionaryItems = new ArrayList<>();
756 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 "
757 + "FROM dictionary_elements de, dictionary d WHERE de.dictionary_id = d.dictionary_id "
758 + ((dictionaryId != null) ? (" AND d.dictionary_id = '" + dictionaryId + "'") : "")
759 + ((dictElementShortName != null) ? (" AND de.dict_element_short_name = '" + dictElementShortName + "'"): "")
760 + ((dictionaryName != null) ? (" AND dictionary_name = '" + dictionaryName + "'") : "");
762 List<Map<String, Object>> rows = jdbcTemplateObject.queryForList(dictionarySql);
764 if (rows != null && rows.size() > 0) {
765 rows.stream().forEach(row -> {
766 CldsDictionaryItem dictionaryItem = new CldsDictionaryItem();
767 dictionaryItem.setDictElementId((String) row.get("dict_element_id"));
768 dictionaryItem.setDictionaryId((String) row.get("dictionary_id"));
769 dictionaryItem.setDictElementName((String) row.get("dict_element_name"));
770 dictionaryItem.setDictElementShortName((String) row.get("dict_element_short_name"));
771 dictionaryItem.setDictElementDesc((String) row.get("dict_element_description"));
772 dictionaryItem.setDictElementType((String) row.get("dict_element_type"));
773 dictionaryItem.setCreatedBy((String) row.get("created_by"));
774 dictionaryItem.setUpdatedBy((String) row.get("modified_by"));
775 dictionaryItem.setLastUpdatedDate(sdf.format(row.get("timestamp")));
776 dictionaryItems.add(dictionaryItem);
779 return dictionaryItems;
783 * Method to delete a tosca model from database.
785 * @param toscaModelId
788 /*public void deleteModel(String toscaModelId, String revisionId) {
789 SqlParameterSource in = new MapSqlParameterSource().addValue("v_tosca_model_id", toscaModelId)
790 .addValue("v_revision_id", revisionId);
791 logSqlExecution(procDelToscaModel, in);