1 /* 2 * Copyright (C) 2021 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package com.android.providers.media.photopicker.data; 18 19 import static android.content.ContentResolver.EXTRA_HONORED_ARGS; 20 import static android.provider.CloudMediaProviderContract.AlbumColumns; 21 import static android.provider.CloudMediaProviderContract.AlbumColumns.ALBUM_ID_CAMERA; 22 import static android.provider.CloudMediaProviderContract.AlbumColumns.ALBUM_ID_DOWNLOADS; 23 import static android.provider.CloudMediaProviderContract.AlbumColumns.ALBUM_ID_SCREENSHOTS; 24 import static android.provider.CloudMediaProviderContract.EXTRA_ALBUM_ID; 25 import static android.provider.CloudMediaProviderContract.EXTRA_MEDIA_COLLECTION_ID; 26 import static android.provider.CloudMediaProviderContract.EXTRA_SYNC_GENERATION; 27 import static android.provider.CloudMediaProviderContract.MediaCollectionInfo; 28 29 import static com.android.providers.media.photopicker.data.PickerDbFacade.QueryFilterBuilder.LONG_DEFAULT; 30 import static com.android.providers.media.photopicker.data.PickerDbFacade.addMimeTypesToQueryBuilderAndSelectionArgs; 31 import static com.android.providers.media.photopicker.util.CursorUtils.getCursorLong; 32 import static com.android.providers.media.photopicker.util.CursorUtils.getCursorString; 33 import static com.android.providers.media.util.DatabaseUtils.bindList; 34 35 import android.content.ContentValues; 36 import android.content.Context; 37 import android.database.Cursor; 38 import android.database.MatrixCursor; 39 import android.database.sqlite.SQLiteConstraintException; 40 import android.database.sqlite.SQLiteDatabase; 41 import android.database.sqlite.SQLiteQueryBuilder; 42 import android.os.Bundle; 43 import android.os.Environment; 44 import android.provider.CloudMediaProviderContract; 45 import android.provider.MediaStore; 46 import android.provider.MediaStore.Files.FileColumns; 47 import android.provider.MediaStore.MediaColumns; 48 import android.text.TextUtils; 49 import android.util.Log; 50 51 import androidx.annotation.VisibleForTesting; 52 53 import com.android.providers.media.DatabaseHelper; 54 import com.android.providers.media.VolumeCache; 55 import com.android.providers.media.photopicker.PickerSyncController; 56 import com.android.providers.media.util.MimeUtils; 57 58 import java.util.ArrayList; 59 import java.util.Arrays; 60 import java.util.List; 61 62 /** 63 * This is a facade that hides the complexities of executing some SQL statements on the external db. 64 * It does not do any caller permission checks and is only intended for internal use within the 65 * MediaProvider for the Photo Picker. 66 */ 67 public class ExternalDbFacade { 68 private static final String TAG = "ExternalDbFacade"; 69 @VisibleForTesting 70 static final String TABLE_FILES = "files"; 71 72 @VisibleForTesting 73 static final String TABLE_DELETED_MEDIA = "deleted_media"; 74 @VisibleForTesting 75 static final String COLUMN_OLD_ID = "old_id"; 76 private static final String COLUMN_OLD_ID_AS_ID = COLUMN_OLD_ID + " AS " + 77 CloudMediaProviderContract.MediaColumns.ID; 78 private static final String COLUMN_GENERATION_MODIFIED = MediaColumns.GENERATION_MODIFIED; 79 80 private static final String[] PROJECTION_MEDIA_COLUMNS = new String[] { 81 MediaColumns._ID + " AS " + CloudMediaProviderContract.MediaColumns.ID, 82 "COALESCE(" + MediaColumns.DATE_TAKEN + "," + MediaColumns.DATE_MODIFIED + 83 "* 1000) AS " + CloudMediaProviderContract.MediaColumns.DATE_TAKEN_MILLIS, 84 MediaColumns.GENERATION_MODIFIED + " AS " + 85 CloudMediaProviderContract.MediaColumns.SYNC_GENERATION, 86 MediaColumns.SIZE + " AS " + CloudMediaProviderContract.MediaColumns.SIZE_BYTES, 87 MediaColumns.MIME_TYPE + " AS " + CloudMediaProviderContract.MediaColumns.MIME_TYPE, 88 FileColumns._SPECIAL_FORMAT + " AS " + 89 CloudMediaProviderContract.MediaColumns.STANDARD_MIME_TYPE_EXTENSION, 90 MediaColumns.DURATION + " AS " + CloudMediaProviderContract.MediaColumns.DURATION_MILLIS, 91 MediaColumns.IS_FAVORITE + " AS " + CloudMediaProviderContract.MediaColumns.IS_FAVORITE, 92 MediaColumns.WIDTH + " AS " + CloudMediaProviderContract.MediaColumns.WIDTH, 93 MediaColumns.HEIGHT + " AS " + CloudMediaProviderContract.MediaColumns.HEIGHT, 94 MediaColumns.ORIENTATION + " AS " + CloudMediaProviderContract.MediaColumns.ORIENTATION, 95 }; 96 private static final String[] PROJECTION_MEDIA_INFO = new String[] { 97 "MAX(" + MediaColumns.GENERATION_MODIFIED + ") AS " 98 + MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION 99 }; 100 private static final String[] PROJECTION_ALBUM_DB = new String[] { 101 "COUNT(" + MediaColumns._ID + ") AS " + CloudMediaProviderContract.AlbumColumns.MEDIA_COUNT, 102 "MAX(COALESCE(" + MediaColumns.DATE_TAKEN + "," + MediaColumns.DATE_MODIFIED + 103 "* 1000)) AS " + CloudMediaProviderContract.AlbumColumns.DATE_TAKEN_MILLIS, 104 MediaColumns._ID + " AS " + CloudMediaProviderContract.AlbumColumns.MEDIA_COVER_ID, 105 }; 106 107 private static final String WHERE_IMAGE_TYPE = FileColumns.MEDIA_TYPE + " = " 108 + FileColumns.MEDIA_TYPE_IMAGE; 109 private static final String WHERE_VIDEO_TYPE = FileColumns.MEDIA_TYPE + " = " 110 + FileColumns.MEDIA_TYPE_VIDEO; 111 private static final String WHERE_MEDIA_TYPE = WHERE_IMAGE_TYPE + " OR " + WHERE_VIDEO_TYPE; 112 private static final String WHERE_IS_DOWNLOAD = MediaColumns.IS_DOWNLOAD + " = 1"; 113 private static final String WHERE_NOT_TRASHED = MediaColumns.IS_TRASHED + " = 0"; 114 private static final String WHERE_NOT_PENDING = MediaColumns.IS_PENDING + " = 0"; 115 private static final String WHERE_GREATER_GENERATION = 116 MediaColumns.GENERATION_MODIFIED + " > ?"; 117 private static final String WHERE_RELATIVE_PATH = MediaStore.MediaColumns.RELATIVE_PATH 118 + " LIKE ?"; 119 120 /* Include any directory named exactly {@link Environment.DIRECTORY_SCREENSHOTS} 121 * and its child directories. */ 122 private static final String WHERE_RELATIVE_PATH_IS_SCREENSHOT_DIR = 123 MediaStore.MediaColumns.RELATIVE_PATH 124 + " LIKE '%/" 125 + Environment.DIRECTORY_SCREENSHOTS 126 + "/%' OR " 127 + MediaStore.MediaColumns.RELATIVE_PATH 128 + " LIKE '" 129 + Environment.DIRECTORY_SCREENSHOTS 130 + "/%'"; 131 132 private static final String WHERE_VOLUME_IN_PREFIX = 133 MediaStore.MediaColumns.VOLUME_NAME + " IN %s"; 134 135 public static final String RELATIVE_PATH_CAMERA = Environment.DIRECTORY_DCIM + "/Camera/%"; 136 137 @VisibleForTesting 138 static String[] LOCAL_ALBUM_IDS = { 139 ALBUM_ID_CAMERA, 140 ALBUM_ID_SCREENSHOTS, 141 ALBUM_ID_DOWNLOADS 142 }; 143 144 private final Context mContext; 145 private final DatabaseHelper mDatabaseHelper; 146 private final VolumeCache mVolumeCache; 147 ExternalDbFacade(Context context, DatabaseHelper databaseHelper, VolumeCache volumeCache)148 public ExternalDbFacade(Context context, DatabaseHelper databaseHelper, 149 VolumeCache volumeCache) { 150 mContext = context; 151 mDatabaseHelper = databaseHelper; 152 mVolumeCache = volumeCache; 153 } 154 155 /** 156 * Returns {@code true} if the PhotoPicker should be notified of this change, {@code false} 157 * otherwise 158 */ onFileInserted(int mediaType, boolean isPending)159 public boolean onFileInserted(int mediaType, boolean isPending) { 160 if (!mDatabaseHelper.isExternal()) { 161 return false; 162 } 163 164 return !isPending && MimeUtils.isImageOrVideoMediaType(mediaType); 165 } 166 167 /** 168 * Adds or removes media to the deleted_media tables 169 * 170 * Returns {@code true} if the PhotoPicker should be notified of this change, {@code false} 171 * otherwise 172 */ onFileUpdated(long oldId, int oldMediaType, int newMediaType, boolean oldIsTrashed, boolean newIsTrashed, boolean oldIsPending, boolean newIsPending, boolean oldIsFavorite, boolean newIsFavorite, int oldSpecialFormat, int newSpecialFormat)173 public boolean onFileUpdated(long oldId, int oldMediaType, int newMediaType, 174 boolean oldIsTrashed, boolean newIsTrashed, boolean oldIsPending, 175 boolean newIsPending, boolean oldIsFavorite, boolean newIsFavorite, 176 int oldSpecialFormat, int newSpecialFormat) { 177 if (!mDatabaseHelper.isExternal()) { 178 return false; 179 } 180 181 final boolean oldIsMedia= MimeUtils.isImageOrVideoMediaType(oldMediaType); 182 final boolean newIsMedia = MimeUtils.isImageOrVideoMediaType(newMediaType); 183 184 final boolean oldIsVisible = !oldIsTrashed && !oldIsPending; 185 final boolean newIsVisible = !newIsTrashed && !newIsPending; 186 187 final boolean oldIsVisibleMedia = oldIsVisible && oldIsMedia; 188 final boolean newIsVisibleMedia = newIsVisible && newIsMedia; 189 190 if (!oldIsVisibleMedia && newIsVisibleMedia) { 191 // Was not visible media and is now visible media 192 removeDeletedMedia(oldId); 193 return true; 194 } else if (oldIsVisibleMedia && !newIsVisibleMedia) { 195 // Was visible media and is now not visible media 196 addDeletedMedia(oldId); 197 return true; 198 } 199 200 if (newIsVisibleMedia) { 201 return (oldIsFavorite != newIsFavorite) || (oldSpecialFormat != newSpecialFormat); 202 } 203 204 205 // Do nothing, not an interesting change 206 return false; 207 } 208 209 /** 210 * Adds or removes media to the deleted_media tables 211 * 212 * Returns {@code true} if the PhotoPicker should be notified of this change, {@code false} 213 * otherwise 214 */ onFileDeleted(long id, int mediaType)215 public boolean onFileDeleted(long id, int mediaType) { 216 if (!mDatabaseHelper.isExternal()) { 217 return false; 218 } 219 if (!MimeUtils.isImageOrVideoMediaType(mediaType)) { 220 return false; 221 } 222 223 addDeletedMedia(id); 224 return true; 225 } 226 227 /** 228 * Adds media with row id {@code oldId} to the deleted_media table. Returns {@code true} if 229 * if it was successfully added, {@code false} otherwise. 230 */ 231 @VisibleForTesting addDeletedMedia(long oldId)232 boolean addDeletedMedia(long oldId) { 233 return mDatabaseHelper.runWithTransaction((db) -> { 234 SQLiteQueryBuilder qb = createDeletedMediaQueryBuilder(); 235 236 ContentValues cv = new ContentValues(); 237 cv.put(COLUMN_OLD_ID, oldId); 238 cv.put(COLUMN_GENERATION_MODIFIED, DatabaseHelper.getGeneration(db)); 239 240 try { 241 return qb.insert(db, cv) > 0; 242 } catch (SQLiteConstraintException e) { 243 String select = COLUMN_OLD_ID + " = ?"; 244 String[] selectionArgs = new String[] {String.valueOf(oldId)}; 245 246 return qb.update(db, cv, select, selectionArgs) > 0; 247 } 248 }); 249 } 250 251 /** 252 * Removes media with row id {@code oldId} from the deleted_media table. Returns {@code true} if 253 * it was successfully removed, {@code false} otherwise. 254 */ 255 @VisibleForTesting removeDeletedMedia(long oldId)256 boolean removeDeletedMedia(long oldId) { 257 return mDatabaseHelper.runWithTransaction(db -> { 258 SQLiteQueryBuilder qb = createDeletedMediaQueryBuilder(); 259 260 return qb.delete(db, COLUMN_OLD_ID + " = ?", new String[] {String.valueOf(oldId)}) > 0; 261 }); 262 } 263 264 /** 265 * Returns all items from the deleted_media table. 266 */ 267 public Cursor queryDeletedMedia(long generation) { 268 final Cursor cursor = mDatabaseHelper.runWithTransaction(db -> { 269 SQLiteQueryBuilder qb = createDeletedMediaQueryBuilder(); 270 String[] projection = new String[] {COLUMN_OLD_ID_AS_ID}; 271 String select = COLUMN_GENERATION_MODIFIED + " > ?"; 272 String[] selectionArgs = new String[] {String.valueOf(generation)}; 273 274 return qb.query(db, projection, select, selectionArgs, /* groupBy */ null, 275 /* having */ null, /* orderBy */ null); 276 }); 277 278 cursor.setExtras(getCursorExtras(generation, /* albumId */ null)); 279 return cursor; 280 } 281 282 /** 283 * Returns all items from the files table where {@link MediaColumns#GENERATION_MODIFIED} 284 * is greater than {@code generation}. 285 */ 286 public Cursor queryMedia(long generation, String albumId, String[] mimeTypes) { 287 final List<String> selectionArgs = new ArrayList<>(); 288 final String orderBy = CloudMediaProviderContract.MediaColumns.DATE_TAKEN_MILLIS + " DESC"; 289 290 final Cursor cursor = mDatabaseHelper.runWithTransaction(db -> { 291 SQLiteQueryBuilder qb = createMediaQueryBuilder(); 292 qb.appendWhereStandalone(WHERE_GREATER_GENERATION); 293 selectionArgs.add(String.valueOf(generation)); 294 295 selectionArgs.addAll(appendWhere(qb, albumId, mimeTypes)); 296 297 return qb.query(db, PROJECTION_MEDIA_COLUMNS, /* select */ null, 298 selectionArgs.toArray(new String[selectionArgs.size()]), /* groupBy */ null, 299 /* having */ null, orderBy); 300 }); 301 302 cursor.setExtras(getCursorExtras(generation, albumId)); 303 return cursor; 304 } 305 306 private Bundle getCursorExtras(long generation, String albumId) { 307 final Bundle bundle = new Bundle(); 308 final ArrayList<String> honoredArgs = new ArrayList<>(); 309 310 if (generation > LONG_DEFAULT) { 311 honoredArgs.add(EXTRA_SYNC_GENERATION); 312 } 313 if (!TextUtils.isEmpty(albumId)) { 314 honoredArgs.add(EXTRA_ALBUM_ID); 315 } 316 317 bundle.putString(EXTRA_MEDIA_COLLECTION_ID, getMediaCollectionId()); 318 bundle.putStringArrayList(EXTRA_HONORED_ARGS, honoredArgs); 319 320 return bundle; 321 } 322 323 /** 324 * Returns the total count and max {@link MediaColumns#GENERATION_MODIFIED} value 325 * of the media items in the files table greater than {@code generation}. 326 */ 327 private Cursor getMediaCollectionInfoCursor(long generation) { 328 final String[] selectionArgs = new String[] {String.valueOf(generation)}; 329 final String[] projection = new String[] { 330 MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION 331 }; 332 333 return mDatabaseHelper.runWithTransaction(db -> { 334 SQLiteQueryBuilder qbMedia = createMediaQueryBuilder(); 335 qbMedia.appendWhereStandalone(WHERE_GREATER_GENERATION); 336 SQLiteQueryBuilder qbDeletedMedia = createDeletedMediaQueryBuilder(); 337 qbDeletedMedia.appendWhereStandalone(WHERE_GREATER_GENERATION); 338 339 try (Cursor mediaCursor = query(qbMedia, db, PROJECTION_MEDIA_INFO, selectionArgs); 340 Cursor deletedMediaCursor = query(qbDeletedMedia, db, 341 PROJECTION_MEDIA_INFO, selectionArgs)) { 342 final int mediaGenerationIndex = mediaCursor.getColumnIndexOrThrow( 343 MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION); 344 final int deletedMediaGenerationIndex = 345 deletedMediaCursor.getColumnIndexOrThrow( 346 MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION); 347 348 long mediaGeneration = 0; 349 if (mediaCursor.moveToFirst()) { 350 mediaGeneration = mediaCursor.getLong(mediaGenerationIndex); 351 } 352 353 long deletedMediaGeneration = 0; 354 if (deletedMediaCursor.moveToFirst()) { 355 deletedMediaGeneration = deletedMediaCursor.getLong( 356 deletedMediaGenerationIndex); 357 } 358 359 long maxGeneration = Math.max(mediaGeneration, deletedMediaGeneration); 360 MatrixCursor result = new MatrixCursor(projection); 361 result.addRow(new Long[] { maxGeneration }); 362 363 return result; 364 } 365 }); 366 } 367 368 public Bundle getMediaCollectionInfo(long generation) { 369 final Bundle bundle = new Bundle(); 370 try (Cursor cursor = getMediaCollectionInfoCursor(generation)) { 371 if (cursor.moveToFirst()) { 372 int generationIndex = cursor.getColumnIndexOrThrow( 373 MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION); 374 375 bundle.putString(MediaCollectionInfo.MEDIA_COLLECTION_ID, getMediaCollectionId()); 376 bundle.putLong(MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION, 377 cursor.getLong(generationIndex)); 378 } 379 } 380 return bundle; 381 } 382 383 /** 384 * Returns the media item categories from the files table. 385 * Categories are determined with the {@link #LOCAL_ALBUM_IDS}. 386 * If there are no media items under an albumId, the album is skipped from the results. 387 */ 388 public Cursor queryAlbums(String[] mimeTypes) { 389 final MatrixCursor c = new MatrixCursor(AlbumColumns.ALL_PROJECTION); 390 391 for (String albumId: LOCAL_ALBUM_IDS) { 392 Cursor cursor = mDatabaseHelper.runWithTransaction(db -> { 393 final SQLiteQueryBuilder qb = createMediaQueryBuilder(); 394 final List<String> selectionArgs = new ArrayList<>(); 395 selectionArgs.addAll(appendWhere(qb, albumId, mimeTypes)); 396 397 return qb.query(db, PROJECTION_ALBUM_DB, /* selection */ null, 398 selectionArgs.toArray(new String[selectionArgs.size()]), /* groupBy */ null, 399 /* having */ null, /* orderBy */ null); 400 }); 401 402 if (cursor == null || !cursor.moveToFirst()) { 403 continue; 404 } 405 406 long count = getCursorLong(cursor, CloudMediaProviderContract.AlbumColumns.MEDIA_COUNT); 407 if (count == 0) { 408 continue; 409 } 410 411 final String[] projectionValue = new String[] { 412 /* albumId */ albumId, 413 getCursorString(cursor, AlbumColumns.DATE_TAKEN_MILLIS), 414 /* displayName */ albumId, 415 getCursorString(cursor, AlbumColumns.MEDIA_COVER_ID), 416 String.valueOf(count), 417 PickerSyncController.LOCAL_PICKER_PROVIDER_AUTHORITY 418 }; 419 420 c.addRow(projectionValue); 421 } 422 423 return c; 424 } 425 426 private static Cursor query(SQLiteQueryBuilder qb, SQLiteDatabase db, String[] projection, 427 String[] selectionArgs) { 428 return qb.query(db, projection, /* select */ null, selectionArgs, 429 /* groupBy */ null, /* having */ null, /* orderBy */ null); 430 } 431 432 private static List<String> appendWhere(SQLiteQueryBuilder qb, String albumId, 433 String[] mimeTypes) { 434 final List<String> selectionArgs = new ArrayList<>(); 435 436 addMimeTypesToQueryBuilderAndSelectionArgs(qb, selectionArgs, mimeTypes); 437 438 if (albumId == null) { 439 return selectionArgs; 440 } 441 442 switch (albumId) { 443 case ALBUM_ID_CAMERA: 444 qb.appendWhereStandalone(WHERE_RELATIVE_PATH); 445 selectionArgs.add(RELATIVE_PATH_CAMERA); 446 break; 447 case ALBUM_ID_SCREENSHOTS: 448 qb.appendWhereStandalone(WHERE_RELATIVE_PATH_IS_SCREENSHOT_DIR); 449 break; 450 case ALBUM_ID_DOWNLOADS: 451 qb.appendWhereStandalone(WHERE_IS_DOWNLOAD); 452 break; 453 default: 454 Log.w(TAG, "No match for album: " + albumId); 455 break; 456 } 457 458 return selectionArgs; 459 } 460 461 private static SQLiteQueryBuilder createDeletedMediaQueryBuilder() { 462 SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 463 qb.setTables(TABLE_DELETED_MEDIA); 464 465 return qb; 466 } 467 468 private SQLiteQueryBuilder createMediaQueryBuilder() { 469 SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 470 qb.setTables(TABLE_FILES); 471 qb.appendWhereStandalone(WHERE_MEDIA_TYPE); 472 qb.appendWhereStandalone(WHERE_NOT_TRASHED); 473 qb.appendWhereStandalone(WHERE_NOT_PENDING); 474 475 String[] volumes = getVolumeList(); 476 if (volumes.length > 0) { 477 qb.appendWhereStandalone(buildWhereVolumeIn(volumes)); 478 } 479 480 return qb; 481 } 482 483 private String buildWhereVolumeIn(String[] volumes) { 484 return String.format(WHERE_VOLUME_IN_PREFIX, bindList((Object[]) volumes)); 485 } 486 487 private String[] getVolumeList() { 488 String[] volumeNames = mVolumeCache.getExternalVolumeNames().toArray(new String[0]); 489 Arrays.sort(volumeNames); 490 491 return volumeNames; 492 } 493 494 private String getMediaCollectionId() { 495 final String[] volumes = getVolumeList(); 496 if (volumes.length == 0) { 497 return MediaStore.getVersion(mContext); 498 } 499 500 return MediaStore.getVersion(mContext) + ":" + TextUtils.join(":", getVolumeList()); 501 } 502 } 503