Lesson 39. onUpgrade. We are updating the database in SQLite


In this lesson:

– change the version and update the database structure in onUpgrade

WITH development applications may be necessary database structure changesThat it uses. In one of my past lessons, I mentioned that this is a method onUpgrade class SQLiteOpenHelper. This method is called if existing DB version is different from the one we are trying to reach connect. version we usually do indicated on call the designer super class SQLiteOpenHelper in the DBHelper constructor.

Let’s try using the onUpgrade method and see how the transition to the new DB is going. To do this, we write a small application similar to one of the past lessons – about employees and positions.

the first the DB version will only contain a table people with the employee’s name and position. But such a table will not be quite correct. If we suddenly change the name of the post, we will have to update all the relevant posts in people. So we decide to change the database and organize the data a little differently.

under second we will add a table to the version position with job title and salary. And in the table people instead of the name of the position, write the appropriate one ID with position.

Let’s create a project:

Project name: P0391_SQLiteOnUpgradeDB
Build Target: Android 2.3.3
Application name: SQLiteOnUpgradeDB
Package name: en.startandroid.develop.p0391sqliteonupgradedb
Create Activity: MainActivity

We do not use the screen again, we will output everything to the log.

open MainActivity.java and Kodyma:

package ru.startandroid.develop.p0391sqliteonupgradedb;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends Activity {

  final String LOG_TAG = "myLogs";

  final String DB_NAME = "staff"; // имя БД
  final int DB_VERSION = 1; // версия БД

  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    DBHelper dbh = new DBHelper(this);
    SQLiteDatabase db = dbh.getWritableDatabase();
    Log.d(LOG_TAG, " --- Staff db v." + db.getVersion() + " --- ");
    writeStaff(db);
    dbh.close();
  }

  // запрос данных и вывод в лог
  private void writeStaff(SQLiteDatabase db) {
    Cursor c = db.rawQuery("select * from people", null);
    logCursor(c, "Table people");
    c.close();
  }

  // вывод в лог данных из курсора
  void logCursor(Cursor c, String title) {
    if (c != null) {
      if (c.moveToFirst()) {
        Log.d(LOG_TAG, title + ". " + c.getCount() + " rows");
        StringBuilder sb = new StringBuilder();
        do {
          sb.setLength(0);
          for (String cn : c.getColumnNames()) {
            sb.append(cn + " = "
                + c.getString(c.getColumnIndex(cn)) + "; ");
          }
          Log.d(LOG_TAG, sb.toString());
        } while (c.moveToNext());
      }
    } else
      Log.d(LOG_TAG, title + ". Cursor is null");
  }

  // класс для работы с БД
  class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context) {
      super(context, DB_NAME, null, DB_VERSION);
    }

    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, " --- onCreate database --- ");

      String[] people_name = { "Иван", "Марья", "Петр", "Антон", "Даша",
          "Борис", "Костя", "Игорь" };
      String[] people_positions = { "Программер", "Бухгалтер",
          "Программер", "Программер", "Бухгалтер", "Директор",
          "Программер", "Охранник" };

      ContentValues cv = new ContentValues();

      // создаем таблицу людей
      db.execSQL("create table people ("
          + "id integer primary key autoincrement,"
          + "name text, position text);");

      // заполняем ее
      for (int i = 0; i < people_name.length; i++) {
        cv.clear();
        cv.put("name", people_name[i]);
        cv.put("position", people_positions[i]);
        db.insert("people", null, cv);
      }
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
  }
}

The code is simple. I grouped operations by output to the data log with Cursor - method logCursor. method writeStaff - selects data from the people table and invokes the method for outputting the log. In the Activity method onCreate we create an object DBHelper, we connect to the database, displayed in the log version DB, we call writeStaff and we disconnect.

IN DBHelper as usual. We call the super-class constructor in the constructor. Pay attention, DB_VERSION = 1 - we will connect to base version 1. In the method onCreate create a table and populate it.

We will save everything and launch the application. We look at the log:

--- onCreate database ---
--- Staff db v.1 ---
Table people. 8 rows
id = 1; name = Ivan; position = Programmer;
id = 2; name = Mary; position = Accountant;
id = 3; name = Peter; position = Programmer;
id = 4; name = Anton; position = Programmer;
id = 5; name = Dasha; position = Accountant;
id = 6; name = Boris; position = Director;
id = 7; name = Bone; position = Programmer;
id = 8; name = Games; position = Guard;

DB created, Version = 1 and the data from the table is logged. The app works, everything is ok. But here we (suddenly!) Understand that an error was made when designing the database structure. Entering a job title in the people table field is incorrect. In addition, we still have data on salaries. You need to create a position table and use the id in the people table. Thus, the structure of our database changes and we assign it a version - 2.

But our app is already installed by users. It has already created a DB version 1, and this database already has data. We can't just delete existing tables and create new ones because maybe the user is already storing their data there. We will need to write scripts to update without losing data.

An upgrade plan is:

- create and fill in a table position
- add to the table people column - posid for storage id with position
- fill people.posid data from position depending on the value people.position
- remove the column people.position

Let's change MainActivity.java. Our application will now be focused on DB version 2. Let's mark this by changing the constant value DB_VERSION on 2:

 final int DB_VERSION = 2; // версия БД

We will rewrite the writeStaff method as follows:

  private void writeStaff(SQLiteDatabase db) {
      Cursor c = db.rawQuery("select * from people", null);
      logCursor(c, "Table people");
      c.close();
      
      c = db.rawQuery("select * from position", null);
      logCursor(c, "Table position");
      c.close();
      
      String sqlQuery = "select PL.name as Name, PS.name as Position, salary as Salary "
        + "from people as PL "
        + "inner join position as PS "
        + "on PL.posid = PS.id ";
      c = db.rawQuery(sqlQuery, null);
      logCursor(c, "inner join");
      c.close();
    }

We will log data from the tables people, position and their association.

We implement the upgrade method - onUpgrade in DBHelper:

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      Log.d(LOG_TAG, " --- onUpgrade database from " + oldVersion
          + " to " + newVersion + " version --- ");

      if (oldVersion == 1 && newVersion == 2) {

        ContentValues cv = new ContentValues();

        // данные для таблицы должностей
        int[] position_id = { 1, 2, 3, 4 };
        String[] position_name = { "Директор", "Программер",
            "Бухгалтер", "Охранник" };
        int[] position_salary = { 15000, 13000, 10000, 8000 };

        db.beginTransaction();
        try {
          // создаем таблицу должностей
          db.execSQL("create table position ("
              + "id integer primary key,"
              + "name text, salary integer);");

          // заполняем ее
          for (int i = 0; i < position_id.length; i++) {
            cv.clear();
            cv.put("id", position_id[i]);
            cv.put("name", position_name[i]);
            cv.put("salary", position_salary[i]);
            db.insert("position", null, cv);
          }

          db.execSQL("alter table people add column posid integer;");

          for (int i = 0; i < position_id.length; i++) {
            cv.clear();
            cv.put("posid", position_id[i]);
            db.update("people", cv, "position = ?",
                new String[] { position_name[i] });
          }

          db.execSQL("create temporary table people_tmp ("
              + "id integer, name text, position text, posid integer);");

          db.execSQL("insert into people_tmp select id, name, position, posid from people;");
          db.execSQL("drop table people;");

          db.execSQL("create table people ("
              + "id integer primary key autoincrement,"
              + "name text, posid integer);");

          db.execSQL("insert into people select id, name, posid from people_tmp;");
          db.execSQL("drop table people_tmp;");

          db.setTransactionSuccessful();
        } finally {
          db.endTransaction();
        }
      }
    }

All according to the upgrade plan I provided above. There are a couple of nuances.

First, we use Database transaction. That is, we need to roll on the database All our updates. And in this case mistakes in the process of updating - all changes must be undone and the database must remain the same. The transactions here are very helpful.

Second, in SQLite can not just remove column, you have to create a temporary table, throw data there, delete the original, create it again with the desired structure, dump the data from the temporary table and delete the temporary table. You can read more about it here - How to Add columns from an existing table in SQLite.

our application has been updated. And now, at startup, it will try to connect to the DB version 2, But will see that the existing version = 1 and will call the method onUpgradeBy giving us the ability to make the necessary changes to the database structure. But this will happen if the program is updated. And what if the user puts our new app on a fresh smartphone for the first time?

In this case, the application will also try to connect to the database version 2. But because the application has just been installed, the database does not yet exist. application program will create DB and give it the version number 2Because it can work with this version. When creating, the method will be called onCreate in DBHelper. So, we have to write in it the code that will create us the database version 2 - that is, an updated table people and a new spreadsheet position.

we are writing onCreate in DBHelper:

    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, " --- onCreate database --- ");

      String[] people_name = { "Иван", "Марья", "Петр", "Антон", "Даша",
          "Борис", "Костя", "Игорь" };
      int[] people_posid = { 2, 3, 2, 2, 3, 1, 2, 4 };

      // данные для таблицы должностей
      int[] position_id = { 1, 2, 3, 4 };
      String[] position_name = { "Директор", "Программер", "Бухгалтер",
          "Охранник" };
      int[] position_salary = { 15000, 13000, 10000, 8000 };

      ContentValues cv = new ContentValues();

      // создаем таблицу должностей
      db.execSQL("create table position (" + "id integer primary key,"
          + "name text, salary integer" + ");");

      // заполняем ее
      for (int i = 0; i < position_id.length; i++) {
        cv.clear();
        cv.put("id", position_id[i]);
        cv.put("name", position_name[i]);
        cv.put("salary", position_salary[i]);
        db.insert("position", null, cv);
      }

      // создаем таблицу людей
      db.execSQL("create table people ("
          + "id integer primary key autoincrement,"
          + "name text, posid integer);");

      // заполняем ее
      for (int i = 0; i < people_name.length; i++) {
        cv.clear();
        cv.put("name", people_name[i]);
        cv.put("posid", people_posid[i]);
        db.insert("people", null, cv);
      }
    }

Create and populate two tables. All clear.

Now you can save everything and launch the application.

We look at the log:

--- onUpgrade database from 1 to 2 version ---
--- Staff db v.2 ---
Table people. 8 rows
id = 1; name = Ivan; posid = 2;
id = 2; name = Mary; posid = 3;
id = 3; name = Peter; posid = 2;
id = 4; name = Anton; posid = 2;
id = 5; name = Dasha; posid = 3;
id = 6; name = Boris; posid = 1;
id = 7; name = Bone; posid = 2;
id = 8; name = Games; posid = 4;
Table position. 4 rows
id = 1; name = Director; salary = 15,000;
id = 2; name = Programmer; salary = 13000;
id = 3; name = Accountant; salary = 10000;
id = 4; name = Guard; salary = 8000;
inner join. 8 rows
Name = Ivan; Position = Programmer; Salary = 13000;
Name = Maria; Position = Accountant; Salary = 10000;
Name = Peter; Position = Programmer; Salary = 13000;
Name = Anton; Position = Programmer; Salary = 13000;
Name = Dasha; Position = Accountant; Salary = 10000;
Name = Boris; Position = Director; Salary = 15000;
Name = Bone; Position = Programmer; Salary = 13000;
Name = Games; Position = Guard; Salary = 8000;

We see what was called onUpgrade and updated us the database from the version 1 on 2. We then output all the data to ensure that the update was correct.

You can also make sure that the new onCreate in DBHelper works correctly. To do this, delete the database file and run the application. The application will not find the database and will create it immediately in the new format and with version 2.

The scenario is fictional, there is something to grapple with and something to argue about, but the point is not. The point is that we saw it happening renewal DB if the application has requested a new version. At first, this creation and updating mechanism may seem confusing. But there is really nothing complicated. With experience will come a full understanding.

I would also like to point out that the Cursor object has a close () method that frees up the resources it uses. Don't forget about him.

I think you can now safely say that working with SQLite on Android we have studied quite thoroughly. And in future lessons we can freely use this knowledge.

full code MainActivity.java:

package ru.startandroid.develop.p0391sqliteonupgradedb;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends Activity {

  final String LOG_TAG = "myLogs";

  final String DB_NAME = "staff"; // имя БД
  final int DB_VERSION = 2; // версия БД

  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    DBHelper dbh = new DBHelper(this);
    SQLiteDatabase db = dbh.getWritableDatabase();
    Log.d(LOG_TAG, " --- Staff db v." + db.getVersion() + " --- ");
    writeStaff(db);
    dbh.close();
  }

  // запрос данных и вывод в лог
  private void writeStaff(SQLiteDatabase db) {
    Cursor c = db.rawQuery("select * from people", null);
    logCursor(c, "Table people");
    c.close();

    c = db.rawQuery("select * from position", null);
    logCursor(c, "Table position");
    c.close();

    String sqlQuery = "select PL.name as Name, PS.name as Position, salary as Salary "
        + "from people as PL "
        + "inner join position as PS "
        + "on PL.posid = PS.id ";
    c = db.rawQuery(sqlQuery, null);
    logCursor(c, "inner join");
    c.close();
  }

  // вывод в лог данных из курсора
  void logCursor(Cursor c, String title) {
    if (c != null) {
      if (c.moveToFirst()) {
        Log.d(LOG_TAG, title + ". " + c.getCount() + " rows");
        StringBuilder sb = new StringBuilder();
        do {
          sb.setLength(0);
          for (String cn : c.getColumnNames()) {
            sb.append(cn + " = "
                + c.getString(c.getColumnIndex(cn)) + "; ");
          }
          Log.d(LOG_TAG, sb.toString());
        } while (c.moveToNext());
      }
    } else
      Log.d(LOG_TAG, title + ". Cursor is null");
  }

  // класс для работы с БД
  class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context) {
      super(context, DB_NAME, null, DB_VERSION);
    }

    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, " --- onCreate database --- ");

      String[] people_name = { "Иван", "Марья", "Петр", "Антон", "Даша",
          "Борис", "Костя", "Игорь" };
      int[] people_posid = { 2, 3, 2, 2, 3, 1, 2, 4 };

      // данные для таблицы должностей
      int[] position_id = { 1, 2, 3, 4 };
      String[] position_name = { "Директор", "Программер", "Бухгалтер",
          "Охранник" };
      int[] position_salary = { 15000, 13000, 10000, 8000 };

      ContentValues cv = new ContentValues();

      // создаем таблицу должностей
      db.execSQL("create table position (" + "id integer primary key,"
          + "name text, salary integer" + ");");

      // заполняем ее
      for (int i = 0; i < position_id.length; i++) {
        cv.clear();
        cv.put("id", position_id[i]);
        cv.put("name", position_name[i]);
        cv.put("salary", position_salary[i]);
        db.insert("position", null, cv);
      }

      // создаем таблицу людей
      db.execSQL("create table people ("
          + "id integer primary key autoincrement,"
          + "name text, posid integer);");

      // заполняем ее
      for (int i = 0; i < people_name.length; i++) {
        cv.clear();
        cv.put("name", people_name[i]);
        cv.put("posid", people_posid[i]);
        db.insert("people", null, cv);
      }
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      Log.d(LOG_TAG, " --- onUpgrade database from " + oldVersion
          + " to " + newVersion + " version --- ");

      if (oldVersion == 1 && newVersion == 2) {

        ContentValues cv = new ContentValues();

        // данные для таблицы должностей
        int[] position_id = { 1, 2, 3, 4 };
        String[] position_name = { "Директор", "Программер",
            "Бухгалтер", "Охранник" };
        int[] position_salary = { 15000, 13000, 10000, 8000 };

        db.beginTransaction();
        try {
          // создаем таблицу должностей
          db.execSQL("create table position ("
              + "id integer primary key,"
              + "name text, salary integer);");

          // заполняем ее
          for (int i = 0; i < position_id.length; i++) {
            cv.clear();
            cv.put("id", position_id[i]);
            cv.put("name", position_name[i]);
            cv.put("salary", position_salary[i]);
            db.insert("position", null, cv);
          }

          db.execSQL("alter table people add column posid integer;");

          for (int i = 0; i < position_id.length; i++) {
            cv.clear();
            cv.put("posid", position_id[i]);
            db.update("people", cv, "position = ?",
                new String[] { position_name[i] });
          }

          db.execSQL("create temporary table people_tmp ("
              + "id integer, name text, position text, posid integer);");

          db.execSQL("insert into people_tmp select id, name, position, posid from people;");
          db.execSQL("drop table people;");

          db.execSQL("create table people ("
              + "id integer primary key autoincrement,"
              + "name text, posid integer);");

          db.execSQL("insert into people select id, name, posid from people_tmp;");
          db.execSQL("drop table people_tmp;");

          db.setTransactionSuccessful();
        } finally {
          db.endTransaction();
        }
      }
    }

  }
}

In the next lesson:

- Let's see how LayoutInflater can be used




Discuss in the forum [50 replies]

Leave a Comment