@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithChildNodeJoinAndColumnsFromBothSidesOfJoin() throws RepositoryException { String sql = "SELECT car.*, category.[jcr:primaryType] from [car:Car] as car JOIN [nt:unstructured] as category ON ISCHILDNODE(car,category) WHERE NAME(category) LIKE 'Utility'"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(carColumnNames("car"), new String[] {"category.jcr:primaryType"}); validateQuery().rowCount(5).hasColumns(columnNames).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithDescendantNodeJoinAndColumnsFromBothSidesOfJoin() throws RepositoryException { String sql = "SELECT car.*, category.[jcr:primaryType] from [car:Car] as car JOIN [nt:unstructured] as category ON ISDESCENDANTNODE(car,category) WHERE NAME(category) LIKE 'Utility'"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(carColumnNames("car"), new String[] {"category.jcr:primaryType"}); validateQuery().rowCount(5).hasColumns(columnNames).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithOrderByOnResidualColumnAndNoAlias() throws RepositoryException { String sql = "SELECT * FROM [nt:unstructured] ORDER BY propC"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(allColumnNames("nt:unstructured"), new String[] {"propC"}); validateQuery().rowCount(24).warnings(1).hasColumns(columnNames).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithOrderByOnResidualColumn() throws RepositoryException { String sql = "SELECT x.* FROM [nt:unstructured] AS x ORDER BY x.propC"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(24).warnings(1).hasColumns(allOf(allColumnNames("x"), new String[] {"propC"})) .validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithDescendantNodeJoinWithoutCriteria() throws RepositoryException { // The 'all' selector will find all nodes, including '/Car' and '/Car/Sports'. Thus, // '/Car/Sports/Infiniti G37' will be a descendant of both '/Car' and '/Car/Sports', and thus will appear // once joined with '/Car' and once joined with '/Car/Sports'. These two tuples will be similar, but they are // actually not repeats (since the columns from 'all' will be different). String sql = "SELECT * FROM [car:Car] as car JOIN [nt:unstructured] as all ON ISDESCENDANTNODE(car,all)"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(carColumnNames("car"), allColumnNames("all")); validateQuery().rowCount(26).hasColumns(columnNames).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithDescendantNodeJoinWithDepthCriteria() throws RepositoryException { String sql = "SELECT * FROM [car:Car] as car JOIN [nt:unstructured] as category ON ISDESCENDANTNODE(car,category) WHERE DEPTH(category) = 2"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(carColumnNames("car"), allColumnNames("category")); validateQuery().rowCount(13).hasColumns(columnNames).validate(query, result); }
@FixFor( {"MODE-1095", "MODE-1680"} ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithOrderByAndJoinCriteriaOnColumnsNotInSelect() throws RepositoryException { String sql = "SELECT y.*, y.propC FROM [nt:unstructured] AS x INNER JOIN [nt:unstructured] AS y ON x.somethingElse = y.propC ORDER BY x.propC"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(allColumnNames("y"), new String[] {"y.propC"}); validateQuery().rowCount(1).warnings(3).hasColumns(columnNames).hasNodesAtPaths("/Other/NodeA[2]") .validate(query, result); }
@FixFor( "MODE-1110" ) @Test public void shouldExecuteQueryWithThreeInnerJoinsAndCriteriaOnDifferentSelectors() throws Exception { String sql = "SELECT * from [nt:base] as car INNER JOIN [nt:base] as categories ON ISDESCENDANTNODE(car, categories) " + " INNER JOIN [nt:base] as carsNode ON ISDESCENDANTNODE (categories, carsNode) " + " WHERE PATH(carsNode) = '/Cars' AND ISDESCENDANTNODE( categories, '/Cars') OR car.[jcr:primaryType] IS NOT NULL"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(allColumnNames("car"), allColumnNames("categories"), allColumnNames("carsNode")); validateQuery().rowCount(13).hasColumns(columnNames).validate(query, result); }
@FixFor( "MODE-1095" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithJoinCriteriaOnSomeColumnsInSelect() throws RepositoryException { String sql = "SELECT y.*, y.propC FROM [nt:unstructured] AS x INNER JOIN [nt:unstructured] AS y ON x.somethingElse = y.propC"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(allColumnNames("y"), new String[] {"y.propC"}); validateQuery().rowCount(1).warnings(2).withRows().withRow("/Other/NodeA[2]").endRows().hasColumns(columnNames) .validate(query, result); }
@FixFor( "MODE-1095" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithJoinCriteriaOnColumnsInSelectAndOrderBy() throws RepositoryException { String sql = "SELECT x.*, y.* FROM [nt:unstructured] AS x INNER JOIN [nt:unstructured] AS y ON x.somethingElse = y.propC ORDER BY x.propC"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(allColumnNames("x"), allColumnNames("y"), new String[] {"x.propC"}); validateQuery().rowCount(1).warnings(3).withRows().withRow("/Other/NodeA", "/Other/NodeA[2]").endRows() .hasColumns(columnNames).validate(query, result); }
@FixFor( "MODE-1095" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithJoinCriteriaOnColumnsInSelect() throws RepositoryException { String sql = "SELECT x.*, x.somethingElse, y.*, y.propC FROM [nt:unstructured] AS x INNER JOIN [nt:unstructured] AS y ON x.somethingElse = y.propC"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allOf(allColumnNames("x"), allColumnNames("y"), new String[] {"x.somethingElse", "y.propC"}); validateQuery().rowCount(1).warnings(2).withRows().withRow("/Other/NodeA", "/Other/NodeA[2]").endRows() .hasColumns(columnNames).validate(query, result); }
@FixFor( "MODE-2151" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithChildNodeJoinWithChildCountCriteria() throws RepositoryException { String sql = "SELECT * FROM [car:Car] as car JOIN [nt:unstructured] as category ON ISCHILDNODE(car,category) WHERE CHILDCOUNT(category) > 4"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); String[] columnNames = allOf(carColumnNames("car"), allColumnNames("category")); // Just the "Utility" nodes ... validateQuery().rowCount(5).hasColumns(columnNames).validate(query, query.execute()); sql = "SELECT * FROM [car:Car] as car JOIN [nt:unstructured] as category ON ISCHILDNODE(car,category) WHERE CHILDCOUNT(category) > 2"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); // Just the "Utility", "Hybrid", and "Luxury" nodes ... validateQuery().rowCount(11).hasColumns(columnNames).validate(query, query.execute()); sql = "SELECT * FROM [car:Car] as car JOIN [nt:unstructured] as category ON ISCHILDNODE(car,category)"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); // All categories ... validateQuery().rowCount(13).hasColumns(columnNames).validate(query, query.execute()); }