前言
前段時間研究了一下greendao數(shù)據(jù)庫升級模塊，發(fā)現(xiàn)了一些存在的一些問題痛點，特拿來晾曬一下，以防發(fā)霉。
問題現(xiàn)狀
話說為什么要做數(shù)據(jù)庫自動升級這塊的探索呢，主要有以下幾點原因：
- 現(xiàn)有的數(shù)據(jù)庫升級方式過于繁瑣，每個版本都需要進行一次手動升級，每次升級都要寫一大推if else判斷新舊數(shù)據(jù)庫版本，一不小心就容易出錯。
- 出現(xiàn)跨版本升級數(shù)據(jù)庫的時候，偶爾會出現(xiàn)數(shù)據(jù)庫字段丟失的情況，造成一些用戶閃退現(xiàn)象。
- 主要還是人懶，不想每次都寫一大堆重復的代碼
思考
話說有沒有一種方式能夠比較優(yōu)雅地解決這個問題呢？一波搜索后，發(fā)現(xiàn)很多解決方案基本都是類似的，分為兩類：
第一類：根據(jù)當前版本依次遞歸的常規(guī)升級方式，即每個新版發(fā)布都在對應的版本號下面加入新增的表或者字段。這種傳統(tǒng)的升級方式，顯得不夠“自動化”，寫起來比較麻煩，而且有時候還容易遺漏掉部分新增字段，造成應用的崩潰問題。
第二類：基本上參考了stackoverflow上面一位大佬的自動化升級方式。他的思路是這樣的：
1.拷貝原有數(shù)據(jù)表，新建temp表備份數(shù)據(jù)
2.刪除原有數(shù)據(jù)表
3.新建現(xiàn)有數(shù)據(jù)表
4.把temp表備份數(shù)據(jù)插入到新建的現(xiàn)有表中
5.刪除備份temp表
6.balabalabla...
反正就是一頓操作猛如虎，數(shù)據(jù)搬過來搬過去，刪完再建、各種反射，看起來很炫酷的樣子。
我就在想，為什么就不直接遍歷檢測 缺失表 + 缺失表字段，然后直接插入缺失的表或字段呢？如果可以這樣操作的話，那么性能方面肯定會有一個顯著的提升，極大的減少了數(shù)據(jù)庫操作開銷，豈不是看起來很棒棒？
窩草，這圖怎么這么大
解決方案
這個時候，一個熱乎的方案新鮮出爐了。主要思路還是遍歷數(shù)據(jù)庫尋找缺失的表和表字段。然后完善對應的表結構。
public final class MigrationHelper {
private static final String TAG = "MigrationHelper";
private static final String SQLITE_MASTER = "sqlite_master";
private static final String SQLITE_TEMP_MASTER = "sqlite_temp_master";
public static void migrate(SQLiteDatabase db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
Database database = new StandardDatabase(db);
migrate(database, daoClasses);
}
public static void migrate(Database database, Class<? extends AbstractDao<?, ?>>... daoClasses) {
generateTempTables(database, daoClasses);
for (int i = 0; i < daoClasses.length; i++) {
DaoConfig daoConfig = new DaoConfig(database, daoClasses[i]);
dropTable(database, true, daoConfig);
createTable(database, false, daoConfig);
}
restoreData(database, daoClasses);
}
private static void dropTable(Database database, boolean ifExists, DaoConfig daoConfig) {
String sql = String.format("DROP TABLE %s\"%s\"", ifExists ? "IF EXISTS " : "", daoConfig.tablename);
database.execSQL(sql);
}
private static void generateTempTables(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
for (int i = 0; i < daoClasses.length; i++) {
String tempTableName = null;
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName = daoConfig.tablename;
if (!isTableExists(db, false, tableName)) {
continue;
}
try {
tempTableName = daoConfig.tablename.concat("_TEMP");
StringBuilder dropTableStringBuilder = new StringBuilder();
dropTableStringBuilder.append("DROP TABLE IF EXISTS ").append(tempTableName).append(";");
db.execSQL(dropTableStringBuilder.toString());
StringBuilder insertTableStringBuilder = new StringBuilder();
insertTableStringBuilder.append("CREATE TEMPORARY TABLE ").append(tempTableName);
insertTableStringBuilder.append(" AS SELECT * FROM ").append(tableName).append(";");
db.execSQL(insertTableStringBuilder.toString());
} catch (SQLException e) {
Log.e(TAG, "【Failed to generate temp table】" + tempTableName, e);
}
}
}
private static boolean isTableExists(Database db, boolean isTemp, String tableName) {
if (db == null || TextUtils.isEmpty(tableName)) {
return false;
}
String dbName = isTemp ? SQLITE_TEMP_MASTER : SQLITE_MASTER;
String sql = "SELECT COUNT(*) FROM " + dbName + " WHERE type = ? AND name = ?";
Cursor cursor=null;
int count = 0;
try {
cursor = db.rawQuery(sql, new String[]{"table", tableName});
if (cursor == null || !cursor.moveToFirst()) {
return false;
}
count = cursor.getInt(0);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null)
cursor.close();
}
return count > 0;
}
private static String getColumnsStr(DaoConfig daoConfig) {
if (daoConfig == null) {
return "no columns";
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < daoConfig.allColumns.length; i++) {
builder.append(daoConfig.allColumns[i]);
builder.append(",");
}
if (builder.length() > 0) {
builder.deleteCharAt(builder.length() - 1);
}
return builder.toString();
}
private static void restoreData(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) {
for (int i = 0; i < daoClasses.length; i++) {
DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]);
String tableName = daoConfig.tablename;
String tempTableName = daoConfig.tablename.concat("_TEMP");
if (!isTableExists(db, true, tempTableName)) {
continue;
}
try {
// get all columns from tempTable, take careful to use the columns list
List<String> columns = getColumns(db, tempTableName);
ArrayList<String> properties = new ArrayList<>(columns.size());
for (int j = 0; j < daoConfig.properties.length; j++) {
String columnName = daoConfig.properties[j].columnName;
if (columns.contains(columnName)) {
properties.add(columnName);
}
}
if (properties.size() > 0) {
final String columnSQL = TextUtils.join(",", properties);
StringBuilder insertTableStringBuilder = new StringBuilder();
insertTableStringBuilder.append("INSERT INTO ").append(tableName).append(" (");
insertTableStringBuilder.append(columnSQL);
insertTableStringBuilder.append(") SELECT ");
insertTableStringBuilder.append(columnSQL);
insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";");
db.execSQL(insertTableStringBuilder.toString());
}
StringBuilder dropTableStringBuilder = new StringBuilder();
dropTableStringBuilder.append("DROP TABLE ").append(tempTableName);
db.execSQL(dropTableStringBuilder.toString());
} catch (SQLException e) {
Log.e(TAG, "【Failed to restore data from temp table 】" + tempTableName, e);
}
}
}
private static List<String> getColumns(Database db, String tableName) {
List<String> columns = null;
Cursor cursor = null;
try {
cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 0", null);
if (null != cursor && cursor.getColumnCount() > 0) {
columns = Arrays.asList(cursor.getColumnNames());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null)
cursor.close();
if (null == columns)
columns = new ArrayList<>();
}
return columns;
}
public static void createTable(Database db, boolean ifNotExists, DaoConfig daoConfig) {
String tableName = daoConfig.tablename;
StringBuilder builder = new StringBuilder();
builder.append("CREATE TABLE ");
builder.append(ifNotExists ? "IF NOT EXISTS ": "");
builder.append(tableName);
builder.append(getColumnsSql(daoConfig));
LogUtil.d(TAG,"【createTable】 sql:" + builder.toString());
db.execSQL(builder.toString()); // 6: Description
}
private static String getColumnsSql(DaoConfig daoConfig) {
if (daoConfig == null) {
return "";
}
StringBuilder builder = new StringBuilder(" (");
for (int i = 0; i < daoConfig.properties.length; i++) {
builder.append(String.format("\"%s\" %s,", daoConfig.properties[i].columnName,
getPropertyType(daoConfig.properties[i].type)));
}
if (daoConfig.properties.length > 0 && builder.length() > 0) {
builder.deleteCharAt(builder.length() - 1);
}
builder.append("); ");
return builder.toString();
}
/**
* 根據(jù)字段類型返回對應的數(shù)據(jù)庫字段語句
* @param type
* @return
*/
private static String getPropertyType(Class<?> type) {
if (type.equals(byte[].class)) {
return "BLOB";
} else if (type.equals(String.class)) {
return "TEXT DEFAULT ''";
} else if (type.equals(boolean.class) || type.equals(Boolean.class)
|| type.equals(int.class) || type.equals(Integer.class)
|| type.equals(long.class) || type.equals(Long.class)
|| type.equals(Date.class) || type.equals(Byte.class)) {
return "INTEGER DEFAULT (0)";
} else if (type.equals(float.class) || type.equals(Float.class)
|| type.equals(double.class) || type.equals(Double.class)){
return "REAL DEFAULT (0)";
}
return "TEXT DEFAULT ''";
}
}
接下來是創(chuàng)建一個關聯(lián)數(shù)據(jù)庫的實體類Demo，比如當前有一個存放關鍵字的表KeywordHistory
@Entity(nameInDb = "KeywordHistory")
public class KeywordHistoryEntity {
@Id(autoincrement = true)
@Property(nameInDb = "Id")
public Long Id;
@Property(nameInDb = "Keyword")
public String Keyword;
@Property(nameInDb = "QueryTime")
public long QueryTime;
@Generated(hash = 4193202)
public KeywordHistoryEntity(Long Id, String Keyword, long QueryTime) {
this.Id = Id;
this.Keyword = Keyword;
this.QueryTime = QueryTime;
}
@Generated(hash = 462930205)
public KeywordHistoryEntity() {
}
public Long getId() {
return this.Id;
}
public void setId(Long Id) {
this.Id = Id;
}
public String getKeyword() {
return this.Keyword;
}
public void setKeyword(String Keyword) {
this.Keyword = Keyword;
}
public long getQueryTime() {
return this.QueryTime;
}
public void setQueryTime(long QueryTime) {
this.QueryTime = QueryTime;
}
}
接下來是創(chuàng)建/升級數(shù)據(jù)庫時候需要完成的操作步驟，很簡單，只需要修改兩個地方
1.build.gradle 文件下greenDAO schemaVersion版本號+1
2.將新增或者修改后的EntityDao 依次放在onCreate和onUpgrade KeywordHistoryEntityDao 對應的位置，即完成數(shù)據(jù)庫的升級。剩下的表和字段的創(chuàng)建工作MigrationHelper這個類幫你自動完成。
1.build.gradle文件
greendao {
schemaVersion 1 //每次更新數(shù)據(jù)庫，這個地方版本號都要加1
}
...
2.數(shù)據(jù)庫OpenHelper 管理類
public class DBOpenHelper extends DaoMaster.OpenHelper {
public DBOpenHelper(Context context, String name) {
super(context, name);
}
@Override
public void onCreate(Database db) {
super.onCreate(db);
startMigrate(db);
}
@Override
public void onUpgrade(Database db, int oldVersion, int newVersion) {
super.onUpgrade(db, oldVersion, newVersion);
startMigrate(db);
}
private void startMigrate(Database db) {
MigrationHelper.migrate(db, KeywordHistoryEntityDao.class);
}
}
什么？這點操作 就完成數(shù)據(jù)庫升級了？沒錯啊，自動化升級就是這么easy~ 如果有需要趕緊也試試看吧
使用方法
對了，還有就是使用了自動升級之后，調(diào)用方法和以前的greenDAO有什么區(qū)別呢？其實調(diào)用的方法其實和greendao的日常操作一致，如下所示：
DBOpenHelper helper = new DBOpenHelper(getApplicationContext(), "test.db");
DaoMaster daoMaster = new DaoMaster(helper.getWritableDatabase());
final KeywordHistoryEntityDao dao = daoMaster.newSession().getKeywordHistoryEntityDao();
KeywordHistoryEntity historyEntity = new KeywordHistoryEntity(1, "關鍵字" , 1);
dao.insert(historyEntity);
自動升級的日志如下
11-19 14:49:43.873 5967-5967/com.github.mhlistener.greendaoupgradeapp D/MigrationHelper: 【Generate temp table】start
11-19 14:49:43.874 5967-5967/com.github.mhlistener.greendaoupgradeapp D/MigrationHelper: 【Generate temp table】 dropTableStringBuilder:DROP TABLE IF EXISTS KeywordHistory_TEMP;
【Generate temp table】 insertTableStringBuilder:CREATE TEMPORARY TABLE KeywordHistory_TEMP AS SELECT * FROM KeywordHistory;
【Table】KeywordHistory
---Columns-->Id,Keyword,QueryTime
11-19 14:49:43.875 5967-5967/com.github.mhlistener.greendaoupgradeapp D/MigrationHelper: 【Generate temp table】KeywordHistory_TEMP
【Generate temp table】complete
【Drop all table and recreate all table】
【createTable】 sql:CREATE TABLE KeywordHistory ("Id" INTEGER DEFAULT (0),"Keyword" TEXT DEFAULT '',"QueryTime" INTEGER DEFAULT (0));
【Restore data】start
11-19 14:49:43.876 5967-5967/com.github.mhlistener.greendaoupgradeapp D/MigrationHelper: 【Restore data】 db sql: INSERT INTO KeywordHistory (Id,Keyword,QueryTime) SELECT Id,Keyword,QueryTime FROM KeywordHistory_TEMP;
【Restore data】 to KeywordHistory
【Drop temp table】KeywordHistory_TEMP
【Restore data】complete
以上便是自動化升級的全部代碼，代碼已上傳https://github.com/mhlistener/GreenDaoUpgrade，喜歡的話可以star一下，大佬們?nèi)绻懈玫慕ㄗh歡迎提一波issue。
