Sqlite3 in android

September 24, 2017

Sqlite3 in Android

About Sqlite

Sqlite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的轻量化 SQL 数据库引擎。Sqlite 是在世界上最广泛部署的 SQL 数据库引擎，更多关于 Sqlite 的细节可以参阅官方文档。
对应到 Android 来看，使用的 Sqlite 的版本跟 Android 版本之间是存在依赖关系的，如下表：

Alt text

Android 也提供了很方便的编程接口去使用 Sqlite 数据库，详情可参阅官方文档。
与此同时，Android SDK 和 Android 模拟器都包含了一个用于方便操作 Sqlite 数据库的命令行工具—— sqlite3 ，该工具在某种程度上可以提升我们开发和调试的效率，更甚者可以做一些有意思的事情（文末简单介绍）。所以本文就详细介绍一下 sqlite3 的使用。

Usage

前面也说到在 Android SDK 及 Android 模拟器中都包含该 sqlite3 工具，所以这里分别对应了两种使用场景：

1.Android SDK

在这种场景下需要先将手机内部需要操作的 db 文件 pull 到本机上，然后可以使用 Android SDK 的 platform-tools/ 目录下的 sqlite3 工具来打开或者操作从手机 pull 到本机的数据库。

2.in shell

在 Android 模拟器中是可以直接使用 sqlite3 工具的，你只需要 adb shell ，之后再模拟器的 shell 中直接就可以使用 sqlite3 命令来操作指定的数据库文件。一般情况下量产的手机真机 shell 中是没有自带 sqlite3 工具的，如果需要，其实可以安装对应真机版本和ABI的模拟器，将 sqlite3 从模拟器中导出后再导入真机的 /system/xbin/ 目录下使用。

Details

不管上面哪种使用场景，使用 sqlite3 的方法是一样的，下面就详细介绍下如何使用 sqlite3 工具来完成一系列的 db 操作。

运行sqlite3

直接运行 sqlite3 即可启动程序：

root@generic_x86:/ # sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

这时候 sqlite3 会打印出当前的版本，并提示你使用 .open 命令打开指定的数据库：

sqlite> .open /data/data/com.android.launcher3/databases/launcher.db

如此一来，就成功启动了sqlite3 并打开了指定的数据库。

当然也可以直接将数据库文件的路径作为运行 sqlite3 命令的参数，从而直接启动 sqlite3 并打开指定的数据库:

root@generic_x86:/ # sqlite3 /data/data/com.android.launcher3/databases/launcher.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite>

启动 sqlite3 并打开指定的数据库之后，可以通过 sqlite3 内置的 .tables 命令列出当前数据库中的所有表：

sqlite> .tables
android_metadata  favorites         workspaceScreens

这里看到我打开的是 launcher app 的数据库，该数据库中目前建了三张表，其中 android_metadata 表是系统自动生成的，用于记录用户的使用语言。

之后便可以输入 SQL 语句操作数据库中的表了。

Note : SQL 语句需要以分号结束。

点命令集

通常情况下，比如针对 SQL 语句，sqlite3 的工作只是读取我们输入的语句并且将它们传递给 SQLite 库去执行。但是当输入的一行以点 (.) 开头时（比如上面有提到的 .open 和 .tables ），这一行将被交由 sqlite3 程序自身来解释，这些点命令集通常被用于设置或完成 sqlite3 程序自身的一些功能。可以在打开 sqlite3 程序后通过 .help 命令来获取所有可用的点命令集的列表：

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.dbinfo ?DB?           Show status information about the database
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off            Enable or disable automatic EXPLAIN QUERY PLAN
.exit                  Exit this program
.explain ?on|off?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.fullschema            Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
                         matching LIKE pattern TABLE.
.limit ?LIMIT? ?VAL?   Display or change the value of an SQLITE_LIMIT
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         ascii    Columns/rows delimited by 0x1F and 0x1E
                         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 strings
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?FILENAME?       Close existing database and reopen FILENAME
.output ?FILENAME?     Send output to FILENAME or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator COL ?ROW?   Change the column separator and optionally the row
                         separator for both the output mode and .import
.shell CMD ARGS...     Run CMD ARGS... in a system shell
.show                  Show the current values for various settings
.stats on|off          Turn stats on or off
.system CMD ARGS...    Run CMD ARGS... in a system shell
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify

Note : 点命令集的输入格式遵循以下约束：

点命令集的命令必须以” . “开头，而且” . “前面不允许存在空格。

一行内只允许存在一个完整的点命令集的命令。

点命令集无法识别注释。

点命令集不允许出现在一个 SQL 语句中。

下面我们挑几个点命令集详细说明一下，其他的大家也可以参考 .help 的输出结合实践看一下。

.open
上面也提到过，用于重新打开一个数据库文件。

.tables
上面也提到过，用于打印出当前打开的数据库中存在的表。

.headers on/off
用来控制是否在输出查询结果时展示表头的列名信息。

.mode & .separator
sqlite3 程序内部可以以多种不同的格式展示查询结果：

csv
column
html
insert
line
list
quote
tabs
tcl

可以使用 .mode 这个点命令指定其中任一格式输出。不指定的话默认以 “list” 格式输出。

1.list mode
该格式输出内容如下：

sqlite> .headers on
sqlite> select * from favorites;
// 下面这行输出信息为.headers on命令导致的结果，这里看到将每列对应的列名展示出来。如果不显示的执行.headers on，默认是出于off状态的。
_id|title|intent|container|screen|cellX|cellY|spanX|spanY|itemType|appWidgetId|isShortcut|iconType|iconPackage|iconResource|icon|uri|displayMode|appWidgetProvider|modified|restored|profileId|rank|options
1|||-100|2|1|0|2|2|4|4||||||||com.android.deskclock/com.android.alarmclock.AnalogAppWidgetProvider|0|0|0|0|0
2|Camera|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.camera/.Camera;end|-100|2|0|3|1|1|0|-1|||||||||0|0|0|0|0
3|Settings|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.settings/.Settings;end|-100|3|2|3|1|1|0|-1|||||||||0|0|0|0|0
4|Phone|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;package=com.android.dialer;component=com.android.dialer/.DialtactsActivity;end|-101|0|0|0|1|1|0|-1|||||||||0|0|0|0|0

每一行的内容输出在同一行，每一列用 ” | ” 符号隔开。通过 .separator 点命令可以指定每列的分割符（默认为 ” | ” ）：

sqlite> .headers off
sqlite>.separator ^
sqlite> select * from favorites;
// 上面又显示执行了.headers off命令关闭了表头信息的输出，所以此处没有展示表头信息。
1^^^-100^2^1^0^2^2^4^4^^^^^^^^com.android.deskclock/com.android.alarmclock.AnalogAppWidgetProvider^0^0^0^0^0
2^Camera^#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.camera/.Camera;end^-100^2^0^3^1^1^0^-1^^^^^^^^^0^0^0^0^0
3^Settings^#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.settings/.Settings;end^-100^3^2^3^1^1^0^-1^^^^^^^^^0^0^0^0^0
4^Phone^#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;package=com.android.dialer;component=com.android.dialer/.DialtactsActivity;end^-101^0^0^0^1^1^0^-1^^^^^^^^^0^0^0^0^0
sqlite>

上面的例子将每列的分割符指定为 ” ^ ” ，sqlite3 输出的查询结果将会以 ” ^ ” 来分割每一列内容。

2.line mode
该格式的输出内容如下：

sqlite> .mode line
sqlite> select * from favorites;
              _id = 1
            title =
           intent =
        container = -100
           screen = 2
            cellX = 1
            cellY = 0
            spanX = 2
            spanY = 2
         itemType = 4
      appWidgetId = 4
       isShortcut =
         iconType =
      iconPackage =
     iconResource =
             icon =
              uri =
      displayMode =
appWidgetProvider = com.android.deskclock/com.android.alarmclock.AnalogAppWidgetProvider
         modified = 0
         restored = 0
        profileId = 0
             rank = 0
          options = 0

              _id = 2
            title = Camera
           intent = #Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.camera/.Camera;end
        container = -100
           screen = 2
            cellX = 0
            cellY = 3
            spanX = 1
            spanY = 1
         itemType = 0
      appWidgetId = -1
       isShortcut =
         iconType =
      iconPackage =
     iconResource =
             icon =
              uri =
      displayMode =
appWidgetProvider =
         modified = 0
         restored = 0
        profileId = 0
             rank = 0
          options = 0

              _id = 3
            title = Settings
           intent = #Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.settings/.Settings;end
        container = -100
           screen = 3
            cellX = 2
            cellY = 3
            spanX = 1
            spanY = 1
         itemType = 0
      appWidgetId = -1
       isShortcut =
         iconType =
      iconPackage =
     iconResource =
             icon =
              uri =
      displayMode =
appWidgetProvider =
         modified = 0
         restored = 0
        profileId = 0
             rank = 0
          options = 0

              _id = 4
            title = Phone
           intent = #Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;package=com.android.dialer;component=com.android.dialer/.DialtactsActivity;end
        container = -101
           screen = 0
            cellX = 0
            cellY = 0
            spanX = 1
            spanY = 1
         itemType = 0
      appWidgetId = -1
       isShortcut =
         iconType =
      iconPackage =
     iconResource =
             icon =
              uri =
      displayMode =
appWidgetProvider =
         modified = 0
         restored = 0
        profileId = 0
             rank = 0
          options = 0

可以看到 line 模式将表中的每行信息以 ” 列名 = value ” 的形式输出，数据库中每一行的所有信息输出完毕后用一行空行隔开。其他几种输出模式大家也可以自己去尝试一下，这里就不一一列举了。在平时开发或者调试过程中当需要对数据做不同的处理时可能使用不同的输出模式会给你带来很大的方便。

.output
默认情况下，sqlite3 是将查询的结果输出到标准输出流。我们可以在使用 .output 命令时通过一个表示输出文件路径的参数将输出重定向到一个指定的文件。如果 .output 没有传参数，那么输出结果将恢复到标准输出流。如下所示：

sqlite> .output /sdcard/db_query.txt
sqlite> select * from favorites;
sqlite>

上面的例子中将输出重定向到了 /sdcard/db_query.txt 文件中，所以可以看到当执行查询动作时 sqlite3 内部并没有像往常一样将查询结果打印出来，而此时看一下 /sdcard/db_query.txt 文件的内容：

root@generic_x86:/ # cat /sdcard/db_query.txt
_id|title|intent|container|screen|cellX|cellY|spanX|spanY|itemType|appWidgetId|isShortcut|iconType|iconPackage|iconResource|icon|uri|displayMode|appWidgetProvider|modified|restored|profileId|rank|options
1|||-100|2|1|0|2|2|4|4||||||||com.android.deskclock/com.android.alarmclock.AnalogAppWidgetProvider|0|0|0|0|0
2|Camera|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.camera/.Camera;end|-100|2|0|3|1|1|0|-1|||||||||0|0|0|0|0
3|Settings|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.settings/.Settings;end|-100|3|2|3|1|1|0|-1|||||||||0|0|0|0|0
4|Phone|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;package=com.android.dialer;component=com.android.dialer/.DialtactsActivity;end|-101|0|0|0|1|1|0|-1|||||||||0|0|0|0|0

可以看到查询结果被重定向到了指定的文件中。
如果想要将输出恢复到标准输出流，使用不带参数的 .output 命令即可:

sqlite> .output
sqlite> select * from favorites;
1|||-100|2|1|0|2|2|4|4||||||||com.android.deskclock/com.android.alarmclock.AnalogAppWidgetProvider|0|0|0|0|0
2|Camera|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.camera/.Camera;end|-100|2|0|3|1|1|0|-1|||||||||0|0|0|0|0
3|Settings|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.settings/.Settings;end|-100|3|2|3|1|1|0|-1|||||||||0|0|0|0|0
4|Phone|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;package=com.android.dialer;component=com.android.dialer/.DialtactsActivity;end|-101|0|0|0|1|1|0|-1|||||||||0|0|0|0|0

Note : 这里我导出的是 txt 文件。但是结合前面的 .mode 命令，可以通过 .mode csv 设置为 csv 模式后通过 .output /path/xxx.csv 直接导出一个 csv 文件。

.import
该命令可以导入一个 csv 文件到数据库的一个表中。该命令有两个参数，一个参数指定要导入的 csv 文件，另一个参数指定需要将数据导入到哪个表中。注意在调用 .import 命令前需要先使用 .mode csv 命令将当前模式切换到 csv 模式，这将避免程序将输入文件解释为其他类型的文件。如下例：

sqlite> .mode csv
sqlite> .import /sdcard/backup.csv favorites

该例子将 /sdcard/backup.csv 文件作为数据源，导入到当前数据库的 favorites 表中。

Note : 导入数据时可能会遇到两种场景：

表 favorites 不存在:
若需要导入数据的表不存在，那么会自动创建该表，并且 csv 文件中的第一行内容将作为该表的表头来填充。第二行内容开始才作为该表的实际数据内容来填充。

表 favorites 已经存在:
若需要导入数据的表已经存在，那么 csv 文件的内容都将作为该表的实际数据内容来填充。

.dump
这个命令可以用来实现数据库的备份及恢复，用例如下：

root@generic_x86:/ # sqlite3 data/data/com.android.launcher3/databases/launcher.db  .dump > /sdcard/dump_backup.txt

首先注意与上面说到的命令不同，此时是在启动 sqlite3 时除了传递了一个要打开的数据库文件路径外，后面直接跟了 .dump 点命令，并将输出的内容通过 ” > ” 重定向到 /sdcard/dump_backup.txt 。该命令调用完成后会生成 /sdcard/dump_backup.txt ，在该文本文件中使用 SQL 语句记录了 data/data/com.android.launcher3/databases/launcher.db 数据库的内容。
现在我们尝试使用备份的文本文件恢复数据库：

root@generic_x86:/ # cat /sdcard/dump_backup.txt | sqlite3 /sdcard/backup.db
root@generic_x86:/ #

首先通过 cat 命令读取 /sdcard/dump_backup.txt 文件的内容，然后通过管道连接 sqlite3 程序，并指定一个新建的数据库路径，该数据库将以 /sdcard/dump_backup.txt 的内容来构建恢复。
运行完上面的命令后:

root@generic_x86:/sdcard # ls -l
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Alarms
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Android
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 DCIM
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Download
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Movies
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Music
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Notifications
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Pictures
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Podcasts
drwxrwx--x root     sdcard_rw          2017-09-10 22:34 Ringtones
-rw-rw---- root     sdcard_rw    15360 2017-09-24 18:05 backup.db
-rw-rw---- root     sdcard_rw    19664 2017-09-24 17:57 dump_backup.txt
root@generic_x86:/sdcard #

可以看到 backup.db 被成功构建。

小结：还有很多其他的点命令就不一一说明了，大家可以参阅官方文档，不过最好还是建议大家动手试一试。其实sqlite3这个小工具用起来也很简单，它的威力可大可小，还看你怎么样运用它。

彩蛋

占用了客官那么多时间，先说声谢谢~最后扔一个相关的小彩蛋出来吧。
为了方便 sqlite3 在脚本中使用，sqlite3 在设计时就支持以下方式直接执行 SQL 语句：

root@generic_x86:/ # sqlite3 /data/data/com.android.launcher3/databases/launcher.db "select * from favorites"
1|||-100|2|1|0|2|2|4|4||||||||com.android.deskclock/com.android.alarmclock.AnalogAppWidgetProvider|0|0|0|0|0
2|Camera|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.camera/.Camera;end|-100|2|0|3|1|1|0|-1|||||||||0|0|0|0|0
3|Settings|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;component=com.android.settings/.Settings;end|-100|3|2|3|1|1|0|-1|||||||||0|0|0|0|0
4|Phone|#Intent;action=android.intent.action.MAIN;category=android.intent.category.LAUNCHER;launchFlags=0x10200000;package=com.android.dialer;component=com.android.dialer/.DialtactsActivity;end|-101|0|0|0|1|1|0|-1|||||||||0|0|0|0|0
root@generic_x86:/ #

当在命令行执行 sqlite3 传递两个参数时，第二个参数将作为一个被 SQL 语句被 sqlite3 直接交给 Sqlite 库执行，至于操作的表来自哪里，则由第一个参数指定的 db 文件确定。
为什么说这是颗彩蛋呢？
首先，这东西可以脚本化执行，那不是爽歪歪，各种自动化XXX立即涌上心头。
其次，试想一下，只要我能拿到 root 权限，我就能访问 data/data/ 下的所有应用数据，且不说如何 root ，就算基于已 root 的用户，通过 sqlite3 将能够实现直接静默修改系统配置的“流氓”需求。比如……静默打开辅助服务（一阵淫笑）….
当然，这么“流氓”的事情，我一般不做的。
也许有同学会问，我已经拿到了 root 权限还这么周折干什么，直接调用系统的 API 修改数据库不就行了？其实不然， root 权限与 Android 的权限是两套不同的权限机制，拿到 root 权限，意味着我对系统中的所有资源都有控制权，但是 Android 的很多敏感 API 在调用时检测的是基于 Android 的权限机制检查。所以这里只能转个弯绕路走。
最后补充一下如果用户手机已经 root ，我的应用直接执行 sqlite3 /data/data/com.android.launcher3/databases/launcher.db “select * from favorites” 这样的命令是会失败的，因为手机 root 并不以为着我的应用也被授予 root 权限了，我的应用没有被授予 root 权限，则在我的应用中不允许访问其他应用的数据，所以得请求以 root 身份来运行该命令：

su -c sqlite3 /data/data/com.android.launcher3/databases/launcher.db "select * from favorites"

这时候用户界面一般会弹出授予 root 权限的窗口，只要用户点击确定，那就妥了…
当然这个方案得考虑兼容性工作，毕竟不同 Android 版本需要对应的 sqlite3 。

参考

https://developer.android.com/reference/android/database/sqlite/package-summary.html
https://developer.android.com/studio/command-line/sqlite3.html
http://www.sqlite.org/cli.html