package com.oracle.ceal
import java.net.HttpURLConnection;
import javax.net.ssl.HostnameVerifier
import javax.net.ssl.HttpsURLConnection
import javax.net.ssl.SSLContext
import javax.net.ssl.SSLSession
import javax.net.ssl.TrustManager
import javax.net.ssl.X509TrustManager
class BicsRestClient {
private HttpURLConnection connection
private bicsUrl
private def proxyHost
private def proxyPort
private def user
private def pwd
private def domain
private def ignoreSSLCertsErrors
public BicsRestClient(bicsServerUrl,httpProxyHost, httpProxyPort, identityDomain,username, password, ignoreSSLCertificationPathErrors) {
bicsUrl=bicsServerUrl
proxyHost=httpProxyHost
proxyPort=httpProxyPort
domain=identityDomain
user=username
pwd=password
ignoreSSLCertsErrors=ignoreSSLCertificationPathErrors
}
def setProxyParams() {
Properties systemProperties = System.getProperties()
systemProperties.setProperty("http.proxyHost",proxyHost)
systemProperties.setProperty("http.proxyPort",proxyPort)
systemProperties.setProperty("https.proxyHost",proxyHost)
systemProperties.setProperty("https.proxyPort",proxyPort)
}
def setSSLParams() {
if (ignoreSSLCertsErrors !=null && ignoreSSLCertsErrors.toUpperCase()=="TRUE") {
println "Ignoring SSL certification path errors"
// Disable SSL cert validation
def hostnameVerifier = [
verify: { hostname, session -> true }
]
def trustManager = [
checkServerTrusted: { chain, authType -> },
checkClientTrusted: { chain, authType -> },
getAcceptedIssuers: { null }
]
HttpsURLConnection.setDefaultHostnameVerifier(hostnameVerifier as HostnameVerifier)
HttpsURLConnection.setDefaultSSLSocketFactory(context.getSocketFactory())
SSLContext context = SSLContext.getInstance("SSL")
context.init(null, [trustManager as X509TrustManager] as TrustManager[], null)
}
}
def openConnection(restUrl,method,contentType, body) {
println "Opening connection to bics $restUrl with method:$method"
int statusCode
setProxyParams()
setSSLParams()
URL newUrl
newUrl=new URL(restUrl)
connection = (HttpURLConnection) newUrl.openConnection()
connection.setDoOutput(true)
connection.setDoInput(true)
connection.setUseCaches(false)
if (method=="")
connection.setRequestMethod("GET")
else
connection.setRequestMethod(method)
//adding X-ID-TENANT-NAME <identity_domain>
//connection.setRequestProperty("X-ID-TENANT-NAME",domain)
if (contentType.toUpperCase()=="FORM") {
connection.setRequestProperty("Content-Type","application/x-www-form-urlencoded")
}
if (contentType.toUpperCase()=="JSON") {
connection.setRequestProperty("Content-Type","application/json")
}
if (contentType.toUpperCase()=="") {
// add no content type
}
String userCredentials = domain +"."+user + ":" + pwd
String basicAuth = "Basic " + javax.xml.bind.DatatypeConverter.printBase64Binary(userCredentials.getBytes())
connection.setRequestProperty("Authorization", basicAuth)
if (body!=null && body!="") {
DataOutputStream wr = new DataOutputStream (connection.getOutputStream ());
wr.writeBytes (body);
wr.flush ();
wr.close ();
}
String response=""
try {
statusCode = connection.responseCode
println "Connection status code: $statusCode "
if (statusCode==401 || statusCode==403) {
println "Not authorized"
}
if (statusCode==200) {
println "Authentication succeeded"
println "Server response:"
println "-----"
response=displayServerResponse(connection)
println "-----"
}
if (statusCode==400 || statusCode==500) {
println "Bad request"
println "Server response:"
println "-----"
response=displayServerResponse(connection)
println "-----"
}
} catch (Exception e) {
println "Error connecting to the URL"
println e.getMessage()
} finally {
if (connection != null) {
connection.disconnect();
}
}
return response
}
def displayServerResponse(connection) {
InputStream is;
if (connection.getResponseCode()==200) {
is=connection.getInputStream();
} else {
is=connection.getErrorStream();
}
println "Response Content-Type:"+connection.getContentType()
BufferedReader br = new BufferedReader(new InputStreamReader(is));
StringBuilder sb = new StringBuilder();
String line;
while ((line = br.readLine()) != null) {
sb.append(line+"\n");
}
br.close();
println sb
return sb.toString()
}
def aboutBics() {
println "**About bics**"
def restUrl=bicsUrl+"/dataload/v1/about"
def response
response=openConnection(restUrl,"GET","FORM","")
println "****"
}
def listAllTables() {
//<URL>/dataload/v1/tables
println "**List tables**"
def restUrl=bicsUrl+"/dataload/v1/tables"
def response
response=openConnection(restUrl,"GET","FORM","")
println "****"
}
def getTableInfo(tableName) {
println "**Get table info**"
def restUrl=bicsUrl+"/dataload/v1/tables?name="+tableName.toUpperCase()
def response
response=openConnection(restUrl,"GET","","")
println "****"
}
def createTableToLoadCSV(tableName, numberOfVarCharCols, columnPrefix ) {
println "**Create table**"
// create json manually for X columns
/*
{
"columnName":"COL_1",
"dataType":"VARCHAR",
"length":300,
"precision":0,
"nullable":true,
"defaultValue":null,
},
* */
def restUrl=bicsUrl+"/dataload/v1/tables/"+tableName.toUpperCase()
def JSONColumns
JSONColumns="["
def i
for (i = 1; i <=numberOfVarCharCols; i++) {
if (i==numberOfVarCharCols) {
JSONColumns=JSONColumns+"{\"columnName\":\""+columnPrefix.toUpperCase()+""+i+"\",\"dataType\":\"VARCHAR\",\"length\":300,\"precision\":0,\"nullable\":true,\"defaultValue\":null}"
} else {
JSONColumns=JSONColumns+"{\"columnName\":\""+columnPrefix.toUpperCase()+""+i+"\",\"dataType\":\"VARCHAR\",\"length\":300,\"precision\":0,\"nullable\":true,\"defaultValue\":null},"
}
}
JSONColumns=JSONColumns+"]"
println "JSON columns:"+JSONColumns
def response
response=openConnection(restUrl,"PUT","JSON",JSONColumns)
println "****"
}
def createTableToLoadCSVWithHeaderNames(tableName, listHeaders ) {
println "**Create table**"
// create json manually for X columns with headers in list
/*
{
"columnName":"COL_1",
"dataType":"VARCHAR",
"length":300,
"precision":0,
"nullable":true,
"defaultValue":null,
},
* */
def restUrl=bicsUrl+"/dataload/v1/tables/"+tableName.toUpperCase()
def JSONColumns
JSONColumns="["
listHeaders.each { headerName ->
if(headerName == listHeaders.last()) {
JSONColumns=JSONColumns+"{\"columnName\":\""+headerName.toUpperCase()+"\",\"dataType\":\"VARCHAR\",\"length\":300,\"precision\":0,\"nullable\":true,\"defaultValue\":null}"
} else {
JSONColumns=JSONColumns+"{\"columnName\":\""+headerName.toUpperCase()+"\",\"dataType\":\"VARCHAR\",\"length\":300,\"precision\":0,\"nullable\":true,\"defaultValue\":null},"
}
}
JSONColumns=JSONColumns+"]"
println "JSON columns:"+JSONColumns
def response
response=openConnection(restUrl,"PUT","JSON",JSONColumns)
println "****"
}
def loadDataInTableUsingCSV(tableName, localCsvFilePath, localCsvFileName, delimiterInCsv,numberOfColumnsInCsv,numberOfLinesToSkip,columnPrefixInTable,isZipped) {
println "**Load csv file in table**"
println "Processing:"+localCsvFilePath+"/"+localCsvFileName
if (isZipped==true) println "Upload of zip not supported at this time. Ignoring isZipped parameter"
File localCsv=new File(localCsvFilePath+"/"+localCsvFileName)
if(!localCsv.exists() || localCsv.isDirectory()) {
println "File does not exist"
println "****"
return
}
def restUrl=bicsUrl+"/dataload/v1/tables/"+tableName.toUpperCase()+"/data"
setProxyParams()
setSSLParams()
URL newUrl
newUrl=new URL(restUrl)
connection = (HttpURLConnection) newUrl.openConnection()
connection.setDoOutput(true)
connection.setDoInput(true)
connection.setUseCaches(false)
connection.setRequestMethod("PUT")
//connection.setRequestProperty("X-ID-TENANT-NAME",domain)
String userCredentials = domain +"."+user + ":" + pwd
String basicAuth = "Basic " + javax.xml.bind.DatatypeConverter.printBase64Binary(userCredentials.getBytes())
connection.setRequestProperty("Authorization", basicAuth)
/*
* The first part is a JSON descriptor (Content-Type: application/json)
* of the data load. The second part is an input stream
* (Content-Type: application/octet-stream).
* Data in the stream can be text data read
* from comma-separated values (CSV)
* */
def boundary = System.currentTimeMillis() ;
connection.setRequestProperty("Content-Type","multipart/mixed; boundary=" + boundary);
OutputStream outputStream = connection.getOutputStream();
PrintWriter writer = new PrintWriter(new OutputStreamWriter(outputStream, "UTF-8"),true);
// JSON
/*
* {
"columnMaps":[
{
"column":{
"name":"NAME",
"optionalJavaSqlType":null,
"partOfUniqueKey":true,
},
"position":1,
},
{...
}
],
"optionalMaximumErrors":null,
"removeDuplicates":true
"optionalWriteMode":"Insert all",
"delimiter":","
"timestampFormat":"yyyy-MM-dd",
"numberOfLinesToSkip":0
},
*
*
*/
def i
def JSONDataLoad
JSONDataLoad="{\"columnMaps\":["
for (i =1; i <=numberOfColumnsInCsv; i++) {
if (i==numberOfColumnsInCsv) {
JSONDataLoad=JSONDataLoad+"{\"column\":{\"name\":\""+columnPrefixInTable.toUpperCase()+""+i+"\","+"\"optionalJavaSqlType\":null,\"partOfUniqueKey\":false},"+"\"position\":"+i+"}"
} else {
JSONDataLoad=JSONDataLoad+"{\"column\":{\"name\":\""+columnPrefixInTable.toUpperCase()+""+i+"\","+"\"optionalJavaSqlType\":null,\"partOfUniqueKey\":false},"+"\"position\":"+i+"},"
}
}
JSONDataLoad=JSONDataLoad+'''],
"optionalMaximumErrors":null,
"removeDuplicates":false,
"optionalWriteMode":"Insert all",
"delimiter":"'''+delimiterInCsv+"\","+'''
"timestampFormat":"",
"numberOfLinesToSkip":''' + numberOfLinesToSkip +'''}
'''
writer.append("--" + boundary).append("\r\n");
writer.append("Content-Type: application/json").append("\r\n");
writer.append("\r\n");
writer.flush();
writer.append(JSONDataLoad)
writer.append("\r\n");
writer.flush();
writer.append("\r\n").flush();
//writer.append("--" + boundary ).append("\r\n");
// CSV or ZIP file content
writer.append("--" + boundary).append("\r\n");
writer.append("Content-Type: application/octet-stream").append("\r\n");
writer.append("\r\n");
writer.flush();
FileInputStream inputStream = new FileInputStream(new File(localCsvFilePath+"/"+localCsvFileName));
byte[] buffer = new byte[4096];
int bytesRead = -1;
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
outputStream.flush();
inputStream.close();
writer.append("\r\n");
writer.flush();
writer.append("\r\n").flush();
writer.append("--" + boundary + "--").append("\r\n");
writer.close();
String response=""
def statusCode
try {
statusCode = connection.responseCode
println "Connection status code: $statusCode "
if (statusCode==401 || statusCode==403) {
println "Not authorized"
}
if (statusCode==200) {
println "Authentication succeeded"
println "Server response:"
println "-----"
response=displayServerResponse(connection)
println "-----"
}
if (statusCode==400 || statusCode==500) {
println "Bad request"
println "Server response:"
println "-----"
response=displayServerResponse(connection)
println "-----"
}
} catch (Exception e) {
println "Error connecting to the URL"
println e.getMessage()
} finally {
if (connection != null) {
connection.disconnect();
}
}
println "****"
}
def loadDataInTableUsingCSVAndHeader(tableName, localCsvFilePath, localCsvFileName, delimiterInCsv,numberOfLinesToSkip,listHeaders,isZipped) {
println "**Load csv file in table using headers**"
println "Processing:"+localCsvFilePath+"/"+localCsvFileName
if (isZipped==true) println "Upload of zip not supported at this time. Ignoring isZipped parameter"
File localCsv=new File(localCsvFilePath+"/"+localCsvFileName)
if(!localCsv.exists() || localCsv.isDirectory()) {
println "File does not exist"
println "****"
return
}
def restUrl=bicsUrl+"/dataload/v1/tables/"+tableName.toUpperCase()+"/data"
setProxyParams()
setSSLParams()
URL newUrl
newUrl=new URL(restUrl)
connection = (HttpURLConnection) newUrl.openConnection()
connection.setDoOutput(true)
connection.setDoInput(true)
connection.setUseCaches(false)
connection.setRequestMethod("PUT")
//connection.setRequestProperty("X-ID-TENANT-NAME",domain)
String userCredentials = domain +"."+user + ":" + pwd
String basicAuth = "Basic " + javax.xml.bind.DatatypeConverter.printBase64Binary(userCredentials.getBytes())
connection.setRequestProperty("Authorization", basicAuth)
/*
* The first part is a JSON descriptor (Content-Type: application/json)
* of the data load. The second part is an input stream
* (Content-Type: application/octet-stream).
* Data in the stream can be text data read
* from comma-separated values (CSV)
* */
def boundary = System.currentTimeMillis() ;
connection.setRequestProperty("Content-Type","multipart/mixed; boundary=" + boundary);
OutputStream outputStream = connection.getOutputStream();
PrintWriter writer = new PrintWriter(new OutputStreamWriter(outputStream, "UTF-8"),true);
// JSON
/*
* {
"columnMaps":[
{
"column":{
"name":"NAME",
"optionalJavaSqlType":null,
"partOfUniqueKey":true,
},
"position":1,
},
{...
}
],
"optionalMaximumErrors":null,
"removeDuplicates":true
"optionalWriteMode":"Insert all",
"delimiter":","
"timestampFormat":"yyyy-MM-dd",
"numberOfLinesToSkip":0
},
*
*
*/
int i
i=1
def JSONDataLoad
JSONDataLoad="{\"columnMaps\":["
listHeaders.each { headerName ->
if(headerName == listHeaders.last()) {
JSONDataLoad=JSONDataLoad+"{\"column\":{\"name\":\""+headerName.toUpperCase()+"\","+"\"optionalJavaSqlType\":null,\"partOfUniqueKey\":false},"+"\"position\":"+i+"}"
} else {
JSONDataLoad=JSONDataLoad+"{\"column\":{\"name\":\""+headerName.toUpperCase()+"\","+"\"optionalJavaSqlType\":null,\"partOfUniqueKey\":false},"+"\"position\":"+i+"},"
}
i=i+1
}
JSONDataLoad=JSONDataLoad+'''],
"optionalMaximumErrors":null,
"removeDuplicates":false,
"optionalWriteMode":"Insert all",
"delimiter":"'''+delimiterInCsv+"\","+'''
"timestampFormat":"",
"numberOfLinesToSkip":''' + numberOfLinesToSkip +'''}
'''
writer.append("--" + boundary).append("\r\n");
writer.append("Content-Type: application/json").append("\r\n");
writer.append("\r\n");
writer.flush();
writer.append(JSONDataLoad)
writer.append("\r\n");
writer.flush();
writer.append("\r\n").flush();
//writer.append("--" + boundary ).append("\r\n");
// CSV or ZIP file content
writer.append("--" + boundary).append("\r\n");
writer.append("Content-Type: application/octet-stream").append("\r\n");
writer.append("\r\n");
writer.flush();
FileInputStream inputStream = new FileInputStream(new File(localCsvFilePath+"/"+localCsvFileName));
byte[] buffer = new byte[4096];
int bytesRead = -1;
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
outputStream.flush();
inputStream.close();
writer.append("\r\n");
writer.flush();
writer.append("\r\n").flush();
writer.append("--" + boundary + "--").append("\r\n");
writer.close();
String response=""
def statusCode
try {
statusCode = connection.responseCode
println "Connection status code: $statusCode "
if (statusCode==401 || statusCode==403) {
println "Not authorized"
}
if (statusCode==200) {
println "Authentication succeeded"
println "Server response:"
println "-----"
response=displayServerResponse(connection)
println "-----"
}
if (statusCode==400 || statusCode==500) {
println "Bad request"
println "Server response:"
println "-----"
response=displayServerResponse(connection)
println "-----"
}
} catch (Exception e) {
println "Error connecting to the URL"
println e.getMessage()
} finally {
if (connection != null) {
connection.disconnect();
}
}
println "****"
}
def deleteTable(tableName) {
println "**Delete table**"
def restUrl=bicsUrl+"/dataload/v1/tables/"+tableName.toUpperCase()
def response
response=openConnection(restUrl,"DELETE","","")
println "****"
}
def deleteDataFromTable(tableName) {
println "**Delete all data from table**"
def restUrl=bicsUrl+"/dataload/v1/tables/"+tableName.toUpperCase()+"/data"
def response
response=openConnection(restUrl,"DELETE","","")
println "****"
}
def truncateList(listName, truncateLength) {
println "**Truncating list**"
def trimmedList
listName=listName*.trim()
trimmedList=listName*.take(truncateLength)
println ("New list:"+trimmedList)
println "****"
return trimmedList
}
}