sqlite3能够直接查看数据库的内容,有时方便调试,首先使用adb或者串口连接手机
1.打开数据库
1)如果不知道数据库的名称,可以先查找
/ # find / -name *.db
/data/data/com.cooliris.media/databases/picasa.db
/data/data/com.android.phone/databases/num_addr.db
/data/data/com.android.providers.media/databases/external-c4a1ebc0.db
/data/data/com.android.providers.media/databases/internal.db
/data/system/accounts.db
......
2)打开,就以internal.db为例
/ # sqlite3 /data/data/com.android.providers.media/databases/internal.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite>
2.查看有那些表
sqlite> .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_meta (_id INTEGER PRIMARY KEY,_data TEXT NOT NULL,_display_name TEXT,_size INTEGER,mime_type TEXT,date_added INTEGER,date_modified INTEGER,drm_type 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 images (_id INTEGER PRIMARY KEY,_data TEXT,_size INTEGER,_display_name TEXT,mime_type TEXT,title TEXT,date_added INTEGER,date_modified INTEGER,drm_type 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,drm_type 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 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) 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 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 INDEX video_id_index on videothumbnails(video_id);
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 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;
CREATE TRIGGER videothumbnails_cleanup DELETE ON videothumbnails BEGIN SELECT _DELETE_FILE(old._data);END;
sqlite>
以上以CREATE TABLE开头的都是表了,例如 CREATE TABLE video
3.查看表中的数据,以audio为例
sqlite> select * from audio;
1|/system/media/audio/ui/KeypressSpacebar.ogg|KeypressSpacebar.ogg|7392|application/ogg|1279692229|1279624670|0|KeypressSpacebar|279|1||1|0||0|1|0|0|0||1&&&&?|Copyright 2009 Android Open Source Project|178638153|ui
2|/system/media/audio/ui/KeypressReturn.wav|KeypressReturn.wav|64320|audio/wav|1279692230|1279624670|0|KeypressReturn|363|2||1|0||0|1|0|0|0||2||<unknown>|178638153|ui
3|/system/media/audio/ui/camera_click.wav|camera_click.wav|13030|audio/wav|1279692230|1279624670|0|camera_click?|139|2||1|0||0|1|0|0|0||2||<unknown>|178638153|ui
select语句后别忘了加分号;
4.能输入SQL语句就能干很多很多事了~
5.帮助
sqlite>.help
6.退出 Ctrl+d
原文出处:http://forest606.blog.163.com/blog/static/134450089201062263432860/