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

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 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 testLimitAndOffsetWithExpressions() {
    // limit = 1 + (count(*) / 4), offset = count(*) / 2
    Field<Integer> limit = Function.add(1, Function.divide(
            Query.select(IntegerProperty.countProperty()).from(Employee.TABLE).asFunction(), 4));
    Field<Integer> offset = Function.divide(
            Query.select(IntegerProperty.countProperty()).from(Employee.TABLE).asFunction(), 2);

    Query query = Query.select().orderBy(Employee.NAME.asc()).limit(limit, offset);
    SquidCursor<Employee> cursor = database.query(Employee.class, query);
    try {
        assertEquals(2, cursor.getCount());
        cursor.moveToFirst();
        assertEquals(elmo, new Employee(cursor));
        cursor.moveToNext();
        assertEquals(ernie, new Employee(cursor));
    } 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 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 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 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 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();
    }
}
 

@Override
public long readWholeData() throws SQLException
{
    long start = System.nanoTime();
    SquidCursor<Message> query = myDatabase.query(Message.class, Query.select());
    Message message = new Message();
    while(query.moveToNext())
    {
        message.readPropertiesFromCursor(query);
    }
    return System.nanoTime() - start;
}
 

private void testReusableQueryWithInCriterionInternal(Collection<String> collection, Query query, String... list) {
    collection.clear();
    collection.addAll(Arrays.asList(list));
    SquidCursor<Employee> cursor = database.query(Employee.class, query);
    try {
        assertEquals(collection.size(), cursor.getCount());
        while (cursor.moveToNext()) {
            String name = cursor.get(Employee.NAME);
            assertTrue(collection.contains(name));
        }
    } finally {
        cursor.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 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 testTriggerWithCriterion() {
    final int threshold = 9000;

    // create trigger record_bar_gt_9k after insert on things when NEW.bar > 9000 begin
    //      insert into trigger_testers (value1, value2) values (NEW._id, NEW.bar);
    // end;
    Insert insert = Insert.into(TriggerTester.TABLE).columns(TriggerTester.VALUE_1, TriggerTester.VALUE_2)
            .values(Trigger.newValueOf(Thing.ID), Trigger.newValueOf(Thing.BAR));
    Trigger trigger = Trigger.after("record_bar_gt_9k")
            .insertOn(Thing.TABLE)
            .when(Trigger.newValueOf(Thing.BAR).gt(threshold))
            .perform(insert);
    CompiledStatement compiledTrigger = trigger.compile(database.getCompileContext());

    verifyCompiledSqlArgs(compiledTrigger, 0);

    database.tryExecSql(compiledTrigger.sql, compiledTrigger.sqlArgs);

    // persist new model instances
    Thing thing1 = new Thing().setFoo("small thing").setBar(5);
    assertTrue(database.persist(thing1)); // should not trigger
    Thing thing2 = new Thing().setFoo("big thing").setBar(9001);
    assertTrue(database.persist(thing2)); // should trigger
    Thing thing3 = new Thing().setFoo("bigger thing").setBar(20000);
    assertTrue(database.persist(thing3)); // should trigger

    SquidCursor<TriggerTester> cursor = database.query(TriggerTester.class, Query.select());
    assertTrue(cursor.getCount() > 0);
    try {
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            assertTrue(cursor.get(TriggerTester.VALUE_2) > threshold);
        }
    } 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 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 testBooleanFunctionOnCriterion() {
    BooleanProperty onCriterion = BooleanProperty
            .fromFunction(Function.caseWhen(TestModel.FIRST_NAME.eq("Sam")), "firstNameSam");
    SquidCursor<TestModel> cursor = database
            .query(TestModel.class, Query.select(onCriterion).orderBy(Order.asc(TestModel.ID)));
    try {
        cursor.moveToFirst();
        assertTrue(cursor.get(onCriterion));
        cursor.moveToNext();
        assertFalse(cursor.get(onCriterion));
    } finally {
        cursor.close();
    }
}
 

public void testQueryUsingSubqueryModel() {
    Employee employee1 = new Employee().setName("Big bird");
    Employee employee2 = new Employee().setName("Elmo");
    database.persist(employee1);
    database.persist(employee2);

    ContentProviderQueryBuilder builder = new ContentProviderQueryBuilder(TestSubqueryModel.PROPERTIES,
            TestSubqueryModel.SUBQUERY);
    Query query = builder.build(null, null, null, null);

    SquidCursor<TestSubqueryModel> cursor = null;
    try {
        cursor = database.query(TestSubqueryModel.class, query);
        assertEquals(2, cursor.getCount());

        cursor.moveToFirst();
        TestSubqueryModel model = new TestSubqueryModel(cursor);
        assertEquals(model1.getRowId(), model.getTestModelId().longValue());
        assertEquals(employee1.getRowId(), model.getEmployeeModelId().longValue());
        assertEquals(model1.getFirstName(), model.getTestName());
        assertEquals(employee1.getName(), model.getEmployeeName());
        assertEquals(employee1.getName().toUpperCase(), model.getUppercaseName());

        cursor.moveToNext();
        model.readPropertiesFromCursor(cursor);
        assertEquals(model2.getRowId(), model.getTestModelId().longValue());
        assertEquals(employee2.getRowId(), model.getEmployeeModelId().longValue());
        assertEquals(model2.getFirstName(), model.getTestName());
        assertEquals(employee2.getName(), model.getEmployeeName());
        assertEquals(employee2.getName().toUpperCase(), model.getUppercaseName());
    } finally {
        if (cursor != null) {
            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 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();
    }
}