/ - Diff - Metacat - Ecoinformatics Redmine

     package edu.ucsb.nceas.dbadapter;
     import java.sql.*;
     import edu.ucsb.nceas.metacat.*;
     import java.sql.Connection;
     import java.sql.ResultSet;
     import java.sql.SQLException;
     import java.sql.Statement;
     /**
      * The PostgreSQL db adapter implementation.
-...
           String sql ="select a.docid, a.rev, a.doctype from ( xml_documents as a left outer join  xml_revisions as b on (a.docid=b.docid and  a.rev<=b.rev)) where b.docid is null ";
           return sql;
+      }
       public String getPagedQuery(String queryWithOrderBy, Integer start, Integer count) {
     	  String query = queryWithOrderBy;
     	  if (count != null) {
     		  query = query + " LIMIT " + count;
+    	  }
     	  if (start != null) {
     		  query = query + " OFFSET " + start;
+    	  }
     	  return query;
+      }
+    }

     package edu.ucsb.nceas.dbadapter;
     import java.sql.*;
     import edu.ucsb.nceas.metacat.*;
     import java.sql.Connection;
     import java.sql.ResultSet;
     import java.sql.SQLException;
     import java.sql.Statement;
     /**
      * The Oracle db adapter implementation.
-...
           return sql;
+      }
       public String getPagedQuery(String queryWithOrderBy, Integer start, Integer count) {
     	  // not limiting rows
     	  StringBuffer query = new StringBuffer("SELECT " + queryWithOrderBy);
     	  // check for params
     	  if (start != null) {
     		  query = new StringBuffer();
     		  query.append("SELECT * FROM ");
     		  query.append("( SELECT a.*, ROWNUM rnum FROM ");
     		  query.append("( " + queryWithOrderBy + " ) a ");
     		  if (count != null) {
     			  // both are limited
     			  query.append(" WHERE ROWNUM <= " + count);
+    		  }
     		  query.append(" ) ");
     		  query.append("WHERE rnum  >= " + start);
+    	  }
     	  return query.toString();
+      }
+    }

     package edu.ucsb.nceas.dbadapter;
     import java.sql.*;
     import edu.ucsb.nceas.metacat.*;
     import java.sql.Connection;
     import java.sql.ResultSet;
     import java.sql.SQLException;
     import java.sql.Statement;
     /**
      * The MS SQL Server db adapter implementation.
-...
           String sql ="select a.docid, a.rev, a.doctype from ( xml_documents a left outer join  xml_revisions b on (a.docid=b.docid and  a.rev<=b.rev)) where b.docid is null ";
           return sql;
+      }
       public String getPagedQuery(String queryFieldsWithOrderBy, Integer start, Integer count) {
     	  // TODO: implement MSSQL server
     	  return null;
+      }
+    }

     package edu.ucsb.nceas.dbadapter;
     import java.sql.*;
     import java.sql.Connection;
     import java.sql.SQLException;
     import edu.ucsb.nceas.metacat.properties.PropertyService;
-...
        * @return
        */
       public abstract String getReplicationDocumentListSQL();
       /**
        * for generating a query for paging
        * @param queryWithOrderBy - the complete query with SELECT, FROM, WHERE and ORDER BY clauses
        * @param start the row number to start from
        * @param count the number of records from start to return
        * @return query specific to the RDBMS in use
        */
       public abstract String getPagedQuery(String queryWithOrderBy, Integer start, Integer count);
+    }

     import edu.ucsb.nceas.metacat.database.DBConnection;
     import edu.ucsb.nceas.metacat.database.DBConnectionPool;
     import edu.ucsb.nceas.metacat.database.DatabaseService;
     import edu.ucsb.nceas.metacat.properties.PropertyService;
     import edu.ucsb.nceas.metacat.util.DocumentUtil;
     import edu.ucsb.nceas.utilities.PropertyNotFoundException;
-...
             	query.append(") ");
                 clauseAdded = true;
+            }
             // always limit by only dataone events
             if (true) {
     	        if (clauseAdded) {
     	            query.append(" and ");
+    	        }
     	    	query.append("event in (");
     	    	for (int i = 0; i < Event.values().length; i++) {
     	    		if (i > 0) {
     	        		query.append(", ");
+    	    		}
     	    		query.append("?");
     	    		paramValues.add(Event.values()[i].xmlValue());
+    	    	}
     	    	query.append(") ");
     	        clauseAdded = true;
+            }
             if (startDate != null) {
                 if (clauseAdded) {
                     query.append(" and ");
-...
                 clauseAdded = true;
                 endIndex = startIndex + 1;
+            }
             // order by
             query.append(" order by entryid ");
     		// limit by count
             String pagedQuery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(query.toString(), start, count);
             DBConnection dbConn = null;
             int serialNumber = -1;
             try {
-...
                 serialNumber = dbConn.getCheckOutSerialNumber();
                 // Execute the query statement
                 PreparedStatement stmt = dbConn.prepareStatement(query.toString());
                 PreparedStatement stmt = dbConn.prepareStatement(pagedQuery);
                 //set the param values
                 int parameterIndex = 1;
                 for (String val: paramValues) {
-...
     			log.setLogEntryList(logs);
     			// d1 paging
     		    int total = logs.size();
     		    if (start != null && count != null) {
     		    	int toIndex = start + count;
     		    	// do not exceed total
     		    	toIndex = Math.min(toIndex, total);
     		    	// do not start greater than total
     		    	start = Math.min(start, total);
     		    	// sub set of the list
     		    	logs = new ArrayList<LogEntry>(logs.subList(start, toIndex));
+    		    }
     		    // NOTE: now using query limits
     //		    if (start != null && count != null) {
     //		    	int toIndex = start + count;
     //		    	// do not exceed total
     //		    	toIndex = Math.min(toIndex, total);
     //		    	// do not start greater than total
     //		    	start = Math.min(start, total);
     //		    	// sub set of the list
     //		    	logs = new ArrayList<LogEntry>(logs.subList(start, toIndex));
     //		    }
     		    log.setLogEntryList(logs);
     		    log.setStart(start);

Project

General

Profile

Metacat

Revision 7437

Added by ben leinfelder almost 12 years ago