Support Foreign Key relationships

[sburba]

I've written a hacky workaround for the problem in this gist. I'm thinking about writing a patch to really solve this issue but I'd like to get input on the best structure for this. I think an annotation would make the most sense but there is precedent for putting it in the TableBuilder as that is the way constraints are handled.

[TimotheeJeannin]

Hi @sburba,

Thank you for the interest in ProviGen.

About foreign key support, I'm not sure an annotation would fit. SQLite doesn't support applying constrains on an already created column (http://stackoverflow.com/questions/1884818/how-do-i-add-a-foreign-key-to-an-existing-sqlite-3-6-21-table). If we had a @ForeignKey annotation, a developer would be able to add the @ForeignKey annotation after the initial table creation and the annotation would have no effect as we can't apply constraint on an existing column. So if we want the annotated contract to actually reflect the table schema we need to re-create the table with the foreign key constraint and provide to the developer a way to handle all the already present data that conflicts with the new constraint. This might lead to some complicated logic and might be rather complicated to explain what does ProviGen do and when.

I feel like it makes more sense to have TableBuilder that handles foreign keys and let the developer manage his data himself. If he really want to add a foreign key to an already filled table. He would have to re-create it and migrate the data from the old table to the new one using the logic that fit's his problem.

@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        new TableBuilder(MyContract.class)
                .addForeignKey(...)
                .createTable(database);

        // Move the data from the old table to the new table while handling conflicting cases.
        // Let the developer handle conflicts according to his logic.

        database.execSQL("DROP TABLE old_table")
}

I'm open to suggestions and ready to change my point of view if you have a better idea in mind.

Again, thank you for your support, having foreign key support would be great. :)

Tim

[sburba]

Very good point. I'll send over a pull request when I get a chance.