From b14db107c5dd7eadbfecde037ad68633775502de Mon Sep 17 00:00:00 2001 From: Patrick Brady Date: Tue, 23 Jul 2019 11:54:01 -0700 Subject: Prepared statements for DG services Convert sql queries to prepared statements in other parts of appc. Change-Id: If29225394de2ab286e4f2f7bdd17f6af6b286576 Signed-off-by: Patrick Brady Issue-ID: OJSI-25 --- .../data/services/db/CtxParameterizedResolver.java | 230 +++++++++++++++++++++ .../appc/data/services/db/DGGeneralDBService.java | 195 +++++++++-------- .../appc/data/services/db/DbLibServiceQueries.java | 182 ++++++++++++++++ 3 files changed, 519 insertions(+), 88 deletions(-) create mode 100644 appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/CtxParameterizedResolver.java create mode 100644 appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DbLibServiceQueries.java (limited to 'appc-config/appc-data-services/provider/src/main/java') diff --git a/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/CtxParameterizedResolver.java b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/CtxParameterizedResolver.java new file mode 100644 index 000000000..bc4728a6b --- /dev/null +++ b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/CtxParameterizedResolver.java @@ -0,0 +1,230 @@ +/*- + * ============LICENSE_START======================================================= + * ONAP : APPC + * ================================================================================ + * Copyright (C) 2019 AT&T Intellectual Property. All rights reserved. + * ================================================================================ + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + * ============LICENSE_END========================================================= + */ + + +package org.onap.appc.data.services.db; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.util.ArrayList; + +import javax.sql.rowset.CachedRowSet; + +import org.apache.commons.lang3.StringUtils; +import org.onap.ccsdk.sli.core.dblib.DbLibService; +import org.onap.ccsdk.sli.core.sli.SvcLogicContext; + +import com.att.eelf.configuration.EELFLogger; +import com.att.eelf.configuration.EELFManager; + +public class CtxParameterizedResolver { + + private static String CRYPT_KEY = "QtfJMKggVk"; + private static final EELFLogger log = EELFManager.getInstance().getLogger(CtxParameterizedResolver.class); + + protected static String resolveCtxVars(String key, SvcLogicContext ctx, ArrayList arguments) { + if (key == null) { + return (null); + } + + if (key.startsWith("'") && key.endsWith("'")) { + key = key.substring(1, key.length() - 1); + log.debug("Stripped outer single quotes - key is now [" + key + "]"); + } + + String[] keyTerms = key.split("\\s+"); + + StringBuffer sqlBuffer = new StringBuffer(); + + for (int i = 0; i < keyTerms.length; i++) { + sqlBuffer.append(resolveTerm(keyTerms[i], ctx, arguments)); + sqlBuffer.append(" "); + } + + return (sqlBuffer.toString()); + } + + private static String resolveTerm(String term, SvcLogicContext ctx, ArrayList arguments) { + if (term == null) { + return (null); + } + + log.trace("resolveTerm: term is " + term); + + if (term.startsWith("$") && (ctx != null)) { + // Resolve any index variables. + term = resolveCtxVariable(term.substring(1), ctx); + // Escape single quote + if (term != null) { + term = term.replaceAll("'", "''"); + } + //valueOf will store null values as a String "null" + arguments.add(String.valueOf(term)); + return "?"; + } else { + return (term); + } + + } + + private static String resolveCtxVariable(String ctxVarName, SvcLogicContext ctx) { + + if (ctxVarName.indexOf('[') == -1) { + // Ctx variable contains no arrays + if ("CRYPT_KEY".equals(ctxVarName)) { + // Handle crypt key as special case. If it's set as a context + // variable, use it. Otherwise, use + // configured crypt key. + String cryptKey = ctx.getAttribute(ctxVarName); + if ((cryptKey != null) && (cryptKey.length() > 0)) { + return (cryptKey); + } else { + return (CRYPT_KEY); + } + } + return (ctx.getAttribute(ctxVarName)); + } + + // Resolve any array references + StringBuffer sbuff = new StringBuffer(); + String[] ctxVarParts = ctxVarName.split("\\["); + sbuff.append(ctxVarParts[0]); + for (int i = 1; i < ctxVarParts.length; i++) { + if (ctxVarParts[i].startsWith("$")) { + int endBracketLoc = ctxVarParts[i].indexOf("]"); + if (endBracketLoc == -1) { + // Missing end bracket ... give up parsing + log.warn("Variable reference " + ctxVarName + " seems to be missing a ']'"); + return (ctx.getAttribute(ctxVarName)); + } + + String idxVarName = ctxVarParts[i].substring(1, endBracketLoc); + String remainder = ctxVarParts[i].substring(endBracketLoc); + + sbuff.append("["); + sbuff.append(ctx.getAttribute(idxVarName)); + sbuff.append(remainder); + + } else { + // Index is not a variable reference + sbuff.append("["); + sbuff.append(ctxVarParts[i]); + } + } + + return (ctx.getAttribute(sbuff.toString())); + } + + protected static void saveCachedRowSetToCtx(CachedRowSet results, SvcLogicContext ctx, String prefix, DbLibService dblibSvc) + throws SQLException { + if (ctx != null) { + if ((prefix != null) && prefix.endsWith("[]")) { + // Return an array. + String pfx = prefix.substring(0, prefix.length() - 2); + int idx = 0; + do { + ResultSetMetaData rsMeta = results.getMetaData(); + int numCols = rsMeta.getColumnCount(); + + for (int i = 0; i < numCols; i++) { + String colValue = null; + String tableName = rsMeta.getTableName(i + 1); + if (rsMeta.getColumnType(i + 1) == java.sql.Types.VARBINARY) { + colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1), + dblibSvc); + } else { + colValue = results.getString(i + 1); + } + log.debug("Setting " + pfx + "[" + idx + "]." + + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue); + ctx.setAttribute(pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), + colValue); + } + idx++; + } while (results.next()); + log.debug("Setting " + pfx + "_length = " + idx); + ctx.setAttribute(pfx + "_length", "" + idx); + } else { + ResultSetMetaData rsMeta = results.getMetaData(); + int numCols = rsMeta.getColumnCount(); + + for (int i = 0; i < numCols; i++) { + String colValue = null; + String tableName = rsMeta.getTableName(i + 1); + if ("VARBINARY".equalsIgnoreCase(rsMeta.getColumnTypeName(i + 1))) { + colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1), + dblibSvc); + } else { + colValue = results.getString(i + 1); + } + if (prefix != null) { + log.debug("Setting " + prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + + colValue); + ctx.setAttribute(prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue); + } else { + log.debug("Setting " + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue); + ctx.setAttribute(rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue); + } + } + } + } + } + + private static String decryptColumn(String tableName, String colName, byte[] colValue, DbLibService dblibSvc) { + String strValue = new String(colValue); + + if (StringUtils.isAsciiPrintable(strValue)) { + + // If printable, not encrypted + return (strValue); + } else { + ResultSet results = null; + try (Connection conn = dblibSvc.getConnection(); + PreparedStatement stmt = conn.prepareStatement("SELECT CAST(AES_DECRYPT(?, ?) AS CHAR(50)) FROM DUAL")) { + + stmt.setBytes(1, colValue); + stmt.setString(2, CRYPT_KEY); + results = stmt.executeQuery(); + + if ((results != null) && results.next()) { + strValue = results.getString(1); + log.debug("Decrypted value is " + strValue); + } else { + log.warn("Cannot decrypt " + tableName + "." + colName); + } + } catch (Exception e) { + log.error("Caught exception trying to decrypt " + tableName + "." + colName, e); + }finally { + if (results != null) { + try { + results.close(); + } catch (SQLException se) { + log.error("Caught exception trying to close ResultSet",se); + } + } + } + } + return (strValue); + } +} diff --git a/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DGGeneralDBService.java b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DGGeneralDBService.java index 83d47ba30..65c0b3015 100644 --- a/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DGGeneralDBService.java +++ b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DGGeneralDBService.java @@ -27,7 +27,7 @@ package org.onap.appc.data.services.db; import java.util.Set; import org.apache.commons.lang3.StringUtils; - +import org.onap.ccsdk.sli.core.dblib.DbLibService; import org.onap.ccsdk.sli.core.sli.SvcLogicContext; import org.onap.ccsdk.sli.core.sli.SvcLogicException; import org.onap.ccsdk.sli.core.sli.SvcLogicResource; @@ -39,7 +39,8 @@ import com.att.eelf.configuration.EELFManager; public class DGGeneralDBService { private static final EELFLogger log = EELFManager.getInstance().getLogger(DGGeneralDBService.class); - private SvcLogicResource serviceLogic; + //private SvcLogicResource serviceLogic; + private DbLibServiceQueries dblib; private static DGGeneralDBService dgGeneralDBService = null; public static DGGeneralDBService initialise() { @@ -51,31 +52,37 @@ public class DGGeneralDBService { } private DGGeneralDBService() { - if (serviceLogic == null) { - serviceLogic = new SqlResource(); + if (dblib == null) { + dblib = new DbLibServiceQueries(); } } - protected DGGeneralDBService(SqlResource svcLogic) { - if (serviceLogic == null) { - serviceLogic = svcLogic; + protected DGGeneralDBService(DbLibService dbLibService) { + if (dblib == null) { + dblib = new DbLibServiceQueries(dbLibService); + } + } + + protected DGGeneralDBService(DbLibServiceQueries dbLibServiceQueries) { + if (dblib == null) { + dblib = dbLibServiceQueries; } } public QueryStatus getDeviceProtocolByVnfType(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * FROM DEVICE_INTERFACE_PROTOCOL WHERE vnf_type = $vnf-type ;"; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } public QueryStatus getDeviceAuthenticationByVnfType(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * FROM DEVICE_AUTHENTICATION WHERE vnf_type = $vnf-type ;"; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; @@ -83,9 +90,9 @@ public class DGGeneralDBService { public QueryStatus getConfigFileReferenceByVnfType(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * FROM CONFIG_FILE_REFERENCE WHERE vnf_type = $vnf-type ;"; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -93,24 +100,26 @@ public class DGGeneralDBService { public QueryStatus getConfigFileReferenceByFileTypeNVnfType(SvcLogicContext ctx, String prefix, String fileType) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { - String key = "SELECT * FROM CONFIG_FILE_REFERENCE WHERE file_type = '" + fileType - + "' and vnf_type = $vnf-type ;"; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + if (dblib != null && ctx != null) { + ctx.setAttribute("file-type", fileType); + String key = "SELECT * FROM CONFIG_FILE_REFERENCE WHERE file_type = $file-type" + + " and vnf_type = $vnf-type ;"; + status = dblib.query(key, prefix, ctx); } return status; } public QueryStatus getTemplate(SvcLogicContext ctx, String prefix, String fileCategory) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { + ctx.setAttribute("file-category", fileCategory); String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id " + " FROM ASDC_ARTIFACTS " + " WHERE asdc_artifacts_id = ( SELECT MAX(a.asdc_artifacts_id) configfileid " + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name " - + " AND file_category = '" + fileCategory + "'" + " AND action = $request-action " + + " AND file_category = $file-category AND action = $request-action " + " AND vnf_type = $vnf-type " + " AND vnfc_type = $vnfc-type ) ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -118,14 +127,15 @@ public class DGGeneralDBService { public QueryStatus getTemplateByVnfTypeNAction(SvcLogicContext ctx, String prefix, String fileCategory) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { + ctx.setAttribute("file-category", fileCategory); String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id " + " FROM ASDC_ARTIFACTS " + " WHERE asdc_artifacts_id = (SELECT MAX(a.asdc_artifacts_id) configfileid " + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name " - + " AND file_category = '" + fileCategory + "'" + " AND action = $request-action " + + " AND file_category = $file-category AND action = $request-action " + " AND vnf_type = $vnf-type ) ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; @@ -134,14 +144,15 @@ public class DGGeneralDBService { public QueryStatus getTemplateByVnfType(SvcLogicContext ctx, String prefix, String fileCategory) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { + ctx.setAttribute("file-category", fileCategory); String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id " + " FROM ASDC_ARTIFACTS " + " WHERE asdc_artifacts_id = (SELECT MAX(a.asdc_artifacts_id) configfileid " + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name " - + " AND file_category = '" + fileCategory + "'" + " AND vnf_type = $vnf-type ) ; "; + + " AND file_category = $file-category AND vnf_type = $vnf-type ) ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -149,13 +160,14 @@ public class DGGeneralDBService { public QueryStatus getTemplateByTemplateName(SvcLogicContext ctx, String prefix, String templateName) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { + ctx.setAttribute("template-name", templateName); String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id " + " FROM ASDC_ARTIFACTS " + " WHERE asdc_artifacts_id = (SELECT MAX(asdc_artifacts_id) configfileid " - + " FROM ASDC_ARTIFACTS " + " WHERE artifact_name = '" + templateName + "' ) ; "; + + " FROM ASDC_ARTIFACTS " + " WHERE artifact_name = $template-name ) ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -163,22 +175,22 @@ public class DGGeneralDBService { public QueryStatus getConfigureActionDGByVnfTypeNAction(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * " + " FROM CONFIGURE_ACTION_DG " + " where vnf_type = $vnf-type and action = $request-action ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } public QueryStatus getConfigureActionDGByVnfType(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * " + " FROM CONFIGURE_ACTION_DG " + " where vnf_type = $vnf-type and action IS NULL ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -186,11 +198,12 @@ public class DGGeneralDBService { public QueryStatus getMaxConfigFileId(SvcLogicContext ctx, String prefix, String fileCategory) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { - String key = "SELECT MAX(config_file_id) configfileid " + " FROM CONFIGFILES " + " WHERE file_category = '" - + fileCategory + "'" + " AND vnf_id = $vnf-id AND vm_name = $vm-name ; "; + if (dblib != null && ctx != null) { + ctx.setAttribute("file-category", fileCategory); + String key = "SELECT MAX(config_file_id) configfileid " + " FROM CONFIGFILES " + " WHERE file_category = " + + "$file-category AND vnf_id = $vnf-id AND vm_name = $vm-name ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -199,7 +212,7 @@ public class DGGeneralDBService { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "INSERT INTO CONFIGFILES " + " SET data_source = $data-source , " + " service_instance_id = $service-instance-id ," + " action = $request-action ," + " vnf_type = $vnf-type ," + " vnfc_type = $vnfc-type ," @@ -207,7 +220,7 @@ public class DGGeneralDBService { + " vm_name = $vm-name ," + " file_category = $file-category ," + " file_content = $file-content ; "; - status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx); + status = dblib.save(key, ctx); } return status; @@ -220,17 +233,17 @@ public class DGGeneralDBService { QueryStatus status = null; String key = null; - if (serviceLogic != null && ctx != null) { - + if (dblib != null && ctx != null) { + ctx.setAttribute("file-id", fileId); if ("Y".equals(sdcInd)) key = "INSERT INTO PREPARE_FILE_RELATIONSHIP " + " SET service_instance_id = $service-instance-id , " - + " request_id = $request-id , " + " asdc_artifacts_id = " + fileId + " ;"; + + " request_id = $request-id , " + " asdc_artifacts_id = $file-id ;"; else key = "INSERT INTO PREPARE_FILE_RELATIONSHIP " + " SET service_instance_id = $service-instance-id , " - + " request_id = $request-id , " + " config_file_id = " + fileId + " ;"; + + " request_id = $request-id , " + " config_file_id = $file-id ;"; - status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx); + status = dblib.save(key, ctx); log.info("DGGeneralDBService.savePrepareRelationship()" + ctx.getAttributeKeySet()); } @@ -255,7 +268,7 @@ public class DGGeneralDBService { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "INSERT INTO UPLOAD_CONFIG " + " SET request_id = $request-id , " + " action = $request-action , " + " originator_id = $originator-id , " + " vnf_id = $vnf-id , " + " vnf_name = $vnf-name , " + " vm_name = $vm-name , " @@ -263,7 +276,7 @@ public class DGGeneralDBService { + " vnfc_type = $vnfc-type , " + " config_indicator = 'Current' , " + " content = $tmp.escaped.devicerunningconfig ; "; - status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx); + status = dblib.save(key, ctx); log.info("DGGeneralDBService.saveUploadConfig()" + ctx.getAttributeKeySet()); @@ -286,12 +299,12 @@ public class DGGeneralDBService { public QueryStatus updateUploadConfig(SvcLogicContext ctx, String prefix, int maxId) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "UPDATE UPLOAD_CONFIG " + " SET config_indicator = null " + " WHERE upload_config_id != " + maxId + " AND config_indicator = 'Current' " + " AND vnf_id = $vnf-id " + " AND vnfc_type = $vnfc-type ; "; - status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx); + status = dblib.save(key, ctx); log.info("DGGeneralDBService.updateUploadConfig()" + ctx.getAttributeKeySet()); @@ -304,15 +317,17 @@ public class DGGeneralDBService { public QueryStatus getTemplateByArtifactType(SvcLogicContext ctx, String prefix, String fileCategory, String artifactType) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { + ctx.setAttribute("file-category", fileCategory); + ctx.setAttribute("artifact-type", artifactType); String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id " + " FROM ASDC_ARTIFACTS " + " WHERE asdc_artifacts_id = (SELECT MAX(a.asdc_artifacts_id) configfileid " + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name " - + " AND file_category = '" + fileCategory + "'" + " AND action = $request-action " - + " AND artifactType = '" + artifactType + "'" + " AND vnf_type = $vnf-type ) ; "; + + " AND file_category = $file-category AND action = $request-action " + + " AND artifactType = $artifact-type AND vnf_type = $vnf-type ) ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -321,17 +336,19 @@ public class DGGeneralDBService { public QueryStatus getConfigFilesByVnfVmNCategory(SvcLogicContext ctx, String prefix, String fileCategory, String vnfId, String vmName) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { - + if (dblib != null && ctx != null) { + ctx.setAttribute("file-category", fileCategory); + ctx.setAttribute("vnf-id", vnfId); + ctx.setAttribute("vm-name", vmName); String key = "SELECT file_content , config_file_id " + " FROM CONFIGFILES " + " WHERE config_file_id = ( SELECT MAX(config_file_id) configfileid " + " FROM CONFIGFILES " - + " WHERE file_category = '" + fileCategory + "'" - + " AND vnf_id = '" + vnfId + "'" - + " AND vm_name = '" + vmName + "' ) ; "; + + " WHERE file_category = $file-category" + + " AND vnf_id = $vnf-id" + + " AND vm_name = $vm-name ) ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -340,9 +357,9 @@ public class DGGeneralDBService { public QueryStatus getDownloadConfigTemplateByVnf(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * FROM DOWNLOAD_CONFIG_TEMPLATE WHERE vnf_type = $vnf-type ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -353,16 +370,14 @@ public class DGGeneralDBService { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { - + if (dblib != null && ctx != null) { String key = "INSERT INTO CONFIG_TRANSACTION_LOG " + " SET request_id = $request-id , " + " message_type = $log-message-type , " + " message = $log-message ;"; - status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx); - + status = dblib.save(key, ctx); } @@ -374,7 +389,7 @@ public class DGGeneralDBService { public QueryStatus getVnfcReferenceByVnfcTypeNAction(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * " + " FROM VNFC_REFERENCE " @@ -384,7 +399,7 @@ public class DGGeneralDBService { + " ORDER BY vm_instance, vnfc_instance ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -393,7 +408,7 @@ public class DGGeneralDBService { public QueryStatus getVnfcReferenceByVnfTypeNAction(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * " + " FROM VNFC_REFERENCE " @@ -401,7 +416,7 @@ public class DGGeneralDBService { + " AND action = $request-action " + " ORDER BY vm_instance, vnfc_instance ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -410,7 +425,7 @@ public class DGGeneralDBService { public QueryStatus getUploadConfigInfo(SvcLogicContext ctx, String prefix) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { + if (dblib != null && ctx != null) { String key = "SELECT * , UNIX_TIMESTAMP(UPLOAD_DATE) UPLOAD_TIMESTAMP " + " FROM UPLOAD_CONFIG " @@ -418,7 +433,7 @@ public class DGGeneralDBService { "( SELECT MAX(upload_config_id) uploadconfigid " + " FROM UPLOAD_CONFIG " + " WHERE vnf_id = $vnf-id AND vm_name = $vm-name ) ; "; - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -429,13 +444,13 @@ public class DGGeneralDBService { QueryStatus status = null; SvcLogicContext localContext = new SvcLogicContext(); localContext.setAttribute("vnf-type", vnf_type); - if (serviceLogic != null) { + if (dblib != null) { String queryString = "select max(internal_version) as maxInternalVersion, artifact_name as artifactName from ASDC_ARTIFACTS " + " where artifact_name in (select artifact_name from ASDC_REFERENCE where vnf_type= $vnf-type " + " and file_category = 'capability' )" ; log.info(fn + "Query String : " + queryString); - status = serviceLogic.query("SQL", false, null, queryString, null, null, localContext); + status = dblib.query(queryString, localContext); if(status.toString().equals("FAILURE")) throw new SvcLogicException("Error - while getting capabilitiesData "); @@ -444,7 +459,7 @@ public class DGGeneralDBService { " where artifact_name = $artifactName and internal_version = $maxInternalVersion "; log.debug(fn + "Query String : " + queryString1); - status = serviceLogic.query("SQL", false, null, queryString1, null, null, localContext); + status = dblib.query(queryString1, localContext); if (status.toString().equals("NOT_FOUND")) return null; if(status.toString().equals("FAILURE")) @@ -457,18 +472,20 @@ public class DGGeneralDBService { public QueryStatus getTemplateWithTemplateModelId(SvcLogicContext ctx, String prefix, String fileCategory, String templateModelId) throws SvcLogicException { QueryStatus status = null; - String templatePattern = "'%_"+ templateModelId +"%'"; - if (serviceLogic != null && ctx != null) { + String templatePattern = "%_"+ templateModelId +"%"; + if (dblib != null && ctx != null) { + ctx.setAttribute("file-category", fileCategory); + ctx.setAttribute("template-pattern", templatePattern); String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id " + " FROM ASDC_ARTIFACTS " + " WHERE asdc_artifacts_id = ( SELECT MAX(a.asdc_artifacts_id) configfileid " + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name " - + " AND file_category = '" + fileCategory + "'" + " AND action = $request-action " + + " AND file_category = $file-category AND action = $request-action " + " AND vnf_type = $vnf-type " + " AND vnfc_type = $vnfc-type ) and ASDC_ARTIFACTS.artifact_name like " - + templatePattern + "; "; + + "$template-pattern ; "; log.info("getTemplateWithTemplateModelId()::: with template:::"+ key); - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } @@ -476,18 +493,20 @@ public class DGGeneralDBService { public QueryStatus getTemplateByVnfTypeNActionWithTemplateModelId(SvcLogicContext ctx, String prefix, String fileCategory, String templateModelId) throws SvcLogicException { QueryStatus status = null; - String templatePattern = "'%_"+ templateModelId +"%'"; - if (serviceLogic != null && ctx != null) { + String templatePattern = "%_"+ templateModelId +"%"; + if (dblib != null && ctx != null) { + ctx.setAttribute("file-category", fileCategory); + ctx.setAttribute("template-pattern", templatePattern); String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id " + " FROM ASDC_ARTIFACTS " + " WHERE asdc_artifacts_id = (SELECT MAX(a.asdc_artifacts_id) configfileid " + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name " - + " AND file_category = '" + fileCategory + "'" + " AND action = $request-action " + + " AND file_category = $file-category AND action = $request-action " + " AND vnf_type = $vnf-type ) and ASDC_ARTIFACTS.artifact_name like " - + templatePattern + "; "; + + "$template-pattern ; "; log.info("getTemplateByVnfTypeNActionWithTemplateModelId()::: with template:::"+ key); - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; @@ -496,18 +515,18 @@ public class DGGeneralDBService { public QueryStatus getVnfcReferenceByVnfTypeNActionWithTemplateModelId(SvcLogicContext ctx, String prefix, String templateModelId) throws SvcLogicException { QueryStatus status = null; - if (serviceLogic != null && ctx != null) { - + if (dblib != null && ctx != null) { + ctx.setAttribute("template-model-id", templateModelId); String key = "SELECT * " + " FROM VNFC_REFERENCE " + " WHERE vnf_type = $vnf-type " + " AND action = $request-action " - + " AND template_id = '" - + templateModelId + "'" + + " AND template_id = " + + "$template-model-id" + " ORDER BY vm_instance, vnfc_instance ; "; log.info("getVnfcReferenceByVnfTypeNActionWithTemplateModelId()::: with template:::"+ key); - status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx); + status = dblib.query(key, prefix, ctx); } return status; } diff --git a/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DbLibServiceQueries.java b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DbLibServiceQueries.java new file mode 100644 index 000000000..827d32824 --- /dev/null +++ b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DbLibServiceQueries.java @@ -0,0 +1,182 @@ +/*- + * ============LICENSE_START======================================================= + * ONAP : APPC + * ================================================================================ + * Copyright (C) 2019 AT&T Intellectual Property. All rights reserved. + * ================================================================================ + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + * ============LICENSE_END========================================================= + */ + +package org.onap.appc.data.services.db; + +import java.sql.SQLException; +import java.util.ArrayList; + +import javax.sql.rowset.CachedRowSet; + +import org.onap.ccsdk.sli.core.dblib.DBResourceManager; +import org.onap.ccsdk.sli.core.dblib.DbLibService; +import org.onap.ccsdk.sli.core.sli.SvcLogicContext; +import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus; +import org.onap.ccsdk.sli.core.sli.provider.SvcLogicService; +import org.osgi.framework.Bundle; +import org.osgi.framework.BundleContext; +import org.osgi.framework.FrameworkUtil; +import org.osgi.framework.ServiceReference; + +import com.att.eelf.configuration.EELFLogger; +import com.att.eelf.configuration.EELFManager; + +public class DbLibServiceQueries { + + private static final String DBLIB_SERVICE = "org.onap.ccsdk.sli.core.dblib.DbLibService"; + private static final EELFLogger log = EELFManager.getInstance().getLogger(DbLibServiceQueries.class); + + DbLibService dbLibService; + + public DbLibServiceQueries() { + this.dbLibService = getDbLibService(); + if(this.dbLibService == null) { + throw new NullPointerException("DbLibService reference not found"); + } + } + + public DbLibServiceQueries(DbLibService dbLibService) { + this.dbLibService = dbLibService; + if(this.dbLibService == null) { + throw new NullPointerException("Provided DbLibService is null"); + } + } + + public DbLibServiceQueries(DbLibService dbLibService, boolean allowNull) { + this.dbLibService = dbLibService; + if(this.dbLibService == null && !allowNull) { + throw new NullPointerException("Provided DbLibService is null"); + } + } + + public QueryStatus query(String query, SvcLogicContext ctx) { + ArrayList arguments = new ArrayList<>(); + query = CtxParameterizedResolver.resolveCtxVars(query, ctx, arguments); + return performQuery(query, ctx, null, arguments); + } + + public QueryStatus query(String query, String prefix, SvcLogicContext ctx) { + ArrayList arguments = new ArrayList<>(); + query = CtxParameterizedResolver.resolveCtxVars(query, ctx, arguments); + return performQuery(query, ctx, prefix, arguments); + } + + public QueryStatus query(String query, SvcLogicContext ctx, ArrayList arguments) { + return performQuery(query, ctx, null, valueOfArrayList(arguments)); + } + public QueryStatus query(String query, SvcLogicContext ctx, String prefix, ArrayList arguments) { + return performQuery(query, ctx, prefix, valueOfArrayList(arguments)); + } + + private QueryStatus performQuery(String query, SvcLogicContext ctx, String prefix, ArrayList arguments) { + + CachedRowSet result = null; + try { + result = dbLibService.getData(query, arguments, null); + if (!result.next()) { + log.debug("No data found"); + return QueryStatus.NOT_FOUND; + } else { + CtxParameterizedResolver.saveCachedRowSetToCtx(result, ctx, prefix, dbLibService); + } + } catch (SQLException e) { + log.error("Exception in query()",e); + return QueryStatus.FAILURE; + } + return QueryStatus.SUCCESS; + + } + public QueryStatus save(String query, SvcLogicContext ctx) { + ArrayList arguments = new ArrayList<>(); + query = CtxParameterizedResolver.resolveCtxVars(query, ctx, arguments); + return performSave(query, arguments); + } + + public QueryStatus save(String query, SvcLogicContext ctx, ArrayList arguments) { + return performSave(query, valueOfArrayList(arguments)); + } + + private QueryStatus performSave(String query, ArrayList arguments) { + boolean success = false; + try { + success = dbLibService.writeData(query, arguments, null); + } catch (SQLException e) { + log.error("Exception in save()",e); + success = false; + } + if(success) { + return QueryStatus.SUCCESS; + } + return QueryStatus.FAILURE; + } + + private static DbLibService getDbLibService() { + + DbLibService dbLibService = null; + BundleContext bundleContext = null; + ServiceReference serviceRef = null; + + Bundle bundle = FrameworkUtil.getBundle(SvcLogicService.class); + + if (bundle != null) { + bundleContext = bundle.getBundleContext(); + } + + if (bundleContext != null) { + log.debug("Getting bundle Context"); + serviceRef = bundleContext.getServiceReference(DBLIB_SERVICE); + } + + if (serviceRef == null) { + log.warn("Could not find service reference for DBLib service"); + + } else { + dbLibService = (DbLibService)bundleContext.getService(serviceRef); + if (dbLibService == null) { + log.warn("DBLIB_SERVICE is null"); + } + } + if (dbLibService == null) { + try { + dbLibService = new DBResourceManager(System.getProperties()); + } catch (Exception e) { + log.error("Caught exception trying to create db service", e); + } + + if (dbLibService == null) { + log.warn("Could not create new DBResourceManager"); + } + } + return dbLibService; + } + + //By using String.valueOf on the array list items, we can store any null values as + //Strings with the value "null". This mirrors the way queries worked prior to the + //prepared statements. + private ArrayList valueOfArrayList(ArrayList original) { + ArrayList valueOfList = new ArrayList<>(); + for(String s : original) { + valueOfList.add(String.valueOf(s)); + } + return valueOfList; + } + +} -- cgit 1.2.3-korg