/**
* @param dbre
* @param template
* @return
*/
private boolean checkAssembly(DatabaseRegistryEntry dbre, SqlTemplate template, int speciesId) {
boolean passes = true;
// 1. get assembly.default
String assDef = template.queryForDefaultObject(META_QUERY, String.class, ASS_DEF, speciesId);
if (StringUtils.isEmpty(assDef)) {
passes = false;
ReportManager.problem(
this,
dbre.getConnection(),
"Meta value for " + ASS_DEF + " is not set for species " + speciesId);
}
if (passes) {
// 2. check to see if it matches the default coord_system version
String version =
template.queryForDefaultObject(COORD_VERSION_QUERY, String.class, speciesId, speciesId);
if (!assDef.equals(version)) {
passes = false;
ReportManager.problem(
this,
dbre.getConnection(),
"Meta value for "
+ ASS_DEF
+ " ("
+ assDef
+ ") does not match min rank coord_system version "
+ version
+ " for species "
+ speciesId);
}
}
return passes;
}
private boolean checkCodingCountAttributes(final DatabaseRegistryEntry dbre) {
boolean result = true;
Connection con = dbre.getConnection();
String code = (dbre.getType() == DatabaseType.SANGER_VEGA) ? "KnwnPCCount" : "coding_cnt";
SqlTemplate t = DBUtils.getSqlTemplate(dbre);
String sql =
"select distinct g.seq_region_id from gene g where g.biotype = ? and g.seq_region_id not in (select distinct g.seq_region_id from gene g, seq_region_attrib sa, attrib_type at where g.seq_region_id = sa.seq_region_id and sa.attrib_type_id = at.attrib_type_id and at.code in (?,?))";
List<String> toplevel =
t.queryForDefaultObjectList(sql, String.class, "protein_coding", "LRG", "non_ref");
sql =
"select distinct g.seq_region_id from gene g, seq_region_attrib sa, attrib_type at where g.seq_region_id = sa.seq_region_id and sa.attrib_type_id = at.attrib_type_id and code =? ";
List<String> known = t.queryForDefaultObjectList(sql, String.class, code);
Set<String> missing = new HashSet<String>(toplevel);
missing.removeAll(known);
if (missing.isEmpty()) {
ReportManager.correct(
this,
con,
"All seq_regions with protein_coding genes have a coding_cnt attribute associated with them");
} else {
String msg =
String.format(
"%s regions with protein_coding genes do not have the coding_cnt attribute associated",
missing.size());
ReportManager.problem(this, con, msg);
result = false;
}
return result;
}
/**
* @param dbre
* @param template
* @return
*/
private boolean checkGeneBuild(DatabaseRegistryEntry dbre, SqlTemplate template, int speciesId) {
boolean passes = true;
String gbStart = template.queryForDefaultObject(META_QUERY, String.class, GB_START, speciesId);
if (StringUtils.isEmpty(gbStart)) {
passes = false;
ReportManager.problem(
this, dbre.getConnection(), "Meta value for " + GB_START + " is not set ");
}
return passes;
}
protected int fetchToplevelSeqRegionCount() {
List<Integer> numSeqRegionsList =
sqlTemplateTestDb.queryForDefaultObjectList(
"select count(*) from seq_region join seq_region_attrib using (seq_region_id) join attrib_type using (attrib_type_id) where code='toplevel'",
Integer.class);
assertLengthIsOne(numSeqRegionsList);
Integer numSeqRegions = numSeqRegionsList.get(0);
return numSeqRegions;
}
protected boolean runTest(DatabaseRegistryEntry dbre) {
boolean passes = true;
SqlTemplate template = getTemplate(dbre);
for (String metaKey : metaKeys) {
Map<String, Integer> map = template.queryForMap(META_QUERY, mapper, metaKey);
for (Entry<String, Integer> e : map.entrySet()) {
if (e.getValue() > 1) {
ReportManager.problem(
this,
dbre.getConnection(),
"Duplicate value "
+ e.getKey()
+ " for "
+ metaKey
+ " found for "
+ e.getValue()
+ " species");
}
}
}
return passes;
}
protected boolean runTest(DatabaseRegistryEntry dbre) {
SqlTemplate stmp = getSqlTemplate(dbre);
boolean passes = true;
Integer genes = stmp.queryForDefaultObject(GENE_QUERY, Integer.class);
if (genes > 0) {
ReportManager.problem(this, dbre.getConnection(), genes + " genes have invalid stable IDs");
ReportManager.problem(
this,
dbre.getConnection(),
"USEFUL SQL: select stable_id from gene where stable_id NOT REGEXP '^[[:alpha:]]{4}[[:digit:]]{6}$';");
passes = false;
}
Integer transcripts = stmp.queryForDefaultObject(TRANSCRIPT_QUERY, Integer.class);
if (transcripts > 0) {
ReportManager.problem(
this, dbre.getConnection(), transcripts + " transcripts have invalid stable IDs");
ReportManager.problem(
this,
dbre.getConnection(),
"USEFUL SQL: select stable_id from transcript where stable_id NOT REGEXP '^[[:alpha:]]{4}[[:digit:]]{6}[[.-.]]R[[:alpha:]]$';");
passes = false;
}
Integer translations = stmp.queryForDefaultObject(TRANSLATION_QUERY, Integer.class);
if (translations > 0) {
ReportManager.problem(
this, dbre.getConnection(), translations + " translations have invalid stable IDs");
ReportManager.problem(
this,
dbre.getConnection(),
"USEFUL SQL: select stable_id from translation where stable_id NOT REGEXP '^[[:alpha:]]{4}[[:digit:]]{6}[[.-.]]P[[:alpha:]]$';");
passes = false;
}
return passes;
}
@Override
protected boolean runTest(DatabaseRegistryEntry dbre) {
init(dbre);
List<Integer> allSpeciesIds =
sqlTemplateTestDb.queryForDefaultObjectList(
"select distinct species_id from meta where species_id is not null", Integer.class);
if (allSpeciesIds.size() == 0) {
ReportManager.problem(this, testDbConn, "No species configured!");
}
boolean allSpeciesPassed = true;
for (int speciesId : allSpeciesIds) {
allSpeciesPassed &= runTestForSpecies(dbre, speciesId);
}
return allSpeciesPassed;
}
/**
* @param sqlTemplateTestDb
* @param metaKey
* @return
*/
protected String fetchSingleMetaValueFor(
final SqlTemplate sqlTemplateTestDb, int speciesId, String metaKey) {
String sql =
"select meta_value from meta where meta.meta_key = '"
+ metaKey
+ "' and species_id="
+ speciesId;
List<String> metaValueList = sqlTemplateTestDb.queryForDefaultObjectList(sql, String.class);
if (metaValueList.size() > 1) {
throw new RuntimeException(
"Got more than one meta_value for metaKey " + metaKey + ". Expected only one!\n" + sql);
}
if (metaValueList.size() == 0) {
throw new RuntimeException("Metakey " + metaKey + " is missing in the meta table!\n" + sql);
}
String metaValue = metaValueList.get(0);
return metaValue;
}
// ---------------------------------------------------------------------
private int metaKeyCount(Connection con, String metaKey) {
String sql = "select count(*) from meta where meta_key =?";
SqlTemplate t = getSqlTemplate(con);
return t.queryForDefaultObject(sql, Integer.class, metaKey);
}
protected boolean assertToplevelSeqRegionInDnaFragTable(final int genomeDbId) {
final EnsTestCase thisTest = this;
Boolean allRowsExistInDnaFragTable =
sqlTemplateTestDb.execute(
"select"
+ " seq_region.seq_region_id, "
+ " seq_region.name, "
+ " seq_region.length, "
+ " coord_system.name "
+ "from "
+ " seq_region join seq_region_attrib using (seq_region_id) "
+ " join attrib_type using (attrib_type_id) "
+ " join coord_system using (coord_system_id) "
+ "where "
+ "code='toplevel' ",
new ResultSetCallback<Boolean>() {
@Override
public Boolean process(ResultSet rs) throws SQLException {
SeqRegionData seqRegionData = new SeqRegionData();
int missingRows = 0;
boolean allRowsExistInDnaFragTable = true;
while (rs.next()) {
seqRegionData.seq_region_id = rs.getInt(1);
seqRegionData.seq_region_name = rs.getString(2);
seqRegionData.seq_region_length = rs.getInt(3);
seqRegionData.coord_system_name = rs.getString(4);
int numCorrespondingRowsInDnaFragTable =
fetchNumCorrespondingRowsInDnaFragTable(seqRegionData, genomeDbId);
boolean currentRowExistsInDnaFragTable = false;
if (numCorrespondingRowsInDnaFragTable == 1) {
currentRowExistsInDnaFragTable = true;
}
if (numCorrespondingRowsInDnaFragTable == 0) {
ReportManager.problem(
thisTest,
testDbConn,
"The following seq region is not in the dnafrag table in the master database:\n"
+ seqRegionData);
ReportManager.problem(
thisTest,
testDbConn,
"The seq region that comes up with this sql in the core database:\n\n"
+ createUsefulSqlCore(seqRegionData)
+ "\n\n"
+ "should come up with this sql:\n\n"
+ createUsefulSqlMaster(seqRegionData, genomeDbId));
missingRows++;
if (missingRows >= reportMaxMissingRows) {
ReportManager.problem(
thisTest,
testDbConn,
"No more rows will be reported, because the maximum of "
+ reportMaxMissingRows
+ " has been reached.");
return false;
}
currentRowExistsInDnaFragTable = false;
}
if (numCorrespondingRowsInDnaFragTable > 1) {
throw new RuntimeException(
"Unexpected value for numCorrespondingRowsInDnaFragTable:"
+ numCorrespondingRowsInDnaFragTable);
}
allRowsExistInDnaFragTable &= currentRowExistsInDnaFragTable;
}
return allRowsExistInDnaFragTable;
}
},
// No bound parameters
//
new Object[0]);
return allRowsExistInDnaFragTable;
}
