@Test @FixFor( "MODE-2247" ) public void shouldBeAbleToExecuteIntersectOperationWithJoinCriteria() throws RepositoryException { String sql = "SELECT category.[jcr:path] AS p FROM [nt:unstructured] AS category " + " INTERSECT " + "SELECT category.[jcr:path] AS p FROM [nt:unstructured] AS category JOIN [car:Car] AS cars ON ISCHILDNODE(cars,category) WHERE cars.[jcr:name]='Land Rover LR3'"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns("p").hasNodesAtPaths("/Cars/Utility").validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryWithElementTestForChildrenOfRootWithName() throws RepositoryException { Query query = session.getWorkspace().getQueryManager().createQuery("/jcr:root/Other/element(NodeA)", Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(3).hasColumns("jcr:primaryType", "jcr:path", "jcr:score").validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryWithComplexContainsCriteriaWithNoHyphenAndWildcard() throws RepositoryException { String xpath = "/jcr:root//*[jcr:contains(., '\"heavy-du*\"')]"; Query query = session.getWorkspace().getQueryManager().createQuery(xpath, Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns("jcr:primaryType", "jcr:path", "jcr:score").validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryWithComplexContainsCriteriaWithHyphen() throws RepositoryException { String xpath = "/jcr:root//*[jcr:contains(., '\"5-speed\"')]"; Query query = session.getWorkspace().getQueryManager().createQuery(xpath, Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns("jcr:primaryType", "jcr:path", "jcr:score").validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryToFindAllCarNodes() throws RepositoryException { Query query = session.getWorkspace().getQueryManager().createQuery("//element(*,car:Car)", Query.XPATH); QueryResult result = query.execute(); String[] columnNames = {"jcr:primaryType", "jcr:mixinTypes", "jcr:path", "jcr:score", "jcr:created", "jcr:createdBy", "jcr:name", "mode:localName", "mode:depth", "mode:id", "car:mpgCity", "car:userRating", "car:mpgHighway", "car:engine", "car:model", "car:year", "car:maker", "car:lengthInInches", "car:valueRating", "car:wheelbaseInInches", "car:msrp", "car:alternateModels"}; validateQuery().rowCount(13).hasColumns(columnNames).validate(query, result); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryToFindNodesUnderPath() throws RepositoryException { String xpath = " /jcr:root/Cars//*"; Query query = session.getWorkspace().getQueryManager().createQuery(xpath, Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(17).hasColumns("jcr:primaryType", "jcr:path", "jcr:score").validate(query, result); }
@FixFor( "MODE-2286" ) @Test public void shouldFindSystemNodesUsingPathLikeCriteria() throws Exception { String sql = "select [jcr:path] from [nt:base] where [jcr:path] like '/Other/NodeA[%]'"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); validateQuery().rowCount(3).validate(query, query.execute()); }
private void executeQueryWithSingleResult( String sql ) throws RepositoryException { Query query = session.getWorkspace().getQueryManager().createQuery(sql, JcrRepository.QueryLanguage.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns("jcr:path").validate(query, result); }
@FixFor( "MODE-1418" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithFullTextSearchWithSelectorAndOneProperty() throws RepositoryException { String sql = "select [jcr:path] from [nt:unstructured] as n where contains(n.something, 'cat wearing')"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns("jcr:path").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-1737" ) @Test public void shouldSupportSelectDistinct() throws RepositoryException { String sql = "SELECT DISTINCT cars.[car:maker], cars.[car:lengthInInches] FROM [car:Car] AS cars"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] expectedColumns = {"car:maker", "car:lengthInInches"}; validateQuery().rowCount(13).hasColumns(expectedColumns).validate(query, result); }
@FixFor( "MODE-1738" ) @Test public void shouldSupportJoinWithOrderByOnPseudoColumn() throws RepositoryException { String sql = "SELECT category.[jcr:path], cars.[car:maker], cars.[car:lengthInInches] FROM [nt:unstructured] AS category JOIN [car:Car] AS cars ON ISDESCENDANTNODE(cars,category) WHERE ISCHILDNODE(category,'/Cars') ORDER BY cars.[mode:localName]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] expectedColumns = {"category.jcr:path", "cars.car:maker", "cars.mode:localName", "cars.car:lengthInInches"}; validateQuery().rowCount(13).hasColumns(expectedColumns).validate(query, result); }
@FixFor( "MODE-1679" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindReferenceableNodes() throws RepositoryException { String sql = "SELECT [jcr:uuid] FROM [mix:referenceable]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(4).hasColumns("jcr:uuid").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 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); }
@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); }
@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 shouldBeAbleToCreateAndExecuteJcrSql2QueryWithOrderByUsingColumnNotInSelect() throws RepositoryException { String sql = "SELECT [car:model], [car:maker] FROM [car:Car] WHERE [car:year] <= 2012 ORDER BY [car:year] DESC"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = {"car:model", "car:maker", "car:year"}; validateQuery().rowCount(13).noWarnings().hasColumns(columnNames).validate(query, result); }
@FixFor( "MODE-1888" ) @Test public void shouldCaptureWarningAboutUseOfPseudoColumnWithPeriodInsteadOfColonDelimiter() throws RepositoryException { String sql = "SELECT [jcr.path] FROM [nt:unstructured]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(24).warnings(1).validate(query, result); }
@FixFor( "MODE-1671" ) @Test public void shouldNotCaptureWarningAboutUseOfNodeIdPseudoColumn() throws RepositoryException { String sql = "SELECT [mode:id] FROM [nt:unstructured]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(24).noWarnings().hasColumns("mode:id").validate(query, result); }