<commit_before>package de.charite.compbio.exomiser.io;

import de.charite.compbio.exomiser.exception.ExomizerInitializationException;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * This class is meant to encapsulate operations needed to connect to the
 * Exomiser database, to enter new data and tables into the database, and to
 * extract statistics from the database. Note that we only use high-confidence
 * interactions, corresponding to those with a score of at least 0.7 (700).
 *
 * @see <a href="http://www.ncbi.nlm.nih.gov/pubmed/15608232">STRING: known and
 * predicted protein-protein associations, integrated and transferred across
 * organisms</a>
 * @version 0.17 (16 February, 2014)
 * @author <NAME>
 */
public class ExomiserDatabase {

    private static final Logger logger = LoggerFactory.getLogger(ExomiserDatabase.class);
    
    /**
     * Default database settings - these should be overwritten with the settings
     * in jdbc.properties
     */
    private static String driverName = "org.postgresql.Driver";

    /**
     * The database connection url
     */
    private static String url = "jdbc:postgresql://localhost/exomizer";

    private static String user = "exome";

    private static String password = "<PASSWORD>";

    /**
     * Set the connection url
     */
    public static void setURL(String URL) {
        ExomiserDatabase.url = URL;
    }

    /**
     * Registering the JDBC driver for the database.
     */
    private static void registerJdbcDriver(String driverClassName) throws ExomizerInitializationException {
        try {
            Class.forName(driverClassName);
        } catch (ClassNotFoundException cnfe) {
            String e = String.format("Couldn't find the driver class: %s", cnfe.toString());
            throw new ExomizerInitializationException(e);
        }
    }

    /**
     * @return true if there is already an entry for the given resource (then we
     * update), otherwise false (then we insert).
     */
    private static boolean versionEntryExists(String resource, Connection connection)
            throws SQLException {
        String query = String.format("SELECT COUNT(*) FROM metadata WHERE resource = ?;");
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, resource);
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            int c = rs.getInt(1);
            if (c == 0) {
                return false;
            } else {
                return true;
            }
        }
        return false;
    }

    /**
     * Update version information about a resource for which there is already a
     * column in the database.
     *
     * @param resource A string such as "human-phenotype-ontology.obo"
     * @param versionInfo A string such as "20-11-2013"
     * @param connection Connection to the postgreSQL Exomiser database.
     */
    private static void updateVersion(String resource, String versionInfo, Connection connection)
            throws SQLException {
        String update = "UPDATE metadata SET version = ? WHERE resource = ?;";
        PreparedStatement preparedStatement = connection.prepareStatement(update);

        preparedStatement.setString(1, versionInfo);
        preparedStatement.setString(2, resource);
        //System.out.println(preparedStatement);
        preparedStatement.executeUpdate();
    }

    /**
     * Insert new information about a resource for which there is already a
     * column in the database.
     *
     * @param resource A string such as "human-phenotype-ontology.obo"
     * @param versionInfo A string such as "20-11-2013"
     * @param connection Connection to the postgreSQL Exomiser database.
     */
    private static void insertVersion(String resource, String versionInfo, Connection connection)
            throws SQLException {
        String insert = "INSERT INTO metadata(resource,version) VALUES(?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement(insert);
        preparedStatement.setString(1, resource);
        preparedStatement.setString(2, versionInfo);
//        System.out.println(preparedStatement);
        preparedStatement.executeUpdate();
    }

    /**
     * Update or Insert new information about a resource for which there is
     * already a column in the database.
     *
     * @param resource A string such as "human-phenotype-ontology.obo"
     * @param versionInfo A string such as "20-11-2013"
     * @param connection Connection to the postgreSQL Exomiser database.
     */
    public static void updateVersionInfo(String resource, String versionInfo, Connection connection)
            throws ExomizerInitializationException {
        logger.info("Adding version information for {} version: {}", resource, versionInfo);

        try {
            if (versionEntryExists(resource, connection)) {
                updateVersion(resource, versionInfo, connection);
            } else {
                insertVersion(resource, versionInfo, connection);
            }
        } catch (SQLException e) {
            String error = "Problem connecting to SQL Exomizer Database: "
                    + e.toString();
            throw new ExomizerInitializationException(error);
        }
    }

    /**
     * Connect to the configured database.
     *
     * @return the database connection.
     * @throws ExomizerInitializationException
     */
    public static Connection openNewDatabaseConnection()
            throws ExomizerInitializationException {
        try {

            Properties properties = new Properties();
            try {
                properties.load(new FileInputStream("jdbc.properties"));
                //set the connection details here
                driverName = properties.getProperty("exomiser.driverClassName");
                url = properties.getProperty("exomiser.url");
                user = properties.getProperty("exomiser.username");
                password = properties.getProperty("exomiser.password");

            } catch (IOException e) {
                String propertiesError = "Unable to find the jdbc.properties file";
                throw new ExomizerInitializationException(propertiesError + e);
            }
            
            registerJdbcDriver(driverName);
            Connection c = DriverManager.getConnection(url, user, password);
            if (url.startsWith("jdbc:postgresql:")){
                    String sql = "set search_path to 'EXOMISER'";
                    PreparedStatement searchPathStatement = c.prepareStatement(sql);
                    searchPathStatement.execute();
                }
            return c;
<commit_msg>Fixed error where jdbc.properties is not found when running exomiser from outside of the classpath.<commit_after>package de.charite.compbio.exomiser.io;

import de.charite.compbio.exomiser.exception.ExomizerInitializationException;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * This class is meant to encapsulate operations needed to connect to the
 * Exomiser database, to enter new data and tables into the database, and to
 * extract statistics from the database. Note that we only use high-confidence
 * interactions, corresponding to those with a score of at least 0.7 (700).
 *
 * @see <a href="http://www.ncbi.nlm.nih.gov/pubmed/15608232">STRING: known and
 * predicted protein-protein associations, integrated and transferred across
 * organisms</a>
 * @version 0.17 (16 February, 2014)
 * @author <NAME>
 */
public class ExomiserDatabase {

    private static final Logger logger = LoggerFactory.getLogger(ExomiserDatabase.class);
    
    /**
     * Default database settings - these should be overwritten with the settings
     * in jdbc.properties
     */
    private static String driverName = "org.postgresql.Driver";

    /**
     * The database connection url
     */
    private static String url = "jdbc:postgresql://localhost/exomizer";

    private static String user = "exome";

    private static String password = "<PASSWORD>";

    /**
     * Set the connection url
     */
    public static void setURL(String URL) {
        ExomiserDatabase.url = URL;
    }

    /**
     * Registering the JDBC driver for the database.
     */
    private static void registerJdbcDriver(String driverClassName) throws ExomizerInitializationException {
        logger.info("Registering JDBC driver: {}", driverClassName);
        try {
            Class.forName(driverClassName);
        } catch (ClassNotFoundException cnfe) {
            String e = String.format("Couldn't find the driver class: %s", cnfe.toString());
            throw new ExomizerInitializationException(e);
        }
    }

    /**
     * @return true if there is already an entry for the given resource (then we
     * update), otherwise false (then we insert).
     */
    private static boolean versionEntryExists(String resource, Connection connection)
            throws SQLException {
        String query = String.format("SELECT COUNT(*) FROM metadata WHERE resource = ?;");
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, resource);
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            int c = rs.getInt(1);
            if (c == 0) {
                return false;
            } else {
                return true;
            }
        }
        return false;
    }

    /**
     * Update version information about a resource for which there is already a
     * column in the database.
     *
     * @param resource A string such as "human-phenotype-ontology.obo"
     * @param versionInfo A string such as "20-11-2013"
     * @param connection Connection to the postgreSQL Exomiser database.
     */
    private static void updateVersion(String resource, String versionInfo, Connection connection)
            throws SQLException {
        String update = "UPDATE metadata SET version = ? WHERE resource = ?;";
        PreparedStatement preparedStatement = connection.prepareStatement(update);

        preparedStatement.setString(1, versionInfo);
        preparedStatement.setString(2, resource);
        //System.out.println(preparedStatement);
        preparedStatement.executeUpdate();
    }

    /**
     * Insert new information about a resource for which there is already a
     * column in the database.
     *
     * @param resource A string such as "human-phenotype-ontology.obo"
     * @param versionInfo A string such as "20-11-2013"
     * @param connection Connection to the postgreSQL Exomiser database.
     */
    private static void insertVersion(String resource, String versionInfo, Connection connection)
            throws SQLException {
        String insert = "INSERT INTO metadata(resource,version) VALUES(?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement(insert);
        preparedStatement.setString(1, resource);
        preparedStatement.setString(2, versionInfo);
//        System.out.println(preparedStatement);
        preparedStatement.executeUpdate();
    }

    /**
     * Update or Insert new information about a resource for which there is
     * already a column in the database.
     *
     * @param resource A string such as "human-phenotype-ontology.obo"
     * @param versionInfo A string such as "20-11-2013"
     * @param connection Connection to the postgreSQL Exomiser database.
     */
    public static void updateVersionInfo(String resource, String versionInfo, Connection connection)
            throws ExomizerInitializationException {
        logger.info("Adding version information for {} version: {}", resource, versionInfo);

        try {
            if (versionEntryExists(resource, connection)) {
                updateVersion(resource, versionInfo, connection);
            } else {
                insertVersion(resource, versionInfo, connection);
            }
        } catch (SQLException e) {
            String error = "Problem connecting to SQL Exomizer Database: "
                    + e.toString();
            throw new ExomizerInitializationException(error);
        }
    }

    /**
     * Connect to the configured database.
     *
     * @return the database connection.
     * @throws ExomizerInitializationException
     */
    public static Connection openNewDatabaseConnection()
            throws ExomizerInitializationException {
        try {
            Properties properties = new Properties();
            try {
                properties.load(ExomiserDatabase.class.getResourceAsStream("/jdbc.properties"));
                //set the connection details here
                driverName = properties.getProperty("exomiser.driverClassName");
                url = properties.getProperty("exomiser.url");
                user = properties.getProperty("exomiser.username");
                password = properties.getProperty("<PASSWORD>");

            } catch (IOException e) {
                logger.error("Unable to find the jdbc.properties file", e);
            }
            registerJdbcDriver(driverName);
            logger.info("Trying to connect as user {} to {}", user, url);
            Connection c = DriverManager.getConnection(url, user, password);
            if (url.startsWith("jdbc:postgresql:")){
                    String sql = "set search_path to 'EXOMISER'";
                    PreparedStatement searchPathStatement = c.prepareStatement(sql);
                    searchPathStatement.execute();
                }
            return c;

{'fraction_non_alphanumeric': 0.0875966888315918, 'fraction_numerical': 0.007463699280770796, 'mean_word_length': 2.6582278481012658, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 12, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3635795', 'n_tokens_mistral': 3740, 'n_tokens_neox': 3586, 'n_words': 1116}