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

Example 11 with OuterJoin

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

the class BioModelDbDriver method getBioModelMetaData.

/**
 * getModel method comment.
 */
private BioModelMetaData getBioModelMetaData(Connection con, User user, KeyValue bioModelKey, DatabaseSyntax dbSyntax) throws SQLException, DataAccessException, ObjectNotFoundException {
    if (user == null || bioModelKey == null) {
        throw new IllegalArgumentException("Improper parameters for getBioModelMetaData");
    }
    // if (lg.isTraceEnabled()) lg.trace("BioModelDbDriver.getBioModelMetaData(user=" + user + ", id=" + bioModelKey + ")");
    // 
    // to construct a BioModelMetaData 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 bioModelKey
    // 
    KeyValue[] simKeys = getSimulationEntriesFromBioModel(con, bioModelKey);
    // 
    // get SimulationContext Keys for bioModelKey
    // 
    KeyValue[] simContextKeys = getSimContextEntriesFromBioModel(con, bioModelKey);
    // 
    // get BioModelMetaData object for bioModelKey
    // 
    String sql;
    VCMetaDataTable vcMetadataTable = VCMetaDataTable.table;
    Field[] f = { new StarField(bioModelTable), userTable.userid, new StarField(vcMetadataTable) };
    Table[] t = { bioModelTable, userTable, vcMetadataTable };
    switch(dbSyntax) {
        case ORACLE:
            {
                String condition = bioModelTable.id.getQualifiedColName() + " = " + bioModelKey + " AND " + userTable.id.getQualifiedColName() + " = " + bioModelTable.ownerRef.getQualifiedColName() + " AND " + vcMetadataTable.bioModelRef.getQualifiedColName() + "(+) = " + bioModelTable.id.getQualifiedColName();
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, null, condition, null, dbSyntax, true);
                break;
            }
        case POSTGRES:
            {
                String condition = bioModelTable.id.getQualifiedColName() + " = " + bioModelKey + " AND " + userTable.id.getQualifiedColName() + " = " + bioModelTable.ownerRef.getQualifiedColName() + " ";
                // " AND "+ VCMetaDataTable.table.bioModelRef.getQualifiedColName() + "(+) = " + bioModelTable.id.getQualifiedColName();
                OuterJoin outerJoin = new OuterJoin(vcMetadataTable, bioModelTable, JoinOp.RIGHT_OUTER_JOIN, vcMetadataTable.bioModelRef, bioModelTable.id);
                sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, outerJoin, condition, null, dbSyntax, true);
                break;
            }
        default:
            {
                throw new RuntimeException("unexpected DatabaseSyntax " + dbSyntax);
            }
    }
    Statement stmt = con.createStatement();
    BioModelMetaData bioModelMetaData = null;
    try {
        ResultSet rset = stmt.executeQuery(sql);
        if (rset.next()) {
            bioModelMetaData = bioModelTable.getBioModelMetaData(rset, con, simContextKeys, simKeys, dbSyntax);
        } else {
            throw new ObjectNotFoundException("BioModel id=" + bioModelKey + " not found for user '" + user + "'");
        }
    } finally {
        // Release resources include resultset
        stmt.close();
    }
    return bioModelMetaData;
}

Example 12 with OuterJoin

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

the class GeomDbDriver method getGeometry.

/**
 * getModel method comment.
 */
private Geometry getGeometry(QueryHashtable dbc, Connection con, User user, KeyValue geomKey, boolean bCheckPermission) throws SQLException, DataAccessException, ObjectNotFoundException {
    if (user == null || geomKey == null) {
        throw new IllegalArgumentException("Improper parameters for getGeometry");
    }
    // log.print("GeomDbDriver.getGeometry(user="+user+", id="+geomKey+")");
    String sql;
    Field[] f = { new StarField(geomTable), userTable.userid, extentTable.extentX, extentTable.extentY, extentTable.extentZ };
    Table[] t = { geomTable, userTable, extentTable };
    String condition = geomTable.id.getQualifiedColName() + " = " + geomKey + " AND " + userTable.id.getQualifiedColName() + " = " + geomTable.ownerRef.getQualifiedColName() + " AND " + extentTable.id.getQualifiedColName() + " = " + geomTable.extentRef.getQualifiedColName();
    sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, null, dbSyntax, bCheckPermission);
    // System.out.println(sql);
    Geometry geom = null;
    // Connection con = conFact.getConnection();
    Statement stmt = con.createStatement();
    try {
        ResultSet rset = stmt.executeQuery(sql);
        if (rset.next()) {
            // This geometry privacy flag gives this user permission to access
            geom = getGeometry(dbc, con, user, rset);
        } else {
            // see if at least 1 geometry parents (Mathmodels and/or BioModels) are shared with this user
            if (bCheckPermission) {
                rset.close();
                String parentSQL = GeometryTable.getParentsPermissionSQL(geomKey, user);
                rset = stmt.executeQuery(parentSQL);
                if (rset.next()) {
                    // At least 1 parent of the geometry exists that's shared to this user so give them the geometry
                    rset.close();
                    // Get the geometry without checking the geometry permission
                    sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, null, dbSyntax, false);
                    rset = stmt.executeQuery(sql);
                    if (rset.next()) {
                        geom = getGeometry(dbc, con, user, rset);
                    }
                }
            }
            if (geom == null) {
                throw new ObjectNotFoundException("Geometry id=" + geomKey + " not found for user '" + user + "'");
            }
        }
    } finally {
        // Release resources include resultset
        stmt.close();
    }
    return geom;
}

Example 13 with OuterJoin

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

the class SimContextTable 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;
    SimContextTable vTable = SimContextTable.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:
            {
                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, (OuterJoin) null, condition, special, dbSyntax);
                return sql;
            }
        case POSTGRES:
            {
                // 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);
                return sql;
            }
        default:
            {
                throw new RuntimeException("unexpected DatabaseSyntax " + dbSyntax);
            }
    }
}

Example 14 with OuterJoin

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

the class SimulationDbDriver method getSimulationSQL.

/**
 * This method was created in VisualAge.
 * @return cbit.vcell.math.MathDescription
 * @param user cbit.vcell.server.User
 * @param mathDescKey cbit.sql.KeyValue
 */
private Simulation getSimulationSQL(QueryHashtable dbc, Connection con, User user, KeyValue simKey) throws SQLException, DataAccessException, ObjectNotFoundException {
    String sql;
    Field[] f = { userTable.userid, new cbit.sql.StarField(simTable) };
    Table[] t = { simTable, userTable };
    String condition = simTable.id.getQualifiedColName() + " = " + simKey + " AND " + userTable.id.getQualifiedColName() + " = " + simTable.ownerRef.getQualifiedColName();
    sql = DatabasePolicySQL.enforceOwnershipSelect(user, f, t, (OuterJoin) null, condition, null, dbSyntax);
    // System.out.println(sql);
    Simulation simulation = null;
    Statement stmt = con.createStatement();
    try {
        ResultSet rset = stmt.executeQuery(sql);
        if (rset.next()) {
            // 
            try {
                simulation = simTable.getSimulation(dbc, rset, con, user, mathDB, dbSyntax);
            } catch (PropertyVetoException e) {
                lg.error(e.getMessage(), e);
                throw new DataAccessException(e.getMessage());
            }
        } else {
            throw new org.vcell.util.ObjectNotFoundException("Simulation id=" + simKey + " not found for user '" + user + "'");
        }
    } finally {
        // Release resources include resultset
        stmt.close();
    }
    return simulation;
}

Example 15 with OuterJoin

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

the class SimulationTable 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;
    SimulationTable vTable = SimulationTable.table;
    SoftwareVersionTable swvTable = SoftwareVersionTable.table;
    String sql;
    // Field[] f = {userTable.userid,new cbit.sql.StarField(vTable)};
    Field[] f = new Field[] { vTable.id, userTable.userid, swvTable.softwareVersion };
    f = (Field[]) BeanUtils.addElements(f, vTable.versionFields);
    f = (Field[]) BeanUtils.addElement(f, vTable.mathRef);
    Table[] t = { vTable, userTable, swvTable };
    switch(dbSyntax) {
        case ORACLE:
            {
                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, (OuterJoin) null, condition, special, dbSyntax);
                return sql;
            }
        case POSTGRES:
            {
                // 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);
                return sql;
            }
        default:
            {
                throw new RuntimeException("unexpected DatabaseSyntax " + dbSyntax);
            }
    }
}