/* Implementation of provenance query interface for SQL v8.

 Copyright (c) 2009-2010 The Regents of the University of California.
 All rights reserved.
 Permission is hereby granted, without written agreement and without
 license or royalty fees, to use, copy, modify, and distribute this
 software and its documentation for any purpose, provided that the above
 copyright notice and the following two paragraphs appear in all copies
 of this software.

 IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
 FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
 ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
 THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
 SUCH DAMAGE.

 THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
 INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
 PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
 CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
 ENHANCEMENTS, OR MODIFICATIONS.

*/

package org.kepler.provenance.sql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.ListIterator;
import java.util.Map;
import java.util.regex.Matcher;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.DOMBuilder;
import org.jdom.output.XMLOutputter;
import org.kepler.moml.NamedObjId;
import org.kepler.objectmanager.lsid.KeplerLSID;
import org.kepler.provenance.DefaultQuery;
import org.kepler.provenance.QueryException;
import org.kepler.sms.NamedOntClass;
import org.kepler.util.RenameUtil;
import org.kepler.util.WorkflowRun;
import org.kepler.util.sql.DatabaseFactory;
import org.kepler.util.sql.DatabaseType;
import org.kepler.util.sql.Schema;

import ptolemy.data.expr.XMLParser;

/**
 * Implementation of query interface for SQL v8.
 *
 * @author Daniel Crawl, Derik Barseghian, Ben Leinfelder
 * @version $Id: SQLQueryV8.java 34619 2017-08-23 20:27:50Z crawl $
 *
 */
public class SQLQueryV8 extends DefaultQuery
{        
    /** Construct a new SQLQueryV8 and connect to a database. */
    public SQLQueryV8(Map<String,String> parameters) throws QueryException
    {
        try
        { 
            _dbType = DatabaseFactory.getConnectedDatabaseType(parameters, "provenance");
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            throw new QueryException("Unable to connect or initialize " +
                "database connection: ", e);
        }

        _init();       
    }

    /** Disconnect from provenance store. */
    @Override
    public void disconnect() throws QueryException
    {
        if(_dbType != null)
        {
            try
            {
                _dbType.disconnect(); 
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to disconnect: ", e);
            }
    
            _dbType = null;
        }
    }

    /** Get a list of workflow names. */
    @Override
    public List<String> getWorkflows() throws QueryException
    {
        try
        {
            List<String> retval = new LinkedList<String>();
            
            ResultSet result = null;
            try
            {
                result = _psWorkflows.executeQuery();
                while(result.next())
                {
                    retval.add(result.getString("name")); 
                }
                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query workflow names: ", e);
        }
    }
    
    /** Get a workflow name. 
     *
     * @param lsid the workflow's LSID.
     * @return the workflow name if found, otherwise null.
     */
    @Override
    public String getWorkflowName(KeplerLSID lsid) throws QueryException
    {
        String retval = null;
        
        try
        {
            _psWorkflowNameForLSID.setString(1, lsid.toStringWithoutRevision());
            ResultSet result = null;
            try
            {
                result = _psWorkflowNameForLSID.executeQuery();
                if(result.next())
                {
                    retval = result.getString(1);
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying workflow name: ", e);
        }
        
        return retval;
    }

    /** Get a workflow name for an execution LSID.
     *  @param lsid the execution LSID.
     *  @return the workflow name if found, otherwise null.
     */
    @Override
    public String getWorkflowNameForExecution(KeplerLSID lsid) throws QueryException {
        
        synchronized(_psWorkflowNameForExecutionLSID) {
            try {
                _psWorkflowNameForExecutionLSID.setString(1, lsid.toString());
                try(ResultSet result = _psWorkflowNameForExecutionLSID.executeQuery();) {
                    if(result.next()) {
                        String retval = result.getString(1);
                        if(result.next()) {
                            System.err.println("WARNING: more than one workflow execution with LSID " + lsid);
                        }
                        return retval;
                    }
                }
            }
            catch(SQLException e) {
                throw new QueryException("Error querying workflow name: ", e);
            }
        }
        return null;
    }
    
    /** Get changes within a timespan. */
    public List<Integer> getChangesForTimespan(Date start, Date end)
        throws QueryException 
    {
        return _getChangesForTimespan(start, end, null, null);
    }

    /** Get a list of all executions. */
    @Override
    public List<Integer> getExecutions() throws QueryException
    {
        try
        {
            return _getIntResults(_psExecutions, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions: ", e);
        }
    }
    
    /** Get a list of all execution LSIDs. */
    @Override
    public List<KeplerLSID> getExecutionLSIDs() throws QueryException {
        List<KeplerLSID> execLsids = new ArrayList<KeplerLSID>();
        try
        {
            synchronized(_psExecutionLSIDs)
            {
                ResultSet result = null;
                try
                {
                    result = _psExecutionLSIDs.executeQuery();
                    while (result.next())
                    {
                        try {
                            KeplerLSID execLSID = new KeplerLSID(result.getString("lsid"));
                            if (!execLsids.contains(execLSID)){
                                execLsids.add(execLSID);
                            }
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
                finally
                {
                    if(result != null)
                    {
                        result.close();
                    }
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query for execution LSIDs");
        }
        return execLsids;
    }

    /** Get a list of executions for workflow(s) with tags that contain tagsSearchString 
     * (case insensitive)
     */
    @Override
    public List<Integer> getExecutionIdsForTags(String tagsSearchString) throws QueryException {
        List<Integer> execIds = new ArrayList<Integer>();
        try
        {
            synchronized(_psExecutionIdsForTags)
            {
                tagsSearchString = tagsSearchString.toLowerCase();
                _psExecutionIdsForTags.setString(1, "%" + tagsSearchString + "%");
                ResultSet result = null;
                try
                {
                    result = _psExecutionIdsForTags.executeQuery();
                    while (result.next())
                    {
                        try {
                            int execId = result.getInt("wf_exec_id");
                            if (!execIds.contains(execId)){
                                execIds.add(execId);
                            }
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
                finally
                {
                    if(result != null)
                    {
                        result.close();
                    }
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query for execution ids for Tags:"+tagsSearchString);
        }
        return execIds;
    }    
    
    @Override
    public Map<NamedOntClass, String> getTagClassesForExecutionId(int execId) throws QueryException {
        Map<NamedOntClass, String> tags = new HashMap<NamedOntClass, String>();
        try
        {
            synchronized(_psTagURNsForExecutionId)
            {
                _psTagURNsForExecutionId.setInt(1, execId);
                ResultSet result = null;
                try
                {
                    result = _psTagURNsForExecutionId.executeQuery();
                    while (result.next())
                    {
                        try {
                            String type = result.getString("type"); //FIXME hardcoded col names
                            boolean isRemovable = (WorkflowRun.TAG_TYPE_RUN).equals(type);
                            String tagURI = result.getString("urn");
    //                        String ontologyURI = result.getString("ontology_uri");
                            NamedOntClass cls = NamedOntClass.createNamedOntClassFromURI(tagURI);
                            cls.setRemovable(isRemovable);
                            if (!tags.containsKey(cls)) {
                                tags.put(cls, type);
                            }
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
                finally
                {
                    if(result != null)
                    {
                        result.close();
                    }
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query for tags for execution id:"+execId);
        }
        return tags;
    }
    
    /** Get the type of a tag, null if none */
    @Override
    public String getTypeForTag(String conceptId) {
        String type = null;

        try{
            synchronized(_psTypeForTag)
            {
                _psTypeForTag.setString(1, conceptId);
                ResultSet result = null;
                try
                {
                    result = _psTypeForTag.executeQuery();
                    while (result.next())
                    {
                        type = result.getString("type");
                    }
                }
                finally
                {
                    if(result != null)
                    {
                        result.close();
                    }
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return type;
    }


    @Override
    public List<String> getTagsForExecutionId(int execId) throws QueryException {
        List<String> tags = new ArrayList<String>();
        try
        {
            synchronized(_psTagsForExecutionId)
            {
                _psTagsForExecutionId.setInt(1, execId);
                ResultSet result = null;
                try
                {
                    result = _psTagsForExecutionId.executeQuery();
                    while (result.next())
                    {
                        try {
                            if (!tags.contains(result.getString("searchstring"))){
                                tags.add(result.getString("searchstring"));
                            }
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
                finally
                {
                    if(result != null)
                    {
                        result.close();
                    }
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query for tags for execution id:"+execId);
        }
        return tags;
    }

    public List<String> getTagURNsForExecutionLSID(KeplerLSID execLSID) throws QueryException {
        List<String> tags = new ArrayList<String>();
        try
        {
            synchronized(_psTagURNsForExecutionId)
            {
                _psTagURNsForExecutionId.setString(1, execLSID.toString());
                ResultSet result = null;
                try
                {
                    result = _psTagURNsForExecutionId.executeQuery();
                    while (result.next())
                    {
                        try {
                            if (!tags.contains(result.getString("urn"))) {
                                tags.add(result.getString("urn"));
                            }
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
                finally
                {
                    if(result != null)
                    {
                        result.close();
                    }
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query for tags for execution LSID:"+execLSID.toString());
        }
        return tags;
    }
    
    /** Get workflow MoML for an execution, using execution id */
    @Override
    public String getMoMLForExecution(int execId) throws QueryException
    {
        try 
        {
            synchronized(_psMomlForExecutionId)
            {
                _psMomlForExecutionId.setInt(1, execId);
                ResultSet result = null;
                
                try
                {
                    result = _psMomlForExecutionId.executeQuery();
    
                    if(!result.next())
                    {
                        throw new QueryException("No MoML found for execution " +
                            execId); 
                    }
    
                    byte[] data = result.getBytes(1);
                    return _changeUnnamedWorkflow(new String(data));
                }
                finally
                {
                    if(result != null)
                    {
                        result.close();
                    }
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query for moml: ", e);
        }
    }
    
    /** Get workflow MoML for an execution, using execution lsid. */
    @Override
    public String getMoMLForExecution(KeplerLSID lsid) throws QueryException
    {
        try 
        {
            synchronized(_psMomlForExecutionLSID)
            {
                _psMomlForExecutionLSID.setString(1, lsid.toString());
                ResultSet result = null;
                
                try
                {
                    result = _psMomlForExecutionLSID.executeQuery();
    
                    if(!result.next())
                    {
                        throw new QueryException("No Workflow MoML found for Run with KeplerLSID " +
                            lsid.toString()); 
                    }
    
                    byte[] data = result.getBytes(1);
                    return _changeUnnamedWorkflow(new String(data));
                }
                finally
                {
                    if(result != null)
                    {
                        result.close();
                    }
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query for moml associated with run lsid: ", e);
        }
    }

    //TODO revisit when putting runs in diff dirs, where each dir might
    // want a separate copy of the same wf.
    /** Get workflow MoMLs for a list of execution lsids. Only returns one copy 
     *  of any duplicates.
     */    
    @Override
    public ArrayList<String> getMoMLForExecutionLSIDs(List<KeplerLSID> lsids)
    {
        //Note that we don't return duplicates (i.e. e.g. 2 runs using 1 wf)
        ListIterator<KeplerLSID> i = lsids.listIterator();
        ArrayList<String> momls = new ArrayList<String>();
        while (i.hasNext()) 
        {
            KeplerLSID j = i.next();
            try
            {
                String moml = getMoMLForExecution(j);
                if (!momls.contains(moml))
                {
                    momls.add(moml);
                }
                
            }
            catch(QueryException qe)
            {
                System.out.println("ERROR trying to getMoMLForExecution("+j+"): "+qe);
            }
        }
        return momls;
    }
    

    /** Get executions within a timespan. */
    @Override
    public List<Integer> getExecutionsForTimespan(Date start, Date end)
        throws QueryException 
    {
        return _getExecutionsForTimespan(start, end, null, null);
    }

        
    /**
     *  Get WorkflowRuns for a list of execution LSIDs
     */
    @Override
    public Map<KeplerLSID, WorkflowRun> getWorkflowRunsForExecutionLSIDs(List<KeplerLSID> executionLSIDs)
        throws QueryException 
    {
           //FIXME: hardcoded references to row names
                
        Statement statement = null;
        try
        {
            Map<KeplerLSID, WorkflowRun> workflowRuns = new LinkedHashMap<KeplerLSID, WorkflowRun>();
        
            if(executionLSIDs.isEmpty()) {
                return workflowRuns;
            }

            // __psWorkflowRunsForExecutionLSIDs.setArray(1, execIds);
            // Note1: Manually setting up this query since java.sql.Array is not designed for this. 
            // To solve such issues it's suggested setting up prepared statement with more ?'s 
            // than you think you'll ever need, set them all null, then set as many as you need. 
            // Not sure if that would really be better, so I'm not doing it currently.
            //        Could also instead just iterate across a number queries, but that
            //        seems less efficient.
            StringBuilder execLSIDs = new StringBuilder();
            for (Iterator<KeplerLSID> i=executionLSIDs.iterator(); i.hasNext(); )
            {
                execLSIDs.append("'"+i.next().toString()+"'");
                if(i.hasNext())
                {
                       execLSIDs.append(",");
                }
            }
            String workflowStr = _dbType.getTableName("workflow");
            String wfExecStr = _dbType.getTableName("workflow_exec");
            String errorStr = _dbType.getTableName("error");

            // NOTE: user is a reserved column name in some RDBMS.
            String userColumnStr = _dbType.getColumnName("user");

            String queryStr = 
                "SELECT wf.name as wf_name, wfexec.wf_full_lsid as wf_lsid," +
                    "wfexec." + userColumnStr + " as user, " + 
                    "wfexec.id as wfexec_id, wfexec.start_time, " +
                    "wfexec.end_time, wfexec.lsid as exec_lsid, wfexec.host_id as host_id, " +
                    "wfexec.annotation as annotation, wfexec.derived_from as derived_from, " +
                    "wfexec.module_dependencies as module_dependencies, " +
                    "wfexec.type as type, " +
                    "err.id as error_id, err.message as error_message " +
                "FROM " + workflowStr + " wf, " + wfExecStr + " wfexec " +
                "LEFT JOIN " + errorStr + " err ON wfexec.id=err.exec_id " +
                "WHERE wf.id=wfexec.wf_id AND wfexec.lsid in (" + execLSIDs + ")";
            
            statement = _dbType.getStatement();
            
            LinkedHashMap<Integer, String> errorMsgs = new LinkedHashMap<Integer, String>();
            
            ResultSet result = null;
            try
            {
                result = statement.executeQuery(queryStr);
                KeplerLSID lastExecLSID = null;
                while(result.next())
                {
                    KeplerLSID execLSID = null;
                    try
                    {
                        execLSID = new KeplerLSID(result.getString("exec_lsid"));
                    }
                    catch(Exception e)
                    {
                        throw new QueryException("Error creating KeplerLSID for execution: ", e);
                    }

                    KeplerLSID wfLSID = null;
                    try
                    {
                        wfLSID = new KeplerLSID(result.getString("wf_lsid"));
                    }
                    catch(Exception e)
                    {
                        throw new QueryException("Error creating KeplerLSID for workflow: ", e);
                    }

                    int errorId;
                    
                    if (!execLSID.equals(lastExecLSID))
                    {
                        errorMsgs = new LinkedHashMap<Integer, String>();
                    }
                    
                    if (result.getString("error_message") != null)
                    {
                        errorId = Integer.parseInt(result.getString("error_id"));
                        errorMsgs.put(errorId, result.getString("error_message"));
                    }

                    Date startTime = result.getTimestamp("start_time");
                    Date endTime = result.getTimestamp("end_time");
                    
                    // endTime may be null if not set
                    if(endTime == null)
                    {
                        endTime = new Date(0);
                    }
                    
                    Long duration = (endTime.getTime() - startTime.getTime()) / numMillisecondsInASecond;
                    if (endTime.compareTo(startTime) < 0)
                    {
                        duration = -1L;
                    }
                    
                    int execId = Integer.parseInt(result.getString("wfexec_id"));
                    // Note: in the case of resultSet that has multiple rows per exec, we just overwrite the entry in
                    // workflowRuns each time, each time with a more complete version of errorMessages. 
                    // a cleaner soln would be to look-ahead and then use result.previous, but not all dbs support result.previous
                    // (e.g. hsqldb)
                    WorkflowRun run = null;                    
                    try
                    {
                        run = new WorkflowRun(execLSID,
                                result.getString("derived_from"),
                            result.getString("user"),
                            result.getString("wf_name"),
                            wfLSID, startTime, duration, execId, errorMsgs, 
                            result.getString("host_id"),
                            result.getString("annotation"),
                            result.getString("module_dependencies"),
                            result.getString("type"),
                            getTagClassesForExecutionId(execId));
                    }
                    catch(Exception e)
                    {
                        e.printStackTrace();
                        throw new QueryException("Error creating WorkflowRun: ", e);
                    }
                    
                    // note it's important to use run.getExecLSID() instead of execLSID here
                    // since WorkflowRun constructor may change the execLSID revision.
                    workflowRuns.put(run.getExecLSID(), run);
                    
                    lastExecLSID = execLSID;
                }
            } 
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
       
            return workflowRuns;
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            throw new QueryException("Unable to query workflow runs: ", e);
        }
        finally
        {
            if(statement != null)
            {
                try
                {
                    statement.close();
                } 
                catch (SQLException e)
                {
                    throw new QueryException("Unable to close statement: ", e);
                }
            }
        }
    }
    
    @Override
    public List<WorkflowRun> getWorkflowRunsForUser(String user) throws QueryException {

        List<WorkflowRun> workflowRuns = new LinkedList<WorkflowRun>();

        String workflowStr = _dbType.getTableName("workflow");
        String wfExecStr = _dbType.getTableName("workflow_exec");
        String errorStr = _dbType.getTableName("error");

        // NOTE: user is a reserved column name in some RDBMS.
        String userColumnStr = _dbType.getColumnName("user");

        String queryStr = 
                "SELECT wf.name as wf_name, wfexec.wf_full_lsid as wf_lsid," +
                        "wfexec." + userColumnStr + " as user, " + 
                        "wfexec.id as wfexec_id, wfexec.start_time, " +
                        "wfexec.end_time, wfexec.lsid as exec_lsid, wfexec.host_id as host_id, " +
                        "wfexec.annotation as annotation, wfexec.derived_from as derived_from, " +
                        "wfexec.module_dependencies as module_dependencies, " +
                        "wfexec.type as type, " +
                        "err.id as error_id, err.message as error_message " +
                        "FROM " + workflowStr + " wf, " + wfExecStr + " wfexec " +
                        "LEFT JOIN " + errorStr + " err ON wfexec.id=err.exec_id " +
                        "WHERE wf.id=wfexec.wf_id AND wfexec.user = ?";

        try(PreparedStatement statement = _dbType.getPrepStatement(queryStr))
        {
            statement.setString(1, user);
            
            LinkedHashMap<Integer, String> errorMsgs = new LinkedHashMap<Integer, String>();

            try(ResultSet result = statement.executeQuery())
            {
                KeplerLSID lastExecLSID = null;
                while(result.next())
                {
                    KeplerLSID execLSID = null;
                    try
                    {
                        execLSID = new KeplerLSID(result.getString("exec_lsid"));
                    }
                    catch(Exception e)
                    {
                        throw new QueryException("Error creating KeplerLSID for execution: ", e);
                    }

                    KeplerLSID wfLSID = null;
                    try
                    {
                        wfLSID = new KeplerLSID(result.getString("wf_lsid"));
                    }
                    catch(Exception e)
                    {
                        throw new QueryException("Error creating KeplerLSID for workflow: ", e);
                    }

                    int errorId;

                    if (!execLSID.equals(lastExecLSID))
                    {
                        errorMsgs = new LinkedHashMap<Integer, String>();
                    }

                    if (result.getString("error_message") != null)
                    {
                        errorId = Integer.parseInt(result.getString("error_id"));
                        errorMsgs.put(errorId, result.getString("error_message"));
                    }

                    Date startTime = result.getTimestamp("start_time");
                    Date endTime = result.getTimestamp("end_time");

                    // endTime may be null if not set
                    if(endTime == null)
                    {
                        endTime = new Date(0);
                    }

                    Long duration = (endTime.getTime() - startTime.getTime()) / numMillisecondsInASecond;
                    if (endTime.compareTo(startTime) < 0)
                    {
                        duration = -1L;
                    }

                    int execId = Integer.parseInt(result.getString("wfexec_id"));
                    // Note: in the case of resultSet that has multiple rows per exec, we just overwrite the entry in
                    // workflowRuns each time, each time with a more complete version of errorMessages. 
                    // a cleaner soln would be to look-ahead and then use result.previous, but not all dbs support result.previous
                    // (e.g. hsqldb)
                    WorkflowRun run = null;                    
                    try
                    {
                        run = new WorkflowRun(execLSID,
                                result.getString("derived_from"),
                                result.getString("user"),
                                result.getString("wf_name"),
                                wfLSID, startTime, duration, execId, errorMsgs, 
                                result.getString("host_id"),
                                result.getString("annotation"),
                                result.getString("module_dependencies"),
                                result.getString("type"),
                                getTagClassesForExecutionId(execId));
                    }
                    catch(Exception e)
                    {
                        e.printStackTrace();
                        throw new QueryException("Error creating WorkflowRun: ", e);
                    }

                    // note it's important to use run.getExecLSID() instead of execLSID here
                    // since WorkflowRun constructor may change the execLSID revision.
                    workflowRuns.add(run);

                    lastExecLSID = execLSID;
                }
            } 

            return workflowRuns;
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            throw new QueryException("Unable to query workflow runs: ", e);
        }
    }
    
    /** Get a list of executions for a specific workflow. */
    @Override
    public List<Integer> getExecutionsForWorkflow(String workflow)
        throws QueryException
    {
        try
        {
            _psExecutionsForWorkflow.setString(1, workflow);
            return _getIntResults(_psExecutionsForWorkflow, 1);            
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                "workflow " + workflow + ": ", e);
        }
    }
    
    /** Get the execution ids for a specific exec LSID. If no execution
     *  exists, returns null.
     */
    @Override
    public Integer getExecutionForExecutionLSID(KeplerLSID execLSID)
        throws QueryException
    {
        try
        {
            _psExecutionForExecutionLSID.setString(1, execLSID.toString());
            ResultSet result = null;
            try
            {
                int retval;
                result = _psExecutionForExecutionLSID.executeQuery();
                if(! result.next() || (retval = result.getInt(1)) == 0)
                {
                    return null;
                }
                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                "run " + execLSID + ": ", e);
        }
    }
    
    /** Get execution id for an execution LSID without revision. 
     *  If no execution exists, returns null.
     */
    @Override
    public Integer getExecutionForExecutionLSIDWithoutRevision(
                        String execLSIDWithoutRevision) throws QueryException {
        try
        {
            _psExecutionForExecutionLSIDWithoutRevision.setString(1, execLSIDWithoutRevision+":%");
            ResultSet result = null;
            try
            {
                int retval;
                result = _psExecutionForExecutionLSIDWithoutRevision.executeQuery();
                if(! result.next() || (retval = result.getInt(1)) == 0)
                {
                    return null;
                }
                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                "run " + execLSIDWithoutRevision + ": ", e);
        }
        }
    
    /** Get execution id for execution that has the given LSID as its
         *      oldest referral. 
         */
    @Override
    public Integer getExecutionForOldestReferralExecutionLSIDWithoutRevision(
                String execLSIDWithoutRevision) throws QueryException 
    {
        try
        {
        
                // Even though what we really want to match on is
                // the last and this could potentially match on one 
                // in the middle, that state is currently possible, so this regex should be ok:
            _psExecutionForOldestReferralExecutionLSIDWithoutRevision.setString(1, "%"+execLSIDWithoutRevision+":%");
            ResultSet result = null;
            try
            {
                int retval;
                result = _psExecutionForOldestReferralExecutionLSIDWithoutRevision.executeQuery();
                if(! result.next() || (retval = result.getInt(1)) == 0)
                {
                    return null;
                }
                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                "run " + execLSIDWithoutRevision + ": ", e);
        }
    }
    
    /** Get the execution LSID for a specific execution id. If no execution
     *  exists, returns null.
     */
    @Override
    public KeplerLSID getExecutionLSIDForExecution(Integer execId)
        throws QueryException 
    {
        synchronized(_psExecutionLSIDForExecution) {
            try
            {
                _psExecutionLSIDForExecution.setInt(1, execId);
                try(ResultSet result = _psExecutionLSIDForExecution.executeQuery();)
                {
                    if(!result.next())
                    {
                        return null;
                    }
                    KeplerLSID executionLSID = new KeplerLSID(result.getString("lsid"));
                    return executionLSID;
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to query execution lsids for " +
                    "execution id " + execId + ": ", e);
            }
        }
        return null;
    }
    
    /** Get a list of executions for workflow(s) with names that contain text (case insensitive). */
    public List<Integer> getExecutionsForWorkflowNameContains(String text)
        throws QueryException
    {
        try
        {
            text = text.toLowerCase();
            _psExecutionsForWorkflowNameContains.setString(1, "%" + text + "%");
            return _getIntResults(_psExecutionsForWorkflowNameContains, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                "workflows with names that contain: " + text + ": ", e);
        }
    }
    
    /** Get a list of executions for workflow(s) that match Workflow Run Manager (WRM) search criteria.*/
    @Override
    public List<Integer> getExecutionsForWorkflowRuns(String workflowName, String userName)
        throws QueryException
    {
        try
        {
            workflowName = workflowName.toLowerCase();
            _psExecutionsForWorkflowRuns.setString(1, "%" + workflowName + "%");
            userName = userName.toLowerCase();
            _psExecutionsForWorkflowRuns.setString(2, "%" + userName + "%");
            return _getIntResults(_psExecutionsForWorkflowRuns, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                "workflows with names that contain: " + workflowName + 
                ", usernames that contain: + "+ userName + ": ", e);
        }
    }
    
    /** Get a list of executions for workflow(s) that match Workflow Run Manager (WRM) search criteria.*/
    @Override
    public List<Integer> getExecutionsForWorkflowRuns(String workflowName,
        String userName, Date after, Date before, int execIdAfter,
        int execIdBefore)
        throws QueryException
    {
        try
        {
            workflowName = workflowName.toLowerCase();
            _psExecutionsForWorkflowRuns2.setString(1, "%" + workflowName + "%");
            userName = userName.toLowerCase();
            _psExecutionsForWorkflowRuns2.setString(2, "%" + userName + "%");
            _psExecutionsForWorkflowRuns2.setTimestamp(3,
                new Timestamp(after.getTime()));
            _psExecutionsForWorkflowRuns2.setTimestamp(4,
                new Timestamp(before.getTime()));
            _psExecutionsForWorkflowRuns2.setInt(5, execIdAfter);
            _psExecutionsForWorkflowRuns2.setInt(6, execIdBefore);

            return _getIntResults(_psExecutionsForWorkflowRuns2, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                "workflows with names that contain: " + workflowName + 
                ", usernames that contain: + "+ userName + 
                ", between " + after + " and "+ before + 
                ", with execId between "+execIdAfter + " and " + execIdBefore+": ", e);
        }
    }
    
    /** Get a list of executions for workflow(s) that match Workflow Run Manager (WRM) search criteria.*/
    @Override
    public List<KeplerLSID> getExecutionLSIDsForWorkflowRuns(
            String workflowName, String userName, Date after, Date before,
            int execIdAfter, int execIdBefore) throws QueryException {
        List<KeplerLSID> execLSIDs = new ArrayList<KeplerLSID>();
        
        try{
            workflowName = workflowName.toLowerCase();
            _psExecutionLSIDsForWorkflowRuns.setString(1, "%" + workflowName + "%");
            userName = userName.toLowerCase();
            _psExecutionLSIDsForWorkflowRuns.setString(2, "%" + userName + "%");
            _psExecutionLSIDsForWorkflowRuns.setTimestamp(3,
                new Timestamp(after.getTime()));
            _psExecutionLSIDsForWorkflowRuns.setTimestamp(4,
                new Timestamp(before.getTime()));
            _psExecutionLSIDsForWorkflowRuns.setInt(5, execIdAfter);
            _psExecutionLSIDsForWorkflowRuns.setInt(6, execIdBefore);
            
            ResultSet result = null;
            try
            {
                result = _psExecutionLSIDsForWorkflowRuns.executeQuery();
                while (result.next())
                {
                    try {
                        KeplerLSID execLSID = new KeplerLSID(result.getString("lsid"));
                        if (!execLSIDs.contains(execLSID)){
                            execLSIDs.add(execLSID);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                    "workflows with names that contain: " + workflowName + 
                    ", usernames that contain: + "+ userName + 
                    ", between " + after + " and "+ before + 
                    ", with execId between "+execIdAfter + " and " + execIdBefore+": ", e);
        }
        
        return execLSIDs;
    }
    
    /** Get a list of executions for workflow(s) that match Workflow Run Manager (WRM) search criteria 
     *  before a date.
     *  */
    @Override
    public List<Integer> getExecutionsForWorkflowRunsBefore(String workflowName, String userName, Date before)
        throws QueryException
    {
        try
        {
            workflowName = workflowName.toLowerCase();
            _psExecutionsForWorkflowRunsBefore.setString(1, "%" + workflowName + "%");
            userName = userName.toLowerCase();
            _psExecutionsForWorkflowRunsBefore.setString(2, "%" + userName + "%");
            _psExecutionsForWorkflowRunsBefore.setTimestamp(3,
                new Timestamp(before.getTime()));
            return _getIntResults(_psExecutionsForWorkflowRunsBefore, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                    "workflows with names that contain: " + workflowName + 
                    ", usernames that contain: + "+ userName + 
                    ", before " + before + ": ", e);
        }
    }
    
    /** Get a list of executions for workflow(s) that match Workflow Run Manager (WRM) search criteria 
     *  after a date.
     *  */
    @Override
    public List<Integer> getExecutionsForWorkflowRunsAfter(String workflowName, String userName, Date after)
        throws QueryException
    {
        try
        {
            workflowName = workflowName.toLowerCase();
            _psExecutionsForWorkflowRunsAfter.setString(1, "%" + workflowName + "%");
            userName = userName.toLowerCase();
            _psExecutionsForWorkflowRunsAfter.setString(2, "%" + userName + "%");
            _psExecutionsForWorkflowRunsAfter.setTimestamp(3,
                new Timestamp(after.getTime()));
            return _getIntResults(_psExecutionsForWorkflowRunsAfter, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                    "workflows with names that contain: " + workflowName + 
                    ", usernames that contain: + "+ userName + 
                    ", after " + after + ": ", e);
        }
    }
    
    /** Get a list of executions for workflow(s) that match Workflow Run Manager (WRM) search criteria 
     *  "at" a date. We treat this date as a string so we can match using LIKE 'date%'.
     *  */
    public List<Integer> getExecutionsForWorkflowRunsAt(String workflowName, String userName, String date)
        throws QueryException
    {
        try
        {
            workflowName = workflowName.toLowerCase();
            _psExecutionsForWorkflowRunsAt.setString(1, "%" + workflowName + "%");
            userName = userName.toLowerCase();
            _psExecutionsForWorkflowRunsAt.setString(2, "%" + userName + "%");
            _psExecutionsForWorkflowRunsAt.setString(3, date + "%"); //TODO fix this
            return _getIntResults(_psExecutionsForWorkflowRunsAt, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                    "workflows with names that contain: " + workflowName + 
                    ", usernames that contain: + "+ userName + 
                    ", at " + date + ": ", e);
        }
    }
    
    /** Get a list of executions for a specific execution annotation. */
    @Override
    public List<Integer> getExecutionsForAnnotation(String annotation) 
        throws QueryException
    {
        try
        {
            _psExecutionsForAnnotation.setString(1, annotation);
            return _getIntResults(_psExecutionsForAnnotation, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
                "annotation " + annotation + ": ", e);
        }
    }

    /** Get the start and end timestamps of an execution.
     *  @param execId the execution id
     *  @return a two-element array: the first entry is the start timestamp,
     *  and the second is the end timestamp. If the execution id is not found,
     *  returns null.
     */
    @Override
    public Date[] getTimestampsForExecution(int execId)
        throws QueryException
    {
        synchronized(_psTimestampsForExecution) {
            try
            {
                _psTimestampsForExecution.setInt(1, execId);
                try(ResultSet result = _psTimestampsForExecution.executeQuery();)
                {
                    if(result.next())
                    {
                        Date[] retval = new Date[2];
                        retval[0] = result.getTimestamp("start_time");
                        retval[1] = result.getTimestamp("end_time");
                        return retval;
                    }
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Error querying timestampts for execution " + execId, e);
            }
        }
        return null;
    }
    
    /** Get the last execution for a workflow. NOTE: the LSID revision 
     *  is ignored.
     *  @param lsid the workflow lsid.
     *  @return if workflow has been executed, the last execution id.
     *  otherwise, returns null.
     */
    @Override
    public Integer getLastExecutionForWorkflow(KeplerLSID lsid)
        throws QueryException
    {
        synchronized(_psLastExecutionForWorkflowLSID) {
            try
            {
                _psLastExecutionForWorkflowLSID.setString(1,
                    lsid.toStringWithoutRevision());
                try(ResultSet result = _psLastExecutionForWorkflowLSID.executeQuery();)
                {
                    if(!result.next()) {
                        return null;
                    }
                    int retval = result.getInt(1);
                    if(retval == 0) {
                        return null;
                    }
                    return Integer.valueOf(retval);
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to query last workflow " +
                    "execution: ", e);
            }
        }
    }

    /** Get the last execution for a workflow.
     *  @param workflow the workflow name
     *  @return if workflow has been executed, the last execution id.
     *  otherwise, returns null.
     */
    @Override
    public Integer getLastExecutionForWorkflow(String workflow)
        throws QueryException
    {
        try
        {
            _psLastExecutionForWorkflow.setString(1, workflow);
            ResultSet result = null;
            try
            {
                int retval;
                result = _psLastExecutionForWorkflow.executeQuery();
                if(! result.next() || (retval = result.getInt(1))== 0)
                {
                    return null;
                }
                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query last workflow " +
                "execution: ", e);
        }
    }

    /** Get the last execution LSID for a workflow.
     *  @param workflow the workflow name.
     *  @return execution lsid
     */
     @Override
    public KeplerLSID getLastExecutionLSIDForWorkflow(String workflow)
        throws QueryException
     {
        try   
        {
            _psLastExecutionLSIDForWorkflowName.setString(1, workflow);
            ResultSet result = null;
            try
            {
                result = _psLastExecutionLSIDForWorkflowName.executeQuery();
                if(! result.next())
                {
                    throw new QueryException("No executions found for " +
                        "workflow " + workflow);
                }
                String lsidStr = result.getString(1);
                return new KeplerLSID(lsidStr);
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(Exception e)
        {
            throw new QueryException("Unable to query last workflow " +
                "execution: ", e);
        }
     }
     
     /** Get the last execution LSID for a workflow.
      *  @param lsid the workflow lsid.
      *  @return execution lsid
      */
      @Override
    public KeplerLSID getLastExecutionLSIDForWorkflow(KeplerLSID lsid)
         throws QueryException
      {
         try   
         {
             _psLastExecutionLSIDForWorkflowLSID.setString(1, lsid.toStringWithoutRevision());
             ResultSet result = null;
             try
             {
                 result = _psLastExecutionLSIDForWorkflowLSID.executeQuery();
                 if(! result.next())
                 {
                     throw new QueryException("No executions found for " +
                         "workflow LSID " + lsid);
                 }
                 String lsidStr = result.getString(1);
                 return new KeplerLSID(lsidStr);
             }
             finally
             {
                 if(result != null)
                 {
                     result.close();
                 }
             }
         }
         catch(Exception e)
         {
             throw new QueryException("Unable to query last workflow " +
                 "execution: ", e);
         }
      }


   /** Get the error for an execution. Returns null if no error message
    *  was found. NOTE: returning null is not an indication if the
    *  execution had an error.
    *  @see isErrorForExecution
    *  @param lsid the workflow execution lsid. 
    */
    @Override
    public String getErrorForExecution(KeplerLSID lsid) throws QueryException
    {
        String retval = null; 

        try
        {
            _psErrorForExecution.setString(1, lsid.toString());
            ResultSet result = null;

            try
            {
                result = _psErrorForExecution.executeQuery();
                if(result.next())
                {
                    retval = result.getString(1); 
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying workflow error: ", e);
        }

        return retval;
    }

    /** Returns true if the execution had an error. */
    @Override
    public boolean isErrorForExecution(KeplerLSID executionLSID) throws QueryException
    {
        try
        {
            ResultSet result = null;
            try
            {
                _psDoesExecutionHaveError.setString(1, executionLSID.toString());
                result = _psDoesExecutionHaveError.executeQuery();
                return result.next();
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query if execution has an error: ", e);
        }
    }

    /** See if an execution appears to be imported. */
    @Override
    public boolean isImportedExecution(KeplerLSID executionLSID) throws QueryException
    {
        try
        {
            ResultSet result = null;
            try
            {
                _psIsExecutionImported.setString(1, executionLSID.toString());
                result = _psIsExecutionImported.executeQuery();
                // if there are rows in the query, then the run was not
                // imported
                return (!result.next());
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query if execution was imported:", e);
        }
    }

    /** Get a list of executions corresponding to a specific type. */
    @Override
    public List<KeplerLSID> getExecutionsForType(WorkflowRun.type type) throws QueryException
    {
        ResultSet result = null;
        try
        {
            try
            {
                List<KeplerLSID> retval = new LinkedList<KeplerLSID>();
                _psExecutionsForType.setString(1, type.toString());
                result = _psExecutionsForType.executeQuery();
                while(result.next())
                {
                    retval.add(new KeplerLSID(result.getString(1)));
                }
                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(Exception e)
        {
            throw new QueryException("Unable to query executions for type: ", e);
        }
    }

    /** Get an sequence of tokens for an execution.
     *  @param execId the execution id
     *  @param portId the port id. If null, returns the ids of
     *  tokens read in entire execution.
     *  @param last if true, the sequence starts at the last token created
     *  and goes backwards to the first; otherwise the sequence starts at
     *  the first.
     */
    @Override
    public List<Integer> getTokensForExecution(int execId, Integer portId, boolean last)
        throws QueryException
    {
        List<Integer> list = new LinkedList<Integer>();
    
        try
        {
            ResultSet result = null;
            try
            {
                if (portId != null)
                {
                    _psEntityTokensForExecution.setInt(1, execId);
                    _psEntityTokensForExecution.setInt(2, portId);
                    _psEntityTokensForExecution.setInt(3, execId);
                    _psEntityTokensForExecution.setInt(4, portId);
                    result = _psEntityTokensForExecution.executeQuery();
                }
                else
                {
                    _psTokensForExecution.setInt(1, execId);
                    result = _psTokensForExecution.executeQuery();
                }
                while(result.next())
                {
                    list.add(result.getInt(1));
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to retrieve for tokens: ", e);
        }

        /*
        if(list.size() == 0)
        {
            throw new QueryException("No tokens found for execution " + execId);
        }
        */
    
        Integer[] array = list.toArray(new Integer[0]);
        Arrays.sort(array);
    
        LinkedList<Integer> retval = new LinkedList<Integer>();
    
        for(int i = 0; i < array.length; i++)
        {
            if(last)
            {
                retval.addFirst(array[i]);
            }
            else
            {
                retval.addLast(array[i]);
            }
        }
        return retval;
    }

    /** Get the firing id(s) of the actor(s) that read or wrote a token.
     *  @param tokenId the token id
     *  @param read If true, return the actor(s) firing id(s) that read
     *  the token. Otherwise, return the actor firing that wrote the token.
     *  */
    @Override
    public List<Integer> getActorFiringForToken(int tokenId, boolean read)
        throws QueryException
    {
        try 
        {
            List<Integer> retval;

            if(read)
            {
                synchronized(_psActorFiringsForTokenRead)
                {
                    _psActorFiringsForTokenRead.setInt(1, tokenId);
                    retval = _getIntResults(_psActorFiringsForTokenRead, 1);
                }
            }
            else
            {
                retval = new LinkedList<Integer>();

                synchronized(_psActorFiringForTokenWrite) 
                {
                    _psActorFiringForTokenWrite.setInt(1, tokenId);
                    ResultSet result = null;
                    
                    try
                    {
                        result =                         
                            _psActorFiringForTokenWrite.executeQuery();

                        // make sure a firing produced this token
                        if(!result.next())
                        {
                            throw new QueryException("No firing produced token " +
                                tokenId);
                        }
                        
                        retval.add(result.getInt(1));
                    }
                    finally
                    {
                        if(result != null)
                        {
                            result.close();
                        }
                    }
                }
            }
                
            return retval;
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query for actor firing: ", e);
        }
    }
  
    /** Get tokens for a firing.
     *  @param fireId the firing id
     *  @param read if true, get the tokens read; otherwise get the tokens
     *  written.
     */
    @Override
    public List<Integer> getTokensForFiring(int fireId, boolean read)
        throws QueryException
    {
        PreparedStatement prep;
        if(read)
        {
            prep = _psTokensReadForFiring;    
        }
        else
        {
            prep = _psTokensWrittenForFiring;    
        }

        synchronized(prep) {
            try
            {
                prep.setInt(1, fireId);
                return _getIntResults(prep, 1);
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to query tokens for firing id: ", e);
            }
        }
    }
    
    /** Get the value of a token. */
    @Override
    public String getTokenValue(int tokenId) throws QueryException
    {
        String dataId = null;
        String tokenValue;
        synchronized(_psPortEventData) {
            try
            {
                _psPortEventData.setInt(1, tokenId);
                try(ResultSet result = _psPortEventData.executeQuery();)
                {
                    if(!result.next()) {
                        throw new QueryException("Token not found: " + tokenId);                
                    }
                    tokenValue = result.getString(1);
                    if(tokenValue == null) {
                        dataId = result.getString(2);
                    }
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to query token value from port_event table: ", e);
            }
        }
        
        if(dataId == null) {
            if(tokenValue == null) {
                throw new QueryException("No value found for token " + tokenId);
            }
            return tokenValue;
        } else {
            synchronized(_psDataContents) {
                //System.out.println("querying from data");
                try {
                    _psDataContents.setString(1, dataId);
                    try(ResultSet result = _psDataContents.executeQuery();) {
                        if(!result.next()) {
                            throw new QueryException("Data md5 not found: " + dataId);
                        }
                        return new String(result.getBytes(1));
                    }
                } catch(SQLException e) {
                    throw new QueryException("Unable to query token value from data table: ", e);
                }
            }
        }
    }

    /** Get the type of a token. */
    @Override
    public String getTokenType(int tokenId) throws QueryException
    {
        synchronized(_psTokenType) {
            try
            {
                _psTokenType.setInt(1, tokenId);
                try(ResultSet result = _psTokenType.executeQuery();)
                {
                    if(!result.next())
                    {
                        throw new QueryException("Token not found: " + tokenId); 
                    }
                    return result.getString(1);
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to query token type: ", e);
            }
        }
    }

    /** Get the channel that a token was read or written on.
     *  @param tokenId the token id
     *  @param read if true, return the channel the token was read on.
     *  otherwise, return the channel the token was written on.
     *  @param fireId the actor firing id. can be null for writes, but
     *  must be specified for reads.
     *  @return the channel the token was read or written on.
     */
    @Override
    public Integer getChannelForToken(int tokenId, boolean read, Integer fireId)
            throws QueryException {
        
        PreparedStatement preparedStatement;
        if(read) {
            preparedStatement = _psChannelForTokenRead;
        } else {
            preparedStatement = _psChannelForTokenWrite;
        }
     
        synchronized(preparedStatement) {
            try {
                preparedStatement.setInt(1, tokenId);
                if(read) {
                    preparedStatement.setInt(2, fireId);
                }
                try(ResultSet result = preparedStatement.executeQuery();) {
                    if(result.next()) {
                        return result.getInt(1);
                    }
                }
            } catch (SQLException e) {
                throw new QueryException("Error querying read/write channel.", e);
            }
        }
        return null;
    }

    /** Get the id of entity. If workflow or entity is not found, returns null.
     *  @param entityName name of entity.
     *  @param lsid the workflow LSID. NOTE: the revision is ignored.
     */
    @Override
    public Integer getEntityId(String entityName, KeplerLSID lsid)
        throws QueryException
    {
        try
        {
            _psEntityIdLSID.setString(1, entityName);
            _psEntityIdLSID.setString(2, lsid.toStringWithoutRevision());
            ResultSet result = null;
            try
            {
                int retval;
                result = _psEntityIdLSID.executeQuery();
                if(!result.next() || (retval = result.getInt(1)) == 0)
                {
                    return null;
                }
                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query entity id: ", e);
        }
    }

    /** Get the id of entity. If workflow or entity is not found, returns null. 
     *  @param entityName the name of the entity. 
     *  @param workflowName the name of the workflow.
     */
    @Override
    public Integer getEntityId(String entityName, String workflowName)
        throws QueryException
    {
        try
        {
            _psEntityId.setString(1, entityName);
            _psEntityId.setString(2, workflowName);
            ResultSet result = null;
            try
            {
                int retval;
                result = _psEntityId.executeQuery();
                if(!result.next() || (retval = result.getInt(1)) == 0)
                {
                    return null; 
                }

                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query entity id: ", e);
        }
    }
    
    @Override
    public String getEntityType(Integer entityId) throws QueryException
    {
        try
        {
            _psEntityType.setInt(1, entityId);
            ResultSet result = null;
            try
            {
                result = _psEntityType.executeQuery();
                if(!result.next())
                {
                    throw new QueryException("Entity not found: " + entityId); 
                }

                return result.getString(1);
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query entity type: ", e);
        }
    }
    
    
    @Override
    public Integer getEntityWorkflowId(Integer entityId) throws QueryException
    {
        try
        {
            _psEntityWorkflowId.setInt(1, entityId);
            ResultSet result = null;
            try
            {
                result = _psEntityWorkflowId.executeQuery();
                if(!result.next())
                {
                    throw new QueryException("Entity not found: " + entityId); 
                }

                return result.getInt(1);
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query entity type: ", e);
        }
    }
    
    /** Get an actor's name for a firing id. */
    @Override
    public String getActorName(int fireId) throws QueryException
    {
        synchronized(_psActorNameForFiring) {
            try 
            {
                _psActorNameForFiring.setInt(1, fireId);
                try(ResultSet result = _psActorNameForFiring.executeQuery();)
                {
                    if(!result.next())
                    {
                        throw new QueryException("No actor for firing id.");
                    }
                    return result.getString("name");
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to get actor name: ", e); 
            }
        }
    }

    /** Get an actor's type for a firing id. */
    @Override
    public String getActorType(int fireId) throws QueryException
    {
        try 
        {
            _psActorTypeForFiring.setInt(1, fireId);
            ResultSet result = null;
            try
            {
                result = _psActorTypeForFiring.executeQuery();
                if(!result.next())
                {
                    throw new QueryException("No actor for firing id.");
                }
            
                return result.getString("class");
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to get actor type: ", e); 
        }
    }

    /** Get an actor's parameter name value pairs for a firing id.
     *  NOTE: Parameter values may change during a firing; this method
     *  returns the values at the <b>start</b> of the firing. 
     */
    @Override
    public Map<String,String> getParameterNameValuesForFiring(int fireId)
        throws QueryException
    {
        try
        {
            String actorName = getActorName(fireId);
            _psParamNameValuesForFiring.setString(1, actorName + ".%");
            _psParamNameValuesForFiring.setInt(2, fireId);
            Map<String,String> nameValuesMap = _getParameterNameValues(_psParamNameValuesForFiring);
            
            // use a different map to remove components (see below) to avoid
            // ConcurrentModificationExceptions
            Map<String,String> retval = new LinkedHashMap<String,String>(nameValuesMap);
            
            // remove the components that are not direct parameters
            // e.g., parameters of the actors ports.
            for(String name : nameValuesMap.keySet())
            {
                if(name.lastIndexOf('.') > actorName.length())
                {
                    retval.remove(name);
                }
            }
            
            return retval;
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to get name value pairs: ", e);
        }
    }

    /** Get the parameter name value pairs for an execution. NOTE: Parameter
     *  values may change during an execution; this method returns the
     *  values at the <b>start</b> of the execution.
     */
    @Override
    public Map<String,String> getParameterNameValuesForExecution(int execId)
        throws QueryException
    {
        try
        {
            _psParamNameValuesForExecution.setInt(1, execId);
            return _getParameterNameValues(_psParamNameValuesForExecution);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to get name value pairs: ", e);
        }
    }

    /** Get the latest value of a parameter. 
     *  @param parameter the parameter name.
     *  @param lsid the workflow lsid
     *  @return the latest value of the parameter. If the workflow LSID or
     *  parameter name cannot be found, returns null.
     */
    @Override
    public String getParameterValueLatest(String parameter, KeplerLSID lsid)
        throws QueryException
    {
        return getParameterValueAtTime(new Date(Long.MAX_VALUE), parameter,
            lsid);
    }

  
    /** Get the value of a parameter at a specific time. 
     * @param timestamp the time
     * @param parameter the parameter name
     * @param lsid the workflow lsid
     * @return the value of the parameter at the specified time. If the
     * workflow LSID or parameter name cannot be found, returns null.
     */
    @Override
    public String getParameterValueAtTime(Date timestamp, String parameter,
        KeplerLSID lsid) throws QueryException
    {
        try
        {
            String paramName = _getEntityName(parameter);
            
            //System.out.println("look for parameter name = " + paramName);

            _psParamValueAtTimeForWorkflowLSID.setString(1, paramName);
            _psParamValueAtTimeForWorkflowLSID.setString(2, lsid.toStringWithoutRevision());
            _psParamValueAtTimeForWorkflowLSID.setTimestamp(3,
                new Timestamp(timestamp.getTime()));
            
            //System.out.println("query " + _psParamValueAtTimeForWorkflowLSID);

            ResultSet result = null;
            
            try
            {
                result = _psParamValueAtTimeForWorkflowLSID.executeQuery();
                if(result.next())
                {
                    return result.getString("value");
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying parameter value: ", e); 
        }

        return null;
    }

    /** Given a fully-qualified entity name, return the name as it would
     *  appear in the entity table.
     */
    protected String _getEntityName(String name)
    {
        int index = name.indexOf('.', 1);
        if(index > -1)
        {
            return name.substring(index);
        }
        return "";
    }
    
    /** Given a name from the entity table, return the fully-qualified name. */
    protected String _getFullNameFromEntityName(String entity, String full)
    {
        int index = full.indexOf('.', 1);
        if(index == -1)
        {
            return full + entity;
        }
        else
        {
            return full.substring(0, index) + entity;
        }
    }
 
    /** Get the immediate tokens that generated a token. */
    @Override
    public List<Integer> getImmediateDependencies(int tokenId)
        throws QueryException
    {
        // get the firing id that generated this token
        List<Integer> firingsList = getActorFiringForToken(tokenId, false);
        int fireId;

        if(firingsList.size() == 0)
        {
            throw new QueryException("No firing generated token " + tokenId +
                ".");
        }
        else if(firingsList.size() == 1)
        {
            fireId = firingsList.get(0);    
        }
        else // firingsList.size() > 1
        {
            throw new QueryException("Multiple firings produced token " +
                tokenId + ".");
        }

        // get the tokens read for that firing id
        return getTokensForFiring(fireId, true);
    }

    /** Get a file associated with an execution. */
    @Override
    public List<byte[]> getAssociatedDataForExecution(int execId,
        Map<String,String> metadataMap, boolean matchAny)
        throws QueryException
    {
        List<byte[]> retList = new ArrayList<byte[]>();

        try
        {    
            //construct the SQL string
            String data = _dbType.getTableName("data");
            String assocDataStr = _dbType.getTableName("associated_data");
                StringBuffer queryBuf = new StringBuffer(
                    // XXX can't distinct() on blob in oracle
                    "SELECT data.contents " +
                "FROM " 
                    + assocDataStr + " ad, " 
                    + data + " data " +
                "WHERE data.md5 = ad.data_id AND ad.wf_exec_id = ? ");
            
            String operator = "AND";
            if (matchAny) 
            {
                operator = "OR";
            }
            
            for(int i = 0; i < metadataMap.size(); i++) 
            {
                queryBuf.append(operator);
                queryBuf.append(" (name = ? AND val = ?)");
            }

            try(PreparedStatement psAssociatedData = 
                    _dbType.getConnection().prepareStatement(queryBuf.toString()))
            {
                psAssociatedData.setInt(1, execId);
                int paramCounter = 2;
                
                for(Map.Entry<String,String> entry : metadataMap.entrySet()) 
                {
                    //select distinct(file) where (name = ? and value = ?) or (name = ? and value = ?) or (name = ? and value = ?)
                    psAssociatedData.setString(paramCounter++, entry.getKey());
                    psAssociatedData.setString(paramCounter++, entry.getValue());
                }
                
                //execute
                try(ResultSet result = psAssociatedData.executeQuery())
                {
                    while (result.next())
                    {
                        retList.add(result.getBytes(1));
                    }
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying associated data: ", e);
        }
        return retList;
    }
   
    /** Get any associated keys-values for an execution. */
    @Override
    public Map<String,String> getAssociatedKeysValuesForExecution(int execId)
        throws QueryException {
     
        Map<String,String> retval = new HashMap<String,String>();

        synchronized(_psAssociatedKeysValuesForExecution) {
            try {
                _psAssociatedKeysValuesForExecution.setInt(1, execId);
                
                try(ResultSet rs = _psAssociatedKeysValuesForExecution.executeQuery()) {
                    while(rs.next()) {
                        retval.put(rs.getString(1), rs.getString(2));
                    }                    
                }
                
            } catch(SQLException e) {
                throw new QueryException("Error querying keys values.", e);
            }
        }
        
        return retval;
        
    }

    /** Get a list of files saved in the database. */
    public List<String> getSavedFilenames() throws QueryException
    {
        List<String> retval = new LinkedList<String>();
        
        Statement statement = null;
        ResultSet result = null;
        try
        {
            try
            {
                statement = _dbType.getStatement();
                // XXX change to prepared statement
                result = statement.executeQuery("select contents from data " +
                    "where md5 in (select data_id from port_event where " +
                    "file_id != '')");
                while(result.next())
                {
                    retval.add(new String(result.getBytes(1)));
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
                
                if(statement != null)
                {
                    statement.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying filenames: ", e);
        }
        
        return retval;
    }
    
    /** Get the ports for an actor.
     *  @param workflow the LSID of the workflow
     *  @param actor the fully-qualified name of the actor
     *  @return a list of fully-qualified names of ports of the actor
     */
    @Override
    public List<String> getPortsForActor(KeplerLSID workflow, String actor)
        throws QueryException
    {        
        try
        {
            _psPortsForActor.setString(1, workflow.toStringWithoutRevision());
            // the names of the ports are prefixed with the actor name and .
            _psPortsForActor.setString(2, _getEntityName(actor) + ".%");

            //System.out.println("actor = " + _getEntityName(actor));
            
            ResultSet result = null;
            try
            {
                List<String> retval = new LinkedList<String>();
                result = _psPortsForActor.executeQuery();
                while(result.next())
                {
                    String name = result.getString(1);
                    //System.out.println("name = " + name);
                    String fullName = _getFullNameFromEntityName(name, actor); 
                    retval.add(fullName);
                }
                return retval;
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying ports for actor: ", e);
        }
    }

    /** Get the host id for a specific execution id. If no execution
     *  exists, returns null.
     */
    @Override
    public String getHostIdForExecution(Integer execId) throws QueryException 
    {
        try
        {
            _psHostIdForExecution.setInt(1, execId);
            ResultSet result = null;
            try
            {
                result = _psHostIdForExecution.executeQuery();
                if(!result.next())
                {
                    return null;
                }
                return result.getString(1);
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query host id for " +
                "execution id " + execId + ": ", e);
        }
    }    
    
    /** Get the user for a specific execution LSID. */
    @Override
    public String getUserForExecution(KeplerLSID execLSID) throws QueryException {
        
        synchronized(_psUserForExecutionLSID) {
            try {
                _psUserForExecutionLSID.setString(1, execLSID.toString());
                try(ResultSet result = _psUserForExecutionLSID.executeQuery()) {
                    if(result.next()) {
                        return result.getString(1);
                    }
                }
            } catch(SQLException e) {
                throw new QueryException("Unable to query user for " +
                    "execution lsid " + execLSID + ": ", e);
            }
        }
        return null;
    }
    
    /** Get the user for a specific execution id. If no execution
     *  exists, returns null.
     */
    @Override
    public String getUserForExecution(Integer execId) throws QueryException 
    {
        try
        {
            _psUserForExecution.setInt(1, execId);
            ResultSet result = null;
            try
            {
                result = _psUserForExecution.executeQuery();
                if(!result.next())
                {
                    return null;
                }
                return result.getString(1);
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query user for " +
                "execution id " + execId + ": ", e);
        }
    } 
    
    
    /** Get the output role (port name) of a token. */
    @Override
    public String getOutputRoleToken(int tokenId) throws QueryException
    {
        synchronized(_psOutputRoleToken) {
            try
            {
                _psOutputRoleToken.setInt(1, tokenId);
                try(ResultSet result = _psOutputRoleToken.executeQuery();) {
                    if(! result.next())
                    {
                        throw new QueryException("Token not found: " + tokenId);                
                    }
                    return result.getString(1);
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to query output role: ", e);
            }
        }
    }    
    
    
    /** Get the input role (port name) of a token. */
    @Override
    public String getInputRoleForTokenAndFireId(int tokenId, int fireId) throws QueryException
    {
        synchronized(_psInputRoleToken) {
            try
            {
                _psInputRoleToken.setInt(1, tokenId);
                _psInputRoleToken.setInt(2, fireId);
    
                try(ResultSet result = _psInputRoleToken.executeQuery();) { 
                    if(! result.next())
                    {
                        throw new QueryException("Token not found: " + tokenId);                
                    }
                    return result.getString(1);
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Unable to query input role: ", e);
            }
        }
    }    
    
    /** Get the parameter name value pairs for an execution, whose parameter type is
     * either '.Parameter' or '.StringParameter' 
     */
    @Override
    public Map<String,String> getParameterNameValuesOfSpecificTypeForExecution(int execId)
        throws QueryException
    {
        try
        {
            _psParamNameValuesOfSpecificTypeForExecution.setInt(1, execId);
            return _getParameterNameValues(_psParamNameValuesOfSpecificTypeForExecution);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to get name value pairs: ", e);
        }
    }   
    
    /** Get the start and end timestamps of an actor firing. */
    @Override
    public Date[] getTimestampsForActorFiring(int fireId)
        throws QueryException
    {
        synchronized(_psTimestampsForActorFiring) {
            try
            {
                _psTimestampsForActorFiring.setInt(1, fireId);
                try(ResultSet result = _psTimestampsForActorFiring.executeQuery();)
                {
                    if(result.next())
                    {
                        Date[] retval = new Date[2];
                        retval[0] = result.getTimestamp("start_time");
                        retval[1] = result.getTimestamp("end_time");
                        return retval;
                    }
                }
            }
            catch(SQLException e)
            {
                throw new QueryException("Error querying timestamps for actor firing:", e);
            }
        }
        return null;
    }
    
    /** Get actor fire ids based on its full name and workflow exec ID. */
    @Override
    public List<Integer> getActorFiringIds(String actorName, Integer wfExecId)
        throws QueryException
    {
        try
        {
                _psActorFireId.setInt(1, wfExecId);
                _psActorFireId.setString(2, _getEntityName(actorName));
                return _getIntResults(_psActorFireId, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying timestamps for actor firing:", e);
        }
    } 
    
    /** Get an actor's parameter and port parameter name value pairs for a firing id.
     *  NOTE: Parameter values may change during a firing; this method
     *  returns the values at the <b>start</b> of the firing. 
     */
    @Override
    public Map<String,String> getParameterAndPortParameterNameValuesForFiring(int fireId)
        throws QueryException
    {
        try
        {
            String actorName = getActorName(fireId);
            _psParamAndPortParamNameValuesForFiring.setString(1, actorName + ".%");
            _psParamAndPortParamNameValuesForFiring.setInt(2, fireId);
            Map<String,String> nameValuesMap = _getParameterNameValues(_psParamAndPortParamNameValuesForFiring);
            
            // use a different map to remove components (see below) to avoid
            // ConcurrentModificationExceptions
            Map<String,String> retval = new LinkedHashMap<String,String>(nameValuesMap);
            
            // remove the components that are not direct parameters
            // e.g., parameters of the actors ports.
            for(String name : nameValuesMap.keySet())
            {
                if(name.lastIndexOf('.') > actorName.length())
                {
                    retval.remove(name);
                }
            }
            
            return retval;
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to get name value pairs: ", e);
        }
    }    

    /** Get all actor fire ids for a workflow exec ID. */
    @Override
    public List<Integer> getActorFirings(int wfExecId) throws QueryException {
        
        synchronized(_psActorFirings) {
            try {
                _psActorFirings.setInt(1, wfExecId);
                return _getIntResults(_psActorFirings, 1);
            } catch(SQLException e) {
                throw new QueryException("Error getting actor firings: ", e);
            }
        }
    }
        
    /** Get the execution times for an actor.
    *
    * @param workflow the workflow LSID. If null, must specify execution id.
    * @param execLSID the execution LSID. If null, the last execution of the workflow
    * is used.
    * @param actor the full actor name.
    * @return a map of actor fire id to execution time pairs. if a firing does not
    * have a valid start or stop time, the firing is not included in the result.
    */
    @Override
    public Map<String,Long> getExecutionTimesForActor(KeplerLSID workflow,
        KeplerLSID execLSID, String actor) throws QueryException
    {
        int runId;
        
        // see if the execution id is specified
        if(execLSID == null)
        {
            runId = _getLastExecutionId(workflow);
        }
        else
        {
            runId = getExecutionForExecutionLSID(execLSID);
        }
        
        try
        {
            //System.out.println(actor + " --> " + _getEntityName(actor));
            
            _psActorExecutionTimes.setString(1, _getEntityName(actor));
            _psActorExecutionTimes.setInt(2, runId);
            
            //System.out.println(_psActorExecutionTimes);
            
            // use a LinkedHashMap for predictable iteration order.
            Map<String,Long> retval = new LinkedHashMap<String,Long>();
    
            try(ResultSet result = _psActorExecutionTimes.executeQuery();)
            {    
                while(result.next())
                {
                    Timestamp start = result.getTimestamp(2);
                    long startVal;
                    if(start == null) {
                        System.err.println("WARNING: missing start time for " + actor);
                        continue;
                    } else {
                        startVal = start.getTime();
                    }
                    
                    Timestamp end = result.getTimestamp(3);
                    long endVal;
                    if(end == null) {
                        System.err.println("WARNING: missing stop time for " + actor);
                        continue;
                    } else {
                        endVal = end.getTime();
                    }
                    
                    long duration = endVal - startVal;
                    
                    retval.put(result.getString(1), duration);
                    //System.out.println("fire time for " + actor + " id " + result.getString(1) + " duration " + duration);
                }
                return retval;
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying actor execution times: ", e);
        }
    }
        
    /** Get the number of bytes read or written by a port.
    *
    *  @param workflow the workflow LSID. If null, must specify execution id.
    *  @param execId the execution id. If null, the last execution of the workflow
    *  is used.
    *  @param port the full port name.
    *  @param read If true, get the bytes read. Otherwise, get the bytes written.
    *  @return a map of port event id to bytes read/written.
    */
    @Override
    public Map<Integer,Integer> getIOBytesForPort(KeplerLSID workflow, Integer execId,
        String port, boolean read) throws QueryException
    {
        
        // FIXME queries need updating after addition of port_event.data
        //
        
        if(execId == null)
        {
            execId = _getLastExecutionId(workflow);
        }
        
        try
        {    
            PreparedStatement ps;
            
            if(read)
            {
                ps = _psPortIOBytesRead;
            }
            else
            {
                ps = _psPortIOBytesWrite;
            }
            
            ps.setString(1, _getEntityName(port));
            ps.setInt(2, execId);
            
            Map<Integer,Integer> retval = new HashMap<Integer,Integer>();

            try(ResultSet result = ps.executeQuery();)
            {
                while(result.next())
                {
                    int id = result.getInt(1);
                    byte[] data = result.getBytes(2);
                    retval.put(id, data.length);
                }
                
                return retval;
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying port IO bytes: ", e);
        }
    }
  
    /*
    @Override
    public List<LinkIO> getLinksIO(KeplerLSID workflow, Integer execId) throws QueryException
    {
        if(execId == null)
        {
            execId = _getLastExecutionId(workflow);
        }
        
        try
        {
            _psIOLinkRead.setInt(1, execId);

            Map<Integer, LinkIO> map = new HashMap<Integer, LinkIO>();
            
            try(ResultSet result = _psIOLinkRead.executeQuery();)
            {
                //System.out.println(_psIOLinkRead);
                
                while(result.next())
                {
                    // reading actor name, id of port of event of writer, data
                    LinkIO io = new LinkIO();
                    io.dest = result.getString(1);
                    io.source_id = result.getInt(2);
                    io.data = result.getBytes(3);
                    map.put(io.source_id, io);
                    //System.out.println(io);
                }
                         
            }
            
            Statement sIOLinkWrite = _dbType.getStatement();
            String actorFireStr = _dbType.getTableName("actor_fire");
            String entityStr =  _dbType.getTableName("entity");
            String portEventStr = _dbType.getTableName("port_event");
  
            StringBuilder queryStr = new StringBuilder("SELECT ent.name, pe.id " +
                "FROM " + portEventStr + " pe, " + actorFireStr + " af, " +
                entityStr + " ent " + 
                "WHERE pe.id IN (");
            
            int i = 0;
            for(LinkIO io : map.values())
            {
                queryStr.append(" " + io.source_id);
                if(i < map.size() - 1)
                {
                    queryStr.append(", ");
                }
                i++;
            }
            
            queryStr.append(" ) AND pe.fire_id = af.id AND af.actor_id = ent.id");
            
            //System.out.println(queryStr);
                
            try(ResultSet result = sIOLinkWrite.executeQuery(queryStr.toString());)
            {
                while(result.next())
                {
                    int portEventId = result.getInt(2);
                    LinkIO io = map.get(portEventId);
                    io.source = result.getString(1);
                }
                
                sIOLinkWrite.close();
                return new LinkedList<LinkIO>(map.values());
                
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying link IO: ", e);
        }
    }
    */
    
    /** Get the timestamp(s) when a token was read. If a token was
     *  never read, this can return null or an empty array.
     */
    @Override
    public Date[] getTimestampsForTokenRead(Integer tokenId) throws QueryException {
        synchronized(_psTimestampsForTokenRead) {
            try {
                _psTimestampsForTokenRead.setInt(1, tokenId);
                try(ResultSet result = _psTimestampsForTokenRead.executeQuery();) {
                    List<Date> timestamps = new ArrayList<Date>();
                    while(result.next()) {
                        timestamps.add(result.getTimestamp(1));
                    }
                    return timestamps.toArray(new Date[timestamps.size()]);
                }
            }
            catch(SQLException e) {
                throw new QueryException("Error querying timestamps for token read.", e);
            }
        }
    }

    /** Get the timestamp when a token was written. */
    @Override
    public Date getTimestampForTokenWrite(Integer tokenId) throws QueryException {
        synchronized(_psTimestampForTokenWrite) {
            try {
                _psTimestampForTokenWrite.setInt(1, tokenId);
                try(ResultSet result = _psTimestampForTokenWrite.executeQuery();) {
                    if(result.next()) {
                        return result.getTimestamp(1);
                    }
                }
            }
            catch(SQLException e) {
                throw new QueryException("Error querying timestamps for token write.", e);
            }
        }
        return null;
    }

    ///////////////////////////////////////////////////////////////////////
    //// protected methods                                             ////

    /** Construct a new SQLQueryV8 from a connected DatabaseType. */
    protected SQLQueryV8(DatabaseType dbType) throws QueryException
    {
        _dbType = dbType;

        try
        {
            _createPreparedStatements();
        }
        catch(SQLException e)
        {
            throw new QueryException("Error creating prepared statements: ", e);
        }
    }

    /** Check the schema version and initialize the prepared statements. */
    protected void _init()
        throws QueryException
    {
        try
        {
            Schema schema = Schemas.createSchemaV8();
            Schemas.checkVersion(_dbType, schema);
            _createPreparedStatements();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            throw new QueryException("Unable to connect or initialize " +
                "database connection: ", e);
        }
    }

    /** Initialize the prepared statements. */
    protected void _createPreparedStatements() throws SQLException
    {
        // get the table table names
        String actorFireStr = _dbType.getTableName("actor_fire");
        String actorStr =  _dbType.getTableName("actor");
        String associatedDataStr = _dbType.getTableName("associated_data");
        String dataStr = _dbType.getTableName("data");
        String entityStr =  _dbType.getTableName("entity");
        String errorStr = _dbType.getTableName("error");
        String parameterStr = _dbType.getTableName("parameter");
        String parameterExecStr = _dbType.getTableName("parameter_exec");
        String portEventStr = _dbType.getTableName("port_event");
        String wfChangeStr = _dbType.getTableName("workflow_change");
        String wfExecStr = _dbType.getTableName("workflow_exec");
        String wfStr = _dbType.getTableName("workflow");
        String tagStr = _dbType.getTableName("tag");
        
        // NOTE: user is a reserved column name in oracle
        String userColumnStr = _dbType.getColumnName("user");


        if(_psWorkflows == null)
        {
            _psWorkflows = _dbType.getSQLSelect("workflow", "*");
        }
        
        if(_psWorkflowNameForLSID == null)
        {
            _psWorkflowNameForLSID = _dbType.getSQLSelect("workflow", "name",
                "lsid = ?");
        }
        
        if(_psWorkflowNameForExecutionLSID == null) {
            _psWorkflowNameForExecutionLSID = _dbType.getPrepStatement(
                "SELECT wf.name " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wfExec.wf_id = wf.id AND wfexec.lsid = ?");
        }

        if(_psExecutions == null)
        {
            _psExecutions = _dbType.getSQLSelect("workflow_exec", "id"); 
        }

        if(_psExecutionsForTimespan == null)
        {
            _psExecutionsForTimespan = _dbType.getSQLSelect("workflow_exec",
                "id", "start_time >= ? AND end_time <= ?");
        }
        
        if(_psExecutionsForTimespanOnHost == null)
        {
            _psExecutionsForTimespanOnHost = _dbType.getPrepStatement(
                "SELECT wfexec.id " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wfexec.start_time >= ? AND wfexec.end_time <= ? AND " +
                "wfexec.host_id = ? AND wf.id = wfexec.wf_id AND wf.name = ?");
        }
        
        if(_psTimestampsForExecution == null)
        {
            _psTimestampsForExecution = _dbType.getSQLSelect("workflow_exec",
                    "start_time, end_time", "id = ?");
        }
        
        if(_psEntitiesForChange == null)
        {
            _psEntitiesForChange = _dbType.getSQLSelect("entity", "id",
                "wf_change_id = ?");
        }

        if(_psChangesForTimespan == null)
        {
            _psChangesForTimespan = _dbType.getSQLSelect("workflow_change",
                "id", "time >= ? AND time <= ?");
        }
        
        if(_psChangesForTimespanOnHost == null)
        {
            _psChangesForTimespanOnHost = _dbType.getPrepStatement(
                "SELECT id " +
                "FROM " + wfStr + " wf, " + wfChangeStr + " wfchange " +
                "WHERE wfchange.time >= ? AND wfchange.time <= ? AND " +
                "wfchange.host_id = ? AND wf.id = wfchange.wf_id AND " +
                "wf.name = ?");
        }
        
        if(_psExecutionsForWorkflow == null)
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wf.id = wfexec.wf_id AND wf.name = ?";
            _psExecutionsForWorkflow = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionForExecutionLSID == null)
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfExecStr + " wfexec " +
                "WHERE wfexec.lsid = ?";
            _psExecutionForExecutionLSID = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionForExecutionLSIDWithoutRevision == null)
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfExecStr + " wfexec " +
                "WHERE wfexec.lsid LIKE ?";
            _psExecutionForExecutionLSIDWithoutRevision = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionForOldestReferralExecutionLSIDWithoutRevision == null)
        {
                 String queryStr = "SELECT wfexec.id " +
             "FROM " + wfExecStr + " wfexec " +
             "WHERE wfexec.derived_from LIKE ?";
                 _psExecutionForOldestReferralExecutionLSIDWithoutRevision = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionLSIDForExecution == null)
        {
            String queryStr = "SELECT wfexec.lsid " +
                "FROM " + wfExecStr + " wfexec " +
                "WHERE wfexec.id = ?";
            _psExecutionLSIDForExecution = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionsForWorkflowNameContains == null)
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wf.id = wfexec.wf_id AND LOWER(wf.name) LIKE ?";
            _psExecutionsForWorkflowNameContains = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionsForWorkflowRuns == null)
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wf.id = wfexec.wf_id AND LOWER(wf.name) LIKE ? " +
                "AND LOWER(wfexec." + userColumnStr + ") LIKE ?";
            _psExecutionsForWorkflowRuns = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionsForWorkflowRunsAfter == null)
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wf.id = wfexec.wf_id AND LOWER(wf.name) LIKE ? " +
                "AND LOWER(wfexec. " + userColumnStr + ") LIKE ? " +
                "AND wfexec.start_time > ?";
            _psExecutionsForWorkflowRunsAfter = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionsForWorkflowRunsAt == null)
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wf.id = wfexec.wf_id AND LOWER(wf.name) LIKE ? " +
                "AND LOWER(wfexec." + userColumnStr + ") LIKE ? " +
                "AND wfexec.start_time LIKE ?"; //TODO implement.
            _psExecutionsForWorkflowRunsAt = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionsForWorkflowRunsBefore == null)
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wf.id = wfexec.wf_id AND LOWER(wf.name) LIKE ? " +
                "AND LOWER(wfexec." + userColumnStr + ") LIKE ? " +
                "AND wfexec.start_time < ?";
            _psExecutionsForWorkflowRunsBefore = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionsForWorkflowRuns2 == null) //inclusive
        {
            String queryStr = "SELECT wfexec.id " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wf.id = wfexec.wf_id " +
                "AND LOWER(wf.name) LIKE ? " +
                "AND LOWER(wfexec." + userColumnStr + ") LIKE ? " +
                "AND wfexec.start_time >= ? " +
                "AND wfexec.start_time <= ? " + 
                "AND wfexec.id >= ? " +
                "AND wfexec.id <= ?";
            _psExecutionsForWorkflowRuns2 = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionLSIDsForWorkflowRuns == null) //inclusive
        {
            String queryStr = "SELECT wfexec.lsid " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfexec " +
                "WHERE wf.id = wfexec.wf_id " +
                "AND LOWER(wf.name) LIKE ? " +
                "AND LOWER(wfexec." + userColumnStr + ") LIKE ? " +
                "AND wfexec.start_time >= ? " +
                "AND wfexec.start_time <= ? " + 
                "AND wfexec.id >= ? " +
                "AND wfexec.id <= ?";
            _psExecutionLSIDsForWorkflowRuns = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionIdsForTags == null)
        {
            String queryStr = "SELECT tag.wf_exec_id " +
                "FROM " + tagStr + " tag " +
                "WHERE LOWER(tag.searchstring) LIKE ?";
            _psExecutionIdsForTags = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionLSIDs == null)
        {
            _psExecutionLSIDs = _dbType.getPrepStatement("SELECT lsid " +
                "FROM " + wfExecStr +
                " ORDER BY lsid ASC");
            
            //_psExecutionLSIDs = _dbType.getSQLSelect("workflow_exec", "lsid");
        }
        
        if(_psTagsForExecutionId == null)
        {
            String queryStr = "SELECT tag.searchstring " +
                "FROM " + tagStr + " tag " +
                "WHERE tag.wf_exec_id = ?";
            _psTagsForExecutionId = _dbType.getPrepStatement(queryStr);
        }

        if (_psTagURNsForExecutionId == null) {
            String queryStr = "SELECT tag.type, tag.urn FROM " + tagStr + " tag WHERE tag.wf_exec_id = ?";
            _psTagURNsForExecutionId = _dbType.getPrepStatement(queryStr);
        }
        
        if (_psTypeForTag == null) {
            String queryStr = "SELECT tag.type FROM " + tagStr + " tag WHERE tag.urn = ?";
            _psTypeForTag = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionsForAnnotation == null)
        {
            _psExecutionsForAnnotation = _dbType.getSQLSelect("workflow_exec",
                "id", "annotation = ?");
        }

        // read port events have write_id = id of port event that generated
        // the token, i.e. != -1.
        if(_psTokensReadForFiring == null)
        {
            _psTokensReadForFiring = _dbType.getSQLSelect("port_event",
                "write_event_id", "write_event_id != -1 AND fire_id = ?");
        }

        // write port events have write_id = -1
        if(_psTokensWrittenForFiring == null)
        {
            _psTokensWrittenForFiring = _dbType.getSQLSelect("port_event",
                "id", "write_event_id = -1 AND fire_id = ?");
        }
        
        if(_psActorNameForFiring == null)
        {
            String queryStr = "SELECT e.name " +
                "FROM " + actorFireStr + " af, " + entityStr + " e " +
                "WHERE af.actor_id = e.id AND af.id = ?";
            _psActorNameForFiring = _dbType.getPrepStatement(queryStr);
        }

        if(_psActorTypeForFiring == null)
        {
            String queryStr = "SELECT a.class " +
                "FROM " + actorFireStr + " af, " + actorStr + " a " +
                "WHERE af.actor_id = a.id AND af.id = ?";
            _psActorTypeForFiring = _dbType.getPrepStatement(queryStr);
        }

        if(_psParamNameValuesForFiring == null)
        {
            String queryStr = "SELECT name, value " +
                "FROM " + entityStr + " e, " + parameterStr + " p, " +
                parameterExecStr + " pe, " + actorFireStr + " af " +
                "WHERE e.type = 'parameter' AND e.id = p.id AND " +
                "e.name LIKE ? AND e.id = pe.parameter_id AND pe.wf_exec_id = af.wf_exec_id AND af.id = ?";
            _psParamNameValuesForFiring =
                _dbType.getPrepStatement(queryStr);
        }
        
        if(_psParamNameValuesForExecution == null)
        {
            String queryStr = "SELECT name, value " +
                "FROM " + entityStr + " e, " + parameterStr + " p, " +
                parameterExecStr + " pe " + 
                "WHERE pe.wf_exec_id = ? AND pe.parameter_id = e.id AND " +
                "pe.parameter_id = p.id";
            _psParamNameValuesForExecution =
                _dbType.getPrepStatement(queryStr);
        }

        if(_psParamValueAtTimeForWorkflowLSID == null)
        {
            String queryStr = 
                "SELECT p.value, chg.time " +
                "FROM " + entityStr + " e, " + wfChangeStr + " chg, " +
                wfStr + " wf, " + parameterStr + " p " +
                "WHERE p.id = e.id AND e.name = ? AND " +
                "e.wf_change_id = chg.id AND wf.lsid = ? AND " +
                "wf.id = chg.wf_id AND chg.time <= ? " +
                "ORDER BY chg.time DESC";
            _psParamValueAtTimeForWorkflowLSID = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psTokensForExecution == null)
        {
            // this returns all the tokens written for an execution.
            String queryStr = "SELECT DISTINCT(pe.write_event_id) " +
                "FROM " + portEventStr + " pe, " + actorFireStr + " af " +
                "WHERE pe.write_event_id != -1 AND pe.fire_id = af.id AND " +
                "af.wf_exec_id = ?";
            _psTokensForExecution = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psTokensForFiring == null)
        {
            _psTokensForFiring = _dbType.getSQLSelect("port_event", "id",
                "fire_id = ?");
        }
        
        if(_psEntityTokensForExecution == null)
        {
            String readQueryStr = "SELECT pe.write_event_id " +
                "FROM " + portEventStr + " pe, " + actorFireStr + " af " +
                "WHERE pe.write_event_id != -1 AND pe.fire_id = af.id AND " +
                "af.wf_exec_id = ? and pe.port_id = ?";
            String writeQueryStr = "SELECT pe.id " +
                "FROM " + portEventStr + " pe, " + actorFireStr + " af " +
                "WHERE pe.write_event_id = -1 AND pe.fire_id = af.id AND " +
                "af.wf_exec_id = ? and pe.port_id = ?";
            _psEntityTokensForExecution = _dbType.getPrepStatement(readQueryStr + " UNION ALL " + writeQueryStr);
        }

        if(_psLastExecutionForWorkflow == null)
        {
            String queryStr = "SELECT MAX(wfe.id) " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfe " +
                "WHERE wf.id = wfe.wf_id AND wf.name = ?";
            _psLastExecutionForWorkflow = _dbType.getPrepStatement(queryStr);
        }

        if(_psLastExecutionForWorkflowLSID == null)
        {
            String queryStr = "SELECT MAX(wfe.id) " +
                "FROM " + wfStr + " wf, " + wfExecStr + " wfe " +
                "WHERE wf.id = wfe.wf_id AND wf.lsid = ?";
            _psLastExecutionForWorkflowLSID = _dbType.getPrepStatement(queryStr);
        }

        if(_psLastExecutionLSIDForWorkflowName == null)
        {
            String queryStr = "SELECT wfe.lsid " +
             "FROM " + wfStr + " wf, " + wfExecStr + " wfe " +
             "WHERE wf.id = wfe.wf_id and wf.name = ? " +
             "ORDER BY wfe.id DESC";
            _psLastExecutionLSIDForWorkflowName = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psLastExecutionLSIDForWorkflowLSID == null)
        {
            String queryStr = "SELECT wfe.lsid " +
             "FROM " + wfStr + " wf, " + wfExecStr + " wfe " +
             "WHERE wf.id = wfe.wf_id and wf.lsid = ? " +
             "ORDER BY wfe.id DESC";
            _psLastExecutionLSIDForWorkflowLSID = _dbType.getPrepStatement(queryStr);
        }

        if(_psLastExecutionStartTime == null)
        {
            _psLastExecutionStartTime = _dbType.getPrepStatement(
                "SELECT MAX(wfe.start_time) " +
                "FROM " + wfExecStr + " wfe, " + wfStr + " wf " +
                "WHERE wf.id = wfe.wf_id AND wf.name = ? AND wfe.host_id = ?");
        }

        if(_psLastExecutionStartTimeForWorkflowId == null)
        {
            _psLastExecutionStartTimeForWorkflowId = 
                _dbType.getSQLSelect("workflow_exec", "MAX(start_time)",
                    "wf_id = ? AND host_id = ?");
        }

        if(_psLastChangeTime == null)
        {
            _psLastChangeTime = _dbType.getPrepStatement(
                "SELECT MAX(wfchange.time) " +
                "FROM " + wfChangeStr + " wfchange, " + wfStr + " wf " +
                "WHERE wf.id = wfchange.wf_id AND wf.name = ? AND " +
                "wfchange.host_id = ?");
        }

        if(_psLastChangeTimeForWorkflowId == null)
        {
            _psLastChangeTimeForWorkflowId = 
                _dbType.getSQLSelect("workflow_change", "MAX(time)",
                "wf_id = ? AND host_id = ?");
        }
        
        if(_psPortEventData == null) {
            
            _psPortEventData = _dbType.getSQLSelect(portEventStr,
                    "data, data_id", "id = ?");            
        }
        
        if(_psDataContents == null)
        {
            _psDataContents = _dbType.getSQLSelect(dataStr, "contents", "md5 = ?");
        }
        
        if(_psTokenType == null)
        {
            _psTokenType = _dbType.getSQLSelect("port_event",
                "type", "id = ?");
        }

        if(_psChannelForTokenRead == null) {
            _psChannelForTokenRead = _dbType.getSQLSelect("port_event",
                    "channel", "write_event_id = ? AND fire_id = ?");
        }

        if(_psChannelForTokenWrite == null) {
            _psChannelForTokenWrite = _dbType.getSQLSelect("port_event",
                    "channel", "id = ?");
        }
        
        if(_psActorFiringsForTokenRead == null)
        {
            _psActorFiringsForTokenRead = 
                _dbType.getSQLSelect("port_event", "fire_id",
                    "write_event_id = ?");
        }
        
        if(_psActorFiringForTokenWrite == null)
        {
            _psActorFiringForTokenWrite = 
                _dbType.getSQLSelect("port_event", "fire_id", "id = ?");
        }

        if(_psActorFiringsForExecution == null)
        {
            _psActorFiringsForExecution = _dbType.getSQLSelect("actor_fire",
                "id", "wf_exec_id = ?");
        }

        if(_psMomlForExecutionId == null)
        {
            String queryStr = "SELECT d.contents " +
                "FROM " + dataStr + " d, " + wfExecStr + " wfe " +
                "WHERE wfe.id = ? AND d.md5 = wfe.wf_contents_id";
            _psMomlForExecutionId = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psMomlForExecutionLSID == null)
        {
            String queryStr = "SELECT d.contents " +
                "FROM " + dataStr + " d, " + wfExecStr + " wfe " +
                "WHERE wfe.lsid = ? AND d.md5 = wfe.wf_contents_id";
            _psMomlForExecutionLSID = _dbType.getPrepStatement(queryStr);
        }

 /* XXX no arrays for hsql 1.7.2
        if(_psMoMLMD5sForExecutions == null)
        {
            _psMoMLMD5sForExecutions = _dbType.getPrepStatement(
                "SELECT d.md5 " +
                "FROM " + dataStr + " d, " + wfExecStr + " wfexec " +
                "WHERE wfexec.wf_contents_id = d.id AND wfexec.id IN ( ? )");
        }
*/
        _psMoMLMD5sForExecutions = _dbType.getStatement();

        //if(_psWorkflowRunsForExecutions == null)
        //{
        //    String queryStr = "SELECT wf.name, wfexec.user, wfexec.id, wfexec.start_time, wfexec.end_time FROM " +
           //        wfStr + " wf INNER JOIN " + wfExecStr + " wfexec " +
           //        "ON wf.id=wfexec.wf_id WHERE wfexec.id in (?)";
        //    _psWorkflowRunsForExecutions = _dbType.getPrepStatement(queryStr);
        //}
        
        //if(_psWorkflowRunsForExecutionLSIDs == null)
        //{
        //    String queryStr = "SELECT wf.name, wfexec.user, wfexec.id, wfexec.start_time, wfexec.end_time FROM " +
           //        wfStr + " wf INNER JOIN " + wfExecStr + " wfexec " +
           //        "ON wf.id=wfexec.wf_id WHERE wfexec.lsid in (?)";
        //    _psWorkflowRunsForExecutionLSIDs = _dbType.getPrepStatement(queryStr);
        //}

        if(_psDataMD5s == null)
        {
            _psDataMD5s = _dbType.getSQLSelect("data", "md5");
        }
        
        if(_psEntityId == null)
        {
            String queryStr = "SELECT e.id FROM "
                + entityStr + " e, " 
                + wfStr + " w "
                + "WHERE e.wf_id = w.id "
                + "AND deleted = 0 "
                + "AND e.name = ? "
                + "AND w.name = ?";
            _psEntityId = _dbType.getPrepStatement(queryStr);
        }

        if(_psEntityIdLSID == null)
        {
            String queryStr = "SELECT e.id FROM "
                + entityStr + " e, "
                + wfStr + " w "
                + "WHERE e.wf_id = w.id "
                + "AND deleted = " + _dbType.getFalseValue()
                + " AND e.name = ? "
                + "AND w.lsid = ?";
            _psEntityIdLSID = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psEntityType == null)
        {
            String queryStr = "SELECT e.type FROM "
                + entityStr + " e " 
                + "WHERE e.id = ? "
                + "AND deleted = " + _dbType.getFalseValue();
            _psEntityType = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psEntityWorkflowId == null)
        {
            String queryStr = "SELECT e.wf_id FROM "
                + entityStr + " e " 
                + "WHERE e.id = ? ";
            _psEntityWorkflowId = _dbType.getPrepStatement(queryStr);
        }

        if(_psErrorForExecution == null)
        {
            String queryStr = "SELECT e.message FROM " +
                errorStr + " e, " +
                wfExecStr + " wfe " +
                "WHERE e.exec_id = wfe.id AND " +
                "wfe.lsid = ?";
            _psErrorForExecution = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psExecutionsForType == null)
        {
            _psExecutionsForType = _dbType.getSQLSelect("workflow_exec", "lsid", "type = ?");
        }

        if(_psDoesExecutionHaveError == null)
        {
            String queryStr = "SELECT e.id FROM " +
                errorStr + " e, " +
                wfExecStr + " wfe " +
                "WHERE e.exec_id = wfe.id AND " +
                "wfe.lsid = ?";
            _psDoesExecutionHaveError = _dbType.getPrepStatement(queryStr);
        }

        if(_psIsExecutionImported == null)
        {
            String queryStr = "SELECT pe.parameter_id FROM " +
                parameterExecStr + " pe, " +
                wfExecStr + " wfe " +
                "WHERE pe.wf_exec_id = wfe.id AND " +
                "wfe.lsid = ?";
            _psIsExecutionImported = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psPortsForActor == null)
        {
            String queryStr = "SELECT DISTINCT ent.name " +
                "FROM " + entityStr + " ent, " + wfStr + " wf " +
                "WHERE wf.lsid = ? AND ent.wf_id = wf.id AND " +
                "(ent.type = 'port' OR ent.type = 'portparameter') AND " +
                "ent.name LIKE ? ORDER BY ent.name ASC";
            _psPortsForActor = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psHostIdForExecution == null)
        {
            String queryStr = "SELECT wfexec.host_id " +
                "FROM " + wfExecStr + " wfexec " +
                "WHERE wfexec.id = ?";
            
            //System.out.println("queryStr= "+queryStr);
            _psHostIdForExecution = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psUserForExecution == null)
        {
            String queryStr = "SELECT wfexec." + userColumnStr +
                " FROM " + wfExecStr + " wfexec " +
                "WHERE wfexec.id = ?";
            
            //System.out.println("queryStr= "+queryStr);
            _psUserForExecution = _dbType.getPrepStatement(queryStr);
        } 
        
        if(_psUserForExecutionLSID == null) {
            String queryStr = "SELECT wfexec." + userColumnStr +
                    " FROM " + wfExecStr + " wfexec " +
                    "WHERE wfexec.lsid = ?";
                
            _psUserForExecutionLSID = _dbType.getPrepStatement(queryStr);            
        }
        
        if(_psOutputRoleToken == null)
        {
            String queryStr = "SELECT e.name " +
                "FROM " + entityStr + " e, " + portEventStr + " pe " +
                "WHERE e.id = pe.port_id AND pe.id = ?";
            _psOutputRoleToken = _dbType.getPrepStatement(queryStr);
        }   
        
        if(_psInputRoleToken == null)
        {
            String queryStr = "SELECT e.name " +
                "FROM " + entityStr + " e, " + portEventStr + " pe " +
                "WHERE e.id = pe.port_id AND pe.write_event_id = ? AND " +
                "pe.fire_id = ?";
            _psInputRoleToken = _dbType.getPrepStatement(queryStr);
        }    
        
        if(_psParamNameValuesOfSpecificTypeForExecution == null)
        {
                String queryStr = "SELECT name, value " +
                "FROM " + entityStr + " e, " + parameterStr + " p, " +
                parameterExecStr + " pe " + 
                "WHERE pe.wf_exec_id = ? AND pe.parameter_id = e.id AND " +
                "pe.parameter_id = p.id AND p.type LIKE '%Parameter'";

                _psParamNameValuesOfSpecificTypeForExecution = _dbType.getPrepStatement(queryStr);
        }  
        
        if(_psTimestampsForActorFiring == null)
        {
            _psTimestampsForActorFiring = _dbType.getSQLSelect("actor_fire",
                "start_time, end_time", "id = ?");
        } 
        
        if(_psTimestampsForTokenRead == null) {
            _psTimestampsForTokenRead = _dbType.getSQLSelect("port_event",
                    "time", "write_event_id = ?");
        }

        if(_psTimestampForTokenWrite == null) {
            _psTimestampForTokenWrite = _dbType.getSQLSelect("port_event",
                    "time", "id = ?");
        }

        if(_psParamAndPortParamNameValuesForFiring == null)
        {
            String queryStr = "SELECT name, value " +
                "FROM " + entityStr + " e, " + parameterStr + " p, " +
                parameterExecStr + " pe, " + actorFireStr + " af " +
                "WHERE e.type LIKE '%parameter' AND e.id = p.id AND " +
                "e.name LIKE ? AND e.id = pe.parameter_id AND pe.wf_exec_id = af.wf_exec_id AND af.id = ?";
            
            _psParamAndPortParamNameValuesForFiring = _dbType.getPrepStatement(queryStr);
        } 
        
        if(_psActorFireId == null)
        {
            String queryStr = "SELECT a.id " +
                    "FROM " + entityStr + " e, " + actorFireStr + " a " +
                        "WHERE e.id = a.actor_id " +
                    "AND a.wf_exec_id = ? " +
                    "AND e.name = ?";
                
            _psActorFireId = _dbType.getPrepStatement(queryStr);        
        }
        
        if(_psActorFirings == null) {
            String queryStr = "SELECT a.id " +
                    "FROM " + actorFireStr + " a " +
                    "WHERE a.wf_exec_id = ? " +
                    "ORDER BY a.id ASC";
            _psActorFirings = _dbType.getPrepStatement(queryStr);
        }

        if(_psActorExecutionTimes == null)
        {
            String queryStr = "SELECT af.id, af.start_time, af.end_time " +
                "FROM " + actorFireStr + " af, " + entityStr + " ent " +
                "WHERE ent.name = ? AND ent.id = af.actor_id AND af.wf_exec_id = ?";
            _psActorExecutionTimes = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psPortIOBytesRead == null)
        {
            String queryStr = "SELECT pe.id, d.contents " +
                "FROM " + dataStr + " d, " + portEventStr + " pe, " +
                entityStr + " ent, " + actorFireStr + "  af " +
                "WHERE ent.name = ? AND ent.id = pe.port_id AND " +
                "af.id = pe.fire_id AND af.wf_exec_id = ? AND " +
                "d.md5 = pe.data_id AND pe.write_event_id != -1";
            
            _psPortIOBytesRead = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psPortIOBytesWrite == null)
        {
            String queryStr = "SELECT pe.id, d.contents " +
                "FROM " + dataStr + " d, " + portEventStr + " pe, " +
                entityStr + " ent, " + actorFireStr + "  af " +
                "WHERE ent.name = ? AND ent.id = pe.port_id AND " +
                "af.id = pe.fire_id AND af.wf_exec_id = ? AND " +
                "d.md5 = pe.data_id AND pe.write_event_id = -1";
            
            _psPortIOBytesWrite = _dbType.getPrepStatement(queryStr);
        }
        
        if(_psAssociatedKeysValuesForExecution == null) {
            String queryStr = "SELECT name, val " +
                "FROM " + associatedDataStr +
                " WHERE data_id is NULL and wf_exec_id = ?";
            _psAssociatedKeysValuesForExecution = _dbType.getPrepStatement(queryStr);
        }
    }

    /** Get the entities for a workflow change. */
    protected List<Integer> _getEntitiesForChange(int changeId)
        throws QueryException
    {
        try
        {
            _psEntitiesForChange.setInt(1, changeId);
            return _getIntResults(_psEntitiesForChange, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying entities for " +
                "change: ", e);
        }
    }

    /** Get the last change time for a workflow. */
    protected Date _getLastChangeTime(String workflowName, String hostName)
        throws QueryException
    {   
        if(workflowName == null || hostName == null)        
        {
            throw new QueryException("not implemented yet."); 
        }
        
        try
        {
            Date retval = null;

            _psLastChangeTime.setString(1, workflowName); 
            _psLastChangeTime.setString(2, hostName); 
            
            ResultSet result = null;
            try
            {
                result = _psLastChangeTime.executeQuery();
                if(result.next())
                {
                    retval = result.getTimestamp(1);
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
            return retval;
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying last change time: ", e);
        }
    }

    /** Get the last change time for a workflow. */
    protected Date _getLastChangeTime(int workflowId, String hostName)
        throws QueryException
    {
        if(hostName == null)        
        {
            throw new QueryException("not implemented yet."); 
        }

        try
        {
            Date retval = null;

            _psLastChangeTimeForWorkflowId.setInt(1, workflowId); 
            _psLastChangeTimeForWorkflowId.setString(2, hostName); 

            ResultSet result = null;
            try
            {
                result = _psLastChangeTimeForWorkflowId.executeQuery();
                if(result.next())
                {
                    retval = result.getTimestamp(1);
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
            return retval;
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying last change time: ", e);
        }
    }

    /** Get changes performed since a specific time. */
    protected List<Integer> _getChangesSince(Date timeStamp,
        /* XXX boolean including */ String workflowName, String hostName)
        throws QueryException
    {
        Timestamp startTime = null;
        if(timeStamp != null)
        {
            startTime = new Timestamp(timeStamp.getTime());
        }
        return _getChangesForTimespan(startTime, _maxEndTime, workflowName,
            hostName);
    }

    /** Get changes performed on a host within a timespan. If host is null,
     *  returns changes for all hosts.
     */
    protected List<Integer> _getChangesForTimespan(Date start,
        Date end, String workflow, String host)
        throws QueryException
    {

        // sanity checks
        if(start == null)
        {
            start = _minStartTime;
        }

        if(end == null)
        {
            end = _maxEndTime;
        }

        if(start.after(end))
        {
            throw new QueryException("Start time " + start + " is after " +
                " end time " + end);
        }

        try
        {
            PreparedStatement query;

            if(host == null)
            {
                query = _psChangesForTimespan;
            }
            else if(workflow == null)
            {
                throw new QueryException("not implemented yet");
            }
            else // host != null && workflow != null
            {
                query = _psChangesForTimespanOnHost;
                query.setString(3, host);
                query.setString(4, workflow);
            }

            query.setTimestamp(1, new Timestamp(start.getTime()));
            query.setTimestamp(2, new Timestamp(end.getTime()));

            return _getIntResults(query, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query changes: ", e);
        }
    }

    /** Get the last execution start time. */
    protected Date _getLastExecutionStartTime(String workflowName,
        String hostName) throws QueryException
    {
        if(workflowName == null || hostName == null) 
        {
            throw new QueryException("not implemented yet.");
        }

        try
        {
            Timestamp retval = null;

            _psLastExecutionStartTime.setString(1, workflowName);
            _psLastExecutionStartTime.setString(2, hostName);
            
            ResultSet result = null;
            try
            {
                result = _psLastExecutionStartTime.executeQuery();
                if(result.next())
                {
                    retval = result.getTimestamp(1);
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
            return retval;
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying last execution start " +
                "time: ", e);
        }
    }

    /** Get the last execution start time. */
    protected Date _getLastExecutionStartTime(int workflowId,
        String hostName) throws QueryException
    {
        if(hostName == null)
        {
            throw new QueryException("not implemented yet.");
        }

        try
        {
            Timestamp retval = null;

            _psLastExecutionStartTimeForWorkflowId.setInt(1, workflowId);
            _psLastExecutionStartTimeForWorkflowId.setString(2, hostName);

            ResultSet result = null;
            try
            {
                result = 
                    _psLastExecutionStartTimeForWorkflowId.executeQuery();
                if(result.next())
                {
                    retval = result.getTimestamp(1);
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
            return retval;
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying last execution start " +
                "time: ", e);
        }
    }

    /** Get executions started since a specific time. 
     *  @param timeStamp get executions started after this time.
     *  @param including if true, include executions starting at timeStamp.
     *  if false, do not include executions starting at timeStamp.
     *  @param workflowName get executions for a specific workflow.
     *  @param hostName get executions for a specific host.
     */
    protected List<Integer> _getExecutionsStartedSince(Date timeStamp,
        boolean including, String workflowName, String hostName) 
        throws QueryException
    {
        Timestamp startTime = null;
        if(timeStamp != null)
        {
            if(including)
            {
                startTime = new Timestamp(timeStamp.getTime());
            }
            else
            {
                startTime = new Timestamp(timeStamp.getTime() + 1);
            }
        }
        return _getExecutionsForTimespan(startTime, _maxEndTime, workflowName,
            hostName);
    }

    /** Get executions for a worfkflow run on a host within a timespan.  */
    protected List<Integer> _getExecutionsForTimespan(Date start,
        Date end, String workflow, String host) throws QueryException
    {
        
        // sanity checks
        if(start == null)
        {
            start = _minStartTime;
            //_debug("setting start time to min");
        }

        if(end == null)
        {
            end = _maxEndTime;
            //_debug("setting end time to max");
        }

        if(start.after(end))
        {
            throw new QueryException("Start time " + start + " is after " +
                " end time " + end);
        }

        //_debug("start = " + start + " | end = " + end);

        try
        {
            PreparedStatement query;
            if(host == null)
            {
                query = _psExecutionsForTimespan;
            }
            else if(workflow == null)
            {
                throw new QueryException("not implemented yet");
            }
            else // host != null && workflow != null
            {
                query = _psExecutionsForTimespanOnHost;
                query.setString(3, host);
                query.setString(4, workflow);

            }

            query.setTimestamp(1, new Timestamp(start.getTime()));
            query.setTimestamp(2, new Timestamp(end.getTime()));

            return _getIntResults(query, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query executions for " +
            "timespan " + start + " - " + end + " :", e);
        }
    }

    /** Get actor firings for an execution. */
    /*
    protected List<Integer> _getActorFiringsForExecution(int execId)
        throws QueryException
    {
        try        
        {
            _psActorFiringsForExecution.setInt(1, execId);
            return _getIntResults(_psActorFiringsForExecution, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying actor firings for " +
                "execution: ", e);
        }
    }
    */

    /** Get the id for a workflow. If workflow does not exist, returns an
     *  an integer less than 0.
     */
    protected int _getWorkflow(String workflow) throws QueryException
    {
        int retval = -1;

        try
        {
            ResultSet result = null;
            try
            {
                result = _psWorkflows.executeQuery();
                while(result.next())
                {
                    if(result.getString("name").equals(workflow))
                    {
                        retval = result.getInt("id");
                        break;
                    }
                }
            }
            finally
            {
                if(result != null)
                {
                    result.close();
                }
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query workflow: ", e);
        }
        return retval;
    }
    
    /*
    protected List<Integer> _getTokensForFiring(int fireId)
        throws QueryException
    {
        try 
        {
            _psTokensForFiring.setInt(1, fireId);
            return _getIntResults(_psTokensForFiring, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Error querying tokens for firing: ", e);
        }
    }
    */

    /** Get all the data MD5 checksums. */
    protected List<String> _getDataMD5s() throws QueryException
    {
        try
        {
            return _getStringResults(_psDataMD5s, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query data md5s: ", e);
        }
    }

    /** Get a list of MoML MD5 checksums for a set of execution ids.*/
    /* XXX need Arrays
    protected List<String> _getMoMLMD5sForExecutions(List<Integer> execList)
        throws QueryException
    {
        try
        {
            String dataStr = _dbType.getTableName("data");
            String wfExecStr = _dbType.getTableName("workflow_exec");
            String queryStr = "SELECT DISTINCT d.md5 " +
                "FROM " + dataStr + " d, " + wfExecStr + " wfexec " +
                "WHERE wfexec.wf_contents_id = d.md5 AND wfexec.id IN ( " +
                StringUtil.listToString(execList) + " )";
           
            //_debug(queryStr);

            return _getStringResults(_psMoMLMD5sForExecutions, queryStr, 1);

            // XXX no arrays
            // _psMoMLMD5sForExecutions.setString(1, buf.toString());
            //return _getStringResults(_psMoMLMD5sForExecutions, 1);
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to query moml md5s for " +
                "executions: ", e);
        }
    }
    */

    /** Convenience routine to get a list of integer SQL results.
     *  @param query the query to execute
     *  @param index column number of result
     */
    protected List<Integer> _getIntResults(PreparedStatement query, int index)
        throws SQLException
    {
        List<Integer> retval = new LinkedList<Integer>();
        try(ResultSet result = query.executeQuery();)
        {
            /*
            java.sql.ResultSetMetaData md = result.getMetaData();
            for(int i = 1; i <= md.getColumnCount(); i++)
            {
                _debug("column " + i + " = " + md.getColumnName(i));
            }
            */

            while(result.next())
            {
                //_debug("got result row; adding " + result.getInt(index));
                retval.add(result.getInt(index));
            }
        }
        return retval;
    }
   
    /** A convenience method that executes a query returning parameter name
     *  value pairs.
     */
    protected static Map<String,String> _getParameterNameValues(PreparedStatement statement)
        throws QueryException, SQLException
    {
        // use a linked map for predictable iteration order
        Map<String,String> retval = new LinkedHashMap<String,String>();
        ResultSet result = null;

        try
        {
            result = statement.executeQuery();
            while(result.next())
            {
                String name = result.getString("name");
                String value = result.getString("value");

                if(name == null)
                {
                    throw new QueryException("No name column.");
                }

                if(value == null)
                {
                    // NOTE: oracle returns null for empty strings
                    value = "";
                }

                retval.put(name, value);
            }
        }
        catch(SQLException e)
        {
            throw new QueryException("Unable to get name value pairs: ", e);
        }
        finally
        {
            if(result != null)
            {
                result.close();
            }
        }

        return retval;
    }
 
    /** Convenience routine to get a list of string SQL results. 
     *  @param query the query to execute
     *  @param index column number of result
     */
    protected List<String> _getStringResults(PreparedStatement query, int index)
        throws SQLException
    {
        List<String> retval = new LinkedList<String>();
        ResultSet result = null;
        try
        {
            result = query.executeQuery();
            while(result.next())
            {
                retval.add(result.getString(index));
            }
        }
        finally
        {
            if(result != null)
            {
                result.close();
            }
        }
        return retval;
    }

    /** Convenience routine to get a list of string SQL results.
     *  @param statement statement to execute
     *  @param queryStr the query string to execute
     *  @param index column number of result
     */
    protected List<String> _getStringResults(Statement statement,
        String queryStr, int index) throws SQLException
    {
        List<String> retval = new LinkedList<String>();
        ResultSet result = null;
        try
        {
            result = statement.executeQuery(queryStr);
            while(result.next())
            {
                retval.add(result.getString(index));
            }
        }
        finally
        {
            if(result != null)
            {
                result.close();
            }
        }
        return retval;
    }


    /** Set our prepared statements to null. */
    protected void _nullPreparedStatements()
    {

        _psActorFiringForTokenWrite = null;
        _psActorFiringsForExecution = null;
        _psActorFiringsForTokenRead = null;
        _psActorNameForFiring = null;
        _psActorTypeForFiring = null;
        _psChangesForTimespan = null;
        _psChangesForTimespanOnHost = null;
        _psDataMD5s = null;
        _psEntitiesForChange = null;
        _psEntityId = null;
        _psEntityIdLSID = null;
        _psEntityTokensForExecution = null;
        _psErrorForExecution = null;
        _psExecutions = null;
        _psExecutionsForAnnotation = null;
        _psExecutionsForTimespan = null;
        _psExecutionsForTimespanOnHost = null;
        _psExecutionsForWorkflow = null;
        _psExecutionsForWorkflowNameContains = null;
        _psExecutionsForWorkflowRuns = null;
        _psExecutionsForWorkflowRunsAfter = null;
        _psExecutionsForWorkflowRunsAt = null;
        _psExecutionsForWorkflowRunsBefore = null;
        _psExecutionsForWorkflowRuns2 = null;
        _psExecutionLSIDsForWorkflowRuns = null;
        _psExecutionIdsForTags = null;
        _psExecutionLSIDs = null;
        _psTagsForExecutionId = null;
        _psTagURNsForExecutionId = null;
        _psTypeForTag = null;
        _psLastChangeTime = null;
        _psLastChangeTimeForWorkflowId = null;
        _psLastExecutionForWorkflow = null;
        _psLastExecutionForWorkflowLSID = null;
        _psLastExecutionLSIDForWorkflowName = null;
        _psLastExecutionStartTime = null;
        _psLastExecutionStartTimeForWorkflowId = null;
        _psMoMLMD5sForExecutions = null;
        _psMomlForExecutionId = null;
        _psParamNameValuesForExecution = null;
        _psParamNameValuesForFiring = null;
        _psParamValueAtTimeForWorkflowLSID = null;
        _psTimestampsForExecution = null;
        _psTokenType = null;
        _psDataContents = null;
        _psPortEventData = null;
        _psTokensForExecution = null;
        _psTokensForFiring = null;
        _psTokensReadForFiring = null;
        _psTokensWrittenForFiring = null;
        _psWorkflows = null;
        _psWorkflowNameForLSID = null;
        _psWorkflowNameForExecutionLSID = null;
        _psPortsForActor = null;
        _psDoesExecutionHaveError = null;
        _psExecutionsForType = null;
        _psIsExecutionImported = null;        
        _psUserForExecution = null;
        _psUserForExecutionLSID = null;
        _psHostIdForExecution = null;
        _psOutputRoleToken = null;
        _psInputRoleToken = null;
        _psParamNameValuesOfSpecificTypeForExecution = null;
        _psTimestampsForActorFiring = null;
        _psParamAndPortParamNameValuesForFiring = null;
        _psActorFireId = null;
        _psActorFirings = null;
        _psActorExecutionTimes = null;
        _psPortIOBytesRead = null;
        _psPortIOBytesWrite = null;
        //_psIOLinkRead = null;
        _psTimestampsForTokenRead = null;
        _psTimestampForTokenWrite = null;
        _psAssociatedKeysValuesForExecution = null;
    }

    protected void _debug(String str)
    {
        System.out.println("DEBUG: " + str);
    }

    /** Returns true since SQLRecordingV8 saves StringToken values
     *  using StringToken.toString(), which adds surrounding
     *  double-quotes.
     */
    @Override
    protected boolean _stringTokenValuesHaveSurroundingQuotes() {
        return true;
    }

    ///////////////////////////////////////////////////////////////////////
    //// protected fields                                              ////

    /** The connection to the database. */
    protected DatabaseType _dbType;

    // jdbc prepared statements for queries

    protected PreparedStatement _psActorFiringForTokenWrite;
    protected PreparedStatement _psActorFiringsForExecution;
    protected PreparedStatement _psActorFiringsForTokenRead;
    protected PreparedStatement _psActorFireId;
    protected PreparedStatement _psActorFirings;
    protected PreparedStatement _psActorNameForFiring;
    protected PreparedStatement _psActorTypeForFiring;
    protected PreparedStatement _psChannelForTokenRead;
    protected PreparedStatement _psChannelForTokenWrite;
    protected PreparedStatement _psChangesForTimespan;
    protected PreparedStatement _psChangesForTimespanOnHost;
    protected PreparedStatement _psDataMD5s;
    protected PreparedStatement _psDoesExecutionHaveError;
    protected PreparedStatement _psEntitiesForChange;
    protected PreparedStatement _psEntityId;
    protected PreparedStatement _psEntityWorkflowId;
    protected PreparedStatement _psEntityIdLSID;
    protected PreparedStatement _psEntityTokensForExecution;
    protected PreparedStatement _psEntityType;
    protected PreparedStatement _psErrorForExecution;
    protected PreparedStatement _psExecutions;
    protected PreparedStatement _psExecutionsForAnnotation;
    protected PreparedStatement _psExecutionsForTimespan;
    protected PreparedStatement _psExecutionsForTimespanOnHost;
    protected PreparedStatement _psExecutionsForType;
    protected PreparedStatement _psExecutionsForWorkflow;
    protected PreparedStatement _psExecutionForExecutionLSID;
    protected PreparedStatement _psExecutionForExecutionLSIDWithoutRevision;
    protected PreparedStatement _psExecutionForOldestReferralExecutionLSIDWithoutRevision;
    protected PreparedStatement _psExecutionLSIDForExecution;
    protected PreparedStatement _psExecutionsForWorkflowNameContains;
    protected PreparedStatement _psExecutionsForWorkflowRuns;
    protected PreparedStatement _psExecutionsForWorkflowRunsAfter;
    protected PreparedStatement _psExecutionsForWorkflowRunsAt;
    protected PreparedStatement _psExecutionsForWorkflowRunsBefore;
    protected PreparedStatement _psExecutionsForWorkflowRuns2;
    protected PreparedStatement _psExecutionLSIDsForWorkflowRuns;
    protected PreparedStatement _psExecutionIdsForTags;
    protected PreparedStatement _psExecutionLSIDs;
    protected PreparedStatement _psIsExecutionImported;
    protected PreparedStatement _psTagsForExecutionId;
    protected PreparedStatement _psTagURNsForExecutionId;
    protected PreparedStatement _psTypeForTag;
    protected PreparedStatement _psLastChangeTime;
    protected PreparedStatement _psLastChangeTimeForWorkflowId;
    protected PreparedStatement _psLastExecutionForWorkflow;
    protected PreparedStatement _psLastExecutionForWorkflowLSID;
    protected PreparedStatement _psLastExecutionLSIDForWorkflowName;
    protected PreparedStatement _psLastExecutionLSIDForWorkflowLSID;
    protected PreparedStatement _psLastExecutionStartTime;
    protected PreparedStatement _psLastExecutionStartTimeForWorkflowId;
    protected PreparedStatement _psMomlForExecutionId;
    protected PreparedStatement _psMomlForExecutionLSID;
    protected PreparedStatement _psParamNameValuesForExecution;
    protected PreparedStatement _psParamNameValuesForFiring;
    protected PreparedStatement _psParamValueAtTimeForWorkflowLSID;
    protected PreparedStatement _psTimestampsForExecution;
    protected PreparedStatement _psTimestampForTokenWrite;
    protected PreparedStatement _psTimestampsForTokenRead;
    protected PreparedStatement _psTokenType;
    protected PreparedStatement _psPortEventData;
    protected PreparedStatement _psDataContents;
    protected PreparedStatement _psTokensForExecution;
    protected PreparedStatement _psTokensForFiring;
    protected PreparedStatement _psTokensReadForFiring;
    protected PreparedStatement _psTokensWrittenForFiring;
    protected PreparedStatement _psWorkflowRunsForExecutions;
    protected PreparedStatement _psWorkflowRunsForExecutionLSIDs;
    protected PreparedStatement _psWorkflows;
    protected PreparedStatement _psWorkflowNameForLSID;
    protected PreparedStatement _psWorkflowNameForExecutionLSID;
    protected Statement _psMoMLMD5sForExecutions;
    protected PreparedStatement _psPortsForActor;
    protected PreparedStatement _psHostIdForExecution;
    protected PreparedStatement _psUserForExecution;
    protected PreparedStatement _psUserForExecutionLSID;
    protected PreparedStatement _psOutputRoleToken;
    protected PreparedStatement _psInputRoleToken;
    protected PreparedStatement _psParamNameValuesOfSpecificTypeForExecution;
    protected PreparedStatement _psTimestampsForActorFiring;
    protected PreparedStatement _psParamAndPortParamNameValuesForFiring;
    protected PreparedStatement _psActorExecutionTimes;
    protected PreparedStatement _psPortIOBytesRead;
    protected PreparedStatement _psPortIOBytesWrite;
    protected PreparedStatement _psAssociatedKeysValuesForExecution;
    
    //protected PreparedStatement _psIOLinkRead;

    /** The most futuristic Timestamp possible! */
    protected static final Timestamp _maxEndTime = new Timestamp(Long.MAX_VALUE);

    /** The earliest Timestamp possible. */
    protected static final Timestamp _minStartTime = new Timestamp(0);
    
    ///////////////////////////////////////////////////////////////////////
    //// private methods                                               ////

    /** If the name of the workflow is Unnamed, change the name to the
     *  name stored in the workflow table.
     */
    private String _changeUnnamedWorkflow(String workflowStr)
        throws QueryException
    {
        String retval = workflowStr;

        String wfName = null;
        Element rootElement = null;
        Document jdomDoc = null;
        
        try
        {
            // convert to xml and get the top level name
            XMLParser parser = new XMLParser();
            org.w3c.dom.Document doc = parser.parser(workflowStr);
            jdomDoc = _domBuilder.build(doc);
            rootElement = jdomDoc.getRootElement();
            wfName = rootElement.getAttributeValue("name");
        }
        catch(Exception e)
        {
            throw new QueryException("XML error: ", e);
        }

        if(wfName == null)
        {
            throw new QueryException("No name found in workflow.");
        }
        
        //System.out.println("name = " + wfName);
        
        Matcher matcher = RenameUtil.unnamedIdPattern.matcher(wfName);
        if(matcher.matches())
        {
            //System.out.println("workflow is unnamed");
            
            // get the lsid
            List<Element> children = rootElement.getChildren("property");
            if(children.size() == 0)
            {
                throw new QueryException("No properties in workflow.");
            }
            
            String lsidStr = null;
            for(Element element : children)
            {
                String name = element.getAttributeValue("name");
                if(name != null && name.equals(NamedObjId.NAME))
                {
                    lsidStr = element.getAttributeValue("value");
                    break;
                }
            }
            
            if(lsidStr == null)
            {
                throw new QueryException("Could not find LSID in workflow.");
            }
            
            //System.out.println("lsid = " + lsidStr);
            
            KeplerLSID lsid = null;
            try
            {
                lsid = new KeplerLSID(lsidStr);
            }
            catch(Exception e)
            {
                throw new QueryException("Error creating LSID: " + e);
            }
            
            // get the name from the workflow table.
            String newName = getWorkflowName(lsid);
            
            // if it's different replace it
            if(!wfName.equals(newName))
            {
                rootElement.setAttribute("name", newName);
                retval = _outputter.outputString(jdomDoc);
            }
        }

        return retval;
    }
        
    /** Used to convert w3c DOM to JDOM. */
    private DOMBuilder _domBuilder = new DOMBuilder();
    
    /** Used to convert XML to string. */
    private XMLOutputter _outputter = new XMLOutputter();
    
    private static final int numMillisecondsInASecond = 1000;
    
}