Java Code Examples for com.yahoo.squidb.data.SquidCursor#moveToFirst()

public void testDefaultOrderBy() {
    ContentProviderQueryBuilder builder = getBuilder();
    builder.setDefaultOrder(TestModel.LUCKY_NUMBER.desc());
    Query query = builder.setDataSource(TestModel.TABLE).build(null, null, null, null);
    CompiledStatement compiled = query.compile(database.getCompileContext());
    verifyCompiledSqlArgs(compiled, 0);

    SquidCursor<TestModel> cursor = null;
    try {
        cursor = database.query(TestModel.class, query);
        assertEquals(3, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(model2, buildModelFromCursor(cursor));
        cursor.moveToNext();
        assertEquals(model1, buildModelFromCursor(cursor));
        cursor.moveToNext();
        assertEquals(model3, buildModelFromCursor(cursor));
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}
 

public void testCriterionWithNestedSelect() {
    TestModel modelOne = new TestModel().setFirstName("Sam").setLastName("Bosley");
    TestModel modelTwo = new TestModel().setFirstName("Kevin").setLastName("Lim");
    TestModel modelThree = new TestModel().setFirstName("Jonathan").setLastName("Koren");

    database.persist(modelOne);
    database.persist(modelTwo);
    database.persist(modelThree);
    assertEquals(3, database.countAll(TestModel.class));

    database.deleteWhere(TestModel.class,
            TestModel.ID.lt(Query.select(Function.max(TestModel.ID)).from(TestModel.TABLE)));
    SquidCursor<TestModel> cursor = null;
    try {
        cursor = database.query(TestModel.class, Query.select());
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(3, cursor.get(TestModel.ID).longValue());
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}
 

public void testAggregateCount() {
    TestModel model1 = insertBasicTestModel();
    TestModel model2 = new TestModel().setFirstName(model1.getFirstName()).setLastName("Smith");
    database.persist(model2);

    IntegerProperty groupCount = IntegerProperty.countProperty(TestModel.FIRST_NAME, false);
    Query query = Query.select(TestModel.PROPERTIES).selectMore(groupCount).groupBy(TestModel.FIRST_NAME);
    SquidCursor<TestModel> groupedCursor = database.query(TestModel.class, query);
    try {
        groupedCursor.moveToFirst();
        assertEquals(1, groupedCursor.getCount());
        assertEquals(2, groupedCursor.get(groupCount).intValue());
    } finally {
        groupedCursor.close();
    }
}
 

public void testAtomicIntegers() {
    AtomicInteger id = new AtomicInteger(1);
    Query query = Query.select(Employee.ID).where(Employee.ID.eq(id));

    SquidCursor<Employee> cursor = database.query(Employee.class, query);
    try {
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(1, cursor.get(Employee.ID).longValue());
    } finally {
        cursor.close();
    }
    id.set(2);
    cursor = database.query(Employee.class, query);
    try {
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(2, cursor.get(Employee.ID).longValue());
    } 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 testRawSelection() {
    String selection = COL_LUCKY_NUMBER + " > ? AND " + COL_IS_HAPPY + " != ?";
    String[] selectionArgs = new String[]{"50", "0"};
    ContentProviderQueryBuilder builder = getBuilder();
    Query query = builder.setDataSource(TestModel.TABLE).build(null, selection, selectionArgs, null);
    CompiledStatement compiled = query.compile(database.getCompileContext());
    verifyCompiledSqlArgs(compiled, 2, "50", "0");

    SquidCursor<TestModel> cursor = null;
    try {
        cursor = database.query(TestModel.class, query);
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(model2, buildModelFromCursor(cursor));
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}
 

public void testMin() {
    LongProperty minId = LongProperty.fromFunction(Function.min(TestModel.ID), "minId");
    SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(minId));
    try {
        cursor.moveToFirst();
        assertEquals(model1.getRowId(), cursor.get(minId).longValue());
    } finally {
        cursor.close();
    }
}
 

public void testJoinOnLiteralValue() {
    TestModel modelOne = new TestModel().setFirstName("Sam").setLastName("Bosley");
    TestModel modelTwo = new TestModel().setFirstName("Kevin").setLastName("Lim");
    TestModel modelThree = new TestModel().setFirstName("Jonathan").setLastName("Koren");

    Thing thingOne = new Thing().setFoo("Thing1").setBar(5);
    Thing thingTwo = new Thing().setFoo("Thing2").setBar(-1);
    Thing thingThree = new Thing().setFoo("Thing3").setBar(100);

    database.persist(modelOne);
    database.persist(modelTwo);
    database.persist(modelThree);
    database.persist(thingOne);
    database.persist(thingTwo);
    database.persist(thingThree);

    Query query = Query.select(TestModel.FIRST_NAME, TestModel.LAST_NAME).selectMore(Thing.FOO, Thing.BAR)
            .from(TestModel.TABLE)
            .join(Join.inner(Thing.TABLE, Thing.BAR.gt(0)));
    SquidCursor<TestModel> cursor = database.query(TestModel.class, query);
    try {
        assertEquals(6, cursor.getCount());
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            assertTrue(cursor.get(Thing.BAR) > 0);
        }
    } finally {
        cursor.close();
    }
}
 

public void testOrderByField() {
    TestModel one = new TestModel().setFirstName("Sam").setLastName("Bosley");
    TestModel two = new TestModel().setFirstName("Kevin").setLastName("Lim");
    TestModel three = new TestModel().setFirstName("Jonathan").setLastName("Koren");

    database.persist(one);
    database.persist(two);
    database.persist(three);

    String[] nameOrder = new String[]{"Kevin", "Sam", "Jonathan"};
    SquidCursor<TestModel> nameOrderCursor = database.query(TestModel.class, Query.select(TestModel.PROPERTIES)
            .orderBy(Order.byArray(TestModel.FIRST_NAME, nameOrder)));
    try {
        assertEquals(3, nameOrderCursor.getCount());
        for (nameOrderCursor.moveToFirst(); !nameOrderCursor.isAfterLast(); nameOrderCursor.moveToNext()) {
            assertEquals(nameOrder[nameOrderCursor.getPosition()], nameOrderCursor.get(TestModel.FIRST_NAME));
        }
    } finally {
        nameOrderCursor.close();
    }

    Long[] idOrder = new Long[]{3L, 1L, 2L};
    SquidCursor<TestModel> idOrderCursor = database.query(TestModel.class, Query.select(TestModel.PROPERTIES)
            .orderBy(Order.byArray(TestModel.ID, idOrder)));
    try {
        assertEquals(3, idOrderCursor.getCount());
        for (idOrderCursor.moveToFirst(); !idOrderCursor.isAfterLast(); idOrderCursor.moveToNext()) {
            assertEquals(idOrder[idOrderCursor.getPosition()], idOrderCursor.get(TestModel.ID));
        }
    } finally {
        idOrderCursor.close();
    }
}
 

public void testAttachDetach() {
    String attachedAs = database2.attachDatabase(database);
    Insert insert = Insert.into(TestModel.TABLE).columns(TestModel.PROPERTIES)
            .select(Query.select(TestModel.PROPERTIES)
                    .from(TestModel.TABLE.qualifiedFromDatabase(attachedAs)));
    Insert insertVirtual = Insert.into(TestVirtualModel.TABLE).columns(TestVirtualModel.PROPERTIES)
            .select(Query.select(TestVirtualModel.PROPERTIES)
                    .from(TestVirtualModel.TABLE.qualifiedFromDatabase(attachedAs)));
    database2.beginTransaction();
    try {
        database2.tryExecStatement(insert);
        database2.tryExecStatement(insertVirtual);
        database2.setTransactionSuccessful();
    } finally {
        database2.endTransaction();
        database2.detachDatabase(database);
    }

    SquidCursor<TestModel> cursor = database2
            .query(TestModel.class,
                    Query.select(TestModel.ID, TestModel.FIRST_NAME, TestModel.LAST_NAME, TestModel.BIRTHDAY));
    try {
        assertEquals(3, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(model1, new TestModel(cursor));
        cursor.moveToNext();
        assertEquals(model2, new TestModel(cursor));
        cursor.moveToNext();
        assertEquals(model3, new TestModel(cursor));
    } finally {
        cursor.close();
    }
    assertEquals(virtualModel,
            database2.fetch(TestVirtualModel.class, virtualModel.getRowId(), TestVirtualModel.PROPERTIES));

    assertFalse(database2.tryExecStatement(insert)); // Should fail after detach
}
 

public void testInsertWithDefaultValues() {
    // insert into things default values;
    Insert insert = Insert.into(Thing.TABLE).defaultValues();
    CompiledStatement compiled = insert.compile(database.getCompileContext());

    verifyCompiledSqlArgs(compiled, 0);

    int rowsBeforeInsert = database.countAll(Thing.class);
    assertEquals(3, database.insert(insert));
    int rowsAfterInsert = database.countAll(Thing.class);

    assertEquals(rowsBeforeInsert + 1, rowsAfterInsert);

    // get the newest
    Thing newThing = null;
    SquidCursor<Thing> cursor = null;
    try {
        cursor = database.query(Thing.class, Query.select(Thing.PROPERTIES).orderBy(Order.desc(Thing.ID)).limit(1));
        if (cursor.moveToFirst()) {
            newThing = new Thing(cursor);
        }
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }

    assertNotNull(newThing);
    assertEquals(Thing.DEFAULT_FOO, newThing.getFoo());
    assertEquals(Thing.DEFAULT_BAR, newThing.getBar().intValue());
    assertEquals(Thing.DEFAULT_IS_ALIVE, newThing.isAlive().booleanValue());
}
 

public void testSelectLiteral() {
    StringProperty literal = StringProperty.literal("literal", "name");
    LongProperty literalLong = LongProperty.literal(12, "twelve");
    SquidCursor<?> c = database.query(null, Query.select(literal, literalLong));
    try {
        assertEquals(1, c.getCount());
        c.moveToFirst();
        assertEquals("literal", c.get(literal));
        assertEquals(12, c.get(literalLong).longValue());
    } finally {
        if (c != null) {
            c.close();
        }
    }
}
 

public void testMax() {
    LongProperty maxId = LongProperty.fromFunction(Function.max(TestModel.ID), "maxId");
    SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(maxId));
    try {
        cursor.moveToFirst();
        assertEquals(model3.getRowId(), cursor.get(maxId).longValue());
    } finally {
        cursor.close();
    }
}
 

private <T extends Number> void testMath(Property<T> property, T expectedValue) {
    SquidCursor<?> cursor = database.query(null, Query.select(property));
    try {
        cursor.moveToFirst();
        T value = cursor.get(property);
        assertEquals(expectedValue, value);
    } finally {
        cursor.close();
    }
}
 

public void testOrderByFunction() {
    AtomicReference<String> name = new AtomicReference<>("Sam");
    Function<Integer> caseWhen = Function.caseWhen(TestModel.FIRST_NAME.eq(name));
    BooleanProperty nameMatches = BooleanProperty.fromFunction(caseWhen, "nameMatches");

    Query query = Query.select(TestModel.ID, TestModel.FIRST_NAME, nameMatches).orderBy(nameMatches.asc());
    SquidCursor<TestModel> cursor = database.query(TestModel.class, query);
    try {
        assertEquals(3, cursor.getCount());
        cursor.moveToFirst();
        assertFalse(cursor.get(nameMatches));
        cursor.moveToNext();
        assertFalse(cursor.get(nameMatches));
        cursor.moveToNext();
        assertTrue(cursor.get(nameMatches));
        assertEquals(name.get(), cursor.get(TestModel.FIRST_NAME));
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }

    name.set("Kevin");
    query = Query.select(TestModel.ID, TestModel.FIRST_NAME, nameMatches).orderBy(nameMatches.desc());
    cursor = database.query(TestModel.class, query);
    try {
        assertEquals(3, cursor.getCount());
        cursor.moveToFirst();
        assertTrue(cursor.get(nameMatches));
        assertEquals(name.get(), cursor.get(TestModel.FIRST_NAME));
        cursor.moveToNext();
        assertFalse(cursor.get(nameMatches));
        cursor.moveToNext();
        assertFalse(cursor.get(nameMatches));
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}
 

public void testBoundArgumentsWorkInHavingClause() {
    Query query = Query.select(Employee.PROPERTIES)
            .groupBy(Employee.MANAGER_ID)
            .having(Function.count(Employee.MANAGER_ID).gt(2));
    SquidCursor<Employee> cursor = database.query(Employee.class, query);
    try {
        assertEquals(1, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(bigBird.getRowId(), cursor.get(Employee.MANAGER_ID).longValue());
    } 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();
    }
}
 

public void testSelectFunction() {
    Function<String> upper = Function.upper(TestModel.LAST_NAME);
    SquidCursor<TestModel> cursor = database
            .query(TestModel.class, Query.select(TestModel.PROPERTIES).selectMore(upper));
    try {
        cursor.moveToFirst();
        new TestModel(cursor); // Should succeed without throwing an exception
    } finally {
        cursor.close();
    }
}
 

public void testJoinOnSameTableUsingAlias() {
    // check precondition
    int rowsWithManager = database.count(Employee.class, Employee.MANAGER_ID.gt(0));
    assertEquals(5, rowsWithManager);

    List<String> resultEmployees = new ArrayList<>(5);
    List<String> resultManagers = new ArrayList<>(5);
    resultEmployees.add(cookieMonster.getName());
    resultManagers.add(bigBird.getName());
    resultEmployees.add(elmo.getName());
    resultManagers.add(bigBird.getName());
    resultEmployees.add(oscar.getName());
    resultManagers.add(bigBird.getName());
    resultEmployees.add(bert.getName());
    resultManagers.add(cookieMonster.getName());
    resultEmployees.add(ernie.getName());
    resultManagers.add(bert.getName());

    // select employees.name as employeeName, managers.name as managerName from employees inner join employees as
    // managers on (employees.managerId = managers._id) order by managers._id ASC;
    Table managerTable = Employee.TABLE.as("managers");
    StringProperty employeeName = Employee.NAME.as("employeeName");
    StringProperty managerName = Employee.NAME.as(managerTable, "managerName");
    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());

    SquidCursor<Employee> cursor = database.query(Employee.class, query);
    try {
        assertEquals(rowsWithManager, cursor.getCount());
        int index = 0;
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext(), index++) {
            String eName = cursor.get(employeeName);
            String mName = cursor.get(managerName);
            assertEquals(resultEmployees.get(index), eName);
            assertEquals(resultManagers.get(index), mName);
        }
    } finally {
        cursor.close();
    }
}
 

private void testJoinWithUsingClauseInternal(boolean leftJoin) {
    final String separator = "|";
    final Map<Long, String> expectedResults = new HashMap<>();
    expectedResults.put(cookieMonster.getRowId(), "2|3|4");
    expectedResults.put(elmo.getRowId(), "2|3|4");
    expectedResults.put(oscar.getRowId(), "2|3|4");
    if (!leftJoin) {
        expectedResults.put(bigBird.getRowId(), "1");
        expectedResults.put(bert.getRowId(), "5");
        expectedResults.put(ernie.getRowId(), "6");
    }

    /*
     * select employees._id, employees.name, employees.managerId, subTable.subordinates as coworkers from employees
     * join (select e.managerId, group_concat(e._id, "|") as subordinates from employees as e group by e.managerId)
     * as subTable using (managerId);
     */

    Table employeesAlias = Employee.TABLE.as("e");
    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)
            .groupBy(aliasedManagerId);

    if (leftJoin) {
        subquery.having(Function.count().gt(1));
    }

    SqlTable<?> subTable = subquery.as("subTable");
    StringProperty coworkers = subTable.qualifyField(subordinates);
    Query query = Query.select(Employee.PROPERTIES).selectMore(coworkers)
            .from(Employee.TABLE);
    if (leftJoin) {
        query.leftJoin(subTable, Employee.MANAGER_ID);
    } else {
        query.innerJoin(subTable, Employee.MANAGER_ID);
    }

    SquidCursor<Employee> cursor = database.query(Employee.class, query);

    try {
        assertEquals(6, cursor.getCount());
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            long id = cursor.get(Employee.ID);
            String coworkersList = cursor.get(coworkers);
            assertEquals(expectedResults.get(id), coworkersList);
        }
    } finally {
        cursor.close();
    }
}