一文理解Jetpack——SQLite

在 Android 中，Google 使用了 SQLite 数据库来解决数据存储的问题。在开发中，我们一般不会直接使用 SQLite，而是使用 ORM（Object Relational Mapping）库。比如 jetpack 的 Room 组件，就是官方推荐使用的 ORM 库。

虽然使用 ORM 库非常方便，但是 ORM 库本质还是对 SQLite 进行操作。 这篇文章就介绍一下 SQLite 的一些需要了解的知识，后面学习 Room 的使用就会更简单。

SQLite 不支持的 SQL特性

我们都知道，SQL 是用于访问和处理数据库的标准结构化查询语言。但是不同的数据库对 SQL 的支持是不一样的。SQLite 实现了 SQL 的大部分通用功能，但还有小部分不支持的 SQL 功能如下所示：

不支持的 SQL 功能	具体介绍
完整的 ALTER TABLE 支持	仅支持 ALTER TABLE 命令的 RENAME TABLE、ADD COLUMN、RENAME COLUMN 和 DROP COLUMN 变体。省略了其他种类的 ALTER TABLE 操作，例如 ALTER COLUMN、ADD CONSTRAINT 等。
完整的触发器支持	支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器。
写入视图	SQLite 中的视图是只读的。您不能对视图执行 DELETE、INSERT 或 UPDATE 语句。但是您可以创建一个触发器，在尝试删除、插入或更新视图时触发，并在触发器主体中执行您需要的操作。
授予和撤销	由于 SQLite 读取和写入普通磁盘文件，因此唯一可以应用的访问权限是底层操作系统的正常文件访问权限。客户端/服务器 RDBMS 上常见的 GRANT 和 REVOKE 命令没有实现，因为它们对于嵌入式数据库引擎毫无意义。

Android中的SQL操作

在 Android 开发中，操作数据库需要使用 Google 提供的 SQLiteOpenHelper 接口。目前 Google 是推荐使用 Room，而不是直接使用 SQLiteOpenHelper 接口，因此下面的内容了解即可。

数据库创建

/**
 * @param context
 * @param name    创建数据库的名字
 * @param factory 用于返回自定义的Cursor，一般填null
 * @param version 表示当前数据库的版本号，可用于对数据库进行升级
 */
class FeedReaderDbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
        // 执行数据库的创建
        db.execSQL(SQL_CREATE_ENTRIES)
    }
}

增删改查

SQLite 的增删改查查找分别对应于 insert 、delete、 update、 select 四个关键字。数据库的增删改查每一篇文章几乎都会提到过，这里给一下代码示例，就不多展开了。

val dbHelper = FeedReaderDbHelper(context)
val db = dbHelper.writableDatabase 

// 增加数据
val values = ContentValues().apply {
    put(FeedEntry.COLUMN_NAME_TITLE, title)
    put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle)
}
//参数说明
// 1.表的名字 
// 2.用于未指定添加数据的情况下给某些可为空的列自动赋值NULL 
// 3. ContentValues 对象
db?.insert(TABLE_NAME, null, values)

// 删除数据
// 后面的两个参数是操作的限制条件,用来约束删除哪几行，如果不指定就删除所有行
db?.delete(TABLE_NAME, selection, selectionArgs)

// 更新数据
// 后面的两个参数是操作的限制条件
db?.update(TABLE_NAME, values, selection, electionArgs)

// 查询数据
val cursor = db?.query(
        TABLE_NAME,   // 查询的表名
        colums,             // 查询的列名
        selection,              // where 的约束条件
        selectionArgs,          // where 占位符的具体值
        null,                   // 需要 groupBy 的列
        null,                   // 对 groupBy 的约束
        sortOrder               // 查询结果的排列顺序
)

事务

在 Android 开发中，事务（Transaction）相关的概念最多是在添加 Fragment 时用到。 在 SQL 中，事务是指一个对数据库执行工作单元，它需要满足以下的条件：

• 原子性（Atomicity）： 确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
• 一致性（Consistency)： 确保数据库在成功提交的事务上正确地改变状态。
• 隔离性（Isolation）： 使事务操作相互独立和透明。
• 持久性（Durability）： 确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

简单的说就是执行操作a 和 操作b时，这两个操作要么都执行成功，要么都执行失败。以用户点赞掘金的一篇文章的场景为例，点赞的操作首先会更新 当前用户动态的数据表；然后再在文章的表中，更新这篇文章的点赞数。这时候就需要确保这两种操作都执行成功或者失败。代码示例如下：

// 开启事务
db?.beginTransaction()
// 执行各种操作
db?.insert(TABLE_NAME, null, values)
db?.delete(TABLE_NAME, selection, selectionArgs)
// 提交事务
db?.endTransaction()

高级概念

在一般的 Android 开发中，了解上面的基本的操作就足够了。下面是一些关于 SQL 的高级概念，大部分只在面试中有用，了解即可。

主键和外键

主键是能确定一条记录的唯一标识，类似于一个人的身份证号，它不允许重复或者为空。

而外键则是用于与另一张表的关联的字段。比如说，A表中的一个字段id，是B表的主键，A 和 B 表通过 id 关联起来，那他就可以是A表的外键。需要注意，外键是可以有重复的，也可以为空的。

那么外键必须是另一个表的主键吗？答案是不一定，外键只是表间关系的参照，可以不是主键，但必须是唯一性索引。只是表明两个表之间的关系是通过那个属性连接起来的。

总结一下，主键、外键的区别如下：

主键	外键
用来保证数据完整性	用来和其他表建立联系用的
主键只能有一个	一个表可以有多个外键

索引

索引（Index）是一种特殊的查找表。通过索引，数据库可以加快数据的查找。简单地说，一个数据库中的索引类似于一本书的目录，是一个指向表中数据的指针。

为什么索引可以加快数据的查找呢？这是因为传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，数据库都会把表的数据从头到尾遍历一遍。而在我们添加完索引之后，数据库会通过相应的算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，而不是遍历所有的表数据。原理图如下所示，图片来源看这里

数据库的索引声明示例如下。在数据库中，索引只要声明一下就可以了，数据库内部会对索引进行处理。

CREATE INDEX index_name
ON table_name (column_name);

需要注意，索引虽然有助于加快 SELECT 查询过程，但它同时会减慢使用 UPDATE 和 INSERT 的数据更新和插入过程。因此在下面几个情况下，我们需要避免使用索引：

• 较小的表上，不建议使用索引
• 声明索引的列，不应该频繁更新
• 需要频繁的更新或插入操作的表上，不建议使用索引
• 索引不应该使用在含有大量的 NULL 值的列上。

视图

视图是一个或多个基本表或视图导出的表，是一个虚表。数据库中只存放视图的定义，不存放视图对应的数据，对视图的操作最终都转化为基本表的操作。代码示例如下：

// 创建一个视图
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

// 查询视图
SELECT * FROM view_name

为什么我们需要视图呢？其实，它的作用主要就两个，一个是限制数据，一个是查找数据更直观。当我们需要查询的数据关联多个表时，可以使用创建视图把这些表关联起来，让查找数据更加直观。也可以限制表的字段，从而达到限制数据的功能。

需要注意，SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句

触发器

触发器（Trigger） 是数据库的回调函数，它会在指定的数据库事件发生时自动执行。代码示例如下：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

在 Room 中，Livedata 能监听到表数据的变化，就是通过触发器来实现的。

FTS

FTS 是 SQLite 虚拟表模块，允许用户对数据库执行全文搜索。目前最新的版本是 FTS5，但是在 ROOM 中只有 FTS3 和 FTS4 的注解，貌似不支持 FTS5。关于 FTS 的使用，可以看SQLite FTS3 和 FTS4 扩展 | SQLite中文网 (readdevdocs.com)

参考

• SQLite Documentation
• SQLite 视图 | 菜鸟教程 (runoob.com)
• SQLite 文档 | SQLite中文网 (readdevdocs.com)
• mysql的主键与外键的理解 - 凡人半睁眼 - 博客园 (cnblogs.com)
• MySQL数据库，从入门到精通：第十四篇——MySQL视图详解-阿里云开发者社区 (aliyun.com)
• Android Jetpack组件之数据库Room详解(三)
• SQLite FTS3 和 FTS4 扩展 | SQLite中文网 (readdevdocs.com)