DBJobManager.java

Go to the documentation of this file.

package cedar.jetweb.db;

import cedar.jetweb.*;

//import cedar.jetweb.model.Model;
//import cedar.jetweb.model.RunSeries;
//import cedar.jetweb.model.ResultSearchPattern;
import cedar.jetweb.batch.SubmitScriptHandler;
import cedar.jetweb.job.*;
import cedar.jetweb.util.JetWebFile;
import cedar.jetweb.util.Match;

import java.io.File;

import java.sql.*;

import java.text.DecimalFormat;

import java.util.Date;
import java.util.Enumeration;
import java.util.Vector;


public abstract class DBJobManager {
    public static final String SUCCESS = "Operation Completed Successfully";

    //private static DBObjectManager objManager = new DBObjectManager();

    public static synchronized void addLogfile(LogFile log)
        throws JetWebException {
        String ins = "";

        try {
            Statement stmt = DBConfig.getConnection().createStatement();

        //add the CutCollection for this logfile to the DB

        log.getGenerator().getCutCollection().store();

            // add a row to the logfile table.
            if (log.getId() > 0) {
                ins = "UPDATE logfile SET file='" + log.getFile() + 
            "',runseries_id=" + log.getRunSeries().getId() + 
            ",status=" + log.getStatus() + 
            ",lumi=" + log.getLumi() + 
            ", cross_section=" +log.getCrossSection() +
            ",cut_collection_id=" + 
            log.getGenerator().getCutCollection().getId()+
            " WHERE log_id=" + log.getId();
            } else {

        String select = "SELECT MAX(log_id) FROM logfile";
        ResultSet rs = stmt.executeQuery(select);

        int newId = 1;

        while(rs.next()){
            newId = rs.getInt(1);
            ++newId;
        }
        
        log.setId(newId);

                ins = "INSERT INTO logfile " + 
            "(log_id, " + 
            "file, " + 
            "runseries_id, " + 
            "status, " + 
            "lumi, " + 
            "cross_section," +
            "cut_collection_id)" 
            + "values(" + 
            log.getId() + ", '" +
                    log.getFile() + "'," + 
            log.getRunSeries().getId() + "," +
                    log.getStatus() + "," + 
            log.getLumi() + "," + 
            log.getCrossSection() + "," +
            log.getGenerator().getCutCollection().getId() +
            ")";
            }

            int rowsChanged = stmt.executeUpdate(ins);
            stmt.close();
        } catch (SQLException E) {
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
            log.setStatus(LogFile.ERROR);
            throw new JetWebDBException(E, ins, "LogfileId:" + log.getId());
        }
    }

    public static synchronized void selectFromDBbyName(LogFile log)
        throws JetWebException {

    String query = "SELECT * from logfile where file like '%" +
        log.getFile() + "%'";

        try {
            // First see if the file is in there.
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(query);

            if (rs.next()) {
        System.out.println
            ("DBJobManager: found logfile in DB matching file " +
             log.getFile());
                // Read from DB to get runseries id from logfile table.
                fillLog(rs, log);
            }else{
        System.out.println
            ("DBJobManager: no file in DB found matching " + 
             log.getFile());
        }

            // Finish up & return.
            rs.close();
            stmt.close();
        } catch (SQLException E) {
            System.out.println("DBFitManager.fillRunSeriesId");
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
            throw new JetWebDBException(E, query, "LogfileId:" + log.getId());
        }
    }

    public static synchronized boolean selectFromDB(CutCollection cuts)
    throws JetWebException{

    try{
        if(cuts.getIdDontRetrieve()>0){
        if(fillById(cuts)) return true;
        }
        
        if(cuts.getCuts().size()!=0){
        if(fillByCuts(cuts)) return true;
        
        // if we get here then there are some cuts in the collection
        // but they do not match any collection in the DB
        
        return false;
        }
    }catch(SQLException err){
        throw new JetWebException(err);
    }
    return false;
    }

    public static synchronized boolean addToDB(CutCollection cuts)
    throws JetWebException{

    boolean hasId = (cuts.getId()>0);
    boolean hasCuts = (cuts.getCuts().size()!=0);

    try{
        //If the collection has an id that is already present in the DB
        // then fill the collection object from the DB and return false
        if(hasId){

        if(fillById(cuts)) return false;
        
        }

        if(hasCuts){
        // if the same collection of cuts already exists in the 
        // DB then just fill the Id and return false

        if(fillByCuts(cuts)) return false;
        
        return writeCheckedCutCollection(cuts);
        }

    }catch(SQLException err){
        throw new JetWebException(err);
    }

    return false;
    }

    private static synchronized boolean fillById(CutCollection cuts)
    throws SQLException{

    Integer id = new Integer(cuts.getId());

    String select = 
        "SELECT cut_id FROM cut_collection WHERE cut_collection_id=" + 
        id;

    Statement stmt = DBConfig.getConnection().createStatement();
    ResultSet rs = stmt.executeQuery(select);

    cuts.clear();

    boolean filled = false;

    while(rs.next()){
        filled = true;
        Cut cut = new Cut(rs.getInt(1));
        cuts.addCut(cut);
    }

    //reset the CutCollection Id because adding cuts sets it to default

    //  cuts.setId(id);

    return filled;
    }

    private static synchronized boolean fillByCuts(CutCollection cuts)
    throws SQLException, JetWebException{

    Vector<Integer> foundIds = new Vector<Integer>();
    Vector<Integer> cutIds = new Vector<Integer>();

    Statement stmt = DBConfig.getConnection().createStatement();
    ResultSet rs;

    boolean firstCut = true;
    //loop over all cuts 
    for(Cut cut: cuts.getCuts()){

        Integer id = cut.getId();
        //retrieve the cut id if necessary (and possible)
        if(id<0){
        cut.retrieve();
        id=cut.getId();
        if(id<0) return false;

        }
        cutIds.add(id);
        //get all CutCollections that contain that cut

        String query = 
        "SELECT cut_collection_id FROM cut_collection WHERE cut_id=" + 
        id;

        rs = stmt.executeQuery(query);
        Vector<Integer> newIds = new Vector<Integer>();
        while(rs.next()){
        newIds.add(rs.getInt(1));
        }

        // Find the list of CutCollections that contains the current cut
        // and all previous cuts (unless this is the first cut)
        Vector<Integer> tmpIds = new Vector<Integer>();
        if(firstCut){
        tmpIds = newIds;
        firstCut = false;
        }else{

        for(Integer i: newIds){
            if(foundIds.contains(i)){
            tmpIds.add(i);
            }
        }

        }
        //if no CutCollection contains all previous cuts then there is no
        //matching CutCollection in the DB
        if(tmpIds.size()==0) return false;

        foundIds = tmpIds;

    }

    //Of the found CutCollections, look for one that matches exactly
    //i.e. contains ^only^ this set of cuts

    for(Integer Id: foundIds){

        String select = 
        "SELECT cut_id FROM cut_collection WHERE cut_collection_id=" + 
        Id;

        rs = stmt.executeQuery(select);
        boolean carryOn = true;
        Integer numCuts = 0;
        while(rs.next() && carryOn){
        if(!cutIds.contains(new Integer(rs.getInt(1)))) carryOn = false;
        ++numCuts;
        }

        if(carryOn && numCuts==cutIds.size()){
        cuts.setId(Id);
        return true;
        }
    }

    return false;
    }

    private static synchronized boolean  writeCheckedCutCollection
    (CutCollection cuts)
    throws SQLException, JetWebException{

    Statement stmt = DBConfig.getConnection().createStatement();
    ResultSet rs = stmt.executeQuery
        ("SELECT MAX(cut_collection_id) FROM cut_collection");
    int newId = 1;
    if(rs.next()){
        newId = rs.getInt(1);
        ++newId;
    }

    if(cuts.getId()<newId) cuts.setId(newId);

    Vector<String> inserts = new Vector<String>();

    for(Cut cut:cuts.getCuts()){

        Integer Id = cut.getId();

        // If any of the cuts in the collection has no id
        // then we can't write it to the DB
        if(Id<0){
        cut.retrieve();
        Id = cut.getId();
        if(Id<0) return false;
        }

        inserts.add
        ("INSERT INTO cut_collection(cut_collection_id, cut_id)VALUES("+
         cuts.getId() + ", " + 
         cut.getId() + ")");
    }

    for(String insert:inserts){
        stmt.executeUpdate(insert);
    }

    return true;
    }

    public static synchronized boolean selectFromDB(Cut cut)
    throws JetWebException{
    String select;
    int id = cut.getId();

    if(id > 0){
        select = "SELECT * FROM cut WHERE cut_id="+id;
    }else{
        //select the cut with the same name and nearest value
        //select = "SELECT * FROM cut where name="+cut.getName()+
        //" AND ABS(value-" + cut.getValue() + 
        //")=MIN(ABS(value-" + cut.getValue() + "))";
        select = selectNearestCut(cut);
    }

    try{
    
        Statement stmt = DBConfig.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(select);

        //if(rs.isAfterLast()){
        if(!rs.next()){
        //result set contains no rows - Cut is not in database
        //if cut name and value present, store it
        
        if(cut.getName().equals("") || 
           cut.getValue()==null){
            return false;
        }
        return cut.store();
        }

        if(id>0){
        //rs.next();
        cut.setValue(rs.getDouble("value"));
        cut.setName(rs.getString("name"));
        return true;
        }else{
        //need to check value in result set matches value of cut
        rs.next();
        Double value = rs.getDouble("value");

        if(Match.compareDouble(value, cut.getValue())){
            cut.setId(rs.getInt("cut_id"));
            return true;
        }
        
        //if we get here then there is no row in the DB matching
        //that value, but there is a cut with that name.

        if(cut.getValue()!=null&&
           !cut.getName().equals("")){
            return cut.store();
        }

        }

    }catch(SQLException err){
        
    }
    return false;
    }

    public static synchronized boolean addToDB(Cut cut)
    throws JetWebException{
    boolean hasId = cut.getId() > 0;
    boolean hasName = !cut.getName().equals("");
    boolean hasValue = !(cut.getValue()==null);

    try{

    if(hasId){
        //check that a cut with this Id doesn't already exist.
        //If it does already exist, do nothing
        if(fillById(cut)) return false;
    }

    if(hasName&&hasValue){

        if(fillByValue(cut)) return false;

        // if we get here then the cut has a value and a name
        // and is not present in the DB

        writeCheckedCut(cut);
        return true;
    }
    
    }catch(SQLException err){
        throw new JetWebException(err);
    }

    return false;
    }

    private static synchronized boolean fillById(Cut cut)
    throws SQLException{
    
    String select = "SELECT * FROM cut WHERE cut_id="+cut.getId(); 

    Statement stmt = DBConfig.getConnection().createStatement();
    ResultSet rs = stmt.executeQuery(select);
    
    if(!rs.next()) return false;

    cut.setValue(rs.getDouble("value"));
    cut.setName(rs.getString("name"));
    
    return true;
    }

    private static synchronized boolean fillByValue(Cut cut)
    throws SQLException{
    String select = selectNearestCut(cut);

    Statement stmt = DBConfig.getConnection().createStatement();
    ResultSet rs = stmt.executeQuery(select);
    if(!rs.next())return false;
    
    if(!Match.compareDouble(cut.getValue(), rs.getDouble("value"))) 
        return false;

    cut.setId(rs.getInt("cut_id"));
    cut.setValue(rs.getDouble("value"));
    cut.setName(rs.getString("name"));

    return true;
    }

    private static synchronized void writeCheckedCut(Cut cut)
    throws SQLException{

    Statement stmt = DBConfig.getConnection().createStatement();
    ResultSet rs = stmt.executeQuery("SELECT MAX(cut_id) FROM cut");
    int newId = 1;
    if(rs.next()){
        newId = rs.getInt(1);
        ++newId;
    }

    cut.setId(newId);

    String insert = "INSERT INTO cut(cut_id, name, value)VALUES("+
        cut.getId() + ",'" + 
        cut.getName() + "', " +
        cut.getValue() +")";

    stmt.executeUpdate(insert);

    return;
    }

    private static String selectNearestCut(Cut cut){

    return "SELECT * FROM cut where name='" + cut.getName() +
        "' AND ABS(value-" + cut.getValue() + 
        ")=(SELECT MIN(ABS(value-" + cut.getValue() + ")) FROM cut)";
    }

    public static synchronized void selectFromDB(PendingJob job)
        throws JetWebException {
        String query = "SELECT * FROM logfile WHERE log_id=" + job.getLogId();

        try {
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(query);

            rs.next();

            if (rs.getInt("status") == 0) {
                job.setValid(true);
            } else {
                job.setValid(false);
            }

            job.setName(rs.getString("file"));
            job.setDate(rs.getTimestamp("time"));
            job.setRunSeriesId(rs.getInt("runseries_id"));

            job.setScriptName("unknown");
        rs.close();
        stmt.close();
        } catch (SQLException e) {
            System.out.println("DB access error in selectFromDB for PendingJob" + e);
            throw new JetWebDBException(e, query, "PendingJob logId:" + job.getLogId());
        }
    }

    public static synchronized Enumeration<PendingJob> selectPendingJobs()
        throws JetWebException {
        Vector<PendingJob> jobs = new Vector<PendingJob>();
        String query = "SELECT log_id FROM logfile WHERE status=0";

        try {
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                PendingJob pj = new PendingJob(rs.getInt("log_id"));

                if (pj.isValid()) {
                    jobs.add(pj);
                }
            }
        rs.close();
        stmt.close();
        } catch (SQLException e) {
            System.out.println("DB access error in selectPendingJobs" + e);
            throw new JetWebDBException(e, query, "");
        }

        return jobs.elements();
    }

    public static synchronized PendingJob reserveJobName(String suggestion)
        throws JetWebException {
        String name = "invalid";
        int id = 0;
        String query = "SELECT MAX(log_id) FROM logfile";

        try {
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                id = rs.getInt(1);
                id++;
            }

        //now we do a check for existing files with a name corresponding
        //to the found log_id.  Keep incrementing the name until we 
        //find a free one
        boolean free = false;
        DecimalFormat form = new DecimalFormat("00000000000");
        while(!free){
        name = "n" + form.format(id);
        JetWebFile testFile = new JetWebFile(name, ".input");
        if(testFile.exists()){
            ++id;
        }else{
            free = true;
        }
        }

            if (id == 0) {
                System.out.println
            ("ResultManager Error allocating Id to " + suggestion);

                return null;
            }

            String ins = 
        "INSERT INTO logfile (log_id,file,runseries_id,status,host) " +
        "VALUES (" + 
        id + ",'" + 
        name + 
        "',0," +
        LogFile.RESERVED + ",'" + 
        SubmitScriptHandler.BATCHFACILITY +
                "')";

            int itmp = stmt.executeUpdate(ins);
        rs.close();
        stmt.close();
        } catch (SQLException E) {
            System.out.println("ResultManager.getJobName");
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
            throw new JetWebDBException(E, query, "suggestion:" + suggestion);
        }

        return new PendingJob(id);
    }

    public static synchronized void updateStatus(LogFile log)
    throws JetWebException{

    String update = 
        "UPDATE logfile " + 
        " SET status=" +log.getStatus() + 
        " WHERE log_id="+log.getId();

    try{
        Statement stmt = DBConfig.getConnection().createStatement();
        stmt.executeUpdate(update);
    }catch(SQLException err){
        throw new JetWebException(err);
    }
    return;
    }

    public static synchronized void checkStatus(LogFile log)
        throws JetWebException {

    JetWebFile lf = log.getFile();
    if(lf == null) return; 

    String query = "SELECT status FROM logfile WHERE file LIKE '%" +
        lf + "%'";

        //String query = "SELECT status FROM logfile WHERE file='" + 
    //  lf.getPath() + "'";

        try {
            // See if the file is in there.
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {

        Integer dbStatus = rs.getInt("status");

        dbStatus = (dbStatus.equals(LogFile.SUBMITTED)&&
                log.hasAidaFile())? LogFile.RUN: dbStatus; 


                log.setStatus(dbStatus);

            }

            // Finish up & return.
            rs.close();
            stmt.close();
        } catch (SQLException E) {
            System.out.println("DBFitManager.checkStatus");
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
            throw new JetWebDBException(E, query, "LogFile:" + log.getId());
        }
    }

    public static void removeLogFile(LogFile log) {
    String name = log.getFile().getPath();
        System.out.println("Removing logfile: " + name + " from table");

    String del = "delete from logfile where file like '%" + 
        log.getFile() + "%'";

        //String del = "delete from logfile where file='" + name + "'";

        try {
            Statement stmt = DBConfig.getConnection().createStatement();
            stmt.executeUpdate(del);
        stmt.close();
        } catch (SQLException e) {
            System.out.println("DBFitManager: Error executing " + del);
        }
    }

    public static synchronized int countJobs(String hostname)
        throws JetWebException {
        String query = "SELECT COUNT(log_id) count_rows FROM logfile WHERE host='" + hostname +
            "'";
        int nJobs = 0;

        try {
            // First see if the file is in there.
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            nJobs = rs.getInt("count_rows");

            // Finish up & return.
            rs.close();
            stmt.close();
        } catch (SQLException E) {
            System.out.println("DBFitManager.countJobs");
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
            throw new JetWebDBException(E, query, "Error counting jobs");
        }

        return nJobs;
    }

    public static synchronized int countCompletedJobs(String hostname)
        throws JetWebException {
        String query = "SELECT COUNT(log_id) count_rows FROM logfile WHERE status=1" +
            " AND host='" + hostname + "'";
        int nJobs = 0;

        try {
            // First see if the file is in there.
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            nJobs = rs.getInt("count_rows");

            // Finish up & return.
            rs.close();
            stmt.close();
        } catch (SQLException E) {
            System.out.println("DBFitManager.countCompletedJobs");
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
            throw new JetWebDBException(E, query, "Error counting completed jobs");
        }

        return nJobs;
    }

    public static synchronized void selectFromDB(LogFile log)
        throws JetWebException {
        String findLog = "SELECT * FROM logfile WHERE log_id=" + log.getId();
        int logId = log.getId();

        try {
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(findLog);

            if (rs.next()) {
                fillLog(rs, log);
            } else {
                System.out.println("No LogFile found with ID=" + logId);
                throw new JetWebDBException(findLog, "logId:" + logId);
            }
        rs.close();
        stmt.close();
        } catch (SQLException e) {
            System.out.println("Error reading logfile table " + e);
            throw new JetWebDBException(e, findLog, "logId:" + logId);
        }

        return;
    }

    private static void fillLog(ResultSet rs, LogFile log)
        throws SQLException, JetWebException {

    int rsid = rs.getInt("runseries_id");
    int status = rs.getInt("status");
    Double cs = rs.getDouble("cross_section");
    
    //if(rsid!=0)log.setRunSeriesId(rsid);
    // If the LogFile status is reserved then no info has been filled
    // apart from the file name and log id
    if(status!=LogFile.RESERVED&&rsid!=0) log.setRunSeriesId(rsid);

        //log.setRunSeriesId(rs.getInt("runseries_id"));
        log.setStatus(status);
        log.setId(rs.getInt("log_id"));
    log.setCrossSection(cs);
    String fName = rs.getString("file");
    if(!(fName.endsWith(JetWebFile.XML + ".gz") &&
         fName.endsWith(JetWebFile.XML))){
        fName = fName + JetWebFile.XML;
    }

    JetWebFile testFile = 
        new JetWebFile(fName);

    if(!testFile.exists()){
        System.out.println("DBJobManager: could not find file - using " +
                   testFile);
    }
     
        log.setFile(testFile);
    }

    public static synchronized Vector<LogFile> getLogFiles(int runSeriesId)
        throws JetWebException {
        Vector<LogFile> logs = new Vector<LogFile>();
        String findLogs = "SELECT log_id FROM logfile WHERE runseries_id=" + runSeriesId;

        try {
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(findLogs);

            while (rs.next()) {
                LogFile log = new LogFile(rs.getInt("log_id"));
                log.retrieve();
                logs.add(log);
            }
        rs.close();
        stmt.close();
        } catch (SQLException e) {
            System.out.println("Error reading logfile table " + e);
            throw new JetWebDBException(e, findLogs, "RunseriesId:" + runSeriesId);
        }

        return logs;
    }

    public static synchronized int getNLogs(int runSeriesId)
        throws JetWebException {
        String findLogs = "SELECT COUNT(log_id) count_logs  FROM logfile WHERE runseries_id=" +
            runSeriesId;

        //System.out.println(findLogs);
        int n = 0;

        try {
            Statement stmt = DBConfig.getConnection().createStatement();
            ResultSet rs = stmt.executeQuery(findLogs);
            rs.next();
            n = rs.getInt("count_logs");
        rs.close();
        stmt.close();
        } catch (SQLException e) {
            System.out.println("Error reading logfile table " + e);
            throw new JetWebDBException(e, findLogs, "runseriesId:" + runSeriesId);
        }

        return n;
    }

}

Generated Wed Jan 17 09:14:27 GMT 2007