CODEWITHSUNDEEP
androidsqlite
Jagadeesh
Jagadeesh

Reputation: 249

How to add a new column to Sqlite database table which does not exist in android?

On Version upgrade i want to add a new column to the the sqlite database table which is not exsit in android. if the column is already exists it should not alter the table. In onUpgrade() method i am not droping the table becoz i dont want to lose the data.

Upvotes: 4

Views: 8334

Answers (3)

Sayem
Sayem

Reputation: 5011

I used pragma to find out column existed or not.

fun SupportSQLiteDatabase.safeRunQueryToAddColumn(
tableName: String,
columnName: String,
block: () -> Any) {
    
    val cursor = query(
        "SELECT count(*) FROM pragma_table_info('$tableName') WHERE name='$columnName'", null)

    val columnExisted = if (cursor.moveToNext()) {
        cursor.getInt(0) == 1 // row found
    } else false

    cursor.close()


    if (!columnExisted) {
        block()
    } else {
        Log.w(
            "ERROR",
            "column add ignored, table : $tableName : column $columnName already existed"
        )
    }
}

Ref link

Upvotes: 0

Matt
Matt

Reputation: 1481

I pieced a few comments together to get this: 

Cursor cursor = database.rawQuery("SELECT * FROM MY_TABLE", null); // grab cursor for all data
int deleteStateColumnIndex = cursor.getColumnIndex("MISSING_COLUMN");  // see if the column is there
if (deleteStateColumnIndex < 0) { 
    // missing_column not there - add it
    database.execSQL("ALTER TABLE MY_TABLE ADD COLUMN MISSING_COLUMN int null;");
}

This intentionally ignores database version number and purely adds the column if it isn't there already (in my case, the version numbers didn't help me as the numbering had gone wonky when this column was supposed to have been added)

Upvotes: 4

Sunny
Sunny

Reputation: 14828

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

    // If you need to add a column
    if (newVersion > oldVersion) {

     if(!ColunmExists) {
        db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
     }
    }
}

Upvotes: 3

Related Questions