Examples with BioModelSimulationLinkTable cbit.vcell.modeldb.BioModelSimulationLinkTable used on opensource projects

Example 1 with BioModelSimulationLinkTable

use of cbit.vcell.modeldb.BioModelSimulationLinkTable in project vcell by virtualcell.

the class SimulationJobDbDriver method getSimpleJobStatus.

/**
 * Insert the method's description here.
 * Creation date: (9/3/2003 8:59:46 AM)
 * @return java.util.List of SimpleJobStatus for managementGUI
 * @param conditions java.lang.String
 *
 * for the subqueries, here is a prototype query which returns the
 *
 * select
 *vc_simulation_1.id as simid,
 *(select max('{"bmid":"' || lpad(vc_biomodel.id,14,0) || '","scid":"' || lpad(vc_simcontext.id,14,0) || '","bmbranch":"' || lpad(vc_biomodel.versionbranchid,14,0) || '","scbranch":"' || lpad(vc_simcontext.versionbranchid,14,0) || '","bmname":"' || vc_biomodel.name || '","scname":"' || vc_simcontext.name || '"}')
 *from vc_biomodel, vc_biomodelsimcontext, VC_BIOMODELSIM, vc_simcontext
 *where vc_simulation_1.mathref = vc_simcontext.mathref
 *and VC_BIOMODELSIMCONTEXT.SIMCONTEXTREF = VC_SIMCONTEXT.id
 *and VC_BIOMODELSIMCONTEXT.BIOMODELREF = vc_biomodel.id
 *and VC_BIOMODELSIM.SIMREF = VC_SIMULATION_1.id
 *and VC_BIOMODELSIM.BIOMODELREF = vc_biomodel.id
 *) as bmLink,
 *(select max('{"mmid":"' || lpad(vc_mathmodel.id,14,0) || '","mmbranch":"' || lpad(vc_mathmodel.versionbranchid,14,0) || '","mmname":"' || vc_mathmodel.name || '"}')
 *from vc_mathmodel, VC_MATHMODELSIM
 *where vc_simulation_1.id = vc_mathmodelsim.SIMREF
 *and vc_mathmodelsim.MATHMODELREF = vc_mathmodel.id
 *) as mmLink
 *from vc_simulation vc_simulation_1
 *where rownum <= 10;
 *
 *which returns the biomodel link (bmlink) and the math model link (mmlink) as JSON strings to be interpreted as needed.
 *
 *simid		bmlink																																								mmlink
 *2,006,065	<null>																																								{"mmid":"00000002001619","mmbranch":"00000001008286","mmname":"Terasaki1"}
 *2,006,075	<null>																																								{"mmid":"00000002001626","mmbranch":"00000001008286","mmname":"Terasaki1"}
 *2,006,085	<null>																																								{"mmid":"00000002001636","mmbranch":"00000001008286","mmname":"Terasaki1"}
 *2,637,970	{"bmid":"00000002669821","scid":"00000002637934","bmbranch":"00000002622407","scbranch":"00000002637935","bmname":"aggregation","scname":"diagonal gradient"}		<null>
 *2,006,427	<null>																																								{"mmid":"00000002002108","mmbranch":"00000001002871","mmname":"DiffusionfromChannel"}
 *2,006,437	<null>																																								{"mmid":"00000002002110","mmbranch":"00000001002871","mmname":"DiffusionfromChannel"}
 *2,006,646	<null>																																								{"mmid":"00000002002254","mmbranch":"00000001118506","mmname":"AliciaProblem1"}
 *10,067,537	{"bmid":"00000010067543","scid":"00000010067469","bmbranch":"00000010033822","scbranch":"00000010067470","bmname":"BMTest_biphasicStatModule1","scname":"figure5"}	<null>
 *10,369,972	{"bmid":"00000010369990","scid":"00000010369900","bmbranch":"00000010009521","scbranch":"00000010369901","bmname":"MemBinding_1","scname":"comp"}					<null>
 *2,007,278	<null>																																								{"mmid":"00000002001884","mmbranch":"00000001036088","mmname":"Wave_no_nucl5"}
 */
public List<SimpleJobStatusPersistent> getSimpleJobStatus(Connection con, String conditions, int startRow, int maxNumRows) throws java.sql.SQLException, DataAccessException {
    BioModelSimulationLinkTable bioSimLinkTable = BioModelSimulationLinkTable.table;
    MathModelSimulationLinkTable mathSimLinkTable = MathModelSimulationLinkTable.table;
    final String BMLINK = "bmlink";
    final String MMLINK = "mmlink";
    String subquery = "SELECT " + "current_timestamp as " + DatabaseConstants.SYSDATE_COLUMN_NAME + "," + jobTable.getTableName() + ".*," + userTable.userid.getQualifiedColName() + "," + userTable.id.getQualifiedColName() + " as ownerkey" + "," + "vc_sim_1." + simTable.ownerRef.getUnqualifiedColName() + "," + "vc_sim_1." + simTable.name.getUnqualifiedColName() + "," + "vc_sim_1." + simTable.taskDescription.getUnqualifiedColName() + "," + "vc_sim_1." + simTable.meshSpecX.getUnqualifiedColName() + "," + "vc_sim_1." + simTable.meshSpecY.getUnqualifiedColName() + "," + "vc_sim_1." + simTable.meshSpecZ.getUnqualifiedColName() + "," + "vc_sim_1." + simTable.mathOverridesLarge.getUnqualifiedColName() + " as " + simTable.mathOverridesLarge.getUnqualifiedColName() + "," + "vc_sim_1." + simTable.mathOverridesSmall.getUnqualifiedColName() + " as " + simTable.mathOverridesSmall.getUnqualifiedColName() + "," + "(SELECT max('{\"" + BioModelLink.bmid + "\":\"' || lpad(vc_biomodel.id,14,0)" + " || '\",\"" + BioModelLink.scid + "\":\"' || lpad(vc_simcontext.id,14,0)" + " || '\",\"" + BioModelLink.bmbranch + "\":\"' || lpad(vc_biomodel.versionbranchid,14,0)" + " || '\",\"" + BioModelLink.scbranch + "\":\"' || lpad(vc_simcontext.versionbranchid,14,0)" + " || '\",\"" + BioModelLink.bmname + "\":\"' || vc_biomodel.name" + " || '\",\"" + BioModelLink.scname + "\":\"' || vc_simcontext.name || '\"}')" + " FROM vc_biomodel" + ", vc_biomodelsimcontext" + ", VC_BIOMODELSIM" + ", vc_simcontext" + " WHERE vc_sim_1.mathref = vc_simcontext.mathref" + " AND VC_BIOMODELSIMCONTEXT.SIMCONTEXTREF = VC_SIMCONTEXT.id" + " AND VC_BIOMODELSIMCONTEXT.BIOMODELREF = vc_biomodel.id" + " AND VC_BIOMODELSIM.SIMREF = vc_sim_1.id" + " AND VC_BIOMODELSIM.BIOMODELREF = vc_biomodel.id" + ") as " + BMLINK + "," + "(SELECT max('{\"" + MathModelLink.mmid + "\":\"' || lpad(vc_mathmodel.id,14,0)" + " || '\",\"" + MathModelLink.mmbranch + "\":\"' || lpad(vc_mathmodel.versionbranchid,14,0)" + " || '\",\"" + MathModelLink.mmname + "\":\"' || vc_mathmodel.name || '\"}')" + " FROM vc_mathmodel" + ", vc_mathmodelsim" + " WHERE vc_sim_1.id = vc_mathmodelsim.SIMREF" + " AND vc_mathmodelsim.MATHMODELREF = vc_mathmodel.id" + ") as " + MMLINK + " FROM " + jobTable.getTableName() + "," + simTable.getTableName() + " vc_sim_1" + "," + userTable.getTableName() + "," + bioSimLinkTable.getTableName() + "," + mathSimLinkTable.getTableName() + " WHERE " + "vc_sim_1." + simTable.id.getUnqualifiedColName() + "=" + jobTable.simRef.getQualifiedColName() + " AND " + "vc_sim_1." + simTable.ownerRef.getUnqualifiedColName() + "=" + userTable.id.getQualifiedColName() + " AND " + bioSimLinkTable.simRef.getQualifiedColName() + " (+) " + "=" + "vc_sim_1." + simTable.id.getUnqualifiedColName() + " AND " + mathSimLinkTable.simRef.getQualifiedColName() + " (+) " + "=" + "vc_sim_1." + simTable.id.getUnqualifiedColName();
    String additionalConditionsClause = "";
    if (conditions != null && conditions.length() > 0) {
        additionalConditionsClause = " AND (" + conditions + ")";
    }
    // most recent records first
    String orderByClause = " order by " + jobTable.submitDate.getQualifiedColName() + " DESC ";
    String sql = null;
    if (maxNumRows > 0) {
        if (startRow <= 1) {
            // simpler query, only limit rows, not starting row
            sql = "select * from " + "(" + subquery + " " + additionalConditionsClause + " " + orderByClause + ") " + "where rownum <= " + maxNumRows;
        } else {
            // full query, limit start and limit
            sql = "select * from " + "(select a.*, ROWNUM rnum from " + "(" + subquery + " " + additionalConditionsClause + " " + orderByClause + ") a " + " where rownum <= " + (startRow + maxNumRows - 1) + ") " + "where rnum >= " + startRow;
        }
    } else {
        sql = subquery + " " + additionalConditionsClause + " " + orderByClause;
    }
    if (lg.isTraceEnabled()) {
        lg.trace(sql);
    }
    List<SimpleJobStatusPersistent> resultList = new ArrayList<SimpleJobStatusPersistent>();
    Statement stmt = con.createStatement();
    SimulationJobStatusPersistent simJobStatus = null;
    cbit.vcell.solver.SolverTaskDescription std = null;
    String username = null;
    try {
        ResultSet rset = stmt.executeQuery(sql.toString());
        while (rset.next()) {
            simJobStatus = jobTable.getSimulationJobStatus(rset);
            username = rset.getString(userTable.userid.getUnqualifiedColName());
            BigDecimal ownerKeyDecimal = rset.getBigDecimal("ownerkey");
            User owner = new User(username, new KeyValue(ownerKeyDecimal));
            std = null;
            try {
                String taskDesc = rset.getString(SimulationTable.table.taskDescription.getUnqualifiedColName());
                if (taskDesc != null) {
                    std = new cbit.vcell.solver.SolverTaskDescription(new org.vcell.util.CommentStringTokenizer(org.vcell.util.TokenMangler.getSQLRestoredString(taskDesc)));
                }
            } catch (DataAccessException ex) {
                ex.printStackTrace();
                lg.error("failed to parse SolverTaskDescription", ex);
            }
            Integer meshSizeX = rset.getInt(SimulationTable.table.meshSpecX.getUnqualifiedColName());
            if (rset.wasNull()) {
                meshSizeX = null;
            }
            Integer meshSizeY = rset.getInt(SimulationTable.table.meshSpecY.getUnqualifiedColName());
            if (rset.wasNull()) {
                meshSizeY = null;
            }
            Integer meshSizeZ = rset.getInt(SimulationTable.table.meshSpecZ.getUnqualifiedColName());
            if (rset.wasNull()) {
                meshSizeZ = null;
            }
            String simname = rset.getString(SimulationTable.table.name.getUnqualifiedColName());
            SimulationDocumentLink simulationDocumentLink = null;
            String latestBioModelLinkJSON = rset.getString(BMLINK);
            if (latestBioModelLinkJSON != null) {
                try {
                    Gson gson = new Gson();
                    BioModelLink bioModelLink = gson.fromJson(latestBioModelLinkJSON, BioModelLink.class);
                    bioModelLink.clearZeroPadding();
                    simulationDocumentLink = bioModelLink;
                } catch (Exception e) {
                    e.printStackTrace();
                    lg.error("failed to parse BioModelLink", e);
                }
            }
            String latestMathModelLinkJSON = rset.getString(MMLINK);
            if (latestMathModelLinkJSON != null) {
                Gson gson = new Gson();
                try {
                    MathModelLink mathModelLink = gson.fromJson(latestMathModelLinkJSON, MathModelLink.class);
                    mathModelLink.clearZeroPadding();
                    simulationDocumentLink = mathModelLink;
                } catch (Exception e) {
                    e.printStackTrace();
                    lg.error("failed to parse MathModelLink", e);
                }
            }
            CommentStringTokenizer mathOverridesTokens = SimulationTable.getMathOverridesTokenizer(rset, dbSyntax);
            List<MathOverrides.Element> mathOverrideElements = MathOverrides.parseOverrideElementsFromVCML(mathOverridesTokens);
            int scanCount = 1;
            for (MathOverrides.Element element : mathOverrideElements) {
                if (element.getSpec() != null) {
                    scanCount *= element.getSpec().getNumValues();
                }
            }
            SimulationMetadata simulationMetadata = new SimulationMetadata(simJobStatus.getVCSimulationIdentifier(), simname, owner, std, meshSizeX, meshSizeY, meshSizeZ, new Integer(scanCount));
            resultList.add(new SimpleJobStatusPersistent(simulationMetadata, simulationDocumentLink, simJobStatus));
        }
    } finally {
        stmt.close();
    }
    return resultList;
}