More robust database upgrade logic.
Idiomatic database upgrade logic is progressive, so we only need to
make a single call in passing to update the schema. Restructure the
existing upgrade steps to follow this design, and add tests that
confirm upgrade and downgrade behavior for the last several major
letter releases.
Bug: 122248315
Test: atest MediaProviderTests
Change-Id: Ia48c4a92741aef31c4308fe399d4cdb44fbc8a53
diff --git a/src/com/android/providers/media/MediaProvider.java b/src/com/android/providers/media/MediaProvider.java
index 797a8eb..9ffb673 100644
--- a/src/com/android/providers/media/MediaProvider.java
+++ b/src/com/android/providers/media/MediaProvider.java
@@ -844,7 +844,7 @@
+ " FROM files WHERE is_download=1");
}
- private static void updateFromKKSchema(SQLiteDatabase db) {
+ private static void updateCollationKeys(SQLiteDatabase db) {
// Delete albums and artists, then clear the modification time on songs, which
// will cause the media scanner to rescan everything, rebuilding the artist and
// album tables along the way, while preserving playlists.
@@ -852,18 +852,15 @@
// collation keys
db.execSQL("DELETE from albums");
db.execSQL("DELETE from artists");
- db.execSQL("ALTER TABLE files ADD COLUMN title_resource_uri TEXT DEFAULT NULL");
- db.execSQL("UPDATE files SET date_modified=0");
- updateAddColorSpaces(db);
+ db.execSQL("UPDATE files SET date_modified=0;");
}
- private static void updateFromOCSchema(SQLiteDatabase db) {
+ private static void updateAddTitleResource(SQLiteDatabase db) {
// Add the column used for title localization, and force a rescan of any
// ringtones, alarms and notifications that may be using it.
db.execSQL("ALTER TABLE files ADD COLUMN title_resource_uri TEXT DEFAULT NULL");
db.execSQL("UPDATE files SET date_modified=0"
+ " WHERE (is_alarm IS 1) OR (is_ringtone IS 1) OR (is_notification IS 1)");
- updateAddColorSpaces(db);
}
private static void updateAddOwnerPackageName(SQLiteDatabase db, boolean internal) {
@@ -947,11 +944,13 @@
if (fromVersion < 700) {
// Anything older than KK is recreated from scratch
createLatestSchema(db, internal);
- } else if (fromVersion < 800) {
- updateFromKKSchema(db);
- } else if (fromVersion < 900) {
- updateFromOCSchema(db);
} else {
+ if (fromVersion < 800) {
+ updateCollationKeys(db);
+ }
+ if (fromVersion < 900) {
+ updateAddTitleResource(db);
+ }
if (fromVersion < 1000) {
updateAddOwnerPackageName(db, internal);
}
diff --git a/tests/src/com/android/providers/media/DatabaseHelperTest.java b/tests/src/com/android/providers/media/DatabaseHelperTest.java
index c4fa2e3..05c715b 100644
--- a/tests/src/com/android/providers/media/DatabaseHelperTest.java
+++ b/tests/src/com/android/providers/media/DatabaseHelperTest.java
@@ -51,8 +51,19 @@
}
@Test
- public void testDowngrade() throws Exception {
- try (DatabaseHelper helper = new DatabaseHelperQ(getContext(), TEST_DB)) {
+ public void testQtoO() throws Exception {
+ assertDowngrade(DatabaseHelperQ.class, DatabaseHelperO.class);
+ }
+
+ @Test
+ public void testQtoP() throws Exception {
+ assertDowngrade(DatabaseHelperQ.class, DatabaseHelperP.class);
+ }
+
+ private void assertDowngrade(Class<? extends DatabaseHelper> before,
+ Class<? extends DatabaseHelper> after) throws Exception {
+ try (DatabaseHelper helper = before.getConstructor(Context.class, String.class)
+ .newInstance(getContext(), TEST_DB)) {
SQLiteDatabase db = helper.getWritableDatabase();
{
final ContentValues values = new ContentValues();
@@ -70,7 +81,8 @@
}
// Downgrade will wipe data, but at least we don't crash
- try (DatabaseHelper helper = new DatabaseHelperP(getContext(), TEST_DB)) {
+ try (DatabaseHelper helper = after.getConstructor(Context.class, String.class)
+ .newInstance(getContext(), TEST_DB)) {
SQLiteDatabase db = helper.getWritableDatabase();
try (Cursor c = db.query("files", null, null, null, null, null, null, null)) {
assertEquals(0, c.getCount());
@@ -79,8 +91,19 @@
}
@Test
+ public void testOtoQ() throws Exception {
+ assertUpgrade(DatabaseHelperO.class, DatabaseHelperQ.class);
+ }
+
+ @Test
public void testPtoQ() throws Exception {
- try (DatabaseHelper helper = new DatabaseHelperP(getContext(), TEST_DB)) {
+ assertUpgrade(DatabaseHelperP.class, DatabaseHelperQ.class);
+ }
+
+ private void assertUpgrade(Class<? extends DatabaseHelper> before,
+ Class<? extends DatabaseHelper> after) throws Exception {
+ try (DatabaseHelper helper = before.getConstructor(Context.class, String.class)
+ .newInstance(getContext(), TEST_DB)) {
SQLiteDatabase db = helper.getWritableDatabase();
{
final ContentValues values = new ContentValues();
@@ -124,7 +147,8 @@
}
}
- try (DatabaseHelper helper = new DatabaseHelperQ(getContext(), TEST_DB)) {
+ try (DatabaseHelper helper = after.getConstructor(Context.class, String.class)
+ .newInstance(getContext(), TEST_DB)) {
SQLiteDatabase db = helper.getWritableDatabase();
try (Cursor c = db.query("files", null, FileColumns.DISPLAY_NAME + "='global.jpg'",
null, null, null, null)) {
@@ -169,6 +193,17 @@
}
}
+ private static class DatabaseHelperO extends DatabaseHelper {
+ public DatabaseHelperO(Context context, String name) {
+ super(context, name, MediaProvider.VERSION_O, false, false, null);
+ }
+
+ @Override
+ public void onCreate(SQLiteDatabase db) {
+ createOSchema(db, false);
+ }
+ }
+
private static class DatabaseHelperP extends DatabaseHelper {
public DatabaseHelperP(Context context, String name) {
super(context, name, MediaProvider.VERSION_P, false, false, null);
@@ -189,6 +224,130 @@
/**
* Snapshot of
* {@link MediaProvider#createLatestSchema(SQLiteDatabase, boolean)} as of
+ * {@link android.os.Build.VERSION_CODES#O}.
+ */
+ private static void createOSchema(SQLiteDatabase db, boolean internal) {
+ makePristineSchema(db);
+
+ db.execSQL("CREATE TABLE android_metadata (locale TEXT)");
+ db.execSQL("CREATE TABLE thumbnails (_id INTEGER PRIMARY KEY,_data TEXT,image_id INTEGER,"
+ + "kind INTEGER,width INTEGER,height INTEGER)");
+ db.execSQL("CREATE TABLE artists (artist_id INTEGER PRIMARY KEY,"
+ + "artist_key TEXT NOT NULL UNIQUE,artist TEXT NOT NULL)");
+ db.execSQL("CREATE TABLE albums (album_id INTEGER PRIMARY KEY,"
+ + "album_key TEXT NOT NULL UNIQUE,album TEXT NOT NULL)");
+ db.execSQL("CREATE TABLE album_art (album_id INTEGER PRIMARY KEY,_data TEXT)");
+ db.execSQL("CREATE TABLE videothumbnails (_id INTEGER PRIMARY KEY,_data TEXT,"
+ + "video_id INTEGER,kind INTEGER,width INTEGER,height INTEGER)");
+ db.execSQL("CREATE TABLE files (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
+ + "_data TEXT UNIQUE COLLATE NOCASE,_size INTEGER,format INTEGER,parent INTEGER,"
+ + "date_added INTEGER,date_modified INTEGER,mime_type TEXT,title TEXT,"
+ + "description TEXT,_display_name TEXT,picasa_id TEXT,orientation INTEGER,"
+ + "latitude DOUBLE,longitude DOUBLE,datetaken INTEGER,mini_thumb_magic INTEGER,"
+ + "bucket_id TEXT,bucket_display_name TEXT,isprivate INTEGER,title_key TEXT,"
+ + "artist_id INTEGER,album_id INTEGER,composer TEXT,track INTEGER,"
+ + "year INTEGER CHECK(year!=0),is_ringtone INTEGER,is_music INTEGER,"
+ + "is_alarm INTEGER,is_notification INTEGER,is_podcast INTEGER,album_artist TEXT,"
+ + "duration INTEGER,bookmark INTEGER,artist TEXT,album TEXT,resolution TEXT,"
+ + "tags TEXT,category TEXT,language TEXT,mini_thumb_data TEXT,name TEXT,"
+ + "media_type INTEGER,old_id INTEGER,storage_id INTEGER,is_drm INTEGER,"
+ + "width INTEGER, height INTEGER)");
+ db.execSQL("CREATE TABLE log (time DATETIME, message TEXT)");
+ if (!internal) {
+ db.execSQL("CREATE TABLE audio_genres (_id INTEGER PRIMARY KEY,name TEXT NOT NULL)");
+ db.execSQL("CREATE TABLE audio_genres_map (_id INTEGER PRIMARY KEY,"
+ + "audio_id INTEGER NOT NULL,genre_id INTEGER NOT NULL,"
+ + "UNIQUE (audio_id,genre_id) ON CONFLICT IGNORE)");
+ db.execSQL("CREATE TABLE audio_playlists_map (_id INTEGER PRIMARY KEY,"
+ + "audio_id INTEGER NOT NULL,playlist_id INTEGER NOT NULL,"
+ + "play_order INTEGER NOT NULL)");
+ db.execSQL("CREATE TRIGGER audio_genres_cleanup DELETE ON audio_genres BEGIN DELETE"
+ + " FROM audio_genres_map WHERE genre_id = old._id;END");
+ db.execSQL("CREATE TRIGGER audio_playlists_cleanup DELETE ON files"
+ + " WHEN old.media_type=4"
+ + " BEGIN DELETE FROM audio_playlists_map WHERE playlist_id = old._id;"
+ + "SELECT _DELETE_FILE(old._data);END");
+ db.execSQL("CREATE TRIGGER files_cleanup DELETE ON files"
+ + " BEGIN SELECT _OBJECT_REMOVED(old._id);END");
+ db.execSQL("CREATE VIEW audio_playlists AS SELECT _id,_data,name,date_added,date_modified"
+ + " FROM files WHERE media_type=4");
+ }
+
+ db.execSQL("CREATE INDEX image_id_index on thumbnails(image_id)");
+ db.execSQL("CREATE INDEX album_idx on albums(album)");
+ db.execSQL("CREATE INDEX albumkey_index on albums(album_key)");
+ db.execSQL("CREATE INDEX artist_idx on artists(artist)");
+ db.execSQL("CREATE INDEX artistkey_index on artists(artist_key)");
+ db.execSQL("CREATE INDEX video_id_index on videothumbnails(video_id)");
+ db.execSQL("CREATE INDEX album_id_idx ON files(album_id)");
+ db.execSQL("CREATE INDEX artist_id_idx ON files(artist_id)");
+ db.execSQL("CREATE INDEX bucket_index on files(bucket_id,media_type,datetaken, _id)");
+ db.execSQL("CREATE INDEX bucket_name on files(bucket_id,media_type,bucket_display_name)");
+ db.execSQL("CREATE INDEX format_index ON files(format)");
+ db.execSQL("CREATE INDEX media_type_index ON files(media_type)");
+ db.execSQL("CREATE INDEX parent_index ON files(parent)");
+ db.execSQL("CREATE INDEX path_index ON files(_data)");
+ db.execSQL("CREATE INDEX sort_index ON files(datetaken ASC, _id ASC)");
+ db.execSQL("CREATE INDEX title_idx ON files(title)");
+ db.execSQL("CREATE INDEX titlekey_index ON files(title_key)");
+
+ db.execSQL("CREATE VIEW audio_meta AS SELECT _id,_data,_display_name,_size,mime_type,"
+ + "date_added,is_drm,date_modified,title,title_key,duration,artist_id,composer,"
+ + "album_id,track,year,is_ringtone,is_music,is_alarm,is_notification,is_podcast,"
+ + "bookmark,album_artist FROM files WHERE media_type=2");
+ db.execSQL("CREATE VIEW artists_albums_map AS SELECT DISTINCT artist_id, album_id"
+ + " FROM audio_meta");
+ db.execSQL("CREATE VIEW audio as SELECT * FROM audio_meta LEFT OUTER JOIN artists"
+ + " ON audio_meta.artist_id=artists.artist_id LEFT OUTER JOIN albums"
+ + " ON audio_meta.album_id=albums.album_id");
+ db.execSQL("CREATE VIEW album_info AS SELECT audio.album_id AS _id, album, album_key,"
+ + " MIN(year) AS minyear, MAX(year) AS maxyear, artist, artist_id, artist_key,"
+ + " count(*) AS numsongs,album_art._data AS album_art FROM audio"
+ + " LEFT OUTER JOIN album_art ON audio.album_id=album_art.album_id WHERE is_music=1"
+ + " GROUP BY audio.album_id");
+ db.execSQL("CREATE VIEW searchhelpertitle AS SELECT * FROM audio ORDER BY title_key");
+ db.execSQL("CREATE VIEW artist_info AS SELECT artist_id AS _id, artist, artist_key,"
+ + " COUNT(DISTINCT album_key) AS number_of_albums, COUNT(*) AS number_of_tracks"
+ + " FROM audio"
+ + " WHERE is_music=1 GROUP BY artist_key");
+ db.execSQL("CREATE VIEW search AS SELECT _id,'artist' AS mime_type,artist,NULL AS album,"
+ + "NULL AS title,artist AS text1,NULL AS text2,number_of_albums AS data1,"
+ + "number_of_tracks AS data2,artist_key AS match,"
+ + "'content://media/external/audio/artists/'||_id AS suggest_intent_data,"
+ + "1 AS grouporder FROM artist_info WHERE (artist!='<unknown>')"
+ + " UNION ALL SELECT _id,'album' AS mime_type,artist,album,"
+ + "NULL AS title,album AS text1,artist AS text2,NULL AS data1,"
+ + "NULL AS data2,artist_key||' '||album_key AS match,"
+ + "'content://media/external/audio/albums/'||_id AS suggest_intent_data,"
+ + "2 AS grouporder FROM album_info"
+ + " WHERE (album!='<unknown>')"
+ + " UNION ALL SELECT searchhelpertitle._id AS _id,mime_type,artist,album,title,"
+ + "title AS text1,artist AS text2,NULL AS data1,"
+ + "NULL AS data2,artist_key||' '||album_key||' '||title_key AS match,"
+ + "'content://media/external/audio/media/'||searchhelpertitle._id"
+ + " AS suggest_intent_data,"
+ + "3 AS grouporder FROM searchhelpertitle WHERE (title != '')");
+ db.execSQL("CREATE VIEW audio_genres_map_noid AS SELECT audio_id,genre_id"
+ + " FROM audio_genres_map");
+ db.execSQL("CREATE VIEW images AS SELECT _id,_data,_size,_display_name,mime_type,title,"
+ + "date_added,date_modified,description,picasa_id,isprivate,latitude,longitude,"
+ + "datetaken,orientation,mini_thumb_magic,bucket_id,bucket_display_name,width,"
+ + "height FROM files WHERE media_type=1");
+ db.execSQL("CREATE VIEW video AS SELECT _id,_data,_display_name,_size,mime_type,"
+ + "date_added,date_modified,title,duration,artist,album,resolution,description,"
+ + "isprivate,tags,category,language,mini_thumb_data,latitude,longitude,datetaken,"
+ + "mini_thumb_magic,bucket_id,bucket_display_name,bookmark,width,height"
+ + " FROM files WHERE media_type=3");
+
+ db.execSQL("CREATE TRIGGER albumart_cleanup1 DELETE ON albums BEGIN DELETE FROM album_art"
+ + " WHERE album_id = old.album_id;END");
+ db.execSQL("CREATE TRIGGER albumart_cleanup2 DELETE ON album_art"
+ + " BEGIN SELECT _DELETE_FILE(old._data);END");
+ }
+
+ /**
+ * Snapshot of
+ * {@link MediaProvider#createLatestSchema(SQLiteDatabase, boolean)} as of
* {@link android.os.Build.VERSION_CODES#P}.
*/
private static void createPSchema(SQLiteDatabase db, boolean internal) {
@@ -309,6 +468,4 @@
db.execSQL("CREATE TRIGGER albumart_cleanup2 DELETE ON album_art"
+ " BEGIN SELECT _DELETE_FILE(old._data);END");
}
-
-
}