Introduction to Android database version upgrade and data migration

Introduction to Android database version upgrade and data migration
Overview

The databases involved in Android development use the lightweight SQLite3. In actual development, it is sufficient to use SharedPreferences to store some simple data, databases are used for storage only when the data structure is slightly complex. The design of database tables is often not perfect at the beginning. In the application version development iteration, the structure of the table also needs to be adjusted. In this case, the Database Upgrade problem is involved.

Database Upgrade

Database upgrades mainly include the following situations:

Add Table Delete table Modify Table
Add Table fields to delete table fields

It is not a problem to add or delete tables because they do not involve data migration. To add a TABLE, you only need to CRTATE the TABLE on the basis of the original one. To delete a TABLE, you do not need to delete historical data, you only need to DROP the TABLE. What about modifying a table?

In fact, in many cases, the simplest and most violent way for programmers to make it easy to figure out is to delete the original table and re-create a new table, so that other factors do not need to be considered. However, the user experience is very poor. For example, if the user is downloading the file from the current download list, update the file at this time, in the new version, the update point is to update the database table of the download list. After the update, the user finds that the download list has become empty, so the user can see the 99% files that have been downloaded. if avi doesn't come, It won't crash. This kind of experience is very bad. You can uninstall your application in minutes.

Data migration becomes very important when database tables are upgraded. How can this problem be achieved?

Table upgrade and data migration

Currently, developers will use third parties for efficiency purposes. The database in this article is based on ORMLite, so we will discuss it based on this.

1->	2->	3
A	A +	A
B	B-	B
C	C	C +

In the table above, the version is upgraded from version 1 to 2 and then to 3, 1-> 2-> 3. During this period, the table ABC changes. '+' indicates that fields are added to the table, '-' indicates that fields are deleted from the table. For example, if field 1 is upgraded to 2, field A is added to table A, and field B is deleted, and Table C is not changed.

First, we must first understand SQLiteOpenHelper

/**     * Called when the database is created for the first time. This is where the     * creation of tables and the initial population of the tables should happen.     *     * @param db The database.     */    public abstract void onCreate(SQLiteDatabase db);

And

/**     * Called when the database needs to be upgraded. The implementation     * should use this method to drop tables, add tables, or do anything else it     * needs to upgrade to the new schema version.     * @param db The database.     * @param oldVersion The old database version.     * @param newVersion The new database version.     */    public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);

When to call. The document clearly states that onCreate () is called when the database is first created, while onUpgrade () is called when the database version is upgraded.

First, create three classes A, B, and C, and use the OrmLite annotation to create A table.

A. class

import com.j256.ormlite.field.DatabaseField;import com.j256.ormlite.table.DatabaseTable;@DatabaseTable(tableName = tb_a)public class A {    @DatabaseField(generatedId = true)    public int id;    @DatabaseField    public String name;}

B. class

import com.j256.ormlite.field.DatabaseField;import com.j256.ormlite.table.DatabaseTable;@DatabaseTable(tableName = tb_b)public class B {    @DatabaseField(generatedId = true)    public int id;    @DatabaseField    public String name;    @DatabaseField    public String age;}

C. class

import com.j256.ormlite.field.DatabaseField;import com.j256.ormlite.table.DatabaseTable;@DatabaseTable(tableName = tb_c)public class C {    @DatabaseField(generatedId = true)    public int id;    @DatabaseField    public String name;}

Create your own Helper MySqliteHelper. class

import android.content.Context;import android.database.sqlite.SQLiteDatabase;import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;import com.j256.ormlite.support.ConnectionSource;import com.j256.ormlite.table.TableUtils;import java.sql.SQLException;public class MySqliteHelper extends OrmLiteSqliteOpenHelper{    private final static String DATABASE_NAME=test.db;    private final static int DATABASE_VERSION = 1;    private static MySqliteHelper mInstance;    public MySqliteHelper(Context context) {        super(context, DATABASE_NAME, null, DATABASE_VERSION);    }    public static MySqliteHelper getInstance(Context context) {        if (mInstance == null) {            mInstance= new MySqliteHelper(context);        }        return mInstance;    }    @Override    public void onCreate(SQLiteDatabase database, ConnectionSource connectionSource) {        try {            TableUtils.createTableIfNotExists(connectionSource,A.class);            TableUtils.createTableIfNotExists(connectionSource,B.class);            TableUtils.createTableIfNotExists(connectionSource,C.class);        } catch (SQLException e) {            e.printStackTrace();        }    }    @Override    public void onUpgrade(SQLiteDatabase database, ConnectionSource connectionSource, int oldVersion, int newVersion) {    }}

Create Dao for Data Operations

import android.content.Context;import com.j256.ormlite.dao.Dao;import java.sql.SQLException;public class ADao {    private Dao dao;    public ADao(Context context){        try {            dao = MySqliteHelper.getInstance(context).getDao(A.class);        } catch (SQLException e) {            e.printStackTrace();        }    }}

Similar to BDao and CDao.
Run the program and perform the Dao operation. In this case, create the database test. db and then execute onCreate () to create the table.

import android.app.Application;import android.test.ApplicationTestCase;import android.test.suitebuilder.annotation.MediumTest;import com.helen.andbase.demolist.db.A;import com.helen.andbase.demolist.db.ADao;public class ApplicationTest extends ApplicationTestCase {    public ApplicationTest() {        super(Application.class);    }    @MediumTest    public void testDao(){        ADao aDao = new ADao(getContext());        A a = new A();        a.name=a;        aDao.add(a);        BDao bDao = new BDao(getContext());        B b = new B();        b.name=a;        b.age =18;        bDao.add(b);    }}

Copy it out and view the database. Use SQLiteExpertPers to view <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4NCjxwPjxpbWcgYWx0PQ = "here write picture description" src = "http://www.bkjia.com/uploads/allimg/150731/0403192038-1.jpg" title = "\"/>

If the table has been created. Then, we upgrade the database and change the database version to 2. Table A adds the field age, table B deletes the field age, and Table C remains unchanged.

@DatabaseTable(tableName = tb_a)public class A {    @DatabaseField(generatedId = true)    public int id;    @DatabaseField    public String name;    @DatabaseField    public String age;}

@DatabaseTable(tableName = tb_b)public class B {    @DatabaseField(generatedId = true)    public int id;    @DatabaseField    public String name;}

@DatabaseTable(tableName = tb_c)public class C {    @DatabaseField(generatedId = true)    public int id;    @DatabaseField    public String name;}

The solution to simple brute force attacks is:

@ Override public void onUpgrade (SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {if (oldVersion <2) {// it does not explain why try {TableUtils is determined so far. dropTable (connectionSource,. class, true); TableUtils. dropTable (connectionSource, B. class, true);} catch (SQLException e) {e. printStackTrace () ;}} onCreate (db, connectionSource );}

Delete the old table and create a new table. This is the most simple and violent, but we have mentioned that this is not the expected result.

Change the code,

@ Override public void onUpgrade (SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {if (oldVersion <2) {// it does not explain why DatabaseUtil should be determined in this way. upgradeTable (db, connectionSource,. class, DatabaseUtil. OPERATION_TYPE.ADD);} onCreate (db, connectionSource );}

The main code is the encapsulated DatabaseUtil. class.

Import android. database. cursor; import android. database. sqlite. SQLiteDatabase; import com. j256.ormlite. misc. javaxPersistence; import com. j256.ormlite. support. connectionSource; import com. j256.ormlite. table. databaseTable; import com. j256.ormlite. table. tableUtils; import java. util. arrays; public class DatabaseUtil {public static final String TAG = DatabaseUtil. java;/** database table operation type */public enum OPERATION_TYPE {/** ADD a field in the table */ADD, /** DELETE field in table */DELETE}/*** upgrade table, add field * @ param db * @ param clazz */public static
  
   
Void upgradeTable (SQLiteDatabase db, ConnectionSource cs, Class
   
    
Clazz, OPERATION_TYPE type) {String tableName = extractTableName (clazz); db. beginTransaction (); try {// Rename table String tempTableName = tableName + _ temp; String SQL = alter table + tableName + rename to + tempTableName; db.exe cSQL (SQL ); // Create table try {SQL = TableUtils. getCreateTableStatements (cs, clazz ). get (0); db.exe cSQL (SQL);} catch (Exception e) {e. printStackTrace (); TableUtils. createTable (cs, clazz);} // Load data String columns; if (type = OPERATION_TYPE.ADD) {columns = Arrays. toString (getColumnNames (db, tempTableName )). replace ([,). replace (],);} else if (type = OPERATION_TYPE.DELETE) {columns = Arrays. toString (getColumnNames (db, tableName )). replace ([,). replace (],);} else {throw new IllegalArgumentException (OPERATION_TYPE error);} SQL = INSERT INTO + tableName + (+ columns +) + SELECT + columns + FROM + tempTableName; db.exe cSQL (SQL); // Drop temp table SQL = drop table if exists + tempTableName; db.exe cSQL (SQL); db. setTransactionSuccessful ();} catch (Exception e) {e. printStackTrace ();} finally {db. endTransaction () ;}}/*** obtain the table name (ormlite DatabaseTableConfig. java) * @ param clazz * @ param
    
     
* @ Return */private static
     
      
String extractTableName (Class
      
        Clazz) {DatabaseTable databaseTable = clazz. getAnnotation (DatabaseTable. class); String name; if (databaseTable! = Null & databaseTable. tableName ()! = Null & databaseTable. tableName (). length ()> 0) {name = databaseTable. tableName ();} else {/** NOTE: to remove javax. persistence usage, comment the following line out */name = JavaxPersistence. getEntityName (clazz); if (name = null) {// if the name isn' t specified, it is the class name lowercased name = clazz. getSimpleName (). toLowerCase () ;}} return name;}/*** obtain the table column name * @ param db * @ param TableName * @ return */private static String [] getColumnNames (SQLiteDatabase db, String tableName) {String [] columnNames = null; Cursor cursor = null; try {cursor = db. rawQuery (PRAGMA table_info (+ tableName +), null); if (cursor! = Null) {int columnIndex = cursor. getColumnIndex (name); if (columnIndex =-1) {return null;} int index = 0; columnNames = new String [cursor. getCount ()]; for (cursor. moveToFirst ();! Cursor. isAfterLast (); cursor. moveToNext () {columnNames [index] = cursor. getString (columnIndex); index ++ ;}} catch (Exception e) {e. printStackTrace ();} finally {if (cursor! = Null) {cursor. close () ;}return columnNames ;}}
      
     
    
   
  

The upgradeTable method uses database transactions and ensures that all SQL statements can be executed based on the atomic properties of transactions. The main idea is: first change the original table name rename table (temporary table), then create a new table create table, and then migrate the data in the old table to the new table, finally, drop table to delete the temporary table.

When adding and deleting fields to a table, the main difference during data migration is that the field sources are different. For example, if the age field is added to table A, the columns variable is obtained based on the old table. This is A constructed SQL statement.

sql = INSERT INTO tb_a (id,name) SELECT id,name FROM tb_a_temp;

Table B deletes the age field. The columns variable is obtained based on the new table. The SQL statement constructed by Table B is

sql = INSERT INTO tb_b (id) SELECT id FROM tb_b_temp;

Run ApplicationTest-> testDao again

@MediumTest    public void testDao(){        ADao aDao = new ADao(getContext());        A a = new A();        a.name=a;        a.age = 20;        aDao.add(a);        BDao bDao = new BDao(getContext());        B b = new B();        b.name=b;        bDao.add(b);    }

View the data again

We can see that the historical data of tables A and B still exists.

Then we will upgrade the database to version 3. In this case, we need to consider the user's various situations, from 1-> 3, from 2-> 3, however, the previous operations are not repeated for each upgrade. For example, user 1 has been upgraded from 1 to 2. This time, user 2 is upgraded from 2 to 3, old Version 1 has been used all the time. He thinks, well, the content of this version upgrade is good and he decided to upgrade it, so he upgraded it from 1 to 3 directly, therefore, the versions they have experienced are different, and the Database Upgrade Strategy will be unnecessary. We need to consider them separately.

This upgrade adds the sex field to Table C.

import com.j256.ormlite.field.DatabaseField;import com.j256.ormlite.table.DatabaseTable;@DatabaseTable(tableName = tb_c)public class C {    @DatabaseField(generatedId = true)    public int id;    @DatabaseField    public String name;    @DatabaseField    public String sex;}

Then perform logical judgment in onUpgrade

    @Override    public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {        if(oldVersion < 2){            DatabaseUtil.upgradeTable(db,connectionSource,A.class,DatabaseUtil.OPERATION_TYPE.ADD);            DatabaseUtil.upgradeTable(db,connectionSource,B.class,DatabaseUtil.OPERATION_TYPE.DELETE);        }        if(oldVersion < 3){            DatabaseUtil.upgradeTable(db,connectionSource,C.class,DatabaseUtil.OPERATION_TYPE.ADD);        }        onCreate(db,connectionSource);    }

In this way, if you upgrade from 1 to 3, both if statements are executed. if you upgrade from 2 to 3, only if (oldVersion <3) is executed. Finally, if you only want to add a new table D, you only need to write TableUtils. createTableIfNotExists (connectionSource, D. class) in onCreate ~

Summary

The data migration discussed in this article is based on the fact that the logic between the new and old tables is not strong and does not involve business scenarios. For example, the user_id field added to table A is the user ID, which is used to mark the user whose data comes from. During retrieval, user_id is used to search for conditions, since the old data is transferred to the new table, user_id is blank by default, the old data may not work at this time. Although you can set the default value, it needs to be set according to specific business scenarios, therefore, the flexibility is lost.