You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@manifoldcf.apache.org by kw...@apache.org on 2010/09/22 00:18:19 UTC
svn commit: r999670 - in /incubator/lcf/trunk/modules/framework:
core/src/main/java/org/apache/acf/core/database/
core/src/main/java/org/apache/acf/core/interfaces/
pull-agent/src/main/java/org/apache/acf/crawler/repository/
Author: kwright
Date: Tue Sep 21 22:18:19 2010
New Revision: 999670
URL: http://svn.apache.org/viewvc?rev=999670&view=rev
Log:
Add parameters to distinct-on abstraction, also rejigger contract to allow subquery-style implementation.
Modified:
incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/BaseTable.java
incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceDerby.java
incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceMySQL.java
incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfacePostgreSQL.java
incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/interfaces/IDBInterface.java
incubator/lcf/trunk/modules/framework/pull-agent/src/main/java/org/apache/acf/crawler/repository/RepositoryHistoryManager.java
Modified: incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/BaseTable.java
URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/BaseTable.java?rev=999670&r1=999669&r2=999670&view=diff
==============================================================================
--- incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/BaseTable.java (original)
+++ incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/BaseTable.java Tue Sep 21 22:18:19 2010
@@ -323,15 +323,17 @@ public class BaseTable
* This filter wraps a query and returns a new query whose results are similar to POSTGRESQL's DISTINCT-ON feature.
* Specifically, for each combination of the specified distinct fields in the result, only the first such row is included in the final
* result.
+ *@param outputParameters is a blank arraylist into which to put parameters. Null may be used if the baseParameters parameter is null.
*@param baseQuery is the base query, which can either be tables and where clause, or can be another SELECT in parens,
* e.g. "(SELECT ...) t3"
+ *@param baseParameters are the parameters corresponding to the baseQuery.
*@param distinctFields are the fields to consider to be distinct.
*@param otherFields are the rest of the fields to return, keyed by the AS name, value being the column value, e.g. "value AS key"
- *@return a revised query that performs the necessary DISTINCT ON operation.
+ *@return a revised query that performs the necessary DISTINCT ON operation. The arraylist outputParameters will also be appropriately filled in.
*/
- public String constructDistinctOnClause(String baseQuery, String[] distinctFields, Map otherFields)
+ public String constructDistinctOnClause(ArrayList outputParameters, String baseQuery, ArrayList baseParameters, String[] distinctFields, Map otherFields)
{
- return dbInterface.constructDistinctOnClause(baseQuery,distinctFields,otherFields);
+ return dbInterface.constructDistinctOnClause(outputParameters,baseQuery,baseParameters,distinctFields,otherFields);
}
/** Quote a sql string.
Modified: incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceDerby.java
URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceDerby.java?rev=999670&r1=999669&r2=999670&view=diff
==============================================================================
--- incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceDerby.java (original)
+++ incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceDerby.java Tue Sep 21 22:18:19 2010
@@ -850,15 +850,37 @@ public class DBInterfaceDerby extends Da
* This filter wraps a query and returns a new query whose results are similar to POSTGRESQL's DISTINCT-ON feature.
* Specifically, for each combination of the specified distinct fields in the result, only the first such row is included in the final
* result.
- *@param baseQuery is the base query, which can either be tables and where clause, or can be another SELECT in parens,
- * e.g. "(SELECT ...) t3"
- *@param distinctFields are the fields to consider to be distinct.
- *@param otherFields are the rest of the fields to return, keyed by the AS name, value being the column value, e.g. "value AS key"
- *@return a revised query that performs the necessary DISTINCT ON operation.
- */
- public String constructDistinctOnClause(String baseQuery, String[] distinctFields, Map otherFields)
- {
- // Derby does not support this functionality. I can find no way around it either.
+ *@param outputParameters is a blank arraylist into which to put parameters. Null may be used if the baseParameters parameter is null.
+ *@param baseQuery is the base query, which is another SELECT statement, without parens,
+ * e.g. "SELECT ..."
+ *@param baseParameters are the parameters corresponding to the baseQuery.
+ *@param distinctFields are the fields to consider to be distinct. These should all be keys in otherFields below.
+ *@param otherFields are the rest of the fields to return, keyed by the AS name, value being the base query column value, e.g. "value AS key"
+ *@return a revised query that performs the necessary DISTINCT ON operation. The arraylist outputParameters will also be appropriately filled in.
+ */
+ public String constructDistinctOnClause(ArrayList outputParameters, String baseQuery, ArrayList baseParameters, String[] distinctFields, Map otherFields)
+ {
+ // Derby does not really support this functionality.
+ // We could hack a workaround, along the following lines:
+ //
+ // SELECT
+ // t1.bucket, t1.bytecount, t1.windowstart, t1.windowend
+ // FROM
+ // (xxx) t1
+ // WHERE
+ // t1.bytecount=( SELECT t2.bytecount FROM (xxx) t2 WHERE
+ // t2.bucket = t1.bucket LIMIT 1 ) AND
+ // t1.windowstart=( SELECT t2.windowstart FROM (xxx) t2 WHERE
+ // t2.bucket = t1.bucket LIMIT 1 ) AND
+ // t1.windowend=( SELECT t2.windowend FROM (xxx) t2 WHERE
+ // t2.bucket = t1.bucket LIMIT 1 )
+ //
+ // However, the cost of doing 3 identical and very costly queries is likely to be too high for this to be viable.
+
+ // Copy arguments
+ if (baseParameters != null)
+ outputParameters.addAll(baseParameters);
+
StringBuffer sb = new StringBuffer("SELECT ");
boolean needComma = false;
Iterator iter = otherFields.keySet().iterator();
@@ -869,9 +891,9 @@ public class DBInterfaceDerby extends Da
if (needComma)
sb.append(",");
needComma = true;
- sb.append(columnValue).append(" AS ").append(fieldName);
+ sb.append("txxx1.").append(columnValue).append(" AS ").append(fieldName);
}
- sb.append(" FROM ").append(baseQuery);
+ sb.append(" FROM (").append(baseQuery).append(") txxx1");
return sb.toString();
}
Modified: incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceMySQL.java
URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceMySQL.java?rev=999670&r1=999669&r2=999670&view=diff
==============================================================================
--- incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceMySQL.java (original)
+++ incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfaceMySQL.java Tue Sep 21 22:18:19 2010
@@ -587,16 +587,22 @@ public class DBInterfaceMySQL extends Da
* This filter wraps a query and returns a new query whose results are similar to POSTGRESQL's DISTINCT-ON feature.
* Specifically, for each combination of the specified distinct fields in the result, only the first such row is included in the final
* result.
- *@param baseQuery is the base query, which can either be tables and where clause, or can be another SELECT in parens,
- * e.g. "(SELECT ...) t3"
- *@param distinctFields are the fields to consider to be distinct.
- *@param otherFields are the rest of the fields to return, keyed by the AS name, value being the column value, e.g. "value AS key"
- *@return a revised query that performs the necessary DISTINCT ON operation.
+ *@param outputParameters is a blank arraylist into which to put parameters. Null may be used if the baseParameters parameter is null.
+ *@param baseQuery is the base query, which is another SELECT statement, without parens,
+ * e.g. "SELECT ..."
+ *@param baseParameters are the parameters corresponding to the baseQuery.
+ *@param distinctFields are the fields to consider to be distinct. These should all be keys in otherFields below.
+ *@param otherFields are the rest of the fields to return, keyed by the AS name, value being the base query column value, e.g. "value AS key"
+ *@return a revised query that performs the necessary DISTINCT ON operation. The arraylist outputParameters will also be appropriately filled in.
*/
- public String constructDistinctOnClause(String baseQuery, String[] distinctFields, Map otherFields)
+ public String constructDistinctOnClause(ArrayList outputParameters, String baseQuery, ArrayList baseParameters, String[] distinctFields, Map otherFields)
{
// I don't know whether MySql supports this functionality or not.
// MHL
+ // Copy arguments
+ if (baseParameters != null)
+ outputParameters.addAll(baseParameters);
+
StringBuffer sb = new StringBuffer("SELECT ");
boolean needComma = false;
Iterator iter = otherFields.keySet().iterator();
@@ -607,9 +613,9 @@ public class DBInterfaceMySQL extends Da
if (needComma)
sb.append(",");
needComma = true;
- sb.append(columnValue).append(" AS ").append(fieldName);
+ sb.append("txxx1.").append(columnValue).append(" AS ").append(fieldName);
}
- sb.append(" FROM ").append(baseQuery);
+ sb.append(" FROM (").append(baseQuery).append(") txxx1");
return sb.toString();
}
Modified: incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfacePostgreSQL.java
URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfacePostgreSQL.java?rev=999670&r1=999669&r2=999670&view=diff
==============================================================================
--- incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfacePostgreSQL.java (original)
+++ incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/database/DBInterfacePostgreSQL.java Tue Sep 21 22:18:19 2010
@@ -869,14 +869,20 @@ public class DBInterfacePostgreSQL exten
* This filter wraps a query and returns a new query whose results are similar to POSTGRESQL's DISTINCT-ON feature.
* Specifically, for each combination of the specified distinct fields in the result, only the first such row is included in the final
* result.
- *@param baseQuery is the base query, which can either be tables and where clause, or can be another SELECT in parens,
- * e.g. "(SELECT ...) t3"
- *@param distinctFields are the fields to consider to be distinct.
- *@param otherFields are the rest of the fields to return, keyed by the AS name, value being the column value, e.g. "value AS key"
- *@return a revised query that performs the necessary DISTINCT ON operation.
- */
- public String constructDistinctOnClause(String baseQuery, String[] distinctFields, Map otherFields)
- {
+ *@param outputParameters is a blank arraylist into which to put parameters. Null may be used if the baseParameters parameter is null.
+ *@param baseQuery is the base query, which is another SELECT statement, without parens,
+ * e.g. "SELECT ..."
+ *@param baseParameters are the parameters corresponding to the baseQuery.
+ *@param distinctFields are the fields to consider to be distinct. These should all be keys in otherFields below.
+ *@param otherFields are the rest of the fields to return, keyed by the AS name, value being the base query column value, e.g. "value AS key"
+ *@return a revised query that performs the necessary DISTINCT ON operation. The arraylist outputParameters will also be appropriately filled in.
+ */
+ public String constructDistinctOnClause(ArrayList outputParameters, String baseQuery, ArrayList baseParameters, String[] distinctFields, Map otherFields)
+ {
+ // Copy arguments
+ if (baseParameters != null)
+ outputParameters.addAll(baseParameters);
+
StringBuffer sb = new StringBuffer("SELECT DISTINCT ON(");
int i = 0;
while (i < distinctFields.length)
@@ -895,9 +901,9 @@ public class DBInterfacePostgreSQL exten
if (needComma)
sb.append(",");
needComma = true;
- sb.append(columnValue).append(" AS ").append(fieldName);
+ sb.append("txxx1.").append(columnValue).append(" AS ").append(fieldName);
}
- sb.append(" FROM ").append(baseQuery);
+ sb.append(" FROM (").append(baseQuery).append(") txxx1");
return sb.toString();
}
Modified: incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/interfaces/IDBInterface.java
URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/interfaces/IDBInterface.java?rev=999670&r1=999669&r2=999670&view=diff
==============================================================================
--- incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/interfaces/IDBInterface.java (original)
+++ incubator/lcf/trunk/modules/framework/core/src/main/java/org/apache/acf/core/interfaces/IDBInterface.java Tue Sep 21 22:18:19 2010
@@ -287,13 +287,15 @@ public interface IDBInterface
* This filter wraps a query and returns a new query whose results are similar to POSTGRESQL's DISTINCT-ON feature.
* Specifically, for each combination of the specified distinct fields in the result, only the first such row is included in the final
* result.
- *@param baseQuery is the base query, which can either be tables and where clause, or can be another SELECT in parens,
- * e.g. "(SELECT ...) t3"
- *@param distinctFields are the fields to consider to be distinct.
- *@param otherFields are the rest of the fields to return, keyed by the AS name, value being the column value, e.g. "value AS key"
- *@return a revised query that performs the necessary DISTINCT ON operation.
+ *@param outputParameters is a blank arraylist into which to put parameters. Null may be used if the baseParameters parameter is null.
+ *@param baseQuery is the base query, which is another SELECT statement, without parens,
+ * e.g. "SELECT ..."
+ *@param baseParameters are the parameters corresponding to the baseQuery.
+ *@param distinctFields are the fields to consider to be distinct. These should all be keys in otherFields below.
+ *@param otherFields are the rest of the fields to return, keyed by the AS name, value being the base query column value, e.g. "value AS key"
+ *@return a revised query that performs the necessary DISTINCT ON operation. The arraylist outputParameters will also be appropriately filled in.
*/
- public String constructDistinctOnClause(String baseQuery, String[] distinctFields, Map otherFields);
+ public String constructDistinctOnClause(ArrayList outputParameters, String baseQuery, ArrayList baseParameters, String[] distinctFields, Map otherFields);
/** Quote a sql string.
* This method quotes a sql string in the proper manner for the database in question.
Modified: incubator/lcf/trunk/modules/framework/pull-agent/src/main/java/org/apache/acf/crawler/repository/RepositoryHistoryManager.java
URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/modules/framework/pull-agent/src/main/java/org/apache/acf/crawler/repository/RepositoryHistoryManager.java?rev=999670&r1=999669&r2=999670&view=diff
==============================================================================
--- incubator/lcf/trunk/modules/framework/pull-agent/src/main/java/org/apache/acf/crawler/repository/RepositoryHistoryManager.java (original)
+++ incubator/lcf/trunk/modules/framework/pull-agent/src/main/java/org/apache/acf/crawler/repository/RepositoryHistoryManager.java Tue Sep 21 22:18:19 2010
@@ -330,7 +330,7 @@ public class RepositoryHistoryManager ex
// end time of the current record. That's why there are two inner clauses with a UNION.
StringBuffer sb = new StringBuffer();
- sb.append("(SELECT * FROM (SELECT t6.bucket AS bucket,")
+ sb.append("SELECT * FROM (SELECT t6.bucket AS bucket,")
.append("t6.windowstart AS windowstart,t6.windowend AS windowend, SUM(t6.activitycount) AS activitycount")
.append(" FROM (SELECT ");
@@ -382,14 +382,14 @@ public class RepositoryHistoryManager ex
sb.append(" AND t1a.").append(startTimeField).append("<t0a.").append(endTimeField)
.append(" AND t1a.").append(endTimeField).append(">t0a.").append(endTimeField).append("-").append(intervalString);
addCriteria(sb,"t1a.",connectionName,filterCriteria,true);
- sb.append(") t6a GROUP BY bucket,windowstart,windowend) t2 ORDER BY bucket ASC, activitycount DESC) t3");
+ sb.append(") t6a GROUP BY bucket,windowstart,windowend) t2 ORDER BY bucket ASC, activitycount DESC");
Map otherColumns = new HashMap();
- otherColumns.put("idbucket","t3.bucket");
- otherColumns.put("activitycount","t3.activitycount");
- otherColumns.put("starttime","t3.windowstart");
- otherColumns.put("endtime","t3.windowend");
- String filteredQuery = constructDistinctOnClause(sb.toString(),new String[]{"idbucket"},otherColumns);
+ otherColumns.put("idbucket","bucket");
+ otherColumns.put("activitycount","activitycount");
+ otherColumns.put("starttime","windowstart");
+ otherColumns.put("endtime","windowend");
+ String filteredQuery = constructDistinctOnClause(null,sb.toString(),null,new String[]{"idbucket"},otherColumns);
sb = new StringBuffer("SELECT * FROM (");
sb.append(filteredQuery).append(") t4");
addOrdering(sb,new String[]{"activitycount","starttime","endtime","idbucket"},sort);
@@ -435,7 +435,7 @@ public class RepositoryHistoryManager ex
// end time of the current record. That's why there are two inner clauses with a UNION.
StringBuffer sb = new StringBuffer();
- sb.append("(SELECT * FROM (SELECT t6.bucket AS bucket,")
+ sb.append("SELECT * FROM (SELECT t6.bucket AS bucket,")
.append("t6.windowstart AS windowstart, t6.windowend AS windowend, SUM(t6.bytecount) AS bytecount")
.append(" FROM (SELECT ");
@@ -488,14 +488,14 @@ public class RepositoryHistoryManager ex
sb.append(" AND t1a.").append(startTimeField).append("<t0a.").append(endTimeField)
.append(" AND t1a.").append(endTimeField).append(">t0a.").append(endTimeField).append("-").append(intervalString);
addCriteria(sb,"t1a.",connectionName,filterCriteria,true);
- sb.append(") t6a GROUP BY bucket,windowstart,windowend) t2 ORDER BY bucket ASC, bytecount DESC) t3");
+ sb.append(") t6a GROUP BY bucket,windowstart,windowend) t2 ORDER BY bucket ASC, bytecount DESC");
Map otherColumns = new HashMap();
- otherColumns.put("idbucket","t3.bucket");
- otherColumns.put("bytecount","t3.bytecount");
- otherColumns.put("starttime","t3.windowstart");
- otherColumns.put("endtime","t3.windowend");
- String filteredQuery = constructDistinctOnClause(sb.toString(),new String[]{"idbucket"},otherColumns);
+ otherColumns.put("idbucket","bucket");
+ otherColumns.put("bytecount","bytecount");
+ otherColumns.put("starttime","windowstart");
+ otherColumns.put("endtime","windowend");
+ String filteredQuery = constructDistinctOnClause(null,sb.toString(),null,new String[]{"idbucket"},otherColumns);
sb = new StringBuffer("SELECT * FROM (");
sb.append(filteredQuery).append(") t4");
addOrdering(sb,new String[]{"bytecount","starttime","endtime","idbucket"},sort);