Search in sources :

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;
}
Also used : User(org.vcell.util.document.User) KeyValue(org.vcell.util.document.KeyValue) ArrayList(java.util.ArrayList) Gson(com.google.gson.Gson) SimulationJobStatusPersistent(cbit.vcell.server.SimulationJobStatusPersistent) SimulationMetadata(cbit.vcell.solver.SimulationMetadata) ResultSet(java.sql.ResultSet) DataAccessException(org.vcell.util.DataAccessException) BioModelSimulationLinkTable(cbit.vcell.modeldb.BioModelSimulationLinkTable) Statement(java.sql.Statement) SimpleJobStatusPersistent(cbit.vcell.server.SimpleJobStatusPersistent) MathModelSimulationLinkTable(cbit.vcell.modeldb.MathModelSimulationLinkTable) BigDecimal(java.math.BigDecimal) SQLException(java.sql.SQLException) DataAccessException(org.vcell.util.DataAccessException) MathOverrides(cbit.vcell.solver.MathOverrides) SimulationDocumentLink(cbit.vcell.server.SimulationDocumentLink) BioModelLink(cbit.vcell.server.BioModelLink) CommentStringTokenizer(org.vcell.util.CommentStringTokenizer) MathModelLink(cbit.vcell.server.MathModelLink)

Aggregations

BioModelSimulationLinkTable (cbit.vcell.modeldb.BioModelSimulationLinkTable)1 MathModelSimulationLinkTable (cbit.vcell.modeldb.MathModelSimulationLinkTable)1 BioModelLink (cbit.vcell.server.BioModelLink)1 MathModelLink (cbit.vcell.server.MathModelLink)1 SimpleJobStatusPersistent (cbit.vcell.server.SimpleJobStatusPersistent)1 SimulationDocumentLink (cbit.vcell.server.SimulationDocumentLink)1 SimulationJobStatusPersistent (cbit.vcell.server.SimulationJobStatusPersistent)1 MathOverrides (cbit.vcell.solver.MathOverrides)1 SimulationMetadata (cbit.vcell.solver.SimulationMetadata)1 Gson (com.google.gson.Gson)1 BigDecimal (java.math.BigDecimal)1 ResultSet (java.sql.ResultSet)1 SQLException (java.sql.SQLException)1 Statement (java.sql.Statement)1 ArrayList (java.util.ArrayList)1 CommentStringTokenizer (org.vcell.util.CommentStringTokenizer)1 DataAccessException (org.vcell.util.DataAccessException)1 KeyValue (org.vcell.util.document.KeyValue)1 User (org.vcell.util.document.User)1