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

 *    Release: @release@

 *

 *   '$Author: perry $'

 *     '$Date: 2006-08-31 16:37:13 -0700 (Thu, 31 Aug 2006) $'

 * '$Revision: 3034 $'

 *

 * 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.FileReader;

import java.io.FileWriter;

import java.io.IOException;

import java.io.InputStream;

import java.io.PrintWriter;

import java.io.StringReader;

import java.io.StringWriter;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Enumeration;

import java.util.Hashtable;

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.log4j.Logger;

import edu.ucsb.nceas.morpho.datapackage.Triple;

import edu.ucsb.nceas.morpho.datapackage.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

{

    static final int ALL = 1;

    static final int WRITE = 2;

    static final int READ = 4;

    //private Connection conn = null;

    private String parserName = null;

    private MetaCatUtil util = new MetaCatUtil();

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

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

    private final boolean METACAT_SPATIAL = true;

    /**

     * the main routine used to test the DBQuery utility.

     * <p>

     * Usage: java DBQuery <xmlfile>

     *

     * @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

                MetaCatUtil util = new MetaCatUtil();

                //Connection dbconn = util.openDBConnection();

                double connTime = System.currentTimeMillis();

                // Execute the query

                DBQuery queryobj = new DBQuery();

                FileReader xml = new FileReader(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");

                    FileWriter fw = new FileWriter(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()

    {

        String parserName = MetaCatUtil.getOption("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

   */

  public void findDocuments(HttpServletResponse response,

                                       PrintWriter out, Hashtable params,

                                       String user, String[] groups,

                                       String sessionid)

  {

    boolean useXMLIndex = (new Boolean(MetaCatUtil.getOption("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

     */

    public void findDocuments(HttpServletResponse response,

                                         PrintWriter out, Hashtable params,

                                         String user, String[] groups,

                                         String sessionid, boolean useXMLIndex)

    {

      // get query and qformat

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

      logMetacat.warn("xmlquery: " + xmlquery);

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

      logMetacat.warn("qformat: " + qformat);

      // 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,

                                          MetaCatUtil.getOption("accNumSeparator"));

         }

         catch (Exception ee)

         {

           logMetacat.error("error generating QuerySpecification object"

                                    +" in DBQuery.findDocuments"

                                    + ee.getMessage());

         }

      }

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

      {

        //xml format

        response.setContentType("text/xml");

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

      }//if

      else

      {

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

        response.setContentType("text/html");

        PrintWriter nonout = null;

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

                                                groups, useXMLIndex);

        //transfer the xml to html

        try

        {

         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(MetaCatUtil.isModerator(user, groups)){

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

         }

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

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

                                 sessionid);

        }

        catch(Exception e)

        {

         logMetacat.error("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

   */

  public StringBuffer createResultDocument(String xmlquery,

                                            QuerySpecification qspec,

                                            PrintWriter out,

                                            String user, String[] groups,

                                            boolean useXMLIndex)

  {

    DBConnection dbconn = null;

    int serialNumber = -1;

    StringBuffer resultset = new StringBuffer();

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

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

    resultset.append("  <query>" + xmlquery + "</query>");

    // sent query part out

    if (out != null)

    {

      out.println(resultset.toString());

    }

    if (qspec != null)

    {

      try

      {

        //checkout the dbconnection

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

        serialNumber = dbconn.getCheckOutSerialNumber();

        //print out the search result

        // search the doc list

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

                                      dbconn, useXMLIndex);

      } //try

      catch (IOException ioe)

      {

        logMetacat.error("IO error in DBQuery.findDocuments:");

        logMetacat.error(ioe.getMessage());

      }

      catch (SQLException e)

      {

        logMetacat.error("SQL Error in DBQuery.findDocuments: "

                                 + e.getMessage());

      }

      catch (Exception ee)

      {

        logMetacat.error("Exception in DBQuery.findDocuments: "

                                 + ee.getMessage());

      }

      finally

      {

        DBConnectionPool.returnDBConnection(dbconn, serialNumber);

      } //finally

    }//if

    String closeRestultset = "</resultset>";

    resultset.append(closeRestultset);

    if (out != null)

    {

      out.println(closeRestultset);

    }

    return resultset;

  }//createResultDocuments

    /*

     * Find the doc list which match the query

     */

    private StringBuffer findResultDoclist(QuerySpecification qspec,

                                      StringBuffer resultsetBuffer,

                                      PrintWriter out,

                                      String user, String[]groups,

                                      DBConnection dbconn, boolean useXMLIndex )

                                      throws Exception

    {

      int offset = 1;

      // this is a hack for offset

      if (out == null)

      {

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

        offset =

            (new Integer(MetaCatUtil.getOption("web_resultsetsize"))).intValue();

      }

      else

      {

          offset =

              (new Integer(MetaCatUtil.getOption("app_resultsetsize"))).intValue();

      }

      int count = 0;

      int index = 0;

      Hashtable docListResult = new Hashtable();

      PreparedStatement pstmt = null;

      String docid = null;

      String docname = null;

      String doctype = null;

      String createDate = null;

      String updateDate = null;

      StringBuffer document = null;

      int rev = 0;

      String query = qspec.printSQL(useXMLIndex);

      String ownerQuery = getOwnerQuery(user);

      logMetacat.info("query: " + query);

      //logMetacat.info("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("WHERE")){

            query = query + accessQuery;

        } else {

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

        }

        logMetacat.warn(" final query: " + query);

      }

      double startTime = System.currentTimeMillis() / 1000;

      pstmt = dbconn.prepareStatement(query);

      // Execute the SQL query using the JDBC connection

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      double queryExecuteTime = System.currentTimeMillis() / 1000;

      logMetacat.warn("Time for execute query: "

                    + (queryExecuteTime - startTime));

      boolean tableHasRows = rs.next();

      while (tableHasRows)

      {

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

        docname = rs.getString(2);

        doctype = rs.getString(3);

        createDate = rs.getString(4);

        updateDate = rs.getString(5);

        rev = rs.getInt(6);

        // if there are returndocs to match, backtracking can be performed

        // otherwise, just return the document that was hit

        Vector returndocVec = qspec.getReturnDocList();

         if (returndocVec.size() != 0 && !returndocVec.contains(doctype)

                        && !qspec.isPercentageSearch())

        {

           logMetacat.warn("Back tracing now...");

           String sep = MetaCatUtil.getOption("accNumSeparator");

           StringBuffer btBuf = new StringBuffer();

           btBuf.append("select docid from xml_relation where ");

           //build the doctype list for the backtracking sql statement

           btBuf.append("packagetype in (");

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

           {

             btBuf.append("'").append((String) returndocVec.get(i)).append("'");

             if (i != (returndocVec.size() - 1))

             {

                btBuf.append(", ");

              }

            }

            btBuf.append(") ");

            btBuf.append("and (subject like '");

            btBuf.append(docid).append("'");

            btBuf.append("or object like '");

            btBuf.append(docid).append("')");

            PreparedStatement npstmt = dbconn.prepareStatement(btBuf.toString());

            //should incease usage count

            dbconn.increaseUsageCount(1);

            npstmt.execute();

            ResultSet btrs = npstmt.getResultSet();

            boolean hasBtRows = btrs.next();

            while (hasBtRows)

            {

               //there was a backtrackable document found

               DocumentImpl xmldoc = null;

               String packageDocid = btrs.getString(1);

               logMetacat.info("Getting document for docid: "

                                         + packageDocid);

                try

                {

                    //  THIS CONSTRUCTOR BUILDS THE WHOLE XML doc not

                    // needed here

                    // xmldoc = new DocumentImpl(dbconn, packageDocid);

                    //  thus use the following to get the doc info only

                    //  xmldoc = new DocumentImpl(dbconn);

                    String accNumber = packageDocid + MetaCatUtil.getOption("accNumSeparator") +

                    DBUtil.getLatestRevisionInDocumentTable(packageDocid);

                    xmldoc = new DocumentImpl(accNumber, false);

                    if (xmldoc == null)

                    {

                       logMetacat.info("Document was null for: "

                                                + packageDocid);

                    }

                }

                catch (Exception e)

                {

                    System.out.println("Error getting document in "

                                       + "DBQuery.findDocuments: "

                                       + e.getMessage());

                }

                String docid_org = xmldoc.getDocID();

                if (docid_org == null)

                {

                   logMetacat.info("Docid_org was null.");

                   //continue;

                }

                docid = docid_org.trim();

                docname = xmldoc.getDocname();

                doctype = xmldoc.getDoctype();

                createDate = xmldoc.getCreateDate();

                updateDate = xmldoc.getUpdateDate();

                rev = xmldoc.getRev();

                document = new StringBuffer();

                String completeDocid = docid

                                + MetaCatUtil.getOption("accNumSeparator");

                completeDocid += rev;

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

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

                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.put(docid, (String) document.toString());

                count++;

                // Get the next package document linked to our hit

                hasBtRows = btrs.next();

              }//while

              npstmt.close();

              btrs.close();

        }

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

        {

           document = new StringBuffer();

           String completeDocid = docid

                            + MetaCatUtil.getOption("accNumSeparator");

           completeDocid += rev;

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

           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.put(docid, (String) document.toString());

              count++;

        }//else

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

        // the hash table

        if (count == offset)

        {

          //reset count

          count = 0;

          handleSubsetResult(qspec,resultsetBuffer, out, docListResult,

                              user, groups,dbconn, useXMLIndex);

          // reset docListResult

          docListResult = new Hashtable();

        }

       // Advance to the next record in the cursor

       tableHasRows = rs.next();

     }//while

     rs.close();

     pstmt.close();

     //if docListResult is not empty, it need to be sent.

     if (!docListResult.isEmpty())

     {

       handleSubsetResult(qspec,resultsetBuffer, out, docListResult,

                              user, groups,dbconn, useXMLIndex);

     }

     double docListTime = System.currentTimeMillis() / 1000;

     logMetacat.warn("prepare docid list time: "

                    + (docListTime - queryExecuteTime));

     //write the persistent spatial dataset

     /*

	 if(metacatSpatialData != null){

		metacatSpatialData.writeTextQueryData();

	 }

      */

     return resultsetBuffer;

    }//findReturnDoclist

    /*

     * Send completed search hashtable(part of reulst)to output stream

     * and buffer into a buffer stream

     */

    private StringBuffer handleSubsetResult(QuerySpecification qspec,

                                           StringBuffer resultset,

                                           PrintWriter out, Hashtable partOfDoclist,

                                           String user, String[]groups,

                                       DBConnection dbconn, boolean useXMLIndex)

                                       throws Exception

   {

     // check if there is a record in xml_returnfield

     // and get the returnfield_id and usage count

     int usage_count = getXmlReturnfieldsTableId(qspec, dbconn);

     boolean enterRecords = false;

     // get value of xml_returnfield_count

     int count = (new Integer(MetaCatUtil

                            .getOption("xml_returnfield_count")))

                            .intValue();

     // set enterRecords to true if usage_count is more than the offset

     // specified in metacat.properties

     if(usage_count > count){

         enterRecords = true;

     }

     if(returnfield_id < 0){

         logMetacat.warn("Error in getting returnfield id from"

                                  + "xml_returnfield table");

	enterRecords = false;

     }

     // get the hashtable containing the docids that already in the

     // xml_queryresult table

     logMetacat.info("size of partOfDoclist before"

                             + " docidsInQueryresultTable(): "

                             + partOfDoclist.size());

     Hashtable queryresultDocList = docidsInQueryresultTable(returnfield_id,

                                                        partOfDoclist, dbconn);

     // remove the keys in queryresultDocList from partOfDoclist

     Enumeration _keys = queryresultDocList.keys();

     while (_keys.hasMoreElements()){

         partOfDoclist.remove(_keys.nextElement());

     }

     // backup the keys-elements in partOfDoclist to check later

     // if the doc entry is indexed yet

     Hashtable partOfDoclistBackup = new Hashtable();

     _keys = partOfDoclist.keys();

     while (_keys.hasMoreElements()){

	 Object key = _keys.nextElement();

         partOfDoclistBackup.put(key, partOfDoclist.get(key));

     }

     logMetacat.info("size of partOfDoclist after"

                             + " docidsInQueryresultTable(): "

                             + partOfDoclist.size());

     //add return fields for the documents in partOfDoclist

     partOfDoclist = addReturnfield(partOfDoclist, qspec, user, groups,

                                        dbconn, useXMLIndex );

     //add relationship part part docid list for the documents in partOfDocList

     partOfDoclist = addRelationship(partOfDoclist, qspec, dbconn, useXMLIndex);

     Enumeration keys = partOfDoclist.keys();

     String key = null;

     String element = null;

     String query = null;

     int offset = (new Integer(MetaCatUtil

                               .getOption("queryresult_string_length")))

                               .intValue();

     while (keys.hasMoreElements())

     {

         key = (String) keys.nextElement();

         element = (String)partOfDoclist.get(key);

	 // check if the enterRecords is true, elements is not null, element's

         // length is less than the limit of table column and if the document

         // has been indexed already

         if(enterRecords && element != null

		&& element.length() < offset

		&& element.compareTo((String) partOfDoclistBackup.get(key)) != 0){

             query = "INSERT INTO xml_queryresult (returnfield_id, docid, "

                 + "queryresult_string) VALUES (?, ?, ?)";

             PreparedStatement pstmt = null;

             pstmt = dbconn.prepareStatement(query);

             pstmt.setInt(1, returnfield_id);

             pstmt.setString(2, key);

             pstmt.setString(3, element);

             dbconn.increaseUsageCount(1);

             pstmt.execute();

             pstmt.close();

         }

         // A string with element

         String xmlElement = "  <document>" + element + "</document>";

         //send single element to output

         if (out != null)

         {

             out.println(xmlElement);

         }

         resultset.append(xmlElement);

     }//while

     keys = queryresultDocList.keys();

     while (keys.hasMoreElements())

     {

         key = (String) keys.nextElement();

         element = (String)queryresultDocList.get(key);

         // A string with element

         String xmlElement = "  <document>" + element + "</document>";

         //send single element to output

         if (out != null)

         {

             out.println(xmlElement);

         }

         resultset.append(xmlElement);

     }//while

     return resultset;

 }

   /**

    * Get the docids already in xml_queryresult table and corresponding

    * queryresultstring as a hashtable

    */

   private Hashtable docidsInQueryresultTable(int returnfield_id,

                                              Hashtable partOfDoclist,

                                              DBConnection dbconn){

         Hashtable returnValue = new Hashtable();

         PreparedStatement pstmt = null;

         ResultSet rs = null;

         // get partOfDoclist as string for the query

         Enumeration keylist = partOfDoclist.keys();

         StringBuffer doclist = new StringBuffer();

         while (keylist.hasMoreElements())

         {

             doclist.append("'");

             doclist.append((String) keylist.nextElement());

             doclist.append("',");

         }//while

         if (doclist.length() > 0)

         {

             doclist.deleteCharAt(doclist.length() - 1); //remove the last comma

             // the query to find out docids from xml_queryresult

             String query = "select docid, queryresult_string from "

                          + "xml_queryresult where returnfield_id = " +

                          returnfield_id +" and docid in ("+ doclist + ")";

             logMetacat.info("Query to get docids from xml_queryresult:"

                                      + query);

             try {

                 // prepare and execute the query

                 pstmt = dbconn.prepareStatement(query);

                 dbconn.increaseUsageCount(1);

                 pstmt.execute();

                 rs = pstmt.getResultSet();

                 boolean tableHasRows = rs.next();

                 while (tableHasRows) {

                     // store the returned results in the returnValue hashtable

                     String key = rs.getString(1);

                     String element = rs.getString(2);

                     if(element != null){

                         returnValue.put(key, element);

                     } else {

                         logMetacat.info("Null elment found ("

                         + "DBQuery.docidsInQueryresultTable)");

                     }

                     tableHasRows = rs.next();

                 }

                 rs.close();

                 pstmt.close();

             } catch (Exception e){

                 logMetacat.error("Error getting docids from "

                                          + "queryresult in "

                                          + "DBQuery.docidsInQueryresultTable: "

                                          + e.getMessage());

              }

         }

         return returnValue;

     }

   /**

    * Method to get id from xml_returnfield table

    * for a given query specification

    */

   private int returnfield_id;

   private int getXmlReturnfieldsTableId(QuerySpecification qspec,

                                           DBConnection dbconn){

       int id = -1;

       int count = 1;

       PreparedStatement pstmt = null;

       ResultSet rs = null;

       String returnfield = qspec.getSortedReturnFieldString();

       // query for finding the id from xml_returnfield

       String query = "SELECT returnfield_id, usage_count FROM xml_returnfield "

            + "WHERE returnfield_string LIKE ?";

       logMetacat.info("ReturnField Query:" + query);

       try {

           // prepare and run the query

           pstmt = dbconn.prepareStatement(query);

           pstmt.setString(1,returnfield);

           dbconn.increaseUsageCount(1);

           pstmt.execute();

           rs = pstmt.getResultSet();

           boolean tableHasRows = rs.next();

           // if record found then increase the usage count

           // else insert a new record and get the id of the new record

           if(tableHasRows){

               // get the id

               id = rs.getInt(1);

               count = rs.getInt(2) + 1;

               rs.close();

               pstmt.close();

               // increase the usage count

               query = "UPDATE xml_returnfield SET usage_count ='" + count

                   + "' WHERE returnfield_id ='"+ id +"'";

               logMetacat.info("ReturnField Table Update:"+ query);

               pstmt = dbconn.prepareStatement(query);

               dbconn.increaseUsageCount(1);

               pstmt.execute();

               pstmt.close();

           } else {

               rs.close();

               pstmt.close();

               // insert a new record

               query = "INSERT INTO xml_returnfield (returnfield_string, usage_count)"

                   + "VALUES (?, '1')";

               logMetacat.info("ReturnField Table Insert:"+ query);

               pstmt = dbconn.prepareStatement(query);

               pstmt.setString(1, returnfield);

               dbconn.increaseUsageCount(1);

               pstmt.execute();

               pstmt.close();

               // get the id of the new record

               query = "SELECT returnfield_id FROM xml_returnfield "

                   + "WHERE returnfield_string LIKE ?";

               logMetacat.info("ReturnField query after Insert:" + query);

               pstmt = dbconn.prepareStatement(query);

               pstmt.setString(1, returnfield);

               dbconn.increaseUsageCount(1);

               pstmt.execute();

               rs = pstmt.getResultSet();

               if(rs.next()){

                   id = rs.getInt(1);

               } else {

                   id = -1;

               }

               rs.close();