@Override
public Gene[] getTranscripts(String sessID, GeneSet geneSet)
throws SQLException, SessionExpiredException {
SelectQuery query =
MedSavantDatabase.GeneSetTableSchema.where(
GENOME, geneSet.getReference(), TYPE, geneSet.getType())
.select(NAME, CHROM, START, END, CODING_START, CODING_END, TRANSCRIPT);
LOG.debug(query);
ResultSet rs = ConnectionController.executeQuery(sessID, query.toString());
Gene[] result = new Gene[geneSet.getSize()];
int i = 0;
while (rs.next()) {
result[i++] =
new Gene(
rs.getString(1),
rs.getString(2),
rs.getInt(3),
rs.getInt(4),
rs.getInt(5),
rs.getInt(6),
rs.getString(7));
}
return result;
}
@Override
public UserRole getRoleByName(String sessID, String roleName)
throws RemoteException, SessionExpiredException, SQLException {
String thisDatabase = SessionManager.getInstance().getDatabaseForSession(sessID);
TableSchema roleTable = MedSavantDatabase.UserRoleTableSchema;
SelectQuery sq = new SelectQuery();
sq.addFromTable(roleTable.getTable());
sq.addAllColumns();
sq.addCondition(
BinaryCondition.equalTo(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLENAME),
roleName));
ResultSet rs = null;
try {
rs = ConnectionController.executeQuery(sessID, sq.toString());
if (rs.next()) {
int roleId = rs.getInt(1);
String name = rs.getString(2);
String roleDescription = rs.getString(3);
return new UserRole(roleId, name, roleDescription, thisDatabase);
}
return null;
} finally {
if (rs != null) {
rs.close();
}
}
}
@Override
public Set<UserRole> getAllRoles(String sessID)
throws RemoteException, SQLException, SecurityException, SessionExpiredException {
String thisDatabase = SessionManager.getInstance().getDatabaseForSession(sessID);
TableSchema roleTable = MedSavantDatabase.UserRoleTableSchema;
SelectQuery sq = new SelectQuery();
sq.addFromTable(roleTable.getTable());
sq.addColumns(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ID),
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLENAME),
roleTable.getDBColumn(
MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLE_DESCRIPTION));
sq.setIsDistinct(true);
ResultSet rs = null;
try {
rs = ConnectionController.executeQuery(sessID, sq.toString());
Set<UserRole> roleSet = new TreeSet<UserRole>();
while (rs.next()) {
int roleId = rs.getInt(1);
String roleName = rs.getString(2);
String roleDescription = rs.getString(3);
roleSet.add(new UserRole(roleId, roleName, roleDescription, thisDatabase));
}
return roleSet;
} finally {
if (rs != null) {
rs.close();
}
}
}
/**
* Get the gene set for the given reference genome.
*
* @param sessID session ID
* @param refName reference name (not ID)
* @return
* @throws SQLException
*/
@Override
public GeneSet getGeneSet(String sessID, String refName)
throws SQLException, SessionExpiredException {
SelectQuery query =
MedSavantDatabase.GeneSetTableSchema.distinct()
.where(GENOME, refName)
.select(TYPE, "COUNT(DISTINCT name)");
ResultSet rs = ConnectionController.executeQuery(sessID, query.toString());
if (rs.next()) {
return new GeneSet(refName, rs.getString(1), rs.getInt(2));
}
return null;
}
/**
* Get a list of all available gene sets.
*
* @param sessID
* @return
* @throws SQLException
*/
@Override
public GeneSet[] getGeneSets(String sessID) throws SQLException, SessionExpiredException {
SelectQuery query =
MedSavantDatabase.GeneSetTableSchema.distinct()
.groupBy(GENOME)
.select(GENOME, TYPE, "COUNT(DISTINCT name)");
LOG.info("getGeneSets:" + query);
ResultSet rs = ConnectionController.executeQuery(sessID, query.toString());
List<GeneSet> result = new ArrayList<GeneSet>();
while (rs.next()) {
result.add(new GeneSet(rs.getString(1), rs.getString(2), rs.getInt(3)));
}
return result.toArray(new GeneSet[0]);
}
public static void main(String[] argv) {
TableSchema table = MedSavantDatabase.GeneSetTableSchema;
SelectQuery query =
MedSavantDatabase.GeneSetTableSchema.where(GENOME, "hg19", TYPE, "RefSeq")
.groupBy(CHROM)
.groupBy(NAME)
.select(NAME, CHROM, "MIN(start)", "MAX(end)", "MIN(codingStart)", "MAX(codingEnd)");
BinaryCondition dumbChrsCondition1 =
BinaryConditionMS.notlike(
table.getDBColumn(MedSavantDatabase.GeneSetColumns.CHROM), "%\\_%");
query.addCondition(dumbChrsCondition1);
BinaryCondition dumbChrsCondition2 =
BinaryConditionMS.notlike(
table.getDBColumn(MedSavantDatabase.GeneSetColumns.CHROM), "%\\-%");
query.addCondition(dumbChrsCondition2);
System.out.println(query.toString());
}
/** {@inheritDoc} */
@Override
public SelectQuery getQuery() {
SelectQuery selectQuery = new SelectQuery();
List<DbColumn> columns = dbTable.getColumns();
if (!columns.isEmpty()) {
selectQuery.addColumns(columns.toArray(new Column[columns.size()]));
} else {
selectQuery.addAllTableColumns(dbTable);
}
selectQuery.addFromTable(dbTable);
if (sort != null) {
selectQuery.addOrderings(sort);
}
applyFilters(selectQuery);
query = selectQuery;
return query;
}
@Override
public Gene[] getGenes(String sessID, GeneSet geneSet)
throws SQLException, SessionExpiredException {
TableSchema table = MedSavantDatabase.GeneSetTableSchema;
SelectQuery query =
MedSavantDatabase.GeneSetTableSchema.where(
GENOME, geneSet.getReference(), TYPE, geneSet.getType())
.groupBy(CHROM)
.groupBy(NAME)
.select(NAME, CHROM, "MIN(start)", "MAX(end)", "MIN(codingStart)", "MAX(codingEnd)");
BinaryCondition dumbChrsCondition =
BinaryConditionMS.notlike(
table.getDBColumn(MedSavantDatabase.GeneSetColumns.CHROM), "%\\_%");
query.addCondition(dumbChrsCondition);
BinaryCondition dumbNameCondition =
BinaryConditionMS.notlike(table.getDBColumn(MedSavantDatabase.GeneSetColumns.NAME), "%-%");
query.addCondition(dumbNameCondition);
System.out.println(query.toString());
LOG.info(query);
ResultSet rs = ConnectionController.executeQuery(sessID, query.toString());
Gene[] result = new Gene[geneSet.getSize()];
int i = 0;
while (rs.next()) {
Gene g =
new Gene(
rs.getString(1),
rs.getString(2),
rs.getInt(3),
rs.getInt(4),
rs.getInt(5),
rs.getInt(6),
null);
result[i++] = g;
}
if (i != result.length) {
LOG.info("There were " + result.length + " genes, but only " + i + " were loaded.");
}
result = Arrays.copyOf(result, i);
return result;
}
private Set<UserRole> getRolesForUser(String sessID, String user)
throws RemoteException, SQLException, SessionExpiredException {
String database = SessionManager.getInstance().getDatabaseForSession(sessID);
TableSchema roleTable = MedSavantDatabase.UserRoleTableSchema;
TableSchema roleATable = MedSavantDatabase.UserRoleAssignmentTableSchema;
SelectQuery sq = new SelectQuery();
sq.addColumns(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ID),
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLENAME),
roleTable.getDBColumn(
MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLE_DESCRIPTION));
Condition joinCondition =
BinaryCondition.equalTo(
roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ID),
roleATable.getDBColumn(
MedSavantDatabase.UserRoleAssignmentTableSchema.COLUMNNAME_OF_ROLE_ID));
sq.addJoin(
SelectQuery.JoinType.INNER, roleTable.getTable(), roleATable.getTable(), joinCondition);
sq.addCondition(
BinaryCondition.equalTo(
roleATable.getDBColumn(
MedSavantDatabase.UserRoleAssignmentTableSchema.COLUMNNAME_OF_USERNAME),
user));
ResultSet rs = null;
try {
rs = ConnectionController.executeQuery(sessID, sq.toString());
Set<UserRole> roleSet = new HashSet<UserRole>();
while (rs.next()) {
int roleId = rs.getInt(1);
String roleName = rs.getString(2);
String roleDescription = rs.getString(3);
roleSet.add(new UserRole(roleId, roleName, roleDescription, database));
}
return roleSet;
} finally {
if (rs != null) {
rs.close();
}
}
}
private void applyFilters(SelectQuery query) {
if (this.filters != null && this.filters.length > 0) {
ComboCondition comboCondition = new ComboCondition(ComboCondition.Op.AND);
if (filters.length > 0) {
for (int i = 0; i < filters.length; i++) {
Filter filter = filters[i];
FilterType filterType = filter.getFilterType();
DbColumn filterColumn = new DbColumn(dbTable, filter.getField(), "", null, null);
if (filterType.equals(FilterType.EQ)) {
comboCondition.addCondition(BinaryCondition.equalTo(filterColumn, filter.getValue()));
} else if (filterType.equals(FilterType.GT)) {
comboCondition.addCondition(
BinaryCondition.greaterThan(filterColumn, filter.getValue(), false));
} else if (filterType.equals(FilterType.LT)) {
comboCondition.addCondition(
BinaryCondition.lessThan(filterColumn, filter.getValue(), false));
} else if (filterType.equals(FilterType.GTE)) {
comboCondition.addCondition(
BinaryCondition.greaterThan(filterColumn, filter.getValue(), true));
} else if (filterType.equals(FilterType.LTE)) {
comboCondition.addCondition(
BinaryCondition.lessThan(filterColumn, filter.getValue(), true));
} else if (filterType.equals(FilterType.NEQ)) {
comboCondition.addCondition(
BinaryCondition.notEqualTo(filterColumn, filter.getValue()));
} else if (filterType.equals(FilterType.IN)) {
ComboCondition comboConditionOR = new ComboCondition(ComboCondition.Op.OR);
String[] condicion = filter.getValue().toString().split(",");
for (int z = 0; z < condicion.length; z++) {
comboConditionOR.addCondition(BinaryCondition.equalTo(filterColumn, condicion[z]));
}
comboCondition.addCondition(comboConditionOR);
} else {
throw new UnsupportedOperationException(
"Currently, the filter operation " + filterType + " is not supported");
}
}
}
query.addCondition(comboCondition);
}
}
