Figure out what to do about migrations, if anything.

[JakeWharton]

Working:

• You can currently define inserts, updates, and deletes in a file and use them for migrations.

Annoying:

• Table creation generates a bunch of nonsense you might not need if the table is deleted/altered by later migrations
• Table creation is restricted to one per file

Missing:

• Detection of breaking changes in the schema and warning about it
• Generation of migrations automatically when you use refactoring tools on the create table statements?

As we discussed, we could go as far as generating the onUpgrade switch automatically. We'd probably want to create something like .sqm files (for migration) which relax the one-table-per-file restriction and don't actually generate anything for them.

Lots to think about since this probably won't happen for a while.

[AlecKazakova]

You can already omit the create table from .sq files and have a generated interface with only string constants

[tadfisher]

It would also be nice to group multiple statements in some Java collection. For example, I'd prefer to loop over an array and run execSQL on each statement it contains, since I group migration statements by the SQLiteOpenHelper version. I could then have a helper method do the looping without relying on a naming scheme and manually running each statement, which can get quite tedious and error-prone with large or complex migrations, e.g. anything needing temp tables.

[AlecKazakova]

potentially useful: http://stackoverflow.com/questions/6460671/sqlite-schema-information-metadata

[gotev]

With the current SQLDelight 0.5.1 version, to perform a table migration (which involves adding and/or removing columns or other changes) I'm using the following procedure.

Let's say this is the initial version (v1) of my table, defined in Billing.sq:

CREATE TABLE billing (
    _id INTEGER NOT NULL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    company_name TEXT,
    address TEXT,
    zip_code TEXT,
    city TEXT,
    vat TEXT,
    country TEXT,
    number_of_users INTEGER,
    FOREIGN KEY (user_id) REFERENCES user(_id) ON DELETE CASCADE
);

get_by_user_id:
SELECT *
FROM billing
WHERE user_id = ?;

This is the corresponding Java model (the code is also using AutoValue, Retrolambda, Timber, SQLBrite, and RxLifecycle):

@AutoValue
public abstract class Billing implements BillingModel {
    private static final Factory<Billing> FACTORY =
                                new Factory<>(AutoValue_Billing::new);

    @UiThread
    public static Observable<Billing> getByUserId(BriteDatabase db,
                                                  Activity activity,
                                                  long userId) {

        Timber.d("Get billing for user with ID %d", userId);

        return getObservable(db, FACTORY.get_by_user_id(userId),
                             FACTORY.get_by_user_idMapper())
                .compose(RxLifecycleAndroid.bindActivity(activity.lifecycle()))
                .observeOn(AndroidSchedulers.mainThread());
    }

    @UiThread
    private static <T> Observable<T> getObservable(BriteDatabase db,
                                                   SqlDelightStatement stmt,
                                                   final RowMapper<T> mapper) {
        return db.createQuery(stmt.tables, stmt.statement, stmt.args)
                 .mapToOne(mapper::map);
    }
}

Create the v2 Billing_v2.sq file and copy the content of Billing.sq. Replace the occurrences of billing with billing_v2. Perform the changes you need on the table by adding or removing columns and by writing an import_from_v1 SQL statement to import the old data into the new table:

CREATE TABLE billing_v2 (
    _id INTEGER NOT NULL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    company_name TEXT,
    billing_type INTEGER,
    address TEXT,
    zip_code TEXT,
    city TEXT,
    vat TEXT,
    country TEXT,
    FOREIGN KEY (user_id) REFERENCES user(_id) ON DELETE CASCADE
);

get_by_user_id:
SELECT *
FROM billing_v2
WHERE user_id = ?;

import_from_v1:
INSERT INTO billing_v2(_id, user_id, company_name, address, zip_code,
                       city, vat, country)
SELECT _id, user_id, company_name, address, zip_code, city, vat, country
FROM billing;

Then modify the Java model by replacing BillingModel with BillingModel_v2 plus some little changes, if needed:

@AutoValue
public abstract class Billing implements BillingModel_v2 {
    private static final Factory<Billing> FACTORY =
                                new Factory<>(AutoValue_Billing::new);

    @UiThread
    public static Observable<Billing> getByUserId(BriteDatabase db,
                                                  Activity activity,
                                                  long userId) {

        Timber.d("Get billing for user with ID %d", userId);

        return getObservable(db, FACTORY.get_by_user_id(userId),
                             FACTORY.get_by_user_idMapper())
                .compose(RxLifecycleAndroid.bindActivity(activity.lifecycle()))
                .observeOn(AndroidSchedulers.mainThread());
    }

    @UiThread
    private static <T> Observable<T> getObservable(BriteDatabase db,
                                                   SqlDelightStatement stmt,
                                                   final RowMapper<T> mapper) {
        return db.createQuery(stmt.tables, stmt.statement, stmt.args)
                 .mapToOne(mapper::map);
    }
}

This will be the database migration:

public void onMigration(SQLiteDatabase db) {

    db.beginTransaction();

    try {
        db.execSQL(Billing_v2Model.CREATE_TABLE);
        db.execSQL(Billing_v2Model.MIGRATE_FROM_V1);
        db.execSQL("DROP TABLE " + BillingModel.TABLE_NAME);

        db.setTransactionSuccessful();

    } finally {
        db.endTransaction();
    }
}

At this point, the migration is over.

In this way you perform few changes in the Java code and you can use SQLDelight mapping without workarounds. However, this approach is error-prone as it requires a lot of manual operations and has drawbacks. I'm going to list a few.

The old Billing.sq file still gets processed by SQLDelight and produces the interface, even if it will not be used anymore. You can be in trouble if you have auto incrementing primary keys or if other tables are referencing the old table's primary key, which requires additional and very careful manual work. SQLite has also many limitations, for example you can't remove a column in an existing table or change its type.

At the moment, I can't think of a better way to do this.

[tadfisher]

I think what you want to do is create a temp table, copy all the data from v1, drop and recreate the table with the v2 schema, and import (mapping columns) from temp into the table.

You can do this in one raw query as a transaction, but sqldelight might make some parts difficult. I would probably not model the migration or the original table in sqldelight, but instead manually perform the migration in SQLiteOpenHelper#onUpgrade, bypassing any mapping done on the sqldelight side until everything is consistent with the v2 state. This is essentially what is usually done without any Java mapping layer.

[gotev]

I think what you want to do is create a temp table, copy all the data from v1, drop and recreate the table with the v2 schema, and import (mapping columns) from temp into the table.

Exactly. Since SQLite has limited ALTER TABLE functionality, in certain situations it's the only way to go.

You can do this in one raw query as a transaction, but sqldelight might make some parts difficult. I would probably not model the migration or the original table in sqldelight, but instead manually perform the migration in SQLiteOpenHelper#onUpgrade, bypassing any mapping done on the sqldelight side until everything is consistent with the v2 state

Of course, I can do everything without using SQLDelight, and what you described is what I'm actually doing during development and when I have to do things purely on the DB level. The scenario which I've described is when you ship an app in production with a certain schema done with SQLDelight, then you have to ship the next release and you have to do data migration. To be able to use SQLDelight mapping, I could not see a better way of doing a migration, otherwise I will have to do manual mapping for every query and I couldn't use the automatically generated database models, because they will be outdated. Correct me if I'm wrong.