Android Database Upgrade Instance

The first part

There is a Onupgrade method in the Sqliteopenhelper class of Andoird. The help document simply says that the method is triggered when the database is upgraded. Through practice, I solved a series of questions:

1. What does "Database Upgrade" mean in the Help document?

You have developed a program that is currently in version 1.0. The program uses the database. By the 1.1 version, you added a field to a table in the database. Then the software 1.0 version of the database in the software version 1.1 will be upgraded.

2. What should I pay attention to in database upgrade?

When the 1.0 version of the software is upgraded to version 1.1, the old data cannot be lost. Then in the 1.1 version of the program will be able to detect the new version of the software is incompatible with the old database, and there is a way to upgrade the 1.0 software database to 1.1 software can use the database. In other words, add that field to the table in the database of the 1.0 software and give the field the default value.

3. 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.

4. 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.

Reference: StackOverflow A question and answer about "where database versions are stored in the database."

Part IIAs 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:
1 ALTER TABLE Subscription ADD COLUMN Activation BLOB;
2 ALTER TABLE Subscription ADD COLUMN Key BLOB;


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
ALTER TABLE Subscription RENAME to __temp__subscription;

2. Create a new table
CREATE TABLE Subscription (OrderId VARCHAR ( +) PRIMARY KEY, UserName VARCHAR ( +) Notnull, ProductId VARCHAR ( -) not NULL);
　　
3. Import data
INSERT into Subscription Select OrderId, "", ProductId from __temp__subscription;
Or
INSERT into Subscription () Select OrderId, "", ProductId from __temp__subscription;
* Note that the double quote "" is used to supplement the original data that does not exist.
　　
4. Delete temporary tables
DROP TABLE __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 Database Upgrade Instance