Management implementation of SQLite database version upgrade

We know the method of construction in Sqliteopenhelper:

Super (context context, String name, Sqlitedatabase.cursorfactory factory, int version)

The last parameter in the list represents the version number of the database. The method is called when the new version number is greater than the current versions:

Onupgrade (sqlitedatabase db, int oldversion, int newversion)

So our focus is to implement the management of SQLite database version upgrade in this method

When we started the project, the first version of Sqliteopenhelper was written like this:

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 Databaseopenhelp    ER 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 (    ), Phone VARCHAR (12)) "; Public Databaseopenhelper (Context context,string name,cursorfactory Factory,int version) {Super (context, name, factory , version);} Note://The Databaseopenhelper is written in a single case.//otherwise, when Openhelper.getwritabledatabase () is called frequently in a for loop, the database does not perform a shutdown 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) {db.execsql (Create_person);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {}}

After a few days, according to the project needs, the need to add a student table, so Databaseopenhelper appeared the second 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; public static final String create_person= ' CREATE table person (PersonID integer PRIMARY key autoincrement,name varchar (        ), Phone VARCHAR (12)) "; public static final String create_student= "CREATE table STUDENT (StudentID integer primary key autoincrement,name Varch    AR (+), phone VARCHAR (12)) "; Public Databaseopenhelper (Context context,string name,cursorfactory Factory,int version) {Super (context, name, factory , version);} Note://The Databaseopenhelper is written in a single case.//otherwise, when Openhelper.getwritabledatabase () is called frequently in a for loop, the database does not perform a shutdown operation static Synchronized Databaseopenhelper getdbinstance (context context) {if (MDatabaseopenhelper = = null) {//change 1mDataBaseOpenHelper = new Databaseopenhelper (context,database_name,null,2);} return mdatabaseopenhelper;} @Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (Create_person);//Change 2db.execsql (create_student);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//Change 3switch (oldversion) {case 1:db.ex Ecsql (create_student);d Efault:}}}

There are three places in version two that are modified by version one:

The 1 version number becomes 2.

2 Code Db.execsql (create_student) added in the OnCreate () method; STUDENT Table created

Because some users do not have the first version of the app, directly from the market to download the second version of the app. So of course will execute OnCreate () and will not execute Onupgrade ()

3 was processed in Onupgrade (): When oldversion is 1 o'clock call Db.execsql (create_student); Create STUDENT table

Because some users have the first version of the app on the phone, so when the app upgrade to the second version will be executed Onupgrade (), will not execute OnCreate ()

This processing allows the student table to be generated when using the second Edition app in different situations

Another one months, according to the project changes, need to add a field to the person table Genderid, so Databaseopenhelper appeared 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 (), phone varchar (), 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 Varch    AR (+), phone VARCHAR (12)) "; Public Databaseopenhelper (Context context,string name,cursorfactory Factory,int version) {Super (context, name, factory , version);} Note://The Databaseopenhelper is written in a single case.//Otherwise when open is called frequently in a for loopHelper.getwritabledatabase () when//will error, prompting the database does not perform shutdown operation static synchronized Databaseopenhelper getdbinstance (Context Context) {if (Mdatabaseopenhelper = = null) {//change 2mDataBaseOpenHelper = new Databaseopenhelper (Context,database_name, null,3);} return mdatabaseopenhelper;} @Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (Create_person);d b.execsql (create_student);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {switch (oldversion) {case 1:db.execsql (create_student);//Change 3case 2:db.execsql (Alter_person);d Efault:}}}

There are three places in version three where the version two is modified:

1 changed the Create_person statement, adding a field to the modified statement Genderid

Similar to the previous description, some users download the third version directly when they install the app for the first time

2 Modified version number is 3

Should the user upgrade from the first version or the second version to the third version (see analysis below)

3 in Onupgrade () method): When Oldversion is 2 o'clock call Db.execsql (Alter_person), modify Person table, add Genderid field

It should be the case that the user upgraded from the second version to the third version (see analysis below)

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

This is to ensure that the upgrade of the cross-version will be performed every time the database is upgraded.

For example, upgrading from the second edition to the third version, Case 2 will be executed.

For example, from the first version of the direct upgrade to the third version, then case 1 will definitely be called, because there is no break, it will be through the switch statement and the Case 2 statement continue to upgrade, so that all versions of the data will be implemented in the upgrade.

Management implementation of SQLite database version upgrade