Return-Path: Delivered-To: apmail-incubator-connectors-commits-archive@minotaur.apache.org Received: (qmail 8362 invoked from network); 7 Dec 2010 03:09:50 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 7 Dec 2010 03:09:50 -0000 Received: (qmail 85510 invoked by uid 500); 7 Dec 2010 03:09:50 -0000 Delivered-To: apmail-incubator-connectors-commits-archive@incubator.apache.org Received: (qmail 85442 invoked by uid 500); 7 Dec 2010 03:09:50 -0000 Mailing-List: contact connectors-commits-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: connectors-dev@incubator.apache.org Delivered-To: mailing list connectors-commits@incubator.apache.org Received: (qmail 85431 invoked by uid 99); 7 Dec 2010 03:09:48 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 07 Dec 2010 03:09:48 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 07 Dec 2010 03:09:44 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 4F97D23888E8; Tue, 7 Dec 2010 03:09:23 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1042896 - in /incubator/lcf/trunk/framework: core/src/main/java/org/apache/manifoldcf/core/database/DBInterfacePostgreSQL.java pull-agent/src/main/java/org/apache/manifoldcf/crawler/repository/RepositoryHistoryManager.java Date: Tue, 07 Dec 2010 03:09:23 -0000 To: connectors-commits@incubator.apache.org From: kwright@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20101207030923.4F97D23888E8@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: kwright Date: Tue Dec 7 03:09:22 2010 New Revision: 1042896 URL: http://svn.apache.org/viewvc?rev=1042896&view=rev Log: Revise history queries to use bound variables everywhere a non-constant parameter is used. Also fix a broken distinct-on clause for postgresql Modified: incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfacePostgreSQL.java incubator/lcf/trunk/framework/pull-agent/src/main/java/org/apache/manifoldcf/crawler/repository/RepositoryHistoryManager.java Modified: incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfacePostgreSQL.java URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfacePostgreSQL.java?rev=1042896&r1=1042895&r2=1042896&view=diff ============================================================================== --- incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfacePostgreSQL.java (original) +++ incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfacePostgreSQL.java Tue Dec 7 03:09:22 2010 @@ -913,7 +913,7 @@ public class DBInterfacePostgreSQL exten sb.append(","); sb.append(distinctFields[i++]); } - sb.append(" "); + sb.append(") "); Iterator iter = otherFields.keySet().iterator(); boolean needComma = false; while (iter.hasNext()) Modified: incubator/lcf/trunk/framework/pull-agent/src/main/java/org/apache/manifoldcf/crawler/repository/RepositoryHistoryManager.java URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/framework/pull-agent/src/main/java/org/apache/manifoldcf/crawler/repository/RepositoryHistoryManager.java?rev=1042896&r1=1042895&r2=1042896&view=diff ============================================================================== --- incubator/lcf/trunk/framework/pull-agent/src/main/java/org/apache/manifoldcf/crawler/repository/RepositoryHistoryManager.java (original) +++ incubator/lcf/trunk/framework/pull-agent/src/main/java/org/apache/manifoldcf/crawler/repository/RepositoryHistoryManager.java Tue Dec 7 03:09:22 2010 @@ -258,17 +258,18 @@ public class RepositoryHistoryManager ex { // Build the query. StringBuffer sb = new StringBuffer("SELECT "); + ArrayList list = new ArrayList(); sb.append(idField).append(" AS id,").append(activityTypeField).append(" AS activity,").append(startTimeField).append(" AS starttime,(") .append(endTimeField).append("-").append(startTimeField).append(")") .append(" AS elapsedtime,").append(resultCodeField).append(" AS resultcode,").append(resultDescriptionField) .append(" AS resultdesc,").append(dataSizeField).append(" AS bytes,").append(entityIdentifierField) .append(" AS identifier FROM ").append(getTableName()); - addCriteria(sb,"",connectionName,criteria,false); + addCriteria(sb,list,"",connectionName,criteria,false); // Note well: We can't order by "identifier" in all databases, so in order to guarantee order we use "id". This will force a specific internal // order for the OFFSET/LIMIT clause. We include "starttime" because that's the default ordering. addOrdering(sb,new String[]{"starttime","id"},sort); addLimits(sb,startRow,maxRowCount); - return performQuery(sb.toString(),null,null,null,maxRowCount); + return performQuery(sb.toString(),list,null,null,maxRowCount); } /** Count the number of rows specified by a given set of criteria. This can be used to make decisions @@ -281,9 +282,10 @@ public class RepositoryHistoryManager ex throws ManifoldCFException { StringBuffer sb = new StringBuffer("SELECT COUNT(*) AS countcol FROM "); + ArrayList list = new ArrayList(); sb.append(getTableName()); - addCriteria(sb,"",connectionName,criteria,false); - IResultSet set = performQuery(sb.toString(),null,null,null); + addCriteria(sb,list,"",connectionName,criteria,false); + IResultSet set = performQuery(sb.toString(),list,null,null); if (set.getRowCount() < 1) throw new ManifoldCFException("Expected at least one row"); IResultRow row = set.getRow(0); @@ -330,6 +332,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(); + ArrayList list = new ArrayList(); 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 "); @@ -347,16 +350,16 @@ public class RepositoryHistoryManager ex .append(" END)) AS DOUBLE PRECISION) / CAST((t1.").append(endTimeField).append("-t1.").append(startTimeField) .append(") AS DOUBLE PRECISION)") .append(" AS activitycount FROM (SELECT DISTINCT "); - addBucketExtract(sb,"",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"",entityIdentifierField,idBucket); sb.append(" AS bucket,").append(startTimeField).append(" FROM ").append(getTableName()); - addCriteria(sb,"",connectionName,filterCriteria,false); + addCriteria(sb,list,"",connectionName,filterCriteria,false); sb.append(") t0,") .append(getTableName()).append(" t1 WHERE "); sb.append("t0.bucket="); - addBucketExtract(sb,"t1.",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"t1.",entityIdentifierField,idBucket); sb.append(" AND t1.").append(startTimeField).append("t0.").append(startTimeField); - addCriteria(sb,"t1.",connectionName,filterCriteria,true); + addCriteria(sb,list,"t1.",connectionName,filterCriteria,true); sb.append(") t6 GROUP BY bucket,windowstart,windowend UNION SELECT t6a.bucket AS bucket,") .append("t6a.windowstart AS windowstart, t6a.windowend AS windowend, SUM(t6a.activitycount) AS activitycount") .append(" FROM (SELECT "); @@ -372,16 +375,16 @@ public class RepositoryHistoryManager ex .append(" END)) AS DOUBLE PRECISION) / CAST((t1a.").append(endTimeField).append("-t1a.").append(startTimeField) .append(") AS DOUBLE PRECISION)") .append(" AS activitycount FROM (SELECT DISTINCT "); - addBucketExtract(sb,"",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"",entityIdentifierField,idBucket); sb.append(" AS bucket,").append(endTimeField).append(" FROM ").append(getTableName()); - addCriteria(sb,"",connectionName,filterCriteria,false); + addCriteria(sb,list,"",connectionName,filterCriteria,false); sb.append(") t0a,") .append(getTableName()).append(" t1a WHERE "); sb.append("t0a.bucket="); - addBucketExtract(sb,"t1a.",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"t1a.",entityIdentifierField,idBucket); sb.append(" AND t1a.").append(startTimeField).append("t0a.").append(endTimeField).append("-").append(intervalString); - addCriteria(sb,"t1a.",connectionName,filterCriteria,true); + addCriteria(sb,list,"t1a.",connectionName,filterCriteria,true); sb.append(") t6a GROUP BY bucket,windowstart,windowend) t2 ORDER BY bucket ASC, activitycount DESC"); Map otherColumns = new HashMap(); @@ -389,12 +392,13 @@ public class RepositoryHistoryManager ex 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); - addLimits(sb,startRow,maxRowCount); - return performQuery(sb.toString(),null,null,null,maxRowCount); + + StringBuffer newsb = new StringBuffer("SELECT * FROM ("); + ArrayList newList = new ArrayList(); + newsb.append(constructDistinctOnClause(newList,sb.toString(),list,new String[]{"idbucket"},otherColumns)).append(") t4"); + addOrdering(newsb,new String[]{"activitycount","starttime","endtime","idbucket"},sort); + addLimits(newsb,startRow,maxRowCount); + return performQuery(newsb.toString(),newList,null,null,maxRowCount); } @@ -435,6 +439,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(); + ArrayList list = new ArrayList(); 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 "); @@ -453,16 +458,16 @@ public class RepositoryHistoryManager ex .append(" END)) / (t1.").append(endTimeField).append("-t1.").append(startTimeField) .append(")") .append(" AS bytecount FROM (SELECT DISTINCT "); - addBucketExtract(sb,"",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"",entityIdentifierField,idBucket); sb.append(" AS bucket,").append(startTimeField).append(" FROM ").append(getTableName()); - addCriteria(sb,"",connectionName,filterCriteria,false); + addCriteria(sb,list,"",connectionName,filterCriteria,false); sb.append(") t0,") .append(getTableName()).append(" t1 WHERE "); sb.append("t0.bucket="); - addBucketExtract(sb,"t1.",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"t1.",entityIdentifierField,idBucket); sb.append(" AND t1.").append(startTimeField).append("t0.").append(startTimeField); - addCriteria(sb,"t1.",connectionName,filterCriteria,true); + addCriteria(sb,list,"t1.",connectionName,filterCriteria,true); sb.append(") t6 GROUP BY bucket,windowstart,windowend UNION SELECT t6a.bucket AS bucket,") .append("t6a.windowstart AS windowstart, t6a.windowend AS windowend, SUM(t6a.bytecount) AS bytecount") .append(" FROM (SELECT ") @@ -478,16 +483,16 @@ public class RepositoryHistoryManager ex .append(" END)) / (t1a.").append(endTimeField).append("-t1a.").append(startTimeField) .append(")") .append(" AS bytecount FROM (SELECT DISTINCT "); - addBucketExtract(sb,"",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"",entityIdentifierField,idBucket); sb.append(" AS bucket,").append(endTimeField).append(" FROM ").append(getTableName()); - addCriteria(sb,"",connectionName,filterCriteria,false); + addCriteria(sb,list,"",connectionName,filterCriteria,false); sb.append(") t0a,") .append(getTableName()).append(" t1a WHERE "); sb.append("t0a.bucket="); - addBucketExtract(sb,"t1a.",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"t1a.",entityIdentifierField,idBucket); sb.append(" AND t1a.").append(startTimeField).append("t0a.").append(endTimeField).append("-").append(intervalString); - addCriteria(sb,"t1a.",connectionName,filterCriteria,true); + addCriteria(sb,list,"t1a.",connectionName,filterCriteria,true); sb.append(") t6a GROUP BY bucket,windowstart,windowend) t2 ORDER BY bucket ASC, bytecount DESC"); Map otherColumns = new HashMap(); @@ -495,12 +500,12 @@ public class RepositoryHistoryManager ex 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); - addLimits(sb,startRow,maxRowCount); - return performQuery(sb.toString(),null,null,null,maxRowCount); + StringBuffer newsb = new StringBuffer("SELECT * FROM ("); + ArrayList newList = new ArrayList(); + newsb.append(constructDistinctOnClause(newList,sb.toString(),list,new String[]{"idbucket"},otherColumns)).append(") t4"); + addOrdering(newsb,new String[]{"bytecount","starttime","endtime","idbucket"},sort); + addLimits(newsb,startRow,maxRowCount); + return performQuery(newsb.toString(),newList,null,null,maxRowCount); } /** Get a bucketed history of different result code/identifier combinations. @@ -519,33 +524,36 @@ public class RepositoryHistoryManager ex // ORDER BY xxx LIMIT yyy OFFSET zzz StringBuffer sb = new StringBuffer("SELECT t1.resultcodebucket,t1.idbucket,COUNT('x') AS eventcount FROM (SELECT "); - addBucketExtract(sb,"",resultCodeField,resultCodeBucket); + ArrayList list = new ArrayList(); + addBucketExtract(sb,list,"",resultCodeField,resultCodeBucket); sb.append(" AS resultcodebucket, "); - addBucketExtract(sb,"",entityIdentifierField,idBucket); + addBucketExtract(sb,list,"",entityIdentifierField,idBucket); sb.append(" AS idbucket FROM ").append(getTableName()); - addCriteria(sb,"",connectionName,filterCriteria,false); + addCriteria(sb,list,"",connectionName,filterCriteria,false); sb.append(") t1 GROUP BY resultcodebucket,idbucket"); addOrdering(sb,new String[]{"eventcount","resultcodebucket","idbucket"},sort); addLimits(sb,startRow,maxRowCount); - return performQuery(sb.toString(),null,null,null,maxRowCount); + return performQuery(sb.toString(),list,null,null,maxRowCount); } /** Turn a bucket description into a return column. * This is complicated by the fact that the extraction code is inherently case sensitive. So if case insensitive is * desired, that means we whack the whole thing to lower case before doing the match. */ - protected void addBucketExtract(StringBuffer sb, String columnPrefix, String columnName, BucketDescription bucketDesc) + protected void addBucketExtract(StringBuffer sb, ArrayList list, String columnPrefix, String columnName, BucketDescription bucketDesc) { boolean isSensitive = bucketDesc.isSensitive(); - sb.append(constructSubstringClause(columnPrefix+columnName,quoteSQLString(bucketDesc.getRegexp()),!isSensitive)); + sb.append(constructSubstringClause(columnPrefix+columnName,"?",!isSensitive)); + list.add(bucketDesc.getRegexp()); } /** Add criteria clauses to query. */ - protected boolean addCriteria(StringBuffer sb, String fieldPrefix, String connectionName, FilterCriteria criteria, boolean whereEmitted) + protected boolean addCriteria(StringBuffer sb, ArrayList list, String fieldPrefix, String connectionName, FilterCriteria criteria, boolean whereEmitted) { whereEmitted = emitClauseStart(sb,whereEmitted); - sb.append(fieldPrefix).append(ownerNameField).append("=").append(quoteSQLString(connectionName)); + sb.append(fieldPrefix).append(ownerNameField).append("=?"); + list.add(connectionName); String[] activities = criteria.getActivities(); if (activities != null) @@ -564,7 +572,8 @@ public class RepositoryHistoryManager ex if (i > 0) sb.append(","); String activity = activities[i++]; - sb.append(quoteSQLString(activity)); + sb.append("?"); + list.add(activity); } sb.append(")"); } @@ -588,14 +597,16 @@ public class RepositoryHistoryManager ex if (entityMatch != null) { whereEmitted = emitClauseStart(sb,whereEmitted); - sb.append(constructRegexpClause(fieldPrefix+entityIdentifierField,quoteSQLString(entityMatch.getRegexpString()),entityMatch.isInsensitive())); + sb.append(constructRegexpClause(fieldPrefix+entityIdentifierField,"?",entityMatch.isInsensitive())); + list.add(entityMatch.getRegexpString()); } RegExpCriteria resultCodeMatch = criteria.getResultCodeMatch(); if (resultCodeMatch != null) { whereEmitted = emitClauseStart(sb,whereEmitted); - sb.append(constructRegexpClause(fieldPrefix+resultCodeField,quoteSQLString(resultCodeMatch.getRegexpString()),resultCodeMatch.isInsensitive())); + sb.append(constructRegexpClause(fieldPrefix+resultCodeField,"?",resultCodeMatch.isInsensitive())); + list.add(resultCodeMatch.getRegexpString()); } return whereEmitted;