Android Database Upgrade Solution

Consider the following:

During Database Upgrade, the table structures defined by different versions of databases may be completely different. For example, table A of V1.0 has 10 columns, table A in Table V1.1 has 12 Colum columns. During the upgrade, Table A adds two columns. What should we do now.

General idea

1. rename table a and change it to a_temp.

2. Create a new table.

3. Insert the_temp data in table.

The following code lists the implementation of update tables, including upgradetables, given the table name, and updated column name. This allows you to update database tables.

/** * Upgrade tables. In this method, the sequence is: * <b> * <p>[1] Rename the specified table as a temporary table. * <p>[2] Create a new table which name is the specified name. * <p>[3] Insert data into the new created table, data from the temporary table. * <p>[4] Drop the temporary table. * </b> * * @param db The database. * @param tableName The table name. * @param columns The columns range, format is "ColA, ColB, ColC, ... ColN"; */protected void upgradeTables(SQLiteDatabase db, String tableName, String columns){    try    {        db.beginTransaction();        // 1, Rename table.        String tempTableName = tableName + "_temp";        String sql = "ALTER TABLE " + tableName +" RENAME TO " + tempTableName;        execSQL(db, sql, null);        // 2, Create table.        onCreateTable(db);        // 3, Load data        sql =   "INSERT INTO " + tableName +                " (" + columns + ") " +                " SELECT " + columns + " FROM " + tempTableName;        execSQL(db, sql, null);        // 4, Drop the temporary table.        execSQL(db, "DROP TABLE IF EXISTS " + tempTableName, null);        db.setTransactionSuccessful();    }    catch (SQLException e)    {        e.printStackTrace();    }    catch (Exception e)    {        e.printStackTrace();    }    finally    {        db.endTransaction();    }}

Obtain the column name of the database table.

You can use an SQL table to obtain the table column name. Note that int columnindex = C. getcolumnindex ("name"); Retrieve the index according to the name.

protected String[] getColumnNames(SQLiteDatabase db, String tableName){    String[] columnNames = null;    Cursor c = null;    try    {        c = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);        if (null != c)        {            int columnIndex = c.getColumnIndex("name");            if (-1 == columnIndex)            {                return null;            }            int index = 0;            columnNames = new String[c.getCount()];            for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext())            {                columnNames[index] = c.getString(columnIndex);                index++;            }        }    }    catch (Exception e)    {        e.printStackTrace();    }    finally    {        closeCursor(c);    }    return columnNames;}

The upgradetables method should be called in the onupgrade method.

Significance of Database Upgrade

During application development, Database Upgrade is an important component (if a database is used), because the program may have V1.0, V2.0, after you install a new version of the program, you must ensure that user data cannot be lost. For database design, if there is a change (such as adding one more table and adding or decreasing table fields ), then we must think about the database update policy.

1. Define the database version

The database version is an integer value. When creating sqliteopenhelper, the database version will be passed in. If the input database version number is larger than the version number stored in the database file, the sqliteopenhelper # onupgrade () method will be called, and our upgrade should be completed in this method.

2. How to Write the upgrade Logic

Assume that the program we developed has released two versions: V1.0 and v1.2. We are developing v1.3. The database versions of each version are, 19, and 20.

In this case, how should we upgrade it?

User selection:

1) V1.0-> v1.3 dB 18-> 20

2) V1.1-> v1.3 dB 19-> 20

3. Note

Each database version of the database must be defined. For example, for a V18 database, it may only have two tables tablea and tableb. If you want to add a table tablec in V19, if V20 needs to modify tablec, the database structure of each version is as follows:

V18 ---> tablea, tableb

V19 ---> tablea, tableb, tablec

V20 ---> tablea, tableb, tablec (change)

The onupgrade () method is implemented as follows:

        // Pattern for upgrade blocks:////    if (upgradeVersion == [the DATABASE_VERSION you set] - 1){//        .. your upgrade logic..//        upgradeVersion = [the DATABASE_VERSION you set]//    }public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){int upgradeVersion  = oldVersion;if (18 == upgradeVersion) {// Create table CString sql = "CREATE TABLE ...";db.execSQL(sql);upgradeVersion = 19;}if (20 == upgradeVersion) {// Modify table CupgradeVersion = 20;}if (upgradeVersion != newVersion) {// Drop tablesdb.execSQL("DROP TABLE IF EXISTS " + tableName);// Create tablesonCreate(db);}}

From the code above, we can see that the onupgrade () method processes the upgrade process of the database version from 18-> 20, or from 19-> 20, the database of the program can be upgraded to the database structure corresponding to V20.
4. How to ensure data is not lost

This is an important part. If you want to update the tablec table, we recommend that you:

1) Rename tablec to tablec_temp

The SQL statement can be written as follows: Alert table tablec Rename to tablec_temp;

2) create a new tablec table

3) insert data from tablec_temp to the tablec table

The SQL statement can be written as follows: insert into tablec (col1, col2, col3) Select (col1, col2, col3) from tablec_temp;

After these three steps, tablec completes the update and retains the data in the original table.

Note:

When deleting a table in the onupgrade () method, pay attention to transaction processing, so that the modification can be immediately reflected in the database file.

SQL statement

Because Android uses open-source sqlite3 as its database, when developing database modules, we must pay attention to the keywords and functions supported by sqlite3, rather than all keywords, SQLite is supported.

Some reference links are listed below:

Sqlite3 official documentation: http://sqlite.org/

W3cschool Website: http://www.w3school.com.cn/ SQL /index.asp/

SQL statement writing can directly affect database operations. I have encountered the impact of SQL statements on query performance. I updated 3000 records and moved around 30 times. However, after adding an index to the where condition field, the performance was improved to 3 ~ 4 seconds.