Changeset 3977

Timestamp:

03/06/08 14:10:22 (11 years ago)

Author:

cbyrom

Message:

Tidy up of searchAgent code - including improving sql construction to
properly handle spatiotemporal searches + simplify construction of vector searches + avoid
duplicate results sets

File:

• TI01-discovery/branches/ws-Discovery2-upgrade/src/ndg/services/discovery/SearchAgent.java (modified) (15 diffs)

TI01-discovery/branches/ws-Discovery2-upgrade/src/ndg/services/discovery/SearchAgent.java

@@ -4 +4 @@
 import java.util.Iterator;
 import java.util.Vector;
-import java.util.Properties;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
 import java.math.BigInteger; // needed because xmlbeans uses this for xsd:positiveInteger
 import java.math.BigDecimal; // needed because xmlbeans uses this for xsd:decimal
 import java.util.Calendar;  // needed because xmlbeans uses this for xsd:date
 import java.util.logging.Logger;
-import java.util.regex.Matcher;
-import java.text.DecimalFormat;
-import java.text.SimpleDateFormat;
 
 /**
@@ -20 +18 @@
 public class SearchAgent 
 {
-
     private static Logger logger = Logger.getLogger(SearchAgent.class.getName());
 
-    private static Properties properties = new ServiceProperties().getProperties();
-
         // Internal defaults
-    
     private String termType = null;
     
         // Initialise input parameters, setting values to internal defaults
-//      private int resultId = 0;
         private BigInteger start = new BigInteger("0"); // what row of results to start retrieval from
         private BigInteger howMany = new BigInteger("30");      // how many results to display in result set
@@ -36 +29 @@
         private String orderByField = null;     // representation used in WSDL e.g. "date", "dataCentre"
         private String orderByDirection = "ASC";        //default orderBy direction
-        // TODO ...add orderByDirection (and to WSDL)
 
         Vector<String> scopes = new Vector<String>();
-        private String spatialOperator = "overlaps";
+        private String spatialOperator = DiscoveryServiceSkeleton.OVERLAPS_OPERATOR_TYPE;
 
         private BigDecimal limitWest;
@@ -51 +43 @@
         
         private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
-
-        // info on the DB table structure
-        private final String ORIGINAL_DOCUMENT_TABLE ="ORIGINAL_DOCUMENT";
-        private final String SPATIAL_DATA_TABLE ="SPATIAL_DATA";
-        private final String TEMPORAL_DATA_TABLE ="TEMPORAL_DATA";
-        private final String SPATIAL_TEMPORAL_DATA_TABLE ="SPATIAL_TEMPORAL_DATA";
-
-        // sql fragments to use when creating sql command
-        private final String WHERE_STATEMENT = " WHERE ";
-        private final String LIMIT_STATEMENT = " LIMIT ";
-        private final String OFFSET_STATEMENT = " OFFSET ";
-        private final String ORDERBY_STATEMENT = " ORDER BY ";
         
         /**
@@ -145 +125 @@
         public void addNewScope(String s)
         {
+                s = s.replace("_", DiscoveryServiceSkeleton.UNDERSCORE_REPLACEMENT);
                 this.scopes.add(s);
         }
@@ -158 +139 @@
 
         /**
-         * Sets north bound of BoundingBox.
-         * <p>Value should be BigDecimal.
-         * @param f value of north bound of search box
-         */
-
-        /**
-         * Returns scopes as space-separated String
-         * @return String containing scope keywords separated by spaces
+         * Returns scopes as '&' String - to allow an 'and' search via tsquery
+         * 
+         * @return String containing scope keywords separated by '&'s
          */
         public String getScopesAsString()
         {
-                String scopesStr = "";
+                StringBuilder scopesStr = new StringBuilder();
                 Iterator<String> it = this.scopes.iterator();
                 while ( it.hasNext() )
                 {
-                        scopesStr += it.next();
-                }
-                System.out.println("got: " + scopesStr);
-                System.out.println("alternatively: " + this.scopes.toString());
-                return scopesStr;
+                        if (scopesStr.length() > 0)
+                                scopesStr.append(" & ");
+                        
+                        scopesStr.append(it.next());
+                }
+                return scopesStr.toString();
         }
 
@@ -324 +301 @@
          * Executes the search and returns a SearchSummary object
          * @return SearchSummary object containing result of search
-         */
-        public SearchSummary doSearch() 
+         * @throws DiscoveryDBException 
+         */
+        public SearchSummary doSearch() throws DiscoveryDBException 
         {
 
                 SearchSummary result = null;
 
-                try
-                {
-                        // firstly prepare the search query string
-                        String sqlQuery = constructSearchQuery();
+                // firstly prepare the search query string
+                String sqlQuery = constructSearchQuery();
+                
+                // now run the query using the db client configured appropriately
+                InterfaceDBClient client = DBProperties.setupDBClient();
+                String[][] results = client.runQuery(sqlQuery);
+                client.closeConnection();
+                
+                // now extract the results data
+                result = processResults(results);
                         
-                        // now run the query using the db client configured appropriately
-                        InterfaceDBClient client = setupDBClient();
-                        String[][] results = client.runQuery(sqlQuery);
-                        
-                        // now extract the results data
-                        result = processResults(results);
-                        
-                }
-                catch (Exception e)
-                {
-                        result.setStatus(false);
-                        result.setStatusMessage("An error ocurred - details : " + e.toString());
-                }
-
                 return result;
         }
@@ -364 +334 @@
                 logger.info("Extracting results");
                 SearchSummary result = new SearchSummary();
-                result.setHits(results.length); // NB ensure this is 0 if not data returned
-                
-                if ( result.getHits() > 0)
+                result.setHits(0); // NB ensure this is 0 if not data returned
+                ArrayList<String> docs = new ArrayList<String>();
+                
+                if (results.length > 0)
                 {
                         Vector<Hashtable<String, String>> documents = new Vector<Hashtable<String, String>>();
@@ -372 +343 @@
                         {
                                 Hashtable<String, String> thisDoc = new Hashtable<String, String>();
-                                thisDoc.put("name", results[i][0]);
-                                
-                                // TODO: these next two are not used anywhere - remove or use?
-                                thisDoc.put("matches", String.valueOf(1));
-                                thisDoc.put("position", String.valueOf(i));
-                                documents.add( thisDoc );
+                                // avoid duplicate results - NB, this could be done using the DISTINCT SQL keyword but
+                                // this would require the 'order by' clause to change so would necessitate a much more
+                                // complex sql query construction
+                                if (! docs.contains(results[i][0]))
+                                {
+                                        logger.info("Found matching document - adding this to results");
+                                        docs.add(results[i][0]);
+                                        thisDoc.put("name", results[i][0]);
+                                        
+                                        // TODO: these next two are not used anywhere - remove or use?
+                                        thisDoc.put("matches", String.valueOf(1));
+                                        thisDoc.put("position", String.valueOf(i));
+                                        documents.add( thisDoc );
+                                }
                         }
                         result.setStatus( true );
                         result.setStatusMessage( "Success" );
                         result.setDocuments( documents );
+                        result.setHits(docs.size()); // NB ensure this is 0 if not data returned
                         logger.info("Results added to search summary");
                 }
@@ -396 +376 @@
 
         /**
-         * Set up a DBClient object to use with running the search - getting the config details
-         * from the properties file
-         * 
-         * @return DBClient ready to accept SQL queries to run
-         * @throws DiscoveryDBException 
-         */
-        private InterfaceDBClient setupDBClient() throws DiscoveryDBException 
-        {
-                logger.info("Setting up DBClient using config info from properties file...");
-                String connectionString = properties.getProperty("jdbc.uri");
-                String userName = properties.getProperty("jdbc.username");
-                String pw = properties.getProperty("jdbc.password");
-                
-                InterfaceDBClient client = new PostgresDBClient(connectionString, userName, pw);
-                logger.info("DBClient set up for use with Discovery service.");
-                return client;
-        }
-
-        /**
          * Set up a SQL Select query with the input search parameters
          * 
@@ -425 +386 @@
                 // NB, we use the postgres text search function to do term searches
                 StringBuffer fromSqlCmd = new StringBuffer("SELECT original_document_filename FROM " + 
-                                ORIGINAL_DOCUMENT_TABLE + " ");
+                                DBProperties.ORIGINAL_DOCUMENT_TABLE + " ");
                 StringBuffer whereSqlCmd = new StringBuffer();
                 
+                String termColumn = null;
                 if (Utilities.isStringDefined(this.getTerm()))
                 {
+                        termColumn = "document_ts_vector"; // default column to use - i.e. a full text search
+                        // NB, if no term type provided, assume full text search
                         if (Utilities.isStringDefined(this.getTermType()))
+                        {
                                 logger.info("Adjusting query for " + this.getTermType() + " type search");
-                        
-                        // NB, if no term type provided, assume full text search
-                        if (! Utilities.isStringDefined(this.getTermType()) || 
-                                        this.getTermType().equals(DiscoveryServiceSkeleton.FULL_TEXT_TERM_TYPE))
-                        {
-                                appendWhereClause(whereSqlCmd, " to_tsquery('" + this.term + "') @@ ts_vector ");
-                        }
-                        else if (this.getTermType().equals(DiscoveryServiceSkeleton.AUTHOR_TERM_TYPE))
-                        {
-                                //TODO: IMPLEMENT datamodel side...
-                                appendWhereClause(whereSqlCmd, " AUTHOR LIKE %" + this.term + "% ");
-                        }
-                        else if (this.getTermType().equals(DiscoveryServiceSkeleton.PARAMETER_TERM_TYPE))
-                        {
-                                //TODO: IMPLEMENT datamodel side...
-                                appendWhereClause(whereSqlCmd, " PARAMETERS LIKE %" + this.term + "% ");
-                        }
-                
-                }
-
-                // Substitute values in template xquery string
-/*                      xqueryStr = xqueryStr.replaceFirst("__subst_term__", this.term);
-                xqueryStr = xqueryStr.replaceFirst("__subst_scope__", this.getScopesAsString() );
-
-                
-*/                      
+                                
+                                if (this.getTermType().equals(DiscoveryServiceSkeleton.AUTHOR_TERM_TYPE))
+                                {
+                                        termColumn = "authors_ts_vector ";
+                                }
+                                else if (this.getTermType().equals(DiscoveryServiceSkeleton.PARAMETER_TERM_TYPE))
+                                {
+                                        termColumn = "parameters_ts_vector";
+                                }
+                        }
+                        fromSqlCmd.append(", to_tsquery('" + this.term + "') AS query ");
+                        appendWhereClause(whereSqlCmd, "query @@ COALESCE(" + termColumn + ", '') ");
+                }
+
+                if (this.getScopes() != null && this.getScopes().size() > 0)
+                {
+                        logger.info("Adding scopes data to query");
+                        appendWhereClause(whereSqlCmd, " to_tsquery('" + this.getScopesAsString() + "') @@ scope_ts_vector ");
+                }
+
                 // Construct the search clause for spatio temporal search, if necessary
                 if (isSpatialSearch())
@@ -464 +423 @@
                         // set up the bounding box geometry for the search
                         String bbox = "SetSRID('BOX3D(" + this.getLimitWest().floatValue() + " " + this.getLimitSouth().floatValue() +
-                                ", " + this.getLimitEast().floatValue() + " " + this.getLimitNorth().floatValue() + ")’::box3d,-1)";
-
+                                ", " + this.getLimitEast().floatValue() + " " + this.getLimitNorth().floatValue() + ")'::box3d, 4326)";
+
+                        fromSqlCmd.append(", " + DBProperties.SPATIAL_DATA_TABLE);
+                        String spatialPart = DBProperties.SPATIAL_DATA_TABLE + ".geometry ";
                         if (spatialOperator.equals(DiscoveryServiceSkeleton.OVERLAPS_OPERATOR_TYPE))
                         {
-                                appendWhereClause(whereSqlCmd, " geometry && " + bbox + " ");
+                                spatialPart += "&& " + bbox + " ";
                         }
                         else if ( spatialOperator.equals(DiscoveryServiceSkeleton.WITHIN_OPERATOR_TYPE) )
                         {
-                                appendWhereClause(whereSqlCmd, " geometry @ " + bbox + " ");
+                                spatialPart +="@ " + bbox + " ";
                         }
                         else if ( spatialOperator.equals("doesNotOverlap") )
                         {
-                                appendWhereClause(whereSqlCmd, "NOT geometry && " + bbox + " ");
-                        } 
+                                spatialPart = "NOT " + spatialPart + "&& " + bbox + " ";
+                        }
+                        appendWhereClause(whereSqlCmd, spatialPart);
                 }
 
@@ -483 +445 @@
                 {
                         logger.info("Adding temporal data to query");
-                        fromSqlCmd.append(", " + TEMPORAL_DATA_TABLE + ", " + SPATIAL_TEMPORAL_DATA_TABLE + "");
-                        appendWhereClause(whereSqlCmd, " " + TEMPORAL_DATA_TABLE + ".start_time, " +
-                                        TEMPORAL_DATA_TABLE + ".end_time) OVERLAPS (DATE '" + 
+                        fromSqlCmd.append(", " + DBProperties.TEMPORAL_DATA_TABLE);
+                        appendWhereClause(whereSqlCmd, " (" + DBProperties.TEMPORAL_DATA_TABLE + ".start_time, " +
+                                        DBProperties.TEMPORAL_DATA_TABLE + ".end_time) OVERLAPS (DATE '" + 
                                 this.sdf.format(this.getDateRangeStart().getTime()) + 
-                                "', DATE '" + this.sdf.format(this.getDateRangeEnd().getTime() ) + "' AND " +
-                                TEMPORAL_DATA_TABLE + ".temporal_data_id == " +
-                                SPATIAL_TEMPORAL_DATA_TABLE + ".temporal_data_id");
+                                "', DATE '" + this.sdf.format(this.getDateRangeEnd().getTime() ) + "') AND " +
+                                DBProperties.TEMPORAL_DATA_TABLE + ".temporal_data_id = " +
+                                DBProperties.SPATIAL_TEMPORAL_DATA_TABLE + ".temporal_data_id");
                 }
                 
@@ -496 +458 @@
                 if (isTemporalSearch() || isSpatialSearch())
                 {
-                        whereSqlCmd.append(" AND " + SPATIAL_TEMPORAL_DATA_TABLE + ".original_document_id == " +
-                                        ORIGINAL_DOCUMENT_TABLE + ".original_document_id");
-                }
-                
-                // add order by, and direction, if required
-                if (this.getOrderByField() != null)
-                {
-                        logger.info("Adding ordering info to query");
-                        // orderByField contains $ chars etc so needs special treatment to 
-                        // avoid "java.lang.IllegalArgumentException: Illegal group reference" when replacing
-                        // TODO: is this true now?
-                        String orderByFieldReplace = Matcher.quoteReplacement(this.getOrderByField());
-                        whereSqlCmd.append(ORDERBY_STATEMENT + orderByFieldReplace + " " + this.getOrderByDirection());
+                        fromSqlCmd.append(", " + DBProperties.SPATIAL_TEMPORAL_DATA_TABLE);
+                        whereSqlCmd.append(" AND " + DBProperties.SPATIAL_TEMPORAL_DATA_TABLE + ".original_document_id = " +
+                                        DBProperties.ORIGINAL_DOCUMENT_TABLE + ".original_document_id");
                 }
                 
                 // add the WHERE statement, if required
                 if (whereSqlCmd.length() > 0)
-                        whereSqlCmd.insert(0, WHERE_STATEMENT);
+                        whereSqlCmd.insert(0, DBProperties.WHERE_STATEMENT);
+                
+                // add order by, and direction, if required
+                logger.info("Adding ordering info to query");
+                if (this.getOrderByField() != null)
+                {
+                        logger.info("- adding specified order by parameter");
+                        whereSqlCmd.append(DBProperties.ORDERBY_STATEMENT + this.getOrderByField() + " " + this.getOrderByDirection());
+                }
+                else if (termColumn != null)
+                {
+                        // use natural ordering of results according to statistics worked out by the text search facility, if appropriate
+                        logger.info("- order according to rank of search result");
+                        whereSqlCmd.append(DBProperties.ORDERBY_STATEMENT + " ts_rank(" + termColumn + ", query) " + this.getOrderByDirection());
+                }
                 
                 // lastly, add the size limit and offset of the returned data set
                 logger.info("Adding size limit and offset of results set");
-                whereSqlCmd.append(OFFSET_STATEMENT + this.getStart());
-                whereSqlCmd.append(LIMIT_STATEMENT + this.getHowMany());
+                whereSqlCmd.append(DBProperties.OFFSET_STATEMENT + this.getStart());
+                whereSqlCmd.append(DBProperties.LIMIT_STATEMENT + this.getHowMany());
                 
                 String fullCmd = fromSqlCmd.toString() + whereSqlCmd.toString() + ";";
@@ -580 +546 @@
                         this.getLimitNorth() != null && this.getLimitSouth() != null)
                 {
-                        if (this.getLimitEast().intValue() != 180 && this.getLimitWest().intValue() != -180 &&
-                                        this.getLimitNorth().intValue() != 90 && this.getLimitSouth().intValue() != -90)
-                        {
-                                logger.info("Valid spatial data specified");
-                                return true;
-                        }
-                        logger.info("Spatial data is equivalent to global search - will ignore in search");
+                        if (this.getLimitEast().intValue() == 180 && this.getLimitWest().intValue() == -180 &&
+                                        this.getLimitNorth().intValue() == 90 && this.getLimitSouth().intValue() == -90)
+                        {
+                                logger.info("Spatial data is equivalent to global search - will ignore in search");
+                                return false;
+                        }
+                        logger.info("Valid spatial data specified");
+                        return true;
                 }
                 logger.info("No valid spatial data specified");
                 return false;
         }
-
-
-        static String xqueryStrTemplate = 
-"xquery version \"1.0\"; \n"+
-"import module namespace voclib=\"http://ndg.nerc.ac.uk/xquery/lib/vocab\" at \"xmldb:exist:///db/xqueryLib/Vocabs/vocab_xquery_lib.xquery\"; \n"+
-"declare namespace sql=\"http://exist-db.org/xquery/sql\"; \n"+
-"declare namespace moles=\"http://ndg.nerc.ac.uk/moles\"; \n"+
-"declare function local:getScope() as xs:string { \n"+
-"   \"__subst_scope__\" \n"+
-"}; \n"+
-"declare function local:getSpatioTempWhereClause() as xs:string { \n"+
-"   \"__subst_spatioTempWhereClause__\" \n"+
-"}; \n"+
-"declare function local:doSpatioTemp() as xs:boolean { \n"+
-"   __subst_doSpatioTemp__ \n"+
-"}; \n"+
-"declare function local:doTerm() as xs:boolean { \n"+
-"   __subst_doTerm__ \n"+
-"}; \n"+
-"declare function local:doOrderBy() as xs:boolean { \n"+
-"   __subst_doOrderBy__ \n"+
-"}; \n"+
-
-"declare function local:orderBy($arg1 as item()* )as item()* { \n"+
-"       if ( local:doOrderBy() ) then ( \n"+
-"               for $i in $arg1 \n"+
-"               let $coll := '/db/discovery/moles' \n"+
-"               let $thisdoc := $i \n"+
-"               let $name := string-join( ($coll , $thisdoc), '/' ) \n"+
-"               let $field := __subst_orderByField__ \n"+
-"               order by $field __subst_orderByDirection__ \n"+
-"               return $i \n"+
-"       ) \n"+
-"       else $arg1 \n"+
-"}; \n"+
-
-"declare function local:doScope() as xs:boolean { \n"+
-"   __subst_doScope__ \n"+
-"}; \n"+
-"declare function local:doSpatioTempQuery() as item()* { \n"+
-"   let $connection := sql:get-connection(\"org.postgresql.Driver\", \""+properties.getProperty("jdbc.uri")+"\", \""+properties.getProperty("jdbc.username")+"\", \""+properties.getProperty("jdbc.password")+"\") \n"+
-"   let $result := sql:execute($connection, fn:string-join( (\"select * from spatiotemp \", local:getSpatioTempWhereClause(),\";\"), \"\"), fn:true()) \n"+
-"   let $s_result := ( \n"+
-"       for $j in $result/sql:row/id/text() \n"+
-"       return $j \n"+
-"   ) \n"+
-"   return $s_result \n"+
-"}; \n"+
-"declare function local:doTermQuery() as item()* { \n"+
-"   (: Get result of full-text search :) \n"+
-"   let $result := __subst_term__ \n"+
-"   for $i in $result \n"+
-"      return util:document-name($i) \n"+
-"}; \n"+
-"declare function local:mergeResults($arg1 as item()*, $arg2 as item()*) as item()* { \n"+
-"   for $i in $arg1[.=$arg2] \n"+
-"   return $i \n"+
-"}; \n"+
-"declare function local:filterByScope($arg1 as item()*) as item()* { \n"+
-"   if ( local:doScope() ) then ( \n"+
-"      for $i in $arg1 \n"+
-"       let $coll := '/db/discovery/moles' \n"+
-"       let $thisdoc := $i \n"+
-"       let $name := string-join( ($coll , $thisdoc), '/' ) \n"+
-"       let $molesdoc := document( $name ) \n"+
-"        where document($name)/moles:dgMetadata/moles:dgMetadataRecord/moles:dgStructuredKeyword[moles:dgValidTerm &= local:getScope() and voclib:spot-vocab($voclib:ndg_data_provider_vocab, moles:dgValidTermID/moles:ParentListID)] \n"+
-"      return $i \n"+
-"   ) \n"+
-"   else $arg1 \n"+
-"}; \n"+
-
-"declare function local:buildResult() as item()* { \n"+
-" \n"+
-"   let $termResult := ( \n"+
-"      if ( local:doTerm() ) then local:doTermQuery() \n"+
-"      else () \n"+
-"   ) \n"+
-"   let $spatioResult := ( \n"+
-"      if ( local:doSpatioTemp() ) then local:doSpatioTempQuery() \n"+
-"      else () \n"+
-"   ) \n"+
-"   (: populate $seq with appropriate combination of result, depending on search options :) \n"+
-"   let $seq := ( \n"+
-   
-"      if ( local:doTerm() ) then ( \n"+
-"         if ( local:doSpatioTemp() ) then ( \n"+
-"            local:mergeResults($termResult, $spatioResult) \n"+
-"         ) \n"+
-"         else ( \n"+
-"            $termResult \n"+
-"         ) \n"+
-"      ) \n"+
-"      else ( \n"+
-"         if ( local:doSpatioTemp() ) then ( \n"+
-"            $spatioResult \n"+
-"         ) \n"+
-"         else ( \n"+
-"            () \n"+
-"         ) \n"+
-"      ) \n"+
-"   ) \n"+
-"   for $i in local:orderBy( local:filterByScope( $seq ) ) \n"+
-"   return $i \n"+
-"}; \n"+
-"for $i in local:buildResult() \n"+
-"return <document>{$i}</document> \n";
-
-
-
 }