I am getting an error from Logcat
saying that a certain column (in my SQLiteOpenHelper
subclass) does not exist. I thought I could upgrade the database by changing the DATABASE_CREATE
string. But apparently not, so how can I (step-by-step) upgrade my SQLite Database from version 1 to version 2?
I apologize if the question seems "noobish", but I am still learning about Android.
@Pentium10 This is what I do in onUpgrade:
private static final int DATABASE_VERSION = 1;
....
switch (upgradeVersion) {
case 1:
db.execSQL("ALTER TABLE task ADD body TEXT");
upgradeVersion = 2;
break;
}
...
Ok, before you run into bigger problems you should know that SQLite is limited on the ALTER TABLE command, it allows add
and rename
only no remove/drop which is done with recreation of the table.
You should always have the new table creation query at hand, and use that for upgrade and transfer any existing data. Note: that the onUpgrade methods runs one for your sqlite helper object and you need to handle all the tables in it.
So what is recommended onUpgrade:
if not exists
(we are doing an upgrade, so the table might not exists yet, it will fail alter and drop)List<String> columns = DBUtils.GetColumns(db, TableName);
ALTER table " + TableName + " RENAME TO 'temp_" + TableName
)columns.retainAll(DBUtils.GetColumns(db, TableName));
)String cols = StringUtils.join(columns, ",");
db.execSQL(String.format(
"INSERT INTO %s (%s) SELECT %s from temp_%s",
TableName, cols, cols, TableName));
)DROP table 'temp_" + TableName
).
public static 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<String>(Arrays.asList(c.getColumnNames()));
}
} catch (Exception e) {
Log.v(tableName, e.getMessage(), e);
e.printStackTrace();
} finally {
if (c != null)
c.close();
}
return ar;
}
public static String join(List<String> list, String delim) {
StringBuilder buf = new StringBuilder();
int num = list.size();
for (int i = 0; i < num; i++) {
if (i != 0)
buf.append(delim);
buf.append((String) list.get(i));
}
return buf.toString();
}