/*
* Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
* Copyright [2016-2017] EMBL-European Bioinformatics Institute
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/*
* Copyright (C) 2004 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.healthcheck.testcase;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;
import java.util.logging.Logger;
import org.apache.commons.lang.StringUtils;
import org.ensembl.healthcheck.DatabaseRegistry;
import org.ensembl.healthcheck.DatabaseRegistryEntry;
import org.ensembl.healthcheck.DatabaseType;
import org.ensembl.healthcheck.ReportManager;
import org.ensembl.healthcheck.Species;
import org.ensembl.healthcheck.Team;
import org.ensembl.healthcheck.TestRunner;
import org.ensembl.healthcheck.configurationmanager.ConfigurationException;
import org.ensembl.healthcheck.util.CollectionUtils;
import org.ensembl.healthcheck.util.DBUtils;
import org.ensembl.healthcheck.util.MapRowMapper;
import org.ensembl.healthcheck.util.SQLParser;
import org.ensembl.healthcheck.util.SqlTemplate;
import org.ensembl.healthcheck.util.SqlUncheckedException;
import org.ensembl.healthcheck.util.Utils;
/**
* Base class for all healthcheck tests.
*/
public abstract class EnsTestCase {
/** the string that is contained in the name of backup tables */
public static final String backupIdentifier = "backup_";
/** The TestRunner associated with this EnsTestCase */
protected TestRunner testRunner;
/**
* A list of Strings representing the groups that this test is a member of.
* Every test is (at least) a member of a group with the same name as the
* test.
*/
protected List<String> groups;
/** Description field */
protected String description;
/** Priority field */
protected Priority priority = null;
/** Effect field */
protected String effect = null;
/** Fix field */
protected String fix = null;
/** Optional text to be printed when the test fails */
protected String failureText;
/** Which team is responsible for fixing this healthcheck */
protected Team teamResponsible;
/** Sometimes more than one team can be responsible */
protected Team secondTeamResponsible;
/** Logger object to use */
protected static Logger logger = Logger.getLogger(EnsTestCase.class
.getCanonicalName());
public static Logger getLogger() {
return logger;
}
public static void setLogger(Logger logger) {
EnsTestCase.logger = logger;
}
/**
* Boolean variable that can be set if the test case is likely to take a
* long time to run
*/
protected boolean hintLongRunning = false;
/**
* Store a list of which types of database this test applies to.
*/
protected List<DatabaseType> appliesToTypes = new ArrayList<DatabaseType>();
/**
* Names of tables in core schema that count as "feature" tables. Used in
* various healthchecks.
*/
private String[] featureTables = { "assembly_exception", "gene", "exon",
"dna_align_feature", "protein_align_feature", "repeat_feature",
"simple_feature", "marker_feature", "misc_feature",
"karyotype", "transcript", "density_feature", "prediction_exon",
"prediction_transcript", "ditag_feature" };
/**
* Tables that have an analysis ID.
*/
private String[] tablesWithAnalysisID = { "gene", "protein_feature",
"dna_align_feature", "protein_align_feature", "repeat_feature",
"prediction_transcript", "simple_feature", "marker_feature",
"density_type", "object_xref", "transcript",
"intron_supporting_evidence", "operon", "operon_transcript",
"unmapped_object", "ditag_feature", "data_file" };
/**
* Names of tables in funcgen schema that count as "feature" tables. Used in
* various healthchecks.
*/
private String[] funcgenFeatureTables = { "probe_feature",
"annotated_feature", "regulatory_feature", "external_feature",
"motif_feature", "mirna_target_feature" };
/**
* Funcgen tables that have an analysis ID.
*/
private String[] funcgenTablesWithAnalysisID = { "probe_feature",
"object_xref", "unmapped_object", "feature_set", "result_set" }; // also feature_type, but this is marked for removal.
protected boolean setSystemProperties = true;
// do we need to add analysis_description here?
public boolean isSetSystemProperties() {
return setSystemProperties;
}
public void setSetSystemProperties(boolean setSystemProperties) {
this.setSystemProperties = setSystemProperties;
}
/**
* A DatabaseRegistryEntry pointing to the production database.
*/
DatabaseRegistryEntry productionDBRE = null;
/**
* A DatabaseRegistryEntry pointing to the Compara Master database.
*/
DatabaseRegistryEntry comparaMasterDbre = null;
// -------------------------------------------------------------------------
/**
* Creates a new instance of EnsTestCase
*/
public EnsTestCase() {
groups = new ArrayList<String>();
addToGroup(getShortTestName()); // each test is in a one-test group
setDescription("No description set for this test.");
setFailureText("");
} // EnsTestCase
// -------------------------------------------------------------------------
/**
* Get the TestRunner that is controlling this EnsTestCase.
*
* @return The parent TestRunner.
*/
public TestRunner getTestRunner() {
return testRunner;
} // getTestRunner
// -------------------------------------------------------------------------
/**
* Sets up this test. <B>Must </B> be called before the object is used.
*
* @param tr
* The TestRunner to associate with this test. Usually just
* <CODE>
* this</CODE> if being called from the TestRunner.
*/
public void init(TestRunner tr) {
this.testRunner = tr;
} // init
// -------------------------------------------------------------------------
/**
* Gets the full name of this test.
*
* @return The full name of the test, e.g.
* org.ensembl.healthcheck.EnsTestCase
*/
public String getTestName() {
return this.getClass().getName();
}
// -------------------------------------------------------------------------
/**
* Gets the full name of this test.
*
* @return The full name of the test, e.g.
* org.ensembl.healthcheck.EnsTestCase
*/
public String getName() {
return this.getClass().getName();
}
// -------------------------------------------------------------------------
/**
* Get the short form of the test name, ie the name of the test class
* without the package qualifier.
*
* @return The short test name, e.g. EnsTestCase
*/
public String getShortTestName() {
String longName = getTestName();
return longName.substring(longName.lastIndexOf('.') + 1);
}
// -------------------------------------------------------------------------
/**
* Get the very short form of the test name; ie that returned by
* getShortTestName() without the trailing "TestCase"
*
* @return The very short test name, e.g. CheckMetaTables
*/
public String getVeryShortTestName() {
String name = getShortTestName();
return name.substring(0, name.lastIndexOf("TestCase"));
}
// -------------------------------------------------------------------------
/**
* Get a list of the names of the groups which this test case is a member
* of.
*
* @return The list of names as Strings.
*/
public List<String> getGroups() {
return groups;
}
// -------------------------------------------------------------------------
/**
* Get a list of the groups that this test case is a member of, formatted
* for easy printing.
*
* @return The comma-separated list of group names.
*/
public String getCommaSeparatedGroups() {
return StringUtils.join(groups, ',');
}
// -------------------------------------------------------------------------
/**
* Remove a test from all groups.
*/
public void removeFromAllGroups() {
groups = new ArrayList<String>();
}
// -------------------------------------------------------------------------
/**
* Convenience method for assigning this test case to several groups at
* once.
*
* @param s
* A list of Strings containing the group names.
*/
public void setGroups(List<String> s) {
groups = s;
}
// -------------------------------------------------------------------------
/**
* Convenience method for assigning this test case to several groups at
* once.
*
* @param s
* Array of group names.
*/
public void setGroups(String[] s) {
for (int i = 0; i < s.length; i++) {
groups.add(s[i]);
}
}
// -------------------------------------------------------------------------
/**
* Add this test case to a new group. If the test case is already a member
* of the group, a warning is printed and it is not added again.
*
* @param newGroupName
* The name of the new group.
*/
public void addToGroup(String newGroupName) {
if (!groups.contains(newGroupName)) {
groups.add(newGroupName);
} else {
logger.warning(getTestName() + " is already a member of "
+ newGroupName + " not added again.");
}
} // addToGroup
// -------------------------------------------------------------------------
/**
* Remove this test case from the specified group. If the test case is not a
* member of the specified group, a warning is printed.
*
* @param groupName
* The name of the group from which this test case is to be
* removed.
*/
public void removeFromGroup(String groupName) {
if (groups.contains(groupName)) {
groups.remove(groupName);
} else {
logger.warning(getTestName() + " was not a memeber of " + groupName);
}
} // removeFromGroup
// -------------------------------------------------------------------------
/**
* Test if this test case is a member of a particular group.
*
* @param group
* The name of the group to check.
* @return True if this test case is a member of the named group, false
* otherwise.
*/
public boolean inGroup(String group) {
return groups.contains(group);
}
// -------------------------------------------------------------------------
/**
* Convenience method for checking if this test case belongs to any of
* several groups.
*
* @param checkGroups
* The list of group names to check.
* @return True if this test case is in any of the groups, false if it is in
* none.
*/
public boolean inGroups(List<String> checkGroups) {
boolean result = false;
Iterator<String> it = checkGroups.iterator();
while (it.hasNext()) {
if (inGroup((String) it.next())) {
result = true;
break;
}
}
return result;
} // inGroups
// -------------------------------------------------------------------------
/**
* Print a warning message about a specific database.
*
* @param con
* The database connection involved.
* @param message
* The message to print.
*/
protected void warn(Connection con, String message) {
logger.warning("Problem in " + DBUtils.getShortDatabaseName(con));
logger.warning(message);
} // warn
// -------------------------------------------------------------------------
/**
* Get the description.
*
* @return The description for this test.
*/
public String getDescription() {
return description;
} // getDescription
// -------------------------------------------------------------------------
/**
* Set the text description of this test case.
*
* @param s
* The new description.
*/
public void setDescription(String s) {
description = s;
} // setDescription
// -------------------------------------------------------------------------
/**
* Get the failure text.
*
* @return The failure text for this test.
*/
public String getFailureText() {
return failureText;
} // getFailureText
// -------------------------------------------------------------------------
/**
* Set the text failure text of this test case.
*
* @param s
* The new failure text.
*/
public void setFailureText(String s) {
failureText = s;
} // setFailureText
protected void setConfiguredProperties() {
// read properties file
String propsFile = System.getProperty("user.dir")
+ System.getProperty("file.separator")
+ TestRunner.getPropertiesFile();
Utils.readPropertiesFileIntoSystem(propsFile, false);
logger.fine("Read database properties from " + propsFile);
}
// -------------------------------------------------------------------------
/**
* Read a database schema from a file and create a temporary database from
* it.
*
* @param fileName
* The name of the schema to read.
* @return A connection to a database built from the schema.
* @throws FileNotFoundException
*/
public Connection importSchema(String fileName)
throws FileNotFoundException {
Connection con = null;
// ----------------------------------------------------
// Parse the file first in case there are problems
SQLParser sqlParser = new SQLParser();
// try {
List sqlCommands = sqlParser.parse(fileName);
// sqlParser.printLines();
// } catch (FileNotFoundException fnfe) {
// fnfe.printStackTrace();
// }
// ----------------------------------------------------
// create the database
String tempDBName = DBUtils.generateTempDatabaseName();
if (setSystemProperties) {
setConfiguredProperties();
}
try {
Class.forName(System.getProperty("driver"));
String databaseURL = System.getProperty("databaseURL");
String user = System.getProperty("user");
String password = System.getProperty("password");
Connection tmpCon = DriverManager.getConnection(databaseURL, user,
password);
String sql = "CREATE DATABASE " + tempDBName;
logger.finest(sql);
Statement stmt = tmpCon.createStatement();
stmt.execute(sql);
logger.fine("Database " + tempDBName + " created!");
// close the temporary connection and create a "real" one
tmpCon.close();
con = DriverManager.getConnection(databaseURL + tempDBName, user,
password);
} catch (Exception e) {
String msg = "Could not create database " + tempDBName;
logger.severe(msg);
throw new RuntimeException(msg, e);
}
// ----------------------------------------------------
// Build the schema
try {
Statement stmt = con.createStatement();
// Fill the batch of SQL commands
stmt = sqlParser.populateBatch(stmt);
// execute the batch that has been built up previously
logger.info("Creating temporary database ...");
stmt.executeBatch();
logger.info("Done.");
// close statement
stmt.close();
} catch (Exception e) {
String msg = "Could not load schema for database " + tempDBName;
logger.severe(msg);
throw new RuntimeException(msg, e);
}
return con;
}
// -------------------------------------------------------------------------
/**
* Remove a whole database. Generally should *only* be used with temporary
* databases. Use at your own risk!
*
* @param con
* The connection pointing to the database to remove. Should be
* connected as a user that has sufficient permissions to remove
* it.
*/
public void removeDatabase(Connection con) {
String dbName = DBUtils.getShortDatabaseName(con);
try {
String sql = "DROP DATABASE " + dbName;
logger.finest(sql);
Statement stmt = con.createStatement();
stmt.execute(sql);
logger.fine("Database " + dbName + " removed!");
} catch (Exception e) {
String msg = "Could not drop database " + dbName;
logger.severe(msg);
throw new RuntimeException(msg, e);
} finally {
// closeQuietly(stmt);
}
}
// -------------------------------------------------------------------------
/**
* Get a list of all the table names.
*
* @param con
* The database connection to use.
* @return An array of Strings representing the names of the tables,
* obtained from the SHOW TABLES command.
*/
public String[] getTableNames(Connection con) {
return DBUtils.getTableNames(con);
}
// -------------------------------------------------------------------------
/**
* Get a list of the table names that match a particular pattern.
*
* @param con
* The database connection to use.
* @param pattern
* The pattern to use - note that this is a <em>SQL</em> pattern,
* not a regexp.
* @return An array of Strings representing the names of the tables that
* match the pattern.
*/
public String[] getTableNames(Connection con, String pattern) {
return DBUtils.getTableNames(con, pattern);
}
// -------------------------------------------------------------------------
/**
* Convenience method for getting a connection to a named schema.
*
* @param schema
* The name of the schema to connect to.
* @return A connection to schema.
*/
public Connection getSchemaConnection(String schema) {
DatabaseRegistryEntry dbre = DBUtils.getMainDatabaseRegistry()
.getByExactName(schema);
return dbre.getConnection();
}
// -------------------------------------------------------------------------
/**
* Get a whole table as a ResultSet
*
* @param table
* The table to get.
* @return A ResultSet containing the contents of the table.
*/
public ResultSet getWholeTable(Connection con, String table, String key) {
ResultSet rs = null;
try {
Statement stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM " + table + " ORDER BY "
+ key);
} catch (Exception e) {
throw new SqlUncheckedException("Could not retrieve whole table", e);
} finally {
}
return rs;
}
// -------------------------------------------------------------------------
/**
* Get all the rows from certain columns of a table, specifying which ones
* to ignore.
*
* @param table
* The table to query.
* @param exceptionColumns
* A list of columns to ignore.
* @return A ResultSet containing the contents of the table, minus the
* columns in question.
*/
public ResultSet getWholeTableExceptSomeColumns(Connection con,
String table, String key, List<String> exceptionColumns,
String whereClause) {
ResultSet rs = null;
List<String> allColumns = DBUtils.getColumnsInTable(con, table);
allColumns.removeAll(exceptionColumns);
String columns = StringUtils.join(allColumns, ",");
try {
Statement stmt = con.createStatement();
rs = stmt.executeQuery(String.format(
"SELECT %s FROM %s %s ORDER BY %s", columns, table,
whereClause, key));
} catch (Exception e) {
throw new SqlUncheckedException("Could not retrieve whole table "
+ table, e);
} finally {
}
return rs;
}
// -------------------------------------------------------------------------
/**
* Get a connection to a new database given a pattern.
*
* @param dbPattern
* - a String pattern to identify the required database
*
* @return A DatabaseRegistryEntry.
*/
public DatabaseRegistryEntry getDatabaseRegistryEntryByPattern(
String dbPattern) {
// create it
List<String> list = new ArrayList<String>();
list.add(dbPattern);
DatabaseRegistryEntry newDBRE = null;
DatabaseRegistry newDBR = new DatabaseRegistry(list, null, null, false);
if (newDBR.getEntryCount() == 0) {
logger.warning("Can't connect to database " + dbPattern
+ ". Skipping.");
return null;
} else if (newDBR.getEntryCount() > 1) {
logger.warning("Found " + newDBR.getEntryCount()
+ " databases matching pattern " + dbPattern
+ ". Only one expected. Skipping.");
return null;
}
newDBRE = newDBR.getAll()[0];
logger.finest("Got new db: " + newDBRE.getName());
return newDBRE;
}
// -------------------------------------------------------------------------
/**
* Get a new DatabaseRegistry given a pattern.
*
* @param dbPattern
* - a String pattern to identify the required databases
*
* @return A DatabaseRegistry.
*/
public DatabaseRegistry getDatabaseRegistryByPattern(String dbPattern) {
// create it
List<String> list = new ArrayList<String>();
list.add(dbPattern);
return new DatabaseRegistry(list, null, null, false);
}
// -------------------------------------------------------------------------
/**
* Get a connection to the production database.
*
* @return A DatabaseRegistryEntry representing the database
*/
public DatabaseRegistryEntry getProductionDatabase() {
// return existing one if we already have it, otherwise use method above
// to find it
return productionDBRE != null ? productionDBRE
: getDatabaseRegistryEntryByPattern(System.getProperty("production.database"));
}
protected String getDataFileBasePath() {
return System.getProperty("dataFileBasePath");
}
public void setProductionDatabase(DatabaseRegistryEntry productionDBRE ) {
this.productionDBRE = productionDBRE;
}
/**
* Get a connection to the Compara master database.
*
* @return A DatabaseRegistryEntry representing the database
*/
public DatabaseRegistryEntry getComparaMasterDatabase() {
// return existing one if we already have it, otherwise use method above
// to find it
return comparaMasterDbre != null ? comparaMasterDbre
: getDatabaseRegistryEntryByPattern(System.getProperty("compara_master.database"));
}
public void setComparaMasterDatabase(DatabaseRegistryEntry comparaMasterDbre) {
this.comparaMasterDbre = comparaMasterDbre;
}
// -------------------------------------------------------------------------
/**
* Compare the contents of a table in the production database with one in
* another database.
*/
public boolean compareProductionTable(DatabaseRegistryEntry dbre,
String tableName, String tableKey, String productionTableName,
String productionKey) {
Connection con = dbre.getConnection();
DatabaseRegistryEntry productionDBRE = getProductionDatabase();
return DBUtils.compareResultSets(
getWholeTable(con, tableName, tableKey),
getWholeTable(productionDBRE.getConnection(),
productionTableName, productionKey), this, "", true,
false, tableName, null, false);
}
// -------------------------------------------------------------------------
/**
* test if a species is merged
* connect to the production database using the species production_name
*/
public boolean isMerged(Species species) {
boolean result = false;
String speciesName = species.toString();
int rows = DBUtils.getRowCount(getProductionDatabase().getConnection(), "SELECT count(*) FROM species s, attrib_type at WHERE at.attrib_type_id = s.attrib_type_id AND code = 'merged' AND production_name = '" + speciesName + "'");
if (rows > 0) {
result = true;
}
return result;
}
// -------------------------------------------------------------------------
/**
* Compare the contents of a table in the production database with one in
* another database, but ignore certain columns.
*/
public boolean compareProductionTableWithExceptions(
DatabaseRegistryEntry dbre, String tableName, String tableKey,
String productionTableName, String productionKey,
List<String> exceptionColumns) {
Connection con = dbre.getConnection();
DatabaseRegistryEntry productionDBRE = getProductionDatabase();
if(productionDBRE==null || productionDBRE.getConnection()==null) {
throw new ConfigurationException("Production database not found");
}
return DBUtils.compareResultSets(
getWholeTableExceptSomeColumns(con, tableName, tableKey,
exceptionColumns, ""),
getWholeTableExceptSomeColumns(productionDBRE.getConnection(),
productionTableName, productionKey, exceptionColumns,
"WHERE is_current=1"), this, "", true, false,
tableName, null, false);
}
// -------------------------------------------------------------------------
/**
* Check if the current test has repair capability. Signified by
* implementing the Repair interface.
*
* @return True if this test implements Repair, false otherwise.
*/
public boolean canRepair() {
return (this instanceof Repair);
}
// -------------------------------------------------------------------------
/**
* Check if a table has rows.
*
* @param con
* The connection to the database to use.
* @param table
* The table to check.
* @return true if the table has >0 rows, false otherwise.
*/
public boolean tableHasRows(Connection con, String table) {
return (DBUtils.getRowCount(con, "SELECT COUNT(*) FROM " + table) > 0);
}
// -------------------------------------------------------------------------
/**
* See if the "hintLongRunning" flag is set.
*
* @return The value of the hintLongRunning flag.
*/
public boolean isLongRunning() {
return hintLongRunning;
}
// -------------------------------------------------------------------------
/**
* Set the flag that indicates that this test may take a long time to run.
*
* @param b
* The new value of the flag.
*/
public void setHintLongRunning(boolean b) {
hintLongRunning = b;
}
// ---------------------------------------------------------------------
/**
* Check if this test case applies to a particular DatabaseType.
*
* @param t
* The database type to check against.
* @return true if this test applies to databases of type t.
*/
public boolean appliesToType(DatabaseType t) {
Iterator<DatabaseType> it = appliesToTypes.iterator();
while (it.hasNext()) {
DatabaseType type = (DatabaseType) it.next();
if (t.equals(type)) {
return true;
}
}
return false;
}
// -----------------------------------------------------------------
/**
* Add another database type to the list of types that this test case
* applies to.
*
* @param t
* The new type.
*/
public void addAppliesToType(DatabaseType t) {
appliesToTypes.add(t);
}
// -----------------------------------------------------------------
/**
* Remove a database type from the list of types that this test case applies
* to.
*
* @param t
* The type to remove.
*/
public void removeAppliesToType(DatabaseType t) {
appliesToTypes.remove(t);
}
// -----------------------------------------------------------------
/**
* Specify the database types that a test applies to.
*
* @param types
* A List of DatabaseTypes - overwrites the current setting.
*/
public void setAppliesToTypes(List<DatabaseType> types) {
appliesToTypes = types;
}
// -----------------------------------------------------------------
/**
* Convenience method for specifying that a test only applies to one type.
*
* @param type
* A DatabaseType - overwrites the current setting.
*/
public void setAppliesToType(DatabaseType type) {
List<DatabaseType> types = new ArrayList<DatabaseType>();
types.add(type);
appliesToTypes = types;
}
// -----------------------------------------------------------------
/**
* @return the list of database types that a test applies to.
*/
public DatabaseType[] getAppliesToTypes() {
return (DatabaseType[]) appliesToTypes
.toArray(new DatabaseType[appliesToTypes.size()]);
}
// -----------------------------------------------------------------
/**
* Set the database type(s) that this test applies to based upon the
* directory name. For directories called "generic", the type is set to
* core, otherfeatures, cdna, rnaseq, presite, vega and sangervega. For all other
* directories the type is set based upon the directory name.
*
* @param dirName
* The directory name to check.
*/
public void setTypeFromDirName(String dirName) {
List<DatabaseType> types = new ArrayList<DatabaseType>();
if (dirName.equals("generic")) {
types.add(DatabaseType.CORE);
types.add(DatabaseType.VEGA);
types.add(DatabaseType.CDNA);
types.add(DatabaseType.OTHERFEATURES);
types.add(DatabaseType.SANGER_VEGA);
types.add(DatabaseType.RNASEQ);
types.add(DatabaseType.PRE_SITE);
logger.finest("Set generic types for " + getName());
} else {
DatabaseType type = DatabaseType.resolveAlias(dirName);
if (type != DatabaseType.UNKNOWN) {
types.add(type);
logger.finest("Set type to " + type.toString() + " for "
+ getName());
} else {
logger.finest("Cannot deduce test type from directory name "
+ dirName + " for " + getName());
}
}
setAppliesToTypes(types);
}
/**
* Helper method to set the applicable types for this test from the parent
* package. For instance, if the package is
* org.ensembl.healthcheck.testcase.core, then core will be set as the type.
* This method delegates to {@link #setTypeFromDirName(String)} using the
* parent package string as an argument
*/
public void setTypeFromPackageName() {
String packageName = this.getClass().getPackage().getName();
String parent = packageName.substring(packageName.lastIndexOf('.') + 1);
setTypeFromDirName(parent);
}
// -------------------------------------------------------------------------
/**
* This method can be overridden in subclasses to define (via
* addAppliesToType/removeAppliesToType) which types of databases the test
* applies to.
*/
public void types() {
}
// -------------------------------------------------------------------------
/**
* Verify foreign-key relations, and fills ReportManager with useful sql if
* necessary.
*
* @param con
* A connection to the database to be tested. Should already be
* open.
* @param table1
* With col1, specifies the first key to check.
* @param col1
* Column in table1 to check.
* @param table2
* With col2, specifies the second key to check.
* @param col2
* Column in table2 to check.
* @return boolean true if everything is fine false otherwise
*/
public boolean checkForOrphans(Connection con, String table1, String col1,
String table2, String col2) {
int orphans = 0;
boolean result = true;
orphans = countOrphans(con, table1, col1, table2, col2, true);
String useful_sql = "SELECT " + table1 + "." + col1 + " FROM " + table1
+ " LEFT JOIN " + table2 + " ON " + table1 + "." + col1 + " = "
+ table2 + "." + col2 + " WHERE " + table2 + "." + col2
+ " iS NULL";
if (orphans > 0) {
ReportManager.problem(this, con, "FAILED " + table1 + " -> "
+ table2 + " using FK " + col1 + "(" + col2 + ")"
+ " relationships");
ReportManager.problem(this, con, "FAILURE DETAILS: " + orphans
+ " " + table1 + " entries are not linked to " + table2);
ReportManager.problem(this, con, "USEFUL SQL: " + useful_sql);
result = false;
} else if (orphans < 0) {
ReportManager.problem(this, con, "TEST NOT COMPLETED " + table1
+ " -> " + table2 + " using FK " + col1
+ ", look at the StackTrace if any");
result = false;
}
return result;
} // checkForOrphans
// -------------------------------------------------------------------------
/**
* Verify foreign-key relations.
*
* @param con
* A connection to the database to be tested. Should already be
* open.
* @param table1
* With col1, specifies the first key to check.
* @param col1
* Column in table1 to check.
* @param table2
* With col2, specifies the second key to check.
* @param col2
* Column in table2 to check.
* @param oneWayOnly
* If false, only a "left join" is performed on table1 and
* table2. If false, the
* @return The number of "orphans"
*/
public int countOrphans(Connection con, String table1, String col1,
String table2, String col2, boolean oneWayOnly) {
if (con == null) {
logger.severe("countOrphans: Database connection is null");
}
int resultLeft, resultRight;
String sql = " FROM " + table1 + " LEFT JOIN " + table2 + " ON "
+ table1 + "." + col1 + " = " + table2 + "." + col2 + " WHERE "
+ table2 + "." + col2 + " IS NULL";
resultLeft = DBUtils.getRowCount(con, "SELECT COUNT(*)" + sql);
logger.finest("Left: " + resultLeft);
if (resultLeft > 0) {
String[] values = DBUtils.getColumnValues(con, "SELECT " + table1
+ "." + col1 + sql + " LIMIT 20");
for (int i = 0; i < values.length; i++) {
ReportManager.info(this, con, table1 + "." + col1 + " "
+ values[i] + " is not linked.");
}
}
if (!oneWayOnly) {
// and the other way ... (a right join?)
sql = " FROM " + table2 + " LEFT JOIN " + table1 + " ON " + table2
+ "." + col2 + " = " + table1 + "." + col1 + " WHERE "
+ table1 + "." + col1 + " IS NULL";
resultRight = DBUtils.getRowCount(con, "SELECT COUNT(*)" + sql);
if (resultRight > 0) {
String[] values = DBUtils.getColumnValues(con, "SELECT "
+ table2 + "." + col2 + sql + " LIMIT 20");
for (int i = 0; i < values.length; i++) {
ReportManager.info(this, con, table2 + "." + col2 + " "
+ values[i] + " is not linked.");
}
}
logger.finest("Right: " + resultRight);
} else {
resultRight = 0;
}
// logger.finest("Left: " + resultLeft + " Right: " + resultRight);
return resultLeft + resultRight;
} // countOrphans
// -------------------------------------------------------------------------
/**
* Verify foreign-key relations.
*
* @param con
* A connection to the database to be tested. Should already be
* open.
* @param table1
* With col1, specifies the first key to check.
* @param col1
* Column in table1 to check.
* @param table2
* With col2, specifies the second key to check.
* @param col2
* Column in table2 to check.
* @param constraint1
* additional constraint on a column in table1
* @return The number of "orphans"
*/
public int countOrphansWithConstraint(Connection con, String table1,
String col1, String table2, String col2, String constraint1) {
if (con == null) {
logger.severe("countOrphans: Database connection is null");
}
int resultLeft;
String sql = " FROM " + table1 + " LEFT JOIN " + table2 + " ON "
+ table1 + "." + col1 + " = " + table2 + "." + col2 + " WHERE "
+ table2 + "." + col2 + " iS NULL";
sql = sql + " AND " + table1 + "." + constraint1;
resultLeft = DBUtils.getRowCount(con, "SELECT COUNT(*)" + sql);
if (resultLeft > 0) {
String[] values = DBUtils.getColumnValues(con, "SELECT " + table1
+ "." + col1 + sql + " LIMIT 20");
for (int i = 0; i < values.length; i++) {
ReportManager.info(this, con, table1 + "." + col1 + " "
+ values[i] + " is not linked.");
}
}
logger.finest("Left: " + resultLeft);
return resultLeft;
} // countOrphans
// -------------------------------------------------------------------------
/**
* Generic way to check for orphan foreign key relationships.
*
* @return true If there are no orphans.
*/
public boolean checkForOrphans(Connection con, String table1, String col1,
String table2, String col2, boolean oneWay) {
logger.finest("Checking for orphans with:\t" + table1 + "." + col1
+ " " + table2 + "." + col2 + ". oneWay is " + oneWay);
int orphans = countOrphans(con, table1, col1, table2, col2, oneWay);
boolean result = true;
String useful_sql = "SELECT " + table1 + "." + col1 + " FROM " + table1
+ " LEFT JOIN " + table2 + " ON " + table1 + "." + col1 + " = "
+ table2 + "." + col2 + " WHERE " + table2 + "." + col2
+ " IS NULL";
if (orphans > 0) {
ReportManager.problem(this, con, "FAILED " + table1 + " -> "
+ table2 + " using FK " + col1 + "(" + col2 + ")"
+ " relationships");
ReportManager.problem(this, con, "FAILURE DETAILS: " + orphans
+ " " + table1 + " entries are not linked to " + table2);
ReportManager.problem(this, con, "USEFUL SQL: " + useful_sql);
if (!oneWay) {
String useful_sql2 = "SELECT " + table2 + "." + col2 + " FROM "
+ table2 + " LEFT JOIN " + table1 + " ON " + table2
+ "." + col2 + " = " + table1 + "." + col1 + " WHERE "
+ table1 + "." + col1 + " IS NULL";
ReportManager.problem(this, con, "alternate useful SQL: "
+ useful_sql2);
}
result = false;
} else if (orphans < 0) {
ReportManager.problem(this, con, "TEST NOT COMPLETED " + table1
+ " -> " + table2 + " using FK " + col1
+ ", look at the StackTrace if any");
result = false;
}
return result;
/*
* if (orphans > 0) { ReportManager.problem(this, con, table1 + " <-> "
* + table2 + " has " + orphans + " unlinked entries"); } else {
* ReportManager.correct(this, con, "All " + table1 + " <-> " + table2 +
* " relationships are OK"); }
*
* return orphans == 0;
*/
} // checkForOrphans
// -------------------------------------------------------------------------
/**
* Verify multiple appearance of a given foreign key
*
* @param con
* A connection to the database to be tested. Should already be
* open.
* @param table
* With col, specifies the foreign key to check.
* @param col
* Column in table to check.
* @param constraint
* Subset of the rows to be tested. This SQL constraint must
* include the WHERE keyword. Leave empty for no filtering
* @return The number of "singles"
*/
public int countSingles(Connection con, String table, String col, String constraint) {
if (con == null) {
logger.severe("countSingles: Database connection is null");
}
int result = 0;
String sql = " FROM " + table + " " + constraint + " GROUP BY (" + col
+ ") HAVING COUNT(*) = 1";
result = DBUtils.getRowCount(con, "SELECT *" + sql);
if (result > 0) {
String[] values = DBUtils.getColumnValues(con, "SELECT " + table
+ "." + col + sql + " LIMIT 20");
for (int i = 0; i < values.length; i++) {
ReportManager.info(this, con, table + "." + col + " "
+ values[i] + " is used only once.");
}
}
logger.finest("Singles: " + result);
return result;
} // countSingles
// -------------------------------------------------------------------------
/**
* Verify multiple appearance of a given foreign key
*
* @param con
* A connection to the database to be tested. Should already be
* open.
* @param table
* With col, specifies the foreign key to check.
* @param col
* Column in table1 to check.
* @return boolean true if everything is fine false otherwise
*/
public boolean checkForSingles(Connection con, String table, String col) {
return checkForSinglesWithConstraint(con, table, col, "");
}
/**
* Verify multiple appearance of a given foreign key
*
* @param con
* A connection to the database to be tested. Should already be
* open.
* @param table
* With col, specifies the foreign key to check.
* @param col
* Column in table1 to check.
* @param constraint
* Subset of the rows to be tested. This SQL constraint must
* include the WHERE keyword. Leave empty for no filtering
* @return boolean true if everything is fine false otherwise
*/
public boolean checkForSinglesWithConstraint(Connection con, String table, String col, String constraint) {
int singles = 0;
boolean result = true;
singles = countSingles(con, table, col, constraint);
String useful_sql = "SELECT " + table + "." + col + " FROM " + table + " " + constraint
+ " GROUP BY (" + col + ") HAVING COUNT(*) = 1";
if (singles > 0) {
ReportManager.problem(this, con, "FAILED " + table + "." + col
+ " is a FK for a 1 to many (>1) relationship");
ReportManager.problem(this, con, "FAILURE DETAILS: " + singles
+ " " + table + "." + col + " entries are used only once");
ReportManager.problem(this, con, "USEFUL SQL: " + useful_sql);
result = false;
} else if (singles < 0) {
ReportManager
.problem(
this,
con,
"TEST NOT COMPLETED "
+ table
+ "."
+ col
+ " is a FK for a 1 to many (>1) relationship, look at the StackTrace if any");
ReportManager.problem(this, con, "USEFUL SQL: " + useful_sql);
result = false;
}
return result;
} // checkForSingles
// -------------------------------------------------------------------------
/**
* Verify foreign-key relations, and fills ReportManager with useful sql if
* necessary.
*
* @param con
* A connection to the database to be tested. Should already be
* open.
* @param table1
* With col1, specifies the first key to check.
* @param col1
* Column in table1 to check.
* @param table2
* With col2, specifies the second key to check.
* @param col2
* Column in table2 to check.
* @param constraint1
* additional constraint on a column in table1
* @return boolean true if everything is fine false otherwise
*/
public boolean checkForOrphansWithConstraint(Connection con, String table1,
String col1, String table2, String col2, String constraint1) {
int orphans = 0;
boolean result = true;
orphans = countOrphansWithConstraint(con, table1, col1, table2, col2,
constraint1);
String useful_sql = "SELECT " + table1 + "." + col1 + " FROM " + table1
+ " LEFT JOIN " + table2 + " ON " + table1 + "." + col1 + " = "
+ table2 + "." + col2 + " WHERE " + table2 + "." + col2
+ " iS NULL";
// System.out.println(table1 + "." + col1 + "." + table2 + "." + col2);
if (!constraint1.equals("")) {
useful_sql = useful_sql + " AND " + table1 + "." + constraint1;
}
if (orphans > 0) {
ReportManager.problem(this, con, "FAILED " + table1 + " -> "
+ table2 + " using FK " + col1 + "(" + col2 + ")"
+ " relationships");
ReportManager.problem(this, con, "FAILURE DETAILS: " + orphans
+ " " + table1 + " entries are not linked to " + table2);
ReportManager.problem(this, con, "USEFUL SQL: " + useful_sql);
result = false;
} else if (orphans < 0) {
ReportManager.problem(this, con, "TEST NOT COMPLETED " + table1
+ " -> " + table2 + " using FK " + col1
+ ", look at the StackTrace if any");
result = false;
}
return result;
} // checkForOrphansWithConstraint
// -------------------------------------------------------------------------
/**
* Verify optional foreign-key relations. The methods checks that non-NULL
* foreign keys point to valid primary keys.
*
* @param con
* A connection to the database to be tested. Should already be
* open.
* @param table1
* With col1, specifies the first key to check.
* @param col1
* Column in table1 to check.
* @param table2
* With col2, specifies the second key to check.
* @param col2
* Column in table2 to check.
* @return boolean true if everything is fine false otherwise
*/
public boolean checkOptionalRelation(Connection con, String table1,
String col1, String table2, String col2) {
return checkForOrphansWithConstraint(con, table1, col1, table2, col2,
col1 + " IS NOT NULL");
}
// ----------------------------------------------------------------------
/**
* Check that a particular column has no null values. Problem or correct
* reports are generated via ReportManager.
*
* @param con
* The database connection to use.
* @param table
* The table name.
* @param column
* The column to check.
* @return True if no columns are null, false otherwise.
*/
public boolean checkNoNulls(Connection con, String table, String column) {
boolean result = true;
String sql = String.format("SELECT COUNT(*) FROM %s WHERE %s IS NULL",
table, column);
int nulls = DBUtils.getRowCount(con, sql);
if (nulls > 0) {
ReportManager.problem(this, con, nulls + " NULL values in " + table
+ "." + column);
result = false;
}
return result;
} // checkNoNulls
/**
* Check a column for zero values. Problem or correct reports are generated
* via ReportManager.
*
* @param con
* The database connection to use.
* @param table
* The table name.
* @param column
* The column to check.
* @return True if no columns have zero values, false otherwise.
*/
public boolean checkNoZeroes(Connection con, String table, String column) {
boolean result = true;
String sql = String.format("SELECT COUNT(*) FROM %s WHERE %s = 0",
table, column);
int zeroes = DBUtils.getRowCount(con, sql);
if (zeroes > 0) {
ReportManager.problem(this, con, "Zeroes found in " + table + "."
+ column);
result = false;
}
return result;
}
/**
* Check a column for odd characters. Problem or correct reports are generated
* via ReportManager. Applicable to display names, and other presentation strings
*
* @param con
* The database connection to use.
* @param table
* The table name.
* @param column
* The column to check.
* @return True if column is devoid of bad characters, false otherwise.
*/
public boolean checkNoBadCharacters(Connection con, String table, String column) {
boolean result = true;
String sql = String.format("SELECT COUNT(*) FROM %s WHERE %s REGEXP '%s'",
table, column, "^\\[\\:\\;\\n\\r\\t\\~\\]|\\[\\:\\;\\n\\r\\t\\~\\]$");
// MOAR slashes
int badrows = DBUtils.getRowCount(con, sql);
if (badrows > 0) {
ReportManager.problem(this, con, "Forbidden characters found in "+badrows+" rows of " + table + "."
+ column);
result = false;
}
return result;
}
// -------------------------------------------------------------------------
/**
* Check that a particular SQL statement has the same result when executed
* on more than one database.
*
* @return True if all matched databases provide the same result, false
* otherwise.
* @param sql
* The SQL query to execute.
* @param regexp
* A regexp matching the database names to check.
* @deprecated moved to
* {@link DBUtils#checkSameSQLResult(EnsTestCase, String, String, boolean)}
*/
@Deprecated
public boolean checkSameSQLResult(String sql, String regexp,
boolean comparingSchema) {
return DBUtils.checkSameSQLResult(this, sql, regexp, comparingSchema);
} // checkSameSQLResult
// -------------------------------------------------------------------------
/**
* Check that a particular SQL statement has the same result when executed
* on more than one database.
*
* @return True if all matched databases provide the same result, false
* otherwise.
* @param sql
* The SQL query to execute.
* @param databases
* The DatabaseRegistryEntries on which to execute sql.
* @deprecated moved to
* {@link DBUtils#checkSameSQLResult(EnsTestCase, String, DatabaseRegistryEntry[], boolean)}
*/
@Deprecated
public boolean checkSameSQLResult(String sql,
DatabaseRegistryEntry[] databases, boolean comparingSchema) {
return DBUtils
.checkSameSQLResult(this, sql, databases, comparingSchema);
} // checkSameSQLResult
// ----------------------------------------------------------------------
/**
* Get a list of the tables in a core schema that conform to various
* characteristics and count as "feature" tables.
*
* @return An array of feature tables.
*/
public String[] getCoreFeatureTables() {
return featureTables;
}
// ----------------------------------------------------------------------
/**
* Get a list of the tables in a core schema that have an analysis_id
* colmun.
*
* @return An array of table names.
*/
public String[] getCoreTablesWithAnalysisID() {
return tablesWithAnalysisID;
}
// ----------------------------------------------------------------------
/**
* Get a list of the tables in a funcgen schema that conform to various
* characteristics and count as "feature" tables.
*
* @return An array of feature tables.
*/
public String[] getFuncgenFeatureTables() {
return funcgenFeatureTables;
}
// ----------------------------------------------------------------------
/**
* Get a list of the tables in a funcgen schema that have an analysis_id
* colmun.
*
* @return An array of table names.
*/
public String[] getFuncgenTablesWithAnalysisID() {
return funcgenTablesWithAnalysisID;
}
// ----------------------------------------------------------------------
/**
* Get the equivalent database from the secondary database server.
* "equivalent" means: same database type and species. If more than one
* database on the secondary server has the same type and species, then the
* one with the highest version number is used.
*
* @param dbre
* The database to find the equivalent for.
* @return The database on the secondary server with the same type and
* species, and the highest version number, or null if none is
* found.
*/
public DatabaseRegistryEntry getEquivalentFromSecondaryServer(
DatabaseRegistryEntry dbre) {
DatabaseRegistry secondaryDatabaseRegistry = DBUtils
.getSecondaryDatabaseRegistry();
// find any databases matching type and species
TreeSet<DatabaseRegistryEntry> matchingDBs = new TreeSet<DatabaseRegistryEntry>(); // get
// sorting
// for
// free
for (DatabaseRegistryEntry secDBRE : secondaryDatabaseRegistry.getAll()) {
if (DBUtils.getSecondaryDatabase() != null) {
if (secDBRE.getName().equals(DBUtils.getSecondaryDatabase())) {
return secDBRE;
}
}
if (dbre.getSpecies() == Species.UNKNOWN) {
// EG where we don't know the species, use type and alias
// matching instead
if (dbre.getType().equals(secDBRE.getType())
&& dbre.getAlias().equals(secDBRE.getAlias())) {
matchingDBs.add(secDBRE);
logger.finest("added "
+ secDBRE.getName()
+ " to list of databases to check for equivalent to "
+ dbre.getName());
}
} else {
// nulls will set type automatically
if (dbre.getType().equals(secDBRE.getType())
&& dbre.getSpecies().equals(secDBRE.getSpecies())) {
matchingDBs.add(secDBRE);
logger.finest("added "
+ secDBRE.getName()
+ " to list of databases to check for equivalent to "
+ dbre.getName());
}
}
}
if (matchingDBs.size() == 0) {
logger.finest("Could not find equivalent database to "
+ dbre.getName() + " on secondary server");
}
// take the highest one that doesn't have the same version number as our
// current one, if available
DatabaseRegistryEntry result = null;
if (matchingDBs.size() > 0) {
result = (DatabaseRegistryEntry) matchingDBs.last();
}
return result;
}
// ------------------------------------------------------------------------------------------
public boolean checkDatabaseExistsByType(DatabaseRegistryEntry dbre,
DatabaseType dbType) {
// figure out the corresponding database
String targetName = dbre.getName().replace(dbre.getType().getName(), dbType.getName());
// work out if we have one
return DBUtils.getSqlTemplate(dbre).queryForDefaultObject("select count(*) from information_schema.tables where table_schema=?", Integer.class, targetName) > 0;
}
// ----------------------------------------------------------------------
/**
* Get a list of the logic names and analysis IDs from the analysis table.
*
* @param con
* The connection to use.
* @return A map of analysis IDs (keys) and logic names (values).
*/
public Map<Integer, String> getLogicNamesFromAnalysisTable(Connection con) {
return DBUtils.getSqlTemplate(con).queryForMap(
"SELECT analysis_id, logic_name FROM analysis",
new MapRowMapper<Integer, String>() {
@Override
public String mapRow(ResultSet resultSet, int position)
throws SQLException {
return resultSet.getString("logic_name");
}
@Override
public Map<Integer, String> getMap() {
return CollectionUtils.createHashMap();
}
@Override
public Integer getKey(ResultSet resultSet)
throws SQLException {
return resultSet.getInt("analysis_id");
}
@Override
public void existingObject(String currentValue,
ResultSet resultSet, int position)
throws SQLException {
throw new SqlUncheckedException(
"Duplicate analysis row found for ID "
+ currentValue);
}
});
}
// ----------------------------------------------------------------------
/**
* Define how severe the effect of a test's failure would be. Note that this
* is a test-level priority; within a testcase the ReportManager methods
* (problem, correct etc) should be used.
*
* @param p
* The new priority to set.
*/
public void setPriority(Priority p) {
priority = p;
}
// ----------------------------------------------------------------------
/**
* Get the priority.
*/
public Priority getPriority() {
return priority;
}
// ----------------------------------------------------------------------
/**
* Define how what will happen if databases which fail this healthcheck are
* left unfixed.
*
* @param e
* The effect to set.
*/
public void setEffect(String e) {
effect = e;
}
// ----------------------------------------------------------------------
/**
* Return what will happen if databases which fail this healthcheck are left
* unfixed.
*/
public String getEffect() {
return effect;
}
// ----------------------------------------------------------------------
/**
* Describe (as text) a possible fix for the problem causing this
* healthcheck to fail.
*
* @param f
* The fix to set.
*/
public void setFix(String f) {
fix = f;
}
// ----------------------------------------------------------------------
/**
* Get (as text) a possible fix for the problem causing this healthcheck to
* fail.
*/
public String getFix() {
return fix;
}
// ----------------------------------------------------------------------
public Team getTeamResponsible() {
return teamResponsible;
}
// ----------------------------------------------------------------------
public void setTeamResponsible(Team teamResponsible) {
this.teamResponsible = teamResponsible;
}
// ----------------------------------------------------------------------
public Team getSecondTeamResponsible() {
return secondTeamResponsible;
}
// ----------------------------------------------------------------------
public void setSecondTeamResponsible(Team secondTeamResponsible) {
this.secondTeamResponsible = secondTeamResponsible;
}
public void removeSecondTeamResponsible() {
this.secondTeamResponsible = null;
}
// ----------------------------------------------------------------------
public String getPrintableTeamResponsibleString() {
if (getTeamResponsible() == null) {
return "The team responsible has not been set.";
}
String team = getTeamResponsible().toString();
if (getSecondTeamResponsible() != null) {
team += " and " + getSecondTeamResponsible();
}
return team;
}
// ----------------------------------------------------------------------
/**
* Get the names of the top level seq_regions.
*/
public List<String> getTopLevelNames(Connection con) {
List<String> names = new ArrayList<String>();
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt
.executeQuery("SELECT sr.name FROM seq_region sr, seq_region_attrib sra, attrib_type at WHERE sra.seq_region_id=sr.seq_region_id AND sra.attrib_type_id=at.attrib_type_id AND at.code='toplevel'");
while (rs.next()) {
names.add(rs.getString(1));
}
} catch (SQLException se) {
se.printStackTrace();
}
return names;
}
/**
* Get the names of the top level seq_regions that are called chromosomes.
*/
public List<String> getTopLevelChromosomeNames(Connection con) {
String sql = "SELECT sr.name FROM seq_region sr, seq_region_attrib sra, attrib_type at, coord_system cs " +
"WHERE cs.coord_system_id=sr.coord_system_id AND sra.seq_region_id=sr.seq_region_id " +
"AND sra.attrib_type_id=at.attrib_type_id AND at.code='toplevel' AND cs.name='chromosome' " +
"AND cs.attrib LIKE '%default_version%' and sr.seq_region_id not in " +
"(select sr2.seq_region_id from seq_region sr2, seq_region_attrib sra1, attrib_type at1 " +
"where sr2.seq_region_id = sra1.seq_region_id and sra1.attrib_type_id = at1.attrib_type_id and at1.code = 'non_ref')";
return DBUtils.getSqlTemplate(con).queryForDefaultObjectList(sql, String.class);
}
/**
* Return the list of views and tables that are required to be present in
* the funcgen database before Biomart can run, but should be removed
* afterwards.
*/
public String[] getBiomartFuncgenTablesAndViews() {
String[] t = { "cs_sr_view", "fs_displayable_view",
"regulatory_feature_view", "external_feature_ox_view",
"external_feature_view", "annotated_feature_view",
"feature_set_view", "probestuff_helper_tmp" };
return t;
}
@Deprecated
public long getChecksum(Connection con, String tableName) {
return DBUtils.getChecksum(con, tableName);
}
/**
* Produce an instance of {@link SqlTemplate} from a
* {@link DatabaseRegistryEntry}.
*/
public SqlTemplate getSqlTemplate(DatabaseRegistryEntry dbre) {
return DBUtils.getSqlTemplate(dbre);
}
/**
* Produce an instance of {@link SqlTemplate} from a {@link Connection}.
*/
public SqlTemplate getSqlTemplate(Connection conn) {
return DBUtils.getSqlTemplate(conn);
}
// -------------------------------------------------------------------------
/**
* Count the number of rows in a table.
*
* @param con
* The database connection to use. Should have been opened
* already.
* @param table
* The name of the table to analyse.
* @return The number of rows in the table.
* @deprecated use {@link DBUtils#countRowsInTable(Connection, String)}
*/
@Deprecated
public int countRowsInTable(Connection con, String table) {
return DBUtils.countRowsInTable(con, table);
} // countRowsInTable
// -------------------------------------------------------------------------
/**
* Use SELECT COUNT(*) to get a row count.
*
* @deprecated use {@link DBUtils#getRowCountFast(Connection, String)}
*/
@Deprecated
public int getRowCountFast(Connection con, String sql) {
return DBUtils.getRowCountFast(con, sql);
} // getRowCountFast
// -------------------------------------------------------------------------
/**
* Use a row-by-row approach to counting the rows in a table.
*
* @deprecated use {@link DBUtils#getRowCountSlow(Connection, String)}
*/
@Deprecated
public int getRowCountSlow(Connection con, String sql) {
return DBUtils.getRowCountSlow(con, sql);
} // getRowCountSlow
// -------------------------------------------------------------------------
/**
* Count the rows in a particular table or query.
*
* @param con
* A connection to the database. Should already be open.
* @param sql
* The SQL to execute. Note that if possible this should begin
* with <code>SELECT COUNT FROM</code> since this is much quicker
* to execute. If a standard SELECT statement is used, a
* row-by-row count will be performed, which may be slow if the
* table is large.
* @return The number of matching rows, or -1 if the query did not execute
* for some reason.
* @deprecated use {@link DBUtils#getRowCount(Connection, String)}
*/
@Deprecated
public int getRowCount(Connection con, String sql) {
return DBUtils.getRowCount(con, sql);
} // getRowCount
// -------------------------------------------------------------------------
/**
* Execute a SQL statement and return the value of one column of one row.
* Only the FIRST row matched is returned.
*
* @param con
* The Connection to use.
* @param sql
* The SQL to check; should return ONE value.
* @return The value returned by the SQL.
* @deprecated use {@link DBUtils#getRowColumnValue(Connection, String)}
*/
@Deprecated
public String getRowColumnValue(Connection con, String sql) {
return DBUtils.getRowColumnValue(con, sql);
} // DBUtils.getRowColumnValue
// -------------------------------------------------------------------------
/**
* Execute a SQL statement and return the value of the columns of one row.
* Only the FIRST row matched is returned.
*
* @param con
* The Connection to use.
* @param sql
* The SQL to check; can return several values.
* @return The value(s) returned by the SQL in an array of Strings.
* @deprecated use {@link DBUtils#getRowValues(Connection, String)}
*/
@Deprecated
public String[] getRowValues(Connection con, String sql) {
return DBUtils.getRowValues(con, sql);
} // getRowValues
@Deprecated
public List<String[]> getRowValuesList(Connection con, String sql) {
return DBUtils.getRowValuesList(con, sql);
}
// -------------------------------------------------------------------------
/**
* Execute a SQL statement and return the values of one column of the
* result.
*
* @param con
* The Connection to use.
* @param sql
* The SQL to check; should return ONE column.
* @return The value(s) making up the column, in the order that they were
* read.
* @deprecated use {@link DBUtils#getColumnValues(Connection, String)}
*/
@Deprecated
public String[] getColumnValues(Connection con, String sql) {
return DBUtils.getColumnValues(con, sql);
} // getColumnValues
// -------------------------------------------------------------------------
/**
* Execute a SQL statement and return the values of one column of the
* result.
*
* @param con
* The Connection to use.
* @param sql
* The SQL to check; should return ONE column.
* @return The value(s) making up the column, in the order that they were
* read.
* @deprecated use {@link DBUtils#getColumnValuesList(Connection, String)}
*/
@Deprecated
public List<String> getColumnValuesList(Connection con, String sql) {
return DBUtils.getColumnValuesList(con, sql);
} // getColumnValues
// -------------------------------------------------------------------------
/**
* Check for the presence of a particular String in a table column.
*
* @param con
* The database connection to use.
* @param table
* The name of the table to examine.
* @param column
* The name of the column to look in.
* @param str
* The string to search for; can use database wildcards (%, _)
* Note that if you want to search for one of these special
* characters, it must be backslash-escaped.
* @return The number of times the string is matched.
* @deprecated use
* {@link DBUtils#findStringInColumn(Connection, String, String, String)}
*/
@Deprecated
public int findStringInColumn(Connection con, String table, String column,
String str) {
return DBUtils.findStringInColumn(con, table, column, str);
} // findStringInColumn
// -------------------------------------------------------------------------
/**
* Check that all entries in column match a particular pattern.
*
* @param con
* The database connection to use.
* @param table
* The name of the table to examine.
* @param column
* The name of the column to look in.
* @param pattern
* The SQL pattern (can contain _,%) to look for.
* @return The number of columns that <em>DO NOT</em> match the pattern.
* @deprecated use
* {@link DBUtils#checkColumnPattern(Connection, String, String, String)}
*/
@Deprecated
public int checkColumnPattern(Connection con, String table, String column,
String pattern) {
return DBUtils.checkColumnPattern(con, table, column, pattern);
} // checkColumnPattern
// -------------------------------------------------------------------------
/**
* Check that all entries in column match a particular value.
*
* @param con
* The database connection to use.
* @param table
* The name of the table to examine.
* @param column
* The name of the column to look in.
* @param value
* The string to look for (not a pattern).
* @return The number of columns that <em>DO NOT</em> match value.
* @deprecated use
* {@link DBUtils#checkColumnValue(Connection, String, String, String)}
*/
@Deprecated
public int checkColumnValue(Connection con, String table, String column,
String value) {
return DBUtils.checkColumnValue(con, table, column, value);
} // checkColumnPattern
// -------------------------------------------------------------------------
/**
* Check if there are any blank entires in a column that is not supposed to
* be null.
*
* @param con
* The database connection to use.
* @param table
* The table to use.
* @param column
* The column to examine.
* @return An list of the row indices of any blank entries. Will be
* zero-length if there are none.
* @deprecated use
* {@link DBUtils#checkBlankNonNull(Connection, String, String)}
*/
@Deprecated
public List<String> checkBlankNonNull(Connection con, String table,
String column) {
return DBUtils.checkBlankNonNull(con, table, column);
} // checkBlankNonNull
// -------------------------------------------------------------------------
/**
* Check all columns of a table for blank entires in columns that are marked
* as being NOT NULL.
*
* @param con
* The database connection to use.
* @param table
* The table to use.
* @return The total number of blank null enums.
* @deprecated Use {@link DBUtils#checkBlankNonNull(Connection, String)}
*/
@Deprecated
public int checkBlankNonNull(Connection con, String table) {
return DBUtils.checkBlankNonNull(con, table);
} // checkBlankNonNull
// -------------------------------------------------------------------------
/**
* Check if a particular table exists in a database.
*
* @param con
* The database connection to check.
* @param table
* The table to check for.
* @return true if the table exists in the database.
* @deprecated use {@link DBUtils#checkTableExists}
*/
@Deprecated
public boolean checkTableExists(Connection con, String table) {
return DBUtils.checkTableExists(con, table);
} // checkTableExists
// -------------------------------------------------------------------------
// ----------------------------------------------------------------------
// ---------------------------------------------------------------------
/**
* Run different queries in two databases and compare the results
*
* @param con1
* Connection to database1
* @param sql1
* SQL query to run in database1
* @param con2
* Connection to database2
* @param sql2
* SQL query to run in database2
* @return true if both queries return the same rows.
*/
public boolean compareQueries(Connection con1, String sql1, Connection con2, String sql2) {
boolean result = true;
String dbName1 = (con1 == null) ? "no_database" : DBUtils.getShortDatabaseName(con1);
String dbName2 = (con2 == null) ? "no_database" : DBUtils.getShortDatabaseName(con2);
Map values1 = runQuery(con1, sql1);
Map values2 = runQuery(con2, sql2);
Iterator it1 = values1.keySet().iterator();
while (it1.hasNext()) {
String thisValue = (String) it1.next();
if (values2.get(thisValue) == null) {
result = false;
ReportManager.problem(this, dbName1, thisValue + " is not in " + dbName2);
}
} // foreach it1
Iterator it2 = values2.keySet().iterator();
while (it2.hasNext()) {
String thisValue = (String) it2.next();
if (values1.get(thisValue) == null) {
result = false;
ReportManager.problem(this, dbName2, thisValue + " is not in " + dbName1);
}
} // foreach it1
return result;
}
/**
* Run a query in a database and return the results as a HashMap where the keys are the rows (cols are concatenated with "::").
*
* @param con
* Connection to database
* @param sql
* SQL query to run in database
* @return Map where the keys are the rows (cols are concatenated with "::").
*/
private Map<String,String> runQuery(Connection con, String sql) {
Map<String,String> values = new HashMap<String,String>();
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
StringBuffer buf = new StringBuffer(rs.getString(1));
for (int a = 2; a <= rs.getMetaData().getColumnCount(); a++) {
buf.append("::");
buf.append(rs.getString(a));
}
values.put(buf.toString(), "1");
}
rs.close();
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
return values;
}
} // EnsTestCase