Android SQLite database Version Upgrade principle analysis

Android uses the SQLite database to save data, the database version of the upgrade is what is going on, here to say.

First, the Software v1.0

Install v1.0, assuming the v1.0 version has only one account table, then go to inherit Sqliteopenhelper OnCreate, do not go onupgrade.

1, v1.0 (direct installation of v1.0)

Second, Software v2.0

There are 2 types of installation software:

1, v1.0---v2.0 do not go oncreate, go onupgrade

2, v2.0 (direct installation v2.0) Go OnCreate, do not go onupgrade

v1.0 version has only one account table, software version upgrade to v2.0, but V2.0 database need to add a member table, what to do? There are 2 kinds of situations: one is to install the v1.0 upgrade to V2.0, this time will not inherit Sqliteopenhelper OnCreate, but go straight to Onupgrade, at this time in Onupgrade Add Member table code, in OnCreate added also useless, because this The situation does not go oncreate. Another situation is that users have never installed this software, directly installed v2.0, then go to inherit Sqliteopenhelper OnCreate, do not go onupgrade, so to add OnCreate table in member code. What about this? This should reasonably upgrade the database version.

Third, software v3.0

Suppose v3.0 adds another news list, here are three things:

1, v1.0---v3.0 do not go oncreate, go onupgrade

2, v2.0---v3.0 do not go oncreate, go onupgrade

3, v3.0 (direct installation v3.0) Go OnCreate, do not go onupgrade

Where does the database Add Table statement write? Database has a version number expressed in Database_version

In fact, think about it, I know it is not oncreate write is Onupgrade write, is to be compatible with all kinds of situations to install the app, can add database tables into the good. This is ingenious:

1, v1.0 database_version=1000 onCreate--add--account

2, v2.0 database_version=1001 onCreate--Add--Account (v1.0 code unchanged) Onupgrade (database_version>1000)

Onupgrade--add--member

3, v3.0 database_version=1002 onCreate--Add--Account (v1.0 code unchanged) Onupgrade (database_version>1001)

Onupgrade--Add--member (v2.0 code unchanged)

Onupgrade--Add--News

This will solve the problem, the first version of the OnCreate, the other version of the new in the Onupgrade, but also in OnCreate execution Onupgrade. Do judge whether to execute onupgrade how to judge, so with the concept of database version, database_version Save the current version of the database, as long as the current database version than the installed version of the database is large, enter the Onupgrade, The previous database version number (oldversion) is also compared to the installed database version number (newversion), and the different database_version add the tables they need (the database is upgraded across versions).

Here's a simple example:

(1), v1.0:database_version = 1000 Add a favorite table

public class DBHelper extends Sqliteopenhelper {private static final String database_name = "mall.db";      private static final int database_version = +;    private static DBHelper instance = Null;public DBHelper (context context) {Super (context, database_name, NULL, DATABASE_VE rsion);} Public synchronized static DBHelper getinstance (context context) {if (instance = = null) {instance = new DBHelper (context); }return instance;} @Overridepublic void OnCreate (Sqlitedatabase db) {Db.execsql (SQL. Create_table_favorite);//If it is not the first version installed, perform a database upgrade directly//Do not modify the value of First_database_version, which is the first database version size final int first_ Database_version = 1000;onupgrade (db, First_database_version, database_version);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//use for to implement cross-version upgrade database for (int i = Oldversi On i < newversion; i++) {switch (i) {default:break;}}}}

Where Sql.java is the build table statement

public class SQL {public    static final String t_favorite = "favorite";    public static final String create_table_favorite =            "CREATE TABLE IF not EXISTS" + T_favorite + "(" +                    "ID VARCHAR PRIMARY KEY, "+"                    title varchar, "+                    " url varchar, "+                    " createdate varchar "+                    ") ";}

(2), v2.0:database_version = 1001 Add 1 deleted fields in favorite table

public class DBHelper extends Sqliteopenhelper {private static final String database_name = "MALL.DB";    private static final int database_version = 1001; private static DBHelper instance = Null;public DBHelper (context context) {Super (context, database_name, NULL, DATABASE_VE rsion);} Public synchronized static DBHelper getinstance (context context) {if (instance = = null) {instance = new DBHelper (context); }return instance;} @Overridepublic void OnCreate (Sqlitedatabase db) {Db.execsql (SQL. Create_table_favorite);//If it is not the first version installed, perform a database upgrade directly//Do not modify the value of First_database_version, which is the first database version size final int first_ Database_version = 1000;onupgrade (db, First_database_version, database_version);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//use for to implement cross-version upgrade database for (int i = Oldversi On i < newversion; i++) {switch (i) {case 1000:upgradetoversion1001 (db); break;default:break;}}} private void upgradeToVersion1001 (Sqlitedatabase db) {//Favorite table added 1 fields string sql1 = "ALTER tablE "+sql. t_favorite+ "ADD COLUMN deleted VARCHAR";d b.execsql (SQL1);}}

(3), V3.0  : database_version = 1002 Add a message and a Type field in the favorite table

public class DBHelper extends Sqliteopenhelper {private static final String database_name = "MALL.DB";    private static final int database_version = 1002; private static DBHelper instance = Null;public DBHelper (context context) {Super (context, database_name, NULL, DATABASE_VE rsion);} Public synchronized static DBHelper getinstance (context context) {if (instance = = null) {instance = new DBHelper (context); }return instance;} @Overridepublic void OnCreate (Sqlitedatabase db) {Db.execsql (SQL. Create_table_favorite);//If it is not the first version installed, perform a database upgrade directly//Do not modify the value of First_database_version, which is the first database version size final int first_ Database_version = 1000;onupgrade (db, First_database_version, database_version);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//use for to implement cross-version upgrade database for (int i = Oldversi On i < newversion; i++) {switch (i) {case 1000:upgradetoversion1001 (db); Break;case 1001:upgradetoversion1002 (db); break;default:break;}}} private void upgradeToVersion1001 (Sqlitedatabase db) {// Favorite Table added 1 fields string sql1 = "ALTER table" +sql. t_favorite+ "ADD COLUMN deleted VARCHAR";d b.execsql (SQL1);} private void upgradeToVersion1002 (Sqlitedatabase db) {//Favorite table added 2 fields, adding a new field only one field in a field plus, SQLite has the restriction not to allow one statement plus multiple fields string sql1 = "ALTER TABLE" +sql. t_favorite+ "ADD COLUMN message VARCHAR"; String sql2 = "ALTER TABLE" +sql. t_favorite+ "ADD COLUMN type VARCHAR";d b.execsql (SQL1);d b.execsql (SQL2);}}

That's it Regardless of v1.0 upgrade to v3.0, or v2.0 upgrade to 3.0, or v3.0 directly installed, after the installation of the V3.0 database structure is the same, understanding is good ah, just do SQLite database will encounter these problems, so here in detail to write a bit, but still have to pay attention, is Onupgrad E Upgrade time must write to, test good, or after the installation of the database have problems on the trouble.

I original, forwarded please attach link: http://www.cnblogs.com/liqw/p/4264925.html

Android SQLite database Version Upgrade principle analysis