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     private static final int VERSION_U = 11;
44     public static final int VERSION_LATEST = VERSION_U;
45 
46     final Context mContext;
47     final String mName;
48     final int mVersion;
49 
PickerDatabaseHelper(Context context)50     public PickerDatabaseHelper(Context context) {
51         this(context, PICKER_DATABASE_NAME, VERSION_LATEST);
52     }
53 
PickerDatabaseHelper(Context context, String name, int version)54     public PickerDatabaseHelper(Context context, String name, int version) {
55         super(context, name, null, version);
56         mContext = context;
57         mName = name;
58         mVersion = version;
59 
60         setWriteAheadLoggingEnabled(true);
61     }
62 
63     @Override
onCreate(final SQLiteDatabase db)64     public void onCreate(final SQLiteDatabase db) {
65         Log.v(TAG, "onCreate() for " + mName);
66 
67         resetData(db);
68     }
69 
70     @Override
onUpgrade(final SQLiteDatabase db, final int oldV, final int newV)71     public void onUpgrade(final SQLiteDatabase db, final int oldV, final int newV) {
72         Log.v(TAG, "onUpgrade() for " + mName + " from " + oldV + " to " + newV);
73 
74         resetData(db);
75     }
76 
77     @Override
onDowngrade(final SQLiteDatabase db, final int oldV, final int newV)78     public void onDowngrade(final SQLiteDatabase db, final int oldV, final int newV) {
79         Log.v(TAG, "onDowngrade() for " + mName + " from " + oldV + " to " + newV);
80 
81         resetData(db);
82     }
83 
84     @Override
onConfigure(SQLiteDatabase db)85     public void onConfigure(SQLiteDatabase db) {
86         Log.v(TAG, "onConfigure() for " + mName);
87 
88         db.setCustomScalarFunction("_GET_EXTENSION", (arg) -> {
89             Trace.beginSection("_GET_EXTENSION");
90             try {
91                 return getExtensionFromMimeType(arg);
92             } finally {
93                 Trace.endSection();
94             }
95         });
96     }
97 
resetData(SQLiteDatabase db)98     private void resetData(SQLiteDatabase db) {
99         clearPickerPrefs(mContext);
100 
101         dropAllTables(db);
102 
103         createLatestSchema(db);
104         createLatestIndexes(db);
105     }
106 
107     @VisibleForTesting
dropAllTables(SQLiteDatabase db)108     static void dropAllTables(SQLiteDatabase db) {
109         // drop all tables
110         Cursor c = db.query("sqlite_master", new String[] {"name"}, "type is 'table'", null, null,
111                 null, null);
112         while (c.moveToNext()) {
113             if (c.getString(0).startsWith("sqlite_")) continue;
114             db.execSQL("DROP TABLE IF EXISTS " + c.getString(0));
115         }
116         c.close();
117     }
118 
createLatestSchema(SQLiteDatabase db)119     private static void createLatestSchema(SQLiteDatabase db) {
120 
121         db.execSQL("CREATE TABLE media (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
122                 + "local_id TEXT,"
123                 + "cloud_id TEXT UNIQUE,"
124                 + "is_visible INTEGER CHECK(is_visible == 1),"
125                 + "date_taken_ms INTEGER NOT NULL,"
126                 + "sync_generation INTEGER NOT NULL CHECK(sync_generation >= 0),"
127                 + "width INTEGER,"
128                 + "height INTEGER,"
129                 + "orientation INTEGER,"
130                 + "size_bytes INTEGER NOT NULL CHECK(size_bytes > 0),"
131                 + "duration_ms INTEGER CHECK(duration_ms >= 0),"
132                 + "mime_type TEXT NOT NULL,"
133                 + "standard_mime_type_extension INTEGER,"
134                 + "is_favorite INTEGER,"
135                 + "CHECK(local_id IS NOT NULL OR cloud_id IS NOT NULL),"
136                 + "UNIQUE(local_id, is_visible))");
137 
138         db.execSQL("CREATE TABLE album_media (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
139                 + "local_id TEXT,"
140                 + "cloud_id TEXT,"
141                 + "album_id TEXT,"
142                 + "date_taken_ms INTEGER NOT NULL,"
143                 + "sync_generation INTEGER NOT NULL CHECK(sync_generation >= 0),"
144                 + "size_bytes INTEGER NOT NULL CHECK(size_bytes > 0),"
145                 + "duration_ms INTEGER CHECK(duration_ms >= 0),"
146                 + "mime_type TEXT NOT NULL,"
147                 + "standard_mime_type_extension INTEGER,"
148                 + "CHECK((local_id IS NULL AND cloud_id IS NOT NULL) "
149                 + "OR (local_id IS NOT NULL AND cloud_id IS NULL)),"
150                 + "UNIQUE(local_id,  album_id),"
151                 + "UNIQUE(cloud_id, album_id))");
152     }
153 
createLatestIndexes(SQLiteDatabase db)154     private static void createLatestIndexes(SQLiteDatabase db) {
155 
156         db.execSQL("CREATE INDEX local_id_index on media(local_id)");
157         db.execSQL("CREATE INDEX cloud_id_index on media(cloud_id)");
158         db.execSQL("CREATE INDEX is_visible_index on media(is_visible)");
159         db.execSQL("CREATE INDEX size_index on media(size_bytes)");
160         db.execSQL("CREATE INDEX mime_type_index on media(mime_type)");
161         db.execSQL("CREATE INDEX is_favorite_index on media(is_favorite)");
162         db.execSQL("CREATE INDEX date_taken_row_id_index on media(date_taken_ms, _id)");
163 
164         db.execSQL("CREATE INDEX local_id_album_index on album_media(local_id)");
165         db.execSQL("CREATE INDEX cloud_id_album_index on album_media(cloud_id)");
166         db.execSQL("CREATE INDEX size_album_index on album_media(size_bytes)");
167         db.execSQL("CREATE INDEX mime_type_album_index on album_media(mime_type)");
168         db.execSQL("CREATE INDEX date_taken_album_row_id_index on album_media(date_taken_ms,_id)");
169     }
170 
clearPickerPrefs(Context context)171     private static void clearPickerPrefs(Context context) {
172         final SharedPreferences prefs = context.getSharedPreferences(
173                 PickerSyncController.PICKER_SYNC_PREFS_FILE_NAME, Context.MODE_PRIVATE);
174         final SharedPreferences.Editor editor = prefs.edit();
175         editor.clear();
176         editor.commit();
177     }
178 }
179