db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tfisc...@apache.org
Subject svn commit: r1625391 - in /db/torque/torque4/trunk: torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java
Date Tue, 16 Sep 2014 21:05:33 GMT
Author: tfischer
Date: Tue Sep 16 21:05:32 2014
New Revision: 1625391

URL: http://svn.apache.org/r1625391
Log:
TORQUE-322 docs and test for set operations

Added:
    db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java
Modified:
    db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml

Modified: db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml?rev=1625391&r1=1625390&r2=1625391&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml
(original)
+++ db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml
Tue Sep 16 21:05:32 2014
@@ -921,6 +921,42 @@ SELECT BOOK.BOOK_ID, BOOK.TITLE FROM BOO
     
   </section>
 
+  <section name="Set operations (UNION, EXCEPT, INTERSECT)">
+    <p>
+      Torque supports the set operations UNION, EXCEPT and INTERSECT.
+      MINUS is the Oracle variant of EXCEPT and is also implicitly supported;
+      Torque knows that for oracle, EXCEPT must be converted to MINUS in SQL.
+    </p>
+    
+    <p>
+      Queries using the set operators can be created using the methods 
+      union, unionAll, except, exceptAll, intersect and intersectAll
+      in Criteria.
+      For example:
+    </p>
+    
+<source><![CDATA[
+Criteria criteria = new Criteria()
+    .where(BookPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
+    .addSelectColumn(BookPeer.BOOK_ID);
+Criteria otherCriteria = new Criteria()
+    .where(BookPeer.AUTHOR_ID, authorList.get(1).getAuthorId(), Criteria.LESS_EQUAL)
+    .addSelectColumn(BookPeer.BOOK_ID);
+criteria.union(otherCriteria);
+
+List<Integer> result = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+]]></source>
+    <p>
+      Note that the resulting Criteria from a set operation results in
+      a so-called composite Criteria, for which some operations (e.g. group by)
+      cannot be performed (because the resulting SQL would make no sense). 
+      Instead, these operations must be performed on the parts of which
+      the Criteria consists.
+      
+    </p>
+  </section>
+
   <section name="Locking">
     <subsection name="Pessimistically lock table rows within a transaction">
       <p>

Added: db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java?rev=1625391&view=auto
==============================================================================
--- db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java
(added)
+++ db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java
Tue Sep 16 21:05:32 2014
@@ -0,0 +1,467 @@
+package org.apache.torque.generated.peer;
+
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.torque.BaseDatabaseTestCase;
+import org.apache.torque.ColumnImpl;
+import org.apache.torque.TorqueException;
+import org.apache.torque.adapter.MssqlAdapter;
+import org.apache.torque.adapter.MysqlAdapter;
+import org.apache.torque.adapter.OracleAdapter;
+import org.apache.torque.criteria.Criteria;
+import org.apache.torque.om.mapper.IntegerMapper;
+import org.apache.torque.test.dbobject.Author;
+import org.apache.torque.test.dbobject.Book;
+import org.apache.torque.test.peer.AuthorPeer;
+import org.apache.torque.test.peer.BookPeer;
+
+/**
+ * Tests simple selects.
+ *
+ * @version $Id: SelectTest.java 1448403 2013-02-20 20:55:15Z tfischer $
+ */
+public class SelectSetOperationTest extends BaseDatabaseTestCase
+{
+    private static Log log = LogFactory.getLog(SelectSetOperationTest.class);
+
+    private List<Author> authorList;
+
+    @Override
+    public void setUp() throws Exception
+    {
+        super.setUp();
+        cleanBookstore();
+        authorList = insertBookstoreData();
+    }
+
+    /**
+     * Tests a select with UNION
+     *
+     * @throws Exception if the test fails
+     */
+    public void testUnion() throws Exception
+    {
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(0).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.union(otherCriteria);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(20, result.size());
+
+        Set<Integer> expected = new HashSet<Integer>();
+        addBookIds(authorList.get(0), expected);
+        addBookIds(authorList.get(1), expected);
+        assertEquals(expected, new HashSet<Integer>(result));
+    }
+
+    /**
+     * Tests a select with UNION ALL
+     *
+     * @throws Exception if the test fails
+     */
+    public void testUnionAll() throws Exception
+    {
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(0).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.unionAll(otherCriteria);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(30, result.size());
+
+        // check unique contents of list
+        Set<Integer> expected = new HashSet<Integer>();
+        addBookIds(authorList.get(0), expected);
+        addBookIds(authorList.get(1), expected);
+        assertEquals(expected, new HashSet<Integer>(result));
+
+        // check that books of first author occur multiple times (in fact twice)
+        // in the list
+        for (int i = 0; i < 10; ++i)
+        {
+            assertFalse(result.indexOf(authorList.get(0).getBooks().get(i).getBookId())
+                == result.lastIndexOf(authorList.get(0).getBooks().get(i).getBookId()));
+        }
+    }
+
+    /**
+     * Tests a select with EXCEPT
+     *
+     * @throws Exception if the test fails
+     */
+    public void testExcept() throws Exception
+    {
+        if (defaultAdapter instanceof MysqlAdapter)
+        {
+            log.error("testExcept(): "
+                + "MySQL does not support "
+                + "the EXCEPT ALL operator");
+            return;
+        }
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.unionAll(otherCriteria);
+        otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                authorList.get(0).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.except(otherCriteria);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(10, result.size());
+
+        Set<Integer> expected = new HashSet<Integer>();
+        addBookIds(authorList.get(1), expected);
+        assertEquals(expected, new HashSet<Integer>(result));
+    }
+
+    /**
+     * Tests a select with EXCEPT ALL.
+     *
+     * @throws Exception if the test fails
+     */
+    public void testExceptAll() throws Exception
+    {
+        if (defaultAdapter instanceof OracleAdapter
+                || defaultAdapter instanceof MysqlAdapter
+                || defaultAdapter instanceof MssqlAdapter)
+        {
+            log.error("testExceptAll(): "
+                + "Oracle, MySQL and MSSQL do not support "
+                + "the EXCEPT ALL operator");
+            return;
+        }
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.unionAll(otherCriteria);
+        otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                authorList.get(0).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.exceptAll(otherCriteria);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(20, result.size());
+
+        // check unique result
+        Set<Integer> expected = new HashSet<Integer>();
+        addBookIds(authorList.get(1), expected);
+        assertEquals(expected, new HashSet<Integer>(result));
+
+        // check that books of the author occur multiple times (in fact twice)
+        // in the list
+        for (int i = 0; i < 10; ++i)
+        {
+            assertFalse(result.indexOf(authorList.get(1).getBooks().get(i).getBookId())
+                == result.lastIndexOf(authorList.get(1).getBooks().get(i).getBookId()));
+        }
+    }
+
+    /**
+     * Tests a select with EXCEPT ALL. The Set from which the Except statement
+     * is executed contains the removable numbers twice,
+     * Except all only removes one occurance so the other remains.
+     *
+     * @throws Exception if the test fails
+     */
+    public void testExceptAllMultipleOccurances() throws Exception
+    {
+        if (defaultAdapter instanceof OracleAdapter
+                || defaultAdapter instanceof MysqlAdapter
+                || defaultAdapter instanceof MssqlAdapter)
+        {
+            log.error("testExceptAllMultipleOccurances(): "
+                + "Oracle, MySQL and MSSQL do not support "
+                + "the EXCEPT ALL operator");
+            return;
+        }
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.unionAll(otherCriteria);
+        otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                authorList.get(0).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.exceptAll(otherCriteria);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(30, result.size());
+
+        // check unique result
+        Set<Integer> expected = new HashSet<Integer>();
+        addBookIds(authorList.get(0), expected);
+        addBookIds(authorList.get(1), expected);
+        assertEquals(expected, new HashSet<Integer>(result));
+
+        // check that books of the second author occur multiple times (in fact twice)
+        // in the list
+        for (int i = 0; i < 10; ++i)
+        {
+            assertFalse(result.indexOf(authorList.get(1).getBooks().get(i).getBookId())
+                == result.lastIndexOf(authorList.get(1).getBooks().get(i).getBookId()));
+        }
+    }
+
+    /**
+     * Tests a select with INTERSECT
+     *
+     * @throws Exception if the test fails
+     */
+    public void testIntersect() throws Exception
+    {
+        if (defaultAdapter instanceof MysqlAdapter)
+        {
+            log.error("testIntersect(): "
+                    + "MySQL does not support "
+                    + "the EXCEPT ALL operator");
+            return;
+        }
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.unionAll(otherCriteria);
+        Criteria intersectCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                authorList.get(1).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                authorList.get(1).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        intersectCriteria.unionAll(otherCriteria);
+        criteria.intersect(intersectCriteria);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(10, result.size());
+
+        Set<Integer> expected = new HashSet<Integer>();
+        addBookIds(authorList.get(1), expected);
+
+        assertEquals(expected, new HashSet<Integer>(result));
+    }
+
+    /**
+     * Tests a select with INTERSECT ALL. The common set is contained twice
+     * in the intersected sets, so the intersection contains the numbers twice.
+     *
+     * @throws Exception if the test fails
+     */
+    public void testIntersectAllMultipleOccurances() throws Exception
+    {
+        if (defaultAdapter instanceof OracleAdapter
+                || defaultAdapter instanceof MysqlAdapter
+                || defaultAdapter instanceof MssqlAdapter)
+        {
+            log.error("testIntersectAllMultipleOccurances(): "
+                + "Oracle, MySQL and MSSQL do not support "
+                + "the EXCEPT ALL operator");
+            return;
+        }
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.unionAll(otherCriteria);
+        Criteria intersectCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                authorList.get(1).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                authorList.get(1).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        intersectCriteria.unionAll(otherCriteria);
+        criteria.intersectAll(intersectCriteria);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(20, result.size());
+
+        // check unique result
+        Set<Integer> expected = new HashSet<Integer>();
+        addBookIds(authorList.get(1), expected);
+        assertEquals(expected, new HashSet<Integer>(result));
+
+        // check that books of the author occur multiple times (in fact twice)
+        // in the list
+        for (int i = 0; i < 10; ++i)
+        {
+            assertFalse(result.indexOf(authorList.get(1).getBooks().get(i).getBookId())
+                == result.lastIndexOf(authorList.get(1).getBooks().get(i).getBookId()));
+        }
+    }
+
+    /**
+     * Tests a select with INTERSECT ALL.
+     *
+     * @throws Exception if the test fails
+     */
+    public void testIntersectAll() throws Exception
+    {
+        if (defaultAdapter instanceof OracleAdapter
+                || defaultAdapter instanceof MysqlAdapter
+                || defaultAdapter instanceof MssqlAdapter)
+        {
+            log.error("testIntersectAll(): "
+                + "Oracle, MySQL and MSSQL do not support "
+                + "the EXCEPT ALL operator");
+            return;
+        }
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(1).getAuthorId(),
+                    Criteria.LESS_EQUAL)
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.unionAll(otherCriteria);
+        otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                authorList.get(1).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.intersectAll(otherCriteria);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(10, result.size());
+
+        // check unique result
+        Set<Integer> expected = new HashSet<Integer>();
+        addBookIds(authorList.get(1), expected);
+        assertEquals(expected, new HashSet<Integer>(result));
+    }
+
+    private void addBookIds(
+                final Author author,
+                final Collection<Integer> expected)
+            throws TorqueException
+    {
+        for (Book book : author.getBooks())
+        {
+            expected.add(book.getBookId());
+        }
+    }
+
+    /**
+     * Tests a select with UNION ALL, order by, limit and offset
+     *
+     * @throws Exception if the test fails
+     */
+    public void testUnionAllOrderByLimitOffset() throws Exception
+    {
+        Criteria criteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(0).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        Criteria otherCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID,
+                    authorList.get(0).getAuthorId())
+            .addSelectColumn(BookPeer.BOOK_ID);
+        criteria.unionAll(otherCriteria)
+            .addAscendingOrderByColumn(new ColumnImpl("1"))
+            .setOffset(2)
+            .setLimit(4);
+
+        List<Integer> result
+                = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+        assertEquals(4, result.size());
+
+        // check unique contents of list
+        List<Integer> expected = new ArrayList<Integer>();
+        expected.add(authorList.get(0).getBooks().get(1).getBookId());
+        expected.add(authorList.get(0).getBooks().get(1).getBookId());
+        expected.add(authorList.get(0).getBooks().get(2).getBookId());
+        expected.add(authorList.get(0).getBooks().get(2).getBookId());
+        assertEquals(expected, result);
+    }
+}



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message