Adding a field to an existing sqlite database

Question

I have an existing app published and I want to add location coords field to the sqlite database.

I want to know if it is possible to do this without creating a new table in the database. I don't want to overwrite the users existing database entries, I just want to add this new field for existing database entries and give it a default value.

Is this possible?

Will Tate · Accepted Answer · 2011-03-15T18:35:57.843

Yes it is,

You need to write your onUpgrade() method when you update your table. Currently, I use the following to create a new table with the new column and to copy all my current data over. Hopefully you can adapt this to your code.

@Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion);

            db.beginTransaction();
            try {
                db.execSQL("CREATE TABLE IF NOT EXISTS " + DATABASE_UPGRADE);
                List<String> columns = GetColumns(db, DATABASE_TABLE);
                db.execSQL("ALTER table " + DATABASE_TABLE + " RENAME TO 'temp_" + DATABASE_TABLE + "'");
                db.execSQL("create table " + DATABASE_UPGRADE);
                columns.retainAll(GetColumns(db, DATABASE_TABLE));
                String cols = join(columns, ","); 
                db.execSQL(String.format( "INSERT INTO %s (%s) SELECT %s from temp_%s", DATABASE_TABLE, cols, cols, DATABASE_TABLE));
                db.execSQL("DROP table 'temp_" + DATABASE_TABLE + "'");
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
        }
    }

    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();
    }

This contains onUpgrade() and two helper methods. DATABASE_UPGRADE is a string that contains the upgrade database:

private static final String DATABASE_UPGRADE =
    "notes (_id integer primary key autoincrement, "
    + "title text not null, "
    + "body text not null, "
    + "date text not null, "
    + "edit text not null, "
    + "reminder text, "
    + "img_source text, "
    + "deletion, "
    + "priority)";

Quick note about how this works:

Check if current version of the table already exists (it never should) as onUpgrade() shouldn't get called in this case.
Since that failed, get the list of columns from the current table (uses helper function GetColumns()).
rename the old table to temp_OLDTABLENAME.
Create the a new version of the database additional columns (currently empty).
get all information from the old version of the database.
insert it into new database
drop the old table (temp_OLDTABLENAME).

I tried to write this generic enough so all i have to do is update DATABASE_UPGRADE with the additional columns and this handles all the rest. It has worked for me through 3 upgrade so far.

can you explain a little about how this works, as it seems you call `create table` twice for `DATABASE_UPGRADE`? Perhaps the second one should be `create table DATABASE_TABLE`? — dave.c, Mar 15 '11 at 18:22
But before you start working, MUST SEE [this question](http://stackoverflow.com/q/14419358/1708390). A very basic problem that could arise. — Bugs Happen, Sep 01 '15 at 08:41

score 1 · Answer 2 · answered Mar 15 '11 at 18:04

1

You can add columns using ALTER TABLE.

ALTER TABLE my_table ADD COLUMN location ...;

answered Mar 15 '11 at 18:04

rmmh

6,997
26
37

score 1 · Answer 3 · answered Mar 15 '11 at 18:09

1

Use the onUpgrade method of the SQLiteOpenHelper to run the "alter table" statements.

answered Mar 15 '11 at 18:09

Robby Pond

73,164
16
126
119

Adding a field to an existing sqlite database

3 Answers3

Linked