기본 DB schema

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_meta (_id INTEGER PRIMARY KEY,_data TEXT NOT NULL,_display_name TEXT,
        _size INTEGER,mime_type TEXT,date_added INTEGER,date_modified INTEGER,title TEXT NOT NULL,
 title_key TEXT NOT NULL,duration INTEGER,artist_id INTEGER,composer TEXT,album_id INTEGER,
 track INTEGER,year INTEGER CHECK(year!=0),is_ringtone INTEGER,is_music INTEGER,is_alarm INTEGER,
 is_notification INTEGER, is_podcast INTEGER, bookmark INTEGER);

 

 

CREATE TABLE audio_playlists (_id INTEGER PRIMARY KEY,_data TEXT,name TEXT NOT NULL,date_added INTEGER,date_modified INTEGER);

 

 

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 images (_id INTEGER PRIMARY KEY,_data TEXT,_size INTEGER,_display_name TEXT,mime_type TEXT,
 title TEXT,date_added INTEGER,date_modified INTEGER,description TEXT,picasa_id TEXT,
 isprivate INTEGER,latitude DOUBLE,longitude DOUBLE,datetaken INTEGER,orientation INTEGER,
 mini_thumb_magic INTEGER,bucket_id TEXT,bucket_display_name TEXT);

 

 

CREATE TABLE thumbnails (_id INTEGER PRIMARY KEY,_data TEXT,image_id INTEGER,kind INTEGER,width INTEGER,height INTEGER);

 

 

CREATE TABLE video (_id INTEGER PRIMARY KEY,_data TEXT NOT NULL,_display_name TEXT,_size INTEGER,
 mime_type TEXT,date_added INTEGER,date_modified INTEGER,title TEXT,duration INTEGER,artist TEXT,
 album TEXT,resolution TEXT,description TEXT,isprivate INTEGER,tags TEXT,category TEXT,
 language TEXT,mini_thumb_data TEXT,latitude DOUBLE,longitude DOUBLE,datetaken INTEGER,
 mini_thumb_magic INTEGER, bucket_id TEXT, bucket_display_name TEXT, bookmark 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) AS number_of_albums, 
 COUNT(*) AS number_of_tracks 
 FROM audio WHEREis_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 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 INDEX albumkey_index on albums(album_key);

 

CREATE INDEX artistkey_index on artists(artist_key);

 

CREATE INDEX image_id_index on thumbnails(image_id);

 

CREATE INDEX mini_thumb_magic_index on images(mini_thumb_magic);

 

CREATE INDEX sort_index on images(datetaken ASC, _id ASC);

 

CREATE INDEX titlekey_index on audio_meta(title_key);

 

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 audio_meta 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 audio_playlists BEGIN DELETE FROM audio_playlists_map WHERE playlist_id = old._id;

SELECT _DELETE_FILE(old._data);

 

END;

 

CREATE TRIGGER images_cleanup DELETE ON images 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 video BEGIN SELECT _DELETE_FILE(old._data);

 

END;

[출처] Android Side - http://www.androidside.com/bbs/board.php?bo_table=B52&wr_id=303