From 1b10ed688bd75f7c2535bab96c5ff705a5df314e Mon Sep 17 00:00:00 2001
From: "Brian S. O'Neill" <bronee@gmail.com>
Date: Sun, 14 Oct 2007 02:31:22 +0000
Subject: Added support for "where exists" in queries via new syntax.

---
 .../carbonado/cursor/TestFilteredCursor.java       | 347 +++++++++++++++++++++
 .../cursor/TestShortCircuitOptimizer.java          |  22 +-
 2 files changed, 356 insertions(+), 13 deletions(-)

(limited to 'src/test/java/com/amazon/carbonado/cursor')

diff --git a/src/test/java/com/amazon/carbonado/cursor/TestFilteredCursor.java b/src/test/java/com/amazon/carbonado/cursor/TestFilteredCursor.java
index 82854c2..2279e9b 100644
--- a/src/test/java/com/amazon/carbonado/cursor/TestFilteredCursor.java
+++ b/src/test/java/com/amazon/carbonado/cursor/TestFilteredCursor.java
@@ -22,6 +22,8 @@ import java.util.Arrays;
 import java.util.Iterator;
 import java.util.List;
 
+import org.joda.time.DateTime;
+
 import junit.framework.TestCase;
 import junit.framework.TestSuite;
 
@@ -29,6 +31,7 @@ import com.amazon.carbonado.*;
 import com.amazon.carbonado.filter.*;
 
 import com.amazon.carbonado.repo.toy.ToyRepository;
+import com.amazon.carbonado.stored.*;
 
 /**
  * 
@@ -386,6 +389,350 @@ public class TestFilteredCursor extends TestCase {
         assertEquals(expected.length, actualCount);
     }
 
+    public void testExistsNoParams() throws Exception {
+        Repository repo = new ToyRepository();
+        Storage<Order> orders = repo.storageFor(Order.class);
+        Storage<OrderItem> items = repo.storageFor(OrderItem.class);
+        Storage<Shipment> shipments = repo.storageFor(Shipment.class);
+
+        Order order = orders.prepare();
+        order.setOrderID(1);
+        order.setOrderNumber("one");
+        order.setOrderTotal(100);
+        order.setAddressID(0);
+        order.insert();
+
+        // Query for orders with any items.
+        long count = orders.query("orderItems()").count();
+        assertEquals(0, count);
+
+        // Query for orders with no items.
+        List<Order> matches = orders.query("!orderItems()").fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(order, matches.get(0));
+
+        // Query for orders with any shipments with any items.
+        matches = orders.query("shipments(orderItems())").fetch().toList();
+        assertEquals(0, matches.size());
+
+        // Query for orders with no shipments with any items.
+        matches = orders.query("!shipments(orderItems())").fetch().toList();
+        assertEquals(1, matches.size());
+
+        // Query for orders with any shipments with no items.
+        matches = orders.query("shipments(!orderItems())").fetch().toList();
+        assertEquals(0, matches.size());
+
+        // Query for orders with no shipments with no items.
+        matches = orders.query("!shipments(!orderItems())").fetch().toList();
+        assertEquals(1, matches.size());
+
+        // Insert more records (orders with items) and re-issue queries.
+
+        order = orders.prepare();
+        order.setOrderID(2);
+        order.setOrderNumber("two");
+        order.setOrderTotal(200);
+        order.setAddressID(0);
+        order.insert();
+
+        OrderItem item = items.prepare();
+        item.setOrderItemID(1);
+        item.setOrderID(2);
+        item.setItemDescription("desc one");
+        item.setItemQuantity(1);
+        item.setItemPrice(100);
+        item.setShipmentID(0);
+        item.insert();
+
+        // Query for orders with any items.
+        matches = orders.query("orderItems()").fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(2, matches.get(0).getOrderID());
+
+        // Query for orders with no items.
+        matches = orders.query("!orderItems()").fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+
+        // Query for orders with any shipments with any items.
+        matches = orders.query("shipments(orderItems())").fetch().toList();
+        assertEquals(0, matches.size());
+
+        // Query for orders with no shipments with any items.
+        matches = orders.query("!shipments(orderItems())").fetch().toList();
+        assertEquals(2, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+        assertEquals(2, matches.get(1).getOrderID());
+
+        // Query for orders with any shipments with no items.
+        matches = orders.query("shipments(!orderItems())").fetch().toList();
+        assertEquals(0, matches.size());
+
+        // Query for orders with no shipments with no items.
+        matches = orders.query("!shipments(!orderItems())").fetch().toList();
+        assertEquals(2, matches.size());
+
+        // Insert more records (orders with shipments with items) and re-issue queries.
+
+        order = orders.prepare();
+        order.setOrderID(3);
+        order.setOrderNumber("three");
+        order.setOrderTotal(300);
+        order.setAddressID(0);
+        order.insert();
+
+        Shipment shipment = shipments.prepare();
+        shipment.setShipmentID(1);
+        shipment.setShipmentNotes("notes");
+        shipment.setShipmentDate(new DateTime());
+        shipment.setOrderID(3);
+        shipment.setShipperID(0);
+        shipment.insert();
+
+        item = items.prepare();
+        item.setOrderItemID(2);
+        item.setOrderID(3);
+        item.setItemDescription("desc two");
+        item.setItemQuantity(1);
+        item.setItemPrice(500);
+        item.setShipmentID(1);
+        item.insert();
+
+        // Query for orders with any items.
+        matches = orders.query("orderItems()").fetch().toList();
+        assertEquals(2, matches.size());
+        assertEquals(2, matches.get(0).getOrderID());
+        assertEquals(3, matches.get(1).getOrderID());
+
+        // Query for orders with no items.
+        matches = orders.query("!orderItems()").fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+
+        // Query for orders with any shipments with any items.
+        matches = orders.query("shipments(orderItems())").fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(3, matches.get(0).getOrderID());
+
+        // Query for orders with no shipments with any items.
+        matches = orders.query("!shipments(orderItems())").fetch().toList();
+        assertEquals(2, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+        assertEquals(2, matches.get(1).getOrderID());
+
+        // Query for orders with any shipments with no items.
+        matches = orders.query("shipments(!orderItems())").fetch().toList();
+        assertEquals(0, matches.size());
+
+        // Query for orders with no shipments with no items.
+        matches = orders.query("!shipments(!orderItems())").fetch().toList();
+        assertEquals(3, matches.size());
+
+        // Insert more records to test for empty shipments.
+
+        order = orders.prepare();
+        order.setOrderID(4);
+        order.setOrderNumber("four");
+        order.setOrderTotal(400);
+        order.setAddressID(0);
+        order.insert();
+
+        shipment = shipments.prepare();
+        shipment.setShipmentID(2);
+        shipment.setShipmentNotes("notes 2");
+        shipment.setShipmentDate(new DateTime());
+        shipment.setOrderID(4);
+        shipment.setShipperID(0);
+        shipment.insert();
+
+        // Query for orders with any shipments with no items.
+        matches = orders.query("shipments(!orderItems())").fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(4, matches.get(0).getOrderID());
+
+        // Query for orders with no shipments with no items.
+        matches = orders.query("!shipments(!orderItems())").fetch().toList();
+        assertEquals(3, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+        assertEquals(2, matches.get(1).getOrderID());
+        assertEquals(3, matches.get(2).getOrderID());
+    }
+
+    public void testExistsWithParams() throws Exception {
+        Repository repo = new ToyRepository();
+        Storage<Order> orders = repo.storageFor(Order.class);
+        Storage<OrderItem> items = repo.storageFor(OrderItem.class);
+        Storage<Shipment> shipments = repo.storageFor(Shipment.class);
+
+        Order order = orders.prepare();
+        order.setOrderID(1);
+        order.setOrderNumber("one");
+        order.setOrderTotal(100);
+        order.setAddressID(0);
+        order.insert();
+
+        Shipment shipment = shipments.prepare();
+        shipment.setShipmentID(1);
+        shipment.setShipmentNotes("notes 1");
+        shipment.setShipmentDate(new DateTime());
+        shipment.setOrderID(1);
+        shipment.setShipperID(0);
+        shipment.insert();
+
+        shipment = shipments.prepare();
+        shipment.setShipmentID(2);
+        shipment.setShipmentNotes("notes 2");
+        shipment.setShipmentDate(new DateTime());
+        shipment.setOrderID(1);
+        shipment.setShipperID(0);
+        shipment.insert();
+
+        OrderItem item = items.prepare();
+        item.setOrderItemID(1);
+        item.setOrderID(1);
+        item.setItemDescription("one one");
+        item.setItemQuantity(1);
+        item.setItemPrice(500);
+        item.setShipmentID(1);
+        item.insert();
+
+        item = items.prepare();
+        item.setOrderItemID(2);
+        item.setOrderID(1);
+        item.setItemDescription("one two");
+        item.setItemQuantity(1);
+        item.setItemPrice(500);
+        item.setShipmentID(1);
+        item.insert();
+
+        item = items.prepare();
+        item.setOrderItemID(3);
+        item.setOrderID(1);
+        item.setItemDescription("one three");
+        item.setItemQuantity(2);
+        item.setItemPrice(500);
+        item.setShipmentID(2);
+        item.insert();
+
+        order = orders.prepare();
+        order.setOrderID(2);
+        order.setOrderNumber("two");
+        order.setOrderTotal(20000);
+        order.setAddressID(0);
+        order.insert();
+
+        order = orders.prepare();
+        order.setOrderID(3);
+        order.setOrderNumber("three");
+        order.setOrderTotal(300);
+        order.setAddressID(0);
+        order.insert();
+
+        item = items.prepare();
+        item.setOrderItemID(4);
+        item.setOrderID(3);
+        item.setItemDescription("three one");
+        item.setItemQuantity(20);
+        item.setItemPrice(500);
+        item.setShipmentID(2);
+        item.insert();
+
+        order = orders.prepare();
+        order.setOrderID(4);
+        order.setOrderNumber("four");
+        order.setOrderTotal(0);
+        order.setAddressID(0);
+        order.insert();
+
+        shipment = shipments.prepare();
+        shipment.setShipmentID(3);
+        shipment.setShipmentNotes("notes 3");
+        shipment.setShipmentDate(new DateTime());
+        shipment.setOrderID(4);
+        shipment.setShipperID(0);
+        shipment.insert();
+
+        item = items.prepare();
+        item.setOrderItemID(5);
+        item.setOrderID(4);
+        item.setItemDescription("four one");
+        item.setItemQuantity(99);
+        item.setItemPrice(500);
+        item.setShipmentID(3);
+        item.insert();
+
+        // Query for orders which has an item with a specific quantity.
+
+        List<Order> matches = orders.query("orderItems(itemQuantity >= ? & itemQuantity < ?)")
+            .with(2).with(10).fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+
+        matches = orders.query("orderItems(itemQuantity >= ? & itemQuantity < ?)")
+            .with(2).with(50).fetch().toList();
+        assertEquals(2, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+        assertEquals(3, matches.get(1).getOrderID());
+
+        // Query for empty orders with a non-zero total.
+
+        matches = orders.query("orderTotal != ? & !orderItems()")
+            .with(0).fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(2, matches.get(0).getOrderID());
+            
+        matches = orders.query("!orderItems() & orderTotal != ?")
+            .with(0).fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(2, matches.get(0).getOrderID());
+
+        // Query for non-empty orders or those with a zero total.
+
+        matches = orders.query("!(!orderItems() & orderTotal != ?)")
+            .with(0).fetch().toList();
+        assertEquals(3, matches.size());
+        for (int i=0; i<matches.size(); i++) {
+            assertTrue(matches.get(i).getOrderID() != 2);
+        }
+
+        // Query for orders in shipments which have an item with a high quantity.
+
+        matches = orders.query("shipments(orderItems(itemQuantity > ?))")
+            .with(10).fetch().toList();
+        assertEquals(2, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+        assertEquals(4, matches.get(1).getOrderID());
+
+        // Query with a specific quantity.
+
+        matches = orders.query("shipments(orderItems(itemQuantity = ?))")
+            .with(99).fetch().toList();
+        assertEquals(1, matches.size());
+        assertEquals(4, matches.get(0).getOrderID());
+
+        // Mix in some more stuff...
+
+        matches = orders
+            .query("orderNumber = ? | shipments(orderItems(itemQuantity = ?) | shipmentNotes = ?)")
+            .with("three").with(99).with("notes 1").fetch().toList();
+        assertEquals(3, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+        assertEquals(3, matches.get(1).getOrderID());
+        assertEquals(4, matches.get(2).getOrderID());
+
+        matches = orders
+            .query("orderNumber = ? | " +
+                   "shipments(orderItems(itemQuantity = ?) | " +
+                              "shipmentNotes = ? | " +
+                              "orderItems(itemQuantity = ?))")
+            .with("three").with(99).with("notes 1").with(25).fetch().toList();
+        assertEquals(3, matches.size());
+        assertEquals(1, matches.get(0).getOrderID());
+        assertEquals(3, matches.get(1).getOrderID());
+        assertEquals(4, matches.get(2).getOrderID());
+    }
+
     @PrimaryKey("ID")
     public static interface FloatRecord extends Storable {
         int getID();
diff --git a/src/test/java/com/amazon/carbonado/cursor/TestShortCircuitOptimizer.java b/src/test/java/com/amazon/carbonado/cursor/TestShortCircuitOptimizer.java
index 3e926a3..6bc62a6 100644
--- a/src/test/java/com/amazon/carbonado/cursor/TestShortCircuitOptimizer.java
+++ b/src/test/java/com/amazon/carbonado/cursor/TestShortCircuitOptimizer.java
@@ -159,11 +159,10 @@ public class TestShortCircuitOptimizer extends TestCase {
         }
     }
 
-    /*
     public void testOneToManyJoins() throws Exception {
         {
             Filter<Order> filter = Filter
-                .filterFor(Order.class, "orderNumber = ? & orderItems.itemPrice = ?");
+                .filterFor(Order.class, "orderNumber = ? & orderItems(itemPrice = ?)");
             Filter<Order> optimized = ShortCircuitOptimizer.optimize(filter);
 
             // No change.
@@ -172,48 +171,45 @@ public class TestShortCircuitOptimizer extends TestCase {
 
         {
             Filter<Order> filter = Filter
-                .filterFor(Order.class, "orderItems.itemPrice = ? & orderNumber = ?");
+                .filterFor(Order.class, "orderItems(itemPrice = ?) & orderNumber = ?");
             Filter<Order> optimized = ShortCircuitOptimizer.optimize(filter);
 
             assertEquals(Filter.filterFor
-                         (Order.class, "orderNumber = ? & orderItems.itemPrice = ?"),
+                         (Order.class, "orderNumber = ? & orderItems(itemPrice = ?)"),
                          optimized);
         }
 
         {
             Filter<Order> filter = Filter
                 .filterFor(Order.class,
-                           "(orderItems.itemPrice = ? & orderNumber = ?) | orderTotal < ?");
+                           "(orderItems(itemPrice = ?) & orderNumber = ?) | orderTotal < ?");
             Filter<Order> optimized = ShortCircuitOptimizer.optimize(filter);
 
             assertEquals(Filter.filterFor
                          (Order.class,
-                          "orderTotal < ? | (orderNumber = ? & orderItems.itemPrice = ?)"),
+                          "orderTotal < ? | (orderNumber = ? & orderItems(itemPrice = ?))"),
                          optimized);
         }
 
         {
             Filter<Order> filter = Filter
                 .filterFor(Order.class,
-                           "orderItems.shipment.shipmentDate > ? | shipments.shipmentDate < ?");
+                           "orderItems(shipment.shipmentDate > ?) | shipments(shipmentDate < ?)");
             Filter<Order> optimized = ShortCircuitOptimizer.optimize(filter);
 
-            assertEquals(Filter.filterFor
-                         (Order.class,
-                          "shipments.shipmentDate < ? | orderItems.shipment.shipmentDate > ?"),
-                         optimized);
+            // No change because optimizer doesn't consider complexity of sub-filters.
+            assertEquals(filter, optimized);
         }
 
         {
             Filter<Order> filter = Filter
                 .filterFor
                 (Order.class,
-                 "orderItems.shipment.shipmentDate > ? | shipments.shipper.shipperName != ?");
+                 "orderItems(shipment.shipmentDate > ?) | shipments(shipper.shipperName != ?)");
             Filter<Order> optimized = ShortCircuitOptimizer.optimize(filter);
 
             // No change.
             assertEquals(filter, optimized);
         }
     }
-    */
 }
-- 
cgit v1.2.3