注意:此方式存在问题,解决方案请看文章最后“缺陷-解决”
- 目的
原有表Reform设计是一个主键reformId,由于业务需求需要设置联合主键(reformId, userId)。 - 思路
由于sqlite不支持建表后删除列和修改主键这些操作(sqlite并不支持建表后修改主键,或删除列,如果要修改,请参考如下做法),如果要进行这些操作,建议是重新建表。 - 实现
- 写好重建表的sql语句,我这里是将原表重命名建立新表,将数据导入后删除原表。也可以建立临时表迁移数据后删除临时表。sql文件放到assets下面,内容如下:
ALTER TABLE Reform RENAME TO tempReform;
create table Reform
(
reformId INTEGER,
userId INTEGER,
reformName TEXT,
reformNumb TEXT,
reformContent TEXT,
reformStatus TEXT,
reformCount TEXT,
reformStartTime TEXT,
reformEndTime TEXT,
realExecTime TEXT,
createTime TEXT,
reformResult TEXT,
reformUserId INTEGER,
reformUserName TEXT,
checkResultId INTEGER,
inspAreaId INTEGER,
inspAreaName TEXT,
inspPointNumb TEXT,
taskId INTEGER,
taskType TEXT,
checkItemId INTEGER,
checkItemName TEXT,
checkItemNumb TEXT,
checkItemDesc TEXT,
keyPointId INTEGER,
keyPointName TEXT,
keyPointNumb TEXT,
regTime TEXT,
spotCheckTime TEXT,
spotCheckResult TEXT,
spotCheckUserName TEXT,
recheckTime TEXT,
recheckResult TEXT,
recheckUserName TEXT,
organId INTEGER,
organName TEXT,
updateTime TEXT,
primary key(reformId, userId)
);
insert into Reform select * from tempReform;
drop table tempReform;
COMMIT;
联合主键要一起写在所有字段最后如primary key(reformId, userId),否则会不生效。
3. 增加新的类Migration_13_Reform(和数据库版本号同步,我这里是从12升到13)继承于com.raizlabs.android.dbflow.sql.migration包下的BaseMigration并重写migrate()方法,记得开启事务。
@Migration(version = VERSION, database = AppDatabase.class)
public static class Migration_13_Reform extends BaseMigration {
@Override
public void migrate(@NonNull DatabaseWrapper database) {
database.beginTransaction();
String sql = AssetsUtils.getString("table_reform_recreate.sql", ScmpApplication.get());
database.execSQL(sql);
database.endTransaction();
}
}
最后附上AssetsUtils源码(网上搬运的):
public final class AssetsUtils {
private AssetsUtils() {
throw new UnsupportedOperationException("u can't instantiate me...");
}
/**
* 从文件中获取字符串
*
* @param fileName
* @param context
* @return
*/
public static String getString(String fileName, Context context) {
StringBuilder stringBuilder = new StringBuilder();
try {
AssetManager assetManager = context.getAssets();
BufferedReader bf = new BufferedReader(new InputStreamReader(
assetManager.open(fileName)));
String line;
while ((line = bf.readLine()) != null) {
stringBuilder.append(line);
}
} catch (IOException e) {
e.printStackTrace();
}
return stringBuilder.toString();
}
/**
* 根据字符串返回实体类
*
* @param fileName
* @param context
* @param tClass
* @param <T> 实体类
* @return
*/
public static <T> T getJsonObject(String fileName, Context context, Class<T> tClass) {
String jsonStr = getString(fileName, context);
Gson gson = new Gson();
return gson.fromJson(jsonStr, tClass);
}
}
- 缺陷
此方式在进行线上灰度测试时发现重大问题,根本就没有改到表结构,导致了app闪退。猜测原因如下:
我重写的migrate方法在dbflow中被调用的地方已经开启了事务,而我这里不仅在sql里面有个commit,还显式使用database.beginTransaction来开启了事务,怀疑这里会导致执行自己写的sql语句失败,并且源码中有try
catch怀疑执行sql出现异常被捕获,但是由于某种原因未被打印出来,下面给出的dbflow执行migration的源码可以看到,这是BaseDatabaseHelper类的executeMigrations方法源码:
protected void executeMigrations(@NonNull final DatabaseWrapper db,
final int oldVersion, final int newVersion) {
// will try migrations file or execute migrations from code
// 这里解释了,将会尝试migrations数据迁移文件或者从数据库版本号的不同来执行数据迁移,也就是我们的BaseMigration
// 从下面代码看出数据迁移文件是指下面格式的sql文件:assets/migrations/{databaseName}/{versionCode}.sql
try {
final List<String> files = Arrays.asList(FlowManager.getContext().getAssets().list(
MIGRATION_PATH + "/" + databaseDefinition.getDatabaseName()));
Collections.sort(files, new NaturalOrderComparator());
final Map<Integer, List<String>> migrationFileMap = new HashMap<>();
for (String file : files) {
try {
final Integer version = Integer.valueOf(file.replace(".sql", ""));
List<String> fileList = migrationFileMap.get(version);
if (fileList == null) {
fileList = new ArrayList<>();
migrationFileMap.put(version, fileList);
}
fileList.add(file);
} catch (NumberFormatException e) {
FlowLog.log(FlowLog.Level.W, "Skipping invalidly named file: " + file, e);
}
}
final Map<Integer, List<Migration>> migrationMap = databaseDefinition.getMigrations();
final int curVersion = oldVersion + 1;
try {
// 这里开启了数据库事务,可以看出,这里面全是try,catch,所以我写的方法极有可能被捕获了异常但是没打印出来。
db.beginTransaction();
// execute migrations in order, migration file first before wrapped migration classes.
for (int i = curVersion; i <= newVersion; i++) {
List<String> migrationFiles = migrationFileMap.get(i);
if (migrationFiles != null) {
for (String migrationFile : migrationFiles) {
executeSqlScript(db, migrationFile);
FlowLog.log(FlowLog.Level.I, migrationFile + " executed successfully.");
}
}
List<Migration> migrationsList = migrationMap.get(i);
if (migrationsList != null) {
for (Migration migration : migrationsList) {
// before migration
migration.onPreMigrate();
// migrate
migration.migrate(db);
// after migration cleanup
migration.onPostMigrate();
FlowLog.log(FlowLog.Level.I, migration.getClass() + " executed successfully.");
}
}
}
// setTransactionSuccessful() 方法设置事务的标志为成功,则所有从beginTransaction()开始的操作都会被提交,如果没有调用setTransactionSuccessful() 方法则回滚事务。
// 这里我怀疑我自己开启的事务有可能没有写这个方法导致了事务回滚
db.setTransactionSuccessful();
} finally {
// 关闭数据库事务
db.endTransaction();
}
} catch (IOException e) {
FlowLog.log(FlowLog.Level.E, "Failed to execute migrations.", e);
}
}
- 解决
从上面的源码也看出了,人家dbflow有自己执行sql的升级方式,那为什么不用呢,所以按照此方式,我是从version=12升级到version=13,那么久把这些sql语句放到13.sql(支持多行sql语句并且必须满足以分号结尾的标准),放到assets/migrations/scmp目录下面(格式是assets/migrations/{databaseName}/{versionCode}.sql),只需要把AppDatabase的版本改为13,那么dbflow就会自动去调用他的逻辑来执行啦。我的目录如下:
dbflow执行sql的源码如下(就是上面代码调用的):
/**
* Supports multiline sql statements with ended with the standard ";"
*
* @param db The database to run it on
* @param file the file name in assets/migrations that we read from
*/
// 支持多行sql语句,并且必须满足以分号结尾的标准
private void executeSqlScript(@NonNull DatabaseWrapper db,
@NonNull String file) {
try {
final InputStream input = FlowManager.getContext().getAssets().open(MIGRATION_PATH + "/" + getDatabaseDefinition().getDatabaseName() + "/" + file);
final BufferedReader reader = new BufferedReader(new InputStreamReader(input));
String line;
// ends line with SQL
String querySuffix = ";";
// standard java comments
String queryCommentPrefix = "--";
StringBuffer query = new StringBuffer();
while ((line = reader.readLine()) != null) {
line = line.trim();
boolean isEndOfQuery = line.endsWith(querySuffix);
if (line.startsWith(queryCommentPrefix)) {
continue;
}
if (isEndOfQuery) {
line = line.substring(0, line.length() - querySuffix.length());
}
query.append(" ").append(line);
if (isEndOfQuery) {
db.execSQL(query.toString());
query = new StringBuffer();
}
}
String queryString = query.toString();
if (queryString.trim().length() > 0) {
db.execSQL(queryString);
}
} catch (IOException e) {
FlowLog.log(FlowLog.Level.E, "Failed to execute " + file, e);
}
}
}
在解决此问题的过程中参考了以下文章,对dbflow及sqlite有了更进一步了解,特别感谢:
DBFlow:数据迁移(Migrations)
有关DBFlow的Migration的几个细节