Adding a field to an existing sqlite database

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 e开发者_StackOverflowxisting database entries and give it a default value.

Is this possible?

---

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:

1. Check if current version of the table already exists (it never should) as onUpgrade() shouldn't get called in this case.
2. Since that failed, get the list of columns from the current table (uses helper function GetColumns()).
3. rename the old table to temp_OLDTABLENAME.
4. Create the a new version of the database additional columns (currently empty).
5. get all information from the old version of the database.
6. insert it into new database
7. 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.

---

You can add columns using ALTER TABLE.

ALTER TABLE my_table ADD COLUMN location ...;

---

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