在使用SugarORM的时候,有可能在后期的工作中进行数据库的升级操作。具体的操作参考:http://satyan.github.io/sugar/migration.html
文章链接:http://blog.csdn.net/lylddingHFFW/article/details/78230320
记录:在进行数据库升级时 注意
1)不支持select等返回数据集结果的语句;
2)在使用alter sql增加表中列的时候,如果Java文件中也增加同样的字段,则会提示错误,增加重复列,后边的分析会有提到;
3)assets的位置是在src/main/assets/;
public void doUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
List<Class> domainClasses = getDomainClasses(context);
String sql = "select count(*) from sqlite_master where type='table' and name='%s';";
for (Class domain : domainClasses) {
String tableName = NamingHelper.toSQLName(domain);
Cursor c = sqLiteDatabase.rawQuery(String.format(sql, tableName), null);
//判断table是否存在,若存在,则检查table中的columns。
if (c.moveToFirst() && c.getInt(0) == 0) {
createTable(domain, sqLiteDatabase);
} else {
addColumns(domain, sqLiteDatabase);
}
}
// 执行升级数据库的sql文件。
executeSugarUpgrade(sqLiteDatabase, oldVersion, newVersion);
}
在addColumns中,检查table中的columns。主要时比对Java文件中的属性和数据库 中table中的columns是否一直,若Java文件中有新增加的属性,则拼接alter table 的sql。
private void addColumns(Class<?> table, SQLiteDatabase sqLiteDatabase) {
List<Field> fields = ReflectionUtil.getTableFields(table);
String tableName = NamingHelper.toSQLName(table);
ArrayList<String> presentColumns = getColumnNames(sqLiteDatabase, tableName);
ArrayList<String> alterCommands = new ArrayList<>();
for (Field column : fields) {
String columnName = NamingHelper.toSQLName(column);
String columnType = QueryBuilder.getColumnType(column.getType());
if (column.isAnnotationPresent(Column.class)) {
Column columnAnnotation = column.getAnnotation(Column.class);
columnName = columnAnnotation.name();
}
if (!presentColumns.contains(columnName)) {
//拼接 sql 语句
StringBuilder sb = new StringBuilder("ALTER TABLE ");
sb.append(tableName).append(" ADD COLUMN ").append(columnName).append(" ").append(columnType);
if (column.isAnnotationPresent(NotNull.class)) {
if (columnType.endsWith(" NULL")) {
sb.delete(sb.length() - 5, sb.length());
}
sb.append(" NOT NULL");
}
// Unique is not working on ALTER TABLE
// if (column.isAnnotationPresent(Unique.class)) {
// sb.append(" UNIQUE");
// }
alterCommands.add(sb.toString());
}
}
for (String command : alterCommands) {
Log.i("Sugar", command);
//执行 拼接的sql 语句。
sqLiteDatabase.execSQL(command);
}
}
executeSugarUpgrade函数中主要执行assets资源文件中的2.sql,3.sql等等。
private boolean executeSugarUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
boolean isSuccess = false;
try {
// 拿到assets中的sql文件名
List<String> files = Arrays.asList(this.context.getAssets().list("sugar_upgrades"));
Collections.sort(files, new NumberComparator());
for (String file : files) {
Log.i(SUGAR, "filename : " + file);
try {
int version = Integer.valueOf(file.replace(".sql", ""));
// 只会执行旧版本到新版本之间的sql文件
if ((version > oldVersion) && (version <= newVersion)) {
//执行sql文件
executeScript(db, file);
isSuccess = true;
}
} catch (NumberFormatException e) {
Log.i(SUGAR, "not a sugar script. ignored." + file);
}
}
} catch (IOException e) {
Log.e(SUGAR, e.getMessage());
}
return isSuccess;
}
执行sql脚本文件
private void executeScript(SQLiteDatabase db, String file) {
try {
//打开sql脚本资源
InputStream is = this.context.getAssets().open("sugar_upgrades/" + file);
BufferedReader reader = new BufferedReader(new InputStreamReader(is));
StringBuilder sb = new StringBuilder();
String line;
while ((line = reader.readLine()) != null) {
sb.append(line);
}
MigrationFileParser migrationFileParser = new MigrationFileParser(sb.toString());
//以分号;来分割成不同的sql语句
for(String statement: migrationFileParser.getStatements()){
Log.i("Sugar script", statement);
if (!statement.isEmpty()) {
// 调用SqliteDatebase,来执行
db.execSQL(statement);
}
}
} catch (IOException e) {
Log.e(SUGAR, e.getMessage());
}
Log.i(SUGAR, "Script executed");
}