Project

General

Profile

« Previous | Next » 

Revision 2421

Added by sgarg over 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

View differences:

src/edu/ucsb/nceas/metacat/DBQuery.java
375 375
      if (out == null)
376 376
      {
377 377
        // for html page, we put everything into one page
378
        offset = 900;
378
        offset =
379
            (new Integer(MetaCatUtil.getOption("web_resultsetsize"))).intValue();
379 380
      }
380 381
      else
381 382
      {
382 383
          offset =
383
              (new Integer(MetaCatUtil.getOption("resultsetsize"))).intValue();
384
              (new Integer(MetaCatUtil.getOption("app_resultsetsize"))).intValue();
384 385
      }
386

  
385 387
      int count = 0;
386 388
      int index = 0;
387 389
      Hashtable docListResult = new Hashtable();
......
613 615
                                       DBConnection dbconn, boolean useXMLIndex)
614 616
                                       throws Exception
615 617
   {
616
     //add return field
617
     partOfDoclist = addRetrunfield(partOfDoclist, qspec, user, groups,
618

  
619
     // get the return field and check if there is a record in xml_returnfield
620
     int returnfield_id = getXmlReturnfieldsTableId(qspec, dbconn);
621
     if(returnfield_id < 0){
622
         MetaCatUtil.debugMessage("Error in getting returnfield id from"
623
                                  + "xml_returnfield table", 20);
624
     }
625

  
626
     // get the hashtable containing the docids that already in the
627
     // xml_queryresult table
628
     MetaCatUtil.debugMessage("size of partOfDoclist before"
629
                             + " docidsInQueryresultTable(): "
630
                             + partOfDoclist.size() , 50);
631
     Hashtable queryresultDocList = docidsInQueryresultTable(returnfield_id,
632
                                                        partOfDoclist, dbconn);
633

  
634
     // remove the keys in queryresultDocList from partOfDoclist
635
     Enumeration _keys = queryresultDocList.keys();
636
     while (_keys.hasMoreElements()){
637
         partOfDoclist.remove(_keys.nextElement());
638
     }
639
     MetaCatUtil.debugMessage("size of partOfDoclist after"
640
                             + " docidsInQueryresultTable(): "
641
                             + partOfDoclist.size() , 50);
642

  
643
     //add return fields for the documents in partOfDoclist
644
     partOfDoclist = addReturnfield(partOfDoclist, qspec, user, groups,
618 645
                                        dbconn, useXMLIndex );
619
     //add relationship part part docid list
646
     //add relationship part part docid list for the documents in partOfDocList
620 647
     partOfDoclist = addRelationship(partOfDoclist, qspec, dbconn, useXMLIndex);
621
     // send the completed search item to output and store it in a buffer
648

  
649

  
650
     Enumeration keys = partOfDoclist.keys();
622 651
     String key = null;
623 652
     String element = null;
624
     Enumeration keys = partOfDoclist.keys();
653
     String query = null;
654
     int offset = (new Integer(MetaCatUtil
655
                               .getOption("queryresult_string_length")))
656
                               .intValue();
625 657
     while (keys.hasMoreElements())
626 658
     {
627
           key = (String) keys.nextElement();
628
           element = (String)partOfDoclist.get(key);
629
           // A string with element
630
           String xmlElement = "  <document>" + element + "</document>";
631
           //send single element to output
632
           if (out != null)
633
           {
659
         key = (String) keys.nextElement();
660
         element = (String)partOfDoclist.get(key);
661

  
662
         if(element != null && element.length() < offset){
663
             query = "INSERT INTO xml_queryresult (returnfield_id, docid, "
664
                 + "queryresult_string) VALUES ('" + returnfield_id
665
                 + "', '" + key + "', '" + element + "')";
666
             PreparedStatement pstmt = null;
667
             pstmt = dbconn.prepareStatement(query);
668
             dbconn.increaseUsageCount(1);
669
             pstmt.execute();
670
             pstmt.close();
671
         }
672

  
673
         // A string with element
674
         String xmlElement = "  <document>" + element + "</document>";
675

  
676
         //send single element to output
677
         if (out != null)
678
         {
634 679
             out.println(xmlElement);
680
         }
681
         resultset.append(xmlElement);
682
     }//while
683

  
684

  
685
     keys = queryresultDocList.keys();
686
     while (keys.hasMoreElements())
687
     {
688
         key = (String) keys.nextElement();
689
         element = (String)queryresultDocList.get(key);
690
         // A string with element
691
         String xmlElement = "  <document>" + element + "</document>";
692
         //send single element to output
693
         if (out != null)
694
         {
695
             out.println(xmlElement);
696
         }
697
         resultset.append(xmlElement);
698
     }//while
699

  
700
     return resultset;
701
 }
702

  
703
   /**
704
    * Get the docids already in xml_queryresult table and corresponding
705
    * queryresultstring as a hashtable
706
    */
707
   private Hashtable docidsInQueryresultTable(int returnfield_id,
708
                                              Hashtable partOfDoclist,
709
                                              DBConnection dbconn){
710

  
711
         Hashtable returnValue = new Hashtable();
712
         PreparedStatement pstmt = null;
713
         ResultSet rs = null;
714

  
715
         // get partOfDoclist as string for the query
716
         Enumeration keylist = partOfDoclist.keys();
717
         StringBuffer doclist = new StringBuffer();
718
         while (keylist.hasMoreElements())
719
         {
720
             doclist.append("'");
721
             doclist.append((String) keylist.nextElement());
722
             doclist.append("',");
723
         }//while
724

  
725

  
726
         if (doclist.length() > 0)
727
         {
728
             doclist.deleteCharAt(doclist.length() - 1); //remove the last comma
729

  
730
             // the query to find out docids from xml_queryresult
731
             String query = "select docid, queryresult_string from "
732
                          + "xml_queryresult where returnfield_id = " +
733
                          returnfield_id +" and docid in ("+ doclist + ")";
734
             MetaCatUtil.debugMessage("Query to get docids from xml_queryresult:"
735
                                      + query, 50);
736

  
737
             try {
738
                 // prepare and execute the query
739
                 pstmt = dbconn.prepareStatement(query);
740
                 dbconn.increaseUsageCount(1);
741
                 pstmt.execute();
742
                 rs = pstmt.getResultSet();
743
                 boolean tableHasRows = rs.next();
744
                 while (tableHasRows) {
745
                     // store the returned results in the returnValue hashtable
746
                     String key = rs.getString(1);
747
                     String element = rs.getString(2);
748

  
749
                     if(element != null){
750
                         returnValue.put(key, element);
751
                     } else {
752
                         MetaCatUtil.debugMessage("Null elment found ("
753
                         + "DBQuery.docidsInQueryresultTable)", 50);
754
                     }
755
                     tableHasRows = rs.next();
756
                 }
757
                 rs.close();
758
                 pstmt.close();
759
             } catch (Exception e){
760
                 MetaCatUtil.debugMessage("Error getting docids from "
761
                                          + "queryresult in "
762
                                          + "DBQuery.docidsInQueryresultTable: "
763
                                          + e.getMessage(), 20);
764
              }
765
         }
766
         return returnValue;
767
     }
768

  
769

  
770
   /**
771
    * Method to get id from xml_returnfield table
772
    * for a given query specification
773
    */
774
   private int getXmlReturnfieldsTableId(QuerySpecification qspec,
775
                                           DBConnection dbconn){
776
       int id = -1;
777
       PreparedStatement pstmt = null;
778
       ResultSet rs = null;
779
       String returnfield = qspec.getSortedReturnFieldString();
780

  
781
       // query for finding the id from xml_returnfield
782
       String query = "select returnfield_id, usage_count from xml_returnfield "
783
           + "where returnfield_string like '" + returnfield +"'";
784
       MetaCatUtil.debugMessage("ReturnField Query:" + query, 50);
785

  
786
       try {
787
           // prepare and run the query
788
           pstmt = dbconn.prepareStatement(query);
789
           dbconn.increaseUsageCount(1);
790
           pstmt.execute();
791
           rs = pstmt.getResultSet();
792
           boolean tableHasRows = rs.next();
793

  
794
           // if record found then increase the usage count
795
           // else insert a new record and get the id of the new record
796
           if(tableHasRows){
797
               // get the id
798
               id = rs.getInt(1);
799
               int count = rs.getInt(2) + 1;
800
               rs.close();
801
               pstmt.close();
802

  
803
               // increase the usage count
804
               query = "UPDATE xml_returnfield SET usage_count ='" + count
805
                   + "' WHERE returnfield_id ='"+ id +"'";
806
               MetaCatUtil.debugMessage("ReturnField Table Update:"+ query, 50);
807

  
808
               pstmt = dbconn.prepareStatement(query);
809
               dbconn.increaseUsageCount(1);
810
               pstmt.execute();
811
               pstmt.close();
812

  
813
           } else {
814
               rs.close();
815
               pstmt.close();
816

  
817
               // insert a new record
818
               query = "INSERT INTO xml_returnfield (returnfield_string, usage_count)"
819
                   + "VALUES ('" + returnfield + "', '1')";
820
               MetaCatUtil.debugMessage("ReturnField Table Insert:"+ query, 50);
821
               pstmt = dbconn.prepareStatement(query);
822
               dbconn.increaseUsageCount(1);
823
               pstmt.execute();
824
               pstmt.close();
825

  
826
               // get the id of the new record
827
               query = "select returnfield_id from xml_returnfield "
828
                   + "where returnfield_string like '" + returnfield +"'";
829
               MetaCatUtil.debugMessage("ReturnField query after Insert:"
830
                                        + query, 50);
831
               pstmt = dbconn.prepareStatement(query);
832
               dbconn.increaseUsageCount(1);
833
               pstmt.execute();
834
               rs = pstmt.getResultSet();
835
               if(rs.next()){
836
                   id = rs.getInt(1);
837
               } else {
838
                   id = -1;
839
               }
840
               rs.close();
841
               pstmt.close();
635 842
           }
636
           resultset.append(xmlElement);
637
      }//while
638 843

  
639
      return resultset;
844
       } catch (Exception e){
845
           MetaCatUtil.debugMessage("Error getting id from xml_returnfield in "
846
                                     + "DBQuery.getXmlReturnfieldsTableId: "
847
                                     + e.getMessage(), 20);
848
           id = -1;
849
       }
850
       return id;
640 851
   }
641 852

  
642 853

  
643 854
    /*
644 855
     * A method to add return field to return doclist hash table
645 856
     */
646
    private Hashtable addRetrunfield(Hashtable docListResult,
857
    private Hashtable addReturnfield(Hashtable docListResult,
647 858
                                      QuerySpecification qspec,
648 859
                                      String user, String[]groups,
649 860
                                      DBConnection dbconn, boolean useXMLIndex )

Also available in: Unified diff