@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 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-934" ) @Test public void shouldNotIncludePseudoColumnsInSelectStarOfJcrSql2Query() throws RepositoryException { Query query = session.getWorkspace().getQueryManager().createQuery("select * FROM [nt:base]", Query.JCR_SQL2); assertThat(query, is(notNullValue())); QueryResult result = query.execute(); validateQuery().rowCount(totalNodeCount).hasColumns(allColumnNames("nt:base")).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindAllUnstructuredNodes() throws RepositoryException { String sql = "SELECT * FROM [nt:unstructured]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(24).hasColumns(allColumnNames("nt:unstructured")).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindAllUnstructuredNodesWithLikeCriteriaOnMultiValuedProperty() throws RepositoryException { String sql = "SELECT * FROM [nt:unstructured] WHERE something LIKE 'white%' and something LIKE 'black%'"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); // print = true; QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns(allColumnNames("nt:unstructured")).validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryToFindAllUnstructuredNodes() throws RepositoryException { Query query = session.getWorkspace().getQueryManager().createQuery("//element(*,nt:unstructured)", Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(24).hasColumns(allColumnNames()).validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryToFindAllUnstructuredNodesOrderedByScore() throws RepositoryException { String xpath = "//element(*,nt:unstructured) order by jcr:score()"; Query query = session.getWorkspace().getQueryManager().createQuery(xpath, Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(24).hasColumns(allColumnNames()).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindAllUnstructuredNodesWithCriteriaOnMultiValuedProperty() throws RepositoryException { String sql = "SELECT * FROM [nt:unstructured] WHERE something = 'white dog' and something = 'black dog'"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); // print = true; QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns(allColumnNames("nt:unstructured")).validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryToFindAllNodes() throws RepositoryException { Query query = session.getWorkspace().getQueryManager().createQuery("//element(*,nt:base)", Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(totalNodeCount).hasColumns(allColumnNames()).validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryToFindAllNodesOrderingByPath() throws RepositoryException { String xpath = "//element(*,nt:base) order by @jcr:path"; Query query = session.getWorkspace().getQueryManager().createQuery(xpath, Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(totalNodeCount).hasColumns(allColumnNames()).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindAllNodesWithOrderByPathUsingAlias() throws RepositoryException { String sql = "SELECT * FROM [nt:base] AS all ORDER BY all.[jcr:path]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allColumnNames("all"); validateQuery().rowCount(totalNodeCount).noWarnings().hasColumns(columnNames).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindAllNodesWithOrderByPath() throws RepositoryException { String sql = "SELECT * FROM [nt:base] ORDER BY [jcr:path]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allColumnNames("nt:base"); validateQuery().rowCount(totalNodeCount).noWarnings().hasColumns(columnNames).validate(query, result); }
@SuppressWarnings( "deprecation" ) @FixFor( "MODE-934" ) @Test public void shouldNotIncludePseudoColumnsInSelectStarOfJcrSqlQuery() throws RepositoryException { String sql = "select * FROM nt:base"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.SQL); QueryResult result = query.execute(); validateQuery().rowCount(totalNodeCount).hasColumns(allColumnNames()).validate(query, result); }
@Test @FixFor( "MODE-2329" ) public void shouldAllowUsingExpandedSelectorNameInQOM() throws Exception { QueryObjectModelFactory qomFactory = session.getWorkspace().getQueryManager().getQOMFactory(); Selector selector = qomFactory.selector(NodeType.NT_BASE, "category"); // Build and execute the query ... Query query = qomFactory.createQuery(selector, qomFactory.childNode("category", "/Cars"), null, new Column[0]); assertThat(query.getStatement(), is("SELECT * FROM [{http://www.jcp.org/jcr/nt/1.0}base] AS category WHERE ISCHILDNODE(category,'/Cars')")); QueryResult result = query.execute(); validateQuery().rowCount(4).hasColumns(allColumnNames("category")).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-1671" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindAllNodesWithOrderByNodeId() throws RepositoryException { String sql = "SELECT * FROM [nt:base] ORDER BY [mode:id]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = allColumnNames("nt:base"); validateQuery().rowCount(totalNodeCount).noWarnings().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); }
@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-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); }