Android 架构之数据库框架升级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
/**
     * 复制单个文件(可更名复制)
     *
     * @param oldPathFile 准备复制的文件源
     * @param newPathFile 拷贝到新绝对路径带文件名(注：目录路径需带文件名)
     * @return
     */
    public static void CopySingleFile(String oldPathFile, String newPathFile) {
        try {
//            int bytesum = 0;
            int byteread = 0;
            File oldfile = new File(oldPathFile);
            File newFile = new File(newPathFile);
            File parentFile = newFile.getParentFile();
            if (!parentFile.exists()) {
                parentFile.mkdirs();
            }
            if (oldfile.exists()) { //文件存在时
                InputStream inStream = new FileInputStream(oldPathFile); //读入原文件
                FileOutputStream fs = new FileOutputStream(newPathFile);
                byte[] buffer = new byte[1024];
                while ((byteread = inStream.read(buffer)) != -1) {
//                    bytesum += byteread; //字节数 文件大小
                    fs.write(buffer, 0, byteread);
                }
                inStream.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<?xml version="1.0" encoding="utf-8"?>
<updateXml>
    <createVersion version="V007">
        <createDb name="hqk">  <!-- 要升级数据库对应名 ，如果应用含多个数据库，那么可以创建多个 createDb 标签-->
            <sql_createTable>  <!-- 创建最新的表结构 -->
                <!--
                     @DbFiled("time")
                    private  String time;
                    @DbFiled("id")
                    private  Long id;
                    @DbFiled("path")
                    private  String path;
                 -->
                create table if not exists tb_photo ( id Long,tb_time TEXT ,tb_path TEXT,tb_name TEXT);
            </sql_createTable>
        </createDb>
    </createVersion>

    <!-- V001,V002对应版本的app升级到 最新V007版本的升级逻辑-->
    <updateStep versionFrom="V001,V002" versionTo="V007">
        <!-- 对应数据升级逻辑，对应上面的 createDb 标签name ，如果有多对 createDb，这里也可执行多对 updateDb-->
        <updateDb name="hqk">
            <sql_before> <!-- 将V001,V002对应的旧表重命名备份-->
                alter table tb_photo rename to bak_tb_photo;
            </sql_before>
            <sql_after>  <!-- 查询重命名后旧表数据，将对应数据添加至新表里-->
                insert into tb_photo(tb_time,id, tb_path) select tb_time,tb_id,tb_path from bak_tb_photo;
            </sql_after>
            <sql_after><!-- 删除旧表备份-->
                drop table if exists bak_tb_photo;
            </sql_after>
        </updateDb>
    </updateStep>

    <updateStep versionFrom="V003,V004,V005,V006" versionTo="V007">
        <updateDb name="hqk">
            <sql_before>
                alter table tb_photo rename to bak_tb_photo;
            </sql_before>
            <sql_after>
                insert into tb_photo(tb_time,id, tb_path) select tb_time,tb_id,tb_path from
                bak_tb_photo;
            </sql_after>

            <sql_after>
                drop table if exists bak_tb_photo;
            </sql_after>
        </updateDb>

    </updateStep>


</updateXml>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
public class DomUtils {
    /**
     * 读取升级xml
     *
     * @param context
     * @return
     */
    public static UpdateDbXml readDbXml(Context context) {
        InputStream is = null;
        Document document = null;
        try {
            is = context.getAssets().open("updateXml.xml");
            DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
            document = builder.parse(is);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        if (document == null) {
            return null;
        }

        UpdateDbXml xml = new UpdateDbXml(document);

        return xml;
    }
    /**
     * 新表插入数据
     *
     * @param xml
     * @param lastVersion 上个版本
     * @param thisVersion 当前版本
     * @return
     */
    public static UpdateStep findStepByVersion(UpdateDbXml xml, String lastVersion, String thisVersion) {
        if (lastVersion == null || thisVersion == null) {
            return null;
        }
        // 更新脚本
        UpdateStep thisStep = null;
        if (xml == null) {
            return null;
        }
        List<UpdateStep> steps = xml.getUpdateSteps();
        if (steps == null || steps.size() == 0) {
            return null;
        }

        for (UpdateStep step : steps) {
            if (step.getVersionFrom() == null || step.getVersionTo() == null) {
            } else {
                // 升级来源以逗号分隔
                String[] lastVersionArray = step.getVersionFrom().split(",");
                if (lastVersionArray != null && lastVersionArray.length > 0) {
                    for (int i = 0; i < lastVersionArray.length; i++) {
                        // 有一个配到update节点即升级数据
                        if (lastVersion.equalsIgnoreCase(lastVersionArray[i]) && step.getVersionTo().equalsIgnoreCase(thisVersion)) {
                            thisStep = step;
                            break;
                        }
                    }
                }
            }
        }
        return thisStep;
    }

    /**
     * 解析出对应版本的建表脚本
     *
     * @return
     */
    public static CreateVersion findCreateByVersion(UpdateDbXml xml, String version) {
        CreateVersion cv = null;
        if (xml == null || version == null) {
            return cv;
        }
        List<CreateVersion> createVersions = xml.getCreateVersions();

        if (createVersions != null) {
            for (CreateVersion item : createVersions) {
                Log.i("david", "item=" + item.toString());
                // 如果表相同则要支持xml中逗号分隔
                String[] createVersion = item.getVersion().trim().split(",");
                for (int i = 0; i < createVersion.length; i++) {
                    if (createVersion[i].trim().equalsIgnoreCase(version)) {
                        cv = item;
                        break;
                    }
                }
            }
        }
        return cv;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
/**
 * @ClassName: UpdateDbXml
 * @Description: 升级更新数据库
 *
 */
public class UpdateDbXml {
    /**
     * 升级脚本列表
     */
    private List<UpdateStep> updateSteps;
    /**
     * 升级版本
     */
    private List<CreateVersion> createVersions;

    public UpdateDbXml(Document document) {
        {
            // 获取升级脚本
            NodeList updateSteps = document.getElementsByTagName("updateStep");
            this.updateSteps = new ArrayList<UpdateStep>();
            for (int i = 0; i < updateSteps.getLength(); i++) {
                Element ele = (Element) (updateSteps.item(i));
                Log.i("jett","updateSteps 各个升级的版本:"+ele.toString());
                UpdateStep step = new UpdateStep(ele);
                this.updateSteps.add(step);
            }
        }
        {
            /**
             * 获取各升级版本
             */
            NodeList createVersions = document.getElementsByTagName("createVersion");
            this.createVersions = new ArrayList<CreateVersion>();
            for (int i = 0; i < createVersions.getLength(); i++) {
                Element ele = (Element) (createVersions.item(i));
                Log.i("jett","各个升级的版本:"+ele.toString());
                CreateVersion cv = new CreateVersion(ele);
                this.createVersions.add(cv);
            }
        }
    }

    public List<UpdateStep> getUpdateSteps() {
        return updateSteps;
    }

    public void setUpdateSteps(List<UpdateStep> updateSteps) {
        this.updateSteps = updateSteps;
    }

    public List<CreateVersion> getCreateVersions() {
        return createVersions;
    }

    public void setCreateVersions(List<CreateVersion> createVersions) {
        this.createVersions = createVersions;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
/**
 * @ClassName: UpdateStep
 * @Description: 数据库升级脚本信息
 */
public class UpdateStep
{
 /**
  * 旧版本
  */
 private String versionFrom;

 /**
  * 新版本
  */
 private String versionTo;

 /**
  * 更新数据库脚本
  */
 private List<UpdateDb> updateDbs;

 // ==================================================

 public UpdateStep(Element ele)
 {
  versionFrom = ele.getAttribute("versionFrom");
  versionTo = ele.getAttribute("versionTo");
  updateDbs = new ArrayList<UpdateDb>();

  NodeList dbs = ele.getElementsByTagName("updateDb");
  for (int i = 0; i < dbs.getLength(); i++)
  {
   Element db = (Element) (dbs.item(i));
   UpdateDb updateDb = new UpdateDb(db);
   this.updateDbs.add(updateDb);
  }
 }

 public List<UpdateDb> getUpdateDbs()
 {
  return updateDbs;
 }

 public void setUpdateDbs(List<UpdateDb> updateDbs)
 {
  this.updateDbs = updateDbs;
 }

 public String getVersionFrom()
 {
  return versionFrom;
 }

 public void setVersionFrom(String versionFrom)
 {
  this.versionFrom = versionFrom;
 }

 public String getVersionTo()
 {
  return versionTo;
 }

 public void setVersionTo(String versionTo)
 {
  this.versionTo = versionTo;
 }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
**
 * @ClassName: UpdateDb
 * @Description: 更新数据库脚本
 *
 */
public class UpdateDb
{
 /**
  * 数据库名称
  */
 private String dbName;
 /**
  * 
  */
 private List<String> sqlBefores;
 /**
  * 
  */
 private List<String> sqlAfters;

 public UpdateDb(Element ele)
 {
  dbName = ele.getAttribute("name");
  sqlBefores = new ArrayList<String>();
  sqlAfters = new ArrayList<String>();

  {
   NodeList sqls = ele.getElementsByTagName("sql_before");
   for (int i = 0; i < sqls.getLength(); i++)
   {
    String sql_before = sqls.item(i).getTextContent();
    this.sqlBefores.add(sql_before);
   }
  }

  {
   NodeList sqls = ele.getElementsByTagName("sql_after");
   for (int i = 0; i < sqls.getLength(); i++)
   {
    String sql_after = sqls.item(i).getTextContent();
    this.sqlAfters.add(sql_after);
   }
  }

 }

 public String getName()
 {
  return dbName;
 }

 public void setDbName(String dbName)
 {
  this.dbName = dbName;
 }

 public List<String> getSqlBefores()
 {
  return sqlBefores;
 }

 public void setSqlBefores(List<String> sqlBefores)
 {
  this.sqlBefores = sqlBefores;
 }

 public List<String> getSqlAfters()
 {
  return sqlAfters;
 }

 public void setSqlAfters(List<String> sqlAfters)
 {
  this.sqlAfters = sqlAfters;
 }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
public class CreateVersion
{
 /**
  * 版本信息
  */
 private String version;

 /**
  * 创建数据库表脚本
  */
 private List<CreateDb> createDbs;

 public CreateVersion(Element ele)
 {
  version = ele.getAttribute("version");
  Log.i("jett","CreateVersion="+version);
  {
   createDbs = new ArrayList<CreateDb>();
   NodeList cs = ele.getElementsByTagName("createDb");
   for (int i = 0; i < cs.getLength(); i++)
   {
    Element ci = (Element) (cs.item(i));
    CreateDb cd = new CreateDb(ci);
    this.createDbs.add(cd);
   }
  }
 }

 public String getVersion()
 {
  return version;
 }

 public void setVersion(String version)
 {
  this.version = version;
 }

 public List<CreateDb> getCreateDbs()
 {
  return createDbs;
 }

 public void setCreateDbs(List<CreateDb> createDbs)
 {
  this.createDbs = createDbs;
 }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
/**
 * @ClassName: CreateDb
 * @Description: 创建数据库脚本
 *
 */
public class CreateDb
{
 /**
  * 数据库表名
  */
 private String name;

 /**
  * 创建表的sql语句集合
  */
 private List<String> sqlCreates;

 public CreateDb(Element ele)
 {
  name = ele.getAttribute("name");

  {
   sqlCreates = new ArrayList<String>();
   NodeList sqls = ele.getElementsByTagName("sql_createTable");
   for (int i = 0; i < sqls.getLength(); i++)
   {
    String sqlCreate = sqls.item(i).getTextContent();
    this.sqlCreates.add(sqlCreate);
   }
  }
 }

 public String getName()
 {
  return name;
 }

 public void setName(String name)
 {
  this.name = name;
 }

 public List<String> getSqlCreates()
 {
  return sqlCreates;
 }

 public void setSqlCreates(List<String> sqlCreates)
 {
  this.sqlCreates = sqlCreates;
 }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
public class UpdateManager {
    private File parentFile = ContUtils.parentFile;
    private File bakFile = ContUtils.bakFile;
    private List<User> userList;

    public void startUpdateDb(Context context) {
        //读取XML文件，将XML内容转化为对应对象
        UpdateDbXml updateDbxml = DomUtils.readDbXml(context);
        //    下载 上一个版本  --》下一个版本  【注：在下载时，需要将旧版本、新版本以逗号的形式写入文件缓存】
        String[] versions = FileUtil.getLocalVersionInfo(new File(parentFile,
                "update.txt"));
        String lastVersion = versions[0];//拿到上一个版本
        String thisVersion = versions[1];//拿到当前版本

        //数据库File原地址
        String userFile = ContUtils.sqliteDatabasePath;
        //数据库File备份地址
        String user_bak = ContUtils.copySqliteDatabasePath;
        //升级前，数据库File备份
        FileUtil.CopySingleFile(userFile, user_bak);
        //根据对应新旧版本号查询XML转化对象里面的脚本，得到对应升级脚本
        UpdateStep updateStep = DomUtils.findStepByVersion(updateDbxml, lastVersion, thisVersion);
        if (updateStep == null) {
            return;
        }
        //拿到对应升级脚本
        List<UpdateDb> updateDbs = updateStep.getUpdateDbs();
        try {
            //将原始数据库中所有的表名 更改成 bak_表名(数据还在)
            executeBeforesSql(updateDbs);
            //检查新表，创建新表
            CreateVersion createVersion = DomUtils.findCreateByVersion(updateDbxml, thisVersion);
            executeCreateVersion(createVersion);
            //将原来bak_表名  的数据迁移到 新表中
            executeAftersSql(updateDbs);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private void executeAftersSql(List<UpdateDb> updateDbs) throws Exception {
        for (UpdateDb db : updateDbs) {
            if (db == null || db.getName() == null) {
                throw new Exception("db or dbName is null;");
            }
            List<String> sqls = db.getSqlAfters();
            SQLiteDatabase sqlitedb = getDb();
            //执行数据库语句
            executeSql(sqlitedb, sqls);
            sqlitedb.close();
        }
    }


    private void executeCreateVersion(CreateVersion createVersion) throws Exception {
        if (createVersion == null || createVersion.getCreateDbs() == null) {
            throw new Exception("createVersion or createDbs is null;");
        }
        for (CreateDb cd : createVersion.getCreateDbs()) {
            if (cd == null || cd.getName() == null) {
                throw new Exception("db or dbName is null when createVersion;");
            }
            // 创建数据库表sql
            List<String> sqls = cd.getSqlCreates();
            SQLiteDatabase sqlitedb = getDb();
            executeSql(sqlitedb, sqls);
            sqlitedb.close();

        }
    }


    //所有的表名 更改成 bak_表名(数据还在)
    private void executeBeforesSql(List<UpdateDb> updateDbs) throws Exception {
        for (UpdateDb db : updateDbs) {
            if (db == null || db.getName() == null) {
                throw new Exception("db or dbName is null;");
            }
            List<String> sqls = db.getSqlBefores();
            SQLiteDatabase sqlitedb = getDb();
            //执行数据库语句
            executeSql(sqlitedb, sqls);
            sqlitedb.close();

        }
    }

    private SQLiteDatabase getDb() {
        String dbfilepath = null;
        SQLiteDatabase sqlitedb = null;

        dbfilepath = ContUtils.sqliteDatabasePath;// logic对应的数据库路径
        if (dbfilepath != null) {
            File f = new File(dbfilepath);
            f.mkdirs();
            if (f.isDirectory()) {
                f.delete();
            }
            sqlitedb = SQLiteDatabase.openOrCreateDatabase(dbfilepath, null);
        }
        return sqlitedb;
    }


    private void executeSql(SQLiteDatabase sqlitedb, List<String> sqls) {
        // 检查参数
        if (sqls == null || sqls.size() == 0) {
            return;
        }
        sqlitedb.beginTransaction();
        for (String sql : sqls) {
            sql = sql.replaceAll("rn", " ");
            sql = sql.replaceAll("n", " ");
            if (!"".equals(sql.trim())) {
                try {
                    // Logger.i(TAG, "执行sql：" + sql, false);
                    sqlitedb.execSQL(sql);
                } catch (SQLException e) {
                }
            }
        }

        sqlitedb.setTransactionSuccessful();
        sqlitedb.endTransaction();
    }

}