Using additional Room may increase size of application, means more lines required to executed. implement SQLiteOpenHelper rather then using Room Library, will give you more controll data for performance.
Please note that:
- deleting column require minimum sqlite version 3.35.0
- every android vendor may use different version of sqlite, it is possible uses lower then sqlite version 3.35.0
Suppose you have application that has published and install in world wide, with sqlite database
version 1
- id (auto increment)
- name
version 2
- id (auto increment)
- name
- datebirth
version 3
- id (auto increment)
- name
- datebirth
- address
The code
public class MySQLHelper extends SQLiteOpenHelper {
private static final String dbName = "[your_database_file_name].db";
private static final int DATABASE_VERSION = 3; // MUST HAVE
/*
* constructor
*/
public MySQLHelper(@Nullable Context context) {
// name String: of the database file, or null for an in-memory database
super(context, dbName, null, DATABASE_VERSION);
}
/*
* only call once when database not exist
*/
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
createDB(sqLiteDatabase);
}
/*
* use latest latest database structure
*/
private void createDB(SQLiteDatabase sqLiteDatabase) {
String CREATE_TABLE = "CREATE TABLE user (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"name TEXT, " +
"datebirth TEXT, " +
"address TEXT)";
db.execSQL(CREATE_TABLE);
}
/*
* only call when database exist
*/
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int curVersion, int newVersion) {
if (oldVersion < 2) {
db.execSQL("ALTER TABLE user ADD COLUMN datebirth TEXT");
}
if (oldVersion < 3) {
db.execSQL("ALTER TABLE user ADD COLUMN address TEXT");
}
}
/*
* only call when database exist
* delete / drop column only available to sqlite 3.35.0 or higher
* it will crash run on lower sqlite version
* NOT RECOMMENDED TO USE DOWNGRADE
*/
@Override
public void onDowngrade(SQLiteDatabase sqLiteDatabase, int curVersion, int newVersion) {
...
}
}
if you wish to drop column on device with sqlite lower then 3.35.0 use rename table, create table, copy values and drop table. Don't use lower DATABASE_VERSION, just increase version and remove unused column using onUpgrade. Here is the example code onUpgrade() to remove column:
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion < 2) {
db.execSQL("ALTER TABLE my_table ADD COLUMN datebirth TEXT");
}
if (oldVersion < 3) {
db.execSQL("ALTER TABLE my_table ADD COLUMN address TEXT");
}
if (oldVersion < 4) {
// --- STEP 1: Rename current table ---
db.execSQL("ALTER TABLE my_table RENAME TO temp_table");
// --- STEP 2: Create new table without 'address' ---
db.execSQL("CREATE TABLE my_table ("
+ "id INTEGER PRIMARY KEY,"
+ "name TEXT,"
+ "datebirth TEXT)");
// --- STEP 3: Copy data (only the columns you want to keep) ---
db.execSQL("INSERT INTO my_table (id, name, datebirth) " +
"SELECT id, name, datebirth FROM temp_table");
// --- STEP 4: Remove the old table ---
db.execSQL("DROP TABLE temp_table");
}
}
