Update SQLite table without losing previous data in android

Question

I am trying to add a new column in old sqlite database tables. The tables are dynamic. I mean they are created when app users add a specific data in the app.

The tables names have a similar suffix. Prefix depends upon user input. Now, in android, how can I add a new column in those tables without losing previous data?

Answer 1

I hope you already know how the onUpgrade method works. The implementation of onUpgrade is pretty simple, which detects if the database version is changed and take the changes in effect for the newer version of database. Here's a sample implementation.

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    // The last case will contain the break statement only. As the migration will take place one by one.
    // Here's a nice explanation - http://stackoverflow.com/a/26916986/3145960
    switch (oldVersion) {
        case 1:
            doSomeChangesInDBForVersion1();
        case 2:
            doSomeChangesInDBForVersion2();
            break;
    }
}

Now let us come to the specific problem that you asked. As far as I could understand, the tables in your database are dynamic which are based on user input. You want to add columns in your database table which are created based on user inputs.

In that case, you need to find out first the existing tables and then run the alter command on each of your tables. To find the existing tables in your database you might do something like the following.

Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
List<String> tableList = new ArrayList<>();

if (c.moveToFirst()) {
    while (!c.isAfterLast()) {
        tableList.add(c.getString(0));
        c.moveToNext();
    }
}

Now store the table names in an array and then run alter command on each of your table to add a new column in each of your tables.

for (String tableName : tableList) {
    db.execSQL("alter table " + tableName +"  add column new_col TEXT default null");
}

Run these executions in your onUpgrade function. I have not tested this code. Please modify as per your requirement.