Changeset 3957

Timestamp:

29/05/08 12:11:28 (11 years ago)

Author:

cbyrom

Message:

Large refactor of existing codebase to replace usage of eXist DB with
postgres:

• adjust term search to use postgres DB
• simplify search agents - no need to separate these since their difference

is minimal and it will simplify codestructure doing this

• separate workflow into clearly defined methods
• remove unnecessary field variables and properly encapsulate others
• add logging using log4j framework
• extend code to allow searches to be ran without term or termtype set

Location:

TI01-discovery/branches/ws-Discovery2-upgrade/src/ndg/services/discovery

Files:

• AuthorSearchAgent.java (deleted)
• DiscoveryServiceSkeleton.java (modified) (6 diffs)
• FullTextSearchAgent.java (deleted)
• InterfaceDBClient.java (modified) (1 diff)
• ParameterSearchAgent.java (deleted)
• PostgresDBClient.java (modified) (1 diff)
• SearchAgent.java (modified) (15 diffs)
• ServiceProperties.java (modified) (1 diff)

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

@@ -8 +8 @@
 import java.util.Vector;
 import java.util.logging.Logger;
+
+import discoveryserviceapi.DoSearchDocument;
 
 /**
@@ -21 +23 @@
     private static final String[] LIST_NAMES = {"presentFormatList", "orderByFieldList", "scopeList",
         "termTypeList", "spatialOperatorList"};
+
+        // Constants representing the possible, valid values for the various input list types
+        public static final String FULL_TEXT_TERM_TYPE = "fullText";
+        public static final String AUTHOR_TERM_TYPE = "author";
+        public static final String PARAMETER_TERM_TYPE = "parameter";
+        public static final String OVERLAPS_OPERATOR_TYPE = "overlaps";
+        public static final String WITHIN_OPERATOR_TYPE = "within";
+        public static final String NO_OVERLAP_OPERATOR_TYPE = "doesNotOverlap";
+        
         /**
          * Performs the doFullTextSearch operation 
@@ -31 +42 @@
         { 
                 logger.info("doSearch() invoked");
-                discoveryserviceapi.DoSearchReturnDocument response = discoveryserviceapi.DoSearchReturnDocument.Factory.newInstance();
+                discoveryserviceapi.DoSearchReturnDocument response = 
+                        discoveryserviceapi.DoSearchReturnDocument.Factory.newInstance();
                 discoveryserviceapi.SearchReturnType responseContent = response.addNewDoSearchReturn();
 
-                // parse the request message
-                discoveryserviceapi.SearchType requestContent = request.getDoSearch();
-                SearchAgent agent = null;
-
-                if ( requestContent.getTermType().equals("fullText") )
-                {
-                        agent = new FullTextSearchAgent( requestContent.getTerm() );
-                }
-                else if ( requestContent.getTermType().equals("author") )
-                {
-                        agent = new AuthorSearchAgent( requestContent.getTerm() );
-                }
-                else if ( requestContent.getTermType().equals("parameter") )
-                {
-                        agent = new ParameterSearchAgent( requestContent.getTerm() );
-                }
-                else
-                {
-                        String errorMessage = "Invalid termType: please use getListNames to get the valid list term types";
-                        logger.warning(errorMessage);
-                        throw new UnsupportedOperationException(errorMessage);
-                }
-
-                if ( requestContent.getTerm().length() > 0 )
-                {
-                        logger.info("Term set - will do term search");
-                        agent.setDoTerm( true );
-                }
-
-                if ( requestContent.isSetStart() )
-                {
-                        logger.info("Start position of results set set to: " + requestContent.getStart());
-                        agent.setStart( requestContent.getStart() );
-                }
-                        
-                if ( requestContent.isSetHowMany() )
-                {
-                        logger.info("Result set size set to: " + requestContent.getHowMany());
-                        agent.setHowMany( requestContent.getHowMany() );
-                }
-
-                if ( requestContent.isSetOrderBy() )
-                {
-                        logger.info("Results ordered by: " + requestContent.getOrderBy());
-                        agent.setOrderBy( requestContent.getOrderBy().toString() );
-                        agent.setDoOrderBy( true );
-                        if ( requestContent.isSetOrderByDirection() )
-                        {
-                                logger.info("Results ordering direction: " + requestContent.getOrderByDirection().toString());
-                                agent.setOrderByDirection( requestContent.getOrderByDirection().toString() );
-                        }
-                }
-
-                if ( requestContent.sizeOfScopeArray() > 0)
-                {
-                        for (int i=0; i<requestContent.sizeOfScopeArray(); i++)
-                        {
-                                logger.info("Adding search scope: " + requestContent.getScopeArray(i));
-                                agent.addNewScope( requestContent.getScopeArray(i).toString() );
-                        }
-                        agent.setDoScope( true );
-                }
-
-                if ( requestContent.isSetBoundingBox() )
-                {
-                        logger.info("Adding bounding box data");
-                        agent.setLimitWest( requestContent.getBoundingBox().getLimitWest() );
-                        agent.setLimitSouth( requestContent.getBoundingBox().getLimitSouth() );
-                        agent.setLimitEast( requestContent.getBoundingBox().getLimitEast() );
-                        agent.setLimitNorth( requestContent.getBoundingBox().getLimitNorth() );
-                        agent.setDoSpatio( true );
-                        if ( requestContent.isSetSpatialOperator() )
-                        {
-                                logger.info("Adding spatial operator: " + requestContent.getSpatialOperator());
-                                agent.setSpatialOperator( requestContent.getSpatialOperator() );
-                        }
-                }
-
-                if ( requestContent.isSetDateRange() )
-                {
-                        logger.info("Adding temporal range");
-                        agent.setDateRangeStart( requestContent.getDateRange().getDateRangeStart() );
-                        agent.setDateRangeEnd( requestContent.getDateRange().getDateRangeEnd() );
-                        agent.setDoTemporal( true );
-                }
 
                 SearchSummary myResult = null;
@@ -125 +52 @@
                 try
                 {
+                        // firstly, parse the request message and set up the search agent
+                        logger.info("Preparing search agent for search");
+                        SearchAgent agent = setupSearchAgent(request);
+
                         logger.info("Running search...");
                         myResult = agent.doSearch();
@@ -186 +117 @@
 
         /**
+         * Prepare the search agent with all the passed in parameters from the WS request, ready to run
+         * a search
+         * 
+         * @param request - DoSearchDocument object passed from webservice call
+         * @return SearchAgent object with all required initialisation done for running a search query
+         * @throws DiscoveryWSException if problems encountered whilst doing the set up
+         */
+        private SearchAgent setupSearchAgent(DoSearchDocument request) throws DiscoveryWSException 
+        {
+                discoveryserviceapi.SearchType requestContent = request.getDoSearch();
+                SearchAgent agent = new SearchAgent();
+                
+                // check if we're doing a term type search - if so, check there is a term to search on
+                if (requestContent.getTermType() != null && requestContent.getTermType().length() > 0)
+                {
+                        logger.info("Term type set - checking for term value");
+
+                        if ( requestContent.getTerm().length() > 0 )
+                        {
+                                logger.info("Term set - will do term search");
+                                agent.setTermType(requestContent.getTermType());
+                                agent.setTerm(requestContent.getTerm());
+                        }
+                        else
+                        {
+                                logger.info("No term set - ignoring term type search specification");
+                        }
+                }
+                
+                if ( requestContent.isSetStart() )
+                {
+                        logger.info("Start position of results set set to: " + requestContent.getStart());
+                        agent.setStart( requestContent.getStart() );
+                }
+                        
+                if ( requestContent.isSetHowMany() )
+                {
+                        logger.info("Result set size set to: " + requestContent.getHowMany());
+                        agent.setHowMany( requestContent.getHowMany() );
+                }
+
+                if ( requestContent.isSetOrderBy() )
+                {
+                        logger.info("Results ordered by: " + requestContent.getOrderBy());
+                        agent.setOrderByField( requestContent.getOrderBy().toString() );
+                        if ( requestContent.isSetOrderByDirection() )
+                        {
+                                logger.info("Results ordering direction: " + requestContent.getOrderByDirection().toString());
+                                agent.setOrderByDirection( requestContent.getOrderByDirection().toString() );
+                        }
+                }
+
+                if ( requestContent.sizeOfScopeArray() > 0)
+                {
+                        for (int i=0; i<requestContent.sizeOfScopeArray(); i++)
+                        {
+                                logger.info("Adding search scope: " + requestContent.getScopeArray(i));
+                                agent.addNewScope( requestContent.getScopeArray(i).toString() );
+                        }
+                }
+
+                if ( requestContent.isSetBoundingBox() )
+                {
+                        logger.info("Adding bounding box data");
+                        agent.setLimitWest( requestContent.getBoundingBox().getLimitWest() );
+                        agent.setLimitSouth( requestContent.getBoundingBox().getLimitSouth() );
+                        agent.setLimitEast( requestContent.getBoundingBox().getLimitEast() );
+                        agent.setLimitNorth( requestContent.getBoundingBox().getLimitNorth() );
+                        if ( requestContent.isSetSpatialOperator() )
+                        {
+                                logger.info("Adding spatial operator: " + requestContent.getSpatialOperator());
+                                agent.setSpatialOperator( requestContent.getSpatialOperator() );
+                        }
+                }
+
+                if ( requestContent.isSetDateRange() )
+                {
+                        logger.info("Adding temporal range");
+                        agent.setDateRangeStart( requestContent.getDateRange().getDateRangeStart() );
+                        agent.setDateRangeEnd( requestContent.getDateRange().getDateRangeEnd() );
+                }
+                return agent;
+        }
+
+
+        /**
          * Performs the doPresent operation
          * @param discoveryserviceapi.DoPresentDocument containing search request
@@ -306 +323 @@
                 else if ( requestContent.getListName().equals("termTypeList") )
                 {
-                        list.addListMember("fullText");
-                        list.addListMember("author");
-                        list.addListMember("parameter");
+                        list.addListMember(FULL_TEXT_TERM_TYPE);
+                        list.addListMember(AUTHOR_TERM_TYPE);
+                        list.addListMember(PARAMETER_TERM_TYPE);
                 }
                 else if ( requestContent.getListName().equals("spatialOperatorList") )
                 {
-                        list.addListMember("overlaps");
-                        list.addListMember("doesNotOverlap");
-                        list.addListMember("within");
+                        list.addListMember(OVERLAPS_OPERATOR_TYPE);
+                        list.addListMember(NO_OVERLAP_OPERATOR_TYPE);
+                        list.addListMember(WITHIN_OPERATOR_TYPE);
                 }
                 else 

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

@@ -24 +24 @@
          * @param sqlQuery
          * @return String[][] - array of results, if applicable, null if none returned
+         * NB, returned array should be populated as follows: String[rowNo][colNo]
          * @throws DiscoveryDBException
          */

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

@@ -135 +135 @@
                                 {
                                         returnData[i][j] = results.getString(j+1);
-                                        System.out.println(returnData[i][j]);
                                 }
-                                
+                                i++;
                             }
                     }
+                    logger.info("Query completed and results set retrieved");
                     results.close();
                 }

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

@@ -9 +9 @@
 import java.util.Calendar;  // needed because xmlbeans uses this for xsd:date
 import java.util.logging.Logger;
-import java.io.ByteArrayInputStream;
+import java.util.regex.Matcher;
 import java.text.DecimalFormat;
 import java.text.SimpleDateFormat;
-import javax.xml.namespace.*;
-import javax.xml.xpath.*;
-import org.xml.sax.InputSource;
-import org.w3c.dom.*;
-
-import org.apache.xmlrpc.XmlRpcClient;
 
 /**
@@ -24 +18 @@
  *  @author     Matt Pritchard <m.j.pritchard@rl.ac.uk>
  */
-public abstract class SearchAgent 
+public class SearchAgent 
 {
 
     private static Logger logger = Logger.getLogger(SearchAgent.class.getName());
 
-        static Properties properties = new ServiceProperties().getProperties();
+    private static Properties properties = new ServiceProperties().getProperties();
 
         // Internal defaults
+    
+    private String termType = null;
+    
         static final BigInteger startDefault = new BigInteger("1"); // default value for first record to display in result set
         static final BigInteger howManyDefault = new BigInteger("30"); // default values for how many results to display in result set
-        boolean doSpatio = false;               // flags whether spatial search should be done
-        boolean doTemporal = false;             // flags whether temporal search should be done
-        boolean doTerm = false;                 // flags whether term search should be done
-        boolean doScope = false;                // flags whether search should be restricted by scope (dataCentre etc)
-        boolean doOrderBy = false;              // flags whether ordering should be applied to result set
         
         // Initialise input parameters, setting values to internal defaults
-        int resultId = 0;
-        BigInteger start = startDefault;
-        BigInteger howMany = howManyDefault;
-        String term = "";
-        String orderByField = "";       // representation used in WSDL e.g. "date", "dataCentre"
-        String orderByDirection = "ascending";  //default orderBy direction
+//      private int resultId = 0;
+        private BigInteger start = startDefault;
+        private BigInteger howMany = howManyDefault;
+        private String term = "";
+        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 scopes = new Vector();
-        String spatialOperator = "overlaps";
-
-        BigDecimal limitWest;
-        BigDecimal limitSouth;
-        BigDecimal limitEast;
-        BigDecimal limitNorth;
-        Calendar dateRangeStart;
-        Calendar dateRangeEnd;
+        Vector<String> scopes = new Vector<String>();
+        private String spatialOperator = "overlaps";
+
+        private BigDecimal limitWest;
+        private BigDecimal limitSouth;
+        private BigDecimal limitEast;
+        private BigDecimal limitNorth;
+        private Calendar dateRangeStart;
+        private Calendar dateRangeEnd;
 
         int hits;
-        Hashtable result = new Hashtable();
-
+        
+        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 ";
+        
         /**
          * Default no-argument constructor.
@@ -102 +107 @@
         }
 
+        public String getTerm() {               return term;    }
 
         /**
@@ -107 +113 @@
          * @param s String, one of "date", "dataCentre", list defined in <code>orderByFieldList</code> returned by <code>getList</code> operation.
          */
-        public void setOrderBy(String s)
-        {
-                this.orderByField = s;
+        public void setOrderByField(String orderByField) 
+        {
+                this.orderByField = orderByField;
+        }
+
+        public String getOrderByField() 
+        {
+                return orderByField;
         }
 
@@ -119 +130 @@
         public void setOrderByDirection(String s)
         {
-                this.orderByDirection = s;
+                if (s != null && s.equals("descending"))
+                        this.orderByDirection = "DESC";
+                else
+                        this.orderByDirection = "ASC";
+        }
+
+        public String getOrderByDirection() 
+        {
+                return orderByDirection;
         }
 
@@ -136 +155 @@
          * @return scopes containing String elements like NERC-DDC etc
          */
-        public Vector getScopes()
+        public Vector<String> getScopes()
         {
                 return this.scopes;
@@ -154 +173 @@
         {
                 String scopesStr = "";
-                Iterator it = this.scopes.iterator();
+                Iterator<String> it = this.scopes.iterator();
                 while ( it.hasNext() )
                 {
-                        scopesStr += (String) it.next();
-                }
+                        scopesStr += it.next();
+                }
+                System.out.println("got: " + scopesStr);
+                System.out.println("alternatively: " + this.scopes.toString());
                 return scopesStr;
         }
@@ -229 +250 @@
 
         /**
-         * Sets whether spatial search should be done.
-         * @param b boolean flag true if spatial search is to be done
-         */
-        public void setDoSpatio(boolean b)
-        {
-                this.doSpatio = b;
-        }
-
-        /**
-         * Sets whether temporal search should be done.
-         * @param b boolean flag true if temporal search is to be done
-         */
-        public void setDoTemporal(boolean b)
-        {
-                this.doTemporal = b;
-        }
-
-        /**
-         * Sets whether term search should be done.
-         * @param b boolean flag true if term search is to be done
-         */
-        public void setDoTerm(boolean b)
-        {
-                this.doTerm = b;
-        }
-
-        /**
-         * Sets whether result set should be ordered.
-         * @param b boolean flag true if result set should be ordered
-         */
-        public void setDoOrderBy(boolean b)
-        {
-                this.doOrderBy = b;
-        }
-
-        /**
-         * Sets whether scope check should be done.
-         * @param b boolean flag true if search should be limited to scope keywords
-         */
-        public void setDoScope(boolean b)
-        {
-                this.doScope = b;
-        }
-
-        /**
          * Sets operator used for spatial search
-         * @param s String spatial operator to be applied, must be member of list <code>spatialOperatorsList</code> returned by <code>getList</code> operation.
+         * @param s String spatial operator to be applied, 
+         *      must be member of list <code>spatialOperatorsList</code> returned by <code>getList</code> operation.
+         * @throws DiscoveryWSException if not correct value of getList list
         */
-        public void setSpatialOperator(String s)
-        {
+        public void setSpatialOperator(String s) throws DiscoveryWSException
+        {
+                if (s.equals(DiscoveryServiceSkeleton.OVERLAPS_OPERATOR_TYPE) || 
+                                s.equals(DiscoveryServiceSkeleton.WITHIN_OPERATOR_TYPE) ||
+                                s.equals(DiscoveryServiceSkeleton.NO_OVERLAP_OPERATOR_TYPE))
+                {
+                        this.spatialOperator = s;
+                }
+                else
+                {
+                        String errorMessage = "Invalid spatial operator, " + s +
+                                " - please use getListNames to get the valid list of spatial operator types";
+                        logger.warning(errorMessage);
+                        throw new DiscoveryWSException(errorMessage);
+                }
                 this.spatialOperator = s;
         }
@@ -291 +282 @@
         }
 
+        public BigDecimal getLimitWest() {              return limitWest;       }
+
+        public BigDecimal getLimitSouth() {             return limitSouth;      }
+
+        public BigDecimal getLimitEast() {              return limitEast;       }
+
+        public BigDecimal getLimitNorth() {             return limitNorth;      }
+
+        public Calendar getDateRangeStart() {           return dateRangeStart;  }
+
+        public Calendar getDateRangeEnd() {             return dateRangeEnd;    }
+
+        public BigInteger getHowMany() {                return howMany;         }
+
+        public String getTermType() {           return termType;        }
+
+        public BigInteger getStart() {          return start;   }
+
+        /**
+         * Set the type of the term to search on; NB, this must be either 'fullText', 'parameter' or 'author'
+         * - if not an exception is thrown
+         * 
+         * @param termType
+         */
+        public void setTermType(String termType) throws DiscoveryWSException
+        {
+                if (termType.equals(DiscoveryServiceSkeleton.FULL_TEXT_TERM_TYPE) || 
+                                termType.equals(DiscoveryServiceSkeleton.AUTHOR_TERM_TYPE) ||
+                                termType.equals(DiscoveryServiceSkeleton.PARAMETER_TERM_TYPE))
+                {
+                        this.termType = termType;
+                }
+                else
+                {
+                        String errorMessage = "Invalid termType, " + termType +
+                                " - please use getListNames to get the valid list of term types";
+                        logger.warning(errorMessage);
+                        throw new DiscoveryWSException(errorMessage);
+                }
+        }
+
+
         /**
          * Executes the search and returns a SearchSummary object
@@ -298 +331 @@
         {
 
-                SearchSummary result = new SearchSummary();
+                SearchSummary result = null;
 
                 try
@@ -307 +340 @@
                         // now run the query using the db client configured appropriately
                         InterfaceDBClient client = setupDBClient();
-                        client.runQuery(sqlQuery);
+                        String[][] results = client.runQuery(sqlQuery);
                         
-
-//                      ByteArrayInputStream resultStream = new ByteArrayInputStream( resultByteArray );
-//
-//                      // Mark the beginning of the Stream, so can reset before each xpath evaluation (!)
-//                      resultStream.mark( resultStream.available() );
-//                      InputSource resultSource = new InputSource( resultStream );
-//                      
-//                      XPath xpath = XPathFactory.newInstance().newXPath();
-//                      String hitsExpr = "/exist:result/@hits"; 
-//                      String documentExpr = "//exist:result/document";
-//                      xpath.setNamespaceContext( new existNamespaceContextImpl() );
-//
-//                      // If there is a successful search but with no hits, exist sets hitCount=0 rather than hits=0 !!
-//                      // Need to check that the thing returned by xpath will convert to an int
-//                      try
-//                      {
-//                              int xpathHits = (new Integer( xpath.evaluate(hitsExpr, resultSource) )).intValue();
-//                              result.setHits( xpathHits );
-//                      }
-//                      catch (NumberFormatException e)
-//                      {
-//                              result.setHits( 0 );
-//                      }
-//                      
-//                      resultStream.reset();
-//                      NodeList nodeList = (NodeList)xpath.evaluate( documentExpr, resultSource, XPathConstants.NODESET ); 
-//                      
-//                      if ( result.getHits() > 0)
-//                      {
-//                              Vector documents = new Vector();
-//                              for (int i=0; i<nodeList.getLength(); i++)
-//                              {
-//                                      Hashtable thisDoc = new Hashtable();
-//                                      thisDoc.put("name", nodeList.item(i).getFirstChild().getNodeValue() );
-//                                      thisDoc.put("matches", 1);
-//                                      thisDoc.put("position", i);
-//                                      documents.add( thisDoc );
-//                              }
-//                              result.setStatus( true );
-//                              result.setStatusMessage( "Success" );
-//                              result.setDocuments( documents );       
-//                      }
-//                      else
-//                      {
-//                              result.setStatus( false );
-//                              result.setStatusMessage("Search was successful but generated no results.");
-//                      }
-//                      
+                        // now extract the results data
+                        result = processResults(results);
                         
                 }
@@ -365 +352 @@
                 }
 
+                return result;
+        }
+
+        
+        /**
+         * Take the results set returned from a call to doSearch and extract the
+         * data into a SearchSummary object
+         * 
+         * @param results - a 2D string array of results from the doSearch call 
+         * @return SearchSummary object wrappering the results set
+         */
+        private SearchSummary processResults(String[][] results) 
+        {
+                SearchSummary result = new SearchSummary();
+                result.setHits(results.length); // NB ensure this is 0 if not data returned
+                
+                if ( result.getHits() > 0)
+                {
+                        Vector<Hashtable<String, String>> documents = new Vector<Hashtable<String, String>>();
+                        for (int i=0; i < results.length; i++)
+                        {
+                                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 );
+                        }
+                        result.setStatus( true );
+                        result.setStatusMessage( "Success" );
+                        result.setDocuments( documents );       
+                }
+                else
+                {
+                        result.setStatus( false );
+                        result.setStatusMessage("Search was successful but generated no results.");
+                }
+                
                 return result;
         }
@@ -387 +412 @@
         }
 
+        /**
+         * Set up a SQL Select query with the input search parameters
+         * 
+         * @return String - SQL command
+         */
         private String constructSearchQuery() 
         {
-                //1. Prepare & execute the search
-                Vector queryParams = new Vector();
-                
-                StringBuffer sqlCmd = new StringBuffer("SELECT original_document_id FROM ORIGINAL DOCUMENT WHERE ");
+                logger.info("Creating search query");
+                
+                // NB, we use the postgres text search function to do term searches
+                StringBuffer fromSqlCmd = new StringBuffer("SELECT original_document_filename FROM " + 
+                                ORIGINAL_DOCUMENT_TABLE + " ");
+                StringBuffer whereSqlCmd = new StringBuffer();
+                
+                if (this.getTerm() != null && this.getTerm().length() > 0)
+                {
+                        logger.info("Adjusting query for " + this.getTermType() + " type search");
+                        
+                        if (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
@@ -398 +451 @@
                 xqueryStr = xqueryStr.replaceFirst("__subst_scope__", this.getScopesAsString() );
 
-                // orderByField contains $ chars etc so needs special treatment to avoid "java.lang.IllegalArgumentException: Illegal group reference" when replacing
-                String orderByFieldReplace = Matcher.quoteReplacement(this.orderByField);
-                xqueryStr = xqueryStr.replaceAll("__subst_orderByField__", orderByFieldReplace); //NB replaceAll cos more than 1!
-                xqueryStr = xqueryStr.replaceFirst("__subst_orderByDirection__", this.orderByDirection);
-                
-*/                      // Construct the search clause for spatio temporal search, if necessary
-                String spatialClause = "";
-                try
-                {
-                        SPATIO:
-                        if ( 
-                                doSpatio && 
-                                limitWest != null &&
-                                limitSouth != null &&
-                                limitNorth != null &&
-                                limitEast != null
-                        )
-                        {
-                                if (    // Ignore spatial search if search area is global
-                                        limitWest.intValue() == -180 &&
-                                        limitEast.intValue() == 180 &&
-                                        limitNorth.intValue() == 90 &&
-                                        limitSouth.intValue() == -90
-                                )
-                                {
-                                        doSpatio = false;
-                                        System.out.println("Global extent selected : spatial search ignored");
-                                        break SPATIO;
-                                }
-
-                                String latFormatPattern = "#0.00";
-                                String lonFormatPattern = "##0.00";
-                                DecimalFormat latFormat = new DecimalFormat( latFormatPattern );
-                                DecimalFormat lonFormat = new DecimalFormat( lonFormatPattern );
-
-                                if ( spatialOperator.equals("within") )
-                                {
-                                        spatialClause += " sbox_contains_box(";
-                                } 
-                                else if ( spatialOperator.equals("overlaps") )
-                                {
-                                        spatialClause += " sbox_overlap_box(";
-                                }
-                                else if ( spatialOperator.equals("doesNotOverlap") )
-                                {
-                                        spatialClause += " NOT sbox_overlap_box(";
-                                } 
-                                else
-                                {
-                                        doSpatio = false;
-                                        throw new Exception("Unrecognised spatial operator: " + spatialOperator + " (see spatialOperatorList for supported values)");
-                                }                                       
-                                spatialClause += " sbox'("+
-                                lonFormat.format( limitWest.floatValue() )+"d,"+
-                                latFormat.format( limitSouth.floatValue() )+"d),("+
-                                lonFormat.format( limitEast.floatValue() )+"d,"+
-                                latFormat.format( limitNorth.floatValue() )+"d)' ";
-                                spatialClause += ", coordinates ) ";
-
-                        }
-                        else {
-                                doSpatio = false; //just in case any components of coordinates were null
-                        }
-                }
-                catch (Exception e)
-                {
-                        System.out.println("Error converting coordinates to SQL: " + e.toString() );
-                        doSpatio = false;
-                }
-
-                String temporalClause = "";
-                try
-                {
-                        if (
-                                doTemporal &&
-                                dateRangeStart != null &&
-                                dateRangeEnd != null &&
-                                dateRangeEnd.getTimeInMillis() > dateRangeStart.getTimeInMillis()
-                        )
-                        {
-                                String format = "yyyy-MM-dd";
-                                SimpleDateFormat sdf = new SimpleDateFormat(format);
-
-                                temporalClause = " (startdate, enddate) OVERLAPS (DATE '"+sdf.format( dateRangeStart.getTime() )+"', DATE '"+ sdf.format( dateRangeEnd.getTime() ) +"') ";
+                
+*/                      
+                // Construct the search clause for spatio temporal search, if necessary
+                if (isSpatialSearch())
+                {
+                        logger.info("Adding spatial data to query");
+                        // 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)";
+
+                        if (spatialOperator.equals(DiscoveryServiceSkeleton.OVERLAPS_OPERATOR_TYPE))
+                        {
+                                appendWhereClause(whereSqlCmd, " geometry && " + bbox + " ");
+                        }
+                        else if ( spatialOperator.equals(DiscoveryServiceSkeleton.WITHIN_OPERATOR_TYPE) )
+                        {
+                                appendWhereClause(whereSqlCmd, " geometry @ " + bbox + " ");
+                        }
+                        else if ( spatialOperator.equals("doesNotOverlap") )
+                        {
+                                appendWhereClause(whereSqlCmd, "NOT geometry && " + bbox + " ");
+                        } 
+                }
+
+                if (isTemporalSearch())
+                {
+                        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 '" + 
+                                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");
+                }
+                
+                // if either or both spatial and temporal data is searched on, need to join this with the main
+                // orig doc search query
+                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());
+                }
+                
+                // 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());
+                
+                String fullCmd = fromSqlCmd.toString() + WHERE_STATEMENT + whereSqlCmd.toString() + ";";
+                logger.info("SQL query generated");
+                logger.fine("Value: " + fullCmd);
+                
+                return fullCmd;
+        }
+
+        /**
+         * Append to 'where' clause in sql statement, ensuring 'AND' joins are added where required
+         * 
+         * @param whereSqlCmd - StringBuffer holding sql statement
+         * @param clause - string sql clause to add to buffer
+         */
+        private void appendWhereClause(StringBuffer whereSqlCmd, String clause) 
+        {
+                if (whereSqlCmd.length() > 0)
+                        whereSqlCmd.append(" AND ");
+                whereSqlCmd.append(clause);
+        }
+
+        /**
+         * Determine whether the search has any temporal data specified
+         * 
+         * @return true if includes temporal search, false otherwise
+         * @throws UnsupportedOperationException if 
+         */
+        private boolean isTemporalSearch() throws UnsupportedOperationException 
+        {
+                logger.info("Checking if valid temporal data specified");
+                if (this.getDateRangeStart() != null && this.getDateRangeEnd() != null)
+                {
+                        if (dateRangeEnd.getTimeInMillis() > dateRangeStart.getTimeInMillis())
+                        {
+                                logger.info("Valid temporal data specified");
+                                return true;
                         }
                         else
                         {
-                                doTemporal = false; // just in case any components of date range were null
-                        }
-                }
-                catch (Exception e)
-                {
-                        System.out.println("Error converting date to SQL: " + e.toString() );
-                        temporalClause = "";
-                        doTemporal = false;
-                }                       
-
-                String whereClause = "";
-                if ( doSpatio && doTemporal )
-                {
-                        whereClause = " WHERE " + spatialClause + " AND " + temporalClause + " ";
-                }
-                else if ( doSpatio && !doTemporal )
-                {
-                        whereClause = " WHERE " + spatialClause + " ";
-                }
-                else if ( doTemporal && !doSpatio )
-                {
-                        whereClause = " WHERE " + temporalClause + " ";
-                }
-                else
-                {
-                        whereClause = "";
-                }
-/*
-                xqueryStr = xqueryStr.replaceFirst("__subst_spatioTempWhereClause__", whereClause );
-
-                xqueryStr = xqueryStr.replaceFirst("__subst_doTerm__", this.doTerm ? "fn:true()" : "fn:false()");
-                xqueryStr = xqueryStr.replaceFirst("__subst_doSpatioTemp__", (this.doSpatio | this.doTemporal) ? "fn:true()" : "fn:false()");
-                xqueryStr = xqueryStr.replaceFirst("__subst_doOrderBy__", this.doOrderBy ? "fn:true()" : "fn:false()"); //NB replaceAll cos more than 1! 
-                xqueryStr = xqueryStr.replaceFirst("__subst_doScope__", this.doScope ? "fn:true()" : "fn:false()");
-                
-                //System.out.println("Xquery was \n" + xqueryStr);
-                
-                queryParams.addElement( xqueryStr.getBytes("UTF-8") ); 
-*/
-                queryParams.addElement( this.howMany.intValue() );
-                queryParams.addElement( this.start.intValue() ); 
-                //System.out.println("start \n" + this.start.intValue());
-                //System.out.println("howMany \n" + this.howMany.intValue());
-                return null;
-        }
-
-        public class existNamespaceContextImpl implements NamespaceContext
-    {
-        public String getNamespaceURI(String prefix)
-        {
-                        return "http://exist.sourceforge.net/NS/exist";
-        }
-     
-                // Doesn't matter what the prefix in the source doc is, override it
-                // Important thing is that the namespace URI matches, and that
-                // whatever prefix is set here, is used in the xpath query
-        public String getPrefix(String namespace)
-        {
-                        return "exist";
-        }
-
-        public Iterator getPrefixes(String namespace)
-        {
-            return null;
-        }
-    } 
+                                String errorMessage = "Invalid temporal data: End date before start date.";
+                                logger.warning(errorMessage);
+                                throw new UnsupportedOperationException(errorMessage);
+                        }
+                }
+                logger.info("No valid temporal data specified");
+                return false;
+        }
+
+        /**
+         * Determine whether the search has any spatial data specified
+         * NB, checks that global limits aren't specified - if so, this is semantically
+         * equivalent to a non spatial search, so treat as such
+         * 
+         * @return true if includes spatial search, false otherwise
+         */
+        private boolean isSpatialSearch() 
+        {
+                logger.info("Checking if valid spatial data specified");
+                if (this.getLimitEast() != null && this.getLimitWest() != null &&
+                        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");
+                }
+                logger.info("No valid spatial data specified");
+                return false;
+        }
 
 
@@ -658 +689 @@
 "return <document>{$i}</document> \n";
 
+
+
 }

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

@@ -12 +12 @@
         public ServiceProperties()
         {
-                properties.setProperty("jdbc.uri","jdbc:postgresql://glue.badc.rl.ac.uk/calum:51000");
+                properties.setProperty("jdbc.uri","jdbc:postgresql://glue.badc.rl.ac.uk:51000/calum");
                 properties.setProperty("jdbc.username","calum");
                 properties.setProperty("jdbc.password","boybear");