greenDAO自动化升级探索

JohnsonZZZ

2018.01.09 13:20:09字数 989阅读 967

前言

好像有好久没写博客的样子~ 行，那今天就再找个理由吹一波。前段时间研究了一下greendao数据库升级模块，发现了一些存在的一些问题痛点，特拿来晾晒一下，以防发霉。

问题现状

话说为什么要做数据库自动升级这块的探索呢，主要有以下几点原因：

现有的数据库升级方式过于繁琐，每个版本都需要进行一次手动升级，每次升级都要写一大推if else判断新旧数据库版本，一不小心就容易出错。
出现跨版本升级数据库的时候，偶尔会出现数据库字段丢失的情况，造成一些用户闪退现象。
主要还是人懒，不想每次都写一大堆重复的代码

思考

话说有没有一种方式能够比较优雅地解决这个问题呢？一波搜索后，发现很多解决方案基本都是类似的，分为两类：

第一类：根据当前版本依次递归的常规升级方式，即每个新版发布都在对应的版本号下面加入新增的表或者字段。这种传统的升级方式，显得不够“自动化”，写起来比较麻烦，而且有时候还容易遗漏掉部分新增字段，造成应用的崩溃问题。
第二类：基本上参考了stackoverflow上面一位大佬的自动化升级方式。他的思路是这样的：
1.拷贝原有数据表，新建temp表备份数据
2.删除原有数据表
3.新建现有数据表
4.把temp表备份数据插入到新建的现有表中
5.删除备份temp表
6.balabalabla...

反正就是一顿操作猛如虎，数据搬过来搬过去，删完再建、各种反射，看起来很炫酷的样子。
我就在想，为什么就不直接遍历检测缺失表 + 缺失表字段，然后直接插入缺失的表或字段呢？如果可以这样操作的话，那么性能方面肯定会有一个显著的提升，极大的减少了数据库操作开销，岂不是看起来很棒棒？

窝草，这图怎么这么大

解决方案

这个时候，一个热乎的方案新鲜出炉了。主要思路还是遍历数据库寻找缺失的表和表字段。然后完善对应的表结构。

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();
    }

    /**
     * 根据字段类型返回对应的数据库字段语句
     * @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 ''";
    }

}

接下来是创建一个关联数据库的实体类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;
    }


}

接下来是创建/升级数据库时候需要完成的操作步骤，很简单，只需要修改两个地方
1.build.gradle 文件下greenDAO schemaVersion版本号+1
2.将新增或者修改后的EntityDao 依次放在onCreate和onUpgrade KeywordHistoryEntityDao 对应的位置，即完成数据库的升级。剩下的表和字段的创建工作MigrationHelper这个类帮你自动完成。


1.build.gradle文件
greendao {
    schemaVersion 1 //每次更新数据库，这个地方版本号都要加1
}

...

2.数据库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);
    }

}

什么？这点操作就完成数据库升级了？没错啊，自动化升级就是这么easy~ 如果有需要赶紧也试试看吧

使用方法

对了，还有就是使用了自动升级之后，调用方法和以前的greenDAO有什么区别呢？其实调用的方法其实和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一下，大佬们如果有更好的建议欢迎提一波issue。

greenDAO

invocationtargetexception kaptexecution greendao

greendao android数据

androidstudio4 androidstudio greendao 1.1

dinstinct greendao with how

526互联

greenDAO自动化升级探索

greenDAO自动化升级探索

前言

问题现状

思考

解决方案

使用方法