Lecture
In this lesson:
- we change the version and update the database structure in onUpgrade
With the development of the application, it may be necessary to change the structure of the database that it uses. In one of the past lessons, I mentioned that the onUpgrade method of the SQLiteOpenHelper class is used for this. This method is called if the existing version of the database is different from the one to which we are trying to connect . We usually specified the version when calling the SQLiteOpenHelper superclass constructor in the DBHelper constructor.
Let's try to use the onUpgrade method and see how the transition to the new version of the database occurs. To do this, we will write a small application similar to one of the applications from previous lessons - about employees and positions.
The first version of the database will contain only the people table with the name of the employee and his position. But such a table would not be entirely correct. If we suddenly change the name of the position, we will have to update all the relevant entries in people. Therefore, we decide to change the database and organize the data a little differently.
In the second version, we will add a position table with the name of the position and salary. And in the people table, instead of the job title, we write the corresponding ID from position .
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 do not use the screen again, we will output everything to the log.
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"; // имя БД
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 have grouped operations by outputting data from the Cursor to the log file - the logCursor method. The writeStaff method selects data from the people table and calls a method to output data to the log. In the Activity onCreate method, we create a DBHelper object, connect to the database, output the database version to the log, call writeStaff, and disconnect .
In DBHelper everything is as usual. In the constructor, we call the super class constructor. Please note, DB_VERSION = 1 - we will connect to the version 1 database. In the onCreate method , create a table and fill it.
All save and run 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 = Marya; 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 = Kostya; position = Programmer;
id = 8; name = Igor; position = Security guard;
The database was created , version = 1 and the data from the table was output to the log. The application works, everything is ok. But here we (suddenly!) Understand that the design of the database structure was a mistake. Writing a job title in the people table is wrong. In addition, we still have data on wages. It is necessary to create a table of posts - position, and use the id from it in the people table. Thus, the structure of our database is changing and we assign the version - 2 to it .
But our application is already installed by users. It has already created a database version 1, and there is already data in this database. We cannot simply delete existing tables and create new ones, since perhaps the user already stores their data there. We will need to write scripts to update without losing data.
The update plan is:
- create and fill data with position table
- add a column to the people table - posid for storing id from position
- fill people.posid with data from position depending on the value of people.position
- delete the people.position column
Let's change MainActivity.java . Our application will now be focused on the version 2 DB. Let's specify this by changing the value of the constant DB_VERSION to 2:
final int DB_VERSION = 2 ; // версия БД The writeStaff method is rewritten 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 output the log data from the people , position tables and their associations .
Implement 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 in accordance with the update plan, which I quoted above. There are a couple of nuances.
First, we use a DB transaction . Those. we need all our updates on the database. And in case of an error in the update process - all changes should be canceled and the database should remain the same. Here transactions very much help out.
Secondly, in SQLite you cannot just delete a column, you have to create a temporary table, transfer data there, delete the original, create it again with the necessary structure, dump data from the temporary table into it and delete the temporary table. More information about this can be found here - How do I add or delete columns from an existing table in SQLite.
Our application has been updated . And now, at startup, it will try to connect to the database version 2 , but it will see that the existing version = 1 and call the onUpgrade method, giving us the opportunity to make the necessary changes to the database structure. But this will happen in the case of updating the application. And what will happen if the user puts our new application on a fresh smartphone for the first time?
In this case, the application will also try to connect to the version 2 DB. But since the application is just installed, the database does not exist yet. The application creates a database and assigns version 2 to it , since It can work with this version. At creation, the onCreate method in DBHelper will be called. So, in it we have to write the code that will create us a database of version 2 - i.e. updated people table and new position table.
We write 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);
}
} Creating and populating two tables with data. All clear.
Now you can save and run 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 = Marya; 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 = Kostya; posid = 2;
id = 8; name = Igor; posid = 4;
Table position. 4 rows
id = 1; name = Director; salary = 15000;
id = 2; name = Programmer; salary = 13000;
id = 3; name = Accountant; salary = 10,000;
id = 4; name = Security Guard; salary = 8000;
inner join. 8 rows
Name = Ivan; Position = Programmer; Salary = 13,000;
Name = Marya; Position = Accountant; Salary = 10,000;
Name = Peter; Position = Programmer; Salary = 13,000;
Name = Anton; Position = Programmer; Salary = 13,000;
Name = Dasha; Position = Accountant; Salary = 10,000;
Name = Boris; Position = Director; Salary = 15,000;
Name = Kostya; Position = Programmer; Salary = 13,000;
Name = Igor; Position = Security Guard; Salary = 8000;
We see that onUpgrade was called and updated our database from version 1 to 2 . Then we print all the data to make sure that the update is correct.
You can also make sure that the new onCreate in DBHelper will work correctly. To do this, delete the database file and run the application. The application will not find the database and create it immediately in the new format and with version 2.
The scenario is fictional, there is something to complain about and argue about, but that is not the point. The point is that we saw how the database is updated if the application requested a new version. At first, this creation and update mechanism may seem confusing. But there is really nothing complicated. With experience, complete understanding will come.
I also want to note that the Cursor object has a close () method, which frees the resources it occupies. Do not forget about it.
I think now we can safely say that we have studied thoroughly with SQLite in Android. And in future lessons we will be able to 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:
- parse how you can use LayoutInflater
! Объясните, пожалуйста по-подробней, откуда в метод onUpgrade передаются аргументы oldVersion и newVersion. Заранее благодарю.
- при создании DBHelper вы указываете версию БД в конструкторе (newVersion)
- прога смотрит на имеющийся файл с БД, читает из него версию (oldVersion)
Две этих цифры сравниваются, если они разные, то вызывается onUpgrade. Думаю, как-то так.
А как обновлять таблицу, в которой есть автоинкрементное поле?
Допустим, есть таблица заголовков, и таблица деталей - оба с инкрементным полем - первичным ключом. Вторая имеет поле-ссылку на строку в таблице заголовков.
Ведь при любом обновлении-удалении автоинкрементное поле нельзя назначить как было .
Или можно? может быть какая-то команда есть, чтоб временно заносить туда значения во время операций обновления БД
Поле ключей не повторяется. При обновлении ключ не меняется, т.к. его нельзя поменять, а при удалении этот ключ больше не появится. Уточните вопрос, что у вас не получается?
Comments
To leave a comment
Mobile Programming (Android IOs)
Terms: Mobile Programming (Android IOs)