39. onUpgrade. SQLite


:

- onUpgrade


, . , onUpgrade SQLiteOpenHelper. , , . - SQLiteOpenHelper DBHelper.

onUpgrade , . , .

people . . , people. -.

position . people ID position.


:

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

, .

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 = 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) {

}
}
}


. Cursor logCursor. writeStaff people . Activity onCreate DBHelper, , , writeStaff .

DBHelper . -. , DB_VERSION = 1 1. onCreate .

. :

--- onCreate database ---
--- Staff db v.1 ---
Table people. 8 rows
id = 1; name = ; position = ;
id = 2; name = ; position = ;
id = 3; name = ; position = ;
id = 4; name = ; position = ;
id = 5; name = ; position = ;
id = 6; name = ; position = ;
id = 7; name = ; position = ;
id = 8; name = ; position = ;

, = 1 . , . (!) , . people . . - position, id people. 2.

. 1, . , .. . .

:

- position
- people posid id position
- people.posid position people.position
- people.position


MainActivity.java. 2. , DB_VERSION 2:

final int DB_VERSION = 2; //


writeStaff :

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
();
}

people, position .


- onUpgrade 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();
}
}
}

, . .

-, -. .. . - . .

-, SQLite , , , , , . - How do I add or delete columns from an existing table in SQLite.

. , , 2, , = 1 onUpgrade, . . ?

2. .. , . 2, .. . onCreate DBHelper. , , 2 .. people position.

onCreate 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);
}
}

. .


.

:

--- onUpgrade database from 1 to 2 version ---
--- Staff db v.2 ---
Table people. 8 rows
id = 1; name = ; posid = 2;
id = 2; name = ; posid = 3;
id = 3; name = ; posid = 2;
id = 4; name = ; posid = 2;
id = 5; name = ; posid = 3;
id = 6; name = ; posid = 1;
id = 7; name = ; posid = 2;
id = 8; name = ; posid = 4;
Table position. 4 rows
id = 1; name = ; salary = 15000;
id = 2; name = ; salary = 13000;
id = 3; name = ; salary = 10000;
id = 4; name = ; salary = 8000;
inner join. 8 rows
Name = ; Position = ; Salary = 13000;
Name = ; Position = ; Salary = 10000;
Name = ; Position = ; Salary = 13000;
Name = ; Position = ; Salary = 13000;
Name = ; Position = ; Salary = 10000;
Name = ; Position = ; Salary = 15000;
Name = ; Position = ; Salary = 13000;
Name = ; Position = ; Salary = 8000;


, onUpgrade 1 2. , , .

, onCreate DBHelper . . 2.


, , . , , , . , , . . .

, Cursor close(), . .


, , SQLite Android . .


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();
}
}
}

}
}



:

- LayoutInflater