TruffelNL
Reputation: 520
SQLite onUpgrade removing and adding column
In my Android project I've got a table which I used for a while. Now I want to remove a column 'image_byte' and add a new one 'image_path'. I cannot just rename the column as the app is being used and it needs to remove the text in image_byte so it will not be used anymore.
I implement SQLiteOpenHelper and the following code is in onUpgrade:
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
//check if upgrade is really needed
if(newVersion > oldVersion) {
tableSQL = "CREATE TABLE IF NOT EXISTS table1 ( id INTEGER PRIMARY KEY, name TEXT, image_path TEXT)";
db.execSQL(tableSQL);
//get all the current tableName table columns
List<String> columns = getColumns(db, "table1");
//rename the old table
db.execSQL("ALTER TABLE table1 RENAME TO temp_table1;");
//create the new tableName table
db.execSQL(tableSQL);
//merge the old and new columns, keep all the old ones
columns.retainAll(getColumns(db, "table1"));
String cols = joinColumnNames(columns, ",");
//insert the old data in the new table
db.execSQL(String.format("INSERT INTO %s (%s) SELECT %s FROM temp_%s", "table1", cols, cols, "table1"));
//delete the old temp table
db.execSQL("DROP table 'temp_table1'");
}
}
And the getColumns function:
private List<String> getColumns(SQLiteDatabase db, String tableName){
List<String> ar = null;
Cursor c = null;
try{
c = db.rawQuery("SELECT * FROM " + tableName + " limit 1", null);
if(c != null){
ar = new ArrayList< >(Arrays.asList(c.getColumnNames()));
}
} catch (Exception e){
} finally {
if (c != null){
c.close();
}
}
return ar;
}
But when debugging the app, it says that column 'image_byte' does not exist and that it cannot copy the contents from temp_table1 to table1. Which is correct, because it is now 'image_path' and image_byte shouldn't be copied. But why is getColumns returning the old columns? Because when I use Log.d, it shows 'id', 'name', 'image_byte'. Therefore the retainAll function will not drop 'image_byte'. I can't seem to figure out why it still returns image_byte. It's probably stupid and simple but I just don't see it.
Upvotes: 3
Views: 962
Answers (1)
MikeT
Reputation: 56983
I believe that the issue may well be due to caching in that columns.retainAll(getColumns(db, "table1")); appears to retrieve the columns from the original table1.
This is the results from some testing that I have undertaken:-
10-15 13:37:19.319 26740-26740/? D/DBINFO: onUpgrade Started.
10-15 13:37:19.320 26740-26740/? D/DBINFO: Databse info in onUpgrade after table rename to temp_table1
10-15 13:37:19.320 26740-26740/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/mjt.sqlitedbexamples/databases/imagestore
10-15 13:37:19.320 26740-26740/? D/SQLITE_CSU: Database Version = 1
10-15 13:37:19.320 26740-26740/? D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
10-15 13:37:19.320 26740-26740/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 13:37:19.320 26740-26740/? D/SQLITE_CSU: Table Name = temp_table1 Created Using = CREATE TABLE "temp_table1"(id INTEGER PRIMARY KEY,name TEXT,image_byte TEXT)
10-15 13:37:19.320 26740-26740/? D/SQLITE_CSU: Table = temp_table1 ColumnName = id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
10-15 13:37:19.320 26740-26740/? D/SQLITE_CSU: Table = temp_table1 ColumnName = name ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 13:37:19.320 26740-26740/? D/SQLITE_CSU: Table = temp_table1 ColumnName = image_byte ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 13:37:19.321 26740-26740/? D/DBINFO: Databse info in onUpgrade after creation of new table1.
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/mjt.sqlitedbexamples/databases/imagestore
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Database Version = 1
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table Name = temp_table1 Created Using = CREATE TABLE "temp_table1"(id INTEGER PRIMARY KEY,name TEXT,image_byte TEXT)
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table = temp_table1 ColumnName = id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table = temp_table1 ColumnName = name ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table = temp_table1 ColumnName = image_byte ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table Name = table1 Created Using = CREATE TABLE table1(id INTEGER PRIMARY KEY, name TEXT,image_path TEXT)
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table = table1 ColumnName = id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table = table1 ColumnName = name ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 13:37:19.321 26740-26740/? D/SQLITE_CSU: Table = table1 ColumnName = image_path ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 13:37:19.321 26740-26740/? D/ORIG COLUMNS: Orignal Column Info - Column=id Column=name Column=image_byte
10-15 13:37:19.321 26740-26740/? D/NEWTAB COLUMNS: Columns from new table1 - Column=id Column=name Column=image_byte
10-15 13:37:19.321 26740-26740/? D/RETAIN COLUMNS: Columns After Retain - Column=id Column=name Column=image_byte
10-15 13:37:19.321 26740-26740/? D/JOINED COLUMNS: id,name,image_byte
10-15 13:37:19.321 26740-26740/? E/SQLiteLog: (1) table table1 has no column named image_byte
10-15 13:37:19.329 26740-26740/? D/AndroidRuntime: Shutting down VM
The last few lines D/ORIG COLUMNS, D/NEWTAB COLUMNS, D/RETAIN COLUMNS. D/RETAIN COLUMNS indicates that image_byte has been extracted.
Perhaps introducing an intermediate table name and then finally rename that to the original table name may resolve that issue.
Note the SQLITE_CSU output was produced from the utilities mentioned here.
This is the onUpgrade code useed for testing/producing the above:-
@Override
public void onUpgrade(SQLiteDatabase db, int oldversion, int newversion) {
String tableSQL = "CREATE TABLE IF NOT EXISTS " + TBNAME + "(" +
"id INTEGER PRIMARY KEY, " +
"name TEXT," +
"image_path TEXT" +
")";
if (newversion > oldversion) {
Log.d("DBINFO","onUpgrade Started.");
db.execSQL(tableSQL);
//get all the current tableName table columns
List<String> columns = getColumns(db, "table1");
//rename the old table
db.execSQL("ALTER TABLE table1 RENAME TO temp_table1;");
Log.d("DBINFO","Databse info in onUpgrade after table rename to temp_table1");
CommonSQLiteUtilities.logDatabaseInfo(db);
//create the new tableName table
db.execSQL(tableSQL);
Log.d("DBINFO","Databse info in onUpgrade after creation of new table1.");
CommonSQLiteUtilities.logDatabaseInfo(db);
//merge the old and new columns, keep all the old ones
String loginfo = "Orignal Column Info - ";
for (String s:columns) {
loginfo = loginfo + "Column=" + s + " ";
}
Log.d("ORIG COLUMNS",loginfo);
loginfo = "Columns from new table1 - ";
for (String s: getColumns(db, "table1")) {
loginfo = loginfo + "Column=" + s + " ";
}
Log.d("NEWTAB COLUMNS",loginfo);
columns.retainAll(getColumns(db, "table1"));
loginfo = "Columns After Retain - ";
for (String s: columns) {
loginfo = loginfo + "Column=" + s + " ";
}
Log.d("RETAIN COLUMNS",loginfo);
String cols = joinColumnNames(columns, ",");
Log.d("JOINED COLUMNS",cols);
//insert the old data in the new table
db.execSQL(String.format("INSERT INTO %s (%s) SELECT %s FROM temp_%s", "table1", cols, cols, "table1"));
//delete the old temp table
db.execSQL("DROP table 'temp_table1'");
Log.d("DBINFO","Databse info in onUpgrade after DELETING temp_table1.");
}
Working example :-
In this example the data is extracted from the original table into a Cursor, the new table is created with a different name, this new table is loaded with the data then the old table is dropped and finally the new table is renamed.
This may be a slightly more complicated as it maintains the id, it's only 1 line though :-
cv.put("id",Long.toString(olddata.getLong(olddata.getColumnIndex("id"))));
Note! lines commenetd with //<<<<< are purely for logging the activity. Although olddata.moveToPosition(-1); //<<<<< is needed if the oldata Cursor is read/traversed (i.e. to return the position to before the first row).
@Override
public void onUpgrade(SQLiteDatabase db, int oldversion, int newversion) {
String temptableSQL = "CREATE TABLE IF NOT EXISTS temp_" + TBNAME + "(" +
"id INTEGER PRIMARY KEY, " +
"name TEXT," +
"image_path TEXT" +
")";
if (newversion > oldversion) {
Log.d("DBINFO","onUpgrade Started.");
db.execSQL(temptableSQL);
Cursor olddata = db.query(TBNAME,null,null,null,null,null,null);
CommonSQLiteUtilities.logCursorData(olddata); //<<<<
olddata.moveToPosition(-1); //<<<<<
//db.execSQL(tableSQL);
Log.d("DBINFO","Databse info in onUpgrade after creation of new table1.");
CommonSQLiteUtilities.logDatabaseInfo(db); //<<<<<
Log.d("DBCOPYDATA",
"Copying data from OLD Data. # rows = " +
Integer.toString(olddata.getCount())
); //<<<<<
while (olddata.moveToNext()) {
ContentValues cv = new ContentValues();
Log.d("DBCOPYDATA","ROW " + Integer.toString(olddata.getPosition()) +
"\n\tID=" + Long.toString(olddata.getLong(olddata.getColumnIndex("id"))) +
"\n\tNAME=" + olddata.getString(olddata.getColumnIndex("name")) +
"\n\tIMG =" + olddata.getString(olddata.getColumnIndex("image_byte"))
); //<<<<<
cv.put("id",Long.toString(olddata.getLong(olddata.getColumnIndex("id"))));
cv.put("name",olddata.getString(olddata.getColumnIndex("name")));
cv.put("image_path",olddata.getString(olddata.getColumnIndex("image_byte")));
Long id = db.insert("temp_" + TBNAME,null,cv);
Log.d("DBCOPYDATA","INSERTED ID=" + Long.toString(id)); //<<<<<
}
olddata.close();
db.execSQL("DROP TABLE IF EXISTS " + TBNAME);
db.execSQL("ALTER TABLE temp_" + TBNAME + " RENAME TO " + TBNAME);
Cursor csr = db.query(TBNAME,null,null,null,null,null,null); //<<<<<
CommonSQLiteUtilities.logDatabaseInfo(db); //<<<<<
CommonSQLiteUtilities.logCursorData(csr); //<<<<<
csr.close(); //<<<<<
}
}
Output from a successful test :-
10-15 17:54:41.618 16960-16960/? D/DBINFO: onUpgrade Started.
10-15 17:54:41.619 16960-16960/? D/SQLITE_CSU: logCursorData Cursor has 3 rows with 3 columns.
10-15 17:54:41.619 16960-16960/? D/SQLITE_CSU: Information for row 1 offset=0
For Column id Type is INTEGER value as String is 1 value as long is 1 value as double is 1.0
For Column name Type is STRING value as String is Test001 value as long is 0 value as double is 0.0
For Column image_byte Type is STRING value as String is mypath/mypath/images/image001 value as long is 0 value as double is 0.0
10-15 17:54:41.619 16960-16960/? D/SQLITE_CSU: Information for row 2 offset=1
For Column id Type is INTEGER value as String is 2 value as long is 2 value as double is 2.0
For Column name Type is STRING value as String is Test001 value as long is 0 value as double is 0.0
For Column image_byte Type is STRING value as String is mypath/mypath/images/image002 value as long is 0 value as double is 0.0
10-15 17:54:41.619 16960-16960/? D/SQLITE_CSU: Information for row 3 offset=2
For Column id Type is INTEGER value as String is 3 value as long is 3 value as double is 3.0
For Column name Type is STRING value as String is Test001 value as long is 0 value as double is 0.0
For Column image_byte Type is STRING value as String is mypath/mypath/images/image003 value as long is 0 value as double is 0.0
10-15 17:54:41.619 16960-16960/? D/DBINFO: Databse info in onUpgrade after creation of new table1.
10-15 17:54:41.619 16960-16960/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/mjt.sqlitedbexamples/databases/imagestore
10-15 17:54:41.619 16960-16960/? D/SQLITE_CSU: Database Version = 1
10-15 17:54:41.619 16960-16960/? D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table Name = table1 Created Using = CREATE TABLE table1(id INTEGER PRIMARY KEY,name TEXT,image_byte TEXT)
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table = table1 ColumnName = id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table = table1 ColumnName = name ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table = table1 ColumnName = image_byte ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table Name = temp_table1 Created Using = CREATE TABLE temp_table1(id INTEGER PRIMARY KEY, name TEXT,image_path TEXT)
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table = temp_table1 ColumnName = id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table = temp_table1 ColumnName = name ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 17:54:41.620 16960-16960/? D/SQLITE_CSU: Table = temp_table1 ColumnName = image_path ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 17:54:41.620 16960-16960/? D/DBCOPYDATA: Copying data from OLD Data. # rows = 3
10-15 17:54:41.620 16960-16960/? D/DBCOPYDATA: ROW 0
ID=1
NAME=Test001
IMG =mypath/mypath/images/image001
10-15 17:54:41.620 16960-16960/? D/DBCOPYDATA: INSERTED ID=1
10-15 17:54:41.620 16960-16960/? D/DBCOPYDATA: ROW 1
ID=2
NAME=Test001
IMG =mypath/mypath/images/image002
10-15 17:54:41.620 16960-16960/? D/DBCOPYDATA: INSERTED ID=2
10-15 17:54:41.620 16960-16960/? D/DBCOPYDATA: ROW 2
ID=3
NAME=Test001
IMG =mypath/mypath/images/image003
10-15 17:54:41.620 16960-16960/? D/DBCOPYDATA: INSERTED ID=3
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/mjt.sqlitedbexamples/databases/imagestore
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Database Version = 1
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Table Name = table1 Created Using = CREATE TABLE "table1"(id INTEGER PRIMARY KEY, name TEXT,image_path TEXT)
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Table = table1 ColumnName = id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Table = table1 ColumnName = name ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Table = table1 ColumnName = image_path ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: logCursorData Cursor has 3 rows with 3 columns.
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Information for row 1 offset=0
For Column id Type is INTEGER value as String is 1 value as long is 1 value as double is 1.0
For Column name Type is STRING value as String is Test001 value as long is 0 value as double is 0.0
For Column image_byte Type is STRING value as String is mypath/mypath/images/image001 value as long is 0 value as double is 0.0
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Information for row 2 offset=1
For Column id Type is INTEGER value as String is 2 value as long is 2 value as double is 2.0
For Column name Type is STRING value as String is Test001 value as long is 0 value as double is 0.0
For Column image_byte Type is STRING value as String is mypath/mypath/images/image002 value as long is 0 value as double is 0.0
10-15 17:54:41.621 16960-16960/? D/SQLITE_CSU: Information for row 3 offset=2
For Column id Type is INTEGER value as String is 3 value as long is 3 value as double is 3.0
For Column name Type is STRING value as String is Test001 value as long is 0 value as double is 0.0
For Column image_byte Type is STRING value as String is mypath/mypath/images/image003 value as long is 0 value as double is 0.0
Upvotes: 2
Related Questions
- How to add column on existing table in android SQLite?
- Difficulty in upgrading SQlite table
- How to add an additional column in a database table in Sqlite?
- Android Sqlite onupgrade delete table from database
- Table column not updated after Alter Table in SQLite
- Android - How to make changes in SQLite table columns.(Add new column)
- Upgrade sqlite table in android
- Alter Table in onUpgrade
- How to modify type in a table of SQLite in Android, using onUpgrade method?
- SQLite android ALTER COLUMN help