"Go" Android version upgrade simultaneous SQLite database upgrade and previous data retention

As an Android app, you will inevitably deal with SQLite. With the continuous upgrading of the application, the original database structure may no longer adapt to the new features, this time, you need to upgrade the structure of the SQLite database. SQLite provides the ALTER TABLE command, which allows the user to rename or add new fields to an existing table, but cannot remove fields from the table.
And you can only add fields at the end of the table, for example, add two fields to subscription:
1ALTERTABLESubscriptionADDCOLUMNActivation BLOB;
2ALTER TABLESubscriptionADDCOLUMNKeyBLOB;


In addition, if you are experiencing complex modifications, such as modifying the data at the same time, you can take the following statement in a transaction to implement the requirements for modifying the table.
1. Change the table name to a temporary table
ALTERTABLESubscription RENAME to__temp__subscription;

2. Create a new table
CREATETABLESubscription (OrderIdVARCHAR( +)PRIMARYKEY, UserNameVARCHAR( +) notNULL, ProductIdVARCHAR( -) notNULL);
　　
3. Import data
INSERT intoSubscriptionSELECTOrderId, "", ProductId from__temp__subscription;
Or
INSERT intoSubscription ()SELECTOrderId, "", ProductId from__temp__subscription;
　　 * Note that the double quote "" is used to supplement the original data that does not exist .
　　
4. Delete temporary tables
DROPTABLE__temp__subscription;

With the above four steps, you can complete the migration of the old database structure to the new database structure and also ensure that the data will not be lost for the upgrade.
Of course, if you encounter a decrease in the field, you can also create a temporary table by the way you implement it. Android version upgrade simultaneous SQLite database upgrade and previous data retention

When an Android application needs to be upgraded, if the previous database table structure has changed or a new table has been added, the database needs to be upgraded and the original database data retained.

How does the program know that the database needs to be upgraded?

The constructor for the Sqliteopenhelper class has a parameter of int version, which means the database version number. For example, in software version 1.0, when we use Sqliteopenhelper to access the database, this parameter is 1, then the database version number 1 will be written in our database.

By the 1.1 version, our database needs to change, then our 1.1 version of the program will use an integer greater than 1 to construct the Sqliteopenhelper class, to access the new database, such as 2.

When our 1.1 new program reads the 1.0 version of the old database, we find that the database version stored in the old database is 1, and when our new program accesses it with a version number of 2, the system knows that the database needs to be upgraded.

When will the database upgrade be triggered? How do I upgrade?

When the system constructs an object of the Sqliteopenhelper class, if the version number is found to be different, the Onupgrade function is automatically called, allowing you to upgrade the database here. Based on the above scenario, add fields to the corresponding tables of the old database in this function and add the default values to each record.

Both the new version number and the old version number are passed in as parameters to the Onupgrade function, making it easy for developers to know which version the database should be upgraded from.

When the upgrade is complete, the database automatically stores the latest version number for the current database version number.

Here's an example of how to write a process:

If my previous version of the database table structure has not changed, but two new tables, and a table before the default has 4 data, now the new version defaults to 11 data, then what to do, assuming that table A has changed, and new Table B, C

1. First we need to rename the original database table

public static final String temp_sql_create_table_subscribe = "Alter TABLE"            + A + "rename to Temp_a";

The original table structure is:

private static final String Sql_create_table_subscribe = "CREATE TABLE  if not exists"            + A + "(ID integer Primary Key Autoincrement,code text not null,name text,username text) ";

2. Then copy the data from the backup table Temp_a to the newly created database table A, which has no structural changes in the table A.

Values ("' | | | code| | "," | | name| | ', ' cnki ', ' | | tablename| | ') '  As Insertsql from Temp_a ";

3. The data in the staging table is now all copied to table A. But my previous table A has four default data, the user may have deleted, may have added new data, I do not care what the user does, I delete the 4, and then add it again, so that the previous four data is still in the new data table.

This is my previous four data, I find out first:

public static final string[] Arrwhereact = {            "Where code = ' K174 ' and tablename = ' Phonepaper '",            "Where code = ' GMRB  ' and tablename = ' Newspaper ' ",            " Where code = ' XJSJ ' and tablename = ' magazine ' ",            " Where code = ' JTKL ' and TableName = ' magazine '};

4. Deleting a backup table

public static final String Delete_temp_subscribe = "DELETE from temp_a";    public static final String drop_temp_subscribe = "DROP table if exists temp_a";

5. Then change the database version number to the previous high version number, execute the above statement in the Onupgrade method, as follows:

@Override

public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {

for (int j = oldversion; J <= NewVersion; j + +) {

Switch (j) {

Case 2:

Create a temporary table

Db.execsql (Temp_sql_create_table_subscribe);

Execute the OnCreate method, where the table initialization operation is performed, such as creating a new table

OnCreate (DB);

Delete the 4 default data from the previous table

for (int i = 0; i < arrwhereact.length; i++) {

Db.execsql (Delete_temp_subscribe + arrwhereact[i]);

}

Put data from a temporary table into table a

cursor cursor = db.rawquery (insert_subscribe, NULL);

if (Cursor.movetofirst ()) {

do {

Db.execsql (cursor.getstring (cursor

. Getcolumnindex ("Insertsql")));

} while (Cursor.movetonext ());

}

Cursor.close ();

Remove the temporary table

Db.execsql (Drop_temp_subscribe);

Break

Default

Break

}

}

}

Why write A For loop in the method, mainly considering the Kua version upgrade, such as some users have not upgraded version, the database version number has been 1, and the latest version of the client is actually the database version is 4, then I may have made a lot of changes in the database, through this for loop, You can iterate the upgrade without errors.