Where is SQLite?
SQLite is available on the Android device itself. The executable is in the /system/bin directory of the device. You can see that this directory contains the shell commands like ls, ps, etc., as well as sqlite3, dalvikvm, and dexdump utilities.
Code Listing 1. Contents of system/bin
# pwd
pwd
/system/bin
# ls -l
ls -l
-rwxr-xr-x root root 196 2008-02-29 01:09 am
-rwxr-xr-x root root 2342 2008-02-29 01:09 dumpstate
-rwxr-xr-x root root 208 2008-02-29 01:09 input
-rwxr-xr-x root root 212 2008-02-29 01:09 monkey
-rwxr-xr-x root root 196 2008-02-29 01:09 pm
…
lrwxr-xr-x root root 2008-02-29 01:16 mount -> toolbox
lrwxr-xr-x root root 2008-02-29 01:16 notify -> toolbox
-rwxr-xr-x root root 28032 2008-02-29 01:16 dexdump
-rwxr-xr-x root root 7100 2008-02-29 01:16 dumpsys
-rwxr-xr-x root root 7904 2008-02-29 01:16 mem_profiler
-rwxr-xr-x root root 6480 2008-02-29 01:16 service
-rwxr-xr-x root root 4320 2008-02-29 01:16 rild
-rwxr-xr-x root root 4928 2008-02-29 01:16 sdutil
-rwxr-xr-x root root 26488 2008-02-29 01:16 sqlite3
-rwxr-xr-x root root 4148 2008-02-29 01:16 dexopt
-rwxr-xr-x root root 4908 2008-02-29 01:16 dalvikvm
-rwsr-sr-x root root 3200 2008-02-29 01:16 surfaceflinger
-rwxr-xr-x root root 5420 2008-02-29 01:16 app_process
-rwxr-xr-x root root 39408 2008-02-29 01:16 runtime
-rwxr-xr-x root root 2920 2008-02-29 01:16 system_server
-rwxr-xr-x root root 9168 2008-02-29 01:17 pv
Where are the SQLite databases on an Android Device?
By default, the SQLite databases have an extension .db. For example, a NotePad application may use note_pad.db database. This might be created from code using Android SDK’s SQLite classes/methods. By default, the SQLite database for an application is in
the /data/data/<app>/databases directory. As you can see from the listing below, the notepad database is in the /data/data/com.google.android.notepad/databases directory.
Code Listing 2. The location of the notepad database
# pwd
pwd
/data/data/com.google.android.notepad/databases
# ls -l
ls -l
-rw-rw---- app_8 app_8 3072 2008-03-18 18:23 note_pad.db
You can see a whole bunch of these sub-directories under the /data/data directory.
Code Listing 3. The /data/data directory
# pwd
pwd
/data/data
# ls
ls
com.google.android.notepad
com.google.android.samples
com.google.android.gtalkservice
com.google.android.phone
com.google.android.maps
com.google.android.providers.im
com.google.android.home
com.google.android.googleapps
com.google.android.gtalksettings
com.google.android.fallback
com.google.android.development
com.google.android.contacts
com.google.android.browser
com.google.android.providers.telephony
com.google.android.providers.settings
com.google.android.providers.media
com.google.android.masfproxyservice
com.google.android.providers.googleapps
com.google.android.providers.contacts
android
Starting the SQLite command line program
Starting generically
Typing sqlite3 in the Android Shell will open the SQLite command line program (since sqlite3 is in the /system/bin directory). Typing .help gives the summarized set of commands available from here.
Code Listing 4. The SQLite commands available from the command line utility
# sqlite3
sqlite3
SQLite version 3.5.0
Enter ".help" for instructions
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 <table> 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
sqlite>
Opening a database
You would, of course, want to see the data and structure of a particular database. In the listing below, the note_pad.db is opened for further querying. Here, I went to the directory where the database is; however, you can also specify the full path.
Code Listing 5. Opening a specific database from SQLite command line
# pwd
pwd
/data/data/com.google.android.notepad/databases
# ls
ls
note_pad.db
# sqlite3 note_pad.db
sqlite3 note_pad.db
SQLite version 3.5.0
Enter ".help" for instructions
sqlite>
Example Commands from the SQLite command line program
Getting the list of tables
You can get the list of all the tables in the current database in two ways: from the .tables command or by querying the sqlite_master table.
Code Listing 6. List of tables
sqlite> .tables
.tables
android_metadata notes
sqlite> select * from sqlite_master;
select * from sqlite_master;
table|android_metadata|android_metadata|2|CREATE TABLE android_metadata (locale
TEXT)
table|notes|notes|3|CREATE TABLE notes (_id INTEGER PRIMARY KEY,title TEXT,note
TEXT,created INTEGER,modified INTEGER)
Getting the schema of the tables
By using the .schema command, you can get the schema of either all of the tables or a single table.
Code Listing 7. Schema of tables
sqlite> .schema
.schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE notes (_id INTEGER PRIMARY KEY,title TEXT,note TEXT,created INTEGER
,modified INTEGER);
sqlite> .schema notes
.schema notes
CREATE TABLE notes (_id INTEGER PRIMARY KEY,title TEXT,note TEXT,created INTEGER
,modified INTEGER);
SQL Queries
You can issue queries like ‘select * from notes’ to get the data from the notes table. As you can see from the figure below, I have entered a series of notes in the notepad application.
Figure 1. Notes in the Note pad application
After entering the notes, if you query the notes table, you will get the data in the _id, title, note, created, and modified columns.
Code Listing 8. Data from notes
sqlite> .schema notes
.schema notes
CREATE TABLE notes (_id INTEGER PRIMARY KEY,title TEXT,note TEXT,created INTEGER
,modified INTEGER);
sqlite> select * from notes;
select * from notes;
1|Title of Note
This is a test note.|This is a test note.|1205871448827|1205873944838
3|Another note
Some text.|A third note
Some text.|1205873990394|1205874145996
4|One more
Body here|One more
Body here|1205874074992|1205874088072
And, finally, we can take a look at how this table was created.
Code Listing 9. Creation of the notes table
public class NotePadProvider extends ContentProvider {
…
private static class DatabaseHelper extends SQLiteOpenHelper {
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE notes (_id INTEGER PRIMARY KEY,"
+ "title TEXT," + "note TEXT," + "created INTEGER,"
+ "modified INTEGER" + ");");
}
…
}
…
@Override
public boolean onCreate() {
DatabaseHelper dbHelper = new DatabaseHelper();
mDB = dbHelper.openDatabase(getContext(), DATABASE_NAME, null, DATABASE_VERSION);
return (mDB == null) ? false : true;
}
…
}
As you can see above, CREATE TABLE sql has been used to create the notes table. Classes like SQLiteOpenHelper and ContentProvider from Android SDK are put to use here.
PS:原文地址http://www.infinitezest.com/articles/using-sqlite-from-shell-in-android.aspx
相关推荐
Ensure that prepared statements automatically reset on extended error codes of SQLITE_BUSY and SQLITE_LOCKED even when compiled using SQLITE_OMIT_AUTORESET. Correct miscounts in the sqlite3_analyzer....
的 Android SDK 提供了在 Android 平台上使用 JaVa 语言进行 Android 应用开发必须的工具和 API 接口。 特性 • 应用程序框架 支持组件的重用与替换 • Dalvik Dalvik Dalvik Dalvik 虚拟机 专为移动设备优化 • ...
Creating a Shell Activity Using the Windows CLI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Running the ActivityCreator.bat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ....
Using the Code from This Book Audience Organization Further Reading Conventions Used in This Book How to Contact Us Safari® Enabled Acknowledgments Chapter 1. Text Introduction ...
, * Read text files and extract information, * Run tasks concurrently using the threading and forking options, * Get information from one process to another using network facilities, * Create ...
Mac: the user's default shell is executed and environmental variables are set in the plugin Python environment Linux: Update X11 selection on clipboard every time selection changes Linux: Improved MOD...
2.2.5 Exit from the Python Shell 27 2.3 BATCH MODE 27 2.3.1 Comments 29 2.3.2 Indentation 30 2.4 CHOOSING AN EDITOR 32 2.4.1 Sublime Text 32 2.4.2 Atom 33 2.4.3 PyCharm 34 2.4.4 Spyder IDE 35 2.4.5 ...
PEP 366: Explicit Relative Imports From a Main Module PEP 370: Per-user site-packages Directory PEP 371: The multiprocessing Package PEP 3101: Advanced String Formatting PEP 3105: print As a ...
Secure Shell2 Functions CLIX. Statistics Functions CLX. Stream Functions CLXI. String 字符串处理函数 CLXII. Subversion 函数 CLXIII. Shockwave Flash Functions CLXIV. Swish Functions CLXV. Sybase ...
Secure Shell2 Functions CLIX. Statistics Functions CLX. Stream Functions CLXI. String 字符串处理函数 CLXII. Subversion 函数 CLXIII. Shockwave Flash Functions CLXIV. Swish Functions CLXV. ...
Secure Shell2 Functions CXLIV. Stream Functions CXLV. String 字符串处理函数 CXLVI. Shockwave Flash Functions CXLVII. Sybase Functions CXLVIII. TCP Wrappers Functions CXLIX. Tidy Functions CL. ...
Secure Shell2 Functions CXLV. Stream Functions CXLVI. String 字符串处理函数 CXLVII. Shockwave Flash Functions CXLVIII. Sybase Functions CXLIX. TCP Wrappers Functions CL. Tidy Functions CLI. Tokenizer...
recommending using the production ini in production and testing environments. ; php.ini-development is very similar to its production variant, except it's ; much more verbose when it comes to errors...