protected static String[] allColumnNames( String selectorName ) { return prefixEach(allColumnNames(), selectorName + "."); }
@Test public void shouldFindSystemNodesUsingPathCriteria() throws Exception { String queryString = "select [jcr:path] from [nt:base] where [jcr:path] like '/jcr:system/%' and [jcr:path] not like '/jcr:system/%/%'"; assertNodesAreFound(queryString, Query.JCR_SQL2, INDEXED_SYSTEM_NODES_PATHS); }
@FixFor( "MODE-1611" ) @Test public void shouldAllowQomEqualityCriteriaOnPropertyDefinedWithBooleanPropertyDefinition() throws RepositoryException { assertQomQueryWithBooleanValue(1, null, null, false); assertQomQueryWithBooleanValue(1, "notion:booleanProperty", JCR_OPERATOR_EQUAL_TO, true); assertQomQueryWithBooleanValue(0, "notion:booleanProperty", JCR_OPERATOR_EQUAL_TO, false); assertQomQueryWithBooleanValue(1, "notion:booleanProperty", JCR_OPERATOR_GREATER_THAN, false); assertQomQueryWithBooleanValue(1, "notion:booleanProperty", JCR_OPERATOR_GREATER_THAN_OR_EQUAL_TO, false); assertQomQueryWithBooleanValue(0, "notion:booleanProperty", JCR_OPERATOR_GREATER_THAN, true); assertQomQueryWithBooleanValue(1, "notion:booleanProperty", JCR_OPERATOR_GREATER_THAN_OR_EQUAL_TO, true); assertQomQueryWithBooleanValue(0, "notion:booleanProperty", JCR_OPERATOR_LESS_THAN, false); assertQomQueryWithBooleanValue(0, "notion:booleanProperty", JCR_OPERATOR_LESS_THAN_OR_EQUAL_TO, false); assertQomQueryWithBooleanValue(0, "notion:booleanProperty", JCR_OPERATOR_LESS_THAN, true); assertQomQueryWithBooleanValue(1, "notion:booleanProperty", JCR_OPERATOR_LESS_THAN_OR_EQUAL_TO, true); assertQomQueryWithBooleanValue(0, "notion:booleanProperty2", JCR_OPERATOR_EQUAL_TO, true); assertQomQueryWithBooleanValue(1, "notion:booleanProperty2", JCR_OPERATOR_EQUAL_TO, false); assertQomQueryWithBooleanValue(0, "notion:booleanProperty2", JCR_OPERATOR_GREATER_THAN, false); assertQomQueryWithBooleanValue(1, "notion:booleanProperty2", JCR_OPERATOR_GREATER_THAN_OR_EQUAL_TO, false); assertQomQueryWithBooleanValue(0, "notion:booleanProperty2", JCR_OPERATOR_GREATER_THAN, true); assertQomQueryWithBooleanValue(0, "notion:booleanProperty2", JCR_OPERATOR_GREATER_THAN_OR_EQUAL_TO, true); assertQomQueryWithBooleanValue(0, "notion:booleanProperty2", JCR_OPERATOR_LESS_THAN, false); assertQomQueryWithBooleanValue(1, "notion:booleanProperty2", JCR_OPERATOR_LESS_THAN_OR_EQUAL_TO, false); assertQomQueryWithBooleanValue(1, "notion:booleanProperty2", JCR_OPERATOR_LESS_THAN, true); assertQomQueryWithBooleanValue(1, "notion:booleanProperty2", JCR_OPERATOR_LESS_THAN_OR_EQUAL_TO, true); }
@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-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); }
@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 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); }
@Override public void validate( int rowNumber, Row row ) throws RepositoryException { assertValueIsNonNullReference(row, "notion:singleReference"); } }).validate(query, result);
@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(); } }
AccessControlList acl = acl("/parent/child1"); parent.addNode("child2"); session.save(); assertNodesAreFound(queryString, Query.JCR_SQL2, "/parent/child1", "/parent/child2");
@FixFor( "MODE-1057" ) @Test public void shouldAllowEqualityNumericCriteriaOnPropertyDefinedWithNumericPropertyDefinition() throws RepositoryException { assertQueryWithLongValue(13, ""); assertQueryWithLongValue(2, "WHERE [car:userRating] = 3"); assertQueryWithLongValue(1, "WHERE [car:userRating] < 3"); assertQueryWithLongValue(8, "WHERE [car:userRating] > 3"); assertQueryWithLongValue(3, "WHERE [car:userRating] <= 3"); assertQueryWithLongValue(10, "WHERE [car:userRating] >= 3"); assertQueryWithLongValue(9, "WHERE [car:userRating] <> 3"); assertQueryWithLongValue(9, "WHERE [car:userRating] != 3"); }
@BeforeClass public static void beforeAll() throws Exception { String configFileName = JcrQueryManagerTest.class.getSimpleName() + ".json"; beforeAll(configFileName); }
protected void assertValueIsNonNullReference( Row row, String refColumnName ) throws RepositoryException { assertValueIsReference(row, refColumnName, false); }
@FixFor( "MODE-1234" ) @Test public void shouldAllowEqualityCriteriaOnPropertyDefinedWithBooleanPropertyDefinition() throws RepositoryException { assertQueryWithBooleanValue(1, ""); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty] = true"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty] = false"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty] > false"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty] >= false"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty] > true"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty] >= true"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty] < false"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty] <= false"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty] < true"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty] <= true"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty2] = true"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty2] = false"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty2] > false"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty2] >= false"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty2] > true"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty2] >= true"); assertQueryWithBooleanValue(0, "WHERE [notion:booleanProperty2] < false"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty2] <= false"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty2] < true"); assertQueryWithBooleanValue(1, "WHERE [notion:booleanProperty2] <= true"); }
@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); }
@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 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); }
@Override public void validate( int rowNumber, Row row ) throws RepositoryException { assertValueIsNonNullReference(row, "notion:singleReference"); } }).validate(query, result);
@FixFor( "MODE-1057" ) @Test public void shouldAllowEqualityStringCriteriaOnPropertyDefinedWithNumericPropertyDefinition() throws RepositoryException { assertQueryWithLongValue(13, ""); assertQueryWithLongValue(2, "WHERE [car:userRating] = '3'"); assertQueryWithLongValue(1, "WHERE [car:userRating] < '3'"); assertQueryWithLongValue(8, "WHERE [car:userRating] > '3'"); assertQueryWithLongValue(3, "WHERE [car:userRating] <= '3'"); assertQueryWithLongValue(10, "WHERE [car:userRating] >= '3'"); assertQueryWithLongValue(9, "WHERE [car:userRating] <> '3'"); assertQueryWithLongValue(9, "WHERE [car:userRating] != '3'"); }