Android SQLite database version upgrade principle, androidsqlite

Android SQLite database version upgrade principle, androidsqlite

Android uses the SQLite database to save data. What is the upgrade of the database version? Let's talk about it here.

I. Software v1.0

When v1.0 is installed, assume that v1.0 has only one account table. In this case, the onCreate that inherits SQLiteOpenHelper does not go through onUpgrade.

1. v1.0 (directly install v1.0)

Ii. Software v2.0

There are two types of software installation:

1. v1.0 --> v2.0 does not go to onCreate or onUpgrade

2. For v2.0 (directly install v2.0), use onCreate instead of onUpgrade.

V1.0 has only one account table, and the software version has been upgraded to v2.0, but a member table needs to be added to v2.0 database. What should I do? There are two cases: one is to install v1.0 and upgrade it to v2.0. At this time, it does not inherit the onCreate of SQLiteOpenHelper, but directly uses onUpgrade, in this case, the code for adding a member table in onUpgrade is required. It is useless to add a member table in onCreate, because in this case, onCreate is not used .. Another scenario is that you have never installed this software and directly installed v2.0. In this case, the onCreate that inherits SQLiteOpenHelper does not go through onUpgrade. Therefore, you need to add the member table code in onCreate. What should we do? This requires a reasonable upgrade of the database version.

III,Software v3.0

Assume that v3.0 adds another news table. There are three situations:

1. v1.0 --> v3.0 does not go through onCreate or onUpgrade

2. v3.0 does not go to onCreate or onUpgrade.

3. v3.0 (directly install v3.0) uses onCreate instead of onUpgrade

Where can I write the statement for adding tables to the database? A database version is represented by DATABASE_VERSION.

In fact, just think about it, you know that either onCreate write or onUpgrade write is compatible with the installation of the app in various situations, and you can simply add the database tables. This is clever:

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 remains unchanged)

OnUpgrade -- add -- news

This will solve the problem. The first version is in onCreate, the other versions are added in onUpgrade, And the onUpgrade is executed in onCreate. How can I determine whether to execute onUpgrade? So with the concept of database version, DATABASE_VERSION saves the current database version, as long as the current database version is larger than the installed database version, in onUpgrade, compare the previous database version (oldVersion) with the installed database version (newVersion, add the tables required by you for different database_versions (upgrade databases across versions ).

The following is a simple example:

(1) V1.0: DATABASE_VERSION = 1000 add an favorite table

Public class DBHelper extends SQLiteOpenHelper {private static final String DATABASE_NAME = "mall. db "; private static final int DATABASE_VERSION = 1000; private static DBHelper instance = null; public DBHelper (Context context) {super (context, DATABASE_NAME, null, DATABASE_VERSION );} public synchronized static DBHelper getInstance (Context context) {if (instance = null) {instance = new DBHelper (context) ;}return instance ;}@ Overridepublic void onCreate (SQLiteDatabase db) mongodb.exe cSQL (SQL. CREATE_TABLE_FAVORITE); // if it is not installed in the first version, upgrade the database directly. // do not modify the value of FIRST_DATABASE_VERSION, which is the final int FIRST_DATABASE_VERSION of the first database version = 1000; onUpgrade (db, FIRST_DATABASE_VERSION, DATABASE_VERSION) ;}@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// use for to upgrade the database across versions for (int I = oldVersion; I <newVersion; I ++) {switch (I) {default: break ;}}}}

SQL. java is a table creation 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 one deleted field to the 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_VERSION );} public synchronized static DBHelper getInstance (Context context) {if (instance = null) {instance = new DBHelper (context) ;}return instance ;}@ Overridepublic void onCreate (SQLiteDatabase db) mongodb.exe cSQL (SQL. CREATE_TABLE_FAVORITE); // if it is not installed in the first version, upgrade the database directly. // do not modify the value of FIRST_DATABASE_VERSION, which is the final int FIRST_DATABASE_VERSION of the first database version = 1000; onUpgrade (db, FIRST_DATABASE_VERSION, DATABASE_VERSION) ;}@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// use for to upgrade the database across versions for (int I = oldVersion; I <newVersion; I ++) {switch (I) {case 1000: upgradeToVersion1001 (db ); break; default: break ;}}private void upgradeToVersion1001 (SQLiteDatabase db) {// a new field String sql1 = "alter table" + SQL is added to the favorite TABLE. t_FAVORITE + "add column deleted VARCHAR" mongodb.exe cSQL (sql1 );}}

(3) V3.0: DATABASE_VERSION = 1002 Add the message and type fields to 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_VERSION );} public synchronized static DBHelper getInstance (Context context) {if (instance = null) {instance = new DBHelper (context) ;}return instance ;}@ Overridepublic void onCreate (SQLiteDatabase db) mongodb.exe cSQL (SQL. CREATE_TABLE_FAVORITE); // if it is not installed in the first version, upgrade the database directly. // do not modify the value of FIRST_DATABASE_VERSION, which is the final int FIRST_DATABASE_VERSION of the first database version = 1000; onUpgrade (db, FIRST_DATABASE_VERSION, DATABASE_VERSION) ;}@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// use for to upgrade the database across versions for (int I = oldVersion; I <newVersion; I ++) {switch (I) {case 1000: upgradeToVersion1001 (db ); break; case 1001: upgradeToVersion1002 (db); break; default: break ;}} private void upgradeToVersion1001 (SQLiteDatabase db) {// a new field String sql1 = "alter table" + SQL is added to the favorite TABLE. t_FAVORITE + "add column deleted VARCHAR" mongodb.exe cSQL (sql1);} private void upgradeToVersion1002 (SQLiteDatabase db) {// two new fields are added to the favorite table, you can add only one field to a new field. sqlite has restrictions on not adding multiple fields to a single statement: String sql1 = "alter table" + SQL. t_FAVORITE + "add column message VARCHAR"; String sql2 = "alter table" + SQL. t_FAVORITE + "add column type VARCHAR" mongodb.execsql(sql1)mongodb.exe cSQL (sql2 );}}

In this way, whether v1.0 is upgraded to v3.0, v2.0 is upgraded to 3.0, or v3.0 is directly installed, the v3.0 database structure after installation is the same, understanding is good, these problems will certainly occur when you are just working on the sqlite database, so I wrote them in detail here, but please note that the onUpgrade upgrade must be correct and tested well, otherwise, problems may occur in the database after installation.

I Original, please attach the link forwarding: http://www.cnblogs.com/liqw/p/4264925.html