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.util.CursorUtils.getCursorLong; 31 import static com.android.providers.media.photopicker.util.CursorUtils.getCursorString; 32 import static com.android.providers.media.util.DatabaseUtils.bindList; 33 import static com.android.providers.media.util.DatabaseUtils.replaceMatchAnyChar; 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 }; 93 private static final String[] PROJECTION_MEDIA_INFO = new String[] { 94 "MAX(" + MediaColumns.GENERATION_MODIFIED + ") AS " 95 + MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION 96 }; 97 private static final String[] PROJECTION_ALBUM_DB = new String[] { 98 "COUNT(" + MediaColumns._ID + ") AS " + CloudMediaProviderContract.AlbumColumns.MEDIA_COUNT, 99 "MAX(COALESCE(" + MediaColumns.DATE_TAKEN + "," + MediaColumns.DATE_MODIFIED + 100 "* 1000)) AS " + CloudMediaProviderContract.AlbumColumns.DATE_TAKEN_MILLIS, 101 MediaColumns._ID + " AS " + CloudMediaProviderContract.AlbumColumns.MEDIA_COVER_ID, 102 }; 103 104 private static final String WHERE_IMAGE_TYPE = FileColumns.MEDIA_TYPE + " = " 105 + FileColumns.MEDIA_TYPE_IMAGE; 106 private static final String WHERE_VIDEO_TYPE = FileColumns.MEDIA_TYPE + " = " 107 + FileColumns.MEDIA_TYPE_VIDEO; 108 private static final String WHERE_MEDIA_TYPE = WHERE_IMAGE_TYPE + " OR " + WHERE_VIDEO_TYPE; 109 private static final String WHERE_IS_DOWNLOAD = MediaColumns.IS_DOWNLOAD + " = 1"; 110 private static final String WHERE_NOT_TRASHED = MediaColumns.IS_TRASHED + " = 0"; 111 private static final String WHERE_NOT_PENDING = MediaColumns.IS_PENDING + " = 0"; 112 private static final String WHERE_GREATER_GENERATION = 113 MediaColumns.GENERATION_MODIFIED + " > ?"; 114 private static final String WHERE_RELATIVE_PATH = MediaStore.MediaColumns.RELATIVE_PATH 115 + " LIKE ?"; 116 private static final String WHERE_MIME_TYPE = MediaStore.MediaColumns.MIME_TYPE 117 + " LIKE ?"; 118 private static final String WHERE_VOLUME_IN_PREFIX = MediaStore.MediaColumns.VOLUME_NAME 119 + " IN %s"; 120 121 public static final String RELATIVE_PATH_SCREENSHOTS = 122 "%/" + Environment.DIRECTORY_SCREENSHOTS + "/%"; 123 124 public static final String RELATIVE_PATH_CAMERA = Environment.DIRECTORY_DCIM + "/Camera/%"; 125 126 @VisibleForTesting 127 static String[] LOCAL_ALBUM_IDS = { 128 ALBUM_ID_CAMERA, 129 ALBUM_ID_SCREENSHOTS, 130 ALBUM_ID_DOWNLOADS 131 }; 132 133 private final Context mContext; 134 private final DatabaseHelper mDatabaseHelper; 135 private final VolumeCache mVolumeCache; 136 ExternalDbFacade(Context context, DatabaseHelper databaseHelper, VolumeCache volumeCache)137 public ExternalDbFacade(Context context, DatabaseHelper databaseHelper, 138 VolumeCache volumeCache) { 139 mContext = context; 140 mDatabaseHelper = databaseHelper; 141 mVolumeCache = volumeCache; 142 } 143 144 /** 145 * Returns {@code true} if the PhotoPicker should be notified of this change, {@code false} 146 * otherwise 147 */ onFileInserted(int mediaType, boolean isPending)148 public boolean onFileInserted(int mediaType, boolean isPending) { 149 if (!mDatabaseHelper.isExternal()) { 150 return false; 151 } 152 153 return !isPending && MimeUtils.isImageOrVideoMediaType(mediaType); 154 } 155 156 /** 157 * Adds or removes media to the deleted_media tables 158 * 159 * Returns {@code true} if the PhotoPicker should be notified of this change, {@code false} 160 * otherwise 161 */ onFileUpdated(long oldId, int oldMediaType, int newMediaType, boolean oldIsTrashed, boolean newIsTrashed, boolean oldIsPending, boolean newIsPending, boolean oldIsFavorite, boolean newIsFavorite, int oldSpecialFormat, int newSpecialFormat)162 public boolean onFileUpdated(long oldId, int oldMediaType, int newMediaType, 163 boolean oldIsTrashed, boolean newIsTrashed, boolean oldIsPending, 164 boolean newIsPending, boolean oldIsFavorite, boolean newIsFavorite, 165 int oldSpecialFormat, int newSpecialFormat) { 166 if (!mDatabaseHelper.isExternal()) { 167 return false; 168 } 169 170 final boolean oldIsMedia= MimeUtils.isImageOrVideoMediaType(oldMediaType); 171 final boolean newIsMedia = MimeUtils.isImageOrVideoMediaType(newMediaType); 172 173 final boolean oldIsVisible = !oldIsTrashed && !oldIsPending; 174 final boolean newIsVisible = !newIsTrashed && !newIsPending; 175 176 final boolean oldIsVisibleMedia = oldIsVisible && oldIsMedia; 177 final boolean newIsVisibleMedia = newIsVisible && newIsMedia; 178 179 if (!oldIsVisibleMedia && newIsVisibleMedia) { 180 // Was not visible media and is now visible media 181 removeDeletedMedia(oldId); 182 return true; 183 } else if (oldIsVisibleMedia && !newIsVisibleMedia) { 184 // Was visible media and is now not visible media 185 addDeletedMedia(oldId); 186 return true; 187 } 188 189 if (newIsVisibleMedia) { 190 return (oldIsFavorite != newIsFavorite) || (oldSpecialFormat != newSpecialFormat); 191 } 192 193 194 // Do nothing, not an interesting change 195 return false; 196 } 197 198 /** 199 * Adds or removes media to the deleted_media tables 200 * 201 * Returns {@code true} if the PhotoPicker should be notified of this change, {@code false} 202 * otherwise 203 */ onFileDeleted(long id, int mediaType)204 public boolean onFileDeleted(long id, int mediaType) { 205 if (!mDatabaseHelper.isExternal()) { 206 return false; 207 } 208 if (!MimeUtils.isImageOrVideoMediaType(mediaType)) { 209 return false; 210 } 211 212 addDeletedMedia(id); 213 return true; 214 } 215 216 /** 217 * Adds media with row id {@code oldId} to the deleted_media table. Returns {@code true} if 218 * if it was successfully added, {@code false} otherwise. 219 */ 220 @VisibleForTesting addDeletedMedia(long oldId)221 boolean addDeletedMedia(long oldId) { 222 return mDatabaseHelper.runWithTransaction((db) -> { 223 SQLiteQueryBuilder qb = createDeletedMediaQueryBuilder(); 224 225 ContentValues cv = new ContentValues(); 226 cv.put(COLUMN_OLD_ID, oldId); 227 cv.put(COLUMN_GENERATION_MODIFIED, DatabaseHelper.getGeneration(db)); 228 229 try { 230 return qb.insert(db, cv) > 0; 231 } catch (SQLiteConstraintException e) { 232 String select = COLUMN_OLD_ID + " = ?"; 233 String[] selectionArgs = new String[] {String.valueOf(oldId)}; 234 235 return qb.update(db, cv, select, selectionArgs) > 0; 236 } 237 }); 238 } 239 240 /** 241 * Removes media with row id {@code oldId} from the deleted_media table. Returns {@code true} if 242 * it was successfully removed, {@code false} otherwise. 243 */ 244 @VisibleForTesting removeDeletedMedia(long oldId)245 boolean removeDeletedMedia(long oldId) { 246 return mDatabaseHelper.runWithTransaction(db -> { 247 SQLiteQueryBuilder qb = createDeletedMediaQueryBuilder(); 248 249 return qb.delete(db, COLUMN_OLD_ID + " = ?", new String[] {String.valueOf(oldId)}) > 0; 250 }); 251 } 252 253 /** 254 * Returns all items from the deleted_media table. 255 */ 256 public Cursor queryDeletedMedia(long generation) { 257 final Cursor cursor = mDatabaseHelper.runWithTransaction(db -> { 258 SQLiteQueryBuilder qb = createDeletedMediaQueryBuilder(); 259 String[] projection = new String[] {COLUMN_OLD_ID_AS_ID}; 260 String select = COLUMN_GENERATION_MODIFIED + " > ?"; 261 String[] selectionArgs = new String[] {String.valueOf(generation)}; 262 263 return qb.query(db, projection, select, selectionArgs, /* groupBy */ null, 264 /* having */ null, /* orderBy */ null); 265 }); 266 267 cursor.setExtras(getCursorExtras(generation, /* albumId */ null)); 268 return cursor; 269 } 270 271 /** 272 * Returns all items from the files table where {@link MediaColumns#GENERATION_MODIFIED} 273 * is greater than {@code generation}. 274 */ 275 public Cursor queryMedia(long generation, String albumId, String mimeType) { 276 final List<String> selectionArgs = new ArrayList<>(); 277 final String orderBy = CloudMediaProviderContract.MediaColumns.DATE_TAKEN_MILLIS + " DESC"; 278 279 final Cursor cursor = mDatabaseHelper.runWithTransaction(db -> { 280 SQLiteQueryBuilder qb = createMediaQueryBuilder(); 281 qb.appendWhereStandalone(WHERE_GREATER_GENERATION); 282 selectionArgs.add(String.valueOf(generation)); 283 284 selectionArgs.addAll(appendWhere(qb, albumId, mimeType)); 285 286 return qb.query(db, PROJECTION_MEDIA_COLUMNS, /* select */ null, 287 selectionArgs.toArray(new String[selectionArgs.size()]), /* groupBy */ null, 288 /* having */ null, orderBy); 289 }); 290 291 cursor.setExtras(getCursorExtras(generation, albumId)); 292 return cursor; 293 } 294 295 private Bundle getCursorExtras(long generation, String albumId) { 296 final Bundle bundle = new Bundle(); 297 final ArrayList<String> honoredArgs = new ArrayList<>(); 298 299 if (generation > LONG_DEFAULT) { 300 honoredArgs.add(EXTRA_SYNC_GENERATION); 301 } 302 if (!TextUtils.isEmpty(albumId)) { 303 honoredArgs.add(EXTRA_ALBUM_ID); 304 } 305 306 bundle.putString(EXTRA_MEDIA_COLLECTION_ID, getMediaCollectionId()); 307 bundle.putStringArrayList(EXTRA_HONORED_ARGS, honoredArgs); 308 309 return bundle; 310 } 311 312 /** 313 * Returns the total count and max {@link MediaColumns#GENERATION_MODIFIED} value 314 * of the media items in the files table greater than {@code generation}. 315 */ 316 private Cursor getMediaCollectionInfoCursor(long generation) { 317 final String[] selectionArgs = new String[] {String.valueOf(generation)}; 318 final String[] projection = new String[] { 319 MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION 320 }; 321 322 return mDatabaseHelper.runWithTransaction(db -> { 323 SQLiteQueryBuilder qbMedia = createMediaQueryBuilder(); 324 qbMedia.appendWhereStandalone(WHERE_GREATER_GENERATION); 325 SQLiteQueryBuilder qbDeletedMedia = createDeletedMediaQueryBuilder(); 326 qbDeletedMedia.appendWhereStandalone(WHERE_GREATER_GENERATION); 327 328 try (Cursor mediaCursor = query(qbMedia, db, PROJECTION_MEDIA_INFO, selectionArgs); 329 Cursor deletedMediaCursor = query(qbDeletedMedia, db, 330 PROJECTION_MEDIA_INFO, selectionArgs)) { 331 final int mediaGenerationIndex = mediaCursor.getColumnIndexOrThrow( 332 MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION); 333 final int deletedMediaGenerationIndex = 334 deletedMediaCursor.getColumnIndexOrThrow( 335 MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION); 336 337 long mediaGeneration = 0; 338 if (mediaCursor.moveToFirst()) { 339 mediaGeneration = mediaCursor.getLong(mediaGenerationIndex); 340 } 341 342 long deletedMediaGeneration = 0; 343 if (deletedMediaCursor.moveToFirst()) { 344 deletedMediaGeneration = deletedMediaCursor.getLong( 345 deletedMediaGenerationIndex); 346 } 347 348 long maxGeneration = Math.max(mediaGeneration, deletedMediaGeneration); 349 MatrixCursor result = new MatrixCursor(projection); 350 result.addRow(new Long[] { maxGeneration }); 351 352 return result; 353 } 354 }); 355 } 356 357 public Bundle getMediaCollectionInfo(long generation) { 358 final Bundle bundle = new Bundle(); 359 try (Cursor cursor = getMediaCollectionInfoCursor(generation)) { 360 if (cursor.moveToFirst()) { 361 int generationIndex = cursor.getColumnIndexOrThrow( 362 MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION); 363 364 bundle.putString(MediaCollectionInfo.MEDIA_COLLECTION_ID, getMediaCollectionId()); 365 bundle.putLong(MediaCollectionInfo.LAST_MEDIA_SYNC_GENERATION, 366 cursor.getLong(generationIndex)); 367 } 368 } 369 return bundle; 370 } 371 372 /** 373 * Returns the media item categories from the files table. 374 * Categories are determined with the {@link #LOCAL_ALBUM_IDS}. 375 * If there are no media items under an albumId, the album is skipped from the results. 376 */ 377 public Cursor queryAlbums(String mimeType) { 378 final MatrixCursor c = new MatrixCursor(AlbumColumns.ALL_PROJECTION); 379 380 for (String albumId: LOCAL_ALBUM_IDS) { 381 Cursor cursor = mDatabaseHelper.runWithTransaction(db -> { 382 final SQLiteQueryBuilder qb = createMediaQueryBuilder(); 383 final List<String> selectionArgs = new ArrayList<>(); 384 selectionArgs.addAll(appendWhere(qb, albumId, mimeType)); 385 386 return qb.query(db, PROJECTION_ALBUM_DB, /* selection */ null, 387 selectionArgs.toArray(new String[selectionArgs.size()]), /* groupBy */ null, 388 /* having */ null, /* orderBy */ null); 389 }); 390 391 if (cursor == null || !cursor.moveToFirst()) { 392 continue; 393 } 394 395 long count = getCursorLong(cursor, CloudMediaProviderContract.AlbumColumns.MEDIA_COUNT); 396 if (count == 0) { 397 continue; 398 } 399 400 final String[] projectionValue = new String[] { 401 /* albumId */ albumId, 402 getCursorString(cursor, AlbumColumns.DATE_TAKEN_MILLIS), 403 /* displayName */ albumId, 404 getCursorString(cursor, AlbumColumns.MEDIA_COVER_ID), 405 String.valueOf(count), 406 PickerSyncController.LOCAL_PICKER_PROVIDER_AUTHORITY 407 }; 408 409 c.addRow(projectionValue); 410 } 411 412 return c; 413 } 414 415 private static Cursor query(SQLiteQueryBuilder qb, SQLiteDatabase db, String[] projection, 416 String[] selectionArgs) { 417 return qb.query(db, projection, /* select */ null, selectionArgs, 418 /* groupBy */ null, /* having */ null, /* orderBy */ null); 419 } 420 421 private static List<String> appendWhere(SQLiteQueryBuilder qb, String albumId, 422 String mimeType) { 423 final List<String> selectionArgs = new ArrayList<>(); 424 425 if (mimeType != null) { 426 qb.appendWhereStandalone(WHERE_MIME_TYPE); 427 selectionArgs.add(replaceMatchAnyChar(mimeType)); 428 } 429 430 if (albumId == null) { 431 return selectionArgs; 432 } 433 434 switch (albumId) { 435 case ALBUM_ID_CAMERA: 436 qb.appendWhereStandalone(WHERE_RELATIVE_PATH); 437 selectionArgs.add(RELATIVE_PATH_CAMERA); 438 break; 439 case ALBUM_ID_SCREENSHOTS: 440 qb.appendWhereStandalone(WHERE_RELATIVE_PATH); 441 selectionArgs.add(RELATIVE_PATH_SCREENSHOTS); 442 break; 443 case ALBUM_ID_DOWNLOADS: 444 qb.appendWhereStandalone(WHERE_IS_DOWNLOAD); 445 break; 446 default: 447 Log.w(TAG, "No match for album: " + albumId); 448 break; 449 } 450 451 return selectionArgs; 452 } 453 454 private static SQLiteQueryBuilder createDeletedMediaQueryBuilder() { 455 SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 456 qb.setTables(TABLE_DELETED_MEDIA); 457 458 return qb; 459 } 460 461 private SQLiteQueryBuilder createMediaQueryBuilder() { 462 SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 463 qb.setTables(TABLE_FILES); 464 qb.appendWhereStandalone(WHERE_MEDIA_TYPE); 465 qb.appendWhereStandalone(WHERE_NOT_TRASHED); 466 qb.appendWhereStandalone(WHERE_NOT_PENDING); 467 468 String[] volumes = getVolumeList(); 469 if (volumes.length > 0) { 470 qb.appendWhereStandalone(buildWhereVolumeIn(volumes)); 471 } 472 473 return qb; 474 } 475 476 private String buildWhereVolumeIn(String[] volumes) { 477 return String.format(WHERE_VOLUME_IN_PREFIX, bindList((Object[]) volumes)); 478 } 479 480 private String[] getVolumeList() { 481 String[] volumeNames = mVolumeCache.getExternalVolumeNames().toArray(new String[0]); 482 Arrays.sort(volumeNames); 483 484 return volumeNames; 485 } 486 487 private String getMediaCollectionId() { 488 final String[] volumes = getVolumeList(); 489 if (volumes.length == 0) { 490 return MediaStore.getVersion(mContext); 491 } 492 493 return MediaStore.getVersion(mContext) + ":" + TextUtils.join(":", getVolumeList()); 494 } 495 } 496