Examples with MatchedVCDocumentsFromSearch cbit.vcell.model.DBFormalSpecies.MatchedVCDocumentsFromSearch used on opensource projects

Example 1 with MatchedVCDocumentsFromSearch

use of cbit.vcell.model.DBFormalSpecies.MatchedVCDocumentsFromSearch in project vcell by virtualcell.

the class DictionaryDbDriver method getDatabaseSpecies.

/**
 * Insert the method's description here.
 * Creation date: (2/15/2003 10:12:24 PM)
 * @return cbit.vcell.dictionary.DBSpecies[]
 * @param con java.sql.Connection
 * @param user cbit.vcell.server.User
 * @param bOnlyUser boolean
 */
public DBFormalSpecies[] getDatabaseSpecies(Connection con, User user, String likeString, boolean isBound, FormalSpeciesType speciesType, int restrictSearch, int rowLimit, boolean bOnlyUser) throws SQLException {
    if (speciesType != null && speciesType.equals(FormalSpeciesType.speciesMatchSearch)) {
        FormalSpeciesType.MatchSearchFormalSpeciesType matchSearchFormalSpeciesType = (FormalSpeciesType.MatchSearchFormalSpeciesType) speciesType;
        if (matchSearchFormalSpeciesType.getMatchCriterias() == null || matchSearchFormalSpeciesType.getMatchCriterias().length == 0) {
            return null;
        }
        ArrayList<VCDocumentInfo> matchedVCDocumentInfos = new ArrayList<VCDocumentInfo>();
        Statement stmt = null;
        // OR condition
        // String sql =
        // "SELECT UNIQUE "  +BioModelTable.table.id.getQualifiedColName() +
        // " FROM "  + BioModelTable.table.getTableName() + "," + SpeciesContextModelTable.table.getTableName() +
        // " WHERE " + BioModelTable.table.modelRef.getQualifiedColName() + " = "+ SpeciesContextModelTable.table.modelRef.getQualifiedColName() +
        // " AND (";
        // for (int i = 0; i < matchSearchFormalSpeciesType.getMatchCriterias().length; i++) {
        // sql+=
        // (i>0?" OR ":"") +
        // " LOWER("+SpeciesContextModelTable.table.name.getQualifiedColName()+") LIKE " + "'" + matchSearchFormalSpeciesType.getMatchCriterias()[i] + "'" + " ESCAPE '"+BeanUtils.SQL_ESCAPE_CHARACTER+"'";
        // }
        // sql+=")";
        // AND condition
        String sql = "";
        for (int i = 0; i < matchSearchFormalSpeciesType.getMatchCriterias().length; i++) {
            sql += (i > 0 ? " INTERSECT " : "") + "SELECT UNIQUE " + BioModelTable.table.id.getQualifiedColName() + " FROM " + BioModelTable.table.getTableName() + "," + SpeciesContextModelTable.table.getTableName() + " WHERE " + BioModelTable.table.modelRef.getQualifiedColName() + " = " + SpeciesContextModelTable.table.modelRef.getQualifiedColName() + " AND " + " LOWER(" + SpeciesContextModelTable.table.name.getQualifiedColName() + ") LIKE " + "'" + matchSearchFormalSpeciesType.getMatchCriterias()[i] + "'" + " ESCAPE '" + BeanUtils.SQL_ESCAPE_CHARACTER + "'";
        }
        try {
            stmt = con.createStatement();
            ResultSet rset = stmt.executeQuery(sql);
            while (rset.next()) {
                BigDecimal versionKey = rset.getBigDecimal(1);
                Version version = new Version(new KeyValue(versionKey), null, null, null, null, null, null, null, null);
                matchedVCDocumentInfos.add(new BioModelInfo(version, null, (BioModelChildSummary) null, null));
            }
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        if (matchedVCDocumentInfos.size() == 0) {
            return null;
        }
        return new DBFormalSpecies[] { new MatchedVCDocumentsFromSearch(matchedVCDocumentInfos) };
    }
    if (!speciesType.bValidProperties(restrictSearch)) {
        throw new IllegalArgumentException("Improper properties in restrictSearch");
    }
    if (bOnlyUser && isBound == false) {
        throw new IllegalArgumentException("user not null expects isBound = true");
    }
    if (!((rowLimit == -1) || (rowLimit > 0))) {
        throw new IllegalArgumentException("rowLimit must be -1(Unlimited) or greater than 0");
    }
    if ((((restrictSearch & FormalSpeciesType.COMPOUND_ID) != 0) && (restrictSearch != FormalSpeciesType.COMPOUND_ID)) || (((restrictSearch & FormalSpeciesType.ENZYME_ID) != 0) && (restrictSearch != FormalSpeciesType.ENZYME_ID)) || (((restrictSearch & FormalSpeciesType.PROTEIN_ID) != 0) && (restrictSearch != FormalSpeciesType.PROTEIN_ID))) {
        throw new RuntimeException("Incompatible search properties together");
    }
    likeString = likeString.toUpperCase();
    // Columns always needed
    String columns = (speciesType.equals(FormalSpeciesType.compound) ? CompoundTable.table.getTableName() + ".*" + "," + CompoundAliasTable.table.name.getQualifiedColName() + "," + CompoundAliasTable.table.preferred.getQualifiedColName() : "") + (speciesType.equals(FormalSpeciesType.enzyme) ? EnzymeTable.table.getTableName() + ".*" + "," + EnzymeAliasTable.table.name.getQualifiedColName() + "," + EnzymeAliasTable.table.preferred.getQualifiedColName() : "") + (speciesType.equals(FormalSpeciesType.protein) ? ProteinTable.table.getTableName() + ".*" + "," + ProteinAliasTable.table.name.getQualifiedColName() + "," + ProteinAliasTable.table.preferred.getQualifiedColName() : "");
    // Columns if Bound (aliased because id in other tables)
    String dbSpeciesTable_id_alias = "dbspecies_id";
    if (isBound) {
        columns = columns + "," + DBSpeciesTable.table.id.getQualifiedColName() + " " + dbSpeciesTable_id_alias;
    }
    // Tables always needed
    String tables = (speciesType.equals(FormalSpeciesType.compound) ? CompoundTable.table.getTableName() + "," + CompoundAliasTable.table.getTableName() : "") + (speciesType.equals(FormalSpeciesType.enzyme) ? EnzymeTable.table.getTableName() + "," + EnzymeAliasTable.table.getTableName() : "") + (speciesType.equals(FormalSpeciesType.protein) ? ProteinTable.table.getTableName() + "," + ProteinAliasTable.table.getTableName() : "");
    // Tables if Bound
    if (isBound) {
        tables = tables + "," + DBSpeciesTable.table.getTableName();
        if (bOnlyUser) {
            tables = tables + "," + SpeciesContextModelTable.table.getTableName() + "," + SpeciesTable.table.getTableName() + "," + ModelTable.table.getTableName();
        }
    }
    String condition = "";
    // Conditions always needed
    condition = condition + (speciesType.equals(FormalSpeciesType.compound) ? CompoundTable.table.id.getQualifiedColName() + "=" + CompoundAliasTable.table.compoundRef.getQualifiedColName() : "") + (speciesType.equals(FormalSpeciesType.enzyme) ? EnzymeTable.table.id.getQualifiedColName() + "=" + EnzymeAliasTable.table.enzymeRef.getQualifiedColName() : "") + (speciesType.equals(FormalSpeciesType.protein) ? ProteinTable.table.id.getQualifiedColName() + "=" + ProteinAliasTable.table.proteinRef.getQualifiedColName() : "");
    java.util.Vector<String> likeConditions = new java.util.Vector<String>();
    if (speciesType.equals(FormalSpeciesType.compound)) {
        if ((restrictSearch & FormalSpeciesType.COMPOUND_ID) != 0) {
            likeConditions.add(" UPPER(" + CompoundTable.table.id.getQualifiedColName() + ") = " + likeString);
        }
        if ((restrictSearch & FormalSpeciesType.COMPOUND_ALIAS) != 0) {
            likeConditions.add(" UPPER(" + CompoundAliasTable.table.name.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.COMPOUND_KEGGID) != 0) {
            likeConditions.add(" UPPER(" + CompoundTable.table.keggID.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.COMPOUND_CASID) != 0) {
            likeConditions.add(" UPPER(" + CompoundTable.table.casID.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.COMPOUND_FORMULA) != 0) {
            likeConditions.add(" UPPER(" + CompoundTable.table.formula.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
    } else if (speciesType.equals(FormalSpeciesType.enzyme)) {
        if ((restrictSearch & FormalSpeciesType.ENZYME_ID) != 0) {
            likeConditions.add(" UPPER(" + EnzymeTable.table.id.getQualifiedColName() + ") = " + likeString);
        }
        if ((restrictSearch & FormalSpeciesType.ENZYME_ALIAS) != 0) {
            likeConditions.add(" UPPER(" + EnzymeAliasTable.table.name.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.ENZYME_SYSNAME) != 0) {
            likeConditions.add(" UPPER(" + EnzymeTable.table.sysname.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.ENZYME_ECNUMBER) != 0) {
            likeConditions.add(" UPPER(" + EnzymeTable.table.ecNumber.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        // if((restrictSearch & FormalSpeciesType.ENZYME_ECNUMBER) != 0){likeConditions.add(" UPPER("+EnzymeTable.table.ecNumber.getQualifiedColName() + ") LIKE " + "'EC "+likeString+"'");}
        if ((restrictSearch & FormalSpeciesType.ENZYME_REACTION) != 0) {
            likeConditions.add(" UPPER(" + EnzymeTable.table.reaction.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.ENZYME_CASID) != 0) {
            likeConditions.add(" UPPER(" + EnzymeTable.table.casID.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
    } else if (speciesType.equals(FormalSpeciesType.protein)) {
        if ((restrictSearch & FormalSpeciesType.PROTEIN_ID) != 0) {
            likeConditions.add(" UPPER(" + ProteinTable.table.id.getQualifiedColName() + ") = " + likeString);
        }
        if ((restrictSearch & FormalSpeciesType.PROTEIN_ALIAS) != 0) {
            likeConditions.add(" UPPER(" + ProteinAliasTable.table.name.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.PROTEIN_ACCESSION) != 0) {
            likeConditions.add(" UPPER(" + ProteinTable.table.accessionNumber.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.PROTEIN_SWISSPROTID) != 0) {
            likeConditions.add(" UPPER(" + ProteinTable.table.swissProtEntryName.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.PROTEIN_ORGANISM) != 0) {
            likeConditions.add(" UPPER(" + ProteinTable.table.organism.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.PROTEIN_KEYWORD) != 0) {
            likeConditions.add(" UPPER(" + ProteinTable.table.keywords.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
        if ((restrictSearch & FormalSpeciesType.PROTEIN_DESCR) != 0) {
            likeConditions.add(" UPPER(" + ProteinTable.table.description.getQualifiedColName() + ") LIKE " + "'" + likeString + "'" + " ESCAPE '/'");
        }
    } else {
        throw new RuntimeException(speciesType.getName() + " Unsupported");
    }
    if (likeConditions.size() > 0) {
        condition = condition + " AND ( ";
        for (int i = 0; i < likeConditions.size(); i += 1) {
            String newCondition = (String) likeConditions.get(i);
            if (i != 0) {
                condition = condition + " OR ";
            }
            condition = condition + newCondition;
        }
        condition = condition + ")";
    }
    if (isBound) {
        condition = condition + " AND " + (speciesType.equals(FormalSpeciesType.compound) ? CompoundTable.table.id.getQualifiedColName() + "=" + DBSpeciesTable.table.compoundRef.getQualifiedColName() : "") + (speciesType.equals(FormalSpeciesType.enzyme) ? EnzymeTable.table.id.getQualifiedColName() + "=" + DBSpeciesTable.table.enzymeRef.getQualifiedColName() : "") + (speciesType.equals(FormalSpeciesType.protein) ? ProteinTable.table.id.getQualifiedColName() + "=" + DBSpeciesTable.table.proteinRef.getQualifiedColName() : "");
        if (bOnlyUser) {
            condition = condition + " AND " + SpeciesContextModelTable.table.speciesRef.getQualifiedColName() + " = " + SpeciesTable.table.id.getQualifiedColName() + " AND " + SpeciesTable.table.dbSpeciesRef.getQualifiedColName() + " = " + DBSpeciesTable.table.id.getQualifiedColName() + " AND " + SpeciesContextModelTable.table.modelRef.getQualifiedColName() + " = " + ModelTable.table.id.getQualifiedColName() + " AND " + ModelTable.table.ownerRef.getQualifiedColName() + " = " + user.getID();
        }
    }
    // ORDER BY 'id' must not be changed, used to collect multiple aliasnames into same info object
    String orderBy = (speciesType.equals(FormalSpeciesType.compound) ? CompoundTable.table.id.getQualifiedColName() : "") + (speciesType.equals(FormalSpeciesType.enzyme) ? EnzymeTable.table.id.getQualifiedColName() : "") + (speciesType.equals(FormalSpeciesType.protein) ? ProteinTable.table.id.getQualifiedColName() : "");
    String sql = "SELECT " + columns + " FROM " + tables + " WHERE " + condition + " ORDER BY " + orderBy;
    DBFormalSpecies[] databaseSpecies = null;
    Statement stmt = con.createStatement();
    if (rowLimit > 0) {
        stmt.setMaxRows(rowLimit);
    }
    try {
        ResultSet rset = stmt.executeQuery(sql);
        if (speciesType.equals(FormalSpeciesType.compound)) {
            databaseSpecies = CompoundTable.table.getCompounds(rset, isBound);
        } else if (speciesType.equals(FormalSpeciesType.enzyme)) {
            databaseSpecies = EnzymeTable.table.getEnzymes(rset, isBound);
        } else if (speciesType.equals(FormalSpeciesType.protein)) {
            databaseSpecies = ProteinTable.table.getProteins(rset, isBound);
        } else {
            throw new RuntimeException("FormalSpeciesType=" + speciesType.getName() + " Unsupported");
        }
    } finally {
        // Release resources include resultset
        stmt.close();
    }
    // 
    return databaseSpecies;
}