SquidCursor<Thing> cursor = database.query(Thing.class, Query.select(Thing.BLOB).orderBy(Thing.ID.asc())); try { assertEquals(randomBlobs.size(), cursor.getCount());
LongProperty managerId = managerTable.qualifyField(Employee.ID); Join join = Join.inner(managerTable, Employee.MANAGER_ID.eq(managerId)); Query query = Query.select(employeeName, managerName).from(Employee.TABLE).join(join).orderBy(managerId.asc());
private void testBetween(List<Long> expectedIds, boolean useNotBetween) { SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID).where(useNotBetween ? Employee.ID.notBetween(2, 5) : Employee.ID.between(2, 5)).orderBy(Employee.ID.asc())); try { assertEquals(expectedIds.size(), cursor.getCount()); for (Long id : expectedIds) { cursor.moveToNext(); assertEquals(id.longValue(), cursor.get(Employee.ID).longValue()); } } finally { cursor.close(); } }
public void testSubqueryJoin() { StringProperty managerName = Employee.NAME.as("managerName"); Query query = Query .fromSubquery(Query.select(Employee.MANAGER_ID).from(Employee.TABLE).groupBy(Employee.MANAGER_ID), "subquery"); query.selectMore(managerName); query.join(Join.inner(Employee.TABLE, query.getTable().qualifyField(Employee.MANAGER_ID).eq(Employee.ID))) .orderBy(Employee.MANAGER_ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals("bigBird", cursor.get(managerName)); cursor.moveToNext(); assertEquals("cookieMonster", cursor.get(managerName)); cursor.moveToNext(); assertEquals("bert", cursor.get(managerName)); } 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 testUnionAll() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .unionAll(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(4, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }
public void testLikeSubquery() { insertBasicTestModel("Sam 1", "A", System.currentTimeMillis() - 5); insertBasicTestModel("Sam 2", "B", System.currentTimeMillis() - 4); insertBasicTestModel("Sam 3", "C", System.currentTimeMillis() - 3); insertBasicTestModel("Bla 1", "D", System.currentTimeMillis() - 2); insertBasicTestModel("Bla 2", "E", System.currentTimeMillis() - 1); Function<String> substr = Function.substr(TestModel.FIRST_NAME, 1, 3); Function<String> strConcat = Function.strConcat(substr, "%"); Query likeFirstName = Query.select().where(TestModel.FIRST_NAME.like( Query.select(strConcat).from(TestModel.TABLE).where(TestModel.ID.eq(1)))).orderBy(TestModel.ID.asc()); SquidCursor<TestModel> cursor = database.query(TestModel.class, likeFirstName); try { assertEquals(3, cursor.getCount()); int index = 1; while (cursor.moveToNext()) { assertEquals("Sam " + index, cursor.get(TestModel.FIRST_NAME)); index++; } } finally { cursor.close(); } }
public void testReverseOrder() { long max = database.countAll(Employee.class); SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID).orderBy(Employee.ID.asc().reverse())); try { assertEquals(max, cursor.getCount()); assertTrue(max > 0); while (cursor.moveToNext()) { long nextId = cursor.get(Employee.ID); if (nextId > max) { fail("IDs not in reverse order"); } max = nextId; } } finally { cursor.close(); } }
public void testSelectFromView() { View view = View.fromQuery(Query.select(Employee.PROPERTIES) .from(Employee.TABLE).where(Employee.MANAGER_ID.eq(bigBird.getRowId())), "bigBirdsEmployees"); database.tryCreateView(view); Query fromView = Query.fromView(view).orderBy(view.qualifyField(Employee.ID).asc()); SquidCursor<Employee> cursor = database.query(Employee.class, fromView); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals("cookieMonster", cursor.get(Employee.NAME)); cursor.moveToNext(); assertEquals("elmo", cursor.get(Employee.NAME)); cursor.moveToNext(); assertEquals("oscar", cursor.get(Employee.NAME)); } finally { cursor.close(); } }
public void testUnion() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .union(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); cursor.moveToNext(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }
public void testSelectDistinct() { Query query = Query.selectDistinct(Employee.MANAGER_ID).orderBy(Employee.MANAGER_ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(4, cursor.getCount()); cursor.moveToFirst(); assertEquals(Long.valueOf(0), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(1), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(2), cursor.get(Employee.MANAGER_ID)); cursor.moveToNext(); assertEquals(Long.valueOf(5), cursor.get(Employee.MANAGER_ID)); } finally { cursor.close(); } }
public void testExcept() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .except(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(2, cursor.getCount()); cursor.moveToFirst(); assertEquals(elmo, new Employee(cursor)); cursor.moveToNext(); assertEquals(oscar, new Employee(cursor)); } finally { cursor.close(); } }
private void testRecyclerAdapterInternal(LongProperty idProperty, RecyclerAdapterTest test) { Query query = Query.select(TestModel.PROPERTIES) .orderBy(TestModel.BIRTHDAY.asc()) .limit(2); if (idProperty != null) { query.selectMore(idProperty); } SquidCursor<TestModel> cursor = database.query(TestModel.class, query); TestRecyclerAdapter adapter = new TestRecyclerAdapter(idProperty); adapter.changeCursor(cursor); try { test.testRecyclerAdapter(adapter); } finally { cursor.close(); } }
private void testGlob(List<Employee> expected, boolean useNotGlob) { SquidCursor<Employee> cursor = database.query(Employee.class, Query.select(Employee.ID, Employee.NAME).where(useNotGlob ? Employee.NAME.notGlob("b*") : Employee.NAME.glob("b*")) .orderBy(Employee.ID.asc())); try { assertEquals(expected.size(), cursor.getCount()); for (Employee e : expected) { cursor.moveToNext(); assertEquals(e.getRowId(), cursor.get(Employee.ID).longValue()); assertEquals(e.getName(), cursor.get(Employee.NAME)); } } finally { cursor.close(); } }
public void testCustomIdColumn() { Function<Long> idSquared = Function.rawFunction("_id * _id"); LongProperty idSquaredProperty = LongProperty.fromFunction(idSquared, "idSquared"); Query query = Query.select(TestModel.PROPERTIES).selectMore(idSquaredProperty).orderBy(TestModel.ID.asc()); testCursorAdapterInternal(new TestModel(), idSquaredProperty, query, new CursorAdapterTest() { @Override public void testCursorAdapter(SquidCursorAdapter<AbstractModel> adapter) { assertTrue(adapter.hasStableIds()); assertEquals(1, adapter.getItemId(0)); assertEquals(4, adapter.getItemId(1)); } }); }
public Query getOrderedTasksWithTags() { Function<Long> unixNow = Function.multiply(1000, Function.functionWithArguments("strftime", "%s", "now")); Function<Long> sinceCompletion = Function.subtract(unixNow, Task.COMPLETION_DATE); return getTasksWithTagsQuery(Task.COMPLETION_DATE.eq(0) .or(sinceCompletion.lt(60000 * 5))) .orderBy(Function.caseWhen(Task.DUE_DATE.neq(0)).desc(), Task.DUE_DATE.asc()); }
public void testIntersect() { Query query = Query.select().from(Employee.TABLE).where(Employee.MANAGER_ID.eq(1)) .intersect(Query.select().from(Employee.TABLE).where(Employee.ID.eq(2))) .orderBy(Employee.ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(1, cursor.getCount()); cursor.moveToFirst(); assertEquals(cookieMonster, new Employee(cursor)); } finally { cursor.close(); } }
public void testReusableModel() { Query query = Query.select(TestModel.PROPERTIES).orderBy(TestModel.ID.asc()); testCursorAdapterInternal(new TestModel(), null, query, new CursorAdapterTest() { @Override public void testCursorAdapter(SquidCursorAdapter<AbstractModel> adapter) { AbstractModel first = adapter.getItem(0); AbstractModel second = adapter.getItem(1); assertEquals(first, second); } }); }
public void testIdColumnForTableModels() { Query query = Query.select(TestModel.PROPERTIES).orderBy(TestModel.ID.asc()); testCursorAdapterInternal(new TestModel(), null, query, new CursorAdapterTest() { @Override public void testCursorAdapter(SquidCursorAdapter<AbstractModel> adapter) { assertTrue(adapter.hasStableIds()); assertEquals(1, adapter.getItemId(0)); assertEquals(2, adapter.getItemId(1)); } }); }