/src/edu/ucsb/nceas/metacat/DBQuery.java - Diff - Metacat - Ecoinformatics Redmine

« Previous | Next »

Revision 2421

Added by sgarg about 19 years ago

Changes for entering new records into xml_queryresult:

1. Added a function which checks if there is a record exsists in xml_returnfield table for a given combination of return fields. If a record is found, the id for the record is returned. otherwise a new record is created in the xml_returnfield table and the id of the new record is returned.

2. Added another function which check if for a given set of docid and returnfields, record already exist in xml_queryresult table. if records do exsist, they are returned as part of a hashtable

3. Modified the code for function handleSubsetResult:
-> get the id of the record from the xml_returnfield table for given set of return fields.
-> get the hashtable containing the docids that already in the xml_queryresult table
-> remove the keys in the above generated hashtable from the old hashtable
-> process the remaining keys in the old hashtable in the old fashion
-> add these entries to the xml_queryresult table
-> generate the result from the two hashtables

           if (out == null)
+          {
             // for html page, we put everything into one page
             offset = 900;
             offset =
                 (new Integer(MetaCatUtil.getOption("web_resultsetsize"))).intValue();
+          }
           else
+          {
               offset =
                   (new Integer(MetaCatUtil.getOption("resultsetsize"))).intValue();
                   (new Integer(MetaCatUtil.getOption("app_resultsetsize"))).intValue();
+          }
           int count = 0;
           int index = 0;
           Hashtable docListResult = new Hashtable();
-...
                                            DBConnection dbconn, boolean useXMLIndex)
                                            throws Exception
+       {
          //add return field
          partOfDoclist = addRetrunfield(partOfDoclist, qspec, user, groups,
          // get the return field and check if there is a record in xml_returnfield
          int returnfield_id = getXmlReturnfieldsTableId(qspec, dbconn);
          if(returnfield_id < 0){
              MetaCatUtil.debugMessage("Error in getting returnfield id from"
                                       + "xml_returnfield table", 20);
+         }
          // get the hashtable containing the docids that already in the
          // xml_queryresult table
          MetaCatUtil.debugMessage("size of partOfDoclist before"
                                  + " docidsInQueryresultTable(): "
                                  + partOfDoclist.size() , 50);
          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());
+         }
          MetaCatUtil.debugMessage("size of partOfDoclist after"
                                  + " docidsInQueryresultTable(): "
                                  + partOfDoclist.size() , 50);
          //add return fields for the documents in partOfDoclist
          partOfDoclist = addReturnfield(partOfDoclist, qspec, user, groups,
                                             dbconn, useXMLIndex );
          //add relationship part part docid list
          //add relationship part part docid list for the documents in partOfDocList
          partOfDoclist = addRelationship(partOfDoclist, qspec, dbconn, useXMLIndex);
          // send the completed search item to output and store it in a buffer
          Enumeration keys = partOfDoclist.keys();
          String key = null;
          String element = null;
          Enumeration keys = partOfDoclist.keys();
          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);
                // A string with element
                String xmlElement = "  <document>" + element + "</document>";
                //send single element to output
                if (out != null)
+               {
              key = (String) keys.nextElement();
              element = (String)partOfDoclist.get(key);
              if(element != null && element.length() < offset){
                  query = "INSERT INTO xml_queryresult (returnfield_id, docid, "
                      + "queryresult_string) VALUES ('" + returnfield_id
                      + "', '" + key + "', '" + element + "')";
                  PreparedStatement pstmt = null;
                  pstmt = dbconn.prepareStatement(query);
                  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 + ")";
                  MetaCatUtil.debugMessage("Query to get docids from xml_queryresult:"
                                           + query, 50);
                  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 {
                              MetaCatUtil.debugMessage("Null elment found ("
                              + "DBQuery.docidsInQueryresultTable)", 50);
+                         }
                          tableHasRows = rs.next();
+                     }
                      rs.close();
                      pstmt.close();
                  } catch (Exception e){
                      MetaCatUtil.debugMessage("Error getting docids from "
                                               + "queryresult in "
                                               + "DBQuery.docidsInQueryresultTable: "
                                               + e.getMessage(), 20);
+                  }
+             }
              return returnValue;
+         }
        /**
         * Method to get id from xml_returnfield table
         * for a given query specification
         */
        private int getXmlReturnfieldsTableId(QuerySpecification qspec,
                                                DBConnection dbconn){
            int id = -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 '" + returnfield +"'";
            MetaCatUtil.debugMessage("ReturnField Query:" + query, 50);
            try {
                // prepare and run the query
                pstmt = dbconn.prepareStatement(query);
                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);
                    int 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 +"'";
                    MetaCatUtil.debugMessage("ReturnField Table Update:"+ query, 50);
                    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 ('" + returnfield + "', '1')";
                    MetaCatUtil.debugMessage("ReturnField Table Insert:"+ query, 50);
                    pstmt = dbconn.prepareStatement(query);
                    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 '" + returnfield +"'";
                    MetaCatUtil.debugMessage("ReturnField query after Insert:"
                                             + query, 50);
                    pstmt = dbconn.prepareStatement(query);
                    dbconn.increaseUsageCount(1);
                    pstmt.execute();
                    rs = pstmt.getResultSet();
                    if(rs.next()){
                        id = rs.getInt(1);
                    } else {
                        id = -1;
+                   }
                    rs.close();
                    pstmt.close();
+               }
                resultset.append(xmlElement);
           }//while
           return resultset;
            } catch (Exception e){
                MetaCatUtil.debugMessage("Error getting id from xml_returnfield in "
                                          + "DBQuery.getXmlReturnfieldsTableId: "
                                          + e.getMessage(), 20);
                id = -1;
+           }
            return id;
+       }
         /*
          * A method to add return field to return doclist hash table
          */
         private Hashtable addRetrunfield(Hashtable docListResult,
         private Hashtable addReturnfield(Hashtable docListResult,
                                           QuerySpecification qspec,
                                           String user, String[]groups,
                                           DBConnection dbconn, boolean useXMLIndex )

Also available in: Unified diff

Project

General

Profile

Metacat

Revision 2421

Added by sgarg about 19 years ago