Examples with QueryService org.apache.geode.cache.query.QueryService used on opensource projects

Example 96 with QueryService

use of org.apache.geode.cache.query.QueryService in project geode by apache.

the class OrderByTestImplementation method testOrderByWithIndexResultWithProjection.

@Test
public void testOrderByWithIndexResultWithProjection() throws Exception {
    String[] queries = { // Test case No. IUMR021
    "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID > 10 order by ID desc ", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID > 10 order by ID asc ", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID > 10 and ID < 20 order by ID asc ", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID > 10 and ID < 20 order by ID desc ", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID desc ", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID asc", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID != 10 order by ID asc ", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID != 10 order by ID desc ", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID > 10 order by ID desc limit 5", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID > 10 order by ID asc limit 5", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID > 10 and ID < 20 order by ID asc limit 5 ", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID > 10 and ID < 20 order by ID desc limit 5", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID desc limit 5", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID >= 10 and ID <= 20 order by ID asc limit 5", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID != 10 order by ID asc limit 10", "SELECT  distinct ID, description, createTime FROM /portfolio1 pf1 where ID != 10 order by ID desc limit 10" };
    Object[][] r = new Object[queries.length][2];
    QueryService qs;
    qs = CacheUtils.getQueryService();
    Position.resetCounter();
    // Create Regions
    Region r1 = createRegion("portfolio1", Portfolio.class);
    for (int i = 0; i < 50; i++) {
        r1.put(i + "", new Portfolio(i));
    }
    // Execute Queries without Indexes
    for (int i = 0; i < queries.length; i++) {
        Query q = null;
        try {
            q = CacheUtils.getQueryService().newQuery(queries[i]);
            CacheUtils.getLogger().info("Executing query: " + queries[i]);
            r[i][0] = q.execute();
        } catch (Exception e) {
            e.printStackTrace();
            fail(q.getQueryString());
        }
    }
    // Create Indexes
    this.createIndex("IDIndexPf1", IndexType.FUNCTIONAL, "ID", "/portfolio1");
    // Execute Queries with Indexes
    for (int i = 0; i < queries.length; i++) {
        Query q = null;
        try {
            q = CacheUtils.getQueryService().newQuery(queries[i]);
            CacheUtils.getLogger().info("Executing query: " + queries[i]);
            QueryObserverImpl observer = new QueryObserverImpl();
            QueryObserverHolder.setInstance(observer);
            r[i][1] = q.execute();
            SelectResults rcw = (SelectResults) r[i][1];
            int indexLimit = queries[i].indexOf("limit");
            int limit = -1;
            boolean limitQuery = indexLimit != -1;
            if (limitQuery) {
                limit = Integer.parseInt(queries[i].substring(indexLimit + 5).trim());
            }
            assertTrue(!limitQuery || rcw.size() <= limit);
            // assertIndexDetailsEquals("Set",rcw.getCollectionType().getSimpleClassName());
            String colType = rcw.getCollectionType().getSimpleClassName();
            if (!(colType.equals("Ordered") || colType.equals("LinkedHashSet"))) {
                fail("The collection type " + colType + " is not expexted");
            }
            if (assertIndexUsedOnQueryNode() && !observer.isIndexesUsed) {
                fail("Index is NOT uesd");
            }
            Iterator itr = observer.indexesUsed.iterator();
            while (itr.hasNext()) {
                if (!(itr.next().toString()).equals("IDIndexPf1")) {
                    fail("<IDIndexPf1> was expected but found " + itr.next().toString());
                }
            // assertIndexDetailsEquals("statusIndexPf1",itr.next().toString());
            }
            int indxs = observer.indexesUsed.size();
            System.out.println("**************************************************Indexes Used :::::: " + indxs + " Index Name: " + observer.indexName);
        } catch (Exception e) {
            e.printStackTrace();
            fail(q.getQueryString());
        }
    }
    StructSetOrResultsSet ssOrrs = new StructSetOrResultsSet();
    ssOrrs.CompareQueryResultsWithoutAndWithIndexes(r, queries.length, true, queries);
    ssOrrs.compareExternallySortedQueriesWithOrderBy(queries, r);
}

Example 97 with QueryService

use of org.apache.geode.cache.query.QueryService in project geode by apache.

the class PdxGroupByTestImpl method testAggregateFuncSum.

@Override
@Test
public void testAggregateFuncSum() throws Exception {
    Region region = this.createRegion("portfolio", PortfolioPdx.class);
    for (int i = 1; i < 200; ++i) {
        PortfolioPdx pf = new PortfolioPdx(i);
        pf.shortID = (short) ((short) i / 5);
        region.put("key-" + i, pf);
    }
    String queryStr = "select  p.status as status, Sum(p.ID) as summ from /portfolio p where p.ID > 0 group by status ";
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery(queryStr);
    CompiledSelect cs = ((DefaultQuery) query).getSelect();
    SelectResults sr = (SelectResults) query.execute();
    assertTrue(sr.getCollectionType().getElementType().isStructType());
    assertEquals(2, sr.size());
    Iterator iter = sr.iterator();
    Region rgn = CacheUtils.getRegion("portfolio");
    int activeSum = 0;
    int inactiveSum = 0;
    for (Object o : rgn.values()) {
        PortfolioPdx pf = (PortfolioPdx) o;
        if (pf.status.equals("active")) {
            activeSum += pf.getID();
        } else if (pf.status.equals("inactive")) {
            inactiveSum += pf.getID();
        } else {
            fail("unexpected value of status");
        }
    }
    while (iter.hasNext()) {
        Struct struct = (Struct) iter.next();
        StructType structType = struct.getStructType();
        ObjectType[] fieldTypes = structType.getFieldTypes();
        assertEquals("String", fieldTypes[0].getSimpleClassName());
        assertEquals("Number", fieldTypes[1].getSimpleClassName());
        if (struct.get("status").equals("active")) {
            assertEquals(activeSum, ((Integer) struct.get("summ")).intValue());
        } else if (struct.get("status").equals("inactive")) {
            assertEquals(inactiveSum, ((Integer) struct.get("summ")).intValue());
        } else {
            fail("unexpected value of status");
        }
    }
    ObjectType elementType = sr.getCollectionType().getElementType();
    assertTrue(elementType.isStructType());
    StructType structType = (StructType) elementType;
    ObjectType[] fieldTypes = structType.getFieldTypes();
    assertEquals("String", fieldTypes[0].getSimpleClassName());
    assertEquals("Number", fieldTypes[1].getSimpleClassName());
}

Example 98 with QueryService

use of org.apache.geode.cache.query.QueryService in project geode by apache.

the class PdxGroupByTestImpl method testAggregateFuncCountStar.

@Override
@Test
public void testAggregateFuncCountStar() throws Exception {
    Region region = this.createRegion("portfolio", PortfolioPdx.class);
    for (int i = 1; i < 200; ++i) {
        PortfolioPdx pf = new PortfolioPdx(i);
        pf.shortID = (short) ((short) i % 5);
        region.put("key-" + i, pf);
    }
    String queryStr = "select  p.status as status, Count(*) as countt from /portfolio p where p.ID > 0 group by status ";
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery(queryStr);
    CompiledSelect cs = ((DefaultQuery) query).getSelect();
    SelectResults sr = (SelectResults) query.execute();
    assertTrue(sr.getCollectionType().getElementType().isStructType());
    assertEquals(2, sr.size());
    Iterator iter = sr.iterator();
    Region rgn = CacheUtils.getRegion("portfolio");
    int activeCount = 0;
    int inactiveCount = 0;
    for (Object o : rgn.values()) {
        PortfolioPdx pf = (PortfolioPdx) o;
        if (pf.status.equals("active")) {
            ++activeCount;
        } else if (pf.status.equals("inactive")) {
            ++inactiveCount;
        } else {
            fail("unexpected status");
        }
    }
    while (iter.hasNext()) {
        Struct struct = (Struct) iter.next();
        StructType structType = struct.getStructType();
        ObjectType[] fieldTypes = structType.getFieldTypes();
        assertEquals("String", fieldTypes[0].getSimpleClassName());
        assertEquals("Integer", fieldTypes[1].getSimpleClassName());
        if (struct.get("status").equals("active")) {
            assertEquals(activeCount, ((Integer) struct.get("countt")).intValue());
        } else if (struct.get("status").equals("inactive")) {
            assertEquals(inactiveCount, ((Integer) struct.get("countt")).intValue());
        } else {
            fail("unexpected value of status");
        }
    }
    ObjectType elementType = sr.getCollectionType().getElementType();
    assertTrue(elementType.isStructType());
    StructType structType = (StructType) elementType;
    ObjectType[] fieldTypes = structType.getFieldTypes();
    assertEquals("String", fieldTypes[0].getSimpleClassName());
    assertEquals("Integer", fieldTypes[1].getSimpleClassName());
}

Example 99 with QueryService

use of org.apache.geode.cache.query.QueryService in project geode by apache.

the class PdxGroupByTestImpl method testAggregateFuncAvgDistinct.

@Override
@Test
public void testAggregateFuncAvgDistinct() throws Exception {
    Region region = this.createRegion("portfolio", PortfolioPdx.class);
    for (int i = 1; i < 200; ++i) {
        PortfolioPdx pf = new PortfolioPdx(i);
        pf.shortID = (short) ((short) i / 5);
        region.put("key-" + i, pf);
    }
    String queryStr = "select   p.status as status,  avg(distinct p.shortID) as average from " + "/portfolio p where p.ID > 0 group by status";
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery(queryStr);
    CompiledSelect cs = ((DefaultQuery) query).getSelect();
    SelectResults sr = (SelectResults) query.execute();
    assertTrue(sr.getCollectionType().getElementType().isStructType());
    assertEquals(2, sr.size());
    Iterator iter = sr.iterator();
    Region rgn = CacheUtils.getRegion("portfolio");
    Set<Short> sumIDActiveSet = new HashSet<Short>(), sumIDInactiveSet = new HashSet<Short>();
    for (Object o : rgn.values()) {
        PortfolioPdx pf = (PortfolioPdx) o;
        if (pf.getID() > 0) {
            if (pf.status.equals("active")) {
                sumIDActiveSet.add(pf.shortID);
            } else if (pf.status.equals("inactive")) {
                sumIDInactiveSet.add(pf.shortID);
            }
        }
    }
    double sumActive = 0, sumInactive = 0;
    for (Short shortt : sumIDActiveSet) {
        sumActive += shortt.doubleValue();
    }
    for (Short shortt : sumIDInactiveSet) {
        sumInactive += shortt.doubleValue();
    }
    Number avgActive = AbstractAggregator.downCast(sumActive / sumIDActiveSet.size());
    Number avgInactive = AbstractAggregator.downCast(sumInactive / sumIDInactiveSet.size());
    while (iter.hasNext()) {
        Struct struct = (Struct) iter.next();
        StructType structType = struct.getStructType();
        ObjectType[] fieldTypes = structType.getFieldTypes();
        assertEquals("String", fieldTypes[0].getSimpleClassName());
        assertEquals("Number", fieldTypes[1].getSimpleClassName());
        if (struct.get("status").equals("active")) {
            assertEquals(avgActive, struct.get("average"));
        } else if (struct.get("status").equals("inactive")) {
            assertEquals(avgInactive, struct.get("average"));
        } else {
            fail("unexpected value of status");
        }
    }
    ObjectType elementType = sr.getCollectionType().getElementType();
    assertTrue(elementType.isStructType());
    StructType structType = (StructType) elementType;
    ObjectType[] fieldTypes = structType.getFieldTypes();
    assertEquals("String", fieldTypes[0].getSimpleClassName());
    assertEquals("Number", fieldTypes[1].getSimpleClassName());
}

Example 100 with QueryService

use of org.apache.geode.cache.query.QueryService in project geode by apache.

the class PdxGroupByTestImpl method testAggregateFuncAvg.

@Override
@Test
public void testAggregateFuncAvg() throws Exception {
    Region region = this.createRegion("portfolio", PortfolioPdx.class);
    for (int i = 1; i < 200; ++i) {
        PortfolioPdx pf = new PortfolioPdx(i);
        pf.shortID = (short) ((short) i / 5);
        region.put("key-" + i, pf);
    }
    String queryStr = "select   p.status as status,  Avg(p.ID) as average from " + "/portfolio p where p.ID > 0 group by status";
    QueryService qs = CacheUtils.getQueryService();
    Query query = qs.newQuery(queryStr);
    CompiledSelect cs = ((DefaultQuery) query).getSelect();
    SelectResults sr = (SelectResults) query.execute();
    assertTrue(sr.getCollectionType().getElementType().isStructType());
    assertEquals(2, sr.size());
    Iterator iter = sr.iterator();
    Region rgn = CacheUtils.getRegion("portfolio");
    double sumIDActive = 0, sumIDInactive = 0;
    int numActive = 0, numInactive = 0;
    for (Object o : rgn.values()) {
        PortfolioPdx pf = (PortfolioPdx) o;
        if (pf.getID() > 0) {
            if (pf.status.equals("active")) {
                sumIDActive += pf.getID();
                ++numActive;
            } else if (pf.status.equals("inactive")) {
                sumIDInactive += pf.getID();
                ++numInactive;
            }
        }
    }
    Number avgActive = AbstractAggregator.downCast(sumIDActive / numActive);
    Number avgInactive = AbstractAggregator.downCast(sumIDInactive / numInactive);
    while (iter.hasNext()) {
        Struct struct = (Struct) iter.next();
        StructType structType = struct.getStructType();
        ObjectType[] fieldTypes = structType.getFieldTypes();
        assertEquals("String", fieldTypes[0].getSimpleClassName());
        assertEquals("Number", fieldTypes[1].getSimpleClassName());
        if (struct.get("status").equals("active")) {
            assertEquals(avgActive, struct.get("average"));
        } else if (struct.get("status").equals("inactive")) {
            assertEquals(avgInactive, struct.get("average"));
        } else {
            fail("unexpected value of status");
        }
    }
    ObjectType elementType = sr.getCollectionType().getElementType();
    assertTrue(elementType.isStructType());
    StructType structType = (StructType) elementType;
    ObjectType[] fieldTypes = structType.getFieldTypes();
    assertEquals("String", fieldTypes[0].getSimpleClassName());
    assertEquals("Number", fieldTypes[1].getSimpleClassName());
}