How to Add New Column to Android SQLite Database

How to add new Column to Android SQLite Database?

you can use ALTER TABLE function on your onUpgrade() method, like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // If you need to add a column
  if (newVersion > oldVersion) {
     db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
  }
}

Obviously, the SQLite will differ depending on the column definition.

How can I add new columns to a SQLite database after the Android app is released?

You need to changes in following method of Database Class

@Override 
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String sql = "ALTER TABLE " + TABLE_SECRET + " ADD COLUMN " +
     "name_of_column_to_be_added" + " INTEGER"; 
    db.execSQL(sql);        
}     

How to add two new Column to Android SQLite Database?

You can only add one column at a time. Split it into two ALTER TABLE statements and you should be fine.

private static final String ALTER_USER_TABLE_ADD_USER_SOCIETY = 
    "ALTER TABLE user_table ADD user_society TEXT";
private static final String ALTER_USER_TABLE_ADD_USER_STREET1 = 
    "ALTER TABLE user_table ADD user_street1 TEXT";

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{
   db.execSQL(ALTER_USER_TABLE_ADD_USER_SOCIETY);
   db.execSQL(ALTER_USER_TABLE_ADD_USER_STREET1);
}

How to add column on existing table in android SQLite?

You'll have to update your SQLite database version first of all, then that will run your onUpgrade() method, which will drop all of your data. Then the table will be remade with the new schema that you have defined in your DATABASE_CREATE string.

So the main issue that this comes with is that you'll have to find a way to recover the data that is already existing in the table as the table will be dropped. You do get to run onUpgrade before this happens though so use this method as a point to save any data you need to from the database.

static final int DATABASE_VERSION = 2;

and update your database creation string.

Android SQLite add new columns to table

Yes, if you are using a DatabaseHelper then there is a way to upgrade the database schema.

you can find plenty of tutorials on doing this.

Here is one.
Android update SQLite DB schema?

The method you are looking for is onUpgrade

And don't rename the variables which reference the actual table columns unless you change the actual column names first. You will have to use sql queries to do what you want.

Android - How to make changes in SQLite table columns.(Add new column)

When you need to update the schema of your database you need to do three things:

1. Change the statements that create the schema in onCreate(). New
   installs will go through this method call and have their schema
   created here.
2. Provide a suitable upgrade step in onUpgrade(). Existing users who already have a database and update the app will go through this method call.
3. Increase the version number of your database. Without this, Android cannot tell that an upgrade (see #2) is necessary.

It appears you did step #1 already by adding ,color text in your creation statement. It's not clear whether you did step #3 because in one version you use a literal 1 and in another you have a named constant DATABASE_VERSION. As long as this value is greater than 1, then you have completed step #3.

For step #2, you currently have code to drop your tables and go to onCreate() to build them again. This isn't necessary for the upgrade you are doing (adding a column), instead you can do

switch (oldVersion) {
    case 1:
        db.execSQL("ALTER TABLE Storage ADD COLUMN color text");
        // no break, in case user is upgrading multiple versions
}

In general this is preferable because dropping tables will result in losing user data (unless temporarily store that data elsewhere and restore it later).

---

Aside: Your code is prone to bugs because you have named constants for things like table names and column names, but you are inconsistent between using those constants vs typing them out manually in your SQL statements. For example

public static final String Tablename = "EasyPadStorage";
...
db.execSQL("create table Storage(...)");
...
db.execSQL("DROP TABLE IF EXISTS" + Tablename);

Notice the DROP TABLE statement will actually not do anything because the table was created with the name Storage--typed manually in SQL--but your constant has the value EasyPadStorage. Also, you would need a space after the EXISTS or the SQL will be invalid, which may be the source of the crash you alluded to (but we can't confirm that without a stack trace).

Adding a new column in SQLite Database

Uninstalling and reinstalling is very naive approach which will only work in development phase. When your app goes on to play store, users are not going to uninstall and reinstall the app.

Correct way to update the database for published apps is to increase your db version and use onUpgrade method to update your database.

look at this method

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

In current scenario if you just increase your db version, it will drop existing table and create a new one with new columns and specifications. The downside is that you'll lose all of your existing data.

If you want to save existing data and add new column to db, you have to do something like this -

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
  switch(oldVersion) {
    case 1:
      //add new column
      sqLiteDatabase.execSQL("ALTER TABLE "+ TABLE_NAME + " ADD COLUMN "+ NEW_COLUMN + " INTEGER/TEXT ");
  }
}

Adding a new column into sqlite produces an error: Exception while computing database live data

The reason for the failure is explained in :-

Expected:
    TableInfo{name='Crime', columns={date=Column{name='date', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, requirePolice=Column{name='requirePolice', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='null'}, suspect=Column{name='suspect', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, title=Column{name='title', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, isSolved=Column{name='isSolved', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='Crime', columns={date=Column{name='date', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, requirePolice=Column{name='requirePolice', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue=''''}, isSolved=Column{name='isSolved', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='null'}, suspect=Column{name='suspect', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, title=Column{name='title', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

More specifically the expected (what the Entity EXPECTS) is :-

requirePolice=Column{name='requirePolice', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}

Whilst the table (what was FOUND) has been altered to be :-

requirePolice=Column{name='requirePolice', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue=''''}

The main difference is that instead of a column type of INTEGER (Boolean equates to INTEGER) the ALTER has set the column type to TEXT.

• i.e. EXPECTED INTEGER (Boolean) FOUND TEXT (String)

As such you need to use :-

val migration_2_3 = object : Migration(2,3){
override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("ALTER TABLE Crime ADD COLUMN requirePolice INTEGER NOT NULL")
}

OR change the Entity to be (probably not what you want):-

@Entity
data class Crime(@PrimaryKey val id : UUID = UUID.randomUUID(),
                 var title: String ="",
                 var date: Date = Date(),
                 var isSolved: Boolean = false,
                 var requirePolice : String = "", <--- this is the new value
                 var suspect: String = ""){

    //designating a picture location p.317
    val photoFileName
        get() = "IMG_$id.jpg"
}

• It is likely that it is the ALTER statement that you want to change rather than the Entity.

How to avoid such EXPECTED/FOUND discrepancies - aka let Room do the work for you

You can actually ascertain what is EXPECTED by creating/changing the Entity and then compiling CTRL+F9, after a successful compile the underlying java is generated and this actually contains the SQL to create the table(s).

You can view the SQL by selecting Android View, locating the java (generated) folder, expanding it and then expanding the folders and then locating the class that is named the same as your @Database class suffixed with _Impl (CrimeDatabase_Impl in your case) in the class there will be a createAllTables method, this contains the EXPECTED SQL for all the tables you can then extract/copy the SQL for the definition(s) of the changed column(s) and use this in the ALTER statement (if adding a new Entity then you also have the SQL for the entire table).

• Note as per the warning DO NOT CHANGE anything in the class.

Here's an example of the above (not for your project though) :-

How can I add a new column to an Android SQLite Database

The easiest way to do this is to add some SQL to the onUpgrade() method in your SQLiteOpenHelper class. Something like:

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

    // If you need to add a column
    if (newVersion > oldVersion) {
        db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
    }
}

Obviously, the SQL will differ depending on the column definition.

---

Related Topics