An overview of the

By database version I mean user_version, the user version of the database for our application. For example, the following situation:

In April 2013, we first released our application with database version 1. In May 2013, we released our application for the second time with database version 2. Due to business needs, we changed the table structure of a table in the database.Copy the code

This is where the dilemma arises:

Some users have downloaded version 1 of April, and have already used it. A lot of data is stored in the database. At this time, he wants to install the new version 2. How do you keep your data from getting lost?

Some users installed the May version directly, and then these users directly used the new table structure format.

There may be version 3,4, N in the future. How to ensure that the database in the user’s mobile phone can be upgraded with “data loss”?

implementation

The onUpgrade method in SQLiteOpenHelper can help us implement this, so how does it work? How do we use him? Here’s how to use it.

At first

When we set up the database in April, we used the following approach

public class MyDbHelper1 extends SQLiteOpenHelper{
    public final static int DB_VERSION = 1;
    public final static String DB_NAME = "mydb.db";
    public final String TABLE_NAME = "tbl_data";
    public final String COL_UID = "uid";
    public final String COL_ITSVALUE = "itsvalue";
    public final String COL_CREATEDDATE = "createddate";
    
    public MyDbHelper1(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "create table ["+TABLE_NAME+"] ( [uid] int identity primary key,[itsvalue] nvarchar(200),createddate TIMESTAMP default (datetime('now', 'localtime')) )";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        
    }
}
Copy the code

So we see here, in our onCreate method, we’re going to create a table that has three fields.

So if we look at the database version, it’s 1

upgrade

[time flies]…

So in May, due to business needs, we wanted to add new fields to the table. We write the code like this:

public class MyDbHelper2 extends SQLiteOpenHelper{ public final static int DB_VERSION = 2; public final static String DB_NAME = "mydb.db"; public final String TABLE_NAME = "tbl_data"; public final String COL_UID = "uid"; public final String COL_ITSVALUE = "itsvalue"; public final String COL_CREATEDDATE = "createddate"; public final String COL_DESC = "desc"; public MyDbHelper2(Context context) { super(context, DB_NAME, null, DB_VERSION); } @override public void onCreate(SQLiteDatabase db) {// Create a new database. There is already a new column desc. String sql = "create table ["+TABLE_NAME+"] ( [uid] int identity primary key,[itsvalue] nvarchar(200),createddate TIMESTAMP default (datetime('now', 'localtime')),[desc] nvarchar(300) )"; db.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, Int newVersion) {if(oldVersion == 1 && newVersion == 2){// From version 1 to version 2, Alter TABLE ["+TABLE_NAME+"] add [desc] nvarchar(300); alter table ["+TABLE_NAME+"] add [desc] nvarchar(300); db.execSQL(sql); }}Copy the code

Now, in the onCreate method, this is the new way to create a table. The onUpgrade method detects this change. If it is from version 1 to version 2, we execute an “Add column SQL statement”. That is, the SQL used to upgrade the database is executed when it is detected that the database needs to be upgraded.

Through the above way, we have completed a database upgrade operation. Android determines the version number of the database and automatically calls the onUpgrade method.

How to version database files

We use SQLite Expert software (running under Windows). You can see that the database file has a property user_Version. This is the “PRAGMA “parameter of the SQLite database. PRAGMA + SQL execution has metadata information that can be obtained from some database files.

Such as:

PRAGMA schema_version; PRAGMA schema_version = integer ; PRAGMA user_version; PRAGMA user_version = integer ; The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. The schema-version and the user-version are big-endian 32-bit signed integers stored in the database header at offsets 40 and 60, respectively. The schema-version is usually only manipulated internally by SQLite. It is incremented by SQLite whenever the database schema is modified (by creating or dropping a table or index). The schema version is used by SQLite each time a query is executed to ensure that the internal cache of the schema used when compiling the SQL query matches the schema of the database against which the compiled query is actually executed. Subverting this mechanism by using "PRAGMA  schema_version" to modify the schema-version is potentially dangerous and may lead to program crashes or database corruption. Use with caution! The user-version is not used internally by SQLite. It may be used by applications for any purpose.Copy the code

In the database, we can execute SQL statements to query it:

PRAGMA main.user_version 
Copy the code

Or to set its value, execute the following statement

PRAGMA main.user_version = 1
Copy the code

Refer to sqLite’s official description for more information.

reference

www.sqlite.org/pragma.html

Dev. 10086. Cn/CMDN/BBS/th…

Blog.sina.com.cn/s/blog_6ffb…

Stackoverflow.com/questions/1…