This lesson:
- changing database version and updating its structure with onUpdate method
Developing your app it may occur a need to change a structure of the database which it uses. I mentioned in one of the previous lessons, that the SQLiteOpenHelper class’ onUpdate method is used for that. This method is called if an existing database version differs from that one, we are trying to connect to. We usually specified database version in DBHelper’s constructor calling SQLiteOpenHelper superclass constructor.
We will try to use the onUpgrade method and look, how the migration to a newer version of the database is processing. For this we will code a little app, similar to the one from the previous lesson about positions and employees.
First version of the database will only contain the people table with employee’s name and position. But such table won’t be correct enough. We will have to update all appropriate rows in the people table, in case the position name will suddenly change. That’s why we decide to change the database structure and to organise data another way.
We will add the position table with salary and position columns within the second version. In the people table we will put an appropriate id from the position table instead of specifying the position name.
Let’s create a project:
Project name: P0391_SQLiteOnUpgradeDB
Build Target: Android 2.3.3
Application name: SQLiteOnUpgradeDB
Package name: ru.startandroid.develop.p0391sqliteonupgradedb
Create Activity: MainActivity
We won’t use the UI components and will display the data in logs.
Open MainActivity.java and code:
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"; // DB name
final int DB_VERSION = 1; // DB version
/** 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();
}
// the data request and displaying
private void writeStaff(SQLiteDatabase db) {
Cursor c = db.rawQuery("select * from people", null);
logCursor(c, "Table people");
c.close();
}
// displaying the cursor data in logs
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");
}
// the class for databasae interaction
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 = { "Ivan", "Maria", "Petr", "Anton", "Dasha",
"Boris", "Kostja", "Igor" };
String[] people_positions = { "Developer", "Accountant",
"Developer", "Developer", "Accountant", "Director",
"Developer", "Security" };
ContentValues cv = new ContentValues();
// the creation of the people table
db.execSQL("create table people ("
+ "id integer primary key autoincrement,"
+ "name text, position text);");
// filling it
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. We grouped the operations of the data displaying into the log from Cursor within the logCursor method. The writeStaff method selects data from the people table and calls the method for displaying data into the logs. We create a DBHelper object within the onCreate method, connect to the database, display database version in logs, call writeStaff method and close the connection.
Everything is as usual with DBHelper. We call the super class method within the constructor. Note, DB_VERSION = 1, we will connect to the first version of the database. Within onCreate method we create a table and fill it with data.
Let’s save and launch the app. Look in logs:
--- onCreate database ---
--- Staff db v.1 ---
Table people. 8 rows
id = 1; name = Ivan; position = Developer;
id = 2; name = Maria; position = Accountant;
id = 3; name = Petr; position = Developer;
id = 4; name = Anton; position = Developer;
id = 5; name = Dasha; position = Accountant;
id = 6; name = Boris; position = Director;
id = 7; name = Kostja; position = Developer;
id = 8; name = Igor; position = Security;
The database was created, it’s version equals to 1 and the table’s data was displayed in logs. The application works and everything is fine, but suddenly we understand that a mistake was made while database designing. It’s not right to write position’s name in people table. In addition, salary data will be added. We should create a position table and use its ids in the people table. This way our database structure will change and we will specify its version as second.
But our application is already installed by users.It has already created the first version of the database, and it was filled with data. We can’t just delete an existing tables and create the new ones, because the user may possibly keep his data in them. We need to write scripts for database updating without data losses.
Here is the plan:
- we will create and fill with data the position table;
- we will add a posid column into the people table for keeping id from the position table;
- we will fill people.posid with data from position depending on people.position value;
- we will delete the people.position column;
Let’s change the MainActivity.java code. Our application will now be oriented to database version 2. Let’s specify this by changing DB_VERSION field value to 2:
final int DB_VERSION = 2; // DB version
Rewrite the writeStaff method this way:
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 display in logs the data from people and position tables and their joins.
We will implement the onUpgrade method within 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();
// position table data
int[] position_id = { 1, 2, 3, 4 };
String[] position_name = { "Director", "Developer",
"Accountant", "Security" };
int[] position_salary = { 15000, 13000, 10000, 8000 };
db.beginTransaction();
try {
// positions table creation
db.execSQL("create table position ("
+ "id integer primary key,"
+ "name text, salary integer);");
// filling it
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();
}
}
}
The code follows the updation plan, I mentioned before. But there are a couple of notes.
At first, we use the database transaction, i.e we need all our updates to be applied to the database. All the updates must be canceled in case of a mistake and the database must remain the same. The transactions are very useful in this case.
Secondly, you can’t just delete a column in SQLite, you need to create a temporary column for that, transfer your data to it, delete the original table, then create it again but with a new structure, fill it with the data from the temporary table and delete the last one. Here you may read about it in details - How do I add or delete columns from an existing table in SQLite.
Our application was updated. It will try to connect to the database version 2 after launch, by it will see that the existing database version is 1 and will call the onUpgrade method, giving us an opportunity to apply all necessary changes to the database structure. This will happen in case of the application updating, but what will happen in case user will install our application on his device for the first time.
In this case the application will try to connect to the database version 2, but since the application has just been installed, the database doesn’t exist. The application will create the database and specify its number as 2, because it knows how to work exactly with this version. Creating the table the DBHelper’s class onCreate method will be called. That means within this method we must to add some code that will create database version 2, i.e updated people table and new position table.
Let’s code the onCreate within DBHelper:
public void onCreate(SQLiteDatabase db) {
Log.d(LOG_TAG, " --- onCreate database --- ");
String[] people_name = { "Ivan", "Maria", "Petr", "Anton", "Dasha",
"Boris", "Kostja", "Igor" };
int[] people_posid = { 2, 3, 2, 2, 3, 1, 2, 4 };
// positions table data
int[] position_id = { 1, 2, 3, 4 };
String[] position_name = { "Director", "Developer", "Accountant",
"Security" };
int[] position_salary = { 15000, 13000, 10000, 8000 };
ContentValues cv = new ContentValues();
// creating of the positions table
db.execSQL("create table position (" + "id integer primary key,"
+ "name text, salary integer" + ");");
// filling it
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);
}
// creating of the people table
db.execSQL("create table people ("
+ "id integer primary key autoincrement,"
+ "name text, posid integer);");
// filling it
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);
}
}
Here are the tables’ creation and filling them with data. Everything is clear.
Now we can save and launch the app.
Look in logs:
--- onUpgrade database from 1 to 2 version ---
--- Staff db v.2 ---
Table people. 8 rows
id = 1; name = Ivan; posid = 2;
id = 2; name = Maria; posid = 3;
id = 3; name = Petr; posid = 2;
id = 4; name = Anton; posid = 2;
id = 5; name = Dasha; posid = 3;
id = 6; name = Boris; posid = 1;
id = 7; name = Kostja; posid = 2;
id = 8; name = Igor; posid = 4;
Table position. 4 rows
id = 1; name = Director; salary = 15000;
id = 2; name = Developer; salary = 13000;
id = 3; name = Accountant; salary = 10000;
id = 4; name = Security; salary = 8000;
inner join. 8 rows
Name = Ivan; Position = Developer; Salary = 13000;
Name = Maria; Position = Accountant; Salary = 10000;
Name = Petr; Position = Developer; Salary = 13000;
Name = Anton; Position = Developer; Salary = 13000;
Name = Dasha; Position = Accountant; Salary = 10000;
Name = Boris; Position = Director; Salary = 15000;
Name = Kostja; Position = Developer; Salary = 13000;
Name = Igor; Position = Security; Salary = 8000;
We see that the onUpgrade method was called and it has updated database version to second from the first one. Then we display data in logs to get sure that the updating was correct.
We may also get sure that the new DBHelper class’ onCreate method works correctly. To do this we need to delete database file and launch the application, it won’t find the database and will create it in a new format with version 2.
The script is fictional, there is something to complain about and what to argue about, but the point is not this. The point is that we saw the way of the database updating, if the application requested for a new version. For the first time may be, this way of creating and updating will seem complicated, but it is really not. A full understanding will come with experience.
I also want to note that the Cursor object has a close () method, which frees the resources the method holds. Do not forget about it.
I think now we can safely say that we sufficiently studied the work with SQLite in Android. And in the future lessons we can freely use this knowledge.
MainActivity.java full code:
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"; // DB name
final int DB_VERSION = 2; // DB version
/** 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();
}
// data query and displaying
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();
}
// displaying data from cursor
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 for database interaction
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 = { "Ivan", "Maria", "Petr", "Anton", "Dasha",
"Boris", "Kostja", "Igor" };
int[] people_posid = { 2, 3, 2, 2, 3, 1, 2, 4 };
// data for position table
int[] position_id = { 1, 2, 3, 4 };
String[] position_name = { "Director", "Developer", "Accountant",
"Security" };
int[] position_salary = { 15000, 13000, 10000, 8000 };
ContentValues cv = new ContentValues();
// positions table creating
db.execSQL("create table position (" + "id integer primary key,"
+ "name text, salary integer" + ");");
// filling it
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);
}
// people table creation
db.execSQL("create table people ("
+ "id integer primary key autoincrement,"
+ "name text, posid integer);");
// filling it
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();
// data for positions table
int[] position_id = { 1, 2, 3, 4 };
String[] position_name = { "Director", "Developer",
"Accountant", "Security" };
int[] position_salary = { 15000, 13000, 10000, 8000 };
db.beginTransaction();
try {
// positions table creating
db.execSQL("create table position ("
+ "id integer primary key,"
+ "name text, salary integer);");
// filling it
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();
}
}
}
}
}
The next lesson:
- find out the usage of LayoutInflater
Присоединяйтесь к нам в Telegram:
- в канале StartAndroid публикуются ссылки на новые статьи с сайта startandroid.ru и интересные материалы с хабра, medium.com и т.п.
- в чатах решаем возникающие вопросы и проблемы по различным темам: Android, Compose, Kotlin, RxJava, Dagger, Тестирование, Performance
- ну и если просто хочется поговорить с коллегами по разработке, то есть чат Флудильня
