Added A Column To Every Table In The Same Schema In PostgresSQL

Sometime, we may find out that we need to add a column to every table in the same schema or same database. For example, we want to add an audit column. The following query is for that.

do $$

declare

    addcolumn record;

    dm_schema VARCHAR(8) := ‘sales_dm';

begin

for addcolumn in

select

      'ALTER TABLE '|| dm_schema ||'.'|| T.targettable ||  ' ADD COLUMN  IF NOT EXISTS last_modified_timestamp timestamp  NULL' as sqlscript

   from

      (

        select tablename as targettable from  pg_tables where schemaname = dm_schema 

      ) t

loop

execute addcolumn.sqlscript;

end loop;

end;

$$;

Get Date OF Week From Datetime

The following function reture weekday in differe format from a day.  

def getWeekDayChar(self,date_time : datetime,rt_type : str ) -> str :

    ''' 

     :param date_time: datetime     

     :param rt_type: 'i' = number, 'l' = long form, 's' = short form     

     :return: week date in spefified format 

    ''' 

    week_date_dict : dict = {0: ('Monday','Mon'), 1:('Tuesday','Tus'),2 : ('Wendsday', 'Wed'),
                             3:('Thursday','Thu'), 4: ('Friday','Fri') ,
                             5 :('Saturday','Sat')   ,6:('Sunday','Sun')}

    week_date_num : int = date_time.weekday()

    if rt_type == 'i' :
        return week_date_num
    elif rt_type == 'l' :
        return week_date_dict(week_date_num)(0)
    elif rt_type == 's' :
        return week_date_dict(week_date_num)(1)
    else :
        return None

Retrieve JWT Authentication Access Token Using HttpsURLConnection

In JWT Authentication, a token is used to access the API interfaces.  In general, it will be a two-step authentication process. First, it will authenticate the user using the user's credentials such as login id, password, client id, and secret keys. Following is a method on how to get the access token uses HTTPS protocol.

    public JwtAuthenticationResponse getHttpsAuthToken(String hostName, int port,     

                                                                    String   userName, String userPassword,

                                                            String grantType, String clientId, String clientSecret,

                                                               String urlPath) 

        HttpsURLConnection httpConn = null;

        final int BUFFER_SIZE = 4096;

        // Create a trust manager that does not validate certificate chains (should be removed in prod env)

        TrustManager[] trustAllCerts = new TrustManager[]{new X509TrustManager() {

            public java.security.cert.X509Certificate[] getAcceptedIssuers() {

                return null;

            }

            public void checkClientTrusted(X509Certificate[] certs, String authType) {

            }

            public void checkServerTrusted(X509Certificate[] certs, String authType) {

            }

        }

        };

        try {

            // Install the all-trusting trust manager

            SSLContext sc = SSLContext.getInstance("SSL");

            sc.init(null, trustAllCerts, new java.security.SecureRandom());

            HttpsURLConnection.setDefaultSSLSocketFactory(sc.getSocketFactory());

            // Create all-trusting host name verifier

            HostnameVerifier allHostsValid = new HostnameVerifier() {

                public boolean verify(String hostname, SSLSession session) {

                    return true;

                }

            };

            HttpsURLConnection.setDefaultHostnameVerifier(allHostsValid);

        } catch (NoSuchAlgorithmException e) {

            logger.debug(String.format("Cannot connect to Hybris server:  %s ;", e.toString()));

            throw new RuntimeException(e);

        } catch (KeyManagementException e) {

            logger.debug(String.format("Cannot connect to Hybris server:  %s ;", e.toString()));

            throw new RuntimeException(e);

        }

        String url;

url = "https://"+hostName + ":"+String.valueOf(port)+"/services/oauth2/token";

        logger.debug("get authendication using: " + url);

        InputStream inputStream;

        try {

            StringBuilder data = new StringBuilder ( );

            data.append ("grant_type=" + grantType);

            data.append ("&client_id=" + clientId);

            data.append ("&client_secret=" + clientSecret);

            if (userId != null && !userId.equals ("undefined")) {

                data.append ("&username=" + userId);

            }

            if (userPassword != null && !userPassword.equals ("undefined")) {

                data.append ("&password=" + userPassword);

            }

           String auth = clientId + ":" + clientSecret;

            byte[] encodedS = Base64.encodeBase64 (auth.getBytes ( ));

            String authHeader = "Basic " + new String (encodedS);

            // Create a byte array of the data to be sent

            byte[] encodedAuth = data.toString ( ).getBytes ("UTF-8");

            String authId = userId + ":" + userPassword;

            byte[] encodedId = Base64.encodeBase64 (authId.getBytes ( ));

            String authHeaderId = "Basic " + new String (encodedId);

            // Setup the Request

            URL request = new URL (url);

            httpConn = (HttpsURLConnection) request.openConnection ( );

            httpConn.setRequestMethod ("POST");

            httpConn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");

            httpConn.setRequestProperty("Content-Length", "" + encodedAuth.length);

            httpConn.setRequestProperty("Content-Length", "" + encodedAuth.length);

            httpConn.setRequestProperty("Authorization", authHeaderId);

            httpConn.setUseCaches (false);

            httpConn.setDoOutput (true);

            // Write data

            OutputStream postStream = httpConn.getOutputStream ( );

            postStream.write (encodedAuth);

            postStream.flush ( );

            postStream.close ( );

            // For POST only - END

            int responseCode = httpConn.getResponseCode ( );

            String response;

            if (responseCode == 200) {

                inputStream = httpConn.getInputStream ( );

                ObjectMapper mapper = new ObjectMapper ( );

                JsonNode jsonMap = mapper.readTree (inputStream);

                String token = jsonMap.get ("access_token").toString ( ).replaceAll ("\"", "");

                String tokenType = jsonMap.get ("token_type").toString ( ).replaceAll ("\"", "");

                String refreshToken = null;

                return new JwtAuthenticationResponse (jsonMap.get ("access_token").toString ( ));

            } else {

                inputStream = httpConn.getErrorStream ( );

                try (Scanner scanner = new Scanner (inputStream)) {

                    scanner.useDelimiter ("\\Z");

                    response = (scanner.next ( ));

             }

            String exceptionMsg = "Response Code " + String.valueOf (responseCode) + ". " +

                                                  response;

                throw new UserDefinedException (exceptionMsg);

            }

        } catch (IOException | UserDefinedException e) {

            logger.debug(String.format("Cannot connect to server:  %s ;", e.toString()));

            throw new RuntimeException(e);

        }

    }