ActivityDiaryContentProvider extends ContentProvider { @Nullable @Override public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) { SQLiteQueryBuilder qBuilder = new SQLiteQueryBuilder(); boolean useRawQuery = false; String grouping = null; String sql = ""; Cursor c; int id = 0; if(selection == null){ selection = ""; } MatrixCursor result = new MatrixCursor(new String[]{ BaseColumns._ID, SUGGEST_COLUMN_TEXT_1, SUGGEST_COLUMN_ICON_1, SUGGEST_COLUMN_INTENT_ACTION, SUGGEST_COLUMN_INTENT_DATA, SUGGEST_COLUMN_QUERY }); if (sUriMatcher.match(uri) < 1) { return null; } switch (sUriMatcher.match(uri)) { case activities_ID: case conditions_ID: case diary_ID: case diary_image_ID: case diary_location_ID: if (selection != null) { selection = selection + " AND "; } else { selection = ""; } selection = selection + "_id=" + uri.getLastPathSegment(); default: } switch (sUriMatcher.match(uri)) { case activities_ID: case activities: int n; boolean hasDiaryJoin = false; String tables = ActivityDiaryContract.DiaryActivity.TABLE_NAME; if (TextUtils.isEmpty(sortOrder)) { sortOrder = ActivityDiaryContract.DiaryActivity.SORT_ORDER_DEFAULT; } n = 0; while(n < projection.length){ if(ActivityDiaryContract.DiaryActivity.X_AVG_DURATION.equals(projection[n])){ projection[n] = "AVG(" + ActivityDiaryContract.Diary.END + " - " + ActivityDiaryContract.Diary.START + ") AS " + ActivityDiaryContract.DiaryActivity.X_AVG_DURATION; hasDiaryJoin = true; } if(ActivityDiaryContract.DiaryActivity.X_START_OF_LAST.equals(projection[n])){ projection[n] = "xx_start AS " + ActivityDiaryContract.DiaryActivity.X_START_OF_LAST; hasDiaryJoin = true; } n++; } if(hasDiaryJoin){ n = 0; while(n < projection.length) { if(ActivityDiaryContract.DiaryActivity._ID.equals(projection[n])){ projection[n] = ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID; } n++; } selection = selection.replaceAll(" " + ActivityDiaryContract.DiaryActivity._ID, " " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID); selection = selection.replaceAll(ActivityDiaryContract.DiaryActivity._DELETED, ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._DELETED); tables = tables + ", " + ActivityDiaryContract.Diary.TABLE_NAME; tables = tables + ", (SELECT xx_ref, " + ActivityDiaryContract.Diary.START + " as xx_start FROM " + ActivityDiaryContract.Diary.TABLE_NAME + "," + "(SELECT " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " AS xx_ref," + " MAX(" + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.END + ") AS xx_ref_end" + " FROM " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + ", " + ActivityDiaryContract.Diary.TABLE_NAME + " WHERE " + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.ACT_ID + " = " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " GROUP BY " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + ")" + " WHERE " + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.END + " = xx_ref_end" + ")" ; selection = selection + " AND " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " = " + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.ACT_ID + " AND " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " = xx_ref"; grouping = ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID; } qBuilder.setTables(tables); break; case diary_image_ID: case diary_image: qBuilder.setTables(ActivityDiaryContract.DiaryImage.TABLE_NAME); if (TextUtils.isEmpty(sortOrder)) sortOrder = ActivityDiaryContract.DiaryImage.SORT_ORDER_DEFAULT; break; case diary_location_ID: case diary_location: qBuilder.setTables(ActivityDiaryContract.DiaryLocation.TABLE_NAME); if (TextUtils.isEmpty(sortOrder)) sortOrder = ActivityDiaryContract.DiaryLocation.SORT_ORDER_DEFAULT; break; case diary_ID: case diary: qBuilder.setTables(ActivityDiaryContract.Diary.TABLE_NAME + " INNER JOIN " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + " ON " + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.ACT_ID + " = " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID ); if (TextUtils.isEmpty(sortOrder)) sortOrder = ActivityDiaryContract.Diary.SORT_ORDER_DEFAULT; break; case diary_stats: useRawQuery = true; List<String> l = uri.getPathSegments(); String start; String end; if(l.size() == 3){ start = l.get(1); end = l.get(2); }else{ start = "0"; end = "6156000000000"; } String subselect = "SELECT SUM(MIN(IFNULL(" + ActivityDiaryContract.Diary.END + ",strftime('%s','now') * 1000), " + end + ") - " + "MAX(" + ActivityDiaryContract.Diary.START + ", " + start + ")) from " + ActivityDiaryContract.Diary.TABLE_NAME + " WHERE ((start >= " + start + " AND start < " + end + ") OR (end > " + start + " AND end <= " + end + ") OR (start < " + start + " AND end > " + end + "))"; if (selection != null && selection.length() > 0) { subselect += " AND (" + selection + ")"; } sql = "SELECT " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity.NAME + " as " + ActivityDiaryContract.DiaryStats.NAME + ", " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity.COLOR + " as " + ActivityDiaryContract.DiaryStats.COLOR + ", SUM(MIN(IFNULL(" + ActivityDiaryContract.Diary.END + ",strftime('%s','now') * 1000), " + end + ") - MAX(" + start + ", " + ActivityDiaryContract.Diary.START + ")) as " + ActivityDiaryContract.DiaryStats.DURATION + ", (SUM(MIN(IFNULL(" + ActivityDiaryContract.Diary.END + ",strftime('%s','now') * 1000), " + end + ") - MAX(" + start + ", " + ActivityDiaryContract.Diary.START + ")) * 100.0 " + "/ (" + subselect + ")) as " + ActivityDiaryContract.DiaryStats.PORTION + " FROM " + ActivityDiaryContract.Diary.TABLE_NAME + ", " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + " WHERE " + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.ACT_ID + " = " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " AND" + " ((start >= " + start + " AND start < " + end + ") OR (end > " + start + " AND end <= " + end + ") OR (start < " + start + " AND end > " + end + "))" ; if(selection != null && selection.length() > 0) { sql += " AND (" + selection + ")"; String[] newArgs = Arrays.copyOf(selectionArgs, selectionArgs.length * 2); System.arraycopy(selectionArgs, 0, newArgs, selectionArgs.length, selectionArgs.length); selectionArgs = newArgs; } sql += " GROUP BY " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID; if (sortOrder != null && sortOrder.length() > 0) { sql += " ORDER by " + sortOrder; } break; case search_recent_suggestion: sql = "SELECT " + ActivityDiaryContract.DiarySearchSuggestion.SUGGESTION + ", " + ActivityDiaryContract.DiarySearchSuggestion.ACTION + " FROM " + ActivityDiaryContract.DiarySearchSuggestion.TABLE_NAME + " ORDER BY " + ActivityDiaryContract.DiarySearchSuggestion._ID + " DESC"; c = mOpenHelper.getReadableDatabase().rawQuery(sql, selectionArgs); if (c != null && c.moveToFirst()) { do { Object icon = null; String action = c.getString(1); String q = c.getString(0); if(action.equals(SEARCH_ACTIVITY)) { int i = Integer.parseInt(q); q = ActivityHelper.helper.activityWithId(i).getName(); }else if(action.equals(SEARCH_NOTE)){ q = getContext().getResources().getString(R.string.search_notes, q); icon = R.drawable.ic_search; }else if(action.equals(SEARCH_GLOBAL) || action.equals(Intent.ACTION_SEARCH)){ q = getContext().getResources().getString(R.string.search_diary, q); icon = R.drawable.ic_search; }else if(action.equals(SEARCH_DATE)){ q = getContext().getResources().getString(R.string.search_date, q); icon = R.drawable.ic_calendar; } result.addRow(new Object[]{id++, q, icon, action, Uri.withAppendedPath(SEARCH_URI, c.getString(0)), c.getString(0) }); } while (c.moveToNext()); } return result; case search_suggestion: String query = uri.getLastPathSegment(); if (query != null && query.length() > 0) { ArrayList<DiaryActivity> filtered = ActivityHelper.helper.sortedActivities(query); for (int i = 0; i < 3; i++) { if (i < filtered.size()) { result.addRow(new Object[]{id++, filtered.get(i).getName(), null, SEARCH_ACTIVITY, Uri.withAppendedPath(SEARCH_URI, Integer.toString(filtered.get(i).getId())), filtered.get(i).getName() }); } } result.addRow(new Object[]{id++, getContext().getResources().getString(R.string.search_notes, query), R.drawable.ic_search, SEARCH_NOTE, Uri.withAppendedPath(SEARCH_URI, query), query }); result.addRow(new Object[]{id++, getContext().getResources().getString(R.string.search_diary, query), R.drawable.ic_search, SEARCH_GLOBAL, Uri.withAppendedPath(SEARCH_URI, query), query }); result.addRow(new Object[]{id++, getContext().getResources().getString(R.string.search_date, query), R.drawable.ic_calendar, SEARCH_DATE, Uri.withAppendedPath(SEARCH_URI, query), query }); } return result; case conditions_ID: case conditions: default: } if (useRawQuery) { c = mOpenHelper.getReadableDatabase().rawQuery(sql, selectionArgs); } else { c = qBuilder.query(mOpenHelper.getReadableDatabase(), projection, selection, selectionArgs, grouping, null, sortOrder); } c.setNotificationUri(getContext().getContentResolver(), uri); return c; }  @Override boolean onCreate(); @Nullable @Override Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder); @Nullable @Override String getType(@NonNull Uri uri); @Nullable @Override Uri insert(@NonNull Uri uri, @Nullable ContentValues values); @Override int delete(@NonNull Uri uri, @Nullable String selection, @Nullable String[] selectionArgs); @Override int update(@NonNull Uri uri, @NonNull ContentValues values, @Nullable String selection, @Nullable String[] selectionArgs); void resetDatabase(); String searchDate(Long dateInMillis); static final String SEARCH_ACTIVITY; static final String SEARCH_NOTE; static final String SEARCH_GLOBAL; static final String SEARCH_DATE; static final Uri SEARCH_URI; }
@Test public void simpleQuery() throws Exception { Cursor cursor = contentResolver.query( ActivityDiaryContract.DiaryActivity.CONTENT_URI, ActivityDiaryContract.DiaryActivity.PROJECTION_ALL, null, null, ActivityDiaryContract.DiaryActivity.SORT_ORDER_DEFAULT); assertNotNull(cursor); assertTrue("at least some random data", cursor.getCount() > 0); }