Android Sqlite Database Upgrade

This week to develop the function of data synchronization, but the first thing to solve is the issue of SQLite database upgrade, about the database upgrade has a lot of aspects involved, perhaps you are new added functionality, so new table, perhaps you have added some fields for some tables, perhaps you are refactoring the data model and structure, regardless of how to upgrade, The original data must not be lost if the user's normal upgrade is to be satisfied. About the correct database upgrade practices on-line data is relatively small, this time to introduce a foreign Daniel to see a summary of the correct practice of database upgrade.

Version 1 of your database

Most of us use Android-provided sqliteopenhelper to create and manage databases with the following code:

 Public classDBHelperextendsSqliteopenhelper {Private Static FinalString database_name = "Mysample.db"; Private Static Final intDatabase_version = 1; Private Static FinalString database_create_sample_table = "CREATE TABLE tblsample (_id Integer primary key autoincrement, name varchar (32);";  PublicDBHelper (Context context) {Super(Context, database_name,NULL, database_version); } @Override Public voidonCreate (sqlitedatabase database) {database.execsql (database_create_sample_table); } @Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {        // do nothing for now    }}

Each time the code executes, it checks that the current version of the database exists, and if it does not, it executes the OnCreate method to create a new database, and then stores the name and version number of the database, and compares the current version and the size of the database_version if it already exists. , and then it executes the Onupgrade method.

Version 2 of your database

The question is, what is the best way to handle the upgrade, and the best way to do this is to cycle through each version of the database change, looking at the example:

Assuming that the next version wants to add an "address" field to the Tblsample table, the new creation statement should look like this:

CREATE TABLE tblsample (    _id Integer primary key autoincrement,    name varchar (+),    address varchar);

So look at what the new code looks like:

 Public classDBHelperextendsSqliteopenhelper {Private Static FinalString database_name = "Mysample.db"; Private Static Final intDatabase_version = 2; Private Static FinalString database_create_sample_table = "CREATE TABLE tblsample (_id Integer primary key autoincrement, name varchar (+), ad Dress varchar (128); ";  PublicDBHelper (Context context) {Super(Context, database_name,NULL, database_version); } @Override Public voidonCreate (sqlitedatabase database) {database.execsql (database_create_sample_table); } @Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {         for(inti = oldversion; i < newversion; i++) {            Switch(i) { Case1: Db.execsql ("ALTER TABLE tblsample ADD address varchar (128)");  Break; }        }    }}

The code logic is simple, which is a for loop plus switch...case ... statement, and then the above code can handle all the database upgrade, whether you upgrade from version 1 to version 9 or from version 4 to version 5, you can easily resolve, to be sure, it will be able to resolve from all previous versions to the current version of the latest update.

It is necessary to note that if some inexplicable users upgrade from a high version to a lower version (to be exact downgrade), such as from version 3 accidentally downgraded to version 1, in this case, if only the above code will throw an exception, causing the system to crash. The database demotion in Android will execute the Ondowngrade method, in order to prevent this situation, also need to re-prevent the program's exception.

Android Sqlite Database Upgrade