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 com.android.providers.media.util.MimeUtils.getExtensionFromMimeType; 20 21 import android.content.Context; 22 import android.content.SharedPreferences; 23 import android.database.Cursor; 24 import android.database.sqlite.SQLiteDatabase; 25 import android.database.sqlite.SQLiteOpenHelper; 26 import android.os.Trace; 27 import android.util.Log; 28 29 import androidx.annotation.VisibleForTesting; 30 31 import com.android.providers.media.photopicker.PickerSyncController; 32 33 /** 34 * Wrapper class for the photo picker database. Can open the actual database 35 * on demand, create and upgrade the schema, etc. 36 * 37 * @see DatabaseHelper 38 */ 39 public class PickerDatabaseHelper extends SQLiteOpenHelper { 40 private static final String TAG = "PickerDatabaseHelper"; 41 42 public static final String PICKER_DATABASE_NAME = "picker.db"; 43 44 private static final int VERSION_T = 9; 45 public static final int VERSION_LATEST = VERSION_T; 46 47 final Context mContext; 48 final String mName; 49 final int mVersion; 50 PickerDatabaseHelper(Context context)51 public PickerDatabaseHelper(Context context) { 52 this(context, PICKER_DATABASE_NAME, VERSION_LATEST); 53 } 54 PickerDatabaseHelper(Context context, String name, int version)55 public PickerDatabaseHelper(Context context, String name, int version) { 56 super(context, name, null, version); 57 mContext = context; 58 mName = name; 59 mVersion = version; 60 61 setWriteAheadLoggingEnabled(true); 62 } 63 64 @Override onCreate(final SQLiteDatabase db)65 public void onCreate(final SQLiteDatabase db) { 66 Log.v(TAG, "onCreate() for " + mName); 67 68 resetData(db); 69 } 70 71 @Override onUpgrade(final SQLiteDatabase db, final int oldV, final int newV)72 public void onUpgrade(final SQLiteDatabase db, final int oldV, final int newV) { 73 Log.v(TAG, "onUpgrade() for " + mName + " from " + oldV + " to " + newV); 74 75 resetData(db); 76 } 77 78 @Override onDowngrade(final SQLiteDatabase db, final int oldV, final int newV)79 public void onDowngrade(final SQLiteDatabase db, final int oldV, final int newV) { 80 Log.v(TAG, "onDowngrade() for " + mName + " from " + oldV + " to " + newV); 81 82 resetData(db); 83 } 84 85 @Override onConfigure(SQLiteDatabase db)86 public void onConfigure(SQLiteDatabase db) { 87 Log.v(TAG, "onConfigure() for " + mName); 88 89 db.setCustomScalarFunction("_GET_EXTENSION", (arg) -> { 90 Trace.beginSection("_GET_EXTENSION"); 91 try { 92 return getExtensionFromMimeType(arg); 93 } finally { 94 Trace.endSection(); 95 } 96 }); 97 } 98 resetData(SQLiteDatabase db)99 private void resetData(SQLiteDatabase db) { 100 clearPickerPrefs(mContext); 101 createLatestSchema(db); 102 createLatestIndexes(db); 103 } 104 105 @VisibleForTesting makePristineSchema(SQLiteDatabase db)106 static void makePristineSchema(SQLiteDatabase db) { 107 // drop all tables 108 Cursor c = db.query("sqlite_master", new String[] {"name"}, "type is 'table'", null, null, 109 null, null); 110 while (c.moveToNext()) { 111 if (c.getString(0).startsWith("sqlite_")) continue; 112 db.execSQL("DROP TABLE IF EXISTS " + c.getString(0)); 113 } 114 c.close(); 115 } 116 117 @VisibleForTesting makePristineIndexes(SQLiteDatabase db)118 static void makePristineIndexes(SQLiteDatabase db) { 119 // drop all indexes 120 Cursor c = db.query("sqlite_master", new String[] {"name"}, "type is 'index'", 121 null, null, null, null); 122 while (c.moveToNext()) { 123 if (c.getString(0).startsWith("sqlite_")) continue; 124 db.execSQL("DROP INDEX IF EXISTS " + c.getString(0)); 125 } 126 c.close(); 127 } 128 createLatestSchema(SQLiteDatabase db)129 private static void createLatestSchema(SQLiteDatabase db) { 130 makePristineSchema(db); 131 132 db.execSQL("CREATE TABLE media (_id INTEGER PRIMARY KEY AUTOINCREMENT," 133 + "local_id TEXT," 134 + "cloud_id TEXT UNIQUE," 135 + "is_visible INTEGER CHECK(is_visible == 1)," 136 + "date_taken_ms INTEGER NOT NULL CHECK(date_taken_ms >= 0)," 137 + "sync_generation INTEGER NOT NULL CHECK(sync_generation >= 0)," 138 + "width INTEGER," 139 + "height INTEGER," 140 + "orientation INTEGER," 141 + "size_bytes INTEGER NOT NULL CHECK(size_bytes > 0)," 142 + "duration_ms INTEGER CHECK(duration_ms >= 0)," 143 + "mime_type TEXT NOT NULL," 144 + "standard_mime_type_extension INTEGER," 145 + "is_favorite INTEGER," 146 + "CHECK(local_id IS NOT NULL OR cloud_id IS NOT NULL)," 147 + "UNIQUE(local_id, is_visible))"); 148 149 db.execSQL("CREATE TABLE album_media (_id INTEGER PRIMARY KEY AUTOINCREMENT," 150 + "local_id TEXT," 151 + "cloud_id TEXT," 152 + "album_id TEXT," 153 + "date_taken_ms INTEGER NOT NULL CHECK(date_taken_ms >= 0)," 154 + "sync_generation INTEGER NOT NULL CHECK(sync_generation >= 0)," 155 + "size_bytes INTEGER NOT NULL CHECK(size_bytes > 0)," 156 + "duration_ms INTEGER CHECK(duration_ms >= 0)," 157 + "mime_type TEXT NOT NULL," 158 + "standard_mime_type_extension INTEGER," 159 + "CHECK((local_id IS NULL AND cloud_id IS NOT NULL) " 160 + "OR (local_id IS NOT NULL AND cloud_id IS NULL))," 161 + "UNIQUE(local_id, album_id)," 162 + "UNIQUE(cloud_id, album_id))"); 163 } 164 createLatestIndexes(SQLiteDatabase db)165 private static void createLatestIndexes(SQLiteDatabase db) { 166 makePristineIndexes(db); 167 168 db.execSQL("CREATE INDEX local_id_index on media(local_id)"); 169 db.execSQL("CREATE INDEX cloud_id_index on media(cloud_id)"); 170 db.execSQL("CREATE INDEX is_visible_index on media(is_visible)"); 171 db.execSQL("CREATE INDEX date_taken_index on media(date_taken_ms)"); 172 db.execSQL("CREATE INDEX size_index on media(size_bytes)"); 173 db.execSQL("CREATE INDEX mime_type_index on media(mime_type)"); 174 db.execSQL("CREATE INDEX is_favorite_index on media(is_favorite)"); 175 176 db.execSQL("CREATE INDEX local_id_album_index on album_media(local_id)"); 177 db.execSQL("CREATE INDEX cloud_id_album_index on album_media(cloud_id)"); 178 db.execSQL("CREATE INDEX date_taken_album_index on album_media(date_taken_ms)"); 179 db.execSQL("CREATE INDEX size_album_index on album_media(size_bytes)"); 180 db.execSQL("CREATE INDEX mime_type_album_index on album_media(mime_type)"); 181 } 182 clearPickerPrefs(Context context)183 private static void clearPickerPrefs(Context context) { 184 final SharedPreferences prefs = context.getSharedPreferences( 185 PickerSyncController.PICKER_SYNC_PREFS_FILE_NAME, Context.MODE_PRIVATE); 186 final SharedPreferences.Editor editor = prefs.edit(); 187 editor.clear(); 188 editor.commit(); 189 } 190 } 191