appc-inbound/appc-design-services/provider/src/main/java/org/onap/appc/design/dbervices/DesignDBService.java

   1 /*-
   2  * ============LICENSE_START=======================================================
   3  * ONAP : APPC
   4  * ================================================================================
   5  * Copyright (C) 2017-2019 AT&T Intellectual Property. All rights reserved.
   6  * ================================================================================
   7  * Copyright (C) 2017 Amdocs
   8  * ================================================================================
   9  * Modifications (C) 2019 Ericsson
  10  * Modifications (C) 2019 IBM
  11  * =============================================================================
  12  * Licensed under the Apache License, Version 2.0 (the "License");
  13  * you may not use this file except in compliance with the License.
  14  * You may obtain a copy of the License at
  15  *
  16  *      http://www.apache.org/licenses/LICENSE-2.0
  17  *
  18  * Unless required by applicable law or agreed to in writing, software
  19  * distributed under the License is distributed on an "AS IS" BASIS,
  20  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  21  * See the License for the specific language governing permissions and
  22  * limitations under the License.
  23  * ============LICENSE_END=========================================================
  24  */
  25
  26 package org.onap.appc.design.dbervices;
  27
  28 import com.att.eelf.configuration.EELFLogger;
  29 import com.att.eelf.configuration.EELFManager;
  30 import com.fasterxml.jackson.core.JsonParser;
  31 import com.fasterxml.jackson.databind.JsonNode;
  32 import com.fasterxml.jackson.databind.ObjectMapper;
  33 import com.google.common.base.Strings;
  34
  35 import java.sql.ResultSet;
  36 import java.sql.SQLException;
  37 import java.util.ArrayList;
  38 import java.util.List;
  39 import java.util.Date;
  40 import java.text.DateFormat;
  41 import java.text.SimpleDateFormat;
  42
  43 import org.json.JSONObject;
  44 import org.onap.appc.design.data.ArtifactInfo;
  45 import org.onap.appc.design.data.DesignInfo;
  46 import org.onap.appc.design.data.DesignResponse;
  47 import org.onap.appc.design.data.StatusInfo;
  48 import org.onap.appc.design.data.UserPermissionInfo;
  49 import org.onap.appc.design.services.util.ArtifactHandlerClient;
  50 import org.onap.appc.design.services.util.DesignServiceConstants;
  51 import org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource;
  52 import org.onap.ccsdk.sli.core.sli.SvcLogicResource;
  53
  54 public class DesignDBService {
  55
  56     private static final EELFLogger log = EELFManager.getInstance().getLogger(DesignDBService.class);
  57     private static DesignDBService dgGeneralDBService;
  58     private static ArtifactHandlerFactory artifactHandlerFactory = new ArtifactHandlerFactory();
  59     private static final String SUCCESS_JSON = "{\"update\" : \"success\" } ";
  60     private static final String STATUS = "STATUS";
  61     private static final String INFO_STR = "Info: ";
  62     private static final String DB_OPERATION_ERROR = "Error during DB operation: ";
  63     private static final String VNFC_TYPE = "vnfc-type";
  64     private static final String VNF_TYPE = "vnf-type";
  65     private static final String QUERY_STR = "Query String: ";
  66     private static final String USER_ID = "userID";
  67     private static final String COLUMN_USER_ID = "user_id";
  68     private static final String COLUMN_PERMISSION = "permission";
  69
  70     private SvcLogicResource serviceLogic;
  71     private DbService dbservice;
  72     public static DesignDBService initialise() {
  73         if (dgGeneralDBService == null) {
  74             dgGeneralDBService = new DesignDBService();
  75         }
  76         return dgGeneralDBService;
  77     }
  78
  79     private DesignDBService() {
  80         if (serviceLogic == null) {
  81             serviceLogic = new SqlResource();
  82         }
  83     }
  84
  85     public String execute(String action, String payload, String requestId) throws Exception {
  86
  87         log.info("Received execute request for action: " + action + " with Payload: " + payload);
  88         RequestValidator.validate(action, payload);
  89         String response;
  90         dbservice = new DbService();
  91         switch (action) {
  92             case DesignServiceConstants.GETDESIGNS:
  93                 response = getDesigns(payload, requestId);
  94                 break;
  95             case DesignServiceConstants.GETAPPCTIMESTAMPUTC:
  96                 response =  getAppcTimestampUTC(requestId);
  97                 break;
  98             case DesignServiceConstants.ADDINCART:
  99                 response = setInCart(payload, requestId);
 100                 break;
 101             case DesignServiceConstants.GETARTIFACTREFERENCE:
 102                 response = getArtifactReference(payload, requestId);
 103                 break;
 104             case DesignServiceConstants.GETARTIFACT:
 105                 response = getArtifact(payload, requestId);
 106                 break;
 107             case DesignServiceConstants.GETGUIREFERENCE:
 108                 response = getGuiReference(payload, requestId);
 109                 break;
 110             case DesignServiceConstants.GETSTATUS:
 111                 response = getStatus(payload, requestId);
 112                 break;
 113             case DesignServiceConstants.SETSTATUS:
 114                 response = setStatus(payload, requestId);
 115                 break;
 116             case DesignServiceConstants.UPLOADARTIFACT:
 117                 response = uploadArtifact(payload, requestId);
 118                 break;
 119             case DesignServiceConstants.SETPROTOCOLREFERENCE:
 120                 response = setProtocolReference(payload, requestId);
 121                 break;
 122             case DesignServiceConstants.UPLOADADMINARTIFACT:
 123                 response = uploadAdminArtifact(payload, requestId);
 124                 break;
 125             case DesignServiceConstants.CHECKVNF:
 126                 response = checkVNF(payload, requestId);
 127                 break;
 128             case DesignServiceConstants.RETRIEVEVNFPERMISSIONS:
 129                 response = retrieveVnfPermissions(payload, requestId);
 130                 break;
 131             case DesignServiceConstants.SAVEVNFPERMISSIONS:
 132                 response = saveUserPermissionInfo(payload, requestId);
 133                 break;
 134             default:
 135                 throw new DBException("Action " + action + " not found while processing request");
 136
 137         }
 138         return response;
 139     }
 140
 141     private String saveUserPermissionInfo(String payload, String requestId) throws Exception {
 142         try {
 143             if (requestId == null || requestId.isEmpty()) {
 144                 throw new DBException("requestId in saveUserPermissionInfo request is null or Blank");
 145             }
 146             log.info("Received Save User Permission from " + requestId + " with payload " + payload);
 147             Date startTime = new Date();
 148             DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
 149             String dbDate = dateFormat.format(startTime);
 150             ObjectMapper objectMapper = new ObjectMapper();
 151             JsonNode payloadObject = objectMapper.readTree(payload);
 152             String vnf_type = payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue();
 153             String modifier = payloadObject.get(DesignServiceConstants.CREATORUSERID).textValue();
 154             JsonNode users = payloadObject.get("users");
 155             if (users == null || !users.isArray()) {
 156                 throw new DBException("Users list is not provided in the input payload");
 157             }
 158             for (JsonNode node : users) {
 159                 String userId = node.get(DesignServiceConstants.USER_ID).textValue();
 160                 String permission = node.get(DesignServiceConstants.PERMISSION).textValue();
 161                 ArrayList<String> argList = new ArrayList<>();
 162                 argList.add(vnf_type);
 163                 argList.add(userId);
 164                 log.info("Checking User - " + userId + " current permissions in db for this vnf type");
 165                 String queryString = "SELECT PERMISSION FROM DT_USER_PERMISSIONS WHERE VNF_TYPE = ? AND USER_ID = ?";
 166                 log.info(QUERY_STR + queryString);
 167                 String user_permission = null;
 168                 int rowCount = 0;
 169                 try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 170                     while (data.next()) {
 171                         rowCount++;
 172                         user_permission = data.getString("PERMISSION");
 173                         if (Strings.isNullOrEmpty(permission)) {
 174                             log.info("Received request to delete db record for User - " + userId);
 175                             ArrayList<String> delArgList = new ArrayList<>();
 176                             delArgList.add(vnf_type);
 177                             delArgList.add(userId);
 178                             String deleteQuery = "DELETE FROM DT_USER_PERMISSIONS WHERE VNF_TYPE = ? AND USER_ID = ?";
 179                             log.info(QUERY_STR + deleteQuery);
 180                             log.info("Arguments List: " + delArgList);
 181                             boolean status = dbservice.updateDBData(deleteQuery, delArgList);
 182                             if (!status) {
 183                                 throw new DBException("Error while deleting record from DT_USER_PERMISSIONS");
 184                             } else {
 185                                 log.info("Record deleted");
 186                             }
 187                         } else if (user_permission.matches(permission)) {
 188                             log.info("User " + userId + " permission record found in db for same vnf_type " + vnf_type
 189                                     + ". No update needed.");
 190                         } else {
 191                             log.info("User's permission record will be updated. New permission: " + permission
 192                                     + " for user " + userId + " as requested by " + requestId
 193                                     + " will be saved to database.");
 194                             ArrayList<String> updateArgList = new ArrayList<>();
 195                             updateArgList.add(permission);
 196                             updateArgList.add(modifier);
 197                             updateArgList.add(dbDate);
 198                             updateArgList.add(vnf_type);
 199                             updateArgList.add(userId);
 200
 201                             String updateQuery =
 202                                     "UPDATE DT_USER_PERMISSIONS SET PERMISSION = ?, MODIFIER = ?, DATE_MODIFIED = ?"
 203                                     + " WHERE VNF_TYPE = ? AND USER_ID = ?";
 204                             log.info(QUERY_STR + updateQuery);
 205                             log.info("Arguments List: " + updateArgList);
 206                             boolean updateStatus = dbservice.updateDBData(updateQuery, updateArgList);
 207                             if (!updateStatus) {
 208                                 throw new DBException("Error while updating User Permissions");
 209                             }
 210                         }
 211                     }
 212                 }
 213                 if (rowCount == 0 && !(Strings.isNullOrEmpty(permission))) {
 214                     log.info("User not found in database for this vnf_type. The new permission " + permission
 215                             + " for user " + userId + " and vnf_type " + vnf_type + " as requested by " + requestId
 216                             + " will be saved to database.");
 217                     ArrayList<String> insertArgList = new ArrayList<>();
 218                     insertArgList.add(vnf_type);
 219                     insertArgList.add(userId);
 220                     insertArgList.add(permission);
 221                     insertArgList.add(modifier);
 222                     String insertQuery =
 223                             "INSERT INTO DT_USER_PERMISSIONS (VNF_TYPE, USER_ID, PERMISSION, DATE_MODIFIED, MODIFIER)"
 224                             + " VALUES (?, ?, ?, sysdate(), ?)";
 225                     log.info(QUERY_STR + insertQuery);
 226                     log.info("Arguments List: " + insertArgList);
 227                     boolean updateStatus = dbservice.updateDBData(insertQuery, insertArgList);
 228                     if (!updateStatus) {
 229                         throw new DBException("Error while inserting record for User Permissions");
 230                     }
 231                 }
 232             }
 233         } catch (Exception e) {
 234             log.error("An error occurred in saveUserPermissionInfo " + e.getMessage(), e);
 235             throw e;
 236         }
 237         return SUCCESS_JSON;
 238     }
 239
 240     private String checkVNF(String payload, String requestId) throws Exception {
 241         try {
 242             log.info("Got into Check VNF Request with payload: " + payload);
 243             if (payload == null || payload.isEmpty()) {
 244                 throw new DBException("Payload in CheckVNF request is null or Blank");
 245             }
 246             if (requestId == null || requestId.isEmpty()) {
 247                 throw new DBException("requestId in CheckVNF request is null or Blank");
 248             }
 249             ObjectMapper objectMapper = new ObjectMapper();
 250             JsonNode payloadObject = objectMapper.readTree(payload);
 251             String vnfType = payloadObject.get("vnf-type").textValue();
 252
 253             log.info("Check VNF Request with VNF TYPE: " + vnfType);
 254
 255             ArrayList<String> argList = new ArrayList<>();
 256             argList.add(vnfType);
 257
 258             String queryString =
 259                     "SELECT DT_ACTION_STATUS_ID, USER FROM sdnctl.DT_ACTION_STATUS WHERE VNF_TYPE = ?"
 260                     + " ORDER BY DT_ACTION_STATUS_ID DESC LIMIT 1;";
 261
 262             log.info(QUERY_STR + queryString);
 263             try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 264
 265                 int rowCount = 0;
 266                 String user = null;
 267                 String dtActionStatusId = null;
 268
 269                 while (data.next()) {
 270                     rowCount++;
 271                     user = data.getString("USER");
 272                     dtActionStatusId = data.getString("DT_ACTION_STATUS_ID");
 273                 }
 274
 275                 log.debug("DT_ACTION_STATUS_ID " + dtActionStatusId + " user " + user);
 276
 277                 JSONObject jObject = new JSONObject();
 278
 279
 280                 if (rowCount == 0) {
 281                     log.debug("vnf-type not present in APPC DB, row Count: " + rowCount);
 282                     jObject.put("result", "No");
 283                 } else {
 284                     log.debug("vnf-type present in APPC DB, row Count: " + rowCount);
 285                     jObject.put("result", "Yes");
 286                     jObject.put("user", user);
 287                 }
 288
 289                 log.info("Check VNF result: " + jObject.toString());
 290                 return jObject.toString();
 291
 292             }
 293         } catch (Exception e) {
 294             log.error("An error occurred in checkVNF " + e.getMessage(), e);
 295             throw e;
 296         }
 297
 298     }
 299
 300     private String uploadAdminArtifact(String payload, String requestId) throws Exception {
 301         try {
 302             ObjectMapper objectMapper = new ObjectMapper();
 303             objectMapper.configure(JsonParser.Feature.ALLOW_COMMENTS, true);
 304             JsonNode payloadObject = objectMapper.readTree(payload);
 305             log.info("Got upload Admin Artifact with requestId: " + requestId + " & Payload: "
 306                     + payloadObject.asText());
 307
 308             if (Strings.isNullOrEmpty(requestId)) {
 309                 throw new DBException("Request-id is missing in the uploadAdminArtifact payload.");
 310             }
 311
 312             ArtifactHandlerClient ac = new ArtifactHandlerClient();
 313             String requestString = ac.createArtifactData(payload, requestId);
 314             ac.execute(requestString, "POST");
 315
 316
 317
 318             int sdcArtifactId = getSDCArtifactIDbyRequestID(requestId);
 319             if (sdcArtifactId == 0) {
 320                 throw new DBException("Error occurred while validating/Saving the artifact to SDC_ARTIFACTS"
 321                         + " or getting SDC_ARTIFACTS_ID.");
 322             }
 323
 324             JsonNode json = payloadObject.get(DesignServiceConstants.USER_ID);
 325             if (json == null) {
 326                 throw new DBException("User Id is null");
 327             } else if (json.asText().trim().isEmpty()) {
 328                 log.info("UserId in Admin Artifact is blank, User Id: " + json.asText());
 329                 throw new DBException("User Id is blank");
 330             }
 331
 332             int sdcReferenceId = 0;
 333             createArtifactTrackingRecord(payload, requestId, sdcArtifactId, sdcReferenceId);
 334
 335         } catch (Exception e) {
 336             log.error("An error occurred in uploadAdminArtifact: " + e.getMessage(), e);
 337             throw e;
 338         }
 339         return SUCCESS_JSON;
 340     }
 341
 342     private String getAppcTimestampUTC(String requestId) throws Exception {
 343         log.info("Starting getAppcTimestampUTC: requestId: " + requestId);
 344         java.util.TimeZone gmtTZ = java.util.TimeZone.getTimeZone("GMT");
 345         java.text.SimpleDateFormat formatter =
 346                 new java.text.SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
 347         formatter.setTimeZone(gmtTZ);
 348         java.util.Date dateVal = new java.util.Date();
 349         log.info("getAppcTimestampUTC: current local Date: [" + dateVal + "]");
 350         String timeStr = formatter.format(dateVal);
 351         log.info("getAppcTimestampUTC: returning: [" + timeStr + "]");
 352         return timeStr;
 353     }
 354
 355     private String setInCart(String payload, String requestId) throws Exception {
 356
 357         ObjectMapper objectMapper = new ObjectMapper();
 358         JsonNode payloadObject = objectMapper.readTree(payload);
 359         ArrayList<String> argList = new ArrayList<>();
 360         argList.add(payloadObject.get(DesignServiceConstants.INCART).textValue());
 361         argList.add(payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue());
 362
 363         String queryString =
 364                 "UPDATE DT_ARTIFACT_TRACKING SET INCART = ? WHERE ASDC_REFERENCE_ID IN"
 365                 + " (SELECT ASDC_REFERENCE_ID FROM ASDC_REFERENCE_ID WHERE VNF_TYPE = ?";
 366
 367         if (payloadObject.get(DesignServiceConstants.VNFC_TYPE) != null
 368                 && !payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue().isEmpty()) {
 369             queryString += " AND VNFC_TYPE = ?";
 370             argList.add(payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue());
 371         }
 372         queryString += ") AND USER = ?";
 373         argList.add(payloadObject.get(DesignServiceConstants.USER_ID).textValue());
 374         log.info(QUERY_STR + queryString);
 375         boolean data = dbservice.updateDBData(queryString, argList);
 376
 377         if (!data) {
 378             throw new DBException("Error while updating ProtocolReference");
 379         }
 380         return SUCCESS_JSON;
 381     }
 382
 383     private String setProtocolReference(String payload, String requestId) throws Exception {
 384
 385         ObjectMapper objectMapper = new ObjectMapper();
 386         JsonNode payloadObject = objectMapper.readTree(payload);
 387         ArrayList<String> argList = new ArrayList<>();
 388
 389         argList.add(payloadObject.get(DesignServiceConstants.ACTION).textValue());
 390         argList.add(payloadObject.get(DesignServiceConstants.ACTION_LEVEL).textValue());
 391         argList.add(payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue());
 392         argList.add(payloadObject.get(DesignServiceConstants.PROTOCOL).textValue());
 393
 394         String queryString = "DELETE FROM PROTOCOL_REFERENCE WHERE ACTION = ? AND ACTION_LEVEL AND VNF_TYPE = ? AND PROTOCOL = ?";
 395
 396         log.info("Delete Query String: " + queryString);
 397         boolean data;
 398
 399         log.info("Record Deleted");
 400
 401         if (payloadObject.get(DesignServiceConstants.TEMPLATE) != null &&
 402             !payloadObject.get(DesignServiceConstants.TEMPLATE).textValue().isEmpty()) {
 403
 404             argList.add(payloadObject.get(DesignServiceConstants.TEMPLATE).textValue());
 405         } else {
 406             argList.add("NO");
 407         }
 408
 409         if (payloadObject.get(DesignServiceConstants.VNFC_TYPE) != null &&
 410             !payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue().isEmpty()) {
 411
 412             queryString += " AND VNFC_TYPE = ?";
 413         }
 414         queryString += ")";
 415         log.info(QUERY_STR + queryString);
 416         data = dbservice.updateDBData(queryString, argList);
 417
 418         if (!data) {
 419             throw new DBException("Error while updating ProtocolReference");
 420         }
 421         return SUCCESS_JSON;
 422     }
 423
 424     private String uploadArtifact(String payload, String requestId) throws Exception {
 425
 426         ObjectMapper objectMapper = new ObjectMapper();
 427         objectMapper.configure(JsonParser.Feature.ALLOW_COMMENTS, true);
 428         JsonNode payloadObject = objectMapper.readTree(payload);
 429         log.info("Got upload Artifact with Payload: " + payloadObject.asText());
 430         try {
 431             ArtifactHandlerClient ac = artifactHandlerFactory.ahi();
 432             String requestString = ac.createArtifactData(payload, requestId);
 433             ac.execute(requestString, "POST");
 434             int sdcArtifactId = getSDCArtifactIDbyRequestID(requestId);
 435             int sdcReferenceId = getSDCReferenceID(payload);
 436             createArtifactTrackingRecord(payload, requestId, sdcArtifactId, sdcReferenceId);
 437             String status = getDataFromActionStatus(payload, STATUS);
 438             if (status == null || status.isEmpty()) {
 439                 log.info("Action Status is: " + status);
 440                 setActionStatus(payload, "Not Tested");
 441             }
 442             linkstatusRelationShip(sdcArtifactId, sdcReferenceId, payload);
 443             savePermissionInfo(payload, requestId);
 444         } catch (Exception e) {
 445             log.error("An error occurred in uploadArtifact", e);
 446             throw e;
 447         }
 448         return SUCCESS_JSON;
 449
 450     }
 451     private void savePermissionInfo(String payload, String requestId) throws Exception {
 452
 453         ObjectMapper objectMapper = new ObjectMapper();
 454         JsonNode payloadObject = objectMapper.readTree(payload);
 455         ArrayList<String> argList = new ArrayList<>();
 456         argList.add(payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue());
 457         argList.add(payloadObject.get(DesignServiceConstants.USER_ID).textValue());
 458         log.info("Entered savePermissionInfo from uploadArtifact with payload " + payload);
 459         String queryString = "SELECT PERMISSION FROM DT_USER_PERMISSIONS WHERE VNF_TYPE = ? AND USER_ID = ?";
 460         log.info(QUERY_STR + queryString);
 461         try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 462             String user_permission = null;
 463             int rowCount = 0;
 464             while (data.next()) {
 465                 rowCount++;
 466                 user_permission = data.getString("PERMISSION");
 467                 log.info("User exists in database with permission = " + user_permission);
 468             }
 469             if (rowCount == 0) {
 470                 log.info("No record found in database");
 471                 log.info("Inserting one record in database");
 472                 String permission = "owner";
 473                 ArrayList<String> insertArgList = new ArrayList<>();
 474                 insertArgList.add(payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue());
 475                 insertArgList.add(payloadObject.get(DesignServiceConstants.USER_ID).textValue());
 476                 insertArgList.add(permission);
 477                 insertArgList.add(payloadObject.get(DesignServiceConstants.USER_ID).textValue());
 478                 String insertQuery =
 479                         "INSERT INTO DT_USER_PERMISSIONS (VNF_TYPE, USER_ID, PERMISSION, DATE_MODIFIED, MODIFIER)"
 480                         + " VALUES (?, ?, ?, sysdate(), ?)";
 481                 log.info(QUERY_STR + insertQuery);
 482                 log.info("Arguments List: " + insertArgList);
 483                 boolean updateStatus = dbservice.updateDBData(insertQuery, insertArgList);
 484                 if (!updateStatus) {
 485                     throw new DBException("Error while inserting record to DT_USER_PERMISSIONS");
 486                 }
 487             }
 488         }
 489     }
 490
 491     private void linkstatusRelationShip(int sdcArtifactId, int sdcReferenceId, String payload) throws Exception {
 492
 493         ObjectMapper objectMapper = new ObjectMapper();
 494         JsonNode payloadObject = objectMapper.readTree(payload);
 495         ArrayList<String> argList = new ArrayList<>();
 496         argList.add(String.valueOf(sdcArtifactId));
 497         argList.add(String.valueOf(sdcReferenceId));
 498         argList.add(payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue());
 499         argList.add(payloadObject.get(DesignServiceConstants.ACTION).textValue());
 500         argList.add(payloadObject.get(DesignServiceConstants.USER_ID).textValue());
 501
 502         String queryString =
 503                 "INSERT INTO DT_STATUS_RELATIONSHIP (DT_ARTIFACT_TRACKING_ID, DT_ACTION_STATUS_ID) VALUES"
 504                 + " ((SELECT DT_ARTIFACT_TRACKING_ID FROM DT_ARTIFACT_TRACKING"
 505                     + " WHERE ASDC_ARTIFACTS_ID = ? AND ASDC_REFERENCE_ID = ?),"
 506                 + " (SELECT DT_ACTION_STATUS_ID FROM DT_ACTION_STATUS"
 507                     + " WHERE VNF_TYPE = ? AND ACTION = ? AND USER = ?";
 508
 509         if (payloadObject.get(DesignServiceConstants.VNFC_TYPE) != null
 510                 && !payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue().isEmpty()) {
 511             queryString += " AND VNFC_TYPE = ?";
 512         }
 513         queryString += " GROUP BY VNF_TYPE HAVING COUNT(VNF_TYPE)>=1 ))";
 514         log.info(QUERY_STR + queryString);
 515         boolean data = dbservice.updateDBData(queryString, argList);
 516
 517         if (!data) {
 518             throw new DBException("Error while updating Relationship table");
 519         }
 520
 521     }
 522
 523     private int getSDCReferenceID(String payload) throws Exception {
 524
 525         ObjectMapper objectMapper = new ObjectMapper();
 526         JsonNode payloadObject = objectMapper.readTree(payload);
 527         ArrayList<String> argList = new ArrayList<>();
 528         argList.add(payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue());
 529
 530         argList.add(payloadObject.get(DesignServiceConstants.ARTIFACT_TYPE).textValue());
 531         argList.add(payloadObject.get(DesignServiceConstants.ARTIFACT_NAME).textValue());
 532
 533         String queryString =
 534                 "SELECT ASDC_REFERENCE_ID FROM ASDC_REFERENCE WHERE VNF_TYPE = ?"
 535                 + " AND ARTIFACT_TYPE = ? AND ARTIFACT_NAME = ?";
 536
 537         if (payloadObject.get(DesignServiceConstants.ACTION) != null
 538                 && !payloadObject.get(DesignServiceConstants.ACTION).textValue().isEmpty()) {
 539             argList.add(payloadObject.get(DesignServiceConstants.ACTION).textValue());
 540             queryString += " AND ACTION = ?";
 541         }
 542         if (payloadObject.get(DesignServiceConstants.VNFC_TYPE) != null
 543                 && !payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue().isEmpty()) {
 544             argList.add(payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue());
 545             queryString += " AND VNFC_TYPE = ?";
 546         }
 547
 548         log.info(QUERY_STR + queryString);
 549         try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 550             int sdcReferenceId = 0;
 551             while (data.next()) {
 552                 sdcReferenceId = data.getInt("ASDC_REFERENCE_ID");
 553             }
 554             log.info("Got sdcReferenceId = " + sdcReferenceId);
 555             return sdcReferenceId;
 556         }
 557     }
 558
 559     private String getDataFromActionStatus(String payload, String dataValue) throws Exception {
 560         String status = null;
 561         ObjectMapper objectMapper = new ObjectMapper();
 562         JsonNode payloadObject = objectMapper.readTree(payload);
 563         ArrayList<String> argList = new ArrayList<>();
 564         argList.add(payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue());
 565         argList.add(payloadObject.get(DesignServiceConstants.ACTION).textValue());
 566         argList.add(payloadObject.get(DesignServiceConstants.USER_ID).textValue());
 567         String queryString =
 568                 "SELECT " + dataValue + " FROM DT_ACTION_STATUS WHERE VNF_TYPE = ? AND ACTION = ? AND USER = ?";
 569         if (payloadObject.get(DesignServiceConstants.VNFC_TYPE) != null
 570                 && !payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue().isEmpty()) {
 571             argList.add(payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue());
 572             queryString += " AND VNFC_TYPE = ?";
 573         }
 574         log.info(QUERY_STR + queryString);
 575         try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 576             while (data.next()) {
 577                 status = data.getString(STATUS);
 578             }
 579             log.info("DT_ACTION_STATUS Status = " + status);
 580             return status;
 581         }
 582     }
 583
 584     private void setActionStatus(String payload, String status) throws Exception {
 585         ObjectMapper objectMapper = new ObjectMapper();
 586         JsonNode payloadObject = objectMapper.readTree(payload);
 587         ArrayList<String> argList = new ArrayList<>();
 588         argList.add(payloadObject.get(DesignServiceConstants.ACTION).textValue());
 589         argList.add(payloadObject.get(DesignServiceConstants.VNF_TYPE).textValue());
 590
 591         String insertQuery = " INSERT INTO DT_ACTION_STATUS (ACTION, VNF_TYPE, VNFC_TYPE, USER, TECHNOLOGY, UPDATED_DATE, STATUS) VALUES (?,?,?,?,?,sysdate() , ?); ";
 592         if (payloadObject.get(DesignServiceConstants.VNFC_TYPE) != null
 593                 && !payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue().isEmpty()) {
 594             argList.add(payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue());
 595             log.info("Vnfc-Type: " + payloadObject.get(DesignServiceConstants.VNFC_TYPE).textValue());
 596         } else {
 597             argList.add(null);
 598         }
 599         argList.add(payloadObject.get(DesignServiceConstants.USER_ID).textValue());
 600         if (payloadObject.get(DesignServiceConstants.TECHNOLOGY) != null
 601                 && !payloadObject.get(DesignServiceConstants.TECHNOLOGY).textValue().isEmpty()) {
 602             argList.add(payloadObject.get(DesignServiceConstants.TECHNOLOGY).textValue());
 603         } else {
 604             argList.add(null);
 605         }
 606         argList.add(status);
 607
 608         log.info("QueryString: " + insertQuery);
 609         log.info("Arguments List: " + argList);
 610         boolean updateStatus = dbservice.updateDBData(insertQuery, argList);
 611         if (!updateStatus) {
 612             throw new DBException("Error while updating Action Status");
 613         }
 614     }
 615
 616     private void createArtifactTrackingRecord(String payload, String requestId, int sdcArtifactId, int sdcReferenceId)
 617             throws Exception {
 618
 619         ObjectMapper objectMapper = new ObjectMapper();
 620         JsonNode payloadObject = objectMapper.readTree(payload);
 621
 622         ArrayList<String> argList = new ArrayList<>();
 623         argList.add(String.valueOf(sdcArtifactId));
 624         argList.add(String.valueOf(sdcReferenceId));
 625         argList.add(payloadObject.get(DesignServiceConstants.USER_ID).textValue());
 626         if (payloadObject.get(DesignServiceConstants.TECHNOLOGY) != null
 627                 && !payloadObject.get(DesignServiceConstants.TECHNOLOGY).textValue().isEmpty()) {
 628             argList.add(payloadObject.get(DesignServiceConstants.TECHNOLOGY).textValue());
 629         } else {
 630             argList.add("");
 631         }
 632
 633         if (payloadObject.get(DesignServiceConstants.PROTOCOL) != null
 634                 && !payloadObject.get(DesignServiceConstants.PROTOCOL).textValue().isEmpty()) {
 635             argList.add(payloadObject.get(DesignServiceConstants.PROTOCOL).textValue());
 636         } else {
 637             argList.add("");
 638         }
 639
 640         String queryString =
 641                 "INSERT INTO DT_ARTIFACT_TRACKING (ASDC_ARTIFACTS_ID, ASDC_REFERENCE_ID, USER, TECHNOLOGY,"
 642                 + " CREATION_DATE, UPDATED_DATE, ARTIFACT_STATUS, PROTOCOL, IN_CART)"
 643                 + " VALUES (?, ?, ?, ?, sysdate(), sysdate(), 'Created', ?, 'N')";
 644
 645         log.info(QUERY_STR + queryString);
 646         boolean data = dbservice.updateDBData(queryString, argList);
 647         if (!data) {
 648             throw new DBException("Error Updating DT_ARTIFACT_TRACKING");
 649         }
 650     }
 651
 652     private int getSDCArtifactIDbyRequestID(String requestId) throws Exception {
 653         log.info("Starting getArtifactIDbyRequestID DB Operation");
 654         int artifactId = 0;
 655         try {
 656             ArrayList<String> argList = new ArrayList<>();
 657             argList.add("TLSUUID" + requestId);
 658             String queryString = "SELECT ASDC_ARTIFACTS_ID FROM ASDC_ARTIFACTS where SERVICE_UUID = ?";
 659             log.info(QUERY_STR + queryString + " & UUID: " + "TLSUUID" + requestId);
 660             try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 661                 while (data.next()) {
 662                     artifactId = data.getInt("ASDC_ARTIFACTS_ID");
 663                 }
 664             }
 665         } catch (Exception e) {
 666             log.error("An error occurred in getSDCArtifactIDbyRequestID", e);
 667             throw e;
 668         }
 669         log.info("Got SDC_ARTIFACTS_ID As: " + artifactId);
 670         return artifactId;
 671     }
 672
 673
 674     private String getArtifact(String payload, String requestId) throws Exception {
 675         log.info("Starting getArtifact DB Operation");
 676         try {
 677             ObjectMapper objectMapper = new ObjectMapper();
 678             JsonNode payloadObject = objectMapper.readTree(payload);
 679             String artifactName = payloadObject.get("artifact-name").textValue();
 680             ArrayList<String> argList = new ArrayList<>();
 681             argList.add(artifactName);
 682             argList.add(payloadObject.get("artifact-type").textValue());
 683
 684             String queryString =
 685                     "SELECT INTERNAL_VERSION, ARTIFACT_CONTENT FROM ASDC_ARTIFACTS"
 686                     + " where ARTIFACT_NAME = ? AND ARTIFACT_TYPE = ?";
 687
 688             log.info(QUERY_STR + queryString);
 689             String artifactContent = null;
 690             try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 691
 692                 int rowCount = 0;
 693                 int highestVersion = -1;
 694
 695                 while (data.next()) {
 696                     rowCount++;
 697
 698                     int version = data.getInt("INTERNAL_VERSION");
 699                     if (highestVersion < version) {
 700                         artifactContent = data.getString("ARTIFACT_CONTENT");
 701                         highestVersion = version;
 702                     }
 703                 }
 704
 705                 log.debug("No of rows: " + rowCount + " highest Internal Version " + highestVersion);
 706
 707                 if (rowCount == 0) {
 708                     throw new DBException(
 709                             "Sorry!!! APPC DB doesn't have any artifact Named: " + artifactName);
 710                 }
 711
 712                 if (artifactContent == null || artifactContent.isEmpty()) {
 713                     throw new DBException("Sorry!!! Artifact Content is stored blank in APPC DB for " + artifactName
 714                         + " and Internal version " + highestVersion);
 715                 }
 716             }
 717
 718             DesignResponse designResponse = new DesignResponse();
 719             List<ArtifactInfo> artifactInfoList = new ArrayList<>();
 720             ArtifactInfo artifactInfo = new ArtifactInfo();
 721             artifactInfo.setArtifact_content(artifactContent);
 722             artifactInfoList.add(artifactInfo);
 723             designResponse.setArtifactInfo(artifactInfoList);
 724             ObjectMapper mapper = new ObjectMapper();
 725             String jsonString = mapper.writeValueAsString(designResponse);
 726             log.debug("End of getArtifact: " + INFO_STR + jsonString);
 727             return jsonString;
 728         } catch (Exception e) {
 729             log.error(DB_OPERATION_ERROR, e);
 730             throw e;
 731         }
 732     }
 733
 734     private String setStatus(String payload, String requestId) throws Exception {
 735
 736         log.info("Starting getStatus DB Operation");
 737         try {
 738             ObjectMapper objectMapper = new ObjectMapper();
 739             JsonNode payloadObject = objectMapper.readTree(payload);
 740
 741             ArrayList<String> argList = new ArrayList<>();
 742             argList.add(payloadObject.get("artifact_status").textValue());
 743             argList.add(payloadObject.get("action_status").textValue());
 744
 745             argList.add(payloadObject.get(USER_ID).textValue());
 746             argList.add(payloadObject.get("vnf-type").textValue());
 747
 748             String queryString =
 749                 " UPDATE DT_ARTIFACT_TRACKING DAT, DT_STATUS_RELATIONSHIP DSR  SET DAT.ARTIFACT_STATUS = ? , DAS.DT_ACTION_STATUS = ? "
 750                     + " where  DAT.USER = DAS.USER and DSR.DT_ARTIFACT_TRACKING_ID = DAT.DT_ARTIFACT_TRACKING_ID "
 751                     + " and DSR.DT_ACTION_STATUS_ID = DAS.DT_ACTION_STATUS_ID and DAT.USER = ? "
 752                     + " and  DAS.VNF_TYPE = ? ";
 753
 754             if (payloadObject.get(VNFC_TYPE) != null && !payloadObject.get(VNFC_TYPE).textValue().isEmpty()) {
 755                 argList.add(payloadObject.get(VNFC_TYPE).textValue());
 756                 queryString += " and DAS.VNFC_TYPE = ?";
 757             }
 758
 759             log.info(QUERY_STR + queryString);
 760
 761             DesignResponse designResponse = new DesignResponse();
 762             designResponse.setUserId(payloadObject.get(USER_ID).textValue());
 763             boolean update = dbservice.updateDBData(queryString, argList);
 764             if (!update) {
 765                 throw new DBException("Sorry .....Something went wrong while updating the Status");
 766             }
 767
 768             ObjectMapper mapper = new ObjectMapper();
 769             String jsonString = mapper.writeValueAsString(designResponse);
 770             log.info(INFO_STR + jsonString);
 771             return jsonString;
 772         } catch (Exception e) {
 773             log.error(DB_OPERATION_ERROR, e);
 774             throw e;
 775         }
 776     }
 777
 778     private String getStatus(String payload, String requestId) throws Exception {
 779         log.info("Starting getStatus DB Operation");
 780         try {
 781             String vnfcType = null;
 782             ObjectMapper objectMapper = new ObjectMapper();
 783             JsonNode payloadObject = objectMapper.readTree(payload);
 784             String userID = payloadObject.get(USER_ID).textValue();
 785             String vnfType = payloadObject.get("vnf-type").textValue();
 786             if (payloadObject.get(VNFC_TYPE) != null) {
 787                 vnfcType = payloadObject.get(VNFC_TYPE).textValue();
 788             }
 789             ArrayList<String> argList = new ArrayList<>();
 790
 791             argList.add(userID);
 792             argList.add(vnfType);
 793
 794             String queryString =
 795                     "SELECT DAS.VNF_TYPE, DAS.VNFC_TYPE, DAS.STATUS, DAS.ACTION, DAT.ARTIFACT_STATUS"
 796                     + "  from  DT_ACTION_STATUS DAS, DT_ARTIFACT_TRACKING DAT, DT_STATUS_RELATIONSHIP DSR"
 797                     + "  where  DAT.USER = DAS.USER and DSR.DT_ARTIFACT_TRACKING_ID = DAT.DT_ARTIFACT_TRACKING_ID"
 798                     + " and DSR.DT_ACTION_STATUS_ID = DAS.DT_ACTION_STATUS_ID and DAT.USER = ?"
 799                     + " and DAS.VNF_TYPE = ?";
 800
 801             if (vnfcType != null && !vnfcType.isEmpty()) {
 802                 argList.add(vnfcType);
 803                 queryString = queryString + " and DAS.VNFC_TYPE = ?";
 804             }
 805
 806             log.info(QUERY_STR + queryString);
 807
 808             DesignResponse designResponse = new DesignResponse();
 809             designResponse.setUserId(userID);
 810             List<StatusInfo> statusInfoList = new ArrayList<>();
 811             try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 812                 while (data.next()) {
 813                     StatusInfo statusInfo = new StatusInfo();
 814                     statusInfo.setAction(data.getString("ACTION"));
 815                     statusInfo.setAction_status(data.getString(STATUS));
 816                     statusInfo.setArtifact_status(data.getString("ARTIFACT_STATUS"));
 817                     statusInfo.setVnf_type(data.getString("VNF_TYPE"));
 818                     statusInfo.setVnfc_type(data.getString("VNFC_TYPE"));
 819                     statusInfoList.add(statusInfo);
 820                 }
 821
 822                 if (statusInfoList.isEmpty()) {
 823                     throw new DBException(
 824                             "OOPS!!!! No VNF information available for VNF-TYPE: " + vnfType + " for User: " + userID);
 825                 }
 826             }
 827             designResponse.setStatusInfoList(statusInfoList);
 828             ObjectMapper mapper = new ObjectMapper();
 829             String jsonString = mapper.writeValueAsString(designResponse);
 830             log.info(INFO_STR + jsonString);
 831             return jsonString;
 832         } catch (SQLException e) {
 833             log.error(DB_OPERATION_ERROR, e);
 834             throw e;
 835         } catch (Exception e) {
 836             log.error(DB_OPERATION_ERROR + e.getMessage());
 837             log.error("Exception:", e);
 838             throw e;
 839         }
 840     }
 841
 842     private String getGuiReference(String payload, String requestId) {
 843         // TODO Auto-generated method stub
 844         return null;
 845     }
 846
 847     private String getArtifactReference(String payload, String requestId) {
 848         // TODO Auto-generated method stub
 849         return null;
 850     }
 851
 852     private String getDesigns(String payload, String requestId) throws Exception {
 853
 854         String queryString;
 855         log.info("Starting getDesigns DB Operation");
 856
 857         try {
 858             ObjectMapper objectMapper = new ObjectMapper();
 859             JsonNode payloadObject = objectMapper.readTree(payload);
 860             String userID = payloadObject.get(USER_ID).textValue();
 861             String filterKey = null;
 862             if (payloadObject.hasNonNull("filter")) {
 863                 filterKey = payloadObject.get("filter").textValue();
 864             }
 865             ArrayList<String> argList = new ArrayList<>();
 866             argList.add(userID);
 867             argList.add(userID);
 868             queryString =
 869                     "SELECT DISTINCT AR.VNF_TYPE, AR.VNFC_TYPE, DAT.PROTOCOL, DAT.IN_CART, AR.ACTION, AR.ARTIFACT_NAME,"
 870                     + " AR.ARTIFACT_TYPE, DUP.PERMISSION, DAS.USER  FROM  "
 871                     + DesignServiceConstants.DB_DT_ARTIFACT_TRACKING + " DAT, "
 872                     + DesignServiceConstants.DB_SDC_REFERENCE + " AR, " + DesignServiceConstants.DB_DT_USER_PERMISSIONS
 873                     + " DUP, " + DesignServiceConstants.DB_DT_ACTION_STATUS + " DAS "
 874                     + " WHERE  AR.VNF_TYPE = DUP.VNF_TYPE AND DAS.VNF_TYPE = DUP.VNF_TYPE"
 875                     + " AND DAT.ASDC_REFERENCE_ID = AR.ASDC_REFERENCE_ID AND DUP.USER_ID = ? AND AR.VNF_TYPE IN"
 876                     + " (SELECT DUP.VNF_TYPE FROM DT_USER_PERMISSIONS DUP"
 877                     + " WHERE DUP.PERMISSION IN('owner','contributor') AND DUP.USER_ID = ? GROUP BY VNF_TYPE)";
 878
 879             if (filterKey != null) {
 880                 queryString += " AND AR.ARTIFACT_NAME like '%" + filterKey + "%'";
 881             }
 882             queryString += " GROUP BY AR.VNF_TYPE, AR.ARTIFACT_NAME";
 883
 884             log.info("QUERY FOR getDesigns: " + queryString);
 885             DesignResponse designResponse = new DesignResponse();
 886             designResponse.setUserId(userID);
 887             List<DesignInfo> designInfoList = new ArrayList<>();
 888             try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 889                 while (data.next()) {
 890                     DesignInfo designInfo = new DesignInfo();
 891                     designInfo.setInCart(data.getString("IN_CART"));
 892                     designInfo.setProtocol(data.getString("PROTOCOL"));
 893                     designInfo.setVnf_type(data.getString("VNF_TYPE"));
 894                     designInfo.setVnfc_type(data.getString("VNFC_TYPE"));
 895                     designInfo.setAction(data.getString("ACTION"));
 896                     designInfo.setArtifact_type(data.getString("ARTIFACT_TYPE"));
 897                     designInfo.setArtifact_name(data.getString("ARTIFACT_NAME"));
 898                     designInfo.setPermission(data.getString("PERMISSION"));
 899                     designInfo.setCreatorUserId(data.getString("USER"));
 900                     designInfoList.add(designInfo);
 901                 }
 902                 if (designInfoList.isEmpty()) {
 903                     throw new DBException(
 904                             "Welcome to CDT, Looks like you don't have Design Yet... Let's create some....");
 905                 }
 906             }
 907             designResponse.setDesignInfoList(designInfoList);
 908             ObjectMapper mapper = new ObjectMapper();
 909             String jsonString = mapper.writeValueAsString(designResponse);
 910             log.info(INFO_STR + jsonString);
 911             return jsonString;
 912         } catch (Exception e) {
 913             log.error("Error while Starting getDesigns DB operation:", e);
 914             throw e;
 915         }
 916     }
 917
 918     private String retrieveVnfPermissions(String payload, String requestId) throws Exception {
 919         log.info("Starting retrieveVnfPermissions DB Operation");
 920
 921         try {
 922             ObjectMapper objectMapper = new ObjectMapper();
 923             JsonNode payloadObject = objectMapper.readTree(payload);
 924             ArrayList<String> argList = new ArrayList<>();
 925             String vnfType = payloadObject.get(VNF_TYPE).textValue();
 926             argList.add(vnfType);
 927
 928             String queryString = "SELECT USER_ID, PERMISSION FROM DT_USER_PERMISSIONS WHERE VNF_TYPE = ?";
 929             log.info(QUERY_STR + queryString);
 930             List<UserPermissionInfo> userPermList = new ArrayList<>();
 931             try (ResultSet data = dbservice.getDBData(queryString, argList)) {
 932
 933                 int rowCount = 0;
 934
 935                 while (data.next()) {
 936                     rowCount++;
 937                     UserPermissionInfo userPermInfo = new UserPermissionInfo();
 938                     userPermInfo.setUserID(data.getString(COLUMN_USER_ID));
 939                     userPermInfo.setPermission(data.getString(COLUMN_PERMISSION));
 940                     userPermList.add(userPermInfo);
 941                 }
 942                 log.info("Number of rows=" + rowCount + ", for vnf-type=" + vnfType);
 943
 944                 if (userPermList.isEmpty()) {
 945                     throw new DBException("No user permissions information available for VNF-TYPE: " + vnfType);
 946                 }
 947             }
 948             DesignResponse designResponse = new DesignResponse();
 949             designResponse.setUsers(userPermList);
 950             designResponse.setVnfType(vnfType);
 951             ObjectMapper mapper = new ObjectMapper();
 952             String jsonString = mapper.writeValueAsString(designResponse);
 953             log.info("End of retrieveVnfPermissions: " + INFO_STR + jsonString);
 954             return jsonString;
 955
 956         } catch (Exception e) {
 957             log.error("Error while Starting retrieveVnfPermissions DB operation:", e);
 958             throw e;
 959         }
 960     }
 961
 962     public static class ArtifactHandlerFactory {
 963         public ArtifactHandlerClient ahi() throws Exception {
 964             return new ArtifactHandlerClient();
 965         }
 966     }
 967
 968 }
 969