How do we make changes to the database

[chrisjeon]

Hi,

I'm wondering how we can make changes to our database (like add/remove columns to the tables, add additional tables, and etc.)

It doesn't seem to be as simple as bumping up the DATABASE_VERSION number. Doing that seems to "upgrade" the database, but it doesn't make any changes to the actual database schema itself.

The github repo seems to be lacking documentation on this, and I couldn't find much on Google either. Can anyone help me out? The more I learn, I'm willing to contribute to make the documentation more solid.

[geovanisouza92]

You need to use @OnUpgrade annotated method.

[chrisjeon]

@geovanisouza92 can you give an example on how we use that? Do we just declare it like that in the link? Because I tried it, and it doesn't work. It seems that we have to fill in the method some how.

[geovanisouza92]

@chrisjeon Try something like this

@Database(fileName = MyDatabase.FILE_NAME, version = MyDatabase.VERSION)
public final class MyDatabase {

    public static final int VERSION = 1;
    public static final String FILE_NAME = "my.sqlite";

    public static final String[] _MIGRATIONS = {
        // Put DDL/DML commands here, one string per VERSION increment
    };

    @OnUpgrade
    public static void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        for (int i = oldVersion; i < newVersion; i++) {
            String migration = _MIGRATIONS[i - 2];
            db.beginTransaction();
            try {
                db.execSQL(migration);
                db.setTransactionSuccessful();
            } catch (Exception e) {
                Timber.e(e, "Error executing database migration: %s", migration);
                break;
            } finally {
                db.endTransaction();
            }
        }
    }
}

[chrisjeon]

In the comment inside _MIGRATIONS, what is "Put DDL/DML commands here, one string per VERSION increment" mean? Sorry, I'm pretty new to Android.

[geovanisouza92]

DDL/DML is just SQL. :)

[chrisjeon]

Ah, so let's say I have 3 sql statements that I would like to run, I would have to bump up the version number by 3?

[geovanisouza92]

You could use DML (CREATE TABLE, ALTER TABLE) or DML (INSERT, UPDATE, DELETE) to modify your db.

Upgrade to 4, because 1 is the first version, and you are applying 3 statements. With the example code that I put above, if you set VERSION to 3, the last command will not be applied.

[chrisjeon]

Thanks dude, when I get the time, I'll do a PR of updated README.md with better documentation.