Metacat

/**

 *  '$RCSfile$'

 *    Purpose: A Class that searches a relational DB for elements and

 *             attributes that have free text matches a query string,

 *             or structured query matches to a path specified node in the

 *             XML hierarchy.  It returns a result set consisting of the

 *             document ID for each document that satisfies the query

 *  Copyright: 2000 Regents of the University of California and the

 *             National Center for Ecological Analysis and Synthesis

 *    Authors: Matt Jones

 *

 *   '$Author: tao $'

 *     '$Date: 2013-08-26 16:30:00 -0700 (Mon, 26 Aug 2013) $'

 * '$Revision: 8164 $'

 *

 * This program is free software; you can redistribute it and/or modify

 * it under the terms of the GNU General Public License as published by

 * the Free Software Foundation; either version 2 of the License, or

 * (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */

package edu.ucsb.nceas.metacat;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.io.OutputStreamWriter;

import java.io.Reader;

import java.io.StringReader;

import java.io.StringWriter;

import java.io.Writer;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Timestamp;

import java.util.ArrayList;

import java.util.Date;

import java.util.Enumeration;

import java.util.Hashtable;

import java.util.Iterator;

import java.util.List;

import java.util.StringTokenizer;

import java.util.Vector;

import java.util.zip.ZipEntry;

import java.util.zip.ZipOutputStream;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringEscapeUtils;

import org.apache.log4j.Logger;

import org.dataone.service.exceptions.NotImplemented;

import edu.ucsb.nceas.metacat.common.query.EnabledQueryEngines;

import edu.ucsb.nceas.metacat.database.DBConnection;

import edu.ucsb.nceas.metacat.database.DBConnectionPool;

import edu.ucsb.nceas.metacat.properties.PropertyService;

import edu.ucsb.nceas.metacat.shared.HandlerException;

import edu.ucsb.nceas.metacat.util.AuthUtil;

import edu.ucsb.nceas.metacat.util.DocumentUtil;

import edu.ucsb.nceas.metacat.util.MetacatUtil;

import edu.ucsb.nceas.utilities.PropertyNotFoundException;

import edu.ucsb.nceas.utilities.access.AccessControlInterface;

import edu.ucsb.nceas.utilities.triple.Triple;

import edu.ucsb.nceas.utilities.triple.TripleCollection;

/**

 * A Class that searches a relational DB for elements and attributes that have

 * free text matches a query string, or structured query matches to a path

 * specified node in the XML hierarchy. It returns a result set consisting of

 * the document ID for each document that satisfies the query

 */

public class DBQuery

{

    public static final String XPATHQUERYOFFINFO = "The Metacat Path Query Engine is turned off. If you want to turn it on, please contact the administrator.";

    static final int ALL = 1;

    static final int WRITE = 2;

    static final int READ = 4;

    private String qformat = "xml";

    // are we combining the query with docid list and, if so, using INTERSECT or UNION?

    private String operator = null;

    //private Connection conn = null;

    private String parserName = null;

    private Logger logMetacat = Logger.getLogger(DBQuery.class);

    /** true if the metacat spatial option is installed **/

    private final boolean METACAT_SPATIAL = true;

    /** useful if you just want to grab a list of docids. Since the docids can be very long,

         it is a vector of vector  **/

    Vector docidOverride = new Vector();

    // a hash table serves as query reuslt cache. Key of hashtable

    // is a query string and value is result xml string

    private static Hashtable queryResultCache = new Hashtable();

    // Capacity of the query result cache

    private static final int QUERYRESULTCACHESIZE;

    static {

    	int qryRsltCacheSize = 0;

    	try {

    		qryRsltCacheSize = Integer.parseInt(PropertyService.getProperty("database.queryresultCacheSize"));

    	} catch (PropertyNotFoundException pnfe) {

    		System.err.println("Could not get QUERYRESULTCACHESIZE property in static block: "

					+ pnfe.getMessage());

    	}

    	QUERYRESULTCACHESIZE = qryRsltCacheSize;

    }

    // Size of page for non paged query

    private static final int NONPAGESIZE = 99999999;

    /**

     * the main routine used to test the DBQuery utility.

     * <p>

     * Usage: java DBQuery <xmlfile>

     * NOTE: encoding should be provided for best results

     * @param xmlfile the filename of the xml file containing the query

     */

    static public void main(String[] args)

    {

        if (args.length < 1) {

            System.err.println("Wrong number of arguments!!!");

            System.err.println("USAGE: java DBQuery [-t] [-index] <xmlfile>");

            return;

        } else {

            try {

                int i = 0;

                boolean showRuntime = false;

                boolean useXMLIndex = false;

                if (args[i].equals("-t")) {

                    showRuntime = true;

                    i++;

                }

                if (args[i].equals("-index")) {

                    useXMLIndex = true;

                    i++;

                }

                String xmlfile = args[i];

                // Time the request if asked for

                double startTime = System.currentTimeMillis();

                // Open a connection to the database

                //Connection dbconn = util.openDBConnection();

                double connTime = System.currentTimeMillis();

                // Execute the query

                DBQuery queryobj = new DBQuery();

                Reader xml = new InputStreamReader(new FileInputStream(new File(xmlfile)));

                Hashtable nodelist = null;

                //nodelist = queryobj.findDocuments(xml, null, null, useXMLIndex);

                // Print the reulting document listing

                StringBuffer result = new StringBuffer();

                String document = null;

                String docid = null;

                result.append("<?xml version=\"1.0\"?>\n");

                result.append("<resultset>\n");

                if (!showRuntime) {

                    Enumeration doclist = nodelist.keys();

                    while (doclist.hasMoreElements()) {

                        docid = (String) doclist.nextElement();

                        document = (String) nodelist.get(docid);

                        result.append("  <document>\n    " + document

                                + "\n  </document>\n");

                    }

                    result.append("</resultset>\n");

                }

                // Time the request if asked for

                double stopTime = System.currentTimeMillis();

                double dbOpenTime = (connTime - startTime) / 1000;

                double readTime = (stopTime - connTime) / 1000;

                double executionTime = (stopTime - startTime) / 1000;

                if (showRuntime) {

                    System.out.print("  " + executionTime);

                    System.out.print("  " + dbOpenTime);

                    System.out.print("  " + readTime);

                    System.out.print("  " + nodelist.size());

                    System.out.println();

                }

                //System.out.println(result);

                //write into a file "result.txt"

                if (!showRuntime) {

                    File f = new File("./result.txt");

                    Writer fw = new OutputStreamWriter(new FileOutputStream(f));

                    BufferedWriter out = new BufferedWriter(fw);

                    out.write(result.toString());

                    out.flush();

                    out.close();

                    fw.close();

                }

            } catch (Exception e) {

                System.err.println("Error in DBQuery.main");

                System.err.println(e.getMessage());

                e.printStackTrace(System.err);

            }

        }

    }

    /**

     * construct an instance of the DBQuery class

     *

     * <p>

     * Generally, one would call the findDocuments() routine after creating an

     * instance to specify the search query

     * </p>

     *

     * @param parserName the fully qualified name of a Java class implementing

     *            the org.xml.sax.XMLReader interface

     */

    public DBQuery() throws PropertyNotFoundException

    {

        String parserName = PropertyService.getProperty("xml.saxparser");

        this.parserName = parserName;

    }

    /**

     * 

     * Construct an instance of DBQuery Class

     * BUT accept a docid Vector that will supersede

     * the query.printSQL() method

     *

     * If a docid Vector is passed in,

     * the docids will be used to create a simple IN query 

     * without the multiple subselects of the printSQL() method

     *

     * Using this constructor, we just check for 

     * a docidOverride Vector in the findResultDoclist() method

     *

     * @param docids List of docids to display in the resultset

     */

    public DBQuery(Vector docids) throws PropertyNotFoundException

    {

    	// since the query will be too long to be handled, so we divided the 

    	// docids vector into couple vectors.

    	int size = (new Integer(PropertyService.getProperty("database.appResultsetSize"))).intValue();

    	logMetacat.info("DBQuery.DBQuery - The size of select doicds is "+docids.size());

    	logMetacat.info("DBQuery.DBQuery - The application result size in metacat.properties is "+size);

    	Vector subset = new Vector();

    	if (docids != null && docids.size() > size)

    	{

    		int index = 0;

    		for (int i=0; i< docids.size(); i++)

    		{

    			if (index < size)

    			{  	

    				subset.add(docids.elementAt(i));

    				index ++;

    			}

    			else

    			{

    				docidOverride.add(subset);

    				subset = new Vector();

    				subset.add(docids.elementAt(i));

    			    index = 1;

    			}

    		}

    		if (!subset.isEmpty())

    		{

    			docidOverride.add(subset);

    		}

    	}

    	else

    	{

    		this.docidOverride.add(docids);

    	}

        String parserName = PropertyService.getProperty("xml.saxparser");

        this.parserName = parserName;

    }

  /**

   * Method put the search result set into out printerwriter

   * @param resoponse the return response

   * @param out the output printer

   * @param params the paratermer hashtable

   * @param user the user name (it maybe different to the one in param)

   * @param groups the group array

   * @param sessionid  the sessionid

 * @throws NotImplemented 

   */

  public void findDocuments(HttpServletResponse response,

                                       Writer out, Hashtable params,

                                       String user, String[] groups,

                                       String sessionid) throws PropertyNotFoundException

  {

    boolean useXMLIndex = (new Boolean(PropertyService.getProperty("database.usexmlindex")))

               .booleanValue();

    findDocuments(response, out, params, user, groups, sessionid, useXMLIndex);

  }

    /**

     * Method put the search result set into out printerwriter

     * @param resoponse the return response

     * @param out the output printer

     * @param params the paratermer hashtable

     * @param user the user name (it maybe different to the one in param)

     * @param groups the group array

     * @param sessionid  the sessionid

     */

    private void findDocuments(HttpServletResponse response,

                                         Writer out, Hashtable params,

                                         String user, String[] groups,

                                         String sessionid, boolean useXMLIndex) {

      if(!EnabledQueryEngines.getInstance().isEnabled(EnabledQueryEngines.PATHQUERYENGINE)) {

          try {

              String output = "";

              output += "<?xml version=\"1.0\"?>";

              output += "<error>";

              output += XPATHQUERYOFFINFO;

              output += "</error>";

              out.write(output);

              out.close();

          } catch (IOException e) {

              logMetacat.warn("DBQuery.findDocuments - metacat can't write the message that the pathquery is off to the client since :"+e.getMessage());

          } 

          return;

      }

      int pagesize = 0;

      int pagestart = 0;

      long transferWarnLimit = 0; 

      if(params.containsKey("pagesize") && params.containsKey("pagestart"))

      {

        String pagesizeStr = ((String[])params.get("pagesize"))[0];

        String pagestartStr = ((String[])params.get("pagestart"))[0];

        if(pagesizeStr != null && pagestartStr != null)

        {

          pagesize = (new Integer(pagesizeStr)).intValue();

          pagestart = (new Integer(pagestartStr)).intValue();

        }

      }

      String xmlquery = null;

      String qformat = null;

      // get query and qformat

      try {

    	xmlquery = ((String[])params.get("query"))[0];

        logMetacat.info("DBQuery.findDocuments - SESSIONID: " + sessionid);

        logMetacat.info("DBQuery.findDocuments - xmlquery: " + xmlquery);

        qformat = ((String[])params.get("qformat"))[0];

        logMetacat.info("DBQuery.findDocuments - qformat: " + qformat);

      }

      catch (Exception ee)

      {

        logMetacat.error("DBQuery.findDocuments - Couldn't retrieve xmlquery or qformat value from "

                  +"params hashtable in DBQuery.findDocuments: "

                  + ee.getMessage()); 

      }

      // Get the XML query and covert it into a SQL statment

      QuerySpecification qspec = null;

      if ( xmlquery != null)

      {

         xmlquery = transformQuery(xmlquery);

         try

         {

           qspec = new QuerySpecification(xmlquery,

                                          parserName,

                                          PropertyService.getProperty("document.accNumSeparator"));

         }

         catch (Exception ee)

         {

           logMetacat.error("DBQuery.findDocuments - error generating QuerySpecification object: "

                                    + ee.getMessage());

         }

      }

      if (qformat != null && qformat.equals(MetacatUtil.XMLFORMAT))

      {

        //xml format

        if(response != null)

        {

            response.setContentType("text/xml");

        }

        createResultDocument(xmlquery, qspec, out, user, groups, useXMLIndex, 

          pagesize, pagestart, sessionid, qformat, false);

      }//if

      else

      {

        //knb format, in this case we will get whole result and sent it out

        response.setContentType("text/html");

        Writer nonout = null;

        StringBuffer xml = createResultDocument(xmlquery, qspec, nonout, user,

                                                groups, useXMLIndex, pagesize, 

                                                pagestart, sessionid, qformat, false);

        //transfer the xml to html

        try

        {

         long startHTMLTransform = System.currentTimeMillis();

         DBTransform trans = new DBTransform();

         response.setContentType("text/html");

         // if the user is a moderator, then pass a param to the 

         // xsl specifying the fact

         if(AuthUtil.isModerator(user, groups)){

        	 params.put("isModerator", new String[] {"true"});

         }

         trans.transformXMLDocument(xml.toString(), "-//NCEAS//resultset//EN",

                                 "-//W3C//HTML//EN", qformat, out, params,

                                 sessionid);

         long transformRunTime = System.currentTimeMillis() - startHTMLTransform;

         transferWarnLimit = Long.parseLong(PropertyService.getProperty("dbquery.transformTimeWarnLimit"));

         if (transformRunTime > transferWarnLimit) {

         	logMetacat.warn("DBQuery.findDocuments - The time to transfrom resultset from xml to html format is "

                  		                             + transformRunTime);

         }

          MetacatUtil.writeDebugToFile("---------------------------------------------------------------------------------------------------------------Transfrom xml to html  "

                             + transformRunTime);

          MetacatUtil.writeDebugToDelimiteredFile(" " + transformRunTime, false);

        }

        catch(Exception e)

        {

         logMetacat.error("DBQuery.findDocuments - Error in MetaCatServlet.transformResultset:"

                                +e.getMessage());

         }

      }//else

  }

  /**

   * Transforms a hashtable of documents to an xml or html result and sent

   * the content to outputstream. Keep going untill hastable is empty. stop it.

   * add the QuerySpecification as parameter is for ecogrid. But it is duplicate

   * to xmlquery String

   * @param xmlquery

   * @param qspec

   * @param out

   * @param user

   * @param groups

   * @param useXMLIndex

   * @param sessionid

   * @return

   */

    public StringBuffer createResultDocument(String xmlquery,

                                              QuerySpecification qspec,

                                              Writer out,

                                              String user, String[] groups,

                                              boolean useXMLIndex)

    {

    	return createResultDocument(xmlquery,qspec,out, user,groups, useXMLIndex, 0, 0,"", qformat, false);

    }

    /**

     * 

     * @param xmlquery

     * @param user

     * @param groups

     * @param useXMLIndex

     * @return

     * @throws IOException 

     * @throws PropertyNotFoundException 

     */

	public String performPathquery(String xmlquery, String user,

			String[] groups) throws PropertyNotFoundException, IOException {

		// get the XML query and convert it to query specification

		xmlquery = transformQuery(xmlquery);

		QuerySpecification qspec = new QuerySpecification(xmlquery, parserName, PropertyService.getProperty("document.accNumSeparator"));

		// force it to output the results to the string buffer, not outputstream

		Writer nonout = null;

		boolean useXMLIndex = (new Boolean(PropertyService.getProperty("database.usexmlindex"))).booleanValue();

		StringBuffer xml = createResultDocument(xmlquery, qspec, nonout, user, groups, useXMLIndex, 0, 0, "", qformat, true);

		return xml.toString();

	}

  /*

   * Transforms a hashtable of documents to an xml or html result and sent

   * the content to outputstream. Keep going untill hastable is empty. stop it.

   * add the QuerySpecification as parameter is for ecogrid. But it is duplicate

   * to xmlquery String

   */

  public StringBuffer createResultDocument(String xmlquery,

                                            QuerySpecification qspec,

                                            Writer out,

                                            String user, String[] groups,

                                            boolean useXMLIndex, int pagesize,

                                            int pagestart, String sessionid, 

                                            String qformat, boolean includeGuid)

  {

    DBConnection dbconn = null;

    int serialNumber = -1;

    StringBuffer resultset = new StringBuffer();

    //try to get the cached version first    

    // Hashtable sessionHash = MetaCatServlet.getSessionHash();

    // HttpSession sess = (HttpSession)sessionHash.get(sessionid);

    resultset.append("<?xml version=\"1.0\"?>\n");

    resultset.append("<resultset>\n");

    resultset.append("  <pagestart>" + pagestart + "</pagestart>\n");

    resultset.append("  <pagesize>" + pagesize + "</pagesize>\n");

    resultset.append("  <nextpage>" + (pagestart + 1) + "</nextpage>\n");

    resultset.append("  <previouspage>" + (pagestart - 1) + "</previouspage>\n");

    resultset.append("  <query><![CDATA[" + xmlquery + "]]></query>");

    //send out a new query

    if (out != null)

    {

    	try {

    	  out.write(resultset.toString());

		} catch (IOException e) {

			logMetacat.error(e.getMessage(), e);

		}

    }

    if (qspec != null)

    {

      try

      {

        //checkout the dbconnection

        dbconn = DBConnectionPool.getDBConnection("DBQuery.findDocuments");

        serialNumber = dbconn.getCheckOutSerialNumber();

        //print out the search result

        // search the doc list

        Vector givenDocids = new Vector();

        StringBuffer resultContent = new StringBuffer();

        if (docidOverride == null || docidOverride.size() == 0)

        {

        	logMetacat.debug("DBQuery.createResultDocument - Not in map query");

        	resultContent = findResultDoclist(qspec, out, user, groups,

                    dbconn, useXMLIndex, pagesize, pagestart, 

                    sessionid, givenDocids, qformat, includeGuid);

        }

        else

        {

        	logMetacat.debug("DBQuery.createResultDocument - In map query");

        	// since docid can be too long to be handled. We divide it into several parts

        	for (int i= 0; i<docidOverride.size(); i++)

        	{

        	   logMetacat.debug("DBQuery.createResultDocument - in loop===== "+i);

        		givenDocids = (Vector)docidOverride.elementAt(i);

        		StringBuffer subset = findResultDoclist(qspec, out, user, groups,

                        dbconn, useXMLIndex, pagesize, pagestart, 

                        sessionid, givenDocids, qformat, includeGuid);

        		resultContent.append(subset);

        	}

        }

        resultset.append(resultContent);

      } //try

      catch (IOException ioe)

      {

        logMetacat.error("DBQuery.createResultDocument - IO error: " + ioe.getMessage());

      }

      catch (SQLException e)

      {

        logMetacat.error("DBQuery.createResultDocument - SQL Error: " + e.getMessage());

      }

      catch (Exception ee)

      {

        logMetacat.error("DBQuery.createResultDocument - General exception: "

                                 + ee.getMessage());

        ee.printStackTrace();

      }

      finally

      {

        DBConnectionPool.returnDBConnection(dbconn, serialNumber);

      } //finally

    }//if

    String closeRestultset = "</resultset>";

    resultset.append(closeRestultset);

    if (out != null)

    {

      try {

		out.write(closeRestultset);

		} catch (IOException e) {

			logMetacat.error(e.getMessage(), e);

		}

    }

    //default to returning the whole resultset

    return resultset;

  }//createResultDocuments

    /*

     * Find the doc list which match the query

     */

    private StringBuffer findResultDoclist(QuerySpecification qspec,

                                      Writer out,

                                      String user, String[]groups,

                                      DBConnection dbconn, boolean useXMLIndex,

                                      int pagesize, int pagestart, String sessionid, 

                                      Vector givenDocids, String qformat, boolean includeGuid)

                                      throws Exception

    {

    	// keep track of the values we add as prepared statement question marks (?)

  	  List<Object> parameterValues = new ArrayList<Object>();

      StringBuffer resultsetBuffer = new StringBuffer();

      String query = null;

      int count = 0;

      int index = 0;

      ResultDocumentSet docListResult = new ResultDocumentSet();

      PreparedStatement pstmt = null;

      String docid = null;

      String guid = null;

      String docname = null;

      String doctype = null;

      String createDate = null;

      String updateDate = null;

      StringBuffer document = null;

      boolean lastpage = false;

      int rev = 0;

      double startTime = 0;

      int offset = 1;

      long startSelectionTime = System.currentTimeMillis();

      ResultSet rs = null;

      // this is a hack for offset. in postgresql 7, if the returned docid list is too long,

      //the extend query which base on the docid will be too long to be run. So we 

      // have to cut them into different parts. Page query don't need it somehow.

      if (out == null)

      {

        // for html page, we put everything into one page

        offset =

            (new Integer(PropertyService.getProperty("database.webResultsetSize"))).intValue();

      }

      else

      {

          offset =

              (new Integer(PropertyService.getProperty("database.appResultsetSize"))).intValue();

      }

      /*

       * Check the docidOverride Vector

       * if defined, we bypass the qspec.printSQL() method

       * and contruct a simpler query based on a 

       * list of docids rather than a bunch of subselects

       */

      // keep track of the values we add as prepared statement question marks (?)

	  List<Object> docidValues = new ArrayList<Object>();

      if ( givenDocids == null || givenDocids.size() == 0 ) {

          query = qspec.printSQL(useXMLIndex, docidValues);

          parameterValues.addAll(docidValues);

      } else {

    	  // condition for the docids

    	  List<Object> docidConditionValues = new ArrayList<Object>();

    	  StringBuffer docidCondition = new StringBuffer();

    	  docidCondition.append( " xml_documents.docid IN (" );

          for (int i = 0; i < givenDocids.size(); i++) {  

        	  docidCondition.append("?");

        	  if (i < givenDocids.size()-1) {

        		  docidCondition.append(",");

        	  }

        	  docidConditionValues.add((String)givenDocids.elementAt(i));

          }

          docidCondition.append( ") " );

    	  // include the docids, either exclusively, or in conjuction with the query

    	  if (operator == null) {

    		  query = "SELECT xml_documents.docid, identifier.guid, docname, doctype, date_created, date_updated, xml_documents.rev " +

    		  		"FROM xml_documents, identifier " +

    		  		"WHERE xml_documents.docid = identifier.docid AND xml_documents.rev = identifier.rev AND ";

              query = query + docidCondition.toString();

              parameterValues.addAll(docidConditionValues);

    	  } else {

    		  // start with the keyword query, but add conditions

              query = qspec.printSQL(useXMLIndex, docidValues);

              parameterValues.addAll(docidValues);

              String myOperator = "";

              if (!query.endsWith("WHERE") && !query.endsWith("OR") && !query.endsWith("AND")) {

	              if (operator.equalsIgnoreCase(QueryGroup.UNION)) {

	            	  myOperator =  " OR ";

	              }

	              else {

	            	  myOperator =  " AND ";

	              }

              }

              query = query + myOperator + docidCondition.toString();

              parameterValues.addAll(docidConditionValues);

    	  }

      } 

      // we don't actually use this query for anything

      List<Object> ownerValues = new ArrayList<Object>();

      String ownerQuery = getOwnerQuery(user, ownerValues);

      //logMetacat.debug("query: " + query);

      logMetacat.debug("DBQuery.findResultDoclist - owner query: " + ownerQuery);

      // if query is not the owner query, we need to check the permission

      // otherwise we don't need (owner has all permission by default)

      if (!query.equals(ownerQuery))

      {

        // set user name and group

        qspec.setUserName(user);

        qspec.setGroup(groups);

        // Get access query

        String accessQuery = qspec.getAccessQuery();

        if(!query.endsWith("AND")){

            query = query + accessQuery;

        } else {

            query = query + accessQuery.substring(4, accessQuery.length());

        }

      }

      logMetacat.debug("DBQuery.findResultDoclist - final selection query: " + query);

      pstmt = dbconn.prepareStatement(query);     

      // set all the values we have collected 

      pstmt = setPreparedStatementValues(parameterValues, pstmt);

      String queryCacheKey = null;

      // we only get cache for public

      if (user != null && user.equalsIgnoreCase("public") 

         && pagesize == 0 && PropertyService.getProperty("database.queryCacheOn").equals("true"))

      {

          queryCacheKey = pstmt.toString() +qspec.getReturnDocList()+qspec.getReturnFieldList();

          String cachedResult = getResultXMLFromCache(queryCacheKey);

          logMetacat.debug("=======DBQuery.findResultDoclist - The key of query cache is " + queryCacheKey);

          //System.out.println("==========the string from cache is "+cachedResult);

          if (cachedResult != null)

          {

          logMetacat.info("DBQuery.findResultDoclist - result from cache !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");

           if (out != null)

             {

                 out.write(cachedResult);

             }

           resultsetBuffer.append(cachedResult);

           pstmt.close();

           return resultsetBuffer;

          }

      }

      startTime = System.currentTimeMillis() / 1000;

      logMetacat.debug("Prepared statement after setting parameter values: " + pstmt.toString());

      rs = pstmt.executeQuery();

      double queryExecuteTime = System.currentTimeMillis() / 1000;

      logMetacat.debug("DBQuery.findResultDoclist - Time to execute select docid query is "

                    + (queryExecuteTime - startTime));

      MetacatUtil.writeDebugToFile("\n\n\n\n\n\nExecute selection query  "

              + (queryExecuteTime - startTime));

      MetacatUtil.writeDebugToDelimiteredFile(""+(queryExecuteTime - startTime), false);

      boolean tableHasRows = rs.next();

      if(pagesize == 0)

      { //this makes sure we get all results if there is no paging

        pagesize = NONPAGESIZE;

        pagestart = NONPAGESIZE;

      } 

      int currentIndex = 0;

      while (tableHasRows)

      {

        logMetacat.debug("DBQuery.findResultDoclist - getting result: " + currentIndex);

        docid = rs.getString(1).trim();

        logMetacat.debug("DBQuery.findResultDoclist -  docid: " + docid);

        guid = rs.getString(2).trim();

        logMetacat.debug("DBQuery.findResultDoclist -  guid: " + guid);

        docname = rs.getString(3);

        doctype = rs.getString(4);

        logMetacat.debug("DBQuery.findResultDoclist - doctype: " + doctype);

        createDate = rs.getString(5);

        updateDate = rs.getString(6);

        rev = rs.getInt(7);

         Vector returndocVec = qspec.getReturnDocList();

       if (returndocVec.size() == 0 || returndocVec.contains(doctype))

        {

          logMetacat.debug("DBQuery.findResultDoclist - NOT Back tracing now...");

           document = new StringBuffer();

           String completeDocid = docid

                            + PropertyService.getProperty("document.accNumSeparator");

           completeDocid += rev;

           document.append("<docid>").append(completeDocid).append("</docid>");

           if (includeGuid) {

        	   document.append("<guid>").append(guid).append("</guid>");

           }

           if (docname != null)

           {

               document.append("<docname>" + docname + "</docname>");

           }

           if (doctype != null)

           {

              document.append("<doctype>" + doctype + "</doctype>");

           }

           if (createDate != null)

           {

               document.append("<createdate>" + createDate + "</createdate>");

           }

           if (updateDate != null)

           {

             document.append("<updatedate>" + updateDate + "</updatedate>");

           }

           // Store the document id and the root node id

           docListResult.addResultDocument(

             new ResultDocument(docid, (String) document.toString()));

           logMetacat.info("DBQuery.findResultDoclist - real result: " + docid);

           currentIndex++;

           count++;

        }//else

        // when doclist reached the offset number, send out doc list and empty

        // the hash table

        if (count == offset && pagesize == NONPAGESIZE)

        { //if pagesize is not 0, do this later.

          //reset count

          //logMetacat.warn("############doing subset cache");

          count = 0;

          handleSubsetResult(qspec, resultsetBuffer, out, docListResult,

                              user, groups,dbconn, useXMLIndex, qformat);

          //reset docListResult

          docListResult = new ResultDocumentSet();

        }

       logMetacat.debug("DBQuery.findResultDoclist - currentIndex: " + currentIndex);

       logMetacat.debug("DBQuery.findResultDoclist - page comparator: " + (pagesize * pagestart) + pagesize);

       if(currentIndex >= ((pagesize * pagestart) + pagesize))

       {

         ResultDocumentSet pagedResultsHash = new ResultDocumentSet();

         for(int i=pagesize*pagestart; i<docListResult.size(); i++)

         {

           pagedResultsHash.put(docListResult.get(i));

         }

         docListResult = pagedResultsHash;

         break;

       }

       // Advance to the next record in the cursor

       tableHasRows = rs.next();

       if(!tableHasRows)

       {

         ResultDocumentSet pagedResultsHash = new ResultDocumentSet();

         //get the last page of information then break

         if(pagesize != NONPAGESIZE)

         {

           for(int i=pagesize*pagestart; i<docListResult.size(); i++)

           {

             pagedResultsHash.put(docListResult.get(i));