IGNITE-4247: Sql queries supports table alias. This closes #1297.
Project: http://git-wip-us.apache.org/repos/asf/ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/ignite/commit/8e622e41 Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/8e622e41 Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/8e622e41 Branch: refs/heads/master Commit: 8e622e41de4acf365da7f933a08b6d31bae11124 Parents: 74d0dcc Author: Andrey V. Mashenkov <[email protected]> Authored: Tue Jan 17 15:18:33 2017 +0300 Committer: Andrey V. Mashenkov <[email protected]> Committed: Tue Jan 17 15:18:33 2017 +0300 ---------------------------------------------------------------------- .../org/apache/ignite/cache/query/SqlQuery.java | 25 +++++++++++++ .../processors/query/GridQueryIndexing.java | 4 ++- .../processors/query/GridQueryProcessor.java | 5 +-- .../processors/query/h2/IgniteH2Indexing.java | 14 +++++--- .../cache/IgniteCacheAbstractQuerySelfTest.java | 37 ++++++++++++++++++++ .../h2/GridIndexingSpiAbstractSelfTest.java | 24 ++++++------- 6 files changed, 89 insertions(+), 20 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ignite/blob/8e622e41/modules/core/src/main/java/org/apache/ignite/cache/query/SqlQuery.java ---------------------------------------------------------------------- diff --git a/modules/core/src/main/java/org/apache/ignite/cache/query/SqlQuery.java b/modules/core/src/main/java/org/apache/ignite/cache/query/SqlQuery.java index 83e171d..5e36d20 100644 --- a/modules/core/src/main/java/org/apache/ignite/cache/query/SqlQuery.java +++ b/modules/core/src/main/java/org/apache/ignite/cache/query/SqlQuery.java @@ -37,6 +37,9 @@ public final class SqlQuery<K, V> extends Query<Cache.Entry<K, V>> { /** */ private String type; + /** Table alias */ + private String alias; + /** SQL clause. */ private String sql; @@ -138,6 +141,27 @@ public final class SqlQuery<K, V> extends Query<Cache.Entry<K, V>> { } /** + * Sets table alias for type. + * + * @return Table alias. + */ + public String getAlias() { + return alias; + } + + /** + * Gets table alias for type. + * + * @param alias table alias for type that is used in query. + * @return {@code this} For chaining. + */ + public SqlQuery<K, V> setAlias(String alias) { + this.alias = alias; + + return this; + } + + /** * Gets the query execution timeout in milliseconds. * * @return Timeout value. @@ -148,6 +172,7 @@ public final class SqlQuery<K, V> extends Query<Cache.Entry<K, V>> { /** * Sets the query execution timeout. Query will be automatically cancelled if the execution timeout is exceeded. + * * @param timeout Timeout value. Zero value disables timeout. * @param timeUnit Time granularity. * @return {@code this} For chaining. http://git-wip-us.apache.org/repos/asf/ignite/blob/8e622e41/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryIndexing.java ---------------------------------------------------------------------- diff --git a/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryIndexing.java b/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryIndexing.java index 6bffa5d..539ebc0 100644 --- a/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryIndexing.java +++ b/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryIndexing.java @@ -95,6 +95,7 @@ public interface GridQueryIndexing { * * @param spaceName Space name. * @param qry Query. + * @param alias Table alias used in Query. * @param params Query parameters. * @param type Query return type. * @param filter Space name and key filter. @@ -102,7 +103,8 @@ public interface GridQueryIndexing { * @throws IgniteCheckedException If failed. */ public <K, V> GridCloseableIterator<IgniteBiTuple<K, V>> queryLocalSql(@Nullable String spaceName, String qry, - Collection<Object> params, GridQueryTypeDescriptor type, IndexingQueryFilter filter) throws IgniteCheckedException; + String alias, Collection<Object> params, GridQueryTypeDescriptor type, IndexingQueryFilter filter) + throws IgniteCheckedException; /** * Executes text query. http://git-wip-us.apache.org/repos/asf/ignite/blob/8e622e41/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryProcessor.java ---------------------------------------------------------------------- diff --git a/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryProcessor.java b/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryProcessor.java index 0f2bc9a..f4ac4ae 100644 --- a/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryProcessor.java +++ b/modules/core/src/main/java/org/apache/ignite/internal/processors/query/GridQueryProcessor.java @@ -796,7 +796,7 @@ public class GridQueryProcessor extends GridProcessorAdapter { if (type == null || !type.registered()) throw new CacheException("Failed to find SQL table for type: " + resType); - return idx.queryLocalSql(space, clause, params, type, filters); + return idx.queryLocalSql(space, clause, null, params, type, filters); } }, false); } @@ -890,7 +890,8 @@ public class GridQueryProcessor extends GridProcessorAdapter { final GridCloseableIterator<IgniteBiTuple<K, V>> i = idx.queryLocalSql( space, - sqlQry, + qry.getSql(), + qry.getAlias(), F.asList(params), typeDesc, idx.backupFilter(requestTopVer.get(), null)); http://git-wip-us.apache.org/repos/asf/ignite/blob/8e622e41/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java index bc51552..cbf2ebd 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java @@ -1046,14 +1046,14 @@ public class IgniteH2Indexing implements GridQueryIndexing { /** {@inheritDoc} */ @SuppressWarnings("unchecked") @Override public <K, V> GridCloseableIterator<IgniteBiTuple<K, V>> queryLocalSql(@Nullable String spaceName, - final String qry, @Nullable final Collection<Object> params, GridQueryTypeDescriptor type, + final String qry, String alias, @Nullable final Collection<Object> params, GridQueryTypeDescriptor type, final IndexingQueryFilter filter) throws IgniteCheckedException { final TableDescriptor tbl = tableDescriptor(spaceName, type); if (tbl == null) throw new CacheException("Failed to find SQL table for type: " + type.name()); - String sql = generateQuery(qry, tbl); + String sql = generateQuery(qry, alias, tbl); Connection conn = connectionForThread(tbl.schemaName()); @@ -1103,7 +1103,7 @@ public class IgniteH2Indexing implements GridQueryIndexing { String sql; try { - sql = generateQuery(qry.getSql(), tblDesc); + sql = generateQuery(qry.getSql(), qry.getAlias(), tblDesc); } catch (IgniteCheckedException e) { throw new IgniteException(e); @@ -1300,11 +1300,12 @@ public class IgniteH2Indexing implements GridQueryIndexing { * Prepares statement for query. * * @param qry Query string. + * @param tableAlias table alias. * @param tbl Table to use. * @return Prepared statement. * @throws IgniteCheckedException In case of error. */ - private String generateQuery(String qry, TableDescriptor tbl) throws IgniteCheckedException { + private String generateQuery(String qry, String tableAlias, TableDescriptor tbl) throws IgniteCheckedException { assert tbl != null; final String qry0 = qry; @@ -1341,10 +1342,13 @@ public class IgniteH2Indexing implements GridQueryIndexing { } if (!upper.startsWith("FROM")) - from = " FROM " + t + + from = " FROM " + t + (tableAlias != null ? " as " + tableAlias : "") + (upper.startsWith("WHERE") || upper.startsWith("ORDER") || upper.startsWith("LIMIT") ? " " : " WHERE "); + if(tableAlias != null) + t = tableAlias; + qry = "SELECT " + t + "." + KEY_FIELD_NAME + ", " + t + "." + VAL_FIELD_NAME + from + qry; return qry; http://git-wip-us.apache.org/repos/asf/ignite/blob/8e622e41/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/IgniteCacheAbstractQuerySelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/IgniteCacheAbstractQuerySelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/IgniteCacheAbstractQuerySelfTest.java index ad6922c..c5a241e 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/IgniteCacheAbstractQuerySelfTest.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/IgniteCacheAbstractQuerySelfTest.java @@ -348,6 +348,43 @@ public abstract class IgniteCacheAbstractQuerySelfTest extends GridCommonAbstrac } /** + * Test table alias in SqlQuery. + * + * @throws Exception In case of error. + */ + public void testTableAliasInSqlQuery() throws Exception { + IgniteCache<Integer, Integer> cache = ignite().cache(null); + + int key = 898; + + int val = 2; + + cache.put(key, val); + + SqlQuery<Integer, Integer> sqlQry = new SqlQuery<>(Integer.class, "t1._key = ? and t1._val > 1"); + + QueryCursor<Cache.Entry<Integer, Integer>> qry = cache.query(sqlQry.setAlias("t1").setArgs(key)); + + Cache.Entry<Integer, Integer> entry = F.first(qry.getAll()); + + assert entry != null; + + assertEquals(key, entry.getKey().intValue()); + assertEquals(val, entry.getValue().intValue()); + + sqlQry = new SqlQuery<>(Integer.class, "FROM Integer as t1 WHERE t1._key = ? and t1._val > 1"); + + qry = cache.query(sqlQry.setAlias("t1").setArgs(key)); + + entry = F.first(qry.getAll()); + + assert entry != null; + + assertEquals(key, entry.getKey().intValue()); + assertEquals(val, entry.getValue().intValue()); + } + + /** * Tests UDFs. * * @throws IgniteCheckedException If failed. http://git-wip-us.apache.org/repos/asf/ignite/blob/8e622e41/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridIndexingSpiAbstractSelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridIndexingSpiAbstractSelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridIndexingSpiAbstractSelfTest.java index 81e34d6..ad8a7e3 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridIndexingSpiAbstractSelfTest.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/GridIndexingSpiAbstractSelfTest.java @@ -223,15 +223,15 @@ public abstract class GridIndexingSpiAbstractSelfTest extends GridCommonAbstract assertEquals(0, spi.size(typeAB.space(), typeAB)); assertEquals(0, spi.size(typeBA.space(), typeBA)); - assertFalse(spi.queryLocalSql(typeAA.space(), "select * from A.A", Collections.emptySet(), typeAA, null).hasNext()); - assertFalse(spi.queryLocalSql(typeAB.space(), "select * from A.B", Collections.emptySet(), typeAB, null).hasNext()); - assertFalse(spi.queryLocalSql(typeBA.space(), "select * from B.A", Collections.emptySet(), typeBA, null).hasNext()); + assertFalse(spi.queryLocalSql(typeAA.space(), "select * from A.A", null, Collections.emptySet(), typeAA, null).hasNext()); + assertFalse(spi.queryLocalSql(typeAB.space(), "select * from A.B", null, Collections.emptySet(), typeAB, null).hasNext()); + assertFalse(spi.queryLocalSql(typeBA.space(), "select * from B.A", null, Collections.emptySet(), typeBA, null).hasNext()); - assertFalse(spi.queryLocalSql(typeBA.space(), "select * from B.A, A.B, A.A", + assertFalse(spi.queryLocalSql(typeBA.space(), "select * from B.A, A.B, A.A", null, Collections.emptySet(), typeBA, null).hasNext()); try { - spi.queryLocalSql(typeBA.space(), "select aa.*, ab.*, ba.* from A.A aa, A.B ab, B.A ba", + spi.queryLocalSql(typeBA.space(), "select aa.*, ab.*, ba.* from A.A aa, A.B ab, B.A ba", null, Collections.emptySet(), typeBA, null).hasNext(); fail("Enumerations of aliases in select block must be prohibited"); @@ -240,10 +240,10 @@ public abstract class GridIndexingSpiAbstractSelfTest extends GridCommonAbstract // all fine } - assertFalse(spi.queryLocalSql(typeAB.space(), "select ab.* from A.B ab", + assertFalse(spi.queryLocalSql(typeAB.space(), "select ab.* from A.B ab", null, Collections.emptySet(), typeAB, null).hasNext()); - assertFalse(spi.queryLocalSql(typeBA.space(), "select ba.* from B.A as ba", + assertFalse(spi.queryLocalSql(typeBA.space(), "select ba.* from B.A as ba", null, Collections.emptySet(), typeBA, null).hasNext()); // Nothing to remove. @@ -298,7 +298,7 @@ public abstract class GridIndexingSpiAbstractSelfTest extends GridCommonAbstract // Query data. Iterator<IgniteBiTuple<Integer, Map<String, Object>>> res = - spi.queryLocalSql(typeAA.space(), "from a order by age", Collections.emptySet(), typeAA, null); + spi.queryLocalSql(typeAA.space(), "from a order by age", null, Collections.emptySet(), typeAA, null); assertTrue(res.hasNext()); assertEquals(aa(3, "Borya", 18).value(null, false), value(res.next())); @@ -306,7 +306,7 @@ public abstract class GridIndexingSpiAbstractSelfTest extends GridCommonAbstract assertEquals(aa(2, "Valera", 19).value(null, false), value(res.next())); assertFalse(res.hasNext()); - res = spi.queryLocalSql(typeAA.space(), "select aa.* from a aa order by aa.age", + res = spi.queryLocalSql(typeAA.space(), "select aa.* from a aa order by aa.age", null, Collections.emptySet(), typeAA, null); assertTrue(res.hasNext()); @@ -315,7 +315,7 @@ public abstract class GridIndexingSpiAbstractSelfTest extends GridCommonAbstract assertEquals(aa(2, "Valera", 19).value(null, false), value(res.next())); assertFalse(res.hasNext()); - res = spi.queryLocalSql(typeAB.space(), "from b order by name", Collections.emptySet(), typeAB, null); + res = spi.queryLocalSql(typeAB.space(), "from b order by name", null, Collections.emptySet(), typeAB, null); assertTrue(res.hasNext()); assertEquals(ab(1, "Vasya", 20, "Some text about Vasya goes here.").value(null, false), value(res.next())); @@ -323,7 +323,7 @@ public abstract class GridIndexingSpiAbstractSelfTest extends GridCommonAbstract assertEquals(ab(4, "Vitalya", 20, "Very Good guy").value(null, false), value(res.next())); assertFalse(res.hasNext()); - res = spi.queryLocalSql(typeAB.space(), "select bb.* from b as bb order by bb.name", + res = spi.queryLocalSql(typeAB.space(), "select bb.* from b as bb order by bb.name", null, Collections.emptySet(), typeAB, null); assertTrue(res.hasNext()); @@ -333,7 +333,7 @@ public abstract class GridIndexingSpiAbstractSelfTest extends GridCommonAbstract assertFalse(res.hasNext()); - res = spi.queryLocalSql(typeBA.space(), "from a", Collections.emptySet(), typeBA, null); + res = spi.queryLocalSql(typeBA.space(), "from a", null, Collections.emptySet(), typeBA, null); assertTrue(res.hasNext()); assertEquals(ba(2, "Kolya", 25, true).value(null, false), value(res.next()));
