@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();
     }
   }
 }
  @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;
  }
  /**
   * 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());
 }
  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();
      }
    }
  }