diff options
Diffstat (limited to 'sql-resource')
22 files changed, 1689 insertions, 0 deletions
diff --git a/sql-resource/.gitignore b/sql-resource/.gitignore new file mode 100755 index 000000000..b73caf31e --- /dev/null +++ b/sql-resource/.gitignore @@ -0,0 +1,34 @@ +#####standard .git ignore entries##### + +## IDE Specific Files ## +org.eclipse.core.resources.prefs +.classpath +.project +.settings +.idea +.externalToolBuilders +maven-eclipse.xml +workspace + +## Compilation Files ## +*.class +**/target +target +target-ide +MANIFEST.MF + +## Misc Ignores (OS specific etc) ## +bin/ +dist +*~ +*.ipr +*.iml +*.iws +classes +out/ +.DS_STORE +.metadata + +## Folders which contain auto generated source code ## +yang-gen-config +yang-gen-sal diff --git a/sql-resource/installer/pom.xml b/sql-resource/installer/pom.xml new file mode 100755 index 000000000..5ec885ce0 --- /dev/null +++ b/sql-resource/installer/pom.xml @@ -0,0 +1,128 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> + <modelVersion>4.0.0</modelVersion> + + <parent> + <groupId>org.onap.ccsdk.parent</groupId> + <artifactId>odlparent-lite</artifactId> + <version>2.1.0</version> + <relativePath/> + </parent> + + <groupId>org.onap.ccsdk.sli.adaptors</groupId> + <artifactId>sql-resource-installer</artifactId> + <version>1.1.1-SNAPSHOT</version> + <packaging>pom</packaging> + + <name>ccsdk-sli-adaptors :: sql-resource :: ${project.artifactId}</name> + + <properties> + <application.name>ccsdk-sql-resource</application.name> + <features.boot>${application.name}</features.boot> + <features.repositories>mvn:org.onap.ccsdk.sli.adaptors/${features.boot}/${project.version}/xml/features</features.repositories> + <include.transitive.dependencies>false</include.transitive.dependencies> + </properties> + + <dependencies> + + <dependency> + <groupId>org.onap.ccsdk.sli.adaptors</groupId> + <artifactId>sql-resource-provider</artifactId> + <version>${project.version}</version> + </dependency> + + </dependencies> + + <build> + <plugins> + <plugin> + <artifactId>maven-assembly-plugin</artifactId> + <version>2.6</version> + <executions> + <execution> + <id>maven-repo-zip</id> + <goals> + <goal>single</goal> + </goals> + <phase>package</phase> + <configuration> + <attach>true</attach> + <finalName>stage/${application.name}-${project.version}</finalName> + <descriptors> + <descriptor>src/assembly/assemble_mvnrepo_zip.xml</descriptor> + </descriptors> + <appendAssemblyId>true</appendAssemblyId> + </configuration> + </execution> + <execution> + <id>installer-zip</id> + <goals> + <goal>single</goal> + </goals> + <phase>package</phase> + <configuration> + <attach>true</attach> + <finalName>${application.name}-${project.version}-installer</finalName> + <descriptors> + <descriptor>src/assembly/assemble_installer_zip.xml</descriptor> + </descriptors> + <appendAssemblyId>false</appendAssemblyId> + </configuration> + </execution> + </executions> + </plugin> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-dependency-plugin</artifactId> + <executions> + <execution> + <id>copy-dependencies</id> + <goals> + <goal>copy-dependencies</goal> + </goals> + <phase>prepare-package</phase> + <configuration> + <transitive>false</transitive> + <outputDirectory>${project.build.directory}/assembly/system</outputDirectory> + <overWriteReleases>false</overWriteReleases> + <overWriteSnapshots>true</overWriteSnapshots> + <overWriteIfNewer>true</overWriteIfNewer> + <useRepositoryLayout>true</useRepositoryLayout> + <addParentPoms>false</addParentPoms> + <copyPom>false</copyPom> + <includeGroupIds>org.onap.ccsdk.sli.adaptor</includeGroupIds> + <scope>provided</scope> + </configuration> + </execution> + </executions> + </plugin> + <plugin> + <artifactId>maven-resources-plugin</artifactId> + <version>2.6</version> + <executions> + <execution> + <id>copy-version</id> + <goals> + <goal>copy-resources</goal> + </goals><!-- here the phase you need --> + <phase>validate</phase> + <configuration> + <outputDirectory>${basedir}/target/stage</outputDirectory> + <resources> + <resource> + <directory>src/main/resources/scripts</directory> + <includes> + <include>install-feature.sh</include> + </includes> + <filtering>true</filtering> + </resource> + </resources> + </configuration> + </execution> + + </executions> + </plugin> + + </plugins> + </build> +</project> diff --git a/sql-resource/installer/src/assembly/assemble_installer_zip.xml b/sql-resource/installer/src/assembly/assemble_installer_zip.xml new file mode 100644 index 000000000..3bed4b5ef --- /dev/null +++ b/sql-resource/installer/src/assembly/assemble_installer_zip.xml @@ -0,0 +1,59 @@ +<!-- + ============LICENSE_START======================================================= + openECOMP : SDN-C + ================================================================================ + Copyright (C) 2017 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========================================================= + --> + +<!-- Defines how we build the .zip file which is our distribution. --> + +<assembly + xmlns="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.0" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.0 http://maven.apache.org/xsd/assembly-1.1.0.xsd"> + <id>installer_zip</id> + <formats> + <format>zip</format> + </formats> + + <!-- we want "system" and related files right at the root level + as this file is suppose to be unzip on top of a karaf + distro. --> + <includeBaseDirectory>false</includeBaseDirectory> + + <fileSets> + <fileSet> + <directory>target/stage/</directory> + <outputDirectory>${application.name}</outputDirectory> + <fileMode>755</fileMode> + <includes> + <include>*.sh</include> + </includes> + </fileSet> + <fileSet> + <directory>target/stage/</directory> + <outputDirectory>${application.name}</outputDirectory> + <fileMode>644</fileMode> + <excludes> + <exclude>*.sh</exclude> + </excludes> + </fileSet> + </fileSets> + + + +</assembly> diff --git a/sql-resource/installer/src/assembly/assemble_mvnrepo_zip.xml b/sql-resource/installer/src/assembly/assemble_mvnrepo_zip.xml new file mode 100644 index 000000000..35e79db18 --- /dev/null +++ b/sql-resource/installer/src/assembly/assemble_mvnrepo_zip.xml @@ -0,0 +1,49 @@ +<!-- + ============LICENSE_START======================================================= + openECOMP : SDN-C + ================================================================================ + Copyright (C) 2017 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========================================================= + --> + +<!-- Defines how we build the .zip file which is our distribution. --> + +<assembly + xmlns="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.0" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.0 http://maven.apache.org/xsd/assembly-1.1.0.xsd"> + <id>repo</id> + <formats> + <format>zip</format> + </formats> + + <!-- we want "system" and related files right at the root level + as this file is suppose to be unzip on top of a karaf + distro. --> + <includeBaseDirectory>false</includeBaseDirectory> + + <fileSets> + <fileSet> + <directory>target/assembly/</directory> + <outputDirectory>.</outputDirectory> + <excludes> + </excludes> + </fileSet> + </fileSets> + + + +</assembly> diff --git a/sql-resource/installer/src/main/resources/scripts/install-feature.sh b/sql-resource/installer/src/main/resources/scripts/install-feature.sh new file mode 100644 index 000000000..cee4a4952 --- /dev/null +++ b/sql-resource/installer/src/main/resources/scripts/install-feature.sh @@ -0,0 +1,39 @@ +#!/bin/bash + +### +# ============LICENSE_START======================================================= +# openECOMP : SDN-C +# ================================================================================ +# Copyright (C) 2017 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========================================================= +### + +ODL_HOME=${ODL_HOME:-/opt/opendaylight/current} +ODL_KARAF_CLIENT=${ODL_KARAF_CLIENT:-${ODL_HOME}/bin/client} +INSTALLERDIR=$(dirname $0) + +REPOZIP=${INSTALLERDIR}/${features.boot}-${project.version}.zip + +if [ -f ${REPOZIP} ] +then + unzip -d ${ODL_HOME} ${REPOZIP} +else + echo "ERROR : repo zip ($REPOZIP) not found" + exit 1 +fi + +${ODL_KARAF_CLIENT} feature:repo-add ${features.repositories} +${ODL_KARAF_CLIENT} feature:install ${features.boot} diff --git a/sql-resource/pom.xml b/sql-resource/pom.xml new file mode 100755 index 000000000..5cbee1089 --- /dev/null +++ b/sql-resource/pom.xml @@ -0,0 +1,24 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> + <modelVersion>4.0.0</modelVersion> + + <parent> + <groupId>org.onap.ccsdk.parent</groupId> + <artifactId>odlparent-lite</artifactId> + <version>2.1.0</version> + <relativePath/> + </parent> + + <groupId>org.onap.ccsdk.sli.adaptors</groupId> + <artifactId>sql-resource</artifactId> + <version>1.1.1-SNAPSHOT</version> + <packaging>pom</packaging> + + <name>ccsdk-sli-adaptors :: sql-resource</name> + <description>The Sql resource adaptor allows service logic to read/write data from a local database using direct SQL statements</description> + + <modules> + <module>provider</module> + <module>installer</module> + </modules> +</project> diff --git a/sql-resource/provider/pom.xml b/sql-resource/provider/pom.xml new file mode 100755 index 000000000..94b0225c9 --- /dev/null +++ b/sql-resource/provider/pom.xml @@ -0,0 +1,83 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> + <modelVersion>4.0.0</modelVersion> + + <parent> + <groupId>org.onap.ccsdk.parent</groupId> + <artifactId>binding-parent</artifactId> + <version>2.1.0</version> + <relativePath/> + </parent> + + <groupId>org.onap.ccsdk.sli.adaptors</groupId> + <artifactId>sql-resource-provider</artifactId> + <version>1.1.1-SNAPSHOT</version> + <packaging>bundle</packaging> + + <name>ccsdk-sli-adaptors :: sql-resource :: ${project.artifactId}</name> + <url>http://maven.apache.org</url> + + <properties> + <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> + <ccsdk.sli.adaptors.version>${project.version}</ccsdk.sli.adaptors.version> + </properties> + + <dependencyManagement> + <dependencies> + <dependency> + <groupId>org.onap.ccsdk.sli.core</groupId> + <artifactId>sli-core-artifacts</artifactId> + <version>${ccsdk.sli.core.version}</version> + <type>pom</type> + <scope>import</scope> + </dependency> + </dependencies> + </dependencyManagement> + <dependencies> + <dependency> + <groupId>junit</groupId> + <artifactId>junit</artifactId> + <scope>test</scope> + </dependency> + <dependency> + <groupId>ch.vorburger.mariaDB4j</groupId> + <artifactId>mariaDB4j</artifactId> + <version>2.2.3</version> + <scope>test</scope> + </dependency> + <dependency> + <groupId>org.onap.ccsdk.sli.core</groupId> + <artifactId>sli-common</artifactId> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.onap.ccsdk.sli.core</groupId> + <artifactId>sli-provider</artifactId> + <scope>provided</scope> + </dependency> + + <dependency> + <groupId>org.slf4j</groupId> + <artifactId>slf4j-api</artifactId> + </dependency> + <dependency> + <groupId>org.slf4j</groupId> + <artifactId>jcl-over-slf4j</artifactId> + </dependency> + <dependency> + <groupId>org.slf4j</groupId> + <artifactId>slf4j-simple</artifactId> + <scope>compile</scope> + </dependency> + <dependency> + <groupId>org.onap.ccsdk.sli.core</groupId> + <artifactId>dblib-provider</artifactId> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>org.osgi</groupId> + <artifactId>org.osgi.core</artifactId> + <scope>provided</scope> + </dependency> + </dependencies> +</project> diff --git a/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResource.java b/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResource.java new file mode 100755 index 000000000..435bc5bb1 --- /dev/null +++ b/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResource.java @@ -0,0 +1,551 @@ +/*- + * ============LICENSE_START======================================================= + * openECOMP : SDN-C + * ================================================================================ + * Copyright (C) 2017 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.ccsdk.sli.adaptors.resource.sql; + +import java.io.File; +import java.io.FileInputStream; +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 java.util.Map; +import java.util.Map.Entry; +import java.util.Properties; + +import javax.sql.rowset.CachedRowSet; + +import org.apache.commons.lang3.StringUtils; +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.SvcLogicException; +import org.onap.ccsdk.sli.core.sli.SvcLogicJavaPlugin; +import org.onap.ccsdk.sli.core.sli.SvcLogicResource; +import org.osgi.framework.Bundle; +import org.osgi.framework.BundleContext; +import org.osgi.framework.FrameworkUtil; +import org.osgi.framework.ServiceReference; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +public class SqlResource implements SvcLogicResource, SvcLogicJavaPlugin { + + private static final Logger LOG = LoggerFactory.getLogger(SqlResource.class); + + private static final String DBLIB_SERVICE = "org.onap.ccsdk.sli.core.dblib.DbLibService"; + + private static String CRYPT_KEY = "QtfJMKggVk"; + + DbLibService dblibSvc = null; + + public SqlResource() { + this(new SqlResourcePropertiesProviderImpl(), null); + } + + public SqlResource(SqlResourcePropertiesProvider propProvider) { + this(propProvider, null); + } + + public SqlResource(SqlResourcePropertiesProvider propProvider, DbLibService dblibSvc) { + + this.dblibSvc = dblibSvc; + + Properties properties = propProvider.getProperties(); + + String cryptKey = properties.getProperty("org.onap.sdnc.resource.sql.cryptkey"); + + if ((cryptKey == null) || (cryptKey.length() == 0)) { + cryptKey = properties.getProperty("org.openecomp.sdnc.resource.sql.cryptkey"); + } + + SqlResource.setCryptKey(cryptKey); + } + + // For sql-resource, is-available is the same as exists + @Override + public QueryStatus isAvailable(String resource, String key, String prefix, SvcLogicContext ctx) + throws SvcLogicException { + + return (exists(resource, key, prefix, ctx)); + + } + + @Override + public QueryStatus exists(String resource, String key, String prefix, SvcLogicContext ctx) + throws SvcLogicException { + + DbLibService dblibSvc = getDbLibService(); + if (dblibSvc == null) { + return (QueryStatus.FAILURE); + } + + String theStmt = resolveCtxVars(key, ctx, resource); + + try { + CachedRowSet results = dblibSvc.getData(theStmt, null, null); + + if (!results.next()) { + return (QueryStatus.NOT_FOUND); + } + + int numRows = results.getInt(1); + + if (numRows > 0) { + return (QueryStatus.SUCCESS); + } else { + return (QueryStatus.NOT_FOUND); + } + } catch (Exception e) { + LOG.error("Caught SQL exception", e); + return (QueryStatus.FAILURE); + } + } + + // @Override + public QueryStatus query(String resource, boolean localOnly, String select, String key, String prefix, + String orderBy, SvcLogicContext ctx) throws SvcLogicException { + + DbLibService dblibSvc = getDbLibService(); + + if (dblibSvc == null) { + return (QueryStatus.FAILURE); + } + + String sqlQuery = resolveCtxVars(key, ctx, resource); + + + try { + + CachedRowSet results = dblibSvc.getData(sqlQuery, null, null); + + QueryStatus retval = QueryStatus.SUCCESS; + + if (!results.next()) { + retval = QueryStatus.NOT_FOUND; + LOG.debug("No data found"); + } else { + saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc); + } + return (retval); + } catch (Exception e) { + LOG.error("Caught SQL exception", e); + return (QueryStatus.FAILURE); + } + } + + public 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); + } + } + } + } + } + + // reserve is no-op + @Override + public QueryStatus reserve(String resource, String select, String key, String prefix, SvcLogicContext ctx) + throws SvcLogicException { + return (QueryStatus.SUCCESS); + } + + // release is no-op + @Override + public QueryStatus release(String resource, String key, SvcLogicContext ctx) throws SvcLogicException { + return (QueryStatus.SUCCESS); + } + + private QueryStatus executeSqlWrite(String key, SvcLogicContext ctx) throws SvcLogicException { + QueryStatus retval = QueryStatus.SUCCESS; + + DbLibService dblibSvc = getDbLibService(); + + if (dblibSvc == null) { + return (QueryStatus.FAILURE); + } + + String sqlStmt = resolveCtxVars(key, ctx, ""); + + LOG.debug("key = [" + key + "]; sqlStmt = [" + sqlStmt + "]"); + try { + + if (!dblibSvc.writeData(sqlStmt, null, null)) { + retval = QueryStatus.FAILURE; + } + } catch (Exception e) { + LOG.error("Caught SQL exception", e); + retval = QueryStatus.FAILURE; + } + + return (retval); + + } + + private String resolveCtxVars(String key, SvcLogicContext ctx, String resource) { + 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 + "]"); + } + + //"SQL-TRUE" allows for the key to be used as is. + if (!resource.equals("SQL-TRUE")) { + String[] keyTerms = key.split("\\s+"); + + StringBuffer sqlBuffer = new StringBuffer(); + + for (int i = 0; i < keyTerms.length; i++) { + sqlBuffer.append(resolveTerm(keyTerms[i], ctx)); + sqlBuffer.append(" "); + } + key = sqlBuffer.toString(); + } + + return (key); + } + + private String resolveTerm(String term, SvcLogicContext ctx) { + 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("'", "''"); + } + return ("'" + term + "'"); + } else { + return (term); + } + + } + + private 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())); + } + + @Override + public QueryStatus save(String resource, boolean force, boolean localOnly, String key, Map<String, String> parms, + String prefix, SvcLogicContext ctx) throws SvcLogicException { + return (executeSqlWrite(key, ctx)); + } + + private DbLibService getDbLibService() { + + if (dblibSvc != null) { + return(dblibSvc); + } + // Try to get dblib as an OSGI service + + try { + BundleContext bctx = null; + ServiceReference sref = null; + + + + Bundle bundle = FrameworkUtil.getBundle(SqlResource.class); + + if (bundle != null) { + bctx = bundle.getBundleContext(); + } + + if (bctx != null) { + sref = bctx.getServiceReference(DBLIB_SERVICE); + } + + if (sref == null) { + LOG.warn("Could not find service reference for DBLIB service (" + DBLIB_SERVICE + ")"); + } else { + dblibSvc = (DbLibService) bctx.getService(sref); + if (dblibSvc == null) { + LOG.warn("Could not find service reference for DBLIB service (" + DBLIB_SERVICE + ")"); + } + } + } catch (NoClassDefFoundError ex) { + LOG.info("OSGI classes not found - must be running outside an OSGi container"); + } + + + if (dblibSvc == null) { + // Must not be running in an OSGI container. See if you can load it + // as a + // a POJO then. + + // If $SDNC_CONFIG_DIR/dblib.properties exists, that should + // be the properties passed to DBResourceManager constructor. + // If not, as default just use system properties. + Properties dblibProps = System.getProperties(); + + String cfgDir = dblibProps.getProperty("sdnc.config.dir", System.getenv("SDNC_CONFIG_DIR")); + + if ((cfgDir == null) || (cfgDir.length() == 0)) { + cfgDir = "/opt/sdnc/data/properties"; + } + + File dblibPropFile = new File(cfgDir + "/dblib.properties"); + if (dblibPropFile.exists()) { + try { + LOG.debug("Loading dblib properties from {}", dblibPropFile.getAbsolutePath()); + dblibProps = new Properties(); + dblibProps.load(new FileInputStream(dblibPropFile)); + } catch (Exception e) { + LOG.warn("Could not load properties file {}", dblibPropFile.getAbsolutePath(), e); + + dblibProps = System.getProperties(); + } + } + + try { + dblibSvc = new DBResourceManager(dblibProps); + } catch (Exception e) { + LOG.error("Caught exception trying to create dblib service", e); + } + + if (dblibSvc == null) { + LOG.warn("Could not create new DBResourceManager"); + } + } + + return (dblibSvc); + } + + @Override + public QueryStatus notify(String resource, String action, String key, SvcLogicContext ctx) + throws SvcLogicException { + if (LOG.isDebugEnabled()) { + LOG.debug("SqlResource.notify called with resource=" + resource + ", action=" + action); + } + return QueryStatus.SUCCESS; + } + + @Override + public QueryStatus delete(String resource, String key, SvcLogicContext ctx) throws SvcLogicException { + return (executeSqlWrite(key, ctx)); + } + + public QueryStatus update(String resource, String key, Map<String, String> parms, String prefix, + SvcLogicContext ctx) throws SvcLogicException { + return (executeSqlWrite(key, ctx)); + } + + private 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, getCryptKey()); + 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); + } + + public static String getCryptKey() { + return (CRYPT_KEY); + } + + public static String setCryptKey(String key) { + CRYPT_KEY = key; + return (CRYPT_KEY); + } + + public String parameterizedQuery(Map<String, String> parameters, SvcLogicContext ctx) throws SvcLogicException { + DbLibService dblibSvc = getDbLibService(); + String prefix = parameters.get("prefix"); + String query = parameters.get("query"); + + ArrayList<String> arguments = new ArrayList<String>(); + for (Entry<String, String> a : parameters.entrySet()) { + if (a.getKey().startsWith("param")) { + arguments.add(a.getValue()); + } + } + + try { + if (dblibSvc == null) { + return mapQueryStatus(QueryStatus.FAILURE); + } + if (query.contains("count") || query.contains("COUNT")) { + CachedRowSet results = dblibSvc.getData(query, arguments, null); + + if (!results.next()) { + return mapQueryStatus(QueryStatus.FAILURE); + } + + int numRows = results.getInt(1); + ctx.setAttribute(prefix + ".count", String.valueOf(numRows)); + if (numRows > 0) { + return "true"; + } else { + return "false"; + } + } else if (query.startsWith("select") || query.startsWith("SELECT")) { + CachedRowSet results = dblibSvc.getData(query, arguments, null); + if (!results.next()) { + return mapQueryStatus(QueryStatus.NOT_FOUND); + } else { + saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc); + } + } else { + if (!dblibSvc.writeData(query, arguments, null)) { + return mapQueryStatus(QueryStatus.FAILURE); + } + } + return mapQueryStatus(QueryStatus.SUCCESS); + } catch (SQLException e) { + LOG.error("Caught SQL exception", e); + return mapQueryStatus(QueryStatus.FAILURE); + } + } + + protected String mapQueryStatus(QueryStatus status) { + String str = status.toString(); + str = str.toLowerCase(); + str = str.replaceAll("_", "-"); + return str; + } +} diff --git a/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourcePropertiesProvider.java b/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourcePropertiesProvider.java new file mode 100755 index 000000000..7970151da --- /dev/null +++ b/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourcePropertiesProvider.java @@ -0,0 +1,28 @@ +/*- + * ============LICENSE_START======================================================= + * onap + * ================================================================================ + * Copyright (C) 2016 - 2017 ONAP + * ================================================================================ + * 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.ccsdk.sli.adaptors.resource.sql; + +import java.util.Properties; + +public interface SqlResourcePropertiesProvider { + + public Properties getProperties(); +} diff --git a/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourcePropertiesProviderImpl.java b/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourcePropertiesProviderImpl.java new file mode 100755 index 000000000..7c9f6f161 --- /dev/null +++ b/sql-resource/provider/src/main/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourcePropertiesProviderImpl.java @@ -0,0 +1,186 @@ +/*- + * ============LICENSE_START======================================================= + * onap + * ================================================================================ + * Copyright (C) 2016 - 2017 ONAP + * ================================================================================ + * 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.ccsdk.sli.adaptors.resource.sql; + +import java.io.File; +import java.io.FileInputStream; +import java.io.IOException; +import java.io.InputStream; +import java.util.Optional; +import java.util.Properties; +import java.util.Vector; + +import org.onap.ccsdk.sli.core.sli.ConfigurationException; +import org.onap.ccsdk.sli.core.utils.JREFileResolver; +import org.onap.ccsdk.sli.core.utils.KarafRootFileResolver; +import org.onap.ccsdk.sli.core.utils.PropertiesFileResolver; +import org.onap.ccsdk.sli.core.utils.common.CoreDefaultFileResolver; +import org.onap.ccsdk.sli.core.utils.common.SdncConfigEnvVarFileResolver; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +/** + * Responsible for determining the properties file to use and instantiating the + * <code>SqlResource</code> Service. The priority for properties file + * resolution is as follows: + * + * <ol> + * <li>A directory identified by the system environment variable + * <code>SDNC_CONFIG_DIR</code></li> + * <li>The default directory <code>DEFAULT_DBLIB_PROP_DIR</code></li> + * <li>A directory identified by the JRE argument + * <code>sql-resource.properties</code></li> + * <li>A <code>sql-resource.properties</code> file located in the karaf root + * directory</li> + * </ol> + */ +public class SqlResourcePropertiesProviderImpl implements SqlResourcePropertiesProvider { + + private static final Logger LOG = LoggerFactory.getLogger(SqlResourcePropertiesProviderImpl.class); + + /** + * The name of the properties file for database configuration + */ + private static final String SQLRESOURCE_PROP_FILE_NAME = "sql-resource.properties"; + + /** + * A prioritized list of strategies for resolving sql-resource properties files. + */ + private Vector<PropertiesFileResolver> sqlResourcePropertiesFileResolvers = new Vector<>(); + + /** + * The configuration properties for the db connection. + */ + private Properties properties; + + /** + * Set up the prioritized list of strategies for resolving dblib properties + * files. + */ + public SqlResourcePropertiesProviderImpl() { + sqlResourcePropertiesFileResolvers + .add(new SdncConfigEnvVarFileResolver("Using property file (1) from environment variable")); + sqlResourcePropertiesFileResolvers.add(new CoreDefaultFileResolver("Using property file (2) from default directory")); + + sqlResourcePropertiesFileResolvers.add( + new JREFileResolver("Using property file (3) from JRE argument", SqlResourcePropertiesProviderImpl.class)); + sqlResourcePropertiesFileResolvers.add(new KarafRootFileResolver("Using property file (4) from karaf root", this)); + + // determines properties file as according to the priority described in the + // class header comment + final File propertiesFile = determinePropertiesFile(this); + if (propertiesFile != null) { + try (FileInputStream fileInputStream = new FileInputStream(propertiesFile)) { + properties = new Properties(); + properties.load(fileInputStream); + } catch (final IOException e) { + LOG.error("Failed to load properties for file: {}", propertiesFile.toString(), + new ConfigurationException("Failed to load properties for file: " + propertiesFile.toString(), + e)); + } + } else { + // Try to read properties as resource + + InputStream propStr = getClass().getResourceAsStream("/" + SQLRESOURCE_PROP_FILE_NAME); + if (propStr != null) { + properties = new Properties(); + try { + properties.load(propStr); + propStr.close(); + } catch (IOException e) { + properties = null; + } + } + + } + + if (properties == null) { + reportFailure("Missing configuration properties resource(3)", new ConfigurationException( + "Missing configuration properties resource(3): " + SQLRESOURCE_PROP_FILE_NAME)); + } + } + + /** + * Extract svclogic config properties. + * + * @return the svclogic config properties + */ + public Properties getProperties() { + return properties; + } + + /** + * Reports the method chosen for properties resolution to the + * <code>Logger</code>. + * + * @param message + * Some user friendly message + * @param fileOptional + * The file location of the chosen properties file + * @return the file location of the chosen properties file + */ + private static File reportSuccess(final String message, final Optional<File> fileOptional) { + if (fileOptional.isPresent()) { + final File file = fileOptional.get(); + LOG.info("{} {}", message, file.getPath()); + return file; + } + return null; + } + + /** + * Reports fatal errors. This is the case in which no properties file could be + * found. + * + * @param message + * An appropriate fatal error message + * @param configurationException + * An exception describing what went wrong during resolution + */ + private static void reportFailure(final String message, final ConfigurationException configurationException) { + + LOG.error("{}", message, configurationException); + } + + /** + * Determines the sql-resource properties file to use based on the following priority: + * <ol> + * <li>A directory identified by the system environment variable + * <code>SDNC_CONFIG_DIR</code></li> + * <li>The default directory <code>DEFAULT_DBLIB_PROP_DIR</code></li> + * <li>A directory identified by the JRE argument + * <code>sql-resource.properties</code></li> + * <li>A <code>sql-resource.properties</code> file located in the karaf root + * directory</li> + * </ol> + */ + File determinePropertiesFile(final SqlResourcePropertiesProviderImpl resourceProvider) { + + for (final PropertiesFileResolver sliPropertiesFileResolver : sqlResourcePropertiesFileResolvers) { + final Optional<File> fileOptional = sliPropertiesFileResolver.resolveFile(SQLRESOURCE_PROP_FILE_NAME); + if (fileOptional.isPresent()) { + return reportSuccess(sliPropertiesFileResolver.getSuccessfulResolutionMessage(), fileOptional); + } + } + + return null; + } +} diff --git a/sql-resource/provider/src/main/resources/OSGI-INF/blueprint/sql-resource-blueprint.xml b/sql-resource/provider/src/main/resources/OSGI-INF/blueprint/sql-resource-blueprint.xml new file mode 100755 index 000000000..5e8d33946 --- /dev/null +++ b/sql-resource/provider/src/main/resources/OSGI-INF/blueprint/sql-resource-blueprint.xml @@ -0,0 +1,41 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ============LICENSE_START======================================================= + openECOMP : SDN-C + ================================================================================ + Copyright (C) 2017 - 2018 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========================================================= + --> + +<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0" + xmlns:odl="http://opendaylight.org/xmlns/blueprint/v1.0.0" + odl:use-default-for-reference-types="true"> + + <bean id="propProvider" class="org.onap.ccsdk.sli.adaptors.resource.sql.SqlResourcePropertiesProviderImpl" /> + <reference id="dblibService" interface="org.onap.ccsdk.sli.core.dblib.DbLibService" /> + + <bean id="sqlResourceInstance" class="org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource"> + <argument ref="propProvider"/> + <argument ref="dblibService"/> + </bean> + + <service ref="sqlResourceInstance"> + <interfaces> + <value>org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource</value> + </interfaces> + </service> + +</blueprint> diff --git a/sql-resource/provider/src/main/resources/org/opendaylight/blueprint/sql-resource-blueprint.xml b/sql-resource/provider/src/main/resources/org/opendaylight/blueprint/sql-resource-blueprint.xml new file mode 100755 index 000000000..5e8d33946 --- /dev/null +++ b/sql-resource/provider/src/main/resources/org/opendaylight/blueprint/sql-resource-blueprint.xml @@ -0,0 +1,41 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + ============LICENSE_START======================================================= + openECOMP : SDN-C + ================================================================================ + Copyright (C) 2017 - 2018 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========================================================= + --> + +<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0" + xmlns:odl="http://opendaylight.org/xmlns/blueprint/v1.0.0" + odl:use-default-for-reference-types="true"> + + <bean id="propProvider" class="org.onap.ccsdk.sli.adaptors.resource.sql.SqlResourcePropertiesProviderImpl" /> + <reference id="dblibService" interface="org.onap.ccsdk.sli.core.dblib.DbLibService" /> + + <bean id="sqlResourceInstance" class="org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource"> + <argument ref="propProvider"/> + <argument ref="dblibService"/> + </bean> + + <service ref="sqlResourceInstance"> + <interfaces> + <value>org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource</value> + </interfaces> + </service> + +</blueprint> diff --git a/sql-resource/provider/src/main/resources/sql-resource.properties b/sql-resource/provider/src/main/resources/sql-resource.properties new file mode 100755 index 000000000..ac32f7734 --- /dev/null +++ b/sql-resource/provider/src/main/resources/sql-resource.properties @@ -0,0 +1,23 @@ +### +# ============LICENSE_START======================================================= +# openECOMP : SDN-C +# ================================================================================ +# Copyright (C) 2017 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========================================================= +### + +org.openecomp.sdnc.resource.sql.cryptkey=QtfJMKggVk + diff --git a/sql-resource/provider/src/main/resources/svclogic.properties b/sql-resource/provider/src/main/resources/svclogic.properties new file mode 100644 index 000000000..18718b869 --- /dev/null +++ b/sql-resource/provider/src/main/resources/svclogic.properties @@ -0,0 +1,34 @@ +### +# ============LICENSE_START======================================================= +# openECOMP : SDN-C +# ================================================================================ +# Copyright (C) 2017 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========================================================= +### + +org.onap.ccsdk.sli.adaptors.dbtype=jdbc +org.onap.ccsdk.sli.adaptors.jdbc.hosts=sdnctldb01,sdnctldb02 +org.onap.ccsdk.sli.adaptors.jdbc.url=jdbc:mysql://DBHOST:3306/sdnctl +org.onap.ccsdk.sli.adaptors.jdbc.database=sdnctl +org.onap.ccsdk.sli.adaptors.jdbc.user=sdnctl +org.onap.ccsdk.sli.adaptors.jdbc.password=gamma +org.onap.ccsdk.sli.adaptors.jdbc.connection.name=sdnctldb01 + +org.onap.ccsdk.sli.adaptors.jdbc.connection.timeout=50 +org.onap.ccsdk.sli.adaptors.jdbc.request.timeout=100 +org.onap.ccsdk.sli.adaptors.jdbc.limit.init=10 +org.onap.ccsdk.sli.adaptors.jdbc.limit.min=10 +org.onap.ccsdk.sli.adaptors.jdbc.limit.max=20 diff --git a/sql-resource/provider/src/test/java/org/onap/ccsdk/sli/adaptors/resource/sql/ITCaseSqlResource.java b/sql-resource/provider/src/test/java/org/onap/ccsdk/sli/adaptors/resource/sql/ITCaseSqlResource.java new file mode 100755 index 000000000..2863c6c96 --- /dev/null +++ b/sql-resource/provider/src/test/java/org/onap/ccsdk/sli/adaptors/resource/sql/ITCaseSqlResource.java @@ -0,0 +1,231 @@ +/*- + * ============LICENSE_START======================================================= + * openECOMP : SDN-C + * ================================================================================ + * Copyright (C) 2017 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.ccsdk.sli.adaptors.resource.sql; + +import java.io.BufferedReader; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.util.Enumeration; +import java.util.Properties; + +import org.onap.ccsdk.sli.core.sli.SvcLogicContext; +import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +import ch.vorburger.mariadb4j.DB; +import ch.vorburger.mariadb4j.DBConfigurationBuilder; +import junit.framework.TestCase; + +public class ITCaseSqlResource extends TestCase { + + private static final Logger LOG = LoggerFactory + .getLogger(ITCaseSqlResource.class); + + + public void testExists() throws Exception { + + + Properties props = new Properties(); + InputStream propStr = getClass().getResourceAsStream("/svclogic.properties"); + if (propStr == null) { + fail("src/test/resources/svclogic.properties missing"); + } + + try { + props.load(propStr); + propStr.close(); + } catch (Exception e) { + e.printStackTrace(); + fail("Could not initialize properties"); + } + + + // Start MariaDB4j database + DBConfigurationBuilder config = DBConfigurationBuilder.newBuilder(); + config.setPort(0); // 0 => autom. detect free port + DB db = DB.newEmbeddedDB(config.build()); + db.start(); + + // Override jdbc URL and database name + props.setProperty("org.onap.ccsdk.sli.jdbc.database", "test"); + props.setProperty("org.onap.ccsdk.sli.jdbc.url", config.getURL("test")); + + // Add properties to global properties + + Enumeration propNames = props.keys(); + + while (propNames.hasMoreElements()) { + String propName = (String) propNames.nextElement(); + + System.setProperty(propName, props.getProperty(propName)); + } + + SqlResource sqlResource = new SqlResource(new SqlResourcePropertiesProviderImpl()); + + + + InputStream testStr = getClass().getResourceAsStream("/save.tests"); + BufferedReader testsReader = new BufferedReader(new InputStreamReader(testStr)); + SvcLogicContext ctx = new SvcLogicContext(); + + try { + String testExpr = null; + + int testNum = 0; + while ((testExpr = testsReader.readLine()) != null) { + testExpr = testExpr.trim(); + + if (testExpr.startsWith("#")) { + testExpr = testExpr.substring(1).trim(); + String[] nameValue = testExpr.split("="); + String name = nameValue[0].trim(); + String value = nameValue[1].trim(); + + LOG.info("Setting context attribute " + name + " = " + + value); + ctx.setAttribute(name, value); + + } else { + + testNum++; + String sqlStmt = testExpr; + QueryStatus status = sqlResource.save("SQL", true, false, sqlStmt, null, "savetest"+testNum, ctx); + + switch (status) { + case SUCCESS: + LOG.info("Found data for query [" + sqlStmt + "]"); + break; + case NOT_FOUND: + LOG.info("Did not data for query [" + sqlStmt + "]"); + break; + default: + fail("Failure executing query [" + sqlStmt + "]"); + + } + } + } + + } catch (Exception e) { + e.printStackTrace(); + fail("Caught exception running tests"); + } + + + testStr = getClass().getResourceAsStream("/query.tests"); + testsReader = new BufferedReader(new InputStreamReader(testStr)); + + try { + String testExpr = null; + + int testNum = 0; + while ((testExpr = testsReader.readLine()) != null) { + testExpr = testExpr.trim(); + if (testExpr.startsWith("#")) { + testExpr = testExpr.substring(1).trim(); + String[] nameValue = testExpr.split("="); + String name = nameValue[0].trim(); + String value = nameValue[1].trim(); + + LOG.info("Setting context attribute " + name + " = " + + value); + ctx.setAttribute(name, value); + + } else { + + testNum++; + + String sqlStmt = testExpr; + QueryStatus status = sqlResource.query("SQL", false, null, + sqlStmt, "querytest" + testNum, null, ctx); + + switch (status) { + case SUCCESS: + LOG.info("Found data for query [" + sqlStmt + "]"); + break; + case NOT_FOUND: + LOG.info("Did not data for query [" + sqlStmt + "]"); + break; + default: + fail("Failure executing query [" + sqlStmt + "]"); + + } + } + } + + } catch (Exception e) { + e.printStackTrace(); + fail("Caught exception running tests"); + } + + + testStr = getClass().getResourceAsStream("/delete.tests"); + testsReader = new BufferedReader(new InputStreamReader(testStr)); + + try { + String testExpr = null; + + int testNum = 0; + while ((testExpr = testsReader.readLine()) != null) { + testExpr = testExpr.trim(); + if (testExpr.startsWith("#")) { + testExpr = testExpr.substring(1).trim(); + String[] nameValue = testExpr.split("="); + String name = nameValue[0].trim(); + String value = nameValue[1].trim(); + + LOG.info("Setting context attribute " + name + " = " + + value); + ctx.setAttribute(name, value); + + } else { + + testNum++; + + String sqlStmt = testExpr; + QueryStatus status = sqlResource.delete("SQL", sqlStmt, ctx); + + switch (status) { + case SUCCESS: + LOG.info("Found data for query [" + sqlStmt + "]"); + break; + case NOT_FOUND: + LOG.info("Did not data for query [" + sqlStmt + "]"); + break; + default: + fail("Failure executing query [" + sqlStmt + "]"); + + } + } + } + + } catch (Exception e) { + e.printStackTrace(); + fail("Caught exception running tests"); + } + + for (String attrName : ctx.getAttributeKeySet()) { + LOG.info("ctx.getAttribute("+attrName+") = "+ctx.getAttribute(attrName)); + } + } + +} diff --git a/sql-resource/provider/src/test/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourceProviderTest.java b/sql-resource/provider/src/test/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourceProviderTest.java new file mode 100755 index 000000000..b261b9f7d --- /dev/null +++ b/sql-resource/provider/src/test/java/org/onap/ccsdk/sli/adaptors/resource/sql/SqlResourceProviderTest.java @@ -0,0 +1,38 @@ +package org.onap.ccsdk.sli.adaptors.resource.sql; + +import static org.junit.Assert.assertNotNull; +import java.lang.reflect.Field; +import java.util.Map; +import java.util.Properties; + +import org.junit.Test; + +public class SqlResourceProviderTest { + + private static SqlResourcePropertiesProvider provider; + private static final String SDNC_CONFIG_DIR = "SDNC_CONFIG_DIR"; + + @Test + public void testSqlResourceProvider() { + try{ + Map<String, String> env = System.getenv(); + Class<?> cl = env.getClass(); + Field field = cl.getDeclaredField("m"); + field.setAccessible(true); + Map<String, String> writableEnv = (Map<String, String>) field.get(env); + writableEnv.put(SDNC_CONFIG_DIR, "./src/test/resources"); + } catch (Exception e) { + throw new IllegalStateException("Failed to set environment variable", e); + } + + provider = new SqlResourcePropertiesProviderImpl(); + assertNotNull(provider); + } + + @Test + public void testGetProperties() { + Properties properties = provider.getProperties(); + assertNotNull(properties); + } + +} diff --git a/sql-resource/provider/src/test/resources/delete.tests b/sql-resource/provider/src/test/resources/delete.tests new file mode 100755 index 000000000..a60a3bc73 --- /dev/null +++ b/sql-resource/provider/src/test/resources/delete.tests @@ -0,0 +1,2 @@ +DROP TABLE SQLRESOURCE_ORDER; +DROP TABLE SQLRESOURCE_ORDER_ITEM; diff --git a/sql-resource/provider/src/test/resources/query.tests b/sql-resource/provider/src/test/resources/query.tests new file mode 100755 index 000000000..ec05c22e3 --- /dev/null +++ b/sql-resource/provider/src/test/resources/query.tests @@ -0,0 +1,5 @@ +# max-price = 100 +SELECT * FROM SQLRESOURCE_ORDER where placed_on < now(); +SELECT SQLRESOURCE_ORDER.order_number, clli, service, price FROM SQLRESOURCE_ORDER, SQLRESOURCE_ORDER_ITEM where SQLRESOURCE_ORDER.order_number = SQLRESOURCE_ORDER_ITEM.order_number and price > $max-price ; +SELECT SQLRESOURCE_ORDER.order_number, clli, service, price FROM SQLRESOURCE_ORDER, SQLRESOURCE_ORDER_ITEM where SQLRESOURCE_ORDER.order_number = SQLRESOURCE_ORDER_ITEM.order_number and price < $max-price ; +SELECT SQLRESOURCE_ORDER.order_number AS ordernum, clli, service, price FROM SQLRESOURCE_ORDER, SQLRESOURCE_ORDER_ITEM where SQLRESOURCE_ORDER.order_number = SQLRESOURCE_ORDER_ITEM.order_number and price < $max-price ; diff --git a/sql-resource/provider/src/test/resources/save.tests b/sql-resource/provider/src/test/resources/save.tests new file mode 100755 index 000000000..e2c42217d --- /dev/null +++ b/sql-resource/provider/src/test/resources/save.tests @@ -0,0 +1,13 @@ +# order-number = 1234 +# zero = 0 +# item[0].clli = MTJNJA14 +# item[0].service = NoD +# item[0].price = 1000000 +# item[1].clli = MTJNJA14 +# item[1].service = Pizza +# item[1].price = 10 +CREATE TABLE IF NOT EXISTS SQLRESOURCE_ORDER (order_number VARCHAR(40), placed_on TIMESTAMP); +CREATE TABLE IF NOT EXISTS SQLRESOURCE_ORDER_ITEM (order_number VARCHAR(40), clli VARCHAR(40), service VARCHAR(40), price INTEGER(4)); +INSERT INTO SQLRESOURCE_ORDER VALUES ( $order-number , now()); +INSERT INTO SQLRESOURCE_ORDER_ITEM VALUES( $order-number , $item[$zero].clli , $item[0].service , $item[0].price ); +INSERT INTO SQLRESOURCE_ORDER_ITEM VALUES( $order-number , $item[1].clli , $item[1].service , $item[1].price ); diff --git a/sql-resource/provider/src/test/resources/simplelogger.properties b/sql-resource/provider/src/test/resources/simplelogger.properties new file mode 100644 index 000000000..61d82660f --- /dev/null +++ b/sql-resource/provider/src/test/resources/simplelogger.properties @@ -0,0 +1,22 @@ +### +# ============LICENSE_START======================================================= +# openECOMP : SDN-C +# ================================================================================ +# Copyright (C) 2017 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========================================================= +### + +org.slf4j.simpleLogger.defaultLogLevel=debug diff --git a/sql-resource/provider/src/test/resources/sql-resource.properties b/sql-resource/provider/src/test/resources/sql-resource.properties new file mode 100755 index 000000000..ac32f7734 --- /dev/null +++ b/sql-resource/provider/src/test/resources/sql-resource.properties @@ -0,0 +1,23 @@ +### +# ============LICENSE_START======================================================= +# openECOMP : SDN-C +# ================================================================================ +# Copyright (C) 2017 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========================================================= +### + +org.openecomp.sdnc.resource.sql.cryptkey=QtfJMKggVk + diff --git a/sql-resource/provider/src/test/resources/svclogic.properties b/sql-resource/provider/src/test/resources/svclogic.properties new file mode 100644 index 000000000..6504e1aec --- /dev/null +++ b/sql-resource/provider/src/test/resources/svclogic.properties @@ -0,0 +1,35 @@ +### +# ============LICENSE_START======================================================= +# openECOMP : SDN-C +# ================================================================================ +# Copyright (C) 2017 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========================================================= +### +org.onap.ccsdk.sli.dbtype=jdbc + +org.onap.ccsdk.sli.jdbc.hosts=sdnctldb01 +org.onap.ccsdk.sli.jdbc.url=jdbc:mysql://dbhost:3306/sdnctl +org.onap.ccsdk.sli.jdbc.driver=org.mariadb.jdbc.Driver +org.onap.ccsdk.sli.jdbc.database=sdnctl +org.onap.ccsdk.sli.jdbc.user=sdnctl +org.onap.ccsdk.sli.jdbc.password=gamma +org.onap.ccsdk.sli.jdbc.connection.name=sdnctldb01 +org.onap.ccsdk.sli.jdbc.connection.timeout=50 +org.onap.ccsdk.sli.jdbc.request.timeout=100 +org.onap.ccsdk.sli.jdbc.limit.init=10 +org.onap.ccsdk.sli.jdbc.limit.min=10 +org.onap.ccsdk.sli.jdbc.limit.max=20 +org.onap.dblib.connection.recovery=false |