validateQuery() .rowCount(4) .hasNodesAtPaths("/words/word1", "/words/word2", "/words/word3", "/words/word4").validate(query, result); validateQuery() .rowCount(4) .hasNodesAtPaths("/words/word1", "/words/word3", "/words/word2", "/words/word4").validate(query, result); } finally { session.getNode("/words").remove();
Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(2).hasNodesAtPaths(node1.getPath(), node2.getPath()).validate(query, result); validateQuery().rowCount(2).hasNodesAtPaths(node1.getPath(), node2.getPath()).validate(query, result); validateQuery().rowCount(2).hasNodesAtPaths(node1.getPath(), node2.getPath()).validate(query, result); } finally { testRoot.remove();
@Test public void shouldSelectIndexWhenMultipleAndedConstraintsApply() throws Exception { registerValueIndex("longValues", "nt:unstructured", "Long values index", "*", "value", PropertyType.LONG); Node root = session().getRootNode(); int valuesCount = 5; for (int i = 0; i < valuesCount; i++) { String name = String.valueOf(i+1); Node node = root.addNode(name); node.setProperty("value", (long) (i+1)); } session.save(); String sql1 = "SELECT number.[jcr:name] FROM [nt:unstructured] as number WHERE (number.value > 1 AND number.value < 3) OR " + "(number.value > 3 AND number.value < 5)"; String sql2 = "SELECT number.[jcr:name] FROM [nt:unstructured] as number WHERE number.value <2"; Query query = jcrSql2Query(sql1 + " UNION " + sql2); validateQuery() .rowCount(2L) .useIndex("longValues") .hasNodesAtPaths("/2", "/4", "/1") .validate(query, query.execute()); }
.rowCount(1L) .useIndex("typesIndex") .hasNodesAtPaths("/node101") .validate(query1, query1.execute()); .rowCount(1L) .useIndex("typesIndex") .hasNodesAtPaths("/node51") .validate(query2, query2.execute()); .rowCount(1L) .useIndex("typesIndex") .hasNodesAtPaths("/node1") .validate(query3, query3.execute());
.rowCount(2L) .useIndex("booleanIndex") .hasNodesAtPaths("/node1", "/node2") .validate(query, query.execute()); .rowCount(1L) .useIndex("booleanIndex") .hasNodesAtPaths("/node1") .validate(query, query.execute()); .rowCount(1L) .useIndex("booleanIndex") .hasNodesAtPaths("/node1") .validate(query, query.execute());
@Test @FixFor( "MODE-2435" ) public void shouldCorrectlyExecuteOrderByWithOffsetAndLimit() throws RepositoryException { // no order by String sql = "SELECT [jcr:path] FROM [car:Car] LIMIT 10 OFFSET 15"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); assertThat("result should contain zero rows", result.getRows().getSize(), is(0L)); // with order by sql = "SELECT [jcr:path] FROM [car:Car] ORDER BY [jcr:path] LIMIT 10 OFFSET 15"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); assertThat("result should contain zero rows", result.getRows().getSize(), is(0L)); sql = "SELECT [jcr:path] FROM [car:Car] ORDER BY [jcr:path] LIMIT 1 OFFSET 0"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); validateQuery().hasNodesAtPaths("/Cars/Hybrid/Nissan Altima").validate(query, result); sql = "SELECT [jcr:path] FROM [car:Car] ORDER BY [jcr:path] LIMIT 1 OFFSET 1"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); validateQuery().hasNodesAtPaths("/Cars/Hybrid/Toyota Highlander").validate(query, result); sql = "SELECT [jcr:path] FROM [car:Car] ORDER BY [jcr:path] LIMIT 3 OFFSET 0"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); validateQuery().hasNodesAtPaths("/Cars/Hybrid/Nissan Altima", "/Cars/Hybrid/Toyota Highlander", "/Cars/Hybrid/Toyota Prius").validate(query, result); }
private RepositoryOperation reindexingExternalContentOperation() { return repository -> { JcrSession session = repository.login(); // sleep a bit to make sure reindexing completes Thread.sleep(300); try { AbstractJcrNode node = session.getNode("/fs2/file.txt"); String createdBy = node.getProperty("jcr:createdBy").getString(); assertNotNull(createdBy); JcrQueryManager jcrQueryManager = session.getWorkspace().getQueryManager(); Query query = jcrQueryManager.createQuery("select file.[jcr:path] from [nt:file] as file where file.[jcr:createdBy]='" + createdBy + "'", JcrQuery.JCR_SQL2); ValidateQuery.validateQuery() .useIndex("nodesByAuthor") .hasNodesAtPaths("/fs2/file.txt") .validate(query, query.execute()); session.getWorkspace().reindex(); ValidateQuery.validateQuery() .useIndex("nodesByAuthor") .hasNodesAtPaths("/fs2/file.txt") .validate(query, query.execute()); } finally { session.logout(); } }; }
@FixFor( "MODE-2062" ) @Test public void fullTextShouldWorkWithBindVar() throws Exception { Node n1 = session.getRootNode().addNode("n1"); n1.setProperty("n1-prop-1", "wow"); n1.setProperty("n1-prop-2", "any"); Node n2 = session.getRootNode().addNode("n2"); n2.setProperty("n2-prop-1", "test"); try { session.save(); // test with literal String queryString = "select * from [nt:unstructured] as a where contains(a.*, 'wow')"; assertNodesAreFound(queryString, Query.JCR_SQL2, "/n1"); // test with bind String queryStringWithBind = "select * from [nt:unstructured] as a where contains(a.*, $text)"; QueryManager queryManager = session.getWorkspace().getQueryManager(); Query query = queryManager.createQuery(queryStringWithBind, Query.JCR_SQL2); query.bindValue("text", session.getValueFactory().createValue("wow")); QueryResult result = query.execute(); validateQuery().rowCount(1).hasNodesAtPaths("/n1").validate(query, result); } finally { n1.remove(); n2.remove(); session.save(); } }
@FixFor( "MODE-1052" ) @Test public void shouldProperlyUseNotWithPathConstraints() throws Exception { // Find all nodes that are children of '/Cars' ... there should be 4 ... String sql = "SELECT [jcr:path] FROM [nt:base] WHERE ISCHILDNODE([nt:base],'/Cars') ORDER BY [jcr:path]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] paths = {"/Cars/Hybrid", "/Cars/Luxury", "/Cars/Sports", "/Cars/Utility"}; validateQuery().rowCount(4).hasColumns("jcr:path").hasNodesAtPaths(paths).validate(query, result); // Find all nodes ... there should be 24 ... sql = "SELECT [jcr:path] FROM [nt:base] ORDER BY [jcr:path]"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); validateQuery().rowCount(totalNodeCount).hasColumns("jcr:path").validate(query, result); // Find all nodes that are NOT children of '/Cars' (and not under '/jcr:system') ... sql = "SELECT [jcr:path] FROM [nt:base] WHERE NOT(ISCHILDNODE([nt:base],'/Cars')) AND NOT(ISDESCENDANTNODE([nt:base],'/jcr:system')) ORDER BY [jcr:path]"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); paths = new String[] {"/", "/Cars", "/Cars/Hybrid/Nissan Altima", "/Cars/Hybrid/Toyota Highlander", "/Cars/Hybrid/Toyota Prius", "/Cars/Luxury/Bentley Continental", "/Cars/Luxury/Cadillac DTS", "/Cars/Luxury/Lexus IS350", "/Cars/Sports/Aston Martin DB9", "/Cars/Sports/Infiniti G37", "/Cars/Utility/Ford F-150", "/Cars/Utility/Hummer H3", "/Cars/Utility/Land Rover LR2", "/Cars/Utility/Land Rover LR3", "/Cars/Utility/Toyota Land Cruiser", "/NodeB", "/Other", "/Other/NodeA", "/Other/NodeA[2]", "/Other/NodeA[3]", "/Other/NodeC", "/jcr:system"}; validateQuery().rowCount(22).hasColumns("jcr:path").validate(query, result); }
@Test public void shouldBeAbleToQueryWithLimitAndOffsetOnNonJoin() throws RepositoryException { // Try with the OFFSET expression ... String sql = "SELECT [jcr:path] FROM [car:Car] ORDER BY [jcr:path]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); final String[] allCarPaths = {"/Cars/Hybrid/Nissan Altima", "/Cars/Hybrid/Toyota Highlander", "/Cars/Hybrid/Toyota Prius", "/Cars/Luxury/Bentley Continental", "/Cars/Luxury/Cadillac DTS", "/Cars/Luxury/Lexus IS350", "/Cars/Sports/Aston Martin DB9", "/Cars/Sports/Infiniti G37", "/Cars/Utility/Ford F-150", "/Cars/Utility/Hummer H3", "/Cars/Utility/Land Rover LR2", "/Cars/Utility/Land Rover LR3", "/Cars/Utility/Toyota Land Cruiser"}; validateQuery().rowCount(13).hasColumns("jcr:path").hasNodesAtPaths(allCarPaths).validate(query, result); // Try with the OFFSET expression ... sql = "SELECT [jcr:path] FROM [car:Car] ORDER BY [jcr:path] LIMIT 2 OFFSET 2"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); validateQuery().rowCount(2).hasColumns("jcr:path").hasNodesAtPaths(allCarPaths[2], allCarPaths[3]) .validate(query, result); // Try with the method ... sql = "SELECT [jcr:path] FROM [car:Car]"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); query.setOffset(2); result = query.execute(); validateQuery().rowCount(11).hasColumns("jcr:path").validate(query, result); }
@Test @Override public void shouldSelectIndexWhenMultipleAndedConstraintsApply() throws Exception { registerValueIndex("longValues", "nt:unstructured", "Long values index", "*", "value", PropertyType.LONG); Node root = session().getRootNode(); int valuesCount = 5; for (int i = 0; i < valuesCount; i++) { String name = String.valueOf(i+1); Node node = root.addNode(name); node.setProperty("value", (long) (i+1)); } session.save(); String sql1 = "SELECT number.[jcr:name] FROM [nt:unstructured] as number WHERE (number.value > 1 AND number.value < 3) OR " + "(number.value > 3 AND number.value < 5)"; String sql2 = "SELECT number.[jcr:name] FROM [nt:unstructured] as number WHERE number.value <2"; Query query = jcrSql2Query(sql1 + " UNION " + sql2); validateQuery() .rowCount(3L) .useIndex("longValues") .hasNodesAtPaths("/2", "/4", "/1") .validate(query, query.execute()); }
@SuppressWarnings( "deprecation" ) @Test public void shouldBeAbleToExecuteXPathQueryToFindSameNameSiblingsByIndex() throws RepositoryException { Query query = session.getWorkspace().getQueryManager().createQuery("/jcr:root/Other/NodeA", Query.XPATH); QueryResult result = query.execute(); validateQuery().rowCount(3).hasColumns("jcr:primaryType", "jcr:path", "jcr:score").validate(query, result); query = session.getWorkspace().getQueryManager().createQuery("/jcr:root/Other/NodeA[2]", Query.XPATH); result = query.execute(); validateQuery().rowCount(1).hasColumns("jcr:primaryType", "jcr:path", "jcr:score").hasNodesAtPaths("/Other/NodeA[2]") .validate(query, result); }
@Test @FixFor( "MODE-2247" ) public void shouldBeAbleToExecuteExceptAllOperation() throws RepositoryException { String sql = "SELECT category.[jcr:path] AS p FROM [nt:unstructured] AS category JOIN [car:Car] AS cars ON ISCHILDNODE(cars,category) " + " WHERE cars.[jcr:name] LIKE '%Rover%' " + "EXCEPT ALL " + "SELECT node.[jcr:path] AS p FROM [nt:unstructured] AS node WHERE NOT ISCHILDNODE(node,'/Cars')"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(2).hasColumns("p").hasNodesAtPaths("/Cars/Utility", "/Cars/Utility").validate(query, result); }
@FixFor( "MODE-1418" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithFullTextSearchWithNoSelectorAndOneProperty() throws RepositoryException { String sql = "select [jcr:path] from [nt:unstructured] as n where contains(something,'cat wearing')"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns("jcr:path").hasNodesAtPaths("/Other/NodeA[2]").validate(query, result); }
@Test @FixFor( "MODE-2166" ) public void shouldSupportCastDynamicOperand() throws Exception { String sql = "SELECT car.[jcr:path] FROM [car:Car] as car WHERE CAST(car.[car:year] AS long) = 1967"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(1).hasNodesAtPaths("/Cars/Utility/Toyota Land Cruiser").validate(query, result); }
@Test @FixFor( "MODE-2247" ) public void shouldBeAbleToExecuteIntersectAllOperation() throws RepositoryException { String sql = "SELECT category.[jcr:path] AS p FROM [nt:unstructured] AS category " + " INTERSECT ALL " + "SELECT category.[jcr:path] AS p FROM [nt:unstructured] AS category JOIN [car:Car] AS cars ON ISCHILDNODE(cars,category) WHERE cars.[jcr:name] LIKE '%Rover%'"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(2).hasColumns("p").hasNodesAtPaths("/Cars/Utility", "/Cars/Utility").validate(query, result); }
@FixFor( "MODE-1840" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithBindVariableInsideContains() throws RepositoryException { String sql = "select [jcr:path] from [nt:unstructured] as n where contains(n.something, $expression)"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); query.bindValue("expression", session.getValueFactory().createValue("cat wearing")); QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns("jcr:path").hasNodesAtPaths("/Other/NodeA[2]").validate(query, result); }
@Test @FixFor( "MODE-2247" ) public void shouldBeAbleToExecuteExceptOperationWithSimpleCriteria() throws Exception { String sql1 = "SELECT car1.[jcr:path] FROM [car:Car] AS car1 WHERE car1.[jcr:name] LIKE '%Land Rover%'"; String sql2 = "SELECT car2.[jcr:path] FROM [car:Car] AS car2 WHERE car2.[jcr:name] LIKE '%LR3%'"; String queryString = sql1 + " EXCEPT " + sql2; Query query = session.getWorkspace().getQueryManager().createQuery(queryString, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(1).hasColumns("jcr:path").hasNodesAtPaths("/Cars/Utility/Land Rover LR2") .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); }
@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); }