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-gg-9829 Commit: fe743909cf483d00ab5189049523ed3353bf5a3d Parents: 2f458d2 Author: Artem Shutak <ashu...@gridgain.com> Authored: Wed Apr 8 19:55:29 2015 +0300 Committer: Artem Shutak <ashu...@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; + } + } +}
