Return-Path: X-Original-To: apmail-db-torque-dev-archive@www.apache.org Delivered-To: apmail-db-torque-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id D0CC09F78 for ; Mon, 7 May 2012 13:41:34 +0000 (UTC) Received: (qmail 56356 invoked by uid 500); 7 May 2012 13:41:34 -0000 Delivered-To: apmail-db-torque-dev-archive@db.apache.org Received: (qmail 56333 invoked by uid 500); 7 May 2012 13:41:34 -0000 Mailing-List: contact torque-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Help: List-Post: List-Id: "Apache Torque Developers List" Reply-To: "Apache Torque Developers List" Delivered-To: mailing list torque-dev@db.apache.org Received: (qmail 56318 invoked by uid 500); 7 May 2012 13:41:34 -0000 Received: (qmail 56313 invoked by uid 99); 7 May 2012 13:41:34 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 07 May 2012 13:41:34 +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; Mon, 07 May 2012 13:41:32 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id B39C023888EA; Mon, 7 May 2012 13:41:11 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1335015 - in /db/torque/torque4/trunk: torque-runtime/src/main/java/org/apache/torque/criteria/ torque-runtime/src/main/java/org/apache/torque/sql/ torque-runtime/src/test/java/org/apache/torque/sql/ torque-test/src/test/java/org/apache/to... Date: Mon, 07 May 2012 13:41:11 -0000 To: torque-commits@db.apache.org From: tfischer@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20120507134111.B39C023888EA@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: tfischer Date: Mon May 7 13:41:10 2012 New Revision: 1335015 URL: http://svn.apache.org/viewvc?rev=1335015&view=rev Log: TORQUE-7 support other operator than equal in joins Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java (original) +++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java Mon May 7 13:41:10 2012 @@ -457,6 +457,39 @@ public class Criteria } /** + * Adds a join to the criteria, E.g. to create the condition + *

+ * PROJECT LEFT JOIN FOO ON PROJECT.PROJECT_ID=FOO.PROJECT_ID + *

use

+ * + * criteria.addJoin( + * ProjectPeer.PROJECT_ID, + * Criteria.NOT_EQUAL, + * FooPeer.PROJECT_ID, + * Criteria.LEFT_JOIN); + * + * + * @param left A String with the left side of the join. + * @param comparison the comparison operator, not null. + * The operator CUSTOM is not supported. + * @param right A String with the right side of the join. + * @param joinType The operator used for the join. Must be one of null, + * Criteria.LEFT_JOIN, Criteria.RIGHT_JOIN, Criteria.INNER_JOIN + * + * @return A modified Criteria object. + */ + public Criteria addJoin( + Column left, + SqlEnum comparison, + Column right, + JoinType joinType) + { + joins.add(new Join(left, comparison, right, joinType)); + + return this; + } + + /** * Get the List of Joins. * * @return a List which contains objects of type Join, not null. Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java (original) +++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java Mon May 7 13:41:10 2012 @@ -30,8 +30,7 @@ import org.apache.torque.Column; * 

  * table_a LEFT JOIN table_b ON table_a.id = table_b.a_id
  *
- * The class is immutable. Because the class is also used by - * {@link org.apache.torque.util.BasePeer}, it is visible from the package. + * The class is immutable. */ public class Join implements Serializable { @@ -41,6 +40,9 @@ public class Join implements Serializabl /** The left column of the join condition, not null. */ private Column leftColumn = null; + /** The comparison operator. The operator CUSTOM is not supported. */ + private SqlEnum comparison = SqlEnum.EQUAL; + /** The right column of the join condition, not null. */ private Column rightColumn = null; @@ -51,17 +53,54 @@ public class Join implements Serializabl private JoinType joinType = null; /** - * Constructor + * Constructor. + * + * @param leftColumn the left column of the join condition; + * might contain an alias name, not null. + * @param rightColumn the right column of the join condition + * might contain an alias name, not null. + * @param joinType the type of the join. Valid join types are + * null (adding the join condition to the where clause), + * SqlEnum.LEFT_JOIN, SqlEnum.RIGHT_JOIN, and SqlEnum.INNER_JOIN + */ + public Join( + final Column leftColumn, + final Column rightColumn, + final JoinType joinType) + { + if (leftColumn == null) + { + throw new NullPointerException("leftColumn is null"); + } + if (rightColumn == null) + { + throw new NullPointerException("rightColumn is null"); + } + this.leftColumn = leftColumn; + this.rightColumn = rightColumn; + this.joinType = joinType; + } + + /** + * Constructor with the comparison operator. + * * @param leftColumn the left column of the join condition; * might contain an alias name, not null. + * @param comparison the comparison, not null. + * The operator CUSTOM is not supported. * @param rightColumn the right column of the join condition * might contain an alias name, not null. * @param joinType the type of the join. Valid join types are * null (adding the join condition to the where clause), * SqlEnum.LEFT_JOIN, SqlEnum.RIGHT_JOIN, and SqlEnum.INNER_JOIN + * + * @throws NullPointerException if leftColumn, comparison or rightColumn + * are null. + * @throws IllegalArgumentException if comparison id SqlEnum.CUSTOM */ public Join( final Column leftColumn, + final SqlEnum comparison, final Column rightColumn, final JoinType joinType) { @@ -73,7 +112,17 @@ public class Join implements Serializabl { throw new NullPointerException("rightColumn is null"); } + if (comparison == null) + { + throw new NullPointerException("comparison is null"); + } + if (comparison == SqlEnum.CUSTOM) + { + throw new IllegalArgumentException( + "The comparison SqlEnum.CUSTOM is not supported for Joins."); + } this.leftColumn = leftColumn; + this.comparison = comparison; this.rightColumn = rightColumn; this.joinType = joinType; } @@ -88,7 +137,7 @@ public class Join implements Serializabl } /** - * @return the left column of the join condition + * @return the left column of the join condition. */ public final Column getLeftColumn() { @@ -96,7 +145,15 @@ public class Join implements Serializabl } /** - * @return the right column of the join condition + * @return the comaprsion operator between leftColumn and rightColumn, + */ + public final SqlEnum getComparison() + { + return comparison; + } + + /** + * @return the right column of the join condition. */ public final Column getRightColumn() { @@ -118,7 +175,7 @@ public class Join implements Serializabl .append(" : "); } result.append(leftColumn.getSqlExpression()) - .append("=") + .append(comparison) .append(rightColumn.getSqlExpression()) .append(" (ignoreCase not considered)"); Modified: db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java (original) +++ db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java Mon May 7 13:41:10 2012 @@ -28,6 +28,7 @@ import org.apache.torque.criteria.Criter import org.apache.torque.criteria.FromElement; import org.apache.torque.criteria.Join; import org.apache.torque.criteria.JoinType; +import org.apache.torque.criteria.SqlEnum; import org.apache.torque.util.UniqueList; /** @@ -78,6 +79,7 @@ public final class JoinBuilder { Join join = criteriaJoins.get(i); Column leftColumn = join.getLeftColumn(); + SqlEnum comparison = join.getComparison(); Column rightColumn = join.getRightColumn(); // Check the join type and add the join to the @@ -99,7 +101,7 @@ public final class JoinBuilder criteria, query); queryWhereClause.add( - buildJoinCondition(leftColumn, rightColumn)); + buildJoinCondition(leftColumn, comparison, rightColumn)); } else { @@ -140,7 +142,10 @@ public final class JoinBuilder FromElement fromElement = new FromElement( rightExpression.getSql().toString(), joinType, - buildJoinCondition(leftColumn, rightColumn), + buildJoinCondition( + leftColumn, + comparison, + rightColumn), rightExpression.getPreparedStatementReplacements()); queryFromClause.add(fromElement); } @@ -167,7 +172,7 @@ public final class JoinBuilder leftExpression.getSql().toString(), reverseJoinType(joinType), buildJoinCondition( - rightColumn, leftColumn), + leftColumn, comparison, rightColumn), leftExpression.getPreparedStatementReplacements()); queryFromClause.add(fromElement); } @@ -208,16 +213,18 @@ public final class JoinBuilder * Used to specify a join on two columns. * * @param column A column in one of the tables to be joined. + * @param operator the join operator. * @param relatedColumn The column in the other table to be joined. * @return A join expression, e.g. table_a.column_a=table_b.column_b. */ private static String buildJoinCondition( Column column, + SqlEnum operator, Column relatedColumn) { return column.getSqlExpression() - + '=' + + operator + relatedColumn.getSqlExpression(); } } Modified: db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java (original) +++ db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java Mon May 7 13:41:10 2012 @@ -917,6 +917,22 @@ public class SqlBuilderTest extends Base query.toString()); } + public void testInnerJoinImplicitWithComparison() + throws Exception + { + Criteria criteria = new Criteria(); + criteria.addSelectColumn(new ColumnImpl("table1.column")); + criteria.addJoin( + new ColumnImpl("table1.column1"), + Criteria.NOT_EQUAL, + new ColumnImpl("table2.column2"), + null); + Query query = SqlBuilder.buildQuery(criteria); + assertEquals( + "SELECT table1.column FROM table1, table2" + + " WHERE table1.column1<>table2.column2", + query.toString()); + } public void testInnerJoinExplicit() throws Exception @@ -955,7 +971,34 @@ public class SqlBuilderTest extends Base + " FROM table2 INNER JOIN table3" + " ON table2.column2=table3.column3" + " INNER JOIN table1" - + " ON table2.column2=table1.column1", + + " ON table1.column1=table2.column2", + query.toString()); + } + + public void testInnerJoinWithExcplicitExistingRightTableAndOperator() + throws Exception + { + Criteria criteria = new Criteria(); + criteria.addSelectColumn(new ColumnImpl("table1.column1")); + criteria.addJoin( + new ColumnImpl("table2.column2"), + Criteria.LESS_THAN, + new ColumnImpl("table3.column3"), + Criteria.INNER_JOIN); + criteria.addJoin( + new ColumnImpl("table1.column1"), + Criteria.GREATER_THAN, + new ColumnImpl("table2.column2"), + Criteria.INNER_JOIN); + Query query = SqlBuilder.buildQuery(criteria); + // second join condition must be changed in order to satisfy + // first join condition + assertEquals( + "SELECT table1.column1" + + " FROM table2 INNER JOIN table3" + + " ON table2.column2table2.column2", query.toString()); } @@ -1029,7 +1072,7 @@ public class SqlBuilderTest extends Base + " FROM table2 INNER JOIN table3" + " ON table2.column2=table3.column3" + " RIGHT JOIN table1" - + " ON table2.column2=table1.column1", + + " ON table1.column1=table2.column2", query.toString()); } @@ -1072,7 +1115,7 @@ public class SqlBuilderTest extends Base + " FROM table2 INNER JOIN table3" + " ON table2.column2=table3.column3" + " LEFT JOIN table1" - + " ON table2.column2=table1.column1", + + " ON table1.column1=table2.column2", query.toString()); } Modified: db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java (original) +++ db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java Mon May 7 13:41:10 2012 @@ -153,6 +153,37 @@ public class JoinTest extends BaseDataba } /** + * Test a join with an operator which is not equal. + * + * @throws Exception if the test fails + */ + public void testInnerJoinsOtherComparator() throws Exception + { + cleanBookstore(); + insertTestData(); + Criteria criteria = new Criteria(); + criteria.addJoin( + BookPeer.AUTHOR_ID, + Criteria.NOT_EQUAL, + AuthorPeer.AUTHOR_ID, + null); + + List authorList = AuthorPeer.doSelect(criteria); + + // Here we get 8 authors: + // three times the author with one book, + // four times the author without book, + // and one time the author with three books + if (authorList.size() != 8) + { + fail("book join author on not equals" + + "incorrect numbers of authors found : " + + authorList.size() + + ", should be 8"); + } + } + + /** * Test double join with aliases. * * @throws Exception if the test fails Modified: db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties (original) +++ db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties Mon May 7 13:41:10 2012 @@ -20,6 +20,7 @@ torque.database.default = bookstore torque.database.bookstore.adapter = derby +torque.transactionManager = org.apache.torque.util.TransactionManagerImpl #Using commons-dbcp torque.dsfactory.bookstore.factory = org.apache.torque.dsfactory.SharedPoolDataSourceFactory Modified: db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties (original) +++ db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties Mon May 7 13:41:10 2012 @@ -27,8 +27,12 @@ torque.database.bookstore.adapter = mssq # Using commons-dbcp torque.dsfactory.bookstore.factory = org.apache.torque.dsfactory.SharedPoolDataSourceFactory torque.dsfactory.bookstore.connection.driver = net.sourceforge.jtds.jdbc.Driver -torque.dsfactory.bookstore.connection.url = jdbc:jtds:sqlserver://fastnet.dukece.com:1433/bookstore -torque.dsfactory.bookstore.connection.user = sa -torque.dsfactory.bookstore.connection.password = +torque.dsfactory.bookstore.connection.url = jdbc:jtds:sqlserver://localhost:1433/torque +torque.dsfactory.bookstore.connection.user = torque +torque.dsfactory.bookstore.connection.password = torqueUserPassword +torque.dsfactory.bookstore.pool.defaultAutoCommit = false torque.dsfactory.bookstore.pool.validationQuery = SELECT 1 + +# use Caching. This property is only used if managers are used by generators. +torque.manager.useCache = true Modified: db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties (original) +++ db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties Mon May 7 13:41:10 2012 @@ -29,7 +29,7 @@ torque.dsfactory.bookstore.factory = org torque.dsfactory.bookstore.connection.driver = org.gjt.mm.mysql.Driver torque.dsfactory.bookstore.connection.url = jdbc:mysql://localhost:3306/bookstore torque.dsfactory.bookstore.connection.user = root -torque.dsfactory.bookstore.connection.password = +torque.dsfactory.bookstore.connection.password = mysql torque.dsfactory.bookstore.pool.validationQuery = SELECT 1 Modified: db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties?rev=1335015&r1=1335014&r2=1335015&view=diff ============================================================================== --- db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties (original) +++ db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties Mon May 7 13:41:10 2012 @@ -28,7 +28,10 @@ torque.database.bookstore.adapter = post torque.dsfactory.bookstore.factory = org.apache.torque.dsfactory.SharedPoolDataSourceFactory torque.dsfactory.bookstore.connection.driver = org.postgresql.Driver torque.dsfactory.bookstore.connection.url = jdbc:postgresql://localhost:5432/bookstore -torque.dsfactory.bookstore.connection.user = torque -torque.dsfactory.bookstore.connection.password = torque +torque.dsfactory.bookstore.connection.user = postgres +torque.dsfactory.bookstore.connection.password = postgresql torque.dsfactory.bookstore.pool.validationQuery = SELECT 1 + +# use Caching. This property is only used if managers are used by generators. +torque.manager.useCache = true \ No newline at end of file --------------------------------------------------------------------- To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org For additional commands, e-mail: torque-dev-help@db.apache.org