com.ibm.cp4waiops.connectors.sdk.EDBPostgresHelper Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of connectors-sdk Show documentation
A developer SDK for creating connectors for CP4WAIOps.
The newest version!
package com.ibm.cp4waiops.connectors.sdk;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Instant;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.json.JSONObject;

public class EDBPostgresHelper {

    static final Logger logger = LogManager.getLogger(EDBPostgresHelper.class.getName());

    private String EDB_DRIVER_CLASS_NAME = "org.postgresql.Driver";
    private int EDB_INITIALSIZE_DB = 1;
    private int EDB_MAXTOTAL_DB = 5;
    private BasicDataSource EDBBasicDS;
    private String edbpostgresHost = "";
    private String edbpostgresPort = "";
    private String edbpostgresUsername = "";
    private String edbpostgresPassword = "";
    private String edbpostgresDbname = "";
    private Boolean foundEDBPostgresEnv = false;

    public static final String DEFAULT_CONNECTIONS_TABLE_NAME = "checkpointtable";
    public static final String DEFAULT_QUERY_KEY = "connection_id";

    public Boolean setEDBPostgresConnection() {
        Boolean success = false;
        // Read EDB Postgres env vars
        if (readEDBPostgtresSecret().booleanValue()) {
            // Setting EDB postgres configuration
            if (!this.edbpostgresHost.isEmpty() && !this.edbpostgresPort.isEmpty()
                    && !this.edbpostgresUsername.isEmpty() && !this.edbpostgresPassword.isEmpty()
                    && !this.edbpostgresDbname.isEmpty()) {
                String URL_EDB_DB = "jdbc:postgresql://" + this.edbpostgresHost + ":" + this.edbpostgresPort + "/"
                        + this.edbpostgresDbname;
                logger.info("URL_EDB_DB - " + URL_EDB_DB);

                EDBBasicDS = new BasicDataSource();
                EDBBasicDS.setDriverClassName(EDB_DRIVER_CLASS_NAME);
                EDBBasicDS.setUrl(URL_EDB_DB);
                EDBBasicDS.setUsername(this.edbpostgresUsername);
                EDBBasicDS.setPassword(this.edbpostgresPassword);
                EDBBasicDS.setDefaultQueryTimeout(300); // 5 min timeout
                // The initial number of connections that are created when the pool is started.
                EDBBasicDS.setInitialSize(EDB_INITIALSIZE_DB);
                // The maximum number of active connections that can be allocated from this pool
                // at the same time
                EDBBasicDS.setMaxTotal(EDB_MAXTOTAL_DB);
                success = true;
            } else {
                logger.error("Something is wrong with Postgres secret as URL_EDB_DB has some credentials missing");
            }
        } else {
            logger.error("Something is wrong with EDB Postgres environment has some credentials missing");
        }
        return success;
    }

    private Boolean readEDBPostgtresSecret() {

        Boolean success = false;

        logger.info("Reading EDB postgres secret....");

        if (System.getenv("EDB_POSTGRES_SECRET_PATH") != null && !System.getenv("EDB_POSTGRES_SECRET_PATH").isEmpty()) {

            String edbSecretPath = System.getenv("EDB_POSTGRES_SECRET_PATH");

            logger.info("EDB postgres secret path is " + edbSecretPath);

            String edbPostgresHost = readSecret(edbSecretPath + "/host");
            String edbPostgresPort = readSecret(edbSecretPath + "/port");
            String edbPostgresUsername = readSecret(edbSecretPath + "/username");
            String edbPostgresPassword = readSecret(edbSecretPath + "/password");
            String edbPostgresDbName = readSecret(edbSecretPath + "/dbname");

            setEDBPostgresHost(new String(edbPostgresHost));

            setEDBPostgresPort(new String(edbPostgresPort));

            setEDBPostgresUsername(new String(edbPostgresUsername));

            setEDBPostgresPassword(new String(edbPostgresPassword));

            setEDBPostgresDbname(new String(edbPostgresDbName));

            setFoundEDBPostgresEnv(true);
            success = true;
        } else {
            logger.error("Failed to get the EDB Postgres DB environments: EDB_POSTGRES_SECRET_PATH");
            throw new EDBPostgresException("Failed to get the EDB Postgres DB environments: EDB_POSTGRES_SECRET_PATH");
        }

        return success;
    }

    public Boolean createInitialTable(String sqlTable, String sqlCreateStatement) {
        Boolean success = false;
        Connection edb_connection = null;

        logger.info("Creating sql table : " + sqlTable + " if not exist...");

        PreparedStatement prepStmt = null;

        try {
            DataSource edb_ds = this.EDBBasicDS;
            edb_connection = edb_ds.getConnection();

            logger.info("edb_ds - " + edb_ds);
            logger.info("edb_connection - " + edb_connection);

            prepStmt = edb_connection.prepareStatement(sqlCreateStatement);

            if (!prepStmt.execute()) {
                success = true;
                logger.info("Successfully created checkPointTable");
            } else {
                return false;
            }
        } catch (Exception e) {
            logger.error("Error while creating " + sqlTable + " for EDB Postgres");
            logger.error(e.getClass().getName() + ": " + e.getMessage());
            throw new EDBPostgresException("Failed to create a " + sqlTable + " table - " + e.getMessage());
        } finally {
            if (edb_connection != null) {
                try {
                    prepStmt.close();
                    edb_connection.close();
                } catch (SQLException e) {
                    logger.error("Failed to close db connection ", e);
                }
            }
        }

        return success;
    }

    /**
     * Deletes the connection row from the specified sqlTable using {@code 'WHERE connectionKey = connectionUID'} as the
     * query condition.
     * 
     * @param sqlTable
     * @param connectionKey
     * @param connectionUID
     * 
     * @return True if deletion executed successfully.
     */
    public Boolean deleteConnectionFromEDBPostgres(String sqlTable, String connectionKey, String connectionUID) {
        Boolean success = false;
        Connection edb_connection = null;
        PreparedStatement prepStmt = null;

        String deleteSQL = String.format("DELETE FROM public.\"%s\" WHERE %s = ?", sqlTable, connectionKey);

        try {
            DataSource edb_ds = this.EDBBasicDS;
            edb_connection = edb_ds.getConnection();
            prepStmt = edb_connection.prepareStatement(deleteSQL);
            prepStmt.setString(1, connectionUID);
            int rowsUpdated = prepStmt.executeUpdate();

            if (rowsUpdated > 0) {

                logger.info("Connection has been removed successfully! connection_id - " + connectionUID + " from "
                        + sqlTable + " table");

                success = true;
            } else {

                logger.error("Failed to remove connection! connection_id - " + connectionUID + " from " + sqlTable
                        + " table");
            }

        } catch (SQLException e) {
            logger.error("Failed to delete connection from SQL query ", e);
        } finally {
            if (edb_connection != null) {
                try {
                    prepStmt.close();
                    edb_connection.close();
                } catch (SQLException e) {
                    logger.error("Failed to close db connection ", e);
                }
            }
        }

        return success;
    }

    /**
     * Deletes the connection from the default {@value #DEFAULT_CONNECTIONS_TABLE_NAME} table using
     * {@value #DEFAULT_QUERY_KEY} as the query key and {@code connectionUID} as the value
     * 
     * @param connectionUID
     * 
     * @return True if deletion executed successfully.
     */
    public Boolean deleteConnectionFromEDBPostgres(String connectionUID) {
        return deleteConnectionFromEDBPostgres(DEFAULT_CONNECTIONS_TABLE_NAME, DEFAULT_QUERY_KEY, connectionUID);
    }

    public Integer readCheckPointFromEDBPostgres(String connectionUID) {

        Connection connection = null;
        PreparedStatement prepStmt = null;
        ResultSet rs = null;
        Integer checkpoint_ts = 0;

        logger.info("readCheckPointFromPostgres() is triggered to read checkpoint value from postgres");

        String querySqlStatement = String.format("SELECT checkpoint_ts from %s WHERE %s = ?",
                DEFAULT_CONNECTIONS_TABLE_NAME, DEFAULT_QUERY_KEY);

        try {
            DataSource ds = this.EDBBasicDS;
            connection = ds.getConnection();

            logger.info("edb_ds - " + ds);
            logger.info("edb_connection - " + connection);

            prepStmt = connection.prepareStatement(querySqlStatement);

            prepStmt.setString(1, connectionUID);

            rs = prepStmt.executeQuery();

            if (rs.next()) {
                checkpoint_ts = rs.getInt("checkpoint_ts");
            } else {
                logger.warn("Checkpoint SQL query return empty result for connection_id - " + connectionUID
                        + " or this is a new historical data");
            }

        } catch (SQLException e) {
            logger.error("Failed to read checkpoint from SQL query ", e);
            throw new EDBPostgresException("Failed to read checkpoint from SQL query - " + e.getMessage());
        } finally {
            if (connection != null) {
                try {
                    prepStmt.close();
                    rs.close();
                    connection.close();
                } catch (SQLException e) {
                    logger.error("Failed to close db connection ", e);
                }
            }
        }

        return checkpoint_ts;
    }

    public Boolean saveCheckPointToEDBPostgres(String connection_id, String connection_type, String connection_name,
            JSONObject connection_config, Long checkpoint_ts) {
        Connection connection = null;
        Boolean success = false;
        PreparedStatement prepStmt = null;
        JSONObject connection_config_mask = new JSONObject();

        logger.info("A checkpoint is triggered to save : " + checkpoint_ts);

        connection_config_mask = maskPasswordAndKeys(connection_config);

        String createConnectionSql = String.format("INSERT INTO public.\"%s\" "
                + "(connection_id, connection_type, connection_name, configuration, checkpoint_ts, last_update_ts) "
                + "VALUES (?,?,?,?::json,?,?) ON CONFLICT(connection_id) DO UPDATE SET connection_type= EXCLUDED.connection_type, connection_name= EXCLUDED.connection_name, configuration= EXCLUDED.configuration, checkpoint_ts = EXCLUDED.checkpoint_ts, last_update_ts = EXCLUDED.last_update_ts;",
                DEFAULT_CONNECTIONS_TABLE_NAME);

        try {
            DataSource ds = this.EDBBasicDS;
            connection = ds.getConnection();

            prepStmt = connection.prepareStatement(createConnectionSql);

            Long last_update_ts = Instant.now().getEpochSecond();

            prepStmt.setString(1, !connection_id.isEmpty() ? connection_id.toString() : "empty_conn_id");
            prepStmt.setString(2, !connection_type.isEmpty() ? connection_type.toString() : "empty_type");
            prepStmt.setString(3, !connection_name.isEmpty() ? connection_name.toString() : "empty_name");
            prepStmt.setString(4,
                    !connection_config_mask.isEmpty() ? connection_config_mask.toString() : "empty_config");
            prepStmt.setLong(5, checkpoint_ts);
            prepStmt.setLong(6, last_update_ts);

            int rowsInserted = prepStmt.executeUpdate();

            if (rowsInserted > 0) {

                logger.info("A checkpoint was successfully saved! connection_id - " + connection_id.toString());
                success = true;

            } else {
                logger.error("Failed to save checkpoint ! connection_id - " + connection_id.toString());
            }

        } catch (SQLException e) {
            logger.error("Failed to save checkpoint from SQL query ", e);
            throw new EDBPostgresException("Failed to save checkpoint from SQL query - " + e.getMessage());
        } finally {
            if (connection != null) {
                try {
                    prepStmt.close();
                    connection.close();
                } catch (SQLException e) {
                    logger.error("Failed to close db connection ", e);
                }
            }
        }

        return success;
    }

    public String readSecret(String file) {
        String returnStr = "";
        try {
            File secrets_file = new File(file);
            if (!secrets_file.exists()) {
                logger.warn("No Secret File Found at " + file);
                return null;
            } else {
                // open input stream from /etc/zeno/secrets/edbpostgresif it exists
                // The secret file should be a single line of text with the full API Key in it
                FileInputStream inStream = new FileInputStream(secrets_file);
                BufferedReader br = new BufferedReader(new InputStreamReader(inStream));
                logger.info("Read Secret from " + file);
                returnStr = br.readLine();
            }
        } catch (Exception e) {
            logger.error("Failed to read secret from " + file, e);
            returnStr = null;
            throw new EDBPostgresException("Failed to read secret for EDB Postgres - " + e.getMessage());
        }
        return returnStr;
    }

    public static JSONObject maskPasswordAndKeys(JSONObject job) {
        return Util.maskSensitiveInfo(job);
    }

    public Boolean getFoundEDBPostgresEnv() {
        return foundEDBPostgresEnv;
    }

    public void setFoundEDBPostgresEnv(Boolean foundEDBPostgresEnv) {
        this.foundEDBPostgresEnv = foundEDBPostgresEnv;
    }

    public void setEDBDatasource(DataSource EDBBasicDS) {
        this.EDBBasicDS = (BasicDataSource) EDBBasicDS;
    }

    public DataSource getEDBDatasource() {
        return this.EDBBasicDS;
    }

    public void setEDBPostgresHost(String edbpostgresHost) {
        this.edbpostgresHost = edbpostgresHost;
    }

    public String getEDBPostgresHost() {
        return edbpostgresHost;
    }

    public void setEDBPostgresPort(String edbpostgresPort) {
        this.edbpostgresPort = edbpostgresPort;
    }

    public String getEDBPostgresPort() {
        return edbpostgresPort;
    }

    public void setEDBPostgresUsername(String edbpostgresUsername) {
        this.edbpostgresUsername = edbpostgresUsername;
    }

    public String getEDBPostgresUsername() {
        return edbpostgresUsername;
    }

    public void setEDBPostgresPassword(String edbpostgresPassword) {
        this.edbpostgresPassword = edbpostgresPassword;
    }

    public String getEDBPostgresPassword() {
        return edbpostgresPassword;
    }

    public void setEDBPostgresDbname(String edbpostgresDbname) {
        this.edbpostgresDbname = edbpostgresDbname;
    }

    public String getEDBPostgresDbname() {
        return edbpostgresDbname;
    }

}