Management Implementation of SQLite database version upgrade, sqlite Database

Management Implementation of SQLite database version upgrade, sqlite Database

We know the constructor in SQLiteOpenHelper:

super(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)

The last parameter indicates the database version number. When the new version number is greater than the current version number, the method is called:

onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

Therefore, we focus on managing the version upgrade of the SQLite database in this method.

At the beginning of our project, the first version of SQLiteOpenHelper was written as follows:

Package cc. database; import android. content. context; import android. database. sqlite. SQLiteDatabase; import android. database. sqlite. SQLiteDatabase. cursorFactory; import android. database. sqlite. SQLiteOpenHelper;/*** Demo Description: * SQLite database version upgrade management implementation ** reference: * http://blog.csdn.net/guolin_blog * Thank you very much */public class DataBaseOpenHelper extends SQLiteOpenHelper {private final static String DATABASE_NAME = "test. db "; private static DataBaseOpenHelper mDataBaseOpenHelper; public static final String CREATE_PERSON =" create table person (personid integer primary key autoincrement, name varchar (20), phone VARCHAR (12 ))"; public DataBaseOpenHelper (Context context, String name, CursorFactory factory, int version) {super (context, name, factory, version);} // note: // write DataBaseOpenHelper as a singleton. // otherwise, openHelper is frequently called in a for loop. getWritableDatabase () // an error is reported, prompting that the database has not executed the close operation static synchronized DataBaseOpenHelper getDBInstance (Context context) {if (mDataBaseOpenHelper = null) {mDataBaseOpenHelper = new DataBaseOpenHelper (context, DATABASE_NAME, null, 1);} return mDataBaseOpenHelper;} @ Overridepublic void onCreate (SQLiteDatabase db) mongodb.exe cSQL (CREATE_PERSON);} @ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){}}

After a few days, you need to add a student table as required by the project, so DataBaseOpenHelper will see the second version:

Package cc. database; import android. content. context; import android. database. sqlite. SQLiteDatabase; import android. database. sqlite. SQLiteDatabase. cursorFactory; import android. database. sqlite. SQLiteOpenHelper; public class DataBaseOpenHelper extends SQLiteOpenHelper {private final static String DATABASE_NAME = "test. db "; private static DataBaseOpenHelper mDataBaseOpenHelper; public static final String CREATE_PERSON =" create table person (personid integer primary key autoincrement, name varchar (20), phone VARCHAR (12 ))"; public static final String CREATE_STUDENT = "create table student (studentid integer primary key autoincrement, name varchar (20), phone VARCHAR (12)"; public DataBaseOpenHelper (Context context, String name, cursorFactory factory, int version) {super (context, name, factory, version);} // Note: // write DataBaseOpenHelper as a singleton. // otherwise, openHelper is frequently called in a for loop. getWritableDatabase () // an error is reported, prompting that the database has not executed the close operation static synchronized DataBaseOpenHelper getDBInstance (Context context) {if (mDataBaseOpenHelper = null) {// Change 1 mDataBaseOpenHelper = new DataBaseOpenHelper (context, DATABASE_NAME, null, 2);} return mDataBaseOpenHelper;} @ Overridepublic void onCreate (SQLiteDatabase db) mongodb.exe cSQL (CREATE_PERSON ); // change 2db.exe cSQL (CREATE_STUDENT) ;}@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// change 3 switch (oldVersion) {case 1: db.exe cSQL (CREATE_STUDENT); default :}}}

Compared with version 1, version 2 has three modifications:

1 version is changed to 2

2. The Code db.exe cSQL (CREATE_STUDENT) is added to oncreate(zookeeper); the student table is created.

Because some users do not have the first version of the APP, they directly download the second version of the App from the market. So of course, onCreate () will be executed instead of onUpgrade ()

3. Processing in onUpgrade (): When oldversionis 1, db.exe cSQL (CREATE_STUDENT) is called; student table is created.

Because some users already have the first version of the APP on their mobile phones, the onUpgrade () will be executed when the App is upgraded to the second version, and the onCreate () will not be executed ()

This makes the student table generated when the second version of the APP is used in different situations.

After another month, according to the project change, a genderid field needs to be added to the person table, so DataBaseOpenHelper will appear in the Third Edition:

Package cc. database; import android. content. context; import android. database. sqlite. SQLiteDatabase; import android. database. sqlite. SQLiteDatabase. cursorFactory; import android. database. sqlite. SQLiteOpenHelper; public class DataBaseOpenHelper extends SQLiteOpenHelper {private final static String DATABASE_NAME = "test. db "; private static DataBaseOpenHelper mDataBaseOpenHelper; // Change 1 public static final String CREATE_PERSON =" create table person (personid integer primary key autoincrement, name varchar (20 ), phone VARCHAR (12), genderid integer) "; public static final String ALTER_PERSON =" alter table person add column genderid integer "; public static final String CREATE_STUDENT = "create table student (studentid integer primary key autoincrement, name varchar (20), phone VARCHAR (12)"; public DataBaseOpenHelper (Context context, String name, cursorFactory factory, int version) {super (context, name, factory, version);} // Note: // write DataBaseOpenHelper as a singleton. // otherwise, openHelper is frequently called in a for loop. getWritableDatabase () // an error is reported, prompting that the database has not executed the close operation static synchronized DataBaseOpenHelper getDBInstance (Context context) {if (mDataBaseOpenHelper = null) {// change 2 mDataBaseOpenHelper = new DataBaseOpenHelper (context, DATABASE_NAME, null, 3);} return mDataBaseOpenHelper;} @ Overridepublic void onCreate (SQLiteDatabase db) {db.execSQL(CREATE_PERSON);db.exe cSQL (CREATE_STUDENT );} @ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {switch (oldVersion) {case 1: db.exe cSQL (CREATE_STUDENT); // modify 3 case 2: db.exe cSQL (ALTER_PERSON); default :}}}

Compared with version 2, there are three modifications in version 3:

1. Changed the CREATE_PERSON statement and added the genderid field to the statement.

Similar to the preceding description, some users directly download the third edition when installing the APP for the first time.

2. Change the version number to 3.

When the user upgrades from the first or second version to the third version (see the analysis below)

3 In the onUpgrade () method): When the oldVersion is 2, db.exe cSQL (ALTER_PERSON) is called. Modify the person table and add the genderid field.

Correct the user's upgrade from version 2 to version 3 (see the analysis below)

Note: Why does the switch statement have no break in each case ???

This is to ensure that each Database Upgrade is performed during a cross-version upgrade.

For example, if you upgrade from version 2 to version 3, case 2 is executed.

For example, if you directly upgrade from the first version to the third version, case 1 will certainly be called. Because there is no break, case 2 statements will be passed through the switch statement and executed to continue the upgrade, this ensures that all versions of the data are updated.