Show me the code! – By Davanum Srinivas

December 11, 2007

Android – How to poke around the sqlite3 databases

Filed under: Uncategorized — Davanum Srinivas @ 10:00 pm

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  code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode

Notes


More info in the original posts from Jason and Luisa.

About these ads

12 Comments »

  1. very interesting. thanks.

    Comment by Josue — December 26, 2007 @ 1:37 am

  2. thanks

    Comment by Naveed — March 10, 2008 @ 4:14 am

  3. How to connect android with sqlite

    Comment by Tsolmon — November 28, 2008 @ 2:39 am

  4. How to connect database in android..

    Comment by Sunil — July 28, 2009 @ 4:59 am

  5. 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

    • How do you get the code working??

      Comment by Darkrune — October 1, 2009 @ 9:11 am

  6. Very interesting and useful to know more about android database programming…………..

    Comment by Paresh Mayani — March 4, 2010 @ 4:47 am

  7. Excellent.. very useful info for beginner to DBs.
    Thanks !!!

    Comment by RAVI — September 29, 2010 @ 4:51 pm

  8. i cannot find the data/data/../databases/ folder can u help me.

    Thanks in advance.

    Comment by yuvarajan — June 6, 2011 @ 8:14 am

  9. [...] http://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

  10. [...] your database and gives a few useful examples on how to use sqlite3 to access the DB and tables.http://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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 132 other followers