protected static String[] carColumnNames( String selectorName ) { return prefixEach(carColumnNames(), selectorName + "."); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithChildNodeJoin() throws RepositoryException { String sql = "SELECT car.* 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); assertThat(query, is(notNullValue())); QueryResult result = query.execute(); validateQuery().rowCount(5).hasColumns(carColumnNames("car")).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithDescendantNodeJoin() throws RepositoryException { String sql = "SELECT car.* 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(); validateQuery().rowCount(5).hasColumns(carColumnNames("car")).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindAllCarNodes() throws RepositoryException { String sql = "SELECT * FROM [car:Car]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(13).hasColumns(carColumnNames("car:Car")).validate(query, result); }
@Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryToFindAllCarNodesOrderedByYear() throws RepositoryException { String sql = "SELECT * FROM [car:Car] ORDER BY [car:year]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(13).hasColumns(carColumnNames("car:Car")).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 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); }
@FixFor( "MODE-869" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithSubqueryInCriteria() throws RepositoryException { String sql = "SELECT * FROM [car:Car] WHERE [car:maker] IN (SELECT [car:maker] FROM [car:Car] WHERE [car:year] >= 2008)"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); // there are 13 cars made by makers that made cars in or after 2008 // The only car made before 2008 is Toyota Land Rover, but Toyota also makes the Highlander and Prius in 2008. validateQuery().rowCount(13).hasColumns(carColumnNames("car:Car")).validate(query, result); }
@FixFor( "MODE-1095" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithOrderByUsingPseudoColumnWithSelectStar() throws RepositoryException { String sql = "SELECT * FROM [car:Car] WHERE [car:year] < 2009 ORDER BY [jcr:path]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = carColumnNames("car:Car"); validateQuery().rowCount(13).noWarnings().hasColumns(columnNames).validate(query, result); }
@FixFor( "MODE-1095" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithOrderByUsingColumnWithSelectStar() throws RepositoryException { String sql = "SELECT * FROM [car:Car] WHERE [car:year] < 2009 ORDER BY [car:year]"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); String[] columnNames = carColumnNames("car:Car"); validateQuery().rowCount(13).noWarnings().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 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); }
Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(13).hasColumns(carColumnNames("car:Car")).onEachRow(new Predicate() { @Override public void validate( int rowNumber,
Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(13).hasColumns(carColumnNames("car:Car")).onEachRow(new Predicate() { @Override public void validate( int rowNumber,
Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(13).hasColumns(carColumnNames("car:Car")).onEachRow(new Predicate() { @Override public void validate( int rowNumber,
@FixFor( "MODE-869" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithSubqueryInCriteria2() throws RepositoryException { String sql = "SELECT [car:maker] FROM [car:Car] WHERE PATH() LIKE '%/Hybrid/%'"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(3).hasColumns("car:maker").validate(query, result); sql = "SELECT * FROM [car:Car] WHERE [car:maker] IN ('Toyota','Nissan')"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); // the 4 kinds of cars made by makers that make hybrids validateQuery().rowCount(4).hasColumns(carColumnNames("car:Car")).validate(query, result); sql = "SELECT * FROM [car:Car] WHERE [car:maker] IN (SELECT [car:maker] FROM [car:Car] WHERE PATH() LIKE '%/Hybrid/%')"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); // the 4 kinds of cars made by makers that make hybrids validateQuery().rowCount(4).hasColumns(carColumnNames("car:Car")).validate(query, result); }
@FixFor( "MODE-1825" ) @Test public void shouldBeAbleToExecuteQueryForAllColumns() throws RepositoryException { QueryManager queryManager = session.getWorkspace().getQueryManager(); QueryObjectModelFactory factory = queryManager.getQOMFactory(); Selector car1Selector = factory.selector("car:Car", "car1"); Selector car2Selector = factory.selector("car:Car", "car2"); Join join = factory.join(car1Selector, car2Selector, QueryObjectModelConstants.JCR_JOIN_TYPE_INNER, factory.equiJoinCondition("car1", "car:maker", "car2", "car:maker")); Column[] columns = new Column[] {factory.column("car1", null, null)}; Constraint constraint = factory.comparison(factory.propertyValue("car1", "car:maker"), QueryObjectModelConstants.JCR_OPERATOR_EQUAL_TO, factory.literal(session.getValueFactory().createValue("Toyota"))); Ordering[] orderings = new Ordering[] {factory.descending(factory.propertyValue("car1", "car:year"))}; Query query = factory.createQuery(join, constraint, orderings, columns); QueryResult result = query.execute(); validateQuery().rowCount(9).hasColumns(carColumnNames("car1")).validate(query, result); }
@FixFor( "MODE-2151" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2QueryWithChildCountCriteria() throws RepositoryException { String sql = "SELECT * FROM [car:Car] as car"; Query query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); String[] columnNames = carColumnNames("car"); // No cars have children validateQuery().rowCount(13).hasColumns(columnNames).validate(query, query.execute()); sql = "SELECT * FROM [car:Car] as car WHERE CHILDCOUNT(car) = 0"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); validateQuery().rowCount(13).hasColumns(columnNames).validate(query, query.execute()); sql = "SELECT [jcr:path], [mode:childCount] FROM [nt:unstructured] WHERE CHILDCOUNT() = 4"; query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); validateQuery().rowCount(2).validate(query, query.execute()); }
@FixFor( "MODE-1809" ) @Test public void shouldBeAbleToCreateAndExecuteJcrSql2UnionOfQueriesWithJoins() throws RepositoryException { String sql1 = "SELECT car.* from [car:Car] as car JOIN [nt:unstructured] as category ON ISDESCENDANTNODE(car,category) WHERE NAME(category) LIKE 'Utility'"; String sql2 = "SELECT car.* from [car:Car] as car JOIN [nt:unstructured] as category ON ISDESCENDANTNODE(car,category) WHERE NAME(category) LIKE 'Sports'"; String sql = sql1 + " UNION " + sql2; String[] columnNames = carColumnNames("car"); // print = true; Query query = session.getWorkspace().getQueryManager().createQuery(sql1, Query.JCR_SQL2); QueryResult result = query.execute(); validateQuery().rowCount(5).hasColumns(columnNames).validate(query, result); query = session.getWorkspace().getQueryManager().createQuery(sql2, Query.JCR_SQL2); result = query.execute(); validateQuery().rowCount(2).hasColumns(columnNames).validate(query, result); query = session.getWorkspace().getQueryManager().createQuery(sql, Query.JCR_SQL2); result = query.execute(); validateQuery().rowCount(7).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()); }