Repository: incubator-ignite Updated Branches: refs/heads/ignite-6 da82d9652 -> 67a50f969
ignite-sql - fix tests Project: http://git-wip-us.apache.org/repos/asf/incubator-ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ignite/commit/3d268155 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ignite/tree/3d268155 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ignite/diff/3d268155 Branch: refs/heads/ignite-6 Commit: 3d2681559cc3bcd8fa71a91fff4578fb97337b54 Parents: ded1823 Author: S.Vladykin <svlady...@gridgain.com> Authored: Mon Jan 26 21:37:18 2015 +0300 Committer: S.Vladykin <svlady...@gridgain.com> Committed: Mon Jan 26 21:37:18 2015 +0300 ---------------------------------------------------------------------- .../cache/GridCacheCrossCacheQuerySelfTest.java | 112 ++++- .../processors/query/h2/sql/GridQueryTest.java | 243 +++++++++++ .../cache/GridCacheCrossCacheQuerySelfTest.java | 437 ------------------- .../processors/query/h2/sql/GridQueryTest.java | 242 ---------- 4 files changed, 341 insertions(+), 693 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/3d268155/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java index 553cbe1..f56409a 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java @@ -21,12 +21,15 @@ import org.apache.ignite.*; import org.apache.ignite.cache.*; import org.apache.ignite.cache.query.*; import org.apache.ignite.configuration.*; +import org.apache.ignite.internal.processors.cache.query.*; +import org.apache.ignite.internal.util.typedef.*; import org.apache.ignite.lang.*; import org.apache.ignite.marshaller.optimized.*; import org.apache.ignite.spi.discovery.tcp.*; import org.apache.ignite.spi.discovery.tcp.ipfinder.*; import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.*; import org.apache.ignite.testframework.junits.common.*; +import org.gridgain.grid.kernal.processors.cache.query.*; import java.util.*; @@ -96,8 +99,79 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { return cc; } + /** + * @throws Exception If failed. + */ + public void testTwoStep() throws Exception { + fillCaches(); + + String cache = "partitioned"; + + GridCacheQueriesEx<Integer, FactPurchase> qx = + (GridCacheQueriesEx<Integer, FactPurchase>)ignite.<Integer, FactPurchase>cache(cache).queries(); + +// for (Map.Entry<Integer, FactPurchase> e : qx.createSqlQuery(FactPurchase.class, "1 = 1").execute().get()) +// X.println("___ " + e); + + GridCacheTwoStepQuery q = new GridCacheTwoStepQuery("select cast(sum(x) as long) from _cnts_ where ? = ?", 1, 1); + + q.addMapQuery("_cnts_", "select count(*) x from \"partitioned\".FactPurchase where ? = ?", 2 ,2); + + Object cnt = qx.execute(cache, q).get().iterator().next().get(0); + + assertEquals(10L, cnt); + } + + /** + * @throws Exception If failed. + */ + public void testTwoStepGroupAndAggregates() throws Exception { + fillCaches(); + + GridCacheQueriesEx<Integer, FactPurchase> qx = + (GridCacheQueriesEx<Integer, FactPurchase>)ignite.<Integer, FactPurchase>cache("partitioned").queries(); + + Set<Integer> set1 = new HashSet<>(); + + X.println("___ simple"); + + for (List<?> o : qx.executeTwoStepQuery("partitioned", "select f.productId, p.name, f.price " + + "from FactPurchase f, \"replicated\".DimProduct p where p.id = f.productId ").get()) { + X.println("___ -> " + o); + + set1.add((Integer)o.get(0)); + } + + Set<Integer> set0 = new HashSet<>(); + + X.println("___ GROUP BY"); + + for (List<?> o : qx.executeTwoStepQuery("partitioned", "select productId from FactPurchase group by productId") + .get()) { + X.println("___ -> " + o); + + assertTrue(set0.add((Integer) o.get(0))); + } + + assertFalse(set1.isEmpty()); + assertEquals(set0, set1); + + X.println("___ AVG MIN MAX SUM COUNT(*) COUNT(x)"); + + for (List<?> o : qx.executeTwoStepQuery("partitioned", + "select p.name, avg(f.price), min(f.price), max(f.price), sum(f.price), count(*), " + + "count(nullif(f.price, 5)) " + + "from FactPurchase f, \"replicated\".DimProduct p " + + "where p.id = f.productId " + + "group by f.productId, p.name").get()) { + X.println("___ -> " + o); + } + } + /** @throws Exception If failed. */ public void testOnProjection() throws Exception { + fillCaches(); + CacheProjection<Integer, FactPurchase> prj = ignite.<Integer, FactPurchase>cache("partitioned").projection( new IgnitePredicate<CacheEntry<Integer, FactPurchase>>() { @Override public boolean apply(CacheEntry<Integer, FactPurchase> e) { @@ -111,14 +185,9 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { } /** - * Fills the caches with data and executes the query. - * - * @param prj Cache projection. - * @throws Exception If failed. - * @return Result. + * @throws IgniteCheckedException If failed. */ - private List<Map.Entry<Integer, FactPurchase>> body(CacheProjection<Integer, FactPurchase> prj) - throws Exception { + private void fillCaches() throws IgniteCheckedException, InterruptedException { int idGen = 0; GridCache<Integer, Object> dimCache = ignite.cache("replicated"); @@ -161,11 +230,21 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { DimStore store = dimStores.get(i % dimStores.size()); DimProduct prod = dimProds.get(i % dimProds.size()); - factCache.put(id, new FactPurchase(id, prod.getId(), store.getId())); + factCache.put(id, new FactPurchase(id, prod.getId(), store.getId(), i + 5)); } + } - CacheQuery<Map.Entry<Integer, FactPurchase>> qry = (prj == null ? factCache : prj).queries().createSqlQuery( - FactPurchase.class, + /** + * Fills the caches with data and executes the query. + * + * @param prj Cache projection. + * @throws Exception If failed. + * @return Result. + */ + private List<Map.Entry<Integer, FactPurchase>> body(CacheProjection<Integer, FactPurchase> prj) + throws Exception { + CacheQuery<Map.Entry<Integer, FactPurchase>> qry = (prj == null ? + ignite.<Integer, FactPurchase>cache("partitioned") : prj).queries().createSqlQuery(FactPurchase.class, "from \"replicated\".DimStore, \"partitioned\".FactPurchase where DimStore.id = FactPurchase.storeId"); List<Map.Entry<Integer, FactPurchase>> res = new ArrayList<>(qry.execute().get()); @@ -213,10 +292,11 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { */ private static class DimProduct { /** Primary key. */ - @CacheQuerySqlField(unique = true) + @CacheQuerySqlField private int id; /** Product name. */ + @CacheQuerySqlField private String name; /** @@ -255,7 +335,7 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { */ private static class DimStore { /** Primary key. */ - @CacheQuerySqlField(unique = true) + @CacheQuerySqlField private int id; /** Store name. */ @@ -298,7 +378,7 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { */ private static class FactPurchase { /** Primary key. */ - @CacheQuerySqlField(unique = true) + @CacheQuerySqlField private int id; /** Foreign key to store at which purchase occurred. */ @@ -309,6 +389,9 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { @CacheQuerySqlField private int productId; + @CacheQuerySqlField + private int price; + /** * Constructs a purchase record. * @@ -316,10 +399,11 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { * @param productId Purchased product ID. * @param storeId Store ID. */ - FactPurchase(int id, int productId, int storeId) { + FactPurchase(int id, int productId, int storeId, int price) { this.id = id; this.productId = productId; this.storeId = storeId; + this.price = price; } /** http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/3d268155/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryTest.java new file mode 100644 index 0000000..425b938 --- /dev/null +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryTest.java @@ -0,0 +1,243 @@ +/* @java.file.header */ + +/* _________ _____ __________________ _____ + * __ ____/___________(_)______ /__ ____/______ ____(_)_______ + * _ / __ __ ___/__ / _ __ / _ / __ _ __ `/__ / __ __ \ + * / /_/ / _ / _ / / /_/ / / /_/ / / /_/ / _ / _ / / / + * \____/ /_/ /_/ \_,__/ \____/ \__,_/ /_/ /_/ /_/ + */ + +package org.apache.ignite.internal.processors.query.h2.sql; + +import org.apache.ignite.cache.*; +import org.apache.ignite.cache.query.*; +import org.apache.ignite.internal.*; +import org.apache.ignite.internal.processors.cache.*; +import org.apache.ignite.internal.processors.query.*; +import org.apache.ignite.internal.processors.query.h2.*; +import org.apache.ignite.internal.util.typedef.internal.*; +import org.gridgain.grid.kernal.processors.query.h2.sql.*; +import org.h2.command.*; +import org.h2.command.dml.*; +import org.h2.engine.*; +import org.h2.jdbc.*; + +import java.io.*; +import java.util.*; + +/** + * + */ +public class GridQueryTest extends GridCacheAbstractQuerySelfTest { + /** {@inheritDoc} */ + @Override protected int gridCount() { + return 1; + } + + /** {@inheritDoc} */ + @Override protected CacheMode cacheMode() { + return CacheMode.REPLICATED; + } + + /** {@inheritDoc} */ + @Override protected void beforeTest() throws Exception { + super.beforeTest(); + + GridCache cache = ignite.cache(null); + + cache.putx("testAddr", new Address()); + cache.putx("testPerson", new Person()); + } + + /** + * + */ + public void testAllExampless() throws Exception { + checkQuery("select name, date from Person"); + checkQuery("select distinct name, date from Person"); + checkQuery("select * from Person p"); + checkQuery("select * from Person"); + checkQuery("select distinct * from Person"); + checkQuery("select p.name, date from Person p"); + + checkQuery("select * from Person p, Address a"); + checkQuery("select * from Person, Address"); + checkQuery("select p.* from Person p, Address a"); + checkQuery("select person.* from Person, Address a"); + checkQuery("select p.*, street from Person p, Address a"); + checkQuery("select p.name, a.street from Person p, Address a"); + checkQuery("select distinct p.name, a.street from Person p, Address a"); + checkQuery("select distinct name, street from Person, Address"); + checkQuery("select p1.name, a2.street from Person p1, Address a1, Person p2, Address a2"); + + checkQuery("select p.name n, a.street s from Person p, Address a"); + checkQuery("select p.name, 1 as i, 'aaa' s from Person p"); + + checkQuery("select p.name + 'a', 1 * 3 as i, 'aaa' s, -p.old, -p.old as old from Person p"); + checkQuery("select p.name || 'a' + p.name, (p.old * 3) % p.old - p.old / p.old, p.name = 'aaa', " + + " p.name is p.name, p.old > 0, p.old >= 0, p.old < 0, p.old <= 0, p.old <> 0, p.old is not p.old, " + + " p.old is null, p.old is not null " + + " from Person p"); + + checkQuery("select p.name from Person p where name <> 'ivan'"); + checkQuery("select p.name from Person p where name like 'i%'"); + checkQuery("select p.name from Person p where name regexp 'i%'"); + checkQuery("select p.name from Person p, Address a where p.name <> 'ivan' and a.id > 10 or not (a.id = 100)"); + + checkQuery("select case p.name when 'a' then 1 when 'a' then 2 end as a from Person p"); + checkQuery("select case p.name when 'a' then 1 when 'a' then 2 else -1 end as a from Person p"); + + checkQuery("select abs(p.old) from Person p"); + checkQuery("select cast(p.old as numeric(10, 2)) from Person p"); + checkQuery("select cast(p.old as numeric(10, 2)) z from Person p"); + checkQuery("select cast(p.old as numeric(10, 2)) as z from Person p"); + + checkQuery("select * from Person p where p.name in ('a', 'b', '_' + RAND())"); // test ConditionIn + checkQuery("select * from Person p where p.name in ('a', 'b', 'c')"); // test ConditionInConstantSet + checkQuery("select * from Person p where p.name in (select a.street from Address a)"); // test ConditionInConstantSet + + checkQuery("select (select a.street from Address a where a.id = p.addrId) from Person p"); // test ConditionInConstantSet + + checkQuery("select p.name, ? from Person p where name regexp ? and p.old < ?"); + + checkQuery("select count(*) as a from Person"); + checkQuery("select count(*) as a, count(p.*), count(p.name) from Person p"); + checkQuery("select count(distinct p.name) from Person p"); + + checkQuery("select p.name, avg(p.old), max(p.old) from Person p group by p.name"); + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name"); + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by n"); + + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.addrId, p.name"); + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name, p.addrId"); + checkQuery("select p.name n, max(p.old) + min(p.old) / count(distinct p.old) from Person p group by p.name"); + checkQuery("select p.name n, max(p.old) maxOld, min(p.old) minOld from Person p group by p.name having maxOld > 10 and min(p.old) < 1"); + + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by n"); + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name"); + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name, m"); + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name, max(p.old) desc"); + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name nulls first"); + checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name nulls last"); + checkQuery("select p.name n from Person p order by p.old + 10"); + checkQuery("select p.name n from Person p order by p.old + 10, p.name"); + checkQuery("select p.name n from Person p order by p.old + 10, p.name desc"); + + checkQuery("select p.name n from Person p, (select a.street from Address a where a.street is not null) "); + checkQuery("select street from Person p, (select a.street from Address a where a.street is not null) "); + checkQuery("select addr.street from Person p, (select a.street from Address a where a.street is not null) addr"); + + checkQuery("select p.name n from PUBLIC.Person p order by p.old + 10"); + } + + /** + * + */ + public void testExample1() throws Exception { + Select select = parse("select p.name n, max(p.old) maxOld, min(p.old) minOld from Person p group by p.name having maxOld > 10 and min(p.old) < 1"); + + GridSqlQueryParser ses = new GridSqlQueryParser(); + + GridSqlSelect gridSelect = ses.parse(select); + + //System.out.println(select.getPlanSQL()); + System.out.println(gridSelect.getSQL()); + } + + /** + * + */ + private JdbcConnection connection() throws Exception { + GridKernalContext ctx = ((GridKernal)ignite).context(); + + GridQueryProcessor qryProcessor = ctx.query(); + + IgniteH2Indexing idx = U.field(qryProcessor, "idx"); + + return (JdbcConnection)idx.connectionForSpace(null); + } + + /** + * @param sql Sql. + */ + private <T extends Prepared> T parse(String sql) throws Exception { + Session ses = (Session)connection().getSession(); + + return (T)ses.prepare(sql); + } + + /** + * @param sql1 Sql 1. + * @param sql2 Sql 2. + */ + private void assertSqlEquals(String sql1, String sql2) { + assertEquals(normalizeSql(sql1), normalizeSql(sql2)); + } + + /** + * @param sql Sql. + */ + private static String normalizeSql(String sql) { + return sql.toLowerCase() + .replaceAll("/\\*(?:.|\r|\n)*?\\*/", " ") + .replaceAll("\\s*on\\s+1\\s*=\\s*1\\s*", " ") + .replaceAll("\\s+", " ") + .replaceAll("\\( +", "(") + .replaceAll(" +\\)", ")") + .trim(); + } + + /** + * @param qry Query. + */ + private void checkQuery(String qry) throws Exception { + Prepared prepared = parse(qry); + + GridSqlQueryParser ses = new GridSqlQueryParser(); + + String res; + + if (prepared instanceof Select) + res = ses.parse((Select) prepared).getSQL(); + else + throw new UnsupportedOperationException(); + + assertSqlEquals(prepared.getPlanSQL(), res); + + System.out.println(normalizeSql(res)); + } + + /** + * + */ + public static class Person implements Serializable { + @CacheQuerySqlField(index = true) + public Date date = new Date(); + + @CacheQuerySqlField(index = true) + public String name = "Ivan"; + + @CacheQuerySqlField(index = true) + public String parentName; + + @CacheQuerySqlField(index = true) + public int addrId; + + @CacheQuerySqlField(index = true) + public int old; + } + + /** + * + */ + public static class Address implements Serializable { + @CacheQuerySqlField(index = true) + public int id; + + @CacheQuerySqlField(index = true) + public int streetNumber; + + @CacheQuerySqlField(index = true) + public String street = "Nevskiy"; + } +} http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/3d268155/modules/indexing/src/test/java/org/gridgain/grid/kernal/processors/cache/GridCacheCrossCacheQuerySelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/gridgain/grid/kernal/processors/cache/GridCacheCrossCacheQuerySelfTest.java b/modules/indexing/src/test/java/org/gridgain/grid/kernal/processors/cache/GridCacheCrossCacheQuerySelfTest.java deleted file mode 100644 index 6e91900..0000000 --- a/modules/indexing/src/test/java/org/gridgain/grid/kernal/processors/cache/GridCacheCrossCacheQuerySelfTest.java +++ /dev/null @@ -1,437 +0,0 @@ -/* - * 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. - */ - -package org.gridgain.grid.kernal.processors.cache; - -import org.apache.ignite.*; -import org.apache.ignite.cache.*; -import org.apache.ignite.cache.query.*; -import org.apache.ignite.configuration.*; -import org.apache.ignite.internal.processors.cache.query.*; -import org.apache.ignite.internal.util.typedef.*; -import org.apache.ignite.lang.*; -import org.apache.ignite.marshaller.optimized.*; -import org.apache.ignite.spi.discovery.tcp.*; -import org.apache.ignite.spi.discovery.tcp.ipfinder.*; -import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.*; -import org.apache.ignite.testframework.junits.common.*; -import org.gridgain.grid.kernal.processors.cache.query.*; - -import java.util.*; - -import static org.apache.ignite.cache.CacheAtomicityMode.*; -import static org.apache.ignite.cache.CacheDistributionMode.*; -import static org.apache.ignite.cache.CachePreloadMode.*; - -/** - * Tests cross cache queries. - */ -public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { - /** */ - private static final TcpDiscoveryIpFinder ipFinder = new TcpDiscoveryVmIpFinder(true); - - /** */ - private Ignite ignite; - - /** {@inheritDoc} */ - @Override protected IgniteConfiguration getConfiguration(String gridName) throws Exception { - IgniteConfiguration c = super.getConfiguration(gridName); - - TcpDiscoverySpi disco = new TcpDiscoverySpi(); - - disco.setIpFinder(ipFinder); - - c.setDiscoverySpi(disco); - - c.setMarshaller(new IgniteOptimizedMarshaller(false)); - - c.setCacheConfiguration(createCache("replicated", CacheMode.REPLICATED), - createCache("partitioned", CacheMode.PARTITIONED)); - - return c; - } - - /** {@inheritDoc} */ - @Override protected void beforeTest() throws Exception { - ignite = startGridsMultiThreaded(3); - } - - /** {@inheritDoc} */ - @Override protected void afterTest() throws Exception { - stopAllGrids(); - - ignite = null; - } - - /** - * Creates new cache configuration. - * - * @param name Cache name. - * @param mode Cache mode. - * @return Cache configuration. - */ - private static CacheConfiguration createCache(String name, CacheMode mode) { - CacheConfiguration cc = defaultCacheConfiguration(); - - cc.setName(name); - cc.setCacheMode(mode); - cc.setWriteSynchronizationMode(CacheWriteSynchronizationMode.FULL_SYNC); - cc.setPreloadMode(SYNC); - cc.setSwapEnabled(true); - cc.setEvictNearSynchronized(false); - cc.setAtomicityMode(TRANSACTIONAL); - cc.setDistributionMode(NEAR_PARTITIONED); - - return cc; - } - - /** - * @throws Exception If failed. - */ - public void testTwoStep() throws Exception { - fillCaches(); - - String cache = "partitioned"; - - GridCacheQueriesEx<Integer, FactPurchase> qx = - (GridCacheQueriesEx<Integer, FactPurchase>)ignite.<Integer, FactPurchase>cache(cache).queries(); - -// for (Map.Entry<Integer, FactPurchase> e : qx.createSqlQuery(FactPurchase.class, "1 = 1").execute().get()) -// X.println("___ " + e); - - GridCacheTwoStepQuery q = new GridCacheTwoStepQuery("select cast(sum(x) as long) from _cnts_ where ? = ?", 1, 1); - - q.addMapQuery("_cnts_", "select count(*) x from \"partitioned\".FactPurchase where ? = ?", 2 ,2); - - Object cnt = qx.execute(cache, q).get().iterator().next().get(0); - - assertEquals(10L, cnt); - } - - /** - * @throws Exception If failed. - */ - public void testTwoStepGroupAndAggregates() throws Exception { - fillCaches(); - - GridCacheQueriesEx<Integer, FactPurchase> qx = - (GridCacheQueriesEx<Integer, FactPurchase>)ignite.<Integer, FactPurchase>cache("partitioned").queries(); - - Set<Integer> set1 = new HashSet<>(); - - X.println("___ simple"); - - for (List<?> o : qx.executeTwoStepQuery("partitioned", "select f.productId, p.name, f.price " + - "from FactPurchase f, \"replicated\".DimProduct p where p.id = f.productId ").get()) { - X.println("___ -> " + o); - - set1.add((Integer)o.get(0)); - } - - Set<Integer> set0 = new HashSet<>(); - - X.println("___ GROUP BY"); - - for (List<?> o : qx.executeTwoStepQuery("partitioned", "select productId from FactPurchase group by productId") - .get()) { - X.println("___ -> " + o); - - assertTrue(set0.add((Integer) o.get(0))); - } - - assertFalse(set1.isEmpty()); - assertEquals(set0, set1); - - X.println("___ AVG MIN MAX SUM COUNT(*) COUNT(x)"); - - for (List<?> o : qx.executeTwoStepQuery("partitioned", - "select p.name, avg(f.price), min(f.price), max(f.price), sum(f.price), count(*), " + - "count(nullif(f.price, 5)) " + - "from FactPurchase f, \"replicated\".DimProduct p " + - "where p.id = f.productId " + - "group by f.productId, p.name").get()) { - X.println("___ -> " + o); - } - } - - /** @throws Exception If failed. */ - public void testOnProjection() throws Exception { - fillCaches(); - - CacheProjection<Integer, FactPurchase> prj = ignite.<Integer, FactPurchase>cache("partitioned").projection( - new IgnitePredicate<CacheEntry<Integer, FactPurchase>>() { - @Override - public boolean apply(CacheEntry<Integer, FactPurchase> e) { - return e.getKey() > 12; - } - }); - - List<Map.Entry<Integer, FactPurchase>> res = body(prj); - - check(res); - } - - /** - * @throws IgniteCheckedException If failed. - */ - private void fillCaches() throws IgniteCheckedException, InterruptedException { - int idGen = 0; - - GridCache<Integer, Object> dimCache = ignite.cache("replicated"); - - for (int i = 0; i < 2; i++) { - int id = idGen++; - - dimCache.put(id, new DimStore(id, "Store" + id)); - } - - for (int i = 0; i < 5; i++) { - int id = idGen++; - - dimCache.put(id, new DimProduct(id, "Product" + id)); - } - - CacheProjection<Integer, DimStore> stores = dimCache.projection(Integer.class, DimStore.class); - CacheProjection<Integer, DimProduct> prods = dimCache.projection(Integer.class, DimProduct.class); - - GridCache<Integer, FactPurchase> factCache = ignite.cache("partitioned"); - - List<DimStore> dimStores = new ArrayList<>(stores.values()); - Collections.sort(dimStores, new Comparator<DimStore>() { - @Override public int compare(DimStore o1, DimStore o2) { - return o1.getId() > o2.getId() ? 1 : o1.getId() < o2.getId() ? -1 : 0; - } - }); - - List<DimProduct> dimProds = new ArrayList<>(prods.values()); - Collections.sort(dimProds, new Comparator<DimProduct>() { - @Override - public int compare(DimProduct o1, DimProduct o2) { - return o1.getId() > o2.getId() ? 1 : o1.getId() < o2.getId() ? -1 : 0; - } - }); - - for (int i = 0; i < 10; i++) { - int id = idGen++; - - DimStore store = dimStores.get(i % dimStores.size()); - DimProduct prod = dimProds.get(i % dimProds.size()); - - factCache.put(id, new FactPurchase(id, prod.getId(), store.getId(), i + 5)); - } - } - - /** - * Fills the caches with data and executes the query. - * - * @param prj Cache projection. - * @throws Exception If failed. - * @return Result. - */ - private List<Map.Entry<Integer, FactPurchase>> body(CacheProjection<Integer, FactPurchase> prj) - throws Exception { - CacheQuery<Map.Entry<Integer, FactPurchase>> qry = (prj == null ? - ignite.<Integer, FactPurchase>cache("partitioned") : prj).queries().createSqlQuery(FactPurchase.class, - "from \"replicated\".DimStore, \"partitioned\".FactPurchase where DimStore.id = FactPurchase.storeId"); - - List<Map.Entry<Integer, FactPurchase>> res = new ArrayList<>(qry.execute().get()); - Collections.sort(res, new Comparator<Map.Entry<Integer, FactPurchase>>() { - @Override public int compare(Map.Entry<Integer, FactPurchase> o1, Map.Entry<Integer, FactPurchase> o2) { - return o1.getKey() > o2.getKey() ? 1 : o1.getKey() < o2.getKey() ? -1 : 0; - } - }); - - return res; - } - - /** - * Checks result. - * @param res Result to check. - */ - private static void check(List<Map.Entry<Integer, FactPurchase>> res) { - assertEquals("Result size", 4, res.size()); - - checkPurchase(res.get(0), 13, 3, 0); - checkPurchase(res.get(1), 14, 4, 1); - checkPurchase(res.get(2), 15, 5, 0); - checkPurchase(res.get(3), 16, 6, 1); - } - - /** - * Checks purchase. - * @param entry Entry to check. - * @param id ID. - * @param productId Product ID. - * @param storeId Store ID. - */ - private static void checkPurchase(Map.Entry<Integer, FactPurchase> entry, int id, int productId, int storeId) { - FactPurchase purchase = entry.getValue(); - - assertEquals("Id", id, entry.getKey().intValue()); - assertEquals("Id", id, purchase.getId()); - assertEquals("ProductId", productId, purchase.getProductId()); - assertEquals("StoreId", storeId, purchase.getStoreId()); - } - - /** - * Represents a product available for purchase. In our {@code snowflake} schema a {@code product} is a {@code - * 'dimension'} and will be cached in {@link CacheMode#REPLICATED} cache. - */ - private static class DimProduct { - /** Primary key. */ - @CacheQuerySqlField - private int id; - - /** Product name. */ - @CacheQuerySqlField - private String name; - - /** - * Constructs a product instance. - * - * @param id Product ID. - * @param name Product name. - */ - DimProduct(int id, String name) { - this.id = id; - this.name = name; - } - - /** - * Gets product ID. - * - * @return Product ID. - */ - public int getId() { - return id; - } - - /** - * Gets product name. - * - * @return Product name. - */ - public String getName() { - return name; - } - } - - /** - * Represents a physical store location. In our {@code snowflake} schema a {@code store} is a {@code 'dimension'} - * and will be cached in {@link CacheMode#REPLICATED} cache. - */ - private static class DimStore { - /** Primary key. */ - @CacheQuerySqlField(unique = true) - private int id; - - /** Store name. */ - @CacheQuerySqlField - private String name; - - /** - * Constructs a store instance. - * - * @param id Store ID. - * @param name Store name. - */ - DimStore(int id, String name) { - this.id = id; - this.name = name; - } - - /** - * Gets store ID. - * - * @return Store ID. - */ - public int getId() { - return id; - } - - /** - * Gets store name. - * - * @return Store name. - */ - public String getName() { - return name; - } - } - - /** - * Represents a purchase record. In our {@code snowflake} schema purchase is a {@code 'fact'} and will be cached in - * larger {@link CacheMode#PARTITIONED} cache. - */ - private static class FactPurchase { - /** Primary key. */ - @CacheQuerySqlField(unique = true) - private int id; - - /** Foreign key to store at which purchase occurred. */ - @CacheQuerySqlField - private int storeId; - - /** Foreign key to purchased product. */ - @CacheQuerySqlField - private int productId; - - @CacheQuerySqlField - private int price; - - /** - * Constructs a purchase record. - * - * @param id Purchase ID. - * @param productId Purchased product ID. - * @param storeId Store ID. - */ - FactPurchase(int id, int productId, int storeId, int price) { - this.id = id; - this.productId = productId; - this.storeId = storeId; - this.price = price; - } - - /** - * Gets purchase ID. - * - * @return Purchase ID. - */ - public int getId() { - return id; - } - - /** - * Gets purchased product ID. - * - * @return Product ID. - */ - public int getProductId() { - return productId; - } - - /** - * Gets ID of store at which purchase was made. - * - * @return Store ID. - */ - public int getStoreId() { - return storeId; - } - } -} http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/3d268155/modules/indexing/src/test/java/org/gridgain/grid/kernal/processors/query/h2/sql/GridQueryTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/gridgain/grid/kernal/processors/query/h2/sql/GridQueryTest.java b/modules/indexing/src/test/java/org/gridgain/grid/kernal/processors/query/h2/sql/GridQueryTest.java deleted file mode 100644 index 478215d..0000000 --- a/modules/indexing/src/test/java/org/gridgain/grid/kernal/processors/query/h2/sql/GridQueryTest.java +++ /dev/null @@ -1,242 +0,0 @@ -/* @java.file.header */ - -/* _________ _____ __________________ _____ - * __ ____/___________(_)______ /__ ____/______ ____(_)_______ - * _ / __ __ ___/__ / _ __ / _ / __ _ __ `/__ / __ __ \ - * / /_/ / _ / _ / / /_/ / / /_/ / / /_/ / _ / _ / / / - * \____/ /_/ /_/ \_,__/ \____/ \__,_/ /_/ /_/ /_/ - */ - -package org.gridgain.grid.kernal.processors.query.h2.sql; - -import org.apache.ignite.cache.*; -import org.apache.ignite.cache.query.*; -import org.apache.ignite.internal.*; -import org.apache.ignite.internal.processors.cache.*; -import org.apache.ignite.internal.processors.query.*; -import org.apache.ignite.internal.processors.query.h2.*; -import org.apache.ignite.internal.util.typedef.internal.*; -import org.h2.command.*; -import org.h2.command.dml.*; -import org.h2.engine.*; -import org.h2.jdbc.*; - -import java.io.*; -import java.util.*; - -/** - * - */ -public class GridQueryTest extends GridCacheAbstractQuerySelfTest { - /** {@inheritDoc} */ - @Override protected int gridCount() { - return 1; - } - - /** {@inheritDoc} */ - @Override protected CacheMode cacheMode() { - return CacheMode.REPLICATED; - } - - /** {@inheritDoc} */ - @Override protected void beforeTest() throws Exception { - super.beforeTest(); - - GridCache cache = ignite.cache(null); - - cache.putx("testAddr", new Address()); - cache.putx("testPerson", new Person()); - } - - /** - * - */ - public void testAllExampless() throws Exception { - checkQuery("select name, date from Person"); - checkQuery("select distinct name, date from Person"); - checkQuery("select * from Person p"); - checkQuery("select * from Person"); - checkQuery("select distinct * from Person"); - checkQuery("select p.name, date from Person p"); - - checkQuery("select * from Person p, Address a"); - checkQuery("select * from Person, Address"); - checkQuery("select p.* from Person p, Address a"); - checkQuery("select person.* from Person, Address a"); - checkQuery("select p.*, street from Person p, Address a"); - checkQuery("select p.name, a.street from Person p, Address a"); - checkQuery("select distinct p.name, a.street from Person p, Address a"); - checkQuery("select distinct name, street from Person, Address"); - checkQuery("select p1.name, a2.street from Person p1, Address a1, Person p2, Address a2"); - - checkQuery("select p.name n, a.street s from Person p, Address a"); - checkQuery("select p.name, 1 as i, 'aaa' s from Person p"); - - checkQuery("select p.name + 'a', 1 * 3 as i, 'aaa' s, -p.old, -p.old as old from Person p"); - checkQuery("select p.name || 'a' + p.name, (p.old * 3) % p.old - p.old / p.old, p.name = 'aaa', " + - " p.name is p.name, p.old > 0, p.old >= 0, p.old < 0, p.old <= 0, p.old <> 0, p.old is not p.old, " + - " p.old is null, p.old is not null " + - " from Person p"); - - checkQuery("select p.name from Person p where name <> 'ivan'"); - checkQuery("select p.name from Person p where name like 'i%'"); - checkQuery("select p.name from Person p where name regexp 'i%'"); - checkQuery("select p.name from Person p, Address a where p.name <> 'ivan' and a.id > 10 or not (a.id = 100)"); - - checkQuery("select case p.name when 'a' then 1 when 'a' then 2 end as a from Person p"); - checkQuery("select case p.name when 'a' then 1 when 'a' then 2 else -1 end as a from Person p"); - - checkQuery("select abs(p.old) from Person p"); - checkQuery("select cast(p.old as numeric(10, 2)) from Person p"); - checkQuery("select cast(p.old as numeric(10, 2)) z from Person p"); - checkQuery("select cast(p.old as numeric(10, 2)) as z from Person p"); - - checkQuery("select * from Person p where p.name in ('a', 'b', '_' + RAND())"); // test ConditionIn - checkQuery("select * from Person p where p.name in ('a', 'b', 'c')"); // test ConditionInConstantSet - checkQuery("select * from Person p where p.name in (select a.street from Address a)"); // test ConditionInConstantSet - - checkQuery("select (select a.street from Address a where a.id = p.addrId) from Person p"); // test ConditionInConstantSet - - checkQuery("select p.name, ? from Person p where name regexp ? and p.old < ?"); - - checkQuery("select count(*) as a from Person"); - checkQuery("select count(*) as a, count(p.*), count(p.name) from Person p"); - checkQuery("select count(distinct p.name) from Person p"); - - checkQuery("select p.name, avg(p.old), max(p.old) from Person p group by p.name"); - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name"); - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by n"); - - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.addrId, p.name"); - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name, p.addrId"); - checkQuery("select p.name n, max(p.old) + min(p.old) / count(distinct p.old) from Person p group by p.name"); - checkQuery("select p.name n, max(p.old) maxOld, min(p.old) minOld from Person p group by p.name having maxOld > 10 and min(p.old) < 1"); - - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by n"); - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name"); - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name, m"); - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name, max(p.old) desc"); - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name nulls first"); - checkQuery("select p.name n, avg(p.old) a, max(p.old) m from Person p group by p.name order by p.name nulls last"); - checkQuery("select p.name n from Person p order by p.old + 10"); - checkQuery("select p.name n from Person p order by p.old + 10, p.name"); - checkQuery("select p.name n from Person p order by p.old + 10, p.name desc"); - - checkQuery("select p.name n from Person p, (select a.street from Address a where a.street is not null) "); - checkQuery("select street from Person p, (select a.street from Address a where a.street is not null) "); - checkQuery("select addr.street from Person p, (select a.street from Address a where a.street is not null) addr"); - - checkQuery("select p.name n from PUBLIC.Person p order by p.old + 10"); - } - - /** - * - */ - public void testExample1() throws Exception { - Select select = parse("select p.name n, max(p.old) maxOld, min(p.old) minOld from Person p group by p.name having maxOld > 10 and min(p.old) < 1"); - - GridSqlQueryParser ses = new GridSqlQueryParser(); - - GridSqlSelect gridSelect = ses.parse(select); - - //System.out.println(select.getPlanSQL()); - System.out.println(gridSelect.getSQL()); - } - - /** - * - */ - private JdbcConnection connection() throws Exception { - GridKernalContext ctx = ((GridKernal)ignite).context(); - - GridQueryProcessor qryProcessor = ctx.query(); - - IgniteH2Indexing idx = U.field(qryProcessor, "idx"); - - return (JdbcConnection)idx.connectionForSpace(null); - } - - /** - * @param sql Sql. - */ - private <T extends Prepared> T parse(String sql) throws Exception { - Session ses = (Session)connection().getSession(); - - return (T)ses.prepare(sql); - } - - /** - * @param sql1 Sql 1. - * @param sql2 Sql 2. - */ - private void assertSqlEquals(String sql1, String sql2) { - assertEquals(normalizeSql(sql1), normalizeSql(sql2)); - } - - /** - * @param sql Sql. - */ - private static String normalizeSql(String sql) { - return sql.toLowerCase() - .replaceAll("/\\*(?:.|\r|\n)*?\\*/", " ") - .replaceAll("\\s*on\\s+1\\s*=\\s*1\\s*", " ") - .replaceAll("\\s+", " ") - .replaceAll("\\( +", "(") - .replaceAll(" +\\)", ")") - .trim(); - } - - /** - * @param qry Query. - */ - private void checkQuery(String qry) throws Exception { - Prepared prepared = parse(qry); - - GridSqlQueryParser ses = new GridSqlQueryParser(); - - String res; - - if (prepared instanceof Select) - res = ses.parse((Select) prepared).getSQL(); - else - throw new UnsupportedOperationException(); - - assertSqlEquals(prepared.getPlanSQL(), res); - - System.out.println(normalizeSql(res)); - } - - /** - * - */ - public static class Person implements Serializable { - @CacheQuerySqlField(index = true) - public Date date = new Date(); - - @CacheQuerySqlField(index = true) - public String name = "Ivan"; - - @CacheQuerySqlField(index = true) - public String parentName; - - @CacheQuerySqlField(index = true) - public int addrId; - - @CacheQuerySqlField(index = true) - public int old; - } - - /** - * - */ - public static class Address implements Serializable { - @CacheQuerySqlField(index = true) - public int id; - - @CacheQuerySqlField(index = true) - public int streetNumber; - - @CacheQuerySqlField(index = true) - public String street = "Nevskiy"; - } -}
