New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Figure out what to do about migrations, if anything. #89

Closed
JakeWharton opened this Issue Feb 11, 2016 · 6 comments

Comments

4 participants
@JakeWharton
Collaborator

JakeWharton commented Feb 11, 2016

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.

@AlecStrong

This comment has been minimized.

Show comment
Hide comment
@AlecStrong

AlecStrong Feb 12, 2016

Collaborator

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

Collaborator

AlecStrong commented Feb 12, 2016

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

@tadfisher

This comment has been minimized.

Show comment
Hide comment
@tadfisher

tadfisher Mar 13, 2016

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.

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.

@AlecStrong

This comment has been minimized.

Show comment
Hide comment
@gotev

This comment has been minimized.

Show comment
Hide comment
@gotev

gotev Feb 23, 2017

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.

gotev commented Feb 23, 2017

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

This comment has been minimized.

Show comment
Hide comment
@tadfisher

tadfisher Feb 25, 2017

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.

tadfisher commented Feb 25, 2017

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

This comment has been minimized.

Show comment
Hide comment
@gotev

gotev Feb 26, 2017

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.

gotev commented Feb 26, 2017

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment