Android Database Upgrade Complete Solution

The significance of database upgrade

When we develop Android applications, we inevitably use the database. The structure of the database is fixed in the first edition, after the release of feature updates, or add business logic, the original database structure may not be applicable. If the structure of the database is different from the previous version, the new version of the application reading the old database will certainly be problematic. There are only two ways to solve this problem:

1. Let the user uninstall the old version and install the new program;

2. The software updates the database structure itself.

The first approach is obviously not operable, and once the user uninstalls the software, the data is lost, which is not tolerated. Therefore, as a developer, you must properly handle database upgrade issues.

Of course, some students will say, this problem is meaningless. We design the software at the time of the database designed to complete a little better, at the beginning of the thoughtful, and then no longer do not care about the upgrade thing. This method is theoretically feasible, but it is very difficult to actually operate, unless you are developing custom software (for example, some products that are combined with hardware that are no longer updated or rarely updated after the hardware is released, so the software does not change much). For market-oriented applications, it is possible at the beginning of the project will not know what will be added later. In this way, we still have to face this problem after all.

Preserving the upgrade of data

Now with an imaginary program database to talk about how to keep the original database for the upgrade. For the sake of intuition, the database files are exported after each software version installation, and the process is withheld using SQLite studio to display the table structure and content. And for the sake of brevity of the code, some exception handling is omitted. Let's assume that there are three versions of the database:

V1:t_user (username, password);

V2:t_user (username, password), t_region (region, code);

V3:t_user (username, password), t_region (region, code, country);

As you can see, the first upgrade adds a table, and the second upgrade modifies the table definition.

Basics of creating and upgrading data tables

We basically customize a class to inherit from Sqliteopenhelper before we use the database. One of the constructor forms of the class is this (the other one Databaseerrorhandler):

Public Sqliteopenhelper (Context context, String name, Cursorfactory factory, int version) {thi    S (context, name, factory, version, NULL); }

The version parameter inside this constructor is the number we set. This version that is passed when the database is first used is logged by the system and the Sqliteopenhelper#oncreate () method is called to make the table operation. If the subsequent incoming version is higher than this, the Sqliteopenhelper#onupgrade () method is called to upgrade.

Add a table

Obviously, the cost of adding a new table (or a table without a foreign key) is minimal, just write the table operation in Onupgrade () and insert the initial data.

public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {if (oldversion==1) {db.execsql ("CREATE TABLE T_r Egion (_id Integer PRIMARY key "+" AutoIncrement, region varchar, code varchar) ");//insert data ...}}

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/44/FA/wKioL1PjS46A0YztAAEsNiHN2ns849.jpg "title=" Version 1 database structure "alt=" wkiol1pjs46a0yztaaesnihn2ns849.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/44/F5/wKioL1PjQQvCoE7JAAEoZeplm10837.jpg "title=" Version 2 database Structure "alt=" wkiol1pjqqvcoe7jaaeozeplm10837.jpg "/>

As you can see from the diagram above, the new version of the database already has the T_region table.

modifying table definitions

The SQLite number Library has very limited support for ALTER TABLE commands, only adding columns at the end of the table, cannot modify the column definition, and cannot delete existing columns. So what if you want to modify the table? We can use the temporary table approach. Specifically, there are four steps:

2. Rename an existing table to a temporary table;

4. Create a new table;

6. Import data from the staging table into a new table (Note that the modified column is processed);

8. Deletes a temporary table.

Take the example of V2 upgrade to V3 as an example:

        public void  Onupgrade (sqlitedatabase db, int oldversion, int newversion)  {if  ( oldversion==2) {db.execsql ("alter table t_region rename to t_region_temp");d B.execsql ( "Create table t_region (_id integer primary key"      +  " autoincrement, region varchar, code varchar,  "     + " Country varchar);d b.execsql ("Insert into t_region (_id, region, code, country)   "     + " select _id, region, code, \ "China\"  from  t_region_temp ");d b.execsql (" Drop table t_region_temp ");}} 

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/44/F7/wKioL1PjRYui_CqZAAExr2IApJw742.jpg "title=" Version 2 Data " alt= "Wkiol1pjryui_cqzaaexr2iapjw742.jpg"/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/44/F7/wKioL1PjRYvzqrd-AAElIqNNLCI543.jpg "title=" Version 3 Data " alt= "Wkiol1pjryvzqrd-aaeliqnnlci543.jpg"/>

Need to note:

• rename table SQL format for ALTER TABLE <oldname> RENAME to <newname>

• The values keyword cannot appear in the INSERT INTO SELECT statement that imports new data ;

• > Remember to delete the temp table

Upgrade across versions

Having handled a single version of the upgrade, there is a more difficult question: If the application publishes multiple versions, resulting in more than three database versions, how can you ensure that all users will be able to use the database after upgrading the application? There are two ways of doing this:

Method One: Determine the difference between adjacent versions , starting from version 1 to iterate over the update, first execute V1 to v2, then v2 to v3 ...

Method Two: For each version to determine the difference with the current database, for each case to write a special upgrade code.

The advantage of way one is that each time you update the database only need to add a section from the last version of the Onupgrade method to upgrade to the new version of the code, easy to understand and maintain, the disadvantage is that when the version becomes more, multiple iterations of the upgrade may take a lot of time, increase user wait;

The advantage of the two is that each version of the user can be in the least amount of time to upgrade to the latest database without having to do useless data multiple dumps, the disadvantage is to force the developer to remember the full structure of all versions of the database, and each upgrade Onupgrade method must be all rewritten.

The above simple analysis of the advantages and disadvantages of the two schemes, they can be said to spend time is just the opposite, as to how to choose, may also need to combine the specific situation analysis.

This article is from the "Flying Cat" blog, make sure to keep this source http://flyingcat2013.blog.51cto.com/7061638/1537074