How to find max release version from DB by custom sql function

Normal MAX function inn sql will get the maximum based on mathematical calculation. 

For e.g.  Max between  

        1.9 and 1.10 will return 1.9

But in case of release highest should be 1.10. 

If we want to sort based on release numbers and get the maximum then we have to create a custom sql function and get the maximum. For this we will LPAD the release number by replacing each number after/replacing each "." with some specific number of 0. After that we can check for max number and return it.

CREATE OR REPLACE FUNCTION MAX_RELEASE_VERSION(release_version IN VARCHAR2) 

RETURN VARCHAR2 AS

sub_string VARCHAR2(50);

BEGIN

FOR char_value IN 

(SELECT LPAD(regexp_substr(release_version,'[^.]+', 1, level),4,'0') as splited_num FROM DUAL CONNECT BY regexp_substr(release_version, '[^.]+', 1, level) IS NOT null)

LOOP

sub_string:=sub_string || char_value.splited_num;

END LOOP;

RETURN sub_string;

END;

/

Read Manifest file of a package without extracting the file in python

We can read the MANIFEST.MF or any file from a archive file without extracting the file.
With below code we can read the manifest file and retrieve the values for the key 'Plugin-Dependencies'. We can add more keys and if that key matches then it will return the values for that key.



import argparse
import os
import sys
import zipfile
import re
import logging

global manifestkeys = []
manifestkey = 'Plugin-Dependencies'

def getManifestkeys():
        manifestkeys.append(manifestkey.lower().strip().replace('-',''))
        return manifestkeys


def getAttributes(manifestFile):
        logging.debug('Parsing attributes from %s'%(manifestFile))
        manifestkeys = getManifestkeys()
        logging.debug('Manifest Key : ' + str(manifestkeys))
        manifest = []
        current = {}
        lines = manifestFile.split('\n')
        idx = 0
        while idx<len(lines):
                line = lines[idx].strip()               
                if len(line) > 0:
                        splits = line.split(':')
                        key = splits[0].lower().strip().replace('-','')
                        if key in manifestkeys:
                                logging.debug('Key matched for ' + key)
                                value = ':'.join(splits[1:]).strip()
                                current[key] = value
                                while len(line.encode('utf-8')) >= 70:
                                        if idx<len(lines)-1:
                                                next_line = lines[idx+1]
                                                if next_line[0] == ' ':
                                                        current[key] += next_line.strip()
                                                        idx += 1
                                                        line = next_line
                                                        continue
                                        break
                elif len(current)>0 :
                        count = len(current.values())
                        # We need to weed out name only entries
                        # We can manually interrogate packages
                        if count > 1 or (count==1 and 'name' not in current.keys()):
                                manifest.append(current)
                        current = {}
                idx += 1
        if current != {}:
                manifest.append(current)
        return manifest

def readManifest(jar_file_name):
  _MANIFEST_NAME = 'META-INF/MANIFEST.MF'
  with zipfile.ZipFile(jar_file_name) as jar:
    try:
      manifest_string = jar.read(_MANIFEST_NAME)
      return getAttributes(manifest_string)
    except KeyError:
      return None


if __name__ == "__main__":
    logging.basicConfig(level=logging.WARNING)
    parser = argparse.ArgumentParser(description='Provide arguments to script as below')
    parser.add_argument('artifact_name',help='A String positional argument as artifact name for which the manifest to be read.')
    args = parser.parse_args()
    readManifest(args.artifact_name)

Convert an integer column value from AlphaNumeric values in SQL

For e.g. We have the column value  ' 1.11+build.201701152243 '

Here we have combination of alphabets and numeric and special characters like + and .
Suppose we need to convert it to integer for comparison purpose.

We can convert like below.


CAST(REGEXP_REPLACE(<COLUMN_NAME>,'[^0-9]','') AS INTEGER )

This will first replace everything except digits with empty. After that it will convert that as Integer.
Then we can use that as int for aggregate function.

How to sort content of a file based on multiple columns in UNIX

For e.g.

We have below data in a file and we want to sort based on the date.
We can sort this by :
 1st sort based on YYYY   ( -k 4n ) Sort 4th column based on number
 2nd sort based on MON    (-k 3M)  Sort 3rd column based on Month
 3rd sort based on DD
 4th sort based on TIME

test.txt
--------
Sun,  20  Aug 2018 23:11:10 GMT   1.0.0-MASTER-20180801-171
Wed, 22  Mar 2019 22:11:10 GMT  1.0.0-20180823-263
Mon, 22  Apr 2018 21:11:10 GMT  1.0.0-20180823-265
Wed, 28  Feb 2018 21:11:09 GMT  1.0.0-20180823-263
Tue,  28  Feb 2018 21:11:10 GMT   1.0.0-20180823-263
Wed, 21  Jan 2020 23:11:10 GMT  1.0.0-20180823-243


sort -k 4n -k 3M -k 2n -k 5n test.txt > sortedTest.txt

How to deploy an external artifact to repository in pom.xml

We can use "maven-deploy-plugin" to upload any artifact to repository from a location by pom.xml.
By this we can upload any artifact without uploading the generated artifacts, also without install/deploy goal.

....
</plugins>
....
<plugin>
    <artifactId>maven-deploy-plugin</artifactId>
    <version>2.8.2</version>
    <executions>
        <execution>
            <id>deploy-file</id>
            <phase>clean</phase> <!-- The phase in which you want to upload the file to Repository . In this case "mvn clean " goal will upload the artifact.-->
            <goals>
                <goal>deploy-file</goal>
            </goals>
            <configuration>
<repositoryId>maven-public</repositoryId>  <!-- This is the id which contains the user/pass to connet to Nexus. This will be under "servers" section in settings.xml -->
                <file>C:\Users\bismayam\Desktop\poc.jar</file>
                <url>http://<host>:<port>/nexus/content/repositories/test-releases/</url>
                <groupId>testGroup</groupId>
                <artifactId>testArtifact</artifactId>
                <version>1.0</version>
                <packaging>jar</packaging>
            </configuration>
        </execution>
    </executions>
</plugin>
</plugins>

How to test private constructors in Junit test by reflection in Java

We can not test private constructors directly by JUnit test. We need to use reflection API to call the constructors.


For e.g we have below class which have a private constructor.

package com.company.main;
import java.lang.reflect.InvocationTargetException;
public class MainClass {
private MainClass(){
System.out.println("From private constructor");
}
}


We have the below test case to use reflection to call the private constructor.  If there are any explicit checking is being done by the developer to restrict the calling of private constructor then it may throw exception. 

package com.company.test;

import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import org.junit.jupiter.api.Test;
import com.company.main.MainClass;

class MainTest {

@Test
void test() throws NoSuchMethodException, SecurityException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {

Constructor<MainClass> constructor = MainClass.class.getDeclaredConstructor(new Class[0]);
constructor.setAccessible(true);
MainClass mainClass = constructor.newInstance(new Object[0]);
}
}

How to restrict a private constructor from being called by reflection API in Java

To restrict the private method from being called by reflection API we have to write a method that will check the caller of the method. If the caller is from any other class than the current class then we have to throw the error.

We can get the caller class from the caller stack of Reflection API.

getCallerClass(0) will return class "sun.reflect.Reflection"
getCallerClass(1) will return class "com.company.main.MainClass" // Same class

getCallerClass(1) will return class "jdk.internal.reflect.NativeConstructorAccessorImpl" // If it is being called from any other calss.

check if caller class of 1 and 3 are not the same then throw exception.

package com.company.main;

public class MainClass {

private MainClass(){
checkCallerClass();
System.out.println("From private constructor");
}


private void checkCallerClass() {
               Class self = sun.reflect.Reflection.getCallerClass(1);
               Class caller = sun.reflect.Reflection.getCallerClass(3);
               if (self != caller) {
                    throw new java.lang.IllegalAccessError();
               }
        }

}

How to check if a jar file is corrupted

Sometimes jar files get corrupted and we will get errors like below when we will run the file.

 $ java -jar test.jar
Error: Invalid or corrupt jarfile test.jar

When we try to see the content with jar -tvf test.jar.

java.util.zip.ZipException: error in opening zip file
        at java.util.zip.ZipFile.open(Native Method)
        at java.util.zip.ZipFile.<init>(ZipFile.java:127)
        at java.util.zip.ZipFile.<init>(ZipFile.java:88)
        at sun.tools.jar.Main.list(Main.java:977)
        at sun.tools.jar.Main.run(Main.java:222)
        at sun.tools.jar.Main.main(Main.java:1147)

Run below command. If this is giving proper output like below then it is not corrupted.

It should give information about the classes present in that jar file and few others.

This jar file may work with -cp command. This usually happens if you use prefix with /META-INF/MANIFEST.MF

testuser@indl123!:testuser> unzip -lv RunDbSttm.jar
Archive:  RunDbSttm.jar
 Length   Method    Size  Cmpr    Date    Time   CRC-32   Name
--------  ------  ------- ---- ---------- ----- --------  ----
       0  Defl:N        2   0% 07-20-2016 11:49 00000000  META-INF/
      68  Defl:N       67   2% 07-20-2016 11:49 0a41c062  META-INF/MANIFEST.MF
    3653  Defl:N     2022  45% 07-20-2016 11:48 e8c11d5d  RunDbSttm.class
--------          -------  ---                            -------
    3721             2091  44%                            3 files

How to start a Java program (jar) from another Jar

We can start a jar inside another jar and get the result by below code.

Process proc = new ProcessBuilder("java", "-XMx8192M", "-jar", "test.jar").start();
int result = proc.waitFor();

You can get details of ProcessBuilder from below.

https://docs.oracle.com/javase/10/docs/api/java/lang/ProcessBuilder.html

Download any file from Database

package com.edu.main;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class DownloadFileFromDB {

public static void main(String[] args) {

final String host = "jdbc:oracle:thin:@<host>:1521:<service_name>";
final String user = "username";
final String pass = "*******";
String SQL = "SELECT <COLUMN> FROM <TABLE> WHERE <COLUMN>=?";

Connection conn = null;
PreparedStatement smt = null;
InputStream input = null;
FileOutputStream output = null;
ResultSet rs = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(host, user, pass);
System.out.println("DB Connection successful..");

smt = conn.prepareStatement(SQL);
smt.setString(1, "<VALUE_TO_BE_REPLACED_WITH_?>");
rs = smt.executeQuery();

output = new FileOutputStream(new File("C:\\Users\\bismaya\\Desktop\\<FILENAME>.<EXT>"));

while (rs.next()) {
input = rs.getBinaryStream("<COLUMN_OF_FILE_IN_DB>");
int r = 0;
while ((data = input.read()) != -1) {
output.write(data);
}
}
System.out.println("Success...");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
System.err.println("DB Connection failed!");
e.printStackTrace();
} catch (FileNotFoundException e) {
System.err.println("File not found in provided location!");
e.printStackTrace();
} catch (IOException e) {
System.err.println("Error while writing to file!");
e.printStackTrace();
} finally {
if (rs != null) {
try {
input.close();
output.flush();
output.close();
smt.close();
conn.close();
} catch (SQLException e) {
System.err.println("Error while closing the connecton!");
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

}

Apache httpd load balancer setup

Install httpd as service by following  http://httpd.apache.org/docs/2.4/install.html 

sudo yum install httpd
sudo systemctl enable httpd
sudo systemctl start httpd

1) Edit /etc/httpd/conf/httpd.conf and change the Listen port to configure the Load balancer port. By default port will be 80.

2) Add a file lb.conf under /etc/httpd/conf.d to configure members to be redirected. httpd service will load all *.conf files present under /etc/httpd/conf.d folder.

3) lb.conf content example as below.

ProxyRequests off
<Proxy balancer://Testbalancer>
BalancerMember <URL1>
BalancerMember <URL2>
BalancerMember <URL3>
ProxySet lbmethod=byrequests Or ProxySet lbmethod=bybusyness Or lbmethod=bytraffic
</Proxy>
<Location /balancer-manager>
SetHandler balancer-manager
</Location>
ProxyPass /balancer-manager !
ProxyPass / balancer://Testbalancer/ connectiontimeout=30 timeout=60


This will redirect all request http://<host>/ to either <URL1> or <URL2> or <URL3>

4) You can configure ProxyPass to redirect in case some specific string comes after the URL.

    ProxyPass "/test" balancer://Testbalancer/ connectiontimeout=30 timeout=60

    ProxyPassMatch "^/(.*\.gif)$" "http://backend.example.com/$1"

This will cause a local request for http://example.com/foo/bar.gif to be internally converted into a proxy request to http://backend.example.com/foo/bar.gif.

    ProxyPassMatch "^/(.*\.gif)$" "http://backend.example.com:8000/$1"


Start/Stop/Restart  service by : service httpd start/stop/restart

POST data to any endpoint via REST API

curl -v -X POST -u "user:password" --header "Content-Type: application/json" 'http://<host>:<port>/<endpoint>' -d @test.json

You can change the Content-Type based on your required type and supported by endpoint.

Type application 

================

application/javascript

application/octet-stream

application/ogg

application/pdf

application/xhtml+xml

application/x-shockwave-flash

application/json

application/ld+json

application/xml

application/zip

Type Audio

==========

audio/mpeg

audio/x-ms-wma

audio/vnd.rn-realaudio

audio/x-wav

Type image

==========

image/gif

image/jpeg

image/png

image/tiff

image/vnd.microsoft.icon

image/x-icon

image/vnd.djvu

image/svg+xml

Encode Decode error in python : UnicodeDecodeError: 'ascii' codec can't decode byte 0x80 in position 0

Error desc:

Traceback (most recent call last):
  File "<stdin>", line 1, in ?
UnicodeDecodeError: 'ascii' codec can't decode byte 0x80 in position 0:
                    ordinal not in range(128)

This type of error comes when some special character is not in range of selected encoding type.

Solution :

Find the default encoding type of python.
             python -c 'import sys; print(sys.getdefaultencoding())'
             o/p : ascii

We need to change the encoding type and try with different type of encoding. e.g.

latin-1
utf-8
utf-16-be


Create a file sitecustomize.py under this location : /usr/lib/python2.7/site-packages/sitecustomize.py

echo "import sys; sys.setdefaultencoding('latin-1')" > sitecustomize.py


Compile the python script and check the encoding type is changed by below command.
python -m py_compile sitecustomize.py

PYTHONPATH=".:$PYTHONPATH" python -c 'import sys; print(sys.getdefaultencoding())'

o/p : latin-1

How to configure postgres database in SqlDeveloper

Download postgressql*.jar and configure it in SqlDeveloper using steps below.

1. Open Sqldeveloper 
2. Click on Tools tab and select Preferences. 
3. Under database tab select ThirdParty JDBC driver and add a new Entry with downloaded postgres jar.

Connect to database from Jenkins Active Choice parameter to retrieve data

We can connect to database and get the data to show in Jenkins job as a parameter.


import groovy.sql.Sql
import java.sql.*

def output = []

def sql = Sql.newInstance("jdbc:oracle:thin:@<host>:<port>:<instance>","<user>","<password>","oracle.jdbc.driver.OracleDriver");
  sql.eachRow("SELECT EMP_ID FROM SCHEMA.EMPLOYEE WHERE EMP_NAME='TEST' AND POSITION LIKE '%Manager'"){ row ->
            output.push(row[0])
  }
 
return output

Make sure you have driver jar present in Jenkins classpath.
Or add the ojdbc7.jar under /usr/java/packages/lib/ext and restart Jenkins service

Auto Refresh specific tab in browser and specific area of Page by Javascript

I had one requirement to auto refresh any page after specif interval and that should show only that part of the page which content I want to see.

Below is the Java script code which has to be added as a bookmark and click that bookmark after opening the page and scroll down to specific part you want to see after each refresh.



javascript:
refresh_interval=prompt("Set Refresh Interval in Seconds [s]");
current_page=location.href;
if(refresh_interval>0) {
  selectedFrame='<frameset cols=\'*\'>\n<frame id="refreshframe" src=\''+current_page+'\'/>';
  selectedFrame+='</frameset>';
  with(document) {
    write(selectedFrame);
    void(close())
  };
  setTimeout('reload()',1000*refresh_interval);
} else {
  location.replace(current_page);
}
function reload() {
  setTimeout('reload()',1000*refresh_interval);
  var frameToSet = document.getElementById('refreshframe');
  frameToSet.contentWindow.location.reload(false);
}

How to get port number of a process by pid in Unix

Get the pid by ps -ef | grep <processName>

Get the port in which this process is listening by below command.

netstat -lanpt | grep LISTEN | grep <pid>