Issues with upgrading or downgrading the SQLite database when the Android version is updated

SQLite is a small relational database built into Android. Sqliteopenhelper is an abstract class that is used to assist in managing database creation and version escalation issues. We can inherit this abstract class and implement some of its methods to customize the operation of the database. The following two methods must be overridden:

• public void OnCreate (Sqlitedatabase db)
• public void Onupgrade (sqlitedatabase db, int oldversion, int newversion)

In addition, the Sqliteopenhelper subclass must specify the name of the current database (name), version number (versions) when constructing the instance. The name here determines the name of the file when the database is stored, and there is no absolute association between the version number and the app's Versioncode defined in Androidmainfest.xml. That is, if the data structure of the database does not change when the app is updated, then the version number of the database does not increase.

OnCreate: The call is initiated after the user installs the app for the first time, or when the app database file is cleared. You can then complete the initial creation of the data table in this function.

Onupgrade: The call is made when the user makes an app update, overwrites the installation, and is called if the database version number in the new version is higher than the database version number in the old version. This can be done in this function to complete the old version of the database version of compatibility issues, as well as data migration issues.

There is also a general case where rewriting is not required, but the method that must be overridden when the application has a reverse demotion (such as applying a package with version number 3 that is downgraded by version number 4) Ondowngrade, if the application demotion overrides the method when overridden by the installation, crashes.

We may encounter situations where the database version is upgraded:

• Fields that need to extend a table
• Remove a redundant field from the original table
• Create a new table

The process of dealing with these problems should be done without compromising the historical data of the old database. Here we assume that the user on the phone before the installation of the database version 1 of the package, the upgrade installed is the database version number 2 of the package. At this point we are going to handle these upgrade logic in a package with a database version of 2.

The first is the implementation of the field in Onupgrade that extends a table as:

[Java]View PlainCopy

2. @Override   
4. Public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {  
6. //The old database version is 1 to add a student_name field for table pedant   
8. if (Oldversion < 2) {
10. Db.execsql ("ALTER TABLE pedant ADD COLUMN student_name text");
12. }
14. }

SQLite support for ALTER TABLE is limited, you can add a field to the end of an existing table, or change the name of the table. But if you want to do something more complicated, such as deleting a field from a table, re-create the table and complete the data migration, rather than using a convenient command like DROP COLUMN . See SQLite frequently Questions

For example, table Pedant originally has three fields A, B, C, and now want to delete the C field, then the onupgrade in the following wording:

[Java]View PlainCopy

2. @Override   
4. Public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {  
6. //The old database version is 1, delete the C field of table Pedant   
8. if (Oldversion < 2) {
10. Db.begintransaction ();
12. Try  {  
14. Db.execsql ("CREATE temporary TABLE pe_backup (A, b);"  );
16. Db.execsql ("INSERT into Pe_backup Select a, b from pedant;"  );
18. Db.execsql ("DROP TABLE pedant;"  );
20. Db.execsql ("CREATE TABLE pedant (a text, b text);"  );
22. Db.execsql ("INSERT into pedant Select a, b from Pe_backup;"  );
24. Db.execsql ("DROP TABLE pe_backup;"  );
26. Db.settransactionsuccessful ();
28. } finally {
30. Db.endtransaction ();
32. }
34. }
36. }

This completes both deleting the C field and preserving the data on the original table.

In the last case, the simplest straightforward execution of the CREATE statement will be possible.

[Java]View PlainCopy

2. @Override   
4. Public  void Onupgrade (sqlitedatabase db, int oldversion, int NewVersion) {
6. //Old database version is 1, create new table NEWTB   
8. if (Oldversion < 2) {
10. Db.execsql ("CREATE TABLE newtb (a text, b text);"  );
12. }
14. }

when the database is being upgraded, we can clearly know what we are going to do with the old tables to be compatible with the new version. But if the database is degraded, the situation is not the same, for us to develop a new version 2 o'clock, we can not clearly know the future of the new version such as version 3, 4 of the database structure of how the trend. For example, later users fall back from version 3 to the version 2 we are developing, because we developed the table structure of version 3 is not foreseen at that time, I do not know whether version 3 of the data table can be compatible to version 2 (if a version 3 is removed when a version 2 has been used in the table field, the fallback data structure may be incompatible), Then we are in the development of version 2 o'clock the most prudent way isrewrite Ondowngrade to rebuild all the tables that are used in the current version, i.e. discard all previous data when demoting.

//Because we cannot predict future versions of the table structure, the surest way to backwards-compatibility is to refactor the table that the version needs for itself once@Override Public void Ondowngrade(Sqlitedatabase DB,intOldversion,intNewVersion) {Db.execsql ("DROP TABLE IF EXISTS t1;"); Db.execsql ("DROP TABLE IF EXISTS T2;"); Db.execsql ("DROP TABLE IF EXISTS T3;"); Db.execsql ("DROP TABLE IF EXISTS T4;"); ..... onCreate (DB);//Build Table}

Issues with upgrading or downgrading the SQLite database when the Android version is updated