数据库迁徙
当数据库表结构有变化时，为了保存当前数据库表存储的数据，就要进行数据库迁徙。
数据库表结构变化分类如下。
- 数据库表新增列。
- 数据库表删除列。
- 数据库表重命名列。
- 数据库表修改列属性。
- 数据库表修改索引。
SQLite 数据库迁徙
SQLite 数据库有关数据库表结构变化的文档参见官方文档SQLite 数据库表结构修改文档
官方文档中的第5点，专门讲到数据库升级。对第5点进行简单翻译，以确定SQLite官方推荐的升级流程。
数据库升级方案1
SQLite直接支持的唯一更改模式的命令有：rename table (重命名数据库表)，rename column (重命名数据库表的列名)，add column(在表中新增列)。其他的数据库表结构的修改，可以通过以使用简单的操作序列对表的格式进行修改。对某些表X的模式设计进行任意更改的步骤如下
- 如果启用了外键约束，使用命令
PRAGMA foreign_keys=OFF.将外键约束关闭。 - 启动一个数据库操作事件。
- 保存与表X有关的所有索引（index），触发器（trigger）和视图（View）。查询索引，触发器，视图的SQL指令如下。相关索引，触发器，视图可以参见下一个小章节数据库表示例。
- 使用
CREATE TABLE创建一个新的表“new_x”，需要注意的是创建表前要保证“new_x”在数据库中不存在。 - 将数据库表x的内容拷贝到new_x表中，命令
select into new_x (新表列名,...) select (旧表列名,...)from x; - 删除旧表，命令
drop table x - 将新表重命名为旧表，命令
alter table new_x rename to x - 使用创建索引（create index）,创建触发器（create_trigger），创建视图（create view)指令创建与表X有关的索引，触发器，视图。
- 如果任何视图以受模式更改影响的方式引用表X，则使用drop VIEW删除这些视图，并使用CREATE VIEW进行必要的更改重新创建它们。
- 如果启用了外键约束，使用命令
PRAGMA foreign_key_check，确保外键约束没有被破坏。 - 将在第2不创建的事务提交。
- 如果启用外键约束，使用命令
PRAGMA foreign_keys=ON.将外键约束启动，与第一步对应。
注意
数据库升级必须按照以上步骤来执行。下面用了两个表格描述升级过程，第一列是正确的，第二列是错误的。首先看起来两个流程都是完成了数据库的升级的操作，但是第二列的操作是无效的，特别是第二列的第一不重命名数据库表，在原数据库存在触发器，视图，外键约束的时候，直接重命名数据库表，会对触发器，视图，外键约束造成破坏，因此正确的操作是创建一个新表，然后将新表重命名为旧表，操作参见第一列。
| 正确操作 | 错误操作 |
|---|---|
| 1. 创建新表 | 1.重命名旧表 为older_x |
| 2.旧表数据拷贝到新表中 | 2.创建新表 x |
| 3.删除旧表数据 | 3.拷贝数据将older_x 拷贝到 X |
| 4.新表重命名为旧表名称 | 4.删除旧表 older_x |
数据库升级流程中的12步操作，可以完美的执行数据库升级，包括删除列，修改列属性，等等操作。
数据库升级方案2
官网提供的第二个数据库升级方案是基于数据库表的结构都是存储在sqlite_master这个表中，通过修改sqlite_master中的数据来达到修改数据库表结构的作用。步骤描述如下：
- 开启一个事务
- 使用命令
PRAGMA schema_version获取当前的schema version。 - 使用命令
PRAGMA writable_schema=ON。使得数据库表结构可以修改。 - 修改主表（sqlite_master）中相关的数据库表的定义表结构的sql命令。命令类似
update sqlite_master set sql='创建数据库表命令' where type='table' AND name='X';。这步的操作属于比较危险的操作，如果操作不当，容易造成数据库不可读，因此要特别注意。 - 如果表X的重新创建会影响其原本的索引，触发器，视图，则需要使用update 命令进行相应的修改。比如说修改了列的名称，则与这个列相关的索引，触发器，视图，外键约束必须进行相应的修改。
- 将第二步获得到的schema version 加1，并使用命令
PRAGMA schema_version=X，保存到数据库中。 - 使用命令
PRAGMA writable_schema=OFF。恢复数据库表结构不可修改。 - 使用 命令
PRAGMA integrity_check确保数据库没有被破坏。 - 提交事物。
后续SQLite支持表结构修改，修改的方案会是以上描述的其中一个。
数据库表示例
数据库表结构
数据库表名：admin。数据库表结构
| 主键 | 账号 | 密码 |
|---|---|---|
| id | username | password |
- 创建数据表命令
CREATE TABLE "admin" ("id" INTEGER NOT NULL,"username" VARCHAR(20),"password" VARCHAR(128),PRIMARY KEY("id"));
- 创建索引
CREATE INDEX "ix_admin_username" ON "admin" (
"user_name"
);
- 创建触发器。触发器的作用是当表Admin有数据修改的时候记录一下，修改时间，作为追溯数据库表修改的日志记录。 表admin_record结构如下
| 列名 | 含义 |
|---|---|
| id | admin表的主键只 |
| datatime | admin表修改的时间 |
// 创建admin_record
CREATE TABLE "admin_record" ("id" INTEGER NOT NULL,"datatime" VARCHAR(20),PRIMARY KEY("id"));
// 创建索引
CREATE TRIGGER admin_record_trigger AFTER INSERT
ON admin
BEGIN
INSERT INTO admin_record(id, datatime) VALUES (new.id, datetime('now'));
END;
// 测试
// 在admin 表中插入一条数据，则会触发 触发器在 admin_record中也插入一条数据
INSERT INTO admin(id, username,password) VALUES (100,'name100','password100');
- 创建视图，在表admin中创建一个视图，视图中只显示表admin的部分列，比如说主键值和账号。命令如下
// 创建视图
CREATE VIEW admin_view AS
SELECT id, username
FROM admin
查询表结构
- 查询表admin的相关索引的命令
SELECT type,sql FROM sqlite_master WHERE tbl_name='admin' AND type='index'
查询结果
| type | index |
|---|---|
| index | CREATE INDEX “ix_admin_username” ON “admin” (“user_name”) |
- 查询admin的触发器
SELECT type,sql FROM sqlite_master WHERE tbl_name='admin' AND type='trigger'
查询结果
| type | sql |
|---|---|
| trigger | CREATE TRIGGER admin_record_trigger AFTER INSERT ON admin BEGIN INSERT INTO admin_record(id, datatime) VALUES (new.id,datetime('now'));END |
- 查询视图
注意查询视图，视图在主表sqlite_master 相当于一个表。不能直接通过指令查询到由某个表创建出来的视图。
SELECT type,sql FROM sqlite_master WHERE type='view'
GreenDao 统一数据库升级方案
greenDAO是一款开源的面向 Android 的轻便、快捷的 ORM 框架，将 Java 对象映射到SQLite数据库中，我们操作数据库的时候，不再需要编写复杂的 SQL语句， 在性能方面，greenDAO针对 Android 进行了高度优化， 最小的内存开销 、依赖体积小 同时还是支持数据库加密。
greenDAO 支持一对一，一对多的多表操作，但是greenDao本身是不支持外键操作的，很多博客说toOne，toMany有指定外键，但是这些不属于SQLite中定义的外键。可以证明greenDAO 不支持外键有两个证据
- greenDAO创建的数据库，使用greenDAO的接口执行命令
PRAGMA foreign_keys。返回的结果为0.代码如下
Cursor cursor = PeopleHelper.getInstance().executeSql("PRAGMA foreign_keys");
if (cursor != null && cursor.moveToFirst()){
do{
String[] columns = cursor.getColumnNames();
for (String name : columns){
Log.d(TAG,"name = "+name);
}
Integer aa = cursor.getInt(0);
Log.d(TAG,"aa = "+aa);
}while (cursor.moveToNext());
}
- 如果greenDAO 支持外键操作，那么需要支持外键约束条件，比如说子表删除一条记录，可以新增一个外键约束条件，将父表的数据也删除，但是没有提供相关的接口，这个方面也可以证明greenDAO不支持外键操作。
- 目前android 官方提供的数据库Room支持外键操作。定义如下
// 定义父表
@Entity(indices = {
@Index(value = "name",unique = true)
})
public class Father {
@PrimaryKey(autoGenerate = true)
private int number;
private String name;
}
// 定义子表
@Entity(foreignKeys = {
@ForeignKey(entity = Father.class,parentColumns = "name",childColumns = "father",
onUpdate = CASCADE,onDelete = CASCADE)
})
public class Child {
@PrimaryKey(autoGenerate = true)
private int number;
private String name;
private String father; }
从另一个方面，运行在终端的程序，本身对数据库的操作要求比较低，因此数据库的高级特性外键，触发器，视图，子表查询等等无特殊要求，因此很少第三方数据库会对此类操作进行封装。同时也意味着在android 终端的应用，数据库结构的修改，基本上都是列的新增和删除，由于sqlite数据库列的数据类型是可变的，所以本身就支持在同一列中存放不同的数据类型。但是不推荐修改列的数据类型，因为在java层可能存在数据转换失败的问题。
由于官方文档说明，数据库升级方案2速度更快，且更加简单，可能是因为少了数据拷贝的过程，所以速度快吧。对数据库升级方案2进行一个简单的封装。
代码参见github
扫一扫
269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
