Return-Path: X-Original-To: apmail-incubator-connectors-commits-archive@minotaur.apache.org Delivered-To: apmail-incubator-connectors-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 4370B4266 for ; Thu, 2 Jun 2011 16:40:23 +0000 (UTC) Received: (qmail 96596 invoked by uid 500); 2 Jun 2011 16:40:23 -0000 Delivered-To: apmail-incubator-connectors-commits-archive@incubator.apache.org Received: (qmail 96552 invoked by uid 500); 2 Jun 2011 16:40:23 -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 96545 invoked by uid 99); 2 Jun 2011 16:40:23 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 Jun 2011 16:40:23 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.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; Thu, 02 Jun 2011 16:40:20 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id B26CD238890A; Thu, 2 Jun 2011 16:39:58 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1130644 - in /incubator/lcf/trunk/framework: core/src/main/java/org/apache/manifoldcf/core/database/DBInterfaceHSQLDB.java crawler-ui/src/main/webapp/maxactivityreport.jsp crawler-ui/src/main/webapp/maxbandwidthreport.jsp Date: Thu, 02 Jun 2011 16:39:58 -0000 To: connectors-commits@incubator.apache.org From: kwright@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20110602163958.B26CD238890A@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: kwright Date: Thu Jun 2 16:39:58 2011 New Revision: 1130644 URL: http://svn.apache.org/viewvc?rev=1130644&view=rev Log: Implement DISTINCT ON functionality for HSQLDB, as part of CONNECTORS-110 Modified: incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfaceHSQLDB.java incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxactivityreport.jsp incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxbandwidthreport.jsp Modified: incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfaceHSQLDB.java URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfaceHSQLDB.java?rev=1130644&r1=1130643&r2=1130644&view=diff ============================================================================== --- incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfaceHSQLDB.java (original) +++ incubator/lcf/trunk/framework/core/src/main/java/org/apache/manifoldcf/core/database/DBInterfaceHSQLDB.java Thu Jun 2 16:39:58 2011 @@ -896,38 +896,78 @@ public class DBInterfaceHSQLDB extends D // For HSQLDB, we want to generate the following: // WITH ct01 ( ... otherfields ... ) AS ( ... baseQuery ... ) // SELECT * FROM (SELECT DISTINCT ... distinctFields ... FROM ct01) AS ct02, - // LATERAL ( SELECT ... otherfields ... FROM ct01 WHERE ... distinctFields = ct02.distinctField ... ORDER BY ??? LIMIT 1) AS ct03 + // LATERAL ( SELECT ... otherfields ... FROM ct01 WHERE ... distinctFields = ct02.distinctField ... ORDER BY ... order by ... LIMIT 1) AS ct03 // - // The problem is that this requires the ORDER BY to not appear in the base query but appear in the LATERAL clause. The - // only solution involves pulling the ORDER BY into this abstraction, so that the base query does not include it and it gets added to - // the end in the appropriate place by this method. - // - // If we change the abstraction in that way, the ordering criteria should be a List of column names, and a parallel List of Booleans. - // We'll have to be very careful to apply the otherfields mapping to the temporary table also, and the order-by will need to be in terms - // of the TARGET column names, not the source column names. // Copy arguments if (baseParameters != null) outputParameters.addAll(baseParameters); - StringBuilder sb = new StringBuilder("SELECT "); + StringBuilder sb = new StringBuilder("WITH txxx1 ("); boolean needComma = false; Iterator iter = otherFields.keySet().iterator(); while (iter.hasNext()) { String fieldName = iter.next(); + if (needComma) + sb.append(","); + sb.append(fieldName); + needComma = true; + } + sb.append(") AS (SELECT "); + needComma = false; + iter = otherFields.keySet().iterator(); + while (iter.hasNext()) + { + String fieldName = iter.next(); String columnValue = otherFields.get(fieldName); if (needComma) sb.append(","); needComma = true; - sb.append("txxx1.").append(columnValue).append(" AS ").append(fieldName); + sb.append("txxx2.").append(columnValue).append(" AS ").append(fieldName); + } + sb.append(" FROM (").append(baseQuery).append(") txxx2)"); + sb.append(" SELECT * FROM (SELECT DISTINCT "); + Map distinctMap = new HashMap(); + int i = 0; + while (i < distinctFields.length) + { + String distinctField = distinctFields[i]; + if (i > 0) + sb.append(","); + sb.append(distinctField); + distinctMap.put(distinctField,distinctField); + i++; + } + sb.append(" FROM txxx1) AS txxx3, LATERAL (SELECT "); + iter = otherFields.keySet().iterator(); + needComma = false; + while (iter.hasNext()) + { + String fieldName = iter.next(); + if (distinctMap.get(fieldName) == null) + { + if (needComma) + sb.append(","); + needComma = true; + sb.append(fieldName); + } + } + sb.append(" FROM txxx1 WHERE "); + i = 0; + while (i < distinctFields.length) + { + String distinctField = distinctFields[i]; + if (i > 0) + sb.append(" AND "); + sb.append(distinctField).append("=txxx3.").append(distinctField); + i++; } - sb.append(" FROM (").append(baseQuery).append(") txxx1"); if (distinctFields.length > 0 || orderFields.length > 0) { sb.append(" ORDER BY "); int k = 0; - int i = 0; + i = 0; while (i < distinctFields.length) { if (k > 0) @@ -950,6 +990,7 @@ public class DBInterfaceHSQLDB extends D k++; } } + sb.append(" LIMIT 1) AS txxx4"); return sb.toString(); } Modified: incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxactivityreport.jsp URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxactivityreport.jsp?rev=1130644&r1=1130643&r2=1130644&view=diff ============================================================================== --- incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxactivityreport.jsp (original) +++ incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxactivityreport.jsp Thu Jun 2 16:39:58 2011 @@ -776,8 +776,8 @@ if (maintenanceUnderway == false) else idBucketString = idBucketObject.toString(); - String startTimeString = org.apache.manifoldcf.ui.util.Formatter.formatTime(((Long)row.getValue("starttime")).longValue()); - String endTimeString = org.apache.manifoldcf.ui.util.Formatter.formatTime(((Long)row.getValue("endtime")).longValue()); + String startTimeString = org.apache.manifoldcf.ui.util.Formatter.formatTime(new Long(row.getValue("starttime").toString()).longValue()); + String endTimeString = org.apache.manifoldcf.ui.util.Formatter.formatTime(new Long(row.getValue("endtime").toString()).longValue()); double activityCount = ((Double)row.getValue("activitycount")).doubleValue(); double activityRate = activityCount * 60000.0 / intervalMilliseconds; Modified: incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxbandwidthreport.jsp URL: http://svn.apache.org/viewvc/incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxbandwidthreport.jsp?rev=1130644&r1=1130643&r2=1130644&view=diff ============================================================================== --- incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxbandwidthreport.jsp (original) +++ incubator/lcf/trunk/framework/crawler-ui/src/main/webapp/maxbandwidthreport.jsp Thu Jun 2 16:39:58 2011 @@ -775,8 +775,8 @@ if (maintenanceUnderway == false) idBucketString = ""; else idBucketString = idBucketObject.toString(); - String startTimeString = org.apache.manifoldcf.ui.util.Formatter.formatTime(((Long)row.getValue("starttime")).longValue()); - String endTimeString = org.apache.manifoldcf.ui.util.Formatter.formatTime(((Long)row.getValue("endtime")).longValue()); + String startTimeString = org.apache.manifoldcf.ui.util.Formatter.formatTime(new Long(row.getValue("starttime").toString()).longValue()); + String endTimeString = org.apache.manifoldcf.ui.util.Formatter.formatTime(new Long(row.getValue("endtime").toString()).longValue()); long byteCount = new Long(row.getValue("bytecount").toString()).longValue(); double bandwidth = ((double)byteCount) * 1000.0 / intervalMilliseconds;