com.yahoo.squidb.sql.Query Java Examples

private void testJsonInsertReplaceSetInternal(int type, String path, Object value, String expectedResult) {
    String jsonString = "{\"a\":2,\"c\":4}";
    Function<String> func = null;
    switch (type) {
        case 0:
            func = JSONFunctions.jsonInsert(jsonString, path, value);
            break;
        case 1:
            func = JSONFunctions.jsonReplace(jsonString, path, value);
            break;
        case 2:
            func = JSONFunctions.jsonSet(jsonString, path, value);
            break;
        default:
            fail("Unsupported insert/replace/set type " + type);
    }
    String result = database.simpleQueryForString(Query.select(func));
    assertEquals(expectedResult, result);
}
 

public void testJsonGroupArray() {
    testJsonFunction(new Runnable() {
        @Override
        public void run() {
            Thing thing = new Thing();
            for (int i = 0; i < 5; i++) {
                thing.setFoo(Integer.toString(i));
                database.createNew(thing);
            }
            Function<String> groupArray = JSONFunctions.jsonGroupArray(Thing.FOO);
            String result = database.simpleQueryForString(Query.select(groupArray).from(Thing.TABLE));
            try {
                JSONArray resultArray = new JSONArray(result);
                Set<String> resultValues = new HashSet<>();
                for (int i = 0; i < resultArray.length(); i++) {
                    resultValues.add(resultArray.getString(i));
                }
                assertEquals(5, resultValues.size());
                assertTrue(resultValues.containsAll(Arrays.asList("0", "1", "2", "3", "4")));
            } catch (JSONException e) {
                fail("JSONException: " + e.getMessage());
            }
        }
    }, JSONFunctions.JSON1_GROUP_FUNCTIONS_VERSION);
}
 

public void testJsonGroupObject() {
    testJsonFunction(new Runnable() {
        @Override
        public void run() {
            Thing thing = new Thing();
            for (int i = 0; i < 5; i++) {
                thing.setFoo(Integer.toString(i))
                        .setBar(i * 2);
                database.createNew(thing);
            }
            Function<String> groupObject = JSONFunctions.jsonGroupObject(Thing.FOO, Thing.BAR);
            String result = database.simpleQueryForString(Query.select(groupObject).from(Thing.TABLE));
            try {
                JSONObject resultObject = new JSONObject(result);
                assertEquals(5, resultObject.length());
                for (int i = 0; i < 5; i++) {
                    assertEquals(i * 2, resultObject.getInt(Integer.toString(i)));
                }
            } catch (JSONException e) {
                fail("JSONException: " + e.getMessage());
            }
        }
    }, JSONFunctions.JSON1_GROUP_FUNCTIONS_VERSION);
}
 

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 testRawOrderBy() {
    String sortOrder = COL_GIVEN_NAME + " ASC";
    ContentProviderQueryBuilder builder = getBuilder();
    Query query = builder.setDataSource(TestModel.TABLE).build(null, null, null, sortOrder);
    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(model3, buildModelFromCursor(cursor));
        cursor.moveToNext();
        assertEquals(model2, buildModelFromCursor(cursor));
        cursor.moveToNext();
        assertEquals(model1, buildModelFromCursor(cursor));
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}
 

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();
        }
    }
}
 

/**
 * Count the number of rows matching a given {@link Criterion}. Use null to count all rows.
 *
 * @param modelClass the model class corresponding to the table
 * @param criterion the criterion to match
 * @return the number of rows matching the given criterion
 */
public int count(Class<? extends AbstractModel> modelClass, Criterion criterion) {
    Property.IntegerProperty countProperty = Property.IntegerProperty.countProperty();
    Query query = Query.select(countProperty);
    if (criterion != null) {
        query.where(criterion);
    }
    query = inferTableForQuery(modelClass, query);
    CompiledStatement compiled = query.compile(getCompileContext());
    acquireNonExclusiveLock();
    try {
        return (int) getDatabase().simpleQueryForLong(compiled.sql, compiled.sqlArgs);
    } finally {
        releaseNonExclusiveLock();
    }
}
 

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();
    }
}
 

/**
 * Build a {@link Query} combining this object's internal state with the arguments passed. If a
 * {@link ProjectionMap} is set, the projection elements will be evaluated and transformed accordingly. If the
 * sortOrder is null or empty, the default order will be used (if one was set).
 *
 * @param projection the raw column names to be selected
 * @param selection a raw selection string
 * @param selectionArgs array of strings which substitute replaceable arguments in the selection string
 * @param sortOrder a raw ordering clause
 * @return a {@link Query} using the projection, selection, selection args, and sort order
 */
public Query build(String[] projection, String selection, String[] selectionArgs, String sortOrder) {
    Query query = Query.select(computeProjection(projection)).from(dataSource);
    boolean hasUserSelection = !SqlUtils.isEmpty(selection);
    if (hasUserSelection) {
        query.where(Criterion.fromRawSelection(selection, selectionArgs));
    }
    if (!SqlUtils.isEmpty(sortOrder)) {
        query.orderBy(Order.fromExpression(sortOrder));
    } else if (defaultOrder != null && defaultOrder.length > 0) {
        query.orderBy(defaultOrder);
    }
    if (strictMode && hasUserSelection) {
        query.requestValidation();
    }
    return query;
}
 

public void testEnumProperties() {
    final TestEnum enumValue = TestEnum.APPLE;
    final String enumAsString = enumValue.name();
    TestModel model = new TestModel()
            .setFirstName("A")
            .setLastName("Z")
            .setBirthday(System.currentTimeMillis())
            .setSomeEnum(enumValue);

    ValuesStorage setValues = model.getSetValues();
    assertEquals(enumAsString, setValues.get(TestModel.SOME_ENUM.getName()));

    database.persist(model);

    SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select()
            .where(TestModel.SOME_ENUM.eq(TestEnum.APPLE)));
    assertEquals(1, cursor.getCount());
    assertTrue(cursor.moveToFirst());
    assertEquals(enumAsString, cursor.get(TestModel.SOME_ENUM));

    TestModel fromDatabase = new TestModel(cursor);
    assertEquals(enumValue, fromDatabase.getSomeEnum());
}
 

public void testSwapCursorDoesNotCloseOldCursor() {
    TestAdapter adapter = new TestAdapter(new TestModel());

    SquidCursor<TestModel> cursor1 = database.query(TestModel.class, Query.select());
    try {
        adapter.swapCursor(cursor1);
        SquidCursor<TestModel> cursor2 = database.query(TestModel.class, Query.select().where(TestModel.ID.eq(1)));
        try {
            SquidCursor<?> swappedCursor = adapter.swapCursor(cursor2);
            assertFalse(swappedCursor.isClosed());
        } finally {
            adapter.swapCursor(null);
            cursor2.close();
        }
    } finally {
        cursor1.close();
    }
}
 

private void testCursorAdapterInternal(AbstractModel model, LongProperty idColumn, Query query,
        CursorAdapterTest test) {
    TestAdapter adapter;
    if (idColumn == null) {
        adapter = new TestAdapter(model);
    } else {
        adapter = new TestAdapter(model, idColumn);
    }

    SquidCursor<? extends AbstractModel> cursor = database.query(model.getClass(), query);
    try {
        adapter.swapCursor(cursor);
        test.testCursorAdapter(adapter);
    } finally {
        cursor.close();
    }
}
 

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));
        }
    });
}
 

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 Query getTasksWithTagsQuery(Criterion filterBy) {
    if (filterBy == null) {
        return TASKS_WITH_TAGS;
    }
    // Since the query is frozen, this will create a clone with the given filter
    return TASKS_WITH_TAGS.where(filterBy);
}
 

/**
 * Directly analogous to {@link #query(Class, Query)}, but instead of returning a result, this method just logs the
 * output of EXPLAIN QUERY PLAN for the given query. This is method is intended for debugging purposes only.
 */
public void explainQueryPlan(Class<? extends AbstractModel> modelClass, Query query) {
    query = inferTableForQuery(modelClass, query);
    CompiledStatement compiled = query.compile(getCompileContext());
    ICursor cursor = rawQuery("EXPLAIN QUERY PLAN " + compiled.sql, compiled.sqlArgs);
    try {
        Logger.d(Logger.LOG_TAG, "Query plan for: " + compiled.sql);
        SquidUtilities.dumpCursor(cursor, -1);
    } finally {
        cursor.close();
    }
}
 

protected <TYPE extends AbstractModel> SquidCursor<TYPE> fetchFirstItem(Class<TYPE> modelClass, Query query) {
    boolean immutableQuery = query.isImmutable();
    Field<Integer> beforeLimit = query.getLimit();
    SqlTable<?> beforeTable = query.getTable();
    query = query.limit(1); // If argument was frozen, we may get a new object
    SquidCursor<TYPE> cursor = query(modelClass, query);
    if (!immutableQuery) {
        query.from(beforeTable).limit(beforeLimit); // Reset for user
    }
    cursor.moveToFirst();
    return cursor;
}
 

public void testJsonValid() {
    testJsonFunction(new Runnable() {
        @Override
        public void run() {
            Function<Integer> valid = JSONFunctions.jsonValid(" { \"this\" : \"is\", \"a\": [ \"test\" ] } ");
            Function<Integer> invalid = JSONFunctions.jsonValid(" { \"this\" : \"is\", \"a\": [ \"test\"  } ");

            long validResult = database.simpleQueryForLong(Query.select(valid));
            assertEquals(1, validResult);

            long invalidResult = database.simpleQueryForLong(Query.select(invalid));
            assertEquals(0, invalidResult);
        }
    });
}
 

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 testNoIdColumnForNonTableModels() {
    Query query = Query.select(TestViewModel.PROPERTIES);
    testCursorAdapterInternal(new TestViewModel(), null, query, new CursorAdapterTest() {
        @Override
        public void testCursorAdapter(SquidCursorAdapter<AbstractModel> adapter) {
            assertFalse(adapter.hasStableIds());
            assertEquals(0, adapter.getItemId(0));
            assertEquals(0, adapter.getItemId(1));
        }
    });
}
 

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));
        }
    });
}
 

@Override
public Loader<SquidCursor<Task>> onCreateLoader(int id, Bundle args) {
    Query query = mTaskUtils.getOrderedTasksWithTags();

    SquidCursorLoader<Task> loader = new SquidCursorLoader<Task>(this, TasksDatabase.getInstance(), Task.class,
            query);
    loader.setNotificationUri(HelloSquiDBApplication.CONTENT_URI);
    return loader;
}
 

private Query inferTableForQuery(Class<? extends AbstractModel> modelClass, Query query) {
    if (!query.hasTable() && modelClass != null) {
        SqlTable<?> table = getSqlTable(modelClass);
        if (table == null) {
            throw new IllegalArgumentException("Query has no FROM clause and model class "
                    + modelClass.getSimpleName() + " has no associated table");
        }
        query = query.from(table); // If argument was frozen, we may get a new object
    }
    return query;
}
 

public void testViewModelJsonProperty() {
    testWithAllMappers(new Runnable() {
        @Override
        public void run() {
            TestModel model = new TestModel();
            JSONPojo pojo = mockPojo();
            Map<String, Map<String, List<Integer>>> crazyMap = mockComplicatedMap();

            model.setSomePojo(pojo).setComplicatedMap(crazyMap);
            database.persist(model);

            Employee forView = new Employee().setName("A");
            database.persist(forView);

            TestViewModel viewModel = database.fetchByQuery(TestViewModel.class,
                    Query.select().from(TestViewModel.VIEW));

            JSONPojo readPojo = viewModel.getJsonProp();
            assertEquals(pojo.pojoStr, readPojo.pojoStr);
            assertEquals(pojo.pojoInt, readPojo.pojoInt);
            assertEquals(pojo.pojoDouble, readPojo.pojoDouble);
            assertEquals(pojo.pojoList, readPojo.pojoList);

            int currentFromJSONCount = currentMapper.fromJSONCount;
            assertFalse(viewModel.hasTransitory(transitoryKeyForProperty(TestViewModel.CRAZY_MAP)));
            assertEquals(crazyMap, viewModel.getCrazyMap());
            assertEquals(currentFromJSONCount + 1, currentMapper.fromJSONCount);
            // Second call reads from cache; assert that works too
            assertTrue(viewModel.hasTransitory(transitoryKeyForProperty(TestViewModel.CRAZY_MAP)));
            assertEquals(crazyMap, viewModel.getCrazyMap());
            assertEquals(currentFromJSONCount + 1, currentMapper.fromJSONCount);
        }
    });
}
 

public void testSelectAllIncludesRowid() {
    // insert
    TestVirtualModel model = new TestVirtualModel()
            .setTitle("Charlie")
            .setBody("Charlie and the Chocolate Factory");
    assertTrue(database.createNew(model));

    long expectedId = model.getRowId();

    TestVirtualModel fetchedModel = database.fetchByQuery(TestVirtualModel.class, Query.select());
    assertEquals(expectedId, fetchedModel.getRowId());
    assertEquals(model, fetchedModel);
}
 

public void testDropTable() {
    database.tryDropTable(TestModel.TABLE);
    testThrowsRuntimeException(new Runnable() {
        @Override
        public void run() {
            database.query(TestModel.class, Query.select().from(TestModel.TABLE));
        }
    });
}
 

public void testDropView() {
    database.tryDropView(TestViewModel.VIEW);
    testThrowsRuntimeException(new Runnable() {
        @Override
        public void run() {
            database.query(TestViewModel.class, Query.select().from(TestViewModel.VIEW));
        }
    });
}
 

public void testFetchByQueryResetsLimitAndTable() {
    TestModel model1 = new TestModel().setFirstName("Sam1").setLastName("Bosley1");
    TestModel model2 = new TestModel().setFirstName("Sam2").setLastName("Bosley2");
    TestModel model3 = new TestModel().setFirstName("Sam3").setLastName("Bosley3");
    database.persist(model1);
    database.persist(model2);
    database.persist(model3);

    Query query = Query.select().limit(2, 1);
    TestModel fetched = database.fetchByQuery(TestModel.class, query);
    assertEquals(model2.getRowId(), fetched.getRowId());
    assertEquals(Field.field("2"), query.getLimit());
    assertEquals(Field.field("1"), query.getOffset());
    assertEquals(null, query.getTable());
}
 

public void testQueriesWithBooleanPropertiesWork() {
    insertBasicTestModel();

    TestModel model;
    SquidCursor<TestModel> result = database.query(TestModel.class,
            Query.select(TestModel.PROPERTIES).where(TestModel.IS_HAPPY.isTrue()));
    try {
        assertEquals(1, result.getCount());
        result.moveToFirst();
        model = new TestModel(result);
        assertTrue(model.isHappy());

        model.setIsHappy(false);
        database.persist(model);
    } finally {
        result.close();
    }

    result = database.query(TestModel.class,
            Query.select(TestModel.PROPERTIES).where(TestModel.IS_HAPPY.isFalse()));

    try {
        assertEquals(1, result.getCount());
        result.moveToFirst();
        model = new TestModel(result);
        assertFalse(model.isHappy());
    } finally {
        result.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;
}