How to poke around the sqlite3 databases
来源:互联网 发布:mac定制无瑕粉底液n18 编辑:程序博客网 时间:2024/06/05 19:42
Android – How to poke around the sqlite3 databases
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
Ref:
http://davanum.wordpress.com/2007/12/11
- How to poke around the sqlite3 databases
- How to Backup Different Release Databases with the Same Catalog Database
- Sliding UITextFields around to avoid the keyboard
- how to get around lines to show and for analyze!!
- How to work around Android’s 24 MB memory limit
- How to sensibly split an array around an object
- How can JDBC connect to each type of databases?
- How to Develop with ADF Business Components for Multiple Databases
- How to support full Unicode in MySQL databases
- How to move the databases that are used by SharePoint Portal Server 2003 to a computer that is running SQL Server
- MongoDB, Cassandra, and HBase -- the three NoSQL databases to watch
- Designing Data Storage Architecture- How to Sync Large SQL Server Databases to SQL Azure
- How to be the rich?
- How to see the world?
- How to change the dbo
- How to install the wine
- How to execute the jar
- How to load the treeview
- Factory Method
- 图片生成指定大小的略缩图(只支持JPG)
- MPI打包和拆包测试FROTRAN代码
- Oracle10G如何切换数据库登录
- SpringMVC 中整合JSON、XML视图二
- How to poke around the sqlite3 databases
- %5程序员
- 从汇编层面深度剖析C++虚函数
- 一个数学问题
- asp.net 把sql表中数据生成为insert语句
- ListControl用法总会(1)
- Abstract Factory
- 转:VS2010和VS2008 KB2465361之后,MFC静态链接 EXE体积变大问题
- C/C++ 笔试、面试题目大汇总