You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by aa...@apache.org on 2015/04/21 11:45:33 UTC
[1/2] cayenne git commit: CAY-2005 Improving generated DISTINCT
performance on PostgreSQL
Repository: cayenne
Updated Branches:
refs/heads/master 8ae86ba9d -> 5ad9e9ff8
CAY-2005 Improving generated DISTINCT performance on PostgreSQL
Project: http://git-wip-us.apache.org/repos/asf/cayenne/repo
Commit: http://git-wip-us.apache.org/repos/asf/cayenne/commit/27a99fab
Tree: http://git-wip-us.apache.org/repos/asf/cayenne/tree/27a99fab
Diff: http://git-wip-us.apache.org/repos/asf/cayenne/diff/27a99fab
Branch: refs/heads/master
Commit: 27a99fab4fb06725cba3e71d2a05d9fad129b260
Parents: 8ae86ba
Author: aadamchik <aa...@apache.org>
Authored: Tue Apr 21 12:26:47 2015 +0300
Committer: aadamchik <aa...@apache.org>
Committed: Tue Apr 21 12:38:55 2015 +0300
----------------------------------------------------------------------
.../translator/select/SelectTranslator.java | 13 +++-
.../dba/postgres/PostgresSelectTranslator.java | 76 ++++++++++++++------
.../org/apache/cayenne/query/SelectQueryIT.java | 18 +++++
docs/doc/src/main/resources/RELEASE-NOTES.txt | 1 +
4 files changed, 85 insertions(+), 23 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/cayenne/blob/27a99fab/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
----------------------------------------------------------------------
diff --git a/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java b/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
index 4c0aa4d..1b54a50 100644
--- a/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
+++ b/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
@@ -157,7 +157,7 @@ public class SelectTranslator extends QueryAssembler {
}
if (!suppressingDistinct) {
- queryBuf.append("DISTINCT ");
+ queryBuf.append(buildDistinctStatement() + " ");
}
}
@@ -212,6 +212,17 @@ public class SelectTranslator extends QueryAssembler {
cachedSqlString = queryBuf.toString();
return cachedSqlString;
}
+
+ /**
+ * Allows subclasses to insert their own dialect of DISTINCT statement to
+ * improve performance.
+ *
+ * @return string representing the DISTINCT statement
+ * @since 3.1.1
+ */
+ protected String buildDistinctStatement() {
+ return "DISTINCT";
+ }
/**
* @since 3.1
http://git-wip-us.apache.org/repos/asf/cayenne/blob/27a99fab/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java
----------------------------------------------------------------------
diff --git a/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java b/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java
index 3344e25..0bb8727 100644
--- a/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java
+++ b/cayenne-server/src/main/java/org/apache/cayenne/dba/postgres/PostgresSelectTranslator.java
@@ -22,6 +22,7 @@ package org.apache.cayenne.dba.postgres;
import java.sql.Connection;
import org.apache.cayenne.access.DataNode;
+import org.apache.cayenne.access.jdbc.ColumnDescriptor;
import org.apache.cayenne.access.translator.select.SelectTranslator;
import org.apache.cayenne.query.Query;
@@ -30,27 +31,58 @@ import org.apache.cayenne.query.Query;
*/
class PostgresSelectTranslator extends SelectTranslator {
- /**
- * @since 4.0
- */
- public PostgresSelectTranslator(Query query, DataNode dataNode, Connection connection) {
- super(query, dataNode, connection);
- }
-
- @Override
- protected void appendLimitAndOffsetClauses(StringBuilder buffer) {
-
- // limit results
- int offset = queryMetadata.getFetchOffset();
- int limit = queryMetadata.getFetchLimit();
-
- if (limit > 0) {
- buffer.append(" LIMIT ").append(limit);
- }
-
- if (offset > 0) {
- buffer.append(" OFFSET ").append(offset);
- }
- }
+ /**
+ * @since 4.0
+ */
+ public PostgresSelectTranslator(Query query, DataNode dataNode, Connection connection) {
+ super(query, dataNode, connection);
+ }
+
+ @Override
+ protected void appendLimitAndOffsetClauses(StringBuilder buffer) {
+
+ // limit results
+ int offset = queryMetadata.getFetchOffset();
+ int limit = queryMetadata.getFetchLimit();
+
+ if (limit > 0) {
+ buffer.append(" LIMIT ").append(limit);
+ }
+
+ if (offset > 0) {
+ buffer.append(" OFFSET ").append(offset);
+ }
+ }
+
+ /**
+ * @since 3.1.1
+ */
+ @Override
+ protected String buildDistinctStatement() {
+ StringBuilder builder = new StringBuilder();
+ builder.append("DISTINCT ");
+
+ boolean foundKey = false;
+
+ for (ColumnDescriptor column : getResultColumns()) {
+ if (column.getAttribute().isPrimaryKey()) {
+
+ if (foundKey) {
+ builder.append(", ");
+ } else {
+ builder.append("ON (");
+ foundKey = true;
+ }
+
+ builder.append(column.getQualifiedColumnName());
+ }
+ }
+
+ if (foundKey) {
+ builder.append(")");
+ }
+
+ return builder.toString();
+ }
}
http://git-wip-us.apache.org/repos/asf/cayenne/blob/27a99fab/cayenne-server/src/test/java/org/apache/cayenne/query/SelectQueryIT.java
----------------------------------------------------------------------
diff --git a/cayenne-server/src/test/java/org/apache/cayenne/query/SelectQueryIT.java b/cayenne-server/src/test/java/org/apache/cayenne/query/SelectQueryIT.java
index aeebf7e..422e4e6 100644
--- a/cayenne-server/src/test/java/org/apache/cayenne/query/SelectQueryIT.java
+++ b/cayenne-server/src/test/java/org/apache/cayenne/query/SelectQueryIT.java
@@ -70,11 +70,13 @@ public class SelectQueryIT extends ServerCase {
private UnitDbAdapter accessStackAdapter;
private TableHelper tArtist;
+ private TableHelper tPainting;
@Before
public void before() {
this.tArtist = new TableHelper(dbHelper, "ARTIST").setColumns("ARTIST_ID", "ARTIST_NAME", "DATE_OF_BIRTH")
.setColumnTypes(Types.BIGINT, Types.CHAR, Types.DATE);
+ tPainting = new TableHelper(dbHelper, "PAINTING").setColumns("PAINTING_ID", "ARTIST_ID", "PAINTING_TITLE");
}
protected void createArtistsDataSet() throws Exception {
@@ -92,6 +94,22 @@ public class SelectQueryIT extends ServerCase {
}
@Test
+ public void testSelect_QualfierOnToMany() throws Exception {
+
+ tArtist.insert(1, "A1", new java.sql.Date(System.currentTimeMillis()));
+ tPainting.insert(4, 1, "P1");
+ tPainting.insert(5, 1, "P2");
+ tPainting.insert(6, null, "P3");
+
+ List<Artist> objects = SelectQuery.query(Artist.class,
+ Artist.PAINTING_ARRAY.dot(Painting.PAINTING_TITLE).like("P%")).select(context);
+
+ // make sure no duplicate objects are returned when matching on a
+ // to-many relationship
+ assertEquals(1, objects.size());
+ }
+
+ @Test
public void testFetchLimit() throws Exception {
createArtistsDataSet();
http://git-wip-us.apache.org/repos/asf/cayenne/blob/27a99fab/docs/doc/src/main/resources/RELEASE-NOTES.txt
----------------------------------------------------------------------
diff --git a/docs/doc/src/main/resources/RELEASE-NOTES.txt b/docs/doc/src/main/resources/RELEASE-NOTES.txt
index 2b634f7..05564c8 100644
--- a/docs/doc/src/main/resources/RELEASE-NOTES.txt
+++ b/docs/doc/src/main/resources/RELEASE-NOTES.txt
@@ -19,6 +19,7 @@ CAY-1992 Allow to exclude DataMap java class from Modeler class generation
CAY-1995 Add support for iterators to Select
CAY-2001 Saving a display state of Project
CAY-2004 EJBQL: Support for ordering on aggregate expressions
+CAY-2005 Improving generated DISTINCT performance on PostgreSQL
Bug Fixes:
[2/2] cayenne git commit: deprecated API
Posted by aa...@apache.org.
deprecated API
Project: http://git-wip-us.apache.org/repos/asf/cayenne/repo
Commit: http://git-wip-us.apache.org/repos/asf/cayenne/commit/5ad9e9ff
Tree: http://git-wip-us.apache.org/repos/asf/cayenne/tree/5ad9e9ff
Diff: http://git-wip-us.apache.org/repos/asf/cayenne/diff/5ad9e9ff
Branch: refs/heads/master
Commit: 5ad9e9ff82f608b22901ecc0ee19a874766e2e6e
Parents: 27a99fa
Author: aadamchik <aa...@apache.org>
Authored: Tue Apr 21 12:45:22 2015 +0300
Committer: aadamchik <aa...@apache.org>
Committed: Tue Apr 21 12:45:22 2015 +0300
----------------------------------------------------------------------
.../cayenne/access/translator/select/SelectTranslator.java | 5 +++--
1 file changed, 3 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/cayenne/blob/5ad9e9ff/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
----------------------------------------------------------------------
diff --git a/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java b/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
index 1b54a50..632a5e5 100644
--- a/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
+++ b/cayenne-server/src/main/java/org/apache/cayenne/access/translator/select/SelectTranslator.java
@@ -24,6 +24,7 @@ import org.apache.cayenne.access.DataNode;
import org.apache.cayenne.access.jdbc.ColumnDescriptor;
import org.apache.cayenne.dba.QuotingStrategy;
import org.apache.cayenne.exp.Expression;
+import org.apache.cayenne.exp.ExpressionFactory;
import org.apache.cayenne.exp.parser.ASTDbPath;
import org.apache.cayenne.map.DataMap;
import org.apache.cayenne.map.DbAttribute;
@@ -415,7 +416,7 @@ public class SelectTranslator extends QueryAssembler {
// for each relationship path add PK of the target entity...
for (String path : ((PrefetchSelectQuery) query).getResultPaths()) {
- ASTDbPath pathExp = (ASTDbPath) oe.translateToDbPath(Expression.fromString(path));
+ ASTDbPath pathExp = (ASTDbPath) oe.translateToDbPath(ExpressionFactory.exp(path));
// add joins and find terminating element
@@ -468,7 +469,7 @@ public class SelectTranslator extends QueryAssembler {
// for each prefetch add all joins plus columns from the target
// entity
- Expression prefetchExp = Expression.fromString(prefetch.getPath());
+ Expression prefetchExp = ExpressionFactory.exp(prefetch.getPath());
ASTDbPath dbPrefetch = (ASTDbPath) oe.translateToDbPath(prefetchExp);
resetJoinStack();