001 /** 002 * Copyright (C) 2010 The Roslin Institute <contact andy.law@roslin.ed.ac.uk> 003 * 004 * This file is part of the Ensembl Java API demonstration project developed by the 005 * Bioinformatics Group at The Roslin Institute, The Royal (Dick) School of 006 * Veterinary Studies, University of Edinburgh. 007 * 008 * This is free software: you can redistribute it and/or modify 009 * it under the terms of the GNU General Public License (version 3) as published by 010 * the Free Software Foundation. 011 * 012 * This software is distributed in the hope that it will be useful, 013 * but WITHOUT ANY WARRANTY; without even the implied warranty of 014 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 015 * GNU General Public License for more details. 016 * 017 * You should have received a copy of the GNU General Public License 018 * in this software distribution. If not, see <http://www.gnu.org/licenses/gpl-3.0.html/>. 019 */ 020 package uk.ac.roslin.ensembl.config; 021 022 import java.io.PrintWriter; 023 import java.io.Serializable; 024 import java.sql.Connection; 025 import java.sql.DriverManager; 026 import java.sql.SQLException; 027 import java.sql.SQLWarning; 028 import java.util.Arrays; 029 import java.util.Enumeration; 030 import java.util.Locale; 031 import java.util.Properties; 032 import java.util.ResourceBundle; 033 import java.util.logging.Level; 034 import org.apache.log4j.Logger; 035 import uk.ac.roslin.ensembl.exception.ConfigurationException; 036 037 public class DBConnection { 038 039 protected String newline = (System.getProperty("line.separator") != null) ? System.getProperty("line.separator") : "\r\n"; 040 String datasourceStyle = "ensembldb"; 041 String driver = null; 042 String url = null; 043 String username = "anonymous"; 044 String password = null; 045 String[] invalidDBMatches = null; 046 String[] invalidDBContains = null; 047 String[] invalidDBStart = null; 048 String[] invalidDBEnd = null; 049 DataSource configuredDatasource; 050 Properties dbProperties; 051 final static Logger LOGGER = Logger.getLogger(DBConnection.class); 052 053 public static enum DataSource implements Serializable { 054 055 ENSEMBLDB("EnsemblDB"), 056 ENSEMBLDB_ARCHIVES("EnsemblDB Archives"), 057 ENSEMBLGENOMES("EnsemblGenomes"), 058 LOCAL("Local Data Source"); 059 private String label; 060 061 DataSource(String label) { 062 this.label = label; 063 } 064 065 @Override 066 public String toString() { 067 return label; 068 } 069 } 070 071 public DBConnection(DataSource datasource) throws ConfigurationException { 072 configuredDatasource = datasource; 073 if (datasource == DataSource.ENSEMBLDB) { 074 dbProperties = this.readResource("uk.ac.roslin.ensembl.configfiles.ensembldb"); 075 } else if (datasource == DataSource.ENSEMBLDB_ARCHIVES) { 076 dbProperties = this.readResource("uk.ac.roslin.ensembl.configfiles.ensembldb_archives"); 077 } else if (datasource == DataSource.ENSEMBLGENOMES) { 078 dbProperties = this.readResource("uk.ac.roslin.ensembl.configfiles.ensemblgenomes"); 079 } else { 080 throw new ConfigurationException("Unrecognized type of DataSource specified for DBConnection"); 081 } 082 initialize(); 083 } 084 085 public Properties getConfigurationProperties() { 086 return dbProperties; 087 } 088 089 public DBConnection(DataSource datasource, Properties local_datasource) throws ConfigurationException { 090 if (datasource != DataSource.LOCAL || local_datasource == null) { 091 throw new ConfigurationException("Invalid attempt to configure DBConnection with unrecognized local DataSource."); 092 } 093 configuredDatasource = datasource; 094 dbProperties = local_datasource; 095 initialize(); 096 } 097 098 private Properties readResource(String id) throws ConfigurationException { 099 Properties p = null; 100 try { 101 //NB: need to pass in the classloader to get this to work in a test environment! 102 ResourceBundle rb = ResourceBundle.getBundle(id, Locale.getDefault(), 103 this.getClass().getClassLoader()); 104 p = new Properties(); 105 for (Enumeration keys = rb.getKeys(); keys.hasMoreElements();) { 106 final String key = (String) keys.nextElement(); 107 final String value = rb.getString(key); 108 p.put(key, value); 109 } 110 } catch (Exception ex) { 111 throw new ConfigurationException("System can't read the configuration file: " + id); 112 } 113 return p; 114 } 115 116 private void initialize() { 117 118 119 if (dbProperties.getProperty("invalidDBStartsWith") != null 120 && !dbProperties.getProperty("invalidDBStartsWith").trim().isEmpty()) { 121 invalidDBStart = dbProperties.getProperty("invalidDBStartsWith").split(" "); 122 } 123 if (dbProperties.getProperty("invalidDBEndsWith") != null 124 && !dbProperties.getProperty("invalidDBEndsWith").trim().isEmpty()) { 125 invalidDBEnd = dbProperties.getProperty("invalidDBEndsWith").split(" "); 126 } 127 if (dbProperties.getProperty("invalidDBContains") != null 128 && !dbProperties.getProperty("invalidDBContains").trim().isEmpty()) { 129 invalidDBContains = dbProperties.getProperty("invalidDBContains").split(" "); 130 } 131 if (dbProperties.getProperty("invalidDBMatches") != null 132 && !dbProperties.getProperty("invalidDBMatches").trim().isEmpty()) { 133 invalidDBMatches = dbProperties.getProperty("invalidDBMatches").split(" "); 134 } 135 136 if (LOGGER.isInfoEnabled()) { 137 if (this.invalidDBContains != null) { 138 LOGGER.info("skipping databases containing String: " + Arrays.toString(this.invalidDBContains)); 139 } 140 if (this.invalidDBMatches != null) { 141 LOGGER.info("skipping databases matching String: " + Arrays.toString(this.invalidDBMatches)); 142 } 143 if (this.invalidDBStart != null) { 144 LOGGER.info("skipping databases beginning with String: " + Arrays.toString(this.invalidDBStart)); 145 } 146 if (this.invalidDBEnd != null) { 147 LOGGER.info("skipping databases ending with String: " + Arrays.toString(this.invalidDBEnd)); 148 } 149 } 150 151 if (dbProperties.getProperty("datasource") != null 152 && !dbProperties.getProperty("datasource").trim().isEmpty()) { 153 datasourceStyle = dbProperties.getProperty("datasource").trim(); 154 } 155 if (dbProperties.getProperty("url") != null 156 && !dbProperties.getProperty("url").trim().isEmpty()) { 157 url = dbProperties.getProperty("url").trim(); 158 } 159 if (dbProperties.getProperty("driver") != null 160 && !dbProperties.getProperty("driver").trim().isEmpty()) { 161 driver = dbProperties.getProperty("driver").trim(); 162 } 163 if (dbProperties.getProperty("username") != null 164 && !dbProperties.getProperty("username").trim().isEmpty()) { 165 username = dbProperties.getProperty("username").trim(); 166 } 167 if (dbProperties.getProperty("password") != null 168 && !dbProperties.getProperty("password").trim().isEmpty()) { 169 password = dbProperties.getProperty("password").trim(); 170 } 171 172 if (LOGGER.isInfoEnabled()) { 173 LOGGER.info("datasource: " + datasourceStyle); 174 LOGGER.info("driver: " + driver); 175 LOGGER.info("url: " + url); 176 LOGGER.info("username: " + username); 177 LOGGER.info("password: " + password); 178 } 179 180 } 181 182 public Boolean isDBNameValid(String s) { 183 if (this.invalidDBContains != null && this.invalidDBContains.length > 0) { 184 for (int i = 0; i < this.invalidDBContains.length; i++) { 185 if (s.contains(this.invalidDBContains[i])) { 186 return false; 187 } 188 } 189 } 190 if (this.invalidDBStart != null && this.invalidDBStart.length > 0) { 191 for (int i = 0; i < this.invalidDBStart.length; i++) { 192 if (s.startsWith(this.invalidDBStart[i])) { 193 return false; 194 } 195 } 196 } 197 if (this.invalidDBEnd != null && this.invalidDBEnd.length > 0) { 198 for (int i = 0; i < this.invalidDBEnd.length; i++) { 199 if (s.endsWith(this.invalidDBEnd[i])) { 200 return false; 201 } 202 } 203 } 204 if (this.invalidDBMatches != null && this.invalidDBMatches.length > 0) { 205 for (int i = 0; i < this.invalidDBMatches.length; i++) { 206 if (s.equalsIgnoreCase(this.invalidDBMatches[i])) { 207 return false; 208 } 209 } 210 } 211 return true; 212 } 213 214 public String getDatasourceStyle() { 215 return datasourceStyle; 216 } 217 218 public String report() { 219 String out = "DB Connection Report" + newline + "--------------"+newline+newline; 220 221 out = out + "Datasource Configured: " + configuredDatasource.toString() + newline; 222 out = out + "Datasource style: " + datasourceStyle + newline; 223 out = out + "Datasource URL: " + url + newline; 224 out = out + "Datasource user: " + username + newline; 225 226 if (this.invalidDBContains != null) { 227 out = out.concat("Skipping databases containing String: " + Arrays.toString(this.invalidDBContains)+newline); 228 } 229 if (this.invalidDBMatches != null) { 230 out = out.concat("Skipping databases matching String: " + Arrays.toString(this.invalidDBMatches)+newline); 231 } 232 if (this.invalidDBStart != null) { 233 out = out.concat("Skipping databases beginning with String: " + Arrays.toString(this.invalidDBStart)+newline); 234 } 235 if (this.invalidDBEnd != null) { 236 out = out.concat("Skipping databases ending with String: " + Arrays.toString(this.invalidDBEnd)+newline); 237 } 238 239 out = out+newline; 240 return out; 241 } 242 243 public String testConnection() { 244 String warning = ""; 245 246 try { 247 Class.forName("com.mysql.jdbc.Driver"); 248 PrintWriter w = new PrintWriter(System.err); 249 DriverManager.setLogWriter(w); 250 Connection conn = DriverManager.getConnection(url, username, password); 251 252 SQLWarning warn = conn.getWarnings(); 253 254 if (warn != null) { 255 warning = "WARNING:\n"; 256 } 257 258 while (warn != null) { 259 warning += "SQLState: " + warn.getSQLState() + "\n"; 260 warning += "Message: " + warn.getMessage() + "\n"; 261 warning += "Vendor: " + warn.getErrorCode() + "\n"; 262 warn = warn.getNextWarning(); 263 } 264 265 266 conn.close(); 267 268 } catch (ClassNotFoundException e) { 269 warning += "ERROR: Can't load driver\n" + e; 270 } catch (SQLException e) { 271 warning += "ERROR: Database access failed\n" + e; 272 } 273 if (warning.isEmpty()) { 274 return "OK"; 275 } else { 276 return warning; 277 } 278 279 } 280 281 public boolean isConnectionOK() { 282 283 try { 284 Class.forName("com.mysql.jdbc.Driver"); 285 PrintWriter w = new PrintWriter(System.err); 286 DriverManager.setLogWriter(w); 287 Connection conn = DriverManager.getConnection(url, username, password); 288 289 SQLWarning warn = conn.getWarnings(); 290 291 if (warn != null) { 292 return false; 293 } 294 conn.close(); 295 } catch (Exception e) { 296 return false; 297 } 298 299 return true; 300 301 } 302 303 304 305 }