Examples with OuterJoin cbit.vcell.modeldb.DatabasePolicySQL.OuterJoin used on opensource projects

Example 16 with OuterJoin

use of cbit.vcell.modeldb.DatabasePolicySQL.OuterJoin in project vcell by virtualcell.

the class ImageTable method getInfoSQL.

/**
 * This method was created in VisualAge.
 * @return java.lang.String
 */
public String getInfoSQL(User user, String extraConditions, String special, boolean bCheckPermission, DatabaseSyntax dbSyntax) {
    UserTable userTable = UserTable.table;
    ImageTable iTable = this;
    ExtentTable eTable = ExtentTable.table;
    BrowseImageDataTable bTable = BrowseImageDataTable.table;
    SoftwareVersionTable swvTable = SoftwareVersionTable.table;
    String sql;
    Field[] f = { userTable.userid, new cbit.sql.StarField(iTable), eTable.extentX, eTable.extentY, eTable.extentZ, bTable.data, swvTable.softwareVersion };
    Table[] t = { iTable, userTable, eTable, bTable, swvTable };
    switch(dbSyntax) {
        case ORACLE:
            {
                String condition = iTable.extentRef.getQualifiedColName() + " = " + eTable.id.getQualifiedColName() + " AND " + bTable.imageRef.getQualifiedColName() + " = " + iTable.id.getQualifiedColName() + " AND " + userTable.id.getQualifiedColName() + " = " + // links in the userTable
                iTable.ownerRef.getQualifiedColName() + " AND " + iTable.id.getQualifiedColName() + " = " + swvTable.versionableRef.getQualifiedColName() + "(+) ";
                if (extraConditions != null && extraConditions.trim().length() > 0) {
                    condition += " AND " + extraConditions;
                }
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, special, dbSyntax, bCheckPermission);
                return sql;
            }
        case POSTGRES:
            {
                String condition = iTable.extentRef.getQualifiedColName() + " = " + eTable.id.getQualifiedColName() + " AND " + bTable.imageRef.getQualifiedColName() + " = " + iTable.id.getQualifiedColName() + " AND " + userTable.id.getQualifiedColName() + " = " + iTable.ownerRef.getQualifiedColName() + // links in the userTable
                " ";
                // " AND " + iTable.id.getQualifiedColName() + " = " + swvTable.versionableRef.getQualifiedColName()+"(+) ";
                if (extraConditions != null && extraConditions.trim().length() > 0) {
                    condition += " AND " + extraConditions;
                }
                OuterJoin outerJoin = new OuterJoin(iTable, swvTable, JoinOp.LEFT_OUTER_JOIN, iTable.id, swvTable.versionableRef);
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, outerJoin, condition, special, dbSyntax, bCheckPermission);
                return sql;
            }
        default:
            {
                throw new RuntimeException("unexpected DatabaseSyntax " + dbSyntax);
            }
    }
}

Example 17 with OuterJoin

use of cbit.vcell.modeldb.DatabasePolicySQL.OuterJoin in project vcell by virtualcell.

the class MathModelDbDriver method getMathModelMetaData.

/**
 * getModel method comment.
 */
private MathModelMetaData getMathModelMetaData(Connection con, User user, KeyValue mathModelKey) throws SQLException, DataAccessException, ObjectNotFoundException {
    if (user == null || mathModelKey == null) {
        throw new IllegalArgumentException("Improper parameters for getMathModelMetaData");
    }
    if (lg.isTraceEnabled())
        lg.trace("MathModelDbDriver.getMathModelMetaData(user=" + user + ", id=" + mathModelKey + ")");
    // 
    // to construct a MathModelMetaData as an immutable object, lets collect all keys first
    // (even before authentication).  If the user doesn't authenticate, then throw away the
    // child keys (from link tables).
    // 
    // 
    // get Simulation Keys for mathModelKey
    // 
    KeyValue[] simKeys = getSimulationEntriesFromMathModel(con, mathModelKey);
    // 
    // get MathModelMetaData object for mathModelKey
    // 
    String sql;
    Field[] f = { new cbit.sql.StarField(mathModelTable), userTable.userid };
    Table[] t = { mathModelTable, userTable };
    String condition = mathModelTable.id.getQualifiedColName() + " = " + mathModelKey + " AND " + userTable.id.getQualifiedColName() + " = " + mathModelTable.ownerRef.getQualifiedColName();
    sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, null, dbSyntax, true);
    Statement stmt = con.createStatement();
    MathModelMetaData mathModelMetaData = null;
    try {
        ResultSet rset = stmt.executeQuery(sql);
        if (rset.next()) {
            mathModelMetaData = mathModelTable.getMathModelMetaData(rset, con, simKeys, dbSyntax);
        } else {
            throw new ObjectNotFoundException("MathModel id=" + mathModelKey + " not found for user '" + user + "'");
        }
    } finally {
        // Release resources include resultset
        stmt.close();
    }
    return mathModelMetaData;
}

Example 18 with OuterJoin

use of cbit.vcell.modeldb.DatabasePolicySQL.OuterJoin in project vcell by virtualcell.

the class ReactStepTable method getSQLUserReactionListQuery.

/**
 * Insert the method's description here.
 * Creation date: (7/12/2003 2:59:27 PM)
 * @return java.lang.String
 * @param likeString java.lang.String
 */
public String getSQLUserReactionListQuery(ReactionQuerySpec rqs, User user, DatabaseSyntax dbSyntax) {
    String reactant_or_flux_likeString = rqs.getReactantLikeString();
    String catalyst_likeString = rqs.getCatalystLikeString();
    String product_likeString = rqs.getProductLikeString();
    DBFormalSpecies reactant_or_flux_dbspecies = rqs.getReactantBoundSpecies();
    DBFormalSpecies catalyst_dbspecies = rqs.getCatalystBoundSpecies();
    DBFormalSpecies product_dbspecies = rqs.getProductBoundSpecies();
    String repWildCard = rqs.getAnyReactionParticipantLikeString();
    DBFormalSpecies typeWildCard = rqs.getAnyReactionParticipantBoundSpecies();
    // Get list of distinct USER reactions that are visible to user
    // 
    // Create comma-separated lists of dbspeciesid
    // 
    StringBuffer reactant_flux_list = new StringBuffer();
    StringBuffer catalyst_list = new StringBuffer();
    StringBuffer product_list = new StringBuffer();
    if (reactant_or_flux_dbspecies != null) {
        reactant_flux_list.append(reactant_or_flux_dbspecies.getDBFormalSpeciesKey().toString());
    }
    if (catalyst_dbspecies != null) {
        catalyst_list.append(catalyst_dbspecies.getDBFormalSpeciesKey().toString());
    }
    if (product_dbspecies != null) {
        product_list.append(product_dbspecies.getDBFormalSpeciesKey().toString());
    }
    // Creat conditions for flux,reaction,catalyst,product
    boolean[] bNeedsDBSpeciesTableArr = new boolean[3];
    StringBuffer[] subConditionsArr = new StringBuffer[3];
    for (int i = 0; i < 3; i += 1) {
        boolean bHasLike = false;
        boolean bHasDBspid = false;
        StringBuffer subConditions = new StringBuffer();
        subConditionsArr[i] = subConditions;
        if (i == 0) {
            bHasLike = (reactant_or_flux_likeString != null && reactant_or_flux_likeString.length() > 0);
            bHasDBspid = (reactant_or_flux_dbspecies != null);
            if (!bHasLike && !bHasDBspid) {
                continue;
            }
            subConditions.append("(");
            subConditions.append("(vc_reactpart_sub.role = 'reactant' OR vc_reactpart_sub.role = 'flux') AND ");
            if (bHasLike) {
                subConditions.append("upper(vc_species_sub.commonname) LIKE upper('" + reactant_or_flux_likeString + "')");
                if (bHasDBspid) {
                    subConditions.append(" OR ");
                }
            }
            if (bHasDBspid) {
                bNeedsDBSpeciesTableArr[i] = true;
                // subConditions.append("vc_species_sub.dbspeciesref IN ("+reactant_flux_list.toString()+")");
                subConditions.append("vc_species_sub.dbspeciesref = vc_dbspecies_sub.id AND (");
                subConditions.append("vc_dbspecies_sub.compoundref IN (" + reactant_flux_list.toString() + ") OR ");
                subConditions.append("vc_dbspecies_sub.enzymeref IN (" + reactant_flux_list.toString() + ") OR ");
                subConditions.append("vc_dbspecies_sub.proteinref IN (" + reactant_flux_list.toString() + ")");
                subConditions.append(")");
            }
            subConditions.append(")");
        } else if (i == 1) {
            bHasLike = (catalyst_likeString != null && catalyst_likeString.length() > 0);
            bHasDBspid = (catalyst_dbspecies != null);
            if (!bHasLike && !bHasDBspid) {
                continue;
            }
            if (subConditions.length() > 0) {
                subConditions.append(" AND ");
            }
            subConditions.append("(");
            subConditions.append("vc_reactpart_sub.role = 'catalyst' AND ");
            if (bHasLike) {
                subConditions.append("upper(vc_species_sub.commonname) LIKE upper('" + catalyst_likeString + "')");
                if (bHasDBspid) {
                    subConditions.append(" OR ");
                }
            }
            if (bHasDBspid) {
                bNeedsDBSpeciesTableArr[i] = true;
                // subConditions.append("vc_species_sub.dbspeciesref IN ("+catalyst_list.toString()+")");
                subConditions.append("vc_species_sub.dbspeciesref = vc_dbspecies_sub.id AND (");
                subConditions.append("vc_dbspecies_sub.compoundref IN (" + catalyst_list.toString() + ") OR ");
                subConditions.append("vc_dbspecies_sub.enzymeref IN (" + catalyst_list.toString() + ") OR ");
                subConditions.append("vc_dbspecies_sub.proteinref IN (" + catalyst_list.toString() + ")");
                subConditions.append(")");
            }
            subConditions.append(")");
        } else if (i == 2) {
            bHasLike = (product_likeString != null && product_likeString.length() > 0);
            bHasDBspid = (product_dbspecies != null);
            if (!bHasLike && !bHasDBspid) {
                continue;
            }
            if (subConditions.length() > 0) {
                subConditions.append(" AND ");
            }
            subConditions.append("(");
            subConditions.append("vc_reactpart_sub.role = 'product' AND ");
            if (bHasLike) {
                subConditions.append("upper(vc_species_sub.commonname) LIKE upper('" + product_likeString + "')");
                if (bHasDBspid) {
                    subConditions.append(" OR ");
                }
            }
            if (bHasDBspid) {
                bNeedsDBSpeciesTableArr[i] = true;
                // subConditions.append("vc_species_sub.dbspeciesref IN ("+product_list.toString()+")");
                subConditions.append("vc_species_sub.dbspeciesref = vc_dbspecies_sub.id AND (");
                subConditions.append("vc_dbspecies_sub.compoundref IN (" + product_list.toString() + ") OR ");
                subConditions.append("vc_dbspecies_sub.enzymeref IN (" + product_list.toString() + ") OR ");
                subConditions.append("vc_dbspecies_sub.proteinref IN (" + product_list.toString() + ")");
                subConditions.append(")");
            }
            subConditions.append(")");
        }
    }
    // 
    String sql = null;
    Field specialBMField = new Field("id bmid", SQLDataType.integer, "");
    specialBMField.setTableName(BioModelTable.table.getTableName());
    Field[] f = { // 1
    ReactStepTable.table.name, // 2
    ReactStepTable.table.id, // 3
    ReactStepTable.table.reactType, // 4
    ReactPartTable.table.role, // 5
    ReactPartTable.table.stoich, // 6
    SpeciesTable.table.commonName, // 7
    specialBMField, // 8
    ReactStepTable.table.structRef };
    Table[] t = { ReactStepTable.table, BioModelTable.table, ReactPartTable.table, SpeciesContextModelTable.table, SpeciesTable.table };
    // 
    // 
    // Non-WildCard subcondition
    boolean hadPreviousCondiiton = false;
    String searchConditions = "";
    for (int i = 0; i < 3; i += 1) {
        if (subConditionsArr[i].length() > 0) {
            // 
            if (hadPreviousCondiiton) {
                searchConditions += " INTERSECT ";
            }
            hadPreviousCondiiton = true;
            // 
            searchConditions += "(" + " SELECT " + Table.SQL_GLOBAL_HINT + " DISTINCT " + " vc_reactstep_sub.id " + " FROM " + ReactStepTable.table.getTableName() + " vc_reactstep_sub" + "," + ReactPartTable.table.getTableName() + " vc_reactpart_sub" + "," + SpeciesContextModelTable.table.getTableName() + " vc_modelsc_sub" + "," + SpeciesTable.table.getTableName() + " vc_species_sub" + (bNeedsDBSpeciesTableArr[i] ? "," + DBSpeciesTable.table.getTableName() + " vc_dbspecies_sub" : "") + " WHERE " + subConditionsArr[i].toString() + " AND " + " vc_species_sub.id=vc_modelsc_sub.speciesref AND " + " vc_modelsc_sub.modelref = " + BioModelTable.table.modelRef.getQualifiedColName() + " AND " + " vc_modelsc_sub.id=vc_reactpart_sub.scref AND " + " vc_reactpart_sub.reactstepref=vc_reactstep_sub.id " + ")";
        }
    }
    // WildCard subcondtion
    if (repWildCard != null || typeWildCard != null) {
        searchConditions = "(" + " SELECT " + Table.SQL_GLOBAL_HINT + " DISTINCT " + " vc_reactstep_sub.id " + " FROM " + ReactStepTable.table.getTableName() + " vc_reactstep_sub" + "," + ReactPartTable.table.getTableName() + " vc_reactpart_sub" + "," + SpeciesContextModelTable.table.getTableName() + " vc_modelsc_sub" + "," + SpeciesTable.table.getTableName() + " vc_species_sub" + (typeWildCard != null ? "," + DBSpeciesTable.table.getTableName() + " vc_dbspecies_sub" : "") + " WHERE " + "(" + (repWildCard != null ? "upper(vc_species_sub.commonname) LIKE upper('" + repWildCard + "')" : "") + (repWildCard != null && typeWildCard != null ? " OR  " : "") + (typeWildCard != null ? "(" + "vc_species_sub.dbspeciesref IS NOT NULL AND " + "vc_species_sub.dbspeciesref = vc_dbspecies_sub.id AND " + "(" + "vc_dbspecies_sub.compoundref=" + typeWildCard.getDBFormalSpeciesKey() + " OR " + "vc_dbspecies_sub.enzymeref=" + typeWildCard.getDBFormalSpeciesKey() + " OR " + "vc_dbspecies_sub.proteinref=" + typeWildCard.getDBFormalSpeciesKey() + ")" + ")" : "") + ")" + " AND " + " vc_species_sub.id=vc_modelsc_sub.speciesref AND " + " vc_modelsc_sub.modelref = " + BioModelTable.table.modelRef.getQualifiedColName() + " AND " + " vc_modelsc_sub.id=vc_reactpart_sub.scref AND " + " vc_reactpart_sub.reactstepref=vc_reactstep_sub.id " + ")";
    }
    // 
    // 
    // 
    String condition = "";
    if (searchConditions.length() > 0) {
        condition += ReactStepTable.table.id.getQualifiedColName() + " IN " + "(" + searchConditions + ")" + " AND ";
    }
    condition += SpeciesTable.table.id.getQualifiedColName() + " = " + SpeciesContextModelTable.table.speciesRef.getQualifiedColName() + " AND " + SpeciesContextModelTable.table.id.getQualifiedColName() + " = " + ReactPartTable.table.scRef.getQualifiedColName() + " AND " + ReactPartTable.table.reactStepRef.getQualifiedColName() + " = " + ReactStepTable.table.id.getQualifiedColName() + " AND " + ReactStepTable.table.modelRef.getQualifiedColName() + " = " + BioModelTable.table.modelRef.getQualifiedColName();
    String special = " ORDER BY " + ReactStepTable.table.id.getQualifiedColName();
    sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, special, dbSyntax, true);
    StringBuffer sb = new StringBuffer(sql);
    // LOBs cannot be accessed if the query uses the DISTINCT or UNIQUE keyword
    sb.insert(7, Table.SQL_GLOBAL_HINT + " DISTINCT ");
    return sb.toString();
}

Example 19 with OuterJoin

use of cbit.vcell.modeldb.DatabasePolicySQL.OuterJoin in project vcell by virtualcell.

the class BioModelTable method getInfoSQL.

/**
 * This method was created in VisualAge.
 * @return java.lang.String
 */
public String getInfoSQL(User user, String extraConditions, String special, DatabaseSyntax dbSyntax) {
    UserTable userTable = UserTable.table;
    BioModelTable vTable = BioModelTable.table;
    SoftwareVersionTable swvTable = SoftwareVersionTable.table;
    String sql;
    Field[] f = { userTable.userid, new cbit.sql.StarField(vTable), swvTable.softwareVersion };
    Table[] t = { vTable, userTable, swvTable };
    switch(dbSyntax) {
        case ORACLE:
            {
                // outer join using (+) syntax in WHERE clause.
                String condition = // links in the userTable
                userTable.id.getQualifiedColName() + " = " + vTable.ownerRef.getQualifiedColName() + " AND " + vTable.id.getQualifiedColName() + " = " + swvTable.versionableRef.getQualifiedColName() + "(+) ";
                if (extraConditions != null && extraConditions.trim().length() > 0) {
                    condition += " AND " + extraConditions;
                }
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, null, condition, special, dbSyntax, true);
                return sql;
            }
        case POSTGRES:
            {
                // outer join in FROM clause explicitly, encoded in "OuterJoin" class and removed from WHERE clause.
                // links in the userTable
                String condition = userTable.id.getQualifiedColName() + " = " + vTable.ownerRef.getQualifiedColName() + " ";
                // " AND " + vTable.id.getQualifiedColName() + " = " + swvTable.versionableRef.getQualifiedColName()+"(+) ";
                if (extraConditions != null && extraConditions.trim().length() > 0) {
                    condition += " AND " + extraConditions;
                }
                OuterJoin outerJoin = new OuterJoin(vTable, swvTable, JoinOp.LEFT_OUTER_JOIN, vTable.id, swvTable.versionableRef);
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, outerJoin, condition, special, dbSyntax, true);
                return sql;
            }
        default:
            {
                throw new RuntimeException("unexpected DatabaseSyntax " + dbSyntax);
            }
    }
}