Simple Android SQLite operation
18:34:32 | category:
Android | font size subscription
Sqlite3 can directly view the database content, and sometimes facilitate debugging. First, use ADB or serial port to connect to the mobile phone
1. open Database 1) If you do not know the database name, you can first find/# Find/-name *. DB/data/COM. cooliris. media/databases/Picasa. DB/data/COM. android. phone/databases/num_addr.db/data/COM. android. providers. media/databases/external-c4a1ebc0.db/data/COM. android. providers. media/databases/internal. DB/data/system/accounts. DB ...... 2) open the file with internal. dB for example/# sqlite3/data/COM. android. providers. media/databases/internal. dbsqlite version 3.5.9enter ". help "for instructionssqlite> 2. check the tables SQLite>. schemacreate 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, long1_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, long1_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 region, count (*) as region 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, 'artlist' 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, 'alipay' 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 indexes 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> All Tables starting with "create table" are listed above. For example, create table video3. you can use audio as an example SQLite> select * from audio; 1 |/ System/Media/Audio/UI/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 | ui2 |/system/Media/Audio/UI/keypressreturn.wav | 64320 | audio/WAV | 1279692230 | 1279624670 | 0 | keypressreturn | 363 | 2 | 1 | 0 | 0 | 1 | 0 | 0 | 2 | <Unknown> | 178638153 | ui3 |/system/Media/Audio/UI/ camera_click.wav | 13030 | audio/WAV | 1279692230 | 1279624670 | 0 | camera_click? | 139 | 2 | 1 | 0 | 0 | 1 | 0 | 0 | 2 | <Unknown> | 178638153 | do not forget the extra points after the uiselect statement; 4. you can input SQL statements to do a lot of things ~ 5. Help SQLite>. help6. exit Ctrl + d
Source: http://forest606.blog.163.com/blog/static/134450089201062263432860/
