/* Copyright (C) 2012 Joachim Baran Copyright (C) 2003 EBI, GRL This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version. This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ package org.ensembl.mart.lib; import java.io.ByteArrayOutputStream; import java.io.PrintStream; import java.io.PrintWriter; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.logging.Logger; //import javax.naming.ConfigurationException; import javax.sql.DataSource; import javax.swing.JOptionPane; import org.ewin.common.util.Log; import org.ewin.javax.sql.DefaultPoolingAlgorithm; import org.ewin.javax.sql.DriverManagerDataSource; import org.ewin.javax.sql.PoolingAlgorithmDataSource; /** * Datasource with extra functionality: * * <ul> * <li> parameters are available via getters. * <li> lazy loads connection (useful when used offline and connection not needed) * <li> offers connection pooling * <li> implements toString() which prints something user friendly. * </ul> * * Users should <code>Connection conn = dataSource.getConnection()</code> * to retrieve a connection from the pool and <code>conn.close()</code> to return it to the pool. * * @author <a href="mailto:craig@ebi.ac.uk">Craig Melsopp</a> * @author <a href="mailto:dlondon@ebi.ac.uk">Darin London</a> */ public class DetailedDataSource implements DataSource { // Disable logging from ewin connection pool package static { //temporarily disable stdout PrintStream n = new PrintStream(new ByteArrayOutputStream()); PrintStream o = new PrintStream(System.out); System.setOut(n); // Configure Ewin logging - remove all defined loggers Iterator ewinLoggers = Log.loggers(); while (ewinLoggers.hasNext()) { Log.removeLogger((Log.Logger) ewinLoggers.next()); } //reset stdout System.setOut(o); } private final static Logger logger = Logger.getLogger(DetailedDataSource.class.getName()); public static final String DEFAULTDATABASETYPE = "mysql"; public static final String DEFAULTDRIVER = "com.mysql.jdbc.Driver"; public static final int DEFAULTPOOLSIZE = 10; public static final String DEFAULTPORT = "3306"; public static final String VERSION = "0.4"; private static final String ORACLEAT = "@"; public static final String ORACLE = "oracle"; public static final String POSTGRES = "postgres"; public static final String ORACLEDRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String POSTGRESDRIVER = "org.postgresql.Driver"; private static final String SYBASE = "sybase:Tds"; private static final String SQLSERVER = "sqlserver"; private static final String SQLSERVERDRIVER = "net.sourceforge.jtds.jdbc.Driver"; private String databaseType; private String host; private String port; private String databaseName; private String schema; private int maxPoolSize; private String password; private String user; private String martUser; private String jdbcDriverClassName; private String name; private DataSource dataSource; private String connectionString; /** * Creates a datasource backed by a connection pool. connectionString should * match the host, port, and dbType. * * @param dbType database type e.g. mysql. * @param host host name e.g. ensembldb.ensembl.org * @param port port number. e.g. 3306. * @param database name of database on database server, can be null for "meta" queries e.g. what databasea are available * @param connectionString database connectionString, e.g. jdbc:mysql://ensembldb.ensembl.org:3036 * @param user username * @param password password, can be null * @param maxPoolSize maximum poolsize * @param jdbcDriverClassName name of jdbc driver to back the datasource. * @param displayName displayName for datasource, if null a default is set **/ public DetailedDataSource( String dbType, String host, String port, String databaseName, String schema, String connectionString, String user, String martUser, String password, int maxPoolSize, String jdbcDriverClassName, String displayName) { /** assert dbType != null : "dbType is null"; assert host != null : "host is null"; assert port != null : "port is null"; assert connectionString != null : "connectionString is null"; assert databaseName == null || connectionString.indexOf(databaseName) != -1 : "database is null or is not in connection string"; assert schema != null : "schema is null"; assert user != null : "user is null"; assert maxPoolSize >= 0; assert jdbcDriverClassName != null : "jdbcDriver is null"; */ this.databaseType = dbType; this.host = host; this.port = port; this.databaseName = databaseName; this.schema = schema; this.connectionString = connectionString; this.user = user; this.martUser = martUser; this.password = password; this.maxPoolSize = maxPoolSize; this.jdbcDriverClassName = jdbcDriverClassName; this.name = displayName; if ( this.name==null ) this.name = defaultName(); //logger.warning(this.toString()); } /** * Creates a datasource backed by a connection pool. connectionString should * match the host, port, and dbType. Sets default displayName. * * @param dbType database type e.g. mysql. * @param host host name e.g. ensembldb.ensembl.org * @param port port number. e.g. 3306. * @param database name of database on database server, can be null for "meta" queries e.g. what databasea are available * @param connectionString database connectionString, e.g. jdbc:mysql://ensembldb.ensembl.org:3036 * @param user username * @param password password, can be null * @param maxPoolSize maximum poolsize * @param jdbcDriverClassName name of jdbc driver to back the datasource. **/ public DetailedDataSource( String dbType, String host, String port, String databaseName, String schema, String connectionString, String user, String password, int maxPoolSize, String jdbcDriverClassName) { this(dbType, host,port, databaseName, schema,connectionString, user, "", password, maxPoolSize, jdbcDriverClassName, null); } /** * Convenience method which calls createDataSource(DEFAULTDATABASETYPE, host, DEFAULTPORT, database, user, password, DEFAULTPOOLSIZE, DEFAULTDRIVER); * * @param host host name e.g. ensembldb.ensembl.org * @param database name of database on database server * @param user username * @param password password, can be null * @throws ConfigurationException thrown if a problem occurs creating the datasource */ public DetailedDataSource( String host, String database, String schema, String user, String password) { this( DEFAULTDATABASETYPE, host, DEFAULTPORT, database, schema, user, password, DEFAULTPOOLSIZE, DEFAULTDRIVER, null); } /** * Datasource constructed by specific parameters. The connection is automatically * derived from the host, port and databaseType. * * @param databaseType database type e.g. mysql. * @param host host name e.g. ensembldb.ensembl.org * @param port port number. e.g. 3306. * @param database name of database on database server * @param user username * @param password password, can be null * @param maxPoolSize maximum poolsize. * @param jdbcDriverClassName name of jdbc driver to back the datasource. */ public DetailedDataSource( String databaseType, String host, String port, String database, String schema, String user, String password, int maxPoolSize, String jdbcDriverClassName) { this(databaseType, host, port, database, schema,user, password, maxPoolSize, jdbcDriverClassName, defaultName(host, port, database, schema,user)); } /** * Datasource constructed by specific parameters. The connection is automatically * derived from the host, port and databaseType. * * @param databaseType database type e.g. mysql. * @param host host name e.g. ensembldb.ensembl.org * @param port port number. e.g. 3306. * @param database name of database on database server * @param user username * @param password password, can be null * @param maxPoolSize maximum poolsize. * @param jdbcDriverClassName name of jdbc driver to back the datasource. * @param displayName name to give to DetailedDataSource */ public DetailedDataSource( String databaseType, String host, String port, String database, String schema, String user, String password, int maxPoolSize, String jdbcDriverClassName, String displayName) { this( databaseType, host, port, database, schema, connectionURL(databaseType, host, port, database), user, "", password, maxPoolSize, jdbcDriverClassName, displayName); } /** * Returns a connection URL for jdbc. This could differ from RDBMS to RDBMS. * Currently supports oracle, mysql, postgres and any other * database whose connnection URL syntax matches one of these. The dbType will produce * different connection strings: * <ul> * <li>oracle:thin --> jdbc:oracle:thin:@host:port:dbname * <li>sybase:Tds --> jdbc:sybase:Tds:host:port/dbname * <li>postgresSQL/mySQL --> jdbc:x://host:port/dbname * <li>MS SQL Server --> jdbc:jtds:sqlserver://host:port/dbname * </ul> * * @param databaseType database type e.g. mysql. * @param host host name e.g. ensembldb.ensembl.org * @param port port number. e.g. 3306. * @param databaseName of database on database server * @return String connectionURL */ public static String connectionURL( String dbType, String host, String port, String databaseName) { if (dbType.equals(ORACLE)) { host = ORACLEAT + host; databaseName = ":" + databaseName; } else if (dbType.equals(SYBASE)) { databaseName = "/" + databaseName; } else { host = "//" + host; databaseName = "/" + databaseName; } StringBuffer dbURL = new StringBuffer(); //defaults to oracle:thin, the driver settings needs to be done nicer if (dbType.equals(ORACLE)) dbType="oracle:thin"; else if (dbType.equals(SQLSERVER)) dbType="jtds:sqlserver"; else if (dbType.equals(POSTGRES)) dbType="postgresql"; dbURL.append("jdbc:").append(dbType).append(":"); dbURL.append(host); if (port != null && !"".equals(port)) dbURL.append(":").append(port); if (databaseName != null && !databaseName.equals("")) dbURL.append(databaseName); //System.out.println("CONNECTING: "+dbURL.toString()); return dbURL.toString(); } public static String getJDBCDriverClassNameFor(String databaseType) { if (databaseType == null) return null; else if (databaseType.equals(DEFAULTDATABASETYPE)) return DEFAULTDRIVER; else if (databaseType.equals(ORACLE)) return ORACLEDRIVER; else if (databaseType.equals(POSTGRES)) return POSTGRESDRIVER; else if (databaseType.equals(SQLSERVER)) return SQLSERVERDRIVER; else return null; //add new ones as needed } /** * Convenience method for closing a connection and handling any SQLException * by printing a stack trace. * * @param conn connection to be closed, method does nothing if conn=null. */ public static void close(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public String[] databaseNames() throws SQLException { List databases = new ArrayList(); Connection conn = getConnection(); // TODO check this works with non-mysql databases ResultSet rs = conn.createStatement().executeQuery("show databases"); while (rs.next()) { databases.add(rs.getString(1)); } close(conn); return (String[]) databases.toArray(new String[databases.size()]); } /** * @return databaseName@host:port */ public String defaultName() { return defaultName(host, port, databaseName,schema, user); } /** * @return databaseName@host:port */ public static String defaultName(String host, String port, String databaseName, String schema,String user) { return user +"/" +databaseName + "@" + host + ":" + port; } /** * A connection pool is created when this merthod is first called * and then connections are returned from it. * * @return Connection to the database specified. * @throws java.sql.SQLException if any problem occurs making the connection. */ public Connection getConnectionNoVersionCheck() throws SQLException { if (dataSource == null) { try { // load driver //System.out.println ("lodading ..."+jdbcDriverClassName); Class.forName(jdbcDriverClassName).newInstance(); dataSource = new DriverManagerDataSource( jdbcDriverClassName, connectionString, user, password); // Wrap data source in connection pool PoolingAlgorithmDataSource tmp = new PoolingAlgorithmDataSource(dataSource); DefaultPoolingAlgorithm poolAlgorithm = new DefaultPoolingAlgorithm(); poolAlgorithm.setPoolMax(maxPoolSize); tmp.setPoolingAlgorithm(poolAlgorithm); dataSource = tmp; } catch (InstantiationException e) { e.printStackTrace(); throw new SQLException( "Failed to initialise database connection pool " + "(is the connection pool jar available?) : "); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new SQLException( "Failed to initialise database connection pool for " + jdbcDriverClassName + " (is the connection pool jar available?) : "); } catch (IllegalAccessException e) { e.printStackTrace(); throw new SQLException( "Failed to initialise database connection pool " + "(is the connection pool jar available?) : "); } catch (NoClassDefFoundError e) { e.printStackTrace(); throw new SQLException( "Failed to initialise database connection pool " + "(is the connection pool jar available?) : "); } } return dataSource.getConnection(); } /** * A connection pool is created when this merthod is first called * and then connections are returned from it. * * @return Connection to the database specified. * @throws java.sql.SQLException if any problem occurs making the connection. */ public Connection getConnection() throws SQLException { try { try { // load driver if (dataSource != null) { return dataSource.getConnection(); } //System.out.println ("lodading ..."+jdbcDriverClassName); Class.forName(jdbcDriverClassName).newInstance(); dataSource = new DriverManagerDataSource( jdbcDriverClassName, connectionString, user, password); // Wrap data source in connection pool PoolingAlgorithmDataSource tmp = new PoolingAlgorithmDataSource(dataSource); DefaultPoolingAlgorithm poolAlgorithm = new DefaultPoolingAlgorithm(); poolAlgorithm.setPoolMax(maxPoolSize); tmp.setPoolingAlgorithm(poolAlgorithm); dataSource = tmp; Connection conn; String version = null; //try { conn = dataSource.getConnection(); // remove version check /* ResultSet vr = conn.getMetaData().getTables(conn.getCatalog(), this.schema, "meta_version__version__main", null); //expect at most one result, if no results, tcheck will remain null String tcheck = null; if (vr.next()) tcheck = vr.getString(3); vr.close(); if (tcheck == null) {// don't check databases with no version table yet return conn; } String[] schemas = null; if(getDatabaseType().equals("oracle")) schemas = getSchema().toUpperCase().split(";"); else schemas = getSchema().split(";"); // remove version check PreparedStatement ps = conn.prepareStatement("select version from "+schemas[0]+".meta_version__version__main"); ResultSet rs = ps.executeQuery(); rs.next(); version = rs.getString(1); rs.close(); if (!version.equals(VERSION)){ throw new SQLException("Database version "+version+" and software version "+VERSION+" do not match"); } */ return conn; } catch (InstantiationException e) { e.printStackTrace(); throw new SQLException( "Failed to initialise database connection pool " + "(is the connection pool jar available?) : "); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new SQLException( "Failed to initialise database connection pool for " + jdbcDriverClassName + " (is the connection pool jar available?) : "); } catch (IllegalAccessException e) { e.printStackTrace(); throw new SQLException( "Failed to initialise database connection pool " + "(is the connection pool jar available?) : "); } catch (NoClassDefFoundError e) { e.printStackTrace(); throw new SQLException( "Failed to initialise database connection pool " + "(is the connection pool jar available?) : "); } } catch (SQLException e) { String message = e.getMessage(); if (message.indexOf('\n')>=0) message = message.substring(0, message.indexOf('\n')); JOptionPane.showMessageDialog(null,message); //return null; //e.printStackTrace(); throw e; } } /* Connection conn; String version = null; try { conn = dataSource.getConnection(); ResultSet vr = conn.getMetaData().getTables(conn.getCatalog(), this.schema, "meta_version__version__main", null); //expect at most one result, if no results, tcheck will remain null String tcheck = null; if (vr.next()) tcheck = vr.getString(3); vr.close(); if (tcheck == null) {// don't check databases with no version table yet return conn; } String[] schemas = null; if(getDatabaseType().equals("oracle")) schemas = getSchema().toUpperCase().split(";"); else schemas = getSchema().split(";"); PreparedStatement ps = conn.prepareStatement("select version from "+schemas[0]+".meta_version__version__main"); ResultSet rs = ps.executeQuery(); rs.next(); version = rs.getString(1); rs.close(); if (!version.equals(VERSION)){ throw new SQLException("Database version "+version+" and software version "+VERSION+" do not match"); } return conn; } catch (SQLException e) { JOptionPane.showMessageDialog(null,"Include a correct meta_version__version__main table entry:" + e); throw new SQLException(""); } * */ /** * @param username * @param password * @return Connection * @throws java.sql.SQLException */ public Connection getConnection(String username, String password) throws SQLException { return dataSource.getConnection(username, password); } /** * @return int loginTimeout * @throws java.sql.SQLException */ public int getLoginTimeout() throws SQLException { return dataSource.getLoginTimeout(); } /** * @return PrintWriter logWriter * @throws java.sql.SQLException */ public PrintWriter getLogWriter() throws SQLException { return dataSource.getLogWriter(); } /** * @param seconds * @throws java.sql.SQLException */ public void setLoginTimeout(int seconds) throws SQLException { dataSource.setLoginTimeout(seconds); } /** * @param out * @throws java.sql.SQLException */ public void setLogWriter(PrintWriter out) throws SQLException { dataSource.setLogWriter(out); } /** * @return String databaseName */ public String getDatabaseName() { return databaseName; } /** * @return String databaseType */ public String getDatabaseType() { return databaseType; } /** * @return DataSource dataSource */ public DataSource getDatasource() { return dataSource; } /** * @return String name */ public String getName() { return name; } public String getSchema() { return schema; } /** * @return String user */ public String getUser() { return user; } /** * @return String user */ public String getMartUser() { return martUser; } /** * @return String host */ public String getHost() { return host; } /** * @return String jdbcDriverClassName */ public String getJdbcDriverClassName() { return jdbcDriverClassName; } /** * @return String port */ public String getPort() { return port; } /** * @return String JDBC Connection String */ public String getConnectionString() { return connectionString; } /** * @return int maxPoolSize */ public int getMaxPoolSize() { return maxPoolSize; } /** * @return String password */ public String getPassword() { return password; } /* (non-Javadoc) * @see java.lang.Object#toString() */ public String toString() { StringBuffer buf = new StringBuffer(); buf.append("["); buf.append(" databaseType=").append(databaseType); buf.append(", host=").append(host); buf.append(", port=").append(port); buf.append(", databaseName=").append(databaseName); buf.append(", schema=").append(schema); buf.append(", maxPoolSize=").append(maxPoolSize); buf.append(", password=").append(password); buf.append(", user=").append(user); buf.append(", jdbcDriverClassName=").append(jdbcDriverClassName); buf.append(", displayName=").append(name); buf.append(", dataSource=").append(dataSource); buf.append(", connectionString=").append(connectionString); buf.append("]"); return buf.toString(); } public boolean isWrapperFor(Class clazz) throws SQLException { return false; } public Object unwrap(Class clazz) throws SQLException { return null; } }