You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by ag...@apache.org on 2015/04/10 03:44:13 UTC
[06/50] [abbrv] incubator-ignite git commit: ignite-437: test for
bigQuery sql
ignite-437: test for bigQuery sql
Project: http://git-wip-us.apache.org/repos/asf/incubator-ignite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-ignite/commit/fe743909
Tree: http://git-wip-us.apache.org/repos/asf/incubator-ignite/tree/fe743909
Diff: http://git-wip-us.apache.org/repos/asf/incubator-ignite/diff/fe743909
Branch: refs/heads/ignite-674
Commit: fe743909cf483d00ab5189049523ed3353bf5a3d
Parents: 2f458d2
Author: Artem Shutak <as...@gridgain.com>
Authored: Wed Apr 8 19:55:29 2015 +0300
Committer: Artem Shutak <as...@gridgain.com>
Committed: Wed Apr 8 19:55:29 2015 +0300
----------------------------------------------------------------------
.../h2/sql/AbstractH2CompareQueryTest.java | 341 ++++++++
.../query/h2/sql/BaseH2CompareQueryTest.java | 823 +++++++++++++++++
.../query/h2/sql/H2CompareBigQueryTest.java | 707 +++++++++++++++
.../query/h2/sql/IgniteVsH2QueryTest.java | 875 -------------------
.../IgniteCacheQuerySelfTestSuite.java | 5 +-
.../processors/query/h2/sql/bigQuery.sql | 55 ++
6 files changed, 1929 insertions(+), 877 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fe743909/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/AbstractH2CompareQueryTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/AbstractH2CompareQueryTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/AbstractH2CompareQueryTest.java
new file mode 100644
index 0000000..c74493f
--- /dev/null
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/AbstractH2CompareQueryTest.java
@@ -0,0 +1,341 @@
+/*
+ * 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.apache.ignite.internal.processors.query.h2.sql;
+
+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.util.typedef.internal.*;
+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.jetbrains.annotations.*;
+
+import java.sql.*;
+import java.util.*;
+
+/**
+ * Abstract test framework to compare query results from h2 database instance and mixed ignite caches (replicated and partitioned)
+ * which have the same data models and data content.
+ */
+public abstract class AbstractH2CompareQueryTest extends GridCommonAbstractTest {
+ /** */
+ private static final TcpDiscoveryIpFinder IP_FINDER = new TcpDiscoveryVmIpFinder(true);
+
+ /** Partitioned cache. */
+ protected static IgniteCache pCache;
+
+ /** Replicated cache. */
+ protected static IgniteCache rCache;
+
+ /** H2 db connection. */
+ protected static Connection conn;
+
+ /** {@inheritDoc} */
+ @SuppressWarnings("unchecked")
+ @Override protected IgniteConfiguration getConfiguration(String gridName) throws Exception {
+ IgniteConfiguration c = super.getConfiguration(gridName);
+
+ TcpDiscoverySpi disco = new TcpDiscoverySpi();
+
+ disco.setIpFinder(IP_FINDER);
+
+ c.setDiscoverySpi(disco);
+
+ c.setMarshaller(new OptimizedMarshaller(true));
+
+ c.setCacheConfiguration(createCache("part", CacheMode.PARTITIONED),
+ createCache("repl", CacheMode.REPLICATED)
+ );
+
+ return c;
+ }
+
+ /**
+ * Creates new cache configuration.
+ *
+ * @param name Cache name.
+ * @param mode Cache mode.
+ * @return Cache configuration.
+ */
+ private CacheConfiguration createCache(String name, CacheMode mode) {
+ CacheConfiguration<?,?> cc = defaultCacheConfiguration();
+
+ cc.setName(name);
+ cc.setCacheMode(mode);
+ cc.setWriteSynchronizationMode(CacheWriteSynchronizationMode.FULL_SYNC);
+ cc.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
+
+ setIndexedTypes(cc, mode);
+
+ return cc;
+ }
+
+ /**
+ * @param cc Cache configuration.
+ * @param mode Cache Mode.
+ */
+ protected abstract void setIndexedTypes(CacheConfiguration<?, ?> cc, CacheMode mode) ;
+
+ /** {@inheritDoc} */
+ @Override protected void beforeTestsStarted() throws Exception {
+ super.beforeTestsStarted();
+
+ Ignite ignite = startGrids(4);
+
+ pCache = ignite.cache("part");
+
+ rCache = ignite.cache("repl");
+
+ awaitPartitionMapExchange();
+
+ conn = openH2Connection(false);
+
+ initializeH2Schema();
+
+ initCacheAndDbData();
+
+ checkAllDataEquals();
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTestsStopped() throws Exception {
+ super.afterTestsStopped();
+
+ conn.close();
+
+ stopAllGrids();
+ }
+
+ /**
+ * Populate cache and h2 database with test data.
+ */
+ protected abstract void initCacheAndDbData() throws SQLException;
+
+ /**
+ * @throws Exception If failed.
+ */
+ protected abstract void checkAllDataEquals() throws Exception;
+
+ /**
+ * Initialize h2 database schema.
+ *
+ * @throws SQLException If exception.
+ */
+ protected abstract void initializeH2Schema() throws SQLException;
+
+ /**
+ * Gets connection from a pool.
+ *
+ * @param autocommit {@code true} If connection should use autocommit mode.
+ * @return Pooled connection.
+ * @throws SQLException In case of error.
+ */
+ private Connection openH2Connection(boolean autocommit) throws SQLException {
+ System.setProperty("h2.serializeJavaObject", "false");
+
+ String dbName = "test";
+
+ Connection conn = DriverManager.getConnection("jdbc:h2:mem:" + dbName + ";DB_CLOSE_DELAY=-1");
+
+ conn.setAutoCommit(autocommit);
+
+ return conn;
+ }
+
+ /**
+ * Execute given sql query on h2 database and on partitioned ignite cache and compare results.
+ *
+ * @param sql SQL query.
+ * @param args SQL arguments.
+ * then results will compare as ordered queries.
+ * @return Result set after SQL query execution.
+ * @throws SQLException If exception.
+ */
+ protected final List<List<?>> compareQueryRes0(String sql, @Nullable Object... args) throws SQLException {
+ return compareQueryRes0(pCache, sql, args, Ordering.RANDOM);
+ }
+
+ /**
+ * Execute given sql query on h2 database and on ignite cache and compare results.
+ * Expected that results are not ordered.
+ *
+ * @param cache Ignite cache.
+ * @param sql SQL query.
+ * @param args SQL arguments.
+ * then results will compare as ordered queries.
+ * @return Result set after SQL query execution.
+ * @throws SQLException If exception.
+ */
+ protected final List<List<?>> compareQueryRes0(IgniteCache cache, String sql, @Nullable Object... args) throws SQLException {
+ return compareQueryRes0(cache, sql, args, Ordering.RANDOM);
+ }
+
+ /**
+ * Execute given sql query on h2 database and on partitioned ignite cache and compare results.
+ * Expected that results are ordered.
+ *
+ * @param sql SQL query.
+ * @param args SQL arguments.
+ * then results will compare as ordered queries.
+ * @return Result set after SQL query execution.
+ * @throws SQLException If exception.
+ */
+ protected final List<List<?>> compareOrderedQueryRes0(String sql, @Nullable Object... args) throws SQLException {
+ return compareQueryRes0(pCache, sql, args, Ordering.ORDERED);
+ }
+
+ /**
+ * Execute given sql query on h2 database and on ignite cache and compare results.
+ *
+ * @param cache Ignite cache.
+ * @param sql SQL query.
+ * @param args SQL arguments.
+ * @param ordering Expected ordering of SQL results. If {@link Ordering#ORDERED}
+ * then results will compare as ordered queries.
+ * @return Result set after SQL query execution.
+ * @throws SQLException If exception.
+ */
+ @SuppressWarnings("unchecked")
+ protected final List<List<?>> compareQueryRes0(IgniteCache cache, String sql, @Nullable Object[] args, Ordering ordering) throws SQLException {
+ if (args == null)
+ args = new Object[] {null};
+
+ info("Sql query:\n" + sql + "\nargs=" + Arrays.toString(args));
+
+ List<List<?>> h2Res = executeH2Query(sql, args);
+
+ List<List<?>> cacheRes = cache.query(new SqlFieldsQuery(sql).setArgs(args)).getAll();
+
+ assertRsEquals(h2Res, cacheRes, ordering);
+
+ return h2Res;
+ }
+
+ /**
+ * Execute SQL query on h2 database.
+ *
+ * @param sql SQL query.
+ * @param args SQL arguments.
+ * @return Result of SQL query on h2 database.
+ * @throws SQLException If exception.
+ */
+ private List<List<?>> executeH2Query(String sql, Object[] args) throws SQLException {
+ List<List<?>> res = new ArrayList<>();
+ ResultSet rs = null;
+
+ try(PreparedStatement st = conn.prepareStatement(sql)) {
+ for (int idx = 0; idx < args.length; idx++)
+ st.setObject(idx + 1, args[idx]);
+
+ rs = st.executeQuery();
+
+ int colCnt = rs.getMetaData().getColumnCount();
+
+ while (rs.next()) {
+ List<Object> row = new ArrayList<>(colCnt);
+
+ for (int i = 1; i <= colCnt; i++)
+ row.add(rs.getObject(i));
+
+ res.add(row);
+ }
+ }
+ finally {
+ U.closeQuiet(rs);
+ }
+
+ return res;
+ }
+
+ /**
+ * Assert equals of result sets according to expected ordering.
+ *
+ * @param rs1 Expected result set.
+ * @param rs2 Actual result set.
+ * @param ordering Expected ordering of SQL results. If {@link Ordering#ORDERED}
+ * then results will compare as ordered queries.
+ */
+ private void assertRsEquals(List<List<?>> rs1, List<List<?>> rs2, Ordering ordering) {
+ assertEquals("Rows count has to be equal.", rs1.size(), rs2.size());
+
+ switch (ordering){
+ case ORDERED:
+ for (int rowNum = 0; rowNum < rs1.size(); rowNum++) {
+ List<?> row1 = rs1.get(rowNum);
+ List<?> row2 = rs2.get(rowNum);
+
+ assertEquals("Columns count have to be equal.", row1.size(), row2.size());
+
+ for (int colNum = 0; colNum < row1.size(); colNum++)
+ assertEquals("Row=" + rowNum + ", column=" + colNum, row1.get(colNum), row2.get(colNum));
+ }
+
+ break;
+ case RANDOM:
+ Map<List<?>, Integer> rowsWithCnt1 = extractUniqueRowsWithCounts(rs1);
+ Map<List<?>, Integer> rowsWithCnt2 = extractUniqueRowsWithCounts(rs2);
+
+ assertEquals("Unique rows count has to be equal.", rowsWithCnt1.size(), rowsWithCnt2.size());
+
+ for (Map.Entry<List<?>, Integer> entry1 : rowsWithCnt1.entrySet()) {
+ List<?> row = entry1.getKey();
+ Integer cnt1 = entry1.getValue();
+
+ Integer cnt2 = rowsWithCnt2.get(row);
+
+ assertEquals("Row has different occurance number.\nRow=" + row, cnt1, cnt2);
+ }
+
+ break;
+ default:
+ throw new IllegalStateException();
+ }
+ }
+
+ /**
+ * @param rs Result set.
+ * @return Map of unique rows at the result set to number of occuriances at the result set.
+ */
+ private Map<List<?>, Integer> extractUniqueRowsWithCounts(Iterable<List<?>> rs) {
+ Map<List<?>, Integer> res = new HashMap<>();
+
+ for (List<?> row : rs) {
+ Integer cnt = res.get(row);
+
+ if (cnt == null)
+ cnt = 0;
+
+ res.put(row, cnt + 1);
+ }
+ return res;
+ }
+
+ /**
+ * Ordering type.
+ */
+ protected enum Ordering {
+ /** Random. */
+ RANDOM,
+ /** Ordered. */
+ ORDERED
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fe743909/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/BaseH2CompareQueryTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/BaseH2CompareQueryTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/BaseH2CompareQueryTest.java
new file mode 100644
index 0000000..eea834d
--- /dev/null
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/BaseH2CompareQueryTest.java
@@ -0,0 +1,823 @@
+/*
+ * 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.apache.ignite.internal.processors.query.h2.sql;
+
+import org.apache.ignite.cache.*;
+import org.apache.ignite.cache.affinity.*;
+import org.apache.ignite.cache.query.annotations.*;
+import org.apache.ignite.configuration.*;
+
+import java.io.*;
+import java.sql.*;
+import java.sql.Date;
+import java.util.*;
+
+/**
+ * Base set of queries to compare query results from h2 database instance and mixed ignite caches (replicated and partitioned)
+ * which have the same data models and data content.
+ */
+public class BaseH2CompareQueryTest extends AbstractH2CompareQueryTest {
+ /** Org count. */
+ public static final int ORG_CNT = 30;
+
+ /** Address count. */
+ public static final int ADDR_CNT = 10;
+
+ /** Person count. */
+ public static final int PERS_CNT = 50;
+
+ /** Product count. */
+ public static final int PROD_CNT = 100;
+
+ /** Purchase count. */
+ public static final int PURCH_CNT = PROD_CNT * 5;
+
+ /** {@inheritDoc} */
+ @Override protected void setIndexedTypes(CacheConfiguration<?, ?> cc, CacheMode mode) {
+ if (mode == CacheMode.PARTITIONED)
+ cc.setIndexedTypes(
+ Integer.class, Organization.class,
+ AffinityKey.class, Person.class,
+ AffinityKey.class, Purchase.class
+ );
+ else if (mode == CacheMode.REPLICATED)
+ cc.setIndexedTypes(
+ Integer.class, Product.class,
+ Integer.class, Address.class
+ );
+ else
+ throw new IllegalStateException("mode: " + mode);
+ }
+
+ /** {@inheritDoc} */
+ @SuppressWarnings("unchecked")
+ @Override protected void initCacheAndDbData() throws SQLException {
+ int idGen = 0;
+
+ // Organizations.
+ List<Organization> organizations = new ArrayList<>();
+
+ for (int i = 0; i < ORG_CNT; i++) {
+ int id = idGen++;
+
+ Organization org = new Organization(id, "Org" + id);
+
+ organizations.add(org);
+
+ pCache.put(org.id, org);
+
+ insertInDb(org);
+ }
+
+ // Adresses.
+ List<Address> addreses = new ArrayList<>();
+
+ for (int i = 0; i < ADDR_CNT; i++) {
+ int id = idGen++;
+
+ Address addr = new Address(id, "Addr" + id);
+
+ addreses.add(addr);
+
+ rCache.put(addr.id, addr);
+
+ insertInDb(addr);
+ }
+
+ // Persons.
+ List<Person> persons = new ArrayList<>();
+
+ for (int i = 0; i < PERS_CNT; i++) {
+ int id = idGen++;
+
+ Person person = new Person(id, organizations.get(i % organizations.size()),
+ "name" + id, "lastName" + id, id * 100.0, addreses.get(i % addreses.size()));
+
+ // Add a Person without lastname.
+ if (id == organizations.size() + 1)
+ person.lastName = null;
+
+ persons.add(person);
+
+ pCache.put(person.key(), person);
+
+ insertInDb(person);
+ }
+
+ // Products.
+ List<Product> products = new ArrayList<>();
+
+ for (int i = 0; i < PROD_CNT; i++) {
+ int id = idGen++;
+
+ Product product = new Product(id, "Product" + id, id*1000);
+
+ products.add(product);
+
+ rCache.put(product.id, product);
+
+ insertInDb(product);
+ }
+
+ // Purchases.
+ for (int i = 0; i < PURCH_CNT; i++) {
+ int id = idGen++;
+
+ Person person = persons.get(i % persons.size());
+
+ Purchase purchase = new Purchase(id, products.get(i % products.size()), person.orgId, person);
+
+ pCache.put(purchase.key(), purchase);
+
+ insertInDb(purchase);
+ }
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void checkAllDataEquals() throws Exception {
+ compareQueryRes0("select _key, _val, id, name from \"part\".Organization");
+
+ compareQueryRes0("select _key, _val, id, firstName, lastName, orgId, salary from \"part\".Person");
+
+ compareQueryRes0("select _key, _val, id, personId, productId, organizationId from \"part\".Purchase");
+
+ compareQueryRes0(rCache, "select _key, _val, id, name, price from \"repl\".Product");
+ }
+
+ /**
+ * *
+ * @throws Exception
+ */
+ public void testAllExamples() throws Exception {
+// compareQueryRes0("select ? limit ? offset ?");
+
+// compareQueryRes0("select cool1()");
+// compareQueryRes0("select cool1() z");
+//
+// compareQueryRes0("select b,a from table0('aaa', 100)");
+// compareQueryRes0("select * from table0('aaa', 100)");
+// compareQueryRes0("select * from table0('aaa', 100) t0");
+// compareQueryRes0("select x.a, y.b from table0('aaa', 100) x natural join table0('bbb', 100) y");
+// compareQueryRes0("select * from table0('aaa', 100) x join table0('bbb', 100) y on x.a=y.a and x.b = 'bbb'");
+// compareQueryRes0("select * from table0('aaa', 100) x left join table0('bbb', 100) y on x.a=y.a and x.b = 'bbb'");
+// compareQueryRes0("select * from table0('aaa', 100) x left join table0('bbb', 100) y on x.a=y.a where x.b = 'bbb'");
+// compareQueryRes0("select * from table0('aaa', 100) x left join table0('bbb', 100) y where x.b = 'bbb'");
+
+ final String addStreet = "Addr" + ORG_CNT + 1;
+
+ List<List<?>> res = compareQueryRes0("select avg(old) from \"part\".Person left join \"repl\".Address " +
+ " on Person.addrId = Address.id where lower(Address.street) = lower(?)", addStreet);
+
+ assertNotSame(0, res);
+
+ compareQueryRes0("select avg(old) from \"part\".Person join \"repl\".Address on Person.addrId = Address.id " +
+ "where lower(Address.street) = lower(?)", addStreet);
+
+ compareQueryRes0("select avg(old) from \"part\".Person left join \"repl\".Address where Person.addrId = Address.id " +
+ "and lower(Address.street) = lower(?)", addStreet);
+
+ compareQueryRes0("select avg(old) from \"part\".Person, \"repl\".Address where Person.addrId = Address.id " +
+ "and lower(Address.street) = lower(?)", addStreet);
+
+ compareQueryRes0("select firstName, date from \"part\".Person");
+ compareQueryRes0("select distinct firstName, date from \"part\".Person");
+
+ final String star = " _key, _val, id, firstName, lastName, orgId, salary, addrId, old, date ";
+
+ compareQueryRes0("select " + star + " from \"part\".Person p");
+ compareQueryRes0("select " + star + " from \"part\".Person");
+ compareQueryRes0("select distinct " + star + " from \"part\".Person");
+ compareQueryRes0("select p.firstName, date from \"part\".Person p");
+
+ compareQueryRes0("select p._key, p._val, p.id, p.firstName, p.lastName, p.orgId, p.salary, p.addrId, p.old, " +
+ " p.date, a._key, a._val, a.id, a.street" +
+ " from \"part\".Person p, \"repl\".Address a");
+// compareQueryRes0("select p.* from \"part\".Person p, \"repl\".Address a");
+// compareQueryRes0("select person.* from \"part\".Person, \"repl\".Address a");
+// compareQueryRes0("select p.*, street from \"part\".Person p, \"repl\".Address a");
+ compareQueryRes0("select p.firstName, a.street from \"part\".Person p, \"repl\".Address a");
+ compareQueryRes0("select distinct p.firstName, a.street from \"part\".Person p, \"repl\".Address a");
+ compareQueryRes0("select distinct firstName, street from \"part\".Person, \"repl\".Address group by firstName, street ");
+ compareQueryRes0("select distinct firstName, street from \"part\".Person, \"repl\".Address");
+ // TODO uncomment and investigate (Rows count has to be equal.: Expected :2500, Actual :900)
+// compareQueryRes0("select p1.firstName, a2.street from \"part\".Person p1, \"repl\".Address a1, \"part\".Person p2, \"repl\".Address a2");
+
+ //TODO look at it (org.h2.jdbc.JdbcSQLException: Feature not supported: "VARCHAR +" // at H2)
+// compareQueryRes0("select p.firstName n, a.street s from \"part\".Person p, \"repl\".Address a");
+ compareQueryRes0("select p.firstName, 1 as i, 'aaa' s from \"part\".Person p");
+
+// compareQueryRes0("select p.firstName + 'a', 1 * 3 as i, 'aaa' s, -p.old, -p.old as old from \"part\".Person p");
+// compareQueryRes0("select p.firstName || 'a' + p.firstName, (p.old * 3) % p.old - p.old / p.old, p.firstName = 'aaa', " +
+// " p.firstName is p.firstName, 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 \"part\".Person p");
+
+ compareQueryRes0("select p.firstName from \"part\".Person p where firstName <> 'ivan'");
+ compareQueryRes0("select p.firstName from \"part\".Person p where firstName like 'i%'");
+ compareQueryRes0("select p.firstName from \"part\".Person p where firstName regexp 'i%'");
+ compareQueryRes0("select p.firstName from \"part\".Person p, \"repl\".Address a where p.firstName <> 'ivan' and a.id > 10 or not (a.id = 100)");
+
+ compareQueryRes0("select case p.firstName when 'a' then 1 when 'a' then 2 end as a from \"part\".Person p");
+ compareQueryRes0("select case p.firstName when 'a' then 1 when 'a' then 2 else -1 end as a from \"part\".Person p");
+
+ compareQueryRes0("select abs(p.old) from \"part\".Person p");
+ compareQueryRes0("select cast(p.old as numeric(10, 2)) from \"part\".Person p");
+ compareQueryRes0("select cast(p.old as numeric(10, 2)) z from \"part\".Person p");
+ compareQueryRes0("select cast(p.old as numeric(10, 2)) as z from \"part\".Person p");
+
+ // TODO alanyse
+// compareQueryRes0("select " + star + " from \"part\".Person p where p.firstName in ('a', 'b', '_' + RAND())"); // test ConditionIn
+ compareQueryRes0("select " + star + " from \"part\".Person p where p.firstName in ('a', 'b', 'c')"); // test ConditionInConstantSet
+ compareQueryRes0("select " + star + " from \"part\".Person p where p.firstName in (select a.street from \"repl\".Address a)"); // test ConditionInConstantSet
+
+ compareQueryRes0("select (select a.street from \"repl\".Address a where a.id = p.addrId) from \"part\".Person p"); // test ConditionInConstantSet
+
+ compareQueryRes0("select p.firstName, ? from \"part\".Person p where firstName regexp ? and p.old < ?", 10, "Iv*n", 40);
+
+ compareQueryRes0("select count(*) as a from \"part\".Person");
+ compareQueryRes0("select count(*) as a, count(p.*), count(p.firstName) from \"part\".Person p");
+ compareQueryRes0("select count(distinct p.firstName) from \"part\".Person p");
+
+ compareQueryRes0("select p.firstName, avg(p.old), max(p.old) from \"part\".Person p group by p.firstName");
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName");
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by n");
+
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.addrId, p.firstName");
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName, p.addrId");
+ compareQueryRes0("select p.firstName n, max(p.old) + min(p.old) / count(distinct p.old) from \"part\".Person p group by p.firstName");
+ compareQueryRes0("select p.firstName n, max(p.old) maxOld, min(p.old) minOld from \"part\".Person p group by p.firstName having maxOld > 10 and min(p.old) < 1");
+
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by n");
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName");
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName, m");
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName, max(p.old) desc");
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName nulls first");
+ compareQueryRes0("select p.firstName n, avg(p.old) a, max(p.old) m from \"part\".Person p group by p.firstName order by p.firstName nulls last");
+ compareQueryRes0("select p.firstName n from \"part\".Person p order by p.old + 10");
+ compareQueryRes0("select p.firstName n from \"part\".Person p order by p.old + 10, p.firstName");
+ compareQueryRes0("select p.firstName n from \"part\".Person p order by p.old + 10, p.firstName desc");
+
+ compareQueryRes0("select p.firstName n from \"part\".Person p, (select a.street from \"repl\".Address a where a.street is not null) ");
+ compareQueryRes0("select street from \"part\".Person p, (select a.street from \"repl\".Address a where a.street is not null) ");
+ compareQueryRes0("select addr.street from \"part\".Person p, (select a.street from \"repl\".Address a where a.street is not null) addr");
+
+ compareQueryRes0("select p.firstName n from \"part\".Person p order by p.old + 10");
+
+ compareQueryRes0("select 'foo' as bar union select 'foo' as bar");
+ compareQueryRes0("select 'foo' as bar union all select 'foo' as bar");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testParamSubstitution() throws Exception {
+ compareQueryRes0("select ? from \"part\".Person", "Some arg");
+ }
+
+ /**
+ * @throws SQLException If failed.
+ */
+ public void testAggregateOrderBy() throws SQLException {
+ compareOrderedQueryRes0("select firstName name, count(*) cnt from \"part\".Person group by name order by cnt desc");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testNullParamSubstitution() throws Exception {
+ List<List<?>> rs1 = compareQueryRes0("select id from \"part\".Person where lastname is ?", null);
+
+ // Ensure we find something.
+ assertNotSame(0, rs1.size());
+ }
+
+ /**
+ *
+ */
+ public void testUnion() throws SQLException {
+ String base = "select _val v from \"part\".Person";
+
+ compareQueryRes0(base + " union all " + base);
+ compareQueryRes0(base + " union " + base);
+
+ base = "select firstName||lastName name, salary from \"part\".Person";
+
+ assertEquals(10, compareOrderedQueryRes0(base + " union all " + base + " order by salary desc").size());
+ assertEquals(5, compareOrderedQueryRes0(base + " union " + base + " order by salary desc").size());
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testEmptyResult() throws Exception {
+ compareQueryRes0("select id from \"part\".Person where 0 = 1");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testSqlQueryWithAggregation() throws Exception {
+ compareQueryRes0("select avg(salary) from \"part\".Person, \"part\".Organization where Person.orgId = Organization.id and "
+ + "lower(Organization.name) = lower(?)", "Org1");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testSqlFieldsQuery() throws Exception {
+ compareQueryRes0("select concat(firstName, ' ', lastName) from \"part\".Person");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testSqlFieldsQueryWithJoin() throws Exception {
+ compareQueryRes0("select concat(firstName, ' ', lastName), "
+ + "Organization.name from \"part\".Person, \"part\".Organization where "
+ + "Person.orgId = Organization.id");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testOrdered() throws Exception {
+ compareOrderedQueryRes0("select firstName, lastName" +
+ " from \"part\".Person" +
+ " order by lastName, firstName"
+ );
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testSimpleJoin() throws Exception {
+ // Have expected results.
+ compareQueryRes0("select id, firstName, lastName" +
+ " from \"part\".Person" +
+ " where Person.id = ?", 3);
+
+ // Ignite cache return 0 results...
+ compareQueryRes0("select pe.firstName" +
+ " from \"part\".Person pe join \"part\".Purchase pu on pe.id = pu.personId " +
+ " where pe.id = ?", 3);
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testSimpleReplicatedSelect() throws Exception {
+ compareQueryRes0(rCache, "select id, name from \"repl\".Product");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void _testCrossCache() throws Exception {
+ //TODO Investigate (should be 20 results instead of 0).
+ compareQueryRes0("select firstName, lastName" +
+ " from \"part\".Person, \"part\".Purchase" +
+ " where Person.id = Purchase.personId");
+
+ //TODO Investigate.
+ compareQueryRes0("select concat(firstName, ' ', lastName), Product.name " +
+ " from \"part\".Person, \"part\".Purchase, \"repl\".Product " +
+ " where Person.id = Purchase.personId and Purchase.productId = Product.id" +
+ " group by Product.id");
+
+ //TODO Investigate.
+ compareQueryRes0("select concat(firstName, ' ', lastName), count (Product.id) " +
+ " from \"part\".Person, \"part\".Purchase, \"repl\".Product " +
+ " where Person.id = Purchase.personId and Purchase.productId = Product.id" +
+ " group by Product.id");
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void initializeH2Schema() throws SQLException {
+ Statement st = conn.createStatement();
+
+ st.execute("CREATE SCHEMA \"part\"");
+ st.execute("CREATE SCHEMA \"repl\"");
+
+ st.execute("create table \"part\".ORGANIZATION" +
+ " (_key int not null," +
+ " _val other not null," +
+ " id int unique," +
+ " name varchar(255))");
+
+ st.execute("create table \"part\".PERSON" +
+ " (_key other not null ," +
+ " _val other not null ," +
+ " id int unique, " +
+ " firstName varchar(255), " +
+ " lastName varchar(255)," +
+ " orgId int not null," +
+ " salary double," +
+ " addrId int," +
+ " old int," +
+ " date Date )");
+
+ st.execute("create table \"repl\".PRODUCT" +
+ " (_key int not null ," +
+ " _val other not null ," +
+ " id int unique, " +
+ " name varchar(255), " +
+ " price int)");
+
+ st.execute("create table \"part\".PURCHASE" +
+ " (_key other not null ," +
+ " _val other not null ," +
+ " id int unique, " +
+ " personId int, " +
+ " organizationId int, " +
+ " productId int)");
+
+ st.execute("create table \"repl\".ADDRESS" +
+ " (_key int not null ," +
+ " _val other not null ," +
+ " id int unique, " +
+ " street varchar(255))");
+
+ conn.commit();
+ }
+
+ /**
+ * Insert {@link Organization} at h2 database.
+ *
+ * @param org Organization.
+ * @throws SQLException If exception.
+ */
+ private void insertInDb(Organization org) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"part\".ORGANIZATION (_key, _val, id, name) values(?, ?, ?, ?)")) {
+ st.setObject(1, org.id);
+ st.setObject(2, org);
+ st.setObject(3, org.id);
+ st.setObject(4, org.name);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Insert {@link Person} at h2 database.
+ *
+ * @param p Person.
+ * @throws SQLException If exception.
+ */
+ private void insertInDb(Person p) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement("insert into \"part\".PERSON " +
+ "(_key, _val, id, firstName, lastName, orgId, salary, addrId, old, date) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
+ st.setObject(1, p.key());
+ st.setObject(2, p);
+ st.setObject(3, p.id);
+ st.setObject(4, p.firstName);
+ st.setObject(5, p.lastName);
+ st.setObject(6, p.orgId);
+ st.setObject(7, p.salary);
+ st.setObject(8, p.addrId);
+ st.setObject(9, p.old);
+ st.setObject(10, p.date);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Insert {@link Product} at h2 database.
+ *
+ * @param p Product.
+ * @throws SQLException If exception.
+ */
+ private void insertInDb(Product p) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"repl\".PRODUCT (_key, _val, id, name, price) values(?, ?, ?, ?, ?)")) {
+ st.setObject(1, p.id);
+ st.setObject(2, p);
+ st.setObject(3, p.id);
+ st.setObject(4, p.name);
+ st.setObject(5, p.price);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Insert {@link Purchase} at h2 database.
+ *
+ * @param p Purchase.
+ * @throws SQLException If exception.
+ */
+ private void insertInDb(Purchase p) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"part\".PURCHASE (_key, _val, id, personId, productId, organizationId) values(?, ?, ?, ?, ?, ?)")) {
+ st.setObject(1, p.key());
+ st.setObject(2, p);
+ st.setObject(3, p.id);
+ st.setObject(4, p.personId);
+ st.setObject(5, p.productId);
+ st.setObject(6, p.organizationId);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Insert {@link Address} at h2 database.
+ *
+ * @param a Address.
+ * @throws SQLException If exception.
+ */
+ private void insertInDb(Address a) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"repl\".ADDRESS (_key, _val, id, street) values(?, ?, ?, ?)")) {
+ st.setObject(1, a.id);
+ st.setObject(2, a);
+ st.setObject(3, a.id);
+ st.setObject(4, a.street);
+
+ st.executeUpdate();
+ }
+ }
+
+ @QuerySqlFunction
+ public static int cool1() {
+ return 1;
+ }
+
+ @QuerySqlFunction
+ public static ResultSet table0(Connection c, String a, int b) throws SQLException {
+ return c.createStatement().executeQuery("select '" + a + "' as a, " + b + " as b");
+ }
+
+ /**
+ * Person class. Stored at partitioned cache.
+ */
+ private static class Person implements Serializable {
+ /** Person ID (indexed). */
+ @QuerySqlField(index = true)
+ private int id;
+
+ /** Organization ID (indexed). */
+ @QuerySqlField(index = true)
+ private int orgId;
+
+ /** First name (not-indexed). */
+ @QuerySqlField
+ private String firstName;
+
+ /** Last name (not indexed). */
+ @QuerySqlField
+ private String lastName;
+
+ /** Salary (indexed). */
+ @QuerySqlField(index = true)
+ private double salary;
+
+ /** Address Id (indexed). */
+ @QuerySqlField(index = true)
+ private int addrId;
+
+ /** Date. */
+ @QuerySqlField(index = true)
+ public Date date = new Date(System.currentTimeMillis());
+
+ /** Old. */
+ @QuerySqlField(index = true)
+ public int old = 17;
+
+
+ /**
+ * Constructs person record.
+ *
+ * @param org Organization.
+ * @param firstName First name.
+ * @param lastName Last name.
+ * @param salary Salary.
+ */
+ Person(int id, Organization org, String firstName, String lastName, double salary, Address addr) {
+ this.id = id;
+ this.firstName = firstName;
+ this.lastName = lastName;
+ this.salary = salary;
+ orgId = org.id;
+ addrId = addr.id;
+ }
+
+ /**
+ * @return Custom affinity key to guarantee that person is always collocated with organization.
+ */
+ public AffinityKey<Integer> key() {
+ return new AffinityKey<>(id, orgId);
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof Person && id == ((Person)o).id;
+
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public String toString() {
+ return "Person [firstName=" + firstName +
+ ", lastName=" + lastName +
+ ", id=" + id +
+ ", orgId=" + orgId +
+ ", salary=" + salary +
+ ", addrId=" + addrId + ']';
+ }
+ }
+
+ /**
+ * Organization class. Stored at partitioned cache.
+ */
+ private static class Organization implements Serializable {
+ /** Organization ID (indexed). */
+ @QuerySqlField(index = true)
+ private int id;
+
+ /** Organization name (indexed). */
+ @QuerySqlField(index = true)
+ private String name;
+
+ /**
+ * Create Organization.
+ *
+ * @param id Organization ID.
+ * @param name Organization name.
+ */
+ Organization(int id, String name) {
+ this.id = id;
+ this.name = name;
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof Organization && id == ((Organization)o).id;
+
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public String toString() {
+ return "Organization [id=" + id + ", name=" + name + ']';
+ }
+ }
+
+ /**
+ * Product class. Stored at replicated cache.
+ */
+ private static class Product implements Serializable {
+ /** Primary key. */
+ @QuerySqlField(index = true)
+ private int id;
+
+ /** Product name. */
+ @QuerySqlField
+ private String name;
+
+ /** Product price */
+ @QuerySqlField
+ private int price;
+
+ /**
+ * Create Product.
+ *
+ * @param id Product ID.
+ * @param name Product name.
+ * @param price Product price.
+ */
+ Product(int id, String name, int price) {
+ this.id = id;
+ this.name = name;
+ this.price = price;
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof Product && id == ((Product)o).id;
+
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public String toString() {
+ return "Product [id=" + id + ", name=" + name + ", price=" + price + ']';
+ }
+ }
+
+ /**
+ * Purchase class. Stored at partitioned cache.
+ */
+ private static class Purchase implements Serializable {
+ /** Primary key. */
+ @QuerySqlField(index = true)
+ private int id;
+
+ /** Product ID. */
+ @QuerySqlField
+ private int productId;
+
+ /** Person ID. */
+ @QuerySqlField
+ private int personId;
+
+ /** Organization id. */
+ @QuerySqlField
+ private int organizationId;
+
+ /**
+ * Create Purchase.
+ * @param id Purchase ID.
+ * @param product Purchase product.
+ * @param organizationId Organization Id.
+ * @param person Purchase person.
+ */
+ Purchase(int id, Product product, int organizationId, Person person) {
+ this.id = id;
+ productId = product.id;
+ personId = person.id;
+ this.organizationId = organizationId;
+ }
+
+ /**
+ * @return Custom affinity key to guarantee that purchase is always collocated with person.
+ */
+ public AffinityKey<Integer> key() {
+ return new AffinityKey<>(id, organizationId);
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof Purchase && id == ((Purchase)o).id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public String toString() {
+ return "Purchase [id=" + id + ", productId=" + productId + ", personId=" + personId + ']';
+ }
+ }
+
+ /**
+ * Address class. Stored at replicated cache.
+ */
+ private static class Address implements Serializable {
+ @QuerySqlField(index = true)
+ private int id;
+
+ @QuerySqlField(index = true)
+ private String street;
+
+ Address(int id, String street) {
+ this.id = id;
+ this.street = street;
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof Address && id == ((Address)o).id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public String toString() {
+ return "Address [id=" + id + ", street=" + street + ']';
+ }
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fe743909/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/H2CompareBigQueryTest.java
----------------------------------------------------------------------
diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/H2CompareBigQueryTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/H2CompareBigQueryTest.java
new file mode 100644
index 0000000..c5a2cad
--- /dev/null
+++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/H2CompareBigQueryTest.java
@@ -0,0 +1,707 @@
+/*
+ * 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.apache.ignite.internal.processors.query.h2.sql;
+
+import org.apache.ignite.cache.*;
+import org.apache.ignite.cache.affinity.*;
+import org.apache.ignite.cache.query.annotations.*;
+import org.apache.ignite.configuration.*;
+
+import java.io.*;
+import java.sql.Date;
+import java.sql.*;
+import java.util.*;
+import java.util.concurrent.atomic.*;
+
+/**
+ * Executes one big query (and subqueries of the big query) to compare query results from h2 database instance and
+ * mixed ignite caches (replicated and partitioned) which have the same data models and data content.
+ *
+ *
+ * <pre>
+ *
+ * -------------------------------------> rootOrderId (virtual) <--------------------------
+ * | | |
+ * | | |
+ * | --------------------- | |
+ * | ------------------ --part.ReplaceOrder-- | |
+ * | --part.CustOrder-- --------------------- | |
+ * | ------------------ - id PK - | |
+ * | - orderId PK - <-- <---> - orderId - | |
+ * -- - rootOrderId - | - rootOrderId - ----------- |
+ * - origOrderId - | - refOrderId - // = origOrderId |
+ * - date - | - date - |
+ * - alias - | - alias - |
+ * - archSeq - | - archSeq - ------------------- |
+ * ------------------ | --------------------- ----repl.Exec------ |
+ * | ------------------- |
+ * ----------------- | - rootOrderId PK - ----
+ * ---part.Cancel--- | - date -
+ * ----------------- | --------------------- - execShares int -
+ * - id PK - | --part.OrderParams--- - price int -
+ * - refOrderId - ---| --------------------- - lastMkt int -
+ * - date - | - id PK - -------------------
+ * ----------------- ------- - orderId -
+ * - date -
+ * - parentAlgo -
+ * ---------------------
+ * </pre>
+ *
+ */
+public class H2CompareBigQueryTest extends AbstractH2CompareQueryTest {
+ /** Root order count. */
+ private static final int ROOT_ORDER_CNT = 1000;
+
+ /** Dates count. */
+ private static final int DATES_CNT = 5;
+
+ /** Full the big query. */
+ private String bigQry = getBigQry();
+
+ /**
+ * Extracts the big query from file.
+ *
+ * @return Big query.
+ */
+ private String getBigQry() {
+ String res = "";
+
+ Reader isr = new InputStreamReader(getClass().getResourceAsStream("bigQuery.sql"));
+
+ try(BufferedReader reader = new BufferedReader(isr)) {
+ for(String line; (line = reader.readLine()) != null; )
+ if (!line.startsWith("--")) // Skip commented lines.
+ res += line + '\n';
+ }
+ catch (Throwable e) {
+ e.printStackTrace();
+
+ fail();
+ }
+
+ return res;
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void setIndexedTypes(CacheConfiguration<?, ?> cc, CacheMode mode) {
+ if (mode == CacheMode.PARTITIONED)
+ cc.setIndexedTypes(
+ Integer.class, CustOrder.class,
+ AffinityKey.class, ReplaceOrder.class,
+ AffinityKey.class, OrderParams.class,
+ AffinityKey.class, Cancel.class
+ );
+ else if (mode == CacheMode.REPLICATED)
+ cc.setIndexedTypes(
+ Integer.class, Exec.class
+ );
+ else
+ throw new IllegalStateException("mode: " + mode);
+ }
+
+ /** {@inheritDoc} */
+ @SuppressWarnings("unchecked")
+ @Override protected void initCacheAndDbData() throws SQLException {
+ final AtomicInteger idGen = new AtomicInteger();
+
+ final Iterable<Integer> rootOrderIds = new ArrayList<Integer>(){{
+ for (int i = 0; i < ROOT_ORDER_CNT; i++)
+ add(idGen.incrementAndGet());
+ }};
+
+ final Date curDate = new Date(new java.util.Date().getTime());
+
+ final List<Date> dates = new ArrayList<Date>(){{
+ for (int i = 0; i < DATES_CNT; i++)
+ add(new Date(curDate.getTime() - i * 24 * 60 * 60 * 1000)); // Minus i days.
+ }};
+
+ final Iterable<CustOrder> orders = new ArrayList<CustOrder>(){{
+ for (int rootOrderId : rootOrderIds) {
+ // Generate 1 - 5 orders for 1 root order.
+ for (int i = 0; i < rootOrderId % 5; i++) {
+ int orderId = idGen.incrementAndGet();
+
+ CustOrder order = new CustOrder(orderId, rootOrderId, dates.get(orderId % dates.size()) ,
+ orderId % 2 == 0 ? "CUSTOM" : "OTHER", orderId);
+
+ add(order);
+
+ pCache.put(order.orderId, order);
+
+ insertInDb(order);
+ }
+ }
+ }};
+
+ final Collection<OrderParams> params = new ArrayList<OrderParams>(){{
+ for (CustOrder o : orders) {
+ OrderParams op = new OrderParams(idGen.incrementAndGet(), o.orderId, o.date,
+ o.orderId % 2 == 0 ? "Algo 1" : "Algo 2");
+
+ add(op);
+
+ pCache.put(op.key(), op);
+
+ insertInDb(op);
+ }
+ }};
+
+ final Collection<ReplaceOrder> replaces = new ArrayList<ReplaceOrder>(){{
+ for (CustOrder o : orders) {
+ if (o.orderId % 7 == 0) {
+ ReplaceOrder replace = new ReplaceOrder(idGen.incrementAndGet(), o.orderId, o.rootOrderId, o.alias,
+ new Date(o.date.getTime() + 12 * 60 * 60 * 1000), o.orderId); // Plus a half of day.
+
+ add(replace);
+
+ pCache.put(replace.key(), replace);
+
+ insertInDb(replace);
+ }
+ }
+ }};
+
+ final Collection<Cancel> cancels = new ArrayList<Cancel>(){{
+ for (CustOrder o : orders) {
+ if (o.orderId % 9 == 0) {
+ Cancel c = new Cancel(idGen.incrementAndGet(), o.orderId,
+ new Date(o.date.getTime() + 12 * 60 * 60 * 1000));// Plus a half of day.
+
+ add(c);
+
+ pCache.put(c.key(), c);
+
+ insertInDb(c);
+ }
+ }
+ }};
+
+ final Collection<Exec> execs = new ArrayList<Exec>(){{
+ for (int rootOrderId : rootOrderIds) {
+ int execShares = 10000 + rootOrderId;
+ int price = 1000 + rootOrderId;
+ int latsMkt = 3000 + rootOrderId;
+
+ Exec exec = new Exec(rootOrderId, dates.get(rootOrderId % dates.size()), execShares, price, latsMkt);
+
+ add(exec);
+
+ rCache.put(exec.rootOrderId, exec);
+
+ insertInDb(exec);
+ }
+ }};
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ @Override protected void checkAllDataEquals() throws Exception {
+ compareQueryRes0("select _key, _val, date, orderId, rootOrderId, alias, archSeq, origOrderId " +
+ "from \"part\".CustOrder");
+ compareQueryRes0("select _key, _val, id, date, orderId, rootOrderId, alias, archSeq, refOrderId " +
+ "from \"part\".ReplaceOrder");
+ compareQueryRes0("select _key, _val, id, date, orderId, parentAlgo from \"part\".OrderParams\n");
+ compareQueryRes0("select _key, _val, id, date, refOrderId from \"part\".Cancel\n");
+ compareQueryRes0(rCache, "select _key, _val, date, rootOrderId, execShares, price, lastMkt from \"repl\".Exec\n");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testUnionAllOrders() throws Exception {
+ compareQueryRes0(
+ " select date, orderId, rootOrderId " +
+ " from \"part\".CustOrder where alias='CUSTOM'");
+
+ compareQueryRes0(
+ " select date, orderId, rootOrderId " +
+ " from \"part\".ReplaceOrder where alias='CUSTOM'");
+
+ compareQueryRes0(
+ " select 10" +
+
+ " union all" +
+
+ " select 20");
+
+ compareQueryRes0(
+ " select date, orderId, rootOrderId " +
+ " from \"part\".CustOrder where alias='CUSTOM'" +
+
+ " union all" +
+
+ " select date, orderId, rootOrderId " +
+ " from \"part\".ReplaceOrder where alias='CUSTOM'");
+ }
+
+ /**
+ * @throws Exception If failed.
+ */
+ public void testBigQuery() throws Exception {
+ List<List<?>> res = compareQueryRes0(bigQry);
+
+ assertTrue(!res.isEmpty()); // Ensure we set good testing data at database.
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void initializeH2Schema() throws SQLException {
+ Statement st = conn.createStatement();
+
+ st.execute("CREATE SCHEMA \"part\"");
+ st.execute("CREATE SCHEMA \"repl\"");
+
+ st.execute("create table \"part\".CustOrder" +
+ " (" +
+ " _key int not null," +
+ " _val other not null," +
+ " orderId int unique," +
+ " rootOrderId int," +
+ " origOrderId int," +
+ " archSeq int," +
+ " date Date, " +
+ " alias varchar(255)" +
+ " )");
+
+ st.execute("create table \"part\".ReplaceOrder" +
+ " (" +
+ " _key other not null," +
+ " _val other not null," +
+ " id int unique," +
+ " orderId int ," +
+ " rootOrderId int," +
+ " refOrderId int," +
+ " archSeq int," +
+ " date Date, " +
+ " alias varchar(255)" +
+ " )");
+
+ st.execute("create table \"part\".OrderParams" +
+ " (" +
+ " _key other not null," +
+ " _val other not null," +
+ " id int unique," +
+ " orderId int ," +
+ " date Date, " +
+ " parentAlgo varchar(255)" +
+ " )");
+
+ st.execute("create table \"part\".Cancel" +
+ " (" +
+ " _key other not null," +
+ " _val other not null," +
+ " id int unique," +
+ " date Date, " +
+ " refOrderId int" +
+ " )");
+
+ st.execute("create table \"repl\".Exec" +
+ " (" +
+ " _key int not null," +
+ " _val other not null," +
+ " rootOrderId int unique," +
+ " date Date, " +
+ " execShares int," +
+ " price int," +
+ " lastMkt int" +
+ " )");
+
+ conn.commit();
+ }
+
+ /**
+ * Insert {@link CustOrder} at h2 database.
+ *
+ * @param o CustOrder.
+ */
+ private void insertInDb(CustOrder o) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"part\".CustOrder (_key, _val, orderId, rootOrderId, date, alias, archSeq, origOrderId) " +
+ "values(?, ?, ?, ?, ?, ?, ?, ?)")) {
+ int i = 0;
+
+ st.setObject(++i, o.orderId);
+ st.setObject(++i, o);
+ st.setObject(++i, o.orderId);
+ st.setObject(++i, o.rootOrderId);
+ st.setObject(++i, o.date);
+ st.setObject(++i, o.alias);
+ st.setObject(++i, o.archSeq);
+ st.setObject(++i, o.origOrderId);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Insert {@link ReplaceOrder} at h2 database.
+ *
+ * @param o ReplaceOrder.
+ */
+ private void insertInDb(ReplaceOrder o) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"part\".ReplaceOrder (_key, _val, id, orderId, rootOrderId, date, alias, archSeq, refOrderId) " +
+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
+ int i = 0;
+
+ st.setObject(++i, o.key());
+ st.setObject(++i, o);
+ st.setObject(++i, o.id);
+ st.setObject(++i, o.orderId);
+ st.setObject(++i, o.rootOrderId);
+ st.setObject(++i, o.date);
+ st.setObject(++i, o.alias);
+ st.setObject(++i, o.archSeq);
+ st.setObject(++i, o.refOrderId);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Insert {@link OrderParams} at h2 database.
+ *
+ * @param o OrderParams.
+ */
+ private void insertInDb(OrderParams o) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"part\".OrderParams (_key, _val, id, date, orderId, parentAlgo) values(?, ?, ?, ?, ?, ?)")) {
+ int i = 0;
+
+ st.setObject(++i, o.key());
+ st.setObject(++i, o);
+ st.setObject(++i, o.id);
+ st.setObject(++i, o.date);
+ st.setObject(++i, o.orderId);
+ st.setObject(++i, o.parentAlgo);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Insert {@link Cancel} at h2 database.
+ *
+ * @param o Cancel.
+ */
+ private void insertInDb(Cancel o) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"part\".Cancel (_key, _val, id, date, refOrderId) values(?, ?, ?, ?, ?)")) {
+ int i = 0;
+
+ st.setObject(++i, o.key());
+ st.setObject(++i, o);
+ st.setObject(++i, o.id);
+ st.setObject(++i, o.date);
+ st.setObject(++i, o.refOrderId);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Insert {@link Exec} at h2 database.
+ *
+ * @param o Execution.
+ */
+ private void insertInDb(Exec o) throws SQLException {
+ try(PreparedStatement st = conn.prepareStatement(
+ "insert into \"repl\".Exec (_key, _val, date, rootOrderId, execShares, price, lastMkt) " +
+ "values(?, ?, ?, ?, ?, ?, ?)")) {
+ int i = 0;
+
+ st.setObject(++i, o.rootOrderId);
+ st.setObject(++i, o);
+ st.setObject(++i, o.date);
+ st.setObject(++i, o.rootOrderId);
+ st.setObject(++i, o.execShares);
+ st.setObject(++i, o.price);
+ st.setObject(++i, o.lastMkt);
+
+ st.executeUpdate();
+ }
+ }
+
+ /**
+ * Custom Order.
+ */
+ static class CustOrder implements Serializable {
+ /** Primary key. */
+ @QuerySqlField(index = true)
+ private int orderId;
+
+ /** Root order ID*/
+ @QuerySqlField
+ private int rootOrderId;
+
+ /** Orig order ID*/
+ @QuerySqlField
+ private int origOrderId;
+
+ /** Date */
+ @QuerySqlField
+ private Date date ;
+
+ /** */
+ @QuerySqlField
+ private String alias = "CUSTOM";
+
+ /** */
+ @QuerySqlField
+ private int archSeq = 11; // TODO: use it.
+
+ /**
+ * @param orderId Order id.
+ * @param rootOrderId Root order id.
+ * @param date Date.
+ * @param alias Alias.
+ * @param origOrderId Orig order id.
+ */
+ CustOrder(int orderId, int rootOrderId, Date date, String alias, int origOrderId) {
+ this.orderId = orderId;
+ this.rootOrderId = rootOrderId;
+ this.origOrderId = origOrderId;
+ this.date = date;
+ this.alias = alias;
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof CustOrder && orderId == ((CustOrder)o).orderId;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return orderId;
+ }
+ }
+
+ /**
+ * Replace Order.
+ */
+ static class ReplaceOrder implements Serializable {
+ /** Primary key. */
+ @QuerySqlField(index = true)
+ private int id;
+
+ /** Order id. */
+ @QuerySqlField(index = true)
+ private int orderId;
+
+ /** Root order ID*/
+ @QuerySqlField
+ private int rootOrderId;
+
+ /** Ref order ID*/
+ @QuerySqlField
+ private int refOrderId;
+
+ /** Date */
+ @QuerySqlField
+ private Date date ;
+
+ /** */
+ @QuerySqlField
+ private String alias = "CUSTOM";
+
+ /** */
+ @QuerySqlField
+ private int archSeq = 111; // TODO: use it.
+
+ /**
+ * @param id Id.
+ * @param orderId Order id.
+ * @param rootOrderId Root order id.
+ * @param alias Alias.
+ * @param date Date.
+ * @param refOrderId Reference order id.
+ */
+ ReplaceOrder(int id, int orderId, int rootOrderId, String alias, Date date, int refOrderId) {
+ this.id = id;
+ this.orderId = orderId;
+ this.rootOrderId = rootOrderId;
+ this.refOrderId = refOrderId;
+ this.date = date;
+ this.alias = alias;
+ }
+
+ /**
+ * @return Afinity key.
+ */
+ public AffinityKey<Integer> key() {
+ return new AffinityKey<>(id, orderId);
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof ReplaceOrder && id == ((ReplaceOrder)o).id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+ }
+
+ /**
+ * Order params.
+ */
+ static class OrderParams implements Serializable {
+ /** Primary key. */
+ @QuerySqlField(index = true)
+ private int id;
+
+ /** Order id. */
+ @QuerySqlField(index = true)
+ private int orderId;
+
+ /** Date */
+ @QuerySqlField
+ private Date date ;
+
+ /** */
+ @QuerySqlField
+ private String parentAlgo = "CUSTOM_ALGO";
+
+ /**
+ * @param id Id.
+ * @param orderId Order id.
+ * @param date Date.
+ * @param parentAlgo Parent algo.
+ */
+ OrderParams(int id, int orderId, Date date, String parentAlgo) {
+ this.id = id;
+ this.orderId = orderId;
+ this.date = date;
+ this.parentAlgo = parentAlgo;
+ }
+
+ /**
+ * @return Afinity key.
+ */
+ public AffinityKey<Integer> key() {
+ return new AffinityKey<>(id, orderId);
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof OrderParams && id == ((OrderParams)o).id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+ }
+
+ /**
+ * Cancel Order.
+ */
+ static class Cancel implements Serializable {
+ /** Primary key. */
+ @QuerySqlField(index = true)
+ private int id;
+
+ /** Order id. */
+ @QuerySqlField(index = true)
+ private int refOrderId;
+
+ /** Date */
+ @QuerySqlField
+ private Date date;
+
+ /**
+ * @param id
+ * @param refOrderId Reference order id.
+ * @param date Date.
+ */
+ Cancel(int id, int refOrderId, Date date) {
+ this.id = id;
+ this.refOrderId = refOrderId;
+ this.date = date;
+ }
+
+ /**
+ * @return Afinity key.
+ */
+ public AffinityKey<Integer> key() {
+ return new AffinityKey<>(id, refOrderId);
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof Cancel && id == ((Cancel)o).id;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return id;
+ }
+ }
+
+ /**
+ * Execute information about root query.
+ */
+ static class Exec implements Serializable {
+ /** Primary key. */
+ @QuerySqlField
+ private int rootOrderId;
+
+ /** Date */
+ @QuerySqlField
+ private Date date ;
+
+ /** */
+ @QuerySqlField
+ private int execShares;
+
+ /** */
+ @QuerySqlField
+ private int price;
+
+ /** */
+ @QuerySqlField
+ private int lastMkt;
+
+ /**
+ * @param rootOrderId Root order id.
+ * @param date Date.
+ * @param execShares Execute shares.
+ * @param price Price.
+ * @param lastMkt Last mkt.
+ */
+ Exec(int rootOrderId, Date date, int execShares, int price, int lastMkt) {
+ this.rootOrderId = rootOrderId;
+ this.date = date;
+ this.execShares = execShares;
+ this.price = price;
+ this.lastMkt = lastMkt;
+ }
+
+ /** {@inheritDoc} */
+ @Override public boolean equals(Object o) {
+ return this == o || o instanceof Exec && rootOrderId == ((Exec)o).rootOrderId;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int hashCode() {
+ return rootOrderId;
+ }
+ }
+}