Find and connect to a database
You can find busybox on Benno’s site. If you can find the location of the .db file, you don’t really need busybox. Though i highly recommend it since it has many useful utilities.
C:\android>adb shell # export PATH=/data/busybox:$PATH export PATH=/data/busybox:$PATH # find data -name "*.db" -print find data -name "*.db" -print data/data/com.google.android.providers.contacts/databases/contacts.db data/data/com.google.android.providers.googleapps/databases/accounts.db data/data/com.google.android.providers.im/databases/im.db data/data/com.google.android.providers.media/databases/media.db data/data/com.google.android.providers.telephony/databases/mms.db data/data/com.google.android.providers.telephony/databases/sms.db data/data/com.google.android.providers.telephony/databases/telephony.db data/data/com.google.android.providers.settings/databases/settings.db data/data/com.google.android.maps/databases/maps.db # sqlite3 data/data/com.google.android.providers.contacts/databases/contacts.db sqlite3 data/data/com.google.android.providers.contacts/databases/contacts.db SQLite version 3.5.0 Enter ".help" for instructions sqlite>
Find the list of tables and their structure
sqlite> .tables .tables _deleted_people contact_methods peopleLookup calls people phones sqlite> .schema people .schema people CREATE TABLE people (_id INTEGER PRIMARY KEY,_sync_account TEXT,_sync_id TEXT,_sync_time TEXT,_sync_version TEXT,_sync_local_id INTEGER,_sync_dirty INTEGER,_sync_mark INTEGER,name TEXT NOT NULL,notes TEXT,photo TEXT,company TEXT,title TEXT,preferred_phone INTEGER,preferred_email INTEGER); CREATE INDEX peopleSyncIdIndex ON people (_sync_id); CREATE TRIGGER contact_cleanup DELETE ON people BEGIN DELETE FROM peopleLookup WHERE source = old._id;DELETE FROM phones WHERE person = old._id;DELETE FROM contact_methods WHERE person = old._id;UPDATE calls SET person = NULL WHERE person = old._id;END; CREATE TRIGGER contact_to_deleted DELETE ON people WHEN old._sync_id is not null BEGIN INSERT INTO _deleted_people (_sync_id, _sync_account, _sync_version) VALUES (old._sync_id, old._sync_account, old._sync_version);END; CREATE TRIGGER peopleLookup_insert AFTER INSERT ON people BEGIN SELECT _TOKENIZE('peopleLookup', new._id, new.name, ' ');END; CREATE TRIGGER peopleLookup_update UPDATE OF name ON people BEGIN DELETE FROM peopleLookup WHERE source = new._id;SELECT _TOKENIZE('peopleLookup', new._id, new.name, ' ');END; sqlite> .schema phones .schema phones CREATE TABLE phones (_id INTEGER PRIMARY KEY,person INTEGER,type INTEGER,number TEXT,number_key TEXT,label TEXT); CREATE INDEX phonesIndex1 ON phones (person); CREATE INDEX phonesIndex2 ON phones (number_key); CREATE TRIGGER phones_delete DELETE ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; CREATE TRIGGER phones_insert INSERT ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person;END; CREATE TRIGGER phones_update UPDATE ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; CREATE TRIGGER preferred_phone_cleanup DELETE ON phones BEGIN UPDATE people SET preferred_phone = NULL WHERE preferred_phone = old._id; END;
Print some of the information from the tables
sqlite> .header on .header on sqlite> .mode column .mode column sqlite> select * from phones; select * from phones; _id person type number number_key label ---------- ---------- ---------- ------------ ------------ ---------- 1 1 1 +15085551212 21215558051+ 2 1 0 +17815551212 21215551871+ 3 1 2 +16175551212 21215557161+ sqlite> select * from people; select * from people; _id _sync_account _sync_id _sync_time _sync_version _sync_local_id _sync_dirty _sync_mark name notes photo company title preferred_phone preferred_email ---------- ------------- ---------- ---------- ------------- -------------- ----------- ---------- ---------------- ---------- ---------- ---------- ---------- --------------- --------------- 1 1 Davanum Srinivas 1
Other commands
sqlite> .help .help .bail ON|OFF Stop after hitting an error. Default OFF .databases List names and files of attached databases .dump ?TABLE? ... Dump the database in an SQL text format .echo ON|OFF Turn command echo on or off .exit Exit this program .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. .header(s) ON|OFF Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices TABLE Show names of all indices on TABLE .load FILE ?ENTRY? Load an extension library .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns. (See .width) html HTML
very interesting. thanks.
Comment by Josue — December 26, 2007 @ 1:37 am
thanks
Comment by Naveed — March 10, 2008 @ 4:14 am
How to connect android with sqlite
Comment by Tsolmon — November 28, 2008 @ 2:39 am
How to connect database in android..
Comment by Sunil — July 28, 2009 @ 4:59 am
hi,
i don’t have any .db file! and i don’t know how i can change it! without .db file, i can’t open a browser on my emulator! can u help me to chang it??
thanks,
sisi
Comment by sisi3092 — August 20, 2009 @ 7:22 am
Try this,
Very simple Android Database
http://sarangasl.blogspot.com/2009/09/create-android-database.html
Comment by sara — September 29, 2009 @ 6:12 am
How do you get the code working??
Comment by Darkrune — October 1, 2009 @ 9:11 am
Very interesting and useful to know more about android database programming…………..
Comment by Paresh Mayani — March 4, 2010 @ 4:47 am
Excellent.. very useful info for beginner to DBs.
Thanks !!!
Comment by RAVI — September 29, 2010 @ 4:51 pm
i cannot find the data/data/../databases/ folder can u help me.
Thanks in advance.
Comment by yuvarajan — June 6, 2011 @ 8:14 am
[…] https://davanum.wordpress.com/2007/12/11/android-how-to-poke-around-the-sqlite3-databases/ KategorienAndroid Tags: Kommentare (0) Trackbacks (0) Einen Kommentar schreiben Trackback […]
Pingback by Bastl.org » Access SQLite-DB on Android-Handy via adb — September 20, 2011 @ 5:29 am
[…] your database and gives a few useful examples on how to use sqlite3 to access the DB and tables.https://davanum.wordpress.com/2007/12/11/android-how-to-poke-around-the-sqlite3-databases/ Related […]
Pingback by How do I find the database file on an Android virtual device | Android Development tutorial | Android Development tutorial — June 13, 2012 @ 6:56 pm
How to build the sqlite3 binary and library yourself.
I have put together some build scripts to compile SQLite for Android Native Code using the Android NDK. It builds the SQLite CLI in two versions: Statically and Dynamically Linked, as well as it’s Static and Shared Libraries. You may get the scripts from my GitHub and build the binaries yourself:
https://github.com/stockrt/sqlite3-android
Hope this will be useful for someone.
Comment by Rogério Schneider — January 11, 2015 @ 8:01 pm