Android Database Upgrade Solution

Turn from:http://blog.csdn.net/leehong2005/article/details/9128501

Please consider the following situation:

In the database upgrade, different versions of the database, they define the table structure may be not the same, such as V1.0 table A has 10 columns, and in V1.1 table A has 12 colum, when the upgrade, table A added two column, at this time we should do.

General Ideas

1, the table a renamed, changed the A_temp.

2, create a new table.

3. Insert data from table a_temp into table A.

The following code lists the implementation of the Update table, Upgradetables, given the table name, updated column name, you can implement the database table updates.

[Java]View Plaincopy

2. /**
4. * Upgrade tables. In this method, the sequence is:
6. * <b>
8. * <p>[1] Rename The specified table as a temporary table.
10. * <p>[2] Create a new table which name is the specified name.
12. * <p>[3] Insert data into the new created table, data from the temporary table.
14. * <p>[4] Drop the temporary table.
16. * </b>
18. *
20. * @param db the database.
22. * @param tableName the table name.
24. * @param columns The columns range, format is "ColA, ColB, Colc, ... Coln ";
26. */
28. protected void Upgradetables (Sqlitedatabase db, String tableName, string columns)
30. {
32. Try
34. {
36. Db.begintransaction ();
40. //1, Rename table.
42. String temptablename = tableName + "_temp";
44. String sql = "ALTER TABLE" + tableName +"RENAME to" + temptablename;
46. EXECSQL (DB, SQL, null);
50. //2, Create table.
52. Oncreatetable (DB);
56. //3, Load data
58. sql = "INSERT into" + TableName +
60. " (" + columns + ")" +
62. "Select" + Columns + "from" + temptablename;
66. EXECSQL (DB, SQL, null);
70. //4, Drop the temporary table.
72. Execsql (db, "DROP TABLE IF EXISTS" + temptablename, null);
76. Db.settransactionsuccessful ();
78. }
80. catch (SQLException e)
82. {
84. E.printstacktrace ();
86. }
88. catch (Exception e)
90. {
92. E.printstacktrace ();
94. }
96. finally
98. {
100. Db.endtransaction ();
102. }
104. }

Get the column name of the database table

We can get the column name of the table through the SQL table. One thing to note here is that int columnindex = C.getcolumnindex ("name"); Here the index is obtained according to the name.

[Java]View Plaincopy

2. Protected string[] Getcolumnnames (sqlitedatabase db, String tableName)
4. {
6. string[] ColumnNames = null;
8. Cursor C = null;
12. Try
14. {
16. c = db.rawquery ("PRAGMA table_info (" + TableName + ")", null);
18. if (null! = c)
20. {
22. int columnindex = C.getcolumnindex ("name");
24. if (-1 = = columnindex)
26. {
28. return null;
30. }
34. int index = 0;
36. ColumnNames = new String[c.getcount ()];
38. For (C.movetofirst ();!c.isafterlast (); C.movetonext ())
40. {
42. Columnnames[index] = c.getstring (columnindex);
44. index++;
46. }
48. }
50. }
52. catch (Exception e)
54. {
56. E.printstacktrace ();
58. }
60. finally
62. {
64. Closecursor (c);
66. }
70. return columnnames;
72. }

The Upgradetables method should be called in the Onupgrade method.

The significance of database upgrade

In the application development process, the database upgrade is a very important component (if the database is used), Because the program may have v1.0,v2.0, when the user installs the new version of the program, must ensure that the user data can not be lost, for the database design, if changes occur (such as adding a table, table fields increase or decrease, etc.), then we must think good database update policy.

1, defining the database version

The version of the database is an integer value that is passed to the version of the database when the Sqliteopenhelper is created, and if the incoming database version number is larger than the version number stored in the database file, then the Sqliteopenhelper#onupgrade () method is called , our upgrade should be done in this method.

2, how to write upgrade logic

If we have developed a program that has been released in two versions: v1.0,v1.2, we are developing V1.3. The database version number for each edition is 18,19,20.

In this case, how should we implement the upgrade?

The user's choices are:

1) V1.3 DB, V1.0, 20

2) 20 V1.1, V1.3 DB

3, note

Each version of the database represents the database must be defined, such as V18 database, it may have only two tables TableA and TableB, if V19 to add a table TableC, if V20 to modify TableC, then each version of the database structure is as follows:

V18---> TableA, TableB

V19---> TableA, TableB, TableC

V20---> TableA, TableB, TableC (change)

The Onupgrade () method is implemented as follows:

[Java]View Plaincopy

2. //Pattern for upgrade blocks:
4. //
6. if (upgradeversion = = [the database_version you set]-1) {
8. //        .. Your upgrade logic.
10. UpgradeVersion = [the database_version you set]
12. //    }
18. Public void Onupgrade (sqlitedatabase db, int oldversion, int newversion)
20. {
22. int upgradeversion = oldversion;
26. if (= = = upgradeversion) {
28. //Create table C
30. String sql = "CREATE TABLE ...";
32. Db.execsql (SQL);
34. UpgradeVersion = 19;
36. }
40. if (= = upgradeversion) {
42. //Modify table C
44. UpgradeVersion = 20;
46. }
50. if (upgradeversion! = newversion) {
52. //Drop Tables
54. Db.execsql ("DROP TABLE IF EXISTS" + tableName);
56. //Create Tables
58. OnCreate (DB);
60. }
62. }

As you can see from the code above, in the Onupgrade () method, we process the upgrade of the database version from 20 to 20, whether the user is from 20, the database of the final program can be upgraded to the corresponding database structure of V20.

4, how to ensure that data is not lost

This is an important part, assuming that you want to update the TableC table, we recommend the following:

1) Rename the TableC to Tablec_temp

The SQL statement can be written like this: ALERT TABLE TableC RENAME to Tablec_temp;

2) Create a new TableC table

3) inserting data from the tablec_temp into the TableC table

SQL statements can be written like this: INSERT into TableC (Col1, Col2, Col3) SELECT (Col1, Col2, Col3) from Tablec_temp;

After these three steps, the TableC has completed the update, and the data in the original table has been preserved.

Attention:

In the Onupgrade () method, when you delete a table, pay attention to using transactions so that the modifications can immediately react to the database file.

SQL statements

Since Android uses open source SQLite3 as its database, we must be aware of the key words, functions, and so on, which are supported by SQLite3 when developing database modules.

Some reference links are listed below:

SQLite3 Official document:http://sqlite.org/

W3cschool website:http://www.w3school.com.cn/sql/index.asp/

The SQL statement can directly affect the operation of the database. I've encountered SQL statements that affect query performance, update 3,000 records, and 30 move around, but performance increases to 3-4 seconds after the fields of the Where condition are indexed.

Reference: http://androidll.iteye.com/blog/1570943

Android Database Upgrade Solution