private void testSubstrInternal(int offset, int length) { Function<String> substr; if (length == 0) { substr = Function.substr(TestModel.LAST_NAME, offset); } else { substr = Function.substr(TestModel.LAST_NAME, offset, length); } StringProperty substrProperty = StringProperty.fromFunction(substr, "substr"); int trueStart = offset - 1; int end = length == 0 ? model1.getLastName().length() : trueStart + length; TestModel model = database.fetch(TestModel.class, model1.getRowId(), substrProperty); String substrLastName = model.get(substrProperty); String expected = model1.getLastName().substring(trueStart, end); assertEquals(expected, substrLastName); }
LongProperty aliasedId = employeesAlias.qualifyField(Employee.ID); LongProperty aliasedManagerId = employeesAlias.qualifyField(Employee.MANAGER_ID); StringProperty subordinates = StringProperty.fromFunction(Function.groupConcat(aliasedId, separator), "subordinates"); Query subquery = Query.select(aliasedManagerId, subordinates).from(employeesAlias)
.elseExpr(PASS) .end(); Property<?> test = StringProperty.fromFunction(caseWhen, "test"); assertExpectedValues(Query.select(test), test, PASS); .when("c", FAIL) .end(); test = StringProperty.fromFunction(caseWhen, "test"); assertExpectedValues(Query.select(test), test, new Object[]{null}); .elseExpr(PASS) .end(); test = StringProperty.fromFunction(caseWhen, "test"); assertExpectedValues(Query.select(test), test, PASS); .elseExpr(FAIL) .end(); test = StringProperty.fromFunction(caseWhen, "test"); assertExpectedValues(Query.select(test), test, PASS); .elseExpr("zero") .end(); test = StringProperty.fromFunction(caseWhen, "test"); assertExpectedValues(Query.select(test).from(TestModel.TABLE).orderBy(TestModel.ID.asc()), test, "negative", "zero", "positive"); .elseExpr("other") .end(); test = StringProperty.fromFunction(caseWhen, "test");
public void testGroupConcat() { setUpAggregateTest(); StringProperty firstNameConcat = StringProperty.fromFunction( Function.groupConcat(TestModel.FIRST_NAME), "fname_concat"); StringProperty firstNameConcatSeparator = StringProperty.fromFunction( Function.groupConcat(TestModel.FIRST_NAME, "|"), "fname_concat_separator"); StringProperty firstNameDistinct = StringProperty.fromFunction( Function.groupConcatDistinct(TestModel.FIRST_NAME), "fname_distinct"); SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(firstNameConcat, firstNameConcatSeparator, firstNameDistinct) .groupBy(TestModel.FIRST_NAME)); try { assertEquals(2, cursor.getCount()); cursor.moveToFirst(); assertEquals("A,A,A", cursor.get(firstNameConcat)); assertEquals("A|A|A", cursor.get(firstNameConcatSeparator)); assertEquals("A", cursor.get(firstNameDistinct)); cursor.moveToNext(); assertEquals("B,B,B", cursor.get(firstNameConcat)); assertEquals("B|B|B", cursor.get(firstNameConcatSeparator)); assertEquals("B", cursor.get(firstNameDistinct)); } finally { cursor.close(); } }
public void testCoalesce() { model2.setFirstName(null); // coalesce should find last name database.persist(model2); model3.setFirstName(null).setLastName(null); // coalesce should find fallback name database.persist(model3); final String FALLBACK_NAME = "Squid"; Function<String> coalesce = Function.coalesce(TestModel.FIRST_NAME, TestModel.LAST_NAME, FALLBACK_NAME); StringProperty modelName = StringProperty.fromFunction(coalesce, "name"); // select *, coalesce(firstName, lastName, 'Squid') as name from testModel order by _id asc; SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(TestModel.PROPERTIES) .selectMore(modelName).orderBy(TestModel.ID.asc())); assertEquals(3, cursor.getCount()); try { cursor.moveToFirst(); assertEquals(model1.getFirstName(), cursor.get(modelName)); cursor.moveToNext(); assertEquals(model2.getLastName(), cursor.get(modelName)); cursor.moveToNext(); assertEquals(FALLBACK_NAME, cursor.get(modelName)); } finally { cursor.close(); } }
public void testUpper() { StringProperty upper = StringProperty.fromFunction(Function.upper(TestModel.FIRST_NAME), "upper"); TestModel fetch = database.fetch(TestModel.class, model1.getRowId(), upper); assertEquals("SAM", fetch.get(upper)); }
public void testLower() { StringProperty lower = StringProperty.fromFunction(Function.lower(TestModel.LAST_NAME), "lower"); TestModel fetch = database.fetch(TestModel.class, model3.getRowId(), lower); assertEquals("koren", fetch.get(lower)); }
public void testUpperOfLower() { StringProperty upperOfLower = StringProperty.fromFunction(Function.upper(Function.lower(TestModel.LAST_NAME)), "upperOfLower"); TestModel fetch = database.fetch(TestModel.class, model1.getRowId(), upperOfLower); assertEquals("BOSLEY", fetch.get(upperOfLower)); }
public void testStrConcat() { Function<String> concat = Function.strConcat(TestModel.FIRST_NAME, TestModel.LAST_NAME); StringProperty concatProperty = StringProperty.fromFunction(concat, "concat"); TestModel model = database.fetch(TestModel.class, model1.getRowId(), concatProperty); assertEquals("SamBosley", model.get(concatProperty)); concat = Function.strConcat(TestModel.FIRST_NAME, " ", TestModel.LAST_NAME); concatProperty = StringProperty.fromFunction(concat, "concat"); model = database.fetch(TestModel.class, model1.getRowId(), concatProperty); assertEquals("Sam Bosley", model.get(concatProperty)); }
public void testLowerOfUpper() { StringProperty lowerOfUpper = StringProperty.fromFunction(Function.lower(Function.upper(TestModel.LAST_NAME)), "lowerOfUpper"); TestModel fetch = database.fetch(TestModel.class, model1.getRowId(), lowerOfUpper); assertEquals("bosley", fetch.get(lowerOfUpper)); }