Android MediaProvider 数据库模式（二）：ICS 的更新

简介：

本文介绍 Android 4.0 （ICS） 相对于 2.3 的修改，在之前说过的内容这里不再赘述，要了解详情请到介绍 2.3 的文章：Android MediaProvider数据库模式

Android 4.0 相对于 2.3 数据库改动较大，主要是为了加入 MTP 文件传输模式的支持而做的修改：

• 原本只有多媒体文件（音乐/视频/图片）才会保存进数据库，现在所有文件均会保存进叫 files 的表，只是非多媒体文件不用分析（如分析专辑名等）。
• 相应地，原来的音乐、视频、图片的表变成了视图，是从 files 表生成的。其他辅助表例如专辑封面、艺术家、缩略图的表保持不变。
• MTP 文件传输模式下读取的文件，就是 files 表中存在的文件，若文件没有保存进 files 表则不会显示。
• 为了使 MTP 模式下手机端删除文件时能通知到主机，在数据库中加入了一个触发器 files_cleanup，里面使用了一个 SQLite 自定义函数来发送通知。只要删除 files 表就会发送通知。

虽然做了若干修改，但上层应用访问数据库完全不受影响，可以不用关心。但媒体文件扫描速度会变慢，因为写入数据库操作占用了扫描的大部分时间，而现在需要写入的文件大幅度增加。

下面是导出来的创建数据库的 SQL 语句，单击可以展开代码：

CREATE TABLE album_art (album_id INTEGER PRIMARY KEY,_data TEXT);
CREATE TABLE albums (album_id INTEGER PRIMARY KEY,album_key TEXT NOT NULL UNIQUE,album TEXT NOT NULL);
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE artists (artist_id INTEGER PRIMARY KEY,artist_key TEXT NOT NULL UNIQUE,artist TEXT NOT NULL);
CREATE TABLE audio_genres (_id INTEGER PRIMARY KEY,name TEXT NOT NULL);
CREATE TABLE audio_genres_map (_id INTEGER PRIMARY KEY,audio_id INTEGER NOT NULL,genre_id INTEGER NOT NULL);
CREATE TABLE audio_playlists_map (_id INTEGER PRIMARY KEY,audio_id INTEGER NOT NULL,playlist_id INTEGER NOT NULL,play_order INTEGER NOT NULL);
CREATE TABLE files (_id INTEGER PRIMARY KEY AUTOINCREMENT,_data TEXT,_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);
CREATE TABLE thumbnails (_id INTEGER PRIMARY KEY,_data TEXT,image_id INTEGER,kind INTEGER,width INTEGER,height INTEGER);
CREATE TABLE videothumbnails (_id INTEGER PRIMARY KEY,_data TEXT,video_id INTEGER,kind INTEGER,width INTEGER,height INTEGER);

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;
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;
CREATE VIEW artists_albums_map AS SELECT DISTINCT artist_id, album_id FROM audio_meta;
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;
CREATE VIEW audio_genres_map_noid AS SELECT audio_id,genre_id from audio_genres_map;
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;
CREATE VIEW audio_playlists AS SELECT _id,_data,name,date_added,date_modified FROM files WHERE media_type=4;
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;
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 != '');
CREATE VIEW searchhelpertitle AS SELECT * FROM audio ORDER BY title_key;
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;

CREATE INDEX album_id_idx ON files(album_id);
CREATE INDEX album_idx on albums(album);
CREATE INDEX albumkey_index on albums(album_key);
CREATE INDEX artist_id_idx ON files(artist_id);
CREATE INDEX artist_idx on artists(artist);
CREATE INDEX artistkey_index on artists(artist_key);
CREATE INDEX bucket_index on files(bucket_id, media_type, datetaken, _id);
CREATE INDEX bucket_name on files(bucket_id, media_type, bucket_display_name);
CREATE INDEX format_index ON files(format);
CREATE INDEX image_id_index on thumbnails(image_id);
CREATE INDEX media_type_index ON files(media_type);
CREATE INDEX parent_index ON files(parent);
CREATE INDEX path_index ON files(_data);
CREATE INDEX sort_index ON files(datetaken ASC, _id ASC);
CREATE INDEX title_idx ON files(title);
CREATE INDEX titlekey_index ON files(title_key);
CREATE INDEX video_id_index on videothumbnails(video_id);

CREATE TRIGGER albumart_cleanup1 DELETE ON albums BEGIN DELETE FROM album_art WHERE album_id = old.album_id;END;
CREATE TRIGGER albumart_cleanup2 DELETE ON album_art BEGIN SELECT _DELETE_FILE(old._data);END;
CREATE TRIGGER audio_delete INSTEAD OF DELETE ON audio BEGIN DELETE from audio_meta where _id=old._id;DELETE from audio_playlists_map where audio_id=old._id;DELETE from audio_genres_map where audio_id=old._id;END;
CREATE TRIGGER audio_genres_cleanup DELETE ON audio_genres BEGIN DELETE FROM audio_genres_map WHERE genre_id = old._id;END;
CREATE TRIGGER audio_meta_cleanup DELETE ON files WHEN old.media_type = 2 BEGIN DELETE FROM audio_genres_map WHERE audio_id = old._id;DELETE FROM audio_playlists_map WHERE audio_id = old._id;END;
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;
CREATE TRIGGER files_cleanup DELETE ON files BEGIN SELECT _OBJECT_REMOVED(old._id);END;
CREATE TRIGGER images_cleanup DELETE ON files WHEN old.media_type = 1 BEGIN DELETE FROM thumbnails WHERE image_id = old._id;SELECT _DELETE_FILE(old._data);END;
CREATE TRIGGER thumbnails_cleanup DELETE ON thumbnails BEGIN SELECT _DELETE_FILE(old._data);END;
CREATE TRIGGER video_cleanup DELETE ON files WHEN old.media_type = 3 BEGIN SELECT _DELETE_FILE(old._data);END;
CREATE TRIGGER videothumbnails_cleanup DELETE ON videothumbnails BEGIN SELECT _DELETE_FILE(old._data);END;